Clever-Excel-Forum - ohne VBA

Biorhythmus [Excel 365]

Sun, 04 Jan 2026 07:12:05 +0000

Moin!
Wer sich rasch eine Auflistung seiner Biorhythmuswerte tabellarisch und als Diagramm darstellen möchte, braucht dazu nur sein Geburtsdatum und eine einzige Formelzelle.
• Spalte A als Datum formatieren
• in B1 kommt das Geburtsdatum
• in A3:
=LET(
GebTag; B1;
Header; {"next31". "physisch". "emotional". "intellektuell"};
next31; SEQUENZ(31; ; HEUTE());
Alter; next31 - GebTag;
Data; HSTAPELN(
next31;
RUNDEN(SIN(2 * PI() * Alter / {23. 28. 33}); 2)
);
VSTAPELN(Header; Data)
)

Wenn man jetzt A3 markiert und Einfügen → Empfohlene Diagramme wählt, hat man seinen aktuellen Biorhythmus für die nächsten 31 Tage.

ScrShot1.png (Größe: 51,56 KB / Downloads: 37)

Sternebewertung (★★★☆☆) per Zelldropdown

Sun, 14 Sep 2025 07:46:13 +0000

Moin!
In Google Sheets gibt es mittels
Einfügen → Smartchips → Bewertung
eine einfache Möglichkeit, in einer Liste eine Bewertung abzugeben.

Dies wollte ich auch in Excel einbinden.
Eine 5-Sterne-Bewertung benötigt 6 Varianten (0 bis 5).
• der "leere" Stern ☆ ist Unizeichen 9734
• der gefüllte Stern ★ ist Unizeichen 9733

Zunächst für alte Versionen bis Excel 2019.
Neues Blatt.
Hier werden 3 Formeln benötigt, die auf 6 Zeilen aufgezogen werden:
A1[:A6] =WIEDERHOLEN(UNIZEICHEN(9733);ZEILE(X1)-1)
B1[:B6] =WIEDERHOLEN(UNIZEICHEN(9734);6-ZEILE(X1))
C1[:C6] =A1&B1

Den Bereich $C$1:$C$6 übergebe ich Datengültigkeit → Liste

Mit aktuellen Excel-Versionen geht es mit einer einzigen Spillformel in A1:
=LET(
StVoll;WIEDERHOLEN(UNIZEICHEN(9733);SEQUENZ(6;;0));
StLeer;WIEDERHOLEN(UNIZEICHEN(9734);SEQUENZ(6;;5;-1));
NACHZEILE(HSTAPELN(StVoll;StLeer);TEXTKETTE))

Den Spillbereich übergebe ich mit =$A$1#

Wer seine Zuneigung lieber mit Herzchen ausdrücken möchte:
♡ ist Unizeichen 9825
♥ ist Unizeichen 9829

Falls es einer benötigen sollte:
Dateien im Anhang.

  ScrShot1.png (Größe: 8,89 KB / Downloads: 31)

Gruß Ralf

  Bewertung_ab_2021.xlsx (Größe: 12,65 KB / Downloads: 11)

  Bewertung_bis_2019.xlsx (Größe: 12,32 KB / Downloads: 4)

Tiefgestellte Ziffern per Formel (Na2CO3 → Na₂CO₃))

Mon, 26 May 2025 15:00:05 +0000

Zugegeben ein Randthema!
Wer chemische Verbindungen in Excel darstellen will, hat ein Problem mit den tiefgestellten Ziffern.
Sie lassen sich mühsam im Bearbeitungsmodus markieren, Strg+1, "Tiefgestellt".
Da die Zeichen (0-9 → ASCII-Code 48-57) nicht geändert werden, sondern nur das Format, kommen wir bei längeren Listen schnell zu einem Format-Komplexitätsproblem, denn Excel ist keine Textverarbeitung.

Im Unicode-Zeichensatz gibt es "echte" Zeichen der Ziffern 0-9.
Diese entsprechen den UNIZEICHEN 8320-8329.
Meine Überlegung war, dass, wenn sich eine Ziffer mittels Multiplikation mit 1 in eine Zahl umwandeln lässt, sie mittels UNIZEICHEN(8320+Zahl) tiefstellen lässt, alle anderen Zeichen bleiben unverändert.

Dies erreiche ich mit
A1: Na2CO3
=LET(
x; TEIL(A1; SEQUENZ(LÄNGE(A1)); 1);
TEXTKETTE(WENNFEHLER(UNIZEICHEN(1 * x + 8320); x))
)

PDF-Kontoauszug von Trade Republic aufbereiten - Update

Mon, 09 Dec 2024 19:01:28 +0000

Da die Tagesgeldzinsen ziemlich ordentlich und Order-/Sparplangebühren sehr niedrig sind, habe ich mich vor einigen Monaten überzeugen lassen und mich auch bei TR angemeldet. Ein kleines Haar in der Suppe ist jedoch, dass aktuell Kontoauszüge nur als PDF und nicht als Datei zur Verfügung gestellt werden.

Aber da ja das in Excel integrierte ETL-Tool Power Query auch PDF-Dokumente importieren kann, habe ich mich dran gemacht und einen Import realisiert, der zumindest meinen Ansprüchen gerecht wird.

Die Hauptabfrage habe ich so aufgebaut, dass entweder ein PDF-Dokument oder von mir aufbereitete Testdaten importiert werden können.

Mittels Ersetzungstabellen können importierte Texte, generell bzw. Positionsabhängig in eigene übersetzt werden. Die Notwendigkeit der Positionsabhängigen Ersetzungen ergibt sich aus dem Umstand, dass erst seit Juli beim Kauf eines ETFs unterschieden wird, ob es sich um eine einzelne Order, oder um das Resultat eines ausgeführten Sparplans handelt.

In der Mappe die ich hochlade, sind einige Erläuterungen enthalten. Somit sollte eigentlich jeder andere PR-Kunde, der sich über die Handy-APP einen Kontoauszug erstellt, diesen in eine auswertbare Tabelle umwandeln können.

cef - Kontoauszug Trade Republic (PQ).xlsx (Größe: 99,65 KB / Downloads: 25)

[365] Ermittlung der Gradtagszahlen im Kalenderjahr

Wed, 01 Nov 2023 08:11:08 +0000

Betrifft hauptsächlich Vermieter/Verwalter, die Heizungskosten bei einem unterjährigen Ein- / Auszug ermitteln wollen, wenn keine Zwischenablesung erfolgte.
Da im Februar meist mehr geheizt wird als im August, gibt es in der DIN 4713-5 eine Gradtagstabelle.
Meine Tabelle ermittelt den anteiligen Verbrauch.

	A	B	C	D	E	F	G	H	I	J	K
1	Einzug	Auszug	Tage	Anteil_Jahr	Anteil_GTZ		2020	Monat	Monat ‰	Tage	Gradtagzahl ‰
2	15.01.2020	14.10.2020	274	74,9%	59,9%		Januar 20	1	170	31	5,48
3	01.05.2020	31.12.2020	245	66,9%	47,0%		Februar 20	2	150	29	5,17
4	01.01.2020	30.06.2020	182	49,7%	58,3%		März 20	3	130	31	4,19
5							April 20	4	80	30	2,67
6							Mai 20	5	40	31	1,29
7							Juni 20	6	13,04	30	0,43
8							Juli 20	7	13,48	31	0,43
9							August 20	8	13,48	31	0,43
10							September 20	9	30	30	1,00
11							Oktober 20	10	80	31	2,58
12							November 20	11	120	30	4,00
13							Dezember 20	12	160	31	5,16
14									1000	366	1000

Zelle	Formel
C2	=B2-A2+1
D2	=C2/J$14
E2	=SUMME(XVERWEIS(MONAT(SEQUENZ(C2;;A2));H$2#;K$2#))/1000
G2	=MONATSENDE(DATUM(G1;SEQUENZ(12);1);0)
H2	=SEQUENZ(12)
J2	=TAG(G2#)
K2	=I2:I13/J2#
I14	=SUMME(I2:I13)
J14	=SUMME(J2#)
K14	=SUMME(J2#*K2#)

Gruß Ralf

Gradtagszahlen.xlsx (Größe: 11,06 KB / Downloads: 6)

Auswertung Lotto

Mon, 20 Mar 2023 11:16:16 +0000

Hallo zusammen,

in den vergangenen Jahren wurden zur Auswertung von Lotto-Tipps immer wieder Formeln mit SUMMENPRODUKT vorgestellt.
Bei neuerem Excel geht auch das:

Code:
=SUMME(--ISTZAHL(VERGLEICH($A$1:$A$6;$D$1:$D$6;0)))

Hierbei entfällt auch, dass die Anordnung der Reihen für die Formel wichtig ist, was ja bei SUMMENPRODUKT der Fall war.

Auswertung_Lotto.xlsx (Größe: 15,82 KB / Downloads: 14)

Mehrfach abhängige Dropdown

Wed, 10 Aug 2022 18:26:08 +0000

Hallo

Mehrfach abhängige Dropdown werden immer wieder noch angefragt.
Hier mal ein Beispiel wie man es ab Excel2021 machen könnte.

Abhängige Dropdown ab Excel 2021.xlsx (Größe: 15,01 KB / Downloads: 55)

[Excel] Text splitten spezial (1)

Sat, 02 Oct 2021 20:55:36 +0000

Moin

Im MrExcel-Forum gab es eine Anfrage zur Text-Splittung.
Ausgangslage ist ein Text der einen Datensatz mit mehreren Nodes enthält (Überschriften und Daten). Vor und nach dem Datensatz stehen weitere Text-Nodes.
Erhöhte Schwierigkeit: Die Anzahl der Text-Nodes vor und nach dem Datensatz ist nicht einheitlich.

Arbeitsblatt mit dem Namen 'Tabelle1'
	A	B	C	D	E	F	G
1	Bla bla bla bla:::Name:::Rudi Rastlos:::ID:::8524:::Alter:::Blubb blubb blubb
2
3	Name	3
4	ID	5
5	Alter	7
6
7	Name	ID	Alter			Methode	Formel-Länge
8	Rudi Rastlos	8524	55			position() mod 2	163
9	Rudi Rastlos	8524	55			INDEX()	88
10	Rudi Rastlos	8524	55			postition() XVERWEIS()	232
11	Rudi Rastlos	8524	55			following-sibling	106
12
13
14
15	Bla bla bla bla:::Wau wau wau:::Name:::Rudi Rastlos:::ID:::8524:::Alter:::Blubb blubb blubb
16
17	Name	4
18	ID	6
19	Alter	8
20
21	Name	ID	Alter			Methode	Formel-Länge
22	Rudi Rastlos	8524	55			position() mod 2	167
23	Rudi Rastlos	8524	55			INDEX()	91
24	Rudi Rastlos	8524	55			postition() XVERWEIS()	236
25	Rudi Rastlos	8524	55			following-sibling	109
26
27
28
29	Bla bla bla bla:::Name:::Rudi Rastlos:::ID:::8524:::Alter:::Blubb blubb blubb:::Miau Miau Miau:::Grr Grr Grr
30
31	Name	3
32	ID	5
33	Alter	7
34
35	Name	ID	Alter			Methode	Formel-Länge
36	Rudi Rastlos	8524	55			position() mod 2	167
37	Rudi Rastlos	8524	55			INDEX()	91
38	Rudi Rastlos	8524	55			postition() XVERWEIS()	236
39	Rudi Rastlos	8524	55			following-sibling	109

Zelle	Formel
B3	=VERGLEICH(A3;XMLFILTERN("<y><z>"&WECHSELN(A1;":::";"</z><z>")&"</z></y>";"//z");0)+1
A8	=MTRANS(XMLFILTERN("<y><z>"&WECHSELN(A1;":::";"</z><z>")&"</z></y>";"//z[position() mod 2="&--ISTUNGERADE(B3)&" and position()>"&B3-1&" and position()<"&B5+1&"]"))
A9	=MTRANS(INDEX(XMLFILTERN("<y><z>"&WECHSELN(A1;":::";"</z><z>")&"</z></y>";"//z");B3:B5))
A10	=LET( a;XMLFILTERN("<y><z>"&WECHSELN(A1;":::";"</z><z>")&"</z></y>";"//z[position()>0 and position()<8]"); b;XMLFILTERN("<y><z>"&WECHSELN(A1;":::";"</z><z>")&"</z></y>";"//z[position()>1 and position()<9]"); XVERWEIS($A$7:$C$7;a;b))
A11	=XMLFILTERN("<y><z>"&WECHSELN(A1;":::";"</z><z>")&"</z></y>";"//z[.='"&A7:C7&"']/following-sibling::z[1]")
G8	=LÄNGE(FORMELTEXT(A8))

Verwendete Systemkomponenten: [Windows (32-bit) NT 10.00] MS Excel 2016

Diese Tabelle wurde mit Tab2Html (v2.6.2) erstellt. ©Gerd alias Bamberg

Das zweite Beispiel enthält einen weiteren Node vor dem Datensatz.
Das dritte Beispiel enthält zwei weitere Nodes nach dem Datensatz.
Die ersten beiden Formeln scheiterten ursprünglich an diesen Beispielen weil Werte fest in die Formeln codiert wurden. Durch Dynamisierung (goldfarbene Felder) wurde dieses Manko behoben.
Die ausgewiesene Formellänge bezieht sich auf die Formel in der jeweiligen Zeile. Für Formel 1 und Formel 2 müssten noch die Formeln der Hilfszellen (organgefarbene Felder) mit einbezogen werden.
Alle Formeln laufen über. Es wird also Office 365 benötigt - eventuell funktioniert es auch mit dem neuen Office 2021.

Fazit: xpath ist ziemlich mächtig wenn man alle Möglichkeiten kennt.

PS: Formel 1 bis Formel 3 wurden von mir beigetragen, Formel 4 wurde vom User Fluff beigetragen.

Text splitten und bestimmte Felder extrahieren.xlsm (Größe: 19,84 KB / Downloads: 8)

[E365] Liste aller festen Feiertage mit einer Formel

Mon, 24 May 2021 06:30:29 +0000

Statt 6 einzelner Formeln für die festen Feiertage reicht in Excel 365 eine einzige Formelzelle:
=DATUM(A1;1;1)+{0;120;275;304;358;359}+WENN(MONAT(DATUM(A1;2;29))=2;{0;1;1;1;1;1})

	A
1	2021
2	Freitag, 1. Januar 2021
3	Samstag, 1. Mai 2021
4	Sonntag, 3. Oktober 2021
5	Montag, 1. November 2021
6	Samstag, 25. Dezember 2021
7	Sonntag, 26. Dezember 2021

Formeln der Tabelle

Zelle	Formel
A2	=DATUM(A1;1;1)+{0;120;275;304;358;359}+WENN(MONAT(DATUM(A1;2;29))=2;{0;1;1;1;1;1})

Folgendes gilt (zunächst) nur für den Beta-Kanal, aber LAMBDA() dürfte bald "für den Rest" freigeschaltet werden.

Ich benötige 3 benannte Lambdas:

1. obige als FFT benannt
=LAMBDA(j;
LET(SchaltJahr;MONAT(DATUM(j;2;29))=2;
FTfest;DATUM(j;1;1)+{0;120;275;304;358;359}+WENN(SchaltJahr;{0;1;1;1;1;1});
FTfest)
)

2. für die FT, die von Ostern abhängig sind, OsterFT benannt
=LAMBDA(j;
LET(A;GANZZAHL(j/100);
B;15+GANZZAHL((3*A+3)/4)-GANZZAHL((8*A+13)/25);
C;2-GANZZAHL((3*A+3)/4);
D;REST(j;19);
E;REST(19*D+B;30);
F;GANZZAHL((E+GANZZAHL(D/11))/29);
G;21+E-F;
H;7-REST(j+GANZZAHL(j/4)+C;7);
I;7-REST(G-H;7);
O;DATUM(j;3;G+I);
OFt;O+{-2;0;1;39;49;50;60};
OFt)
)

3. Eine LAMBDA, die Listen verbindet (stammt von LCohen), List.Join benannt:
=LAMBDA(Liste1;Liste2;

LET(i;ZEILEN(Liste1);
j;ZEILEN(Liste2);
k;SEQUENZ(i+j);
WENN(k<=i;INDEX(Liste1;k);INDEX(Liste2;k-i)))
)

Und schon kann man mittels einer Formel alle FT eines Jahres erstellen.

	A
1	2021
2	Freitag, 1. Januar 2021
3	Freitag, 2. April 2021
4	Sonntag, 4. April 2021
5	Montag, 5. April 2021
6	Samstag, 1. Mai 2021
7	Donnerstag, 13. Mai 2021
8	Sonntag, 23. Mai 2021
9	Montag, 24. Mai 2021
10	Donnerstag, 3. Juni 2021
11	Sonntag, 3. Oktober 2021
12	Montag, 1. November 2021
13	Samstag, 25. Dezember 2021
14	Sonntag, 26. Dezember 2021

Formeln der Tabelle

Zelle	Formel
A2	=SORTIEREN(List.Join(FFT(A1);OsterFT(A1)))

Namen in Formeln

Zelle	Name	Bezieht sich auf
A2	FFT	=LAMBDA(j; LET(SchaltJahr;MONAT(DATUM(j;2;29))=2; FTfest;DATUM(j;1;1)+{0;120;275;304;358;359}+WENN(SchaltJahr;{0;1;1;1;1;1}); FTfest) )
A2	List.Join	=LAMBDA(Liste1;Liste2; LET(i;ZEILEN(Liste1); j;ZEILEN(Liste2); k;SEQUENZ(i+j); WENN(k<=i;INDEX(Liste1;k);INDEX(Liste2;k-i)) ) )
A2	OsterFT	=LAMBDA(j; LET(A;GANZZAHL(j/100); B;15+GANZZAHL((3A+3)/4)-GANZZAHL((8A+13)/25); C;2-GANZZAHL((3A+3)/4); D;REST(j;19); E;REST(19D+B;30); F;GANZZAHL((E+GANZZAHL(D/11))/29); G;21+E-F; H;7-REST(j+GANZZAHL(j/4)+C;7); I;7-REST(G-H;7); O;DATUM(j;3;G+I); OFt;O+{-2;0;1;39;49;50;60}; OFt) )

Namen verstehen

Ich hänge mal meine Testdatei an.

Feiertagsliste.xlsx (Größe: 10,2 KB / Downloads: 19)

[Excel] Liste der Tabellenblattnamen

Fri, 02 Apr 2021 09:50:03 +0000

Ziel ist eine Liste aller Tabellenblätter in einem einzigen Tabellenblatt anzuzeigen.
Hier wird zunächst die (umständliche) alte Version vorgestellt, um folgend dann die enorme Vereinfachung mittels Excel 365 aufzuzeigen.

Vorweg:
Dies ist ja problemlos durch den weitgehend unbekannten Rechtsklick auf die Tabellennavigation (◄► unten links) möglich.
Hier geht es aber um die Auflistung in Zellen.
Mögliche Anwendung: Zugriff auf Tabellenblätter mittels INDIREKT().
Oder Integration des Tabellennamens in HYPERLINK().

Ausgangslage: gespeicherte Mappe mit den Blättern Montag, Dienstag, Samstag und Sonntag
Grundsätzlich notwendig ist zunächst die Vergabe eines Namen, der eine alte Excel4-Makro-Funktion enthält:
► Formeln → Namensmanager → Neu
► Name: x
► bezieht sich auf: =ARBEITSMAPPE.ZUORDNEN(1+0*JETZT())
(durch 0*JETZT() wird es volatil, heißt, eine Änderung eines Namens wird sofort in die Liste übernommen)

Versionen bis Excel 2019 (in Excel 2022 wird sich wohl auch nichts ändern):
In die erste Zelle der Auflistung:
=WENN(ZEILE(X1)>ANZAHL2(x);"";TEIL(INDEX(x;ZEILE(X1));FINDEN("]";INDEX(x;ZEILE(X1)))+1;31))
Die 31 ist die zulässige Maximallänge eines Blattnamen.
Die Formel so weit als nötig herunterziehen.
Man kann einen Puffer einbauen und sie großzügig ziehen, dann erfolgt die Ausgabe des Leerstrings "".

In Excel 365 wird die notwendige Formel enorm verkürzt.
Dies reicht in einer einzigen Formelzelle:
=MTRANS(TEIL(x;FINDEN("]";x)+1;31))

	A	B
1	"alt"	Excel365
2	Montag	Montag
3	Dienstag	Dienstag
4	Samstag	Samstag
5	Sonntag	Sonntag
6
7

Name	Bezug
x	=ARBEITSMAPPE.ZUORDNEN(1+0*JETZT())

Zelle	Formel
A2	=WENN(ZEILE(X1)>ANZAHL2(x);"";TEIL(INDEX(x;ZEILE(X1));FINDEN("]";INDEX(x;ZEILE(X1)))+1;31))
B2	=MTRANS(TEIL(x;FINDEN("]";x)+1;31))

Gruß Ralf

[Excel] Autofilter Formel Aggregat

Mon, 01 Mar 2021 07:28:18 +0000

Zuweilen steht die Aufgabe, gefilterte Daten in einen anderen Tabellenbereich zu übernehmen.

Im Excel 365 gibt es neue Möglichkeiten mit der Funktion FILTER. Diese ist leider in älteren und auch in der aktuellen Desktop-Version 2019 nicht enthalten. Für den Austausch müsste man demzufolge auf eine andere Formellösung zurückgreifen.

Im Netz findet man zuweilen Formellösungen für den Autofilter. Diese basieren oft auf den eingeschränkten Möglichkeiten älterer Excelversionen vor 2010.

Seit 2010 bietet die AGGREGAT - Funktion in Verbindung mit der INDEX - Funktion alternative Möglichkeiten zum Filtern nach einem oder mehreren Kriterien. Hier habe ich ein Beispiel für eine einfache Filterung.

In den Spalten A und B habe ich eine Datenliste, aus der ich meine Daten extrahieren will. In D2 habe ich (m)ich dazu eingetragen. In Spalte E habe ich eine zum Filtern passende Funktion eingetragen - hier nur für E2 angezeigt. De Funktion kann nach Eingabe so weit erforderlich nach unten gezogen werden.

Zu den Parametern in der verwendeten AGGREGAT-FUNKTION:

15 entspricht der Funktion KKLEINSTE
6 steht für ignorieren von Fehlerwerten

ZEILE($A$2:$A$10)
ist das Array, in dem die Daten stehen
-1
ist ein Zeilenversatz da hier im Beispiel die Arrayangabe in Zeile 2 beginnt und die Auswertung aber mit dem ersten Element beginnen soll.

/($A$2:$A$10=$D$2)
ist die Angabe des Vergleichs- / Filterwertes und des Bereichs, in dem der Filterwert verglichen wird
Hier können z.B. auch mehrere Filter für verschiedene Spalten gesetzt werden, z.B. /(Bereich2=Filter2)/(Bereich3=Filter3)
ZEILE(A1)
ist der Index für KKLEINSTE

Hinweis zum Array ZEILE($A$2:$A$10) und dem Subtrahend (Zeilenversatz) 1
Das Array kennzeichnet genau genommen hier den Zeilenumfang / die Zeilenanzahl des Bereichs, in dem die Daten stehen. Ich könnte stattdessen auch ZEILE($X$66:$X$74)-65 schreiben - der Zeilenversatz ist dann also auch entsprechend größer - oder ZEILE($A$1:$A$9) und das dann ohne Zeilenversatz.
Die Angabe ZEILE($A$2:$A$10) - 1 erhöht aber m.E. die Nachvollziehbarkeit der Formel. Ich muss weder schauen, was in $X$66 steht noch grübeln, ob bei $A$1:$A$9 auch Zeile 10 berücksichtigt wird ... Dass hier im Beispiel die Überschrift nicht in die Formel einbezogen ist, soll zeigen, dass man mit dieser Formel sehr einfach auch Bereiche betrachten kann, die nicht in Zeile 1 beginnen.

Arbeitsblatt mit dem Namen 'Tabelle3'
	A	B	C	D	E
1	Wer	Wann		Wer	Wann
2	ich	01. Mrz		ich	01. Mrz
3	du	02. Mrz			04. Mrz
4	er	03. Mrz			07. Mrz
5	ich	04. Mrz
6	du	05. Mrz
7	er	06. Mrz
8	ich	07. Mrz
9	du	08. Mrz
10	er	09. Mrz

Zelle	Formel
E2	=WENNFEHLER(INDEX(B$2:B$10;AGGREGAT(15;6;ZEILE($A$2:$A$10)-1/($A$2:$A$10=$D$2);ZEILE(A1)));"")

Verwendete Systemkomponenten: [Windows (32-bit) NT 10.00] MS Excel 2016

Diese Tabelle wurde mit Tab2Html (v2.6.2) erstellt. ©Gerd alias Bamberg

Lottozahlengenerator [Excel 365 oder Online]

Thu, 13 Aug 2020 03:27:18 +0000

Moin!
Vorab:
Wer nach "excel lottozahlen generieren" sucht, findet über 10.000 Lösungen.
Meine ist nicht dabei. :21:
Mit einem aktuellen Excel kann man dies mit einer einzigen Formelzelle erschlagen!
(Mittels F9 erfolgt eine neue "Ziehung")

Hier mal für 6 aus 49:

	A
1	46
2	22
3	25
4	43
5	35
6	28

Zelle	Formel
A1	=INDEX(SORTIERENNACH(SEQUENZ(49);ZUFALLSMATRIX(49));SEQUENZ(6))

Sollen die Zahlen in einer Zeile stehen, dann:

	B	C	D	E	F	G
1	39	29	45	36	26	14

Zelle	Formel
B1	=INDEX(SORTIERENNACH(SEQUENZ(;49);ZUFALLSMATRIX(;49));SEQUENZ(;6))

Falls eine sortierte Ausgabe gewünscht ist, dann muss noch ein SORTIEREN() herumgewickelt werden:
=SORTIEREN(Formel)

Gruß Ralf

[Excel] Zeitstempel / Datumsstempel per Formel

Wed, 21 Nov 2018 06:10:59 +0000

Hallöchen,

Excel verwendet für die Ermittlung der aktuellen Zeit oder des aktuellen Datums die Funktionen JETZT() und HEUTE(). Die Funktionen aktualisieren den Zelleintrag bei Neuberechnung des Blattes. Um die Eintragungen festzuschreiben, gibt es neben einer Makrolösung auch eine Formellösung:

Arbeitsblatt mit dem Namen 'Tabelle1'
	A	B
1	17.03.2014	a

Zelle	Formel
A1	=WENN(B1<>"";WENN(A1="";JETZT();A1);"")

Verwendete Systemkomponenten: [Windows (32-bit) NT 10.00] MS Excel 2016

Wie zu sehen ist, bezieht sich die Formel auch auf die Zelle, in der sie steht. Das verursacht im Normalfall jedoch einen Zirkelbezug nebst entsprechender Fehlermeldung. Um das zu vermeiden, muss eine Excel-Einstellung verändert werden.
Unter Excel | Optionen | Formeln muss die Iteration aktiviert werden. Ich empfehle, auch die Iterationszahl auf 1 zu reduzieren.

Iterationen.png (Größe: 13,55 KB / Downloads: 158)

[Excel] SVERWEIS bringt 0 statt leer

Sat, 14 Jan 2017 05:32:29 +0000

Hallöchen,

Beim Auslesen von Daten aus einem Datenbestand, in dem man leere Zellen und Zellen mit der Zahl 0 differenziert, kommt man mit einer einfachen SVERWEIS-Formel nicht zum Ziel. SVERWEIS bringt in beiden Fällen 0 als Ergebnis.
Eine Ergänzung mit ISTZAHL hilft hier. Das ungewöhnliche ist, dass ISTZAHL innerhalb der Formel in Bezug auf den SVERWEIS FALSCH liefert, obwohl der SVERWEIS ohne diese Prüfung augenscheinlich die Zahl 0 ergibt - siehe Formel0. Das liegt aber weniger am SVERWEIS als am Umgang von Excel mit den Funktionen bzw. dem Eintrag der Ergebnisse der Funktionen in die Zellen.

Arbeitsblatt mit dem Namen 'Tabelle1'
	A	B	C
1	Formel0	0	WAHR
2	Formel1		FALSCH
3		Oder komplexer
4	Formel2		FALSCH
5		Oder einfacher
6	Formel3		FALSCH
7		(von shift-del)

Zelle	Formel
B1	=SVERWEIS($A1;Tabelle2!$A$1:$B$10;SPALTE();)
C1	=ISTZAHL(B1)
B2	=WENN(ISTZAHL(SVERWEIS($A2;Tabelle2!$A$1:$B$10;SPALTE();));SVERWEIS($A2;Tabelle2!$A$1:$B$10;SPALTE(););"")
C2	=ISTZAHL(B2)
B4	=WENN(INDIREKT(ADRESSE(VERGLEICH($A4;Tabelle2!$A$1:$A$10;);SPALTE();;;"Tabelle2"))="";"";SVERWEIS($A4;Tabelle2!$A$1:$B$10;SPALTE();))
C4	=ISTZAHL(B4)
B6	=WENNFEHLER(--(SVERWEIS($A6;Tabelle2!$A$1:$B$10;SPALTE();)&"");"")
C6	=ISTZAHL(B6)

Arbeitsblatt mit dem Namen 'Tabelle2'
	A	B
1	Formel0
2	Formel1
3	Formel2
4	Formel3

[Excel] einfaches Zählen von Zellinhalten

Sat, 14 Jan 2017 05:12:38 +0000

Hallo,

anbei habe ich eine kurze Übersicht von einfachen Möglichkeiten, Inhalte von Zellen zu zählen. Hintergrund dieses Beitrages war für mich u.a. die Frage nach dem Zählen von "leeren" Zellen oder korrekt formuliert von Zellen, die Formeln enthalten und als Ergebnis "" bringen. Zur Vervollständigung habe ich gleich weitere Zählmöglichkeiten aufgeführt, ohne jetzt auf komplexere Möglichkeiten und Bedingungen einzugehen.

Arbeitsblatt mit dem Namen 'Tabelle1'
	A	B	C	D
1	Text	Anzahl	1	nur Zahlen
2	12	Anzahl2	5	alle Einträge einschl. Formeln mit Ergebnis ""
3		Zählenwenn	5	alle Einträge einschl. Formeln mit Ergebnis ""
4	#DIV/0!	Zählenwenn	1	nur Texte mit min. 1 Zeichen - dafür steht das ?
5	#NAME?	Zählenwenn + Anzahl	2	Texte mit min. ein Zeichen und Zahlen
6		Zählenwenn	0	Fehler mit #* zählen geht nicht
7		Zählenwenn	1	Zählenwenn #DIV/0!
8		Summe Istfehler	2	Anzahl aller Fehler

Zelle	Formel
C1	=ANZAHL(A1:A5)
C2	=ANZAHL2(A1:A5)
A3	=""
C3	=ZÄHLENWENN(A1:A5;"<>")
A4	=2/0
C4	=ZÄHLENWENN(A1:A5;">?*")
A5	=a0
C5	=ZÄHLENWENN(A1:A5;">?*")+ANZAHL(A1:A5)
C6	=ZÄHLENWENN(A1:A5;"#*")
C7	=ZÄHLENWENN(A2:A5;1/0)
C8	{=SUMME(ISTFEHLER(A1:A5)*1)}

Achtung, Matrixformel enthalten!

Die geschweiften Klammern{} werden nicht eingegeben.

Verlassen Sie den Zelleneditor mit Strg+Shift + Enter, statt Enter alleine.