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 effettivi è 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: 1 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 di controllo 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.
