Spickzettel für Excel-Formeln: Leitfaden für Fortgeschrittene

Sie haben es also geschafft, die Grundlagen von Excel zu beherrschen – großes Lob an Sie! Jetzt haben Sie sich entschieden, noch einen Schritt weiter zu gehen und sich mit den fortgeschritteneren Dingen zu befassen, oder? Nun, Sie haben Glück, denn wir haben einen praktischen Excel-Spickzettel für Fortgeschrittene speziell für Sie zusammengestellt!

Dieser Spickzettel für Fortgeschrittene in Microsoft Excel deckt eine breite Palette von Themen ab, die Ihre Fähigkeiten in numerischer Analyse, Textverarbeitung und komplexer Datums- und Zeitlogik verbessern.

Mit dem Excel Intermediate Spickzettel als Desktop-Referenz können Sie tief in die leistungsstärkeren Formeln und Funktionen von Microsoft Excel eintauchen.

Lesen Sie jetzt unbedingt weiter, drucken Sie aber auch den Spickzettel unten aus und bewahren Sie ihn auf!

Lass uns anfangen!

Excel-Formeln-Cheat-Sheet-Intermediate-1 Herunterladen

Inhaltsverzeichnis

• Mittlere mathematische Formeln
  • Bedingte Logik
• Mittlere statistische Formeln
  • 1. MINA und MAXA
  • 2. COUNTA und COUNTIF
• Excel-Formeln für die Finanzanalyse
  • 1. PRODUKT-Funktion
  • 2. QUOTIENT- und LOG-Funktionen
  • Funktionen für Investitionsszenarien
• Fortgeschrittene Excel-Datums- und Uhrzeitformeln
  • 1. Zwischenmonatsfunktionen
  • 2. Funktionen der Zwischenwoche
  • 3. Zwischenzeitliche Funktionen
• Excel-Funktionen für bedingte und logische Algebra
  • 1. IF-Funktion
  • 2. UND-Funktion
  • 3. ODER-Funktion
  • 4. NICHT funktionsfähig
• Referenzieren von Zellen
  • 1. INDIREKTE Funktion
  • 2. INDEX-Funktion
  • 3. OFFSET-Funktion
• Textformeln für Fortgeschrittene
  • 1. TEXTJOIN-Funktion
  • 2. REPLACE-Funktion
  • 3. SUBSTITUTE-Funktion
• Mittlere Suchformeln
• Formeln zur Behandlung von Microsoft Excel-Fehlern
  • 1. IFERROR-Funktion
  • 2. IFNA-Funktion
• Formel- und Funktionsverknüpfungen
• Mittlere Zellformatierung
• Abschließende Gedanken

Mittlere mathematische Formeln

Unser Einsteiger-Spickzettel deckt die gängigsten Mathematikfunktionen für Einsteiger ab, beispielsweise die SUMME-Funktion. Fortgeschrittene Benutzer sollten sich mit diesen komplexeren Funktionen vertraut machen:

• ABS() gibt den absoluten Wert einer Zahl zurück.

• SQRT() gibt die Quadratwurzel einer Zahl zurück

• RAND() liefert eine Zufallszahl zwischen 0 und 1

Bedingte Logik

Fortgeschrittene Benutzer sollten auch wissen, wie sie mithilfe dieser Funktionen mathematische Berechnungen auf der Grundlage mehrerer Bedingungen durchführen können:

• SUMIFS(sum_range, Kriterien_Bereich1, Kriterien1, [….])

• COUNTIFS(Kriterien_Bereich1, Kriterien1, [Kriterien_Bereich2, Kriterien2], …)

• DURCHSCHNITTWENN(Kriterien_Bereich1, Kriterien1, [Kriterien_Bereich2, Kriterien2], …)

Die Funktionen werten die bereitgestellten Kriterien aus und erzeugen einen logischen Wert von TRUE oder FALSE.

Nehmen wir an, Sie haben die folgenden Verkaufsdaten in den Spalten A, B und C:

Um die Summe der Verkäufe für rote T-Shirts zu berechnen, verwenden Sie die SUMIFS-Formel:

=SUMIFS(C2:C6, A2:A6, „T-Shirt“, B2:B6, „Rot“)

Um die Anzahl der Zeilen mit roten T-Shirts zu zählen, verwenden Sie die ZÄHLENWENN-Formel:

=COUNTIFS(C2:C6, A2:A6, „T-Shirt“, B2:B6, „Rot“)

Um den durchschnittlichen Umsatz für rote T-Shirts zu berechnen, verwenden Sie die AVERAGEIFS-Formel:

=AVERAGEIFS(C2:C6, A2:A6, „T-Shirt“, B2:B6, „Rot“)

Mittlere statistische Formeln

Neben Grundfunktionen wie MIN und MAX verfügt Excel über eine größere Auswahl an Statistikformeln für fortgeschrittene Benutzer. Hier sind einige der nützlichsten:

• MINA

• MAXA

• COUNTA

• ZÄHLENWENN

1. MINA und MAXA

Die häufiger verwendeten MIN- und MAX-Funktionen ignorieren Textwerte.

Die alternativen Funktionen MINA und MAXA berücksichtigen bei der Suche nach den höchsten oder niedrigsten Werten sowohl Text als auch Zahlen. Textwerte werden so ausgewertet, als wären sie Null.

Die Unterschiede sind im Bild unten dargestellt. Die erste Zeile wertet einen Mindestwert von 10 aus, während die zweite Zeile aufgrund des Vorhandenseins eines Textwerts den Wert 0 ergibt.

2. COUNTA und COUNTIF

Mit der Funktion COUNTA wird die Anzahl der nicht leeren Zellen in einem Bereich gezählt.

Die ZÄHLENWENN-Funktion ist insofern weniger spezifisch, als sie dazu verwendet wird, die Anzahl der Zellen innerhalb eines Bereichs zu zählen, die eine bestimmte Bedingung oder ein bestimmtes Kriterium erfüllen.

Sie können diese Funktionen auch verwenden, um die Anzahl unterschiedlicher Werte in einer Spalte zu zählen. Dieses Video zeigt, wie das geht.

Excel-Formeln für die Finanzanalyse

Es gibt mehrere Formeln, die Sie bei der Durchführung von Finanzanalysen wie der Prognose von Investitionen verwenden sollten:

• PRODUKT(Nummer1, [Nummer2…])

• QUOTIENT(Zähler, Nenner)

• LOG(Zahl, [Basis])

1. PRODUKT-Funktion

Angenommen, Sie möchten den zukünftigen Wert einer Anfangsinvestition von 1.000 USD (Zelle B1) bei einem jährlichen Zinssatz von 4 % (Zelle B2) nach fünf Jahren (Zelle B3) berechnen.

Verwenden Sie die PRODUCT-Funktion, um den zukünftigen Wert mit dieser Formel zu berechnen:

=B1 * PRODUKT(1 + B2)^B3

Diese Formel gibt den zukünftigen Wert Ihrer Investition zurück.

2. QUOTIENT- und LOG-Funktionen

Der nächste Schritt besteht darin, zu berechnen, wie viele Jahre es dauern wird, bis sich der Wert einer Investition bei dem gegebenen Zinssatz verdoppelt.

Verwenden Sie die QUOTIENT-Funktion in Kombination mit der LOG-Funktion, um dies wie folgt zu berechnen:

=QUOTIENT(LOG(2) / LOG(1 + B2), 1)

Diese Formel gibt mit den Beispieldaten 17 zurück, was bedeutet, dass es 17 Jahre dauern wird, bis sich der Wert der Investition bei einem jährlichen Zinssatz von 4 % verdoppelt.

Dieses Bild zeigt die Formeln in einem Excel-Arbeitsblatt :

Funktionen für Investitionsszenarien

Dies sind einige Funktionen, die für bestimmte Finanzszenarien geeignet sind. Eine davon könnte genau das sein, was Sie brauchen:

• NPV – Die NPV- Funktion (Net Present Value) berechnet den Nettobarwert einer Investition auf der Grundlage einer Reihe zukünftiger Cashflows basierend auf einem Abzinsungssatz.

• ACCRINT – Die ACCRINT- Funktion berechnet die aufgelaufenen Zinsen eines Wertpapiers, das regelmäßig Zinsen zahlt. Dies ist nützlich, um die Zinsen zu ermitteln, die für ein Wertpapier seit dem letzten Zahlungsdatum bis zu einem bestimmten Abrechnungsdatum erzielt wurden.

• INTRATE – Die INTRATE- Funktion berechnet den Zinssatz für ein vollständig investiertes Wertpapier.

• PMT – Die PMT- Funktion berechnet die Gesamtzahlung für eine Schuldverschreibung.

• IRR – Die IRR- Funktion liefert den internen Zinssatz einer Rendite.

• RENDITE – Die RENDITE- Funktion liefert die Rendite eines Wertpapiers basierend auf Zinssatz, Nennwert und Laufzeit.

Fortgeschrittene Excel-Datums- und Uhrzeitformeln

Zu den grundlegenden Datums- und Uhrzeitfunktionen in Excel gehören die Funktionen JETZT und HEUTE für das aktuelle Datum. Fortgeschrittene Benutzer sollten auch wissen, wie sie Komponenten aus einem bestimmten Datum extrahieren können, indem sie Folgendes verwenden:

• TAG(Datum)

• Monatsdatum)

• JAHR(Datum)

Die Formel =MONTH(“23. April 2023″) gibt für den 4. Monat ein Ergebnis von 4 zurück. Ebenso geben die Funktionen DAY und YEAR 23 bzw. 2023 zurück.

1. Zwischenmonatsfunktionen

Fortgeschrittene Benutzer müssen manchmal Monate zu einem Datum addieren oder subtrahieren und das Ende eines Monats ermitteln.

• EDATE(start_date, number_of_months)

• EOMONTH(Startdatum, Anzahl_Monate)

Beispielsweise berechnet die Formel =EDATE(„23. April 2023“, 2) das Datum zwei Monate später.

Die Formel =EOMONTH(23. April 2023“, 2) berechnet das Monatsende zwei Monate später.

Das Ergebnis lautet „30. Juni 2023“, wobei berücksichtigt wird, dass der Juni nur dreißig Tage hat. Wenn Sie 3 Monate angeben, lautet das Ergebnis „31. Juli 2023“.

2. Funktionen der Zwischenwoche

Sie können eine Kombination der Funktionen SUM und WEEKDAY verwenden, um die Anzahl ganzer Arbeitstage innerhalb eines Datumsbereichs zu zählen.

Die WEEKDAY-Funktion gibt den Wochentag für ein bestimmtes Datum zurück. Der zweite Parameter bestimmt das Nummerierungssystem, wobei der Standardwert Sonntag bis Samstag ist.

Wenn Sie eine Reihe von Datumsangaben in einem Zellbereich von B1:B40 haben, verwenden Sie diese Formel, um die Arbeitstage zu berechnen:

=SUMME(–(WOCHENTAG(B1:B40,2)>5))

3. Zwischenzeitliche Funktionen

Die YEARFRAC-Funktion berechnet den Bruchteil eines Jahres zwischen zwei Daten.

Die Funktion TIMEVALUE wird verwendet, um eine als Text dargestellte Zeit in eine Dezimalzahl umzuwandeln, die den Anteil eines 24-Stunden-Tages darstellt.

Das Ergebnis ist eine Excel-Seriennummer, wobei 1 für einen vollen 24-Stunden-Tag steht, 0,5 für 12 Stunden, 0,25 für 6 Stunden und so weiter.

Angenommen, Sie haben in Zelle A1 eine Uhrzeit im Textformat „17:45“ und möchten diese in eine Dezimalzahl umwandeln . Dies ist die Formel:

=ZEITWERT(A1)

Der Wert 0,74 stellt den Anteil des 24-Stunden-Tages dar, der zum Zeitpunkt fünfzehn Minuten vor sechs Uhr vergangen ist.

Mit können Sie die Zeitzelle von einem Zeitformat in ein Textformat ändern. Zur besseren Übersicht können Sie dies als Prozentsatz formatieren.

Excel-Funktionen für bedingte und logische Algebra

Bedingte und logische Funktionen sind für die Entscheidungsfindung in Excel unerlässlich. Die folgenden Funktionen können mit den einfacheren Excel-Formeln für leistungsstarke Logik kombiniert werden:

1. IF(condition, value_if_true, value_if_false):

2. UND(Bedingung1, Bedingung2, …)

3. ODER(Bedingung1, Bedingung2, …)

4. NICHT(Bedingung1, Bedingung2, …)

Die IF-Funktion wertet eine Bedingung aus und gibt unterschiedliche Werte zurück, je nachdem, ob die Bedingung wahr oder falsch ist.

Die Funktionen AND, OR und NOT prüfen, welche Bedingungen wahr sind, und ermöglichen Ihnen, entsprechende Entscheidungen zu treffen.

Hier sind einige Beispiele, die auf den Verkaufsdaten basieren, die wir zuvor verwendet haben.

1. IF-Funktion

Angenommen, Sie möchten den Text „Niedrige Verkäufe“ anzeigen, wenn die Verkäufe für die Artikel weniger als 15 betragen. Wenn die Verkäufe höher sind, möchten Sie „Hohe Verkäufe“ in einer neuen Spalte anzeigen.

Verwenden Sie diese Formel und kopieren Sie sie in die anderen Zeilen:

=WENN(C2<15, „Geringer Umsatz“, „Hoher Umsatz“)

2. UND-Funktion

Angenommen, Sie möchten für T-Shirts mit mindestens 15 Verkäufen WAHR oder FALSCH anzeigen. Verwenden Sie diese Formel:

=AND(A3=“T-Shirt“, C3>=15)

3. ODER-Funktion

Angenommen, Sie möchten mehrere Bedingungen testen und TRUE zurückgeben, wenn eine der Bedingungen erfüllt ist, andernfalls FALSE. Dann würden Sie die OR-Funktion verwenden.

Wenn wir beispielsweise die Ergebnisse eines Tests in den Zellen A1 und B1 haben und wissen möchten, ob einer der Punkte über 80 liegt, könnten wir Folgendes verwenden: =OR( A1>80, B1>80) . Dies gibt TRUE zurück, wenn einer der Punkte (oder beide) über 80 liegt, und FALSE, wenn beide 80 oder weniger betragen.

4. NICHT funktionsfähig

Angenommen, Sie möchten überprüfen, ob es sich bei dem Artikel nicht um einen Kapuzenpullover handelt. Verwenden Sie diese Formel:

=NICHT(A2=“Hoodie“)

Referenzieren von Zellen

Ein Anfänger wird sich schnell mit der Verwendung absoluter und relativer Bezüge wie $A$1 oder B2 vertraut machen . Fortgeschrittene Benutzer sollten sich mit der Verwendung indirekter Referenzen, Indizes und Offsets vertraut machen:

1. INDIREKT(ref_text)

2. INDEX(Bereich, Zeilennummer, Spaltennummer)

3. OFFSET(Referenzzelle, Zeilen, Spalten):

1. INDIREKTE Funktion

Die Funktion INDIRECT gibt den Wert des angegebenen Zellbezugs zurück, der als Text eingegeben wurde. Um auf Zelle B3 zu verweisen, verwenden Sie diese Formel:

=INDIREKT(“B3”)

Der Vorteil gegenüber der Verwendung einfacher Zellbezüge besteht darin, dass Sie mit dieser Funktion einen dynamischen Bezug erhalten. Dies bedeutet, dass Excel die Referenz automatisch aktualisiert, wenn sich die Struktur der Tabelle ändert, z. B. wenn eine Zeile gelöscht wird.

2. INDEX-Funktion

Die INDEX-Funktion wird verwendet, um Zellen innerhalb eines angegebenen Bereichs basierend auf Zeilen- und Spaltennummern zu referenzieren.

Um Zelle B3 in einer Tabelle mit sechs Zeilen zu referenzieren, verwenden Sie diese Formel:

=INDEX(B1:B6, 3)

3. OFFSET-Funktion

Die OFFSET-Funktion gibt eine Zelle oder einen Bereich zurück, die bzw. der eine angegebene Anzahl von Zeilen und Spalten von einer Referenzzelle entfernt ist.

Wenn Sie den Zellwert anzeigen möchten, der zwei Zellen quer und eine Zelle tiefer von A1 liegt, verwenden Sie diese Formel:

=OFFSET(A1, 2, 1)

Hier sind die Formeln in Aktion:

Textformeln für Fortgeschrittene

Anfänger sollten Textfunktionen wie LEFT kennen, die ein oder mehrere Zeichen aus der linken Seite einer Zeichenfolge extrahieren. Ein Power-User sollte mit Funktionen wie diesen vertraut sein:

1. TEXTJOIN(Trennzeichen,ignore_empty, text1, [text2, …])

2. REPLACE(old_text, start_num, num_chars, new_text)

3. SUBSTITUTE(text, old_text, new_text, [instance_num])

1. TEXTJOIN-Funktion

Die TEXTJOIN-Funktion verkettet Zellen mit dem von Ihnen angegebenen Trennzeichen. Sie können auch angeben, wo leere Zellen ignoriert werden sollen.

Um eine durch Kommas getrennte Liste einiger Elemente in Spalte A zu erstellen, verwenden Sie diese Formel:

=TEXTJOIN(“, „, TRUE, A2:A4)

2. REPLACE-Funktion

Mit der Ersetzungsfunktion können Sie die Startposition und Länge der Zeichenfolge angeben, die Sie in das Ziel ersetzen möchten.

Um die ersten beiden Zeichen einer Zeichenfolge durch „XX“ zu ersetzen, verwenden Sie diese Formel:

=TEXTJOIN(A2, 1, 2, „XX“)

3. SUBSTITUTE-Funktion

Die Ersatzfunktion ersetzt bestimmte Vorkommen einer Textzeichenfolge innerhalb einer anderen Textzeichenfolge durch eine neue Textzeichenfolge.

Um Vorkommen des Wortes „Jeans“ durch „Leggings“ zu ersetzen, verwenden Sie diese Formel:

=SUBSTITUTE(A3, „Jeans“, „Leggings“)

Hier sind die Formeln in Aktion:

Mittlere Suchformeln

Gängige Suchfunktionen wie VLOOKUP und MATCH werden im Spickzettel für Anfänger behandelt.

Fortgeschrittene Benutzer sollten sich mit der CHOOSE-Suchfunktion vertraut machen:

• CHOOSE(index_num, value1, [value2, …])

Die CHOOSE-Funktion gibt einen Wert aus einer Werteliste basierend auf einer angegebenen Indexnummer zurück.

Angenommen, Sie haben eine Spalte mit T-Shirt-Größen, die mit 1, 2 oder 3 beschriftet sind. Sie möchten jeweils eine Kategorie von klein, mittel und groß in derselben Zeile anzeigen. Verwenden Sie diese Formel:

=CHOOSE(A1, „Klein“, „Mittel“, „Groß“)

Formeln zur Behandlung von Microsoft Excel-Fehlern

Im Cheatsheet für Anfänger sind die typischen Fehlermeldungen aufgeführt, die bei der Arbeit mit Excel auftreten. Fortgeschrittene Benutzer sollten in der Lage sein, Formeln zu verwenden, um die Fehler elegant zu behandeln.

1. IFERROR(Wert, if_error)

2. IFNA(Wert, value_if_na)

1. IFERROR-Funktion

Fehler bei der Division durch Null sind häufig, aber die Standardanzeige von Excel lautet #DIV/0! wird nicht jedem Benutzer klar sein.

Verwenden Sie die IFERROR-Funktion, um den Standardfehler durch „Kann nicht durch Null dividieren“ mit dieser Formel zu ersetzen:

=IFERROR(A1/B1, „Kann nicht durch Null dividieren“)

Dieses Bild zeigt den Rohfehler in der dritten Zeile und die IFERROR-Funktion, die die vierte Zeile verarbeitet.

2. IFNA-Funktion

Die IFNA-Funktion in Excel wird zum Erkennen und Behandeln von #N/A-Fehlern verwendet.

Ein anderer Abschnitt dieses Spickzettels zeigte die CHOOSE-Funktion. Diese Funktion gibt einen N/A-Fehler zurück, wenn die Indexnummer keine Ganzzahl ist.

Angenommen, eine T-Shirt-Größe wurde mit 1,5 eingegeben, wobei der Benutzer fälschlicherweise annimmt, dass hier eine Kategorie irgendwo zwischen klein und mittel angezeigt wird. Stattdessen wird ein #N/A -Fehler angezeigt.

Um einen hilfreicheren Fehler bereitzustellen, verwenden Sie diese Formel:

=IFNA(CHOOSE(A2, „Niedrig“, „Mittel“, „Hoch“), „Ungültiger Index“)

Formel- und Funktionsverknüpfungen

Es gibt mehrere Tastaturkürzel, mit denen Sie effizienter mit Ihrem Arbeitsblatt arbeiten können.

• F2 : Bearbeiten Sie die aktive Zelle und positionieren Sie die Einfügemarke am Ende des Zellinhalts.

• F9 : Berechnen Sie das Ergebnis des ausgewählten Teils einer Formel und zeigen Sie es an.

• Strg + Umschalt + Eingabetaste : Geben Sie eine Array-Formel ein.

• Umschalt + F3 : Öffnen Sie das Dialogfeld „Funktion einfügen“.

• Esc : Brechen Sie die Eingabe einer Formel ab und kehren Sie zum ursprünglichen Zelleninhalt zurück.

Einige dieser Excel-Tastenkombinationen sind möglicherweise nicht in allen Sprachen oder Tastaturlayouts verfügbar.

Mittlere Zellformatierung

Bei der Finanzanalyse ist die richtige Formatierung von Zellen mit Zahlen, Datumsangaben und Währungen von entscheidender Bedeutung. Um Zellen zu formatieren, gehen Sie folgendermaßen vor:

1. Wählen Sie die Zellen aus, die Sie formatieren möchten.

2. Klicken Sie mit der rechten Maustaste auf die ausgewählten Zellen und wählen Sie „Zellen formatieren“.

3. Wählen Sie die entsprechende Kategorie (z. B. Zahl, Währung, Datum) und wenden Sie das gewünschte Format an.

Einige gängige Formatierungsarten, die im Finanzwesen berücksichtigt werden sollten, sind:

• Währung : 1.234,56 $

• Prozentsatz : 12,34 %

• Buchhaltung : (1.234,00 $)

• Datum : 10. Mai 2023

Abschließende Gedanken

Je vertrauter Sie mit den verschiedenen in diesem Spickzettel hervorgehobenen Formeln und Funktionen werden, desto besser sind Sie für die Bewältigung immer komplexerer Aufgaben und Projekte gerüstet.

Übung ist der Schlüssel zur Beherrschung jeder Fertigkeit. Scheuen Sie sich also nicht, in Ihrer täglichen Arbeit mit diesen Formeln zu experimentieren. Sie vermitteln Ihr Fachwissen vom Anfängerniveau bis zum Mittelniveau.

Dies ist nur ein Vorgeschmack darauf, was die Zwischenfunktionen von Excel tun können, um Ihre Arbeit zu vereinfachen, Ihre Analysen zu optimieren und Ihnen dabei zu helfen, Erkenntnisse aus Ihren Daten zu gewinnen.

Die wahre Magie entsteht, wenn Sie beginnen, die Funktionen in unserem Spickzettel zu kombinieren und sie an Ihre individuellen Bedürfnisse anzupassen. Also hören Sie hier nicht auf. Entdecken Sie weiter, experimentieren Sie weiter, und Sie werden feststellen, dass Excel nicht nur ein Werkzeug ist, sondern das Spiel verändert!

Haben Sie Lust, die Dinge auf die nächste Stufe zu bringen? Sehen Sie sich unseren Spickzettel für erweiterte Excel-Formeln an.