Potpuni vodič za korištenje funkcija pretraživanja i referenciranja u Excelu

Ako ste ikada radili sa nadmašiti, vjerojatno ste se susreli s potrebom za učinkovitim i brzim pretraživanjem ili referenciranjem podataka. Iako se mnogi ljudi ograničavaju na korištenje osnovnih funkcija, funkcije pretraživanja i referenciranja U Excelu su formule moćni alati koji mogu nevjerojatno optimizirati način na koji radite i analizirate informacije. Dobro razumijevanje načina na koji te formule funkcioniraju omogućit će vam uštedu vremena, izbjegavanje pogrešaka i donošenje puno informiranijih odluka.

U ovom ćemo članku istražiti u dubokom svi ključne funkcije pretraživanja i referenciranja koje postoje u Excelu, otkrivajući kako se koriste, njihove specifične značajke i praktične trikove koji će vam pomoći da iz njih izvučete maksimum. Osim pregleda primjera i sintakse, razjasnit ćemo svrhu svake funkcije i dati vam savjete kako ih integrirati u vlastite proračunske tablice, bez obzira jeste li početnik ili već imate iskustva, ali želite poboljšati svoje znanje.

Što su funkcije pretraživanja i referenciranja u Excelu?

Unutar univerzuma Excel funkcija, grupa pretraživanje i referenciranje Ističe se po tome što nam omogućuje lociranje određenih informacija unutar raspona, tablice ili skupa matrica, vraćajući rezultate na temelju podudaranja ili relativnih položaja. Ove su funkcije osmišljene za automatizaciju dohvaćanja povezanih podataka, olakšavanje složenih analiza, unakrsnog povezivanja informacija između tablica, dinamičko generiranje izvješća i još mnogo toga.

Povezani članak:

Kako savladati analizu podataka pomoću naprednih Excel alata

Glavne funkcije pretraživanja i referenciranja u Excelu

Excel uključuje širok raspon funkcija ove vrste, svaka sa svojom specifičnom upotrebom. Neke su široko poznate, kao što su VLOOKUP o HLOOKUP, dok drugi kao što su PRETRAŽIX o INDEKS dobivaju na važnosti s najnovijim verzijama. Evo opisa najvažnijih značajki:

• VLOOKUP: Pronalazi vrijednost u prvom stupcu polja i vraća vrijednost iz istog retka u drugom stupcu koji navedete.
• HLOOKUPSlično kao VLOOKUP, ali pretragu izvodi u prvom retku niza, vraćajući vrijednosti iz ostalih redaka.
• TRAŽI: Omogućuje vam pretraživanje u jednom retku ili stupcu i pronalaženje vrijednosti na istoj poziciji u drugom retku ili stupcu.
• PRETRAŽIXPoboljšana verzija prethodnih, koja omogućuje horizontalno ili vertikalno pretraživanje, napredne opcije podudaranja i puno je fleksibilnija.
• INDEKSVraća vrijednost određene ćelije u nizu, na temelju položaja u retku i stupcu.
• COINCIDEVraća relativni položaj vrijednosti unutar raspona ili polja.
• POSREDNO: Stvara referencu na temelju tekstualne vrijednosti, korisno za dinamičke reference.
• OFFSETVraća pomak reference od početne ćelije, vrlo korisno za reference varijabli.
• ODABERITE: Omogućuje vam odabir vrijednosti s popisa argumenata na temelju indeksa koji definirate.
• PODRUČJAVraća broj područja u referenci.
• KOLUMNA y FILAVraća broj stupca ili retka zadane reference.
• REDOVI y KOLUMNEVraća ukupan broj redaka ili stupaca u referenci.
• HIPERVEZA: Stvorite brzi pristup drugom dokumentu, web-mjestu ili lokaciji unutar same radne knjige.
• TRANSPONIRATI: Zamijenite retke i stupce u matrici.
• UNICOSVraća popis jedinstvenih vrijednosti iz niza ili raspona.
• FILTAR: Izdvaja podatke iz raspona na temelju kriterija koje definirate.

Kako funkcioniraju popularne funkcije pretraživanja

Analizirajmo najpopularnije funkcije pretraživanja, objasnimo njihovu sintaksu i vidimo kako se ponašaju u tipičnim situacijama. To će vam pomoći da shvatite razlike i primijenite svaku od njih prema potrebi.

VLOOKUP funkcija

Funkcija VLOOKUP (ili VLOOKUP na engleskom) vjerojatno je najčešće korištena funkcija kada je u pitanju vertikalno pretraživanje informacija unutar tablice.

Sintaksa: BUSCARV(valor_buscado, matriz_buscar_en, indicador_columnas, )

• tražena_vrijednostPodaci koje želite pronaći.
• pretraživanje_niza_uRaspon ćelija u kojima se izvodi pretraga, pri čemu prvi stupac sadrži tražene vrijednosti.
• stupac_indikatorBroj stupca iz kojeg će se uzeti rezultirajuća vrijednost, počevši s lijeva.
• uredan: Neobavezno. True (zadano) za približno podudaranje ili False za točno podudaranje.

Na primjer, ako imate tablicu proizvoda i želite pretražiti cijenu određene reference, VLOOKUP može automatski vratiti tu vrijednost bez potrebe da je ručno tražite.

HLOOKUP funkcija

Sestra prethodnog, HLOOKUP, pretražuje gornji redak raspona i vraća podatke iz navedenog stupca.

Sintaksa: BUSCARH(valor_buscado, matriz_buscar_en, indicador_filas, )

Idealan je za tablice u kojima su ključne informacije raspoređene vodoravno.

Funkcija PRETRAŽIVANJA

Funkcija TRAŽI Korisno je kada želite pretražiti jedan redak ili stupac i vratiti vrijednost na istoj poziciji iz drugog raspona. Može se koristiti na dva načina: kao vektor ili kao matrica.

• Vektorski oblikOmogućuje vam pretraživanje u rasponu jednog retka ili stupca (vektora) i dobivanje vrijednosti iz drugog vektora na istoj poziciji.
• Matrični oblik: Pretražuje prvi redak ili stupac matrice i vraća vrijednost iz odgovarajućeg posljednjeg retka ili stupca.

Sintaksa (vektor): BUSCAR(valor_buscado; vector_de_comparación; )

Sintaksa (niz): BUSCAR(valor_buscado; matriz)

U oba slučaja, ako tražena vrijednost nije pronađena točno, funkcija vraća najbližu vrijednost koja je manja ili jednaka traženoj vrijednosti. Ako je tražena vrijednost manja od bilo koje vrijednosti u polju ili vektoru, vraća #N/A.

FUNKCIJA BUSCARX

Jedan od najnovijih dodataka, PRETRAŽIX (XLOOKUP) uvelike poboljšava i pojednostavljuje pretraživanja. Omogućuje vam horizontalno i vertikalno pretraživanje, podržava približna i točna podudaranja, obrnuto pretraživanje, zadane vrijednosti ako se ne pronađe podudaranje i još mnogo toga. Da biste saznali više o njegovim prednostima, pogledajte naš .

Ova je funkcija fleksibilnija i izbjegava mnoga ograničenja funkcija VLOOKUP i HLOOKUP, na primjer, ne zahtijeva da se tražena vrijednost nalazi u prvom stupcu ili retku polja.

Funkcije INDEX i MATCH

Kombinacija INDEKS y COINCIDE omogućuje napredna pretraživanja daleko izvan onoga što VLOOKUP može. INDEKS omogućuje vam izdvajanje vrijednosti iz niza na temelju njegove pozicije. COINCIDE pronaći položaj vrijednosti unutar raspona, a zajedno možete pretraživati ​​bilo koju vrijednost koju želite u bilo kojem retku ili stupcu tablice bez obzira na redoslijed.

Povezani članak:

Kako ispravno upravljati datumima i vremenima u Excelu: Potpuni vodič korak po korak

Sintaksa INDEKSA: ÍNDICE(matriz, núm_fila, )

Sintaksa MATCH-a: COINCIDIR(valor_buscado, rango_buscado, )

Zajedno vam omogućuju dinamičko pretraživanje vrijednosti i izradu složenih pretraga.

Druge korisne referentne funkcije

• OFFSET omogućuje vam dobivanje pomaka raspona od početne ćelije, ključno za stvaranje dinamičkih raspona i saznajte više u Kako stvoriti uvjetne analize u Excelu.
• POSREDNO Vrlo je korisno ako trebate izgraditi reference pomoću teksta, pomažući vam u radu s varijabilnim formulama.
• KOLUMNA y FILA Označavaju broj stupca ili retka reference, što je vrlo praktično za automatske izračune.
• TRANSPONIRATI omogućuje vam zamjenu redaka stupcima u slučaju da je vaša tablica u suprotnom smjeru od onoga što vam je potrebno.

Moderne i napredne funkcije pretraživanja i referenciranja

Tijekom vremena, Excel je uključio nove značajke koje dodatno poboljšavaju njegove mogućnosti analize i automatizacije:

• FILTAROmogućuje vam dobivanje tablice filtrirane prema uvjetu ili skupu uvjeta koje definirate.
• UNICOSIzdvaja sve jedinstvene vrijednosti iz raspona, uklanjajući duplikate.
• NARUDŽBA y SORTIRAJ POOlakšavaju sortiranje podataka unutar matrice ili na temelju drugog raspona.
• UZETI, KAP, TOCOL, TOROWRazne funkcije koje vam omogućuju manipuliranje matricama, vraćanje samo određenih redaka ili stupaca ili transformaciju raspona u pojedinačne stupce/retke.
• WRAPCOLS y OMOTAČIReorganizirajte matrice dijeljenjem prema broju elemenata koje odredite.

Ove funkcije vam omogućuju stvaranje dinamičnije tablice i analize, automatizirajući mnoge zadatke bez potrebe za drugim vanjskim resursima.

Praktični primjer: PRETRAŽIVANJE u vektorima i matricama

Zamislite da imate stupac s numeričkim vrijednostima i drugi s pridruženim bojama. Želite znati koja boja odgovara vrijednosti 4,19.

1. Podatke stavite u dva stupca, na primjer, A i B, ovako:
   

   Frekvencija	Boja
   4,14	crvena
   4,19	narančasta
   5,17	žuti
   5,77	zelena
   6,39	Azul

2. Sada, u drugu ćeliju možete napisati formulu: =BUSCAR(4,19; A2:A6; B2:B6)

Excel će u stupcu A potražiti vrijednost 4,19 i vratiti boju koja se nalazi u istom retku u stupcu B: narančasta.

Ako tražite vrijednost koja nije u tablici, vratit će se boja s najbližim brojem. Ako je vrijednost pretraživanja manja od minimuma u stupcu, vratit će se #N/A.

Ova vrsta pretraživanja je bitna u analizi podataka, inventarizaciji, kontroli zaliha i bilo kojoj situaciji u kojoj trebate unakrsno referencirati informacije.

Ključni savjeti za savladavanje funkcija pretraživanja i referenciranja

• U novijim verzijama Excela, kladite se na SEARCHX kad god je to moguće, jer se time rješavaju mnoga ograničenja VLOOKUP-a i HLOOKUP-a. Da biste saznali kako ga koristiti, preporučujemo da posjetite naš .
• Kada radite s dinamičkim nizovima ili podacima koji se često mijenjaju, kombinira INDEX i MATCH za robusne i fleksibilne rezultate.
• Ne zaboravite iskoristiti značajke poput FILTAR, UNICOS i nove matrične funkcije za automatizaciju izvješća.
• Ako trebate referencirati raspone koji se mijenjaju po veličini, koristite OFFSET ili INDIRECT kako bi vaše formule bile ažurne.
• Da biste olakšali čitanje formula, koristite nazive raspona i jasno opišite što svaka funkcija radi, posebno ako datoteku dijelite s drugima.

Praktične primjene i primjeri iz stvarnog života

Funkcije pretraživanja i referenciranja temelj su automatizacije svakodnevnih zadataka u Excelu, od integriranja podataka iz različitih proračunskih tablica do generiranja prilagođenih izvješća i izrade nadzornih ploča. Neke praktične primjene uključuju:

• Unakrsno usporedite podatke o prodaji i kupcima kako biste brzo dobili relevantne informacije.
• Izradite predloške inventara koji se automatski ažuriraju na temelju unosa proizvoda.
• Izradite dinamička izvješća koja prikazuju vrijednosti na temelju prilagođenih odabira.
• Usporedite popise zaposlenika, studenata ili proizvoda, otkrivajući duplikate i razlike.
• Filtrirajte velike količine informacija kako biste dobili samo zapise koji zadovoljavaju određene kriterije.

Uobičajene pogreške i kako ih izbjeći

Uobičajeno je napraviti određene pogreške pri korištenju ovih funkcija, posebno ako ne obratite pozornost na sintaksu i raspone:

• Pogreške tipa #N/A: Do ovoga dolazi kada tražena vrijednost nije pronađena ili kada raspon pretraživanja nije adekvatan.
• Problemi s relativnim i apsolutnim referencamaBudite oprezni prilikom povlačenja formula; koristite simbol $ za sidrenje stupaca ili redaka.
• Neusklađenost u broju redaka/stupaca: Prilikom korištenja LOOKUP ili INDEX/MATCH, provjerite jesu li rasponi jednaki po veličini.
• Pretrage koje razlikuju velika i mala slovaOve funkcije uglavnom ne razlikuju velika i mala slova, pa će 'John' i 'john' biti ekvivalentni.
• Uvijek pamtiti sortirajte podatke ako ćete raditi približne pretrage, budući da nedostatak reda može dati neočekivane rezultate.

Savjeti za poboljšanje produktivnosti pomoću ovih značajki

• Vježbajte s primjerima tablica, na ovaj način naučit ćete kako se nositi s pogreškama i razumjeti ponašanje svake funkcije.
• Koristite nove čarobnjake za formule u Excelu za izradu složenih funkcija korak po korak.
• Uvijek razmislite o tome kako možete automatizirati ponavljajuće procese korištenjem referentnih formula, štedeći vrijeme i izbjegavajući ljudske pogreške.
• Iskoristite Pomoć za Excel i službenu dokumentaciju za učenje naprednih varijanti i mogućnosti svake funkcije.

Povezani članak:

Kako sortirati i filtrirati podatke kao stručnjak u Excelu: Profesionalni i praktični vodič

Ovladajte funkcije pretraživanja i referenciranja Excel je vještina koja čini razliku između jednostavnog ispunjavanja proračunskih tablica i postajanja pravim stručnjakom za analizu podataka. Ako iskoristite ove alate i vježbate s različitim primjerima, automatizirat ćete svoje zadatke, smanjiti pogreške i povećati produktivnost, tako da se možete usredotočiti na ono što je zaista važno: donošenje boljih odluka i olakšavanje svakodnevnog rada. Usudite se eksperimentirati s njima i primijetit ćete promjenu u tren oka! Podijelite vodič kako bi više ljudi znalo kako to učiniti..