De vraag van vandaag van Quentin die bij mijn Atlanta Power Excel-seminar was. Quentin moet dezelfde 7 enquêtevragen genereren voor elk van de 1000+ klanten in Excel.
Zoals je in deze figuur kunt zien, staan de klanten in A. De te herhalen vragen staan in kolom D.
Je zou dit kunnen oplossen met VBA of formules, maar dit is Power Query-week bij, dus ik ga een coole truc gebruiken in Power Query.
Als u een lege rij tussen elke enquête wilt, voegt u een volgnummer toe en voegt u het nummer 7 toe na de laatste vraag.
Druk op Ctrl + T vanuit beide gegevenssets. Geef de tweede dataset een naam die u kunt onthouden, zoals Vragen of Enquête.
Gebruik Gegevens, uit tabel vanaf de tweede gegevensset.
De Power Query-editor wordt geopend. Kies op het tabblad Home de vervolgkeuzelijst Sluiten en laden en kies Sluiten en laden naar…. Kies in het volgende dialoogvenster Alleen een verbinding maken.
U bent nu terug in Excel. Kies een cel in de klantentabel in kolom A. Gegevens, uit tabel. Zodra de Query-editor is geopend, klikt u op het tabblad Kolom toevoegen in het lint en kiest u vervolgens Aangepaste kolom. De formule is =#"Questions"(inclusief de # en de aanhalingstekens).
Er verschijnt een nieuwe kolom in de editor met de waarde Tabel die in elke rij wordt herhaald. Klik op het pictogram Uitvouwen in de kolomkop.
Kies beide velden in de tabel. Kies op het tabblad Start de optie Sluiten en laden.
Er verschijnt een nieuw werkblad met de 7 vragen die voor elk van de 1000+ klanten worden herhaald.
Bekijk video
Videotranscriptie
Leer Excel van podcast-aflevering 2205: explosie van leningonderzoek.
Hé, welkom terug bij netcast, ik ben Bill Jelen. Nu, gisteren in aflevering 2204, was het Kaylee uit Nashville die een VERT.ZOEKEN-explosie moest doen - voor elk item hier in kolom D hadden we een bijpassende reeks items in kolom G en moesten die exploderen. Dus als Paleis C 8 items had, zouden we 8 rijen krijgen.
Nu, vandaag hebben we Quentin. Nu was Quentin op mijn seminar in Atlanta, maar hij komt eigenlijk uit Florida, en Quentin heeft hier bijna 1000 klanten - nou ja, meer dan 1000 klanten - in kolom A, en voor elke klant moet hij deze enquête maken - - dit overzicht van 1, 2, 3, 4, 5, 6 vragen. En wat ik hier ga doen, is dat ik een volgnummer toevoeg met alleen de nummers 1 tot en met 7, zodat ik op die manier een mooie lege rij ertussen kan maken. Ik ga van beide datasets een tabel maken; dus we proberen deze 7 rijen te laten exploderen voor elk van deze 1000 klanten. Dat is het doel.
Nu kan ik dit doen met VPA; Ik kan dit doen met formules; maar het is een soort "Power Query Week" hier, we zijn bezig met dit is ons derde Power Query-voorbeeld op rij, dus ik ga Power Query gebruiken. Ik ga van deze linker een tafel maken. Ik ga heel voorzichtig zijn om dit niet tabel 1 te noemen. Ik ga het een naam geven. We zullen die naam later opnieuw moeten gebruiken, dus ik ga het vragen noemen, zoals dat. En dan wordt dit tabel 2, maar ik ga dat hernoemen tot klanten - niet zo belangrijk dat ik deze hernoem omdat het de tweede is die de naam moet hebben. Dus we gaan hiervoor kiezen; Gegevens; en we gaan zeggen From Table / Range. Gegevens ophalen en transformeren - dit staat bekend als Power Query. Het is ingebouwd in Excel 2016. Als u 2010 of 2013 heeft, in Windows,geen Mac, geen iOS, geen Android, u kunt Power Query gratis downloaden van Microsoft.
Dus we gaan gegevens halen uit tabel / bereik; hier is onze tafel - we gaan er niets aan doen, alleen sluiten en laden; Sluiten en laden naar; alleen een verbinding maken; oke, en kijk, de naam van die vraag is vragen. Het gebruikt dezelfde naam als hier. En dan komen we terug op deze en Data; Van tafel / reeks; er is dus een lijst met onze 1000 of meer klanten.
Hey nu, hier is een schreeuw naar Miguel Escobar, mijn vriend, die de co-auteur is van M Is For (DATA) MONKEY). Ik zal daar een link naar plaatsen in de video - geweldig boek over Power Query - dat me hiermee heeft geholpen. We gaan een gloednieuwe aangepaste kolom toevoegen, en de aangepaste kolomformule is hier: = # "de naam van de zoekopdracht". Ik zou dat nooit hebben bedacht zonder Miguel, dus dank aan Miguel daarvoor.
En als ik op OK klik, ja, het ziet er niet naar uit dat het werkte - we krijgen gewoon tafel, tafel, tafel, maar dat is precies wat we gisteren hadden met Kaylee en de kaartverkoop. En het enige wat ik hoef te doen is dit uitbreiden, en ik ga eigenlijk zeggen dat ik de Sequence waarschijnlijk niet nodig heb … nou, laten we het erin doen voor het geval dat. We kunnen het eruit halen nadat we het hebben gezien. Op dit moment hebben we 1000 rijen en nu hebben we 7000 rijen - prachtig. Ik kan nu zien dat het in Sequence verschijnt, dus dat heb ik niet nodig. Ik klik met de rechtermuisknop en verwijder alleen die ene kolom. En dan kan ik naar huis; Sluiten en laden; en BAM! - we zouden nu meer dan 7000 rijen met 6 vragen en een lege ruimte voor elke klant moeten hebben. Quentin was daar enthousiast over tijdens het seminar. Coole, coole truc - vermijdt VBA, vermijdt een hele reeks formules met Index,en dat soort dingen … een geweldige manier om te gaan.
Maar laat me je vandaag op pad sturen met M Is For (DATA) MONKEY. Ken Puls en Miguel Escobar schreven het beste boek over Power Query. Ik hou van dat boek; in 2 uur word je een professional met dat boek.
Oké, dus sluit het vandaag af - Quentin moet een identieke enquête genereren voor 1000 verschillende klanten. Per klant zijn er 6 of 7 of 8 vragen. Nu kunnen we dit doen met VBA of macro, maar aangezien we hier een Power Query uitvoeren, laten we een Power Query doen. Ik heb een extra lege vraag aan de vragen toegevoegd; Ik heb een volgnummer toegevoegd om ervoor te zorgen dat de blanco daar blijft; maak van de klanten een tafel; maak van de vragen een tabel; het is heel belangrijk dat je vragen noemt die je kunt onthouden. Ik noemde de mijne 'vragen'. Voeg de vragen toe aan Power Query, alleen als verbinding; en vervolgens, terwijl u de klanten aan Power Query toevoegt, maakt u een nieuwe aangepaste kolom met de formule: # "de naam van de eerste query" en vouwt u die kolom vervolgens uit in de Power Query-editor; Dichtbij &Laad terug naar de spreadsheet en u bent klaar. Een geweldige truc - ik ben dol op Power Query - het beste wat Excel in 20 jaar is overkomen.
Ik wil Quentin bedanken voor zijn optreden op mijn seminar. Hij is al een paar keer naar mijn seminar geweest … geweldige kerel. Ik wil je bedanken voor het langskomen. We zien je de volgende keer voor een nieuwe netcast van.
Download Excel-bestand
Om het Excel-bestand te downloaden: loan-survey-explosion.xlsx
Power Query blijft me verbazen. Bekijk het boek M is voor Data Monkey voor meer informatie over Power Query.
Excel-gedachte van de dag
Ik heb mijn Excel Master-vrienden om advies over Excel gevraagd. De gedachte van vandaag om na te denken:
"Je kunt alles doen met AGGREGATE, behalve het begrijpen."
Liam Bastick
