Kas Excel VLOOKUP saab kõik tulemused tagastada ja ühendada nende vahel komaga?
Vaata videot
- Eesmärk on liita kõik VLOOKUPi tekstivastused
- Billi meetod: kasutage VBA-funktsiooni nimega GetAll
- Unikaalne loend, kasutades käsku Eemalda duplikaadid
- Mike'i meetod:
- Unikaalne loend, kasutades täpsemat filtrit
- Funktsioon TEXTJOIN lisati Office 365-sse
TEXTJOIN(", ",,IF(OilChangeData(ID)=D2,OilChangeData(Comment),""))- Funktsiooni IF tõttu nõuab valem Ctrl + Shift + Enter alati, kui valemit muudate
- Alt AQOR Enter käivitab täpsema filtri uuesti!
Video ärakiri
Episood 183: Liitu kõigi VLOOKUP-i matšidega
Bill Jelen: Hei, tere tulemast tagasi. Aeg on veel üks Dueling Exceli podcast. Ma olen Bill Jelen, minuga liitub Mike Girvin teenusest Excel Is Fun. See on meie osa 183: Liitu kõigi VLOOKUP-i matšidega.
(Muusika)
Hea küll, tänane küsimus Mattilt. Kas VLOOKUP suudab kõik tulemused tagastada ja ühendada nende vahel koma tühimikuga. Näiteks 109876, mis on need kaks siin, kas see tagastab madala õlisisaldusega komakoha Kontrollitud 12/12. Ja muidugi, kui neid oleks rohkem, tagastaks see rohkem. Hästi, nii et minu siinses lahenduses kasutatakse mõnda VBA-d. Hea küll, seega veenduge, et see oleks salvestatud kui xlsm või te ei saa käivitada VBA ega xlsb, kuid mitte xlsx - xlsx on üks fail, mis ei saa VBA-d käitada. Vajutame klahve Alt + F11, veenduge, et kasutaksite Dual183 või mis iganes teie töövihiku nimi on. Sisestage moodul tühja moodulisse ja kleepime selle koodi, olgu.
Vaatame seda funktsiooni GetAll ja siin on ID-number, mida otsime, ja seejärel vahemik, mida soovime vaadata. Ja alustame, tagastame muutuja nimega GetAll, nii et alustame sellega, et see on võrdne tühja tühjaga. Minu vahemiku iga lahtri jaoks võtame võtme GetAll = GetAll & "" ja seejärel lahtri.Offset (0 rida, 1 veerg), teisisõnu väärtuse see on lihtsalt selle ID-numbri kõrval, sest tagasi VBA-s on siin ID-number. Kui leiame sobiva ID-numbri, tahame minna üle ühe veeru. Mis siis, kui soovite minna 2 veergu üle või 3 veergu üle, siis muudate selle 0 rea ja ühe veeru väärtuseks 2. Hea. Kontrollige ka, kas - me ei pane koma tühikut, kui see on esimene.Nii et kui muutuja GetAll on praegu „”, siis me ei pane komakohta, olgu?
Nii et nüüd, kui meil on see funktsioon siin, vaadake, kui lihtne on Matti probleemi lahendada. Me tuleme siia ja võtame tema ID-d, Ctrl + C ja kleepime Ctrl + V niimoodi. Andmed, eemaldage duplikaadid, klõpsake nuppu OK. Nii et seal on ainulaadne ID-de loend ja siis tahame öelda = getall ja otsime seda väärtust E2 koma. Siit vahemikust läbi vaadates vajutan klahvi F4. F4 töötab täpselt nagu tavaline funktsioon. Ja liikudes uuesti Matti küsimusest kõrvale, topeltklõpsake, et see maha lasta. See töötab.
Ja proovime lihtsalt, proovime siin midagi hullu. Teeme fraasi 1 ja paneme lihtsalt hulga neid nagu fraasid 1 kuni 10. Allkirjastame need kõik numbrile 109999. Kleepige ja kleepige siis siia. Kopeerige see valem alla, muutke valemit, nii et see läheb loomulikult lõpuni. Jep. Ja see tagastab kõik need fraasid. Hästi, nii et see on minu lahendus, VBA, väike funktsioon seal. Mike, vaatame, mis sul on.
Mike Girvin: aitäh ,. GetAll, see on vinge VBA funktsioon. Hästi, lähen siinsamas lehe juurde. Ma olen selle juba Exceli tabeliks teisendanud, nii et kui allpool kirjeid lisame, loodetavasti asjad uuenevad.
Nüüd esimese asjana kavatsen seda teha kahes osas. Ma saaksin siin teha valemi ainulaadse loendi väljavõtmiseks, kuid ma tahan vaadata veel ühte võimalust: Täpsemal filtril on väljavõtte ainulaadne loend ja seda saab värskendada. Esiletõstan ainult ID-veeru andmed, täpsema filtri juurde või kasutan klaviatuuri Alt, A, Q. Nüüd filtreerige loend kohapeal, mitte mingil juhul. Ma tahan selle kopeerida teise asukohta. See sai lihtsalt veeru A ja kuna see on Exceli tabel, mida hiljem laiendatakse. Mul pole ühtegi kriteeriumi, ma tahan selle kopeerida D1-sse ja kontrollida ainult ainulaadseid kirjeid. Klõpsake nuppu OK.
Nüüd tulen siia, kõik kommentaarid sisestatakse ja hakkan kasutama funktsiooni, mis töötab ainult funktsioonis Excel 2016 Office 365: = TEXTJOIN. Juba see funktsioon on väärt Exceli uusima versiooni hankimist. See on nii tavaline ülesanne, et inimesed tahavad teha, ühendada paljusid asju omavahel. Nüüd on meie piiritleja “,” ja selle funktsiooni suurepärane külg on see, et võime seda öelda tühjade lahtrite eiramiseks. Nüüd saan panna TRUE, 1 või Jäta, jäta vahele. Niisiis, ma jätan selle maha, jäta see ära. Ja siin on meil vaja oma teksti. Kasutame IF-funktsiooni, et välja filtreerida ja hankida just soovitud üksused. Ma ütlen, et vaadake siin kogu see veerg läbi: kas tabeli nimi ja seejärel välja () väljanimes on keegi teist = selle suhtelise lahtriviite jaoks, see on loogiline test. Kui klõpsaksin seda ja vajutaksin hindamiseks klahvi F9,näete praegu, et meil on ainult 2 TÕELISUST, Ctrl + Z kirjutan nüüd koma ja koos hulga Trues and Falses'iga saan nüüd anda talle esemed välja valida. Nii et nüüd valime sellest vahemikust välja ainult need üksused, millel on siin TÕSI. Koma ja tahame kindlasti panna "" - see kuvatakse TEXTJOINi teise argumendi osas tühja lahtrina.
Nüüd sulgen sulgud ja nüüd loob funktsioon IF selle Trues and Falses stringi. Selle vahemiku tegelikud elemendid tõstetakse üles, kui see näeb välja True ja kõigil teistel üksustel on see tühi lahter. Ja arva ära mis? TEXTJOIN ignoreerib täielikult kõiki neid tühje lahtrid ja tagastab ainult elemendid, mis vastavad sellele ID-le, ja liidetakse seejärel selle eraldajaga. Nüüd on see kindlasti massiivi valem, mis nõuab spetsiaalset klahvivajutust Ctrol + Tõst + Enter. Loogiline testargument hoiab meie massiivi operatsiooni ja see argument ei saa seda massiivi toimingut õigesti arvutada, kui me ei kasuta klaviatuuri Ctrl + Tõst + Enter. Nüüd sulgen sulgud. Tegelikult võiksime siin tekstis 1 tõestada ühe, kui ma seda kõike F9 näen, näeme, et saame 2 üksust, ülejäänud neid tühje lahtrit ignoreeritakse. Ctrl + Z. Nüüd lasemes sisestage see lahtrisse klahvikombinatsiooniga Ctrl + Tõst + Enter. Otse koheselt tõstke üles Vormelibaar. Need lokkis sulud on Excel, mis ütleb teile, et ta mõistis seda ja arvutas selle massiivi valemina. Nüüd saan topeltklõpsata ja alla saata. See näeb hea välja.
Lähen viimasesse lahtrisse ja vajutan klahvi F2, et kontrollida, kas kõik vahemikud näevad õigesti. Nüüd, mida ma ei taha teha, on see, et ma ei taha Enteri vajutada, kuna see valem pärast seda, kui oleme selle redigeerimisrežiimi pannud, arvutab õigesti ainult siis, kui kasutame klahve Ctrl + Tõst + Enter; või kuna valem on juba sisestatud, saame klahvi Esc abil lihtsalt tagasi raku mis tahes lahtrisse naasta, enne kui paneme selle redigeerimisrežiimi.
Nüüd proovime seda. Klõpsan siin all olevas viimases lahtris ja vajutan tabulaatorit ning tippin siis uue ID, vahelehe, vahelehe Veel üks uus plaat Tab ja ma näen juba, et mul polnud siin piisavalt tööd. Olen, paneme - Perfect ja siis Enter. Nüüd ei hakka seda automaatselt värskendama, näiteks kui meil on hulk valemeid, mille järgi loendame unikaalseid üksusi ja seejärel unikaalsed üksused, kuid pole probleemi. Vaadake seda. Saame seda unikaalsete kirjete loendit värskendada, kuna kasutasime täpsemat filtrit ja pole ka vahet, millisest lahtrist alustate, sest kui täiustatud filtrit käivitada, jätab see meelde ekstrakti vahemiku ja vahemikud, mida ta algselt vaatas. Võite klõpsata nupul Täpsem filter või kasutada klaviatuuri Alt + A + Q. Peame valima Kopeeri teise asukohta, kuid vaadake seda.Exceli tabeli funktsiooni tõttu jäi see täielikult meelde ja laienes A13-le. See mäletas ekstrakti valikut. Pean kontrollima ainult ainulaadseid kirjeid, kuid klõpsake nuppu OK.
Nüüd pean tulema üle ja selle valemi alla kopeerima. Ja sealt lähete, kasutades massiivi režiimis Advanced Filter ja hämmastavat funktsiooni TEXTJOIN, et saada just sobivad üksused. Hästi, viska tagasi.
Bill Jelen: Hei, Mike, see on vinge. Hästi, pakkisin selle episoodi kokku. Ma kasutasin funktsiooni VBA nimega GetAll ja minu ainulaadse loendi lõi duplikaatide eemaldamine, mis on palju lihtsam kui täpsem filter, kuid probleem on see, et see on ühekordne asi. Varasemaid seadeid see ei mäleta. Mike lõi oma ainulaadse loendi, kasutades täiustatud filtrit, mis tähendab, et ta saaks selle täiustatud filtri hiljem uuesti teha, ilma et sisendivahemikku ja väljavõtte vahemikku uuesti täpsustaks. Ja siis lisas uus ilus funktsioon TEXTJOIN Office 365. Mike sõnul on ainuüksi põhjus uusima Office hankimiseks. Ma ütlesin, et TEXTJOIN muudab elu. TEXTJOIN on vinge, sest see saab massiividega hakkama.
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 hei, ma tahan tänada kõiki peatumast. Järgmisel korral näeme veel Dueling Exceli taskuhäälingusaates ja Excel on lõbus.
Laadige fail alla
Laadige näidisfail alla siit: Duel183.xlsm
