RELATED() statt VLOOKUP

    Eine Tabelle enthält Ihre Versandarten (DPD, DHL, GLS, etc.) sowie einen Paketpreis. Dagegen steht eine Auftragstabelle mit der Anzahl Packstücke und der genutzten Versandart. Stimmen die vom Spediteur berechneten Frachtkosten mit denen laut meinem System überein? Wir zeigen Ihnen, wie Sie schnell mit der Funktion RELATED() eine einfache Prüfung in Power Pivot erstellen!

    Die Ausgangsdaten

    Als Datenbasis nehmen wir zwei verbundenen Tabellen aus Excel – das sind zugegebenermaßen nicht viele Daten, aber das Prinzip sollte deutlich werden:

    Aufträge

    Versandarten

    Diese beiden Tabellen importieren Sie als verknüpfte Tabellen in Ihr Power Pivot Datenmodell. Ich habe die Tabellen gleich umbenannt in Versandarten und Aufträge:

    Verknüpfte Tabellen im Power Pivot Modell

    Die beiden Tabellen verbinden wir über die Versandart.

    Verknüpfung

    Die Berechnung des Preises

    Mit den jetzigen Daten lässt sich ein einfaches Pivot erstellen, beispielsweise eine Übersicht über versandte Packstücke pro Versandart:

    Pivot Anzahl Packstücke/Versandart

    Aber das Ziel war die Frachtkosten zu berechnen. Dazu müssen wir das Feld Preis aus der Tabelle Versandarten in die Tabelle Aufträge bekommen. Dann können wir den Preis mit dem Feld Menge multiplizieren. Früher eine typische Aufgabe der VLOOKUP-Funktion, mit der die meisten von uns eine Hassliebe verbindet – nicht nur, dass sie sehr langsam war, sie war leider auch sehr fehleranfällig. Power Pivot stellt uns die RELATED()-Funktion zur Verfügung. Durch die Verknüpfung der beiden Tabellen, ist die Verbindung zwischen Aufträge und Versandarten eindeutig. Damit kann von einem berechneten Feld in der Tabelle Aufträge direkt auf Versandarten und damit auch auf das Feld Preis zugegriffen werden:

    berechnete Spalte mit RELATED()

    Damit können wir in einer weiteren Spalte den Preis berechnen:

    Kosten berechnen

    Das Problem mit der Datenqualität

    In der Tabelle Versandarten darf eine Versandart natürlich nur einmal vorkommen. Haben Sie zwei Zeilen mit beispielsweise DPD, verhindert Power Pivot bereits die Verknüpfung zwischen Aufträge und Versandarten.

    Natürlich werden mit Absicht in der Auftragstabelle aber Versandarten verwendet, die wir nicht in der Versandarten-Tabelle finden – oder aber wir haben keine Versandart angegeben. Damit erhalten wir für diese Fälle, also konkret für die Versandart AUX und im Falle des Auftrages mit der Nummer 5, keine Frachtkosten. Das sieht etwas unschön aus – eigentlich müssten wir herausfinden, ob die Funktion RELATED() etwas gefunden hat und falls nicht, eine Null eintragen:

    =IF ( ISBLANK( [Preis]); 0; [Menge] * [Preis] )

    ISBLANK() prüft, ob in der Zelle ein Wert vorhanden ist. Falls nicht, wird Null eingetragen, falls doch, werden unsere Kosten korrekt berechnet.  “Eigentlich” deshalb, weil man sich in diesem Fall auch einfacher behelfen kann:

    =[Preis]*[Menge] + 0

    Wenn Sie Null zu “nichts” addieren, kommt bei Power Pivot ebenfalls Null heraus Smiley

    Ich habe aber verschiedene Werte für Land und Versandart

    Praktisch sind Frachtberechnungen komplexer – beispielsweise kostet DHL in die Schweiz mehr als nach Deutschland. Damit müssen wir die Auftrags- und Versandarten-Tabelle um ein Land ergänzen. Eine Verknüpfung funktioniert dann nicht, da man nur über ein Feld verknüpfen kann. Dies löst man mit der LOOKUPVALUE()-Funktion – ein Thema für einen weiteren Blog-Artikel Smiley

    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.