Može li Excel VLOOKUP vratiti sve rezultate i pridružiti im se zarezom između?
Gledaj video
- Cilj je povezati sve tekstualne odgovore iz VLOOKUP-a
- Billova metoda: Koristite VBA funkciju koja se naziva GetAll
- Jedinstveni popis pomoću Uklanjanje duplikata
- Mikeova metoda:
- Jedinstveni popis pomoću naprednog filtra
- U sustavu Office 365 dodana je funkcija TEXTJOIN
TEXTJOIN(", ",,IF(OilChangeData(ID)=D2,OilChangeData(Comment),""))- Zbog funkcije IF, formula zahtijeva Ctrl + Shift + Enter bilo kada uređujete formulu
- Alt AQOR Enter ponovno će pokrenuti Napredni filtar!
Prijepis videozapisa
Epizoda 183: Pridružite se svim VLOOKUP utakmicama
Bill Jelen: Hej, dobrodošao natrag. Vrijeme je za još jedan dvobojni Excel Podcast. Ja sam Bill Jelen iz, pridružit će mi se Mike Girvin za Excel Is Fun. Ovo je naša Epizoda 183: Pridružite se svim VLOOKUP utakmicama.
(Glazba, muzika)
U redu, današnje pitanje od Matta. Može li VLOOKUP vratiti sve rezultate i pridružiti ih zajedno sa zarezom između svakog. Na primjer, 109876, koja su ovo dvoje ovdje, može li vratiti prostor sa zarezom s niskim uljem Provjereno 12/12. I naravno da ih je više, vratilo bi se više. Dobro, ovdje će moje rješenje biti korištenje neke VBA. U redu, pazite da je spremljen kao xlsm ili ne možete pokrenuti VBA ili xlsb, ali ne i xlsx - xlsx je jedina datoteka koja ne može pokrenuti VBA. Pritisnite Alt + F11, provjerite jeste li na Dual183 ili kako se već zove vaša radna knjiga. Umetnite modul u prazan modul i mi ćemo zalijepiti ovaj kod, u redu.
Pogledajmo ovu funkciju GetAll, a ovdje je ID broj koji tražimo, a zatim raspon koji želimo tražiti. I započinjemo, vratit ćemo varijablu koja se zove GetAll, pa započinjemo s time da je jednaka praznini. Za svaku ćeliju u mom rasponu, ako je vrijednost ćelije ono što tražimo, uzet ćemo GetAll = GetAll & "", a zatim Cell.Offset (0 redaka, 1 stupac), drugim riječima vrijednost to je tik uz taj ID broj, jer u VBA, evo ID broja. Ako pronađemo odgovarajući ID broj, želimo prijeći 1 stupac. Što ako želite prijeći 2 stupca ili 3 stupca, onda promijenite 0 redaka i 1 stupac u 2. U redu, provjerite i ako ne stavimo razmak zarezom ako je to prvi.Dakle, ako je varijabla GetAll trenutno "", tada nećemo stavljati prostor za zarez, u redu?
Dakle, sada kada imamo ovu funkciju, pripazite kako je ovo lako riješiti Mattov problem. Doći ćemo ovamo i uzmimo njegove ID-ove, Ctrl + C i tako zalijepimo Ctrl + V. Podaci, Ukloni duplikate, kliknite U redu. Dakle, postoji jedinstveni popis ID-ova, a zatim želimo reći = getall i tražimo tu vrijednost u zarezu E2. Gledajući kroz ovaj raspon ovdje, pritisnite F4. F4 djeluje poput uobičajene funkcije. I opet pomaknite Matt-ovo pitanje s puta, dvaput kliknite da biste ga oborili. Radit će.
I samo probajmo, probajmo ovdje nešto ludo. Napravimo frazu 1 i stavimo ih gomilu poput fraza od 1 do 10. Sve ćemo to potpisati na 109999. Zalijepite i zalijepite ovdje. Kopirajte tu formulu, uredite formulu tako da ide sve do dna, naravno. Da. I vratit će sve te fraze. U redu, to je moje rješenje, VBA, mala funkcija tamo. Mike, da vidimo što imaš.
Mike Girvin: Hvala ,. GetAll, to je sjajna VBA funkcija. U redu, idem na list ovdje. Već sam ga pretvorio u Excel tablicu, pa će se nadamo da će se, kad dodamo zapise u nastavku, ažurirati.
Prvo što ću napraviti u dva dijela. Ovdje bih mogao izraditi formulu za izdvajanje jedinstvenog popisa, ali želim pogledati drugu opciju: Napredni filtar ima mogućnost izdvajanja jedinstvenog popisa i može se ažurirati. Istaknut ću samo podatke stupca ID, pa sve do Naprednog filtra ili ću upotrijebiti tipkovnicu Alt, A, Q. Sad, filtrirajte popis na mjestu, nema šanse. Želim ga kopirati na drugo mjesto. Dobio je samo stupac A i jer je riječ o Excel tablici koja će se kasnije proširiti. Nemam nikakve kriterije, želim ga kopirati u D1 i provjeriti samo jedinstvene zapise. Kliknite U redu.
Sada ću doći ovdje, ući Svi komentari i upotrijebit ću funkciju koja radi samo u programu Excel 2016 Office 365: = TEXTJOIN funkcija. Samo je za ovu funkciju vrijedno nabaviti najnoviju verziju Excela. Ovo je tako čest zadatak koji ljudi žele obaviti, spojiti mnoge stvari zajedno. Sada je naš graničnik u "," i sjajna stvar ove funkcije je to što joj možemo reći da zanemaruje prazne stanice. Sada mogu staviti ISTINA, 1 ili Ostaviti, izostaviti. Dakle, Ostavit ću to, izostaviti. I ovdje nam treba naš tekst. Upotrijebit ćemo funkciju IF za filtriranje i dobivanje samo željenih predmeta. Reći ću da ovdje pogledate cijeli ovaj stupac: Ime tablice, a zatim u () naziv polja, jesu li svi od vas = na ovu relativnu referencu ćelije, to je logičan test. Ako bih kliknuo ovo i pritisnuo tipku F9 za procjenu,mogli ste vidjeti trenutno imamo samo 2 ISTINICE, Ctrl + Z, sada tipkam zarez i s nizom Istina i netačnosti sada mu mogu dati stavke na odabir. Dakle, sada ćemo iz ovog raspona odabrati samo predmete koji ovdje imaju ISTINU. Zarez i želim obavezno staviti "" - to će se prikazati kao prazna ćelija u odnosu na drugi argument u TEXTJOIN.
Sada ću zatvoriti zagradu i sada će funkcija IF stvoriti taj niz Trues i Falses, stvarne stavke iz ovog raspona će se pokupiti ako ga vide True, a sve ostale stavke će imati tu praznu ćeliju. I pogodi što? TEXTJOIN će potpuno zanemariti sve one prazne ćelije i vratiti samo stavke koje odgovaraju ovom ID-u, a zatim će mu se pridružiti s tim graničnikom. Ovo je definitivno Array formula koja zahtijeva posebno pritiskanje tipke Ctrol + Shift + Enter. Logički testni argument sadrži našu Array operaciju i taj argument ne može pravilno izračunati ovu Array operaciju ako ne koristimo tipkovnicu Ctrl + Shift + Enter. Sad ću zatvoriti zagrade. Zapravo bismo mogli dokazati 1 upravo ovdje u tekstu 1 ako F9 sve ovo, možemo vidjeti da smo dobili 2 stavke, ostatak tih praznih ćelija će biti zanemaren. Ctrl + Z. Sada, nekas unesite ovo u ćeliju pomoću Ctrl + Shift + Enter. Odmah se ugledajte na Formula Bar. Te kovrčave zagrade su Excel koji vam govori da je to razumio i izračunao kao formulu niza. Sada mogu dvaput kliknuti i poslati ga dolje. To izgleda dobro.
Idem do posljednje ćelije i pritisnite F2 kako bih provjerio izgledaju li svi rasponi ispravno. Sada ono što ne želim učiniti je da ne želim pritisnuti Enter jer će se ta formula nakon što je stavimo u način uređivanja ispravno izračunati samo ako koristimo Ctrl + Shift + Enter; ili, jer smo već unijeli formulu, možemo se jednostavno koristiti tipkom Esc za povratak na ono što se nalazi u ćeliji prije nego što je stavimo u način uređivanja.
Ajmo sad, testirajmo ovo. Kliknut ću zadnju ćeliju ovdje dolje i pritisnuti Tab, a zatim upisati novi ID, Tab, Tab. Još jedan novi album, Tab, i već vidim da ovdje nisam imao dovoljno posla. Jesam, stavit ćemo - Perfect, a zatim Enter. Sada se ovo neće automatski ažurirati kao da imamo gomilu formula da brojimo jedinstvene stavke, a zatim izdvajamo jedinstvene stavke, ali nema problema. Gledajte ovo. Ovaj popis jedinstvenih zapisa možemo ažurirati jer smo koristili napredni filtar i nije važno od koje ćelije krećete, jer kada se pozove napredni filtar, pamti raspon ekstrakta i raspone koje je izvorno gledao. Možete kliknuti na Napredni filtar ili upotrijebiti tipkovnicu Alt + A + Q. Moramo odabrati Kopiraj na drugo mjesto, ali pogledajte to.Potpuno je zapamćen i proširen na A13 zbog značajke Excel tablice. Sjetilo se raspona ekstrakata. Moram provjeriti samo jedinstvene zapise, ali kliknite U redu.
Sad moram doći i kopirati ovu formulu. Eto, koristeći Napredni filtar i nevjerojatnu funkciju TEXTJOIN pomoću, u operaciji Array dobivate samo stavke koje se podudaraju. Dobro, vratite se na.
Bill Jelen: Hej, Mike, to je sjajno. U redu, završavam ovu epizodu. Koristio sam VBA funkciju pod nazivom GetAll, a moj jedinstveni popis stvorio je Remove Duplicates, što je mnogo lakše od Naprednog filtra, ali problem je u tome što je to jednokratna stvar. Ne sjeća se prethodnih postavki. Mike je stvorio svoj jedinstveni popis pomoću Naprednog filtra, što znači da bi ga kasnije mogao ponoviti bez ponovnog određivanja ulaznog raspona i raspona izdvajanja. A onda je TEXTJOIN, prekrasna nova funkcija, dodala Office 365. Mike kaže da je samo to razlog da nabavite najnoviji Office. Rekao sam da će TEKSTJON promijeniti život. TEXTJOIN je strašan jer može obraditi nizove.
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.
O, hej, želim zahvaliti svima što su navratili. Vidjet ćemo se sljedeći put za još jedan podcast programa Dueling Excel i Excel Is Fun.
Preuzmi datoteku
Preuzmite uzorak datoteke ovdje: Duel183.xlsm
