Ab in die Matrix: Eine kleine Einführung in Array-Formeln 9

Für viele ein Buch mit sieben Siegeln: Array- oder Matrix-Formeln erklärt.
 

Auf dem Weg zu den höheren Excel-Weihen führt kein Weg daran vorbei: Du musst den Sprung in die Matrix wagen. Nein, die Rede ist nicht vom Film-Klassiker mit Keanu Reeves. Du bist hier schließlich auf einem Excel-Blog gelandet. Ich spreche von Matrixformeln, auch Array-Formeln genannt.

Was bei dem unbedarften Excel-Anwender ein fragendes Stirnrunzeln auslöst, jagt manch anderem einen Schauer des Entsetzens über den Rücken. Haftet den Array-Formeln vielleicht nicht der Ruf von schwarzer Magie an, so handelt es sich dabei doch zumindest um schwer zu durchschauendes Teufelswerk. Oder?

Und genau mit diesem Vorurteil möchte ich mit der heutigen kleinen Einführung aufräumen.

Eine kleine Einführung

Ein Array ist ganz allgemein gesprochen eine Sammlung von Datenelementen. In Excel also eine Zelle oder ein Zellbereich.

Mit einer “normalen” Excel-Funktion kann man Berechnungen mit einem solchen Zellbereich durchführen und erhält ein einzelnes Ergebnis zurück (zum Beispiel mit der SUMME-Funktion).

Array- oder Matrixformeln hingegen können auf einen solchen Zellbereich mehrere Berechnungen gleichzeitig durchführen oder mehrere Zellbereiche in der Berechnung kombinieren. Das Ergebnis kann ebenfalls ein einzelner Wert (in einer Zelle) oder auch mehrere Werte (in mehreren Zellen) sein. Eine einzelne Array-Formel ermöglicht damit Berechnungen, für die ansonsten mehrere Formeln und Hilfsspalten nötig gewesen wären.

Eine Array-Formel ist in Excel an den sie umschließenden geschweiften Klammern zu erkennen. Diese dürfen jedoch nicht über die Tastatur eingetippt werden. Stattdessen muss eine Array-Formel immer mit der Tastenkombination STRG+Umschalt+Eingabe abgeschlossen werden (STRG+Taste drücken und gedrückt halten, dann die Umschalt-Taste dazunehmen und ebenfalls gedrückt halten und schließlich mit der Eingabetaste alles übernehmen).
 
Beende die Eingabe einer Array-Formel immer mit STRG+Umschalt+Eingabe
 
Das war jetzt alles noch ziemlich abstrakt. Die folgenden Beispiele sollen das ein wenig greifbarer machen.

Noch ein Hinweis an die Excel-Profis:
Ich weiß, dass sich viele der folgenden Beispiele auch mit Standard-Funktionen umsetzen lassen. Aber darum geht es hier nicht. Ich möchte vielmehr ein grundsätzliches Verständnis für Array-Formeln vermitteln, um den noch nicht so erfahrenen Excel-Anwendern die Scheu davor zu nehmen und vielleicht die Experimentierfreude ein wenig zu fördern. Denn manche Probleme lassen sich eben doch nur mit den Array-Funktionen lösen.

So, genug der Vorrede. Jetzt geht’s los (die Beispieldatei kannst du dir hier herunterladen).

Gesamtsumme ermitteln (Variante 1)

Für 5 verkaufte Artikel soll der gesamte erzielte Umsatz berechnet werden. Dazu sind zwei Berechnungsschritte notwendig:
1. Multipliziere pro Artikel die Menge mit dem Einzelpreis
2. Addiere sämtliche Artikelumsätze zu einem Gesamtumsatz

Der klassische Weg in zwei Schritten

Der klassische Weg in zwei Schritten

Eine Array-Formel vereinigt diese beiden Schritte in einer einzigen Ergebniszelle. Links im Bild die eben gezeigte klassische Variante, rechts die bessere Variante mit einer Array-Formel:

Der elegantere Weg über eine Array-Formel

Der elegantere Weg über eine Array-Formel


Der innere Teil der Formel (blau markiert) führt zunächst die Multiplikation der beiden Spalten durch (A4:A8*B4:B8), der äußere Teile addiert mit der SUMME-Funktion die errechneten Einzelergebnisse.

Dies ist deshalb möglich, weil beiden angegebenen Bereiche die gleiche Anzahl an Spalten und Zeilen umfasst. Eine Array-Formel wäre nicht möglich, wenn ein Bereich weniger Zeilen hätte:

Matrizenmultiplikation. Was geht und was geht nicht.

Matrizenmultiplikation. Was geht und was geht nicht.

Gesamtsumme ermitteln (Variante 2)

Die Bereiche in Array-Formeln müssen aber nicht zwingend den gleichen Aufbau haben. Möglich ist z.B. auch folgende Variante:

Matrizenmultiplikation, Teil 2

Matrizenmultiplikation, Teil 2

Hier wird der erste Bereich zunächst mit der ersten Spalte des zweiten Bereichs multipliziert und dann noch mit der zweiten Spalte. Die Einzelergebnisse werden dann wieder summiert.

Was dahinter steckt, sind die Regeln der Matrizenmultiplikation und ich möchte dich hier nicht mit allen Varianten langweilen. Aber ein Verständnis für das grundsätzliche Prinzip ist nicht schädlich, wenn man Array-Formeln durchdringen möchte. Wer es genauer wissen will, kann gerne auf Wikipedia ein wenig schmökern.

Rechnen mit Bedingungen

Bisher haben wir nur an der Oberfläche gekratzt und die Beispiele waren zugegebenermaßen auch noch nicht der Knaller. Das könnte sich jetzt ändern. Eine der großen Stärken von Array-Formeln ist, dass man mit ihnen Berechnungen anstellen kann, für die bestimmte Bedingungen zutreffen müssen. Beispiel:

Bilde die Summe der 5 größten Werte einer Liste

Die Summe der 5 größten Werte. Aber wie?

Die Summe der 5 größten Werte. Aber wie?

Wie würde man ohne Array-Formel an das Problem herangehen?

Der erste Schritt wäre, die 5 größten Werte zu bestimmen, um sie dann in einem zweiten Schritt zu summieren. Dazu kann man die KGRÖSSTE-Funktion verwenden:

Klassiker: Die KGRÖSSTE-Funktion

Klassiker: Die KGRÖSSTE-Funktion


Der Wert für k wurde dabei fest in die Formel eingetragen.

Wie packt man so etwas in eine Array-Formel? Die Schwierigkeit ist hier, dass sich der Wert für k immer ändern muss, nämlich von 1 bis 5. Dafür gibt es einen kleinen, aber feinen Trick: Die ZEILE-Funktion.

ZEILE macht nichts anderes, als die Zeilennummer der angegebenen Zelladresse zurückzuliefern. So ergibt =ZEILE(B5) den Wert 5, ZEILE(A3) den Wert 3 und so weiter. Man kann statt einer Zelladresse auch eine Zeilennummer angeben:
=ZEILE(1:1) gibt naheliegenderweise den Wert 1, =ZEILE(7:20) ergibt den Wert 7, also die erste Zeilennummer im angegebenen Bereich.

Was im Normalfall relativ sinnfrei erscheint, erweist sich innerhalb einer Array-Funktion als genial. Gehen wir es Schritt für Schritt an.
Markiere die ersten 5 Zellen in Spalte C und gib dann in die erste folgende Formel ein:
=KGRÖSSTE(B3:B12;ZEILE(1:5))
Schließe dann die Formel mit STRG+Umschalt+Eingabe ab.

Array-Formel: Die Zwischenlösung

Array-Formel: Die Zwischenlösung

Was genau passiert hier? In jeder der 5 Zellen steht die gleiche Formel. Da wir sie über die spezielle Tastenkombination aber als Array-Formel eingeben haben, verwendet Excel abhängig von der Zellenposition jeweils die nächste Zeilennummer in der ZEILE-Funktion. Und damit erhalten wir genau das gewünschte Ergebnis für die KGRÖSSTE-Funktion.

Aber wir wollen ja die Summe daraus bilden und das Ganze auch noch ohne Zwischenberechnungen. Dazu fehlt nur ein letzter kleiner Schritt:
{=SUMME(KGRÖSSTE(B3:B12;ZEILE(1:5)))}

Array-Formal: Die finale Lösung

Array-Formal: Die finale Lösung

Das soll es für diese kleine Einführung in die Welt der Array-Formeln gewesen sein. Wie eingangs erwähnt, wären die gleichen Ergebnisse auch anders zu erzielen (Stichwort SUMMENPRODUKT). Aber ich hoffe, du hast jetzt zumindest eine bessere Vorstellung, wozu Array-Formeln in der Lage sind. Denn auch SUMMENPRODUKT ist nichts anderes als eine Array-Funktion, lediglich die geschweiften Klammern sind nicht notwendig.

In einem der folgenden Artikel werde ich das Thema wieder aufgreifen und dir einige weitere, teils verblüffende Beispiele zeigen, wozu man diese Spezialformeln einsetzen kann.

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.

9 Gedanken zu “Ab in die Matrix: Eine kleine Einführung in Array-Formeln

  • Andreas Unkelbach

    Hallo Martin,

    vielen Dank für die Einführung in die Matrixformeln (egal ob hierfür nun die rote oder die blaue Zellenfarbe gewählt werden musste). Gerade das einfache Beispiel mit der Kombination von ZEILE() in der Matrix hilft hier sehr weiter und ist mir hier wesentlich einleuchtender als eine etwas umfangreichere Berechnung der EAN Prüfziffer per Matrixformel. Hier hatten sich Gerhard Pundt und ich gemeinsam um eine Berechnung der Prüfziffer ebenfalls per Matrixformel bemüht. 🙂

    Ich freue mich auf jeden Fall schon auf die folgenden Artikel gerade da ich hier hoffe meinen inneren Widerstand gegen die Matrix abbauen zu können und die Matrixformeln künftig ebenso gerne einzusetzen wie mittlerweile die INDIREKT() Funktion, zu der ich ebenfalls eine halbe Ewigkeit brauchte um diese zu mögen. 🙂

    Viele Grüße und schon vorab Danke für die Serie
    Andreas

    • Martin Weiß Autor des Beitrags

      Hallo Andreas,

      mir geht es da sehr ähnlich. Es gibt einfach Funktionen, die brauchen länger, bis man sich damit anfreunden kann 🙂

      Schöne Grüße,
      Martin

  • Tscharne Renato

    Guten Tag Martin

    Du hast wieder eine interessante Info mit der Arrey-Formel platziert. Besten Dank dafür.
    Ich habe diese nachgemacht, einmal auf deinem Arbeitsblatt, einmal mit einem selbstgemachten Arbeitsblatt.
    Die Formel stimmt, jedoch nimmt es bei meiner Berechnung auf deinem Arbeitsblatt als fünfte Grösse nicht die 53, sondern nach der 69 direkt die 39 und dann die 21. Warum das?
    Danke für deine Hilfe.

    Freundliche Grüsse
    Renato

    • Martin Weiß Autor des Beitrags

      Hallo Renato,

      in meinem Beispiel wäre die 53 nicht die fünftgrößte, sondern die viertgrößte Zahl. Die Reihenfolge ist 95, 87, 69, 53, 39.
      Du hast vermutlich irgendwo einen Wurm mit den Zellbezügen drin. Für die fünf größten Werte muss die ZEILE-Funktion immer ZEILE(1:5) lauten.
      (und das abschließende STRG+Umschalt+Eingabe nicht vergessen)

      Schöne Grüße,
      Martin

      • Tscharner Renato

        Guten Tag Martin

        Besten Dank für deine Rückmeldung.
        Ich habe es heute im Büro nochmals versucht, jedoch erhalte ich immer das selbe Resultat.
        95, 87, 69, 39, 21.
        Die Berechnung habe ich direkt ab deinem Excel-File gemacht.
        Komischerweise stimmt die Summe der 5 Grössten mit 343 ;-).
        Ich wollte dir ein jpg senden, damit du sehen kannst, dass ich alles korrekt eingegeben und berechnet habe.
        Ich werde noch ein wenig mit anderen Berechnungen “experimentieren”. Vielleicht sehe ich, was nicht passt.

        Trotzdem vielen Dank für deine Bemühungen.

        Freundliche Grüsse
        Renato

        • Renato

          Guten Tag Martin

          Übung mach den Meister ;-).
          Es hat mir keine Ruhe gelassen und ich habe geübt und geübt, bis ich den Fehler (endlich) gefunden habe. Ich habe die Zellen nicht markiert!! Dieses Unterlassen wird mir hier nie mehr passieren ;-). Nun passt auch bei mir die Reihenfolge.

          Herzlichen Dank und ich freue mich auf deine nächsten Tipps
          Renato

          • Martin Weiß Autor des Beitrags

            Hallo Renato,

            das freut mich, dass Du den Fehler doch noch gefunden hast. Manchmal hilft einfach nur eine gewisse Hartnäckigkeit.

            Schöne Grüße,
            Martin

  • Angela Simetzberger

    Hi,

    Erstmal großes Lob an deinen Blog den ich erst gestern entdeckt habe und der mich jetzt schon um viel Wissen bereichert hat! 🙂
    Ich habe dein Beispiel nachgespielt und dann die Formel =SUMME(KGRÖSSTE(H8:H22;ZEILE(1:5))) einmal mit und einmal ohne Array eingegeben. Jetzt spucken mir die beiden Formeln unterschiedliche Zahlen aus. Kannst du mir vielleicht erklären woran das liegen könnte? Der einzige unterschied zwischen den beiden Formeln sind eben die Array-Klammern. Was genau machen diese Klammern eigentlich in der Formel? Weil theoretisch würde die Formel ja auch ohne den Klammern funktionieren oder?

    Ich glaube, dass das meinen Knoten lösen würde was diese Thema angeht 🙂

    Danke und liebe Grüße
    Angela

    • Martin Weiß Autor des Beitrags

      Hallo Angela,

      die Unterschiede kommen eben genau daher, dass die Array-Version (also die mit den geschweiften Klammern), die Ergebnisse anders berechnet. Es finden sozusagen im Hintergrund lauter einzelne Zwischenrechnungen statt, im die fünf größten Werte zu ermitteln und sie dann mit SUMME zu addieren.
      In der Version ohne Klammern ignoriert Excel in der KGRÖSSTE-Funktion, dass man mit ZEILE eigentlich 5 einzelne Werte verwenden möchte. Stattdessen nimmt Excel den ersten Wert 1 und liefert damit nur das größte Ergebnis. Wenn Du statt ZEILE(1:5) jetzt ZEILE(3:5) schreibst, liefert es nur den drittgrößten Wert.

      Wie Du siehst, funktioniert diese Rechnung also nur in der Array-Formel.

      Vielleicht hat das ja ein wenig weitergeholfen.

      Schöne Grüße,
      Martin