Google Sheets Formelsammlung

Google Sheets Formelsammlung

Google Sheets ist mächtig, und dabei gar nicht kompliziert. Anstelle von „Codes“ genügen dir teils einfache Formeln. Welche Formeln ich fast täglich nutze und mir meine Arbeit enorm vereinfachen, das fasse ich hier zusammen.

Hinweis: Dieser Beitrag wird dauerhaft aktualisiert / editiert. 

Hier eine Übersicht aller Formeln in der Formelsammlung:

  • IMPORTRANGE
  • FILTER
  • GOOGLEFINANCE
  • INDEX/VERGLEICH

=IMPORTRANGE

Mit IMPORTRANGE importierst du, wie die Formel schon sagt, eine Range von A nach B, von einem Google Spreadsheet in ein anderes. Einfach so.

Das heißt, dass du mithilfe dieser Formel beispielsweise den gut mit Daten gefüllten Produktkatalog in ein aktuelles Spreadsheet importierst, um dort den Produkttitel, den Einkaufspreis oder sonstige in deinem Katalog hinterlegten Werte verwenden kannst.

Die Formel dazu ist simple, nach ein bis zwei Wiederholungen sitzt die:

=IMPORTRANGE(„URL“;“Tabellenblatt!A1:B50″)

URL ersetzt du natürlich mit der URL des Spreadsheets, von welchem du Daten importieren möchtest. Als nächstes gibst du den Namen des Tabellenblatts ein. Bei mir heißt es zum Beispiel „Katalog“ (PS: Anführungszeichen nicht vergessen). 

Nach dem Ausrufezeichen folgt dann die Range, also der Bereich, den du importieren möchtest. Bei großen, sich auch oft ändernden Tabellenblättern, verwende ich lediglich die Spaltenangaben, zum Beispiel A:R. Du kannst aber auch einen spezifischen Bereich importieren, indem du B4:C20 eingibst. Das musst du für dich selbst herausfinden.

Meine Formel lautet also:

=IMPORTRANGE(„URLderDatei“;“Katalog!A:R“)

Anschließend fragt dich Google noch, ob du das Tabellenblatt verbinden möchtest (was natürlich notwendig ist). Sobald du die Freigabe erteilt hast, importiert Google Spreadsheet die Daten und du kannst diese weiterverwenden.

=FILTER

Die Funktion FILTER ist eine weitere mächtige Funktion, mit welcher du Daten innerhalb eines Spreadsheets nach Belieben filtern kannst und diese in einem weiteren Tabellenblatt ausgeben kannst. Perfekt für schnelle Analysen von Verkäufen auf Produktebene o.ä.

Der Formelaufbau ist wieder relativ einfach:

=FILTER(„BEREICH“;“BEDINGUNG1″;“BEDINGUNG2″)

Es sind mehrere Bedingungen möglich, was super praktisch ist.

Nehmen wir also an, wir wollen aus der Verkaufsstatistik der letzten Woche alle Produkte herausfiltern (und damit weiter arbeiten), die weniger als 5% zum Gesamtumsatz beigetragen haben. Meine Formel könnte dann wie folgt aussehen:

=FILTER(„VerkaufsstatistikA1:D500″;“C1:C500<5%“)

In diesem speziellen Beispiel heißt meine Tabelle also „Verkaufsstatistik“ und ich habe diese Tabelle schon vorbereitet um eine Spalte mit dem prozentualen Anteil an Verkäufen; diese sind in diesem Beispiel in Spalte C.

Nun sage ich Google mit der Formel folgendes:

  1. Filtere mir den Bereich A1 bis D500 meiner Verkaufsstatistik
  2. und zeige mir alles an, bei dem ich weniger als 5% Umsatzanteil hatte

Natürlich gibt es noch so viel mehr Möglichkeiten, die Formel zu nutzen. Aber manchmal ist es auch einfacher, bestimmte Auswertungen im Ursprungstabellenblatt zu machen; wichtig ist, dass du dein Ziel gut kennst.

=GOOGLEFINANCE

Ein bisschen leichte Kost zwischendrin gefällig? 🙂 Dann geht es jetzt mal um den aktuellen Wechselkurs. Denn wenn du eine saubere Liquiditäts-Planung aufsetzen möchtest, benötigst du natürlich alle Werte in Euro – und nicht in USD.

Hierzu bietet dir Google die Möglichkeit, an jedweder Stelle im Dokument folgende Formel einzusetzen:

=GOOGLEFINANCE(„Currency:EURUSD“)

Diese Formel zieht den aktuellen EUR/USD Wechselkurs in die Zelle, in welcher du die Formel eingibst. Du kannst in der gleichen Zelle nun direkt weiterrechnen. Nehmen wir also an, du leitest immer eine 30%-Anzahlung, und dein EK-Preis liegt in Zelle H5.

=H5/GOOGLEFINANCE(„Currency:EURUSD“)*30%

Und et voila, schon steht dort der Wert, den du (Wechselschwankungen unberücksichtigt) voraussichtlich zahlen wirst. Dazu noch ein paar nette Formeln für die Zahlungsfälligkeit und das Grundgerüst deiner Liqui-Planung steht.

Nett, wa?

=INDEX/VERGLEICH

Fast jeder kennt ihn: Den SVERWEIS. Und obwohl der SVERWEIS viele Probleme schon lösen kann, gibt es noch eine viel bessere Formel, die das gleiche Problem löst und noch viele weitere: INDEX in Kooperation mit VERGLEICH.

Der Unterschied? Mit SVERWEIS suchst du nach Daten nur in eine Richtung, mit SVERWEIS also nach rechts, mit HVERWEIS nach unten. Das limitiert häufig. Auch wenn du mittendrin nochmal neue Spalten in das ursprüngliche Dokument einfügst, wird’s nervig. Mit einem INDEX/VERGLEICH/VERGLEICH löst man dann auch dieses Problem und ist enorm flexibel.

So sieht die Formel INDEX aus:

INDEX(Bezug[Zeile][Spalte])

Mit dieser Formel gibst du lediglich einen Wert zurück. Alleinstehend habe ich diese Formel tatsächlich noch nie benutzt.

Und das ist die Formel VERGLEICH:

VERGLEICH(SuchschlüsselBereich[Suchtyp])

Auch diese Formel habe ich alleinstehend bisher nicht genutzt. In Kombination sind diese beiden Formeln aber fast unschlagbar.

So viel zur Theorie. Klingt erstmal alles interessant, kann man aber wenig mit anfangen. In der Praxis sieht das bei mir dann aber so aus:

=INDEX(Katalog!E:E;VERGLEICH(A3;Katalog!A:A;0);1)

Was macht diese Formel nun? Ich erklär’s dir:

INDEX(Katalog!E:E besagt, dass wir einen Wert aus der Spalte E im Tabellenblatt Katalog ziehen möchten. Nun benötigen wir den Vergleich um herauszufinden, aus welcher Zeile wir den Wert ziehen. Im Beispiel eines Katalogs möchten wir zum Beispiel den Einkaufspreis (Spalte E) ziehen, haben auf unserem aktuellen Blatt aber nur die SKU.

VERGLEICH(A3;Katalog!A:A;0);1) löst genau das auf. Wir sagen, dass wir den Wert aus der Zelle A3 (unsere SKU) suchen wollen, und zwar in der Spalte A im Tabellenblatt Katalog. Das Ende mit ;0;1) klärt dann noch eine weitere Variable, die wir in diesem Fall nicht haben.

 

Und wo liegt nun der Vorteil zum SVERWEIS?

Erstmal, nach ein paar Versuchen sitzt diese Formel leichter und das Abzählen von Spalten ist Geschichte. Zum Anderen geht, wie eingangs erwähnt, der INDEX/VERGLEICH in beide Richtungen. Wenn deine SKU also in Spalte F ist und du einen Wert aus Spalte B möchtest, dann geht der SVERWEIS nicht.

Und INDEX/VERGLEICH kann noch mehr?

Yes. Anstatt der 0 in der Formel gegen Ende kannst du nämlich einen weiteren Vergleich nutzen. Das bedeutet, dass du nach zwei Variablen suchen kannst. Ich benutze das zum Beispiel um einen Wert zu finden, der auf einer SKU basiert, aber auch auf einer KW. So kann ich mir gezielt wie ein Scharfschütze den relevanten Wert in meine Tabelle ziehen. Die Formel ist dann jedoch schon etwas komplexer.

Schreibe einen Kommentar

Deine E-Mail-Adresse wird nicht veröffentlicht.