Kako izvršiti dvosmjerno traženje u Excelu 2013

Možda imate tablicu u programu Excel 2013 u kojoj trebate izvršiti dvosmjerno traženje, pri čemu se dio podataka dohvaća iz tablice pretraživanja na temelju traženja vrijednosti u gornjem retku (sa naslovima stupaca tablice) i vrijednost u prvom stupcu (sa naslovima redaka tablice).

Slika ilustrira situaciju u kojoj biste koristili dvije vrijednosti, datum proizvodnje i broj dijela, za traženje očekivane proizvodnje. U tablici Raspored proizvodnje 2013. datumi proizvodnje za svaki dio čine naslove stupaca u prvom retku tablice, a brojevi dijelova čine naslove redaka u prvom stupcu tablice.

Da biste potražili broj dijela koji je planiran za proizvodnju u određenom mjesecu, trebate upotrijebiti funkciju MATCH, koja vraća relativni položaj određene vrijednosti u rasponu ćelija ili nizu. Sintaksa funkcije MATCH je sljedeća:

MATCH(vrijednost_potraži,niz_potraživanja,[vrsta_podudaranja])

Tražena_vrijednost argument je, naravno, o vrijednosti čiji položaj želite vratiti kada se pronađe odgovarajući, a polje_pregleda je stanica Raspon ili polje sadrži vrijednosti koje želite uskladiti. Opcionalni MATCH_TYPE argument je broj 1, 0 ili -1, koji određuje kako Excel odgovara vrijednosti koje navodi tražena_vrijednost argument u rasponu određenom na polje_pregleda argument:

• Koristite match_type 1 da biste pronašli najveću vrijednost koja je manja ili jednaka lookup_value . Imajte na umu da su vrijednosti u polje_pregleda moraju biti smješteni u rastućem redoslijedu kada koristite jedan MATCH_TYPE argument. (Excel koristi ovu vrstu podudaranja kada je argument match_type izostavljen iz funkcije MATCH.)

• Koristite match_type 0 da pronađete prvu vrijednost koja je točno jednaka lookup_value . Imajte na umu da su vrijednosti u polje_pregleda može biti u bilo kojem redoslijedu kada koristite 0 MATCH_TYPE argument.

• Koristite match_type – 1 da pronađete najmanju vrijednost koja je veća ili jednaka lookup_value . Imajte na umu da su vrijednosti u polje_pregleda moraju biti smješteni u redoslijedu kada koristite -1 MATCH_TYPE argument.

Osim traženja položaja datuma proizvodnje i broja dijela u naslovima stupaca i redaka u tablici Raspored proizvodnje, trebate upotrijebiti funkciju INDEX, koja koristi relativnu poziciju broja retka i stupca za vraćanje broja za proizvodnju sa samog stola.

Funkcija INDEX slijedi dva različita oblika sintakse: niz i referencu. Obrazac polja koristite kada želite da se vrijednost vrati iz tablice (kao u ovom primjeru), a referentni obrazac koristite kada želite da se referenca vrati iz tablice.

Sintaksa oblika polja funkcije INDEX je sljedeća:

INDEX(niz,[broj_reda],[broj_stupca])

Sintaksa referentnog oblika funkcije INDEX je sljedeća:

INDEX(referenca,[broj_reda],[broj_stupca],[broj_područja])

Niz argument obliku niza funkcija INDEX je raspon ćelija ili polje konstanti koje želite Excel koristiti u pretragu. Ako ovaj raspon ili stalna sadrži samo jedan redak ili stupac, odgovarajući broj_retka ili col_num argumenti su obavezna.

Ako je raspon ili polje konstanti ima više od jednog retka ili više od jednog stupca, a vi odredite oba broj_retka i col_num argumente, Excel vraća vrijednost u polja argument koji se nalazi na raskrižju broj_retka argumenta i col_num argument .

Za funkcije MATCH i INDEX u primjeru, sljedeći nazivi raspona dodijeljeni su sljedećim rasponima ćelija:

• table_data u raspon ćelija A2:J6 s proizvodnim podacima plus naslovima stupaca i redaka

• part_list u raspon ćelija A2:A6 s naslovima redaka u prvom stupcu tablice

• date_list u raspon ćelija A2:J2 s naslovima stupaca u prvom retku tablice

• part_lookup u ćeliju B10 koja sadrži naziv dijela koji treba potražiti u tablici

• date_lookup u ćeliju B11 koja sadrži naziv datuma proizvodnje za traženje u tablici

Kao što slika pokazuje, ćelija B12 sadrži prilično dugu i - na prvi pogled - složenu formulu koja koristi nazive raspona koji su prethodno navedeni i kombinira funkcije INDEX i MATCH:

=INDEX(podaci_tablice,MATCH(potraga_dijelova,popis_dijelova),MATCH(potraga_datuma,popis_datuma))