Was PowerPivot für Excel am häufigsten aufführt, ist die Möglichkeit, Ihren Datensätzen Lookup-Tabellen hinzuzufügen. Meistens haben die Daten, mit denen Sie arbeiten, nicht alle Felder, die Sie für Ihre Analyse benötigen. Beispielsweise haben Sie möglicherweise ein Datumsfeld, müssen Ihre Daten jedoch nach Quartalen gruppieren. Sie könnten eine Formel schreiben, aber es ist einfacher, eine einfache Nachschlagetabelle in der PowerPivot-Umgebung zu erstellen.
Sie können diese Nachschlagetabelle auch für eine andere Gruppierung verwenden, z. B. den Namen des Monats und das erste / zweite Halbjahr. In Data Warehousing-Begriffen erstellen Sie tatsächlich eine Datumstabelle. In diesem Artikel werde ich Ihnen ein paar Beispieldimensionstabellen geben, um Ihr PowerPivot für Excel-Projekt zu verbessern.
Neue Textbemaßung (Nachschlagetabelle)
Betrachten wir eine Tabelle mit Bestelldaten. Angenommen, die Tabelle enthält Felder für Kunden, Auftragsdatum, Auftragssumme und Auftragstyp. Wir werden uns auf das Feld Auftragsart konzentrieren. Angenommen, das Feld für den Auftragstyp enthält Werte wie:
- Netbooks
- Desktops
- Monitore
- Projektoren
- Drucker
- Scanner
- Digitalkameras
- Digitale Spiegelreflexkameras
- Filmkameras
- Camcorder
- Büro-Telefone
- Smartphones
- PDAs
- Handys Zubehör
In der Realität hätten Sie Codes für diese, aber um dieses Beispiel einfach zu halten, nehmen Sie an, dass dies die tatsächlichen Werte in der Auftragstabelle sind. Wenn Sie PowerPivot für Excel verwenden, können Sie Ihre Aufträge leicht nach Auftragstyp gruppieren.
Was wäre, wenn Sie eine andere Gruppierung wollten? Angenommen, Sie benötigen eine Kategoriegruppe wie Computer, Kameras und Telefone. Die Auftragstabelle hat kein Kategoriefeld, aber Sie können sie ganz einfach als Nachschlagetabelle in PowerPivot für Excel erstellen. Die vollständige Beispiel-Lookup-Tabelle befindet sich unten in Tabelle 1 .
Hier sind die Schritte:
- Schritt 1: Sie benötigen eine eindeutige Liste aus dem Typenfeld für Ihre Nachschlagetabelle. Dies wird Ihr Suchfeld sein. Erstellen Sie aus Ihrem Datensatz eine eindeutige Liste von Werten aus dem Feld Auftragstyp. Geben Sie die eindeutige Liste der Typen in eine Excel-Arbeitsmappe ein. Beschriften Sie die Spalte Art .
- Schritt 2: Fügen Sie in der Spalte neben Ihrer Nachschlagespalte (Typ) das neue Feld hinzu, nach dem Sie gruppieren möchten. Fügen Sie in unserem Beispiel eine Spalte mit einer Bezeichnung hinzu Kategorie .
- Schritt 3: Fügen Sie für jeden Wert in Ihrer unterschiedlichen Werteliste (in diesem Beispiel Typen) den entsprechenden Wert hinzu Kategorie Werte. Geben Sie in unserem einfachen Beispiel entweder ein Computers , Kameras oder Telefone in die Kategorie Säule.
- Schritt 4: Kopiere das Art und Kategorie Datentabelle in Ihre Zwischenablage.
- Schritt 5: Öffnen Sie die Excel-Arbeitsmappe mit den Auftragsdaten in PowerPivot für Excel. Starten Sie das PowerPivot-Fenster. Klicken Sie auf Einfügen, um Ihre neue Nachschlagetabelle einzufügen. Geben Sie der Tabelle einen Namen und stellen Sie sicher, dass Sie dies überprüfen Verwenden Sie die erste Zeile als Spaltenüberschrift. Klicken OK. Sie haben in PowerPivot eine Lookup-Tabelle erstellt.
- Schritt 6: Erstellen Sie eine Beziehung zwischen dem Art Feld in der Auftrag Tisch und die Kategorie Feld in der Nachschlagetabelle. Klicke auf das Design Band und wählen Beziehung erstellen. Treffen Sie die Auswahl im Beziehung erstellen Dialog und klicken Sie auf Erstellen.
Wenn Sie in Excel eine PivotTable basierend auf den PowerPivot-Daten erstellen, können Sie nach neuen gruppieren Kategorie Feld. Beachten Sie, dass PowerPivot für Excel nur Inner Joins unterstützt. Wenn du einen hast Auftragsart In der Nachschlagetabelle fehlen alle entsprechenden Datensätze für diesen Typ in allen PivotTables, die auf den PowerPivot-Daten basieren. Sie müssen dies von Zeit zu Zeit überprüfen.
Datumsdimension (Nachschlagetabelle)
Die Date-Lookup-Tabelle wird höchstwahrscheinlich in den meisten PowerPivot-Projekten für Excel benötigt. Die meisten Datensätze haben einen Typ von Datumsfeldern. Es gibt Funktionen zur Berechnung von Jahr und Monat.
Wenn Sie jedoch den tatsächlichen Monatstext oder das Quartal benötigen, müssen Sie eine komplexe Formel schreiben. Es ist viel einfacher, eine Datumsdimension (Lookup) -Tabelle zu verwenden und mit der Monatsnummer in Ihrem Hauptdatensatz abzugleichen. Sie müssen Ihrer Bestellungstabelle eine Spalte hinzufügen, um die Monatsnummer aus dem Bestelldatum-Feld darzustellen. Die DAX-Formel für Monat in unserem Beispiel ist = MONAT (Bestelldatum). Dadurch wird für jeden Datensatz eine Zahl zwischen 1 und 12 zurückgegeben. In unserer Dimensionstabelle finden Sie alternative Werte, die auf die Monatsnummer verweisen. Dies gibt Ihnen Flexibilität bei Ihrer Analyse. Die vollständige Bemaßungstabelle für Datumsangaben befindet sich unten in Tabelle 2 .
Die Datumsdimension oder Nachschlagetabelle enthält 12 Datensätze. Die Monatsspalte enthält die Werte 1 - 12. Andere Spalten enthalten abgekürzten Monatstext, vollständigen Monatstext, Quartal usw. Hier sind die Schritte:
- Schritt 1: Kopieren Sie die Tabelle aus Tabelle 2 und fügen Sie sie in PowerPivot ein. Sie können diese Tabelle in Excel erstellen, aber ich spare Ihnen Zeit. Sie sollten in der Lage sein, direkt aus den unten ausgewählten Daten einzufügen, wenn Sie Internet Explorer oder Edge verwenden. PowerPivot übernimmt die Tabellenformatierung in meinen Tests. Wenn Sie einen anderen Browser verwenden, müssen Sie möglicherweise zuerst in Excel einfügen und aus Excel kopieren, um die Tabellenformatierung zu übernehmen.
- Schritt 2: Öffnen Sie die Excel-Arbeitsmappe mit den Auftragsdaten in PowerPivot für Excel. Starten Sie das PowerPivot-Fenster. Klicken Einfügen Dadurch wird Ihre Lookup-Tabelle aus der nachstehenden Tabelle oder aus Excel kopiert. Geben Sie der Tabelle einen Namen und stellen Sie sicher, dass Sie dies überprüfen Verwenden Sie die erste Zeile als Spaltenüberschrift. Klicken OK. Sie haben in PowerPivot eine Datumsverweistabelle erstellt.
- Schritt 3: Erstellen Sie eine Beziehung zwischen dem Monat Feld in der Auftrag Tisch und die MonthNumber Feld in der Nachschlagetabelle. Klicke auf das Design Band und wählen Beziehung erstellen. Treffen Sie die Auswahl im Beziehung erstellen Dialog und klicken Sie auf Erstellen.
Durch das Hinzufügen einer Datumsdimension können Sie die Daten in Ihrer PivotTable auch mit anderen Werten aus der Datumssuchtabelle gruppieren. Die Gruppierung nach Viertel oder dem Namen des Monats ist ein Kinderspiel.
Beispiel für Bemaßungstabellen
Tabelle 1
| Art | Kategorie |
| Netbooks | Computer |
| Desktops | Computer |
| Monitore | Computer |
| Projektoren und Bildschirme | Computer |
| Drucker, Scanner und Fax | Computer |
| Computer Setup & Service | Computer |
| Computerzubehör | Computer |
| Digitalkameras | Kamera |
| Digitale Spiegelreflexkameras | Kamera |
| Filmkameras | Kamera |
| Camcorder | Kamera |
| Kameras und Camcorder Zubehör | Kamera |
| Telefone für zu Hause und im Büro | Telefon |
| Touchscreen-Telefone | Telefon |
| Smartphones und PDAs | Telefon |
Tabelle 2
| MonthNumber | MonthTextShort | MonthTextFull | Quartal | Semester |
| 1 | Jan | Januar | Q1 | H1 |
| 2 | Feb. | Februar | Q1 | H1 |
| 3 | Beschädigen | März | Q1 | H1 |
| 4 | April | April | Q2 | H1 |
| 5 | Kann | Kann | Q2 | H1 |
| 6 | Jun | Juni | Q2 | H1 |
| 7 | Jul | Juli | Q3 | H2 |
| 8 | Aug | August | Q3 | H2 |
| 9 | Sep | September | Q3 | H2 |
| 10 | Okt | Oktober | Q4 | H2 |
| 11 | Nov | November | Q4 | H2 |
| 12 | Dez | Dezember | Q4 | H2 |
