Kaley din Nashville lucrează la o foaie de calcul pentru bilete. Pentru fiecare eveniment, ea alege un plan de biletare. Acest plan de biletare ar putea indica de la 4 la 16 tipuri de bilete pentru eveniment. Kaley dorește o formulă care să meargă în tabelul de căutare și să returneze * toate * potrivirile, inserând rânduri noi după caz.
Deși nu am o VLOOKUP care să rezolve acest lucru, noile instrumente Power Query încorporate în Excel 2016 o pot rezolva.
Notă
Dacă aveți versiunea Windows pentru Excel 2010 sau Excel 2013, puteți descărca gratuit Power Query de la Microsoft. Din păcate, Power Query nu este încă disponibil pentru Excel pentru Android, Excel pentru iOS sau Excel pentru Mac.
Pentru a ilustra obiectivul: Mike McCann and the Mechanics apare în Teatrul Allen cu planul de bilete C. Deoarece există patru rânduri potrivite în tabelul de căutare, Kaley dorește patru rânduri care să spună Mike McCann și Mechanics, fiecare cu o potrivire diferită de tabelul de căutare.
Selectați o celulă din tabelul original. Apăsați Ctrl + T pentru a marca acele date ca un tabel. În fila Instrumente de tabel, redenumiți tabelul de la Tabelul 1 la Spectacole. Repetați pentru tabelul de căutare, numindu-l Bilete.
Selectați o celulă din tabelul Spectacole. Din fila Date, alegeți Din tabel / interval.
După ce se deschide editorul Power Query, deschideți meniul derulant Închidere și încărcare și alegeți Închidere și încărcare în …
În caseta de dialog Import date, alegeți Creați doar o conexiune.
Accesați tabelul Bilete. Repetați pașii pentru a crea doar o conexiune la bilete. Ar trebui să vedeți ambele conexiuni în panoul Interogări:
Selectați orice celulă necompletată. Alegeți date, obțineți date, combinați interogări, îmbinați.
Există șase pași în dialogul Merge. A treia și a patra nu mi se par intuitive.
- Alegeți Spectacole din meniul derulant de sus
- Alegeți Bilete din al doilea meniu derulant.
- Faceți clic pe titlul pentru Planul de bilete din partea de sus pentru a selecta acea coloană ca cheie externă în tabelul Spectacole.
- Faceți clic pe titlul pentru Planul de bilete din partea de jos pentru a selecta acea coloană ca câmp cheie în tabelul de căutare.
- Deschideți tipul de alăturare și alegeți interior (numai rânduri potrivite).
- Faceți clic pe OK
Rezultatele sunt inițial dezamăgitoare. Vedeți toate câmpurile din tabelul 1 și o coloană care spune Tabel, Tabel, Tabel.
Faceți clic pe pictograma Extindeți din partea de sus a coloanei Bilete.
Deselectați planul de bilete, deoarece aveți deja acel câmp. Câmpul rămas se va numi Tickets.Ticket Type, dacă nu debifați Use Original Name as Prefix.
Succes! Fiecare rând pentru fiecare spectacol explodează în mai multe rânduri.
Nu sunt deosebit de mulțumit de sortarea datelor. Sortarea după dată determină sortarea tipurilor de bilete într-un mod ciudat.
Urmăriți videoclipul
În cazul de astăzi, videoclipul a fost filmat după ce a fost scris articolul. Vă sugerez să adăugați o coloană de secvență la tipurile de bilete pentru a controla ordinea de sortare.
Transcriere video
Aflați Excel din Podcast, Episodul 2204: Returnează toate VLOOKUP-urile.
Hei, bine ai venit din nou pe netcast, eu sunt Bill Jelen. Întrebarea de astăzi de la Nashville Music City. Am fost acolo în Nashville, cineva este responsabil pentru programarea încărcării biletelor într-un sistem de biletare și iată ce avem: Avem o listă de evenimente - evenimente viitoare - avem data, locul de desfășurare și un plan de bilete. Deci, deși, deși se ține ceva la Palat, ar putea exista diferite planuri de bilete - cum ar fi, poate, podeaua este configurată, știi, cu locuri sau poate este doar o cameră în picioare, nu?
Deci, în funcție de ce tip de plan de bilete, trebuie să veniți aici la tabelul Căutare și să găsiți toate evenimentele potrivite și, în esență, vom face ceea ce eu numesc o explozie VLOOKUP. Deci, dacă ceva este la Hannah C, vor coborî la Hannah C și dacă există - 1, 2, 3, 4, 5, 6-7 articole în Hannah C, vom avea pentru a returna șapte rânduri - ceea ce înseamnă că va trebui să introduceți încă șase rânduri și să copiați aceste date. Bine.
Acum, nu vom face deloc acest lucru cu un VLOOKUP, dar veți obține conceptul - facem un VLOOKUP și returnăm toate răspunsurile ca rânduri noi. Bine, deci, voi lua ambele tabele și le voi transforma într-o masă reală cu Ctrl + T. Primele numite Tabelul 1 - nume oribil, să numim acest eveniment sau spectacole, să-i spunem spectacole, așa - și al doilea, acum, hei, iată ce am învățat pentru că am practicat asta - trebuie să avem un câmp de secvență aici. Deci = ROW (A1), faceți dublu clic și copiați-l și apoi copiați și lipiți valori speciale. Bine. Acum facem ca acest lucru să transforme acest lucru într-un tabel - Ctrl + T și îl vom numi Bilete.
Bine. Deci avem spectacole, avem bilete. Mă duc la fila Date și sunt aici în spectacole, vreau să spun că vreau să obțin datele mele dintr-un tabel sau un interval - apropo este Power Query. Dacă sunteți din nou în Excel 2010 sau 2013, puteți descărca acest lucru gratuit de la Microsoft, descărcați instrumentul Power Query. Dacă sunteți pe un Mac sau iOS sau Android, îmi pare rău, nu aveți nicio interogare Power pentru dvs. În regulă, deci dintr-o masă sau o gamă … găsește pe cineva care are un … găsește un prieten care are un … PC Windows și pune-l în funcțiune. Bine. Iată un tabel, nu vom face nimic în acest sens, doar Închideți și încărcați, Închideți și încărcați și apoi spuneți „Creați doar conexiune”, perfect. Vom veni aici la al doilea tabel: Obțineți date, dintr-un tabel sau un interval, nu îi facem nimic, Închideți și încărcațiÎnchideți și încărcați în „Creați doar conexiune”, OK. Deci, ceea ce avem acum este că avem o conexiune la primul tabel și o conexiune la al doilea tabel. Nu vom combina aceste două, ceea ce, în esență, este ca și cum ați face VLOOKUP, sau un Joint Database, cred, este chiar așa cum este. Combinați interogări, vom merge la Îmbinare. Bine.
Acum, șapte lucruri pe care trebuie să le faceți în această casetă de dialog - și este puțin confuz - vom alege Spectacole ca primul tabel; alege Bilete ca al doilea tabel; alegeți ce câmp au în comun, iar acesta poate fi câmpuri multiple - puteți controla-faceți clic - dar în acest caz există un singur plan de bilete; și apoi Plan de bilete; și apoi vom schimba tipul de Înscriere într-o îmbinare interioară cu „numai rândurile potrivite”. Bine. Acum, faceți clic pe OK și credeți că întreaga dvs. problemă va fi rezolvată, dar sunteți doar zdrobiți deoarece iată toate datele de la A - nu au inserat deloc rânduri noi - și aici, doar un câmp prost plictisitor numit Tickets care are doar Table, Table, Table, hah.
Dar, din fericire, în partea de sus este o pictogramă Expand și vom extinde asta - Nu trebuie să iau un plan, am deja acel … Tipul și secvența biletului. Nu vreau să se numească Tickets.TicketType, ceea ce dorește să facă Power Query - așa că debifez această casetă. Bine. În acest moment avem 17 rânduri de date; când dau clic pe OK, BAM! Este explozia. Așadar, Michael Seeley și Starlighter’s apar cu toate tipurile diferite de bilete, ca acesta. Bine, și vedeți aceste tipuri de bilete apar în ordine, este minunat. Dar Michael Seeley nu este următorul spectacol, următorul spectacol este pe 5 iunie. Așadar, când încerc să sortez acest lucru după dată - asta mă înnebunește, nu pot să explic acest lucru. Sortați după dată, iar Mike Man and the Mechanics ajunge la 65, dar apoi biletele sunt înșelate. Ei'Am o secvență greșită, și de aceea a trebuit să fac această secvență - se simte așa. Pot să sortez după secvență. Deci, acum, 6, 5, frumos, și apoi în acest sens, Biletele sunt corecte. Și, de fapt, în acest moment, nu mai avem nevoie de această coloană. Așadar, pot face clic dreapta și elimina, apoi Închideți și încărcați - de data aceasta voi închide și încărca de fapt, nu închideți și încărcați în - și avem rezultatul nostru. Bine.
Așa că am trecut de la o listă de evenimente la această listă mare, dar iată partea minunată: am înșelat acest lucru, Mike Man și Mecanici nu sunt Palatul B, Palatul lui C. Deci, mă întorc la originalul din dreapta sus - colț de mână pentru mai multe informații despre carte.
Bine. Subiecte din acest episod: Kaley din Nashville trebuie să facă un VLOOKUP pentru a returna toate meciurile, inserând de obicei rânduri noi. Și este o bază de date de ticketing, bine? Deci, voi numi asta o Explozie VLOOKUP, deoarece fiecare spectacol va exploda până la 16 rânduri. Vom folosi Power Query pentru a rezolva acest lucru și am aflat că Data va apărea pe secvența greșită, cu excepția cazului în care adăugăm un câmp Secvență la tipul de bilet. Faceți ambele seturi într-un tabel cu Ctrl + T; denumiți-le să fie spectacole și bilete; și apoi din fiecare tabel, Obțineți date, Din tabel, Închidere și încărcare, pentru a crea doar o conexiune; repetați pentru celălalt tabel; apoi Date, Obțineți date, Combinați interogări, Merge; și apoi acea casetă de dialog, este destul de confuză pentru mine - alege Evenimente, alege Bilete, dă clic pe Tipul de bilet în ambele, schimbă îmbinarea într-o îmbinare interioarăfaceți clic pe OK și apoi veți obține acel rezultat oribil de dezamăgitor în care este doar o coloană care spune Tabel, Tabel, Tabel, Tabel; faceți clic pe pictograma Extindeți din partea de sus; alege câmpul Secvență de bilete; nu prefixați cu numele tabelului; și puteți sorta după dată, sortați după secvență; Închideți și încărcați pe foaia de calcul. Lucrul frumos este că dacă datele subiacente se schimbă - doar Reîmprospătați și aveți rezultatele.
Acum, hei, pentru a descărca registrul de lucru folosit din videoclipul de astăzi, accesați adresa URL de jos în descrierea YouTube. De asemenea, o listă cu seminariile viitoare - Mi-ar plăcea să vă văd la unul dintre seminariile mele live Power Excel.
Vreau să îi mulțumesc lui Kaley pentru că a apărut la Nashville și mi-a dat această întrebare minunată. Vreau să te oprești. Ne vedem data viitoare pentru un alt netcast de la.
Descărcați fișierul Excel
Pentru a descărca fișierul Excel: return-all-vlookups.xlsx
Power Query continuă să mă uimească. Acesta este al doilea dintr-o serie de trei zile în care răspunsul este Power Query:
- Marți: convertiți o coloană de dată / oră în dată
- Astăzi: Întoarceți toate VLOOKUP-urile
- Joi: creați un sondaj pentru fiecare dintre cele 1100 de articole
Am o întreagă listă de redare YouTube cu lucruri pe care am ajuns să le rezolv cu Power Query.
Gândul Excel al zilei
Le-am cerut prietenilor mei Excel Master sfatul lor despre Excel. Gândul de astăzi să medităm:
"Când aveți îndoieli, utilizați funcția ROUND!"
Mike Girvin
