Kaley fra Nashville jobber med et regneark for billetter. For hvert arrangement velger hun en billettplan. Den billettplanen kan indikere alt fra 4 til 16 billettyper for arrangementet. Kaley vil ha en formel som går til oppslagstabellen og returnerer * alle * treff, og setter inn nye rader etter behov.
Selv om jeg ikke har en VLOOKUP som kan løse dette, kan de nye Power Query-verktøyene som er innebygd i Excel 2016, løse det.
Merk
Hvis du har Windows-versjonen av Excel 2010 eller Excel 2013, kan du laste ned Power Query gratis fra Microsoft. Dessverre er Power Query ennå ikke tilgjengelig for Excel for Android, Excel for iOS eller Excel for Mac.
For å illustrere målet: Mike McCann and the Mechanics vises i Allen Theatre med billettplan C. Siden det er fire matchende rader i oppslagstabellen, vil Kaley ha fire rader som sier Mike McCann and the Mechanics, hver med en annen kamp fra oppslagstabellen.
Velg en celle i den opprinnelige tabellen. Trykk Ctrl + T for å merke dataene som en tabell. I Tabellverktøy-fanen, endre navn på tabellen fra Tabell 1 til Vis. Gjenta for oppslagstabellen, og kaller den Billetter.
Velg en celle i showtabellen. Fra tabellen Data velger du Fra tabell / område.
Etter at Power Query-redigereren åpnes, åpner du rullegardinmenyen Lukk og last og velg Lukk og last inn….
Velg Bare opprett en tilkobling i dialogboksen Importer data.
Gå til billettbordet. Gjenta trinnene til Bare opprett en forbindelse til billetter. Du bør se begge tilkoblingene i spørringsruten:
Velg hvilken som helst tom celle. Velg Data, Få data, Kombiner spørringer, Slå sammen.
Det er seks trinn i flettedialogen. Den tredje og fjerde virker ikke intuitiv for meg.
- Velg viser fra rullegardinmenyen øverst
- Velg Billetter fra den andre rullegardinmenyen.
- Klikk på overskriften for Ticket Plan øverst for å velge den kolonnen som fremmednøkkel i Show-tabellen.
- Klikk på overskriften for Billettplan nederst for å velge den kolonnen som nøkkelfelt i oppslagstabellen.
- Åpne Join type og velg Inner (bare matchende rader).
- Klikk OK
Resultatene er opprinnelig skuffende. Du ser alle feltene fra tabell 1 og en kolonne som sier Tabell, Tabell, Tabell.
Klikk på Utvid-ikonet øverst i billettkolonnen.
Opphev valget av billettplan siden du allerede har det feltet. Det gjenværende feltet vil bli kalt Tickets.Billettype med mindre du fjerner merket for Bruk originalnavn som prefiks.
Suksess! Hver rad for hvert show eksploderer i flere rader.
Jeg er ikke spesielt fornøyd med sorteringen av dataene. Sortering etter dato fører til at billettypene sorteres på en merkelig måte.
Se på video
I dagens tilfelle ble videoen tatt opp etter at artikkelen ble skrevet. Jeg foreslår at du legger til en sekvenskolonne i billettypene for å kontrollere sorteringsrekkefølgen.
Videoutskrift
Lær Excel fra Podcast, Episode 2204: Return All VLOOKUPs.
Hei, velkommen tilbake til netcast, jeg er Bill Jelen. Dagens spørsmål fra Nashville Music City. Jeg var der nede i Nashville, noen er ansvarlige for å planlegge innlasting av billetter til et billettsystem, og så er det her vi har: Vi har en liste over begivenheter - kommende arrangementer - vi har datoen, lokalet og en billettplan. Så som om, selv om noe holdes på palasset, kan det være forskjellige billettplaner - som om gulvet er konfigurert, vet du, med seter, eller kanskje det bare er et stuerom, ikke sant?
Så avhengig av hvilken type billettplan, må du komme hit til Lookup-tabellen og finne alle de matchende hendelsene, og egentlig skal vi gjøre det jeg kaller en VLOOKUP-eksplosjon. Så hvis noe er ved Hannah C, kommer de til å gå ned til Hannah C, og hvis det er - 1, 2, 3, 4, 5, 6--7 ting i Hannah C, vil vi ha for å returnere syv rader - noe som betyr at du må sette inn seks rader til og kopiere dataene ned. Ok.
Nå skal vi ikke gjøre dette med en VLOOKUP i det hele tatt, men du får konseptet - vi gjør en VLOOKUP, og vi returnerer alle svarene som nye rader. Ok, så jeg skal ta begge disse bordene og gjøre dem til et ekte bord med Ctrl + T. De første som heter Tabell 1 - fryktelig navn, la oss kalle dette hendelser eller viser, la oss kalle det viser, slik - og den andre, nå, hei, her er det jeg lærte fordi jeg praktiserte dette - vi må ha et sekvensfelt her. Så = RAD (A1), dobbeltklikk og kopier den ned og kopier og lim inn spesielle verdier. Ok. Nå lager vi det som gjør det til et bord - Ctrl + T, og vi vil kalle den ene Billetter.
Ok. Så vi har forestillinger, vi har billetter. Jeg kommer til å gå til Data-fanen, og jeg er her i showtingen, jeg vil si at jeg vil hente dataene mine fra en tabell eller et område - dette er forresten Power Query. Hvis du er tilbake i Excel 2010 eller 2013, kan du laste ned dette gratis fra Microsoft, last ned Power Query-verktøyet. Hvis du bruker Mac eller iOS eller Android, beklager, ingen Power Query for deg. OK, så fra en tabell eller et utvalg … finn noen som har en - finn en venn som har en - Windows PC og få dem til å sette opp dette. Ok. Her er et bord, vi skal ikke gjøre noe med dette, bare lukk & last, lukk & last til, og så si "Bare opprett forbindelse", perfekt. Vi kommer hit til vårt andre bord: Få data, fra en tabell eller et område, vi gjør ikke noe med denne, Lukk og last,Lukk og last til, "Bare opprett forbindelse", OK. Så det vi har nå, er at vi har en forbindelse til den første tabellen og en forbindelse til den andre tabellen. Vi kommer ikke til å slå sammen disse to, som egentlig er som å gjøre VLOOKUP, eller en Database Joint, antar jeg, er egentlig hvordan det er. Kombiner spørringer, vi skal slå sammen. Ok.
Nå, syv ting du må gjøre i denne dialogboksen - og det er litt forvirrende - vi skal velge viser som første tabell; velg Billetter som andre bord; velg hvilket felt de har til felles, og dette kan være flere felt - du kan kontrollere og klikke - men i dette tilfellet er det bare en billettplan; og deretter billettplan; og så skal vi endre koblingstypen til en indre kobling med "bare de samsvarende radene". Ok. Nå klikker du OK, og du tror at hele problemet ditt blir løst, men du blir bare knust fordi her er alle dataene fra A - de har ikke satt inn noen nye rader i det hele tatt - og her borte, bare et kjedelig dumt felt som heter Tickets som bare har Table, Table, Table, hah.
Men heldigvis, på toppen av det, er det et utvidelsesikon, og vi skal utvide det - jeg trenger ikke ta en plan, det har jeg allerede - billettype og sekvens. Jeg vil ikke at det skal hete Tickets.TicketType, det er hva Power Query vil gjøre - så jeg fjerner merket for denne boksen. Ok. Akkurat nå har vi 17 rader med data; når jeg klikker OK, BAM! Det er eksplosjonen. Så, Michael Seeley and the Starlighter's dukker opp med alle de forskjellige billettypene, som dette. Ok, og se disse billettypene vises i rekkefølge, det er flott. Men Michael Seeley er ikke neste show, neste show er 5. juni. Så når jeg prøver å sortere dette etter dato - dette gjør meg sinnssyk, kan jeg ikke forklare dette. Sorter etter dato, og Mike Man and the Mechanics kommer opp til 65, men så er alle billettene skrudd opp. De'er på feil sekvens, og det var derfor jeg måtte gjøre denne sekvensen - føles slik. Jeg kan sortere etter sekvens. Så nå, 6, 5, vakker, og så innenfor det, er billettene riktige. Og faktisk, på dette punktet, trenger vi ikke denne kolonnen lenger. Så jeg kan høyreklikke og fjerne, og deretter Lukk og last - denne gangen skal jeg faktisk Lukke og laste, ikke Lukke og laste til-- og vi har resultatet vårt. Ok.
Så vi gikk fra en liste over hendelser til hele denne store listen, men her er den fantastiske delen: Jeg skrudde opp dette, Mike Man og Mechanics er ikke Palace B, dets Palace C. Så jeg kommer tilbake til originalen øverst til høyre -håndkrok for mer informasjon om boka.
Ok. Emner i denne episoden: Kaley i Nashville må gjøre en VLOOKUP for å returnere alle kampene, og vanligvis setter inn nye rader. Og det er en billettdatabase, ok? Så jeg skal kalle dette en VLOOKUP-eksplosjon fordi hvert show vil eksplodere i opptil 16 rader. Vi skal bruke Power Query for å løse dette, og jeg har lært at datoen kommer til å vises på feil sekvens med mindre vi legger til et sekvensfelt til billettypen. Lag begge settene til en tabell med Ctrl + T; ename dem til å være show og billetter; og deretter fra hver tabell, Få data, Fra tabell, Lukk og last, til Bare opprett en forbindelse; gjenta for det andre bordet; deretter Data, Få data, Kombiner spørringer, Slå sammen; og deretter den dialogboksen, det er ganske forvirrende for meg - velg Arrangementer, velg Billetter, klikk på Billettype i begge, endre leddet til en indre sammenføyning,klikk OK, og så får du det fryktelig skuffende resultatet der det bare er en kolonne som sier Tabell, Tabell, Tabell, Tabell; klikk på Utvid-ikonet øverst på det; velg billettsekvensfelt; ikke prefiks med navnet på tabellen; og du kan sortere etter dato, sortere etter sekvens; Lukk og last inn regnearket. Det vakre er at hvis de underliggende dataene endres - bare oppdater og du har resultatene dine.
Nå, hei, for å laste ned arbeidsboken som brukes fra dagens video, besøk URL-en der nede i YouTube-beskrivelsen. Også en liste der over de kommende seminarene - Jeg vil gjerne se deg på et av mine live Power Excel-seminarer.
Jeg vil takke Kaley for at hun dukket opp i Nashville og ga meg det store spørsmålet. Jeg vil at du kom innom. Vi sees neste gang for en ny netcast fra.
Last ned Excel-fil
For å laste ned Excel-filen: return-all-vlookups.xlsx
Power Query fortsetter å forbløffe meg. Dette er den andre av en tredagers serie der svaret er Power Query:
- Tirsdag: Konverter en kolonne med dato / tid til bare dato
- I dag: Returner alle VLOOKUP
- Torsdag: Lag en undersøkelse for hver av 1100 artikler
Jeg har en hel YouTube-spilleliste med ting som jeg endte med å løse med Power Query.
Excel-tanken om dagen
Jeg har bedt mine Excel Master-venner om deres råd om Excel. Dagens tanke å tenke på:
"Når du er i tvil, bruk RUND funksjon!"
Mike Girvin
