Ar „Excel VLOOKUP“ gali grąžinti visus rezultatus ir sujungti juos su kableliu?
Žiūrėti video
- Tikslas yra susieti visus teksto atsakymus iš VLOOKUP
- Bilo metodas: naudokite VBA funkciją, vadinamą „GetAll“
- Unikalus sąrašas naudojant „Pašalinti dublikatus“
- Mike'o metodas:
- Unikalus sąrašas naudojant išplėstinį filtrą
- „TEXTJOIN“ funkcija pridėta „Office 365“
TEXTJOIN(", ",,IF(OilChangeData(ID)=D2,OilChangeData(Comment),""))- Dėl IF funkcijos formulei reikalinga Ctrl + Shift + Enter bet kuriuo metu, kai redaguojate formulę
- „Alt AQOR Enter“ iš naujo paleis Išplėstinį filtrą!
Vaizdo įrašo nuorašas
183 serija: Prisijunkite prie visų „VLOOKUP“ rungtynių
Billas Jelenas: Ei, sveiki sugrįžę. Atėjo laikas kitam „Dueling Excel“ tinklui. Aš esu Billas iš, prie manęs prisijungs Mike'as Girvinas, skirtas programai „Excel Is Fun“. Tai yra mūsų 183 serija: Prisijunkite prie visų „VLOOKUP“ rungtynių.
(Muzika)
Gerai, šiandien Matto klausimas. Ar VLOOKUP gali grąžinti visus rezultatus ir sujungti juos su kableliu tarp jų. Pvz., 109876, kurie čia yra šie du, ar gali grąžinti kablelį su mažu aliejumi. Patikrinta 12/12. Ir, žinoma, jei jų būtų daugiau, tai grįžtų daugiau. Gerai, todėl mano sprendimas čia bus naudoti tam tikrą VBA. Gerai, todėl įsitikinkite, kad jis išsaugotas kaip xlsm, kitaip negalite paleisti VBA ar xlsb, bet ne xlsx - xlsx yra tas failas, kuris negali paleisti VBA. Paspausime „Alt“ + F11, įsitikinkite, kad naudojatės „Dual183“ ar kitu jūsų darbaknygės pavadinimu. Įdėkite modulį į tuščią modulį ir mes įklijuosime šį kodą, gerai.
Pažvelkime į šią funkciją „GetAll“, o čia yra asmens kodas, kurio ieškome, ir diapazonas, kurio norime ieškoti. Pradedame, grąžiname kintamąjį, vadinamą „GetAll“, todėl pradėsime nuo to, kad jis bus lygus tuščiam tuščiam. Kiekvienai mano diapazono langelei, jei langelio vertė yra tai, ko ieškome, imsime „GetAll = GetAll &“ “ir tada„ Cell.Offset “(0 eilučių, 1 stulpelis), kitaip tariant, reikšmę tai tik šalia to ID numerio, nes vėl VBA, čia yra ID numeris. Jei rasime atitinkantį ID numerį, norime pereiti 1 stulpelį. Ką daryti, jei norite pereiti per 2 arba 3 stulpelius, tada pakeiskite šią 0 eilučių ir 1 stulpelį į 2. Gerai, taip pat patikrinkite, ar - mes nededame kablelio tarpo, jei taip yra Pirmasis.Taigi, jei „GetAll“ kintamasis šiuo metu yra „“, kablelio nedėsime, gerai?
Taigi dabar, kai turime šią funkciją, stebėkite, kaip lengva tai išspręsti Mato problemą. Mes eisime čia ir paimkime jo ID, „Ctrl“ + C ir taip įklijuokime „Ctrl“ + V. Duomenys, Pašalinti dublikatus, spustelėkite Gerai. Taigi yra unikalus ID sąrašas, tada mes norime pasakyti = getall ir mes ieškome šios vertės E2 kablelyje. Peržiūrėdamas šį diapazoną čia, paspausiu F4. F4 veikia kaip įprasta funkcija. Ir vėl pakreipdamas Matto klausimą iš kelio, dukart spustelėkite, kad jį nušautumėte. Tai veiks.
Ir pabandykime, pabandykime čia ką nors beprotiško. Padarykime 1 frazę ir tiesiog įdėkime krūva jų, pavyzdžiui, nuo 1 iki 10 frazių. Visa tai pasirašysime prie 109999. Įklijuokite ir tada įklijuokite čia. Nukopijuokite šią formulę žemyn, redaguokite formulę, kad ji, žinoma, eitų iki galo. Taip. Ir tai grąžins visas tas frazes. Gerai, tai yra mano sprendimas VBA, šiek tiek funkcijų. Mike, pažiūrėkime, ką tu turi.
Mike'as Girvinas: Ačiū. „GetAll“, tai yra nuostabi VBA funkcija. Gerai, aš eisiu prie čia pat esančio lapo. Aš jau konvertuojau jį į „Excel“ lentelę, kad, kai pridėsime įrašus žemiau, tikiuosi, viskas bus atnaujinta.
Pirmiausia tai darysiu iš dviejų dalių. Čia galėčiau padaryti formulę, kaip išgauti unikalų sąrašą, bet noriu pažvelgti į kitą variantą: Išplėstinis filtras turi išskirtinio sąrašo parinkties parinktį ir ją galima atnaujinti. Pabrėšiu tik ID stulpelio duomenis į „Advanced Filter“ arba naudosiu klaviatūrą „Alt“, „A“, „Q“. Dabar filtruokite sąrašą vietoje, jokiu būdu. Noriu nukopijuoti jį į kitą vietą. Jis gavo tik A stulpelį ir todėl, kad tai yra „Excel“ lentelė, kuri vėliau išsiplės. Neturiu jokių kriterijų, noriu nukopijuoti jį į D1 ir patikrinti tik unikalius įrašus. Spustelėkite Gerai.
Dabar eisiu čia, visi komentarai įeis ir naudosiu funkciją, kuri veikia tik „Excel 2016 Office 365“: = TEXTJOIN funkcija. Vien šią funkciją verta gauti naujausią „Excel“ versiją. Tai yra tokia dažna užduotis, kurią žmonės nori atlikti, daugelį dalykų sujungti. Dabar mūsų skiriamasis ženklas „,“ ir puikus šios funkcijos dalykas yra tai, kad galime tai pasakyti Ignoruoti tuščias langelius. Dabar galiu įdėti TRUE, 1 arba palikti, praleisti. Taigi, aš tai paliksiu, praleisk. Ir čia mums reikia mūsų teksto. Mes naudosime IF funkciją norėdami filtruoti ir gauti tik norimus daiktus. Aš pasakysiu, kad peržiūrėkite visą šį stulpelį čia: Lentelės pavadinimas ir () lauko pavadinime esate kas nors iš jūsų = ši santykinė langelio nuoroda, tai yra loginis testas. Jei norėčiau įvertinti tai spustelėjęs F9 klavišą,dabar galėjote pamatyti, kad mes turime tik 2 PATIESES, „Ctrl“ + Z dabar įvedu kablelį ir su „Trues and Falses“ masyvu, dabar galiu duoti jam išsirinktus daiktus. Taigi dabar iš šio asortimento išrinksime tik tuos daiktus, kurie čia turi TIESĄ. Kableliu ir noriu įsitikinti, kad įdėta „“ - tai bus tuščia langelis, atsižvelgiant į antrąjį argumentą TEXTJOIN.
Dabar aš uždarysiu skliaustus ir dabar IF funkcija sukurs tą „Trues and Falses“ eilutę. Faktiniai šio diapazono elementai bus paimti, jei jis pamatys, kad tai tiesa, ir visi kiti elementai turės tą tuščią langelį. Ir, spėk kas? „TEXTJOIN“ visiškai nepaisys visų tuščių langelių ir grąžins tik tuos elementus, kurie atitinka šį ID, ir sujungs jį su tuo skirikliu. Dabar tai tikrai masyvo formulė, kuriai reikalingas specialus klavišų paspaudimas „Ctrol“ + „Shift“ + „Enter“. Loginis bandymo argumentas turi mūsų masyvo operaciją ir šis argumentas negali tinkamai apskaičiuoti šios masyvo operacijos, nebent mes naudojame klaviatūrą Ctrl + Shift + Enter. Dabar uždarysiu skliaustus. Tiesą sakant, mes galėtume įrodyti 1 čia pat, 1-ame tekste, jei visa tai atlikčiau F9, pamatytume, kad gauname 2 elementus, likusios tuščios langelės bus ignoruojamos. „Ctrl“ + Z. Dabar teguls įveskite tai į langelį naudodami Ctrl + Shift + Enter. Nedelsdami pažvelkite į „Formulės“ juostą. Šie garbanoti skliaustai yra „Excel“, sakantys, kad tai suprato ir apskaičiavo kaip masyvo formulę. Dabar galiu dukart spustelėti ir išsiųsti. Tai atrodo gerai.
Eisiu į paskutinę langelį ir paspausiu F2, kad patikrinčiau, ar visi diapazonai atrodo teisingai. Dabar aš nenoriu daryti to, kad nenoriu paspausti Enter, nes ši formulė, kai mes ją įvedsime į Redagavimo režimą, teisingai apskaičiuos tik tada, jei naudosime Ctrl + Shift + Enter; arba, kadangi mes jau įvedėme formulę, galime tiesiog naudoti „Esc“ klavišą, kad grįžtumėte į bet kurį langelyje esantį elementą, prieš įvesdami jį į Redagavimo režimą.
Dabar išbandykime tai. Spustelėkite paskutinę langelį čia ir paspauskite Tab, tada įveskite naują ID, Tab, Tab. Dar vienas naujas įrašas „Tab“ ir jau matau, kad man čia neužteko darbo. Aš esu, mes ketiname įdėti - Perfect ir tada Enter. Dabar tai nebus automatiškai atnaujinama, pavyzdžiui, jei mes turime daugybę formulių, kad mes suskaičiuosime unikalius elementus ir tada išgausime unikalius elementus, bet ne problema. Žiūrėkite tai. Mes galime atnaujinti šį unikalių įrašų sąrašą, nes mes naudojome Išplėstinį filtrą ir nesvarbu, iš kurios langelio pradėsite, nes, kai iškviečiamas Išplėstinis filtras, jis įsimena ištraukos diapazoną ir diapazonus, į kuriuos iš pradžių žiūrėjo. Galite spustelėti Išplėstinis filtras arba naudoti klaviatūrą Alt + A + Q. Turime pasirinkti Kopijuoti į kitą vietą, bet pažiūrėkite į tai.Jis visiškai prisiminė ir išplėtė A13 dėl „Excel“ lentelės funkcijos. Tai prisiminė ekstraktų asortimentą. Aš turiu patikrinti tik unikalius įrašus, bet spustelėkite Gerai.
Dabar turiu ateiti ir nukopijuoti šią formulę žemyn. Eik, naudodamiesi „Advanced Filter“ ir nuostabiąja „TEXTJOIN“ funkcija, naudodami „Array“, galite gauti tik tuos daiktus, kurie atitinka. Gerai, mesk atgal.
Billas Jelenas: Ei, Mike, tai yra nuostabu. Gerai, apvyniokite šį epizodą. Aš naudojau VBA funkciją, vadinamą „GetAll“, ir mano unikalų sąrašą sukūrė „Remove Duplicates“, kuris yra daug lengvesnis nei „Advanced Filter“, tačiau problema yra tai, kad tai yra vienkartinis dalykas. Jis neprisimena ankstesnių nustatymų. Mike'as sukūrė savo unikalų sąrašą naudodamas Išplėstinį filtrą, o tai reiškia, kad vėliau jis galėjo perdaryti tą Išplėstinį filtrą nenurodydamas iš naujo įvesties ir ištraukimo diapazonų. Tada TEXTJOIN, graži nauja funkcija, pridėjo „Office 365“. Mike'as sako, kad vien tai yra priežastis įsigyti naujausią „Office“. Aš sakiau, kad TEXTJOIN pakeis gyvenimą. „TEXTJOIN“ yra nuostabu, nes jis gali tvarkyti masyvus.
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.
Ei, noriu padėkoti visiems, kad užsukote. Kitą kartą pamatysime kitą „Dueling Excel“ tinklalaidę iš „Excel Is Fun“.
Atsisiųsti failą
Failo pavyzdį atsisiųskite čia: „Duel183.xlsm“
