Richtlinien und Beispiele für Arrayformeln

Richtlinien und Beispiele für Arrayformeln

Hinweis: Wir möchten Ihnen die aktuellsten Hilfeinhalte so schnell wie möglich in Ihrer eigenen Sprache bereitstellen. Diese Seite wurde automatisiert übersetzt und kann Grammatikfehler oder Ungenauigkeiten enthalten. Unser Ziel ist es, Ihnen hilfreiche Inhalte bereitzustellen. Teilen Sie uns bitte über den Link am unteren Rand dieser Seite mit, ob die Informationen für Sie hilfreich sind. Hier finden Sie den englischen Artikel als Referenz.

Eine Matrixformel ist eine Formel, die mehrere Berechnungen an einem oder mehreren Elementen in einem Array ausführen kann. Sie können sich ein Array als eine Zeile oder Spalte mit Werten oder eine Kombination aus Zeilen und Spalten mit Werten vorstellen. Array Formeln können entweder mehrere Ergebnisse oder ein einzelnes Ergebnis zurückgeben.

Beginnend mit dem 2018-Update vom September für Office 365werden alle Formeln, die mehrere Ergebnisse zurückgeben können, automatisch entweder nach unten oder in benachbarte Zellen verschütten. Diese Änderung des Verhaltens wird auch von mehreren neuen dynamischen Array Funktionenbegleitet. Dynamische Arrayformeln, unabhängig davon, ob Sie vorhandene Funktionen oder die dynamischen Array Funktionen verwenden, müssen nur in eine einzelne Zelle eingegeben werden und dann durch Drücken der EINGABETASTEbestätigt werden. Frühere Arrayformeln erfordern zuerst die Auswahl des gesamten Ausgabebereichs und bestätigen dann die Formel mit STRG + UMSCHALT + EINGABETASTE. Sie werden gemeinhin als CSE -Formeln bezeichnet.

Sie können Arrayformeln zum Ausführen komplexer Aufgaben verwenden, z. b.:

  • Erstellen Sie schnell Beispiel Datasets.

  • Zählen Sie die Anzahl der Zeichen, die in einem Zellbereich enthalten sind.

  • Summieren Sie nur Zahlen, die bestimmte Bedingungen erfüllen, z. b. die niedrigsten Werte in einem Bereich oder Zahlen, die zwischen einem oberen und unteren Grenzwert liegen.

  • Summieren Sie jeden n-ten Wert in einem Wertebereich.

Die folgenden Beispiele zeigen, wie Sie Arrayformeln mit mehreren Zellen und einzelnen Zellen erstellen. Wo möglich, haben wir Beispiele mit einigen der dynamischen Array Funktionen sowie vorhandenen Arrayformeln, die als dynamische und ältere Arrays eingegeben wurden, aufgenommen.

Herunterladen unserer Beispiele

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

    Arrayfunktion mit mehreren Zellen in Zelle H10 = F10: F19 * G10: G19 zum Berechnen der Anzahl von Autos, die nach Einzelpreis verkauft werden

  • Hier werden die Gesamtumsätze von Coupés und Limousinen für jeden Verkäufer berechnet, indem Sie = F19: F19 * G10: G19 in Zelle H10 eingeben.

    Wenn Sie die Eingabe Taste drücken, sehen Sie, wie sich die Ergebnisse auf die Zellen H10: H19. Beachten Sie, dass der verschüttenbereich mit einem Rahmen hervorgehoben wird, wenn Sie eine beliebige Zelle im Überlaufbereich auswählen. Möglicherweise beachten Sie auch, dass die Formeln in den Zellen H10: H19 abgeblendet sind. Wenn Sie die Formel anpassen möchten, müssen Sie die Zelle H10 auswählen, in der sich die Master Formel befindet.

  • Formel für ein einzelnes Zell Array

    Formel für ein einzelnes Zell Array zum Berechnen eines Gesamtergebnisses mit = Summe (F10: F19 * G10: G19)

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

    In diesem Fall multipliziert Excel die Werte im Array (den Zellbereich F10 bis G19) und verwendet dann die Summe-Funktion, um die Summen zusammen zu addieren. Das Ergebnis ist eine Gesamtsumme von $1.590.000 im Vertrieb.

    Dieses Beispiel zeigt, wie leistungsfähig diese Formel sein kann. Nehmen wir beispielsweise an, Sie haben 1.000 Zeilen mit Daten. Sie können einen Teil oder alle Daten summieren, indem Sie eine Arrayformel in einer einzelnen Zelle erstellen, statt die Formel durch die 1.000-Zeilen nach unten zu ziehen. Beachten Sie außerdem, dass die Formel für eine Zelle in der Zelle H20 völlig unabhängig von der mehr Zellenformel (der Formel in den Zellen H10 bis H19) ist. Dies ist ein weiterer Vorteil bei der Verwendung von Arrayformeln – Flexibilität. Sie können die anderen Formeln in Spalte H ändern, ohne die Formel in H20 zu beeinflussen. Außerdem kann es sich als empfehlenswert erweisen, unabhängige Gesamtergebnisse wie diesen zu besitzen, da dadurch die Genauigkeit der Resultate überprüft wird.

  • Dynamische Matrixformeln bieten auch folgende Vorteile:

    • Konsistenz    Wenn Sie auf eine der Zellen von H10 nach unten klicken, wird die gleiche Formel angezeigt. Diese Konsistenz kann die Genauigkeit erhöhen.

    • Sicherheits    Sie können eine Komponente einer Arrayformel mit mehreren Zellen nicht überschreiben. Klicken Sie beispielsweise auf Zellen H11, und drücken Sie ENTF. Excel ändert die Ausgabe des Arrays nicht. Um Sie zu ändern, müssen Sie die obere linke Zelle im Array oder die Zelle H10 auswählen.

    • Kleinere Dateigrößen    Sie können häufig eine einzelne Matrixformel anstelle mehrerer zwischen Formeln verwenden. Beispielsweise wird im Car Sales-Beispiel eine Matrixformel verwendet, um die Ergebnisse in Spalte E zu berechnen. Wenn Sie Standardformeln wie = F10 * G10, F11 * G11, F12 * G12 usw. verwendet haben, hätten Sie elf verschiedene Formeln verwendet, um die gleichen Ergebnisse zu berechnen. Das ist nicht eine große Sache, aber was ist, wenn Sie Tausende von Zeilen insgesamt hatten? Dann kann es einen großen Unterschied machen.

    • Effizienz    Array Funktionen können eine effiziente Möglichkeit zum Erstellen komplexer Formeln sein. Die Matrixformel = Summe (F10: F19 * G10: G19) ist identisch mit diesem: = Summe (F10 * G10, F11 * G11, F12 * G12, F13 * G13, F14 * G14, F15 * G15, F16 * G16, F17 * G17, F18 * G18, F19 * G19).

    • Verschütten    Dynamische Arrayformeln werden automatisch in den Ausgabebereich verschütten. Wenn sich Ihre Quelldaten in einer Excel-Tabelle befinden, werden Ihre dynamischen Arrayformeln beim Hinzufügen oder Entfernen von Daten automatisch geändert.

    • #SPILL! Fehler    Dynamische Arrays führten den #SPILL!-Fehlerein, der angibt, dass der beabsichtigte Überlaufbereich aus irgendeinem Grund blockiert ist. Wenn Sie die Blockierung beheben, wird die Formel automatisch verschüttet.

Arraykonstanten sind eine Komponente von Arrayformeln. Sie erstellen Arraykonstanten, indem Sie eine Liste von Elementen eingeben und diese dann manuell in geschweifte Klammern ({ }) einschließen. Beispiel:

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

Wenn Sie die Elemente mithilfe von Kommas trennen, erstellen Sie ein horizontales Array (eine Zeile). Wenn Sie die Elemente mithilfe von Semikolons trennen, erstellen Sie ein vertikales Array (eine Spalte). Wenn Sie ein zweidimensionales Array erstellen möchten, begrenzen Sie die Elemente in jeder Zeile durch Kommata, und begrenzen Sie jede Zeile durch Semikolons.

Mit den folgenden Verfahren erhalten Sie einige praktische Schritte zum Erstellen horizontaler, vertikaler und zweidimensionaler Konstanten. Wir zeigen Beispiele, die die Sequence-Funktion verwenden, um Arraykonstanten sowie manuell eingegebene Arraykonstanten 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 = Sequence (1,5)ein. Die SEQUENCE-Funktion erstellt ein Array von 1 Zeile um 5 Spalten wie = {1, 2, 3, 4, 5}. Das folgende Ergebnis wird angezeigt:

    Erstellen einer horizontalen Matrixkonstante mit = SEQUENCE (1,5) oder = {1, 2, 3, 4, 5}

  • Erstellen einer vertikalen Konstante

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

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

  • Erstellen einer zweidimensionalen Konstante

    Wählen Sie eine leere Zelle mit einem Raum rechts und darunter aus, und geben Sie = Sequence (3, 4)ein. Das folgende Ergebnis wird angezeigt:

    Erstellen einer Arraykonstanten von 3 Zeilen nach vier Spalten mit = SEQUENCE (3, 4)

    Sie können auch Folgendes eingeben: oder = {1, 2, 3, 4; 5, 6, 7, 8; 9, 10, 11, 12}, aber Sie sollten beachten, wo Sie Semikolons im Vergleich zu Kommata platzieren.

    Wie Sie sehen können, bietet die Sequenz Option erhebliche Vorteile gegenüber der manuellen Eingabe Ihrer Array Konstantenwerte. Sie spart Ihnen in erster Linie Zeit, kann aber auch dazu beitragen, Fehler bei der manuellen Eingabe zu verringern. Es ist auch leichter zu lesen, zumal die Semikolons durch Trennzeichen schwer zu unterscheiden sind.

Im folgenden finden Sie ein Beispiel, in dem Arraykonstanten als Teil einer größeren Formel verwendet werden. Wechseln Sie in der Beispielarbeitsmappe zu der Konstanten in einem Formel Arbeitsblatt, 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. Da es nichts besonderes an dieser bestimmten Zahlen Auswahl gibt, haben wir nur etwas anderes als 1-5 für die Differenzierung ausgewählt.

Geben Sie in Zelle E11 die Zeichen Folge = Summe (D9: H9 * Sequenz (1,5))oder = Summe (D9: H9 * {1, 2, 3, 4, 5})ein. Die Formeln geben 85 zurück.

Verwenden Sie Arraykonstanten in Formeln. In diesem Beispiel haben wir = Summe (D9: H (* SEQUENCE (1,5)) verwendet.

Die SEQUENCE-Funktion erstellt das Äquivalent der Matrixkonstante {1, 2, 3, 4, 5}. Da Excel Operationen an Ausdrücken ausführt, die in Klammern zuerst eingeschlossen werden, sind die nächsten zwei Elemente, die in die Wiedergabe eingehen, 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 Konstanten. Das entspricht:

= 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 speichern, 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 Arraykonstanten verwenden können

  • Array Konstanten können Zahlen, Text und Wahrheitswerte (z. b. wahr und falsch) sowie Fehlerwerte wie #N/A Sie können Zahlen in ganzzahligen, dezimalen und wissenschaftlichen Formaten verwenden. Wenn Sie Text einbeziehen, müssen Sie ihn mit 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 Matrixkonstanten besteht darin, Sie zu benennen. Benannte Konstanten können sehr viel einfacher zu verwenden sein, und Sie können einige der Komplexität Ihrer Arrayformeln von anderen ausblenden. Gehen Sie wie folgt vor, um eine Matrixkonstante zu benennen und in einer Formel zu verwenden:

Wechseln Sie zu Formeln _GT_ definierte Namen > Name definieren. Geben Sie im Feld Name den Namen 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 Matrixkonstante aus Formeln > definierte Namen > Name Manager > neu

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

Das folgende Ergebnis wird angezeigt:

Verwenden Sie eine benannte Arraykonstante in einer Formel, z. b. = Zeichenfolge Quartal1, wobei Zeichenfolge Quartal1 als = {"Januar", "Februar", "März" definiert wurde}

Wenn die Ergebnisse vertikal statt horizontal verschüttet werden sollen, können Sie = Transponieren ( Zeichenfolge Quartal1)verwenden.

Wenn Sie eine Liste von 12 Monaten anzeigen möchten, wie Sie möglicherweise beim Erstellen einer Finanzaufstellung verwenden, können Sie das aktuelle Jahr mit der Funktion Sequenz abgleichen. Die nette Sache an dieser Funktion ist, dass, obwohl nur der Monat angezeigt wird, ein gültiges Datum dahinter vorhanden ist, das Sie in anderen Berechnungen verwenden können. Diese Beispiele finden Sie in den Tabellen "benannte Matrixkonstante" und " schnell Beispieldataset " in der Beispielarbeitsmappe.

= TEXT (Datum (Jahr (heute ()), Sequenz (1, 12), 1), "MMM")

Verwenden einer Kombination aus den Funktionen TEXT, Datum, Jahr, heute und Sequenz zum Erstellen einer dynamischen Liste mit 12 Monaten

Dadurch wird die Datum-Funktion verwendet, um ein Datum basierend auf dem aktuellen Jahr zu erstellen, die Sequenz erstellt eine Matrixkonstante von 1 bis 12 für Januar bis Dezember, und die Text-Funktion wandelt das Anzeigeformat in "MMM" um (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 = Zeichenfolge Quartal1, nicht nur Zeichenfolge Quartal1, einzugeben. Andernfalls interpretiert Excel das Array als eine Textzeichenfolge, und die Formel funktioniert nicht erwartungsgemäß. Denken Sie abschließend daran, dass Sie Kombinationen aus Funktionen, Text und Zahlen verwenden können. Alles hängt davon ab, wie kreativ Sie erhalten möchten.

In den folgenden Beispielen werden einige der Methoden veranschaulicht, mit denen Sie Arraykonstanten in Matrixformeln verwenden können. Einige der Beispiele verwenden die Transponieren- Funktion , um Zeilen in Spalten zu konvertieren und umgekehrt.

  • Mehrere Elemente in einem Array

    Enter = Sequence (1, 12) * 2oder = {1, 2, 3, 4; 5, 6, 7, 8; 9, 10, 11, 12} * 2

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

  • Quadrieren der Elemente in einem Array

    Enter = Sequence (1, 12) ^ 2oder = {1, 2, 3, 4; 5, 6, 7, 8; 9, 10, 11, 12} ^ 2

  • Suchen der Quadratwurzel von quadrierten Elementen in einem Array

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

  • Transponieren einer eindimensionalen Zeile

    Enter = Transponieren (Sequenz (1,5))oder = Transponieren ({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

    Geben Sie = Transponieren (Sequenz (5, 1))oder = Transponieren ({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 = Transponieren (Reihenfolge (3, 4))oder = Transponieren ({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 die Verwendung von Arrayformeln zum Erstellen eines neuen Arrays aus einem vorhandenen Array erläutert.

    Enter = Sequence (3, 6, 10, 10)oder = {10, 20, 30, 40, 50, 60; 70, 80, 90100110120; 130140150160170180}

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

    Geben Sie als nächstes = D9 #oder = D9: I11 in einer leeren Zelle ein. Ein Array von 3 x 6 Zellen wird mit denselben Werten angezeigt, die Sie in D9 sehen: D11. Das #-Zeichen wird als verschütteter Bereichsoperatorbezeichnet, und es ist Excel's Möglichkeit, auf den gesamten Arraybereich zu verweisen, anstatt es eingeben zu müssen.

    Verwenden des verschütteten Bereichsoperators (#) zum referenzieren eines vorhandenen Arrays

  • Erstellen einer Arraykonstanten aus vorhandenen Werten

    Sie können die Ergebnisse einer verschütteten Arrayformel übertragen und diese in ihre Bestandteile umwandeln. Wählen Sie Zelle D9 aus, und drücken Sie dann F2, um in den Bearbeitungsmodus zu wechseln. Drücken Sie dann F9, um die Zellbezüge in Werte umzuwandeln, die Excel dann in eine Matrixkonstante konvertiert. Wenn Sie die Eingabe Taste drücken, sollte die Formel = D9 # jetzt = {10, 20, 30; 40, 50, 60; 70, 80, 90} sein.

  • Zählen der Anzahl von Zeichen in einem Zellbereich

    Im folgenden Beispiel wird gezeigt, wie Sie die Anzahl der Zeichen in einem Zellbereich zählen. Dies schließt Leerzeichen ein.

    Zählen der Gesamtzahl der Zeichen in einem Bereich und anderer Arrays für die Arbeit mit Textzeichenfolgen

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

    In diesem Fall gibt die Len-Funktion die Länge der einzelnen Textzeichenfolgen in den einzelnen Zellen im Bereich zurück. Die Summe-Funktion addiert diese Werte und zeigt das Ergebnis (66) an. 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, ÜBEREINSTIMMUNG (MAX (LÄNGE (C9: C13)), LEN (C9: C13), 0), 1)

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

    Sehen wir uns die Formel genauer an, beginnend mit den inneren Elementen und der Arbeit nach außen. Die Funktion len gibt die Länge der einzelnen Elemente im Zellbereich D2: D6 zurück. Die Max-Funktion berechnet den größten Wert unter diesen Elementen, der der längsten Zeichenfolge in Zelle D3 entspricht.

    Hier werden die Dinge ein wenig komplexer. Die Funktion Vergleich berechnet den Offset (die relative Position) der Zelle, die die längste Textzeichenfolge enthält. Dazu sind drei Argumente erforderlich: ein Nachschlagewert, ein Nachschlagefeld und ein Übereinstimmungs. Die Funktion Vergleich durchsucht das Nachschlagefeld nach dem angegebenen Nachschlagewert. In diesem Fall ist der Nachschlagewert die längste Textzeichenfolge:

    MAX (LÄNGE (C9: C13)

    und diese Zeichenfolge befindet sich in folgendem Array:

    LEN (C9: C13)

    Das Argument Übereinstimmungs ist in diesem Fall 0. Der Übereinstimmungs kann ein Wert von 1, 0 oder-1 sein.

    • 1 – gibt den größten Wert zurück, der kleiner oder gleich dem Nachschlage-Val ist.

    • 0 – gibt den ersten Wert zurück, der genau dem Nachschlagewert entspricht.

    • -1 – gibt den kleinsten Wert zurück, der größer als oder gleich dem angegebenen Nachschlagewert ist

    • Wenn Sie einen übereinstimmenden Typ weglassen, wird von Excel 1 angenommen.

    Die Index- Funktion verwendet schließlich die folgenden Argumente: ein Array und eine Zeilen-und Spaltennummer innerhalb dieses Arrays. Der Zellbereich C9: C13 stellt das Array bereit, die ÜBEREINSTIMMUNGs Funktion liefert die Zelladresse, und das abschließende Argument (1) gibt an, dass der Wert aus der ersten Spalte des Arrays 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 veranschaulicht, wie Sie die drei kleinsten Werte in einem Zellbereich finden, wobei ein Array mit Beispieldaten in den Zellen B9: B18has mit: = int (RANDARRAY(10, 1) * 100)erstellt wurde. Beachten Sie, dass RANDARRAY eine flüchtige Funktion ist, sodass Sie bei jeder Berechnung von Excel einen neuen Satz von Zufallszahlen erhalten.

    Excel-Arrayformel zum Ermitteln des n-ten kleinsten Werts: = klein (B9 #, Sequenz (D9))

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

    Mit dieser Formel wird eine Matrixkonstante verwendet, um die kleine Funktion dreimal auszuwerten und die kleinsten drei Elemente im Array zurückzugeben, die in den Zellen B9: B18 enthalten sind, wobei 3 ein Variablenwert in Zelle D9 ist. Um weitere Werte zu finden, können Sie den Wert in der SEQUENCE-Funktion erhöhen oder der Konstanten weitere Argumente hinzufügen. Sie können auch zusätzliche Funktionen für diese Formel verwenden, z. b. Summe oder Mittelwert. 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 kleine Funktion durch die Funktion Largeersetzen. Darüber hinaus werden im folgenden Beispiel die Funktionen " Zeile " und " indirekt " verwendet.

    Geben Sie = groß ein (B9 #, Zeile (indirekt ("1:3")))oder = groß (B9: B18, Zeile (indirekt ("1:3") ))

    An diesem Punkt kann es hilfreich sein, ein wenig über die Funktion Zeile und indirekt zu erfahren. Sie können die ROW-Funktion verwenden, um ein Array mit aufeinanderfolgenden Ganzzahlen zu erstellen. Wählen Sie beispielsweise eine leere aus, und geben Sie Folgendes ein:

    = ZEILE (1:10)

    Die Formel erstellt eine Spalte mit 10 aufeinander folgenden ganzen Zahlen. Wenn Sie ein potenzielles Problem sehen möchten, fügen Sie eine Zeile oberhalb des Bereichs ein, der die Matrixformel enthält (also über Zeile 1). Excel passt die Zeilenbezüge an, und die Formel generiert jetzt ganze Zahlen von 2 bis 11. Um dieses Problem zu beheben, fügen Sie die inDIREKTe Funktion zur Formel hinzu:

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

    Die indirekt-Funktion verwendet Textzeichenfolgen als Argumente (daher ist der Bereich 1:10 von Anführungszeichen umgeben). Excel passt keine Textwerte an, wenn Sie Zeilen einfügen oder die Arrayformel auf andere Weise verschieben. Daher generiert die Funktion Zeile immer das gewünschte Array von Ganzzahlen. Sie können die Sequenz auch ganz einfach verwenden:

    = SEQUENZ (10)

    Untersuchen wir die Formel, die Sie zuvor verwendet haben – = groß (B9 #, Zeile (indirekt ("1:3"))) – beginnend bei den inneren Klammern und nach außen: die Funktion indirekt gibt einen Satz von Textwerten zurück, in diesem Fall die Werte 1 bis 3. Die ROW-Funktion generiert wiederum ein Spaltenarray aus drei Zellen. Die LARGE-Funktion verwendet die Werte im Zellbereich B9: B18 und wird dreimal einmal für jeden von der Zeile-Funktion zurückgegebenen Verweis ausgewertet. Wenn Sie nach weiteren Werten suchen möchten, fügen Sie der Funktion indirekt einen größeren Zellbereich hinzu. Wie bei den kleinen Beispielen können Sie diese Formel auch für andere Funktionen verwenden, z. b. "Summe" und "Mittelwert".

  • Addieren eines Bereichs, der Fehlerwerte enthält

    Die Funktion Summe in Excel funktioniert nicht, wenn Sie versuchen, einen Bereich zu addieren, der einen Fehlerwert enthält, z. b. #VALUE! oder #N/A In diesem Beispiel wird veranschaulicht, wie Sie die Werte in einem Bereich mit dem Namen "Daten" addieren, die Fehler enthalten:

    Verwenden von Arrays zum Behandeln von Fehlern So summiert z. b. = Summe (wenn (isERROR (Daten); ""; Daten) den Bereich mit dem Namen "Daten", auch wenn er Fehler enthält, wie #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 ähnelt der vorherigen Formel, gibt aber die Anzahl der Fehlerwerte in einem Bereich mit dem Namen "Daten" zurück, anstatt Sie 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 der Bedingungen addieren.

Sie können Arrays verwenden, um basierend auf bestimmten Bedingungen zu berechnen. = Summe (wenn (Sales>0)) summiert alle Werte, die größer als 0 sind, in einem Bereich mit der Bezeichnung Sales.

Beispielsweise summiert diese Arrayformel nur die positiven Ganzzahlen in einem Bereich mit dem Namen "Sales", der die Zellen e9: E24 im obigen Beispiel darstellt:

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

Die wenn-Funktion erstellt ein Array von positiven und falschen Werten. Die Funktion Summe ignoriert im Wesentlichen die falschen Werte, da 0 + 0 = 0 ist. 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 beispielsweise Werte, die größer als 0 und kleiner als 2500 sind:

= Summe ((Sales>0) * (Sales<2500) * (Umsätze))

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 einen Typ oder eine Bedingung verwenden. So können Sie beispielsweise Werte addieren, die größer als 0 oder kleiner als 2500 sind:

= Summe (wenn ((Sales>0) + (Sales<2500); Umsatz))

Sie können die Funktionen und und oder in Arrayformeln nicht direkt verwenden, da diese Funktionen ein einzelnes Ergebnis, entweder wahr oder falsch, zurückgeben und Array Funktionen Arrays von Ergebnissen erfordern. Sie können das Problem umgehen, indem Sie die in der vorherigen Formel gezeigte Logik verwenden. Mit anderen Worten: Sie führen mathematische Operationen aus, z. b. Addition oder Multiplikation für Werte, die die OR-oder-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 dem Namen "myData" und "FremdeDaten" und gibt die Anzahl der Unterschiede zwischen den beiden Werten zurück. Wenn die Inhalte der beiden Bereiche identisch sind, gibt die Formel 0 zurück. Um diese Formel zu verwenden, müssen die Zellbereiche die gleiche Größe und die gleiche Dimension aufweisen. Wenn beispielsweise MyData ein Bereich von 3 Zeilen mit fünf Spalten ist, muss FremdeDaten auch 3 Zeilen durch 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<>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 .

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 Tabelle, und fügen Sie Sie in Zelle a1 auf einem leeren Arbeitsblatt ein.

Verkäufe Personen

Car Geben Sie

Zahl Verkauft

Einheit Preis

Gesamtsumme Verkäufe

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. Wenn Sie die Gesamtumsätze von Coupés und Limousinen für jeden Verkäufer anzeigen möchten, wählen Sie Zellen E2: e11 aus, geben Sie die Formel = C2: C11 * D2: D11ein, und drücken Sie dann STRG + UMSCHALT + Eingabe Taste.

  2. Wenn Sie die Gesamtsumme aller Umsätze anzeigen möchten, wählen Sie Zelle F11 aus, geben Sie die Formel = Summe (C2: C11 * D2: D11)ein, und drücken Sie dann STRG + UMSCHALT + Eingabe Taste.

Wenn Sie STRG + UMSCHALT + Eingabe Taste drücken, umschließt Excel die Formel mit geschweiften Klammern ({}) und fügt eine Instanz der Formel in jede Zelle des ausgewählten Bereichs ein. Dies geschieht sehr schnell, sodass in Spalte E der Gesamtumsatz für jeden Fahrzeugtyp für jeden Verkäufer angezeigt wird. Wenn Sie E2 auswählen und dann E3, E4 usw. auswählen, wird die gleiche Formel angezeigt: {= C2: C11 * D2: D11}.

Die Summen in Spalte E werden mithilfe einer Arrayformel berechnet

  • Erstellen einer Einzelzellen-Matrixformel

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

=SUMME(C2:C11*D2:D11)

In diesem Fall multipliziert Excel die Werte im Array (den Zellbereich C2 bis D11) und verwendet dann die Summe-Funktion, um die Summen zusammen zu addieren. Das Ergebnis ist eine Gesamtsumme von $1.590.000 im Vertrieb. Dieses Beispiel zeigt, wie leistungsfähig diese Formel sein kann. Nehmen wir beispielsweise an, Sie haben 1.000 Zeilen mit Daten. Sie können einen Teil oder alle Daten summieren, indem Sie eine Arrayformel in einer einzelnen Zelle erstellen, statt die Formel durch die 1.000-Zeilen nach unten zu ziehen.

Beachten Sie außerdem, dass die Formel für eine einzelne Zelle in der Zelle D13 vollständig von der mehr Zellenformel (der Formel in den Zellen E2 bis E11) unabhängig ist. Dies ist ein weiterer Vorteil bei der Verwendung von Arrayformeln – Flexibilität. Sie können die Formeln in Spalte E ändern oder die Spalte ganz löschen, ohne dass sich dies auf die Formel in D13 auswirkt.

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.

  • Sicherheits    Sie können eine Komponente einer Arrayformel mit mehreren Zellen nicht überschreiben. Klicken Sie beispielsweise auf Zelle E3, und drücken Sie ENTF. Sie müssen entweder den gesamten Zellbereich (E2 bis E11) auswählen und die Formel für das gesamte Array ändern oder das Array unverändert beibehalten. Als zusätzliche Sicherheitsmaßnahme müssen Sie STRG + UMSCHALT + Eingabe Taste drücken, um eine Änderung an der Formel zu bestätigen.

  • Geringere Dateigrößen    Sie können häufig eine einzelne Arrayformel anstelle mehrerer Formeln für Zwischenschritte verwenden. So wird beispielsweise in der Arbeitsmappe eine einzige Arrayformel zum Berechnen der Ergebnisse in Spalte E verwendet. Bei Verwendung von Standardformeln (z. B. =C2*D2;C3*D3;C4*D4…) hätten Sie 11 verschiedene Formeln zur Berechnung der gleichen Ergebnisse verwendet.

Im Allgemeinen verwenden Arrayformeln die Standardformel Syntax. Alle beginnen mit einem Gleichheitszeichen (=), und Sie können die meisten der integrierten Excel-Funktionen in ihren Arrayformeln verwenden. Der entscheidende Unterschied besteht darin, dass Sie bei Verwendung einer Arrayformel STRG + UMSCHALT + Eingabe Taste drücken, um die Formel einzugeben. Wenn Sie dies tun, umgibt Excel die Arrayformel mit geschweiften Klammern – Wenn Sie die geschweiften Klammern manuell eingeben, wird die Formel in eine Textzeichenfolge konvertiert, und Sie funktioniert nicht.

Array Funktionen können eine effiziente Möglichkeit zum Erstellen komplexer Formeln sein. Die Matrixformel = Summe (C2: C11 * D2: D11) ist identisch mit folgendem: = Sum (C2 * D2, C3 * D3, C4 * D4, C5 * D5, C6 * D6, C7 * D7, C8 * D8, C9 * D9, C10 * D10, C11 * D11).

Wichtig: Drücken Sie bei Bedarf STRG + UMSCHALT + Eingabe Taste, um eine Matrixformel einzugeben. Dies gilt für Formeln mit einem oder mehreren Zellen.

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.

  • Um eine Matrixformel zu löschen, markieren Sie den gesamten Formelbereich (z. b. E2: e11), und drücken Sie dann ENTF.

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

Manchmal müssen Sie möglicherweise eine Arrayformel erweitern. Wählen Sie die erste Zelle im vorhandenen Arraybereich aus, und fahren Sie fort, bis Sie den gesamten Bereich ausgewählt 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 + Eingabe Taste, um die Formel zu bestätigen, nachdem Sie den Formelbereich angepasst haben. Der Schlüssel ist die Auswahl des gesamten Bereichs, beginnend mit der oberen linken Zelle im Array. Die obere linke Zelle ist diejenige, die bearbeitet wird.

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

  • Sie können gelegentlich vergessen, STRG + UMSCHALT + Eingabe Taste zu drücken. Es kann auch für erfahrene Excel-Benutzer vorkommen. Denken Sie daran, diese Tastenkombination immer dann zu drücken, wenn Sie eine Arrayformel eingeben oder bearbeiten.

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

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

Arraykonstanten sind eine Komponente von Arrayformeln. Sie erstellen Arraykonstanten, indem Sie eine Liste von Elementen eingeben und diese dann manuell in geschweifte Klammern ({ }) einschließen. Beispiel:

={1.2.3.4.5}

Jetzt wissen Sie, dass Sie beim Erstellen von Matrixformeln STRG + UMSCHALT + Eingabe Taste drücken müssen. Da Arraykonstanten eine Komponente von Matrixformeln sind, umgeben Sie die Konstanten mit geschweiften Klammern, indem Sie Sie manuell eingeben. Sie verwenden dann STRG + UMSCHALT + Eingabe Taste, um die gesamte Formel einzugeben.

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}. Dies ist ein Array in einer einzelnen Spalte: {1; 2; 3; 4}. Und hier ist ein Array von zwei Zeilen und vier Spalten: {1, 2, 3, 4; 5, 6, 7, 8}. Im Array zwei Zeilen ist die erste Zeile 1, 2, 3 und 4 und die zweite Zeile 5, 6, 7 und 8. Ein einzelnes Semikolon trennt die beiden Zeilen zwischen 4 und 5.

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. Wählen Sie auf einem leeren Arbeitsblatt die Zellen a1 bis E1 aus.

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

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

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

    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 Bearbeitungsleiste die folgende Formel ein, und drücken Sie dann STRG + UMSCHALT + Eingabe Taste:

    ={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 Bearbeitungsleiste die folgende Formel ein, und drücken Sie dann STRG + UMSCHALT + Eingabe Taste:

    ={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 + Eingabe Taste:

    =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

Das letzte Element innerhalb der Klammern ist die Arraykonstante: {1, 2, 3, 4, 5}. Denken Sie daran, dass Excel keine Arraykonstanten mit geschweiften Klammern einschließt. Sie geben Sie tatsächlich ein. Denken Sie auch daran, dass Sie nach dem Hinzufügen einer Konstante zu einer Matrixformel STRG + UMSCHALT + Eingabe Taste drücken, um die Formel einzugeben.

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 zu versuchen, kopieren Sie die Funktion, wählen Sie eine leere Zelle in der Arbeitsmappe aus, fügen Sie die Formel in die Bearbeitungsleiste ein, und drücken Sie dann STRG + UMSCHALT + Eingabe Taste. Sie sehen dasselbe Ergebnis wie in der vorherigen Übung, die die Matrixformel verwendet hat:

=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 Methoden zum Verwenden von Matrixkonstanten besteht darin, Sie zu benennen. Benannte Konstanten können sehr viel einfacher zu verwenden sein, und Sie können einige der Komplexität Ihrer Arrayformeln von anderen ausblenden. Gehen Sie wie folgt vor, um eine Matrixkonstante zu benennen und in einer Formel zu verwenden:

  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 + Eingabe Taste.

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

  • Einige Elemente sind möglicherweise nicht mit dem richtigen Zeichen getrennt. Wenn Sie ein Komma oder Semikolon weglassen, oder wenn Sie einen 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 werden einige der Methoden veranschaulicht, mit denen Sie Arraykonstanten in Matrixformeln verwenden können. Einige der Beispiele verwenden die Transponieren- Funktion , um Zeilen in Spalten zu konvertieren und umgekehrt.

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 + Eingabe Taste:

    = {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 Arrayformel ein, und drücken Sie dann STRG + UMSCHALT + Eingabe Taste:

    ={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 + Eingabe Taste:

    =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 + Eingabe Taste:

    =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 + Eingabe Taste:

    =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 + Eingabe Taste, die dieses Array von Zahlen im Zellbereich C8: E10 mit einer Matrixformel eingibt. Auf Ihrem Arbeitsblatt sollte C8 bis E10 wie folgt aussehen:

    10

    20

    30

    40

    50

    60

    70

    80

    90

  3. Markieren Sie den Zellbereich C1 bis E3.

  4. Geben Sie in der Bearbeitungsleiste die folgende Formel ein, und drücken Sie dann STRG + UMSCHALT + Eingabe Taste:

    =C8:E10

    Eine 3X3-Matrix von Zellen wird in den Zellen C1 bis E3 mit denselben Werten angezeigt, die Sie in C8 bis E10 sehen.

Erstellen einer Arraykonstanten aus vorhandenen Werten

  1. Drücken Sie F2, um in den Bearbeitungsmodus zu wechseln, wenn die Zellen C1: C3 ausgewählt sind.

  2. Drücken Sie F9, um die Zellbezüge in Werte umzuwandeln. Excel wandelt die Werte in eine Matrixkonstante um. Die Formel sollte nun = {10, 20, 30; 40, 50, 60; 70, 80, 90}sein.

  3. Drücken Sie STRG + UMSCHALT + Eingabe Taste, um die Arraykonstante als Matrixformel einzugeben.

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. Wählen Sie Zelle A8 aus, und drücken Sie dann STRG + UMSCHALT + Eingabe Taste, um die Gesamtzahl der Zeichen in den Zellen A2: A6 (66) anzuzeigen.

  3. Wählen Sie Zelle A10 aus, und drücken Sie dann STRG + UMSCHALT + Eingabe Taste, um den Inhalt des längsten der Zellen A2: A6 (Zelle A3) anzuzeigen.

Die folgende Formel wird in Zelle A8 verwendet. zählt die Gesamtzahl der Zeichen (66) in den Zellen A2 bis A6.

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

In diesem Fall gibt die len -Funktion die Länge der einzelnen Textzeichenfolgen in den einzelnen Zellen im Bereich zurück. Die Summe -Funktion addiert diese Werte und zeigt das ergebnis (66) an.

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 in den Zellen a1: A11 einige zufällige Zahlen ein.

  2. Wählen Sie die Zellen C1 bis C3 aus. Diese Gruppe von Zellen speichert die von der Arrayformel zurückgegebenen Ergebnisse.

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

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

Mit dieser Formel wird eine Matrixkonstante verwendet, um die kleine Funktion dreimal auszuwerten und die kleinste (1), die zweitkleinste (2) und die dritte kleinste (3) im Array zurückzugeben, die in den Zellen a1: A10 enthalten sind, um weitere Werte zu finden, fügen Sie weitere Argumente der Konstante. Sie können auch zusätzliche Funktionen für diese Formel verwenden, z. b. Summe oder Mittelwert. 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. Wählen Sie die Zellen D1 bis D3 aus.

  2. Geben Sie in der Bearbeitungsleiste diese Formel ein, und drücken Sie dann STRG + UMSCHALT + Eingabe Taste:

    =KGRÖSSTE(A1:A10;ZEILE(INDIREKT("1:3")))

An diesem Punkt kann es hilfreich sein, ein wenig über die Funktion Zeile und indirekt zu erfahren. Sie können die Row -Funktion verwenden, um ein Array mit aufeinanderfolgenden Ganzzahlen zu erstellen. Wählen Sie z. b. eine leere Spalte mit 10 Zellen in der Praxis Arbeitsmappe aus, geben Sie diese Arrayformel ein, und drücken Sie dann STRG + UMSCHALT + Eingabe Taste:

=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 – = groß (A5: A14, Zeile (indirekt ("1:3"))) – beginnend bei 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 Row -Funktion generiert wiederum ein drei Zellen-Säulen Array. Die Large -Funktion verwendet die Werte im Zellbereich A5: A14 und wird dreimal einmal für jeden von der Zeile -Funktion zurückgegebenen Verweis ausgewertet. Die Werte 3200, 2700 und 2000 werden an das drei Zellen-Säulen Array 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 für andere Funktionen verwenden, z. b. " Summe " und " Mittelwert".

Suchen der längsten Textzeichenfolge in einem Zellbereich

Kehren Sie zum vorherigen Beispiel für die Textzeichenfolge zurück, geben Sie die folgende Formel in eine leere Zelle ein, und drücken Sie STRG + UMSCHALT + Eingabe Taste:

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

Der Text "Zellgruppe" wird angezeigt.

Sehen wir uns die Formel genauer an, beginnend mit den inneren Elementen und der Arbeit nach außen. Die Funktion len gibt die Länge der einzelnen Elemente im Zellbereich a2: A6 zurück. Die Max -Funktion berechnet den größten Wert unter diesen Elementen, der der längsten Zeichenfolge in Zelle a3 entspricht.

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 (A2: A6))

und diese Zeichenfolge befindet sich in folgendem Array:

LÄNGE (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".

Die Index -Funktion verwendet schließlich die folgenden Argumente: ein Array und eine Zeilen-und Spaltennummer innerhalb dieses Arrays. Der Zellbereich a2: A6 stellt das Array bereit, die Funktion Vergleich stellt die Zelladresse bereit, und das abschließende Argument (1) gibt an, dass der Wert aus der ersten Spalte des Arrays 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<>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.

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 beruhen auf einer Reihe von Excel Power User-Spalten, die von Colin Wilcox geschrieben wurden, und wurden aus den Kapiteln 14 und 15 von Excel 2002-Formeln, einem Buch von John Walkenbach, einem ehemaligen Excel-MVP, angepasst.

Benötigen Sie weitere Hilfe?

Sie können jederzeit einen Experten in der Excel Tech Community fragen, Unterstützung in der Answers Community erhalten oder aber ein neues Feature oder eine Verbesserung auf Excel User Voice vorschlagen.

Siehe auch

Dynamische Arrays und Verhalten von übergelaufenem Array

Dynamische Matrixformeln im Vergleich zu Legacy-CSE-Arrayformeln

FILTER-Funktion

ZUFALLSMATRIX-Funktion

SEQUENZ-Funktion

EINZELW-Funktion

SORTIEREN-Funktion

SORTIERENNACH-Funktion

EINDEUTIG-Funktion

#ÜBERLAUF!-Fehler in Excel

Übersicht über Formeln

Ihre Office-Fähigkeiten erweitern
Schulung erkunden
Neue Funktionen als Erster erhalten
An Office Insider teilnehmen

War diese Information hilfreich?

Vielen Dank für Ihr Feedback!

Vielen Dank für Ihr Feedback. Es klingt, als ob es hilfreich sein könnte, Sie mit einem unserer Office-Supportmitarbeiter zu verbinden.

×