Google Sheets-Kochbuch: Arrays und Arrayformeln

Arrays

Ein Array in einer Tabellenkalkulation ist ein Tabellenbereich, der aus einer Zelle, einer Zeile, einer Spalte, oder mehreren Zeilen und Spalten bestehen kann. Also nichts anderes als das, was Sie schon kennen.

Arrays werden durch geschweifte Klammern {} gekennzeichnet. Spaltenarrays werden mit ; (Semikolon) erzeugt, Zeilenarrays im Deutschen mit \ (Backslash), im Englischen mit , (Komma). Da das Komma im Deutschen als Dezimaltrennzeichen verwendet wird, wird im Deutschen eben der Backslash verwendet.

Ein Spaltenarray kann auch als Spaltenvektor bezeichnet werden, ein Zeilenarray als Zeilenvektor. Ein zweidimensionales Array wird als Matrix bezeichnet.

Alle Arrays können in Formeln verwendet werden, so, wie man sonst auch Zellbezüge verwendet.

• ={1;2;3}
  • Ein Spaltenarray, dieses erzeugt drei untereinanderliegende Zellen mit den Werten 1, 2 und 3.
  • Strings müssen in Hochkommata eingeschlossen werden: ={"Apfel";"Biene";"Baum"}
• ={"A"\"B"\"C"}
  • Ein Zeilenarray, dieses erzeugt drei nebeneinanderliegende Zellen mit den Werten A, B und C. Da es sich um Zeichenketten handelt, müssen diese in Hochkommata eingeschlossen werden.
• ={{"Frucht"\"Apfel"};{"Tier"\"Biene"};{"Planze"\"Baum"}}
  • Ein zweidimensionales Array mit zwei Spalten und drei Zeilen.
  • Die doppelten geschweiften Klammern sind nicht notwendig. Identisch ist der folgende Ausdruck: ={"Frucht"\"Apfel";"Tier"\"Biene";"Planze"\"Baum"}

Zellbezüge in Arrays

In Arrays können Zellbezüge verwendet werden. Das ist insbesondere deswegen hilfreich, da damit existierende Tabellen "on the fly" umsortiert werden können.

• ={A1}
  • Das wäre das einfachste Array, das nur aus einer Zelle - hier A1 besteht.
• ={B1:B6\A1:A6}
  • Der Bereich A1:B6 wird mit umgekehrten Spalten wieder ausgegeben, also erst die Spalte B, dann die Spalte A.
• =SORT({B2:B6\A2:A6};1;TRUE)
  • Hier verwenden wir ein Array bei dem wir die Spalten vertauscht haben, und lassen es nach Spalte 1 aufsteigend sortieren.
• =VLOOKUP(3,4;SORT({B2:B6\A2:A6};1;TRUE);2;TRUE)
  • Wir suchen mit einem VLOOKUP (=SVERWEIS) in einem sortieren Bereich. Da mit VLOOKUP immer nur in der ersten Spalte gesucht werden kann, sortieren wir mit dem Array die Spalten in der richtigen Reihenfolge, und lassen uns dann die zweite Spalte ausgeben. Die zweite Spalte ist für VLOOKUP die Spalte A, die Suchspalte die Spalte B (suchen nach links).

Zusammenhalten von Bezeichnung und Wert

Es gibt sehr häufig die Situation, dass wir einen Wert in der Tabelle bezeichnen wollen. Dann schreiben wir die Bezeichnung in die eine Zelle, die Berechnung in die Zelle daneben.

Summe:

=sum(A1:B15)

Es wäre schöner, wenn die Bezeichnung und der Wert zusammengehalten würden.

• ={"Summe:"\SUM(A1:A5)}
  • Wir erhalten einen Zeilenvektor, der in einer Zelle definiert ist.

Dynamische Listen und ihre Auswertung

Dynamische Listen entstehen immer dann, wenn die Anzahl der Einträge nicht bekannt ist. Ein typisches Beispiel sind die Ergebnisse von Google-Formularen, die dynamisch in einer Tabelle gesammelt werden. Oder man hat eine bestimmte Auswertung erstellt, und möchte dem Endanwender die Möglichkeit geben weitere Einträge nachzutragen, ohne dass er die Auswertung verändern muss.

Je nach Typ der Abfrage müssen Sie unter Datei->Einstellungen->Berechnung die Iterative Berechnun anstellen. Es genügt eine Max. Anzahl der Iterationen von 1.

Beispiel:

• {{query(A:A;"select * where (A >7)")};{"Anzahl: " & COUNT(D:D)}}
  • Wir fragen Spalte A ab und wollen wissen, wie groß die Anzahl der Werte größer als 7 ist.
  • Die Abfrage wird in Spalte D ausgegeben.
  • Unter der Liste wird die Summe angezeigt, egal, wie lang diese Liste ist.
  • COUNT zählt die Anzahl der numerischen Werte. Achtung! COUNT zählt sich selbst in diesem Fall nicht mit, da hier ein String ausgegeben wird. Würde nur die Anzahl ausgegeben würde COUNT sich selbst mitzählen, von der Anzahl der Ergebnisse müsste also 1 abgezogen werden.

ARRAYFORMULA - Matrixformeln

Was ist eine Matrixformel?

Matrixformeln erlauben es, eine Berechnung nicht nur für einzelne Zellen durchzuführen, sondern als Eingabe ganze Zeilen, Spalten oder Matrizen zu verwenden. Statt also z.B. zu schreiben: A1 + B1; A2 + B2; A3 + B3; können Sie A1:A3 + B1:B3 verwenden. Dabei ist zu beachten, dass die Daten die gleiche Dimension haben wenn nötig, also nicht z.B. A1:A4 + B1:B3, hier passt die Anzahl der Reihen nicht.

Matrixformeln können Sie in Google Sheets auf zwei Arten erstellen:

• mit INDEX
• mit ARRAYFORMULA

Mit der Funktion INDEX können Sie zusätzlich noch einen Ergebnisbereich festlegen, ARRAYFORMULA hat keine weiteren Parameter. Wir wollen hier zunächst Beispiele für die ARRAYFORMULA besprechen.

Im folgenden Beispiel wollen wir die durchschnittliche Bewertung eines Produktes erfahren. Es gibt vier Stufen der Bewertung, angegeben ist die jeweilige Anzahl der Antworten.

A1:B5 - Antworten einer Umfrage

Bewertung	Anzahl der Antworten
1	789
2	3819
3	2389
4	329

• =ARRAYFORMULA(A2:A5*B2:B5)
  • ARRAYFORMULA berechnet das Produkt für jede Zeile einzeln und liefert eine Liste mit dem Ergebnis zurück.

• =SUM(ARRAYFORMULA(A2:A5*B2:B5))/SUM(B2:B5)
  • Mit SUM berechnen wir dann die Summe der Werte in der Liste.
  • Da wir den Durchschnittswert brauchen, teilen wir durch die Anzahl der Antworten.
  • Äquivalent hierzu wäre der Ausdruck: =SUMPRODUCT(A2:A5;B2:B5)/sum(B2:B5)

1x1 Tabelle

Wir können Zeilen- und Spaltenvektoren miteinander multiplizieren, als Beispiel dient uns eine 1x1-Tabelle.

A1:D5 - 1x1-Tabelle

1x1	1	2	3
1	=ARRAYFORMULA(A2:A5*B1:D1)
2
3
4

• =ARRAYFORMULA(A2:A5*B1:D1)
  • Diese Arrayformel füllt die Tabelle auf einmal aus. Sie berechnet für jede Zelle das Produkt aus den entsprechenden Zeilen und Spaltenköpfen.

A1:D5 - Ergebnistabelle

1x1	1	2	3
1	A2*B1	A2*C1	A2*D1
2	A3*B1	A3*B2	usw.
3	A4*B1	usw.
4	usw.

INDEX und Matrixformeln

Statt der Funktion ARRAYFORMULA können Sie genauso gut die Funktion INDEX verwenden. Mit der Funktion INDEX haben Sie zusätzlich die Möglichkeit, die Ausgabe zu beschränken.

• =INDEX(Bezug;[Zeile];[Spalte])

A1:D4 Demonstration von INDEX A B C D   a b c 1 a1 b1 c1 2 a2 b2 c2 3 a3 b3 c3

A1:D4 Index nur 1. Zeile A B C D   a b c 1 a1 b1 c1 2       3

A1:D4 Index nur 2. Spalte A B C D   a b c 1 b1     2 b2     3 b3

A1:D4 Index nur Zelle A B C D   a b c 1 b2     2       3

A1:D4 Index 2. Spalte mit Versatz A B C D   a b c 1   b1   2   b2   3   b3

• =INDEX(CONCAT(B1:D1;A2:A4))
  • Das Ergebnis sehen Sie oben links in der Tabelle. B1&A2, C1&A2, D1&A2, B1&A3, C1&A3, ... usw.
• =INDEX(CONCAT(B1:D1;A2:A4);1;0)
  • Hier geben Sie nur die erste Zeile aus.
• =INDEX(CONCAT(B1:D1;A2:A4);0;2)
  • Hier geben Sie nur die zweite Spalte aus. Allerdings wird das Ergebnis nicht in Spalte C, sondern in Spalte B eingetragen.
• =INDEX(CONCAT(B1:D1;A2:A4);2;2)
  • Hier beschränken Sie die Ausgabe auf eine Zelle, den Kreuzungspunkt der Zeile und Spalte. Hier also das Ergebnis "b2".

Wenn Sie das Ergebnis der zweiten Spalte auch wirklich in der zweiten Spalte ausgeben wollen, können Sie einen leeren Spaltenvektor in die erste Zeile schreiben.

• ={{"";"";""}\INDEX(CONCAT(B1:D1;A2:A4);0;2)}
  • Mit {"";"";""} erzeugen Sie einen leeren Spaltenvektor, der so viele Reihen enthalten muss, wie das Ergebnis der Indexfunktion (hier 3).
  • Jetzt wird das Ergebnis unterhalb des Buchstabens "b", also in Spalte C ausgegeben.

SEQUENCE - Arrays erstellen

Die Funktion SEQUENCE dient dazu, ein mit Zahlen gefülltes Array zu erstellen.

• SEQUENCE(Zeilen; [Spalten];[Beginn];[Schritt])
  • Zeilen legt die Anzahl der Zeilen, Spalten die Anzahl der Spalten fest.
  • Beginn legt die erste Zahl fest, Schritt die Schrittweite, um die die Zahlen erhöht/verringert werden.

SEQUENCE 2x3 1 2 3 4 5 6

SEQUENCE 3x2 1 2 3 4 5 6

Transponierte SEQUENCE 2x3 1 4 2 5 3 6

SEQUENCE, Schrittweite 2 1 3 5 7 9 11

• =SEQUENCE(2;3)
  • Erstellt ein zweidimensionales Array mit zwei Zeilen und 3 Spalten. Beginn 1, Schrittweite 1.
• =SEQUENCE(3;2)
  • Erstellt ein zweidimensionales Array mit drei Zeilen und 2 Spalten. Beginn 1, Schrittweite 1.
• =TRANSPOSE(SEQUENCE(2;3))
  • Transponiertes Array.
• =SEQUENCE(2;3;1;2)
  • Erstellt ein zweidimensionales Array mit zwei Zeilen und 3 Spalten, das mit 1 beginnt, Schrittweite 2.

SEQUENCE kann natürlich noch lauter schöne andere Dinge mehr.

• =ARRAYFORMULA(CHAR(SEQUENCE(15;1;65)))
  • Erstellt einen Spaltenvektor A-O
• =ARRAYFORMULA(DATE(2022;SEQUENCE(12);1))
  • Erstellt einen Spaltenvektor mit den Daten 01.01.2022 - 01.12.2022.
• =INDEX(MOD(SEQUENCE(1;12;B1);12)+12*(MOD(SEQUENCE(1;12;B1);12)=0))
  • Erstellt einen Zeilenvektor von B1 bis 12 und von 1 bis B1-1
  • Bsp.: in B1 steht 8, dann erstellt der Aufruf: 8|9|10|11|12|1|2|3|4|5|6|7
  • Dient z.B. dazu, die Nummern der Monate zu erstellen.

SEQUENCE mit Lücken (leeren Zellen) erstellen

Um eine SEQUENCE mit Lücken zu erstellen, erzeugen wir zunächst mit JOIN einen String mit Trennzeichen, den wir anschließend mit SPLIT wieder teilen.

• =TRANSPOSE(SPLIT(JOIN(", ,";ARRAYFORMULA(CHAR(SEQUENCE(15;1;65))));",";TRUE;FALSE))
  • Erstellt einen Spaltenvektor A-O, wobei zwischen den Reihen jeweils eine Zelle Platz ist.

Ein Beispiel für den Einsatz von SEQUENCE finden Sie auf der Seite über SPLIT.

Informationen über Arrays herausfinden

• =COLUMNS(Bereich)
  • Liefert die Spaltenanzahl zurück.
• =ROWS(Bereich)
  • Liefert die Zeilenanzahl zurück.

Arrays manipulieren

• =ARRAY_CONSTRAIN(Bereich, Zeilen, Spalten)
  • Liefert einen Ausschnitt aus dem Bereich zurück, der durch die Zeilen- und Spaltenanzahl beschränkt ist.
  • Ist das Array kleiner als die angegebene Zeilen- und/oder Spaltenanzahl, ist das kein Problem.

• =CONCATENATE(Bereich)
  • Hängt den Inhalt aller Zellen eines ein- oder zweidimensionalen Bereiches hintereinander.

• =JOIN("Trennzeichen";Bereich [;Bereiche])
  • Hängt den Inhalt aller Zellen eines oder mehrerer eindimensionaler Bereiche mit Trennzeichen hintereinander (umgekehrter SPLIT).

• =FLATTEN(Bereich [;Bereiche])
  • Fasst die Werte von einem oder mehreren Bereichen in einer einzigen Spalte zusammen.

• =TRANSPOSE(Bereich)
  • Macht aus einem Zeilenbereich einen Spaltenbereich bzw. umgekehrt.
  • Beispiel: =TRANPOSE(A1:A5)
    • Erzeugt aus dem Spaltenvektor einen Zeilenvektor, der also horizontal ausgegeben wird.

Die Beispiele auf dieser Seite wurden getestet: 03/2022