Tänane küsimus Quentinilt, kes oli minu Atlanta Power Exceli seminaril. Quentin peab Excelis iga 1000+ kliendi jaoks genereerima samad 7 uuringuküsimust.
Nagu näete sellelt jooniselt, on kliendid A-s. Kordatavad küsimused on veerus D.
Selle võiksite lahendada VBA või valemitega, kuid see on Power Query nädal, nii et ma kasutan Power Query'is lahedat trikki.
Kui soovite iga uuringu vahel tühja rida, lisage järjekorranumber ja viimase küsimuse järele number 7.
Mõlemas andmekogumis vajutage klahvikombinatsiooni Ctrl + T. Nimetage teine andmekogum nimega, mida mäletate, näiteks Küsimused või Uuring.
Alates teisest andmekogumist kasutage Andmed, Tabelist.
Avaneb Power Query redaktor. Valige vahekaardil Avaleht rippmenüü Sule ja laadige ning valige Sule ja laadige …. Järgmises dialoogiboksis valige Ainult ühenduse loomine.
Olete nüüd Excelis tagasi. Valige veerus A. klienditabeli mis tahes lahter. Andmed, tabelist. Kui päringiredaktor avaneb, klõpsake lindil vahekaarti Lisa veerg ja valige seejärel kohandatud veerg. Valem on =#"Questions"(sh # ja jutumärgid).
Redaktorisse ilmub uus veerg, mille väärtust Tabel korratakse igas reas. Klõpsake veeru päises ikooni Laienda.
Valige tabelis mõlemad väljad. Valige vahekaardil Avaleht Sule ja laadige.
Ilmub uus tööleht, kus iga 7+ küsimuse korral korratakse üle 1000 kliendi.
Vaata videot
Video ärakiri
Õppige Exceli podcastide episoodist 2205: laenuküsitluse plahvatus.
Tere, tere tulemast tagasi netiülekandesse, ma olen Bill Jelen. Nüüd, just eile jaos 2204, pidi Kaylee Nashville'ist tegema VLOOKUP-plahvatuse - iga veerus D oleva elemendi jaoks oli meil veerus G vastav hunnik esemeid ja pidime need plahvatama. Nii et kui Palace C-l oleks 8 eset, saaksime 8 rida.
Nüüd, täna, on meil Quentin. Nüüd oli Quentin minu seminaril Atlantas, kuid ta oli tegelikult Floridast ja Quentinil on siin veerus A peaaegu 1000 klienti - noh, üle 1000 kliendi - ja iga kliendi jaoks peab ta selle uuringu koostama - - see küsitlus 1, 2, 3, 4, 5, 6 küsimusega. Ja mida ma siin tegema hakkan, on see, et lisan järjekorranumbri lihtsalt numbritega 1 kuni 7, nii et nii saan luua vahele vahva tühja rea. Ma teen mõlemad need andmekogumid tabeliks; Niisiis, me üritame neid 7 rida plahvatada iga 1000 kliendi jaoks. See on eesmärk.
Nüüd saan seda teha VPA-ga; Ma saan seda teha valemitega; aga siin on selline "Power Query Week", meil on käsil see, see on meie kolmas Power Query näide järjest, nii et ma hakkan kasutama Power Query-d. Ma teen selle vasakpoolse lauale. Ma hakkan olema väga ettevaatlik, nimetades seda mitte tabeliks 1. Annan sellele nime. Peame seda nime hiljem uuesti kasutama, nii et ma nimetan seda küsimusteks - niimoodi. Ja siis saab sellest tabel 2, kuid ma nimetan selle ümber, et olla Kliendid - mitte nii oluline, et nimetaksin selle ümber, sest see peab olema teine, millel peab olema nimi. Nii et valime selle; Andmed; ja me ütleme tabelist / vahemikust. Andmete hankimine ja teisendamine - see on tuntud kui Power Query. See on sisse ehitatud Exceli 2016. Kui teil on 2010 või 2013, siis Windowsismitte Mac, mitte iOS, mitte Android, saate Power Query Microsofti tasuta alla laadida.
Niisiis saame andmeid tabelist / vahemikust; siin on meie tabel - me ei hakka sellega midagi tegema, lihtsalt sulgege ja laadige; Sule ja laadi; ainult ühenduse loomine; olgu, ja näe, selle päringu nimi on Küsimused. See kasutab sama nime nagu siin. Ja siis tuleme tagasi selle ühe juurde, ja, Data; Tabelist / vahemikust; seega on olemas meie 1000 või enama kliendi nimekiri.
Hei, siin on hüüd Miguel Escobarile, mu sõbrale, kes on M Is For (DATA) MONKEY kaasautor. Lisan selle video lingi - suurepärane raamat Power Query kohta - aitas mind selles. Paneme uhiuue kohandatud veeru ja kohandatud veeru valem on siin: = # "päringu nimi". Ma poleks seda kunagi ilma Miguelita välja mõelnud, nii et aitäh selle eest Miguelile.
Ja kui ma klõpsan nuppu OK, siis jah, tundub, et see ei töötanud - saame lihtsalt tabeli, tabeli, tabeli, aga täpselt nii oli meil eile Kaylee ja piletimüügiga. Ja mul on vaja ainult seda laiendada ja ma ütlen tegelikult, et mul pole ilmselt järjestust vaja … noh, paneme selle igaks juhuks sisse. Me võime selle pärast nägemist välja võtta. Praegu on meil 1000 rida ja nüüd on meil 7000 rida - ilus. Ma näen nüüd, et see ilmub järjestuses, nii et mul pole seda vaja. Paremklõpsan ja eemaldan just selle ühe veeru. Ja siis saan koju; Sule & Laadi; ja BAM! - meil peaks nüüd olema rohkem kui 7000 rida koos 6 küsimusega ja tühi koht iga kliendi jaoks. Quentin oli sellest seminarist vaimustatud. Lahe ja lahe trikk - väldib VBA-d, väldib Indeksi abil tervet hulka valemeid,ja sellised asjad - suurepärane võimalus minna.
Aga, hei, lubage mul täna teid saata koos M Is For (DATA) MONKEY'ga. Suurima raamatu Power Query kohta kirjutasid Ken Puls ja Miguel Escobar. Ma armastan seda raamatut; 2 tunni pärast saate sellest raamatust proff.
Hästi, nii, täna - Quentin peab genereerima identse uuringu 1000 erineva kliendi jaoks. Igale kliendile on 6 või 7 või 8 küsimust. Nüüd saaksime seda teha VBA või makro abil, kuid kuna meil on siin käimas Power Query, teeme Power Query. Lisasin küsimustele täiendava tühja küsimuse; Lisasin järjenumbri, veendumaks, et tühi jääb sinna; teha klientidest tabel; tee küsimused tabelisse; on väga oluline, et nimetaksite küsimused midagi, mida mäletate - ma nimetasin oma küsimusi küsimusteks. Lisage küsimused jaotisse Power Query, ainult ühendusena; ja seejärel, kui lisate kliente Power Queryle, looge uus kohandatud veerg, kus valem on: # "esimese päringu nimi" ja seejärel laiendage seda veergu Power Query redaktoris; Sulge &Laadige tagasi arvutustabelisse ja olete valmis. Hämmastav trikk - mulle meeldib Power Query - suurim asi, mis Exceliga 20 aasta jooksul juhtub.
Ma tahan tänada Quentinit minu seminaril esinemise eest. Ta on paar korda varem minu seminaril käinud - vahva mees. Tahan teid tänada, et peatusite. Järgmisel korral näeme järgmise võrguülekande jaoks.
Laadige alla Exceli fail
Exceli faili allalaadimiseks: loan-survey-explosion.xlsx
Power Query hämmastab mind jätkuvalt. Power Query kohta leiate lisateavet raamatust M on mõeldud Data Monkey'ile.
Exceli päeva mõte
Olen küsinud oma Exceli meistri sõpradelt Exceli kohta nõu. Tänane mõte mõelda:
"AGGREGATE'iga saate teha kõike muud, kui sellest aru saada."
Liam Bastick
