Pretraživanje tablice ili baze podataka i pronalaženje podatka koji se nalazi u sjecištu nekog reda i stupca

Ako imate potrebu pronaći nekakav podatak koji se nalazi u tablici baze podataka koji želite naknadno uvrstiti u neki izračun tada iskoristite funkcije OFFSET i MATCH.

Uzmimo situaciju kao na slici ispod. Potrebno je uz uvjete koji se nalaze u stupcu H (range B1:F1) i I (range A2:A41) pronaći podatak u tablici (range B2:F41) koji odgovara postavljenim uvjetima a nalazi se u sjecištu nepoznatog reda i stupca. Uvjeti u stupcu I su kilometri koji se nalaze u tablici u rasponu A2:A41. U prvom redu naslova stupaca tablice (raspon B1:F1) nalaze se količine u litrama. U ćeliji B1 je 0 (nula) a u stvari to je raspon litara od 0-5000 litara jer nam se u ćeliji C1 nalazi prvi slijedeći broj 5001 litara (u stvari to je raspon 5001-10000) itd itd...

Rješavanje ovog problema započet ćemo u ćeliji J1. Uvjeti su 40 km (I2) i 13170 litara (H2). Pratite sliku iznad.

Laički idemo razmisliti gdje bi nam bilo rješenje prvog zadataka. Ako vizualno gledamo naše uvjete i uspoređujemo ih u tablici podataka tada možemo uočiti da podatak 40 km nalazi se u ćeliji A5. Ako gledamo prvi podatak raspona u ćeliji A2 tada možemo zaključiti da se broj 40 nalazi u četvrtom (4) redu raspona A2:A41. Uvjetovana litraža 13170 nalazi se u stupcu D. Zašto baš u stupcu D? Zato što nam podatak naslova stupca (ćelija D1) ima broj 10001 a s obzirom na stupac desno i podatak u E1 (broj litara 20001) znači da nam uvjetovana litraža 13170 spada u taj raspon a to nam je treći (3) stupac po redu.

Ovdje ću malo pojasniti sve dijelove formule koja rješava ovaj problem.

Za prvi korak idemo upotrijebiti funkciju MATCH koja će nam pronaći poziciju (relativni položaj) nekog podatka.
Idemo prvo riješiti uvjet km (40) koji se nalazi u ćeliji I2

Sintaksu za funkciju Match pogledajte na linku a ovako bi izgledala formula za prvi uvjet.

=MATCH(I2;$A$2:$A$41;1)

Dakle funkcija Match traži relativni položaj podatka iz I2 u rasponu podataka stupca A2:A41. Kao rezultat vratit će nam se broj reda u kojem se nalazi uvjet (40), a to je red broj 4. Pogledajte sliku iznad

Za drugi korak idemo upotrijebiti funkciju MATCH koja će nam pronaći poziciju (relativni položaj) nekog podatka.
Idemo riješiti uvjet litara (13170) koji se nalazi u ćeliji H2

Sintaksu za funkciju Match pogledajte na linku a ovako bi izgledala formula za prvi uvjet.

=MATCH(H2;$B$1:$F$1;1)

Dakle funkcija Match traži relativni položaj podatka iz H2 u rasponu podataka B1:F1. Kao rezultat vratit će nam se broj stupca u kojem se nalazi uvjet (13170), a to je stupac broj 3. Zašto baš u ovom stupcu? Zato što broj 13170 se nalazi u rasponu brojeva 10001-20000. Pogledajte sliku iznad

Kada riješimo pronalaženje relativnog položaja reda i stupca za tražene podatke tada pomoću funkcije OFFSET idemo pronaći sjecište pronađenih rezultata. Pomoću funkcije Offset odredit ćemo pomak (pomjeranje) od ćelije A1. Dakle od ćelije A1 pomjeramo se dolje po prvom rezultatu (broj redova 4 ) i desno od A1 (broj stupaca 3)

I na kraju naša formula izgleda ovako

=OFFSET($A$1;MATCH(I2;$A$2:$A$41;1);MATCH(H2;$B$1:$F$1;1))

Download datoteke iz primjera.

Još jedan primjer gdje možemo uz dva uvjeta pretražiti bazu podataka u tablici (sjecište stupca i reda)

=INDEX(tablica;MATCH(y_value;y_header;0);MATCH(x_value;x_header;0))

tablica = B3:F28
Y_value = H6
Y_header = A3:A28
X_value = I6
X_header = B2:F2

Dakle formula1 je

=INDEX(B3:F28;MATCH(H6;A3:A28;0);MATCH(I6;B2:F2;0))

ili formula2

=OFFSET($A$1;MATCH(H6;$A$3:$A$28;1)+1;MATCH(I6;$B$2:$F$2;1))

ili formula3

=HLOOKUP(I6;tablica;MATCH(H6;stupac)+1;FALSE)
 

.

PRETRAŽIVANJE TABLICE PODATAKA I TRAŽENJE SJECIŠTA ZA DVA UVJETA

Ovaj primjer pretraživanja tablice podataka u kojem imamo dva uvjeta je malo drugačiji od iznad navedenih. Ovdje koristimo VLOOKUP funkciju a uz to pogledajte na istom primjeru i za INDEX i MATCH funkcije.

=VLOOKUP(G1;$A$2:$D$4;MATCH(H1;$A$1:$D$1;0);0)

Na slici uočite tablicu podataka raspona A1:D4, U ćeliji G1 imamo Uvjet1 a u H1 imamo Uvjet2. Na osnovu tih uvjeta želimo dobiti tj. pronaći rezultat iz tablice podataka. Da bi pronašli rezultat na osnovu uvjeta možemo koristiti dvije funkcije VLOOKUP i MATCH. Vlookup funkcija pronalazi određeni podatak u istom redu uz uvjet ali u rednom broju stupca (kolone) koji smo zadali dok funkcija Match pronalazi određeni podatak tj. njegov relativan položaj i u ovom primjeru ova funkcija nam zamjenjuje zadani stupac za funkciju vlookup.

=VLOOKUP(G1;$A$2:$D$4;2;0) => vraća kao rezultat broj 55 iz drugog stupca (2) u redu u kojem se nalazi ime Marko (G1)

=MATCH(H1;$A$1:$D$1;0) => ova funkcija nam vraća rezultat broj 4 za uvjet iz H1 (sri). Znači vraća nam redni broj stupca počevši od A1

=VLOOKUP(G1;$A$2:$D$4;MATCH(H1;$A$1:$D$1;0);0) => Kada ugnijezdimo funkciju Match u Vlookup gdje nam Match funkcija glumi redni broj stupca, možemo vrlo lako doći do sjecišta reda i stupca u nekom rasponu podataka

Isto tako rješenje možemo potražiti i kombinacijom funkcija INDEX i MATCH

=INDEX(B2:D4;MATCH(G1;$A$2:$A$4;0);MATCH(H1;$B$1:$D$1;0))

ili

=INDEX($A$1:$D$4;MATCH(G1;INDEX($A$1:$D$4;;1);0);MATCH(H1;INDEX($A$1:$D$4;1;);0))
=INDEX(tablica;MATCH(G1;INDEX(tablica;;1);0);MATCH(H1;INDEX(tablica;1;);0))

Također pogledajte primjere gdje možemo koristiti MATCH funkciju u kombinacijama sa ostalim Excel funkcijama.

- Traženje sjecišta reda i stupca u tablici Excela
- Intersection of Rows and Columns and return value