Kako Izvući Podatke s Proračunske Tablice koristeći VLOOKUP, MATCH i INDEX

() translation by (you can also view the original English article)

Kada morate pronaći i izvući stupac podataka iz jedne tablice i smjestiti je u drugu, upotrijebite VLOOKUP funkciju. Ova funkcija također radi sa bilo kojom verzijom Excela u Windowsima ili na Mac-u, te također i u Google Sheets. Omogućuje vam da pronađete podatke u jednoj tablici koristeći neki identifikator koji mu je zajednički s drugom tablicom. Dvije tablice mogu biti na različitim listama ili čak radnim knjigama. Postoji također i HLOOKUP funkcija, koja radi istu stvar, ali sa horizontalno uređenim podacima preko redova.

MATCH i INDEX funkcije dobro je upotrijebiti kada vam je potrebna lokacija određenih podataka, kao što su kolumne ili redovi koji sadrže ime osobe.

Premium Opcije

Prije nego što krenemo dalje sa Excel funkcijama, jeste li znali da Envato Market ima cijeli niz Excel skripti i plugin-ova koji vam omogućuju da primijenite napredne funkcije?

Na primjer, možete:

Excel scripts and plugins on Envato Market — Excel skripte i plugin-ovi na Envato Marketu

Video Prikaz

Ako želite, možete pratiti ovaj tutorial koristeći svoju Excel datoteku. Ili, ako vam je draže, preuzmite zip datoteku uključenu u ovaj tutorial, koja sadrži primjer radne knjige pod nazivom vlookup example.xlsx.

Rad sa VLOOKUP-om

Kada VLOOKUP pronađe identifikator koji ste odredili u izvoru podataka, tada može pronaći bilo koju ćeliju u tom stupcu i vama vratiti informaciju. Imajte na umu da u izvorišnom podatku, identifikator mora biti u prvom stupcu tablice.

unique identifiers for vlookup — Jedinstveni identifikator trebao bi biti poput serijskog broja, gdje ne postoje dva ista u istoj tablici.

Sintaksa

Sintaksa VLOOKUP funkcije je:

=VLOOKUP(lookup value, table range, column number, [true/false])

Evo što ovi argumenti znače:

Lookup value. Ćelija koja ima jedinstveni identifikator.
Table range. Raspon ćelija koja ima identifikator u prvom stupcu, praćen ostatkom podataka u ostalim stupcima.
Column number. Broj stupca koji sadrži podatke koje tražite. Nemojte to pobrkati sa slovom stupca. Na donjoj ilustraciji, "states" se nalazi u 4. stupcu.
True/False. Ovaj argument je opcionalan. True znači da je prihvatljiva približna vrijednost, i False znači da je prihvatljiva samo identična vrijednost.

Na ilustraciji iznad želimo pronaći iznos prodaja iz tablice, stoga koristimo sljedeće argumente:

syntax of vlookup — Sintaksa VLOOKUP funkcije

Definicija Imena Raspona za Izradu Apsolutne Adrese

U Vlookup example.xlsx, pogledajte Sales Amounts radnu listu. Unijeti ćemo formulu u B5, zatim upotrijebiti AutoFill značajku da kopiramo formulu dolje na listu. To znači da raspon tablice u formuli mora biti apsolutna adresa. Dobar način da to učinite jest da definirate ime za raspon tablice (table range).

Definiranje Imena Raspona u Excel-u

Prije nego što unesete formulu, idite na izvorišne podatke radne liste.
Selektirajte sve ćelije iz A4 (zaglavlje za Order # stupac) dolje do H203. Brz način da to učinite je sljedeći: kliknite A4, zatim pritisnite Ctrl-Shift-End (Command-Shift-End na Mac-u).
Kliknite u Name Box iznad stupca A (Name Box sada prikazuje A4).
Upišite data, zatim pritisnite Enter.
Sada u formuli možete koristiti ime data umjesto $A$4:$H$203.

Name box displaying a range name — Name box obično prikazuje adresu trenutne ćelije. Kliknite u taj okvir i upišite ime da definirate raspon.

Definiranje Imena Raspona u Google Sheetes

U Google Sheets, definiranje imena je malo drugačije.

Kliknite na prvi stupac vašeg izvorišnog podatka, zatim pritisnite Ctrl-Shift-Desna Strelica (Command-Shift-Desna Strelica na Mac-u). To selektira red zaglavlja stupca.
Pritisnite Ctrl-Shift-Strelica Dolje (Command-Shift-Strelica Dolje na Mac-u). To selektira stvarne podatke.
Kliknite Data izbornik, zatim odaberite Named and protected ranges.
U Named and protected ranges okviru zdesna, upišite data, zatim kliknite Done.

Data menu in Google Sheets — Definiranje imena raspona u Google Sheets

Unos Formule

Kako biste unijeli formulu, idite na Sales Amounts radnu listu i kliknite B5.

Unesite formulu:

=VLOOKUP(A5,data,8,FALSE)

Pritisnite Enter.

entering the vlookup function — Unos VLOOKUP funkcije

Rezultat bi trebao biti 40. Kako biste ispunili vrijednosti dolje u stupcu, kliknite opet na B5, ako je potrebno. Stavite pokazivač miša na AutoFill točku donjem desnom kutu ćelije, tako da pokazivač miša postane križić.

mouse pointer on the autofill dot — Kada stavite pokazivač miša na točku u donjem desnom kutu ćelije, postaje AutoFill križić

Dvaput kliknite da ispunite vrijednosti dolje u stupcu.

worksheet with data after using autofill — Dvaput kliknite AutoFill križić da kopirate formulu dolje u stupcu

Ako želite, možete pokrenuti VLOOKUP funkciju u sljedećim stupcima kako biste izvukli druga polja, poput posljednjeg imena ili države.

Rad sa MATCH-em

MATCH funkcija vam ne vraća vrijednost podatka; vi dajete vrijednost koju tražite, i funkcija vraća poziciju te vrijednosti. To je kao da pitate gdje je #135 Main Street ulica, i dobijete odgovor da je to 4. neboder dolje u ulici.

Sintaksa

Sintaksa MATCH funkcije je:

=MATCH(lookup value, table range, [match type])

Argumenti su:

Lookup value. Ćelija koja ima jedinstveni identifikator.
Table range. Raspon ćelija koje tražite.
Match type. Opcionalno. Ovisi kako specificirate koliko približnu vrijednost želite, kao što slijedi:

Sljedeća najviša vrijednost	-1	Vrijednosti moraju biti u silaznom redoslijedu.
Ciljana Vrijednost	0	Vrijednosti mogu biti u bilo kojem redoslijedu.
Sljedeća najniža vrijednost	1	Zadani tip. Vrijednosti moraju biti u uzlaznom (rastućem) rasporedu.

Kao i sa VLOOKUP funkcijom, vjerojatno će vam MATCH funkcija biti lakša ako primijenite ime raspona. Idite na Source Data listu, selektirajte B4 (zaglavlje stupca za redoslijed ") do dna, kliknite Name okvir iznad stupca A i nazovite ga order_number. Vrijednosti su u uzlaznom redoslijedu.

name box with one column defined — Imenovani raspon može biti samo jedan stupac, samo jedan red, ili čak samo jedna ćelija

Idite na Match karticu na radnoj listi. U B5, unesite MATCH funkciju:

=MATCH(A5,order_number,1)

entering the match function — Unos MATCH funkcije

Da niste definirali ime raspona, funkciju biste pisali kao:

=MATCH(A5,'Source Data'!A5:A203,0)

Kako god, možete vidjeti da je ovo na 14-oj poziciji (čineći ga 13-om narudžbom).

completed match function — Rezultat MATCH funkcije

Upotreba INDEX-a

INDEX funkcija je suprotnost MATCH funkciji i slična VLOOKUP funkciji. Vi funkciji govorite koji red i stupac podataka želite, a ona vam govori vrijednost onoga što se nalazi u ćeliji.

Sintaksa

Sintaksa INDEX funkcije je:

=INDEX(data range, row number, [column number])

Argumenti su:

Data range. Baš kao i druge dvije funkcije, ovo je tablica podataka.
Row number. Redni broj podataka, koji nije nužno i red radne liste. Ako tablica raspona počinje na 10 redu liste, tada je to red #1.
Column number. Broj stupca raspona podataka. Ako raspon počinje na stupcu E, tada je to stupac #1.

Excel-ova dokumentacija reći će vam da je column number opcionalan, ali i 'row number' je u neku ruku opcionalan također. Ako raspon tablice ima samo jedan red ili stupac, ne morate koristiti drugi argument.

Idite na Index listu radne knjige i kliknite C6. Prvo želimo pronaći što je sadržano u redu 9, stupcu 3, na tablici. U formuli ćemo upotrijebiti ime raspona koje smo kreirali ranije.

Unesite formulu:

=INDEX(data,A6,B6)

entering the index function — unos index funkcije

Vraća posljednje ime korisnika: Strevell. Promijenite vrijednosti A6 i B6, i rezultat u C6 će pokazati drugačije rezultate (mnogo redova ima iste države i isto ime proizvoda).

Zaključak

Mogućnost radne liste da gleda u drugu radnu listu i izvuče podatke čini odličan alat. Na ovaj način možete imati samo jednu listu koja sadrži sve podatke koji su vam potrebni za mnoge svrhe, zatim izvući ono što trebate za određene instance.