La domanda di oggi di Quentin che era al mio seminario di Atlanta Power Excel. Quentin deve generare le stesse 7 domande del sondaggio per ciascuno degli oltre 1000 clienti in Excel.
Come puoi vedere in questa figura, i clienti sono in A. Le domande da ripetere sono nella colonna D.
Potresti risolverlo con VBA o formule, ma questa è la settimana di Power Query alle, quindi userò un trucco interessante in Power Query.
Se vuoi una riga vuota tra ogni sondaggio, aggiungi un numero progressivo e aggiungi il numero 7 dopo l'ultima domanda.
Premi Ctrl + T da entrambi i set di dati. Assegna al secondo set di dati un nome che puoi ricordare, qualcosa come Domande o Sondaggio.
Dal secondo set di dati, utilizza Data, From Table.
Si apre l'editor di Power Query. Dalla scheda Home, scegli il menu a discesa Chiudi e carica e scegli Chiudi e carica in…. Nella finestra di dialogo successiva, scegli Crea solo una connessione.
Ora sei tornato in Excel. Scegli una cella qualsiasi nella tabella dei clienti nella colonna A. Dati, da tabella. Una volta aperto l'editor di query, fare clic sulla scheda Aggiungi colonna nella barra multifunzione e quindi selezionare Colonna personalizzata. La formula è =#"Questions"(compreso il # e le virgolette).
Nell'editor viene visualizzata una nuova colonna con il valore Tabella ripetuto in ogni riga. Fare clic sull'icona Espandi nell'intestazione della colonna.
Scegli entrambi i campi nella tabella. Dalla scheda Home, scegli Chiudi e carica.
Apparirà un nuovo foglio di lavoro con le 7 domande ripetute per ciascuno degli oltre 1000 clienti.
Guarda un video
Trascrizione del video
Impara Excel dal podcast Episode 2205: Loan Survey Explosion.
Ehi, bentornato al netcast, sono Bill Jelen. Ora, proprio ieri nell'episodio 2204, è stata Kaylee di Nashville a dover eseguire un'esplosione di CERCA.VERT - per ogni oggetto qui nella colonna D, avevamo un gruppo di oggetti corrispondenti nella colonna G e dovevamo farli esplodere. Quindi, se Palace C avesse 8 elementi, avremmo 8 righe.
Ora, oggi, abbiamo Quentin. Quentin era al mio seminario ad Atlanta, ma in realtà è della Florida, e Quentin ha quasi 1000 clienti qui - beh, più di 1000 clienti - nella colonna A, e per ogni cliente deve creare questo sondaggio- - questo sondaggio di 1, 2, 3, 4, 5, 6 domande. E quello che farò qui è che aggiungerò un numero di sequenza solo con i numeri da 1 a 7, in questo modo, posso creare una bella riga vuota nel mezzo. Trasformerò entrambi questi set di dati in una tabella; quindi, stiamo cercando di far esplodere queste 7 righe per ognuno di questi 1000 clienti. Questo è l'obiettivo.
Ora posso farlo con VPA; Posso farlo con le formule; ma è una specie di "Power Query Week" qui, siamo in esecuzione di questo è il nostro terzo esempio di Power Query di seguito, quindi userò Power Query. Trasformerò questo sinistro in un tavolo. Farò molta attenzione a non nominare questo Tabella 1. Gli darò un nome. Dovremo riutilizzare quel nome più tardi, quindi lo chiamerò Domande, così. E poi questa sarà la Tabella 2, ma la rinominerò come Clienti, non così importante da rinominare questa perché è la seconda che deve avere il nome. Quindi, sceglieremo questo; Dati; e diremo From Table / Range. Ottieni e trasforma i dati: questo è noto come Power Query. È integrato in Excel 2016. Se hai il 2010 o il 2013, su Windows,non un Mac, non iOS, non Android, puoi scaricare Power Query gratuitamente da Microsoft.
Quindi, otterremo i dati dalla tabella / intervallo; ecco la nostra tabella - non faremo nulla, solo Chiudi e carica; Chiudi e carica in; solo Crea una connessione; va bene, e vedi, il nome di quella Query è Domande. Usa lo stesso nome di qui. E poi torniamo a questo, e, Data; Da tabella / intervallo; quindi, c'è un elenco dei nostri 1000 o più clienti.
Ehi, ora, ecco un grido a Miguel Escobar, il mio amico, che è il coautore di M Is For (DATA) MONKEY). Metterò un collegamento a questo nel video - ottimo libro su Power Query - mi ha aiutato in questo. Metteremo una colonna personalizzata nuova di zecca e la formula della colonna personalizzata è proprio qui: = # "il nome della query". Non l'avrei mai capito senza Miguel, quindi grazie a Miguel per questo.
E quando faccio clic su OK, sì, non sembra che abbia funzionato - otteniamo solo tavolo, tavolo, tavolo, ma è esattamente quello che avevamo ieri con Kaylee e la biglietteria. E tutto quello che devo fare è espandere questo, e in realtà sto per dire che probabilmente non ho bisogno della sequenza … beh, mettiamola per ogni evenienza. Possiamo tirarlo fuori dopo averlo visto. In questo momento, abbiamo 1000 righe e ora abbiamo 7000 righe, bellissime. Ora posso vedere che appare in Sequence, quindi non ne ho bisogno. Farò clic con il pulsante destro del mouse e rimuoverò solo quella colonna. E poi posso tornare a casa; Chiudi e carica; e BAM! - ora dovremmo avere più di 7000 righe con 6 domande e uno spazio vuoto per ogni cliente. Quentin era entusiasta di quello al seminario. Trucco fantastico e interessante: evita VBA, evita un sacco di formule usando Index,e cose del genere … ottimo modo per andare.
Ma, ehi, oggi, lascia che ti mandi via con M Is For (DATA) MONKEY. Ken Puls e Miguel Escobar hanno scritto il miglior libro su Power Query. Amo quel libro; in 2 ore diventerai un professionista con quel libro.
Va bene, quindi, riepilogo oggi: Quentin deve generare un sondaggio identico per 1000 clienti diversi. Ci sono 6 o 7 o 8 domande per ogni cliente. Ora, potremmo farlo con VBA o macro, ma, poiché siamo in esecuzione di Power Query qui, eseguiamo una Power Query. Ho aggiunto una domanda in più alle domande; Ho aggiunto un numero di sequenza, per assicurarmi che lo spazio vuoto rimanga lì; trasformare i clienti in un tavolo; trasformare le domande in una tabella; è molto importante che tu chiami Domande qualcosa che puoi ricordare - Ho chiamato il mio "Domande". Aggiungere le domande a Power Query, solo come connessione; quindi, mentre aggiungi i clienti a Power Query, crea una nuova colonna personalizzata in cui la formula è: # "il nome della prima query" e quindi Espandi quella colonna nell'editor di Power Query; Vicino &Ricarica il foglio di calcolo e il gioco è fatto. Un trucco straordinario, adoro Power Query, la cosa migliore che è capitata a Excel in 20 anni.
Voglio ringraziare Quentin per aver partecipato al mio seminario. È stato al mio seminario un paio di volte prima … bravo ragazzo. Voglio ringraziarti per essere passato. Ci vediamo la prossima volta per un altro netcast da.
Scarica il file Excel
Per scaricare il file excel: prestito-sondaggio-esplosione.xlsx
Power Query continua a stupirmi. Consulta il libro M è per Data Monkey per saperne di più su 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:
"Puoi fare qualsiasi cosa con AGGREGATE tranne capirlo."
Liam Bastick
