Una delle cose che elencho di più su PowerPivot per Excel è la possibilità di aggiungere tabelle di ricerca ai tuoi set di dati. La maggior parte delle volte, i dati con cui stai lavorando non hanno tutti i campi necessari per la tua analisi. Ad esempio, potresti avere un campo data ma devi raggruppare i dati per trimestre. È possibile scrivere una formula, ma è più semplice creare una semplice tabella di ricerca all'interno dell'ambiente PowerPivot.
Puoi anche utilizzare questa tabella di ricerca per un altro raggruppamento, ad esempio il nome del mese e il primo / secondo semestre dell'anno. In termini di data warehousing, stai effettivamente creando una tabella delle dimensioni data. In questo articolo, fornirò un paio di tabelle dimensionali di esempio per migliorare il progetto PowerPivot per Excel.
Nuova tabella Dimensione testo (ricerca)
Consideriamo una tabella con i dati dell'ordine. Si supponga che la tabella contenga campi per il cliente, data dell'ordine, totale dell'ordine e tipo di ordine. Ci concentreremo sul campo del tipo di ordine. Supponiamo che il campo del tipo di ordine includa valori come:
- netbook
- Desktops
- monitor
- proiettori
- stampanti
- scanner
- Fotocamere digitali
- Fotocamere digitali SLR
- Macchine da presa
- Videocamere
- Telefoni d'ufficio
- Smart phone
- PDA
- Accessori per telefoni cellulari
In realtà, avresti dei codici per questi, ma per mantenere semplice questo esempio, supponiamo che questi siano i valori effettivi nella tabella degli ordini. Utilizzando PowerPivot per Excel, è possibile raggruppare facilmente gli ordini per tipo di ordine.
E se volessi un altro gruppo? Ad esempio, supponi di aver bisogno di un raggruppamento di categorie come computer, fotocamere e telefoni. La tabella degli ordini non ha un campo categoria, ma puoi facilmente crearlo come tabella di ricerca in PowerPivot per Excel. La tabella di ricerca dei campioni completa è in basso a Tabella 1 .
Ecco i passaggi:
- Passo 1: È necessario un elenco distinto dal campo del tipo per la tabella di ricerca. Questo sarà il tuo campo di ricerca. Dal tuo set di dati, crea un distinto elenco di valori dal campo del tipo di ordine. Inserisci l'elenco distinto di tipi in una cartella di lavoro di Excel. Etichetta la colonna genere .
- Passo 2: Nella colonna accanto alla colonna di ricerca (Tipo), aggiungi il nuovo campo su cui desideri raggruppare. Nel nostro esempio, aggiungi una colonna con un'etichetta chiamata Categoria .
- Passaggio 3: Per ogni valore nel tuo distinto elenco di valori (tipi in questo esempio), aggiungi il corrispondente Categoria valori. Nel nostro semplice esempio, inserisci entrambi computers , Macchine fotografiche o Cellulari nel Categoria colonna.
- Passaggio 4: Copia il genere e Categoria tabella dei dati negli Appunti.
- Passaggio 5: Aprire la cartella di lavoro di Excel con i dati dell'ordine in PowerPivot per Excel. Avvia la finestra di PowerPivot. Fai clic su Incolla che porterà nella tua nuova tabella di ricerca. Dai al tavolo un nome e assicurati di controllare Usa la prima riga come intestazioni di colonna. Clic ok. È stata creata una tabella di ricerca in PowerPivot.
- Passaggio 6: Creare una relazione tra il genere campo nel Ordine tavolo e il Categoria campo nella tabella di ricerca. Clicca sul Design nastro e scegliere Crea relazione. Effettua le selezioni in Crea relazione finestra di dialogo e fare clic Creare.
Quando si crea una tabella pivot in Excel in base ai dati di PowerPivot, sarà possibile raggruppare in base al nuovo Categoria campo. Ricorda che PowerPivot per Excel supporta solo Inner Joins. Se hai un tipo di ordine mancante dalla tabella di ricerca, tutti i record corrispondenti per quel tipo mancheranno da qualsiasi tabella pivot in base ai dati di PowerPivot. Dovrai controllarlo di volta in volta.
Tabella dimensione data (ricerca)
La tabella di ricerca della data sarà probabilmente necessaria nella maggior parte dei progetti PowerPivot per Excel. La maggior parte dei set di dati ha alcuni tipi di campi di date. Ci sono funzioni per calcolare l'anno e il mese.
Tuttavia, se hai bisogno del testo del mese reale o del trimestre, devi scrivere una formula complessa. È molto più semplice includere una tabella di dimensioni Date (ricerca) e combinarla con il numero del mese nel set di dati principale. Dovrai aggiungere una colonna alla tua tabella degli ordini per rappresentare il numero del mese dal campo della data dell'ordine. La formula DAX per mese nel nostro esempio è = MESE (Data ordine). Ciò restituirà un numero compreso tra 1 e 12 per ciascun record. La nostra tabella delle dimensioni fornirà valori alternativi, che si collegano al numero del mese. Ciò ti fornirà flessibilità nella tua analisi. La tabella delle dimensioni della data di esempio completa è riportata di seguito Tavolo 2 .
La dimensione della data o la tabella di ricerca includerà 12 record. La colonna del mese avrà i valori 1 - 12. Altre colonne includeranno il testo del mese abbreviato, il testo del mese completo, il trimestre, ecc. Ecco i passaggi:
- Passo 1: Copia la tabella dalla tabella 2 in basso e incollala in PowerPivot. È possibile creare questa tabella in Excel ma sto risparmiando tempo. Dovresti essere in grado di incollare direttamente dai dati selezionati qui sotto se utilizzi Internet Explorer o Edge. PowerPivot riprende la formattazione della tabella durante i miei test. Se si utilizza un altro browser, potrebbe essere necessario incollare prima Excel e copiarlo da Excel per raccogliere la formattazione della tabella.
- Passo 2: Aprire la cartella di lavoro di Excel con i dati dell'ordine in PowerPivot per Excel. Avvia la finestra di PowerPivot. Clic Incolla che porterà nella tua tabella di ricerca copiata dalla tabella sottostante o da Excel. Dai al tavolo un nome e assicurati di controllare Usa la prima riga come intestazioni di colonna. Clic ok. È stata creata una tabella di ricerca della data in PowerPivot.
- Passaggio 3: Crea una relazione tra il Mese campo nel Ordine tavolo e il MonthNumber campo nella tabella di ricerca. Clicca sul Design nastro e scegliere Crea relazione. Effettua le selezioni in Crea relazione finestra di dialogo e fare clic Creare.
Ancora una volta, con l'aggiunta di una dimensione di data, sarà possibile raggruppare i dati nella tabella pivot utilizzando uno qualsiasi dei diversi valori dalla tabella di ricerca della data. Il raggruppamento per trimestre o il nome del mese sarà un gioco da ragazzi.
Tabelle di dimensione campione (ricerca)
Tabella 1
| genere | Categoria |
| netbook | Computer |
| Desktops | Computer |
| monitor | Computer |
| Proiettori e schermi | Computer |
| Stampanti, scanner e fax | Computer |
| Computer Setup e servizio | Computer |
| Accessori per computer | Computer |
| Fotocamere digitali | telecamera |
| Fotocamere digitali SLR | telecamera |
| Macchine da presa | telecamera |
| Videocamere | telecamera |
| Fotocamere e Videocamere Accessori | telecamera |
| Telefoni per casa e ufficio | Telefono |
| Touch Screen Phones | Telefono |
| Smart phone e PDA | Telefono |
Tavolo 2
| MonthNumber | MonthTextShort | MonthTextFull | Trimestre | Semestre |
| 1 | Jan | gennaio | Q1 | H1 |
| 2 | Febbraio | febbraio | Q1 | H1 |
| 3 | mar | marzo | Q1 | H1 |
| 4 | aprile | aprile | Q2 | H1 |
| 5 | potrebbe | potrebbe | Q2 | H1 |
| 6 | giugno | giugno | Q2 | H1 |
| 7 | luglio | luglio | Q3 | H2 |
| 8 | agosto | agosto | Q3 | H2 |
| 9 | settembre | settembre | Q3 | H2 |
| 10 | ottobre | ottobre | Q4 | H2 |
| 11 | novembre | novembre | Q4 | H2 |
| 12 | dicembre | dicembre | Q4 | H2 |
