En av de tingene jeg skriver mest om PowerPivot for Excel, er muligheten til å legge til oppslagstabeller i datasettene dine. Mesteparten av tiden har dataene du jobber med, ikke alle feltene du trenger for analysen din. Du kan for eksempel ha et datafelt, men må gruppere dataene dine etter kvartalet. Du kan skrive en formel, men det er lettere å lage et enkelt oppslagstabell i PowerPivot-miljøet.
Du kan også bruke dette oppslagstabellen til en annen gruppe, for eksempel månedens navn og første / andre halvdel av året. I data warehousing vilkår, er du faktisk opprette en date dimensjon tabellen. I denne artikkelen skal jeg gi deg et par eksempeldimensjonstabeller for å forbedre PowerPivot for Excel-prosjektet.
Ny tekstdimensjon (oppslag) tabell
La oss vurdere et bord med bestillingsdata. Anta at tabellen har felt for kunde, ordre dato, ordre total og ordre type. Vi skal fokusere på ordretypefeltet. Anta at rekkefølgefeltet inkluderer verdier som:
- netbooks
- Stasjonære PC-er
- skjermer
- projektorer
- skrivere
- skannere
- Digitale kameraer
- Digitale speilreflekskameraer
- Filmkameraer
- Videokameraer kameraer~~POS=HEADCOMP
- Kontor-telefoner
- Smarte telefoner
- PDA
- Mobiltelefoner Tilbehør
I virkeligheten vil du ha koder for disse, men for å holde dette eksemplet enkelt, antar disse er de faktiske verdiene i ordrebordet. Ved å bruke PowerPivot for Excel, vil du enkelt kunne gruppere dine bestillinger etter ordre type.
Hva om du ville ha en annen gruppering? For eksempel, anta at du trenger en kategori gruppering som datamaskiner, kameraer og telefoner. Ordrebordet har ikke et kategorifelt, men du kan enkelt lage det som et oppslagstabell i PowerPivot for Excel. Det komplette utvalgsoppslagstabellen er under i Tabell 1 .
Her er trinnene:
- Trinn 1: Du trenger en tydelig liste fra typefeltet for oppslagstabellen. Dette blir ditt oppslagsfelt. Fra datasettet, opprett en distinkt liste over verdier fra feltet ordretype. Skriv inn den forskjellige listen over typer i en Excel-arbeidsbok. Merk kolonnen Type .
- Steg 2: I kolonnen ved siden av oppslagskolonnen (Type) legger du til det nye feltet du vil gruppere på. I vårt eksempel legger du til en kolonne med en etikett som heter Kategori .
- Trinn 3: For hver verdi i din distinkte liste over verdier (typer i dette eksempelet), legg til tilsvarende Kategori verdier. I vårt enkle eksempel, skriv inn enten datamaskiner , kameraer eller telefoner inn i det Kategori kolonne.
- Trinn 4: Kopier Type og Kategori Datatabell i utklippstavlen.
- Trinn 5: Åpne Excel-arbeidsboken med bestillingsdataene i PowerPivot for Excel. Start PowerPivot-vinduet. Klikk Lim inn som vil ta med det nye oppslagstabellen. Gi tabellen et navn og sørg for at du sjekker Bruk første rad som kolonneoverskrifter. Klikk OK. Du har opprettet et oppslagstabell i PowerPivot.
- Trinn 6: Opprett et forhold mellom Type felt i Rekkefølge bord og Kategori feltet i oppslagstabellen. Klikk på Design bånd og velg Opprett forhold. Lag valgene i Opprett forhold dialog og klikk Skape.
Når du lager en PivotTable i Excel basert på PowerPivot-dataene, kan du gruppere etter det nye Kategori felt. Husk at PowerPivot for Excel bare støtter Inner Joins. Hvis du har en bestillings type Mangler fra oppslagstabellen din, vil alle tilsvarende poster for den typen mangle fra en PivotTable basert på PowerPivot-dataene. Du må sjekke dette fra tid til annen.
Dato Dimensjon (Oppslag) Tabell
Datooppslagstabellen vil trolig være nødvendig i de fleste av PowerPivot for Excel-prosjekter. De fleste datasett har noen type datafelt (er). Det er funksjoner for å beregne år og måned.
Men hvis du trenger den faktiske måneden teksten eller kvartalet, må du skrive en kompleks formel. Det er mye lettere å inkludere et Date-dimensjon (oppslag) -tabell og samsvare det med månedstallet i hoveddatasettet. Du må legge til en kolonne i bestillingsbordet ditt for å representere månedenummeret fra feltet for bestillingsdato. DAX-formelen for måned i vårt eksempel er = Måned (Bestillingsdato). Dette returnerer et tall mellom 1 og 12 for hver post. Vårt dimensjonstabell vil gi alternative verdier, som knytter til månedstallet. Dette gir deg fleksibilitet i analysen. Dimensjonstabellen for komplett utvalgsdata er under i Tabell 2 .
Daterdimensjonen eller oppslagstabellen vil inneholde 12 poster. Måneders kolonnen vil ha verdiene 1 - 12. Andre kolonner vil inkludere forkortet måneds tekst, fullmåned tekst, kvart, etc. Her er trinnene:
- Trinn 1: Kopier tabellen fra tabell 2 nedenfor og lim inn i PowerPivot. Du kan lage dette bordet i Excel, men jeg sparer deg tid. Du bør kunne lim inn direkte fra de valgte dataene nedenfor hvis du bruker Internet Explorer eller Edge. PowerPivot plukker opp tabellformateringen i testingen min. Hvis du bruker en annen nettleser, må du kanskje lime inn i Excel først og kopiere den fra Excel for å hente opp tabellformatering.
- Steg 2: Åpne Excel-arbeidsboken med bestillingsdataene i PowerPivot for Excel. Start PowerPivot-vinduet. Klikk Lim inn som vil bringe opp oppslagstabellen kopiert fra tabellen nedenfor eller fra Excel. Gi tabellen et navn og sørg for at du sjekker Bruk første rad som kolonneoverskrifter. Klikk OK. Du har opprettet et datautslagstabell i PowerPivot.
- Trinn 3: Lag et forhold mellom Måned felt i Rekkefølge bord og MonthNumber feltet i oppslagstabellen. Klikk på Design bånd og velg Opprett forhold. Lag valgene i Opprett forhold dialog og klikk Skape.
Igjen, med tillegg av en datadimensjon, vil du kunne gruppere dataene i pivottabellen din ved hjelp av noen av de forskjellige verdiene fra datooppslagstabellen. Gruppering per kvartal eller navnet på måneden vil være et snap.
Sample Dimension (Oppslag) Tabeller
Tabell 1
| Type | Kategori |
| netbooks | Datamaskin |
| Stasjonære PC-er | Datamaskin |
| skjermer | Datamaskin |
| Projektorer og skjermer | Datamaskin |
| Skrivere, skannere og faks | Datamaskin |
| Datamaskinoppsett og service | Datamaskin |
| Datamaskiner Tilbehør | Datamaskin |
| Digitale kameraer | Kamera |
| Digitale speilreflekskameraer | Kamera |
| Filmkameraer | Kamera |
| Videokameraer kameraer~~POS=HEADCOMP | Kamera |
| Kameraer og videokameraer Tilbehør | Kamera |
| Hjem og kontor telefoner | telefon |
| Berøringsskjerm telefoner | telefon |
| Smart telefoner og PDAer | telefon |
Tabell 2
| MonthNumber | MonthTextShort | MonthTextFull | Fjerdedel | semester |
| 1 | jan | januar | Q1 | H1 |
| 2 | februar | februar | Q1 | H1 |
| 3 | mar | mars | Q1 | H1 |
| 4 | april | april | Q2 | H1 |
| 5 | Kan | Kan | Q2 | H1 |
| 6 | juni | juni | Q2 | H1 |
| 7 | juli | juli | Q3 | H2 |
| 8 | august | august | Q3 | H2 |
| 9 | september | september | Q3 | H2 |
| 10 | oktober | oktober | Q4 | H2 |
| 11 | november | november | Q4 | H2 |
| 12 | desember | desember | Q4 | H2 |
