Današnje pitanje Quentina koji je bio na mom seminaru u Atlanti Power Excel. Quentin mora generirati istih 7 anketnih pitanja za svakog od 1000+ kupaca u programu Excel.
Kao što vidite na ovoj slici, kupci su u A. Pitanja koja treba ponoviti nalaze se u stupcu D.
To biste mogli riješiti VBA-om ili formulama, ali ovo je Power Query tjedan u, pa ću upotrijebiti cool trik u Power Query-u.
Ako želite prazan redak između svake ankete, dodajte redni broj i dodajte broj 7 nakon zadnjeg pitanja.
Pritisnite Ctrl + T iz oba skupa podataka. Nazovite drugi skup podataka imenom kojeg se sjećate, poput Pitanja ili Anketa.
Iz drugog skupa podataka koristite podatke, iz tablice.
Otvara se Power Query editor. Na kartici Početna odaberite padajući izbornik Zatvori i učitaj i odaberite Zatvori i učitaj u …. U sljedećem dijaloškom okviru odaberite Samo stvori vezu.
Sada ste se vratili u Excel. Odaberite bilo koju ćeliju u tablici kupaca u stupcu A. Podaci iz tablice. Nakon što se otvori uređivač upita, kliknite karticu Dodaj stupac na vrpci, a zatim odaberite Prilagođeni stupac. Formula je =#"Questions"(uključujući # i navodnike).
U uređivaču se pojavljuje novi stupac s vrijednošću Tablica koja se ponavlja u svakom retku. Kliknite ikonu Proširi u zaglavlju stupca.
Odaberite oba polja u tablici. Na kartici Početna odaberite Zatvori i učitaj.
Pojavit će se novi radni list sa 7 pitanja koja se ponavljaju za svakog od 1000+ kupaca.
Gledaj video
Prijepis videozapisa
Naučite Excel iz podcasta Epizoda 2205: Eksplozija ankete o zajmu.
Hej, dobrodošao natrag na netcast, ja sam Bill Jelen. E, baš jučer u epizodi 2204, Kaylee iz Nashvillea morala je napraviti eksploziju VLOOKUP-a - za svaki predmet ovdje u stupcu D imali smo odgovarajuću gomilu predmeta u stupcu G i trebali smo ih eksplodirati. Dakle, da palača C ima 8 predmeta, dobili bismo 8 redova.
Sad, danas imamo Quentina. Quentin je bio na mom seminaru u Atlanti, ali zapravo je s Floride, a Quentin ovdje ima gotovo 1000 kupaca - pa, više od 1000 kupaca - u stupcu A, a za svakog kupca on mora stvoriti ovu anketu - - ovo istraživanje s 1, 2, 3, 4, 5, 6 pitanja. I ono što ću ovdje učiniti je da dodam broj sekvence samo s brojevima od 1 do 7, tako da između toga mogu stvoriti lijep prazan redak. Sastavit ću oba ova skupa podataka u tablicu; Dakle, trudimo se da ovih 7 redaka eksplodira za svakog od ovih 1000 kupaca. To je cilj.
Sada to mogu učiniti s VPA-om; Mogu to učiniti s formulama; ali ovdje je svojevrsni "Power Query Week", u tijeku je. Ovo je naš treći primjer Power Queryja zaredom, pa ću koristiti Power Query. Spremit ću ovaj lijevi u stol. Pažljivo ću imenovati ovo, a ne Tablica 1. Nazvat ću ga. Kasnije ćemo morati ponovno upotrijebiti to ime, pa ću ga nazvati Pitanja-- tako. A onda će ovo biti tablica 2, ali preimenovat ću to u Kupci - ne toliko važno da bih ovaj preimenovao jer je drugi koji mora imati ime. Dakle, mi ćemo odabrati ovo; Podaci; i reći ćemo From Table / Range. Dohvat i transformacija podataka - ovo je poznato kao Power Query. Ugrađen je u Excel 2016. Ako imate 2010. ili 2013., u sustavu Windows,ni Mac, ni iOS, ni Android, Power Query možete besplatno preuzeti s Microsofta.
Dakle, podatke ćemo dobiti iz tablice / raspona; evo našeg stola - nećemo mu ništa učiniti, samo zatvorite i učitajte; Zatvori i učitaj u; samo Stvori vezu; u redu, i vidite, naziv tog upita je Pitanja. Koristi isto ime kao ovdje. A onda se vraćamo ovom, i, Data; Iz tablice / raspona; Dakle, postoji popis naših 1000 ili više kupaca.
Hej, evo, viknite Miguela Escobara, mog prijatelja, koji je koautor M Is For (DATA) MONKEY). U video ću staviti vezu na to - sjajna knjiga o Power Queryu - pomogla mi je u ovome. Umetnut ćemo potpuno novi prilagođeni stupac, a formula prilagođenog stupca je ovdje: = # "naziv upita". Nikad to ne bih shvatio bez Miguela, pa hvala Miguelu na tome.
A kad kliknem OK, da, izgleda da nije uspjelo - samo dobijemo stol, stol, stol, ali to je točno ono što smo imali jučer s Kaylee i kartama. I sve što moram učiniti je proširiti ovo, a zapravo ću reći da mi vjerojatno ne treba Sekvenca … pa, stavimo je za svaki slučaj. Možemo ga izvaditi nakon što ga vidimo. Trenutno imamo 1000 redaka, a sada imamo 7000 redova-- prekrasno. Sad vidim da se pojavljuje u Sequence, pa mi to nije potrebno. Desnim klikom ću ukloniti samo taj jedan stupac. A onda mogu kući; Zatvori i učitaj; i BAM! - sada bismo trebali imati više od 7000 redaka sa 6 pitanja i prazno mjesto za svakog kupca. Quentin je bio oduševljen onim na seminaru. Kul, kul trik - izbjegava VBA, izbjegava cijelu gomilu formula koristeći Index,i takve stvari-- sjajan put.
Ali, hej, danas, dopusti mi da te ispratim s M Is For (DATA) MONKEY. Ken Puls i Miguel Escobar napisali su najveću knjigu o Power Queryju. Volim tu knjigu; za 2 sata postat ćete profesionalac s tom knjigom.
U redu, završite danas - Quentin mora generirati identičnu anketu za 1000 različitih kupaca. Za svakog kupca postoji 6 ili 7 ili 8 pitanja. Sada bismo to mogli učiniti s VBA-om ili makronaredbom, ali, budući da smo ovdje pokrenuli Power Query, napravimo Power Query. Pitanjima sam dodao dodatno prazno pitanje; Dodao sam redni broj kako bih bio siguran da tamo ostane prazno; pretvoriti kupce u stol; složite pitanja u tablicu; stvarno je važno da Pitanja imenujete nešto čega se sjećate - svoje sam nazvao "Pitanja". Dodajte pitanja u Power Query, samo kao vezu; a zatim, dok dodajete kupce u Power Query, stvorite novi prilagođeni stupac u kojem je formula: # "naziv prvog upita", a zatim proširite taj stupac u uređivaču Power Query; Zatvoriti &Učitajte proračunsku tablicu i gotovi ste. Nevjerojatan trik - volim Power Query - najveća stvar koja se dogodi Excelu u 20 godina.
Želim zahvaliti Quentinu što se pojavio na mom seminaru. Već je nekoliko puta bio na mom seminaru-- sjajan momak. Želim vam zahvaliti što ste navratili. Vidimo se sljedeći put za još jedan prijenos od.
Preuzmite datoteku Excel
Da biste preuzeli excel datoteku: zajam-anketa-eksplozija.xlsx
Power Query me i dalje oduševljava. Pogledajte knjigu M je za majmuna podataka kako biste saznali više o Power Queryju.
Excel misao dana
Pitao sam svoje prijatelje Excel Master za savjet o Excelu. Današnja misao za razmišljanje:
"S AGREGATE-om možete učiniti sve osim da ga razumijete."
Liam Bastick
