Hinweise zu den Beispielen finden Sie hier: Home: VBA-Beispiele
Kategorie: Arrays ▸ Arrayformeln
Was sind die Arrayformeln, die ab Excel 365 eingeführt wurden?
=SUMMEWENN(B2:B13;"Juni";C2:C13)
Auch hier haben wir in einer Zelle ein Ergebnis, nämlich die Summe. Der Unterschied bis hierher liegt nur in den fehlenden geschweiften Klammern.
Der größte Unterschied wird jedoch deutlich, wenn in eine Zelle folgende einfache Formel eingegeben wird:
=A2:C13
Geschieht dies bei einer Excelinstallation zum ersten Mal, erscheint eine Meldung:
Formel übergelaufen: Ihre Formel hat mehrere Werte zurückgegeben, weshalb wir sie in die benachbarten leeren Zellen haben überlaufen lassen.
Das Ergebnis wird dann auch deutlich: Die Formel liefert alle Werte, die sich im Bereich befinden, der in der Formel angegeben ist - von der 1 in A2 bis zur 600 in C13. Der erste Wert steht dabei in der Zelle mit der Formel, die anderen Werte rechts neben und unter dieser Zelle - die Formel ist übergelaufen. Das Verhalten ist vergleichbar mit der CSS-Eigenschaft float: left;. Allerdings steht die Formel tatsächlich nur in der einen Zelle; sie kann auch nur dort bearbeitet werden. Sichtbar wird das, wenn im Ergebnis der Inhalt einer anderen Zelle gelöscht werden soll - es geht nicht.
Dieses Verhalten, dass mehrere Ergebnisse ausgegeben werden sollen, kann vielfältig genutzt werden. Beispiel für eine weitere einfache Formel:
=WENN(C2:C13>400;B2:B13;"")
In Zeile 2 neben der Basistabelle liefert sie in den entsprechenden Zeilen die Monate, bei denen die Beträge größer als 400 sind. Das Gleiche passiert natürlich auch, wenn die Formel in eine andere Zeile eingetragen wird - nur hat man dann den Offset, weil die Ergebnisse ab der Zelle mit der Formel angezeigt werden.
Diese Formel würde die Zahlen in den entsprechenden Zeilen anzeigen, die zum "Juni" gehören:
=WENN(B2:B13="Juni";C2:C13;"")
Natürlich kann die als Arrayfunktion verwendete Wenn-Funktion auch in anderen Funktionen eingesetzt werden. Hier werden alle Zahlen aus C addiert, wenn sie größer als 400 sind:
=SUMME(WENN(C2:C13>400;C2:C13))
In dem Fall haben wir natürlich wieder nur ein Ergebnis, nicht eine Matrix aus mehreren Werten
Dieser Fehler erscheint, wenn Excel eine Arrayformel nicht berechnen oder deren Ergebnisse nicht darstellen kann.
Meist wird dies der Fall sein, wenn schlicht zu wenig Platz für die Ausgabe ist. Dann erscheint in der Zelle mit der Formel die Meldung und es wird mit einem Rahmen dargestellt, wie viel Platz benötigt würde. Im Weg können dabei Zellinhalte sein, aber auch das Ende der Tabelle, verbundene Zellen usw.
Auch wenn in einer Arrayfunktion Zufallszahlen oder andere Ergebnisse verwendet werden, kann der Fehler erscheinen. Dann ist die innere Funktion (zum Beispiel eine für Zufallszahlen) noch nicht fertig, während die äußere aber schon rechnen möchte. Da aber die Ergebnisse der inneren Funktion fehlen bzw. unvollständig sind, kommt diese Meldung.
Möglicherweise haben Sie schon die Meldung bekommen:
Warum ist der @-Operator hier? Wir haben ein Upgrade der Formelsprache von Excel durchgeführt. Hieraus resultiert, dass Ihnen vielleicht in manchen Formeln der @-Operator auffallen wird. Ihre Formeln verhalten sich auf dieselbe Weise wie immer.
Gleichzeitig kann es sein, dass sich in Ihren Formeln auf einmal @-Zeichen nach den Gleichheitszeichen befinden, die Sie gar nicht eingetragen haben.
Dieses Verhalten hängt unmittelbar mit den Arrayformeln bzw. Arrayfunktionen zusammen. Nehmen wir obige Formel:
=WENN(C2:C13>400;C2:C13;"")
Sie liefert, wie wir gesehen haben, alle Werte, die größer als 400 sind, in und unter der Zelle mit der Formel. Was nun aber, wenn wir nur ein Ergebnis benötigen, das erste? Dieser Fall kann auftreten, wenn Formeln über mehrere Zellen gezogen oder wenn Funktionen verschachtelt werden sollen. In diesem Fall setzen wir direkt hinter das Gleichheitszeichen das @-Zeichen:
=@WENN(C2:C13>400;C2:C13;"")
Nun haben wir nur noch ein Ergebnis; in diesem Fall das erste, die 500.
Um bei diesem Beispiel zu bleiben: Setzen wir in der Formel die letzte Zeile absolut (durch das $-Zeichen), kann die Formel nach unten gezogen werden und wir haben als Ergebnis immer den Wert aus den Zellen ab der Zeile mit der Formel:
=@WENN(C2:C$13>400;C2:C$13;"")
Kategorie: Programmiertechnik ▸ Darstellung
Wie kann man eine %-Zahl als Balkendiagramm in einer Zelle darstellen?
Eine Zahl soll grafisch dargestellt werden, aber ein Diagrammobjekt soll es auch nicht gleich sein? Kein Problem - es geht auch mit einfachen Kästchen oder anderen Zeichen, die in vorhandenen Schriftarten enthalten sind. Also: Die Zelle, in/an der der Balken erscheinen soll, als Wingdings formatieren und eingeben:
=WIEDERHOLEN("n";B1*100)
In B1 steht im Beispiel die Zahl, die dargestellt werden soll. Die 100 so anpassen, dass die Länge des Balkens wie gewünscht ist.
Kategorien: Tabelle ▸ Zellen und Berechnungen
Kann ich mit einer Formel/Funktion (=max) auch den zweit- oder dritthöchsten Wert ziehen, ohne sortieren zu müssen?
Dafür gibt es eine weitere Funktion:
Dementsprechend funktioniert auch die Funktion KKLEINSTE, sie sucht nach den kleinsten Zahlen.
Mit der Funktion FILTER() kann man sich (ab Excel 365) auch gleich die Datensätze ausgeben lassen, die zu den entsprechenden Kriterien passen:
=FILTER(B1:C14;B1:B14=KGRÖSSTE(B1:B14;D1))
Kategorien: Stringoperationen ▸ Teile und Format ▸ Datum
Wie kann man von einem Datum in einer anderen Zelle nur die ersten zwei Zahlen und den Punkt angezeigt bekommen, also Tag.?
Um den (zweistelligen) Tag mit dem Punkt aus dem Datum zu extrahieren gibt es verschiedene Möglichkeiten. Beispiele:
=TEXT(TAG(A1);"TT")&"."
=LINKS(TEXT(A1;"TT.MM.JJ");3)
Einfach die Formel =A1 eintragen und die Zelle benutzerdefiniert mit TT. formatieren.
Natürlich geht es auch mit einer UDF, zum Beispiel indem gesplittet wird. Oder mit dieser Regex:
Function Tagausdatum(ByVal strDatum) Dim Regex As Object, regMatches If Regex Is Nothing Then Set Regex = CreateObject("VBScript.RegExp") Regex.Pattern = "([0-9]{1,2})\..*" Set regMatches = Regex.Execute(strDatum) Tagausdatum = regMatches(0).SubMatches(0) & "." Set Regex = Nothing End Function
In die Zelle müsste dann:
=Tagausdatum(C1)
Kategorie: System ▸ Windows
Wie erhalte ich in einer Zelle die aktuelle Windows- und Excelversion?
Die Funktion INFO() ist nützlich für diverse Informationen zur Umgebung: support.microsoft.com. Beispiele (F9 drücken zum Aktualisieren):
Kategorie: Datum/Zeit ▸ Datum
Um die Differenz zwischen 2 Datumswerten zu erhalten, nimmt man einfach die Differenz der beiden Werte und formatiert die Zelle als Standard. Was aber wenn die Zeitspanne größer als ein Jahr ist und man getrennt Jahre, Monate und Tage erhalten will?
Hierzu gibt es in Excel eine schöne Funktion: DATEDIF.
Syntax: DATEDIF(Datum1;Datum2;"Parameter")
Das Datum2 muß größer als das Datum1 sein, der Parameter muss immer in "" stehen.
y | Jahre | DATEDIF("01.01.98";"15.02.99";"y") | = 1 |
m | Monate | DATEDIF("01.01.98";"15.02.99";"m") | = 13 |
d | Tage | DATEDIF("01.01.98";"15.02.99";"d") | = 410 |
ym | Monate minus Jahre | DATEDIF("01.01.98";"15.02.99";"ym") | = 1 (13 Monate -1 Jahr (12 Monate)) |
yd | Tage minus Jahre | DATEDIF("01.01.98";"15.02.99";"yd") | = 45 (410 Tage - 1 Jahr (365 Tage!)) |
md | Tage minus Monate minus Jahre | DATEDIF("01.01.98";"15.02.99";"md") | = 14 (Differenz vom 01.02. & 15.02)) |
Das Ergebnis der letzten beiden Beispiele sieht seltsam aus - man beachte aber, dass es sich um die Differenz handelt.
Kategorien: Tabelle ▸ Zellen und Berechnungen
Wie kann man mit der Funktion SUMMEWENN auch mehrere Kriterien bestimmen?
Dazu bietet sich eine Array-Funktion an. Die erreicht man, indem man erst die normale Formel eingibt und die Eingabe mit Strg + Umschalttaste + Enter beendet. Dann werden automatisch um die Formel geschweifte Klammern ({}) gesetzt, woran man die Array-Funktion erkennt.
Beispiel: Folgende Formel summiert, wenn in Spalte A ein a und in derselben Zeile in Spalte B ein h ist, die entsprechenden Werte der Spalte C. Die geschweiften Klammern signalisieren, dass es ein Array ist - diese nicht mit kopieren/eingeben, sondern im Bearbeitungsmodus mit Strg + Umschalttaste + Enter erzeugen.
{=SUMME(WENN(A1:A11="a";WENN(B1:B11="h";C1:C11;"")))}
Ab Excel 365 sind die geschweiften Klammern nicht mehr notwendig, die Formel kann ganz normal eingetragen und die Eingabe einfach mit Enter abgeschlossen werden:
=SUMME(WENN(A1:A11="a";WENN(B1:B11="h";C1:C11;"")))
Hier geht allerdings auch die Funktion SUMMEWENNS(), in die mehrere Kriterien als Parameter eingetragen werden können. Im Beispiel wäre das:
=SUMMEWENNS(C1:C11;A1:A11;"a";B1:B11;"h")
Kategorien: Drucken/Seite und Tabelle ▸ Formeln
Wie kann ich anstelle der Ergebnisse die Formeln drucken?
Sollen alle Formeln des Blattes gedruckt werden:
Alternativ geht es auch mit der Tastenkombination Strg und ` (Graviszeichen, Taste rechts neben ß mit Umschalttaste drücken).
Sollen nur ausgewählte Formeln gedruckt werden, kann man als erstes in die Zelle ein Hochkomma (') eingeben, dann wird der Inhalt der Zelle als Text behandelt. Das empfiehlt sich übrigens auch dann, wenn eine längere Formel nicht beendet ist, aber die Mappe geschlossen werden soll.
Sollen die Formeln zusätzlich zu den Ergebnissen gedruckt werden, können die mit der Funktion FORMELTEXT() angezeigt werden:
=FORMELTEXT(A1)
Kategorien: Stringoperationen ▸ Teile und Stringoperationen ▸ Verketten
In Zeile 1 stehen in den Zellen Vornamen und Namen, in Zeile 2 die zugehörigen Anreden. Wie kann ich erreichen, daß in einer anderen Zelle nur die Anrede mit dem Nachnamen erscheint?
=A2&" "&RECHTS(A1; LÄNGE(A1)-FINDEN(" ";A1))
Kategorie: Tabelle ▸ Zellen
Wie kann ich die Anzahl der Einträge in einer Spalte feststellen?
=ANZAHL2(A1:A9999)
Kategorie: Tabelle ▸ Datenüberprüfung
Wie kann ich erreichen, daß bei einer Berechnung eine Zahl nur dann erscheint, wenn eine andere Zelle eine Zahl enthält?
Je nachdem, was den Gegebenheiten am besten entspricht:
=WENN(UND(ISTZAHL(A1);ISTZAHL(A2));A1+A2;"")
=WENN(A1="";"";WENN(A2="";"";A1+A2))
=WENN(A1+A2>0;A1+A2;"")
Kategorie: Datum/Zeit ▸ Zeit
Wie kann ich als Ergebnis einer Uhrzeitenberechnung eine Dezimalzahl erhalten?
=(A2-A1)*24
Kategorie: Datum/Zeit ▸ Datum
Wie kann ich eine Zufallszahl zwischen dem 01.01.60 und dem 01.01.80 erzeugen?
Dazu kann man die Funktion ZUFALLSZAHL nutzen. Allerdings liefert sie Zahlen von 0 bis 1, so dass man sich damit behelfen muss:
=GANZZAHL(ZUFALLSZAHL()*(49-1)+1)
Dabei ist 1 die Untergrenze und 49 die Obergrenze.
Für das Datum gibt es nun verschiedene Möglichkeiten. Es kann die Zahl jedes Datums verwendet werden, Bezüge zu Zellen mit den Datumsangaben sind möglich, aber auch die Datumsangaben in Anführungszeichen sind anwendbar:
Hier geht es wesentlich einfacher:
Kategorien: Steuerelemente ▸ Formular und Tabelle ▸ Zellen
Wie kann ich in einer Zelle den Eintrag eines Listenfeldes ausgeben statt der Zahl?
=INDEX(A1:A13;E1)
In E1 steht die Zellverknüpfung, A1:A13 ist die Liste.
Kategorien: Format ▸ Text und Tabelle ▸ Zellen
In A1 steht AAA, in B1 steht 1 - allerdings formatiert mit 0000, also erscheint 0001. Wenn ich jetzt die Formel =A1&B1 eingebe, erscheint leider nur AAA1 - nicht AAA0001.
=A1&TEXT(B1;"0000")
Kategorie: Datum/Zeit ▸ Zeit
Ich benötige für unsere Zeiterfassung die Möglichkeit, auch negative Zeiten (in Stunden und Minuten) berechnen zu können. Beispiel: A1 = 100:00 A2 = 120:00 A3 = (A1-A2) Daraufhin werden nur ####... angezeigt.
Das Vorgehen ist hier immer etwas von den konkreten Gegebenheiten abhängig, denn die Rauten (####) werden nur angezeigt. Dahinter stecken trotzdem die Ergebnisse, man kann also mit diesen Rauten also sogar weiterrechnen. Zum Testen:
Dieses Prinzip kann also verwendet werden, so dass mit der WENN-Funktion auf ein negatives Ergebnis geprüft wird und eine entsprechende Ausgabe erfolgt.
Alternativ kann die Prüfung bereits bei der Berechnung erfolgen:
=WENN(A8<B8;(A8-B8)*-1;A8-B8)
Nur ist hier der Nachteil, dass immer ein positives Ergebnis angezeigt wird. Ggf. könnte man per bedingter Formatierung darauf aufmerksam machen, aber die positive Zahl bleibt.
Kommt es auf weitere Berechnungen nicht an, sondern nur auf diese Anzeige, kann das Minuszeichen hinzugefügt werden:
=WENN(A8<B8;"-"&TEXT((A8-B8)*-1;"[hh]:mm");A8-B8)
Das negative Ergebnis liegt dann jedoch als Text vor - bei Berechnungen kann es nun Fehlermeldungen geben.
Wie vorgegangen wird, muss also im Einzelfall entschieden werden.
Auf das 1904-Datumsformat wird hier nicht eingegangen, weil das Probleme bereiten kann.
Kategorien: Filter/Sortieren und Tabelle ▸ Matrix
Ich möchte in einer Spalte die Anzahl eines bestimmten Begriffes. Dies funktioniert mit "Zählenwenn". Wenn ich aber Filter setze, ändert sich die Anzahl nicht.
Die 3 steht für Anzahl2, in C2 bis C6 befinden sich die zu zählenden Daten:
=TEILERGEBNIS(3;C2:C6)
Kategorie: Tabelle ▸ Zellen
In A1 steht $C$1, in C1 steht Hallo. Wie kann ich in B1 den Inhalt der Zelle sichtbar machen, die in A1 genannt ist - also von C1?
=INDIREKT(A1)
Kategorien: Tabelle ▸ Zellen und Berechnungen
Gibt es eine EXCEL-Funktion, um auf fünf Cent zu runden oder auf volle Fünfer, z. B. immer auf 5 oder auf 0?
Auf fünf Cent:
=VRUNDEN(A1;0,05)
Auf volle Fünfer:
=VRUNDEN(A1;5)
Kategorie: Tabelle ▸ Matrix
Per SVERWEIS lasse ich in Spalte A einen Begriff suchen. Wie ist es möglich, alle sich rechts vom gefundenen Begriff enthaltenen Zahlen zu addieren?
In E1 befindet sich der Suchbegriff.
=SUMME(INDIREKT("B"&VERGLEICH(E1;A1:A15;0)):INDIREKT("D"&VERGLEICH(E1;A1:A15;0)))
Hier hat man natürlich leichtes Spiel, indem man die Funktion FILTER() verwendet. Im ersten Parameter, der Matrix, wählt man einfach den Bereich, in dem die Zahlen stehen. Da die dann auch ausgegeben werden, können die auch gleich addiert werden:
=SUMME(FILTER(B1:C5;A1:A5=E1;0))
Kategorie: Datum/Zeit ▸ Zeit
Ich benötige die Differenz in Sekunden, um mit dieser Zahl weitere Berechnungen durchführen zu können. Die Zeitdifferenz habe ich über die Formatierung (Benutzerdefiniert [s]) in Sekunden vorliegen.
Durch die Zellformatierung hat sich an der Zahl selbst nichts geändert. In Excel entspricht der Wert 1,0 einem Tag. Um nun die Sekunden herauszukriegen muss man den Zeitwert mit 24*60*60 multiplizieren.
Kategorie: Tabelle ▸ Zellen
Beim Kopieren einer Formel wird der Zellbezug verändert, z. B. wird aus =A1 =A2. Wie kann ich das verhindern?
Beim normalen Verwenden eines Bezuges merkt sich Excel nicht den Bezug, sondern den Weg zu der Zelle, auf die sich der Bezug bezieht. Steht in B1 die Formel = A1, heißt das für Excel eine Zelle nach links. Befindet sich die Formel in A2, heißt das eine Zelle nach oben. Wenn die gleiche Formel (=A1) in B2 eingetragen ist, bedeutet dies für Excel eine Zelle nach oben und eine Zelle nach links.
Beim Kopieren verändert Excel die Bezüge in Formeln deshalb.
Wird die Formel =A1 (eine Zelle nach links) eine Zelle nach unten kopiert, lautet sie nun =A2 - also wiederum eine Zelle nach links. Deshalb nennt man diesen Bezug auch Relativer Zellbezug.
Kurzgefasst: Beim senkrechten Kopieren ändert sich die Zeile im Bezug, beim waagerechten Kopieren die Spalte.
Beispiel: Die Formel =A1 wird nach rechts, unten und rechts unten kopiert.
A | B | C | Bedeutung | |
Zeile 1 | =A1 | =B1 | eine Zelle nach links | |
Zeile 2 | =A2 | =B2 | eine Zelle nach links |
Um festzulegen, dass sich in einem Bezug eine Angabe (Spalte oder Zeile) beim Kopieren nicht ändert, schreibt man vor diese Angabe ein $. Soll sich die Zeile nicht ändern, schreibt man im Beispiel =A$1, soll sich die Spalte nicht ändern: =$A1. Diesen Bezug nennt man Gemischter Bezug.
Sind im Bezug zwei $-Zeichen enthalten, nennt man dies Absoluter Bezug.
Tip: Setzt man den Cursor in den Bezug, kann man die $-Zeichen durch mehrmaliges Drücken der Taste F4 einfügen.
Kategorie: Datum/Zeit ▸ Datum
Wie kann man das Datum des Ostersonntags aus einem Jahr errechnen?
Das Jahr steht in A1, die Zelle mit der Formel muss als Datum formatiert sein.
=DM((TAG(MINUTE(A1/38)/2+55)&".4."&A1)/7;)*7-6
Kategorie: Tabelle ▸ Zellen
Wie kann ich in einer Formel einen Teil des Zellbezugs (z. B. Zeilennummer) aus einer anderen Zelle übernehmen?
In A2 steht die Zeilennummer des in der Formel zu verwendenden Zellbezugs:
=INDIREKT("C"&A2)
Wenn in A2 zum Beispiel eine 3 stehen würde, wäre die Formel identisch mit =C3
Kategorie: Tabelle ▸ Zellen
Wie kann ich mit Formeln eine Rechnung erstellen?
Du hast eine Tabelle namens "Rechnung" und eine Tabelle namens "Artikel". Auf der Tabelle "Artikel" befinden sich ab A2 die Artikelnummern (hier fortlaufend numeriert), ab B2 die Artikel und ab C2 die Einzelpreise. Auf dem Blatt "Rechnung" wird die Rechnung erstellt.:
Kategorie: Tabelle ▸ Matrix
Wie kann ich den Wert einer Zelle rechts neben der Zelle mit dem Maximalwert ermitteln?
=SVERWEIS(MAX(A:A);A:B;2;0)
Hier steht die Funktion FILTER() zur Verfügung, die den Eintrag aus Spalte B zurückgibt, wo in A das Maximum steht:
=FILTER(B1:B5;A1:A5=MAX(A1:A5))
Auf den ersten Blick scheint es das gleiche Verhalten wie beim SVERWEIS zu sein. Deutlich wird es jedoch, wenn das Maximum in zwei verschiedenen Zellen enthalten ist. Dann gibt der SVERWEIS die erste Fundstelle zurück, die Filter-Funktion jedoch alle.
Kategorien: Datum/Zeit ▸ Datum und Tabelle ▸ Matrix
Wie kann ich Zahlen zu Datumsangaben, die in einem bestimmten Bereich liegen, addieren?
Gegeben:
Anforderung:
Summe der Zahlen in B, wenn das Datum in A in einem bestimmten Zeitraum liegt und in C eine bestimmte Zahl steht.
Beispiel: In E1 und E2 stehen Beginn und Ende der gewünschten Zeitspanne, in E3 die geforderte Zahl, die in C enthalten sein soll.
Formel:
Die Arrayfunktion FILTER(B1:B35;(A1:A35>=E1)*(A1:A35<=E2)*(C1:C35=E3);0) liefert die Zahlen zu den Zeilen, die den Kriterien entsprechen. Das heißt, dass diese nur addiert werden müssen:
=SUMME(FILTER(B1:B35;(A1:A35>=E1)*(A1:A35<=E2)*(C1:C35=E3);0))
Kategorie: Tabelle ▸ Matrix
Wie kann ich mit einer Formel den Schnittpunkt aus Zeilen- und Spaltenüberschrift ermitteln?
In einer Tabelle gibt es Zeilen- und Spaltenüberschriften. Per Formel soll der Schnittpunkt ermittelt werden bzw. die Zahl, die sich dort befindet.<7P>
Im Beispiel ist die Tabelle im Bereich A1:M6. Spalte A enthält Namen, Zeile 1 Monate.
In A10 soll der zu suchende Name eingetragen werden, in B10 der zu suchende Monat.
MIt SVERWEIS lassen wir in Spalte A nach dem Namen suchen, als Spaltenindex nehmen wir die Funktion VERGLEICH():
=SVERWEIS(A10;A2:M5;VERGLEICH(B10;B1:M1;0)+1)
Damit erhalten wir die Zahl im Schnittpunkt des ersten Treffers in Zeile 3, was hier die Zahl 22 ist.
Allerdings gibt es noch weitere mögliche Schnittpunkte, denn sowohl der Name als auch der Monat sind zweimal enthalten. Das wäre insgesamt vier Schnittpunkte.
Mit FILTER(B2:M6;A2:A6=A10;"") erhalten wir alle Daten der Tabelle, bei denen in Spalte A der zu suchende Name steht - allerdings nur die beiden Zeilen. Wir haben also eine Matrix bzw. einen Array, die/der aus zwei Zeilen zu jeweils 12 Zahlen besteht.
Aus diesem Array lassen wir mit einem zusätzlichen Filter die Spalten filtern, die auf den zu suchenden Monat zutreffen. Dazu nehmen wir wieder die Filter-Funktion und verwenden dort als Matrix das Ergebnis der ersten Filter-Funktion:
=FILTER(FILTER(B2:M6;A2:A6=A10;"");B1:M1=B10;"")
Als Ergebnis dieser Formel haben wir nun die vier Schnittpunkte, wie es im Beispiel zu sehen ist.
Mit dem Schnittmengenoperator @ können wir - wenn gewünscht - festlegen, dass das Verhalten wie beim SVERWEIS ist, dass also nur das erste Ergebnis angezeigt wird. Dazu fügen wir das @-Zeichen nach dem Gleichheitszeichen ein:
=@FILTER(FILTER(B2:M6;A2:A6=A10;"");B1:M1=B10;"")
Kategorien: Datum/Zeit ▸ Datum und Filter/Sortieren
Wie kann ich Datumsangaben in einem Bereich nicht nach Datum, sondern wahlweise auch nach Monat oder Tag sortieren?
Angenommen die Datumsangaben stehen in A2:A8:
Die Datumsangaben können mit Formeln auf mehrere Spalten aufgeteilt werden:
Anschließend kann ganz normal nach einer der Spalten sortiert werden.
Mit dieser Funktion kann das Sortieren in einem Schritt erledigt werden, sie gibt alle Daten auf einmal sortiert aus.
Nach Monat aufsteigend (die 1 steht für aufsteigend):
=SORTIERENNACH(A2:A8;MONAT(A2:A8);1)
Nach Monat und Tag aufsteigend:
=SORTIERENNACH(A2:A8;MONAT(A2:A8);1;TAG(A2:A8);1)
Falls ein größerer Bereich in die Funktion eingetragen werden soll, falls zum Beispiel abzusehen ist, dass es mehr Datumsangaben werden, werden auch die noch leeren Zellen ausgegeben. Das sieht nicht schön aus. Dafür kann die Adresse der letzten Zelle mit INDIREKT() „zusammengebastelt“ werden. Hier als Beispiel über die Funktion ANZAHL():
=SORTIERENNACH(INDIREKT("A2:A"&ANZAHL(A2:A15)+1);MONAT(INDIREKT("A2:A"&ANZAHL(A2:A15)+1));1;TAG(INDIREKT("A2:A"&ANZAHL(A2:A15)+1));1)
Kategorien: Datum/Zeit ▸ Datum und Tabelle ▸ Matrix
Auf Blättern, die mit Monatsnamen benannt sind, befinden sich in Zeile 1 Ab Spalte A nebeneinander die Datumsangaben des Monats (bis AE). Unter diesen Angaben sind ab Zeile 2 die anwesenden Mitarbeiter eingetragen. Wie kann ich diese zählen?
Zunächst muss das Blatt mit dem Monatsnamen des aktuellen Datums ermittelt werden. Das geht mit Indirekt:
=INDIREKT(TEXT(HEUTE();"MMMM")&"!A1:AE1")
Diese Formel liefert die komplette erste Zeile mit den Datumsangaben des aktuellen Monats. Sie kann nun in der Funktion FILTER() als Suchbereich verwendet werden. Damit können alle Mitarbeiter des Tages ermittelt werden (hier bis Zeile 30):
=FILTER(INDIREKT(TEXT(HEUTE();"MMMM")&"!A2:AE30");INDIREKT(TEXT(HEUTE();"MMMM")&"!A1:AE1")=HEUTE())
Die Formel gibt aus der Datumsspalte alle Einträge zurück, die bis Zeile 30 liegen.
Da aber mehr Zeilen (wie hier bis 30) verwendet werden müssen (die maximale Anwesenheit muss ja berücksichtigt werden), liefert diese Formel für die leeren Zellen, in denen also kein Mitarbeiter eingetragen ist, jeweils eine 0. Das kann also weder mit ANZAHL() noch mit ANZAHL2() gezählt werden, da sonst die 0 immer einfließen würde.
Wir verwenden im Beispiel SUMME(Wenn( und lassen die Einträge zählen, die <> 0 sind:
=SUMME(WENN(FILTER(INDIREKT(TEXT(HEUTE();"MMMM")&"!A2:AE30");INDIREKT(TEXT(HEUTE();"MMMM")&"!A1:AE1")=HEUTE())<>0;1;0))
Hier ist die Formel etwas länger:
=ANZAHL2(INDIREKT(TEXT(HEUTE();"MMMM")&"!"&LINKS(ADRESSE(1;VERGLEICH(HEUTE();INDIREKT(TEXT(HEUTE();"MMMM")&"!1:1"));4);1+(VERGLEICH(HEUTE();INDIREKT(TEXT(HEUTE();"MMMM")&"!1:1"))>26))&"2:"&LINKS(ADRESSE(1;VERGLEICH(HEUTE();INDIREKT(TEXT(HEUTE();"MMMM")&"!1:1"));4);1+(VERGLEICH(HEUTE();INDIREKT(TEXT(HEUTE();"MMMM")&"!1:1"))>26))&"1000"))