Nashville'ist pärit Kaley töötab piletiarvutiga. Iga ürituse jaoks valib ta piletiplaani. See piletimüügiplaan võiks näidata ürituse piletitüüpe 4–16. Kaley soovib valemit, mis läheb otsingu tabelisse ja tagastab * kõik * vasted, lisades vastavalt vajadusele uued read.
Kuigi mul pole VLOOKUP-i, mis saaks selle lahendada, suudavad selle lahendada Excel 2016 sisseehitatud uued Power Query tööriistad.
Märge
Kui teil on Exceli 2010 või Excel 2013 Windowsi versioon, saate Power Query Microsofti tasuta alla laadida. Kahjuks pole Power Query veel saadaval rakendusele Excel Androidile, Exceli iOS-ile ega Exceli Mac-ile.
Eesmärgi illustreerimiseks: Mike McCann ja Mechanics ilmuvad Alleni teatrisse piletiplaaniga C. Kuna otsingutabelis on neli vastavat rida, soovib Kaley nelja rida, kus on kirjas Mike McCann ja Mechanics, mõlemal on erinev vaste otsingu tabel.
Valige lahter algsest tabelist. Nende andmete tabelina märkimiseks vajutage klahvikombinatsiooni Ctrl + T. Nimetage vahekaardil Tabelitööriistad tabel tabelist 1 ümber suvandiks Näitused. Korrake otsingu tabelit ja nimetage seda Piletiteks.
Valige tabelist Näitused lahter. Valige vahekaardil Andmed tabelist / vahemikust.
Pärast Power Query redaktori avanemist avage rippmenüü Sule ja laadige ning valige Sule ja laadige ….
Dialoogis Andmete importimine valige Ainult ühenduse loomine.
Minge tabelisse Piletid. Korrake samme ainult ühenduse loomiseks piletitega. Paanil Päringud peaksite nägema mõlemat ühendust:
Valige ükskõik milline tühi lahter. Valige Andmed, Hangi andmed, Kombineeri päringud, Ühenda.
Ühendamise dialoogis on kuus sammu. 3. ja 4. ei tundu mulle intuitiivsed.
- Valige ülemisest rippmenüüst Näitused
- Valige teisest rippmenüüst Piletid.
- Klõpsake ülaservas pealkirja Piletiplaan, et tabelis Näitused see veerg võõrvõti valida.
- Klõpsake allpool asuvat pealkirja Piletiplaan, et valida see veerg otsingutabeli võtmeväljaks.
- Avage liidetüüp ja valige Sisemine (ainult sobivad read).
- Klõpsake nuppu OK
Tulemused on esialgu pettumused. Näete kõiki tabeli 1 välju ja veergu Tabel, Tabel, Tabel.
Klõpsake veeru Piletid ülaosas ikooni Laienda.
Tühistage piletiplaani valik, kuna teil on see väli juba olemas. Ülejäänud välja nimi on Piletid. Piletitüüp, kui te ei tühista valikut Kasuta algset nime prefiksina.
Edu! Iga saate iga rida plahvatatakse mitmeks reaks.
Ma pole eriti rahul andmete sortimisega. Kuupäeva järgi sorteerimine põhjustab piletitüüpide paaritu sorteerimise.
Vaata videot
Tänases juhtumis filmiti video pärast artikli kirjutamist. Sorteerimise järjekorra kontrollimiseks soovitan lisada Piletitüüpidele järjestuse veeru.
Video ärakiri
Õppige Exceli programmi Podcast, osa 2204: tagastage kõik VLOOKUP-id.
Tere, tere tulemast tagasi netisaate juurde, ma olen Bill Jelen. Tänane küsimus Nashville Music Citylt. Ma olin seal Nashville'is, keegi vastutab piletite piletimüügisüsteemi laadimise ajastamise eest ja nii on meil järgmine: meil on nimekiri sündmustest - eelseisvad sündmused - meil on kuupäev, toimumiskoht ja piletiplaan. Niisiis, ehkki palees peetakse midagi, võivad piletiplaanid olla erinevad - võib-olla on põrand konfigureeritud, teate, istmetega või võib-olla on see ainult seisev tuba, eks?
Niisiis, sõltuvalt piletiplaani tüübist, peate siia tulema otsingu tabeli juurde ja leidma kõik sobivad sündmused ja sisuliselt teeme seda, mida ma nimetan VLOOKUPi plahvatuseks. Nii et kui midagi on Hannah C-s, lähevad nad Hannah C-sse ja kui Hannah C-s on 1, 2, 3, 4, 5, 6-7 eset, siis meil on seitsme rea tagastamiseks - see tähendab, et peate lisama veel kuus rida ja need andmed alla kopeerima. Hästi.
Nüüd ei tee me seda VLOOKUPiga üldse, kuid saate idee - teeme VLOOKUPi ja tagastame kõik vastused uute ridadena. Hea küll, ma võtan mõlemad tabelid ja muudame need Ctrl + T abil tõeliseks tabeliks. Esimesed nimetasid tabelit 1 - kohutav nimi, nimetagem seda sündmusteks või saadeteks, nimetagem seda näitusteks - ja teised, nüüd, hei, siin on see, mida ma õppisin, sest ma harjutasin seda - meil peab olema jadaväli siin. Nii = RIDA (A1), topeltklõpsake ja kopeerige see alla ning seejärel kopeerige ja kleepige eriväärtused. Hästi. Nüüd teeme nii, et sellest saaks tabel - Ctrl + T ja nimetame seda Piletiteks.
Hästi. Nii et meil on etendused, meil on piletid. Lähen vahekaardile Andmed ja olen siin saates, tahan öelda, et tahan oma andmed tabelist või vahemikust hankida - see on muide Power Query. Kui olete tagasi rakenduses Excel 2010 või 2013, saate selle Microsofti tasuta alla laadida, laadige alla tööriist Power Query. Kui kasutate Maci, iOS-i või Android-i, siis vabandage, teie jaoks pole vaja Power Query'i. Hästi, nii et tabelist või vahemikust … leidke keegi, kellel on-- leidke sõber, kellel on Windowsi arvuti, ja laske neil see seadistada. Hästi. Siin on tabel, me ei hakka sellega midagi tegema, lihtsalt sulgege ja laadige, sulgege ja laadige ning seejärel öelge "Loo ainult ühendus", täiuslik. Me läheme siia oma teise tabeli juurde: Hangi andmed, tabelist või vahemikust, me ei tee sellele midagi, Sulge ja laadige,Sulgege ja laadige "Ainult ühenduse loomine", OK. Mis meil nüüd on, kas meil on ühendus esimese tabeliga ja ühendus teise tabeliga. Me ei hakka neid kahte ühendama, mis sisuliselt sarnaneb VLOOKUP-i tegemisega või on andmebaasiühendus vist selline, nagu see on. Ühendage päringud, läheme ühendama. Hästi.
Seitse asja, mida peate selles dialoogiboksis tegema - ja see on natuke segane - valime esimeseks tabeliks Näitused; vali teiseks tabeliks Piletid; vali, milline väli on neil ühine, ja see võib olla mitu välja - saate juhtklõpsata - kuid sel juhul on ainult üks piletiplaan; ja siis Piletiplaan; ja siis muudame liidetüübi sisemiseks liitumiseks "ainult vastavate ridadega". Hästi. Nüüd klõpsate nuppu OK ja arvate, et kogu teie probleem lahendatakse, kuid olete lihtsalt purustatud, sest siin on kõik A-i andmed - nad pole üldse uusi ridu sisestanud - ja siin igav loll väli nimega Piletid, millel on lihtsalt Laud, Laud, Laud, hah.
Kuid õnneks on selle ülaosas laiendamise ikoon ja me laiendame seda - mul pole vaja plaani võtta, mul on see juba olemas - pileti tüüp ja järjestus. Ma ei taha, et seda nimetatakse Tickets.TicketType, mida Power Query soovib teha - nii et ma tühjendan selle kasti. Hästi. Praegu on meil 17 rida andmeid; kui klõpsan nuppu OK, BAM! Seal on plahvatus. Niisiis, Michael Seeley ja Tähesüütaja ilmuvad kõigi erinevate piletiliikidega, nagu see. Hästi ja kui näete, et need piletitüübid ilmuvad järjestikku, on see suurepärane. Kuid Michael Seeley pole järgmine saade, järgmine saade on 5. juunil. Nii et kui ma proovin seda kuupäeva järgi sorteerida - see ajab mind hulluks, ei oska ma seda seletada. Sorteeri kuupäeva järgi ja Mike Man and the Mechanics jõuab 65-ni, kuid siis on piletid kõik kinni keeratud. Nemadolen vale järjestuse peal ja siis pidin sellepärast seda jada tegema - tundub nii. Ma saan järjestada järjestuse järgi. Nii et nüüd, 6, 5, ilus ja siis selle sees on Piletid õiged. Ja tegelikult pole meil seda veergu enam vaja. Nii et ma saan paremklõpsata ja eemaldada ning seejärel sulgeda ja laadida - seekord kavatsen tegelikult sulgeda ja laadida, mitte sulgeda ja laadida - ja meil on oma tulemus. Hästi.
Niisiis läksime sündmuste loendist kogu selle suure nimekirja juurde, kuid siin on vinge osa: ma keerasin selle lahti, Mike Man ja Mechanics pole Palace B, selle Palace C. Nii et ma tulen tagasi paremas ülanurgas oleva originaali juurde - käenurk raamatu kohta lisateabe saamiseks.
Hästi. Selle osa teemad: Kaley Nashville'is peab kõigi vastete tagastamiseks tegema VLOOKUP, lisades tavaliselt uued read. Ja see on piletimüügi andmebaas? Nii et ma nimetan seda VLOOKUPi plahvatuseks, sest iga saade plahvatab kuni 16 rida. Kasutame selle lahendamiseks Power Query ja olen teada saanud, et kuupäev kuvatakse valel järjestusel, kui me ei lisa pileti tüübile välja Sequence. Tehke mõlemad komplektid tabelisse, kasutades klahvikombinatsiooni Ctrl + T; eenime neile saated ja piletid; ja seejärel igast tabelist Hangi andmeid, Tabelist, Sule ja laadi, et luua ainult ühendus; korrake teise laua jaoks; seejärel Data, Get Data, Combine Queries, Merge; ja siis see dialoogiboks on minu jaoks üsna segane - valige Sündmused, valige Piletid, klõpsake mõlemas Piletitüüp, muutke ühine sisemiseks liitmiseks,klõpsake nuppu OK ja siis saate selle kohutavalt pettumust valmistava tulemuse, kus see on lihtsalt veerg, mis ütleb tabelit, tabelit, tabelit, tabelit; klõpsake selle ülaosas laiendamise ikooni; vali väli Piletite järjestus; ärge lisage tabeli nimele eesliidet; ja saate sortida kuupäeva järgi, järjestada järjestuse järgi; Sule ja laadi arvutustabelisse. Ilus on see, et kui alusandmed muutuvad - värskendage lihtsalt oma tulemusi.
Hei, kasutage tänasest videost kasutatud töövihiku allalaadimiseks YouTube'i kirjelduses URL-i. Seal on ka loetelu eelseisvatest seminaridest - ma sooviksin teid näha ühel mu otseülekandes Power Exceli seminaril.
Ma tahan tänada Kaleyt Nashville'is ilmumise eest ja selle suurepärase küsimuse esitamise eest. Ma tahan, et te peatuksite. Järgmisel korral näeme järgmise Netcast'i jaoks.
Laadige alla Exceli fail
Exceli faili allalaadimiseks: return-all-vlookups.xlsx
Power Query hämmastab mind jätkuvalt. See on teine kolmepäevasest sarjast, kus vastus on Power Query:
- Teisipäev: teisendage kuupäeva / kellaaja veerg lihtsalt kuupäevaks
- Täna: tagastage kõik VLOOKUPid
- Neljapäev: koostage küsitlus kõigi 1100 üksuse kohta
Mul on terve YouTube'i esitusloend asjadest, mille ma lõpuks Power Query abil lahendasin.
Exceli päeva mõte
Olen küsinud oma Exceli meistri sõpradelt Exceli kohta nõu. Tänane mõte mõelda:
"Kui kahtlete, kasutage funktsiooni ROUND!"
Mike Girvin
