Vermeiden Sie VLOOKUP mit dem Datenmodell. Sie haben also zwei Tabellen, die mit VLOOKUP verknüpft werden müssen, bevor Sie eine Pivot-Tabelle erstellen können. Wenn Sie Excel 2013 oder neuer auf einem Windows-PC haben, können Sie dies jetzt einfach und problemlos tun.
Angenommen, Sie haben einen Datensatz mit Produkt-, Kunden- und Verkaufsinformationen.
Die IT-Abteilung hat vergessen, den Sektor dort einzubauen. Hier ist eine Nachschlagetabelle, die Kunden dem Sektor zuordnet. Zeit für einen VLOOKUP, oder?
Wenn Sie über Excel 2013 oder Excel 2016 verfügen, müssen Sie keine VLOOKUPs ausführen, um diese Datensätze zu verbinden. Beide Versionen von Excel haben die Power Pivot-Engine in das Kern-Excel integriert. (Sie können dies auch mit dem Power Pivot-Add-In für Excel 2010 tun, es gibt jedoch einige zusätzliche Schritte.)
Verwenden Sie sowohl im Originaldatensatz als auch in der Nachschlagetabelle Home, Format as Table. Benennen Sie auf der Registerkarte Tabellentools die Tabelle von Tabelle1 in eine aussagekräftige Tabelle um. Ich habe Daten und Sektoren verwendet.
Wählen Sie eine Zelle in der Datentabelle aus. Wählen Sie Einfügen, Pivot-Tabelle. Ab Excel 2013 gibt es ein zusätzliches Feld Diese Daten zum Datenmodell hinzufügen, das Sie auswählen sollten, bevor Sie auf OK klicken.
Die Liste Pivot-Tabellenfelder wird mit den Feldern aus der Datentabelle angezeigt. Wählen Sie Umsatz. Da Sie das Datenmodell verwenden, wird oben in der Liste eine neue Zeile mit Aktiv oder Alle angezeigt. Klicken Sie auf Alle.
Überraschenderweise bietet die Liste der PivotTable-Felder alle anderen Tabellen in der Arbeitsmappe. Das ist bahnbrechend. Du hast noch keinen VLOOKUP gemacht. Erweitern Sie die Tabelle Sektoren und wählen Sie Sektor. Zwei Dinge warnen Sie, dass ein Problem vorliegt.
Zunächst wird die Pivot-Tabelle in allen Zellen mit derselben Nummer angezeigt.
Die subtilere Warnung ist möglicherweise, dass oben in der Liste der PivotTable-Felder ein gelbes Feld angezeigt wird, das angibt, dass Sie eine Beziehung erstellen müssen. Wählen Sie Erstellen. (Wenn Sie in Excel 2010 oder 2016 sind, nehmen Sie Ihr Glück mit Auto-Detect.)
Im Dialogfeld Beziehung erstellen haben Sie vier Dropdown-Menüs. Wählen Sie Daten unter Tabelle, Kunde unter Spalte (Fremd) und Sektoren unter Verwandte Tabelle. Power Pivot füllt automatisch die passende Spalte unter der zugehörigen Spalte (primär) aus. OK klicken.
Die resultierende Pivot-Tabelle ist ein Mashup der Originaldaten und der Nachschlagetabelle. Keine VLOOKUPs erforderlich.
Schau Video
- Ab Excel 2013 bietet das Dialogfeld "Pivot-Tabelle" das Datenmodell
- Dies ist das Codewort für Power Pivot Engine
- Um das Datenmodell zu verwenden, erstellen Sie aus jeder Tabelle in der Arbeitsmappe eine Strg + T-Tabelle
- Erstellen Sie eine Pivot-Tabelle aus der ersten Tabelle
- Wechseln Sie in der Feldliste der Pivot-Tabelle von Aktiv zu Alle
- Wählen Sie ein Feld aus der Nachschlagetabelle
- Erstellen Sie entweder die Beziehung oder die automatische Erkennung
- Auto-Detect war 2013 nicht da
- Vielen Dank an Colin Michael und Alejandro Quiceno für den Vorschlag von Power Pivot im Allgemeinen.
Video-Transkript
Lerne Excel aus dem Podcast, Folge 2014 - Eliminiere VLOOKUP!
Klicken Sie beim Podcast des gesamten Buches auf das „i“ in der oberen rechten Ecke, um die Wiedergabeliste anzuzeigen!
Hey, willkommen zurück im Netcast, ich bin Bill Jelen, das heißt eigentlich Eliminate VLOOKUP mit dem Datenmodell! Jetzt entschuldige ich mich, dies ist Excel 2013 und neuer. Wenn Sie wieder in Excel 2010 sind, müssen Sie das Power Pivot-Add-In herunterladen, das natürlich 2010 kostenlos ist. Wir haben also hier unser Hauptdatensatz, hier gibt es ein Kundenfeld, und dann habe ich eine kleine Tabelle, die Kunden dem Sektor zuordnet. Ich muss den Gesamtumsatz nach Sektoren erstellen, oder? Dies ist ein VLOOKUP, machen Sie einfach einen VLOOKUP, aber hey, dank Excel 2013 müssen wir keinen VLOOKUP machen! Ich habe beide in eine Tabelle umgewandelt, und in den Tabellenwerkzeugen, Design, benenne ich die Tabellen um, nenne diesen einen Sektor und ich nenne diesen einen Daten, um daraus eine Tabelle zu machen, wähle einfach eine Zelle aus und drücke Strg + T. T. Wenn wir also einige Überschriften und Zahlen haben, wenn Sie Strg + T drücken,Sie fragen "Wo sind die Daten für Ihre Tabelle?", meine Tabelle hat Überschriften, und dann nennen sie es Tabelle3, Sie nennen es etwas anderes. Okay, so habe ich diese beiden Tabellen erstellt, ich werde diese Tabelle loswerden, okay.
Damit dieser Trick funktioniert, müssen alle Daten in Tabellen gespeichert sein. Wir gehen zur Registerkarte Einfügen, wählen PivotTable und fügen hier unten unten diese Daten zum Datenmodell hinzu. Das klingt sehr harmlos, oder? Es gibt nichts Schöneres als einen Flammpunkt, der sagt: "Hey, damit kannst du erstaunliche Dinge tun!" Und was sie hier sagen, was sie nicht sagen wollen, ist Folgendes: Übrigens, hinter jeder Kopie von Excel 2013 steckt die Power Pivot-Engine. Sie wissen, wenn Sie in Office 365 sind, zahlen Sie 10 US-Dollar pro Monat, und sie möchten, dass Sie 12 oder 15 US-Dollar pro Monat zahlen, um Power Pivot zu erhalten, die zusätzlichen zwei oder fünf Dollar. Nun, hey, shh, sag nicht, du hast tatsächlich den größten Teil von Power Pivot bereits in Excel 2013. Okay, also klicke ich auf OK, das Laden des Datenmodells dauert etwas länger, okay, aber das ist in Ordnung und gleich vorbei Hier,In den PivotTable-Feldern habe ich eine Liste aller Felder. Ich möchte Revenue natürlich zeigen, aber was hier oben anders ist, ist Active and All. Wenn ich Alle auswähle, werden alle Tabellen in der Arbeitsmappe angezeigt. Okay, also gehe ich zu den Sektoren und sagte, ich möchte den Sektor in den Zeilenbereich stellen. Jetzt wird der Bericht zunächst falsch sein, sehen Sie die 6,7 Millionen ganz unten, und diese gelbe Warnung hier sagt, dass Sie eine Beziehung herstellen müssen.und diese gelbe Warnung hier sagt, dass Sie eine Beziehung herstellen müssen.und diese gelbe Warnung hier sagt, dass Sie eine Beziehung herstellen müssen.
Okay, 2010, mit Power Pivot, würde es nur AutoDetect bieten, 2013 haben sie AutoDetect herausgenommen und 2016 haben sie AutoDetect zurückgebracht, okay? Ich sollte Ihnen zeigen, wie CREATE aussieht, aber wenn ich auf diese CREATE-Schaltfläche klicke, ist das alles in Ordnung. Aus unserer ersten Tabelle Daten habe ich ein Feld namens Kunde, aus den zugehörigen Tabellensektoren habe ich ein Feld namens Kunde, und dann klicken Sie auf OK, in Ordnung. Aber lassen Sie mich Ihnen nur zeigen, wie cool AutoDetect ist. Wenn Sie 2016 dort sind, haben sie es herausgefunden. Wie großartig ist das, oder? Sie müssen sich keine Sorgen um VLOOKUP machen, und das Komma fällt am Ende. Wenn VLOOKUP Ihren Kopf verletzt, werden Sie das Datenmodell lieben. Ich habe diese beiden Tische genommen, sie zusammengefügt, wissen Sie, wie Access es wohl tun würde, und eine Pivot-Tabelle erstellt, absolut erstaunlich.Überprüfen Sie das Datenmodell, wenn Sie das nächste Mal einen VLOOKUP zwischen zwei Tabellen durchführen müssen. Nun, dieser und alle anderen 40 Tipps sind im Buch enthalten. Klicken Sie auf das „i“ in der oberen rechten Ecke. Sie können das Buch kaufen, einen vollständigen Querverweis auf diese ganze Reihe von Videos haben, den ganzen August, den ganzen September, zum Teufel, wir könnten sogar in den Oktober übertragen, um das Ganze zu erledigen.
Okay, fassen Sie noch heute zusammen: Ab Excel 2013 bietet das Dialogfeld "Pivot-Tabelle" das sogenannte Datenmodell, das Codewort für die Power Pivot-Engine. Bevor Sie Ihre Pivot-Tabellen erstellen, drücken Sie Strg + T, um aus jeder Arbeitsmappe eine Tabelle zu erstellen. Ich habe mir die zusätzliche Zeit genommen, um jede Tabelle zu benennen. Erstellen Sie eine Pivot-Tabelle aus der ersten Tabelle, und gehen Sie dann in der Feldliste nach oben und wechseln Sie von Aktiv zu Alle. Wählen Sie ein Feld aus der Nachschlagetabelle aus, und Sie werden gewarnt, dass Sie entweder eine Beziehung erstellen müssen oder AutoDetect. 2013 müssen Sie auf ERSTELLEN klicken. Aber es ist was, 4 Klicks, um es zu erstellen, 5, wenn Sie die OK-Taste zählen, also wirklich, wirklich einfach zu tun.
Okay, Colin, Michael und Alejandro Quiceno haben Power Pivot im Allgemeinen für die Bücher vorgeschlagen. Dank ihnen, danke für Ihren Besuch, wir sehen uns beim nächsten Mal für einen weiteren Netcast von!
Download-Datei
Laden Sie die Beispieldatei hier herunter: Podcast2014.xlsx
