Kaley från Nashville arbetar med ett kalkylark för biljetter. För varje evenemang väljer hon en biljettplan. Den biljettplanen kan ange var som helst från 4 till 16 biljetttyper för evenemanget. Kaley vill ha en formel som går till uppslagstabellen och returnerar * alla * matchningar och infogar nya rader efter behov.
Även om jag inte har en VLOOKUP som kan lösa detta kan de nya Power Query-verktygen inbyggda i Excel 2016 lösa det.
Notera
Om du har Windows-versionen av Excel 2010 eller Excel 2013 kan du ladda ner Power Query gratis från Microsoft. Tyvärr är Power Query ännu inte tillgängligt för Excel för Android, Excel för iOS eller Excel för Mac.
För att illustrera målet: Mike McCann and the Mechanics dyker upp i Allen Theatre med biljettplan C. Eftersom det finns fyra matchande rader i uppslagstabellen vill Kaley ha fyra rader som säger Mike McCann and the Mechanics, var och en med en annan matchning från uppslagstabellen.
Välj en cell i originaltabellen. Tryck på Ctrl + T för att markera data som en tabell. På fliken Tabellverktyg byter du namn på tabellen från Tabell1 till Visningar. Upprepa för uppslagstabellen, kallar det biljetter.
Välj en cell i tabellen Show. På fliken Data väljer du Från tabell / intervall.
När Power Query-redigeraren öppnas öppnar du rullgardinsmenyn Stäng och laddar och väljer Stäng och Ladda till ….
Välj Endast skapa en anslutning i dialogrutan Importera data.
Gå till biljettbordet. Upprepa stegen för att bara skapa en anslutning till biljetter. Du bör se båda anslutningarna i frågerutan:
Välj en tom cell. Välj Data, Hämta data, kombinera frågor, slå samman.
Det finns sex steg i dialogrutan Sammanfoga. Den 3: e och 4: e verkar inte vara intuitiv för mig.
- Välj Show från det övre rullgardinsmenyn
- Välj biljetter från den andra rullgardinsmenyn.
- Klicka på rubriken för Ticket Plan överst för att välja den kolumnen som främmande nyckel i tabellen Show.
- Klicka på rubriken för biljettplan längst ned för att välja den kolumnen som nyckelfält i uppslagstabellen.
- Öppna typen av anslutning och välj Inre (endast matchande rader).
- Klicka på OK
Resultaten är initialt nedslående. Du ser alla fälten från tabell 1 och en kolumn som säger Tabell, Tabell, Tabell.
Klicka på Expand-ikonen högst upp i kolumnen Biljetter.
Avmarkera biljettplan eftersom du redan har det fältet. Det återstående fältet kommer att kallas Tickets.Ticket Type såvida du inte avmarkerar Use Original Name as Prefix.
Framgång! Varje rad för varje show exploderar i flera rader.
Jag är inte särskilt nöjd med sorteringen av uppgifterna. Om du sorterar efter datum sorteras biljetttyperna på ett udda sätt.
Kolla på video
I dagens fall spelades upp videon efter att artikeln skrevs. Jag föreslår att du lägger till en sekvenskolumn i biljetttyperna för att styra sorteringsordningen.
Videoutskrift
Lär dig Excel från Podcast, avsnitt 2204: Returnera alla VLOOKUPs.
Hej, välkommen tillbaka till netcast, jag heter Bill Jelen. Dagens fråga från Nashville Music City. Jag var där nere i Nashville, någon är ansvarig för att schemalägga lastning av biljetter till ett biljettsystem och så här är vad vi har: Vi har en lista med evenemang - kommande evenemang - vi har datum, plats och en biljettplan. Så, som om, trots att något hålls på slottet, kan det finnas olika biljettplaner - som, kanske golvet är konfigurerat, du vet, med platser eller kanske det bara är ett stående rum, eller hur?
Så, beroende på vilken typ av biljettplan, måste du komma hit till Lookup-tabellen och hitta alla matchande händelser, och i huvudsak ska vi göra det jag kallar en VLOOKUP-explosion. Så om något är på Hannah C, kommer de att gå ner till Hannah C och om det finns - 1, 2, 3, 4, 5, 6-- 7 artiklar i Hannah C, kommer vi att ha för att returnera sju rader - vilket innebär att du måste infoga ytterligare sex rader och kopiera ned data. OK.
Nu ska vi inte göra detta med en VLOOKUP alls, men du förstår konceptet - vi gör en VLOOKUP och vi returnerar alla svaren som nya rader. Okej, så jag ska ta båda dessa tabeller och göra dem till ett riktigt bord med Ctrl + T. De första som heter Tabell 1 - hemskt namn, låt oss kalla detta händelser eller shower, låt oss kalla det visar, så här - och den andra, nu, hej, här är vad jag lärde mig för att jag tränade det här - vi måste ha ett sekvensfält här. Så = RAD (A1), dubbelklicka och kopiera den nedåt och kopiera och klistra in specialvärden. OK. Nu gör vi det som kommer att göra det till en tabell - Ctrl + T, och vi kommer att kalla den för biljetter.
OK. Så vi har shower, vi har biljetter. Jag kommer att gå till fliken Data, och jag är här i showen, jag vill säga att jag vill hämta mina data från en tabell eller ett intervall - det här är förresten Power Query. Om du är tillbaka i Excel 2010 eller 2013 kan du ladda ner detta gratis från Microsoft, ladda ner Power Query-verktyget. Om du använder en Mac eller iOS eller Android, tyvärr, ingen Power Query åt dig. Okej, så från en tabell eller ett intervall … hitta någon som har en - hitta en vän som har en - Windows PC och låt dem ställa in det här. OK. Här är en tabell, vi kommer inte att göra något åt detta, bara Stäng & Ladda, Stäng & Ladda till, och säg sedan "Skapa bara anslutning", perfekt. Vi kommer hit till vår andra tabell: Få data, från en tabell eller ett intervall, vi gör inte något åt den här, Stäng & ladda,Stäng & ladda till, "Skapa bara anslutning", OK. Så vad vi har nu är att vi har en anslutning till den första tabellen och en anslutning till den andra tabellen. Vi kommer inte att slå samman dessa två, vilket i huvudsak är som att göra VLOOKUP, eller en databas Joint, antar jag, är verkligen hur det är. Kombinera frågor, vi ska slå samman. OK.
Nu, sju saker du måste göra i den här dialogrutan - och det är lite förvirrande - vi ska välja Visar som första tabell; välj biljetter som andra bord; välj vilket fält de har gemensamt, och det kan vara flera fält - du kan klicka och klicka - men i det här fallet finns det bara en biljettplan; och sedan biljettplan; och sedan ändrar vi typen av koppling till en inre koppling med "endast matchande rader". OK. Nu klickar du på OK och du tror att hela problemet kommer att lösas, men du är bara krossad för att här är alla data från A - de har inte lagt in några nya rader alls - och här borta, bara ett tråkigt dumt fält som heter Tickets som bara har Table, Table, Table, hah.
Men tack och lov, högst upp på det finns en Expand-ikon, och vi kommer att utöka det - Jag behöver inte ta en plan, det har jag redan - Ticket Type and Sequence. Jag vill inte att det ska kallas Tickets.TicketType, vilket är vad Power Query vill göra - så jag avmarkerar den här rutan. OK. Just nu har vi 17 rader med data; när jag klickar på OK, BAM! Det är explosionen. Så, Michael Seeley och Starlighter's dyker upp med alla de olika biljetttyperna, så här. Okej, och se dessa biljettyper visas i följd, det är jättebra. Men Michael Seeley är inte nästa show, nästa show är den 5 juni. Så när jag försöker sortera det här efter datum - det gör mig galen, jag kan inte förklara detta. Sortera efter datum, och Mike Man and the Mechanics når upp till 65, men sedan är alla biljetterna skruvade upp. De'är på fel sekvens, och det var därför jag var tvungen att göra den här sekvensen - känns så. Jag kan sortera efter sekvens. Så nu, 6, 5, vackra, och sedan inom det är biljetterna korrekta. Och faktiskt, vid denna tidpunkt behöver vi inte den här kolumnen längre. Så jag kan högerklicka och ta bort och sedan Stäng & Ladda - den här gången kommer jag faktiskt att stänga & ladda, inte stänga & ladda till-- och vi har vårt resultat. OK.
Så vi gick från en lista över händelser till hela den här stora listan, men här är den fantastiska delen: Jag klumpade ihop det här, Mike Man och Mechanics är inte Palace B, dess Palace C. Så jag kommer tillbaka till originalet uppe till höger -hörn för mer information om boken.
OK. Ämnen i detta avsnitt: Kaley i Nashville måste göra en VLOOKUP för att returnera alla matcher, vanligtvis infoga nya rader. Och det är en biljettdatabas, okej? Så jag kommer att kalla detta en VLOOKUP Explosion eftersom varje show kommer att explodera i upp till 16 rader. Vi kommer att använda Power Query för att lösa detta, och jag har lärt mig att datumet kommer att visas på fel sekvens såvida vi inte lägger till ett sekvensfält till biljettypen. Gör båda uppsättningarna till en tabell med Ctrl + T; ename dem att vara shower och biljetter; och sedan från varje tabell, hämta data, från tabell, stäng och ladda, för att bara skapa en anslutning; upprepa för det andra bordet; sedan Data, Hämta data, kombinera frågor, slå samman; och sedan den dialogrutan, det är ganska förvirrande för mig - välj Händelser, välj Biljetter, klicka på Biljettyp i båda, ändra leden till en inre koppling,klicka på OK och då får du det hemskt nedslående resultatet där det bara är en kolumn som säger Tabell, Tabell, Tabell, Tabell; klicka på Expand-ikonen längst upp på den; välj fältet Ticket Sequence; prefix inte med namnet på tabellen; och du kan sortera efter datum, sortera efter sekvens; Stäng & ladda till kalkylbladet. Det vackra är att om de underliggande uppgifterna ändras - uppdatera bara så får du dina resultat.
Nu, hej, för att ladda ner arbetsboken som används från dagens video, besök webbadressen där nere i YouTube-beskrivningen. Också en lista där över de kommande seminarierna - Jag skulle gärna se dig på ett av mina live Power Excel-seminarier.
Jag vill tacka Kaley för att hon dykt upp i Nashville och gav mig den här stora frågan. Jag vill att du stannar 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: return-all-vlookups.xlsx
Power Query fortsätter att förvåna mig. Detta är den andra av en tre dagar lång serie där svaret är Power Query:
- Tisdag: Konvertera en kolumn med datum / tid till bara datum
- Idag: Returnera alla VLOOKUPs
- Torsdag: Skapa en undersökning för var och en av 1100 artiklar
Jag har en hel YouTube-spellista med saker som jag slutade lösa med 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å:
"När du är osäker, använd RUND-funktionen!"
Mike Girvin
