Dagens fråga från Quentin som var på mitt Atlanta Power Excel-seminarium. Quentin måste skapa samma 7 enkätsfrågor för var och en av 1000+ kunder i Excel.
Som du kan se i denna figur är kunderna i A. Frågorna som ska upprepas finns i kolumn D.
Du kan lösa detta med VBA eller formler, men det här är Power Query-veckan kl, så jag ska använda ett coolt trick i Power Query.
Om du vill ha en tom rad mellan varje enkät, lägg till ett sekvensnummer och lägg till siffran 7 efter den sista frågan.
Tryck på Ctrl + T från båda datamängderna. Namnge den andra datamängden med ett namn du kan komma ihåg, något som Frågor eller Undersökning.
Från den andra datamängden, använd Data, Från tabell.
Power Query-redigeraren öppnas. På fliken Hem väljer du rullgardinsmenyn Stäng och läs in och välj Stäng och ladda till …. I nästa dialogruta väljer du Endast skapa en anslutning.
Du är nu tillbaka i Excel. Välj vilken cell som helst i kundtabellen i kolumn A. Data, från tabell. När frågeredigeraren öppnas klickar du på fliken Lägg till kolumn i menyfliksområdet och väljer sedan Anpassad kolumn. Formeln är =#"Questions"(inklusive # och citattecken).
En ny kolumn visas i redigeraren med värdetabellen upprepas i varje rad. Klicka på Expand-ikonen i kolumnrubriken.
Välj båda fälten i tabellen. Välj Stäng och ladda in på fliken Hem.
Ett nytt kalkylblad kommer att visas med de 7 frågorna som upprepas för var och en av 1000+ kunder.
Kolla på video
Videoutskrift
Lär dig Excel från Podcast avsnitt 2205: Loan Survey Explosion.
Hej, välkommen tillbaka till netcast, jag heter Bill Jelen. Nu, just igår i avsnitt 2204, var det Kaylee från Nashville som var tvungen att göra en VLOOKUP-explosion - för varje objekt här i kolumn D hade vi en matchande massa saker över i kolumn G och behövde explodera dem. Så om Palace C hade åtta föremål skulle vi få 8 rader.
Nu, idag, har vi Quentin. Nu var Quentin på mitt seminarium i Atlanta, men han är faktiskt från Florida, och Quentin har nästan 1000 kunder här - ja, mer än 1000 kunder - i kolumn A, och för varje kund måste han skapa den här undersökningen- - den här undersökningen av 1, 2, 3, 4, 5, 6 frågor. Och vad jag ska göra här är att jag lägger till ett sekvensnummer bara med siffrorna 1 till 7, så på det sättet kan jag skapa en fin tom rad däremellan. Jag ska göra båda dessa datamängder till en tabell; så vi försöker få dessa 7 rader att explodera för var och en av dessa 1000 kunder. Det är målet.
Nu kan jag göra detta med VPA; Jag kan göra detta med formler; men det är typ av "Power Query Week" här, vi kör på det här är vårt tredje Power Query-exempel i rad, så jag ska använda Power Query. Jag ska göra den här vänstra till ett bord. Jag kommer att vara mycket försiktig med att namnge detta inte tabell 1. Jag ska ge det ett namn. Vi kommer att behöva återanvända det namnet senare, så jag kommer att kalla det frågor - så. Och då kommer detta att vara tabell 2, men jag ska byta namn på det för att vara kunder - inte så viktigt att jag byter namn på den här eftersom den är den andra som måste ha namnet. Så vi ska välja det här; Data; och vi ska säga Från tabell / intervall. Hämta och omvandla data - detta kallas Power Query. Det är inbyggt i Excel 2016. Om du har 2010 eller 2013, på Windows,inte en Mac, inte iOS, inte Android, du kan ladda ner Power Query gratis från Microsoft.
Så vi kommer att få data från tabell / intervall; här är vårt bord - vi ska inte göra något åt det, bara stäng och ladda; Stäng & ladda till; bara skapa en anslutning; okej, och se, namnet på den frågan är frågor. Den använder samma namn som här. Och sedan kommer vi tillbaka till den här, och, Data; Från tabell / intervall; så det finns en lista över våra 1000 eller fler kunder.
Hej nu, här är ett rop till Miguel Escobar, min vän, som är medförfattare till M Is For (DATA) MONKEY). Jag lägger till en länk till det i videon - bra bok om Power Query - hjälpte mig med detta. Vi ska sätta in en helt ny anpassad kolumn, och formeln för anpassad kolumn är här: = # "namnet på frågan". Jag skulle aldrig ha tänkt på det utan Miguel, så tack till Miguel för det.
Och när jag klickar på OK, ja, det ser inte ut som om det fungerade - vi får bara bord, bord, bord, men det var precis vad vi hade igår med Kaylee och biljetten. Och allt jag behöver göra är att utöka detta, och jag kommer faktiskt att säga att jag förmodligen inte behöver sekvensen … ja, låt oss sätta in den för alla fall. Vi kan ta ut det när vi ser det. Just nu har vi 1000 rader och nu har vi 7000 rader - vackra. Jag kan se nu att det visas i sekvens, så jag behöver inte det. Jag högerklickar och tar bort bara den kolumnen. Och då kan jag komma hem; Stäng & ladda; och BAM! - vi borde nu ha mer än 7000 rader med 6 frågor och ett tomt utrymme för varje kund. Quentin var mycket nöjd med den i seminariet. Coolt, coolt trick - undviker VBA, undviker en hel massa formler med Index,och sådant - bra väg att gå.
Men hej, idag, låt mig skicka dig iväg med M Is For (DATA) MONKEY. Ken Puls och Miguel Escobar skrev den största boken om Power Query. Jag älskar den boken; om två timmar blir du proffs med den boken.
Okej, så, avsluta idag - Quentin måste skapa en identisk undersökning för 1000 olika kunder. Det finns 6 eller 7 eller 8 frågor för varje kund. Nu kan vi göra detta med VBA eller makro, men eftersom vi kör en Power Query här, låt oss göra en Power Query. Jag lade till en extra tom fråga i frågorna; Jag lade till ett sekvensnummer för att se till att tomt stannar där; göra kunderna till ett bord; göra frågorna till en tabell; det är väldigt viktigt att du heter Frågor något du kommer ihåg - jag kallade mina "Frågor". Lägg till frågorna i Power Query, Only as a Connection; och sedan, när du lägger till kunderna i Power Query, skapar du en ny anpassad kolumn där formeln är: # "namnet på den första frågan" och sedan Expandera den kolumnen i Power Query-redigeraren; Stänga &Ladda tillbaka till kalkylbladet så är du klar. Ett fantastiskt knep - Jag älskar Power Query - det bästa som händer med Excel på 20 år.
Jag vill tacka Quentin för att ha visat på mitt seminarium. Han har varit på mitt seminarium ett par gånger tidigare - bra kille. Jag vill tacka dig för att du kom förbi. Vi ses nästa gång för en ny netcast från.
Ladda ner Excel-fil
För att ladda ner Excel-filen: loan-survey-explosion.xlsx
Power Query fortsätter att förvåna mig. Kolla in boken M är för Data Monkey för att lära dig mer Power Query.
Excel-tanke på dagen
Jag har frågat mina Excel Master-vänner om deras råd om Excel. Dagens tanke att fundera på:
"Du kan göra vad som helst med AGGREGATE förutom att förstå det."
Liam Bastick
