Die Matrix Reloaded. Oder in Excel: {STRG+Umschalt+Eingabe} 5

Noch mehr Tipps zu Array-Formeln in Excel
 

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

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)

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

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)

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

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

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

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 MODALWERT und Array-Formel

Beispiel 3: Dublettensuche mit MODALWERT 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

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)

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)

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:

Kennst Du eigentlich schon meine Urlaubsplaner-Vorlage?
Excel-Vorlage  Urlaubsplaner
Damit wird die Abstimmung und Planung der Abwesenheiten für Dich und Deine Kollegen zum Kinderspiel!
Mehr erfahren...

 

Das könnte dich auch interessieren:

Und immer daran denken: Excel beißt nicht!

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.



Schreibe einen Kommentar

Deine E-Mail-Adresse wird nicht veröffentlicht. Erforderliche Felder sind mit * markiert.

5 Gedanken zu “Die Matrix Reloaded. Oder in Excel: {STRG+Umschalt+Eingabe}

  • Lutz W.

    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

  • Jochen S.

    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