Naučite da savladate funkcije poput LOOKUP i INDEX u Excelu za moćne pretrage.

  • Excelove funkcije pretraživanja omogućavaju vam brzo i bez grešaka pronalaženje podataka u velikim tabelama.
  • INDEX i MATCH, kombinovani, prevazilaze ograničenja funkcija VLOOKUP i HLOOKUP, omogućavajući dinamičko pretraživanje u bilo kom smeru.
  • Pravilno korištenje ovih funkcija poboljšava efikasnost, sprječava uobičajene greške i olakšava automatizaciju ponavljajućih zadataka.
Pablo

9 minuta

osnovne Excel formule

Savladajte funkcije pretraga u Excelu Excel je jedna od onih supermoći koje čine veliku razliku kada želite brže raditi, izbjeći greške i dobro upravljati velikim količinama podataka. Vjerovatno ste se našli pred ogromnom tabelom i pomislili: kako da pronađem tačno one podatke koje tražim, a da ne poludim? Pa, za to služe funkcije poput LOOKUP, VLOOKUP, HLOOKUP, INDEX i MATCH - pravi spas za svakoga ko redovno koristi Excel.

U ovom članku ćete detaljno saznati kako iskoristiti PRETRAŽIVANJE, INDEKS i druge formule za pretragu u Excelu. Objasnit ćemo vam to korak po korak na jednostavan način, s primjerima koje možete odmah primijeniti u svom svakodnevnom radu. Također ćemo vam reći kako kombinirati ove funkcije za izvođenje složenih pretraga, dati savjete za postizanje još veće snage i izbjeći uobičajene greške pri njihovom korištenju.

Zašto su funkcije pretraživanja u Excelu toliko važne?

U Excelu, prilikom upravljanja tabelama podataka, alati koji vam omogućavaju pronalaženje određenih informacija postaju neophodni. Zamislite da imate spisak zaposlenih sa njihovim brojevima kancelarija i da trebate znati u kojoj sobi John Smith radi, a da ne morate gledati u svaku od njih. Funkcije pretrage automatizuju ovaj proces, štedeći vam mnogo vremena i ručnih grešaka.

LOOKUP, VLOOKUP (što je skraćenica za "vertikalna pretraga"), HLOOKUP (horizontalna pretraga), INDEX i MATCH su klasične formule za ovu vrstu zadatka. Iako svaka ima svoje specifičnosti, sve one dijele cilj efikasnog lociranja podataka.

Funkcija VLOOKUP I dalje je najpopularniji za vertikalno pretraživanje unutar tabela, dok INDEKS i PODUDARANJE Omogućavaju mnogo fleksibilnije i moćnije pretrage, posebno u složenijim ili loše strukturiranim tabelama. Poznavanje prednosti svake funkcije daje vam slobodu da uvijek odaberete najbolji alat za svaku situaciju.

Ključne razlike između VLOOKUP, HLOOKUP, INDEX i MATCH

Prije nego što se upustimo u detalje, razjasnimo razlike i kada je najbolje koristiti svaku funkciju:

  • VLOOKUP: Pretražuje vrijednost u prvoj koloni tabele i vraća rezultat iz određene kolone u istom redu. Pretražuje samo s lijeva na desno.
  • HLOOKUPRadi isto što i VLOOKUP, ali pretražuje prvi red i vraća rezultate iz ostalih redova u istoj koloni. Pretražuje od vrha prema dnu.
  • INDEXVraća vrijednost ćelije na osnovu njenog položaja (red i kolona) unutar niza ili raspona. Može pretraživati ​​i vertikalno i horizontalno, što je čini fleksibilnijom.
  • COINCIDEVraća relativni položaj vrijednosti unutar raspona, idealno za kombinovanje sa INDEX funkcijom.

Kombinacija INDEX i MATCH U velikoj mjeri prevazilazi ograničenja VLOOKUP-a i HLOOKUP-a, jer omogućava i horizontalno i vertikalno pretraživanje, može usmjeriti pretraživanje u bilo kojem smjeru i ne zahtijeva da tražena vrijednost bude u prvoj koloni ili redu.

Razlaganje VLOOKUP-a: klasična funkcija vertikalnog pretraživanja

VLOOKUP je najčešće korištena funkcija za vertikalno pretraživanje u Excelu. Njena sintaksa je jednostavna:

=VLOOKUP(vrijednost_pretrage, raspon_tabele, indeks_kolone, )

Pogledajmo šta svaki argument znači:

  • lookup_valuePodaci koje želimo pronaći. To može biti tekst, broj ili referenca ćelije.
  • table_rangePodručje ćelija gdje će Excel tražiti vrijednost i odakle će vratiti rezultat.
  • indeks_koloneBroj kolone unutar table_range-a iz koje će vrijednost biti izvučena.
  • približno_podudaranje: Opcionalno. Ako ga postavite na TRUE ili ostavite prazno, Excel će tražiti približna podudaranja; ako ga postavite na FALSE, tražit će samo tačna podudaranja.

Na primjer:

=VLOOKUP(B2; C2:E7; 3; TAČNO)

U ovom slučaju, Excel će tražiti vrijednost u ćeliji B2 u prvoj koloni raspona C2:E7 i vratiti odgovarajuću vrijednost iz treće kolone tog raspona. Ako nema potpunog podudaranja i postavljena je vrijednost TRUE, vraćat će najbližu vrijednost prema zadanim postavkama.

Trik: Ako trebate pretraživati ​​u redovima umjesto u kolonama, koristite HLOOKUP, što radi potpuno isto, ali horizontalno. Da biste saznali više o tome kako iskoristiti ove funkcije, možete pogledati naš Kompletan vodič za VLOOKUP u Excelu.

Jasni primjeri VLOOKUP-a i HLOOKUP-a

Da bismo otklonili bilo kakvu zabunu, proći ćemo kroz praktične primjere svake funkcije (imajte na umu da možete uzeti fragmente stvarnih tabela i zalijepiti formule da biste ih testirali):

Osnovni primjer VLOOKUP-a

Pretpostavimo da imate listu gustoća i viskoziteta. Želite pronaći vrijednost viskoziteta koja odgovara određenoj gustoći od 1:

  • =VLOOKUP(1; A2:C10; 2) – Pronalazi vrijednost 1 u koloni A i vraća vrijednost iz kolone B u istom redu (u ovom slučaju, 2,17).
  • =VLOOKUP(1; A2:C10; 3; TAČNO) – Potražite vrijednost 1 u koloni A i vratite vrijednost iz kolone C u istom redu (100).

Osnovni primjer HLOOKUP-a

  • =HLOOKUP("Ose"; A1:C4; 2; TAČNO) – Pretražite „Ose“ u redu 1 i vratite vrijednost iz reda 2 u istoj koloni.
  • =HLOOKUP("Ležajevi"; A1:C4; 3; FALSE) – Pretražite „Ležajevi“ u redu 1 i vratite vrijednost iz reda 3 u koloni B.

Vidite, ovo su jednostavne formule za pamćenje! Ali šta ako kolona koju želite pretražiti nije s lijeve strane ili ako vam je potrebna svestranija pretraga?

INDEX i MATCH: Savršen par za napredne pretrage

Ovdje je INDEX y COINCIDE može vam dati supermoći. Kada vas ograničenja VLOOKUP-a ostave na cjedilu, posebno ako trebate pretraživati ​​lijevo, kombinirajte INDEX y COINCIDE otvara vam niz mogućnosti.

Počnimo s jasnim objašnjenjem svake funkcije:

Kako funkcionira funkcija INDEX?

INDEX omogućava vam da izdvojite vrijednost ćelije tačno tamo gdje želite, navođenjem reda i kolone unutar raspona ili niza. Njegova sintaksa je:

=INDEX(niz, broj_reda, )

Na primjer, ako imate tabelu prodaje i želite znati koja je vrijednost u redu 4 i koloni 2, samo trebate upisati:

=INDEX(A1:C10; 4; 2)

Ovo vraća vrijednost u ćeliji B4. Važan detalj: INDEX Možete pretraživati ​​vertikalno ili horizontalno, a vrlo je svestran za dinamičke formule.

Za šta se koristi COINCIDIR?

S druge strane, COINCIDIR označava relativni položaj vrijednosti unutar rasponaTo jest, govori vam u kojem redu ili koloni se nalaze podaci koje tražite. Njegova sintaksa je:

=MATCH(vrijednost_pretrage, niz_pretrage, )

  • lookup_valuePodaci koje želite pronaći.
  • niz_pretrageRaspon u kojem ga tražite.
  • vrsta_podudaranjaObično 0 za tačno podudaranje (preporučeno za većinu slučajeva).

Na primjer, da biste pronašli poziciju "John" na listi:

=MATCH(«Jovan», A2:A20, 0)

Ovo vraća poziciju (broj reda unutar raspona) gdje se nalazi "John".

Kombiniranje INDEX-a i MATCH-a za moćne pretrage

Prava magija je u koristite INDEX i MATCH zajednoNa ovaj način možete pronaći vrijednost presjeka reda i kolone koja se dinamički mijenja s vašim kriterijima pretrage.

Na primjer, imate tabelu s imenima prodavača u redovima i godinama u kolonama, i želite znati koliko je "Laura" fakturirala 2011. godine. Imena nisu u prvoj koloni, tako da VLOOKUP Ne funkcioniše, ali možete koristiti:

=INDEX(opseg_prodaje, COINCIDE("Laura", raspon_name_range, 0), COINCIDE(2011, raspon_godina, 0))

Na ovaj način, ako promijenite ime ili godinu, formula automatski vraća ispravne podatke. Ključ je:

  1. MATCH locira poziciju "Laura" u nizu imena.
  2. MATCH locira poziciju godine u zaglavlju niza.
  3. INDEX prelazi preko tih pozicija kako bi pristupio tačnoj ćeliji.

Ovo omogućava dinamičke upite, poređenje, filtriranje i unakrsno referenciranje podataka bez ograničenja.

Funkcija INDEX u referentnom obliku

Takođe, INDEX Ima naprednu varijantu u obliku reference, koja može vratiti više područja istovremeno. U ovom načinu rada, Možete raditi s više tabela i pretraživati ​​ih sve pomoću jedne formule..

Njegova sintaksa je:

=INDEX((površina1; površina2), broj_reda, broj_kolone, broj_površine)

Na primjer, ako imate oba područja (jedno za boje i jedno za voće), možete koristiti ovu metodu za pretraživanje oba na osnovu vaših kriterija.

ZapamtiKada stavljate više područja, koristite dvostruke zagrade kako biste izbjegli greške.

Praktičan primjer pretraživanja pomoću INDEX i MATCH

Pretpostavimo da upravljate tabelom faktura sa brojem, gradom i datumom, i želite da dobijete prvu fakturu za svaki grad sa njenim brojem i datumom. Formula bi bila:

= "Grad: " & INDEX(raspon_faktura, COINCIDE("Grad", raspon_gradova, 0), 1) & «, Datum: » & TEKST(INDEX(raspon_faktura, COINCIDE("Grad", raspon_gradova, 0), 3), "d/m/gg")

Na ovaj način, za svaki grad, dobijate potrebne podatke u jednoj ćeliji, formatiranoj na odgovarajući način.

Osnovni savjeti za izbjegavanje grešaka u funkcijama pretraživanja

  • Uvijek koristite 0 en COINCIDE kako bi se osigurala tačna podudaranja.
  • En VLOOKUP, ako modificirate strukturu, indeks_kolone može propasti; stoga, INDEKS + PODUDARANJE je otporniji/otpornija.
  • Postavite reference sa F4 prilikom kopiranja formula, kako se rasponi ne bi slučajno mijenjali.
  • Provjerite da li tražene vrijednosti postoje, da li su rasponi ispravni i da li su argumenti funkcije dobro definirani kako biste izbjegli greške poput # N / A o #REF!.

Prednosti INDEX+MATCH u odnosu na VLOOKUP i HLOOKUP

Iako se VLOOKUP široko koristi zbog svoje jednostavnosti, INDEKS+PODRŠKA ima nekoliko prednosti:

  • Omogućava vam pretraživanje u bilo kojem smjeru: s lijeva na desno i obrnuto.
  • Bolje se opire promjenama u tabeliAko umetnete kolone, formule ostaju ispravne.
  • Omogućava dvodimenzionalne pretrage: fiksno u redovima i kolonama, idealno za složene ili dinamičke podatke.
  • Radi s više raspona ili područja: korisno u naprednim tabelama.

Praktične vježbe za konsolidaciju funkcija pretraživanja u Excelu

Vježbajte ove funkcije s vlastitim podacima ili s gotovim primjerima. Evo nekoliko ideja:

  1. Pronađite cijenu proizvodaSa listom proizvoda i cijena, koristite VLOOKUP ili da biste dobili cijenu unosom naziva.
  2. Pronađite lokaciju zaposlenikaU tabeli s imenima i uredima pronađite u kojem uredu zaposlenik radi, bez obzira na položaj kolone.
  3. Izdvojite datum prve prodaje u graduKombinacijom INDEX i MATCH, brzo i efikasno dobijate željene podatke.
pretragaV excel
Vezani članak:
Potpuni vodič za funkciju VLOOKUP u Excelu: kako je koristiti, primjeri i napredni savjeti.

Savladajte funkcije kao što su LOOKUP, VLOOKUP, HLOOKUP, INDEX i MATCH u Excelu Čini vas efikasnijim i omogućava vam rješavanje složenih problema, obavljanje naprednih analiza i automatizaciju procesa. Kontinuirana praksa će vas učiniti stručnjakom, omogućavajući vam da radite preciznije i brže u svojim tabelama.