Ein Excel Array Formel ist eine Formel, die Berechnungen der Werte in einem oder mehreren Arrays anstelle eines einzelnen Datenwerts ausführt. In Tabellenkalkulationsprogrammen ist ein Array ein Bereich oder eine Reihe von zugehörigen Datenwerten, die normalerweise in benachbarten Zellen eines Arbeitsblatts gespeichert werden. In diesem Handbuch wird erläutert, wie Sie Arrayformeln in allen modernen Versionen von Excel verwenden.
Dieses Lernprogramm ist mit den meisten Excel-Versionen einschließlich 2007, 2010, 2013, 2016 und Office 365 kompatibel.
Was sind Arrayformeln?
Arrayformeln ähneln regulären Formeln:
- Sie beginnen mit einem Gleichheitszeichen ( = )
- Verwenden Sie dieselbe Syntax wie reguläre Formeln
- Verwenden Sie dieselben mathematischen Operatoren
- Folgen Sie der gleichen Reihenfolge
In Excel sind Arrayformeln von geschweiften Klammern umgeben { } - Diese Klammern können nicht einfach eingegeben werden. Sie müssen durch Drücken der Taste zu einer Formel hinzugefügt werden Ctrl , Verschiebung , und Eingeben Tasten nach dem Eingeben der Formel in eine Zelle oder Zellen. Aus diesem Grund wird eine Matrixformel manchmal als a bezeichnet CSE Formel in Excel.
Eine Ausnahme von dieser Regel ist, wenn geschweifte Klammern verwendet werden, um ein Array als Argument für eine Funktion einzugeben, die normalerweise nur einen einzelnen Wert oder eine Zellenreferenz enthält. Zum Beispiel in dem unten stehenden Tutorial VLOOKUP und das WÄHLEN Funktion zum Erstellen einer linken Nachschlagformel wird ein Array für die erstellt WÄHLEN Funktion Index_Num Argument durch Eingabe der geschweiften Klammern um das eingegebene Array.
Schritte zum Erstellen einer Array-Formel
- Geben Sie die Formel ein.
- Halten Sie die Ctrl und Verschiebung Tasten auf der Tastatur.
- Drücken Sie kurz die Eingeben Schlüssel zum Erstellen der Array-Formel.
- Befreit dasCtrl und Verschiebung Schlüssel.
Bei korrekter Ausführung wird die Formel von geschweiften Klammern umgeben und jede Zelle, die die Formel enthält, enthält ein anderes Ergebnis.
Bearbeiten einer Array-Formel
Bei jeder Bearbeitung einer Array-Formel verschwinden die geschweiften Klammern um die Array-Formel. Um sie zurückzubekommen, muss die Matrixformel durch Drücken der Taste eingegeben werden Ctrl, Verschiebung, und Eingeben Schlüssel erneut, als wenn die Array-Formel zum ersten Mal erstellt wurde.
Arten von Array-Formeln
Es gibt zwei Haupttypen von Arrayformeln:
- Einzelzellenfeldformeln, die mehrere Berechnungen in einer einzelnen Arbeitsblattzelle ausführen.
- Matrixformeln mit mehreren Zellen, die sich in mehr als einer Arbeitsblattzelle befinden.
Einzelzellen-Array-Formeln
Diese Art von Matrixformel verwendet eine Funktion wie z SUMME, DURCHSCHNITTLICH, oder ANZAHL, um die Ausgabe einer Matrixformel mit mehreren Zellen zu einem einzelnen Wert in einer einzelnen Zelle zu kombinieren. Ein Beispiel für eine einzelne Zellenfeldformel wäre:
{= SUM (A1: A2 * B1: B2)}
Diese Formel addiert das Produkt von A1 * B1 und A2 * B2und gibt dann ein einzelnes Ergebnis in einer einzelnen Zelle im Arbeitsblatt zurück. Eine andere Schreibweise für die obige Formel wäre:
Mehrzellen-Array-Formeln
Wie der Name vermuten lässt, befinden sich diese Arrayformeln in mehreren Arbeitsblattzellen und geben ein Array als Antwort zurück. Die gleiche Formel befindet sich also in zwei oder mehr Zellen und gibt in jeder Zelle unterschiedliche Antworten zurück.
Jede Kopie oder Beispiel, der Array-Formel führt in jeder Zelle, in der sie sich befindet, die gleiche Berechnung aus, aber jede Instanz der Formel verwendet bei ihren Berechnungen andere Daten. Daher führt jede Instanz zu unterschiedlichen Ergebnissen. Ein Beispiel für eine Matrixformel mit mehreren Zellen würde folgendermaßen aussehen:
{= A1: A2 * B1: B2}
Wenn sich das obige Beispiel in befindet Zellen C1 und C2 In einem Arbeitsblatt wären dann die Ergebnisse wie folgt:
- Die Daten in A1 wird mit den Daten in multipliziert B1und die Ergebnisse werden in gespeichert Zelle C1.
- Die Daten in A2 wird mit den Daten in multipliziert B2und die Ergebnisse werden in gespeichert Zelle C2.
Verwenden von Arrays zum Transponieren von Zeilen und Spalten
Das TRANSPONIEREN Funktion wird verwendet, um Daten aus einer Zeile in eine Spalte zu kopieren oder umgekehrt. Diese Funktion ist eine der wenigen Funktionen in Excel, die immer als Matrixformel verwendet werden muss.
Arrayformeln und Excel-Funktionen
Viele der in Excel integrierten Funktionen, wie z SUMME, DURCHSCHNITTLICH, und ANZAHL, kann auch in einer Matrixformel verwendet werden. Es gibt auch einige Funktionen wie die TRANSPONIEREN Funktion, die immer als Array eingegeben werden muss, damit sie ordnungsgemäß funktioniert. Die Nützlichkeit vieler Funktionen wie INDEX und SPIEL oder MAX und OB kann erweitert werden, indem sie zusammen in einer Arrayformel verwendet werden.
Beispiele für Arrayformeln in Excel:
01 von 08Erstellen Sie eine einfache Einzelzellenarrayformel
Einzelzellenfeldformeln führen normalerweise zuerst eine Mehrzellenberechnung aus und verwenden dann eine Funktion wie DURCHSCHNITTLICH oder SUMME um die Ausgabe des Arrays zu einem einzigen Ergebnis zu kombinieren.
02 von 08Fehlerwerte beim Suchen von Daten ignorieren
Diese Matrixformel verwendet die DURCHSCHNITTLICH, OB, und IST NUMMER Funktionen, um den Durchschnittswert für die vorhandenen Daten zu ermitteln, während Fehlerwerte wie z # DIV / 0! und #NAME?
03 von 08Zählen Sie Datenzellen
Verwenden Sie die SUMME undOB Funktionen in einer Matrixformel zum Zählen von Datenzellen, die eine von mehreren Bedingungen erfüllen; Diese Technik unterscheidet sich von der Verwendung von Excel COUNTIFS Funktion, die erfordert, dass alle eingestellten Bedingungen erfüllt sind, bevor die Zelle gezählt wird.
04 von 08Finden Sie die größte positive oder negative Zahl
Dieses Beispiel kombiniert die MAX Funktion undOB Funktion in einer Arrayformel, die den größten oder maximalen Wert für einen Datenbereich ermittelt, wenn bestimmte Kriterien erfüllt sind.
05 von 08Finden Sie die kleinste positive oder negative Zahl
Ähnlich wie in dem obigen Beispiel wird hier die Kombination aus MINDEST Funktion und WENN Funktion in einer Arrayformel, um den kleinsten oder minimalen Wert für einen Datenbereich zu finden, wenn bestimmte Kriterien erfüllt sind.
06 von 08Finden Sie den mittleren oder mittleren Wert
Das MEDIAN Funktion in Excel findet den mittleren Wert für eine Liste von Daten. Durch die Kombination mit dem OB Funktion in einer Array-Formel kann der mittlere Wert für verschiedene Gruppen verwandter Daten gefunden werden.
07 von 08Machen Sie eine Suchformel mit mehreren Kriterien
Diese Matrixformel beinhaltet das Verschachteln von SPIEL und INDEX Funktionen zum Auffinden bestimmter Informationen in einer Datenbank.
08 von 08Machen Sie eine Left Lookup-Formel
Das VLOOKUP Diese Funktion sucht normalerweise nur nach Daten, die sich in Spalten auf der rechten Seite befinden WÄHLEN Mit dieser Funktion können Sie eine linke Nachschlagformel erstellen, mit der die Datenspalten links von der durchsucht werden Lookup-Wert Streit.
