Office 365/Excel 2016+-Formeln

  • 15 Minuten

In dieser Einheit werden drei neue Formeln vorgestellt, die in Office 365/Excel 2016+ verfügbar sind: XLOOKUP(), FILTER() und LET(). Eine weitere Möglichkeit, neue Features und Funktionen kennenzulernen, die der Anwendung hinzugefügt wurden, ist die Teilnahme am Insider-Programm für Microsoft Office (siehe Link im Referenzabschnitt am Ende dieses Moduls).

XLOOKUP()

XLOOKUP() ist eine neue, leistungsfähigere Version von VLOOKUP(). Sie ist einfacher, schneller und flexibler.

Aus folgenden Gründen ist XLOOKUP() optimaler als VLOOKUP():

  • In Suchspalten und -zeilen werden VLOOKUP() und HLOOKUP() für eine umfassendere Suche kombiniert.

  • Suchspalten auf der linken Seite ersetzen INDEX() MATCH()-Muster, sodass Sie eine Kombination verwenden können, die für Ihre Suche am besten geeignet ist.

  • Die Formel ist stabiler, da sie nicht „unterbrochen“ wird, wenn Spalten hinzugefügt/gelöscht werden.

XLOOKUP() enthält eine Syntax mit drei erforderlichen Parametern. Die Funktion untersucht standardmäßig, ob eine genaue Übereinstimmung vorliegt.

Screenshot: Excel-Bearbeitungsleiste mit XLOOKUP()-Funktionssyntax.

XLOOKUP() bietet die folgenden Features:

  • Rückgabe eines Werts aus einer angegebenen Spalte basierend auf einem Wert in einer anderen Spalte

  • Rückgabe eines anderen Werts, wenn kein Ergebnis gefunden wird

  • Suchen von oben oder von unten

XLOOKUP() verfügt über sechs Parameter, wobei die letzten drei Parameter optional sind:

  • lookup_value: Mit diesem Parameter wird der gesuchte Wert definiert.

  • lookup_array: Arrayparameter zum Angeben der Spalte, in der der Wert gesucht werden soll.

  • return_array: Arrayparameter zum Definieren der Spalte, aus der der Wert zurückgegeben werden soll.

  • if_not_found: Wenn keine Übereinstimmung gefunden wird, wird dieser optionale Wert zurückgegeben.

  • match_mode: Optionaler Parameter, um eine genaue Übereinstimmung, den ersten Treffer oberhalb/unterhalb oder eine Platzhaltersuche anzugeben.

  • search_mode: Mit diesem optionalen Parameter wird eine Suche von oben oder von unten angegeben.

Screenshot: XLookup()-Beispiele.

Beachten Sie im vorherigen Datasetbeispiel die XLOOKUP()-Formel rechts in der Blackbox, in der die zurückgegebenen Ergebnisse angezeigt werden. Die drei Beispiele beantworten folgende Fragen:

  • Find Product by ID (Produkt nach ID suchen): Die Formel veranschaulicht eine Produktsuche nach „Product ID = 109“, wobei die Produktergebnisse in einer Spalte rechts neben der Spalte „Product ID“ zu finden sind.

  • Find City by ZIP (Ort nach PLZ suchen): Die Beispielformel veranschaulicht eine Ortssuche nach „ZIP = 21658“. Die Ergebnisse befinden sich in einer Spalte links von der Spalte „ZIP“.

  • Find last Product by City (Letztes Produkt nach Ort suchen): Diese Formel veranschaulicht die Verwendung optionaler Parameter. Werden keine Ergebnisse gefunden, wird „No Results found“ (Keine Ergebnisse gefunden) zurückgegeben, eine genaue Übereinstimmung ist gewünscht, und „-1“ gibt an, dass in der Datentabelle von unten nach oben gesucht werden soll.

FILTER()

FILTER() ist eine neue Arrayfunktion. Durch Hinzufügen der Formel zu einer einzelnen Zelle wird eine Teilmenge der Tabelle zurückgegeben, und die weiteren Werte werden auf die anderen Zellen innerhalb des Ergebnisses verteilt. FILTER() gibt Datenzeilen zurück und lässt mithilfe von AND/OR-Logik mehrere Bedingungen zu.

FILTER() bietet die folgenden Features:

  • Rückgabe mehrerer Übereinstimmungsergebnisse für einen oder mehrere Suchwerte

  • Filtern von Daten ohne [refresh]{.underline}

  • Kann in anderen Excel-Funktionen geschachtelt werden

Im Folgenden werden die drei in FILTER() enthaltenen Parameter im Einzelnen erläutert:

  • array: Parameter, mit dem ein zu filternder Bereich von Spalten und Zeilen angegeben wird.

  • include: Parameter zum Angeben von Filterregelkriterien.

  • if_empty: Optionaler Parameterwert, der zurückgegeben werden soll, wenn keine der Zeilen die Bedingungen erfüllt.

Screenshot eines „Filter() Single“-Beispiels.

Das vorherige Datasetbeispiel zeigt die FILTER()-Formel in der Blackbox mit den zurückgegebenen Ergebnissen. Beachten Sie, dass anstelle eines Bereichs eine Tabelle verwendet wird. Sie sollten nach Möglichkeit immer eine Tabelle verwenden. Im vorherigen Beispiel wird die SalesTable-Tabelle gefiltert, wobei Region = West gilt und alle übereinstimmenden Zeilen innerhalb des Ergebnisses zurückgegeben werden.

Screenshot eines „Filter() Multiple“-Beispiels.

In diesem Beispiel wird dasselbe Dataset verwendet, aber es werden drei Filter auf die Tabelle angewendet. Die Formel filtert die Tabelle nach den folgenden Kriterien. Alle Kriterien müssen erfüllt sein, damit die Zeile einbezogen werden kann.

  • Product = Palma UM-01

  • Region = West

Revenue = Greater than USD 1,215.00 (Umsatz höher als 1.215,00 USD)

Die Formel verwendet die Multiplikationsfunktion, da ein logischer Vergleich null (0) für FALSE oder eins (1) für TRUE ergibt. Wenn alle Bedingungen TRUEsind, dann 1 * 1 * 1 = 1. Wenn jedoch eine Bedingung null (0) oder FALSE ist, ist die gesamte Logik FALSE.

Ein Sternchen (*) wird für AND-Bedingungen und das Pluszeichen (+) für OR-Bedingungen verwendet.

LET()

Die LET()-Funktion bietet eine erhebliche Flexibilität für komplexe Berechnungen und ist eine einfachere Möglichkeit, die verschiedenen Teile der Formel zu verarbeiten. Sie kombiniert die Möglichkeit zum Speichern von Berechnungen und Werten, die Variablen verwenden, mit der nativen Formelsyntax von Excel.

Diagramm der LET()-Funktionssyntax.

Mit den Variablen wird einem Wert oder einer Berechnung ein Name zugewiesen. Mit diesen Variablen wird die Syntax abgerufen, ohne die Formel wiederholt neu schreiben zu müssen. Sie können bis zu 126 verschiedene Variablen in der Funktion definieren, aber es müssen mindestens die drei Komponenten (Variable, Wert der Variablen und Berechnung) vorhanden sein. Sie können auch andere Arrayfunktionen wie FILTER() in der LET()-Funktion nutzen. Das folgende Beispiel basiert auf dem früheren FILTER()-Beispiel, aber jetzt sind die Variablen zugewiesen.

Screenshot des LET()-Beispiels.

Im vorherigen Screenshot sind die Zahlen 1 bis 4 Variablen und Definitionen. Die letzte Anweisung ist die Berechnung, die die Variablen verwendet.

  • ProductRange = Product column range (Produktspaltenbereich)

  • Product = Produkt, nach dem gefiltert werden soll

  • RegionRange = Region column range (Regionsspaltenbereich)

  • Region = Region, nach der gefiltert werden soll

  • Filter = Filtering on the table for the Product and Region (Filtern der Tabelle für Produkt und Region)