5 Excel Formeln, die du kennen solltest

Ich vergleiche Excel oft mit dem Erlernen einer neuen Sprache. Es gibt Hunderte von Excel Formeln, die dann auch noch miteinander kombiniert werden können. Daher werde ich oft gefragt: Welche Formeln MUSS ich denn können, um den Großteil meiner Aufgaben bewältigen zu können.

Natürlich ist das wiederum abhängig von deinen Aufgaben.

Ein Controller benötigt andere Werkzeuge als ein Marktforscher.

Kommen wir jedoch wieder zurück zu meinem Gleichnis - Du möchtest eine neue Sprache lernen – Mit welchen 5 Wörtern solltest du starten?

Vermutlich nicht „Kraftwerk“, „Reformation“ oder „Umsatzsteuer“.

Du lernst die 5 Wörter, mit denen du die meisten Situationen in dieser Sprache meistern kannst:

„Hallo“

 „Ja“,

 „Nein“,

„Danke“

„Auf Wiedersehen“,

Daher stellte ich mir die Frage:

Was sind denn die 5 Excel Formeln bzw Funktionen, mit denen man die meisten Situationen meistern kann?

Was ist das Excel Pendant zu „Hallo“?

Bin ich alleine in der Lage dir zu sagen, was die 5 wichtigsten Excel Formeln sind?

Wahrscheinlich schon :D

Jedoch möchte ich nicht nur meine Sicht wiederspiegeln und habe daher das Excel Know-How von meinen 6 Lieblings Excel-Bloggern (national und international) abgerufen.

Die Frage, die ich ihnen gestellt habe:

„Wenn du nur noch 5 Excel Formeln verwenden dürftest, welche wären das?“

Somit erhältst du hier eine Excel Formel Übersicht, die viel mehr Wert ist, als die übliche Excel Formel Zusammenfassung. Denn das Ergebnis dieser Frage spiegelt die Erfahrung von 6 Excel Profis wider, die sich täglich mit den unterschiedlichsten Fragestellungen in Excel auseinandersetzen.

Der Artikel teilt sich in folgende Bestandteile auf:

1. TOP 5 Excel Formeln der Profis

2. Wir krönen die Champions - Das sind die TOP 5

3. Tutorial zu allen TOP 5 Excel Formeln

4. Downloade die Excel Vorlage mit den TOP 5 Formeln

5. Fazit

Blog: www.tabellenexperte.de

SUMMENPRODUKT

Damit lassen sich viele andere Funktionen ersetzen, wie z.B. SUMME, SUMMEWENNS, ANZAHL, ANZAHLWENN… Die absolute Geheimwaffe.

INDEX

VERGLEICH

Weil man mit damit problemlos den SVERWEIS ersetzen kann und obendrein noch wesentlich flexibler ist.

WENN

UND/ODER

Ohne WENN sind viele Auswertungen schwer möglich. Schließlich steht man immer vor irgendwelchen Entscheidungen, ganz wie im richtigen Leben..

Blog: www.excelnova.org

SUMME

...wann öffnet man Excel ohne dass man diese Formel verwendet? Klar, unten in der Statusleiste könnte man die Summe auch noch sehen, aber ein Bericht erstellen ohne SUMME? Unmöglich.

INDEX/VERGLEICH

(das gilt als eine Formel ;- ) ...damit ich jeden Wert in einer Matrix finden kann und um Listen anzureichern mit mehr Daten

SUMMENPRODUKT

...fürs Reporting unerlässlich

ANZAHL2

&

...eigentlich keine Formel, aber mit dieser ersetze ich die VERKETTEN() Formel. Wenn ich irgendetwas mit Text mache dann benutze ich bestimmt auch ein Ampersandzeichen

Blog: www.trumpexcel.com

INDEX/MATCH (INDEX/VERGLEICH)

I love the combination of INDEX and MATCH functions. I call it 'VLOOKUP on steroids'. It allows me to scan through huge data sets and do lookup operations. I use it extensively in Excel Dashboards.

IF (WENN)

Sometimes, it the simple ones that save the day. If I have only a couple of conditions to check, I prefer using the IF function.

ROW/COLUMN (ZEILE/SPALTE)

This function allows me to introduce a variable that can change as the row/column number changes. I use it in other formulas such as MATCH/VLOOKUP/SUMPRODUCT (Vergleich/Sverweis/Summenprodukt) to make my formulas dynamic.

SUMPRODUCT (SUMMENPRODUKT)

This is my favorite function while creating Excel Dashboards. It allows filtering data sets in the formula to get the final result.

TEXT (TEXT)

This function allows me to use numbers along with text in a specific format. For example, if I have the value .01 in cell A1, I can make it show up as "1.0% Increase" by using the formula =TEXT(A1&"0.0%")&" Increase". This can make a text statement dynamic as it is linked to a cell.

Blog: www.atlaspm.com

AGGREGATE (AGGREGAT)

Like the SUMPRODUCT (SUMMENPRODUKT) and SUBTOTAL (TEILERGEBNIS) functions, the AGGREGATE (AGGREGAT) function can treat arguments as arrays without array-entering the formula. AGGREGATE (AGGREGAT) allows you to apply functions such AVERAGE (MITTELWERT), SUM (SUMME), COUNT (ANZAHL), MAX (MAX) or MIN (MIN) and ignore errors or hidden rows.

CONVERT (UMWANDELN)

The CONVERT  (UMWANDELN) function converts one unit of measurement with another unit, such as for weights (example, pounds to grams) or distance (example, miles to kilometers), which is an increasingly useful tool in today’s global business economy.

EXACT (IDENTISCH)

The EXACT (IDENTISCH) function is case sensitive and compares two strings to return TRUE if both values match, regardless of how they are formatted. Useful for comparing passwords, names of people and places, and situations where fuzzy matching of the Find method is needed.

INFO (INFO)

The versatile INFO (INFO) function returns information about the operating environment. The type of information you can derive from INFO (INFO) includes the current directory, operating system, Office release version, and calculation mode, among others.

LOOKUP (VERWEIS)

The LOOKUP (VERWEIS) function returns a certain kind of value from a single row or column. Examples, to find the last numeric value in column A, use =LOOKUP(9.99999999999999E+307,A:A), and for the last non-zero number in row 5, use =LOOKUP(2,1/(A5:Z5<>0),A5:Z5). 

Blog: www.clevercalcul.wordpress.com

SUMME
SUMMENPRODUKT
INDEX
BEREICH.VERSCHIEBEN
VERKETTEN
Warum diese?
Als Controller habe ich immer wieder Berichte zu erstellen. Ich brauche diese Funktionen, um Daten aus unterschiedlichen Dateien abzurufen. SUMME brauche ich natürlich stets.

Blog: www.andreas-unkelbach.de

INDIREKT

Besonders im Berichtswesen und bei der Gegenüberstellung von verschiedenen Ergebnistabellen weiß ich mittlerweile einen indirekten den direkten Bezug zu bevorzugen. Im Rahmen eines Finanzberichtes habe ich die einzelnen Jahre als Tabellenblätter mit der Bezeichnung FIN2016, FIN2015, ... usw. angelegt und der weitere Aufbau der Tabellenblätter ist stets identisch. Durch die Angabe von Spalte und Zelle kann ich aus jeder Tabelle mir den jeweiligen Jahreswert nehmen (bspw. ist das Budget der Kostenstelle 101000 immer in Zelle E6 zu finden).

In der Tabelle FIN_ALLEJAHRE habe ich nun in der Spalte A die Zeilennummer und in der Zeile 3 habe ich die Spaltenbuchstaben oberhalb der Zellenüberschriften hinterlegt. Budget befindet sich in der Spalte B, Kosten Spalte C während die Kostenstelle 101000 in der Zeile 6 zu finden ist. In der Spalte B trage ich nun das Jahr in der Form FIN2016 ein und kann über die INDIREKT Formel direkt auf die Daten zugreifen. Während ich Zeilen und Spalten als feste indirekte Bezüge definiere kann ich das Tabellenblatt vorgeben und für ein neues Jahr die ganzen Formeln kopieren und muss nur das Jahr sprich Tabellenblattname eintragen. Für den Budgetwert der Kostenstelle 1010000 trage ich also folgende Formel ein:

=INDIREKT($B6&"!"&D$3&$A$6)

Wobei hier $B6 der Tabellenblattname FIN2016, D$3 der Spaltenbuchstabe C und A$6 die Zeile 6 ist.

Somit wird automatisch ein Bezug auf =FIN2016!C6 gesetzt und kann auch auf die anderen Felder übertragen werden.

Darüber hinaus verknüpfe ich in einer weiteren Zelle noch Tabellenblattnamen und Kostenstelle um dieses als Index für meine zweite Lieblingsfunktion zu nutzen.

VERWEIS

Die einzelnen Verweisfunktionen hatte ich einmal im Artikel "Excel: WVERWEIS Funktion oder Jahresdaten umgruppieren für Mehrjahresvergleich" hier vorgestellt und bin mittlerweile sowohl von SVERWEIS, WVERWEIS und selten auch VERWEIS sehr angetan. In meiner oberen Tabelle mit den INDEX Bezügen kann ich zum Beispiel durch Angabe des Jahres in der Zelle A und Kostenstelle in der Spalte B mir automatisch einen Vergleich einzelner Jahre (FIN2015, FIN2016) ebenso wie Kostenstellen verwenden indem ich hier folgende Formel verwende um auf das Tabellenblatt FIN_ALLEJAHRE zuzugreifen.

=SVERWEIS($A17&$B17;FIN_AlleJahre!$C:$Q;E$15;FALSCH)

Eine Besonderheit ist, dass hier in der Spalte E die Zeilennummer des Budgetwert der Kostenstelle und damit den Spaltenindex der Matrix entspricht. Da ich die FIN20.. ebenso wie die Kostenstellen als Gültigkeitsbereich über eine Liste hinterlegt habe kann hier direkt eine Auswahl getroffen werden.

BEREICH.VERSCHIEBEN

Da ich grundsätzlich gerne mit Bereichen arbeite habe ich auch für eine Auswertung in der in den Spalten A bis D entsprechende Daten aus unseren Finanzsystem erhoben werden in den Spalten E bis G  eine Formel die mir hier über mehrere Zeilen Formeln berechnet (bspw. ERTAG mit * -1, da aus unseren ERP System Erträge negativ ausgewiesen werden.

Nun ist aber das Problem, dass ich hier natürlich auch viele 0 Werte habe, wenn weniger Daten in den Spalten A bis D stehen. Daher habe ich durch

=Bereich.Verschieben($E$2;;;Anzahl2($A:$A)-1;3)

Hier ist eine Matrix als Name definiert, so dass nur die berechneten Zeilen verwendet werden, hinter denen auch Daten liegen.

WENN

Gerade bei der Überprüfung von Werten (oder auch wenn anhand eines Merkmals der Stammdaten, Daten aus einer anderen Tabelle entnommen werden sollen, ist die WENN Funktion außerordentlich hilfreich damit hier auch komplexere Darstellungen funktionieren. Für die eigene Bequemlichkeit nutze ich dann natürlich auch noch die WENNFEHLER um mir bestimmte Fehler oder Hilfskonstruktionen über ISTNV bei Verweisen zu ersparen. :-)

LINKS / RECHTS

Sicherlich könnte man auch die Funktion TEIL verwenden um einen Teilstring eines Wertes auszuwerten, aber tatsächlich nutze ich noch immer gerne Verschachtelungen mit LINKS(rechts())) um aus Kostenträger samt Bezeichnung nur den Kostenträger zu erhalten.

WENN

Fast jede Formel beginnt bei mir mit einem WENN oder das WENN ist Bestandteil einer Verschachtelung. Gerade bei der Erstellung von Formularen/Vorlagen arbeitet man mit verschiedenen Szenarien und somit auch mit mehreren Bedingungen, die über die einfache WENN-Formel dargestellt werden können.

Index/Vergleich

Ich war lange Zeit Anhänger der SVerweis Formel (daher auch meine SVerweis Trilogie) und habe mich lange gesträubt das Lager zu wechseln und Index/Vergleich zu verwenden. Aber was soll ich sagen, es ist nun mal die Variante mit der größeren Flexibilität.

Summe

Simpel aber wichtig! Summe ist ganz klar das „Hallo“ bei Excel. Ich vermute, dass 100% aller Excel Neulinge zunächst die Summenformel austesten – und das zu Recht.

KKleinste/KGroesste

Da ich Riesenfan von Management Summaries bin, arbeite ich oft in meinen Reports mit TOP Listen z.B. Top 10 Kostentreiber, Top 5 Umsatzquellen usw. Daher sind KKleinste/KGroesste für mich unerlässlich.

ODER

Hier gilt das gleiche wie bei der WENN Formel. Für das Arbeiten mit mehreren Bedingungen ist es wirklich schwierig für mich darauf zu verzichten.

Na, hast du fleißig mitgezählt? Wer sind denn nun unsere Top 5 Excel Formeln und wer ist unser Gesamt Champion? 

Hier kommt die Auflösung in Form einer Infografik:

Eine Anmerkung hierzu: INDEX und VERGLEICH wurden auch separat genannt. Ich habe es bei der Auszählung jeweils zur Kombi INDEX/VERGLEICH zusammengefasst. Außerdem teilen sich SUMMENPRODUKT und WENN den 2. Platz.

Für alle Skeptiker gibt es hier auch noch einmal das Ergebnis der  Auszählung im Excel Diagramm :)

Unsere TOP 5 Excel Formeln stehen also fest. INDEX/VERGLEICH konnte sich mit 5 Stimmen durchsetzen. Aus meiner Sicht vollkommen zu Recht, auch wenn nun alle SVerweis Nostalgiker vom Stuhl fallen :D

Am besten hier direkt die Excel Vorlage zum nachvollziehen herunterladen und dann in die Tutorials starten.

Eigentlich habe ich ja hier ein wenig geschummelt, da INDEX/VERGLEICH eine Kombination von 2 Excel Funktionen darstellt. Aber wir wollen ja nicht so kleinlich sein. Um die Kombination erklären zu können, widmen wir uns zunächst den beiden Bestandteilen:

INDEX

=INDEX(MATRIX;ZEILE;SPALTE)

Die Index Funktion beantwortet mir folgende Frage: Was steht in meiner Tabelle in Zeile 4 der Spalte 5?

Wie man sieht, ist die Index Funktion ziemlich einfach, jedoch auch nur in seltenen Fällen ist sie für sich alleine anwendbar. Daher kommen wir nun zum zweiten Bestandteil unseres Champions.

VERGLEICH

=VERGLEICH(SUCHKRITERIUM;SUCHMATRIX;VERGLEICHSTYP)

Die Vergleich Funktion beantwortet mir die Frage: an welcher Position finde ich mein Suchkriterium. Zum Beispiel: In welcher Zeilennummer finde ich den Betrag 12?

Der Vergleichstyp ist hierbei optional. Hier kann ich zwischen 3 Einstellungen wählen:

0 = genaue Übereinstimmung (Standardwert, wenn ich den Vergleichstyp nicht angebe)

-1= Größer als

1= Kleiner als

Ähnlich wie die Index Funktion, ist der Vergleich ziemlich easy, jedoch bietet sie auch kaum Anwendungsmöglichkeiten. 

Ganz anders verhält es sich wenn sich diese beiden schlichten Formeln zu unseren Champion formieren:

INDEX / VERGLEICH

Wer den SVerweis mag, wird Index/Vergleich lieben. Nicht umsonst ist diese Kombi unser Champion geworden. Was ist denn der Vorteil gegenüber dem SVerweis? Ganz einfach: Der Sverweis  sucht die Zeile nur von links nach rechts ab (außer man verwurstelt die Formel mit der Wahl - Formel). Daher muss das Suchkriterium immer links vom gesuchten Wert stehen. Index/Vergleich hingegen ist völlig flexibel und dem Champ ist es völlig egal, wo das Suchkriterium steht. 

Man setzt die Index/Vergleich Kombi bei der Suche eines Wertes anhand eines Suchkriteriums ein. Somit ist Index/Vergleich dein persönlicher Detektiv in allen Excel Lebenslagen.

=INDEX(MATRIX; VERGLEICH(SUCHKRITERIUM; SUCHMATRIX;VERGLEICHSTYP).

In diesem Beispiel ist mein Suchkriterium "12" und mein gesuchter Wert ist der dazugehörige Name. In normalen Worten lautet die Formel:

Index(Matrix mit dem gewünschten Ergebnis; Vergleich(Suchkriterium; Matrix mit dem Suchkriterium;0)

SVerweis Freunde werden direkt bemerkt haben, dass wir hier tatsächlich von rechts nach links suchen :)

Ich muss zugeben, dass meine Formeln fast immer ein "Wenn" beinhalten, da gerade bei der Erstellung von Excel Vorlagen und Berechnungsszenarien immer mehrere Bedingungen erfüllt sein müssen. Kurz gesagt, kann man die Wenn Formel so beschreiben:

WENN  -  DANN -  SONST

=WENN(WAHRHEITSTEST;DANN;SONST)

In diesem Beispiel ist

WAHRHEITSTEST : Gesamtbetrag > Schuldengrenze

DANN: "Schlägertruppe"

SONST: "Entspanne" (tritt ein wenn der Gesamtbetrag < Schuldengrenze ist)

Das schöne bei der WENN Formel ist, das wir bis zu 8 Wenn Formeln miteinander verschachteln können. Bei 3 Bedingungen sieht es dann z.B. folgendermaßen aus:

=WENN(Wahrheitswert1;DANN;Wenn(Wahrheitswert2;DANN;Wenn(Wahrheitswert3;DANN;SONST)))

SUMMENPRODUKT ist so vielfältig einsetzbar, dass diese paar Zeilen ihm eigentlich nicht gerecht werden. Ich versuche daher zumindest 2 Versionen und Einsatzmöglichkeiten zu erläutern.

Zunächst einmal die Frage: Was genau macht denn SUMMENPRODUKT? Grundsätzlich sagt der Name schon alles aus. Mithilfe der einfachen Version der Formel werden Werte miteinander multipliziert und zum Schluss hiervon die Summe gezogen. Klingt erstmal banal, erspart dir jedoch viel Aufwand.

=SUMMENPRODUKT(ARRAY1;ARRAY2;ARRAY3......)

Arrays stehen hierbei für die Zellbereiche, die in die Berechnung einfließen sollen. Du kannst bis zu 255 dieser Bereiche angeben. Wenn du nur einen Bereich auswählst, wird ganz normal die Summe hiervon berechnet. Jedoch genug Theorie. Starten wir mit einem Beispiel für die einfach Anwendung der SUMMENPRODUKT Formel:

Im Beispiel möchte ich anhand der beiden Werte "noch offen" und "Ausfall-Risiko" das gesamte Ausfallrisiko berechnen. Um auf das Ergebnis zu kommen, kann ich auch jeweils die beiden Spalten mulitplizieren (Spalte M) und danach die Summe ziehen (M14). Diese zusätzliche Spalte kann ich mir mit SUMMENPRODUKT sparen. 

"noch offen" = Array 1      "Ausfall-Risiko" = Array 2

Nun möchte ich die Formel noch ein wenig anreichern. Hierzu erweitere ich die Tabelle, um den Wert "Gesamtbetrag" (Spalte H).

In diesem Beispiel möchte ich nur das Ausfallrisiko für Gesamtbeträge haben, die > 10.000 sind. Wie im ersten Beispiel habe ich zur Prüfung in der Spalte M eine manuelle Rechnung eingefügt mit der WENN Funktion. 

Wie du sehen kannst, wird durch den 3. Array eine Bedingung eingefügt (Gesamtbetrag > 10.000) und du sparst dir die manuelle Berechnung über die WENN Formel.

Naja..was soll ich sagen: einfach aber nützlich. Ich erspare dir jetzt einfach mal die Formel hierzu :D

Mit BEREICH.VERSCHIEBEN sorgst du für maximale Dynamik in deiner Excel Datei - egal ob für Dropdown Felder oder als Datenbasis für Diagramme, hier kannst du dich richtig austoben. 

=BEREICH:VERSCHIEBEN(BEZUG;ZEILEN;SPALTEN;HÖHE;BREITE) 

Nehmen wir die Funktion mal auseinander:

Bezug: die Zelle, die du als Startpunkt festlegst. Von hier aus, beginnt die Verschieberei 

Zeilen: um wie viele Zeilen soll deine Startzelle verschoben werden?

Spalten: um wie viele Spalten, soll der Bereich verschoben werden?

Höhe: Wie viele Zeilen, soll der neue Bereich beinhalten

Breite: Wie viele Spalten soll der neue Bereich beinhalten

Im Beispiel können wir nun sehen, wie ich die BEREICH.VERSCHIEBEN Funktion mit Leben fülle. Ich möchte hier anhand der beiden Navigatoren "ID" und "Kennzahl" jeweils den dazugehörigen Wert ermitteln. 

=BEREICH:VERSCHIEBEN(BEZUG;ZEILEN;SPALTEN;HÖHE;BREITE) 

Der Bezug (D7) liegt eine Position über der ersten ID.

Die Angabe der Zeilen erfolgt über die Auswahl der ID. Die ID gibt hier passenderweise an, um wie viele Zeilen ich vom Startpunkt (D7) schieben möchte. Hier: ID=5 , somit 5 Zeilen unter dem Startpunkt.

Bei der Angabe der Spalten habe ich auch die WENN Formel zurück gegriffen (hätte auch mit Vergleich funktioniert). Ich habe jeder Kennzahl einen Wert zugeordnet, der zu der relativen Spaltenposition zum Startpunkt passt;

Gesamtbetrag =1 

noch offen = 2

Ausfallrisiko = 3

Wenn ich also nun die gewünschte Kennzahl ändere, wird die Spaltenangabe in meine BEREICH.VERSCHIEBEN Funktion angepasst.

Da ich nur einen Wert ermitteln möchte, beträgt meine Höhe und Breite = 1.

Mit diesen Einstellungen kann ich nun wild durch meine Datensätze navigieren:

Lade kostenlos die Excel Vorlage herunter, um zu üben, nachzuvollziehen oder zu kopieren. 

So sieht das ganze dann aus:

Das war mit Abstand der längste Artikel, den ich bislang gepostet habe, jedoch war es mir wichtig, dieses Thema umfassend zu bearbeiten. Wir haben unsere TOP 5 Excel Formeln gefunden und ich bin neugierig darauf, ob dieses Ranking auch auf dich zutrifft. Ich freue mich auf dein Feedback oder deine Kritik. 

Kommen wir abschließend wieder zu meinem Gleichnis. Ich bin sicher, dass wir mit unseren TOP 5 in einem Land, das nur Excel spricht, gut überleben könnten :D

• Die 10 Excel Gebote
• 5 Excel Tricks für einen frühen Feierabend
• Der Kostenstellenbericht

Excel Formeln   Excel Funktionen   TOP5 Excel Formeln   INDEX/VERGLEICH  Summenprodukt   WENN-Formel   SUMME  Bereich.verschieben   Formelübersicht   Wichtige Formeln  Excel Tabelle Excel 2016