Kuidas Excel 2013-s kahesuunalist otsingut teha

Teil võib olla Excel 2013 tabel, milles peate tegema kahesuunalise otsingu, mille käigus otsitakse tabelist Otsing andmeid, mis põhinevad väärtuse otsimisel ülemisest reast (tabeli veergude pealkirjadega) ja väärtus esimeses veerus (koos tabeli reapealkirjadega).

Joonis illustreerib olukorda, kus te kasutaksite eeldatava toodangu otsimiseks kahte väärtust, tootmiskuupäeva ja osanumbrit. 2013. aasta tootmisgraafiku tabelis moodustavad iga osa tootmiskuupäevad tabeli esimeses reas veergude pealkirjad ja osade numbrid moodustavad tabeli esimeses veerus reapealkirjad.

Konkreetsel kuul toota plaanitud osa numbri otsimiseks peate kasutama funktsiooni MATCH, mis tagastab konkreetse väärtuse suhtelise asukoha lahtrivahemikus või massiivis. Funktsiooni MATCH süntaks on järgmine:

VASTAVUS(otsingu_väärtus,otsingu_massiiv,[vaste_tüüp])

Lookup_value argument on muidugi väärtus, kelle positsioon, mida soovite tagasi, kui leitakse sobivus ja otsingu_massiiv on lahtrivahemik või massiivi, mis sisaldab väärtusi, mida soovite sobitada. Valikuline match_type argument on number 1, 0 või -1, mis määrab, kuidas Excel sobib väärtus määratud lookup_value argument vahemikus määratud otsingu_massiiv argument:

• Kasutage parameetrit match_type 1, et leida suurim väärtus, mis on otsingu_väärtusest väiksem või sellega võrdne . Pange tähele, et kui kasutate argumenti 1 match_type, tuleb otsingu_massiivi väärtused paigutada kasvavas järjekorras . (Excel kasutab seda tüüpi sobitamist, kui argument match_type jäetakse funktsioonist MATCH välja.)

• Kasutage vaste_tüüp 0, et leida esimene väärtus, mis on täpselt võrdne otsingu_väärtusega . Pange tähele, et kui kasutate argumenti 0 match_type, võivad otsingu_massiivi väärtused olla mis tahes järjekorras .

• Väikseima väärtuse leidmiseks, mis on otsingu_väärtusest suurem või sellega võrdne, kasutage parameetrit match_type – 1 . Pange tähele, et kui kasutate argumenti –1 match_type, tuleb otsingu_massiivi väärtused paigutada kahanevas järjekorras .

Lisaks tootmiskuupäeva ja osa numbri asukoha otsimisele tabeli Tootmisgraafik veerus ja ridade pealkirjades, peate kasutama funktsiooni INDEX, mis kasutab toodetava numbri tagastamiseks rea ja veeru numbri suhtelist positsiooni. lauast endast.

Funktsioon INDEX järgib kahte erinevat süntaksivormi: massiiv ja viide. Massiivivormi kasutatakse siis, kui soovite tabelist tagastada väärtuse (nagu selles näites), ja viitevormi, kui soovite tabelist viidet tagastada.

Funktsiooni INDEX massiivivormi süntaks on järgmine:

INDEX(massiiv, [rea_arv], [veeru_arv])

Funktsiooni INDEX viitevormi süntaks on järgmine:

INDEX(viide,[rea_arv], [veeru_arv], [ala_arv])

Funktsiooni INDEX massiivivormi massiivi argument on lahtrite vahemik või massiivikonstant, mida soovite Excelis otsingus kasutada. Kui see valik või pidev sisaldab ainult ühe rea või veeru, vastava rea_nr või col_num argumendid on vabatahtlik.

Kui vahemik või massiivi konstant on rohkem kui üks rida või enam kui üks veerg ja saab määrata nii rea_nr ja col_num argumendid, Excel tagastab väärtuse massiivi argument, et asub ristumiskohas rea_nr argument ja col_num argument .

Näites toodud funktsioonide MATCH ja INDEX jaoks määrati järgmistele lahtrivahemikele järgmised vahemikunimed:

• tabeli_andmed lahtrivahemikku A2:J6 koos tootmisandmete ning veeru- ja reapäistega

• part_list lahtrivahemikku A2:A6 koos tabeli esimese veeru reapealkirjadega

• date_list lahtrivahemikku A2:J2 koos tabeli esimese rea veerupealkirjadega

• osa_otsing lahtrisse B10, mis sisaldab tabelist otsitava osa nime

• date_lookup lahtrisse B11, mis sisaldab tabelist otsitava tootmiskuupäeva nime

Nagu jooniselt näha, sisaldab lahter B12 üsna pikka ja esmapilgul keerukat valemit, mis kasutab eelnevalt kirjeldatud vahemike nimesid ja ühendab funktsioonid INDEX ja MATCH:

=INDEKS(tabeli_andmed,VASTANE(osa_otsing,osade_loend),MATCH(kuupäeva_otsing,kuupäeva_loend))