Als Excel- Benutzer haben Sie wahrscheinlich schon von zwei der bekanntesten Funktionen von Excel gehört : der vlookup-Funktion und Pivot-Tabellen.
Aber wenn Sie sie bisher nicht oft verwendet haben, fragen Sie sich vielleicht: Wann würden Sie vlookup und wann Pivot-Tabellen verwenden? Und wofür sollten Sie sie verwenden?
In diesem Artikel untersuchen wir vlookup, Pivot-Tabellen und auch Power Pivot und wann Sie diese für optimale Ergebnisse verwenden sollten.
Was ist vlookup?
Vlookup ist eine Nachschlage- und Referenzfunktion in Excel.
Es wird häufig in einem Arbeitsblatt verwendet, um Daten aus einer anderen Excel-Tabelle oder einem anderen Excel-Arbeitsblatt nachzuschlagen und abzurufen.
Beispielsweise haben wir eine Excel-Tabelle mit dem Namen „Umsatz“, die Details zu den Produktverkäufen für alle Monate des Jahres enthält.
Und eine weitere Tabelle namens „Produkte“ mit Produktdetails.
Wir möchten eine PivotTable erstellen, die den Gesamtumsatz nach verschiedenen Produktkategorien anzeigt.
Die Tabelle „Verkäufe“ enthält jedoch keine Details zu den Produktkategorien. Diese Informationen sind in der Tabelle „Produkte“ gespeichert.
Wir können die Funktion vlookup verwenden, um die Kategorieinformationen in die Tabelle „Verkäufe“ zu bringen.
So verwenden Sie die vlookup-Funktion
Die vlookup-Funktion hat vier Argumente (erforderliche Informationen).
Dabei handelt es sich um den Nachschlagewert, das Tabellenarray, die Spaltenindexnummer und die Bereichsnachschlage.
Der Suchwert ist der Wert, nach dem Sie suchen. In unserem Beispiel ist dies die Kategorie-ID.
Das Tabellenarray ist die Tabelle, in der wir diese Informationen nachschlagen müssen. Dies ist die Tabelle „Produkte“.
Col Index Num ist die Spaltennummer der Tabelle, die die zurückzugebenden Informationen enthält. Dies ist die Spalte mit der Kategorie, also die zweite Spalte. Wir verwenden also 2.
Bereichssuche ist die Art der Suche, die Sie durchführen. Suchen Sie in Bereichen? In unserem Beispiel tun wir das nicht, weil wir nach einer bestimmten Kategorie-ID suchen.
Die folgende Formel wird der Tabelle „Umsatz“ in Spalte F hinzugefügt.
=vlookup([@[Kategorie-ID]],Produkte,2,FALSCH)
vlookup ist eine äußerst nützliche Funktion in Excel, die auf viele andere clevere Arten verwendet werden kann, beispielsweise zum Vergleichen von Listen oder Testen von Werten.
Neben vlookup sind auch die INDEX- und MATCH-Formeln sehr nützlich, um Daten aus anderen Excel-Tabellen nachzuschlagen.
Was ist eine Pivot-Tabelle?
Eine Pivot-Tabelle ist ein Berichtstool in Excel, das Daten zusammenfasst und eine Aggregation von Werten durchführt.
Beispielsweise um den Gesamtumsatz nach Monat oder die Anzahl der Bestellungen für jedes Produkt anzuzeigen.
Es ist sehr leistungsstark und ermöglicht eine schnelle und einfache Berichterstellung.
So erstellen Sie eine Pivot-Tabelle
Mit der Kategoriespalte in der Tabelle „Umsatz“ können wir die Pivot-Tabelle erstellen, um den Gesamtumsatz für jede Produktkategorie anzuzeigen.
Klicken Sie in die Tabelle „Umsatz“ und dann auf Einfügen > PivotTable .
Als zu verwendende Datenquelle wird die Tabelle „Umsatz“ ausgewählt. Die Standardoption besteht darin, die Pivot-Tabelle in ein neues Arbeitsblatt einzufügen.
Das neue Arbeitsblatt wird eingefügt und die PivotTable darauf platziert. Auf der rechten Seite wird eine Feldliste mit allen Spalten aus der Tabelle „Umsatz“ angezeigt.
Klicken Sie auf das Feld „Kategorie“ und ziehen Sie es in den Zeilenbereich der Pivot-Tabelle und das Feld „Gesamt“ in den Wertebereich .
Die Pivot-Tabelle zeigt den Gesamtumsatz für jede Produktkategorie.
So formatieren Sie die Werte richtig: Klicken Sie mit der rechten Maustaste auf einen PivotTable-Wert und klicken Sie auf „Zahlenformat“ .
Wählen Sie die Formatierung aus, die Sie verwenden möchten. In diesem Beispiel habe ich „Buchhaltung“ mit 0 Dezimalstellen ausgewählt.
Die Pivot-Tabelle ist jetzt richtig formatiert.
Pivot-Tabellen sind ein leistungsstarkes Excel-Tool. Sehen Sie sich einige erweiterte Pivot-Tabellentechniken an .
Was ist Power Pivot?
Power Pivot wird auch als Datenmodell in Excel bezeichnet .
Vereinfacht ausgedrückt ermöglicht es uns, aus mehreren Tabellen eine Pivot-Tabelle zu erstellen, die als Datenmodell bezeichnet wird.
Power Pivot ist eine erweiterte Funktion mit einer eigenen Formelsprache namens DAX. Weitere Informationen hierzu finden Sie in diesem umfassenden Handbuch zu Power Pivot .
Der Einfachheit halber kann es jedoch auch als Alternative zu vlookup verwendet werden.
Anstatt eine Nachschlageformel zu verwenden, um Daten aus mehreren Tabellen in einer einzigen zu konsolidieren, können Sie sie in eigenen Tabellen behalten und Power Pivot verwenden, um sie zu verknüpfen.
Sie können dann aus allen zugehörigen Tabellen (dem Datenmodell) eine Pivot-Tabelle erstellen.
Laden von Tabellen in Power Pivot
Zuerst müssen Sie die Tabellen in das Datenmodell laden.
Klicken Sie in die Tabelle „Umsatz“ und dann auf Daten > Aus Tabelle/Bereich .
Dadurch wird das Fenster des Power Query-Editors geöffnet.
Power Query ist ein Tool, mit dem Sie Ihre Daten leistungsstark transformieren können, um sie für die Analyse vorzubereiten. Es eignet sich hervorragend für die Vorbereitung von Daten für Power Pivot .
Diese Daten sind sauber und erfordern keine Transformationen, sodass sie direkt in das Datenmodell geladen werden können.
Klicken Sie auf „Start“ > „Schließen und laden“ > „Schließen und laden nach“ .
Wählen Sie die Option „ Nur Verbindung erstellen“ und aktivieren Sie das Kontrollkästchen „ Diese Daten zum Datenmodell hinzufügen“ .
Die Tabelle wird in das Modell geladen. Dies wird im Fenster „Abfragen und Verbindungen“ angezeigt.
Wiederholen Sie diese Schritte für die Tabelle „Produkte“.
Beide Tabellen werden in das Datenmodell geladen und sind im Bereich „Abfragen und Verbindungen“ sichtbar.
Erstellen von Beziehungen zwischen Tabellen im Modell
Um nun die Beziehung zwischen den beiden Tabellen zu erstellen, müssen wir das Power Pivot-Fenster öffnen.
Klicken Sie auf Daten > Datenmodell verwalten .
Das Fenster „Power Pivot für Excel“ wird geöffnet und führt Sie zur Datenansicht.
Es sieht aus wie eine Excel-Arbeitsmappe (ist es aber nicht). Die beiden Registerkarten am unteren Bildschirmrand sind die beiden Tabellen, die in das Datenmodell geladen wurden.
Klicken Sie auf die Schaltfläche „Diagrammansicht“ .
Klicken und ziehen Sie vom Feld „Kategorie-ID“ in „Verkäufe“ zum Feld „ID“ in „Produkte“.
Wenn Sie die Maustaste loslassen, wird die Beziehung hergestellt.
Zwischen den beiden Tabellen wird eine Linie mit einer 1 auf der Seite „Produkte“ und einem Unendlichkeitssymbol auf der Seite „Verkäufe“ gezeichnet.
Dies deutet auf eine Eins-zu-viele-Beziehung hin, da ein Produkt viele Male verkauft werden kann.
Schließen Sie das Power Pivot-Fenster.
Erstellen einer Pivot-Tabelle aus einem Power Pivot-Datenmodell
Da die Tabellen nun verknüpft sind, können wir mit beiden eine Pivot-Tabelle erstellen.
Klicken Sie auf Einfügen > PivotTable .
Stellen Sie sicher, dass die Option Datenmodell dieser Arbeitsmappe verwenden ausgewählt ist. Klicken Sie auf Neues Arbeitsblatt als Speicherort für die Pivot-Tabelle.
Die Pivot-Tabelle wird erstellt und die Feldliste wird angezeigt.
Die Feldliste zeigt die beiden Tabellen im Datenmodell und auch die beiden Tabellen im Arbeitsblatt.
Wir müssen die beiden im Datenmodell verwenden. Sie sind an den unterschiedlichen Symbolen neben ihrem Namen zu erkennen.
Ziehen Sie das Feld „Kategorie“ aus der Tabelle „Produkte“ in die Zeilen. Ziehen Sie anschließend das Feld „Gesamt“ aus der Tabelle „Umsatz“ in die Werte.
Formatieren Sie die Werte und wir erhalten dieselben PivotTable-Ergebnisse wie zuvor.
Dieses Mal wurden die Tabellen getrennt gehalten und mithilfe des Datenmodells verknüpft.
Dies ist effizienter, als mehrere vlookup-Funktionen zu schreiben, um Daten aus verschiedenen Tabellen in einer zusammenzuführen. Dies gilt insbesondere, wenn Sie mit großen Datensätzen und mehreren Tabellen arbeiten.
vlookup zum Abrufen von Daten aus einer Pivot-Tabelle
Daher wird vlookup häufig verwendet, um Daten für eine Pivot-Tabelle zu konsolidieren. Es kann aber auch verwendet werden, um Werte aus einer Pivot-Tabelle zurückzugeben.
Ja. Unten sehen Sie ein Beispiel für die Verwendung einer vlookup-Funktion, um den Gesamtumsatz von Lebensmitteln aus der von uns erstellten PivotTable zurückzugeben.
Dies gibt die richtige Antwort zurück.
Allerdings verwendet die vlookup-Funktion einen Verweis auf den Zellbereich A4:B6. Und obwohl dies funktioniert, funktioniert die vlookup-Funktion nicht mehr, wenn die PivotTable geändert wird.
Dabei werden die Daten nicht wirklich aus einer Pivot-Tabelle abgerufen, sondern aus dem Zellbereich.
Mehr lesen: Power Query Tipps: Die 10 nützlichsten Befehle
vlookup vs. GETPIVOTDATA
Dies könnte also zu einem Problem führen. Es hängt davon ab, wofür und wie die Pivot-Tabelle verwendet wird.
Pivot-Tabellen sind ein dynamisches Tool, vlookup hingegen nicht.
Ein besserer Ansatz könnte daher die Verwendung der integrierten PivotTable-Nachschlagefunktion namens GETPIVOTDATA sein.
Um diese Funktion zu verwenden, geben Sie = ein und klicken Sie dann auf eine Zelle in der Pivot-Tabelle.
Die Funktion GETPIVOTDATA wird automatisch erstellt, wenn Sie in einer Formel auf eine Zelle in der Pivot-Tabelle klicken.
In diesem Beispiel wird die aus dem Datenmodell erstellte Pivot-Tabelle verwendet.
Die Funktion GETPIVOTDATA sucht den Wert in der Spalte „Summe des Gesamtwerts“ und nach der Lebensmittelkategorie.
Wenn die Größe der Pivot-Tabelle zunimmt, kann GETPIVOTDATA den Wert erfolgreich abrufen. Allerdings würde vlookup immer noch im Bereich A4:B6 suchen, was nicht korrekt wäre.
Einpacken
vlookup und Pivot-Tabellen sind zwei Funktionen, die sich gegenseitig ergänzen. Es geht also eher darum, wie man sie zusammen verwendet, als vlookup und Pivot-Tabelle gegeneinander auszuspielen.
Normalerweise erhalten Sie Tabellen aus verschiedenen Systemen und von verschiedenen Personen. Daher kann vlookup dabei helfen, die Daten für Pivot-Tabellen vorzubereiten, um dann Analysen und Berichte daraus zu erstellen.
Power Pivot bietet hierzu einen alternativen Ansatz, indem es die verschiedenen Tabellen verknüpft, aus denen Pivot-Tabellen erstellt werden.
Erfahren Sie mehr mit GoSkills
Möchten Sie mehr über Excel erfahren? Schauen Sie sich die umfassenden Online-Excel-Kurse von GoSkills an.
Mit den Kursen „Excel-Grundlagen“ und „Excel-Fortgeschrittene“ werden Sie vom Anfänger zum Excel-Ninja, während Sie mit den Kursen „Pivot-Tabellen“ und „Power Pivot“ noch besser mit Excel vertraut werden. Jeder Kurs beginnt mit den Grundlagen, sodass Sie eine solide Grundlage für den Aufbau Ihrer Kenntnisse und Fähigkeiten haben.
Melden Sie sich noch heute für eine 7-tägige kostenlose Testversion an , um alle Kurse von GoSkills auszuprobieren, einschließlich unserer preisgekrönten Excel-Kurse.