Dagens spørgsmål fra Quentin, der var på mit Atlanta Power Excel-seminar. Quentin skal generere de samme 7 spørgeskemaundersøgelser til hver af 1000+ kunder i Excel.
Som du kan se i denne figur, er kunderne i A. Spørgsmålene, der skal gentages, er i kolonne D.
Du kunne løse dette med VBA eller formler, men det er Power Query-uge kl., Så jeg vil bruge et sejt trick i Power Query.
Hvis du vil have en tom række mellem hver undersøgelse, skal du tilføje et sekvensnummer og tilføje nummeret 7 efter det sidste spørgsmål.
Tryk på Ctrl + T fra begge datasæt. Navngiv det andet datasæt med et navn, du kan huske, noget som spørgsmål eller undersøgelse.
Fra det andet datasæt skal du bruge Data, Fra tabel.
Power Query-editoren åbnes. Fra fanen Hjem skal du vælge rullelisten Luk og indlæs og vælg Luk og indlæs til…. I den næste dialog skal du vælge Kun Opret en forbindelse.
Du er nu tilbage i Excel. Vælg en hvilken som helst celle i kundetabellen i kolonne A. Data, fra tabel. Når Query Editor åbnes, skal du klikke på fanen Tilføj kolonne i båndet og derefter vælge Tilpasset kolonne. Formlen er =#"Questions"(inklusive # og anførselstegn).
En ny kolonne vises i editoren med værditabellen gentaget i hver række. Klik på ikonet Udvid i kolonneoverskriften.
Vælg begge felter i tabellen. Vælg Luk og indlæs under fanen Hjem.
Et nyt regneark vises med de 7 spørgsmål gentaget for hver af de 1000+ kunder.
Se video
Videoudskrift
Lær Excel fra Podcast Episode 2205: Loan Survey Explosion.
Hej, velkommen tilbage til netcast, jeg er Bill Jelen. Nu, lige i går i episode 2204, var det Kaylee fra Nashville, der måtte lave en VLOOKUP-eksplosion - for hver vare her i kolonne D havde vi en matchende flok ting i kolonne G og havde brug for at eksplodere dem. Så hvis Palace C havde 8 varer, ville vi få 8 rækker.
Nu i dag har vi Quentin. Nu var Quentin på mit seminar i Atlanta, men han er faktisk fra Florida, og Quentin har næsten 1000 kunder herover - godt, mere end 1000 kunder - i kolonne A, og for hver kunde skal han oprette denne undersøgelse - - denne undersøgelse af 1, 2, 3, 4, 5, 6 spørgsmål. Og hvad jeg skal gøre her, er at jeg tilføjer et sekvensnummer bare med tallene 1 til 7, så på den måde kan jeg oprette en dejlig tom række imellem. Jeg vil lave begge disse datasæt til en tabel; så vi prøver at få disse 7 rækker eksploderet for hver af disse 1000 kunder. Det er målet.
Nu kan jeg gøre dette med VPA; Jeg kan gøre dette med formler; men det er en slags "Power Query Week" her, vi kører, dette er vores tredje Power Query-eksempel i træk, så jeg vil bruge Power Query. Jeg vil gøre denne venstre til et bord. Jeg vil være meget forsigtig med at navngive dette ikke tabel 1. Jeg giver det et navn. Vi bliver nødt til at genbruge dette navn senere, så jeg kalder det spørgsmål - sådan. Og så vil dette være tabel 2, men jeg vil omdøbe det til at være kunder - ikke så vigtigt, at jeg omdøber denne, fordi det er den anden, der skal have navnet. Så vi skal vælge dette; Data; og vi siger fra bord / rækkevidde. Hent og transformer data - dette er kendt som Power Query. Det er indbygget i Excel 2016. Hvis du har 2010 eller 2013, på Windows,ikke en Mac, ikke iOS, ikke Android, du kan downloade Power Query gratis fra Microsoft.
Så vi får data fra tabel / rækkevidde; her er vores bord - vi vil ikke gøre noget ved det, bare Luk og indlæs; Luk & indlæs til; kun Opret en forbindelse; okay, og se, navnet på denne forespørgsel er spørgsmål. Det bruger samme navn som her. Og så kommer vi tilbage til denne, og, Data; Fra tabel / rækkevidde; så der er en liste over vores 1000 eller flere kunder.
Hej nu, her er et råb til Miguel Escobar, min ven, som er medforfatter af M Is For (DATA) MONKEY). Jeg sætter et link til det i videoen - stor bog om Power Query - hjalp mig med dette. Vi lægger en helt ny brugerdefineret kolonne, og den tilpassede kolonneformel er lige her: = # "navnet på forespørgslen". Jeg ville aldrig have fundet ud af det uden Miguel, så tak til Miguel for det.
Og når jeg klikker på OK, ja, det ser ikke ud til at det fungerede - vi får bare bord, bord, bord, men det var præcis, hvad vi havde i går med Kaylee og billetprisen. Og alt hvad jeg skal gøre er at udvide dette, og jeg vil faktisk sige, at jeg sandsynligvis ikke har brug for sekvensen … ja, lad os sætte det i bare i tilfælde. Vi kan tage det ud, når vi ser det. Lige nu har vi 1000 rækker, og nu har vi 7000 rækker - smukke. Jeg kan nu se, at det vises i sekvens, så jeg har ikke brug for det. Jeg højreklikker og fjern bare den ene kolonne. Og så kan jeg komme hjem; Luk & indlæs; og BAM! - vi skal nu have mere end 7000 rækker med 6 spørgsmål og et tomt sted til hver kunde. Quentin var begejstret for den i seminaret. Cool, cool trick-- undgår VBA, undgår en hel masse formler ved hjælp af Index,og sådan noget - fantastisk vej at gå.
Men hej, i dag, lad mig sende dig afsted med M Is For (DATA) ABE. Ken Puls og Miguel Escobar skrev den største bog om Power Query. Jeg elsker den bog; om 2 timer bliver du en proff med den bog.
Okay, så afslut i dag - Quentin skal generere en identisk undersøgelse for 1000 forskellige kunder. Der er 6 eller 7 eller 8 spørgsmål til hver kunde. Nu kunne vi gøre dette med VBA eller makro, men da vi kører her, lad os lave en strømforespørgsel. Jeg tilføjede et ekstra blankt spørgsmål til spørgsmålene; Jeg tilføjede et sekvensnummer for at sikre, at tomt forbliver der; gøre kunderne til et bord lav spørgsmålene til en tabel; det er virkelig vigtigt, at du navngiver spørgsmål noget, du kan huske - jeg kaldte mine "spørgsmål". Føj spørgsmålene til Power Query, kun som en forbindelse; og derefter, når du tilføjer kunderne til Power Query, skal du oprette en ny brugerdefineret kolonne, hvor formlen er: # "navnet på den første forespørgsel" og derefter udvide denne kolonne i Power Query-editoren; Tæt &Læg tilbage til regnearket, så er du færdig. Et fantastisk trick-- Jeg elsker Power Query - den største ting der skal ske med Excel i 20 år.
Jeg vil takke Quentin for at have vist på mit seminar. Han har været på mit seminar et par gange før - god fyr. Jeg vil gerne takke dig, fordi du kom forbi. Vi ses næste gang til endnu en netcast fra.
Download Excel-fil
For at downloade excel-filen: loan-survey-explosion.xlsx
Power Query fortsætter med at forbløffe mig. Tjek bogen M er for Data Monkey for at lære mere Power Query.
Excel-tanken om dagen
Jeg har bedt mine Excel Master-venner om deres råd om Excel. Dagens tanke at tænke over:
"Du kan gøre hvad som helst med AGGREGATE undtagen at forstå det."
Liam Bastick
