Vzhled vyhledávání ve formátu Excel s více kritérii

Použitím vzorce pole v aplikaci Excel můžeme vytvořit vzorec vyhledávání, který používá více kritérií k nalezení informací v databázi nebo tabulce dat.

Vzorec pole zahrnuje vnoření funkce MATCH uvnitř funkce INDEX .

Tento návod obsahuje krok za krokem příklad vytvoření formuláře vyhledávání, který používá více kritérií pro nalezení dodavatele titanových Widgetů ve vzorové databázi.

Postupujte podle kroků v níže uvedených tématech a projděte vytvořením a použitím vzoru uvedeného na obrázku výše.

01 ze dne 09

Zadání výukového data

Prvním krokem v tutoriálu je zadání dat do listu aplikace Excel.

Chcete-li postupovat podle pokynů v tutoriálu, zadejte data uvedená na obrázku do následujících buněk .

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

Řádky 3 a 4 jsou prázdné, aby vyhovovaly vzoru pole vytvořenému během tohoto kurzu.

Výukový program neobsahuje formátování viděné v obraze, ale to neovlivní způsob, jakým funguje vyhledávací vzorec.

Informace o možnostech formátování, které jsou podobné těm, které jste viděli výše, jsou k dispozici v tomto základním výukovém formátu Excel.

02 ze dne 09

Spuštění funkce INDEX

Funkce INDEX je jednou z mála v aplikaci Excel, která má několik formulářů. Funkce má formulář pole a referenční formulář .

Formulář Array vrátí skutečná data z databáze nebo tabulky dat, zatímco Referenční formulář poskytuje odkaz na buňky nebo umístění dat v tabulce.

V tomto výukovém programu použijeme formulář Array, protože chceme znát jméno dodavatele pro widgety z titanu spíše než odkaz na buňky u tohoto dodavatele v naší databázi.

Každý formulář má jiný seznam argumentů, které musí být zvoleny před zahájením funkce.

Tutorial Kroky

1. Klepnutím na buňku F3 ji vytvoříte jako aktivní buňku . Zde vstoupíme do vnořené funkce.
2. Klikněte na kartu Vzorce v nabídce pásu karet .
3. Z rozbalovací nabídky vyberte položku Vyhledat a odkaz a otevřete rozevírací seznam funkcí.
4. Klepnutím na položku INDEX v seznamu vyvoláte dialogové okno Vybrat argumenty .
5. V dialogovém okně vyberte pole, row_num, col_num .
6. Klepnutím na tlačítko OK otevřete dialogové okno funkce INDEX.

03 ze dne 09

Zadání argumentu argumentu pole INDEX

Prvním požadovaným argumentem je argument Array. Tento argument určuje rozsah buněk, které mají být vyhledány pro požadovaná data.

Pro tento tutoriál bude tento argument ukázkovou databází .

Tutorial Kroky

1. V dialogovém okně funkce INDEX klepněte na řádek pole .
2. Zvýrazněte buňky D6 až F11 v pracovním listu a zadejte rozsah do dialogového okna.

04 ze dne 09

Spuštění funkce vnořeného MATCH

Při vkládání jedné funkce do jiné není možné otevřít dialogové okno druhé nebo vnořené funkce pro zadání potřebných argumentů .

Vnořená funkce musí být zadána jako jeden z argumentů první funkce.

V tomto tutoriálu bude vložena vnořená funkce MATCH a její argumenty do druhého řádku dialogového okna funkce INDEX - řádek Row_num .

Je důležité si uvědomit, že při ručním zadávání funkcí jsou argumenty funkce navzájem odděleny čárkou "," .

Zadání Argumentu funkce Lookup_value funkce MATCH

Prvním krokem při zadávání vnořené funkce MATCH je zadání argumentu Lookup_value .

Hodnota Lookup_value bude umístění nebo odkaz na buňku pro hledaný výraz, který chceme v databázi shodovat.

Hodnota Lookup_value obvykle přijímá pouze jedno vyhledávací kritérium nebo termín. Abychom mohli hledat více kritérií, musíme rozšířit vyhledávací hodnotu .

To se provádí spojením dvou nebo více buněčných odkazů pomocí symbolu ampersand " & ".

Tutorial Kroky

1. V dialogovém okně funkce INDEX klepněte na řádek Row_num .
2. Zadejte název shody názvu a následnou otevřenou kulatou konzolu " ( "
3. Klepnutím na buňku D3 zadejte odkaz buňky do dialogového okna.
4. Zadejte ampersand " & " po odkazu na buňku D3 za účelem přidání druhé reference buňky.
5. Klepnutím na buňku E3 zadejte tuto druhou odkaz buňky do dialogového okna.
6. Zadejte čárku "," za odkazem na buňku E3, abyste dokončili zadání argumentu Lookup_value funkce MATCH.
7. Ponechte dialogové okno funkce INDEX otevřené pro další krok v tutoriálu.

V posledním kroku tutoriálu budou hodnoty Lookup_values ​​zadány do buněk D3 a E3 listu.

05 ze dne 09

Přidání funkce Lookup_array pro funkci MATCH

Tento krok zahrnuje přidání argumentu Lookup_array pro vnořenou funkci MATCH.

Lookup_array je rozsah buněk, které vyhledává vyhledávací argument vyhledávaný v předchozím kroku tutoriálu.

Vzhledem k tomu, že jsme identifikovali dvě vyhledávací pole v argumentu Lookup_array, musíme pro vyhledávací pole použít stejný postup . Funkce MATCH vyhledává pouze jedno pole pro každý zadaný výraz.

Chcete-li zadat více polí, znovu použijeme ampersand " & " pro spojování polí dohromady.

Tutorial Kroky

Tyto kroky se zadávají za čárkou zadanou v předchozím kroku na řádku Row_num v dialogovém okně funkce INDEX.

1. Klepněte na řádek Row_num za čárkou a umístěte kurzor na konec aktuálního záznamu.
2. Zvýrazněte buňky D6 až D11 v listu a zadejte rozsah. Toto je první pole, na které je funkce vyhledávána.
3. Zadejte ampersand " & " za odkazy na buňky D6: D11, protože chceme, aby funkce vyhledávala dvě pole.
4. Zvýrazněte buňky E6 až E11 v listu a zadejte rozsah. Toto je druhé pole, které je vyhledáváno.
5. Zadejte čárku "," za odkazem na buňku E3 dokončete položku argumentu Lookup_array funkce MATCH.
6. Ponechte dialogové okno funkce INDEX otevřené pro další krok v tutoriálu.

06 z 09

Přidání typu shody a dokončení funkce MATCH

Třetím a posledním argumentem funkce MATCH je argument Match_type.

Tento argument řekne aplikaci Excel, jak odpovídat hodnotám Lookup_value s hodnotami v Lookup_array. Možnosti jsou: 1, 0 nebo -1.

Tento argument je nepovinný. Pokud je funkce vynechána, použije výchozí hodnotu 1.

• pokud Match_type = 1 nebo je vynechán: MATCH najde nejvyšší 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á je přesně stejná jako hodnota 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 vyhledávací hodnotě. Data Lookup_array musí být seřazeny v sestupném pořadí.

Tutorial Kroky

Tyto kroky se zadávají za čárkou zadanou v předchozím kroku na řádku Row_num v dialogovém okně funkce INDEX.

1. Po čárce na řádku Row_num zadejte nulu " 0 ", protože chceme, aby vnořená funkce vrátila přesné shody s výrazy, které zadáváme v buňkách D3 a E3.
2. Zadejte uzavírací kulatý držák " ) " pro dokončení funkce MATCH.
3. Ponechte dialogové okno funkce INDEX otevřené pro další krok v tutoriálu.

07 ze dne 09

Zpět na funkci INDEX

Po dokončení funkce MATCH se přesuneme na třetí řádek otevřeného dialogového okna a zadáme poslední argument pro funkci INDEX.

Tento třetí a poslední argument je argument Column_num, který řeší Excel číslo sloupce v rozsahu D6 až F11, kde najde informace, které chceme vrátit funkcí. V tomto případě je dodavatelem pro titanové miniaplikace .

Tutorial Kroky

1. Klepněte na řádek Column_num v dialogovém okně.
2. Do tohoto řádku zadejte číslo třetí " 3 " (žádné uvozovky), protože hledáme údaje ve třetím sloupci rozsahu D6 až F11.
3. Klepněte na tlačítko OK nebo zavřete dialogové okno funkce INDEX. Musí zůstat otevřený pro další krok v tutoriálu - vytvoření vzorce pole .

08 z 09

Vytváření vzorec pole

Před uzavřením dialogového okna musíme vnořenou funkci zformovat do vzorce pole .

Vzorec pole je to, co umožňuje vyhledávat více výrazů v tabulce dat. V tomto tutoriálu chceme splnit dva pojmy: Widgety ze sloupce 1 a titan ze sloupce 2.

Vytvoření vzorce pole v aplikaci Excel se provádí současným stisknutím kláves CTRL , SHIFT a klávesy ENTER .

Účinkem stisknutí těchto tlačítek je obklopení funkce pomocí křivkových příchytků: {}, což naznačuje, že je nyní vzorec pole.

Tutorial Kroky

1. Dokončeným dialogovým oknem, který je stále otevřen od předchozího kroku v tomto výukovém programu, stiskněte a podržte klávesy CTRL a SHIFT na klávesnici, poté stiskněte a uvolněte klávesu ENTER .
2. Pokud se provede správně, dialogové okno se zavře a v buňce F3 - buňce, do které jsme zadali funkci, se objeví chyba # N / A.
3. V buňce F3 se objeví chyba # N / A, protože buňky D3 a E3 jsou prázdné. D3 a E3 jsou buňky, u kterých jsme v kroku č. 5 tutoriálu řekli funkci najít vyhledávací hodnoty. Po přidání dat do těchto dvou buněk bude chyba nahrazena informacemi z databáze .

09 z 09

Přidání kritérií vyhledávání

Posledním krokem v tutoriálu je přidání hledaných výrazů do našeho pracovního listu.

Jak již bylo řečeno v předchozím kroku, snažíme se shodovat s termíny Widgety ze sloupce 1 a Titanu ze sloupce 2.

Pokud a pouze v případě, že náš vzorec nalezne shodu pro oba termíny v příslušných sloupcích v databázi, vrátí hodnotu ze třetího sloupce.

Tutorial Kroky

1. Klikněte na buňku D3.
2. Zadejte Widgety a stiskněte klávesu Enter na klávesnici.
3. Klikněte na buňku E3.
4. Zadejte Titan a stiskněte klávesu Enter na klávesnici.
5. Jméno dodavatele Widgets Inc. by se mělo objevit v buňce F3 - umístění funkce, protože je jediným dodavatelem, který prodává titanové miniaplikace.
6. Když klepnete na buňku F3 úplnou funkci
   {= INDEX (D6: F11, MATCH (D3 a E3, D6: D11 & E6: E11, 0), 3)}
   se zobrazí ve sloupci vzorců nad tabulkou .

Poznámka: V našem příkladu existuje pouze jeden dodavatel pro titanové miniaplikace. Pokud by měl více než jeden dodavatel, dodavatel uvedený nejprve v databázi je vrácen funkcí.