Kaley uit Nashville werkt aan een spreadsheet voor ticketverkoop. Voor elk evenement kiest ze een ticketplan. Dat kaartverkoopplan kan 4 tot 16 soorten tickets voor het evenement aangeven. Kaley wil een formule die naar de opzoektabel gaat en * alle * overeenkomsten retourneert, waarbij indien nodig nieuwe rijen worden ingevoegd.
Hoewel ik geen VERT.ZOEKEN heb die dit kan oplossen, kunnen de nieuwe Power Query-tools die in Excel 2016 zijn ingebouwd, dit oplossen.
Opmerking
Als u de Windows-versie van Excel 2010 of Excel 2013 heeft, kunt u Power Query gratis downloaden van Microsoft. Helaas is Power Query nog niet beschikbaar voor Excel voor Android, Excel voor ios of Excel voor Mac.
Om het doel te illustreren: Mike McCann and the Mechanics verschijnt in het Allen Theatre met ticketplan C.Aangezien er vier overeenkomende rijen in de opzoektabel zijn, wil Kaley vier rijen met Mike McCann and the Mechanics, elk met een andere match dan de opzoektabel.
Selecteer een cel in de originele tabel. Druk op Ctrl + T om die gegevens als een tabel te markeren. Wijzig de naam van de tabel op het tabblad Hulpmiddelen voor tabellen van Tabel1 in Voorstellingen. Herhaal dit voor de opzoektabel en noem het Tickets.
Selecteer een cel in de tabel met shows. Kies Uit tabel / bereik op het tabblad Gegevens.
Nadat de Power Query-editor is geopend, opent u de vervolgkeuzelijst Sluiten en laden en kiest u Sluiten en laden naar….
Kies Alleen een verbinding maken in het dialoogvenster Gegevens importeren.
Ga naar de kaartentafel. Herhaal de stappen om alleen een verbinding met tickets te maken. U zou beide verbindingen in het deelvenster Query's moeten zien:
Selecteer een lege cel. Kies Gegevens, Gegevens ophalen, Query's combineren, Samenvoegen.
Er zijn zes stappen in het dialoogvenster Samenvoegen. De 3e en 4e lijken mij niet intuïtief.
- Kies Shows in de vervolgkeuzelijst
- Kies Tickets in de tweede vervolgkeuzelijst.
- Klik op het kopje Ticketplan bovenaan om die kolom te selecteren als de externe sleutel in de tabel met shows.
- Klik op de kop Ticketplan onderaan om die kolom te selecteren als het sleutelveld in de opzoektabel.
- Open het Join-type en kies Inner (alleen overeenkomende rijen).
- Klik OK
De resultaten zijn aanvankelijk teleurstellend. U ziet alle velden uit tabel 1 en een kolom met de tekst Tabel, Tabel, Tabel.
Klik op het pictogram Uitvouwen bovenaan de kolom Tickets.
Deselecteer Ticketplan aangezien u dat veld al heeft. Het resterende veld heet Tickets.Tickettype, tenzij u Originele naam gebruiken als voorvoegsel uitschakelt.
Succes! Elke rij voor elke show explodeert in meerdere rijen.
Ik ben niet bijzonder blij met het sorteren van de gegevens. Sorteren op datum zorgt ervoor dat de soorten tickets op een vreemde manier worden gesorteerd.
Bekijk video
In het geval van vandaag is de video gemaakt nadat het artikel was geschreven. Ik stel voor om een reekskolom toe te voegen aan de tickettypen om de sorteervolgorde te regelen.
Videotranscriptie
Leer Excel van Podcast, aflevering 2204: Retourneer alle VERT.ZOEKEN.
Hé, welkom terug bij de netcast, ik ben Bill Jelen. De vraag van vandaag uit Nashville Music City. Ik was daarginds in Nashville, iemand is verantwoordelijk voor het plannen van het laden van tickets in een ticketingsysteem en dus hier is wat we hebben: we hebben een lijst met evenementen - aankomende evenementen - we hebben de datum, de locatie en een ticketplan. Dus ook al wordt er iets gehouden in het paleis, er kunnen verschillende kaartjesplannen zijn, zoals, misschien is de vloer geconfigureerd, weet je, met stoelen of misschien is het alleen een sta-kamer, toch?
Dus, afhankelijk van het soort ticketplan, moet je hier naar de opzoektafel komen en alle overeenkomende evenementen vinden, en in wezen gaan we doen wat ik een VLOOKUP-explosie noem. Dus als er iets bij Hannah C is, gaan ze naar Hannah C en als er - 1, 2, 3, 4, 5, 6 - 7 items in Hannah C zijn, hebben we om zeven rijen te retourneren, wat betekent dat je nog zes rijen moet invoegen en die gegevens naar beneden moet kopiëren. Oké.
Nu gaan we dit helemaal niet doen met VERT.ZOEKEN, maar u begrijpt het concept - we doen een VERT.ZOEKEN en we retourneren alle antwoorden als nieuwe rijen. Oké, dus ik ga van beide tabellen een echte tabel maken met Ctrl + T. De eerste noemden Tabel 1 - vreselijke naam, laten we dit Evenementen of Shows noemen, laten we het shows noemen, zo - en de tweede, nu, hey, hier is wat ik heb geleerd omdat ik dit heb geoefend - we moeten een sequentieveld hier. Dus = RIJ (A1), dubbelklik en kopieer dat naar beneden en kopieer en plak vervolgens speciale waarden. Oké. Nu maken we dat er een tafel van wordt - Ctrl + T, en dat noemen we Tickets.
Oké. Dus we hebben shows, we hebben kaartjes. Ik ga naar het tabblad Gegevens, en ik ben hier in het show-ding, ik wil zeggen dat ik mijn gegevens uit een tabel of bereik wil halen - dit is trouwens Power Query. Als u weer in Excel 2010 of 2013 bent, kunt u dit gratis downloaden van Microsoft, download de Power Query-tool. Als u een Mac of iOS of Android gebruikt, sorry, geen Power Query voor u. Oké, dus van een tafel of bereik … zoek iemand die een-- zoek een vriend met een-- Windows-pc en laat hem dit instellen. Oké. Hier is een tabel, we gaan hier niets aan doen, gewoon Sluiten en laden, Sluiten en laden naar, en dan zeggen "Alleen verbinding maken", perfect. We komen hier naar onze tweede tabel: gegevens ophalen, uit een tabel of bereik, we doen niets aan deze, sluiten en laden,Sluiten & laden naar, "Alleen verbinding maken", OK. Dus wat we nu hebben, is dat we een verbinding hebben met de eerste tafel en een verbinding met de tweede tafel. We gaan deze twee niet samenvoegen, wat in wezen hetzelfde is als VERT.ZOEKEN doen, of een Database Joint, denk ik, is echt hoe het is. Combineer zoekopdrachten, we gaan samenvoegen. Oké.
Nu, zeven dingen die u in dit dialoogvenster moet doen - en het is een beetje verwarrend - we gaan Show kiezen als de eerste tabel; kies Tickets als de tweede tafel; kies welk veld ze gemeen hebben, en dit kunnen meerdere velden zijn - je kunt control-klikken - maar in dit geval is er maar één ticketplan; en vervolgens Ticketplan; en dan gaan we het Join-type veranderen in een Inner join met "alleen de overeenkomende rijen". Oké. Nu, je klikt op OK en je denkt dat je hele probleem zal worden opgelost, maar je bent gewoon verpletterd, want hier zijn alle gegevens van A - ze hebben helemaal geen nieuwe rijen ingevoegd - en hier, gewoon een saai stom veld genaamd Tickets dat alleen Table, Table, Table, hah heeft.
Maar gelukkig staat bovenaan een pictogram Uitvouwen, en we gaan dat uitbreiden - ik hoef geen plan te maken, dat heb ik al - Ticket Type en Sequence. Ik wil niet dat het Tickets.TicketType heet, en dat is wat Power Query wil doen, dus ik verwijder het vinkje uit dit vakje. Oké. Op dit moment hebben we 17 rijen met gegevens; als ik op OK klik, BAM! Daar is de explosie. Dus Michael Seeley en de Starlighter's komen opdagen met alle verschillende soorten tickets, zoals deze. Oké, en zie hoe deze soorten tickets achter elkaar verschijnen, dat is geweldig. Maar Michael Seeley is niet de volgende show, de volgende show is op 5 juni. Dus als ik dit op datum probeer te sorteren - dit maakt me gek, ik kan dit niet uitleggen. Sorteer op datum, en Mike Man and the Mechanics komt tot 65, maar dan zijn de kaartjes allemaal verknald. Ze'zitten in de verkeerde volgorde, en daarom moest ik deze reeks doen - voelt zo. Ik kan sorteren op volgorde. Dus nu, 6, 5, mooi, en dan daarbinnen, zijn de tickets correct. En eigenlijk hebben we deze column op dit moment niet meer nodig. Dus ik kan met de rechtermuisknop klikken en verwijderen, en dan Close & Load - deze keer ga ik eigenlijk Close & Load, niet Close & Load to - en we hebben ons resultaat. Oké.
Dus we gingen van een lijst met evenementen naar deze hele grote lijst, maar hier is het geweldige deel: ik heb dit verpest, Mike Man en Mechanics is niet Palace B, het is Palace C. Dus ik kom terug naar het origineel rechtsboven -hand hoekje voor meer informatie over het boek.
Oké. Onderwerpen in deze aflevering: Kaley in Nashville moet een VERT.ZOEKEN uitvoeren om alle overeenkomsten te retourneren, waarbij meestal nieuwe rijen worden ingevoegd. En het is een ticketdatabase, oké? Dus ik ga dit een VLOOKUP-explosie noemen omdat elke show in maximaal 16 rijen explodeert. We gaan Power Query gebruiken om dit op te lossen, en ik heb vernomen dat de datum in de verkeerde volgorde zal verschijnen, tenzij we een veld Volgorde toevoegen aan het tickettype. Maak van beide sets een tabel met Ctrl + T; noem ze shows en tickets; en vervolgens vanuit elke tabel, Gegevens ophalen, Uit tabel, Sluiten en laden, om alleen een verbinding te maken; herhaal voor de andere tafel; vervolgens gegevens, gegevens ophalen, query's combineren, samenvoegen; en dan dat dialoogvenster, het is nogal verwarrend voor mij - kies Evenementen, kies Tickets, klik op Ticket Type in beide, verander de joint in een inner join,klik op OK, en dan krijg je dat vreselijk teleurstellende resultaat waar het gewoon een kolom is met de tekst Table, Table, Table, Table; klik op het pictogram Uitvouwen bovenaan; kies het veld Ticketreeks; geen voorvoegsel met de naam van de tabel; en u kunt sorteren op datum, sorteren op volgorde; Sluit en laad naar de spreadsheet. Het mooie is dat als de onderliggende gegevens veranderen, je gewoon vernieuwt en je hebt je resultaten.
Om de werkmap van de video van vandaag te downloaden, bezoek je de URL daar beneden in de YouTube-beschrijving. Ook een lijst daar van de komende seminars. Ik zou je graag zien op een van mijn live Power Excel-seminars.
Ik wil Kaley bedanken voor het verschijnen in Nashville en het stellen van die geweldige vraag. Ik wil dat je langskomt. Ik zie je de volgende keer voor nog een netcast van.
Download Excel-bestand
Om het Excel-bestand te downloaden: return-all-vlookups.xlsx
Power Query blijft me verbazen. Dit is de tweede van een driedaagse serie waarin het antwoord Power Query is:
- Dinsdag: converteer een kolom met datum / tijd naar alleen de datum
- Vandaag: alle VERT.ZOEKEN retourneren
- Donderdag: maak een enquête voor elk van de 1100 items
Ik heb een hele YouTube-afspeellijst met dingen die ik uiteindelijk heb opgelost met 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:
"Gebruik bij twijfel de functie ROUND!"
Mike Girvin
