10 praktische Google Spreadsheet Formeln für Datenjournalisten

Eine kurze Einführung in einige hilfreiche Google Spreadsheet Formeln, besonders hilfreich für datengetriebenen Journalismus.

(1) SPLIT

Sample Data for split formula

Teilt Text an bestimmten Trennzeichen in einzelne Zellen auf (das Trennzeichen wird dabei entfernt – entspricht der Menüfunktion “Text in Spalten aufteilen”):

=SPLIT(C3; ":"; TRUE)
  • Was soll aufgeteilt werden?
  • Angabe des Trennzeichen
  • Angabe ob bei jedem Vorkommen des Trennzeichens getrennt werden soll

(2) VLOOKUP

Animated gif with sample vlookup formula

Senkrechter Verweis. Sucht von der ersten Spalte eines Bereichs abwärts nach einem Schlüssel und gibt den Wert einer angegebenen Zelle in der Zeile zurück, die gefunden wurde. Sehr hilfreich beim Zusammenführen von Daten in verschiedenen Tabellenblättern mit einem gemeinsamen Schlüssel.

=VLOOKUP(A3;'02 - VLOOKUP (b)'!A:B;2;FALSE)
  • Angabe des Suchschlüssels
  • Bereich in dem gesucht werden soll (im Beispiel im Tabellenblatt mit dem Namen “02 – VLOOKUP (b)” in den Spalten A und B)
  • Angabe der Spalte im Bereich, die zurückgegeben wird
  • Angabe, ob die zu durchsuchende Spalte (die erste Spalte des angegebenen Bereichs) sortiert ist (default ist TRUE; wenn der Schlüssel nur 1x im Bereich vorkommt ist FALSE empfehlenswert)

 

HLOOKUP funktioniert analog als horizontaler Verweis.

 

(3) GOOGLETRANSLATE

Sample formulas for googletranslate

Liefert Übersetzungen via Google Translate – schneller Weg für Übersetzungen, die natürlich wie immer bei automatischen Übersetzungen mit Vorsicht zu genießen sind. Für fremdsprachige Datensätze aber mitunter sehr hilfreich.

=GOOGLETRANSLATE(A4; "de"; "fr")
  • Was wird übersetzt (Text oder Zellen)
  • Länderkürzel der Sprache des Quelltextes
  • Länderkürzel der Sprache in die übersetzt wird

(4) CONCATENATE

Concatenate sample formula

 

concatenate2

Verknüpft Zeichenfolgen miteinander (umgekehrte Funktion zu SPLIT).

=CONCATENATE(A2:A5;)
=CONCATENATE(A2; " "; A3; " "; A4; " "; A5; " ")
  • Angabe der zu vernüpfenden Zeichen (z.B. ein Bereich mehrerer Zellen)
  • Angabe kann beliebig oft wiederholt werden (Beispiel 2 mit Einfügen von Leerzeichen zwischen Zellwerten)

(5) LEFT, MID, RIGHT

Sample formula for left function

 

mid

 

right

Mit LEFT, MID und RIGHT erhält man den Teil einer Zeichenfolge.

=LEFT(A1; 3)
  • Die ersten 3 Zeichen von links werden ausgegeben
=MID(A1; 5; 6)
  • Ab dem 5. Zeichen werden die folgenden 6 Zeichen ausgegeben (Achtung: ein Leerzeichen wird mitgezählt, es ist ja auch ein Zeichen)
=RIGHT(A1; 10)
  • Die ersten 10 Zeichen von links werden ausgegeben

(6) SUBSTITUTE

Sample formula for substitute

Ersetzt Teile in einem Text durch eine neue Zeichenfolge.

=SUBSTITUTE(A2; "Max"; "Sabine"; 1)
  • Angabe des Ausgangstextes (z.B. Zelle A2)
  • Zeichenfolge, die ersetzt werden soll
  • Neue Zeichenfolge
  • Anzahl der Wiederholungen (wenn die zu ersetzende Zeichenfolge mehrfach vorkommt; default ist alle Vorkommen zu ersetzen)

(7) UNIQUE

Sample formula for Unique

Gibt eindeutige Werte/Texte aus dem angegebenen Quellbereich zurück.

=UNIQUE(A2:A12)
  • Angabe des Bereichs, in dem die eindeutigen Werte gesucht werden

(8) AVERAGE + MEDIAN

Sample formula for average

 

median

Average errechnet den Mittelwert (Durchschnitt) aus einem angegebenen Bereich; mit Median wird der mittlere Wert errechnet (man stellt sich alle Werte als aufsteigende Reihe vor, und es wird genau der Wert in der Mitte dieser Reihe zurückgegeben). Bei Daten zu Einkommen o.ä. ist oft der Median aussagekräftiger, weil er immun gegen einzelne starke Ausreisser am oberen oder unteren Ende der Reihe ist.

=AVERAGE(B2:B9)
=MEDIAN(B2:B9)
  • beim Median kommt im Vergleich zu Average ein niedrigerer Wert zurück, weil im angegebenen Bereich 8 Werte vorhanden sind, es wird bei einer geraden Anzahl der Durchschnitt der beiden mittleren Werte genommen (im Beispiel: 2500 und 3000 -> Durchschnitt davon 2750, das ergibt den Median)

(9) TRIM

Screenshot Trim Formel

Entfernt unnötige Leerzeichen (am Anfang, am Ende, doppelte Leerzeichen innerhalb des Strings):

=trim(" lorem ipsum")
=trim(A2)

(10) IMPORTHTML

Screenshot showing importhtml formula on sample data

 

Import die Daten einer Tabelle oder einer Liste aus einer HTML-Seite:

=IMPORTHTML("https://de.wikipedia.org/wiki/Liste_der_höchsten_Bauwerke_der_Welt"; "table"; 1)
  • Angabe der URL
  • Element (table oder list)
  • Index des Elements (die wievielte Tabelle/Liste in der HTML-Seite)

Aktualisiert sich laufend aus der HTML-Seite.

 

Daten der Beispiele:

Weiterführende Links: