Bei Microsoft anmelden
Melden Sie sich an, oder erstellen Sie ein Konto.
Hallo,
Wählen Sie ein anderes Konto aus.
Sie haben mehrere Konten.
Wählen Sie das Konto aus, mit dem Sie sich anmelden möchten.
Richtlinien und Beispiele für Arrayformeln

Eine Matrixformel ist eine Formel, mit der mehrere Berechnungen für ein oder mehrere Elemente in einem Array durchgeführt werden können. Sie können sich ein Array als eine Zeile oder Spalte von Werten oder eine Kombination aus Zeilen und Spalten von Werten auswerten. Arrayformeln können entweder mehrere Ergebnisse oder ein einzelnes Ergebnis zurückgeben.

Beginnend mit dem September 2018-Update für Microsoft 365überlaufen alle Formeln, die mehrere Ergebnisse zurückgeben können, automatisch nach unten oder überlaufen in benachbarte Zellen. Diese Verhaltensänderung wird auch von mehreren neuen dynamischen Arrayfunktionen begleitet. Dynamische Matrixformeln (gleich, ob sie vorhandene oder dynamische Arrayfunktionen verwenden) müssen nur in eine einzelne Zelle eingegeben und dann durch Drücken der EINGABETASTE bestätigt werden. Früher müssen für Legacy-Arrayformeln zuerst der gesamte Ausgabebereich ausgewählt und dann die Formel mit STRG+UMSCHALT+EINGABE BESTÄTIGT werden. Sie werden häufig als CSE-Formeln bezeichnet.

Sie können Arrayformeln verwenden, um komplexe Aufgaben auszuführen, z. B.:

  • Erstellen Sie schnell Beispieldatensets.

  • Zählen sie die Anzahl von Zeichen, die in einem Zellbereich enthalten sind.

  • Sie können nur Zahlen summieren, 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 summieren

Die folgenden Beispiele zeigen, wie Mehrfachzellen- und Einzelzellen-Arrayformeln erstellt werden. Wenn möglich, haben wir Beispiele für einige der dynamischen Arrayfunktionen sowie vorhandene Arrayformeln aufgenommen, die sowohl als dynamische Arrays als auch als Legacyarrays eingegeben wurden.

Herunterladen unserer Beispiele

Laden Sie eine Beispielarbeitsmappe mit allen Beispielen für Arrayformeln 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

    Mehrfachzellen-Arrayfunktion in Zelle H10 =F10:F19*G10:G19 zum Berechnen der Anzahl von Autos, die nach Einheitspreis verkauft wurden

  • Hier berechnen wir den Gesamtumsatz von Coupen und Sornen für jeden Verkäufer, indem wir =F10:F19*G10:G19 in Zelle H10 eingeben.

    Wenn Sie die EINGABETASTEdrücken, werden die Ergebnisse in die Zellen H10:H19 überlaufen. Beachten Sie, dass beim Auswählen einer beliebigen Zelle im Überlaufbereich der Überlaufbereich mit einem Rahmen hervorgehoben wird. Möglicherweise sind auch die Formeln in den Zellen H10:H19 abgeblendet. Sie sind nur zu Referenzdaten da. Wenn Sie also die Formel anpassen möchten, müssen Sie Zelle H10 auswählen, in der sich die Masterformel lebt.

  • Einzelzellen-Arrayformel

    Einzelzellen-Arrayformel 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 sie ein, und drücken Sie dann die EINGABETASTE.

    In diesem Fall multipliziert Excel Werte im Array (der Zellbereich F10 bis G19) und addiert dann die Summen mithilfe der Funktion SUMME. Das Ergebnis ist eine Gesamtsumme von 1.590.000 $ Umsatz.

    Dieses Beispiel zeigt, wie leistungsfähig dieser Formeltyp sein kann. Angenommen, Sie haben 1.000 Datenzeilen. Sie können einen Teil dieser Daten oder alle Daten summiert, indem Sie eine Matrixformel in einer einzelnen Zelle erstellen, statt 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. Es kann auch eine bewährte Methode sein, unabhängige Gesamtwerte wie diese zu haben, da die Genauigkeit der Ergebnisse überprüft werden kann.

  • Dynamische Arrayformeln bieten darüber hinaus die folgenden Vorteile:

    • Konsistenz    Wenn Sie auf eine der Zellen von H10 nach unten klicken, wird dieselbe Formel verwendet. Durch diese Konsistenz kann größere Genauigkeit gewährleistet werden.

    • Sicherheit    Sie können eine Komponente einer Mehrfachzellen-Arrayformel nicht überschreiben. Klicken Sie z. B. auf Zelle H11, und drücken Sie DIE LÖSCHEN-TASTE. Excel wird die Ausgabe des Arrays nicht ändern. Um dies zu ändern, müssen Sie die obere linke Zelle (oder Zelle H10) im Array auswählen.

    • Kleinere Dateigrößen    Sie können häufig eine einzelne Arrayformel anstelle mehrerer Formeln für Zwischenschritte verwenden. Im Beispiel für fahrzeugvertrieb wird z. B. eine Matrixformel zum Berechnen der Ergebnisse in Spalte E verwendet. Wenn Sie Standardformeln wie =F10*G10, F11*G11, F12*G12 usw. verwendet hätten, hätten Sie 11 verschiedene Formeln zum Berechnen derselben Ergebnisse verwendet. Das ist keine große Sache, aber was wäre, wenn man Tausende von Zeilen insgesamt hätte? Dann kann es einen großen Unterschied machen.

    • Effizienz    Arrayfunktionen können eine effiziente Möglichkeit zum Erstellen komplexer Formeln sein. Die Arrayformel =SUMME(F10:F19*G10:G19) ist identisch mit der Formel: =SUMME(F10*G10;F11*G11;F12*G12;F 13*G13;F14*G14;F15*G15;F16*G16;F17*G17;F18*G18;F19*G19).

    • Überlaufen    Dynamische Arrayformeln werden automatisch in den Ausgabebereich überlaufen. Wenn sich ihre Quelldaten in einer Excel-Tabelle befindet, ändern Ihre dynamischen Arrayformeln automatisch die Größe, während Sie Daten hinzufügen oder entfernen.

    • #SPILL! Fehler    Dynamische Arrays, die den Fehler #SPILL!gibt an, dass der beabsichtigte Überlaufbereich aus einem bestimmten Grund blockiert ist. Wenn Sie die Blockierung auflösen, überlaufen die Formel automatisch.

Arraykonstante sind eine Komponente von Arrayformeln. Sie erstellen Arraykonstante, indem Sie eine Liste mit Elementen eingeben und die Liste dann manuell in geschweifte Klammern ({ }) umblättern, wie hier:

={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. Es werden Beispiele gezeigt, wie die SEQUENZ-Funktion verwendet wird, um Arraykonstante sowie manuell eingegebene Arraykonstante automatisch zu generieren.

  • Erstellen einer horizontalen Konstante

    Verwenden Sie die Arbeitsmappe aus den vorherigen Beispielen, oder erstellen Sie eine neue Arbeitsmappe. Wählen Sie eine beliebige leere Zelle aus, und geben Sie =SEQUENZ(1;5) ein. Mit der SEQUENZ-Funktion wird ein Array aus einer Zeile und 5 Spalten erstellt, das dem Array ={1.2.3.4.5} gleich ist. Das folgende Ergebnis wird angezeigt:

    Erstellen einer horizontalen Arraykonstante mit =SEQUENZ(1;5) oder ={1.2.3.4.5}

  • Erstellen einer vertikalen Konstante

    Wählen Sie eine beliebige leere Zelle mit darunterliegenden Platz aus, und geben Sie =SEQUENZ(5)oder ={1;2;3;4;5} ein. Das folgende Ergebnis wird angezeigt:

    Erstellen einer vertikalen Arraykonstante mit =SEQUENZ(5) oder ={1;2;3;4;5}

  • Erstellen einer zweidimensionalen Konstante

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

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

    Sie können auch eingeben: oder ={1.2.3.4;5.6.7.8;9.10.11.12}, aber Sie sollten darauf achten, wo Sie Semikolonen und Kommas setzen.

    Wie Sie sehen, bietet die Option SEQUENZ wesentliche Vorteile gegenüber der manuellen Eingabe von Arraykonstante-Werten. In erster Linie spart es Ihnen Zeit, kann aber auch dabei helfen, Fehler bei der manuellen Eingabe zu vermeiden. Außerdem ist es einfacher zu lesen, insbesondere, da die Semikolonen schwer von den Kommastrennzeichen zu unterscheiden sind.

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

In Zelle D9 haben wir =SEQUENZ(1;5;3;1)eingegeben, aber Sie könnten auch 3, 4, 5, 6 und 7 in die Zellen A9:H9 eingeben. An dieser speziellen Zahlenauswahl gibt es nichts Besonderes, wir haben lediglich etwas anderes als 1-5 zur Differenzierung ausgewählt.

Geben Sie in Zelle E11 =SUMME(D9:H9*SEQUENZ(1;5))oder =SUMME(D9:H9*{1.2.3.4.5}) ein. Die Formeln geben 85 zurück.

Verwenden Sie Arraykonstante in Formeln. In diesem Beispiel wurde =SUMME(D9:H(*SEQUENZ(1;5)) verwendet.

Die SEQUENZ-Funktion erstellt die Entsprechung der Arraykonstante {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 (*) ins Spiel. 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 addiert die Funktion SUMME die Werte und gibt 85 zurück.

Um die Verwendung des gespeicherten Arrays zu vermeiden und den Vorgang vollständig im Arbeitsspeicher zu be lassen, können Sie ihn durch eine andere Arraykonstante ersetzen:

=SUMME(SEQUENZ(1;5;3;1)*SEQUENZ(1;5))oder =SUMME({3.4.5.6.7}*{1.2.3.4.5})

Elemente, die Sie in Arraykonstante verwenden können

  • Arraykonstante können Zahlen, Text, logische Werte (z. B. WAHR und FALSCH) und Fehlerwerte wie #N/A enthalten. Zahlen können als ganze Zahl, als Dezimalzahl und als wissenschaftliche Zahl verwendet werden. Wenn Sie Text eingeben, müssen Sie ihn in Anführungszeichen ("Text") umgeben.

  • 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 Methoden zum Verwenden von Arraykonstante ist deren Namen. 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 > Definierten Namen, >Namen definieren. Geben Sie im Feld Name den Namen 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 hier aussehen:

Hinzufügen einer benannten Arraykonstante aus Formeln > definierten Namen > Namens-Manager > Neu

Klicken Sie auf OK,wählen Sie dann eine beliebige Zeile mit drei leeren Zellen aus, und geben Sie =Quartal1 ein.

Das folgende Ergebnis wird angezeigt:

Verwenden Sie eine benannte Arraykonstante in einer Formel wie =Quartal1, wobei "Quartal1" als ={"Januar";"Februar";"März"} definiert wurde

Wenn die Ergebnisse nicht horizontal, sondern vertikal überlaufen sollen, können Sie =M TRANSPOSE(Quartal1) verwenden.

Wenn Sie eine Liste mit 12 Monaten anzeigen möchten, wie sie z. B. beim Erstellen eines Finanzauszugs verwendet wird, können Sie mit der SEQUENZ-Funktion eine Liste vom 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 auf den Arbeitsblättern benannter Arraykonstante und Schnellbeispiel-Dataset in der Beispielarbeitsmappe.

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

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

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

Wenn Sie eine benannte Konstante als eine Arrayformel verwenden, denken Sie daran, das Gleichheitszeichen wie in =Quartal1 und nicht nur in Quartal1 ein eingeben. Wenn dies nicht geschieht, interpretiert Excel das Array als eine Textzeichenfolge, und die Formel funktioniert nicht wie erwartet. Denken Sie schließlich daran, dass Sie Kombinationen aus Funktionen, Text und Zahlen verwenden können. Dies hängt davon ab, wie kreativ Sie sein möchten.

In den folgenden Beispielen sind einige Möglichkeiten dargestellt, wie Sie Arraykonstanten in Arrayformeln einsetzen können. In einigen Beispielen wird die Funktion M TRANSPOSE verwendet, um Zeilen in Spalten und umgekehrt zu konvertieren.

  • Mehrere Elemente in einem Array

    Eingeben von =SEQUENZ(1.12)*2oder ={1.2.3.4;5.6.7.8;9.10.11.12}*2

    Sie können auch durch dividieren (/), addieren mit (+), und subtrahieren mit (-).

  • Quadrieren der Elemente in einem Array

    Geben Sie =SEQUENZ(1;12)^2oder ={1.2.3.4;5.6.7.8;9.10.11.12}^2 ein.

  • Suchen der Quadratwurzel von quadratischen Elementen in einem Array

    Enter =SQRT(SEQUENCE(1,12)^2)oder =SQRT({1.2.3.4;5.6.7.8;9.10.11.12}^2)

  • Transponieren einer eindimensionalen Zeile

    Geben Sie =M TRANSPOSE(SEQUENZ(1;5))oder =M M.({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 =M TRANSPON(SEQUENZ(5;1))oder =M M.({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 =M TRANSPOSE(SEQUENZ(3;4))oder =M M({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 Arrayformeln 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 { (öffnende geschweifte Klammer) ein, bevor Sie 10 und } (schließende geschweifte Klammer) nach der Eingabe von 180 eingeben, da Sie ein Array von Zahlen erstellen.

    Geben Sie als Nächstes =D9#oder =D9:I11 in eine leere Zelle ein. Ein 3 x 6-Array von Zellen wird mit den gleichen Werten angezeigt, die auch in D9:D11 angezeigt werden. Das Zeichen # wird als übergelaufener Bereichsoperator bezeichnet und Excel, um auf den gesamten Arraybereich zu verweisen, anstatt ihn eingeben zu müssen.

    Verwenden des übergelaufenen Bereichsoperators (#), um auf ein vorhandenes Array zu verweisen

  • Erstellen einer Arraykonstante aus vorhandenen Werten

    Sie können die Ergebnisse einer übergelaufenen Matrixformel in ihre Komponenten umwandeln. Wählen Sie Zelle D9 aus, und drücken Sie dann F2, um in den Bearbeitungsmodus zu wechseln. Drücken Sie als Nächstes F9, um die Zellbezüge in Werte zu konvertieren, die Excel dann in eine Arraykonstante konvertiert wird. Wenn Sie die EINGABETASTEdrücken, sollte die Formel =D9# jetzt ={10.20.30;40.50.60;70.80.90} lauten.

  • Zählen der Anzahl von Zeichen in einem Zellbereich

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

    Zählen der Gesamtanzahl von Zeichen in einem Bereich und weitere Arrays 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 des Bereichs zurück. Die Funktion SUMME addiert dann diese Werte und zeigt das Ergebnis an (66). Wenn Sie die durchschnittliche Anzahl von Zeichen erhalten möchten, können Sie verwenden:

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

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

    =INDEX(C9:C13;ÜBEREINSTIMMUNG(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, der der längsten Textzeichenfolge entspricht, die sich in Zelle D3 befindet.

    Nun wird es etwas komplexer. Mit der Funktion VERGLEICH wird der Versatz (die relative Position) der Zelle berechnet, die die längste Textzeichenfolge enthält. 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(LEN(C9:C13)

    und diese Zeichenfolge befindet sich in folgendem Array:

    LEN(C9:C13)

    In diesem Fall ist das Argument "Übereinstimmungstyp" "0". Der Übereinstimmungstyp kann ein Wert 1, 0 oder -1 sein.

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

    • 0: Gibt den ersten Wert zurück, der dem Nachschlagewert genau entspricht.

    • -1 : Gibt den kleinsten Wert zurück, der größer oder gleich dem angegebenen Nachschlagewert ist.

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

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

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

  • Suchen der n kleinsten Werte in einem Bereich

    In diesem Beispiel wird gezeigt, wie Die drei kleinsten Werte in einem Zellbereich suchen, wobei ein Array mit Beispieldaten in den Zellen B9:B18 erstellt wurde mit: =GANZINT(ZUUNT(10;1)*100). Beachten Sie, dass ZUFALLSARRAY eine flüchtige Funktion ist, sodass Sie jedes Mal einen neuen Satz von Zufallszahlen erhalten, Excel berechnen.

    Excel Matrixformel, um den n-kleinsten Wert zu finden: =KKLEINSTE(B9#;SEQUENZ(D9))

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

    Diese Formel verwendet eine Arraykonstante, um die Funktion KLEIN dreimal auszuwerten und die kleinsten 3 Elemente im Array zurück, 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(KLEIN(B9#;SEQUENZ(D9))

    =MITTELWERT(KLEIN(B9#;SEQUENZ(D9))

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

    Um die größten Werte in einem Bereich zu finden, können Sie die Funktion KLEIN durch die Funktion GROß ersetzen. Zusätzlich werden im folgenden Beispiel die Funktionen ZEILE und INDIREKT verwendet.

    Geben Sie =GROß(B9#;ZEILE(INDIREKT("1:3")))oder =GROß(B9:B18;ZEILE(INDIREKT("1:3"))) ein.

    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. Wählen Sie z. B. einen leeren aus, und geben Sie 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 die Zeilenbezüge an, und die Formel generiert jetzt ganze Zahlen 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 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. Sequenz kann genauso einfach verwendet werden:

    =SEQUENZ(10)

    Sehen wir uns die zuvor verwendete Formel = GROSS(B9#;ZEILE(INDIREKT("1:3"))) an – beginnend mit den inneren Klammern nach außen: Die Funktion INDIREKT gibt eine Reihe von Textwerten zurück, in diesem Fall die Werte 1 bis 3. Die Funktion ZEILE generiert wiederum ein Array mit drei Zellen. Die Funktion GROSS verwendet die Werte im Zellbereich B9:B18 und wird dreimal ausgewertet, einmal für jeden Bezug, der von der Funktion ZEILE zurückgegeben wird. Wenn Sie nach weiteren Werten suchen möchten, fügen Sie der Funktion INDIREKT einen größeren Zellbereich hinzu. Wie in den Beispielen für KLEIN können Sie diese Formel auch mit anderen Funktionen wie SUMME und MITTELWERT verwenden.

  • Addieren eines Bereichs, der Fehlerwerte enthält

    Die Summe-Funktion in Excel funktioniert nicht, wenn Sie versuchen, einen Bereich zu summiert, der einen Fehlerwert enthält, z. B. #VALUE! oder #N/A. Dieses Beispiel zeigt, wie Sie die Werte in einem Bereich mit dem Namen "Daten" summieren, der Fehler enthält:

    Verwenden Sie Arrays, um Fehler zu beheben. Beispiel: =SUMME(WENN(ISTFEHLER(Daten);"";Daten) summiert den Bereich namens "Daten", auch wenn er Fehler enthält, z. B. #VALUE! oder #NA!.

  • =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 ist wie die vorherige Formel, gibt aber die Anzahl der Fehlerwerte in einem Bereich namens "Daten" zurück, statt sie heraus zu filtern:

    =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 Arrays verwenden, um Auf Grundlage bestimmter Bedingungen zu berechnen. =SUMME(WENN(Umsatz>0;Umsatz)) summiert alle Werte größer als 0 in einem Bereich namens "Umsatz".

Mit dieser Arrayformel werden beispielsweise nur die positiven Ganzzahlen in einem Bereich namens "Umsatz" summiert, der die Zellen E9:E24 im vorstehenden 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. Mit dieser Arrayformel werden beispielsweise Werte berechnet, die größer als 0 UND kleiner als 2500 sind:

=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. Sie können beispielsweise 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 aus, z. B. Additionen oder Multiplikation für Werte, die der ODER- oder UND-Bedingung entsprechen.

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 MyData z. B. ein Bereich von 3 Zeilen und 5 Spalten ist, muss "YourData" ebenfalls 3 Zeilen und 5 Spalten sein:

=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<>Daten))

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.

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

Kopieren Sie die gesamte nachstehende Tabelle, und fügen Sie sie in Zelle A1 eines leeren Arbeitsblatts ein.

Vertriebsmitarbeiter

Fahrzeugtyp

Nummer verkauft

Einheitenpreis

Gesamtumsatz

Bott

Limousine

5

33000

Coupé

4

37000

Inger

Limousine

6

24000

Coupé

8

21000

Jordan

Limousine

3

29000

Coupé

1

31000

Probst

Limousine

9

24000

Coupé

5

37000

Sanchez

Limousine

6

33000

Coupé

8

31000

Formel (Gesamtsumme)

Gesamtsumme

'=SUMME(C2:C11*D2:D11)

=SUMME(C2:C11*D2:D11)

  1. Um den Gesamtumsatz von Coupen und Sornen für jeden Verkäufer zu sehen, markieren Sie die Zellen E2:E11, geben Sie die Formel =C2:C11*D2:D11ein, und drücken Sie dann STRG+UMSCHALT+EINGABETASTE.

  2. Um die Gesamtsumme aller Umsätze zu sehen, markieren Sie Zelle F11, geben Sie die Formel =SUMME(C2:C11*D2:D11)ein, und drücken Sie dann STRG+UMSCHALT+EINGABETASTE.

Wenn Sie STRG+UMSCHALT+EINGABETASTEdrücken, wird Excel die Formel in geschweifte Klammern ({ }) eingeschlossen und eine Instanz der Formel in jede Zelle des markierten Bereichs eingefügt. Dies geschieht sehr schnell, und es wird Ihnen in Spalte E der Gesamtbetrag des Umsatzes für jeden Fahrzeugtyp und jeden Verkäufer angezeigt. Wenn Sie E2 und dann E3, E4 und so weiter auswählen, wird die gleiche Formel angezeigt: {=C2:C11*D2:D11}. 

Die Summen in Spalte E werden mithilfe einer Arrayformel berechnet

  • Erstellen einer Einzelzellen-Arrayformel

Geben Sie in Zelle D13 der Arbeitsmappe die folgende Formel ein, und drücken Sie dann STRG+UMSCHALT+EINGABETASTE:

=SUMME(C2:C11*D2:D11)

In diesem Fall multipliziert Excel Werte im Array (Zellbereich C2 bis D11) und addiert dann die Summen mithilfe der Funktion SUMME. Das Ergebnis ist eine Gesamtsumme von 1.590.000 $ Umsatz. Dieses Beispiel zeigt, wie leistungsfähig dieser Formeltyp sein kann. Angenommen, Sie haben 1.000 Datenzeilen. Sie können einen Teil dieser Daten oder alle Daten summiert, indem Sie eine Matrixformel in einer einzelnen Zelle erstellen, statt die Formel durch die 1.000 Zeilen nach unten zu ziehen.

Beachten Sie auch, dass die Einzelzellenformel in Zelle D13 vollkommen unabhängig von der Mehrfachzellenformel (der Formel in den Zellen E2 bis E11) ist. Dies ist ein weiterer Vorteil von Arrayformeln: Flexibilität. Sie können die Formeln in Spalte E ändern oder diese Spalte vollständig löschen, ohne dass dies Auswirkungen auf die Formel in D13 hat.

Arrayformeln bieten darüber hinaus folgende Vorteile:

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

  • Sicherheit    Eine Komponente einer Mehrfachzellen-Arrayformel kann nicht überschrieben werden. Klicken Sie beispielsweise auf Zelle E3, und drücken Sie DIE TASTE. Sie müssen entweder den gesamten Zellbereich (E2 bis E11) markieren und die Formel für das gesamte Array ändern, oder Sie müssen das Array wie folgt be lassen. Als zusätzliche Sicherheitsmaßnahme müssen Sie STRG+UMSCHALT+EINGABETASTE drücken, um alle Änderungen an der Formel zu bestätigen.

  • Kleinere Dateigrößen    Sie können häufig eine einzelne Arrayformel anstelle mehrerer Formeln für Zwischenschritte verwenden. In der Arbeitsmappe wird z. B. eine Matrixformel zum Berechnen der Ergebnisse in Spalte E verwendet. Wenn Sie Standardformeln (z. B. =C2*D2, C3*D3, C4*D4 ...) verwendet hätten, hätten Sie 11 verschiedene Formeln zum Berechnen derselben Ergebnisse verwendet.

Im Allgemeinen verwenden Arrayformeln eine Standardformelsyntax. Alle beginnen mit einem Gleichheitszeichen (=), und Sie können die meisten der integrierten Excel in Matrixformeln verwenden. Der Hauptunterschied besteht in der Verwendung einer Arrayformel: Drücken Sie STRG+UMSCHALT+EINGABETASTE, um die Formel ein eingeben. Wenn Sie dies tun, Excel ihre Arrayformel in geschweifte Klammern ein – wenn Sie die geschweiften Klammern manuell eingeben, wird die Formel in eine Textzeichenfolge konvertiert und funktioniert nicht.

Arrayfunktionen können eine effiziente Möglichkeit zum Erstellen komplexer Formeln sein. Die Arrayformel =SUMME(C2:C11*D2:D11) entspricht Folgendem: =SUMME(C2*D2;C3*D3;C4*D4;C5*D5;C6*D6;C7*D7;C8*D8;C9*D9;C10*D10;C11*D11).

Wichtig: Drücken Sie immer STRG+UMSCHALT+EINGABETASTE, wenn Sie eine Matrixformel eingeben müssen. Dies gilt sowohl für Einzelzellen- als auch für Mehrfachzellenformeln.

Beim Arbeiten mit Mehrfachzellenformeln ist außerdem Folgendes zu beachten:

  • Wählen Sie den Zellbereich für die Ergebnisse aus, bevor Sie die Formel eingeben. Dies war beim Erstellen der Mehrfachzellen-Arrayformel der Fall, als Sie die Zellen E2 bis E11 markierten.

  • Sie können den Inhalt einer einzelnen Zelle in einer Arrayformel nicht ändern. Probieren Sie dies einmal aus, indem Sie die Zelle E3 in der Arbeitsmappe markieren und ENTF drücken. Es wird eine Meldung in Excel angezeigt, die Ihnen mitteilt, dass Teile eines Arrays nicht geändert werden können .

  • Sie können eine gesamte Arrayformel, jedoch nicht einen Teil der Formel verschieben oder löschen. Mit anderen Worten: Zum Verkleinern einer Arrayformel löschen Sie zunächst die vorhandene Formel, und beginnen Sie dann erneut.

  • Wenn Sie eine Matrixformel löschen möchten, markieren Sie den gesamten Formelbereich (z. B. E2:E11),und drücken Sie dann DIE LÖSCHEN-TASTE.

  • Sie können keine leeren Zellen in eine Mehrfachzellen-Arrayformel einfügen oder Zellen aus einer Mehrfachzellen-Arrayformel löschen.

Manchmal kann es der Fall sein, dass Sie eine Arrayformel erweitern müssen. Markieren Sie die erste Zelle im vorhandenen Arraybereich, und fahren Sie fort, bis Sie den gesamten Bereich markiert haben, auf den Sie die Formel erweitern möchten. Drücken Sie F2, um die Formel zu bearbeiten, und drücken Sie dann STRG+UMSCHALT+EINGABETASTE, um die Formel zu bestätigen, nachdem Sie den Formelbereich angepasst haben. Der Schlüssel besteht in der Auswahl des gesamten Bereichs, beginnend mit der Zelle oben links im Array. Die Zelle in der oberen linken Zelle wird bearbeitet.

Arrayformeln sind großartig, doch können sie auch einige Nachteile aufweisen:

  • Es kann gelegentlich vergessen werden, STRG+UMSCHALT+EINGABETASTE zu drücken. Das kann auch dem erfahrensten Excel-Benutzer passieren. Denken Sie beim Eingeben oder Bearbeiten einer Arrayformel immer daran, diese Tastenkombination zu drücken.

  • Andere Benutzer der Arbeitsmappe verstehen Ihre Formeln möglicherweise nicht. Im Allgemeinen werden Arrayformeln in einem Arbeitsblatt nicht erläutert. Wenn andere Personen Ihre Arbeitsmappen ändern müssen, sollten Sie daher entweder Arrayformeln vermeiden oder sicherstellen, dass diese Personen über Arrayformeln wissen und verstehen, wie sie bei Bedarf geändert werden können.

  • Je nach Verarbeitungsgeschwindigkeit und Speicherkapazität des Computers können große Arrayformeln Berechnungen verlangsamen.

Arraykonstante sind eine Komponente von Arrayformeln. Sie erstellen Arraykonstante, indem Sie eine Liste mit Elementen eingeben und die Liste dann wie hier angegeben manuell in geschweifte Klammern ({ }) umblättern:

={1.2.3.4.5}

Bis jetzt wissen Sie, dass Sie beim Erstellen von Arrayformeln STRG+UMSCHALT+EINGABETASTE drücken müssen. Da Arraykonstanten eine Komponente von Arrayformeln sind, schließen Sie die Konstanten durch manuelle Eingabe von geschweiften Klammern ein. Anschließend verwenden Sie STRG+UMSCHALT+EINGABETASTE, um die gesamte Formel ein eingeben.

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). Zum Erstellen eines zweidimensionalen Arrays trennen Sie die Elemente in jeder Zeile durch Kommas und jede Zeile durch Semikolons.

Dies ist ein Array in einer einzelnen Zeile: {1.2.3.4}. Ein Array in einer einzelnen Spalte lautet: {1;2;3;4}. Ein Array mit zwei Zeilen und vier Spalten lautet: {1.2.3.4;5.6.7.8}. Im Array mit zwei Zeilen ist die erste Zeile 1, 2, 3 und 4, und die zweite Zeile ist 5, 6, 7 und 8. Die beiden Zeilen werden durch ein einzelnes Semikolon zwischen 4 und 5 getrennt.

Wie Arrayformeln können Sie auch Arraykonstanten für die meisten der integrierten Funktionen in Excel verwenden. In den folgenden Abschnitten wird beschrieben, wie die einzelnen Arten von Konstanten erstellt und für Funktionen in Excel verwendet werden.

Die folgenden Verfahren dienen als Übung zum Erstellen horizontaler, vertikaler und zweidimensionaler Konstanten.

Erstellen einer horizontalen Konstante

  1. Markieren Sie in einem leeren Arbeitsblatt die Zellen A1 bis E1.

  2. Geben Sie in der Formelleiste die folgende Formel ein, und drücken Sie dann STRG+UMSCHALT+EINGABETASTE:

    ={1.2.3.4.5}

    In diesem Fall sollten Sie die öffnenden und schließenden geschweiften Klammern ({ }) eingeben Excel dann den zweiten Satz für Sie hinzu.

    Das folgende Ergebnis wird angezeigt.

    Horizontale Arraykonstante in einer Formel

Erstellen einer vertikalen Konstante

  1. Markieren Sie in der Arbeitsmappe eine Spalte mit fünf Zellen.

  2. Geben Sie in der Formelleiste die folgende Formel ein, und drücken Sie dann STRG+UMSCHALT+EINGABETASTE:

    ={1;2;3;4;5}

    Das folgende Ergebnis wird angezeigt.

    Vertikale Arraykonstante in einer Arrayformel

Erstellen einer zweidimensionalen Konstante

  1. Markieren Sie in Ihrer Arbeitsmappe einen Block von Zellen mit einer Breite von vier Spalten und einer Höhe von drei Zeilen.

  2. Geben Sie in der Formelleiste die folgende Formel ein, und drücken Sie dann STRG+UMSCHALT+EINGABETASTE:

    ={1.2.3.4;5.6.7.8;9.10.11.12}

    Das folgende Ergebnis wird angezeigt:

    Zweidimensionale Arraykonstante in einer Arrayformel

Verwenden von Konstanten in Formeln

Es folgt ein einfaches Beispiel, bei dem Konstanten verwendet werden:

  1. Erstellen Sie ein neues Arbeitsblatt in der Beispielarbeitsmappe.

  2. Geben Sie in Zelle A1 den Wert 3, dann in B1 den Wert 4, in C1 den Wert 5, in D1 den Wert 6 und in E1 den Wert 7 ein.

  3. Geben Sie in Zelle A3 die folgende Formel ein, und drücken Sie dann STRG+UMSCHALT+EINGABETASTE:

    =SUMME(A1:E1*{1.2.3.4.5})

    Beachten Sie, dass Excel die Konstante in einen weiteren Satz geschweifter Klammern einschließt, da Sie diese als eine Arrayformel eingegeben haben.

    Arrayformel mit Arraykonstante

    In der Zelle A3 wird der Wert 85 angezeigt.

Im nächsten Abschnitt wird die Funktionsweise der Formel erläutert.

Die gerade verwendete Formel umfasst mehrere Teile.

Syntax einer Arrayformel mit Arraykonstante

1. Funktion

2. Gespeichertes Array

3. Operator

4. Arraykonstante

Bei dem letzten Element innerhalb der Klammer handelt es sich um die Arraykonstante {1.2.3.4.5}. Wie Sie wissen, schließt Excel Arraykonstanten nicht in geschweifte Klammern ein, sondern sie werden von Ihnen eingegeben. Denken Sie auch daran, dass Sie nach dem Hinzufügen einer Konstante zu einer Arrayformel STRG+UMSCHALT+EINGABETASTE drücken, um die Formel ein eingeben.

Da Excel zuerst Operationen für Ausdrücke ausführt, die in Klammern eingeschlossen sind, handelt es sich bei den nächsten beiden Elementen, die ins Spiel kommen, um die in der Arbeitsmappe gespeicherten Werte (A1:E1) und den Operator. An diesem Punkt multipliziert die Formel die Werte im gespeicherten Array mit den entsprechenden Werten in der Konstante. Dies entspricht Folgendem:

=SUMME(A1*1;B1*2;C1*3;D1*4;E1*5)

Schließlich werden mit der Funktion SUMME die Werte addiert, und die Summe 85 wird in Zelle A3 angezeigt.

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

=SUMME({3.4.5.6.7}*{1.2.3.4.5})

Um dies auszuprobieren, kopieren Sie die Funktion, markieren Sie eine leere Zelle in Ihrer Arbeitsmappe, fügen Sie die Formel in die Formelleiste ein, und drücken Sie dann STRG+UMSCHALT+EINGABETASTE. Es wird dasselbe Ergebnis wie in der vorherigen Übung angezeigt, in der die folgende Arrayformel verwendet wurde:

=SUMME(A1:E1*{1.2.3.4.5})

Arraykonstanten können Zahlen, Text, logische Werte (z. B. WAHR und FALSCH) und Fehlerwerte (z. B. #NV) enthalten. Bei Zahlen kann es sich um ganze Zahlen, Dezimalzahlen und Zahlen in wissenschaftlichen Formaten handeln. Wenn Sie Text einfügen, müssen Sie diesen in Anführungszeichen (") 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 Verfahren zum Verwenden von Arraykonstante ist deren Namen. 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:

  1. Klicken Sie auf der Registerkarte Formeln in der Gruppe Definierte Namen auf Namen definieren.
    Das Dialogfeld Namen definieren wird angezeigt.

  2. Geben Sie im Feld Name die Zeichenfolge Quartal1 ein.

  3. Geben Sie im Feld Bezieht sich auf die folgende Konstante ein (denken Sie daran, die geschweiften Klammern manuell einzugeben):

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

    Der Inhalt des Dialogfelds sieht jetzt folgendermaßen aus:

    Dialogfeld 'Name bearbeiten' mit Formel

  4. Klicken Sie auf OK, und markieren Sie dann eine Zeile mit drei leeren Zellen.

  5. Geben Sie die folgende Formel ein, und drücken Sie dann STRG+UMSCHALT+EINGABETASTE.

    =Quartal1

    Das folgende Ergebnis wird angezeigt.

    Benanntes Array als Formel eingegeben

Wenn Sie eine benannte Konstante als eine Arrayformel verwenden, denken Sie daran, das Gleichheitszeichen einzugeben. Wenn dies nicht geschieht, interpretiert Excel das Array als eine Textzeichenfolge, und die Formel funktioniert nicht wie erwartet. Sie sollten auch beachten, dass Sie Kombinationen von Text und Zahlen verwenden können.

Sollten die Arraykonstanten nicht funktionieren, prüfen Sie diese auf folgende Probleme:

  • Möglicherweise sind einige Elemente nicht durch das richtige Zeichen getrennt. Wenn Sie ein Komma oder Semikolon weglassen oder an der falschen Stelle platzieren, wird die Arraykonstante möglicherweise nicht ordnungsgemäß erstellt, oder es wird eine Warnmeldung angezeigt.

  • Möglicherweise haben Sie einen Zellbereich ausgewählt, der nicht der Anzahl der Elemente in Ihrer Konstante entspricht. Wenn Sie z. B. eine Spalte mit sechs Zellen zur Verwendung für eine fünfzellige Konstante ausgewählt haben, wird der Fehlerwert "#NV" in der leeren Zelle angezeigt. Wenn Sie hingegen zu wenige Zellen auswählen, lässt Excel die Werte aus, für die keine entsprechende Zelle vorhanden ist.

In den folgenden Beispielen sind einige Möglichkeiten dargestellt, wie Sie Arraykonstanten in Arrayformeln einsetzen können. In einigen Beispielen wird die Funktion M TRANSPOSE verwendet, um Zeilen in Spalten und umgekehrt zu konvertieren.

Multiplizieren jedes Elements in einem Array

  1. Erstellen Sie ein neues Arbeitsblatt, und markieren Sie dann einen Block leerer Zellen mit einer Breite von vier Spalten und einer Höhe von drei Zeilen.

  2. Geben Sie die folgende Formel ein, und drücken Sie dann STRG+UMSCHALT+EINGABETASTE:

    ={1.2.3.4;5.6.7.8;9.10.11.12}*2

Quadrieren der Elemente in einem Array

  1. Markieren Sie einen Block leerer Zellen mit einer Breite von vier Spalten und einer Höhe von drei Zeilen.

  2. Geben Sie die folgende Matrixformel ein, und drücken Sie dann STRG+UMSCHALT+EINGABETASTE:

    ={1.2.3.4;5.6.7.8;9.10.11.12}*{1.2.3.4;5.6.7.8;9.10.11.12}

    Alternativ können Sie diese Arrayformel eingeben, bei der das Caretzeichen (^) als Operator verwendet wird:

    ={1.2.3.4;5.6.7.8;9.10.11.12}^2

Transponieren einer eindimensionalen Zeile

  1. Markieren Sie eine Spalte mit fünf leeren Zellen.

  2. Geben Sie die folgende Formel ein, und drücken Sie dann STRG+UMSCHALT+EINGABETASTE:

    =MTRANS({1.2.3.4.5})

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

Transponieren einer eindimensionalen Spalte

  1. Markieren Sie eine Zeile mit fünf leeren Zellen.

  2. Geben Sie die folgende Formel ein, und drücken Sie dann STRG+UMSCHALT+EINGABETASTE:

    =MTRANS({1;2;3;4;5})

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

Transponieren einer zweidimensionalen Konstante

  1. Markieren Sie einen Block von Zellen mit einer Breite von drei Spalten und einer Höhe von vier Zeilen.

  2. Geben Sie die folgende Konstante ein, und drücken Sie dann STRG+UMSCHALT+EINGABETASTE:

    =MTRANS({1.2.3.4;5.6.7.8;9.10.11.12})

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

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

Erstellen von Arrays und Arraykonstanten aus vorhandenen Werten

Das folgende Beispiel erläutert, wie Arrayformeln zum Erstellen von Verknüpfungen zwischen Zellbereichen in verschiedenen Arbeitsblättern verwendet werden. Es veranschaulicht außerdem, wie eine Arraykonstante aus demselben Satz von Werten erstellt wird.

Erstellen eines Arrays aus vorhandenen Werten

  1. Markieren Sie in einem Arbeitsblatt in Excel die Zellen C8:E10, und geben Sie die folgende Formel ein:

    ={10.20.30;40.50.60;70.80.90}

    Geben Sie unbedingt { (eine öffnende geschweifte Klammer) vor dem Eingeben von "10" und } (eine schließende geschweifte Klammer) nach dem Eingeben von "90" ein, weil Sie ein Array von Zahlen erstellen.

  2. Drücken Sie STRG+UMSCHALT+EINGABETASTE,um dieses Array von Zahlen in den Zellbereich C8:E10 mithilfe einer Matrixformel ein eingaben zu können. Auf ihrem Arbeitsblatt sollten C8 bis E10 wie hier aussehen:

    10

    20

    30

    40

    50

    60

    70

    80

    90

  3. Markieren Sie den Zellbereich C1 bis E3.

  4. Geben Sie die folgende Formel in die Formelleiste ein, und drücken Sie dann STRG+UMSCHALT+EINGABETASTE:

    =C8:E10

    Ein 3 x 3-Array von Zellen wird in den Zellen C1 bis E3 mit den gleichen Werten angezeigt, die in C8 bis E10 angezeigt werden.

Erstellen einer Arraykonstante aus vorhandenen Werten

  1. Drücken Sie bei markierten Zellen C1:C3 F2, um in den Bearbeitungsmodus zu wechseln. 

  2. Drücken Sie F9, um die Zellbezüge in Werte zu konvertieren. Excel wandelt die Werte in eine Arraykonstante um. Die Formel sollte jetzt ={10.20.30;40.50.60;70.80.90} sein.

  3. Drücken Sie STRG+UMSCHALT+EINGABETASTE, um die Arraykonstante als Matrixformel ein eingaben zu können.

Zählen der Anzahl von Zeichen in einem Zellbereich

Das folgende Beispiel zeigt, wie die Anzahl der Zeichen, einschließlich Leerzeichen, in einem Zellbereich gezählt wird.

  1. Kopieren Sie die gesamte folgende Tabelle, und fügen Sie sie in Zelle A1 eines Arbeitsblatts ein.

    Daten

    Dies ist eine

    Gruppe von Zellen,

    die zusammen

    einen Satz

    bilden.

    Gesamtzahl der Zeichen in A2:A6

    =SUMME(LÄNGE(A2:A6))

    Inhalt der längsten Zelle (A3)

    =INDEX(A2:A6;VERGLEICH(MAX(LÄNGE(A2:A6));LÄNGE(A2:A6);0);1)

  2. Markieren Sie Zelle A8, und drücken Sie dann STRG+UMSCHALT+EINGABETASTE, um die Gesamtzahl der Zeichen in den Zellen A2:A6 (66) zu sehen.

  3. Markieren Sie Zelle A10, und drücken Sie dann STRG+UMSCHALT+EINGABETASTE, um den Inhalt der längsten Zelle A2:A6 (Zelle A3) anzuzeigen.

Mit der folgenden Formel wird in Zelle A8 die Gesamtzahl der Zeichen (66) in den Zellen A2 bis A6 ermittelt.

=SUMME(LÄNGE(A2:A6))

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 (66).

Suchen der n kleinsten Werte in einem Bereich

Dieses Beispiel zeigt, wie nach den drei kleinsten Werten in einem Zellbereich gesucht wird.

  1. Geben Sie einige Zufallszahlen in die Zellen A1:A11 ein.

  2. Markieren Sie die Zellen C1 bis C3. Diese Gruppe von Zellen wird die von der Arrayformel zurückgegebenen Ergebnisse enthalten.

  3. Geben Sie die folgende Formel ein, und drücken Sie dann STRG+UMSCHALT+EINGABETASTE:

    =KLEIN(A1:A11;{1;2;3})

Diese Formel verwendet eine Arraykonstante, um die Funktion KLEIN dreimal auszuwerten und das kleinste (1), das zweitkleinste (2) und das drittkleinste (3) Member im Array zurück, das in den Zellen A1:A10 enthalten ist. Um weitere Werte zu finden, fügen Sie der Konstante weitere Argumente hinzu. Sie können auch zusätzliche Funktionen wie SUMME oder MITTELWERT in dieser Formel verwenden. Beispiel:

=SUMME(KLEIN(A1:A10;{1.2.3})

=MITTELWERT(KLEIN(A1:A10;{1.2.3})

Suchen der n größten Werte in einem Bereich

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

  1. Markieren Sie die Zellen D1 bis D3.

  2. Geben Sie diese Formel in die Formelleiste ein, und drücken Sie dann STRG+UMSCHALT+EINGABETASTE:

    =KGRÖSSTE(A1:A10;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 z. B. eine leere Spalte mit 10 Zellen in Ihrer Übungsarbeitsmappe, geben Sie diese Arrayformel ein, und drücken Sie dann STRG+UMSCHALT+EINGABETASTE:

=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.

Werfen wir einen Blick auf die formel, die Sie zuvor verwendet haben – =GROSS(A5:A14;ZEILE(INDIREKT("1:3"))) – beginnend mit den inneren Klammern und nach außen: Die Funktion INDIREKT gibt eine Reihe von Textwerten zurück, in diesem Fall die Werte 1 bis 3. Die Funktion ZEILE generiert wiederum ein aus drei Zellen spaltiges Array. Die Funktion GROßE verwendet die Werte im Zellbereich A5:A14 und wird dreimal ausgewertet, einmal für jeden Bezug, der von der Funktion ZEILE zurückgegeben wird. Die Werte 3200, 2700 und 2000 werden an die Matrix mit drei Spalten zurückgegeben. Wenn Sie nach weiteren Werten suchen möchten, fügen Sie der Funktion INDIREKT einen größeren Zellbereich hinzu.

Wie bei früheren Beispielen können Sie diese Formel auch mit anderen Funktionen wie SUMME und MITTELWERT verwenden.

Suchen der längsten Textzeichenfolge in einem Zellbereich

Wechseln Sie zurück zum Beispiel für eine frühere Textzeichenfolge, geben Sie die folgende Formel in eine leere Zelle ein, und drücken Sie STRG+UMSCHALT+EINGABETASTE:

=INDEX(A2:A6;VERGLEICH(MAX(LÄNGE(A2:A6));LÄNGE(A2:A6);0);1)

Der Text "Reihe von Zellen, der" wird angezeigt.

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 A2:A6 zurück. Die Funktion MAX berechnet den größten Wert unter diesen Elementen, der der längsten Textzeichenfolge entspricht, die sich in Zelle A3 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(LEN(A2:A6))

und diese Zeichenfolge befindet sich in folgendem Array:

LEN(A2:A6)

Das Argument für den Vergleichstyp lautet 0. Der Vergleichstyp kann den Wert "1", "0" oder "-1" aufweisen. Wenn Sie "1" eingeben, gibt VERGLEICH den größten Wert zurück, der kleiner oder gleich dem Suchwert ist. Wenn Sie "0" angeben, gibt VERGLEICH den ersten Wert zurück, der dem Suchwert genau entspricht. Wenn Sie "-1" eingeben, gibt VERGLEICH 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 und eine Zeilen- und Spaltennummer innerhalb dieses Arrays. Der Zellbereich A2:A6 stellt das Array, die Funktion ERSTERWERT stellt die Zelladresse fest, und das letzte Argument (1)gibt an, dass der Wert aus der ersten Spalte im Array stammt.

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

Addieren eines Bereichs, der Fehlerwerte enthält

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

=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, doch wird die Anzahl der Fehlerwerte in einem Bereich mit dem Namen "Daten" zurückgegeben, statt diese 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.

Addieren von Werten basierend auf Bedingungen

Möglicherweise müssen Sie Werte auf Grundlage von Bedingungen addieren. Mit dieser Arrayformel werden beispielsweise nur die positiven Ganzzahlen in einem Bereich mit dem Namen "Umsatz" addiert:

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

Die WENN-Funktion erstellt ein Array positiver Werte 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 Arrayformel berechnet z. B. Werte größer als 0 und kleiner als oder gleich 5:

=SUMME((Umsatz>0)*(Umsatz<=5)*(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. So können Sie beispielsweise Werte addieren, die kleiner als 5 und größer als 15 sind:

=SUMME(WENN((Umsatz<5)+(Umsatz>15);Umsatz))

Die WENN-Funktion sucht nach allen Werten, die kleiner als 5 und größer als 15 sind, und übergibt diese Werte an die Funktion SUMME.

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 der ODER- oder UND-Bedingung entsprechen.

Berechnen eines Mittelwerts unter Ausschluss von Nullen

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.

Zählen der Anzahl von Unterschieden zwischen zwei Zellbereichen

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 die gleiche Größe und die gleiche 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<>Daten))

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

Suchen der Position des maximalen Werts in einem Bereich

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))

Bestätigung

Teile dieses Artikels basieren auf einer Reihe von Excel Power User-Spalten, die von Colin Wilcox geschrieben wurden und aus den Kapiteln 14 und 15 von Excel 2002-Formeln angepasst wurden, einem Buch des früheren Excel MVP.

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 Arrayformeln im Vergleich zu älteren CSE-Arrayformeln

FILTER-Funktion

ZUFALLSMATRIX-Funktion

SEQUENZ-Funktion

SORTIEREN-Funktion

SORTIERENNACH-Funktion

EINDEUTIG-Funktion

#ÜBERLAUF! -Fehler in Excel

Impliziter Schnittmengenoperator: @

Übersicht über Formeln

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.

In den Communities können Sie Fragen stellen und beantworten, Feedback geben und von Experten mit umfassendem Wissen hören.

War diese Information hilfreich?

Wie zufrieden sind Sie mit der Sprachqualität?
Was hat Ihre Erfahrung beeinflusst?
Wenn Sie auf "Absenden" klicken, wird Ihr Feedback zur Verbesserung von Produkten und Diensten von Microsoft verwendet. Ihr IT-Administrator kann diese Daten sammeln. Datenschutzbestimmungen.

Vielen Dank für Ihr Feedback!

×