Vzhľad vyhľadávania vo formáte Excel s viacerými kritériami

Použitím vzorca pre poľa v programe Excel môžeme vytvoriť vzhľad vyhľadávania, ktorý používa viacero kritérií na vyhľadanie informácií v databáze alebo tabuľke údajov.

Vzorec poľa obsahuje vkladanie funkcie MATCH do vnútra funkcie INDEX .

Tento návod obsahuje krok za krokom príklad vytvorenia vzoru vyhľadávania, ktorý používa viacero kritérií na nájdenie dodávateľa titánových miniaplikácií v ukážkovej databáze.

Nasledujúcimi krokmi v témach tutoriálu nižšie prechádzate vytváraním a použitím vzoru uvedeného na obrázku vyššie.

01 z 09

Zadanie údajov výučby

Prvým krokom v príručke je zadanie údajov do pracovného hárka programu Excel .

Ak chcete postupovať podľa krokov v príručke, zadajte údaje uvedené na obrázku vyššie do nasledujúcich buniek .

• Zadajte najvyšší rozsah údajov do buniek D1 až F2
• Zadajte druhý rozsah do buniek D5 až F11

Riadky 3 a 4 zostávajú prázdne, aby sa prispôsobil vzorec poľa vytvorený počas tohto tutoriálu.

Tutoriál nezahŕňa formátovanie zobrazené na obrázku, ale to neovplyvní spôsob fungovania vyhľadávacieho vzorca.

Informácie o možnostiach formátovania, ktoré sú podobné tým, ktoré sú uvedené vyššie, sú k dispozícii v tomto základnom výučbe formátovania programu Excel.

02 z 09

Spustenie funkcie INDEX

Funkcia INDEX je jednou z mála v aplikácii Excel, ktorá má viacero foriem. Funkcia má formát poľa a referenčný formulár .

Formulár Array vráti skutočné dáta z databázy alebo tabuľky dát, zatiaľ čo Referenčný formulár poskytuje odkaz na bunku alebo umiestnenie údajov v tabuľke.

V tomto výučbe budeme používať formulár Array, pretože chceme poznať meno dodávateľa pre titánové miniaplikácie, a nie odkaz na bunku pre tohto dodávateľa v našej databáze.

Každý formulár má iný zoznam argumentov, ktoré musia byť vybraté pred začiatkom funkcie.

Tutorial Kroky

1. Kliknutím na bunku F3 ju vytvoríte ako aktívnu bunku . Tu zadáme vnorenú funkciu.
2. Kliknite na kartu Vzorce v ponuke pásky .
3. Z rozbaľovacej ponuky vyberte položku Vyhľadávanie a referencia a otvorte rozbaľovací zoznam funkcií.
4. Kliknutím na položku INDEX v zozname vyvoláte dialógové okno Vybrať argumenty .
5. V dialógovom okne zvoľte pole pole, row_num, col_num .
6. Kliknutím na tlačidlo OK otvoríte dialógové okno funkcie INDEX.

03 z 09

Zadanie Argumentu Array funkcie INDEX

Prvý požadovaný argument je argument Array. Tento argument určuje rozsah buniek, ktoré sa majú vyhľadávať požadované údaje.

Pre tento návod bude tento argument našou vzorovou databázou .

Tutorial Kroky

1. V dialógovom okne funkcie INDEX kliknite na riadok Array .
2. Zvýraznite bunky D6 až F11 v pracovnom hárku a zadajte rozsah do dialógového okna.

04 z 09

Spustenie funkcie VLOŽENÝ MATCH

Pri vkladaní jednej funkcie do inej nie je možné otvoriť dialógové okno druhej alebo vnorenej funkcie, aby ste zadali potrebné argumenty .

Vnorená funkcia musí byť zadaná ako jeden z argumentov prvej funkcie.

V tomto návode bude vložená vnorená funkcia MATCH a jej argumenty zadané do druhého riadka dialógového okna funkcie INDEX - riadok Row_num .

Je dôležité poznamenať, že pri zadávaní funkcií ručne sú argumenty funkcie navzájom oddelené čiarkou "," .

Zadanie Argumentu Lookup_value funkcie MATCH

Prvým krokom pri vstupe do vnorenej funkcie MATCH je zadanie argumentu Lookup_value .

Hodnota Lookup_value bude umiestnenie alebo referencia bunky pre hľadaný výraz, ktorý chceme v databáze porovnať.

Normálne hodnota Lookup_value akceptuje iba jedno vyhľadávacie kritérium alebo termín. Aby sme mohli hľadať viacero kritérií, musíme rozšíriť hodnotu vyhľadávania .

To sa vykonáva zlučovaním alebo spájaním dvoch alebo viacerých bunkových odkazov pomocou symbolu ampersand " & ".

Tutorial Kroky

1. V dialógovom okne funkcie INDEX kliknite na riadok Row_num .
2. Zadajte zhodu názvu funkcie, ktorú nasleduje otvorená kruhová konzola " ( "
3. Kliknutím na bunku D3 zadajte odkaz do bunky do dialógového okna.
4. Zadajte ampersand " & " po referencii bunky D3, aby ste pridali druhú bunkovú referenciu.
5. Kliknutím na bunku E3 zadáte túto druhú bunkovú referenciu do dialógového okna.
6. Po vyplnení odkazu na bunku E3 zadajte čiarku "," a dokončite zadanie argumentu Lookup_value funkcie MATCH.
7. Ponechajte dialógové okno funkcie INDEX otvorené pre ďalší krok v príručke.

V poslednom kroku tutoriálu budú hodnoty Lookup_values ​​zadané do buniek D3 a E3 pracovného hárka.

05 z 09

Pridanie Lookup_array pre funkciu MATCH

Tento krok zahŕňa pridanie argumentu Lookup_array pre vnorenú funkciu MATCH.

Vyhľadávací_obrázok je rozsah buniek, ktoré funkcia MATCH vyhľadá, aby našla argument vyhľadávania, ktorý bol pridaný v predchádzajúcom kroku tutoriálu.

Vzhľadom na to, že sme identifikovali dve vyhľadávacie polia v argumentu Lookup_array, musíme urobiť to isté pre Lookup_array . Funkcia MATCH vyhľadáva iba jedno pole pre každý zadaný výraz.

Ak chcete zadať viac polí, opäť použijeme ampersand " & " na zoskupenie polí dohromady.

Tutorial Kroky

Tieto kroky sa zadajú po čiarke zadanej v predchádzajúcom kroku na riadok Row_num v dialógovom okne funkcie INDEX.

1. Kliknite na riadok Row_num za čiarou a umiestnite kurzor na koniec aktuálneho záznamu.
2. Zvýraznite bunky D6 až D11 v pracovnom hárku a zadajte rozsah. Toto je prvé pole, ktoré je funkciou vyhľadávania.
3. Zadajte znak ampersand " & " za odkazy na bunku D6: D11, pretože chceme, aby funkcia vyhľadala dve polia.
4. Zvýraznite bunky E6 až E11 v pracovnom hárku a zadajte rozsah. Toto je druhé pole, ktoré je funkciou vyhľadávania.
5. Zadajte čiarku "," za odkazom na bunku E3, aby ste vyplnili zadanie argumentu Lookup_array funkcie MATCH.
6. Ponechajte dialógové okno funkcie INDEX otvorené pre ďalší krok v príručke.

06 z 09

Pridanie typu zhody a dokončenie funkcie MATCH

Tretí a posledný argument funkcie MATCH je argument Match_type.

Tento argument informuje Excel o tom, ako porovnať hodnotu Lookup_value s hodnotami vo vyhľadávacom poli. Možnosti sú: 1, 0 alebo -1.

Tento argument je nepovinný. Ak sa vynechá, funkcia použije predvolenú hodnotu 1.

• ak Match_type = 1 alebo je vynechané: MATCH nájde najväčšiu hodnotu, ktorá je menšia alebo rovná vyhľadávaciemu hodnoteniu. Údaje Lookup_array sa musia zoradiť vo vzostupnom poradí.
• ak match_type = 0: MATCH nájde prvú hodnotu, ktorá je presne rovná hodnote Lookup_value. Údaje Lookup_array možno triediť v ľubovoľnom poradí.
• ak Match_type = -1: MATCH nájde najmenšiu hodnotu, ktorá je väčšia alebo rovná hodnote Lookup_value. Údaje Lookup_array sa musia zoradiť v zostupnom poradí.

Tutorial Kroky

Tieto kroky sa zadajú po čiarke zadanej v predchádzajúcom kroku na riadok Row_num v dialógovom okne funkcie INDEX.

1. Po čiarke na riadku Row_num zadajte nulu " 0 ", pretože chceme, aby vnorená funkcia vrátila presné zhody s výrazmi, ktoré zadáme do buniek D3 a E3.
2. Zadajte uzatváraciu kruhovú konzolu " ) ", aby ste dokončili funkciu MATCH.
3. Ponechajte dialógové okno funkcie INDEX otvorené pre ďalší krok v príručke.

07 z 09

Späť na funkciu INDEX

Po dokončení funkcie MATCH sa presunieme na tretí riadok otvoreného dialógového okna a zadáme posledný argument pre funkciu INDEX.

Tento tretí a posledný argument je argument Column_num, ktorý informuje Excel o čísle stĺpca v rozsahu D6 až F11, kde nájde informácie, ktoré chceme vrátiť funkciou. V tomto prípade je dodávateľom nástrojov na výrobu titánu .

Tutorial Kroky

1. Kliknite na riadok Column_num v dialógovom okne.
2. Do tohto riadku zadajte číslo tri " 3 " (bez úvodzoviek), pretože hľadáme údaje v treťom stĺpci rozsahu D6 až F11.
3. Kliknite na tlačidlo OK alebo zatvorte dialógové okno funkcie INDEX. Musí zostať otvorený pre ďalší krok v príručke - vytvoriť vzorec poľa .

08 z 09

Vytvorenie vzorca pre pole

Pred zatvorením dialógového okna musíme premeniť našu vnorenú funkciu na vzorec poľa .

Vzorec poľa je to, čo mu umožňuje vyhľadávať viaceré výrazy v tabuľke údajov. V tomto výučbe hľadáme dva výrazy: Widgety zo stĺpca 1 a titán zo stĺpca 2.

Vytvorenie vzorca pre pole v programe Excel sa vykonáva stlačením klávesov CTRL , SHIFT a ENTER na klávesnici súčasne.

Účinok stlačenia týchto klávesov je obklopiť funkciu s kučeravými ramenami: {}, čo naznačuje, že je teraz vzorec poľa.

Tutorial Kroky

1. Po dokončení otvoreného dialógového okna z predchádzajúceho kroku tohto tutoriálu stlačte a podržte klávesy CTRL a SHIFT na klávesnici, potom stlačte a uvoľnite kláves ENTER .
2. Ak sa vykoná správne, dialógové okno sa zatvorí a v bunke F3 - bunke, do ktorej sme vstúpili do funkcie, sa zobrazí chyba # N / A.
3. V bunke F3 sa objaví chyba # N / A, pretože bunky D3 a E3 sú prázdne. D3 a E3 sú bunky, kde sme povedali funkciu nájsť Lookup_values ​​v kroku 5 tutoriálu. Po pridaní údajov do týchto dvoch buniek sa chyba nahradí informáciou z databázy .

09 z 09

Pridávanie kritérií vyhľadávania

Posledným krokom v príručke je pridanie hľadaných výrazov do nášho pracovného hárka.

Ako sme uviedli v predchádzajúcom kroku, snažíme sa zhodovať s termínmi Widgety zo stĺpca 1 a Titanium zo stĺpca 2.

Ak av našom vzorci nájde zhodu pre obidva výrazy v príslušných stĺpcoch v databáze, vráti hodnotu z tretieho stĺpca.

Tutorial Kroky

1. Kliknite na bunku D3.
2. Napíšte položku Widgety a stlačte kláves Enter na klávesnici.
3. Kliknite na bunku E3.
4. Zadajte Titanium a stlačte kláves Enter na klávesnici.
5. Názov dodávateľa Widgets Inc. by sa mal objaviť v bunke F3 - miesto funkcie, pretože je jediným dodávateľom, ktorý predáva produkty Titanium Widgets.
6. Keď kliknete na bunku F3 úplnú funkciu
   {= INDEX (D6: F11, MATCH (D3 a E3, D6: D11 & E6: E11, 0), 3)}
   sa zobrazí vo formulári nad pracovným hárkom .

Poznámka: V našom príklade bol len jeden dodávateľ pre titánové miniaplikácie. Ak by mal viac ako jeden dodávateľ, dodávateľ uvedený v databáze ako prvý je vrátený funkciou.