Šiandienos Quentino, dalyvavusio mano „Atlanta Power Excel“ seminare, klausimas. „Quentin“ turi sugeneruoti tuos pačius 7 apklausos klausimus kiekvienam iš daugiau nei 1000 „Excel“ klientų.
Kaip matote šiame paveikslėlyje, klientai yra A. Klausimai, kuriuos reikia pakartoti, yra D stulpelyje.
Tai galite išspręsti naudodami VBA ar formules, tačiau tai yra „Power Query“ savaitė, todėl „Power Query“ naudosiu šaunų triuką.
Jei norite tuščios eilutės tarp kiekvienos apklausos, pridėkite eilės numerį ir po paskutinio klausimo pridėkite skaičių 7.
Abiejuose duomenų rinkiniuose paspauskite „Ctrl“ + T. Pavadinkite antrąjį duomenų rinkinį pavadinimu, kurį galite prisiminti, pvz., „Klausimai“ arba „Apklausa“.
Iš antrojo duomenų rinkinio naudokite duomenis, lentelę.
Atidaromas „Power Query“ redaktorius. Skirtuke Pagrindinis pasirinkite išskleidžiamąjį meniu Uždaryti ir įkelti ir pasirinkite Uždaryti ir įkelti …. Kitame dialogo lange pasirinkite Tik sukurti ryšį.
Dabar grįžote į „Excel“. Pasirinkite bet kurią langelį kliento lentelėje A stulpelyje. Duomenys, iš lentelės. Kai atsidarys Užklausų rengyklė, juostoje spustelėkite skirtuką Pridėti stulpelį ir pasirinkite Pasirinktinis stulpelis. Formulė yra =#"Questions"(įskaitant # ir kabutes).
Redaktoriuje pasirodys naujas stulpelis, kurio vertės lentelė kartojama kiekvienoje eilutėje. Stulpelio antraštėje spustelėkite piktogramą Išskleisti.
Pasirinkite abu lentelės laukus. Skirtuke Pagrindinis pasirinkite Uždaryti ir įkelti.
Bus parodytas naujas darbalapis su 7 klausimais, pakartotais kiekvienam iš 1000 ir daugiau klientų.
Žiūrėti video
Vaizdo įrašo nuorašas
Sužinokite „Excel“ iš „Podcast“ serijos 2205: Paskolos tyrimo sprogimas.
Ei, sveiki sugrįžę į netcast'ą, aš Billas Jelenas. Ką tik vakar 2204 epizode būtent Kaylee iš Našvilio turėjo padaryti VLOOKUP sprogimą - kiekvienam daiktui, esančiam D stulpelyje, mes turėjome atitinkamą krūvą daiktų, esančių G stulpelyje, ir juos reikėjo susprogdinti. Taigi, jei rūmuose C būtų 8 daiktai, gautume 8 eiles.
Dabar šiandien turime Quentiną. Dabar Quentinas dalyvavo mano seminare Atlantoje, bet jis iš tikrųjų yra iš Floridos, o Quentinas turi beveik 1000 klientų - gerai, daugiau nei 1000 klientų - A stulpelyje ir kiekvienam klientui jis turi sukurti šią apklausą. - ši 1, 2, 3, 4, 5, 6 klausimų apklausa. Ką aš čia darysiu, aš pridėsiu eilės numerį tik su skaičiais nuo 1 iki 7, kad tokiu būdu galėčiau sukurti gražią tuščią eilutę tarp jų. Aš sudarysiu abu šiuos duomenų rinkinius į lentelę; Taigi, mes stengiamės, kad šios 7 eilutės būtų susprogdintos kiekvienam iš šių 1000 klientų. Tai ir yra tikslas.
Dabar tai galiu padaryti naudodamasis VPA; Aš tai galiu padaryti formulėmis; bet čia kažkokia „Power Query savaitė“, mes jau bandome tai yra trečiasis „Power Query“ pavyzdys iš eilės, todėl aš naudosiuosi „Power Query“. Padarysiu šį kairįjį į stalą. Aš būsiu labai atsargus, kai tai pavadinsiu ne 1 lentele. Aš duosiu jai pavadinimą. Vėliau turėsime pakartotinai naudoti šį vardą, todėl aš jį pavadinsiu klausimais. Tada tai bus 2 lentelė, bet aš pervardysiu tai kaip „Klientai“ - ne taip svarbu, kad pervardysiu šią, nes tai yra antroji, kuri turi turėti pavadinimą. Taigi, mes pasirinksime tai; Duomenys; ir mes sakysime nuo lentelės / diapazono. Gauti ir transformuoti duomenis - tai vadinama „Power Query“. Tai įmontuota „Excel 2016.“. Jei turite 2010 ar 2013 m., Sistemoje „Windows“,Ne „Mac“, ne „iOS“, ne „Android“, galite nemokamai atsisiųsti „Power Query“ iš „Microsoft“.
Taigi, mes gausime duomenis iš lentelės / diapazono; čia yra mūsų stalas - mes nieko nedarysime, tiesiog uždarykite ir įkraukite; Uždaryti ir įkelti; tik sukurti ryšį; gerai, ir žiūrėk, tos užklausos pavadinimas yra Klausimai. Jis naudoja tą patį pavadinimą kaip ir čia. Tada grįšime prie šio ir „Data“; From Table / Range; Taigi, yra mūsų 1000 ar daugiau klientų sąrašas.
Ei, štai, šūkis Migueliui Escobarui, mano draugui, kuris yra „M Is For“ (DATA) MONKEY bendraautorius. Aš padėjau nuorodą į tai vaizdo įraše - puiki knyga apie „Power Query“ - man tai padėjo. Mes ketiname įdėti visiškai naują pasirinktinį stulpelį, o pasirinkto stulpelio formulė yra tokia: = # "užklausos pavadinimas". Niekada nebūčiau to supratęs be Miguelio, todėl ačiū Migueliui už tai.
Spustelėjus Gerai, taip, neatrodo, kad tai pavyko - mes tiesiog gauname lentelę, lentelę, lentelę, bet būtent tai ir turėjome vakar su Kaylee ir bilietais. Viskas, ką aš turiu padaryti, tai išplėsti ir iš tikrųjų pasakysiu, kad sekos man tikriausiai nereikia … na, įdėkime tai kiekvienam atvejui. Mes galime jį išsiimti, kai pamatysime. Šiuo metu turime 1000 eilučių, o dabar turime 7000 eilučių - gražu. Dabar matau, kad jis pasirodo „Sekoje“, todėl man to nereikia. Dešiniuoju pelės mygtuku spustelėkite ir pašalinsiu tik tą vieną stulpelį. Ir tada aš galiu Namai; Uždaryti & įkelti; ir BAM! - dabar turėtume turėti daugiau nei 7000 eilučių su 6 klausimais ir tuščią vietą kiekvienam klientui. Seminare Quentinas tuo džiaugėsi. Kietas, kietas triukas - išvengiama VBA, išvengiama daugybės formulių naudojant „Index“,ir panašūs dalykai - puikus būdas eiti.
Bet, ei, šiandien, leisk man tave išsiųsti su M Is For (DATA) MONKEY. Kenas Pulsas ir Migelis Escobaras parašė didžiausią knygą apie „Power Query“. Aš myliu tą knygą; per 2 valandas tapsite tos knygos profesionalu.
Gerai, taigi, šiandien užbaigkite - Quentinui reikia sukurti identišką apklausą 1000 skirtingų klientų. Kiekvienam klientui yra 6 arba 7 arba 8 klausimai. Dabar tai galėtume padaryti naudodami VBA ar „makrokomandą“, bet kadangi čia vykdome „Power Query“, atlikime „Power Query“. Į klausimus pridėjau papildomą tuščią klausimą; Aš pridėjau sekos numerį, norėdamas įsitikinti, kad tuščia vieta lieka; sudaryti klientus į stalą; sudarykite klausimus lentelėje; labai svarbu, kad klausimus pavadintumėte kažkuo, ką galite prisiminti - aš pavadinau savo klausimus „klausimais“. Pridėkite klausimus prie „Power Query“, tik kaip ryšys; ir tada, kai pridedate klientus prie „Power Query“, sukurkite naują pasirinktinį stulpelį, kuriame formulė yra: # „pirmosios užklausos pavadinimas“, tada išplėskite tą stulpelį „Power Query“ redaktoriuje; Uždaryti &Įkelkite atgal į skaičiuoklę ir viskas. Nuostabus triukas - man patinka „Power Query“ - didžiausias dalykas, nutikęs „Excel“ per 20 metų.
Noriu padėkoti Quentinui už pasirodymą mano seminare. Jis prieš keletą kartų buvo mano seminare - puikus vaikinas. Noriu padėkoti, kad užsukote. Pamatysime kitą kartą kitai internetinei transliacijai iš.
Atsisiųskite „Excel“ failą
Norėdami atsisiųsti „Excel“ failą: loan-survey-explosion.xlsx
„Power Query“ ir toliau mane stebina. Norėdami sužinoti daugiau apie „Power Query“, skaitykite knygą „M skirta duomenų beždžionei“.
„Excel“ dienos mintis
Aš paprašiau savo „Excel Master“ draugų patarimo apie „Excel“. Šiandienos mintis apmąstyti:
"Su AGGREGATE galite padaryti bet ką, išskyrus tai suprasti."
Liam Bastick
