Ron želi napraviti hrpu VLOOKUP-ova i zbrojiti rezultate. Za ovaj problem postoji rješenje s jednom formulom.
Ron pita:
Kako možete zbrojiti sve VLOOKUP-ove, a da ne obavite svako pojedinačno pretraživanje?
Mnogi ljudi su upoznati sa:
=VLOOKUP(B4,Table,2,True)
Ako radite približnu verziju podudaranja VLOOKUP-a (gdje kao četvrti argument navodite True), također možete napraviti LOOKUP.
Pretraživanje je čudno jer vraća zadnji stupac u tablici. Ne odredite broj stupca. Ako se vaša tablica izvodi od E4 do J8, a želite rezultat iz stupca G, trebali biste odrediti E4: G4 kao tablicu pretraživanja.
Još jedna razlika: ne navodite True / False kao četvrti argument kao što biste to učinili u VLOOKUP-u: funkcija LOOKUP uvijek radi verziju Približnog podudaranja VLOOKUP-a.
Zašto se zamarati ovom drevnom funkcijom? Budući da Lookup ima poseban trik: možete pregledati sve vrijednosti odjednom i on će ih zbrojiti. Umjesto da kao prvi argument proslijedite jednu vrijednost kao što je B4, možete navesti sve svoje vrijednosti =LOOKUP(B4:B17,E4:F8). Budući da će to vratiti 14 različitih vrijednosti, morate umotati funkciju u funkciji omotač, kao što su =SUM( LOOKUP(B4:B17,E4:F8)), ili =COUNT(LOOKUP(B4:B17,E4:F8))ili =AVERAGE( LOOKUP(B4:B17,E4:F8)). Zapamtite, trebate funkciju omotača, ali ne i funkciju repera. =SNOOPDOGG(LOOKUP(B4:B17,E4:F8))neće raditi.
Doista je česta pogreška koju ćete htjeti izbjeći. Nakon upisivanja ili uređivanja formule, nemojte pritiskati Enter! Umjesto toga, napravite ovaj trik s tri prsta. Držite pritisnuto Ctrl i Shift. Dok držite Ctrl i Shift, pritisnite Enter. Sada možete otpustiti Ctrl i Shift. Ovo nazivamo Ctrl + Shift + Enter, ali stvarno mora biti točno tempirano: Pritisnite i držite Ctrl + Shift, pritisnite Enter, otpustite Ctrl + Shift. Ako ste to ispravno učinili, formula će se pojaviti na traci s formulama okružena vitičastim zagradama:(=SUM(LOOKUP(B4:B17,E4:F8)))
Bočna napomena
LOOKUP također može učiniti ekvivalent HLOOKUP-a. Ako je vaša tablica pretraživanja šira nego što je visoka, LOOKUP će se prebaciti na HLOOKUP. U slučaju izjednačenja … stola 8x8 ili 10x10, LOOKUP će stol tretirati kao okomit.
Pogledajte video u nastavku ili proučite ovu snimku zaslona.
Gledaj video
Prijepis videozapisa
Naučite Excel iz Podcasta, epizoda 2184: Zbroj svih pretraživanja.
Hej, dobrodošao natrag u prijenos uživo, ja sam Bill Jelen. Današnje pitanje Rona o korištenju starog, starog programa LOOKUP. A ovo je iz moje knjige Excel 2016 u dubini.
Recimo da smo ovdje imali gomilu proizvoda i morali smo koristiti tablicu pretraživanja. I za svaki proizvod morali smo, znate, dobiti vrijednost iz tablice pretraživanja i zbrojiti je. Pa, tipičan način je da za ovaj proizvod u ovoj tablici koristimo VLOOKUP-- = VLOOKUP. Pritisnut ću F4, zaključati to i s drugom vrijednošću. I u ovom konkretnom slučaju, jer se svaka pojedinačna vrijednost koju tražimo nalazi u tablici, a tablica je sortirana, sigurno je koristiti TRUE. Obično nikad ne bih koristio TRUE, ali u ovoj ćemo epizodi koristiti TRUE. Tako dobivam sve te vrijednosti i onda ovdje dolje, Alt + =, dobivamo ukupno njih, zar ne? Ali što ako nam je cijeli cilj samo dobiti 1130? Ne trebaju nam sve ove vrijednosti. Samo nam treba ovaj rezultat.
Pa, u redu, postoji stara, stara funkcija koja postoji od dana Visicala, a zove se LOOKUP. Ne VLOOKUP. Ne HLOOKUP, samo LOOKUP. Čini se da je isprva slično VLOOKUP - vi odredite koju vrijednost tražite i tablicu pretraživanja, pritisnite F4, ali onda smo gotovi. Ne moramo odrediti koji stupac jer LOOKUP samo ide do zadnjeg stupca u tablici. Ako imate tablicu sa sedam stupaca i želite potražiti četvrtu vrijednost, samo biste naveli stupce jedan do četiri. U redu? I, dakle, kakav god bio zadnji stupac, to će ono potražiti. I ne moramo navesti FALSE ili TRUE, jer uvijek koristi TRUE; ne postoji, LAŽNA verzija. U redu?
Dakle, morate razumjeti, ako radite VLOOKUP, na kraju uvijek koristim FALSE, ali u ovom slučaju to je uži popis - znamo da je sve na popisu u tablici. Ništa ne nedostaje, a tablica je sortirana. U redu? Dakle, ovo će nam dati potpuno isti rezultat koji imamo za VLOOKUP.
Sjajno, želim ovo kopirati: Alt + =. U redu. Ali to nam ništa ne kupuje, jer još uvijek moramo staviti sve formule, a zatim funkciju SUM. Lijepa stvar je da LOOKUP može napraviti trik koji VLOOKUP ne može, u redu? A to je raditi sve pretrage odjednom. Dakle, tamo gdje to pošaljem funkciji SUM, kad kažem LOOKUP, Što je stavka pretraživanja? Želimo potražiti sve te stvari, zarez, a onda je tablica - i ne moramo pritisnuti F4, jer ovo nećemo nigdje kopirati, postoji samo jedna formula - zatvorite LOOKUP, zatvorite zbroj.
U redu, evo mjesta na kojem se stvari mogu zeznuti: Ako ovdje jednostavno pritisnete Enter, dobit ćete 60, u redu? Jer samo će ići prva. Ono što morate učiniti je zadržati tri magična pritiska tipke, a ovo je Ctrl + Shift - lijevom rukom držim Ctrl + shift, držim ih pritisnute, a desnom pritisnite ENTER i napravit će svu matematiku VLOOKUP-a. Nije li to strašno? Obavijest na traci s formulama ovdje gore ili u tekstu formule stavlja kovrčave zagrade oko sebe. Ne ukucate te kovrčave zagrade, Excel ih stavlja, kako bi rekao: "Hej, za ovo ste pritisnuli Ctrl + Shift + Enter."
Eto, ova tema i puno drugih tema nalaze se u ovoj knjizi: Power Excel with, izdanje iz 2017. godine. Kliknite ono "I" gore u gornjem desnom kutu da biste pročitali više o knjizi.
Danas, pitanje od Rona: Kako možete zbrojiti sve VLOOKUP-ove? Sada većina ljudi zna VLOOKUP gdje odredite vrijednost pretraživanja, tablicu, koji stupac, a zatim TRUE ili FALSE. A ako radite - ako se kvalificirate za - ISTINITU verziju VLOOKUP-a. onda možete i ovaj stari LOOKUP. Čudno je, jer vraća zadnji stupac tablice, ne navodite broj stupca i ne kažete TRUE ili FALSE. To je uvijek ISTINA. Zašto bismo ovo koristili? Budući da ima poseban trik: možete napraviti sve vrijednosti pretraživanja odjednom i zbrojit će ih. Nakon unosa te formule morate pritisnuti Ctrl + Shift + Enter ili neće uspjeti. A onda ću vam na izlazu pokazati još jedan trik za LOOKUP.
Pa, hej, želim zahvaliti Ronu što je poslao to pitanje i želim vam zahvaliti što ste navratili. Vidimo se sljedeći put za još jedan prijenos od.
U redu, dok ovdje govorimo o LOOKUP-u, druga stvar koju LOOKUP može učiniti: Znate, imamo VLOOKUP za vertikalnu tablicu poput ove ili HLOOKUP za horizontalnu tablicu poput ove; LOOKUP može ići u bilo kojem smjeru. tako da možemo reći hej želimo = LOOKUP ovu vrijednost, D, u ovoj tablici, a budući da je tablica šira nego što je visoka, LOOKUP se automatski prebacuje na verziju HLOOKUP, zar ne? Dakle, u ovom slučaju, jer specificiramo 3 retka s 5 stupaca, to će obaviti HLOOKUP. A budući da su ovdje posljednji redak brojevi, donijet će nam taj broj. Dakle, imamo D, Date, dobiva 60. U redu. Ako bih odredio tablicu koja je išla samo u 12. red, tada ću umjesto toga dobiti naziv proizvoda. U redu? To je nekako zanimljiva mala funkcija. Mislim da je Excel pomoć znao reći: "Hej, nemoj koristiti ovu funkciju", ali tamo 's određena vremena u kojima možete koristiti ovu funkciju.
Naslovna fotografija: grandcanyonstate / pixabay
