Evita CERCA.VERT utilizzando il modello di dati. Quindi, hai due tabelle che devono essere unite con CERCA.VERT prima di poter creare una tabella pivot. Se hai Excel 2013 o più recente su un PC Windows, ora puoi farlo in modo semplice e facile.
Supponi di disporre di un set di dati con informazioni su prodotti, clienti e vendite.
Il reparto IT si è dimenticato di inserire il settore. Ecco una tabella di ricerca che mappa il cliente al settore. Tempo per un CERCA.VERT, giusto?
Non è necessario eseguire CERCA.VERT per unire questi set di dati se si dispone di Excel 2013 o Excel 2016. Entrambe queste versioni di Excel hanno incorporato il motore Power Pivot nel nucleo di Excel. (È anche possibile eseguire questa operazione utilizzando il componente aggiuntivo Power Pivot per Excel 2010, ma ci sono alcuni passaggi aggiuntivi.)
Sia nel set di dati originale che nella tabella di ricerca, usa Home, Formatta come tabella. Nella scheda Strumenti tabella, rinomina la tabella da Table1 a qualcosa di significativo. Ho utilizzato dati e settori.
Seleziona una cella nella tabella dati. Scegli Inserisci, Tabella pivot. A partire da Excel 2013, è disponibile una casella aggiuntiva Aggiungi questi dati al modello di dati che è necessario selezionare prima di fare clic su OK.
Viene visualizzato l'elenco Campi tabella pivot con i campi della tabella Dati. Scegli Entrate. Poiché stai utilizzando il modello di dati, viene visualizzata una nuova riga all'inizio dell'elenco, che offre Active o All. Fare clic su tutto.
Sorprendentemente, l'elenco Campi tabella pivot offre tutte le altre tabelle nella cartella di lavoro. Questo è rivoluzionario. Non hai ancora eseguito un CERCA.VERT. Espandi la tabella Settori e scegli Settore. Succedono due cose per avvertirti che c'è un problema.
Innanzitutto, la tabella pivot viene visualizzata con lo stesso numero in tutte le celle.
Forse l'avviso più sottile è che una casella gialla appare nella parte superiore dell'elenco Campi tabella pivot che indica che è necessario creare una relazione. Scegli Crea. (Se sei in Excel 2010 o 2016, porta fortuna con Auto-Detect.)
Nella finestra di dialogo Crea relazione, hai quattro menu a discesa. Scegli Dati in Tabella, Cliente in Colonna (estero) e Settori in Tabella correlata. PowerPivot compilerà automaticamente la colonna corrispondente nella colonna correlata (primaria). Fare clic su OK.
La tabella pivot risultante è un mashup dei dati originali e della tabella di ricerca. Nessun VLOOKUP richiesto.
Guarda un video
- A partire da Excel 2013, la finestra di dialogo Tabella pivot offre il modello di dati
- Questa è la parola in codice per Power Pivot Engine
- Per utilizzare il modello di dati, crea una tabella Ctrl + T da ogni tabella nella cartella di lavoro
- Costruisci una tabella pivot dalla prima tabella
- Nell'elenco dei campi della tabella pivot, passare da Attivo a Tutti
- Scegli un campo dalla tabella di ricerca
- Crea la relazione o il rilevamento automatico
- Auto-Detect non c'era nel 2013
- Grazie a Colin Michael e Alejandro Quiceno per aver suggerito Power Pivot in generale.
Trascrizione del video
Impara Excel da podcast, episodio 2014 - Elimina CERCA.VERT!
Durante il podcast dell'intero libro, fai clic sulla "i" nell'angolo in alto a destra per la playlist!
Ehi, bentornato al netcast, sono Bill Jelen, in realtà si chiama Elimina CERCA.VERT con il modello di dati! Ora mi scuso, questo è Excel 2013 e versioni successive, se torni in Excel 2010, devi scaricare il componente aggiuntivo Power Pivot, che ovviamente è gratuito nel 2010. Quindi quello che abbiamo qui è che abbiamo il nostro set di dati principale, c'è un campo Cliente qui, e poi ho una piccola tabella che mappa il cliente per settore, devo creare entrate totali per settore, giusto? Questo è un CERCA.VERT, basta fare un CERCA.VERT, ma ehi, grazie a Excel 2013, non dobbiamo fare un CERCA.VERT! Ho trasformato entrambi questi in una tabella e in Strumenti tabella, Design, rinomino le tabelle, chiamo questo Settore e lo chiamo Dati, per trasformarlo in una tabella, basta scegliere una cella, premere Ctrl + T. Quindi, se abbiamo alcune intestazioni e alcuni numeri, quando premi Ctrl + T,chiedono "Dove sono i dati per la tua tabella?", La mia tabella ha intestazioni, e poi la chiamano Tabella3, tu la chiami qualcos'altro. Va bene, è così che ho creato quei due tavoli, mi sbarazzerò di questo tavolo, va bene.
Quindi, affinché questo trucco funzioni, tutti i dati devono risiedere in tabelle. Andiamo alla scheda Inserisci, scegliamo Tabella pivot e proprio qui in basso, aggiungi questi dati al modello di dati. Sembra molto innocuo, vero? Non c'è niente come un punto lampo che dice "Ehi, ti permetterà di fare cose incredibili!" E quello che stanno dicendo qui, quello che stanno cercando di non dire è che- Oh, a proposito, ogni copia di Excel 2013 ha il motore Power Pivot dietro. Sai, se sei in Office 365, paghi $ 10 al mese e loro vogliono che tu paghi $ 12 o $ 15 al mese per ottenere Power Pivot, i due o cinque dollari extra. Bene, ehi, shh, non dirlo, in realtà hai già la maggior parte di Power Pivot in Excel 2013. Va bene, quindi faccio clic su OK, ci vuole un po 'più di tempo per caricare il modello di dati, va bene, ma va bene, e subito Qui,nei campi della tabella pivot, ho un elenco di tutti i campi. Quindi, voglio mostrare Revenue, certamente, ma ciò che è diverso è qui con Active and All. Quando scelgo Tutto, ottengo tutte le tabelle nella cartella di lavoro. Va bene, quindi vado ai Settori e ho detto che voglio mettere il settore nell'area Righe. Ora, inizialmente, il rapporto sarà sbagliato, vedrai i 6,7 milioni fino in fondo e questo avvertimento giallo qui dirà che devi creare una relazione.e questo avvertimento giallo qui dirà che devi creare una relazione.e questo avvertimento giallo qui dirà che devi creare una relazione.
Va bene ora, nel 2010 con Power Pivot, avrebbe semplicemente offerto AutoDetect, nel 2013 hanno eliminato AutoDetect e nel 2016 hanno ripristinato AutoDetect, va bene? Dovrei mostrarti come appare CREA, ma quando clicco su questo pulsante CREA, oh sì, è tutto, va bene, bene. Quindi dalla nostra prima tabella Data, ho un campo chiamato Customer, dalla relativa tabella Sectors, ho un campo chiamato Customer, e poi fai clic su OK, va bene. Ma lascia che ti mostri quanto è interessante AutoDetect, se ti capita di essere nel 2016, lì, l'hanno capito, quanto è fantastico, giusto? Non devi preoccuparti di CERCA.VERT e la virgola cade alla fine, se CERCA.VERT ti fa male alla testa, adorerai il modello di dati. Ha preso quei due tavoli, li ha uniti insieme, sai, come avrebbe fatto Access, immagino, e ha creato una tabella pivot, assolutamente incredibile.Quindi controlla il modello di dati la prossima volta che devi fare un CERCA.VERT tra due tabelle. Bene, questo e tutti gli altri 40 suggerimenti sono nel libro, fare clic su quella "i" nell'angolo in alto a destra. Puoi acquistare il libro, avere un riferimento incrociato completo a tutta questa serie di video, tutto agosto, tutto settembre, diamine, potremmo persino portare a termine ottobre per completare il tutto.
Va bene, ricapitoliamo oggi: a partire da Excel 2013, la finestra di dialogo Tabella pivot offre qualcosa chiamato Modello di dati, è la parola in codice per il motore Power Pivot. Prima di creare le tue tabelle pivot, fai Ctrl + T per creare una tabella da ogni cartella di lavoro, ho impiegato più tempo per nominarle. Crea una tabella pivot dalla prima tabella, quindi nell'elenco dei campi, vai in alto e cambia da Attivo a Tutti. Scegli un campo dalla tabella di ricerca, quindi ti avviserà che devi creare una relazione o AutoDetect, nel 2013, devi fare clic su CREA. Ma è cosa, 4 clic per crearlo, 5 se conti il pulsante OK, quindi davvero, davvero facile da fare.
Va bene, Colin, Michael e Alejandro Quiceno hanno suggerito Power Pivot in generale per i libri, grazie a loro, grazie a te per essere passato, ci vediamo la prossima volta per un altro netcast da!
Download file
Scarica il file di esempio qui: Podcast2014.xlsx
