So verwenden Sie Array-Formeln in Google Sheets

Anfang 2023 führte Google mehrere neue Funktionen für Sheets ein, darunter acht für die Arbeit mit Arrays. Mit diesen Funktionen können Sie ein Array in eine Zeile oder Spalte umwandeln, ein neues Array aus einer Zeile oder Spalte erstellen oder ein aktuelles Array anhängen.

Da wir mehr Flexibilität bei der Arbeit mit Arrays haben und über die grundlegende Funktion ARRAYFORMULA hinausgehen, schauen wir uns an, wie Sie diese Array-Funktionen mit Formeln in Google Sheets verwenden .

Inhaltsverzeichnis

Tipp : Einige dieser Funktionen kommen Ihnen möglicherweise bekannt vor, wenn Sie auch Microsoft Excel verwenden.

Transformieren Sie ein Array: TOROW und TOCOL

Wenn Ihr Datensatz ein Array enthält, das Sie in eine einzelne Zeile oder Spalte umwandeln möchten, können Sie die Funktionen TOROW und TOCOL verwenden.

Die Syntax für jede Funktion ist dieselbe: TOROW(array, ignorieren, scannen) und TOCOL(array, ignorieren, scannen) , wobei für beide nur das erste Argument erforderlich ist.

• Array : Das Array, das Sie transformieren möchten, formatiert als „A1:D4“.
• Ignorieren : Standardmäßig werden keine Parameter ignoriert (0), aber Sie können 1 zum Ignorieren von Leerzeichen, 2 zum Ignorieren von Fehlern oder 3 zum Ignorieren von Leerzeichen und Fehlern verwenden.
• Scan : Dieses Argument bestimmt, wie die Werte im Array gelesen werden. Standardmäßig scannt die Funktion zeilenweise oder mit dem Wert „False“. Wenn Sie möchten, können Sie jedoch auch „True“ verwenden, um spaltenweise zu scannen.

Lassen Sie uns einige Beispiele mit den Funktionen TOROW und TOCOL und ihren Formeln durchgehen.

In diesem ersten Beispiel nehmen wir unser Array A1 bis C3 und wandeln es mithilfe der Standardargumente mit dieser Formel in eine Zeile um:

=TOROW(A1:C3)

Wie Sie sehen können, befindet sich das Array jetzt in einer Zeile. Da wir das Standard- Scan- Argument verwendet haben, liest die Funktion von links nach rechts (A, D, G), nach unten und dann wieder von links nach rechts (B, E, H), bis der Vorgang abgeschlossen ist – zeilenweise gescannt.

Um das Array spaltenweise statt zeilenweise zu lesen, können wir True als Scan- Argument verwenden. Wir lassen das Argument „Ignorieren“ leer. Hier ist die Formel:

=TOROW(A1:C3,,TRUE)

Jetzt sehen Sie, dass die Funktion das Array von oben nach unten (A, B, C), von oben nach unten (D, E, F) und von oben nach unten (G, H, I) liest.

Die TOCOL-Funktion funktioniert auf die gleiche Weise, wandelt das Array jedoch in eine Spalte um. Unter Verwendung desselben Bereichs, A1 bis C3, ist hier die Formel mit den Standardargumenten:

=TOCOL(A1:C3)

Unter Verwendung der Standardeinstellung für das Scan- Argument liest die Funktion erneut von links nach rechts und stellt das Ergebnis als solches bereit.

Um das Array spaltenweise statt zeilenweise zu lesen, fügen Sie „ True“ für das Scan- Argument wie folgt ein:

=TOCOL(A1:C3,,TRUE)

Jetzt sehen Sie, dass die Funktion das Array stattdessen von oben nach unten liest.

Erstellen Sie ein neues Array aus Zeilen oder Spalten: CHOOSEROWS und CHOOSECOLS

Möglicherweise möchten Sie ein neues Array aus einem vorhandenen erstellen. Auf diese Weise können Sie einen neuen Zellbereich erstellen, der nur bestimmte Werte aus einem anderen enthält. Dazu verwenden Sie die Google Sheets-Funktionen CHOOSEROWS und CHOOSECOLS .

Die Syntax für jede Funktion ist ähnlich: CHOOSEROWS (array, row_num, row_num_opt) und CHOOSECOLS (array, col_num, col_num_opt), wobei die ersten beiden Argumente für beide erforderlich sind.

• Array : Das vorhandene Array, formatiert als „A1:D4“.
• Row_num oder Col_num : Die Nummer der ersten Zeile oder Spalte, die Sie zurückgeben möchten.
• Row_num_opt oder Col_num_opt : Die Zahlen für zusätzliche Zeilen oder Spalten, die Sie zurückgeben möchten. Google empfiehlt, negative Zahlen zu verwenden , um Zeilen von unten nach oben oder Spalten von rechts nach links anzuzeigen.

Schauen wir uns einige Beispiele für die Verwendung von CHOOSEROWS und CHOOSECOLS und ihren Formeln an.

In diesem ersten Beispiel verwenden wir das Array A1 bis B6. Wir möchten die Werte in den Zeilen 1, 2 und 6 zurückgeben. Hier ist die Formel:

=CHOOSEROWS(A1:B6,1,2,6)

Wie Sie sehen können, haben wir diese drei Zeilen erhalten, um unser neues Array zu erstellen.

Für ein weiteres Beispiel verwenden wir dasselbe Array. Dieses Mal möchten wir die Zeilen 1, 2 und 6 zurückgeben, jedoch mit 2 und 6 in umgekehrter Reihenfolge. Sie können positive oder negative Zahlen verwenden, um das gleiche Ergebnis zu erhalten.

Wenn Sie negative Zahlen verwenden, verwenden Sie diese Formel:

=CHOOSEROWS(A1:B6,1,-1,-5)

Zur Erläuterung: 1 ist die erste zurückzugebende Zeile, -1 ist die zweite zurückzugebende Zeile, also die erste Zeile, die unten beginnt, und -5 ist die fünfte Zeile von unten.

Wenn Sie positive Zahlen verwenden, würden Sie diese Formel verwenden, um das gleiche Ergebnis zu erhalten:

=CHOOSEROWS(A1:B6,1,6,2)

Die Funktion CHOOSECOLS funktioniert ähnlich, außer dass Sie sie verwenden, wenn Sie ein neues Array aus Spalten statt aus Zeilen erstellen möchten.

Mithilfe des Arrays A1 bis D6 können wir die Spalten 1 (Spalte A) und 4 (Spalte D) mit dieser Formel zurückgeben:

=CHOOSECOLS(A1:D6,1,4)

Jetzt haben wir unser neues Array mit nur diesen beiden Spalten.

Als weiteres Beispiel verwenden wir dasselbe Array beginnend mit Spalte 4. Anschließend fügen wir die Spalten 1 und 2 mit 2 (Spalte B) zuerst hinzu. Sie können entweder positive oder negative Zahlen verwenden:

=CHOOSECOLS(A1:D6,4,2,1)

=CHOOSECOLS(A1:D6,4,-3,-4)

Wie Sie im obigen Screenshot sehen können, erhalten wir mit den Formeln in den Zellen und nicht in der Formelleiste mit beiden Optionen das gleiche Ergebnis.

Hinweis : Da Google die Verwendung negativer Zahlen empfiehlt, um die Platzierung der Ergebnisse umzukehren, sollten Sie dies im Hinterkopf behalten, wenn Sie mit positiven Zahlen nicht die richtigen Ergebnisse erhalten.

Wrap, um ein neues Array zu erstellen: WRAPROWS und WRAPCOLS

Wenn Sie ein neues Array aus einem vorhandenen erstellen möchten, die Spalten oder Zeilen jedoch jeweils mit einer bestimmten Anzahl von Werten umschließen möchten, können Sie die Funktionen WRAPROWS und WRAPCOLS verwenden.

Die Syntax für jede Funktion ist dieselbe: WRAPROWS (Range, Count, Pad) und WRAPCOLS (Range, Count, Pad), wobei die ersten beiden Argumente für beide erforderlich sind.

• Bereich : Der vorhandene Zellbereich, den Sie für ein Array verwenden möchten, formatiert als „A1:D4“.
• Anzahl : Die Anzahl der Zellen für jede Zeile oder Spalte.
• Pad : Mit diesem Argument können Sie Text oder einen einzelnen Wert in leere Zellen einfügen. Dies ersetzt den #N/A-Fehler, den Sie für die leeren Zellen erhalten. Fügen Sie den Text oder Wert in Anführungszeichen ein.

Lassen Sie uns einige Beispiele mit den Funktionen WRAPROWS und WRAPCOLS und ihren Formeln durchgehen.

In diesem ersten Beispiel verwenden wir den Zellbereich A1 bis E1. Wir erstellen ein neues Array, das Zeilen mit drei Werten in jeder Zeile umschließt. Hier ist die Formel:

=WRAPROWS(A1:E1,3)

Wie Sie sehen, haben wir ein neues Array mit dem richtigen Ergebnis, drei Werte in jeder Zeile. Da wir eine leere Zelle im Array haben, wird der Fehler #N/A angezeigt. Im nächsten Beispiel verwenden wir das Pad- Argument, um den Fehler durch den Text „None“ zu ersetzen. Hier ist die Formel:

=WRAPROWS(A1:E1,3,“Keine“)

Jetzt sehen wir ein Wort anstelle eines Google Sheets-Fehlers.

Die WRAPCOLS-Funktion macht dasselbe, indem sie ein neues Array aus einem vorhandenen Zellbereich erstellt, dabei jedoch Spalten statt Zeilen umbricht.

Hier verwenden wir dasselbe Array, A1 bis E3, und umschließen Spalten mit drei Werten in jeder Spalte:

=WRAPCOLS(A1:E1,3)

Wie im WRAPROWS-Beispiel erhalten wir das korrekte Ergebnis, aber aufgrund der leeren Zelle auch eine Fehlermeldung. Mit dieser Formel können Sie das Pad- Argument verwenden, um das Wort „Empty“ hinzuzufügen:

=WRAPCOLS(A1:E1,3,“Leer“)

Dieses neue Array sieht mit einem Wort anstelle des Fehlers viel besser aus.

Kombinieren Sie, um ein neues Array zu erstellen: HSTACK und VSTACK

Zwei letzte Funktionen, die wir uns ansehen werden, dienen dem Anhängen von Arrays. Mit HSTACK und VSTACK können Sie zwei oder mehr Zellbereiche horizontal oder vertikal zu einem einzigen Array zusammenfügen.

Die Syntax für jede Funktion ist dieselbe: HSTACK (Bereich1, Bereich2,…) und VSTACK (Bereich1, Bereich2,…), wobei nur das erste Argument erforderlich ist. Allerdings verwenden Sie fast immer das zweite Argument, das einen anderen Bereich mit dem ersten kombiniert.

• Bereich1 : Der erste Zellbereich, den Sie für das Array verwenden möchten, formatiert als „A1:D4“.
• Bereich2,… : Der zweite Zellbereich, den Sie zum ersten hinzufügen möchten, um das Array zu erstellen. Sie können mehr als zwei Zellbereiche kombinieren.

Schauen wir uns einige Beispiele mit HSTACK und VSTACK und ihren Formeln an.

In diesem ersten Beispiel kombinieren wir die Bereiche A1 bis D2 mit A3 bis D4 mithilfe dieser Formel:

=HSTACK(A1:D2,A3:D4)

Sie können sehen, wie unsere Datenbereiche zu einem einzigen horizontalen Array zusammengefasst werden.

Als Beispiel für die VSTACK-Funktion kombinieren wir drei Bereiche. Mit der folgenden Formel verwenden wir die Bereiche A2 bis C4, A6 bis C8 und A10 bis C12:

=VSTACK(A2:C4,A6:C8,A10:C12)

Jetzt haben wir ein Array mit allen unseren Daten mithilfe einer Formel in einer einzigen Zelle.

Bearbeiten Sie Arrays ganz einfach

Während Sie ARRAYFORMULA in bestimmten Situationen verwenden können , beispielsweise mit der SUMME-Funktion oder der IF-Funktion, können Sie mit diesen zusätzlichen Google Sheets-Arrayformeln Zeit sparen. Sie helfen Ihnen dabei, Ihr Blatt genau so anzuordnen, wie Sie es möchten, und das mit einer einzigen Array-Formel.

Weitere Tutorials wie dieses, jedoch mit Nicht-Array-Funktionen, finden Sie unter Verwendung der ZÄHLENWENN- oder SUMMENWENN-Funktion in Google Sheets .