Excel-Funktionen: Top-8-Funktionen für das Finanz- und Rechnungswesen
Adrian Frei
Adrian Frei, M.A. HSG, Geschäftsführer und Inhaber von exact construct GmbH, Frauenfeld. exact construct ist spezialisiert auf die Erstellung von Excel-Tools und Programmierung von Makros.
UND/ODER
Wir haben diese Formel in die Top-8-Formel-Sammlung aufgenommen, weil sie, zusammen mit anderen Formeln kombiniert, sehr leistungsstark ist. Mit der «UND»- und «ODER»-Formel können unterschiedliche Formeln verknüpft werden, indem eine logische Aussage generiert wird. Eine logische Aussage ist eine Aussage, die entweder WAHR oder FALSCH sein kann. Durch den Vergleich der in ihren Parametern bereitgestellten Anweisungen geben die Ergebnisse entweder WAHR oder FALSCH zurück. Dabei kann der Benutzer verschiedene Kriterien nacheinander prüfen.
Die UND-Formel gibt nur WAHR zurück, wenn alle bereitgestellten logischen Aussagen WAHR sind. Wenn mindestens eine logische Aussage FALSCH ist, dann ist das Ergebnis FALSCH. Anders funktioniert die ODER-Formel. Wenn alle bereitgestellten logischen Aussagen FALSCH sind, dann ist das Ergebnis FALSCH. Diese Formel gibt WAHR zurück, wenn es mindestens eine logische Anweisung gibt, die WAHR ist.
WENN
Die WENN-Formel gehört unseres Erachtens zu den wichtigsten Formeln. Diese Formel wird oft als eine der ersten Formeln gelernt, weil sie intuitiv erlernbar ist (siehe Abbildung 1). Die Verwendung der «WENN»-Formel ist nicht so komplex, der Benutzer muss einzig die drei Bestandteile der Formel verstehen:
=wenn(Bedingung;Resultat_1;Resultat_2)
Die Formel ist von links nach rechts zu lesen und bedeutet: Wenn die Bedingung erfüllt ist, dann zeig das Resultat_1 an, sonst das Resultat_2. Diese Formel kann nun mit den folgenden Werten befüllt werden.
Bedingung: A1 = «Apfel»
Resultat_1: «Frucht»
Resultat_2: «Gemüse»
Dies sieht dann so aus:
=wenn(A1 = «Apfel»;«Frucht»;«Gemüse»)
Die Formel ist nun so zu lesen: Wenn in Zelle A1 Apfel steht, dann zeig Frucht und sonst Gemüse an.
Im ersten Teil Bedingung können wir nun auch die bereits gelernte Formel ODER() wieder verwenden, indem wir gleichzeitig mehrere Kriterien prüfen. Beispielsweise, ob A1 = Apfel oder A2 = Birne ist. Wenn eines der beiden Kriterien korrekt ist, dann wäre das Resultat Frucht, sonst Gemüse.
VERKETTEN
Wenn Sie verschiedene Wörter haben, die Sie zusammenfügen möchten, dann ist die Formel VERKETTEN dafür geeignet. Beispielsweise, wenn in einer Spalte die Strasse steht und in der anderen Spalte die Nummer, dann können diese mit dieser Funktion zusammengefügt werden. Das manuelle Kopieren von solchen Werten ist nicht nur aufwendig, sondern auch fehleranfällig. Aus diesem Grund hat Excel diverse Möglichkeiten, verschiedene Zellen zu verbinden.
Eine Variante ist die «VERKETTEN-Formel». Mit dieser Formel können Sie maximal 30 Inhalte miteinander verknüpfen. Das folgende Beispiel ist die Kombination von Zellenwerten und einer Zeichenfolge.
=VERKETTEN(Text1;Text2;Text3)
Der Text kann eine manuelle Eingabe oder eine Zelle sein.
Text1: C3 = «Konto 1»
Text2: «hat einen Stand von »
Text3: E3 = «1’622.72»
Verketten können Sie auch mittels «&». Dies ist bei wenigen Zellen einfach anzuwenden, sind es viele Zellen, macht VERKETTEN mehr Sinn. In diesem Beispiel: =C3&» hat einen Stand von «&E3 (siehe Abbildung 2).
Abbildung 2: Beispiel VERKETTEN
MAX und MIN
Diese Formeln sind in der Liste der Top 8 enthalten, weil sie in Kombination mit anderen Formeln, beispielsweise mit der WENN-Formel, eingesetzt werden können. Zudem kann mit der Formel in einer Notenliste herausgefunden werden, welches die beste oder welches die schlechteste Note ist oder welches Produkt am wenigsten oder am meisten verkauft wird. Die MIN-Formel wird verwendet, um den niedrigsten oder minimalen Wert in einer Reihe von Werten zu finden, während die MAX-Formel den höchsten oder maximalen Wert in einer Reihe von Werten findet. Beide Formeln haben die gleichen Parameter.
=MIN(ZAHL1;ZAHL2;ZAHL3)
Die Parameter in der Formel können entweder ein verlinkter Bereich sein (siehe Zahl1), es kann eine Zahl sein, die Sie in die Formel reinschreiben (siehe Zahl2), oder Sie können mit dem Namensmanager einen Namen für einen Bereich definieren und mit der Formel auf diesen Namen verweisen.
ZAHL1: E3:E7 -> Bereich
ZAHL2: 10.5 -> Wert
ZAHL3: Testname -> definierter Name
In Abbildung 3 finden Sie ein Beispiel für die Ermittlung des Höchst- und Mindestbetrags in einem Bereich.
Passende Produkt-Empfehlungen
SUMMEWENN/SUMMEWENNS
Angenommen, Sie haben ein Geschäft und möchten nicht nur den Gesamtumsatz, sondern auch den Umsatz jedes Produkts pro Monat wissen. Da diese Formeln Summen unter Berücksichtigung von Bedingungen rechnen können, erachten wir diese Formel als sehr leistungsstark und für den Finanzbereich als äusserst wichtig.
Die «SUMMEWENN»/«SUMMEWENNS» berechnet die Summe in Abhängigkeit davon, ob die Kriterien für die angegebene Bedingung erfüllt sind. Der Unterschied zwischen den beiden besteht darin, dass im ersten Fall nur ein Kriterium geprüft wird, während bei der anderen Variante mehr als ein Kriterium geprüft werden kann.
Für die SUMMEWENN-Formel sind der Kriterienbereich und die Kriterien die ersten beiden Parameter. Der Summenbereich kommt als Letztes in die Formel.
SUMMEWENN
=SUMMEWENN(BEREICH;Suchkriterien;[Summe_Bereich])
Das Suchkriterium muss nicht unbedingt eine Referenz sein, es kann auch eine manuelle Eingabe sein. Es ist aber zu empfehlen, auf eine Zelle zu referenzieren.
BEREICH: D3:D12 -> in diesem Bereich werden die Kriterien geprüft.
SUCHKRITERIEN: H4 = «Produkt A», hierbei handelt es sich um das Kriterium, d.h., wir möchten den Umsatz von Produkt A wissen.
SUMME_BEREICH: E3:E12 -> aus diesem Bereich werden die Werte aufsummiert.
SUMMEWENNS
Die andere Formel ist SUMMEWENNS, hier wird mit dem Summenbereich begonnen und anschliessend ein Kriterium nach dem anderen erfasst. Da die SUMMEWENNS-Formel mehr als ein Kriterium haben kann, können der nächste Kriterienbereich und die Kriterien optional sein.
=SUMMEWENNS(Summe_Bereich;Kriterien_Bereich1;Kriterien1;Kriterien_Bereich2;Kriterien2)
Das Suchkriterium muss nicht unbedingt eine Zelle sein, es kann auch eine manuelle Eingabe sein.
Summe_Bereich: F3:F12 -> aus diesem Bereich werden die Werte aufsummiert
Kriterien_Bereich1: D3:D12 = In diesem Bereich wird das Kriterium 1 gesucht.
Kriterien1: H3 = «Produkt A» -> Das Kriterium für die erste Prüfung ist das Produkt A. Dieses wird im Kriterien_Bereich1 geprüft.
Kriterien_Bereich2: E3:E12 = In diesem Bereich wird das Kriterium 2 gesucht.
Kriterien1: M2 = «Januar» -> Das Kriterium für die zweite Prüfung ist Januar. Dieses wird im Kriterien_Bereich2 geprüft.
Mit SUMMEWENNS können Sie beispielsweise den Umsatz pro Monat und Produkt berechnen und so ein Dashboard erstellen (siehe Abbildung 4).
Abbildung 4: Anwendung SUMMEWENNS
MITTELWERTWENN/MITTELWERTWENNS
Die MITTELWERTWENN und MITTELWERTWENNS-Formel funktioniert sehr ähnlich wie die SUMMEWENN(S) Formel (siehe Abbildung 5). Die Parameter dieser Formel sind praktisch identisch.
=MITTELWERTWENN(Bereich;Kriterien;Durchschnittsbereich)
=SUMMEWENNS(Mittelwert_Bereich;Kriterien_Bereich1;Kriterien1;Kriterien_Bereich2;Kriterien2)
Der einzige Unterschied zur SUMMEWENN(S)-Formel ist, dass der Bereich nicht aufsummiert wird, sondern der Mittelwert aufgrund bestimmter Kriterien berechnet wird.
=Mittelwertwenn(D4:D13;H4;F4:F13)
=Mittelwertwenns(F4:F13;D4:D13;L4;E4:E13;M3)
ZÄHLENWENN/ZÄHLENWENNS
Gehen wir davon aus, dass Sie wissen möchten, wie viele Verkäufe Sie gemacht haben. Das heisst, Sie möchten die Anzahl Einträge pro Produkt bzw. pro Produkt und Monat wissen. ZÄHLENWENN/ZÄHLENWENNS zählt die Zellen im Bereich, die die spezifischen Kriterien erfüllen. Die Formel funktioniert gleich wie die vorherigen Formeln, der einzige Unterschied ist, dass die Anzahl unter Berücksichtigung der eingegebenen Kriterien berechnet wird (siehe Abbildung 6). Es gibt nur ein Kriterium für ZÄHLENWENN und mehrere Kriterien für ZÄHLENWENNS. Die Parameter dieser Formel sind der Bereich und die Kriterien.
=ZÄHLENWENN(BEREICH;Suchkriterien)
BEREICH: D3:D12 = In diesem Bereich wird das Kriterium geprüft.
SUCHKRITERIEN: H3 = «Produkt A», hierbei handelt es sich um das Kriterium, d.h., wir möchten die Anzahl Zeilen mit Produkt A wissen.
=ZÄHLENWENNS(Kriterienbereich1;Kriterien1; Kriterienbereich2;Kriterien2)
Kriterienbereich1: D3:D12 = In diesem Bereich wird das Kriterium 1 gesucht.
Kriterien1: L3 = «Produkt A» -> Das Kriterium für die erste Prüfung ist das Produkt A. Dieses wird im Kriterien_Bereich1 geprüft.
Kriterien_Bereich2: E3:E12 = In diesem Bereich wird das Kriterium 2 gesucht.
Kriterien1: M2 = «Januar» -> Das Kriterium für die zweite Prüfung ist Januar. Dieses wird im Kriterien_Bereich2 geprüft.
Diese Formeln werden oft in Dashboards für die Analyse der Anzahl von Verkäufen verwendet.
Abbildung 6: Beispiel ZÄHLENWENNS
SVERWEIS
Diese Formel gehört zu den meistverwendetsten Formeln in Excel und darf daher hier nicht fehlen. Im Englischen heisst sie «VLOOKUP». Wie der Name schon sagt, handelt es sich um eine Formel, die nach einem Wert in einem Bereich sucht. Das «V» bedeutet hierbei vertikal.
=SVERWEIS(Suchkriterium;Matrix;Spaltenindex;[Bereich_Verweis])
Suchkriterium: I3 = 1003 -> Dieses Kriterium wird in einer anderen Liste gesucht.
MATRIX: B3:E8 (oder Tabellenname) -> Hierbei handelt es sich um die Tabelle, in der das Suchkriterium gesucht werden muss.
Spaltenindex: 4 = Spalte Betrag -> Die erste Spalte in der Matrix muss immer der Schlüssel sein (Kriterium), der Index bestimmt die Spalte, welche ausgegeben werden muss, d.h., es wird die Anzahl Spalten gezählt.
Bereich_Verweis: FALSCH -> Das Suchkriterium muss genau übereinstimmen (WAHR oder leer lassen bedeutet, dass nach einer möglichst genauen Übereinstimmung gesucht wird).
Das Beispiel aus Abbildung 7 sucht nach einem Wert. Dies ist für eine Abfrage sehr hilfreich. Das heisst, kann eine Übersicht beispielsweise pro Konto erstellt werden, das Konto kann ausgetauscht werden, und das Resultat wird für das neue Konto angezeigt. Im Beispiel müsste also nur die Kontonummer ausgewechselt werden.
Müssen Sie nun beispielsweise einer Kontoliste die Währungskurse hinzufügen, kann die Formel auch direkt in einer Tabelle für sämtliche Einträge angewendet werden. Es ist zu empfehlen, dass die Kursliste in einem separaten Excel-Blatt vorhanden ist. Damit die Formel in der Spalte Kurs heruntergezogen werden kann, muss der Matrixbereich mit $ fixiert werden (z.B.: $A$1:$D$12) oder Spalten in Excel-Tabellen ausgewählt werden (siehe Abbildungen 7 und 8).
Abbildung 7: Beispiel SVERWEIS
Abbildung 8: SVERWEIS Anwendung
WAHR wird vor allem für die Einteilung von Werten in Bereiche verwendet, dies könnte beispielsweise ein Lohnband sein. Falsch wird immer dann verwendet, wenn die Übereinstimmung des Suchkriteriums zu 100% stimmen muss.
Fazit
Hierbei handelt es sich um jene acht Formeln, welche wir in Zusammenarbeit mit Mitarbeitenden von Finanzabteilungen am häufigsten benötigen. Abgesehen von den in diesem Artikel aufgeführten Formeln gibt es viele weitere nützliche Formeln, die Excel anbietet. Klar ist ebenfalls, dass diese Liste in gewisser Hinsicht ein wenig willkürlich zusammengestellt ist. Nichtsdestotrotz, wenn Sie diese acht Formeln beherrschen, werden Sie auch ohne Probleme die anderen verfügbaren Formeln in Excel anwenden können. Aus unserer Sicht die meistunterschätzte Formel ist die SUMMEWENNS-Formel. Sollten Sie diese Formel noch nicht anwenden, wenn Sie Auswertungen erstellen, dann lohnt es sich definitiv, diese zu lernen.
