Non hai Power Pivot? Non importa. La maggior parte di Power Pivot è integrata in Excel 2013 e ancora di più in Excel 2016. Oggi, il nostro consiglio di Ash è unire le tabelle in una tabella pivot.
Ogni mercoledì per sette settimane propongo uno dei consigli preferiti di Ash Sharma. Ash è un product manager del team di Excel. Il suo team ti offre tabelle pivot e molte altre cose buone. Oggi, la funzione preferita di Ash è l'unione di più set di dati utilizzando le relazioni e il modello di dati.
Supponiamo che il tuo reparto IT ti fornisca il set di dati mostrato nelle colonne A: D. Ci sono campi per cliente e mercato. È necessario combinare determinati mercati in regioni. Ogni cliente appartiene a un settore. Regione e Settore non sono nei dati originali, ma sono disponibili tabelle di ricerca per fornire queste informazioni.
Normalmente, appiattiresti i dati utilizzando CERCA.VERT per estrarre i dati dalle tabelle arancioni e gialle nella tabella blu. Ma poiché il campo chiave non si trova sul lato sinistro di ogni tabella, dovrai passare a INDICE e CONFRONTA o riorganizzare le tabelle di ricerca.
A partire da Excel 2013, puoi lasciare le tabelle di ricerca dove si trovano e combinarle nel rapporto della tabella pivot stesso.
Affinché questa tecnica funzioni, tutte e tre le tabelle devono essere formattate come una tabella. Seleziona una cella in ogni set di dati e scegli Home, Formatta come tabella o premi Ctrl + T. Le tre tabelle saranno inizialmente chiamate Table1, Table2 e Table3. Uso la scheda Progettazione strumenti tabella della barra multifunzione e rinomino ciascuna tabella. Cambio anche il colore di ogni tavolo. In questo esempio, la tabella blu si chiama Data. La tabella arancione è RegionTable. La tabella gialla è SectorTable.
Nota
Alcuni ti diranno che dovresti usare nomi geek come Fact, TblSector e TblRegion. Se qualcuno ti infastidisce in questo modo, ruba la sua tasca protettiva e fagli sapere che preferisci i nomi che suonano in inglese.
Per rinominare una tabella, digita un nuovo nome nella casella sul lato sinistro della scheda Progettazione strumenti tabella. I nomi delle tabelle non devono contenere spazi.
Una volta definite le tre tabelle, vai alla scheda Dati e fai clic su Relazioni.
Nella finestra di dialogo Gestisci relazioni, fare clic su Nuovo. Nella finestra di dialogo Crea relazione, specificare che il campo Cliente della tabella dati è correlato al campo Cliente della tabella di settore. Fare clic su OK.
Definire un'altra nuova relazione tra il campo Market nei campi Data e RegionTable. Dopo aver definito entrambe le relazioni, le vedrai nella finestra di dialogo Gestisci relazioni.
Congratulazioni: hai appena creato un modello di dati nella tua cartella di lavoro. È ora di costruire una tabella pivot.
Seleziona la cella vuota in cui desideri visualizzare la tabella pivot. Per impostazione predefinita, la finestra di dialogo Crea tabella pivot sceglierà Usa modello di dati di questa cartella di lavoro. La posizione della tabella pivot verrà impostata per impostazione predefinita sulla cella che hai scelto. Fare clic su OK.
L'elenco Campi tabella pivot elencherà tutte e tre le tabelle. Usa il triangolo a sinistra di una tabella per espandere il nome della tabella e mostrarti i campi.
Espandi la tabella dei dati. Seleziona il campo Entrate. Si sposterà automaticamente nell'area Valori. Espandi la tabella di settore. Scegli il campo Settore. Si sposterà nell'area Righe. Espandere la RegionTable. Trascina il campo Regione nell'area Colonne. Ora avrai una tabella pivot che riassume i dati delle tre tabelle.
Nota
In ogni libro che ho scritto prima di oggi, utilizzo una tecnica diversa per costruire questo rapporto. Dopo aver definito le tre tabelle, scelgo la cella A1 e Inserisci, Tabella pivot. Controllo la casella Aggiungi questi dati al modello di dati. Nell'elenco Campi tabella pivot, seleziona Tutto dalla parte superiore dell'elenco. Scegli i campi per il report, quindi definisci le relazioni dopo il fatto. La tecnica sopra descritta sembra più fluida e in realtà richiede un po 'di pianificazione in anticipo. Le persone che usano Option Explicit nel loro codice VBA apprezzerebbero sicuramente questo metodo.
Le relazioni nel modello di dati fanno sembrare Excel più simile a Access o SQL Server, ma con tutta la bontà di Excel.
Mi piace chiedere al team di Excel le loro funzionalità preferite. Ogni mercoledì condividerò una delle loro risposte. Grazie ad Ash Sharma per aver fornito questa idea.
Excel pensiero del giorno
Ho chiesto ai miei amici di Excel Master per i loro consigli su Excel. Pensiero di oggi su cui riflettere:
"Non cercare se hai una relazione"
John Michaloudis
