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 Bearbeiten
Sie haben eine Umfrage - typischerweise mit Google Forms - durchgeführt, und möchten diese auswerten.
| 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.
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 Bearbeiten
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).
| 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 |
- 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