Ali lahko Excel VLOOKUP vrne vse rezultate in se jim pridruži z vejico?
Oglejte si video
- Cilj je združiti vse besedilne odgovore iz VLOOKUP-a
- Billova metoda: Uporabite funkcijo VBA, imenovano GetAll
- Edinstven seznam z odstranjevanjem dvojnikov
- Mikeova metoda:
- Edinstven seznam z uporabo naprednega filtra
- V Office 365 dodana funkcija TEXTJOIN
TEXTJOIN(", ",,IF(OilChangeData(ID)=D2,OilChangeData(Comment),""))- Zaradi funkcije IF zahteva formula Ctrl + Shift + Enter kadar koli uredite formulo
- Alt AQOR Enter bo znova zagnal Napredni filter!
Video zapis
Epizoda 183: Pridružite se vsem tekem VLOOKUP
Bill Jelen: Hej, dobrodošli nazaj. Čas je za še en dvobojni podcast Excel. Sem Bill Jelen iz, pridružil se mi bo Mike Girvin za Excel Is Fun. To je naša epizoda 183: Pridružite se vsem tekem VLOOKUP.
(Glasba)
V redu, današnje vprašanje od Matta. Ali lahko VLOOKUP vrne vse rezultate in jih pridruži skupaj z vejico med njimi. Na primer, 109876, ki sta tukaj tukaj, ali lahko vrne prostor z vejico z nizko stopnjo olja Preverjeno 12/12. In seveda, če bi jih bilo več, bi se več vrnilo. Torej, moja rešitev tukaj bo uporaba nekaj VBA. V redu, torej poskrbite, da je shranjen kot xlsm ali sicer ne morete zagnati VBA ali xlsb, ne pa xlsx - xlsx je tista datoteka, ki ne more zagnati VBA. Pritisnili bomo Alt + F11, se prepričali, da uporabljate Dual183 ali kako koli je ime vaše delovne knjige. V prazni modul vstavite modul in prilepili bomo to kodo.
Oglejmo si to funkcijo GetAll, tu je ID-številka, ki jo iščemo, in nato obseg, ki ga želimo videti. In začnemo, vrnili bomo spremenljivko, imenovano GetAll, zato začnemo s tem, da je enaka prazno prazno. Za vsako celico v mojem obsegu, če je vrednost celice tisto, kar iščemo, bomo vzeli GetAll = GetAll & "" in nato Cell.Offset (0 vrstic, 1 stolpec), z drugimi besedami vrednost to je tik ob tej številki ID, ker je v VBA tu številka ID. Če najdemo ujemajočo se številko ID, gremo za 1 stolpec. Kaj pa, če bi radi prešli 2 stolpca čez ali 3 stolpce, potem spremenite teh 0 vrstic in 1 stolpec v 2. V redu, preverite tudi, če ne vstavimo presledka z vejico, če je to Prvi.Torej, če je spremenljivka GetAll trenutno “”, potem ne bomo postavili vejice, v redu?
Zdaj, ko imamo tukaj to funkcijo, poglejte, kako enostavno je to rešiti Mattov problem. Prišli bomo sem in mu vzeli osebne izkaznice, Ctrl + C in tako prilepili Ctrl + V. Podatki, Odstrani dvojnike, kliknite V redu. Torej obstaja edinstven seznam ID-jev, nato želimo reči = getall in to vrednost iščemo v vejici E2. Če pogledam ta razpon tukaj, bom pritisnil F4. F4 deluje tako kot običajna funkcija. In spet premaknite Mattovo vprašanje, dvokliknite, da ga ustrelite. Delovalo bo.
In samo poskusimo, poskusimo nekaj norega tukaj. Naredimo frazo 1 in jih postavimo na vrsto, kot so fraze od 1 do 10. Vse to bomo podpisali na 109999. Prilepite in nato prilepite sem. Kopirajte to formulo navzdol, uredite formulo, tako da bo šla vse do dna, seveda. Ja. In vrnil bo vse te stavke. Torej, to je moja rešitev, VBA, malo funkcije tam. Mike, poglejmo, kaj imaš.
Mike Girvin: Hvala. GetAll, to je izjemna funkcija VBA. V redu, grem do lista tukaj. Sem ga že pretvoril v Excelovo tabelo, tako da, ko bomo spodaj dodali zapise, upajmo, da se bodo stvari posodobile.
Zdaj prva stvar, ki jo bom naredil v dveh delih. Tu bi lahko naredil formulo za izvleček unikatnega seznama, vendar bi rad pogledal še eno možnost: Napredni filter ima možnost izvlečka edinstvenega seznama in ga je mogoče posodobiti. Poudaril bom samo podatke stolpca ID, do naprednega filtra ali pa bom uporabil tipkovnico Alt, A, Q. Zdaj pa seznam filtrov namestite, nikakor. Želim ga kopirati na drugo mesto. Dobil je samo stolpec A in ker gre za Excelovo tabelo, ki se bo pozneje razširila. Nimam nobenih meril, želim ga kopirati v D1 in preveriti samo Unikatni zapisi. Kliknite V redu.
Zdaj bom prišel sem, vstopijo vsi komentarji in uporabil bom funkcijo, ki deluje samo v Excelu 2016 Office 365: = funkcija TEXTJOIN. Samo za to funkcijo je vredno dobiti najnovejšo različico Excela. To je tako pogosta naloga, ki jo ljudje želijo narediti, združiti marsikaj skupaj. Zdaj je ločilo v "," in odlična lastnost te funkcije je, da ji lahko naročimo, da prezre prazne celice. Zdaj lahko postavim TRUE, 1 ali Pusti, opusti. Torej, pustil bom, izpustite. In tu potrebujemo svoje besedilo. Uporabili bomo funkcijo IF, da filtriramo in dobimo samo elemente, ki jih želimo. Rekel bom, da si tukaj oglejte celoten stolpec: Ime tabele in nato () ime polja, ali ste kdo od vas = na to relativno referenco celice, to je logični test. Če bi to kliknil in za oceno pritisnil tipko F9,zdaj lahko vidite, da imamo samo 2 RESNICI, Ctrl + Z, zdaj vtipkam vejico in z vrsto resnic in neresnic lahko zdaj dam elemente na izbiro. Zdaj bomo iz tega obsega izbrali samo predmete, ki imajo tukaj TRUE. Vejica in želim zagotoviti, da vstavimo “” - to bo prikazano kot prazna celica glede na drugi argument v TEXTJOIN
Zdaj bom zaprl oklepaj in zdaj bo funkcija IF ustvarila niz Trues in Falses, dejanski elementi iz tega obsega bodo pobrani, če bo videl True, vsi drugi elementi pa bodo imeli to prazno celico. In ugani kaj? TEXTJOIN bo popolnoma prezrl vse te prazne celice in vrnil samo elemente, ki se ujemajo s tem ID-jem, nato pa se mu pridružil s tem ločevalnikom. Zdaj je to zagotovo formula Array, ki zahteva posebno tipko Ctrol + Shift + Enter. Argument logičnega preizkusa vsebuje operacijo Array in ta argument te operacije Array ne more pravilno izračunati, razen če uporabimo tipkovnico Ctrl + Shift + Enter. Zdaj bom zaprl oklepaje. Pravzaprav bi lahko dokazali 1 tukaj v besedilu 1, če bi vse to F9 videl, da smo dobili dva elementa, ostale prazne celice bodo prezrte. Ctrl + Z. Zdaj pas to vnesite v celico s kombinacijo tipk Ctrl + Shift + Enter. Takoj poiščite Formula Bar. Ti skodrani oklepaji so Excel, ki vam pove, da je to razumel in izračunal kot Array formulo. Zdaj lahko dvokliknem in ga pošljem dol. To je videti dobro.
Odšel bom do zadnje celice in pritisnil F2, da preverim, ali vsi obsegi izgledajo pravilno. Zdaj ne želim storiti tega, da ne želim pritisniti Enter, ker bo formula, potem ko jo postavimo v način urejanja, pravilno izračunana le, če uporabimo Ctrl + Shift + Enter; ali ker smo formulo že vnesli, lahko s tipko Esc vrnemo nazaj na vse, kar je v celici, preden jo postavimo v način urejanja.
Zdaj pa preizkusimo to. Kliknil bom v zadnjo celico tukaj spodaj in pritisnil Tab in nato vpisal nov ID, Tab, Tab. Še en nov album, Tab, in že vidim, da tukaj nisem imel dovolj dela. Sem, postavili bomo - Perfect in nato Enter. Zdaj se to ne bo samodejno posodabljalo, kot če imamo kup formul, da štejemo unikatne predmete in nato izvlečemo unikatne predmete, vendar brez težav. Glejte to. Ta seznam edinstvenih zapisov lahko posodobimo, ker smo uporabili napredni filter, pri čemer ni pomembno, iz katere celice začnete, saj pri priklicu naprednega filtra zapomni obseg izvlečka in obsege, ki jih je prvotno gledal. Lahko kliknete Napredni filter ali uporabite tipkovnico Alt + A + Q. Moramo izbrati Kopiraj na drugo lokacijo, vendar si oglejte to.Popolnoma si je zapomnil in razširil na A13 zaradi funkcije tabele Excel. Zapomnil si je obseg ekstraktov. Moram preveriti samo Unikatne zapise, vendar kliknite V redu.
Zdaj moram priti in kopirati to formulo. In tu ste z uporabo naprednega filtra in neverjetne funkcije TEXTJOIN v operaciji Array dobili samo elemente, ki se ujemajo. V redu, vrni se nazaj.
Bill Jelen: Hej, Mike, to je super. V redu, zaključujem to epizodo. Uporabil sem funkcijo VBA, imenovano GetAll, in moj edinstven seznam je ustvaril Remove Duplicates, ki je veliko lažji od naprednega filtra, težava pa je, da gre za enkratno stvar. Ne spomni se prejšnjih nastavitev. Mike je svoj edinstveni seznam ustvaril z uporabo naprednega filtra, kar pomeni, da ga je pozneje lahko nadaljeval, ne da bi ponovno določil obseg vnosa in obseg izvlečka. Nato je TEXTJOIN, čudovita nova funkcija, dodal Office 365. Mike pravi, da je že to razlog, da dobite najnovejši Office. Rekel sem, da bo TEXTJOIN spremenil življenje. TEXTJOIN je super, ker lahko obvlada nize.
Alright, so here's the formula that Mike wrote: putting an IF in there and the “ ” returning the equivalent of an empty cell; and here we're saying Ignore empty cells. Ahh, that's beautiful but because of the IF function, the formula requires Ctrl+Shift+Enter to create the formula, or any time you edit the formula, all that Mike used to Esc to get out. And this section about IF forces you into Ctrl+Shift+Enter is a topic in Mike's awesome, awesome book, An Array Formulas Ctrl+Shift+Enter. Check that out at Amazon or elsewhere, your favorite bookseller. And then, the beautiful thing is that because Advanced Filter remembers the old settings, Mike used Alt+A+Q and then could have used O+R Enter, will rerun the Advanced Filter, copy the formula down for the new cells and it works. That is beautiful, alright.
Oh hej, vsem bi se rad zahvalil, da so se ustavili. Naslednjič se vidimo za še en podcast programa Dueling Excel in Excel Is Fun.
Prenesite datoteko
Prenesite vzorčno datoteko tukaj: Duel183.xlsm
