Google Sheets-Kochbuch: Auswahllisten

Auswahllisten erzeugen Sie über Daten->Datenvalidierung->Liste aus einem Bereich oder über die Elementliste. Der typische Fall wird vermutlich sein, dass Sie eine Liste in einem Bereich haben, aus deren Elementen Sie wählen wollen. Da die Liste selbst dynamisch sein kann, ergeben sich einige interessante Anwendungsmöglichkeiten.

Die Umfrage - Beispiel 1

Sie haben eine Umfrage - typischerweise mit Google Forms - durchgeführt, und möchten diese auswerten.

Beispieltabelle Umfrage A1:D12

Antwort 1	Antwort 2	Antwort 3	Freitext
ja	ja	nein	Text 1
nein	ja	nein	Text 2
nein	nein	nein	Text 3
ja	nein	ja	Text 4
ja	ja	nein	Text 5
nein	nein	ja	Text 6
ja	nein	ja	Text 7
nein	ja	nein	Text 8
ja	ja	ja	Text 9
nein	ja	nein	Text 10
ja	ja	nein	Text 11

Für jede der Spalten erzeugen wir eine Dropdown-Liste mit Hilfe der Datenvalidierung. Es gibt in diesem Fall zwei Möglichkeiten:

• Liste aus einem Bereich (A2:A bzw. B2:B bzw. C2:C).
• Elementliste (ja,nein)

Die Elementliste ist performanter. Das fällt bei wenigen Zeilen nicht auf, aber wenn Sie 1000 und mehr Zeilen in der Tabelle haben, gibt es einen Unterschied.

Beispiel für eine Umfrageauswertung mit Auswahllisten

Zusammenfassung der Freitext-Antworten:

• =IFERROR(JOIN("|";QUERY(A2:D;"select D where A='"&E2&"' and B='"&F2&"' and C='"&G2&"'")))
  • Die Freitextanworten werden hier in einem Feld zusammengefasst, das funktioniert natürlich nicht so gut, wenn sehr viele Antworten vorliegen.
  • Beim JOIN habe ich ein Zeichen verwendet, das vermutlich nicht in den Antworten vorkommt. Damit können wir zum einen leere Antworten ausfiltern (wenn zwei oder mehr JOIN-Zeichen hintereinander vorkommen), und zum anderen die Antworten wieder splitten, wenn das später notwendig sein sollte.
  • Um leere JOINs auszufiltern, können Sie folgenden regulären Ausdruck mit REGEXREPLACE verwenden:
    • =REGEXREPLACE(REGEXREPLACE(REGEXREPLACE(H2;"\|\|+";"|");"\|+$";"");"^\|+";"")
      • Hier werden vielfache senkrechte Linien im Inneren des Strings durch einen ersetzt, vielfache senkrechte Linien am Beginn oder am Ende durch einen leeren String.

Die Anzahl der Antworten können wir leicht zählen:

• =COUNTIFS(A2:A;E2;B2:B;F2;C2:C;G2)

Die Bücherliste - Beispiel 2

Mit kaskadierenden Auswahllisten kann man Filter erstellen, wenn das Endergebnis automatisiert weiterverarbeitet werden soll (z.B. in einem Diagramm dargestellt). Man benötigt hier allerdings Hilfslisten, da leider eine Formel nicht verwendet werden kann, um eine Auswahlliste zu erstellen (zumindest kenne ich keine solche Möglichkeit).

Beispieltabelle Bücherliste A1:G18

Buch-Nr	Sachgebiet	Autor	Titel	Ort	Jahr	Verlag
375	Jugend	Baumann, Hans	Der Sohn des Columbus	München	1992	dtv
416	Geschichte	Baumann, Hans	Der große Alexanderzug	München	1994	dtv junior
434	Geschichte	Baumann, Hans	Ich zog mit Hannibal	München	1994	dtv junior
500	Jugend	Baumann, Hans	Steppensöhne	Berlin	1957	Ensslin
1336	Polytechnik	Baumann, Klaus	In der Autowerkstatt	Düsseldorf	1967	Schwann
40	Belletristik	Bergengruen, Werner	Die drei Falken	Zürich	1947	Arche
41	Belletristik	Bergengruen, Werner	Zorn, Zeit und Ewigkeit	Zürich	1945	Arche
1265	Belletristik	Bergengruen, Werner	Der letzte Rittmeister	Zürich	1952	Schifferli
1643	Belletristik	Bergengruen, Werner	Der spanische Rosenstock	Tübingen	1963	Wunderlich
218	Kinder	Blyton, Enid	5 Freunde auf der Felseninsel	München	1991	Bertelsmann
219	Kinder	Blyton, Enid	5 Freunde auf geheimnisvollen Spuren	Berlin	1962	Bertelsmann
419	Kinder	Blyton, Enid	Die Insel der Abenteuer	München	1950	dtv junior
423	Kinder	Blyton, Enid	Die See der Abenteuer	München	1951	dtv junior
823	Kinder	Blyton, Enid	Die Burg der Abenteuer	Berlin	1965	Klopp
971	Kinder	Blyton, Enid	Die schwarze 7	Stuttgart	1965	Mosaik
1280	Kinder	Blyton, Enid	Dollys großer Tag	München	1967	Schneider
1284	Kinder	Blyton, Enid	Hanni und Nanni in neuen Abenteuern	München	1965	Schneider

Beispiel für die Verwendung von Auswahllisten

• Für die Autoren nehmen wir eine Liste aus einem Bereich, hier z.B. C2:C.
• Die Spalte "Verlage" ist die Hilfsliste. Wir brauchen eine Liste aller Verlage, bei denen der Autor publiziert hat.
  • =QUERY(C2:G18;"select G where C matches '" & H2 & "' order by G")
• Mit der Hilfsliste füllen wir die Auswahlliste für den Verlag, also K2:K.
• Die Titel erhalten wir wieder über einen QUERY:
  • =QUERY(C2:G18;"select D where C matches '" & H2 & "' and G matches '" & I2 & "' order by G")

Die Beispiele auf dieser Seite wurden getestet: 04/2022