Više VLOOKUP - Pretražujte po kriterijima

Imate ove opcije

Najbrži i najbolji način traženja određenih kriterija je upotreba funkcije VLOOKUP. Pomoću nje moguće je pronaći vrijednost u Microsoft Excelu u milisekundama.

VLOOKUP - ovako radi s kriterijem pretraživanja

Da bi razumjeli kako VLOOKUP radi s više kriterija pretraživanja, korisnici programa Excel prvo moraju razumjeti kako je funkcija VLOOKUP u osnovi strukturirana. Sljedeći primjer prikazuje broj privremenih radnika zaposlenih u poduzeću u različitim mjesecima kalendarske godine.

VLOOKUP bi se trebao koristiti za prikaz koliko je privremenih radnika zaposleno u ožujku i prosincu.

Formula VLOOKUP u primjeru za prosinac je:

VLOOKUP (D8, A: B, 2, 0)

Formula traži odgovarajuće brojeve privremenih radnika na sljedeći način:

1. Pronađite vrijednost D8 (mjesec prosinac) u stupcima A i B.
2. Pronađite vrijednost stupca B. To je predstavljeno s 2 u formuli.
3. 0 u formuli označava da treba tražiti točnu vrijednost.

Funkcija VLOOKUP traži jedan kriterij pretraživanja. Matrica formule je područje u kojem se nalaze relevantne vrijednosti. Osim toga, stupac VLOOKUP mora sadržavati vrijednost koju tražite.

Važno:

S funkcijom VLOOKUP kriterij pretraživanja uvijek mora biti u stupcu 1. Pridružene vrijednosti koje se traže također se moraju prikazati desno od kriterija pretraživanja. Ako je proračunska tablica drugačije strukturirana, VLOOKUP neće raditi. U ovom slučaju, kombinacija funkcija INDEX i COMPARE može biti korisna u stvaranju željenog upita.

VLOOKUP s više kriterija pretraživanja - kako integrirati IF funkciju

U gore prikazanom primjeru postavljeno je pitanje koliko je privremenih radnika radilo u poduzeću u određenom mjesecu. Pomoću funkcije VLOOKUP lako se mogu odrediti određene vrijednosti. U sljedećem primjeru možete vidjeti opsežan popis članaka sa 36 unosa [1], iz koje želite brzo i jednostavno filtrirati prikladne brojeve članaka na temelju nekoliko kriterija.

Kriteriji pretraživanja navedeni su u proračunskoj tablici s desne strane. Pronađeni broj članka trebao bi se pojaviti u ćeliji H8.

Prvi korak je uključivanje formule s VLOOKUP -om pomoću koje možete pronaći broj stavke na temelju kriterija pretraživanja. Zatim ovu formulu korak po korak proširujete za ostale kriterije pretraživanja.

Pomoću sljedeće formule u ćeliji H8 pronađite broj stavke za veličinu iz ćelije H3: [2]

= VLOOKUP (H3, A3: E40, 5

Formula traži odgovarajući broj članka na sljedeći način:

1. Nađite vrijednost H3 (veličina 142) u stupcima A do E.
2. Vrijednost pronađite u stupcu E (broj članka). To je predstavljeno 5 u formuli.

Ukratko, funkcija VLOOKUP prenosi prvi popis u kojem tražite sadržaj kao prvi argument. Kriterij pretraživanja koji se traži u prvom stupcu prenesenog područja fokusiran je kao drugi argument. Treći argument definira stupac iz kojega se vraća rezultat.

Pošaljite popis razvrstan uzlaznim redoslijedom na temelju prvog stupca kako bi Excel mogao pronaći sljedeću nižu vrijednost. Formula nalazi broj artikla 2.253 iz ćelije E16. Budući da se veličina 142 iz ćelije H3 ne pojavljuje na popisu, pronađena je sljedeća niža vrijednost 139.

Uključite drugi kriterij pretraživanja

U sljedećem koraku također želite potražiti grupu u ćeliji H4 kao drugi kriterij. Treba pronaći samo broj članka u kojem se nalazi grupa koju tražite.

Da biste to učinili, dodajte formulu IF formuli u ćeliji H8 i umetnite formulu kao formulu niza:

= VLOOKUP (H3; IF (B3: B40 = H4; A3: E40; ""); 5)

Podaci:

U programu Microsoft Excel razlikuje se konvencionalne formule od moćnijih formula matrice. Matrične formule korisnicima programa Excel omogućuju složene izračune. To se ne može izvesti standardnim formulama. Budući da se nakon unosa formule mora pritisnuti kombinacija tipki "CTRL + SHIFT + ENTER", matrične formule su međunarodno poznate kao ili CSE formule (CTRL + SHIFT + ENTER).

Potvrdite formulu nakon što je unesete pomoću kombinacije tipki Ctrl + Shift + Enter kao formulu matrice. [3]

Rezultat toga je isporučena stavka broj 1.188. Formula nalazi vrijednost 126 u stupcu "Veličina". To je najveća vrijednost u proračunskoj tablici. Manja je ili jednaka traženoj vrijednosti 142 i ima sadržaj C2 u odnosu na grupu.

IF funkcija u formuli provjerava podudarnost svake ćelije u rasponu B3: B40 sa sadržajem ćelije H4. Čim postoji podudaranje, odgovarajući redak u području B3: E40 prosljeđuje se funkciji VLOOKUP.

Jednostavno u bilo kojem trenutku dodajte dodatne kriterije pretraživanja

Na isti način možete dodati dodatne kriterije pretraživanja. To možete učiniti tako da u formulu ugnijezdite više funkcija IF. Kako biste zadovoljili sva četiri kriterija pretraživanja, u formulu u ćeliju H8 uključite još dvije ugniježđene IF funkcije:

= VLOOKUP (H3; IF (B3: B40 = H4; IF (C3: C40 = H5; IF (D3: D40 = H6; A3: E40; ""); ""); ""); 5)

Nakon potvrde formule matrice pomoću kombinacije tipki Ctrl + Shift + Enter, VLOOKUP vraća broj članka 1.748. Ovo je broj iz retka 14 za veličinu 125. To je redak s najvećom vrijednošću u stupcu "Veličina" u kojem su zadovoljena sva tri dodatna kriterija. [4]

Prva IF funkcija ne prenosi odmah odgovarajući redak u funkciju VLOOKUP ako je provjera pozitivna. Umjesto toga, druga IF funkcija provjerava postoji li podudaranje s drugim dodatnim kriterijem. Ako je i ova provjera pozitivna, koristi se treća funkcija IF. Tek kad sve tri IF funkcije vrate pozitivnu provjeru, treća IF funkcija prosljeđuje odgovarajući redak funkciji VLOOKUP. Na taj način možete ugnijezditi do sedam IF funkcija i na taj način provjeriti do osam kriterija.

Sažetak: Evo učinkovitog načina pretraživanja više kriterija u Excelu

Korisnici programa Microsoft Excel stalno se suočavaju s pitanjem kako čitati podatke i informacije iz opsežnih ili ugniježđenih proračunskih tablica u svom svakodnevnom radu. Korisnici programa Excel pronaći će najučinkovitiju referentnu funkciju u funkciji VLOOKUP. Velika prednost funkcije VLOOKUP i drugih referentnih funkcija je ta što prema zadanim postavkama traže sljedeću nižu vrijednost čim se tražena vrijednost ne pronađe.

Budući da je funkcija VLOOKUP prema zadanim postavkama ograničena na jedan pojam za pretraživanje, za više kriterija pretraživanja mora se koristiti proširena formula. Za pronalaženje nekoliko pojmova za pretraživanje pomoću funkcije VLOOKUP prikladna je formula niza s IF funkcijama. On jednu za drugom provjerava pojedinačne IF funkcije i, ako je rezultat pozitivan, odnosi se na VLOOKUP. To osigurava ispis točnih rezultata za opsežne proračunske tablice s različitim pojmovima za pretraživanje.

Pitanja

Što može učiniti funkcija VLOOKUP?

Funkcija VLOOKUP u programu Microsoft Excel jedna je od najvažnijih i najučinkovitijih funkcija u programu Excel. Putem VLOOKUP -a možete automatski pretraživati opsežnu proračunsku tablicu za određenu vrijednost. Rezultat se uređuje i šalje u drugu ćeliju.

Kada koristite funkciju IF u EXCEL -u?

IF funkcija jedna je od najčešće korištenih funkcija u programu Microsoft Excel. U prvom koraku funkcija IF koristi se za definiranje uvjeta. U drugom koraku povezujete IF s, na primjer, funkcijom THEN i određujete koje informacije želite ispisati. Funkcija ELSE također se može koristiti za određivanje alternativnog rezultata. Praktični primjer: AKO je zaposlenik A radio više od 240 dana godišnje, ONDA bonus A OSTALI bonus B.

Koja je razlika između formula polja i standardnih formula u Excelu?

Najpoznatije u Excelu su konvencionalne standardne formule. U Excelu su matrične formule moćne formule pomoću kojih je moguće napraviti složene izračune. Kako bi se izvršila formula niza, formula polja se mora potvrditi nakon što se unese kombinacijom tipki "CTRL + SHIFT + ENTER". Tipkovni prečac pokazuje Excelu da je to formula polja.