VLOOKUP je moćna funkcija. Ali na jednom od svojih Power Excel seminara često dobijem pitanje od nekoga tko želi znati može li VLOOKUP vratiti sve odgovarajuće vrijednosti. Kao što znate, VLOOKUP s False kao četvrtim argumentom uvijek će vratiti prvo podudaranje koje pronađe. Na sljedećem snimku zaslona ćelija F2 vraća 3623 jer je to prvo pronađeno podudaranje za posao J1199.
Pitanje je, dakle, može li VLOOKUP vratiti sve utakmice?
VLOOKUP neće. Ali druge funkcije mogu.
Ako želite zbrojiti sve troškove iz posla J1199, koristili biste =SUMIFS($B$2:$B$53,$A$2:$A$53,G2),
Ako imate tekstualne vrijednosti i želite spojiti sve rezultate u jednu vrijednost, možete koristiti =TEXTJOIN(", ",TRUE,IF($A$2:$A$53=G2,$C$2:$C$53,"")). Ova formula djeluje samo u sustavu Office 365 i Excel 2019.
Ili ćete možda trebati vratiti sve rezultate za jedan posao u novi raspon radnog lista. Potpuno nova =FILTER(B2:C53,A2:A53=K1,"None Found")funkcija koja dolazi u Office 365 2019. riješit će problem:
Ponekad ljudi žele izvesti sve VLOOKUP-ove i zbrojiti ih. Ako je vaša tablica pretraživanja sortirana, mogli biste koristiti =SUM(LOOKUP(B2:B53,M3:N5)).
Ako trebate zbrojiti sve VLOOKUP-ove s verzijom Exact Match VLOOKUP-a, morat ćete imati pristup Dynamic Arrays da biste ih mogli koristiti =SUM(VLOOKUP(B2:B53,M3:N5,2,TRUE)).
Da biste saznali više o dinamičkim nizovima, pogledajte Excel dinamičke nizove ravno do točke.
Gledaj video
Prijepis videozapisa
Naučite Excel iz, Podcast epizoda 2247: Možete li vratiti sve VLookUp vrijednosti?
Hej. Dobrodošli natrag na netcast. Ja sam Bill Jelen. Dva su se pitanja pojavila na mom seminaru u Appletonu u Wisconsinu prošlog tjedna - oba su se odnosila. Rekli su, hej, kako da vratimo sve VLOOKUP-ove, u redu? U ovom slučaju, poput J1199 ima gomilu šibica i oni ih, znate, žele vratiti sve, a moje prvo pitanje kad god me netko pita ovo je, pa, što želite učiniti sa šibicama? Jesu li to brojevi koje želite zbrojiti ili je to tekst koji želite spojiti? I to je smiješno. Dva pitanja na istom seminaru, jedna ih je osoba željela zbrojiti, a druga je htjela povezati rezultate.
Pa pogledajmo obje. U opisu YouTubea potražite sadržaj u kojem možete prijeći na drugi ako želite vidjeti rezultat teksta.
U redu, dakle, prvo, ako ih želimo zbrojiti, uopće nećemo koristiti VLOOKUP. Koristit ćemo funkciju zvanu SUMIF ili SUMIFS koja će sažeti sve što se podudara s ovom stavkom. Dakle, SUMIFIRAJ. Evo numeričkih vrijednosti koje želimo zbrojiti, a ja ću pritisnuti F4 da to zaključam. Na taj način, dok kopiram ovo, nastavit će pokazivati na isti raspon, a zatim želimo provjeriti i vidjeti je li JOB broj u stupcu A, opet tamo F4, = = vrijednost lijevo od nas - u ovom slučaju E2 - i dok to kopiramo, vidjet ćemo UKUPNO za svaku stavku. (SUMIFI ($ B $ 2: $ B $ 53, $ A $ 2: $ A $ 53, E2))
Idemo samo malo provjeriti ovdje. J1199. Ukupno je 25365. U redu. Dakle, to djeluje. Ako se radi o brojevima i želite dobiti sve brojeve i zbrojiti ih, prebacite se na SUMIF ili SUMIFS, ali ako je to tekst, u redu, sada je ova funkcija nova u sustavu Office 365 u veljači 2017. Dakle, ako imate Excel 2016 ili Excel 2013 ili Excel 2010 ili bilo koji od onih starijih, nećete imati ovu funkciju. To je funkcija koja se naziva TEXTJOIN. TEKSTJON. Ovo je još jedna funkcija (Joe McDade - 01:50) koji nam je upravo donio sve one sjajne formule dinamičkih nizova na Igniteu 2018. godine, a Joe se pobrinuo da TEXTJOIN radi s nizovima, što je stvarno sjajno.
Dakle, graničnik ovdje će biti, PROSTOR, definitivno ignoriraj PRAZNO. Ovdje želimo zanemariti EMPTY jer ćemo generirati puno praznina u ovom sljedećem dijelu, IF izjavi. AKO je ta stavka iznad A2, F4, ovdje = broj ovog JOB-a, tada želim odgovarajuću stavku iz stupca C, F4, inače želim "" tako. Zatvorite tu izjavu IF. Zatvorite TEKSTJOIN. Moram li pritisnuti CONTROL + SHIFT + ENTER? Ne ja ne. Donosi mi sve proizvode koji se tako poklapaju, u redu? Dakle, vraćanje svih VLOOKUP-ova, ako ih želimo zbrojiti, da, ako ih želimo spojiti, da. (= TEKSTJOIN (",", Tačno, AKO ($ A $ 2: $ A $ 53 = E2, $ C $ 2: $ C $ 53, "")))
U redu, postoji još jedna mogućnost ovdje kada me ljudi pitaju mogu li vratiti sve VLOOKUP-ove. To bi mogao biti problem kada ovdje želimo potražiti svaki od ovih troškova i utvrditi TROŠKOVE RUKOVANJA, a zatim ih zbrojiti. Kao, ne želim ovdje staviti VLOOKUP i VLOOKUP ovdje i VLOOKUP ovdje i VLOOKUP ovdje. Samo ih želim sve raditi i, u tom ćemo slučaju koristiti funkciju SUM, a zatim staru, staru funkciju LOOKUP. LOOKUP kaže da ćemo sve ove vrijednosti potražiti u stupcu B. Ovdje mi ne treba F4 jer ga nigdje ne kopiram. ,. Evo naše tablice pretraživanja. ), zatvorite SUM, a on se gasi i radi svaki pojedini VLOOKUP, a zatim ih sve tako sumira. (= ZBOR (PREGLED (B2: B53, K3: L5)))
Pa, hej. Sve su ove teme moja knjiga LIV: 54 najveća savjeta svih vremena. Kliknite i u gornjem desnom kutu da biste saznali više.
Dakle, pitanje je možete li vratiti sve VLOOKUP-ove? Pa, nekako, ali zapravo ne koristim VLOOKUP. Ili ćemo upotrijebiti SUMIF, TEXTJOIN ili SUM ili LOOKUP da bismo to riješili.
Pa, hej. Želim vam zahvaliti što ste navratili. Vidimo se sljedeći put za još jedan prijenos od.
Znate, dobro, pričam o tim dinamičkim nizovima već tjedan dana. Želio sam napraviti jedan video u kojem nisam dirao dinamičke nizove jer znam da ih mnogi ljudi još nemaju, ali tu smo. To je izlaz. Znate, ovo nije abecedno. To bi bilo puno bolje kad bismo ih mogli razvrstati, a ako slučajno imate nove dinamičke nizove, možete ovo poslati u funkciju SORT, SORT ovako i pritisnuti ENTER, a sada će se rezultati tako sortirati.
Znate, čak bi i ova formula mogla postati bolja s dinamičkim nizovima. Za pretraživanje je potrebno upotrijebiti, TRUE. Što ako želite koristiti FALSE? To bismo mogli promijeniti u VLOOKUP, potražiti sav ovaj tekst u toj tablici, 2,. U ovom slučaju koristit ću TRUE, ali u drugom slučaju možete koristiti FALSE. CONTROL + SHIFT + ENTER. Ne. Samo će uspjeti, u redu? (= ZBIR (PREGLED (B2: B53, K3: L5,2, Tačno)))
Dinamični nizovi koji izlaze početkom 2019. riješit će toliko problema.
Hvala na druženju ovdje. Vidimo se sljedeći put za još jedan prijenos od.
Preuzmite datoteku Excel
Da biste preuzeli excel datoteku: can-you-return-all-vlookup-values.xlsx
Kad netko pita "Može li VLOOKUP vratiti sve podudarnosti, odgovor je Ne. Ali, postoje mnoge druge funkcije koje u osnovi mogu učiniti isto.
Excel misao dana
Pitao sam svoje prijatelje Excel Master za savjet o Excelu. Današnja misao za razmišljanje:
"Normalizirajte svoje podatke kao što biste htjeli da drugi normaliziraju vaše podatke umjesto vas"
Kevin Lehrbass
