Jak vytvořit vzorec pro vyhledávání v Excelu s více kritérii

Co je třeba 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 Lookup_array argument následovaný Match_type argument, zadejte rozsah sloupců.
• Poté vnořenou funkci přepněte do maticového vzorce stisknutím Ctrl+Posun+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. Vzorec pole zahrnuje vnoření funkce MATCH do funkce INDEX. Informace zahrnují 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 pokynů v tomto kurzu, 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 se do nich vešel vzorec pole vytvořený během tohoto kurzu. (Upozorňujeme, že tento výukový program nezahrnuje formátování viděné na obrázku.)

• Zadejte horní rozsah dat do buněk D1 až F2.
• Zadejte druhý rozsah do buněk D5 až F11.

Vytvořte funkci INDEX v aplikaci Excel

Funkce INDEX je jednou z mála funkcí v aplikaci Excel, která má více formulářů. Funkce má formulář pole a referenční formulář. Formulář pole vrací data z databáze nebo tabulky dat. Referenční formulář poskytuje odkaz na buňku nebo umístění dat v tabulce. V tomto kurzu se formulář pole používá k vyhledání názvu dodavatele titanových widgetů, nikoli odkazu na buňku tohoto dodavatele v databázi. Chcete-li vytvořit funkci INDEX, postupujte takto:

1. Vyberte buňku F3 aby se stala aktivní buňkou. Tato buňka je místem, kde bude zadána vnořená funkce.

2. Jít do Vzorce.

   

3. Vybrat Vyhledávání a reference otevřete rozevírací seznam funkcí.

4. Vybrat INDEX otevřít Vyberte argumenty dialogové okno.

5. Vybrat pole, číslo_řádku, číslo_sloupce.

6. Vybrat OK otevřít Argumenty funkcí dialogové okno. V aplikaci Excel pro Mac se otevře Tvůrce vzorců.

7. Umístěte kurzor do Pole Textové pole.

8. Zvýrazněte buňky D6 přes F11 v listu zadejte rozsah do dialogového okna. Nechte otevřené dialogové okno Argumenty funkcí. Vzorec není dokončen. Vyplníte vzorec v pokynech níže.

   

Spusťte funkci Vnořená shoda

Při vnoření jedné funkce do jiné není možné otevřít druhý nebo vnořený tvůrce vzorců 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 odděleny čárkou. Prvním krokem k zadání 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 nalezen v databázi. Lookup_value přijímá pouze jedno vyhledávací kritérium nebo výraz. Chcete-li vyhledat více kritérií, rozšiřte Lookup_value zřetězením nebo spojením dvou nebo více odkazů na buňky pomocí symbolu ampersand (&).

1. V Argumenty funkcí v dialogovém okně umístěte kurzor do Row_num Textové pole.

2. Enter ZÁPAS(.

3. Vyberte buňku D3 zadejte odkaz na tuto buňku do dialogového okna.

4. Enter & (ampersand) za odkazem na buňku D3 přidat odkaz na druhou buňku.

5. Vyberte buňku E3 pro zadání odkazu na druhou buňku.

6. Enter , (čárka) za odkazem na buňku E3 k dokončení zadání argumentu Lookup_value funkce MATCH.

   

   V posledním kroku tutoriálu se do buněk D3 a E3 listu zadají hodnoty vyhledávání.

Dokončete funkci Nested 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á, aby našel argument Lookup_value přidaný v předchozím kroku tutoriálu. Protože byla v argumentu Lookup_array identifikována dvě vyhledávací pole, musí být totéž provedeno pro Lookup_array. Funkce MATCH prohledá pouze jedno pole pro každý zadaný termín. Chcete-li zadat více polí, použijte ampersand ke zřetězení polí dohromady.

1. Umístěte kurzor na konec dat do Row_num Textové pole. Kurzor se objeví za čárkou na konci aktuálního záznamu.

2. Zvýrazněte buňky D6 přes D11 v listu zadejte rozsah. Tento rozsah je první pole, které funkce prohledá.

3. Enter & (ampersand) za odkazy na buňku D6: D11. Tento symbol způsobí, že funkce prohledá dvě pole.

4. Zvýrazněte buňky E6 přes E11 v listu zadejte rozsah. Tento rozsah je druhým polem, které funkce prohledává.

5. Enter , (čárka) za odkazem na buňku E3 k dokončení zadání argumentu Lookup_array funkce MATCH.

   

6. Nechte otevřené dialogové okno pro další krok v kurzu.

Přidejte argument MATCH Type

Třetí a poslední argument Funkce MATCH je Match_type argument. 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, použije funkce 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 řazena vzestupně.
• Pokud Match_type = 0, MATCH najde první hodnotu, která se rovná Lookup_value. Data Lookup_array lze řadit 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í.

Tyto kroky zadejte po čárce zadané v předchozím kroku na řádku Row_num ve funkci INDEX:

1. Enter 0 (nula) za čárkou v Row_num Textové pole. 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.

2. Enter ) (uzavírací kulatá závorka) k dokončení funkce MATCH.

   

3. Nechte otevřené dialogové okno pro další krok v kurzu.

Dokončete funkci INDEX

Funkce MATCH je hotová. Je čas se přesunout do textového pole Column_num v dialogovém okně a zadat poslední argument pro funkci INDEX. Tento argument říká aplikaci Excel, že číslo sloupce je v rozsahu D6 až F11. V tomto rozsahu najde informace vrácené funkcí. V tomto případě dodavatel titanových widgetů.

1. Umístěte kurzor do Sloupec_číslo Textové pole.

2. Enter 3 (číslo tři). Toto číslo říká vzorci, aby hledal data ve třetím sloupci rozsahu D6 až F11.

   

3. Nechte otevřené dialogové okno pro další krok v kurzu.

Vytvořte vzorec pole

Před zavřením dialogového okna změňte vnořenou funkci na maticový vzorec. Toto pole umožňuje funkci vyhledávat v tabulce dat více výrazů. V tomto kurzu se shodují dva termíny: Widgety ze sloupce 1 a Titan ze sloupce 2. Chcete-li vytvořit vzorec pole v aplikaci Excel, stiskněte CTRL, POSUN, a ENTER současně. Po stisknutí je funkce obklopena složenými závorkami, což znamená, že funkce je nyní pole.

1. Vybrat OK zavřete dialogové okno. V aplikaci Excel pro Mac vyberte Hotovo.

2. Vyberte buňku F3 Chcete-li zobrazit vzorec, umístěte kurzor na konec vzorce do řádku vzorců.

3. Chcete-li převést vzorec na pole, stiskněte CTRL+POSUN+ENTER.

4. A # N / A v buňce F3 se objeví chyba. Toto je buňka, do které byla funkce zadána.

5. Chyba # N / A se objeví v buňce F3, protože buňky D3 a E3 jsou prázdné. D3 a E3 jsou buňky, kde funkce vypadá, že najde Lookup_value. Po přidání dat do těchto dvou buněk je chyba nahrazena informacemi z databáze.

   

Přidejte kritéria vyhledávání

Posledním krokem je přidání hledaných výrazů do listu. Tento krok odpovídá výrazům Widgety ze sloupce 1 a Titan 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.

1. Vyberte buňku D3.

2. Enter Widgety.

3. Vyberte buňku E3.

4. Typ Titana stiskněte Enter.

5. Název dodavatele, Widgets Inc., se objeví v buňce F3. Toto je jediný uvedený dodavatel, který prodává titanové widgety.

6. Vyberte buňku F3. Funkce se zobrazí na řádku vzorců nad listem. {= INDEX (D6: F11, MATCH (D3 a E3, D6: D11 a E6: E11,0), 3)}

   V tomto příkladu existuje pouze jeden dodavatel titanových widgetů. Pokud existoval více než jeden dodavatel, vrátí funkce uvedený jako první v databázi dodavatele.