Mai kérdés Quentintől, aki részt vett az Atlanta Power Excel szemináriumomon. A Quentinnek ugyanazt a 7 felmérési kérdést kell generálnia minden több mint 1000 ügyfél számára az Excelben.
Amint ezen az ábrán látható, az ügyfelek A-ban vannak. Az ismétlendő kérdések a D oszlopban találhatók.
Megoldhatod ezt VBA-val vagy képletekkel, de ez a Power Query hét, tehát egy jó trükköt fogok használni a Power Query-ben.
Ha az egyes felmérések között üres sort szeretne, adjon hozzá egy sorszámot, és az utolsó kérdés után adja hozzá a 7-es számot.
Nyomja meg a Ctrl + T gombot mindkét adathalmazban. Nevezze el a második adatsort olyan névvel, amelyre emlékezhet, például kérdésekre vagy felmérésre.
A második adatsortól használja az Adatok, a Táblázat menüpontot.
Megnyílik a Power Query szerkesztő. A Kezdőlapon válassza a Bezárás és betöltése legördülő menüt, majd válassza a Bezárás és betöltés… lehetőséget. A következő párbeszédpanelen válassza a Csak kapcsolat létrehozása lehetőséget.
Most visszatért az Excel programba. Válassza ki az A oszlop ügyféltáblázatának bármelyik celláját. Adatok, táblázatból. Miután megnyílt a Lekérdezés szerkesztő, kattintson a szalag Add Oszlop hozzáadása fülére, majd válassza az Egyéni oszlop lehetőséget. A képlet az =#"Questions"(beleértve a # és az idézőjeleket).
Egy új oszlop jelenik meg a szerkesztőben, ahol a táblázat értéke megismétlődik minden sorban. Kattintson az Oszlop fejlécében a Kibontás ikonra.
Válassza ki a táblázat mindkét mezőjét. A Kezdőlapon válassza a Bezárás és betöltés lehetőséget.
Megjelenik egy új munkalap, amelyben megismétli a 7 kérdést az 1000+ ügyfél számára.
Videót nézni
Videó átirat
Ismerje meg az Excel programot a 2205. Podcast epizódból: Hitelfelmérés robbanása
Hé, üdvözlöm újra a netcasten, Bill Jelen vagyok. Most, tegnap a 2204-es epizódban, a nashville-i Kaylee-nek kellett VLOOKUP-robbanást végrehajtania - minden elemre, itt a D oszlopban, volt egy megfelelő rakás elemünk a G oszlopban, és ezeket fel kellett robbantani. Tehát, ha a C palotának 8 eleme lenne, 8 sort kapnánk.
Most, ma van Quentin. Most Quentin az atlantai szemináriumomon volt, de valójában floridai származású, és Quentinnek csaknem 1000 vásárlója van itt - nos, több mint 1000 ügyfél - az A oszlopban, és minden ügyfél számára létre kell hoznia ezt a felmérést - - ez a felmérés 1, 2, 3, 4, 5, 6 kérdésből áll. És amit itt fogok tenni, hozzáadok egy sorozatszámot csak az 1-től 7-ig terjedő számokkal, így így szép üres sort hozhatok létre közöttük. Mindkét adatsort táblába fogom tenni; Tehát megpróbáljuk felrobbantani ezt a 7 sort ebből az 1000 ügyfélből. Ez a cél.
Most megtehetem ezt a VPA-val; Megtehetem ezt képletekkel; de ez itt egyfajta "Power Query Week", ez a sorozat a harmadik Power Query példánk, ezért a Power Query-t fogom használni. Ezt a baloldalt asztalkává fogom tenni. Nagyon óvatosan fogom nevezni ezt, nem pedig az 1. táblázatot. Nevet fogok adni neki. Később újra fel kell használnunk ezt a nevet, úgyhogy Kérdéseknek fogom nevezni - így. És akkor ez a 2. táblázat lesz, de át fogom nevezni Ügyfeleknek - nem olyan fontos, hogy átnevezzem ezt, mert a másodiknak kell lennie a névnek. Tehát ezt választjuk; Adat; és azt fogjuk mondani, hogy Táblázatból / Tartományból. Adatok lekérése és átalakítása - ez Power Query néven ismert. Be van építve az Excel 2016-ba. Ha 2010-es vagy 2013-as verziója van, Windows rendszeren,Nem Mac, nem iOS, nem Android, ingyen letöltheti a Power Query-t a Microsoft-tól.
Tehát adatokat fogunk kapni a táblázatból / tartományból; itt van az asztalunk - nem fogunk tenni semmit, csak bezárni és betölteni; Bezárás és betöltés ide; csak Kapcsolat létrehozása; rendben, és nézze meg, hogy a lekérdezés neve Kérdések. Ugyanazt a nevet használja, mint itt. És akkor visszatérünk erre az adatra; Táblázatból / tartományból; Tehát ott van egy lista az 1000 vagy több ügyfelünkről.
Hé, most kiáltás Miguel Escobarnak, a barátomnak, aki az M Is For (DATA) MONKEY társszerzője. Teszek egy linket erre a videóba - a Power Queryről szóló nagyszerű könyv - ebben segített. Be fogunk tenni egy teljesen új Egyéni oszlopot, és az Egyéni oszlop képlet itt található: = # "a lekérdezés neve". Soha nem jöttem volna rá erre Miguel nélkül, ezért köszönet érte Miguelnek.
És amikor rákattintok az OK gombra, igen, úgy tűnik, hogy nem működött - csak asztalt, asztalt, asztalt kapunk, de pontosan ez volt tegnap Kaylee-vel és a jeggyel. És csak annyit kell tennem, hogy ezt kibővítem, és tulajdonképpen azt fogom mondani, hogy valószínűleg nincs szükségem a sorozatra … nos, tegyük be minden esetre. Kivihetjük, miután meglátjuk. Most 1000 sorunk van, most pedig 7000 sorunk van - gyönyörű. Most már látom, hogy a Sequence-ben jelenik meg, ezért nincs rá szükségem. Kattintson a jobb gombbal, és eltávolítom azt az egyetlen oszlopot. És akkor haza tudok menni; Bezárás & Betöltés; és BAM! - Mostantól több mint 7000 sorral kell rendelkeznünk 6 kérdéssel, és minden ügyfél számára egy üres helyet kell megadni. Quentin el volt ragadtatva a szemináriumon. Hűvös, klassz trükk - elkerüli a VBA-t, elkerül egy csomó képletet az Index használatával,és ehhez hasonló dolgok - nagyszerű út.
De, hé, ma hadd küldjek el az M Is For (DATA) MONKEY-val. Ken Puls és Miguel Escobar írták a legnagyobb könyvet a Power Query-ről. Imádom azt a könyvet; 2 óra múlva profi leszel azzal a könyvvel.
Rendben, tehát befejezés ma - Quentinnek azonos felmérést kell készítenie 1000 különböző ügyfél számára. Minden vásárlónak van 6, 7 vagy 8 kérdése. Most megtehetjük ezt VBA-val vagy makróval, de mivel itt egy Power Query fut, most végezzünk egy Power Query-t. Egy további üres kérdést tettem fel a kérdésekhez; Hozzáadtam egy sorozatszámot, hogy megbizonyosodjak arról, hogy az üres ott marad; tegye az ügyfeleket asztalra; tegye táblázatba a kérdéseket; nagyon fontos, hogy olyan kérdéseket nevezz meg, amelyekre emlékszel - én "Kérdéseknek" neveztem. Adja hozzá a kérdéseket a Power Query szolgáltatáshoz, csak kapcsolatként; majd az ügyfelek Power Query-hez való hozzáadásakor hozzon létre egy új egyéni oszlopot, ahol a képlet: # "az első lekérdezés neve", majd bontsa ki ezt az oszlopot a Power Query szerkesztőben; Bezárás &Töltse vissza a táblázathoz, és kész. Elképesztő trükk - imádom a Power Query-t - a legnagyobb dolog, ami 20 év alatt megtörténhet az Excelszel.
Szeretnék köszönetet mondani Quentinnek, hogy megmutatta a szemináriumomat. Néhányszor járt már a szemináriumomon - nagyszerű srác. Szeretném megköszönni, hogy megálltál. Legközelebb találkozunk egy újabb netcast-lal.
Töltse le az Excel fájlt
Az Excel fájl letöltése: loan-survey-explosion.xlsx
A Power Query továbbra is meghökkent. Tekintse meg az M Adatmajom című könyvet, hogy többet tudjon meg a Power Queryről.
Excel gondolata a napnak
Az Excel Master barátaimtól kértem tanácsokat az Excel-mel kapcsolatban. Mai gondolat, hogy elgondolkodjak:
"Az AGGREGATE alkalmazással bármit megtehet, kivéve, ha megérti."
Liam Bastick
