Dnešní otázka od Quentina, který byl na mém semináři v Atlanta Power Excel. Quentin musí v aplikaci Excel vygenerovat stejných 7 dotazníků pro každého z více než 1000 zákazníků.
Jak vidíte na tomto obrázku, zákazníci jsou v A. Otázky, které se mají opakovat, jsou ve sloupci D.
Můžete to vyřešit pomocí VBA nebo vzorců, ale toto je týden Power Query, takže v Power Query použiji skvělý trik.
Pokud chcete mezi jednotlivými průzkumy prázdný řádek, přidejte pořadové číslo a za poslední otázku přidejte číslo 7.
Stiskněte Ctrl + T z obou datových sad. Pojmenujte druhou sadu dat názvem, který si pamatujete, například Dotazy nebo Průzkum.
Z druhé datové sady použijte Data z tabulky.
Otevře se editor Power Query. Na kartě Domů vyberte rozbalovací nabídku Zavřít a načíst a zvolte Zavřít a načíst do…. V dalším dialogovém okně zvolte Pouze vytvořit připojení.
Nyní jste zpět v aplikaci Excel. Vyberte libovolnou buňku v tabulce zákazníků ve sloupci A. Data, z tabulky. Jakmile se otevře editor dotazů, klikněte na pásu karet na kartu Přidat sloupec a poté vyberte Vlastní sloupec. Vzorec je =#"Questions"(včetně # a uvozovek).
V editoru se objeví nový sloupec s hodnotou Table opakovanou v každém řádku. Klikněte na ikonu Rozbalit v záhlaví sloupce.
Vyberte obě pole v tabulce. Na kartě Domů zvolte Zavřít a načíst.
Objeví se nový list se 7 otázkami opakovanými pro každého z více než 1000 zákazníků.
Sledovat video
Přepis videa
Naučte se Excel z podcastu Epizoda 2205: Exploze průzkumu půjčky.
Hej, vítej zpět na netcastu, jsem Bill Jelen. Právě včera v epizodě 2204 to byla Kaylee z Nashvillu, kdo musel provést explozi VLOOKUP - pro každou položku zde ve sloupci D jsme měli ve sloupci G odpovídající hromadu položek a potřebovali jsme je explodovat. Pokud by tedy Palace C měl 8 položek, dostali bychom 8 řádků.
Dnes máme Quentina. Quentin teď byl na mém semináři v Atlantě, ale ve skutečnosti pochází z Floridy a Quentin má téměř 1000 zákazníků - tedy více než 1000 zákazníků - ve sloupci A a pro každého zákazníka musí vytvořit tento průzkum - - tento průzkum 1, 2, 3, 4, 5, 6 otázek. A to, co zde udělám, je, že přidám číslo sekvence jen s čísly 1 až 7, takže tak můžu mezi nimi vytvořit pěkný prázdný řádek. Obě tyto datové sady udělám do tabulky; takže se snažíme, aby těchto 7 řádků explodovalo pro každého z těchto 1000 zákazníků. To je cíl.
Nyní to mohu udělat s VPA; Umím to pomocí vzorců; ale jedná se o jakýsi „Týden Power Query“, jsme na běhu tohoto našeho třetího příkladu Power Query v řadě, takže použiji Power Query. Z tohoto levého udělám stůl. Budu velmi opatrný, abych to pojmenoval, ne Tabulka 1. Pojmenuji to. Toto jméno budeme muset znovu použít později, takže tomu budu říkat Otázky - takhle. A pak to bude Tabulka 2, ale přejmenuji to na Zákazníci - není to tak důležité, abych přejmenoval tuto, protože je to druhá, která musí mít jméno. Takže si vybereme toto; Data; a řekneme Z tabulky / Rozsahu. Získávání a transformace dat - toto se nazývá Power Query. Je integrován do aplikace Excel 2016. Pokud máte 2010 nebo 2013, v systému Windowsne Mac, ne iOS, ne Android, můžete si zdarma stáhnout Power Query z Microsoftu.
Budeme tedy získávat data z tabulky / rozsahu; tady je náš stůl - nebudeme s tím nic dělat, jen zavřít a načíst; Zavřít a načíst do; pouze vytvořit připojení; v pořádku, a podívejte se, název tohoto dotazu je Otázky. Používá stejný název jako zde. A pak se vrátíme k tomuto a, Datu; Z tabulky / rozsahu; takže existuje seznam našich 1000 nebo více zákazníků.
Ahoj, tady je výkřik na Miguela Escobara, mého přítele, který je spoluautorem M Is For (DATA) MONKEY). Vložím na to odkaz ve videu - skvělá kniha o Power Query - mi s tím pomohla. Vložíme zcela nový vlastní sloupec a vzorec vlastního sloupce je právě tady: = # "název dotazu". Bez Miguela bych to nikdy nezjistil, takže za to Miguelovi děkuji.
A když kliknu na OK, jo, nevypadá to, že by to fungovalo - prostě dostaneme stůl, stůl, stůl, ale to je přesně to, co jsme měli včera s Kaylee a lístkem. A vše, co musím udělat, je rozšířit to a já vlastně řeknu, že sekvenci pravděpodobně nepotřebuji … no, pojďme to uvést pro každý případ. Můžeme to vyjmout, až to uvidíme. Právě teď máme 1000 řádků a teď máme 7000 řádků - nádhera. Teď vidím, že se to objevuje v Sekvenci, takže to nepotřebuji. Kliknu pravým tlačítkem a odstraním pouze ten jeden sloupec. A pak mohu domů; Zavřít a načíst; a BAM! - nyní bychom měli mít více než 7000 řádků se 6 otázkami a prázdné místo pro každého zákazníka. Quentin byl na semináři nadšený. Skvělý trik - vyhýbá se VBA, vyhýbá se spoustě vzorců pomocí indexu,a podobné věci - skvělý způsob, jak jít.
Ale hej, dnes vám dovolím poslat vás s M Is For (DATA) MONKEY. Ken Puls a Miguel Escobar napsali největší knihu o Power Query. Miluji tu knihu; za 2 hodiny se s touto knihou stanete profesionálem.
Dobře, tak to dnes zabalte - Quentin musí vygenerovat identický průzkum pro 1000 různých zákazníků. Každý zákazník má 6 nebo 7 nebo 8 otázek. Nyní bychom to mohli udělat pomocí VBA nebo makra, ale protože jsme zde spuštěni Power Query, udělejme Power Query. K Dotazům jsem přidal další prázdnou otázku; Přidal jsem pořadové číslo, abych se ujistil, že tam zůstane mezera; udělat ze zákazníků stůl; sestavit otázky do tabulky; je opravdu důležité, abyste pojmenovali Otázky něčím, na co si pamatujete - mému jsem říkal „Dotazy“. Přidejte otázky do Power Query, Pouze jako připojení; a poté, když přidáváte zákazníky do Power Query, vytvořte nový vlastní sloupec, kde je vzorec: # "název prvního dotazu" a poté tento sloupec rozbalte v editoru Power Query; Zavřít &Načtěte zpět do tabulky a jste hotovi. Úžasný trik - Miluji Power Query - největší věc, která se stane Excelu za 20 let.
Chci poděkovat Quentinovi za předvedení na mém semináři. Už byl několikrát na mém semináři - skvělý chlap. Chci vám poděkovat, že jste se zastavili. Uvidíme se příště na dalším netcastu z.
Stáhněte si soubor Excel
Stažení souboru aplikace Excel: loan-survey-explosion.xlsx
Power Query mě i nadále udivuje. Podívejte se do knihy M je pro Data Monkey a dozvíte se více Power Query.
Excel myšlenka dne
Požádal jsem své přátele Excel Master o radu ohledně Excelu. Dnešní myšlenka k zamyšlení:
„S AGGREGATE můžete dělat cokoli, kromě toho, že tomu rozumíte.“
Liam Bastick
