LookupValue() – Nachschlagen reloaded

    Im Artikel Related() statt VLookup haben wir vorgestellt, wie man über verknüpfte Tabellen navigieren kann, um Werte zu finden. Tabellen lassen sich allerdings nur über ein Merkmal verknüpfen. Was, wenn wir einen Wert über zwei oder mehr Merkmale suchen wollen? Beispielsweise die Frachtkosten eines Spediteurs in ein spezielles Warenempfängerland? Hier hilft uns die Funktion LookupValue().

    Unsere Ausgangsdaten

    Für verschiedene Kurier-Dienstleister haben wir Offerten mit Preisen pro Packstück pro Warenempfängerland. Beispielsweise wie folgt (die Offerten sind frei erfunden!):

    Offerten

    Darüberhinaus haben wir aus unserem Versandsystem folgende Auftragsdaten vorliegen:

    Aufträge

    In unserem Beispiel haben wir die Beispieldaten in Excel erfasst und als verknüpfte Tabellen in unser Modell übernommen.

    Die Verknüpfungen

    Genau hier haben wir mit den bisherigen Werkzeugen ein Problem: Die beiden Tabellen müssen sowohl über das Merkmal Spediteur als auch über das Merkmal Empfänger Land miteinander verknüpft werden – was Power Pivot erst Mal nicht zulässt. Ziel ist es, ein weiteres, berechnetes Feld in die Auftrags-Tabelle aufzunehmen, mit dem für den Auftrag geltenden Packstückpreis.

    LookupValue()

    Hier kommt die Funktion LookupValue() zum Einsatz. Die Signatur ist wie folgt:

    LOOKUPVALUE( <result_columnName>, <search_columnName>, <search_value>[, <search_columnName>, <search_value>]…)

    Wir setzen die Funktion in einer neuen, berechneten Spalte in der Auftragstabelle ein. Der erste Parameter ist das Merkmal der anderen Tabelle, für das wir uns interessieren – in unserem Fall also Offerten[Preis]. Der zweite Parameter ist das Merkmal, nach dem wir suchen. Zunächst suchen wir den richtigen Spediteur in der Offerten-Tabelle – der Parameter ist daher Offerte[Spediteur]. Dieser muss übereinstimmen mit dem Spediteur unserer Auftragstabelle, also Aufträge[Spediteur]. Aber nicht nur der Spediteur muss übereinstimmen, sondern eben auch das Warenempfängerland. Daher hängen wir als nächsten Parameter das Merkmal Offerten[Empfänger Land] dran und bestimmen wiederum mit dem nächsten Parameter den Wert, nach dem gesucht werden soll: Aufträge[WELand]. Die Parameter der Funktion LookupValue() können beliebig ergänzt werden. Immer mit dem Muster “Suchmerkmal”, “Suchkriterium”. Die Suchkriterien müssen dabei immer alle gelten und es wird immer auf Gleichheit der Werte geprüft. Unser Aufruf sieht wie folgt aus:

    LookupValue für Frachtkosten

    Nichts gefunden?

    Wir ergänzen unsere Auftragstabelle um einen Satz – eine Sendung in die USA:

    Auftragssatz in die USA

    Dafür gibt es keine Offerte:

    Auftrag ohne Offerte

    Bei einer weiteren Berechnung kann man diesen Umstand mit IsBlank() abfragen, um beispielsweise einen Standard-Paketpreis von 2,00 EUR anzunehmen:

    Packstückpreis mit Standardpreis

    Die Frachtkosten…

    …sind jetzt nur noch der letzte Schritt – wir multiplizieren die Packstück-Anzahl mit dem Einzelpreis und können darauf Auswertungen, eine einfache Rechnungsprüfung oder sogar ein Gutschriftsverfahren durchführen.

     

    Kategorien: Funktionen, Power Pivot
    Holger Gubbels

    Holger Gubbels ist Spezialist für alles, was mit Business Intelligence zu tun hat. Der Diplom-Informatiker strukturiert und löst als Data Scientist und Berater der mogular GmbH tagtäglich komplexe Problemstellungen rund um die Datenanalyse. Als Dozent der Hochschule für Technik Stuttgart ist er ein ausgewiesener Experte seines Fachs und gewohnt, Inhalte einfach zu erläutern. Genau das macht er auch hier in seinen Beiträgen für das PowerPivotInsights Blog. Zusätzlich ist er Autor für die Computerwoche und den Springer-Verlag. Sie erreichen ihn unter hg@mogular.com

    Antwort schreiben

    Deine E-Mail-Adresse wird nicht veröffentlicht. Erforderliche Felder sind mit * markiert.