En av de saker jag listar mest om PowerPivot för Excel är möjligheten att lägga till uppslagstabeller till dina dataset. För det mesta har de data du arbetar med inte alla fält du behöver för din analys. Du kan till exempel ha ett datumfält, men måste gruppera dina uppgifter per kvartal. Du kan skriva en formel, men det är lättare att skapa ett enkelt uppslagstabell i PowerPivot-miljön.
Du kan också använda det här uppslagstabellen för en annan gruppering, som månadens namn och första / andra halvåret. I datalagringsvillkor skapar du faktiskt ett datumdimensionstabell. I den här artikeln kommer jag att ge dig ett antal exempel dimension tabeller för att förbättra ditt PowerPivot för Excel-projektet.
Ny textmått (uppslag) tabell
Låt oss överväga en tabell med orderdata. Antag att tabellen har fält för kund, beställningsdatum, order totalt och beställningstyp. Vi kommer att fokusera på ordningstypfältet. Antag att fältet Order Type innehåller värden som:
- netbooks
- Stationära datorer
- Bildskärmar
- projektorer
- skrivare
- skannrar
- Digital kameror
- Digital SLR-kameror
- Filmkameror
- Videokameror
- Office-telefoner
- Smarta telefoner
- handdatorer
- Mobiltelefoner Tillbehör
I verkligheten skulle du ha koder för dessa men för att hålla detta exempel enkelt, antar att det här är de faktiska värdena i ordertabellen. Med PowerPivot för Excel kan du enkelt gruppera dina beställningar per ordertyp.
Vad händer om du vill ha en annan gruppering? Anta exempelvis att du behöver en gruppgruppering som datorer, kameror och telefoner. Orderbordet har inget kategorifält, men du kan enkelt skapa det som ett uppslagstabell i PowerPivot for Excel. Det fullständiga provuppslagstabellen är nedanfört bord 1 .
Här är stegen:
- Steg 1: Du behöver en särskild lista från typfältet för ditt uppslagstabell. Detta kommer att vara ditt sökfält. Skapa en särskild lista över värden från fältet Order Type från din dataset. Ange den distinkta listan med typer i en Excel-arbetsbok. Märk kolumnen Typ .
- Steg 2: I kolumnen bredvid din uppslagskolumn (Typ) lägg till det nya fältet du vill gruppera på. I vårt exempel lägger du till en kolumn med en etikett som heter Kategori .
- Steg 3: För varje värde i din distinkta värdeslista (typer i det här exemplet), lägg till motsvarande Kategori värden. I vårt enkla exempel anger du antingen datorer , kameror eller telefoner in i Kategori kolumn.
- Steg 4: Kopiera Typ och Kategori datatabell i ditt urklipp.
- Steg 5: Öppna Excel-arbetsboken med orderuppgifterna i PowerPivot for Excel. Starta PowerPivot-fönstret. Klicka på Klistra in som kommer att ta in ditt nya lookup-bord. Ge bordet ett namn och se till att du kolla Använd första raden som kolumnrubriker. Klick ok. Du har skapat ett uppslagstabell i PowerPivot.
- Steg 6: Skapa ett förhållande mellan Typ fält i Ordning bord och Kategori fält i uppslagstabellen. Klicka på Design band och välj Skapa förhållande. Gör valen i Skapa förhållande dialog och klicka Skapa.
När du skapar en pivottabell i Excel baserat på PowerPivot-data kan du gruppera med din nya Kategori fält. Tänk på att PowerPivot för Excel endast stöder Innehållsförbindelser. Om du har en ordertyp saknas från ditt tittartabell, kommer alla motsvarande poster för den typen att saknas från vilken pivottabell som helst baserat på PowerPivot-data. Du måste kontrollera detta från tid till annan.
Datum Dimension (Uppslag) Tabell
Datumuppslagstabellen kommer sannolikt att behövas i de flesta av dina PowerPivot-projekt för Excel. De flesta dataset har någon typ av datumfält. Det finns funktioner för att beräkna året och månaden.
Men om du behöver den faktiska månadens text eller kvartalet måste du skriva en komplex formel. Det är mycket lättare att inkludera ett datumdimension (uppslag) -tabell och matcha det med månadsnumret i din huvuddatasats. Du måste lägga till en kolumn i beställningstabellen för att representera månadsnumret från fältet för orderdatum. DAX-formuläret för månad i vårt exempel är = MÅNAD (beställningsdatum). Detta kommer att returnera ett tal mellan 1 och 12 för varje post. Vårt dimensionstabell kommer att ge alternativa värden, som länkar till månadsnumret. Detta ger dig flexibilitet i din analys. Det fullständiga provdatumsdimensionstabellen är under i Tabell 2 .
Datumdimensionen eller uppslagstabellen kommer att innehålla 12 poster. Månadskolumnen kommer att ha värdena 1 - 12. Andra kolumner kommer att innehålla förkortad månadstext, helmånadstext, kvart osv. Här följer stegen:
- Steg 1: Kopiera tabellen från Tabell 2 nedan och klistra in i PowerPivot. Du kan skapa denna tabell i Excel men jag sparar tid. Du bör kunna klistra in direkt från vald data nedan om du använder Internet Explorer eller Edge. PowerPivot plockar upp tabellen formatering i min testning. Om du använder en annan webbläsare måste du kanske klistra in i Excel först och kopiera den från Excel för att hämta tabellformatering.
- Steg 2: Öppna Excel-arbetsboken med orderuppgifterna i PowerPivot for Excel. Starta PowerPivot-fönstret. Klick Klistra som kommer att ta in ditt tittartabell kopierat från tabellen nedan eller från Excel. Ge bordet ett namn och se till att du kolla Använd första raden som kolumnrubriker. Klick ok. Du har skapat ett datumuppslagstabell i PowerPivot.
- Steg 3: Skapa ett förhållande mellan Månad fält i Ordning bord och MonthNumber fält i uppslagstabellen. Klicka på Design band och välj Skapa förhållande. Gör valen i Skapa förhållande dialog och klicka Skapa.
Återigen, med tillägg av en datumdimension kommer du att kunna gruppera data i din PivotTable med någon av de olika värdena från datumuppslagstabellen. Gruppering per kvartal eller månadens namn kommer att vara en snap.
Provmått (uppslag) tabeller
bord 1
| Typ | Kategori |
| netbooks | Dator |
| Stationära datorer | Dator |
| Bildskärmar | Dator |
| Projektorer och skärmar | Dator |
| Skrivare, Skannrar och fax | Dator |
| Datorinstallation och service | Dator |
| Datorer Tillbehör | Dator |
| Digital kameror | Kamera |
| Digital SLR-kameror | Kamera |
| Filmkameror | Kamera |
| Videokameror | Kamera |
| Kameror och videokameror Tillbehör | Kamera |
| Hem & Kontor Telefoner | Telefon |
| Pekskärm telefoner | Telefon |
| Smarta telefoner och handdatorer | Telefon |
Tabell 2
| MonthNumber | MonthTextShort | MonthTextFull | Fjärdedel | Termin |
| 1 | jan | januari | Q1 | H1 |
| 2 | februari | februari | Q1 | H1 |
| 3 | mar | Mars | Q1 | H1 |
| 4 | april | april | Q2 | H1 |
| 5 | Maj | Maj | Q2 | H1 |
| 6 | juni | juni | Q2 | H1 |
| 7 | juli | juli | Q3 | H2 |
| 8 | augusti | augusti | Q3 | H2 |
| 9 | september | september | Q3 | H2 |
| 10 | oktober | oktober | Q4 | H2 |
| 11 | november | november | Q4 | H2 |
| 12 | december | december | Q4 | H2 |
