Co je dobré vědět
- Nejprve vytvořte funkci INDEX a poté spusťte vnořenou funkci MATCH zadáním argumentu Lookup_value.
- Dále přidejte argument Lookup_array následovaný argumentem Match_type a poté zadejte rozsah sloupců.
- Poté převeďte vnořenou funkci na maticový vzorec stisknutím Ctrl+ Shift+ Enter. Nakonec přidejte hledané výrazy do listu.
Tento článek vysvětluje, jak vytvořit vyhledávací vzorec, který používá více kritérií v aplikaci Excel k vyhledání informací v databázi nebo tabulce dat pomocí maticového vzorce. Pole vzorec zahrnuje vnoření funkce MATCH uvnitř funkce INDEX. Informace pokrývají Excel pro Microsoft 365, Excel 2019, Excel 2016, Excel 2013, Excel 2010 a Excel pro Mac.
Postupujte společně s výukovým programem
Chcete-li postupovat podle kroků v tomto tutoriálu, zadejte ukázková data do následujících buněk, jak je znázorněno na obrázku níže. Řádky 3 a 4 jsou ponechány prázdné, aby vyhovovaly maticovému vzorci vytvořenému během tohoto kurzu. (Všimněte si, že tento tutoriál nezahrnuje formátování zobrazené na obrázku.)
- Zadejte nejvyšší rozsah dat do buněk D1 až F2.
- Zadejte druhý rozsah do buněk D5 až F11.
Vytvořte funkci INDEX v Excelu
Funkce INDEX je jednou z mála funkcí v Excelu, která má více formulářů. Funkce má formulář pole a referenční formulář. Array Form vrací data z databáze nebo tabulky dat. Referenční formulář uvádí odkaz na buňku nebo umístění dat v tabulce.
V tomto tutoriálu se k nalezení jména dodavatele titanových widgetů používá formulář Array, nikoli odkaz na buňku tohoto dodavatele v databázi.
Při vytvoření funkce INDEX postupujte podle těchto kroků:
- Vyberte buňku F3, aby se z ní stala aktivní buňka. Do této buňky bude vložena vnořená funkce.
-
Přejít na Formulas.
Image - Vyberte Vyhledat a reference pro otevření rozevíracího seznamu funkcí.
- Výběrem INDEX otevřete dialogové okno Vybrat argumenty.
- Vyberte array, row_num, column_num.
- Výběrem OK otevřete dialogové okno Argumenty funkce. V Excelu pro Mac se otevře Formula Builder.
- Umístěte kurzor do textového pole Array.
-
Zvýrazněte buňky D6 až F11 v listu a zadejte rozsah do dialogového okna.
Ponechte dialogové okno Argumenty funkcí otevřené. Formule není dokončena. Vzorec doplníte podle pokynů níže.
Image
Spustit vnořenou funkci MATCH
Při vnořování jedné funkce do druhé není možné otevřít tvůrce vzorců druhé nebo vnořené funkce a zadat potřebné argumenty. Vnořená funkce musí být zadána jako jeden z argumentů první funkce.
Při ručním zadávání funkcí jsou argumenty funkce od sebe odděleny čárkou.
Prvním krokem ke vstupu do vnořené funkce MATCH je zadání argumentu Lookup_value. Lookup_value je umístění nebo odkaz na buňku pro hledaný výraz, který má být v databázi nalezen.
Hodnota Lookup_value přijímá pouze jedno vyhledávací kritérium nebo výraz. Chcete-li vyhledat více kritérií, rozšiřte hodnotu Lookup_value zřetězením nebo spojením dvou nebo více odkazů na buňky pomocí symbolu ampersand (&).
- V dialogovém okně Argumenty funkce umístěte kurzor do textového pole Row_num.
- Zadejte MATCH(.
- Vyberte buňku D3 a zadejte odkaz na buňku do dialogového okna.
- Za odkazem na buňku D3 přidejte odkaz na druhou buňku, když zadáte & (ampersand).
- Vyberte buňku E3 a zadejte odkaz na druhou buňku.
-
Zadejte , (čárka) za odkazem na buňku E3 pro dokončení zadávání argumentu Lookup_value funkce MATCH.
Image V posledním kroku výukového programu budou hodnoty Lookup_values zadány do buněk D3 a E3 listu.
Dokončete vnořenou funkci MATCH
Tento krok zahrnuje přidání argumentu Lookup_array pro vnořenou funkci MATCH. Lookup_array je rozsah buněk, které funkce MATCH prohledává, aby našla argument Lookup_value přidaný v předchozím kroku výukového programu.
Vzhledem k tomu, že v argumentu Lookup_array byla identifikována dvě vyhledávací pole, je třeba totéž udělat pro pole Lookup_array. Funkce MATCH prohledá pouze jedno pole pro každý zadaný výraz. Chcete-li zadat více polí, použijte ampersand ke zřetězení polí dohromady.
- Umístěte kurzor na konec dat v textovém poli Row_num. Kurzor se objeví za čárkou na konci aktuálního záznamu.
-
Zvýrazněte buňky D6 až D11 v listu a zadejte rozsah. Tento rozsah je první pole, které funkce hledá.
- Za odkazy na buňky zadejte & (ampersand) D6:D11. Tento symbol způsobí, že funkce prohledá dvě pole.
- Zvýrazněte buňky E6 až E11 v listu a zadejte rozsah. Tento rozsah je druhým polem, které funkce hledá.
-
Zadejte , (čárku) za odkaz na buňku E3 pro dokončení zadávání argumentu Lookup_array funkce MATCH.
Image - Ponechte dialogové okno otevřené pro další krok výukového programu.
Přidat argument typu SHODY
Třetím a posledním argumentem funkce MATCH je argument Match_type. Tento argument říká Excelu, jak porovnat Lookup_value s hodnotami v Lookup_array. Dostupné možnosti jsou 1, 0 nebo -1.
Tento argument je volitelný. Pokud je vynechán, funkce použije výchozí hodnotu 1.
- Pokud Match_type=1 nebo je vynechán, MATCH najde největší hodnotu, která je menší nebo rovna Lookup_value. Data Lookup_array musí být seřazeny ve vzestupném pořadí.
- Pokud Match_type=0, MATCH najde první hodnotu, která se rovná Lookup_value. Data Lookup_array lze třídit v libovolném pořadí.
- Pokud Match_type=-1, MATCH najde nejmenší hodnotu, která je větší nebo rovna Lookup_value. Data Lookup_array musí být seřazeny v sestupném pořadí.
Za čárkou zadanou v předchozím kroku na řádku Row_num ve funkci INDEX zadejte tyto kroky:
- Za čárku do textového pole Row_num zadejte 0 (nulu). Toto číslo způsobí, že vnořená funkce vrátí přesné shody s výrazy zadanými v buňkách D3 a E3.
-
Zadejte ) (uzavírací kulatá závorka) pro dokončení funkce MATCH.
Image - Ponechte dialogové okno otevřené pro další krok výukového programu.
Dokončete funkci INDEX
Funkce MATCH je dokončena. Je čas přesunout se do textového pole Column_num v dialogovém okně a zadat poslední argument pro funkci INDEX. Tento argument říká Excelu, že číslo sloupce je v rozsahu D6 až F11. Tento rozsah je místo, kde najde informace vrácené funkcí. V tomto případě dodavatel titanových widgetů.
- Umístěte kurzor do textového pole Column_num.
-
Zadejte 3 (číslo tři). Toto číslo říká, že vzorec má hledat data ve třetím sloupci rozsahu D6 až F11.
Image - Ponechte dialogové okno otevřené pro další krok výukového programu.
Vytvořte vzorec pole
Před zavřením dialogového okna převeďte vnořenou funkci na maticový vzorec. Toto pole umožňuje funkci vyhledávat více termínů v tabulce dat. V tomto tutoriálu se shodují dva pojmy: Widgety ze sloupce 1 a Titan ze sloupce 2.
Chcete-li vytvořit maticový vzorec v aplikaci Excel, stiskněte CTRL, SHIFT a ENTERkláves současně. Po stisknutí je funkce obklopena složenými závorkami, což znamená, že funkce je nyní pole.
- Výběrem OK zavřete dialogové okno. V Excelu pro Mac vyberte Hotovo.
- Vyberte buňku F3 pro zobrazení vzorce a poté umístěte kurzor na konec vzorce v řádku vzorců.
- Chcete-li převést vzorec na pole, stiskněte CTRL+ SHIFT+ ENTER.
- V buňce F3 se zobrazí chyba N/A. Toto je buňka, do které byla zadána funkce.
-
V buňce F3 se zobrazí chyba N/A, protože buňky D3 a E3 jsou prázdné. D3 a E3 jsou buňky, kde funkce hledá hodnotu Lookup_value. Po přidání dat do těchto dvou buněk je chyba nahrazena informacemi z databáze.
Image
Přidat kritéria vyhledávání
Posledním krokem je přidat hledané výrazy do listu. Tento krok odpovídá výrazům Widgety ze sloupce 1 a Titanium ze sloupce 2.
Pokud vzorec najde shodu pro oba výrazy v příslušných sloupcích v databázi, vrátí hodnotu ze třetího sloupce.
- Vyberte buňku D3.
- Zadejte Widgety.
- Vyberte buňku E3.
- Napište Titanium a stiskněte Enter.
- V buňce F3 se objeví jméno dodavatele, Widgets Inc. Toto je jediný uvedený dodavatel, který prodává titanové widgety.
-
Vyberte buňku F3. Funkce se zobrazí v řádku vzorců nad pracovním listem.
{=INDEX(D6:F11, MATCH(D3&E3, D6:D11&E6:E11, 0); 3)}
V tomto příkladu je pouze jeden dodavatel titanových widgetů. Pokud existuje více než jeden dodavatel, funkce vrátí dodavatele uvedeného v databázi jako první.
Image
