Ich geb’s ja gerne zu: Mir waren diese gruseligen Array-Formeln mit den geschweiften Klammern selbst lange Zeit unheimlich. Wenn man eine Formel schon mit dieser komischen Tastenkombination abschließen muss, dann kann doch etws nicht stimmen.
Sie sind anfangs schwer zu durchschauen und es bedarf einiger geistiger Kopfstände, bis man den Zugang findet. Aber ich kann dir versprechen, es lohnt sich. Heute geht’s weiter mit ein paar praktischen Anwendungsbeispielen:
- Quersumme berechnen
- Listen mit Fehlerwerten summieren
- Liste auf Dubletten überprüfen
- Liste per Formel alphabetisch sortieren
Und los geht’s:
Sollte das Thema mit den Array- oder Matrix-Formeln für dich noch neu sein, empfehle ich dir den ersten Teil meiner kleinen Einführung. Die Beispieldatei zum heutigen Artikel kannst du dir hier herunterladen.
Quersumme
Die Quersumme einer Zahl erhält man durch Addition der einzelnen Ziffern dieser Zahl. Wie berechnet man in Excel die Quersumme?
Man könnte die Zahl mit der TEIL-Funktion in ihre einzelnen Ziffern zerlegen und diese dann Addieren:
Beispiel 1: Quersumme
Bei der TEIL-Funktion handelt es sich aber um eine Textfunktion. Das Ergebnis daraus ist also immer ein Text, auch wenn der wie in unserem Beispiel wie eine Ziffer aussieht. Da man mit Texten nicht rechnen kann, liefert die SUMME-Funktion leider den Wert 0 (Null) zurück.
Eine richtige Zahl erhält man erst, wenn man die „Text“-Zahl mit 1 multipliziert:
Beispiel 1: Quersumme (Fortsetzung)
Damit funktioniert auch die Summe und wir haben – sehr umständlich – die Quersumme berechnet.
Die folgende Array-Formel vereinigt die einzelnen TEIL-Funktionen in einer einzigen Formel und kommt damit zum gleichen Ergebnis:
{=SUMME(1*TEIL(A25;ZEILE(INDIREKT("1:"&LÄNGE(A25)));1))}
Beispiel 1: Quersumme mit Array-Formel
Statt einer festen Position verwenden wir hier das Konstrukt ZEILE(INDIREKT(„1:“&LÄNGE(A25))):
Beispiel 1: Quersumme mit Array-Formel (Fortsetzung)
Vielleicht musst du das erst einen Moment sitzen lassen, aber dann macht’s irgendwann „Klick“.
Liste mit Fehlern addieren
Manchmal sind in Tabellen mit berechneten Werten auch Zellen mit Fehlerwerten enthalten. Der Versuch, solche Werte zu summieren, führt in einer normalen SUMME-Funktion ebenfalls zu einem Fehler:
Beispiel 2: Summe über Fehlerliste
Abhilfe schafft eine Kombination mit der WENNFEHLER-Funktion. Diese ersetzt jeden Fehler mit der Ziffer 0 und dann klappt es auch mit der Summme:
Beispiel 2: Summe über Fehlerliste per Array-Formel
Und die Formel immer schön mit STRG+Umschalt+Eingabe abschließen!
Enthält die Liste doppelte Werte?
Die Suche nach Dubletten ist ein häufiger Anwendungsfall in Excel. Wenn man nur prüfen möchte, ob eine Liste mehrfach vorkommende Werte enthält, dann ist die ZÄHLENWENN-Funktion ein guter Ausgangspunkt dafür.
=ZÄHLENWENN(Bereich;Suchkriterium)
Mit ihr zählt man, wie oft ein Suchkritierum in einem Tabellenbereich vorkommt. Um einen Bereich auf Dubletten zu überprüfen, müsste man jeden einzelnen Wert zählen und dann z.B. mit der MAX-Funktion prüfen, ob ein Wert größer als 1 dabei ist:
Beispiel 3: Dublettensuche mit MODALWERT
Du ahnst es sicherlich schon: Das Ganze lässt sich auch in einer einzigen Array-Formel darstellen.
Beispiel 3: Dublettensuche mit ZÄHLENWENN und Array-Formel
Textliste sortieren
Das absolute Highlight habe ich mir aber für den Schluß aufgehoben. Dieses letzte Beispiel habe ich auf der Website Get Digital Help des Schweden Oscar Cronquist gefunden.
Er hat eine – zugegeben, etwas komplexere – Formel entwickelt, um eine Liste alphabetisch zu sortieren.
Dazu nimmt er die Funktionen VERGLEICH, KKLEINSTE und ZÄHLENWENN, verwurschtelt diese in einer INDEX-Funktion und packt das Ganze in eine geniale Array-Formel:
Beispiel 4: Automatische Sortierung
Ich habe versucht, die Formel in ihre Bestandteile aufzudröseln und damit hoffentlich ein wenig leichter durchschaubar zu machen.
Beispiel 4: Automatische Sortierung (Fortsetzung)
Alles baut auf der INDEX-Funktion auf. Diese liefert aus einem Bereich den Wert zurück, der sich in der angegebenen Zeile befindet. Die Zeile wiederum wird über eine VERGLEICH-Funktion bestimmt.
Die VERGLEICH-Funktion verwendet als Suchkriterium den jeweils kleinsten Wert, den die ZÄHLENWENN-Funktion ermittelt. Diese prüft für jeden Namen, wieviele andere Namen „kleiner“ sind, also weiter vorne im Alphabet kommen. Für den Namen „Michaela“ wird der Wert 5 errechnet, weil noch 5 andere Namen alphabtisch davor kommen:
Beispiel 4: Automatische Sortierung (Fortsetzung)
Und auf diesem Weg ergibt sich auf fast wundersame Weise die sortierte Liste. Sei nicht frustriert, wenn du diese Formel nicht gleich durchblickst – mir ging es nicht anders. Lade dir die Beispieldatei herunter und spiele ein wenig damit herum, das hilft bestimmt.
Du hast noch nicht genug? Hier findest du noch zwei weitere Artikel über Array-Formeln und deren praktische Anwendungsmöglichkeiten:
Die Summe der Top-3-Werte bilden
Wie zählt man nur eindeutige Werte?
Besser als SVERWEIS: Alle Werte finden
Das soll es für heute gewesen sein. Welche anderen Anwendungsbeispiele hast du schon gefunden? Lass es uns in den Kommentaren wissen!
Falls du jedoch nach diesem Artikel reif für den Urlaub sein solltest, habe ich noch etwas anderes für dich:
Den Excel-Urlaubsplaner!
P.S. Die Lösung ist immer einfach. Man muss sie nur finden.
(Alexander Solschenizyn)
P.P.S. Das Problem sitzt meistens vor dem Computer.
Hallo Martin,
und wieder fängt die Woche mit einem erfolgreichen Excel-Start an. Danke!
Endlich eine passende Formel (Liste mit Fehlern addieren) die genau so in meiner Tabelle (in meinem Fall zwar kein Divisionsfehler, sondern ein nicht vorhandener Sverweis) immer wieder vorkommt.
Weiter so.
Gruß Lutz
Gern geschehen 🙂
Liste mit Fehlern addieren
Mir ist schon klar, dass es in dem Artikel in erster Linie darum geht, die Verwendung von Matrixformeln zu demonstrieren.
Wenn es aber um die praktische Umsetzung der Summation (oder anderer Berechnungen) mit möglichen Fehlerwerten geht, ist zumindest bei aktuelleren Excel-Versionen die AGGREGAT-Funktion einfacher (und Benutzer-sicherer) zu handhaben.
Ich bin selbst ein großer Freund von Matrix-Formeln. Man muss sich nur darüber im Klaren sein, dass ein unbedarfter (fremder) Benutzer diese ganz leicht einmal zerschießen kann, wenn er die geschweiftem Klammern nicht zu interpretieren weiß.
Gruß
Jochen
Hier noch die Verweise auf Martins Artikel zur AGGREGAT-Funktion:
https://www.tabellenexperte.de/viele-funktionen-in-einer-aggregat/
https://www.tabellenexperte.de/aggregat-und-teilergebnis-im-zusammenspiel/
Hallo Jochen,
Du hast Recht, die AGGREGAT-Funktion bietet hier auch schöne Möglichkeiten. Danke für den Hinweis.
Schöne Grüße,
Martin
Hallo Martin,
bezüglich Matrix-Array Formeln hab ich eine Frage. Hab mich die letzten Monate in Excel gut eingearbeitet und mit der Formel “Besser als SVERWEIS: Alle Werte finden” genau das passende gefunden. Eine Liste mit ca. 1400 Posten von Bestellungen und unterschiedliche Kostenstellen. Die Formel gibt mir genau alle Werte wieder die ich für eine Kostenstelle haben will welche ich in einem DropDown Menü auswählen kann. Kann ich die Formel erweitern und mehrere Kostenstellen 2,3 oder sogar 4 herausfiltern so dass er mir für diese alle Posten angibt. Als Zwischenlösung hab ich eine gesonderte Datei welche mit einer PivotTabelle arbeitet wo ich mehrere Argumente aufsummiert auslesen kann. Die Formellösung wäre für mich aber besser falls es da was gibt.
P.s. Dein Blog war mir bis jetzt immer der hilfreichste.
Schöne Grüße
Sebastian
Hallo Sebastian,
spontan habe ich leider keine Formel-Lösung dafür, aber vielleicht hat ja ein anderer Leser einen guten Tipp. Abgesehen davon sind Pivot-Tabellen ohnehin in vielen Fällen die bessere Variante, da man hier auf komplexe Formeln komplett verzichten kann.
Schöne Grüße,
Martin
Servus,
auch der Kommentar ist uralt, ich weiß. Habe mich aber beim durchlesen dieselbe Frage gestellt, also falls es jemand braucht, hier die Formel die ich gebastelt habe:
=WENNFEHLER(INDEX(Ausgabe;KKLEINSTE(WENN((Spalte1=Wert1)*(Spalte2Wert2)*(Spalte3=Wert3)=1;ZEILE(Ausgabe)-1;““);ZEILE(1:1)));““) {Array-Formel}
Ausgabe -> Spalte die den Ausgabewert enthält (Bsp. A2:A200)
Spalte=Wert -> kann alle Prüfungen enthalten, wie bei der Wenn-Funktion (Bsp. B2:B200<1000)
Es können beliebig viele Prüfungen eingefügt werden, einfach Klammern setzen und Multiplikationszeichen einfügen.
Habe sie nur mit der Millionenstadt-Tabelle getestet, sollte aber auf alles übertragbar sein.
LG, Jean-Luc
Hallöchen,
ich weiß, der Artikel ist schon etwas älter. Er hat mich in Bezug auf Array-Formeln ordentlich weiter gebracht und daher vielen Dank für den so gut verständlichen Artikel. Eine Frage bleibt mir aber: Warum soll die Textliste auf so umständlichem Wege sortiert werden? Der gesamt „KKleinste“-Breich (in grün) kann doch weggelassen werden, da dass Suchkriterium bekannt ist. Nämlich von 0 bis x in fortlaufender Reihenfolge. Die Formel {=INDEX(B$5:B$14;VERGLEICH(ZEILE(1:1)-1;ZÄHLENWENN(B$5:B$14;“<"&B$5:B$14);0))} ergibt doch das gleiche Ergebnis, ist aber kürzer und leichter verständlich.
Gruß Jean-Luc N.
Hallo Jean-Luc,
vielen Dank für die Ergänzung. Und im Grunde hast du auch recht, deine verkürzte Formel funktioniert genauso gut – sofern jeder Name in der Liste nur ein einziges Mal vorkommt.
Bei Mehrfachnennungen wird deine Formel einen Fehlerwert liefern.
Wenn die Liste aber nur eindeutige Namen enthält, ist deine Formel tatsächlich wesentlich übersichtlicher.
Schöne Grüße,
Martin
Wieder was gelernt, danke 🙂
Zu „Textliste sortieren“
Erst einmal vielen Dank für die sehr aufschlussreichen Artikel über die Array-Formeln.
Zu dem Highlight des Schweden Oscar Cronquist kam mir der Gedanke, dass ich eine Liste evtl. nach 2 Spalten sortieren möchte. In deinem Beispiel wären das Vornamen und zusätzlich Nachnamen. Nach mehreren unterschiedlichen Ansätzen, Versuchen und Fehlschlägen habe ich die obige Formel erweitert. Herausgekommen ist eine passable (wenn auch enorm Große) Formel die ich gerne mit euch Teilen möchte.
Gegeben ist eine Unsortierte Liste in 2 Spalten. In Spalte B (B5:B14) stehen die Vornamen und in C (C5:C14) die zugehörigen Nachnamen. Die Formel Sortiert in dem Fall zuerst nach den Nachnamen und anschließend nach den Vornamen und gibt diese mit Semikolon getrennt aus („Nachname; Vorname“).
Formel:
{ =INDEX($C$5:$C$14&"; "&$B$5:$B$14; VERGLEICH(KKLEINSTE((ZÄHLENWENN($C$5:$C$14; "<" &$C$5:$C$14)+(ZÄHLENWENN($B$5:$B$14; "<"&$B$5:$B$14)/ZAHLENWERT(("1"&WIEDERHOLEN("0"; LÄNGE(ANZAHL2($B$5:$B$14))))))); ZEILE(1:1)); (ZÄHLENWENN($C$5:$C$14; "<"&$C$5:$C$14)+(ZÄHLENWENN($B$5:$B$14; "<"&$B$5:$B$14)/ZAHLENWERT(("1"&WIEDERHOLEN("0"; LÄNGE(ANZAHL2($B$5:$B$14)))))));0)) }
Ergänzung zur ursprünglichen Formel:
+(ZÄHLENWENN($B$5:$B$14; "<"&$B$5:$B$14)/ZAHLENWERT(("1"&WIEDERHOLEN("0"; LÄNGE(ANZAHL2($B$5:$B$14))))))
Die Formel addiert hier den zusätzlich gefundenen Wert aus der Spalte Vornamen zum Wert der Spalte Nachnamen, teilt diesen vorher allerdings durch X um den Wert in den Dezimalbereich zu bringen.
X errechnet sich hierbei so aus der Anzahl an Einträgen, dass X immer Größer ist als die Anzahl an Zeilen. Damit soll vermieden werden, dass X größer 1 wird, was zu Falschausgaben führen würde.
Mfg M.T-S
Hallo M.T-S,
vielen Dank, dass Du diese Formel mit uns teilst. Und meinen allergrößten Respekt vor dieser genialen Erweiterung! Ich kann mir vorstellen, dass da einiges an Zeit, Energie und Hirnschmalz hineingeflossen ist.
Schöne Grüße,
Martin
Hallo Martin,
erstmal vielen Lieben Dank für das große Lob. 🙂
Ich habe mit der Formel noch ein wenig herumgespielt und dabei ist mir aufgefallen, dass die Formel nicht mit „als Text gespeicherte Zahlen“ umgehen kann.
Die Grundidee war, das x-personen in verschiedenen Gruppen eingeteilt sind. Die Gruppen waren nach dem Schema 1.1, 1.2, 1.3.., 2.1, 2.2, 2.3… ect. eingeteilt. Da Excel angenommen hat ich wolle mit „1.1“ ein Datum darstellen stand dort „1. Jan.“. Ich habe der Zahl also ein Apostroph vorangestellt ‚1.1 und Excel hat dann auch 1.1 angezeigt. Anschließend sollten diese noch nach Nachnamen und dann nach Vornamen Sortiert werden.
Die entsprechend um eine weitere Spalte ergänzte Formel hat die zusätzliche Sortierung allerdings vollständig ignoriert (nicht ganz richtig, aber es sah im Ergebnis so aus).
Als ich die Formel dann einzeln auseinander genommen habe, hat sich herauskristallisiert, dass das Problem im ZÄHLENWENN Teil liegt. Tatsächlich weiß ich nicht genau WIE Excel hier Interpretiert, aber am Ende gibt er dort nur 0 aus.
Bezogen auf die Ursprungsformel von Oscar Cronquist schaut das dann so aus. (Vereinfacht nur mit einer Ziffer dargestellt)
Unsortiert: 1, 3, 2, 6, 5, 4
Sortiert: 1, 1, 1, 1, 1, 1
Ab diesem Punkt habe ich einige sehr lehrreiche (jedoch erfolglose) Versuche hinter mich gebracht bis ich darauf gekommen bin den ZÄHLENWENN Teil mit einem Apostroph zu schmücken:
ZÄHLENWENN($B$5:$B$14; „<'"&$B$5:$B$14
Fällt kaum auf aber es versteckt sich rechts neben dem "Kleiner als". Genau dort! " < ' "
DIe Sortierung sieht dann so aus:
Unsortiert: 1, 3, 2, 6, 5, 4
Sortiert: 1, 2, 3, 4, 5, 6
Oder auch gemischt:
Unsortiert: 1, 3, B, 4, 2, A
Sortiert: 1, 2, 3, 4, A, B
ABER !!!
Das ganze funktioniert nur mit "Text" und "Als Text gespeicherte Zahlen". Wenn man damit nach Zahlen sortiert schaut das wieder so aus.
Unsortiert: 1, 3, 2, 6, 5, 4
Sortiert: 1, 1, 1, 1, 1, 1
Und absolut vermeiden sollte man Zahlen, gemischt mit Text.
Unsortiert: C, 2, A, 3, B, 1
Sortiert mit Apostroph: 2, 2, 2, A, B, C
Sortiert ohne Apostroph: A, A, 2, 2, C, C
Mfg M.T-S
(Marcel)
PS: Vielen Dank für die vielen Beiträge rund um Excel, die du für uns zu Verfügung stellst. 🙂