Kaley di Nashville sta lavorando a un foglio di calcolo per l'emissione di biglietti. Per ogni evento sceglie un piano di ticketing. Quel piano di emissione dei biglietti potrebbe indicare da 4 a 16 tipi di biglietti per l'evento. Kaley vuole una formula che andrà alla tabella di ricerca e restituirà * tutte * le corrispondenze, inserendo nuove righe come appropriato.
Sebbene non disponga di CERCA.VERT in grado di risolvere questo problema, i nuovi strumenti di Power Query integrati in Excel 2016 possono risolverlo.
Nota
Se disponi della versione Windows di Excel 2010 o Excel 2013, puoi scaricare Power Query gratuitamente da Microsoft. Sfortunatamente, Power Query non è ancora disponibile per Excel per Android, Excel per ios o Excel per Mac.
Per illustrare l'obiettivo: Mike McCann and the Mechanics apparirà all'Allen Theater con il piano di biglietti C.Poiché ci sono quattro righe corrispondenti nella tabella di ricerca, Kaley vuole quattro righe che dicano Mike McCann and the Mechanics, ciascuna con una corrispondenza diversa da la tabella di ricerca.
Seleziona una cella nella tabella originale. Premi Ctrl + T per contrassegnare i dati come tabella. Nella scheda Strumenti tabella, rinominare la tabella da Table1 a Shows. Ripeti per la tabella di ricerca, chiamandola Biglietti.
Seleziona una cella nella tabella Spettacoli. Dalla scheda Dati, scegli Da tabella / intervallo.
Dopo l'apertura dell'editor di Power Query, aprire l'elenco a discesa Chiudi e carica e scegliere Chiudi e carica in….
Nella finestra di dialogo Importa dati, scegli Crea solo una connessione.
Vai al tavolo dei biglietti. Ripetere i passaggi per creare solo una connessione ai ticket. Dovresti vedere entrambe le connessioni nel riquadro Query:
Seleziona una cella vuota. Scegli dati, ottieni dati, combina query, unisci.
Ci sono sei passaggi nella finestra di dialogo Unisci. Il 3 ° e il 4 ° non mi sembrano intuitivi.
- Scegli Programmi dal menu a discesa in alto
- Scegli Biglietti dal secondo menu a discesa.
- Fare clic sull'intestazione Ticket Plan in alto per selezionare quella colonna come chiave esterna nella tabella Show.
- Fare clic sull'intestazione per il piano del biglietto in basso per selezionare quella colonna come campo chiave nella tabella di ricerca.
- Apri il tipo di unione e scegli Interno (solo righe corrispondenti).
- Fare clic su OK
I risultati sono inizialmente deludenti. Vengono visualizzati tutti i campi della tabella 1 e una colonna che dice Tabella, Tabella, Tabella.
Fare clic sull'icona Espandi nella parte superiore della colonna Biglietti.
Deseleziona Ticket Plan poiché hai già quel campo. Il campo rimanente si chiamerà Tipo Ticket.Ticket a meno che non deselezioni Usa nome originale come prefisso.
Successo! Ogni riga per ogni spettacolo esplode in più righe.
Non sono particolarmente soddisfatto dell'ordinamento dei dati. L'ordinamento per data fa sì che i tipi di biglietto vengano ordinati in modo strano.
Guarda un video
Nel caso di oggi, il video è stato girato dopo la scrittura dell'articolo. Suggerisco di aggiungere una colonna di sequenza ai tipi di biglietto per controllare l'ordinamento.
Trascrizione del video
Impara Excel da Podcast, episodio 2204: Return All VLOOKUPs.
Ehi, bentornato al netcast, sono Bill Jelen. La domanda di oggi da Nashville Music City. Ero laggiù a Nashville, qualcuno è responsabile della pianificazione del caricamento dei biglietti in un sistema di biglietteria e quindi ecco cosa abbiamo: abbiamo un elenco di eventi - eventi imminenti - abbiamo la data, il luogo e un piano dei biglietti. Quindi, anche se qualcosa si tiene a Palazzo, potrebbero esserci piani di biglietti diversi - tipo, forse il pavimento è configurato, sai, con posti a sedere o forse è solo una stanza in piedi, giusto?
Quindi, a seconda del tipo di piano di biglietti, devi venire qui alla tabella di ricerca e trovare tutti gli eventi corrispondenti, ed essenzialmente faremo quella che chiamo un'esplosione di CERCA. Quindi, se qualcosa è a Hannah C, andranno a Hannah C e se ci sono-- 1, 2, 3, 4, 5, 6-- 7 elementi in Hannah C, avremo per restituire sette righe, il che significa che dovrai inserire altre sei righe e copiare quei dati. Tutto a posto.
Ora, non lo faremo affatto con un CERCA.VERT, ma ottieni il concetto: stiamo facendo un CERCA.VERT e stiamo restituendo tutte le risposte come nuove righe. Va bene, quindi, prenderò entrambe queste tabelle e le trasformerò in una tabella reale con Ctrl + T. I primi chiamati Tabella 1 - nome orribile, chiamiamolo Eventi o Spettacoli, chiamiamolo Spettacoli, così - e il secondo, ora, ehi, ecco cosa ho imparato perché ho praticato questo - dobbiamo avere un campo sequenza qui. Quindi = RIGA (A1), fare doppio clic e copiarlo, quindi copiare e incollare valori speciali. Tutto a posto. Ora lo trasformeremo in un tavolo: Ctrl + T, e lo chiameremo Tickets.
Tutto a posto. Quindi abbiamo spettacoli, abbiamo i biglietti. Vado alla scheda Dati e sono qui nella cosa degli spettacoli, voglio dire che voglio ottenere i miei dati da una tabella o da un intervallo: questo è Power Query, tra l'altro. Se sei tornato in Excel 2010 o 2013, puoi scaricarlo gratuitamente da Microsoft, scaricare lo strumento Power Query. Se sei su un Mac o iOS o Android, mi dispiace, nessuna Power Query per te. Va bene, quindi da un tavolo o da un intervallo … trova qualcuno che ha un … trova un amico che ha un … PC Windows e fallo configurare. Tutto a posto. Ecco una tabella, non faremo nulla per questo, basta chiudere e caricare, chiudere e caricare su, e poi dire "solo creare connessione", perfetto. Andremo qui alla nostra seconda tabella: Ottieni dati, Da una tabella o intervallo, non stiamo facendo nulla per questa, Chiudi e carica,Chiudi e carica in, "Crea solo connessione", OK. Quindi quello che abbiamo ora, è che abbiamo una connessione alla prima tabella e una connessione alla seconda tabella. Non uniremo questi due, che essenzialmente è come fare CERCA.VERT, o un giunto di database, immagino, è davvero quello che è. Combina query, stiamo andando a Merge. Tutto a posto.
Ora, sette cose che devi fare in questa finestra di dialogo - ed è un po 'confuso - sceglieremo Show come prima tabella; scegli Biglietti come secondo tavolo; scegli quale campo hanno in comune e questo può essere più campi (puoi fare clic tenendo premuto il tasto Ctrl) ma in questo caso c'è un solo piano di biglietti; e poi Ticket Plan; e poi cambieremo il tipo di join in un join interno con "solo le righe corrispondenti". Tutto a posto. Ora, fai clic su OK e pensi che l'intero problema sarà risolto, ma sei semplicemente distrutto perché ecco tutti i dati da A - non hanno inserito nessuna nuova riga - e qui, solo un campo noioso e stupido chiamato Tickets che ha solo Table, Table, Table, hah.
Ma, per fortuna, in cima c'è un'icona Espandi, e la espanderemo - non ho bisogno di prendere un piano, ce l'ho già - Tipo di biglietto e sequenza. Non voglio che si chiami Tickets.TicketType, che è ciò che Power Query vuole fare, quindi deseleziono questa casella. Tutto a posto. Al momento abbiamo 17 righe di dati; quando faccio clic su OK, BAM! C'è l'esplosione. Quindi, Michael Seeley e lo Starlighter si presentano con tutti i diversi tipi di biglietti, come questo. Va bene, e vedere questi tipi di biglietti apparire in sequenza, è fantastico. Ma Michael Seeley non è il prossimo spettacolo, il prossimo spettacolo è il 5 giugno. Quindi, quando provo a ordinare questo per Data, questo mi fa impazzire, non riesco a spiegarlo. Ordina per data e Mike Man and the Mechanics arriva a 65, ma poi i biglietti sono tutti incasinati. Essi'sei sulla sequenza sbagliata, e quindi è per questo che ho dovuto fare questa sequenza - si sente in questo modo. Posso ordinare per sequenza. Quindi ora, 6, 5, bello, e poi all'interno di quello, i biglietti sono corretti. E in realtà, a questo punto, non abbiamo più bisogno di questa colonna. Quindi posso fare clic con il pulsante destro del mouse e rimuovere, quindi Chiudi e carica, questa volta in realtà Chiudi e carica, non Chiudi e carica su, e abbiamo il nostro risultato. Tutto a posto.
Quindi siamo passati da un elenco di eventi a tutto questo grande elenco, ma ecco la parte fantastica: ho sbagliato tutto, Mike Man and Mechanics non è Palace B, è Palace C.Quindi torno all'originale in alto a destra -angolo a mano per ulteriori informazioni sul libro.
Tutto a posto. Argomenti in questo episodio: Kaley a Nashville deve eseguire un CERCA.VERT per restituire tutte le partite, di solito inserendo nuove righe. Ed è un database di biglietti, va bene? Quindi la chiamerò esplosione CERCA.VERT perché ogni spettacolo esploderà fino a 16 righe. Useremo Power Query per risolvere questo problema e ho appreso che la data apparirà nella sequenza sbagliata a meno che non aggiungiamo un campo Sequenza al tipo di biglietto. Trasforma entrambi i set in una tabella con Ctrl + T; nominateli come spettacoli e biglietti; e quindi da ciascuna tabella, Ottieni dati, Da tabella, Chiudi e carica, per creare solo una connessione; ripetere per l'altro tavolo; quindi Dati, Ottieni dati, Combina query, Unisci; e poi quella finestra di dialogo, mi confonde abbastanza: scegli Eventi, scegli Biglietti, fai clic su Tipo di biglietto in entrambi, cambia il giunto in un join interno,fai clic su OK e ottieni quel risultato orribilmente deludente in cui è solo una colonna che dice Tabella, Tabella, Tabella, Tabella; fare clic sull'icona Espandi nella parte superiore di quella; scegli il campo Sequenza biglietti; non aggiungere il prefisso al nome della tabella; e puoi ordinare per data, ordinare per sequenza; Chiudi e carica nel foglio di calcolo. La cosa bella è che se i dati sottostanti cambiano, basta aggiornare e ottenere i risultati.
Ora, ehi, per scaricare la cartella di lavoro utilizzata dal video di oggi, visita l'URL in basso nella descrizione di YouTube. C'è anche un elenco dei prossimi seminari: mi piacerebbe vederti a uno dei miei seminari Power Excel dal vivo.
Voglio ringraziare Kaley per essersi presentato a Nashville e avermi fatto questa grande domanda. Voglio che tu sia passato. Ci vediamo la prossima volta per un altro netcast da.
Scarica il file Excel
Per scaricare il file excel: return-all-vlookups.xlsx
Power Query continua a stupirmi. Questa è la seconda di una serie di tre giorni in cui la risposta è Power Query:
- Martedì: converti una colonna di data / ora in una sola data
- Oggi: restituisci tutti i CERCA.VERT
- Giovedì: crea un sondaggio per ciascuno dei 1100 articoli
Ho un'intera playlist di YouTube di cose che ho finito per risolvere con Power Query.
Excel pensiero del giorno
Ho chiesto ai miei amici di Excel Master per i loro consigli su Excel. Pensiero di oggi su cui riflettere:
"Quando sei in dubbio, usa la funzione ROUND!"
Mike Girvin
