Excel
Formeln und Funktionen
Formeln
Formeln
Richtlinien und Beispiele für Arrayformeln
Gilt fürExcel für Microsoft 365 Excel für Microsoft 365 für Mac Excel 2024 Excel 2024 für Mac Excel 2021 Excel 2021 für Mac Excel 2019 Excel 2016 Excel für iPad Excel für iPhone

Eine Matrixformel ist eine Formel, mit der mehrere Berechnungen für ein oder mehrere Elemente in einem Array ausgeführt werden können. Ein Array kann man sich als eine Zeile von Werten, eine Spalte von Werten oder eine Kombination aus Zeilen und Spalten von Werten vorstellen. Matrixformeln können mehrere Ergebnisse oder ein einzelnes Ergebnis zurückgeben.

Ab dem Update vom September 2018 für Microsoft 365 wird jede Formel, die mehrere Ergebnisse zurückgeben kann, Letztere automatisch entweder nach unten oder in benachbarte Zellen per Überlauf ausgeben. Diese Verhaltensänderung geht mit mehreren neuen dynamischen Matrixfunktionen einher. Dynamische Matrixformeln, unabhängig davon, ob sie vorhandene Funktionen oder dynamische Matrixfunktionen verwenden, müssen nur in eine einzelne Zelle eingegeben und dann durch Drücken der EINGABETASTE bestätigt werden. Früher erforderten Vorgänger-Matrixformeln zunächst die Auswahl des gesamten Ausgabebereichs und dann das Bestätigen der Formel mit STRG+UMSCHALT+EINGABETASTE. Sie werden häufig als CSE-Formeln bezeichnet.

Mit Matrixformeln können Sie z. B. die folgenden komplexen Aufgaben ausführen:

  • Schnell Beispieldatasets erstellen

  • Die Anzahl von Zeichen zählen, die in einem Zellbereich enthalten sind

  • Nur Zahlen addieren, die bestimmte Bedingungen erfüllen, z. B. die niedrigsten Werte in einem Bereich oder Zahlen, die zwischen einem oberen und unteren Grenzwert liegen

  • Jeden n-ten Wert in einem Wertebereich addieren

Die Beispiele im folgenden Abschnitt zeigen, wie Mehrfachzellen- und Einzelzellen-Matrixformeln erstellt werden. Wenn möglich, haben wir Beispiele mit einigen der dynamischen Matrixformeln sowie für bereits vorhandene Matrixformeln hinzugefügt, die sowohl als dynamische als auch als Vorgänger-Arrays eingegeben wurden.

Herunterladen unserer Beispiele

Laden Sie eine Beispielarbeitsmappe mit allen Beispielen für Matrixformeln in diesem Artikel herunter.

In dieser Übung wird gezeigt, wie Mehrfachzellen- und Einzelzellen-Arrayformeln zum Berechnen einer Reihe von Umsatzzahlen verwendet werden. In der ersten Gruppe von Schritten wird eine Mehrfachzellenformel zum Berechnen einer Reihe von Teilergebnissen verwendet. In der zweiten Gruppe von Schritten wird eine Einzelzellenformel zum Berechnen einer Gesamtsumme verwendet.

  • Mehrfachzellen-Arrayformel

    Mehrzellen-Matrixfunktion in Zelle H10 =F10:F19*G10:G19 zum Berechnen der Anzahl verkaufter Fahrzeuge nach Preis je Einheit

  • Hier berechnen wir den Gesamtumsatz von Coupés und Limousinen durch die einzelnen Vertriebsmitarbeiter, indem wir in Zelle H10 =F10:F19*G10:G19 eingeben.

    Wenn Sie die EINGABETASTE drücken, werden die Ergebnisse nach unten in die Zellen H10:H19 überlaufen. Beachten Sie, dass der Überlaufbereich durch einen Rahmen hervorgehoben ist, wenn Sie eine beliebige Zelle innerhalb dieses Bereichs markieren. Möglicherweise stellen Sie auch fest, dass die Formeln in den Zellen H10:H19 ausgegraut sind. Sie dienen nur als Referenz. Wenn Sie also die Formel anpassen möchten, müssen Sie Zelle H10 markieren, in der sich die Masterformel befindet.

  • Formel für einzelliges Array

    Formel für einzellige Matrix zum Berechnen einer Gesamtsumme mit =SUMME(F10:F19*G10:G19)

    Geben Sie in Zelle H20 der Beispielarbeitsmappe =SUMME(F10:F19*G10:G19) ein, oder kopieren und fügen Sie die Formel ein, und drücken Sie dann die EINGABETASTE.

    In diesem Fall multipliziert Excel die Werte im Array (der Zellbereich F10 bis G19) und verwendet dann die Funktion SUMME, um die Summen zusammenzurechnen. Das Ergebnis ist ein Gesamtumsatz von 1.590.000 USD.

    Dieses Beispiel zeigt, wie leistungsfähig diese Art von Formel sein kann. Angenommen, Sie verfügen über 1.000 Datenzeilen. Sie können einen Teil oder alle diese Daten summieren, indem Sie eine Matrixformel in einer einzelnen Zelle erstellen, anstatt die Formel durch die 1.000 Zeilen nach unten zu ziehen. Beachten Sie auch, dass die Einzelzellenformel in Zelle H20 vollkommen unabhängig von der Mehrfachzellenformel (die Formel in den Zellen H10 bis H19) ist. Dies ist ein weiterer Vorteil von Arrayformeln: Flexibilität. Sie können die anderen Formeln in Spalte H ändern, ohne dass dies Auswirkungen auf die Formel in H20 hat. Eine bewährte Methode besteht auch darin, unabhängige Gesamtsummen wie diese zu verwenden, da dies dabei hilft, die Genauigkeit Ihrer Ergebnisse zu überprüfen.

  • Matrixformeln bieten darüber hinaus folgende Vorteile:

    • Konsistenz    Wenn Sie auf eine der Zellen unter Zelle H10 klicken, wird die gleiche Formel angezeigt. Durch diese Konsistenz kann größere Genauigkeit gewährleistet werden.

    • Sicherheit:    Eine Komponente einer Mehrzellen-Matrixformel kann nicht überschrieben werden. Klicken Sie beispielsweise auf Zelle H11, und drücken Sie ENTF. Excel ändert die Ausgabe des Arrays nicht. Für eine Änderung müssen Sie die Zelle oben links im Array oder Zelle H10 markieren.

    • Geringere Dateigrößen:    Häufig kann eine einzelne Matrixformel anstelle mehrerer Formeln für Zwischenschritte verwendet werden. So wird etwa im Beispiel zum Fahrzeugumsatz eine einzige Matrixformel zum Berechnen der Ergebnisse in Spalte E verwendet. Bei Verwendung von Standardformeln (z. B. =F10*G10, F11*G11, F12*G12 usw.) hätten Sie 11 verschiedene Formeln zur Berechnung der gleichen Ergebnisse verwenden müssen. Das ist keine große Sache, aber was wäre, wenn Sie Tausende von Zeilen zusammenrechnen müssten? Dann kann es einen großen Unterschied machen.

    • Effizienz    Matrixfunktionen können ein effizientes Instrument zum Erstellen komplexer Formeln sein. Die Matrixformel =SUMME(F10:F19*G10:G19) ist identisch mit dieser: =SUMME(F10*G10,F11*G11,F12*G12,F13*G13,F14*G14,F15*G15,F16*G16,F17*G17,F18*G18,F19*G19).

    • Überlauf:    Dynamische Matrixformeln werden automatisch in den Ausgabebereich überlaufen. Wenn sich die Quelldaten in einer Excel-Tabelle befinden, ändern sich die Größe der dynamischen Arrayformeln automatisch, wenn Sie Daten hinzufügen oder entfernen.

    • #Überlauf!-Fehler:    Bei dynamischen Arrays wurde der #Überlauf!-Fehler eingeführt, der angibt, dass der vorgesehene Überlaufbereich aus irgendeinem Grund blockiert ist. Wenn Sie die Blockierung aufheben, wird die Formel automatisch überlaufen.

Matrixkonstanten sind eine Komponente von Matrixformeln. Sie werden erstellt, indem eine Liste von Elementen eingegeben und diese dann manuell in geschweifte Klammern ({ }) einschlossen wird. Beispiel:

={1,2,3,4,5} oder ={"Januar","Februar","März"}

Wenn Sie die Elemente durch Kommas trennen, erstellen Sie ein horizontales Array (eine Zeile). Wenn Sie die Elemente durch Semikolons trennen, erstellen Sie ein vertikales Array (eine Spalte). Um ein zweidimensionales Array zu erstellen, trennen Sie die Elemente in jeder Zeile durch Kommas und jede Zeile durch Semikolons.

Die folgenden Verfahren dienen als Übung zum Erstellen horizontaler, vertikaler und zweidimensionaler Konstanten. Wir zeigen Beispiele für die Verwendung der SEQUENZ-Funktion zum automatischen Generieren von Matrixkonstanten sowie manuell eingegebener Matrixkonstanten.

  • Erstellen einer horizontalen Konstante

    Verwenden Sie die Arbeitsmappe aus den vorherigen Beispielen, oder erstellen Sie eine neue Arbeitsmappe. Markieren Sie eine leere Zelle, und geben Sie =SEQUENZ(1,5) ein. Die SEQUENZ-Funktion erstellt ein Array aus einer Zeile und 5 Spalten, das mit ={1,2,3,4,5} identisch ist. Das folgende Ergebnis wird angezeigt:

    Erstellen Sie eine horizontale Matrixkonstante mit =SEQUENZ(1;5) oder ={1;2;3;4;5}

  • Erstellen einer vertikalen Konstante

    Markieren Sie eine leere Zelle mit darunter liegendem Platz, und geben Sie =SEQUENZ(5) oder ={1;2;3;4;5} ein. Das folgende Ergebnis wird angezeigt:

    Erstellen Sie eine vertikale Matrixkonstante mit =SEQUENZ(5) oder ={1;2;3;4;5}

  • Erstellen einer zweidimensionalen Konstante

    Markieren Sie eine beliebige leere Zelle mit Platz rechts und darunter, und geben Sie =SEQUENZ(3,4) ein. Das folgende Ergebnis wird angezeigt:

    Erstellen einer Matrixkonstante mit 3 Zeilen und 4 Spalten mit =SEQUENZ(3;4)

    Sie können auch „oder ={1,2,3,4;5,6,7,8;9,10,11,12}“ eingeben, aber Sie sollten darauf achten, wo Sie Semikolons und wo Kommas setzen müssen.

    Wie Sie sehen, bietet die SEQUENZ-Option erhebliche Vorteile gegenüber der manuellen Eingabe von Matrixkonstantenwerten. In erster Linie sparen Sie Zeit, aber es kann auch dazu beitragen, Fehler bei der manuellen Eingabe zu reduzieren. Die Formel ist auch einfacher zu lesen, da Semikolons schwer von Kommatrennzeichen zu unterscheiden sein können.

Im folgenden Beispiel werden Matrixkonstanten als Teil einer größeren Formel verwendet. Wechseln Sie in der Beispielarbeitsmappe zum Arbeitsblatt Konstante in einem Formel, oder erstellen Sie ein neues Arbeitsblatt.

In Zelle D9 haben wir =SEQUENZ(1,5,3,1) eingegeben, aber Sie können auch 3, 4, 5, 6 und 7 in die Zellen A9:H9 eingeben. Es gibt nichts Besonderes an dieser bestimmten Zahlenauswahl, wir haben nur etwas anderes als 1-5 zur besseren Unterscheidung gewählt.

Geben Sie in Zelle E11 =SUMME(D9:H9*SEQUENZ(1,5)) oder =SUMME(D9:H9*{1,2,3,4,5}) ein. Beide Formeln geben 85 zurück.

Verwenden von Matrixkonstanten in Formeln. In diesem Beispiel haben wir =SUMME(D9:H(*SEQUENZ(1;5)) verwendet

Die SEQUENZ-Funktion erstellt das Äquivalent der Matrixkonstante {1,2,3,4,5}. Da Excel zuerst Operationen für Ausdrücke ausführt, die in Klammern eingeschlossen sind, sind die nächsten beiden Elemente, die ins Spiel kommen, die Zellwerte in D9:H9 und der Multiplikationsoperator (*). An diesem Punkt multipliziert die Formel die Werte im gespeicherten Array mit den entsprechenden Werten in der Konstante. Dies entspricht Folgendem:

=SUMME(D9*1,E9*2,F9*3,G9*4,H9*5) oder =SUMME(3*1,4*2,5*3,6*4,7*5)

Schließlich fügt die SUMME-Funktion die Werte hinzu und gibt 85 zurück.

Um die Verwendung des gespeicherten Arrays zu vermeiden und die Operation vollständig im Speicher beizubehalten, ersetzen Sie das gespeicherte Array durch eine andere Matrixkonstante:

=SUMME(SEQUENZ(1,5,3,1)*SEQUENCE(1,5)) oder =SUMME({3,4,5,6,7}*{1,2,3,4,5})

Elemente, die in Konstanten verwendet werden können

  • Matrixkonstanten können Zahlen, Text, logische Werte (z. B. WAHR und FALSCH) und Fehlerwerte (z. B. #NV) enthalten. Zahlen können ganze Zahlen, Dezimalzahlen und Zahlen in wissenschaftlichen Formaten sein. Wenn Sie Text einfügen, müssen Sie diesen in Anführungszeichen („Text“) einschließen.

  • Arraykonstanten können keine zusätzlichen Arrays, Formeln oder Funktionen enthalten. Sie können also nur Text oder Zahlen enthalten, die durch Kommas oder Semikolons getrennt sind. Excel zeigt eine Warnmeldung an, wenn eine Formel wie "{1.2.A1:D4}" oder "{1.2.SUMME(Q2:Z8)}" eingegeben wird. Zudem können numerische Werte keine Prozentzeichen, Dollarzeichen, Kommas oder Klammern enthalten.

Eine der besten Möglichkeiten zur Verwendung von Matrixkonstanten besteht darin, sie zu benennen. Benannte Konstanten können wesentlich einfacher verwendet werden, und mit ihnen kann ein Teil der Komplexität von Arrayformeln für andere Benutzer verborgen bleiben. Um eine Arraykonstante zu benennen und in einer Formel zu verwenden, gehen Sie folgendermaßen vor:

Wechseln Sie zu Formeln > Definierte Namen > Namen definieren. Geben Sie im Feld Name die Zeichenfolge „Quartal1“ ein. Geben Sie im Feld Bezieht sich auf die folgende Konstante ein (denken Sie daran, die geschweiften Klammern manuell einzugeben):

={"Januar"."Februar"."März"}

Das Dialogfeld sollte nun wie folgt aussehen:

Hinzufügen einer benannten Matrixkonstanten aus Formeln > Definierten Namen > Namens-Manager > Neu

Klicken Sie auf OK, markieren Sie dann eine beliebige Zeile mit drei leeren Zellen, und geben Sie =Quartal1 ein.

Das folgende Ergebnis wird angezeigt:

Verwenden Sie eine benannte Matrixkonstante in einer Formel wie =Quartal1, wobei Quartal1 als ={"January","February","March"} definiert wurde

Wenn die Ergebnisse vertikal statt horizontal überlaufen sollen, können Sie =MTRANS(Quartal1) verwenden.

Wenn Sie eine Liste von 12 Monaten anzeigen möchten, wie etwa beim Erstellen einer Finanzaufstellung, können Sie mit der SEQUENZ-Funktion eine aus dem aktuellen Jahr erstellen. Das Schöne an dieser Funktion ist, dass, obwohl nur der Monat angezeigt wird, ein gültiges Datum dahinter steht, das Sie in anderen Berechnungen verwenden können. Sie finden diese Beispiele in den Arbeitsblättern Benannte Matrixkonstante und Schnelles Beispieldataset in der Beispielarbeitsmappe.

=TEXT(DATUM(JAHR(HEUTE());SEQUENZ(1,12),1),"mmm")

Verwenden Sie eine Kombination aus den Funktionen TEXT, DATUM, JAHR, HEUTE und SEQUENZ, um eine dynamische Liste mit 12 Monaten zu erstellen

Hier wird die DATE-Funktion verwendet, um ein Datum basierend auf dem aktuellen Jahr zu erstellen, und SEQUENZ erstellt eine Matrixkonstante von 1 bis 12 für Januar bis Dezember. Anschließend konvertiert die TEXT-Funktion das Anzeigeformat in "mmm" (Jan, Feb, Mär usw.). Wenn Sie den vollständigen Monatsnamen anzeigen möchten, z. B. Januar, verwenden Sie "mmmm".

Wenn Sie eine benannte Konstante als Matrixformel verwenden, denken Sie daran, das Gleichheitszeichen wie in „=Quartal1“ und nicht nur „Quartal1“ einzugeben. Wenn dies nicht geschieht, interpretiert Excel das Array als eine Textzeichenfolge, und die Formel funktioniert nicht wie erwartet. Sie können jederzeit auch Kombinationen von Text und Zahlen verwenden. Ihrer Kreativität sind keine Grenzen gesetzt.

In den folgenden Beispielen sind einige Möglichkeiten dargestellt, wie Sie Arraykonstanten in Arrayformeln einsetzen können. Bei einigen Beispielen wird die Funktion MTRANS zum Konvertieren von Zeilen in Spalten und umgekehrt verwendet.

  • Multiplizieren jedes Elements in einem Array

    Geben Sie =SEQUENZ(1,12)*2 oder ={1,2,3,4;5,6,7,8;9,10,11,12}*2 ein.

    Sie können auch mit (/) dividieren, mit (+) hinzufügen und mit (-) subtrahieren.

  • Quadrieren der Elemente in einem Array

    Geben Sie =SEQUENZ(1,12)^2 oder ={1,2,3,4;5,6,7,8;9,10,11,12}^2 ein.

  • Ermitteln der Quadratwurzel von Elementen im Quadrat in einem Array

    Geben Sie =WURZEL(SEQUENZ(1,12)^2) oder =WURZEL({1,2,3,4;5,6,7,8;9,10,11,12}^2) ein.

  • Transponieren einer eindimensionalen Zeile

    Geben Sie =MTRANS(SEQUENZ(1,5)) oder =MTRANS({1,2,3,4,5}) ein.

    Obwohl Sie eine horizontale Arraykonstante eingegeben haben, wandelt die MTRANS-Funktion die Arraykonstante in eine Spalte um.

  • Transponieren einer eindimensionalen Spalte

    Geben Sie =MTRANS(SEQUENZ(5,1)) oder =MTRANS({1;2;3;4;5}) ein.

    Obwohl Sie eine vertikale Arraykonstante eingegeben haben, wandelt die MTRANS-Funktion die Konstante in eine Zeile um.

  • Transponieren einer zweidimensionalen Konstante

    Geben Sie =MTRANS(SEQUENZ(3,4)) oder =MTRANS({1,2,3,4;5,6,7,8;9,10,11,12}) ein.

    Die MTRANS-Funktion wandelt jede Zeile in eine Reihe von Spalten um.

Dieser Abschnitt enthält Beispiele für grundlegende Arrayformeln.

  • Erstellen eines Arrays aus vorhandenen Werten

    Im folgenden Beispiel wird erläutert, wie Matrixformeln verwendet werden, um ein neues Array aus einem vorhandenen Array zu erstellen.

    Geben Sie =SEQUENZ(3,6,10,10) oder ={10,20,30,40,50,60;70,80,90,100,110,120;130,140,150,160,170,180} ein.

    Geben Sie unbedingt „{“ (eine öffnende geschweifte Klammer) vor der Eingabe von „10“ und „}“ (eine schließende geschweifte Klammer) nach der Eingabe von „180“ ein, da Sie ein Array von Zahlen erstellen.

    Geben Sie als Nächstes =D9# oder =D9:I11 in eine leere Zelle ein. Es wird ein Array aus 3 mal 6 Zellen mit den gleichen Werten angezeigt, die Sie in D9:D11 sehen. Das #-Zeichen wird als Überlaufbereich-Operator bezeichnet. Hiermit wird in Excel auf den gesamten Arraybereich verwiesen, anstatt ihn zur Gänze eingeben zu müssen.

    Verwenden Sie den Überlaufbereichsoperator (#), um auf eine vorhandene Matrix zu verweisen

  • Erstellen einer Arraykonstante aus vorhandenen Werten

    Sie können die Ergebnisse einer übergelaufenen Matrixformel in ihre Komponenten konvertieren. Markieren Sie Zelle D9, und drücken Sie dann F2, um in den Bearbeitungsmodus zu wechseln. Drücken Sie dann F9, um die Zellbezüge in Werte zu konvertieren, die Excel anschließend in eine Matrixkonstante konvertiert. Wenn Sie die EINGABETASTE drücken, sollte die Formel „=D9#“ nun „={10,20,30;40,50,60;70,80,90}“ sein.

  • Zählen der Anzahl von Zeichen in einem Zellbereich

    Das folgende Beispiel zeigt, wie die Anzahl der Zeichen in einem Zellbereich gezählt wird. Dies schließt Leerzeichen ein.

    Zählen Sie die Gesamtanzahl der Zeichen in einem Bereich und in anderen Matrizen zum Arbeiten mit Textzeichenfolgen

    =SUMME(LÄNGE(C9:C13))

    In diesem Fall gibt die Funktion LÄNGE die Länge der einzelnen Textzeichenfolgen in jeder Zelle im Bereich zurück. Die Funktion SUMME addiert dann diese Werte und zeigt das Ergebnis an, also 66. Wenn Sie die durchschnittliche Anzahl von Zeichen abrufen möchten, können Sie Folgendes verwenden:

    =MITTELWERT(LÄNGE(C9:C13))

  • Inhalt der längsten Zelle im Bereich C9:C13

    =INDEX(C9:C13;VERGLEICH(MAX(LÄNGE(C9:C13));LÄNGE(C9:C13);0);1)

    Diese Formel funktioniert nur, wenn ein Datenbereich eine einzelne Spalte von Zellen enthält.

    Sehen wir uns nun die Formel beginnend bei den inneren Elementen nach außen genauer an. Die Funktion LÄNGE gibt die Länge der einzelnen Elemente im Zellbereich D2:D6 zurück. Die Funktion MAX berechnet den größten Wert unter diesen Elementen, also die längste Textzeichenfolge, die sich in Zelle D3 befindet.

    Nun wird es etwas komplexer. Die Funktion VERGLEICH berechnet den Versatz (die relative Position) der Zelle mit der längsten Textzeichenfolge. Dazu werden drei Argumente benötigt: ein Suchwert, ein Sucharray und ein Vergleichstyp. Die Funktion VERGLEICH durchsucht das Sucharray nach dem angegebenen Suchwert. In diesem Fall ist der Suchwert die längste Textzeichenfolge:

    MAX(LÄNGE(C9:C13)

    und diese Zeichenfolge befindet sich in folgendem Array:

    LÄNGE(C9:C13)

    Das Übereinstimmungstypargument ist in diesem Fall 0. Der Übereinstimmungstyp kann den Wert „1“, „0“ oder „-1“ aufweisen.

    • „1“ gibt den größten Wert zurück, der kleiner oder gleich dem Suchwert ist.

    • „0“ gibt den ersten Wert zurück, der dem Suchwert genau entspricht.

    • „-1“ gibt den kleinsten Wert zurück, der größer oder gleich dem angegebenen Suchwert ist.

    • Wenn Sie keinen Vergleichstyp angeben, verwendet Excel den Wert "1".

    Schließlich übernimmt die Funktion INDEX diese Argumente: ein Array sowie eine Zeilen- und Spaltennummer innerhalb dieses Arrays. Der Zellbereich C9:C13 stellt das Array bereit, die Funktion VERGLEICH stellt die Zelladresse bereit und das letzte Argument (1) gibt an, dass der Wert aus der ersten Spalte im Array stammt.

    Wenn Sie den Inhalt der kleinsten Textzeichenfolge abrufen möchten, ersetzen Sie MAX im obigen Beispiel durch MIN.

  • Suchen der n kleinsten Werte in einem Bereich

    In diesem Beispiel wird gezeigt, wie Sie die drei kleinsten Werte in einem Zellbereich finden, wobei ein Array von Beispieldaten in den Zellen B9:B18 erstellt wurde mit: =GANZZAHL(ZUFALLSMATRIX(10,1)*100). Beachten Sie, dass ZUFALLSMATRIX eine veränderliche Funktion ist, d. h. dass Sie von Excel bei jeder Berechnung eine neue Gruppe von Zufallszahlen erhalten.

    Excel-Matrixformel zum Ermitteln des N. kleinsten Werts: =KKLEINSTE(B9#;SEQUENZ(D9))

    Geben Sie =KKLEINSTE(B9#,SEQUENZ(D9), =KKLEINSTE(B9:B18,{1;2;3}) ein.

    Diese Formel verwendet eine Matrixkonstante, um die KKLEINSTE-Funktion dreimal auszuwerten und die 3 kleinsten Elemente im Array zurückzugeben, das in den Zellen B9:B18 enthalten ist, wobei 3 ein Variablenwert in Zelle D9 ist. Um weitere Werte zu finden, können Sie den Wert in der SEQUENZ-Funktion erhöhen oder der Konstante weitere Argumente hinzufügen. Sie können auch zusätzliche Funktionen wie SUMME oder MITTELWERT in dieser Formel verwenden. Beispiel:

    =SUMME(KKLEINSTE(B9#,SEQUENZ(D9))

    =MITTELWERT(KKLEINSTE(B9#,SEQUENZ(D9))

  • Suchen der n größten Werte in einem Bereich

    Um nach den größten Werten in einem Bereich zu suchen, können Sie die KKLEINSTE-Funktion durch die KGRÖSSTE-Funktion ersetzen. Zusätzlich werden im folgenden Beispiel die Funktionen ZEILE und INDIREKT verwendet.

    Geben Sie =KGRÖSSTE(B9#,ZEILE(INDIREKT("1:3"))) oder =KGRÖSSTE(B9:B18,ZEILE(INDIREKT("1:3")))

    An diesem Punkt können Kenntnisse über die Funktionen ZEILE und INDIREKT hilfreich sein. Sie können die Funktion ZEILE verwenden, um ein Array aufeinander folgender Ganzzahlen zu erstellen. Markieren Sie beispielsweise eine leere Zelle, und geben Sie Folgendes ein:

    =ZEILE(1:10)

    Die Formel erstellt eine Spalte mit 10 aufeinander folgenden Ganzzahlen. Um ein mögliches Problem zu veranschaulichen, fügen Sie eine Zeile oberhalb des Bereichs ein, der die Arrayformel enthält (d. h. über Zeile 1). Excel passt die Zeilenbezüge an, und die Formel generiert Ganzzahlen von 2 bis 11. Um dieses Problem zu beheben, fügen Sie die Funktion INDIREKT zur Formel hinzu:

    =ZEILE(INDIREKT("1:10"))

    Die Funktion INDIREKT verwendet Textzeichenfolgen als Argumente (weshalb der Bereich 1:10 in doppelte Anführungszeichen eingeschlossen ist). Excel passt Textwerte beim Einfügen von Zeilen oder sonstigem Verschieben der Arrayformel nicht an. Daher wird mit der Funktion ZEILE immer das gewünschte Array von Ganzzahlen generiert. Sie können SEQUENZ genauso einfach verwenden:

    =SEQUENZ(10)

    Sehen wir uns die Formel an, die Sie zuvor verwendet haben – =KGRÖSSTE(B9#,ZEILE(INDIREKT("1:3"))) –, beginnend bei den inneren Klammern und uns dann nach außen arbeitend: Die INDIREKT-Funktion gibt einen Satz von Textwerten zurück, in diesem Fall die Werte 1 bis 3. Die ZEILE-Funktion generiert wiederum ein dreizelliges Spaltenarray. Die KGRÖSSTE-Funktion verwendet die Werte im Zellbereich B9:B18 und wird dreimal ausgewertet, einmal für jeden von der ZEILE-Funktion zurückgegebenen Bezug. Wenn Sie nach weiteren Werte suchen möchten, fügen Sie der INDIREKT-Funktion einen größeren Zellbereich hinzu. Wie bei den KKLEINSTE-Beispielen können Sie diese Formel schließlich auch noch mit anderen Funktionen verwenden, z. B. SUMME und MITTELWERT.

  • Addieren eines Bereichs, der Fehlerwerte enthält

    Die SUMME-Funktion in Excel funktioniert nicht, wenn Sie einen Bereich addieren möchten, der einen Fehlerwert enthält, z. B. #WERT! oder #NV. Dieses Beispiel zeigt, wie Sie die Werte in einem Bereich mit dem Namen „Daten“ addieren, der Fehler enthält.

    Verwenden Sie Matrizen, um Fehler zu behandeln. Beispielsweise summiert =SUMME(WENN(ISTFEHLER(Daten);„“;Daten)) den Bereich mit dem Namen Data, auch wenn er Fehler enthält, wie z.B. #WERT! oder #NV!.

  • =SUMME(WENN(ISTFEHLER(Daten);"";Daten))

    Die Formel erstellt ein neues Array mit den ursprünglichen Werten ohne alle Fehlerwerte. Beginnend mit den inneren Funktionen nach außen durchsucht zunächst die Funktion ISTFEHLER den Zellbereich (Daten) nach Fehlern. Die Funktion WENN gibt einen bestimmten Wert zurück, wenn eine angegebene Bedingung als WAHR bewertet wird, und einen anderen Wert, wenn sie als FALSCH bewertet wird. In diesem Fall werden leere Zeichenfolgen ("") für alle Fehlerwerte zurückgegeben, da sie als WAHR bewertet werden, und die übrigen Werte aus dem Bereich (Daten) zurückgegeben, da diese als FALSCH bewertet werden, d. h., dass keine Fehlerwerte enthalten sind. Die Funktion SUMME berechnet dann die Summe für das gefilterte Array.

  • Zählen der Anzahl von Fehlerwerten in einem Bereich

    Dieses Beispiel ähnelt der vorherigen Formel, gibt jedoch die Anzahl der Fehlerwerte in einem Bereich namens „Daten“ zurück, anstatt sie herauszufiltern:

    =SUMME(WENN(ISTFEHLER(Daten);1;0))

    Mit dieser Formel wird ein Array erstellt, das den Wert 1 für die Zellen aufweist, die Fehler enthalten, und den Wert 0 für die Zellen, die keine Fehler enthalten. Sie können die Formel vereinfachen und dasselbe Ergebnis erzielen, wenn Sie das dritte Argument für die Funktion WENN wie folgt entfernen:

    =SUMME(WENN(ISTFEHLER(Daten);1))

    Wird das Argument nicht angegeben, gibt die WENN-Funktion eine Bewertung als FALSCH zurück, wenn eine Zelle keinen Fehlerwert enthält. Sie können die Formel sogar noch weiter vereinfachen:

    =SUMME(WENN(ISTFEHLER(Daten)*1))

    Diese Version funktioniert, da WAHR*1=1 und FALSCH*1=0.

Möglicherweise müssen Sie Werte auf Grundlage von Bedingungen addieren.

Sie können Matrizen verwenden, um basierend auf bestimmten Bedingungen zu berechnen. =SUMME(WENN(Sales>0;Sales)) summiert alle Werte größer als 0 in einem Bereich namens Sales.

Diese Matrixformel summiert beispielsweise nur die positiven ganzen Zahlen in einem Bereich mit dem Namen „Umsatz“, der die Zellen E9:E24 im obigen Beispiel darstellt:

=SUMME(WENN(Umsatz>0;Umsatz))

Die WENN-Funktion erstellt ein Array positiver und falscher Werte. Die SUMME-Funktion ignoriert die falschen Werte, da 0+0=0. Der Zellbereich, den Sie in dieser Formel verwenden, kann aus einer beliebigen Anzahl von Zeilen und Spalten bestehen.

Sie können auch Werte addieren, die mehr als eine Bedingung erfüllen. Diese Matrixformel berechnet z. B. Werte größer als 0 UND kleiner als oder gleich 2500:

=SUMME((Umsatz>0)*(Umsatz<2500)*(Umsatz))

Beachten Sie, dass diese Formel einen Fehler zurückgibt, wenn der Bereich eine oder mehrere nicht numerische Zellen enthält.

Sie können auch Arrayformeln erstellen, die eine Art von ODER-Bedingung verwenden. Beispielsweise können Sie Werte summieren, die größer als 0 ODER kleiner als 2500 sind:

=SUMME(WENN((Umsatz>0)+(Umsatz<2500),Umsatz))

Sie können die Funktionen UND und ODER nicht direkt in Arrayformeln verwenden, da diese Funktionen ein einzelnes Ergebnis zurückgeben, entweder WAHR oder FALSCH, und Arrayfunktionen Ergebnisarrays benötigen. Sie können das Problem umgehen, indem Sie die in der vorherigen Formel dargestellte Logik verwenden. Mit anderen Worten: Sie führen mathematische Operationen wie eine Addition oder Multiplikation für Werte aus, die die ODER- oder UND-Bedingung erfüllen.

Dieses Beispiel zeigt, wie Nullen aus einem Bereich entfernt werden, wenn ein Mittelwert für die Werte in diesem Bereich berechnet werden muss. Für die Formel wird ein Datenbereich mit dem Namen "Umsatz" verwendet:

=MITTELWERT(WENN(Umsatz<>0;Umsatz))

Die WENN-Funktion erstellt ein Array von Werten, die ungleich 0 sind, und übergibt diese Werte dann an die Funktion MITTELWERT.

Diese Arrayformel vergleicht die Werte in zwei Zellbereichen mit den Namen "EigeneDaten" und "FremdeDaten" und gibt die Anzahl der Unterschiede zwischen den beiden Bereichen zurück. Wenn die Inhalte der beiden Bereiche identisch sind, gibt die Formel den Wert "0" zurück. Um diese Formel verwenden zu können, müssen die Zellbereiche dieselbe Größe und dieselbe Dimension aufweisen. Wenn z. B. „EigeneDaten“ einen Bereich von 3 Zeilen mal 5 Spalten umfasst, muss auch „FremdeDaten“ einen Bereich von 3 Zeilen mal 5 Spalten umfassen:

=SUMME(WENN(EigeneDaten=FremdeDaten;0;1))

Die Formel erstellt ein neues Array mit der gleichen Größe wie die Bereiche, die verglichen werden. Die WENN-Funktion füllt das Array mit dem Wert "0" und dem Wert "1" ("0" für unterschiedliche und "1" für identische Zellen). Die Funktion SUMME gibt dann die Summe der Werte im Array zurück.

Sie können die Formel folgendermaßen vereinfachen:

=SUMME(1*(EigeneDaten<>FremdeDaten))

Wie die Formel, mit der Fehlerwerte in einem Bereich gezählt werden, funktioniert auch diese Formel, da WAHR*1=1 und FALSCH*1=0.

Diese Arrayformel gibt die Zeilennummer des maximalen Werts in einem einspaltigen Bereich mit dem Namen "Daten" zurück:

=MIN(WENN(Daten=MAX(Daten);ZEILE(Daten);""))

Die WENN-Funktion erstellt ein neues Array, das dem Bereich "Daten" entspricht. Wenn eine entsprechende Zelle den maximalen Wert im Bereich enthält, enthält das Array die Zeilennummer. Andernfalls enthält das Array eine leere Zeichenfolge (""). Die Funktion MIN verwendet das neue Array als zweites Argument und gibt den kleinsten Wert zurück, der der Zeilennummer des maximalen Werts in "Daten" entspricht. Wenn der Bereich "Daten" identische maximale Werte enthält, gibt die Formel die Zeile des ersten Werts zurück.

Wenn Sie die eigentliche Zelladresse eines maximalen Werts zurückgeben möchten, verwenden Sie die folgende Formel:

=ADRESSE(MIN(WENN(Daten=MAX(Daten);ZEILE(Daten);""));SPALTE(Daten))

Ähnliche Beispiele finden Sie in der Beispielarbeitsmappe auf dem Arbeitsblatt Unterschiede zwischen Datasets.

Danksagung

Teile dieses Artikels basieren auf einer Reihe von Excel Power User-Spalten, die von Colin Wilcox geschrieben und aus den Kapiteln 14 und 15 von „Excel 2002 Formulas“, einem Buch des ehemaligen Excel MVP John Walkenbach, adaptiert wurden.

Benötigen Sie weitere Hilfe?

Sie können jederzeit einen Experten in der Excel Tech Community fragen oder Unterstützung in den Communities erhalten.

Siehe auch

Dynamische Arrays und Verhalten von übergelaufenem Array

Dynamische Matrixformeln im Vergleich zu früheren CSE-Matrixformeln

FILTER-Funktion

ZUFALLSMATRIX-Funktion

SEQUENZ-Funktion

SORTIEREN-Funktion

SORTIERENNACH-Funktion

EINDEUTIG-Funktion

#ÜBERLAUF! -Fehler in Excel

Impliziter Schnittmengen-Operator: @

Übersicht über Formeln

Facebook LinkedIn E-Mail

Benötigen Sie weitere Hilfe?

Möchten Sie weitere Optionen?

Erkunden Sie die Abonnementvorteile, durchsuchen Sie Trainingskurse, erfahren Sie, wie Sie Ihr Gerät schützen und vieles mehr.

Vorteile des Microsoft 365-Abonnements

Microsoft 365-Training

Microsoft Security

Barrierefreiheitscenter