Dnešná otázka od Quentina, ktorý bol na mojom seminári v Atlante Power Excel. Quentin musí vygenerovať rovnakých 7 otázok z prieskumu pre každého z viac ako 1 000 zákazníkov v programe Excel.
Ako vidíte na tomto obrázku, zákazníci sa nachádzajú v A. Otázky, ktoré sa majú opakovať, sú v stĺpci D.
Môžete to vyriešiť pomocou VBA alebo vzorcov, ale toto je týždeň Power Query, takže v Power Query použijem skvelý trik.
Ak chcete medzi jednotlivými prieskumami prázdny riadok, pridajte poradové číslo a za poslednú otázku pridajte číslo 7.
Stlačte Ctrl + T na oboch množinách údajov. Pomenujte druhú množinu údajov menom, ktoré si pamätáte, napríklad Dotazy alebo Prieskum.
Z druhého súboru údajov použite údaje, z tabuľky.
Otvorí sa editor Power Query. Na karte Domovská stránka vyberte rozbaľovaciu ponuku Zatvoriť a načítať a vyberte možnosť Zavrieť a načítať do …. V ďalšom dialógovom okne vyberte možnosť Iba vytvoriť pripojenie.
Teraz ste späť v programe Excel. Vyberte ľubovoľnú bunku v tabuľke zákazníka v stĺpci A. Údaje, z tabuľky. Po otvorení editora dotazov kliknite na karte Pridať stĺpec na páse s nástrojmi a potom vyberte položku Vlastný stĺpec. Vzorec je =#"Questions"(vrátane # a úvodzoviek).
V editore sa objaví nový stĺpec s hodnotou Table, ktorá sa opakuje v každom riadku. Kliknite na ikonu Rozbaliť v hlavičke stĺpca.
Vyberte obe polia v tabuľke. Na karte Domov zvoľte Zatvoriť a načítať.
Zobrazí sa nový pracovný list so 7 otázkami opakovanými pre každého z viac ako 1 000 zákazníkov.
Pozeraj video
Prepis videa
Naučte sa Excel z Podcastu Epizóda 2205: Explozia prieskumu pôžičky.
Ahoj, vitaj naspäť na netcast, som Bill Jelen. Len včera v epizóde 2204 to bola Kaylee z Nashvillu, ktorá musela urobiť výbuch VÝHĽADU - pre každú položku tu v stĺpci D sme mali v stĺpci G zodpovedajúcu hromadu položiek a potrebovali sme ich explodovať. Keby teda Palace C mal 8 položiek, dostali by sme 8 riadkov.
Teraz, dnes, máme Quentin. Teraz bol Quentin na mojom seminári v Atlante, ale v skutočnosti je z Floridy a Quentin má takmer 1 000 zákazníkov tu - teda viac ako 1 000 zákazníkov - v stĺpci A a pre každého zákazníka musí vytvoriť tento prieskum - - tento prieskum s 1, 2, 3, 4, 5, 6 otázkami. A čo tu urobím je, že pridám sekvenčné číslo len s číslami 1 až 7, aby som medzi nimi vytvoril pekný prázdny riadok. Z týchto dvoch súborov údajov urobím tabuľku; takže sa snažíme, aby týchto 7 riadkov explodovalo pre každého z týchto 1000 zákazníkov. To je cieľ.
Teraz to môžem urobiť pomocou VPA; Môžem to urobiť pomocou vzorcov; ale je to tu akýsi „týždeň Power Query“, sme na ceste za týmto našim tretím príkladom v Power Query za sebou, takže použijem Power Query. Z tohto ľavého urobím stôl. Budem veľmi opatrný, aby som to pomenoval, nie Tabuľka 1. Pomenujem to. Toto meno budeme musieť neskôr znova použiť, takže ho budem nazývať Otázky - tak. A potom to bude Tabuľka 2, ale chystám sa ju premenovať na Zákazníci - nie je to také dôležité, aby som premenoval túto, pretože je to druhá, ktorá musí mať meno. Takže si vyberieme toto; Údaje; a povieme Z tabuľky / Rozsahu. Získajte a transformujte údaje - toto sa nazýva Power Query. Je zabudovaný do Excelu 2016. Ak máte 2010 alebo 2013, v systéme Windowsnie pre Mac, ani pre iOS, ani pre Android, môžete si zadarmo stiahnuť Power Query z Microsoftu.
Takže dostaneme údaje z tabuľky / rozsahu; tu je náš stôl - nebudeme s tým nič robiť, iba zavrieť a načítať; Zatvoriť a načítať do; iba Vytvoriť pripojenie; v poriadku, a uvidíte, názov tohto dotazu je Questions. Používa rovnaký názov ako tu. A potom sa vrátime k tejto, a, Date; Z tabuľky / rozsahu; takže existuje zoznam našich 1 000 alebo viac zákazníkov.
Ahoj, teraz je to výkrik na Miguela Escobara, môjho priateľa, ktorý je spoluautorom knihy M Is For (DATA) MONKEY). Pomôžem ti vo videu - skvelá kniha o Power Query - mi s tým pomohla. Chystáme sa vložiť úplne nový vlastný stĺpec a vzorec vlastného stĺpca je tu: = # "názov dotazu". Bez Miguela by som to nikdy neprišiel, takže za to Miguelovi ďakujem.
A keď kliknem na OK, jo, nevyzerá to, že to fungovalo - iba dostaneme stôl, stôl, stôl, ale to je presne to, čo sme včera mali s Kaylee a lístkom. A všetko, čo musím urobiť, je rozšíriť to, a vlastne poviem, že sekvenciu asi nepotrebujem … no, poďme to uviesť pre každý prípad. Keď to uvidíme, môžeme ho vytiahnuť. Momentálne máme 1000 riadkov a teraz máme 7000 riadkov - nádherné. Teraz vidím, že sa to objavuje v Sekvencii, takže to nepotrebujem. Kliknem pravým tlačidlom myši a odstránim iba tento jeden stĺpec. A potom môžem Domov; Zatvoriť a načítať; a BAM! - teraz by sme mali mať viac ako 7000 riadkov so 6 otázkami a prázdnym priestorom pre každého zákazníka. Quentin bol tým seminárom nadšený. Super trik - vyhýba sa VBA, vyhýba sa množine vzorcov pomocou indexu,a podobné veci - skvelý spôsob, ako ísť.
Ale hej, dnes mi dovoľte poslať vás s M Is For (DATA) MONKEY. Ken Puls a Miguel Escobar napísali najväčšiu knihu o Power Query. Milujem tú knihu; za 2 hodiny sa z tejto knihy staneš profík.
Dobre, dnes teda všetko - Quentin musí vygenerovať identický prieskum pre 1 000 rôznych zákazníkov. Pre každého zákazníka je 6 alebo 7 alebo 8 otázok. Teraz by sme to mohli urobiť pomocou VBA alebo makra, ale keďže práve pracujeme na Power Query, urobme Power Query. K Otázkam som pridal ďalšiu prázdnu otázku; Pridal som poradové číslo, aby som sa uistil, že tam ostane prázdny; urobiť zo zákazníkov stôl; spracujte otázky do tabuľky; je skutočne dôležité, aby ste pomenovali Otázky niečo, na čo si spomeniete - môj som nazval „Otázky“. Pridajte otázky do Power Query, Len ako pripojenie; a potom, keď pridávate zákazníkov do Power Query, vytvorte nový vlastný stĺpec, kde je vzorec: # "názov prvého dotazu" a potom tento stĺpec rozbaľte v editore Power Query; Zavrieť &Načítajte späť do tabuľky a máte hotovo. Úžasný trik - Milujem Power Query - najväčšia vec, ktorá sa stane Excelu za posledných 20 rokov.
Chcem poďakovať Quentinovi za predvedenie na mojom seminári. Bol už niekoľkokrát na mojom seminári - skvelý chlap. Chcem sa vám poďakovať, že ste sa zastavili. Uvidíme sa nabudúce pri ďalšom netcaste z.
Stiahnite si súbor Excel
Stiahnutie súboru programu Excel: loan-survey-explosion.xlsx
Power Query ma stále udivuje. Pozrite si knihu M je pre Data Monkey a dozviete sa viac Power Query.
Excel myslel dňa
Požiadal som svojich priateľov programu Excel Master o radu o programe Excel. Dnešná myšlienka na zamyslenie:
„S AGGREGÁTOM môžeš robiť čokoľvek, ibaže tomu rozumieš.“
Liam Bastick
