Excel Data Model (Power Pivot) consente di connettere un ampio set di dati dettagliati di valori effettivi a un budget di primo livello utilizzando tabelle di unione.
I budget vengono effettuati al livello più alto: entrate per linea di prodotti per regione per mese. I dati effettivi si accumulano lentamente nel tempo: fattura per fattura, voce per voce. Confrontare il piccolo file di budget con i voluminosi dati Actual è stato un problema per sempre. Adoro questo trucco di Rob Collie, alias PowerPivotPro.com.
Per impostare l'esempio, hai una tabella del budget di 54 righe: una riga al mese per regione per prodotto.
Il file della fattura è a livello di dettaglio: 422 righe finora quest'anno.
Non esiste CERCA.VERT al mondo che ti consenta di abbinare questi due set di dati. Ma, grazie a Power Pivot (noto anche come modello di dati in Excel 2013+), questo diventa facile.
È necessario creare piccole tabelle minuscole che io chiamo "joiners" per collegare i due set di dati più grandi. Nel mio caso, Prodotto, Regione e Data sono in comune tra le due tabelle. La tabella Product è una piccola tabella a quattro celle. Idem per la regione. Crea ognuno di questi copiando i dati da una tabella e utilizzando Rimuovi duplicati.
La tabella del calendario a destra era effettivamente più difficile da creare. I dati del budget hanno una riga al mese, che cade sempre alla fine del mese. I dati della fattura mostrano le date giornaliere, solitamente nei giorni feriali. Quindi, ho dovuto copiare il campo Data da entrambi i set di dati in una singola colonna e quindi rimuovere i duplicati per assicurarmi che tutte le date fossero rappresentate. Ho quindi =TEXT(J4,"YYYY-MM")creato una colonna Mese dalle date giornaliere.
Se non si dispone del componente aggiuntivo Power Pivot completo, è necessario creare una tabella pivot dalla tabella Budget e selezionare la casella Aggiungi questi dati al modello di dati.
Come discusso nel suggerimento precedente, quando aggiungi campi alla tabella pivot, dovrai definire sei relazioni. Sebbene tu possa farlo con sei visite alla finestra di dialogo Crea relazione, ho attivato il mio componente aggiuntivo Power Pivot e ho usato la visualizzazione diagramma per definire le sei relazioni.
Ecco la chiave per far funzionare tutto questo: sei libero di utilizzare i campi numerici da Budget e da Actual. Ma se vuoi mostrare Regione, Prodotto o Mese nella tabella pivot, devono provenire dalle tabelle di joiner!
Ecco una tabella pivot con i dati provenienti da cinque tabelle. La colonna A proviene dal falegname della regione. La riga 2 proviene dal falegname di Calendar. L'affettatrice del prodotto proviene dal falegname del prodotto. I numeri del budget provengono dalla tabella Budget e i numeri effettivi provengono dalla tabella Fattura.
Questo funziona perché le tabelle di joiner applicano filtri alla tabella Budget e Effettivo. È una tecnica bellissima e mostra che Power Pivot non è solo per i big data.
Guarda un video
- Hai un piccolo set di dati di budget top-down
- Si desidera eseguire il confronto con un set di dati dei valori effettivi bottom-up
- I valori effettivi potrebbero provenire da un registro delle fatture
- Il modello di dati ti consentirà di confrontare questi set di dati di dimensioni diverse
- Trasforma entrambi i set di dati in una tabella Ctrl + T
- Per ogni campo di testo in base al quale si desidera eseguire il report, creare una tabella di joiner
- Copia i valori e rimuovi i duplicati
- Per le date, puoi includere date da entrambe le tabelle e convertirle a fine mese
- Rendi i joiners tabelle Ctrl + T
- Facoltativo ma utile per nominare tutte e cinque le tabelle
- Crea una tabella pivot da Budget e scegli il modello di dati
- Crea una tabella pivot utilizzando Budget e Effettivo dalle tabelle originali
- Tutti gli altri campi devono provenire dalle tabelle di joiner
- Aggiungi filtri dei dati in base al prodotto
- Crea tre relazioni da Budget a Falegnami
- Crea tre relazioni da Actual a Joiners
- Domani: come la creazione di relazioni è più semplice con Power Pivot e le formule DAX
Trascrizione del video
Impara Excel dal podcast, episodio 2016 - Budget top-down vs Actual bottom-up!
Ehi, sto podcasting l'intero libro, fai clic sulla "i" nell'angolo in alto a destra e segui la playlist.
Ehi, lo interrompo, questo è Bill Jelen di 15 minuti da adesso. Mi rendo conto che ora questo è un podcast incredibilmente lungo e sei tentato di fare clic su di esso, ma lascia che ti dica solo questo breve. Se sei in Excel 2013 e hai mai avuto una piccola tabella del budget e una enorme tabella dei valori effettivi e devi mapparli insieme, questa è una nuova straordinaria capacità che abbiamo in Excel 2013, che non molte persone hanno spiegato e probabilmente non lo sai. Se sei tu, sei nel 2013 e hai bisogno di mappare questi due set di dati, prenditi il tempo, forse oggi, forse domani, magari aggiungilo alla watch list, ne vale la pena, è una tecnica straordinaria.
Va bene, ecco cosa abbiamo, sul lato sinistro abbiamo un budget, questo budget, è fatto al livello più alto, dall'alto verso il basso, a destra per ogni linea di prodotti, per ogni regione, per ogni mese, c'è un budget . Non molti record qui, conteggio di 55, sul lato destro stiamo cercando di confrontare questo con i valori effettivi. I valori effettivi provengono da un registro delle fatture, quindi abbiamo Regione, Prodotto e Entrate, ma sono fatture individuali, molti più dati qui, siamo già a metà anno e ho già 423 record. Va bene, quindi come si associano questi 55 a questi 423? Potrebbe essere difficile da fare con CERCA.VERT, dovresti prima riassumere, ma per fortuna in Excel 2013, il modello di dati lo rende davvero, davvero facile. Ciò di cui abbiamo bisogno per consentire a questo grande tavolo enorme di comunicare con questo piccolo tavolo sono intermediari, io li chiamo falegnami.Piccoli tavolini, Prodotto, Regione e Calendario, uniremo il budget a queste tre tabelle, uniremo l'attuale a queste tre tabelle e miracolosamente la tabella Pivot funzionerà. Va bene, quindi ecco come lo facciamo.
Prima di tutto devo creare i falegnami, quindi prendo questo campo Prodotto dalla colonna A e lo copio nella colonna F, quindi Dati, Rimuovi duplicati, fai clic su OK e ci rimane un piccolo tavolino, 1 intestazione 3 righe. Stessa cosa per Regione, prendi le regioni, Ctrl + C, vai alla colonna G, Incolla, Rimuovi duplicati, fai clic su OK, 3 righe 1 intestazione, va bene. Ora per le date, le date non sono le stesse, si tratta di date di fine mese, in realtà sono memorizzate come date di fine mese e questi sono giorni feriali. Prenderò entrambi gli elenchi, Ctrl + C il secondo elenco e lo incollerò qui, Ctrl + V, quindi prenderò l'elenco più breve, lo copierò e lo incollerò sotto, va bene. Ed è davvero fastidioso che, anche se queste sono archiviate come date, vengono visualizzate come mesi e Rimuovi duplicati non le vedrà uguali.Quindi, prima di utilizzare Rimuovi duplicati, devo cambiarlo in una data breve. Scegli quei dati, Dati, Rimuovi duplicati, fai clic su OK, quindi un piccolo ordinamento qui per farlo funzionare.
Va bene, ora non voglio creare rapporti per data giornaliera, quindi aggiungerò una colonna qui, una colonna di ricerca che dice Mese, e questo sarà EOMONTH quella data,, 0, che ci porterà a alla fine del mese. Lo formatterà come data breve e lo copierà, va bene. Ora, dobbiamo trasformare ognuno di questi in una tabella Ctrl + T, quindi da qui Ctrl + T, La mia tabella ha intestazioni, bellissimo. Quelli piccoli, non si rende conto che quelli sono intestazioni lassù, quindi dobbiamo assicurarci di spuntarlo e Ctrl + T, va bene, e chiamano queste tabelle Table1, Table2, Table3, nomi davvero noiosi, giusto? Quindi li rinominerò e li chiamerò BudTable, ProdTable, RegTable, my CalTable e quindi ActTable, va bene.
Partiamo dalla prima tabella e dal modo in cui non utilizzeremo PowerPivot oggi, faremo tutto questo con il modello di dati. Quindi, Excel 2013 o più recente, hai questo Inserisci, Tabella pivot, selezioneremo la casella "Aggiungi questi dati al modello di dati", fare clic su OK e otteniamo il nostro elenco di campi con il pulsante magico Tutto, che consente scegliere da tutte e cinque le tabelle nella cartella di lavoro, Effettivo, Budget, Calendario, Prodotto, Regione. Va bene, quindi i numeri verranno dalla tabella Budget, inserirò il budget lì, e dalla tabella Actual inserirò l'effettivo lì, ma poi ecco il punto per il resto della tabella Pivot. Qualsiasi altro campo di testo che metteremo nell'area delle righe o nell'area delle colonne o come affettatrici, devono provenire dai join, devono provenire da quelle tabelle tra le tabelle.
Va bene, quindi dalla tabella Calendario prenderemo quel campo Mese e lo inseriremo in alto, ignoreremo le altre relazioni in questo momento. Creerò le relazioni, ma voglio crearle tutte in una volta. E la tabella Regione, metti le regioni a lato. Potrei mettere i prodotti a lato, ma in realtà userò la tabella Product come un'affettatrice, quindi Analizza, Inserisci affettatrice, di nuovo devi andare su Tutti se non hai ancora usato la tabella Prodotto. Quindi vai su Tutti e vedrai che il Prodotto è disponibile per creare come affettatrice dai prodotti, in questo modo. Va bene ora, a questo punto non abbiamo creato relazioni, quindi tutti questi numeri sono sbagliati. E le relazioni che dobbiamo creare, dobbiamo creare 3 tabelle da questa piccola tabella di budget, una per i prodotti, una per le regioni, una per il calendario,sono 3 relazioni. Quindi dobbiamo creare relazioni dalla tabella Actual all'area Product in Calendar, quindi un totale di 6 tabelle. E sì, questo sarebbe sicuramente più facile se avessimo PowerPivot, ma non lo facciamo o supponiamo di no.
Quindi userò il vecchio modo, il dialogo Crea qui, dove abbiamo la tabella Budget a sinistra, e useremo il campo Regione e lo collegheremo alla tabella Regione, il campo Regione . Perfetto, ne vengono creati 1/6. Sceglierò Crea, di nuovo dalla tabella Budget andiamo al Prodotto, quindi lo collegherò alla tabella Prodotto, al Prodotto, fare clic su OK. Dalla tabella Budget il campo Data, andiamo alla tabella Calendar, e il campo Fate, facciamo clic su OK, siamo a metà strada, va bene. Dalla tabella Actuals, andiamo Region, alla tabella Region, facciamo clic su OK, dalla tabella Actuals al Product e dalla tabella Actuals al Calendar. In realtà prenderò i Valori e lo farò scendere di lato, va bene. Progetta, Layout report, Mostra in formato tabulare per ottenere una visualizzazione che preferisco, Ripeti tutte le etichette degli elementi, va bene,questo è assolutamente fantastico! Ora abbiamo questa piccola piccola tabella, una cinquantina di record in questa tabella di centinaia di record e abbiamo creato un'unica tabella pivot grazie al modello di dati. Per ogni posizione in cui possiamo vedere il budget, possiamo vedere le entrate, è suddiviso per regione, è suddiviso per mese ed è divisibile per prodotto.
Questo concetto mi è venuto in mente da Rob Collie, che gestisce Power Pivot Pro, e Rob ha creato molti libri là fuori, il suo ultimo è "Power Pivot e Power BI". Penso che questo fosse effettivamente nel libro "Power Pivot Alchemy", è quello che ho visto e ho detto "Bene, questo, anche se non ho milioni di righe da segnalare tramite Power Pivot, questo è uno che sarebbe hanno fatto un'ENORME differenza nella mia vita, avendo due set di dati di dimensioni non corrispondenti e dovendo riferire da entrambi ". Bene, questo esempio e molti altri sono in questo libro, alla fine avrò l'intero podcast del libro, sembra che ci vorranno due mesi e mezzo. Ma puoi ottenere l'intero libro oggi, alla stessa ora, andare lì, acquistare il libro, $ 10 per l'e-book, $ 25 per il libro stampato, e puoi avere tutti quei suggerimenti in una volta.
Va bene, un episodio davvero lungo qui: abbiamo un piccolo budget top-down e un bottom up Actual, sono di dimensioni diverse, ma utilizzando il modello di dati in Excel 2013 … E a proposito, se sei nel 2010, potresti , in teoria, fai ciò ottenendo il componente aggiuntivo Power Pivot e ripeti tutti questi passaggi nel 2010. Trasforma entrambi i set di dati in una tabella Ctrl + T, quindi unisci le tue tabelle per qualsiasi cosa su cui desideri generare rapporti, etichetta di riga, etichetta di colonna o filtri dei dati, quindi copia questi valori e Rimuovi duplicati per le date. In realtà ho preso valori da entrambe le tabelle, perché c'erano alcuni valori univoci in ciascuna, e poi ho usato EOMONTH per uscire, fare in modo che quelle tabelle di joiner fossero tabelle controllate. È facoltativo, ma ho chiamato tutte e 5 le tabelle, perché è più facile quando imposti quelle relazioni, piuttosto che essere chiamato Tabella1,Table2, Table3.
Quindi, inizia dalla tabella Budget, Inserisci, Tabella pivot, seleziona la casella per Modello di dati e quindi crea una tabella pivot utilizzando Budget e Effettivo. Tutto il resto proviene dalle tabelle di joiner, quindi Regione e Mese nell'area delle righe e delle colonne, i filtri dei dati provengono dalla tabella Prodotto. E poi abbiamo dovuto creare 3 relazioni dal budget ai falegnami, 3 relazioni dall'effettivo ai falegnami e abbiamo una fantastica tabella pivot. Ora domani daremo un'occhiata all'utilizzo della scheda PowerPivot e alla creazione di alcuni calcoli aggiuntivi. Quindi tutto questo è possibile, è quando vogliamo inserire un campo calcolato, è allora che devi pagare $ 2 extra al mese per ottenere la versione Pro Plus di Office 365.
Ehi, grazie a Rob Collie di Power Pivot Pro per questo suggerimento, e grazie a te per essere passato, ci vediamo la prossima volta per un altro netcast da!
Download file
Scarica il file di esempio qui: Podcast2016.xlsx
