Kaley fra Nashville arbejder på et billetark. For hver begivenhed vælger hun en billetplan. Denne billetplan kunne angive hvor som helst fra 4 til 16 billettyper til begivenheden. Kaley ønsker en formel, der går til opslagstabellen og returnerer * alle * matches og indsætter nye rækker efter behov.
Selvom jeg ikke har en VLOOKUP, der kan løse dette, kan de nye Power Query-værktøjer, der er indbygget i Excel 2016, løse det.
Bemærk
Hvis du har Windows-versionen af Excel 2010 eller Excel 2013, kan du downloade Power Query gratis fra Microsoft. Desværre er Power Query endnu ikke tilgængelig til Excel til Android, Excel til iOS eller Excel til Mac.
For at illustrere målet: Mike McCann and the Mechanics vises i Allen Theatre med billetplan C. Da der er fire matchende rækker i opslagstabellen, vil Kaley have fire rækker, der siger Mike McCann and the Mechanics, hver med en anden match fra opslagstabellen.
Vælg en celle i den originale tabel. Tryk på Ctrl + T for at markere disse data som en tabel. Omdøb tabellen fra Tabel1 til Vis på fanen Tabelværktøjer. Gentag for opslagstabellen, kald det Billetter.
Vælg en celle i tabellen Vis. Fra fanen Data skal du vælge Fra tabel / rækkevidde.
Når Power Query-editoren er åbnet, skal du åbne rullelisten Luk og indlæs og vælge Luk og indlæs til….
I dialogboksen Importer data skal du kun vælge Opret en forbindelse.
Gå til billetbordet. Gentag trinene til Kun Opret en forbindelse til billetter. Du skal se begge forbindelser i forespørgselsruden:
Vælg en hvilken som helst tom celle. Vælg Data, Hent data, kombiner forespørgsler, flet.
Der er seks trin i dialogboksen Flet. Den 3. og 4. synes ikke intuitiv for mig.
- Vælg Vis fra det øverste rullemenu
- Vælg billetter fra den anden rullemenu.
- Klik på overskriften til Billetplan øverst for at vælge den kolonne som den fremmede nøgle i tabellen Vis.
- Klik på overskriften til Billetplan nederst for at vælge den kolonne som nøglefelt i opslagstabellen.
- Åbn tilslutningstypen, og vælg Indre (kun matchende rækker).
- Klik på OK
Resultaterne er oprindeligt skuffende. Du ser alle felterne fra tabel 1 og en kolonne, der siger Tabel, Tabel, Tabel.
Klik på ikonet Udvid øverst i kolonnen Billetter.
Fravælg billetplan, da du allerede har dette felt. Det resterende felt kaldes Tickets.Billettype, medmindre du fjerner afkrydsningsfeltet Brug originalt navn som præfiks.
Succes! Hver række for hvert show eksploderer i flere rækker.
Jeg er ikke særlig tilfreds med sorteringen af dataene. Sortering efter dato får billettyperne til at sortere på en underlig måde.
Se video
I dagens tilfælde blev videoen optaget, efter at artiklen blev skrevet. Jeg foreslår, at du tilføjer en sekvenskolonne til billettyperne for at kontrollere sorteringsrækkefølgen.
Videoudskrift
Lær Excel fra Podcast, afsnit 2204: Returner alle VLOOKUP'er.
Hej, velkommen tilbage til netcast, jeg er Bill Jelen. Dagens spørgsmål fra Nashville Music City. Jeg var dernede i Nashville, nogen er ansvarlig for at planlægge indlæsning af billetter i et billetsystem, og så er her hvad vi har: Vi har en liste over begivenheder - kommende begivenheder - vi har datoen, spillestedet og en billetplan. Så, ligesom selvom der holdes noget ved paladset, kan der være forskellige billetplaner - ligesom gulvet måske er konfigureret, ved du med pladser, eller måske er det kun en stående plads, ikke?
Så afhængigt af hvilken type billetplan skal du komme herover til Opslagstabellen og finde alle de matchende begivenheder, og i det væsentlige skal vi gøre det, jeg kalder en VLOOKUP-eksplosion. Så hvis der er noget ved Hannah C, går de ned til Hannah C, og hvis der er - 1, 2, 3, 4, 5, 6--7 ting i Hannah C, har vi for at returnere syv rækker - hvilket betyder, at du bliver nødt til at indsætte seks flere rækker og kopiere dataene ned. I orden.
Nu vil vi slet ikke gøre dette med en VLOOKUP, men du får konceptet - vi laver en VLOOKUP, og vi returnerer alle svarene som nye rækker. Okay, så jeg tager begge disse tabeller og gør dem til et rigtigt bord med Ctrl + T. De første kaldte tabel 1 - forfærdeligt navn, lad os kalde dette begivenheder eller shows, lad os kalde det viser, sådan - og den anden, nu, hej, her er hvad jeg lærte, fordi jeg praktiserede dette - vi skal et sekvensfelt her. Så = RÆK (A1), dobbeltklik, og kopier det ned, og kopier og indsæt derefter specielle værdier. I orden. Nu laver vi det, der gør det til en tabel - Ctrl + T, og vi kalder den ene Billetter.
I orden. Så vi har shows, vi har billetter. Jeg går til fanen Data, og jeg er her i showet, jeg vil sige, at jeg vil hente mine data fra en tabel eller et interval - dette er forresten Power Query. Hvis du er tilbage i Excel 2010 eller 2013, kan du downloade dette gratis fra Microsoft, downloade Power Query-værktøjet. Hvis du bruger en Mac eller iOS eller Android, undskyld, ingen Power Query til dig. Okay, så fra en tabel eller rækkevidde … find nogen, der har en - find en ven, der har en - Windows pc og få dem til at konfigurere dette. I orden. Her er en tabel, vi vil ikke gøre noget ved dette, bare Luk & Indlæs, Luk & Indlæs til og derefter sige "Kun Opret forbindelse", perfekt. Vi kommer herover til vores anden tabel: Få data, fra en tabel eller et interval, vi gør ikke noget ved denne, Close & Load,Luk og indlæs til, "Kun Opret forbindelse", OK. Så hvad vi har nu, er, at vi har en forbindelse til den første tabel og en forbindelse til den anden tabel. Vi vil ikke fusionere disse to, hvilket i det væsentlige er som at gøre VLOOKUP, eller en databaseforbindelse, tror jeg, virkelig er, hvordan det er. Kombiner forespørgsler, vi går sammen. I orden.
Nu, syv ting, du skal gøre i denne dialogboks - og det er lidt forvirrende - vi vælger Vis som den første tabel; vælg Billetter som andet bord; vælg hvilket felt de har til fælles, og dette kan være flere felter - du kan kontrollere-klikke - men i dette tilfælde er der kun en billetplan; og derefter billetplan; og så ændrer vi typen Join til en indre join med "kun de matchende rækker". I orden. Nu klikker du på OK, og du tror, at hele dit problem vil blive løst, men du er bare knust, fordi her er alle data fra A - de har slet ikke indsat nogen nye rækker - og herovre, bare et kedeligt dumt felt kaldet billetter, der bare har bord, bord, bord, hah.
Men heldigvis er der et Udvid-ikon øverst på det, og det skal vi udvide-- Jeg behøver ikke tage en plan, det har jeg allerede - Billetype og rækkefølge. Jeg vil ikke have, at det hedder Tickets.TicketType, hvilket Power Query vil gøre - så jeg fjerner markeringen i dette felt. I orden. Lige nu har vi 17 rækker med data; når jeg klikker på OK, BAM! Der er eksplosionen. Så, Michael Seeley og Starlighter's dukker op med alle de forskellige billettyper, som denne. Okay, og se disse billettyper vises i rækkefølge, det er godt. Men Michael Seeley er ikke det næste show, det næste show er den 5. juni. Så når jeg prøver at sortere dette efter dato - dette gør mig sindssyg, kan jeg ikke forklare dette. Sorter efter dato, og Mike Man and the Mechanics kommer op til 65, men så er billetterne alle sammen. De'er på den forkerte sekvens, og så var det derfor, jeg måtte gøre denne sekvens - føles sådan. Jeg kan sortere efter sekvens. Så nu, 6, 5, smuk, og inden for det er billetterne korrekte. Og faktisk, på dette tidspunkt har vi ikke brug for denne kolonne længere. Så jeg kan højreklikke og fjerne og derefter Lukke & indlæse - denne gang vil jeg faktisk Lukke & indlæse, ikke Lukke og indlæse til-- og vi har vores resultat. I orden.
Så vi gik fra en liste over begivenheder til hele denne store liste, men her er den fantastiske del: Jeg skruede op, Mike Man og Mechanics er ikke Palace B, dens Palace C. Så jeg kommer tilbage til originalen øverst til højre -hjørne for mere information om bogen.
I orden. Emner i denne episode: Kaley i Nashville har brug for en VLOOKUP for at returnere alle kampene, normalt indsætter nye rækker. Og det er en billetdatabase, okay? Så jeg vil kalde dette en VLOOKUP-eksplosion, fordi hvert show eksploderer i op til 16 rækker. Vi skal bruge Power Query til at løse dette, og jeg har lært, at datoen vises i den forkerte sekvens, medmindre vi tilføjer et sekvensfelt til billettypen. Lav begge sæt til en tabel med Ctrl + T; ename dem til at være shows og billetter; og derefter fra hver tabel, Få data, Fra tabel, Luk og indlæs, til Kun at oprette en forbindelse; gentag for det andet bord; derefter Data, Hent data, Kombiner forespørgsler, Flet; og derefter den dialogboks, det er ret forvirrende for mig - vælg begivenheder, vælg billetter, klik på billet type i begge, skift leddet til en indre sammenføjning,klik på OK, og så får du det forfærdelige skuffende resultat, hvor det bare er en kolonne, der siger Tabel, Tabel, Tabel, Tabel; klik på ikonet Udvid øverst på det; vælg feltet Billettsekvens; ikke præfikset med navnet på tabellen; og du kan sortere efter dato, sortere efter rækkefølge; Luk & indlæs i regnearket. Den smukke ting er, at hvis de underliggende data ændres - bare opdater, og du har dine resultater.
Nu, hej, for at downloade den brugte projektmappe fra dagens video skal du besøge URL'en dernede i YouTube-beskrivelsen. Også en liste der over de kommende seminarer - Jeg vil meget gerne se dig på et af mine live Power Excel-seminarer.
Jeg vil takke Kaley for at dukke op i Nashville og give mig det store spørgsmål. Jeg vil gerne have dig for at komme forbi. Vi ses næste gang til endnu en netcast fra.
Download Excel-fil
For at downloade excel-filen: return-all-vlookups.xlsx
Power Query fortsætter med at forbløffe mig. Dette er den anden af en tre-dages serie, hvor svaret er Power Query:
- Tirsdag: Konverter en kolonne med dato / tid til bare dato
- I dag: Returner alle VLOOKUPs
- Torsdag: Opret en undersøgelse for hver af 1100 genstande
Jeg har en hel YouTube-afspilningsliste med ting, som jeg endte med at løse med 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:
"I tvivlstilfælde, brug RUND-funktion!"
Mike Girvin
