Jak provést obousměrné vyhledávání v Excelu 2013

Můžete mít tabulku v Excelu 2013, ve které potřebujete provést obousměrné vyhledávání, kdy se část dat získá z vyhledávací tabulky na základě vyhledání hodnoty v horním řádku (s záhlavím sloupců tabulky) a hodnotu v prvním sloupci (s záhlavím řádků tabulky).

Obrázek znázorňuje situaci, kdy byste použili dvě hodnoty, datum výroby a číslo dílu, abyste našli očekávanou výrobu. V tabulce Plán výroby na rok 2013 tvoří data výroby každého dílu záhlaví sloupců v prvním řádku tabulky a čísla dílů tvoří záhlaví řádků v prvním sloupci tabulky.

Chcete-li vyhledat číslo dílu plánovaného na výrobu v konkrétním měsíci, musíte použít funkci MATCH, která vrátí relativní pozici konkrétní hodnoty v oblasti buněk nebo v poli. Syntaxe funkce MATCH je následující:

MATCH(hledaná_hodnota,lookup_pole,[typ_shody])

Argument lookup_value je samozřejmě hodnota, jejíž pozici chcete vrátit, když je nalezena shoda, a lookup_array je rozsah buněk nebo pole obsahující hodnoty, které chcete porovnat. Volitelný argument typ_shody je číslo 1, 0 nebo –1, které určuje, jak Excel odpovídá hodnotě určené argumentem hodnota_hledání v rozsahu určeném argumentem pole_hledání :

• Použijte match_type 1 k nalezení největší hodnoty, která je menší nebo rovna lookup_value . Všimněte si, že při použití argumentu 1 typ_shody musí být hodnoty v poli lookup_array umístěny ve vzestupném pořadí . (Excel používá tento typ shody, když je argument match_type vynechán z funkce MATCH.)

• Použijte match_type 0 k nalezení první hodnoty, která se přesně rovná lookup_value . Všimněte si, že hodnoty v lookup_array mohou být v libovolném pořadí, když použijete argument 0 match_type .

• Použijte match_type – 1 k nalezení nejmenší hodnoty, která je větší nebo rovna lookup_value . Všimněte si, že když použijete argument –1 match_type, hodnoty v lookup_array musí být umístěny v sestupném pořadí .

Kromě vyhledání pozice data výroby a čísla dílu v záhlaví sloupců a řádků v tabulce Plán výroby musíte použít funkci INDEX, která používá relativní pozici čísla řádku a sloupce k vrácení čísla, které se má vyrobit. ze samotného stolu.

Funkce INDEX má dvě různé formy syntaxe: pole a odkaz. Formulář pole použijete, když chcete hodnotu vrácenou z tabulky (jako v tomto příkladu), a referenční formulář použijete, když chcete z tabulky vrátit odkaz.

Syntaxe tvaru pole funkce INDEX je následující:

INDEX(pole;[číslo_řádku];[číslo_sloupce])

Syntaxe referenční formy funkce INDEX je následující:

INDEX(odkaz;[číslo_řádku];[číslo_sloupce];[číslo_oblasti])

Pole argument podobě pole funkce INDEX je oblast buněk nebo pole konstanta, která chcete Excel použít ve vyhledávání. Pokud tento rozsah nebo konstanta obsahuje pouze jeden řádek nebo sloupec, jsou odpovídající argumenty row_num nebo col_num volitelné.

Pokud má rozsah nebo konstanta pole více než jeden řádek nebo více než jeden sloupec a zadáte argumenty row_num i col_num , Excel vrátí hodnotu v argumentu pole, který se nachází na průsečíku argumentu row_num a argumentu col_num. .

Pro funkce MATCH a INDEX v příkladu byly následující názvy rozsahů přiřazeny následujícím rozsahům buněk:

• table_data do oblasti buněk A2:J6 s produkčními daty plus záhlavími sloupců a řádků

• part_list do oblasti buněk A2:A6 se záhlavími řádků v prvním sloupci tabulky

• date_list do rozsahu buněk A2:J2 se záhlavími sloupců v prvním řádku tabulky

• part_lookup do buňky B10, která obsahuje název součásti, kterou chcete vyhledat v tabulce

• date_lookup do buňky B11, která obsahuje název data výroby k vyhledání v tabulce

Jak ukazuje obrázek, buňka B12 obsahuje poměrně dlouhý a – na první pohled – složitý vzorec využívající názvy rozsahů uvedené dříve a kombinující funkce INDEX a MATCH:

=INDEX(data_tabulky,SHODA(vyhledání_části,seznam_částí),SHODA(vyhledání_data,seznam_dat))