Dagens spørsmål fra Quentin som var på Atlanta Power Excel-seminaret mitt. Quentin må generere de samme 7 spørreundersøkelsesspørsmålene for hver av 1000+ kunder i Excel.
Som du kan se i denne figuren, er kundene i A. Spørsmålene som skal gjentas er i kolonne D.
Du kan løse dette med VBA eller formler, men dette er Power Query-uken kl, så jeg skal bruke et kult triks i Power Query.
Hvis du vil ha en tom rad mellom hver undersøkelse, kan du legge til et sekvensnummer og legge til nummeret 7 etter det siste spørsmålet.
Trykk Ctrl + T fra begge datasettene. Navngi det andre datasettet med et navn du kan huske, noe som spørsmål eller spørreundersøkelse.
Fra det andre datasettet, bruk Data, From Table.
Power Query-redigereren åpnes. Fra rullegardinmenyen Hjem velger du Lukk og last ned rullegardinmenyen og velger Lukk og last til …. I den neste dialogen velger du Bare Opprett en tilkobling.
Du er nå tilbake i Excel. Velg hvilken som helst celle i kundetabellen i kolonne A. Data, fra tabell. Når spørreredigereren åpnes, klikker du på kategorien Legg til kolonne i båndet og velger deretter Egendefinert kolonne. Formelen er =#"Questions"(inkludert # og anførselstegn).
En ny kolonne vises i redigeringsprogrammet med verdien Tabell gjentatt i hver rad. Klikk på Utvid-ikonet i kolonneoverskriften.
Velg begge feltene i tabellen. Velg Lukk og last i kategorien Hjem.
Et nytt regneark vises med de 7 spørsmålene gjentatt for hver av 1000+ kunder.
Se på video
Videoutskrift
Lær Excel fra Podcast Episode 2205: Loan Survey Explosion.
Hei, velkommen tilbake til netcast, jeg er Bill Jelen. Nå i går, i episode 2204, var det Kaylee fra Nashville som måtte gjøre en VLOOKUP-eksplosjon - for hvert element her i kolonne D hadde vi en matchende haug med ting i kolonne G og trengte å eksplodere dem. Så hvis Palace C hadde 8 ting, ville vi få 8 rader.
Nå i dag har vi Quentin. Nå var Quentin på seminaret mitt i Atlanta, men han er faktisk fra Florida, og Quentin har nesten 1000 kunder her - vel, mer enn 1000 kunder - i kolonne A, og for hver kunde må han lage denne undersøkelsen - - denne undersøkelsen av 1, 2, 3, 4, 5, 6 spørsmål. Og det jeg skal gjøre her er at jeg skal legge til et sekvensnummer bare med tallene 1 til 7, så på den måten kan jeg lage en fin blank rad i mellom. Jeg skal lage begge disse datasettene til en tabell; så vi prøver å få disse 7 radene eksplodert for hver av disse 1000 kundene. Det er målet.
Nå kan jeg gjøre dette med VPA; Jeg kan gjøre dette med formler; men det er en slags "Power Query Week" her, vi er i gang med dette er vårt tredje Power Query-eksempel på rad, så jeg skal bruke Power Query. Jeg skal lage denne venstre til et bord. Jeg kommer til å være veldig forsiktig med å nevne dette ikke tabell 1. Jeg skal gi det et navn. Vi blir nødt til å bruke det navnet senere, så jeg kommer til å kalle det spørsmål - slik. Og så vil dette være tabell 2, men jeg skal gi det nytt navn til kunder - ikke så viktig at jeg omdøper denne fordi den er den andre som må ha navnet. Så vi skal velge dette; Data; og vi skal si Fra bord / rekkevidde. Få og transformer data - dette er kjent som Power Query. Den er innebygd i Excel 2016. Hvis du har 2010 eller 2013, på Windows,ikke en Mac, ikke iOS, ikke Android, du kan laste ned Power Query gratis fra Microsoft.
Så vi skal få data fra tabell / rekkevidde; her er vårt bord - vi kommer ikke til å gjøre noe med det, bare lukk og last; Lukk og last inn; bare opprett en tilkobling; ok, og se, navnet på den spørringen er spørsmål. Den bruker samme navn som her. Og så kommer vi tilbake til denne, og, Data; Fra bord / rekkevidde; så det er en liste over 1000 eller flere kunder.
Hei nå, her er et rop til Miguel Escobar, min venn, som er medforfatter av M Is For (DATA) MONKEY). Jeg vil sette en lenke til det i videoen - flott bok om Power Query - hjalp meg med dette. Vi skal sette inn en helt ny tilpasset kolonne, og den tilpassede kolonneformelen er denne akkurat her: = # "navnet på spørringen". Jeg hadde aldri skjønt det uten Miguel, så takk til Miguel for det.
Og når jeg klikker OK, ja, det ser ikke ut til at det fungerte - vi får bare bord, bord, bord, men det var akkurat det vi hadde i går med Kaylee og billettprisen. Og alt jeg trenger å gjøre er å utvide dette, og jeg skal faktisk si at jeg sannsynligvis ikke trenger Sekvensen … vel, la oss sette den inn i tilfelle. Vi kan ta den ut etter at vi ser den. Akkurat nå har vi 1000 rader, og nå har vi 7000 rader - vakre. Jeg kan se nå at den vises i Sequence, så jeg trenger ikke det. Jeg høyreklikker og fjerner bare den ene kolonnen. Og så kan jeg komme hjem; Lukk og last inn; og BAM! - vi skal nå ha mer enn 7000 rader med 6 spørsmål og en blank plass for hver kunde. Quentin var begeistret for den i seminaret. Kult, kult triks - unngår VBA, unngår en hel haug med formler ved hjelp av indeks,og sånne ting - flott vei å gå.
Men hei, i dag, la meg sende deg med M Is For (DATA) APE. Ken Puls og Miguel Escobar skrev den største boka om Power Query. Jeg elsker den boka; om to timer blir du proff med den boka.
OK, så avslutt i dag - Quentin må lage en identisk undersøkelse for 1000 forskjellige kunder. Det er 6 eller 7 eller 8 spørsmål til hver kunde. Nå kan vi gjøre dette med VBA eller makro, men siden vi er i en Power Query-kjøring her, la oss gjøre en Power Query. Jeg la til et ekstra blankt spørsmål til spørsmålene; Jeg la til et sekvensnummer for å forsikre meg om at tomt blir der; gjøre kundene til et bord; lag spørsmålene til et bord; det er veldig viktig at du navngir spørsmål noe du kan huske - jeg kalte mine "spørsmål". Legg til spørsmålene i Power Query, Only as a Connection; og når du legger til kundene i Power Query, oppretter du en ny tilpasset kolonne der formelen er: # "navnet på det første spørringen" og utvider deretter den kolonnen i Power Query-redigereren. Lukk &Last tilbake til regnearket, så er du ferdig. Et utrolig triks - Jeg elsker Power Query - den største tingen å skje med Excel på 20 år.
Jeg vil takke Quentin for showet på seminaret mitt. Han har vært på seminaret mitt et par ganger før - flott fyr. Jeg vil takke deg for at du var innom. Vi sees neste gang for en ny netcast fra.
Last ned Excel-fil
For å laste ned Excel-filen: loan-survey-explosion.xlsx
Power Query fortsetter å forbløffe meg. Sjekk ut boken M er for Data Monkey for å lære mer Power Query.
Excel-tanken om dagen
Jeg har bedt mine Excel Master-venner om deres råd om Excel. Dagens tanke å tenke på:
"Du kan gjøre hva som helst med AGGREGATE, bortsett fra å forstå det."
Liam Bastick
