Današnje vprašanje Quentina, ki je bil na mojem seminarju v Atlanti Power Excel. Quentin mora ustvariti enakih 7 vprašanj za anketo za vsako od 1000+ strank v Excelu.
Kot lahko vidite na tej sliki, so stranke v A. Vprašanja, ki jih je treba ponoviti, so v stolpcu D.
To bi lahko rešili z VBA ali formulami, toda to je teden Power Query, zato bom uporabil kul trik v Power Queryju.
Če želite prazno vrstico med vsako anketo, dodajte zaporedno številko in za zadnjim vprašanjem dodajte številko 7.
Pritisnite Ctrl + T v obeh naborih podatkov. Drugi niz podatkov poimenujte z imenom, ki se ga lahko spomnite, na primer Vprašanja ali Anketa.
V drugem naboru podatkov uporabite Data, From Table.
Odpre se urejevalnik Power Query. Na zavihku Domov izberite spustni meni Zapri in naloži in izberite Zapri in naloži v…. V naslednjem pogovornem oknu izberite Samo ustvari povezavo.
Zdaj ste spet v Excelu. Izberite poljubno celico v tabeli strank v stolpcu A. Podatki iz tabele. Ko se odpre urejevalnik poizvedb, na traku kliknite zavihek Dodaj stolpec in izberite Stolpec po meri. Formula je =#"Questions"(vključno z # in narekovaji).
V urejevalniku se prikaže nov stolpec z vrednostjo Tabela, ki se ponovi v vsaki vrstici. V glavi stolpca kliknite ikono Razširi.
Izberite obe polji v tabeli. Na zavihku Domov izberite Zapri in naloži.
Pojavil se bo nov delovni list s 7 vprašanji, ponovljenimi za vsako od 1000+ strank.
Oglejte si video
Video zapis
Naučite se Excel iz podcasta Epizoda 2205: Eksplozija ankete o posojilih.
Hej, dobrodošli nazaj na netcastu, jaz sem Bill Jelen. Zdaj, ravno včeraj v epizodi 2204, je morala Kaylee iz Nashvillea narediti eksplozijo VLOOKUP - za vsak predmet tukaj v stolpcu D smo imeli v stolpcu G ustrezen kup predmetov, ki smo jih morali eksplodirati. Torej, če bi palača C imela 8 predmetov, bi dobili 8 vrstic.
Danes imamo Quentina. Zdaj je bil Quentin na mojem seminarju v Atlanti, vendar je dejansko s Floride in Quentin ima tu skoraj 1000 strank - no, več kot 1000 strank - v stolpcu A in za vsako stranko mora pripraviti to anketo - ta raziskava z 1, 2, 3, 4, 5, 6 vprašanji. In tukaj bom storil, da bom dodal zaporedno številko samo s številkami od 1 do 7, tako da bom lahko vmes ustvaril lepo prazno vrstico. Oba ta nabora podatkov bom naredil v tabelo; torej poskušamo razbiti teh 7 vrstic za vsako od teh 1000 strank. To je cilj.
Zdaj lahko to storim z VPA; To lahko storim s formulami; vendar je tu nekako "teden poizvedb", že smo v teku, to je naš tretji primer Power Query zapored, zato bom uporabil Power Query. Levo bom postavil v mizo. Zelo previdno bom poimenoval to in ne tabela 1. Ime ji bom dal. Kasneje bomo morali to ime ponovno uporabiti, zato ga bom poklical Vprašanja - tako. In potem bo to tabela 2, vendar jo bom preimenoval v Stranke - ni tako pomembno, da bi jo preimenoval, ker mora biti druga ime. Torej, to bomo izbrali; Podatki; in rekli bomo From Table / Range. Pridobivanje in preoblikovanje podatkov - to je znano kot Power Query. Vgrajen je v Excel 2016. Če imate 2010 ali 2013 v sistemu Windows,ne Mac, ne iOS, ne Android, Power Query lahko brezplačno prenesete iz Microsofta.
Torej, podatke bomo dobili iz tabele / obsega; tukaj je naša miza - ne bomo ji ničesar naredili, samo Zapri in naloži; Zapri & Naloži v; samo Ustvari povezavo; v redu in glej, ime te poizvedbe je Vprašanja. Uporablja isto ime kot tukaj. In potem se vrnemo k tej in Podatki; Iz tabele / obsega; Torej, tu je seznam naših 1000 ali več strank.
Hej, zdaj je krik Miguelu Escobarju, mojemu prijatelju, ki je soavtor opice M Is For (DATA) MONKEY). Povezavo do tega bom postavil v video - odlična knjiga o Power Query - mi je pomagala pri tem. Vstavili bomo povsem nov stolpec po meri, formula stolpca po meri pa je tukaj: = # "ime poizvedbe". Brez Miguela tega ne bi nikoli ugotovil, zato se zahvaljujem Miguelu za to.
In ko kliknem OK, ja, ni videti, da je delovalo - samo dobimo mizo, mizo, mizo, ampak točno to smo imeli včeraj s Kaylee in vstopnicami. In vse, kar moram storiti, je, da to razširim in pravzaprav bom rekel, da zaporedja verjetno ne rabim … no, dajmo ga za vsak slučaj. Ko ga vidimo, ga lahko vzamemo ven. Trenutno imamo 1000 vrstic, zdaj pa imamo 7000 vrstic-- čudovito. Zdaj vidim, da se pojavlja v zaporedju, zato tega ne rabim. Z desno miškino tipko odstranim samo ta stolpec. In potem lahko domov; Zapri & Naloži; in BAM! - zdaj bi morali imeti več kot 7000 vrstic s 6 vprašanji in praznim prostorom za vsako stranko. Quentin je bil nad tem na seminarju navdušen. Kul, kul trik - izogiba se VBA, izogiba se celemu številu formul z uporabo indeksa,in podobne stvari - odlična pot.
Ampak, hej, danes naj vas pošljem z opico M Is For (DATA) MONKEY. Ken Puls in Miguel Escobar sta napisala največjo knjigo o Power Queryju. Obožujem to knjigo; čez 2 uri boste s to knjigo postali profesionalec.
Torej, zaključek danes - Quentin mora pripraviti enako anketo za 1000 različnih strank. Za vsako stranko je na voljo 6 ali 7 ali 8 vprašanj. Zdaj bi to lahko storili z VBA ali makrom, toda ker smo tukaj v Power Query, naredimo Power Query. Vprašanjem sem dodal dodatno prazno vprašanje; Dodala sem zaporedno številko, da zagotovim, da tam ostane prazno; naredite stranke v mizo; postavite vprašanja v tabelo; res je pomembno, da Vprašanja poimenujete nekaj, česar se lahko spomnite - svoje sem imenoval "Vprašanja". Vprašanja dodajte v Power Query, samo kot povezavo; in nato, ko dodajate stranke v Power Query, ustvarite nov stolpec po meri, kjer je formula: # "ime prve poizvedbe" in nato razširite ta stolpec v urejevalniku Power Query; Zapri &Naložite nazaj v preglednico in končali ste. Neverjeten trik - obožujem Power Query - največja stvar, ki se zgodi Excelu v 20 letih.
Zahvaljujem se Quentinu za nastop na mojem seminarju. Že nekajkrat je bil na mojem seminarju-- super fant. Želim se vam zahvaliti, da ste se ustavili. Se vidimo naslednjič za še eno oddajo iz.
Prenesite datoteko Excel
Za prenos datoteke excel: izposoja-anketa-eksplozija.xlsx
Power Query me še naprej preseneča. Če želite izvedeti več o Power Queryju, si oglejte knjigo M is for Data Monkey.
Excel Misel dneva
Za nasvet glede Excela sem prosil prijatelje Excel Excel. Današnja misel o premišljevanju:
"Z AGREGATE lahko storite kar koli, razen da ga razumete."
Liam Bastick
