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 tutoriál obsahuje postupný příklad vytvoření vzoru vyhledávání, který používá několik kritérií pro nalezení dodavateletitanové miniaplikace v ukázkové databázi. Postupujte podle pokynů a zjistěte, jak snadné je vytvořit vlastní vzorce pro vlastní vyhledávání.
Připravte sešit aplikace Excel s daty
Chcete-li postupovat podle kroků v tomto návodu, musíte začít zadáním vzorových dat do následujících buněk, jak je uvedeno na obrázku výše. Řádky 3 a 4 jsou prázdné, aby vyhovovaly vzoru pole vytvořenému během tohoto kurzu.
- Zadejte nejvyšší rozsah dat do buněk D1 na F2
- Zadejte druhý rozsah do buněk D5 na F11
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 z 08Vytvoření funkce INDEX v aplikaci Excel
The INDEX funkce je jedna z mála v aplikaci Excel, která má několik formulářů. Funkce má funkci Formulář pole a 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 tutoriálu použijeme formulář Array, protože chceme znát jméno dodavatele pro widgety titanu spíše než odkaz na buňky u tohoto dodavatele v naší databázi. Pro vytvoření funkce INDEX postupujte takto:
- Klikněte na buňku F3 aby byla aktivní buňka - to je místo, kde vstoupíme do vnořené funkce.
- Klikněte na tlačítko Vzorce kartu nabídky pásu.
- Vybrat Vyhledávání a reference z pruhu pásu otevřete rozbalovací funkci.
- Klikněte na INDEX v seznamu vyvolejte Vyberte Argumenty dialogové okno.
- Vyber pole, řádka_číslo, kol_num v dialogovém okně.
- Klikněte na tlačítko OK otevřete dialogové okno funkce INDEX; otevře se nástroj Formula Builder v aplikaci Excel.
- V Formula Builder, klikněte na tlačítko Array řádek.
- Zvýrazněte buňky D6 na F11, klepnutím a přetažením do listu zadejte rozsah do stavitele.
Ve starších verzích aplikace Excel Formula Builder bude nahrazen Funkční argumenty okno. Použijte stejné kroky, které jsou uvedeny v tomto návodu, do okna.
03 ze dne 08Spusťte funkci Vnořený MATCH
Při vkládání jedné funkce do jiné není možné otevřít druhou nebo vnořenou funkci formuláře o zadat potřebné argumenty. Vnořená funkce musí být zadána jako jedna ze tříděných prvních funkcí.
Při zadávání funkcí ručně jsou argumenty funkce navzájem odděleny čárkou.
Prvním krokem při zadávání vnořené funkce MATCH je zadání Vyhledávací_hodnota argument. 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; tjeho je prováděno spojením dvou nebo více buněčných odkazů pomocí symbolu ampersand - &.
- V Formula Builder, klikněte na tlačítko Row_num řádek.
- Zadejte název funkce ZÁPAS následovaný otevřenou kulatou konzolou.
- Klikněte na buňku D3 zadejte odkaz buňky do dialogového okna.
- Zadejte ampersand za odkaz na buňku D3 za účelem přidání druhého odkazu na buňku.
- Klikněte na buňku E3 pro zadání této druhé buněčné reference.
- Zadejte čárku za odkazem na buňku E3 dokončete zadání funkce MATCH Vyhledávací_hodnota argument.
V posledním kroku tutoriálu, náš Hodnoty vyhledávání budou vloženy do buněk D3 a E3 listu.
04 ze dne 08Dokončete funkci Vnořený MATCH
Tento krok zahrnuje přidání Lookup_array argument pro vnořené ZÁPAS funkce. 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 prohledává pouze jedno pole pro každý zadaný výraz, a proto vstupuje do více polí, opět použijeme ampersand pro spojování polí dohromady.
Tyto kroky je nutné zadat za čárkou zadanou v předchozím kroku na Row_num line v INDEX funkce.
- Klikněte na tlačítko Row_num Po čárce umístěte kurzor na konec aktuálního záznamu.
- Zvýrazněte buňky D6 na D11 v listu zadejte rozsah - toto je první pole, ve které je funkce vyhledávána.
- Zadejte ampersand za odkazy na buňky D6: D11 protože chceme, aby funkce vyhledávala dvě pole.
- Zvýrazněte buňky E6 na E11 v listu pro zadání rozsahu - toto je druhé pole funkce je prohledávání.
- Zadejte čárku za odkazem na buňku E3 dokončení zápisu ZÁPAS funkce Lookup_array argument.
- Opustit INDEX dialogové okno funkce pro další krok v tutoriálu.
Přidat argument argumentu typu MATCH
Třetí a poslední argument funkce MATCH je Match_type argument; to řekne programu Excel, jak odpovídat Vyhledávací_hodnota s hodnotami v Lookup_array - dostupné volby 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 hodnotě Lookup_value. Data Lookup_array musí být seřazeny v sestupném pořadí.
Tyto kroky je nutné zadat za čárkou zadanou v předchozím kroku na Row_num line v INDEX funkce.
- Po čárku na Row_num line, zadejte nulu - 0 - protože chceme, aby vnořené funkce vrátily přesné shody s výrazy, které jsme zadali v buňkách D3 a E3.
- Zadejte uzavírací kruhový držák - ) - dokončení ZÁPAS funkce.
- Opustit Formula Builder otevřete další krok v tutoriálu.
Dokončení funkce INDEX
Nyní, když ZÁPAS je provedena, my se přesuneme na třetí řádek formuláře Builder a zadáme poslední argument pro INDEX funkce. Tento třetí a poslední argument je Sloupec_num argument, který řeší Excel číslo sloupce v rozsahu D6 na F11; Zde nalezneme informace, které chceme vrátit funkcí. V tomto případě dodavatel pro titan widgets.
- Klikněte na tlačítko Sloupec_num řádek.
- Zadejte číslo tři - 3 - na tomto řádku, protože hledáme údaje ve třetím sloupci rozsahu D6 na F11.
- Opět opusťte Formula Builder otevřete další krok v tutoriálu.
Vytváření vzorec pole
Před uzavřením Formula Builder, musíme zvětšit vnořenou funkci na vzorec pole; to je to, co dovoluje naší funkci vyhledávat více výrazů v tabulce dat. V tomto tutoriálu se snažíme splnit dva pojmy: Widgety ze sloupců 1 a Titan ze sloupce 2.
Vytvoření vzorce pole v aplikaci Excel se provádí stisknutím klávesy CTRL, POSUN, a ENTER na klávesnici současně. Po stisknutí bude funkce obklopena kudrnatými příchytkami, což naznačuje, že funkce je nyní pole.
- Zavři Formula Builder klepnutím na tlačítko Hotovo tlačítko.
- Poté vyberte buňku F4 a stiskněte tlačítko Zadejte pro zobrazení vzorce.
- Chcete-li převést vzorec na pole, současně stiskněte tlačítko CTRL + SHIFT + ENTER na klávesnici.
- Pokud je provedeno správně a # N / A chyba se objeví v buňce F3 - buňce, do které jsme zadali funkci.
- The # N / A v buňce se zobrazí chyba F3 protože buňky D3 a E3 jsou prázdné. D3 a E3 jsou buňky, kde jsme tuto funkci napsali Hodnoty vyhledávání. Po přidání dat do těchto dvou buněk bude chyba nahrazena informacemi z databáze.
Přidání kritérií vyhledávání
Nakonec přidáme hledané výrazy do našeho pracovního listu. Jak již bylo řečeno v předchozím kroku, snažíme se splnit podmínky Widgety ze sloupců 1 a Titan 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 z třetího sloupce.
- Klikněte na buňku D3.
- Typ Widgety a stiskněte tlačítko Zadejte na klávesnici.
- Klikněte na buňku E3.
- Typ Titan a stiskněte tlačítko Zadejte na klávesnici.
- Jméno dodavatele Widgety Inc. by se měla objevit v buňce F3 - umístění funkce, neboť je jediným dodavatelem, který prodává výrobky Titanium Widgets.
- Po klepnutí na buňku F3 se zobrazí úplná funkce ve vzorci nad tabulkou.
{= INDEX (D6: F11, MATCH (D3 a E3, D6: D11 & E6: E11, 0), 3)}
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í.
