Kaley iz Nashvillea dela na preglednici vozovnic. Za vsak dogodek izbere načrt vstopnic. Ta načrt vstopnic lahko vsebuje od 4 do 16 vrst vstopnic za dogodek. Kaley želi formulo, ki bo šla v iskalno tabelo in vrnila * vse * ujemanja ter po potrebi vstavila nove vrstice.
Čeprav nimam VLOOKUP-a, ki bi to lahko rešil, ga lahko rešijo nova orodja Power Query, vgrajena v Excel 2016.
Opomba
Če imate Windows različico Excel 2010 ali Excel 2013, lahko Power Query brezplačno prenesete iz Microsofta. Na žalost Power Query še ni na voljo za Excel za Android, Excel za iOS ali Excel za Mac.
Za ponazoritev cilja: Mike McCann in mehanika se pojavlja v gledališču Allen z načrtom vstopnic C. Ker so v iskalni tabeli štiri ujemajoče se vrstice, Kaley želi štiri vrstice, na katerih piše Mike McCann in mehanika, vsaka z drugačnim ujemanjem. iskalne tabele.
V izvirni tabeli izberite celico. Pritisnite Ctrl + T, če želite te podatke označiti kot tabelo. Na zavihku Orodja za tabele preimenujte tabelo iz Tabela1 v Razstave. Ponovite za iskalno tabelo in jo pokličite vstopnice.
V tabeli Oddaje izberite celico. Na zavihku Podatki izberite Iz tabele / obsega.
Ko se odpre urejevalnik Power Query, odprite spustni meni Zapri in naloži in izberite Zapri in naloži v….
V pogovornem oknu Uvozi podatke izberite Samo ustvari povezavo.
Pojdite do tabele Vstopnice. Ponovite korake do Ustvari samo povezavo do vstopnic. V podoknu Poizvedbe bi morali videti obe povezavi:
Izberite poljubno prazno celico. Izberite Podatki, Pridobi podatke, Združi poizvedbe, Združi.
V pogovornem oknu Merge je šest korakov. 3. in 4. se mi ne zdita intuitivna.
- V zgornjem spustnem meniju izberite Oddaje
- V drugem spustnem meniju izberite Vstopnice.
- Kliknite naslov Načrt vozovnic na vrhu, da izberete stolpec kot tuji ključ v tabeli Oddaje.
- Na dnu kliknite naslov Načrt vozovnic, da izberete stolpec kot ključno polje v iskalni tabeli.
- Odprite vrsto Združevanja in izberite Notranja (samo ujemajoče se vrstice).
- Kliknite V redu
Rezultati so sprva razočarani. Vidite vsa polja iz tabele 1 in stolpec z napisom Tabela, Tabela, Tabela.
Kliknite ikono Razširi na vrhu stolpca Vstopnice.
Prekličite izbiro načrta vozovnice, ker to polje že imate. Preostalo polje se bo imenovalo Tickets.Ticket Type, razen če počistite polje Uporabi izvirno ime kot predpono.
Uspeh! Vsaka vrstica za vsako oddajo eksplodira v več vrstic.
Z razvrščanjem podatkov nisem posebej zadovoljen. Razvrščanje po datumu povzroči, da se vrste vstopnic razvrstijo na čuden način.
Oglejte si video
V današnjem primeru je bil video posnet po pisanju članka. Predlagam, da v vrstice vstopnic dodate stolpec zaporedja za nadzor vrstnega reda razvrščanja.
Video zapis
Naučite se Excel iz podcasta, epizoda 2204: Vrnite vse VLOOKUPE.
Hej, dobrodošli nazaj v oddaji, jaz sem Bill Jelen. Današnje vprašanje iz Nashville Music Cityja. Bil sem tam spodaj v Nashvillu, nekdo je odgovoren za razpored nalaganja vozovnic v sistem za prodajo vozovnic, zato imamo to: Imamo seznam dogodkov - prihajajoči dogodki - imamo datum, prizorišče in načrt vstopnic. Torej, čeprav je v palači nekaj, lahko obstajajo različni načrti vstopnic - na primer, mogoče je tla konfigurirana, veste, s sedeži ali pa je to samo samo stoječa soba, kajne?
Torej, odvisno od vrste paketa vozovnic, morate priti sem do iskalne tabele in poiskati vse ustrezne dogodke, v bistvu pa bomo naredili tisto, čemur pravim eksplozija VLOOKUP. Torej, če je nekaj v Hannah C, se bodo spustili do Hannah C in če je v Hannah C - 1, 2, 3, 4, 5, 6 - 7 predmetov, bomo imeli vrniti sedem vrstic - kar pomeni, da boste morali vstaviti še šest vrstic in te podatke kopirati. Vredu.
Zdaj tega sploh ne bomo storili z VLOOKUP-om, vendar dobite koncept - delamo VLOOKUP in vse odgovore vrnemo kot nove vrstice. Torej, vzel bom obe tabeli in jih s Ctrl + T naredil v pravo mizo. Prvi, ki se imenujejo Tabela 1 - grozljivo ime, recimo temu Dogodki ali oddaje, recimo tako kažejo - in drugi, zdaj, hej, tukaj sem se naučil, ker sem to vadil - polje zaporedja tukaj. Torej = ROW (A1), dvokliknite in kopirajte, nato pa kopirajte in prilepite posebne vrednosti. Vredu. Zdaj naredimo, da bo to postalo tabela - Ctrl + T, in to bomo poklicali Tickets.
Vredu. Tako imamo predstave, imamo karte. Odšel bom na zavihek Podatki in tukaj sem v oddaji, rečem, da želim podatke dobiti iz tabele ali obsega - mimogrede, to je Power Query. Če ste že v programu Excel 2010 ali 2013, ga lahko brezplačno prenesete iz Microsofta, naložite orodje Power Query. Če uporabljate Mac ali iOS ali Android, žal ne potrebujete Power Query. V redu, torej iz tabele ali obsega … poiščite nekoga, ki ima - poiščite prijatelja, ki ima - računalnik z operacijskim sistemom Windows in naj to nastavi. Vredu. Tukaj je tabela, s tem ne bomo naredili ničesar, samo zapri in naloži, zapri in naloži in nato izgovori "Samo ustvari povezavo", popolno. Tukaj bomo prišli do naše druge tabele: Pridobi podatke, iz tabele ali obsega, tej ne naredimo ničesar, zapri in naloži,Zapri in naloži v, "Samo ustvari povezavo", v redu. Torej, kar imamo zdaj, imamo povezavo s prvo tabelo in povezavo z drugo tabelo. Teh dveh ne bomo združili, kar je v bistvu, kot da bi delali VLOOKUP, ali pa je zbirka podatkovnih baz, mislim, res takšna. Združite poizvedbe, združili se bomo. Vredu.
Sedem stvari, ki jih morate storiti v tem pogovornem oknu - in to je nekoliko zmedeno - za prvo tabelo bomo izbrali oddaje; za drugo mizo izberite Vstopnice; izberite, katero polje jim je skupno, in to je lahko več polj - lahko pritisnete in kliknete - vendar je v tem primeru samo en načrt vstopnic; in nato načrt vozovnic; in nato bomo vrsto Združevanja spremenili v Notranje združevanje z "samo ustreznimi vrsticami". Vredu. Zdaj kliknete V redu in mislite, da bo vaša celotna težava rešena, vendar ste preprosto zdrobljeni, ker so tukaj vsi podatki iz A - sploh niso vstavili nobene vrstice - in tukaj, samo dolgočasno neumno polje z imenom Vstopnice, ki ima samo tabelo, mizo, mizo, hah.
K sreči pa je na vrhu ikone Expand in to bomo še razširili - ni mi treba vzeti načrta, to že imam - vrsta in zaporedje vozovnice. Ne želim, da se imenuje Tickets.TicketType, kar želi storiti Power Query - zato počistim to polje. Vredu. Trenutno imamo 17 vrstic podatkov; ko kliknem OK, BAM! Tam je eksplozija. Torej, Michael Seeley in Starlighter's se prikažejo z vsemi različnimi vrstami vstopnic, kot je ta. V redu in glej, da se ti tipi vozovnic pojavljajo zaporedoma, to je super. Toda Michael Seeley ni naslednja oddaja, naslednja oddaja je 5. junija. Torej, ko poskusim to razvrstiti po datumu - to me obnore, tega ne znam razložiti. Razvrsti po datumu, Mike Man in mehanika pa pride do 65, potem pa so karte vse zamočene. Oni 'spet na napačnem zaporedju, in potem sem zato moral narediti to zaporedje - tako se počuti. Lahko razvrstim po zaporedju. Torej, zdaj 6, 5, lepo, potem pa so vstopnice pravilne. In v tem trenutku tega stolpca ne potrebujemo več. Tako lahko z desno miškino tipko kliknem in odstranim, nato pa Zapri in naloži - tokrat bom dejansko zaprl in naložil, ne pa Zapri in naložil v - in dobili smo svoj rezultat. Vredu.
Torej smo s seznama dogodkov prešli na celoten seznam, toda tukaj je čudovit del: to sem zajebal, Mike Man in mehanika ni palača B, njena palača C. Torej se vrnem k izvirniku zgoraj desno -ročni kotiček za več informacij o knjigi.
Vredu. Teme v tej epizodi: Kaley v Nashvillu mora narediti VLOOKUP, da vrne vsa ujemanja, običajno vstavi nove vrstice. In to je baza podatkov o vozovnicah, v redu? Torej bom temu rekel VLOOKUP Explosion, ker bo vsaka oddaja eksplodirala v do 16 vrstic. Za rešitev tega vprašanja bomo uporabili Power Query in izvedel sem, da se bo datum prikazal v napačnem zaporedju, razen če vrsti vstopnice dodamo polje Zaporedje. Naredite oba niza v tabelo s kombinacijo tipk Ctrl + T; poimenujte jih kot oddaje in vstopnice; in nato iz vsake tabele, Pridobi podatke, Iz tabele, Zapri in naloži, da ustvarite samo povezavo; ponovite za drugo tabelo; nato Podatki, Pridobi podatke, Združi poizvedbe, Združi; in potem tisto pogovorno okno me precej zmede - izberite Dogodki, izberite Vstopnice, v obeh kliknite Tip vstopnice, spremenite spoj v notranji spoj,kliknite V redu in nato dobite tisti grozljivo razočarajoč rezultat, kjer je le stolpec z napisom Tabela, Tabela, Tabela, Tabela; na vrhu kliknite ikono Expand; izberite polje Zaporedje vozovnic; ne predpona z imenom tabele; in lahko razvrstite po datumu, razvrstite po zaporedju; Zaprite in naložite v preglednico. Čudovito je, da če se osnovni podatki spremenijo, samo osvežite in dobili boste svoje rezultate.
Zdaj, če želite prenesti delovni zvezek, uporabljen iz današnjega videoposnetka, obiščite URL spodaj v opisu YouTube. Tam je tudi seznam prihajajočih seminarjev - rad bi vas videl na enem od mojih seminarjev Power Excel.
Zahvaljujem se Kaley, ker se je pojavila v Nashvillu in mi postavila to čudovito vprašanje. Želim vas, da ste se ustavili. Se vidimo naslednjič za še eno oddajo od.
Prenesite datoteko Excel
Če želite prenesti datoteko excel: return-all-vlookups.xlsx
Power Query me še naprej preseneča. To je druga iz tridnevne serije, kjer je odgovor Power Query:
- Torek: Pretvorite stolpec Datum / čas v samo datum
- Danes: Vrnite vse VLOOKUP-ove
- Četrtek: ustvarite anketo za vsakega od 1100 predmetov
Imam celoten seznam predvajanja v YouTubu, ki sem ga na koncu rešil z Power Query.
Excel Misel dneva
Za nasvet glede Excela sem prosil prijatelje Excel Excel. Današnja misel o premišljevanju:
"Ko dvomite, uporabite funkcijo OKROGLA!"
Mike Girvin
