Kaley iz Nashvillea radi na proračunskoj tablici za prodaju karata. Za svaki događaj ona bira plan ulaznica. Taj plan ulaznica mogao bi sadržavati od 4 do 16 vrsta ulaznica za događaj. Kaley želi formulu koja će ići u tablicu pretraživanja i vraćati * sve * podudaranja, umetajući nove retke prema potrebi.
Iako nemam VLOOKUP koji to može riješiti, novi Power Query alati ugrađeni u Excel 2016 mogu to riješiti.
Bilješka
Ako imate verziju programa Excel 2010 ili Excel 2013 za Windows, Power Query možete besplatno preuzeti s Microsofta. Nažalost, Power Query još nije dostupan za Excel za Android, Excel za iOS ili Excel za Mac.
Da bi ilustrirao cilj: Mike McCann i mehanika pojavljuju se u kazalištu Allen s planom ulaznica C. Budući da se u tablici za pretraživanje nalaze četiri odgovarajuća retka, Kaley želi četiri reda s natpisom Mike McCann i mehanika, svaki s različitim podudaranjem tablica pretraživanja.
Odaberite ćeliju u izvornoj tablici. Pritisnite Ctrl + T da biste te podatke označili kao tablicu. Na kartici Alati tablice preimenujte tablicu iz Tablica1 u Prikazi. Ponovite za tablicu pretraživanja, nazvavši je Ulaznice.
Odaberite ćeliju u tablici Emisije. Na kartici Podaci odaberite Iz tablice / raspona.
Nakon što se otvori uređivač Power Query, otvorite padajući meni Zatvori i učitaj i odaberite Zatvori i učitaj u ….
U dijaloškom okviru Uvoz podataka odaberite Samo stvori vezu.
Idite do stola Ulaznice. Ponovite korake do Samo stvaranje veze s ulaznicama. U oknu Upiti trebali biste vidjeti obje veze:
Odaberite bilo koju praznu ćeliju. Odaberite Podaci, Dohvati podatke, Kombiniraj upite, Spoji.
U dijaloškom spoju nalazi se šest koraka. 3. i 4. mi se ne čine intuitivno.
- Odaberite Shows s vrha padajućeg izbornika
- Odaberite Ulaznice s drugog padajućeg izbornika.
- Kliknite naslov za Plan karata na vrhu da biste taj stupac odabrali kao strani ključ u tablici Emisije.
- Kliknite naslov za Plan karata na dnu da biste taj stupac odabrali kao ključno polje u tablici pretraživanja.
- Otvorite vrstu Pridruživanja i odaberite Unutarnji (samo odgovarajući redovi).
- Kliknite U redu
Rezultati su u početku razočaravajući. Vidite sva polja iz tablice 1 i stupac koji kaže Tablica, Tablica, Tablica.
Kliknite ikonu Proširi na vrhu stupca Ulaznice.
Poništite odabir plana ulaznica jer to polje već imate. Preostalo polje zvat će se Tickets.Ticket Type, osim ako poništite opciju Koristi izvorno ime kao prefiks.
Uspjeh! Svaki redak za svaku emisiju eksplodira u više redaka.
Nisam posebno zadovoljan sortiranjem podataka. Sortiranje po datumu uzrokuje sortiranje vrsta ulaznica na neobičan način.
Gledaj video
U današnjem slučaju video je snimljen nakon pisanja članka. Predlažem da se u vrste ulaznica doda stupac niza za kontrolu redoslijeda sortiranja.
Prijepis videozapisa
Naučite Excel iz Podcasta, epizoda 2204: Vratite sve VLOOKUP-ove.
Hej, dobrodošao natrag u prijenos uživo, ja sam Bill Jelen. Današnje pitanje iz Nashville Music Cityja. Bio sam tamo dolje u Nashvilleu, netko je odgovoran za raspoređivanje utovara karata u sustav za prodaju karata, pa evo što imamo: Imamo popis događaja - nadolazeće događaje - imamo datum, mjesto i plan ulaznica. Pa, iako, iako se nešto održava u Palači, možda postoje različiti planovi ulaznica - poput, možda je pod konfiguriran, znate, sa sjedištima ili je to možda samo stajaća soba, zar ne?
Dakle, ovisno o tipu plana karte, morate doći ovdje do tablice Pretraživanja i pronaći sve odgovarajuće događaje, a u osnovi ćemo napraviti ono što ja nazivam eksplozijom VLOOKUP-a. Dakle, ako je nešto u Hannah C, oni će se spustiti u Hannah C, a ako u Hannah C postoji - 1, 2, 3, 4, 5, 6-- 7 predmeta, imat ćemo da biste vratili sedam redaka - što znači da ćete morati umetnuti još šest redaka i kopirati te podatke. U redu.
Sada to uopće nećemo raditi s VLOOKUP-om, ali shvaćate koncept - radimo VLOOKUP i sve odgovore vraćamo kao nove retke. U redu, dakle, uzet ću obje ove tablice i pretvoriti ih u pravi stol pomoću Ctrl + T. Prvi koji se zovu Tablica 1 - grozno ime, nazovimo ovo Događaji ili emisije, nazovimo to emisije, onako - a drugi, eto, evo što sam naučio jer sam ovo vježbao - moramo imati ovdje polje slijeda. Dakle = RED (A1), dvaput kliknite i kopirajte to, a zatim kopirajte i zalijepite posebne vrijednosti. U redu. Sada napravimo da to pretvori u tablicu - Ctrl + T, a mi ćemo je nazvati Tickets.
U redu. Dakle, imamo predstave, imamo karte. Idem na karticu Podaci, a ovdje sam u emisiji, želim reći da svoje podatke želim dobiti iz tablice ili raspona - usput je ovo Power Query. Ako ste se vratili u Excel 2010 ili 2013, ovo možete besplatno preuzeti s Microsofta, preuzmite alat Power Query. Ako ste na Macu ili iOS-u ili Androidu, nažalost, nema Power Queryja za vas. U redu, dakle iz tablice ili raspona … pronađite nekoga tko ima-- pronađite prijatelja koji ima-- Windows PC i neka to postave. U redu. Evo tablice, na ovome nećemo učiniti ništa, samo zatvorite i učitajte, zatvorite i učitajte, a zatim recite "Samo stvori vezu", savršeno. Doći ćemo ovdje do naše druge tablice: Preuzmi podatke, iz tablice ili raspona, ovoj ne radimo ništa, Zatvori i učitaj,Zatvorite i učitajte u "Ustvari samo vezu", u redu. Dakle, ono što imamo sada, je da imamo vezu s prvom tablicom i vezu s drugom tablicom. Nećemo spajati ovo dvoje, što je u osnovi kao da radite VLOOKUP, ili je Database Joint, pretpostavljam, stvarno takav kakav je. Kombinirajte upite, idemo u Spajanje. U redu.
Sada, sedam stvari koje morate učiniti u ovom dijaloškom okviru - i pomalo je zbunjujuće - odabrat ćemo Shows kao prvu tablicu; odaberite Ulaznice kao drugi stol; odaberite koje im je zajedničko polje, a to može biti više polja - možete pritisnuti i kliknuti - ali u ovom slučaju postoji samo jedan plan ulaznica; a zatim Plan ulaznica; a zatim ćemo promijeniti tip Pridruživanja u Unutarnji spoj sa "samo odgovarajućim redovima". U redu. Sada, kliknete U redu i mislite da će se cijeli vaš problem riješiti, ali jednostavno ste zgnječeni jer su ovdje svi podaci iz A - oni uopće nisu umetnuli nove retke - i ovdje, samo dosadno glupo polje zvano Tickets koje ima samo Table, Table, Table, hah.
Ali, srećom, na vrhu toga nalazi se ikona Proširi, a mi ćemo to proširiti - ne trebam uzimati plan, to već imam - vrsta i redoslijed ulaznica. Ne želim da se zove Tickets.TicketType, što Power Query želi učiniti - pa poništim ovaj okvir. U redu. Trenutno imamo 17 redaka podataka; kad kliknem OK, BAM! Eno eksplozije. Dakle, Michael Seeley i Starlighter's pojavljuju se sa svim različitim vrstama karata, poput ove. U redu, i ako se ove vrste karata pojavljuju u nizu, to je sjajno. Ali Michael Seeley nije sljedeća emisija, sljedeća emisija je 5. lipnja. Pa kad ovo pokušam razvrstati po datumu - ovo me izluđuje, ne mogu to objasniti. Poredaj po datumu, a Mike Man and the Mechanics dolazi do 65, ali onda su karte sve zeznute. Oni'ponovno na pogrešnom slijedu, i onda sam zato morao napraviti ovaj niz - osjeća se tako. Mogu sortirati prema slijedu. Dakle, sada 6, 5, prekrasno, a onda su ulaznice točne. I zapravo, u ovom trenutku nam ova kolona više nije potrebna. Tako da mogu kliknuti desnom tipkom miša i ukloniti, a zatim Zatvoriti i učitati - ovaj put ću zapravo zatvoriti i učitati, a ne Zatvoriti i učitati u - i imamo svoj rezultat. U redu.
Prešli smo s popisa događaja na čitav ovaj veliki popis, ali ovdje je strašan dio: zeznuo sam ovo, Mike Man i Mehanika nije Palača B, već Palača C. Vraćam se na izvornik gore desno -kut za više informacija o knjizi.
U redu. Teme u ovoj epizodi: Kaley u Nashvilleu mora napraviti VLOOKUP da bi vratio sve podudarnosti, obično umetajući nove retke. I to je baza podataka za ulaznice, u redu? Stoga ću ovo nazvati VLOOKUP eksplozijom jer će svaka emisija eksplodirati u do 16 redova. Koristit ćemo Power Query da bismo to riješili, a saznao sam da će se Datum pojaviti na pogrešnoj sekvenci, osim ako vrsti ulaznica ne dodamo polje Sequence. Napravite oba seta u tablicu pomoću Ctrl + T; nazovite ih emisijama i ulaznicama; a zatim iz svake tablice, Dohvati podatke, Iz tablice, Zatvori i učitaj, da se stvori samo veza; ponovite za drugom tablicom; zatim Podaci, Dohvati podatke, Kombiniraj upite, Spoji; a onda me taj dijaloški okvir prilično zbunjuje - odaberite Događaji, odaberite Ulaznice, kliknite tip karte u oba, promijenite spoj u unutarnji spoj,kliknite U redu i tada ćete dobiti onaj užasno razočaravajući rezultat gdje je to samo stupac koji kaže Tablica, Tablica, Tablica, Tablica; kliknite ikonu Proširi na vrhu; odaberite polje Redoslijed ulaznica; nemojte dodati prefiks s nazivom tablice; a možete sortirati po datumu, sortirati prema slijedu; Zatvorite i učitajte u proračunsku tablicu. Lijepa stvar je ta da ako se osnovni podaci promijene - samo osvježite i imate svoje rezultate.
E, hej, da biste preuzeli radnu knjigu korištenu iz današnjeg videozapisa, posjetite URL tamo dolje u opisu YouTubea. Također i popis predstojećih seminara - Volio bih vas vidjeti na jednom od mojih Power Excel seminara.
Želim zahvaliti Kaley što se pojavila u Nashvilleu i postavila mi to sjajno pitanje. Želim da svratiš. Vidimo se sljedeći put za još jedan prijenos od.
Preuzmite datoteku Excel
Da biste preuzeli excel datoteku: return-all-vlookups.xlsx
Power Query me i dalje oduševljava. Ovo je druga iz trodnevne serije u kojoj je odgovor Power Query:
- Utorak: Pretvorite stupac Datum / vrijeme u samo datum
- Danas: Vrati sve VLOOKUP-ove
- Četvrtak: Stvorite anketu za svaki od 1100 predmeta
Imam cijeli popis pjesama na YouTubeu koje sam na kraju riješio pomoću Power Queryja.
Excel misao dana
Pitao sam svoje prijatelje Excel Master za savjet o Excelu. Današnja misao za razmišljanje:
"Kada sumnjate, koristite funkciju OKRUGLO!"
Mike Girvin
