Întrebarea de astăzi a lui Quentin care a fost la seminarul meu din Atlanta Power Excel. Quentin trebuie să genereze aceleași 7 întrebări de sondaj pentru fiecare dintre peste 1000 de clienți din Excel.
După cum puteți vedea în această figură, clienții sunt în A. Întrebările care trebuie repetate sunt în coloana D.
Ați putea rezolva acest lucru cu VBA sau formule, dar aceasta este săptămâna Power Query la, așa că voi folosi un truc interesant în Power Query.
Dacă doriți un rând gol între fiecare sondaj, adăugați un număr de ordine și adăugați numărul 7 după ultima întrebare.
Apăsați Ctrl + T din ambele seturi de date. Denumiți al doilea set de date cu un nume pe care îl puteți aminti, ceva de genul Întrebări sau Sondaj.
Din al doilea set de date, utilizați Date, Din tabel.
Se deschide editorul Power Query. Din fila Acasă, alegeți meniul derulant Închidere și încărcare și alegeți Închidere și încărcare în … În dialogul următor, alegeți Creați doar o conexiune.
Sunteți din nou în Excel. Alegeți orice celulă din tabelul clientului din coloana A. Date, Din tabel. Odată ce se deschide Editorul de interogări, faceți clic pe fila Adăugare coloană din panglică și apoi alegeți Coloană personalizată. Formula este =#"Questions"(inclusiv # și ghilimelele).
O nouă coloană apare în editor cu valoarea Tabelului repetată în fiecare rând. Faceți clic pe pictograma Extindeți din antetul coloanei.
Alegeți ambele câmpuri din tabel. Din fila Acasă, alegeți Închideți și încărcați.
Va apărea o nouă foaie de lucru cu cele 7 întrebări repetate pentru fiecare dintre cei peste 1000 de clienți.
Urmăriți videoclipul
Transcriere video
Aflați Excel din Podcast Episodul 2205: Explozia sondajului de împrumut.
Hei, bine ai revenit pe netcast, eu sunt Bill Jelen. Acum, chiar ieri în episodul 2204, Kaylee din Nashville a fost cea care a trebuit să facă o explozie VLOOKUP - pentru fiecare articol din coloana D, aveam o grămadă de articole potrivite în coloana G și aveam nevoie să le explodăm. Deci, dacă Palatul C ar avea 8 obiecte, am obține 8 rânduri.
Acum, astăzi, îl avem pe Quentin. Acum, Quentin a fost la seminarul meu din Atlanta, dar de fapt este din Florida, iar Quentin are aproape 1000 de clienți aici - ei bine, mai mult de 1000 de clienți - în coloana A și pentru fiecare client, el trebuie să creeze acest sondaj - - acest sondaj de 1, 2, 3, 4, 5, 6 întrebări. Și ceea ce voi face aici este că voi adăuga un număr de secvență doar cu numerele de la 1 la 7, astfel încât să pot crea un rând frumos gol între ele. Voi transforma aceste două seturi de date într-un tabel; deci, încercăm să explodăm aceste 7 rânduri pentru fiecare dintre acești 1000 de clienți. Acesta este scopul.
Acum, pot face asta cu VPA; Pot face acest lucru cu formule; dar este un fel de „săptămână de interogare a energiei” aici, suntem pe o fugă de acest lucru este cel de-al treilea exemplu de interogare a noastră la rând, așa că voi folosi Power Query. O să fac din stânga o masă. Voi fi foarte atent să numesc acest lucru nu în Tabelul 1. Îi voi da un nume. Va trebui să refolosim acest nume mai târziu, așa că o voi numi Întrebări - așa. Și atunci acesta va fi Tabelul 2, dar voi redenumi acest lucru pentru a fi Clienți - nu atât de important încât îl redenumesc pe acesta, deoarece este al doilea care trebuie să aibă numele. Deci, vom alege acest lucru; Date; și vom spune De la tabel / gamă. Obțineți și transformați date - aceasta este cunoscută sub numele de Power Query. Este încorporat în Excel 2016. Dacă aveți 2010 sau 2013, pe Windows,nu un Mac, nu iOS, nu Android, puteți descărca gratuit Power Query de la Microsoft.
Deci, vom obține date din tabel / interval; iată masa noastră - nu vom face nimic, ci doar Închide și Încarcă; Închideți & Încărcați în; creați doar o conexiune; bine, și vezi, numele acelei întrebări este Întrebări. Folosește același nume ca și aici. Și apoi ne întoarcem la acesta și, Data; Din tabel / interval; deci, există o listă cu cei peste 1000 de clienți ai noștri.
Hei acum, iată un strigăt către Miguel Escobar, prietenul meu, care este coautorul lui M Is For (DATA) MONKEY). Voi pune un link către asta în videoclip - o carte grozavă despre Power Query - m-a ajutat în acest sens. Vom introduce o nouă coloană personalizată, iar formula personalizată a coloanei este chiar aici: = # "numele interogării". Nu mi-aș fi dat seama asta fără Miguel, așa că mulțumesc lui Miguel pentru asta.
Și când fac clic pe OK, da, nu pare că a funcționat - primim doar masă, masă, masă, dar exact asta am avut ieri cu Kaylee și biletele. Și tot ce trebuie să fac este să extind acest lucru și, de fapt, voi spune că probabil nu am nevoie de Secvență … ei bine, hai să o introducem pentru orice eventualitate. O putem scoate după ce o vedem. În acest moment, avem 1000 de rânduri, iar acum avem 7000 de rânduri - frumos. Acum văd că apare în Secvență, deci nu am nevoie de asta. Voi face clic dreapta și voi elimina doar acea coloană. Și atunci pot acasă; Închideți & Încărcați; și BAM! - ar trebui să avem acum peste 7000 de rânduri cu 6 întrebări și un spațiu gol pentru fiecare client. Quentin a fost încântat de acest lucru la seminar. Tricou grozav, tare - evită VBA, evită o grămadă de formule folosind Index,și lucruri de genul asta - o modalitate excelentă de parcurs.
Dar, hei, azi, lasă-mă să te trimit cu M Is For (DATA) MAIMĂ. Ken Puls și Miguel Escobar au scris cea mai mare carte despre Power Query. Îmi place acea carte; în 2 ore vei deveni un profesionist cu acea carte.
Bine, deci, încheiați astăzi - Quentin trebuie să genereze un sondaj identic pentru 1000 de clienți diferiți. Există 6 sau 7 sau 8 întrebări pentru fiecare client. Acum, am putea face acest lucru cu VBA sau macro, dar, întrucât suntem într-o interogare Power rulată aici, să facem o interogare Power. Am adăugat o întrebare goală suplimentară la Întrebări; Am adăugat un număr de secvență, pentru a mă asigura că spațiul gol rămâne acolo; transformă clienții într-o masă; transformă întrebările într-un tabel; este cu adevărat important să numiți Întrebări ceva ce vă puteți aminti … Eu am numit-o pe a mea „Întrebări”. Adăugați întrebările la Power Query, doar ca conexiune; și apoi, pe măsură ce adăugați clienții la Power Query, creați o nouă coloană personalizată în care formula este: # „numele primei interogări” și apoi Extindeți coloana respectivă în editorul Power Query; Închide &Încărcați înapoi în foaia de calcul și ați terminat. Un truc uimitor - Îmi place Power Query - cel mai mare lucru care i se întâmplă Excel în 20 de ani.
Vreau să îi mulțumesc lui Quentin pentru prezentarea la seminarul meu. A mai fost la seminarul meu de câteva ori înainte … tip grozav. Vreau să vă mulțumesc că ați trecut pe aici. Ne vedem data viitoare pentru un alt netcast de la.
Descărcați fișierul Excel
Pentru a descărca fișierul excel: loan-survey-explosion.xlsx
Power Query continuă să mă uimească. Consultați cartea M este pentru Data Monkey pentru a afla mai multe Power Query.
Gândul Excel al zilei
Le-am cerut prietenilor mei Excel Master sfatul lor despre Excel. Gândul de astăzi să medităm:
„Puteți face orice cu AGGREGATE decât să înțelegeți”.
Liam Bastick
