Ako vykonať obojsmerné vyhľadávanie v Exceli 2013

Môžete mať tabuľku v Exceli 2013, v ktorej musíte vykonať obojsmerné vyhľadávanie, pričom časť údajov sa získa z vyhľadávacej tabuľky na základe vyhľadania hodnoty v hornom riadku (s hlavičkami stĺpcov tabuľky) a hodnotu v prvom stĺpci (s hlavičkami riadkov tabuľky).

Obrázok znázorňuje situáciu, v ktorej by ste použili dve hodnoty, dátum výroby a číslo dielu, na vyhľadanie očakávanej výroby. V tabuľke Harmonogram výroby na rok 2013 tvoria dátumy výroby pre každý diel nadpisy stĺpcov v prvom riadku tabuľky a čísla dielov tvoria nadpisy riadkov v prvom stĺpci tabuľky.

Ak chcete vyhľadať číslo dielu, ktorý sa má vyrobiť v konkrétnom mesiaci, musíte použiť funkciu MATCH, ktorá vráti relatívnu polohu konkrétnej hodnoty v rozsahu buniek alebo poli. Syntax funkcie MATCH je nasledovná:

MATCH(hľadaná_hodnota,vyhľadávacie_pole,[typ_zhody])

Argument lookup_value je, samozrejme, hodnota, ktorej pozíciu chcete vrátiť, keď sa nájde zhoda, a pole lookup_array je rozsah buniek alebo pole obsahujúce hodnoty, ktoré chcete zhodovať. Voliteľný match_type argumentom je číslo 1, 0 alebo -1, ktorý určuje, ako Excel zodpovedá hodnote určený lookup_value argumentu v rozsahu stanovenom prohledávané_pole argumentom:

• Použite match_type 1 na nájdenie najväčšej hodnoty, ktorá je menšia alebo rovná lookup_value . Všimnite si, že hodnoty v lookup_array musia byť umiestnené vo vzostupnom poradí, keď použijete argument 1 match_type . (Excel používa tento typ zhody, keď je argument match_type vynechaný z funkcie MATCH.)

• Použite match_type 0 na nájdenie prvej hodnoty, ktorá sa presne rovná lookup_value . Všimnite si, že hodnoty v lookup_array môžu byť v ľubovoľnom poradí, keď použijete argument 0 match_type .

• Použite match_type – 1 na nájdenie najmenšej hodnoty, ktorá je väčšia alebo rovná lookup_value . Všimnite si, že hodnoty v lookup_array musia byť umiestnené v zostupnom poradí, keď použijete argument –1 match_type .

Okrem vyhľadania pozície dátumu výroby a čísla dielu v hlavičkách stĺpcov a riadkov v tabuľke Plán výroby musíte použiť funkciu INDEX, ktorá používa relatívnu polohu čísla riadku a stĺpca na vrátenie čísla, ktoré sa má vyrobiť. zo samotného stola.

Funkcia INDEX má dve rôzne formy syntaxe: pole a odkaz. Formulár poľa použijete, keď chcete hodnotu vrátiť z tabuľky (ako to robíte v tomto príklade), a referenčný formulár použijete, keď chcete, aby sa z tabuľky vrátil odkaz.

Syntax tvaru poľa funkcie INDEX je nasledovná:

INDEX(pole;[číslo_riadka];[číslo_stĺpca])

Syntax referenčného formulára funkcie INDEX je nasledovná:

INDEX(odkaz;[číslo_riadka];[číslo_stĺpca];[číslo_oblasti])

Pole argument podobe polia funkcie INDEX je oblasť buniek alebo polia konštanta, ktorá chcete Excel použiť vo vyhľadávaní. Ak tento rozsah alebo konštanta obsahuje iba jeden riadok alebo stĺpec, zodpovedajúce argumenty row_num alebo col_num sú voliteľné.

Ak má rozsah alebo konštanta poľa viac ako jeden riadok alebo viac ako jeden stĺpec a zadáte argumenty row_num aj col_num , Excel vráti hodnotu v argumente poľa, ktorý sa nachádza na priesečníku argumentu row_num a argumentu col_num .

Pre funkcie MATCH a INDEX v príklade boli nasledujúce názvy rozsahov priradené k nasledujúcim rozsahom buniek:

• table_data do rozsahu buniek A2:J6 s produkčnými údajmi plus hlavičky stĺpcov a riadkov

• part_list do rozsahu buniek A2:A6 s hlavičkami riadkov v prvom stĺpci tabuľky

• date_list do rozsahu buniek A2:J2 s nadpismi stĺpcov v prvom riadku tabuľky

• part_lookup do bunky B10, ktorá obsahuje názov dielu, ktorý sa má vyhľadať v tabuľke

• date_lookup do bunky B11, ktorá obsahuje názov dátumu výroby, ktorý sa má vyhľadať v tabuľke

Ako ukazuje obrázok, bunka B12 obsahuje pomerne dlhý a na prvý pohľad zložitý vzorec využívajúci názvy rozsahov uvedené vyššie a kombinujúci funkcie INDEX a MATCH:

=INDEX(údaje_tabuľky,ZHODA(vyhľadávanie_častí,zoznam_častí),ZHODA(vyhľadávanie_dátumov,zoznam_dátumov))