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

Sie haben sich also mit den Grundlagen befasst, die mittelschweren Dinge in Angriff genommen und sind nun bereit, sich mit den großen Geschützen herumzuschlagen – den erweiterten Formeln von Excel! Excel verfügt über viele erweiterte Funktionen und Formeln für anspruchsvolle Berechnungen. Daher ist es hilfreich, einen Leitfaden zu haben, der genau die Funktionen und Formeln enthält, die Sie benötigen.

Dieser Spickzettel für Excel-Formeln behandelt erweiterte Prognoseformeln, statistische Analysen, Datenmanipulationsfunktionen, Fehlerbehandlung und mehr.

Diese Referenz vermittelt Ihnen das Wissen zur Verwendung dieser erweiterten Funktionen. Jede Formel wird von klaren Erklärungen, Syntax und praktischen Beispielen begleitet, um fortgeschrittenen Excel-Benutzern zu fortgeschrittenen Power-Benutzern zu verhelfen.

Bitte laden Sie den Spickzettel herunter, drucken Sie ihn aus und halten Sie ihn griffbereit.

Ok, fangen wir an.

Excel-Formeln-Cheat-Sheet-Advanced-1 herunterladen

Kommen wir zunächst zu den Array-Formeln.

Inhaltsverzeichnis

• Array-Formeln
  • 1. EINZIGARTIGE Funktion
  • 2. SORT-Funktion
  • 3. FILTER-Funktion
• Randomisieren von Excel-Funktionen
  • 1. RANDBETWEEN-Funktion
  • 2. RANDARRAY-Funktion
• Erweiterte Prognoseformeln in Microsoft Excel
  • 1. FORECAST.ETS-Funktion
  • 2. TREND-Funktion
• Erweiterte statistische Formeln
  • 1. PERCENTIL-Funktion
  • 2. QUARTILE-Funktion
• Erweiterte Formeln für die Datenanalyse und -manipulation
  • 1. TRANSPOSE-Funktion
  • 2. FREQUENCY-Funktion
  • 3. GETPIVOTDATA-Funktion
• Erweiterte Fehlerbehandlung
• Erweiterte Suchformeln
  • 1. XLOOKUP-Funktion
  • 2. XMATCH-Funktion
• Abschließende Gedanken

Array-Formeln

Unser Einsteiger-Spickzettel zeigt Ihnen, wie Sie Ihre Daten manuell sortieren und filtern. Fortgeschrittene Benutzer erledigen dies programmgesteuert mit Array-Formeln.

Mit Array-Formeln können Sie Berechnungen für mehrere Zellen gleichzeitig durchführen. Dies sind drei Schlüsselfunktionen:

1. EINZIGARTIG

2. SORTIEREN

3. FILTER

Einige dieser Funktionen sind nur in den neuesten Versionen von Microsoft Excel verfügbar.

1. EINZIGARTIGE Funktion

Die UNIQUE-Funktion akzeptiert einen Bereich und gibt eine Liste eindeutiger Werte zurück.

Angenommen, Sie verfügen über Verkaufsdaten für Bekleidungsartikel. Um die eindeutigen Elemente in Spalte A zu finden, verwenden Sie diese Formel:

=EINZIGARTIG(A2:A6)

2. SORT-Funktion

Die SORT-Funktion sortiert den Inhalt eines Bereichs. Die Syntax lautet:

SORT(array, [sort_index], [sort_order], [by_col])

• Array : der zu sortierende Wertebereich.

• sort_index : die zu sortierende Spalte (standardmäßig 1)

• sort_order : 1 für aufsteigend (Standard) oder 2 für absteigend.

• by_col : TRUE zum Sortieren nach Spalte (Standard) oder FALSE zum Sortieren nach Zeile.

Die letzten drei Argumente sind optional und die Standardwerte entsprechen normalerweise Ihren Wünschen.

Um die Beispieldaten nach der ersten Spalte zu sortieren, verwenden Sie diese Formel:

=SORTIEREN(A2:C6)

3. FILTER-Funktion

Mit der FILTER-Funktion können Sie einen Bereich nach einer bestimmten Bedingung filtern. Dies ist die Syntax:

=FILTER(array, include, [if_empty])

• Array : der zu filternde Bereich.

• include : die Bedingung, die bestimmt, welche Werte gefiltert werden sollen.

• if_empty : Gibt an, was zurückgegeben werden soll, wenn keine Werte die Filterkriterien erfüllen (Standard ist „“).

Angenommen, Sie möchten die Zeilen in den Beispieldaten filtern, um nur anzuzeigen, wo der Verkaufswert größer als 15 $ ist. Verwenden Sie diese Formel:

=FILTER(A2:C6, C2:C6>15)

Randomisieren von Excel-Funktionen

Unser Zwischen-Cheat zeigt, wie man die RAND-Funktion verwendet, die eine Zufallszahl zwischen 0 und 1 erzeugt.

Fortgeschrittene Excel-Benutzer wissen, wie sie die Zufallsfunktionen nutzen, um schnell Beispieldaten zu generieren.

1. RANDBETWEEN-Funktion

Die Funktion RANDBETWEEN ist flexibler als RAND, da Sie die unteren und oberen Zahlen als etwas anderes als 0 und 1 angeben können.

Um Daten mit Zahlen zwischen 1 und 100 zu generieren, geben Sie diese Formel in Zelle A1 ein:

=RANDBETWEEN(1, 100)

Kopieren Sie dann die Zelle in beliebig viele Zeilen und Spalten. Es dauert Sekunden, ein Raster aus Zufallszahlen zu erstellen:

2. RANDARRAY-Funktion

Sie denken vielleicht, es wäre schön, das manuelle Kopieren der RANDBETWEEN-Funktion zu vermeiden. Um besonders fortgeschritten zu sein, können Sie die neue RANDARRAY-Funktion in der neuesten Version von Microsoft Excel verwenden.

Die Syntax lautet:

RANDARRAY([Zeilen], [Spalten], [Min.], [Max], [Ganzzahl])

• Reihen : Anzahl der Reihen

• Spalten : Anzahl der Spalten

• min : niedrigste Zahl

• max : höchste Zahl

• ganze Zahl : Der Standardwert ist TRUE, andernfalls werden Dezimalzahlen verwendet.

Verwenden Sie diese Formel, um Zufallszahlen zwischen 1 und 100 über sechs Zeilen und neun Spalten zu generieren:

=RANDARRAY(6, 9, 1, 100, TRUE)

Erweiterte Prognoseformeln in Microsoft Excel

Die Prognosefunktionen von Excel werden verwendet, um zukünftige Werte auf der Grundlage vorhandener Datentrends vorherzusagen. Diese Funktionen helfen dabei, anhand Ihrer Daten Muster und Projekttrends zu erkennen.

1. FORECAST.ETS-Funktion

Die ältere FORECAST-Funktion wurde in Excel 2016 durch eine Reihe neuerer Funktionen ersetzt.

Sie wählen die Funktion basierend auf dem gewünschten Prognosemodell aus. Beispielsweise verwendet die Funktion FORECAST.ETS den Exponential Smoothing -Algorithmus.

Die Syntax lautet:

FORECAST.ETS(Zieldatum, Werte, Zeitleiste)

• Zieldatum : das Datum, für das Sie einen berechneten Wert wünschen.

• Werte : die historischen Daten.

• Zeitleiste : eine Reihe von Daten

Angenommen, Sie haben Daten vom 1. bis 5. Januar in Spalte A und Verkaufsbeträge in Spalte B. Diese Formel sagt den nächsten Verkaufsbetrag voraus:

=FORECAST.ETS(“6. Januar 2023”, B2:B6, A2:A6)

2. TREND-Funktion

Die TREND-Funktion projiziert eine Reihe von Werten basierend auf der Methode der kleinsten Quadrate. Es gibt ein Array zurück. Die Syntax lautet:

TREND(known_y, [known_x], [new_x], [const])

• known_y : Bereich von y-Werten

• known_x : Bereich von x-Werten

• new_x : Bereich der berechneten Werte

Häufig handelt es sich bei „known_y“ um die Datenpunkte und bei „known_x“ um die Daten.

Mit denselben Daten wie im vorherigen Beispiel können Sie die folgende Formel in Zelle C2 eingeben. Es wird eine Reihe von Werten generiert.

=TREND(B2:B6, A2:A6)

Erweiterte statistische Formeln

Zu den erweiterten Statistikfunktionen gehört die Berechnung von Perzentilen und Quartilen. Aus Gründen der Abwärtskompatibilität sind einige mathematische Funktionen verfügbar, es wird jedoch empfohlen, die aktuellsten Versionen zu verwenden.

1. PERCENTIL-Funktion

Diese Funktion berechnet den Prozentsatz der Datenpunkte, die unter einen bestimmten Wert fallen. Die Syntax lautet:

PERCENTILE.INC(array, k)

• Array : der Zellbereich

• k : das Perzentil von 0 bis 1

Angenommen, Sie möchten das 70. Perzentil der Daten in Spalte B berechnen. Verwenden Sie diese Formel:

=PERZENTIL.INC(B2:B6, 0,7)

2. QUARTILE-Funktion

Diese Funktion ist eine Variation der Perzentilfunktion, verwendet jedoch Viertel, um die Daten zu teilen. Dies ist die Syntax:

QUARTILE.INC(array, quart)

• Array : der Datenbereich

• Quart : 1 für das 25. Perzentil, 2 für das 50., 3 für das 75. und 4 für das Maximum.

Die folgende Formel berechnet das erste Quartil der Daten in Spalte A.

=QUARTILE.INC(A2:A5, 1)

Erweiterte Formeln für die Datenanalyse und -manipulation

Mit mehreren erweiterten Funktionen können Sie das Datenformat ändern, Häufigkeitsverteilungen analysieren und Daten aus Pivot-Tabellen extrahieren.

1. TRANSPOSIEREN

2. FREQUENZ

3. GETPIVOTDATA

1. TRANSPOSE-Funktion

Manchmal möchten Sie die Daten in Ihren Zeilen in Spalten verschieben und umgekehrt. Sie können dies manuell tun oder stattdessen die TRANSPOSE-Funktion verwenden.

Angenommen, Sie haben die Artikel „T-Shirt“, „Hoodie“ und „Jeans“ in den Zellen A2, A3 und A4. Sie möchten diese in Spaltenüberschriften umwandeln. Diese Funktion gibt die Werte in einer einzelnen Zeile zurück:

=TRANSPOSIEREN(A2:A4)

2. FREQUENCY-Funktion

Diese Funktion berechnet die Häufigkeitsverteilung von Werten innerhalb eines Datensatzes. Dies ist die Syntax:

HÄUFIGKEIT(data_array, bins_array)

• data_array : Wertebereich.

• bins_array : die zu verwendenden Intervalle.

Angenommen, Sie haben Verkaufsdaten in Spalte B und möchten die Häufigkeitsverteilung der Werte basierend auf der Anzahl der Beträge analysieren:

• unter 20.

• von 20 bis 80.

• über 80.

Das stellt drei Klassen dar und kann mit dieser Formel berechnet werden:

=HÄUFIGKEIT(A2:A6, {20,80})

Weitere Informationen zu Häufigkeitsverteilungen in Excel finden Sie in diesem Video:

3. GETPIVOTDATA-Funktion

Mit dieser Funktion können Sie zusammengefasste Informationen aus Pivot-Tabellen extrahieren. Dies ist die Syntax:

GETPIVOTDATA(Datenfeld, Pivottabelle, [Feld1, Element1], [Feld2, Element2], …)

• data_field : das Datenfeld oder der Wert, den Sie aus der Pivot-Tabelle abrufen möchten.

• Pivot_table : ein Verweis auf die Pivot-Tabelle.

• Feld1, Element1 usw .: die Feld/Element-Paare, nach denen gefiltert werden soll.

Angenommen, Sie haben eine Pivot-Tabelle, die auf der Farbe der verkauften Artikel basiert. Um die Verkäufe für rote Artikel zu extrahieren, verwenden Sie diese Formel:

=GETPIVOTDATA(„Umsatz“, A1, „Farbe“, „Rot“)

Erweiterte Fehlerbehandlung

Selbst die einfachsten Excel-Formeln können zu Fehlern führen. Fortgeschrittene Benutzer sollten wissen, wie sie ISERROR zur Fehlerbehandlung verwenden. Fortgeschrittene Benutzer sollten außerdem mit der Funktion ERROR.TYPE zur Fehlererkennung vertraut sein.

Die Funktion ERROR.TYPE hilft dabei, den spezifischen Fehlertyp innerhalb einer Zelle oder Formel zu identifizieren.

Es gibt einen numerischen Wert zurück, der verschiedenen Fehlertypen entspricht, z. B. #N/A, #VALUE!, #REF! und mehr.

Angenommen, Sie haben einen Fehler in Zelle A1 und möchten den Fehlertyp identifizieren. Die folgende Formel gibt die Zahl zurück, die dem spezifischen Fehler entspricht:

=FEHLER.TYP(A1)

Sie können dies mit mehreren Funktionen kombinieren, um je nach Fehlerart unterschiedlich zu reagieren. Dies sind die häufigsten Fehler und ihre Bedeutung:

1. #NULL! (keine gemeinsame Zelle in einem Bereich gefunden)

2. #DIV/0! ( Division durch Null oder eine leere Zelle)

3. #WERT! (unpassender Datentyp oder Argument in einer Formel)

4. #REF! (Eine referenzierte Zelle wurde gelöscht oder es gibt einen Zirkelverweis )

5. #NAME? (Excel erkennt die Funktion oder den Bereich nicht)

6. #NUM! (ungültiger numerischer Wert)

7. #N/A (Wert kann nicht gefunden werden)

Angenommen, Sie möchten drei spezifische Fehlertypen behandeln. Verwenden Sie diese Formel, um eine spezifische Fehlermeldung basierend auf dem Typ anzuzeigen:

=IF(ISERROR(C2), IF(ERROR.TYPE(C2)=2, „Fehler Division durch Null“, IF(ERROR.TYPE(C2)=3, „Fehler durch ungültigen Wert“, IF(ERROR.TYPE(C2 )=7, „Fehler „Wert nicht gefunden“, „Anderer Fehler“))), C2)

Erweiterte Suchformeln

Unsere Spickzettel für Anfänger und Fortgeschrittene deckten eine Auswahl an Suchfunktionen ab . Hier sind einige erweiterte Optionen:

1. XLOOKUP

2. XMATCH

1. XLOOKUP-Funktion

Mit dieser Suchfunktion können Sie nach einem Wert in einem Bereich suchen und einen entsprechenden Wert aus einer anderen Spalte oder einem anderen Bereich zurückgeben.

Es bietet mehr Vielseitigkeit als einfachere Suchfunktionen wie VLOOKUP . Dies ist die Syntax:

XLOOKUP(lookup_value, lookup_array, return_array, [match_mode], [search_mode], [if_not_found])

• lookup_value : der Wert, nach dem Sie suchen möchten.

• lookup_array : der Bereich für die Suche.

• return_array : der Bereich, der den entsprechenden Wert anzeigt.

• match_mode : Genaue Übereinstimmung (0), nächstkleinere (1), nächstgrößere (-1) oder Wildcard- Übereinstimmung (2).

• Suchmodus : -1 für von oben nach unten, 1 für von unten nach oben oder 2 für binäre Suche.

• if_not_found : Legt den Wert fest, der zurückgegeben wird, wenn keine Übereinstimmung gefunden wird.

Angenommen, Sie möchten einen Datenbereich nach dem ersten Vorkommen eines Kleidungsstücks durchsuchen und die Verkaufsbeträge zurückgeben. Diese Formel sucht nach dem Text „Hoodie“ und gibt den Wert in der angrenzenden Zelle zurück, falls gefunden:

=XLOOKUP(„Hoodie“, A2:A6, B2:B6)

2. XMATCH-Funktion

Mit dieser Funktion können Sie die Position eines angegebenen Werts innerhalb eines Bereichs oder eines Arrays ermitteln. Dies ist die Syntax:

XMATCH(lookup_value, lookup_array, [match_type], [search_mode])

• lookup_value : der Wert, den Sie finden möchten.

• lookup_array : Der Bereich, den Sie durchsuchen möchten.

• match_type : genaue Übereinstimmung (0), nächstkleiner (-1), nächstgrößter (1).

• Suchmodus : binäre Suche (1) oder lineare Suche (2).

Angenommen, Sie möchten das erste Vorkommen eines gelben Elements in einem Bereich in Spalte B finden. Verwenden Sie diese Formel:

=XMATCH(“Gelb”, B2:B6, 0)

Abschließende Gedanken

Dieser Spickzettel deckt ein breites Spektrum an Funktionen ab, von statistischen Analysen über Nachschlageformeln bis hin zu Datenmanipulationstechniken und Fehlerbehandlungsstrategien.

Die bereitgestellten Beispiele und Erklärungen helfen dabei, diese fortgeschrittenen Formeln zu entmystifizieren und sie auch für Personen mit begrenzter Erfahrung zugänglich zu machen.

Wenn Sie damit beginnen, sie in Ihre Excel-Aufgaben zu integrieren, sind Sie auf dem Weg, Ihre Excel-Kenntnisse auf ein fortgeschrittenes Niveau zu bringen.

Aber denken Sie daran, dieser Spickzettel ist nur die Spitze des Eisbergs. Das wirklich Erstaunliche passiert, wenn Sie kreativ werden, diese Formeln kombinieren und sie an die Lösung Ihrer individuellen Herausforderungen anpassen. Excel ist wie eine Leinwand und diese Formeln sind Ihre Palette – also legen Sie los und malen Sie Ihr Meisterwerk!