VLOOKUP on võimas funktsioon. Kuid sageli saan ühel oma Power Exceli seminaril küsimuse kelleltki, kes soovib teada, kas VLOOKUP suudab kõik vastavad väärtused tagastada. Nagu teate, tagastab VLOOKUP koos vale kui neljanda argumendiga alati esimese leitud vaste. Järgmisel ekraanipildil tagastab lahter F2 3623, kuna see on esimene tööle J1199 leitud vaste.
Küsimus, kas VLOOKUP võib siis kõik kohtumised tagasi anda?
VLOOKUP ei tee seda. Kuid muud funktsioonid saavad.
Kui soovite kokku kõik kulud töökohtade J1199, siis oleks kasutada =SUMIFS($B$2:$B$53,$A$2:$A$53,G2),
Kui teil on tekstiväärtusi ja soovite ühendada kõik tulemused ühe väärtusega, saate seda kasutada =TEXTJOIN(", ",TRUE,IF($A$2:$A$53=G2,$C$2:$C$53,"")). See valem töötab ainult Office 365 ja Excel 2019-s.
Või peate võib-olla tagastama kõik ühe töö tulemused kõik töölehe uude vahemikku. =FILTER(B2:C53,A2:A53=K1,"None Found")2019. aastal Office 365 saabuv uhiuus funktsioon lahendab probleemi:
Mõnikord tahavad inimesed kõik VLOOKUP-id läbi viia ja need kokku võtta. Kui teie otsingutabel on sorteeritud, võite seda kasutada =SUM(LOOKUP(B2:B53,M3:N5)).
Kui peate kokku võtma kõik VLOOKUP-id VLOOKUP-i täpse vaste versiooniga, peab teil kasutamiseks olema juurdepääs dünaamilistele massiividele =SUM(VLOOKUP(B2:B53,M3:N5,2,TRUE)).
Dünaamiliste massiivide kohta lisateabe saamiseks vaadake Exceli dünaamilisi massiive otse punkti.
Vaata videot
Video ärakiri
Learn Excel From, Podcast Episode 2247: Kas saate tagastada kõik VLookUpi väärtused?
Hei. Tere tulemast tagasi netisaate juurde. Olen Bill Jelen. Minu eelmisel nädalal Wisconsinis Appletonis toimunud seminaril kerkis üles kaks küsimust - mõlemad on seotud. Nad ütlesid, et hei, kuidas me kõik VLOOKUPid tagastame, eks? Sel juhul on sarnaselt J1199-ga hunnik vasteid ja nad tahavad neid kõiki tagastada ja minu esimene küsimus, kui keegi minult seda küsib, on noh, mida sa tahad tikkudega teha? Kas need on numbrid, mille soovite kokku liita, või on see tekst, mida soovite liita? Ja see on naljakas. Kaks sama seminari küsimust soovisid üks inimene need kokku liita ja teine soovis tulemusi liita.
Nii et vaatame neid mõlemaid. YouTube'i kirjelduses leiate sisukorra, kus saate teksti tulemuse nägemiseks teise juurde hüpata.
Okei, nii et esimese asjana, kui me tahame need kõik kokku liita, ei hakka me VLOOKUPi üldse kasutama. Kasutame funktsiooni SUMIF või SUMIFS, mis võtab kokku kõik, mis sellele üksusele sobib. Niisiis, SUMIFS. Siin on arvväärtused, mida me summeerida tahame ja selle lukustamiseks vajutan klahvi F4. Sel moel, kui ma selle alla kopeerin, osutab see pidevalt samale vahemikule ja siis tahame minna kontrollima, kas veeru A JOB-number, jälle seal F4, on = meist vasakul oleval väärtusel - antud juhul E2 - ja kui me selle alla kopeerime, näeme iga üksuse KOKKU. (SUMIFS ($ B $ 2: $ B $ 53, $ A $ 2: $ A $ 53, E2))
Teeme siin lihtsalt väikese kontrolli. J1199. Kokku on 25365. Hästi. Nii et see töötab. Kui see on numbreid ja soovite kõik numbrid hankida ja need kokku liita, minge üle summadele SUMIF või SUMIFS, kuid kui see on tekst, olgu, siis on see funktsioon 2017. aasta veebruaris Office 365-s uus. Nii et kui teil on Excel 2016 või Excel 2013 või Excel 2010 või mõni neist vanematest, teil seda funktsiooni pole. See on funktsioon nimega TEXTJOIN. TEXTJOIN. See on veel üks funktsioon kasutajalt (Joe McDade - 01:50), kes tõi meile just kõik need suurepärased dünaamilise massiivi valemid 2018. aastal Ignite'i ja Joe hoolitses selle eest, et TEXTJOIN töötaks massiividega, mis on tõesti suurepärane.
Niisiis, piiritleja siin saab olema, SPACE, ignoreeri kindlasti TÜHJUST. Tahame siinkohal ignoreerida TÜHJUST, sest selles järgmises osas, IF-i avalduses, loome palju tühjendusi. KUI see üksus A2, F4 kohal on = sellele JOB-numbrile siin, siis ma tahan vastavat üksust veerust C, F4, muidu tahan sellist "". Sulgege see IF-lause. Sulgege TEXTJOIN. Kas ma pean vajutama CONTROL + SHIFT + ENTER? Ei ma ei tee. See toob mulle kõik tooted, mis niimoodi sobivad, eks? Nii et tagastades kõik VLOOKUPid, kui tahame need kokku võtta, siis jah, kui tahame neid liita, jah. (= TEXTJOIN (“,”, tõsi, KUI ($ A $ 2: $ A $ 53 = E2, $ C $ 2: $ C $ 53, “”))
Hästi, nüüd on siin veel üks võimalus, kui inimesed küsivad minult, kas nad saavad kõik VLOOKUP-id tagastada. See võib olla probleem, kus me tahame siin kõik need kulud üles otsida, välja selgitada KÄITLUSKULUD ja need siis kõik kokku võtta. Nagu ma ei taha siia panna VLOOKUPi ja siia VLOOKUPi ning siia VLOOKUPi ja siia VLOOKUPi. Ma tahan neid lihtsalt teha ja sel juhul kasutame funktsiooni SUM ja siis vana, vana funktsiooni LOOKUP. LOOKUP ütleb, et otsime kõik need väärtused veerust B. Ma ei vaja siin F4, sest ma ei kopeeri seda kuhugi. ,. Siin on meie otsingu tabel. ), sulgege SUM, see kustub ja teeb iga üksiku VLOOKUPi ning võtab need siis kõik kokku. (= SUMMA (VAATAMINE (B2: B53, K3: L5)))
Noh, hei. Kõik need teemad on minu raamat LIV: 54 kõigi aegade suurimat näpunäidet. Lisateabe saamiseks klõpsake paremas ülanurgas nuppu i.
Seega on küsimus, kas saate kõik VLOOKUPid tagastada? Noh, omamoodi, kuid tegelikult ei kasuta VLOOKUPi. Selle lahendamiseks kasutame kas SUMIF, TEXTJOIN või SUM või LOOKUP.
Noh, hei. Tahan teid tänada, et peatusite. Järgmisel korral näeme järgmise võrguülekande jaoks.
Tead, olgu, ma olen juba nädal aega rääkinud nendest dünaamilistest massiividest. Tahtsin teha ühe video, kus ma dünaamilisi massiive ei puudutanud, sest tean, et paljudel inimestel neid veel pole, aga siin me oleme. See on väljund. Teate, need pole tähestikulised. See oleks nii palju parem, kui saaksime need sorteerida ja kui teil juhtub olema uusi dünaamilisi massiive, võite selle saata SORT-funktsiooni, niimoodi SORT-i ja vajutada sisestusklahvi (ENTER) ning nüüd sorteeritakse tulemused niimoodi.
Teate, ka see valem võib dünaamiliste massiividega paremaks saada. Otsimiseks peate kasutama valikut TRUE. Mis siis, kui soovite kasutada valet FALSE? Võiksime selle muuta VLOOKUPiks, otsida kogu see tekst sellesse tabelisse, 2,. Sel juhul kavatsen kasutada TRUE, kuid teisel juhul võite kasutada FALSE. CONTROL + SHIFT + ENTER. Ei. See lihtsalt töötab, olgu? (= SUMMA (VLOOKUP (B2: B53, K3: L5,2, True)))
2019. aasta alguses ilmuvad dünaamilised massiivid lahendavad nii mõnegi probleemi.
Täname, et veetsite siin tegutsemise kaudu aega. Järgmisel korral näeme järgmise võrguülekande jaoks.
Laadige alla Exceli fail
Exceli faili allalaadimiseks: saate-tagasi-tagasi-kõik-vlookup-väärtused.xlsx
Kui keegi küsib: "Kas VLOOKUP võib kõik mängud tagasi anda, on vastus Ei. Kuid on palju muid funktsioone, mis suudavad sisuliselt sama teha.
Exceli päeva mõte
Olen küsinud oma Exceli meistri sõpradelt Exceli kohta nõu. Tänane mõte mõelda:
"Normaliseerige oma andmed nii, nagu teistel oleks nende eest teie andmeid normaliseerida"
Kevin Lehrbass
