Čo by ste mali vedieť
- Najprv vytvorte funkciu INDEX a potom spustite vnorenú funkciu MATCH zadaním argumentu Lookup_value.
- Ďalej pridajte argument Lookup_array nasledovaný argumentom Match_type a potom zadajte rozsah stĺpcov.
- Potom premeňte vnorenú funkciu na vzorec poľa stlačením Ctrl+ Shift+ Enter. Nakoniec pridajte hľadané výrazy do pracovného hárka.
Tento článok vysvetľuje, ako vytvoriť vyhľadávací vzorec, ktorý používa viacero kritérií v Exceli na nájdenie informácií v databáze alebo tabuľke údajov pomocou vzorca poľa. Vzorec poľa zahŕňa vnorenie funkcie MATCH do funkcie INDEX. Informácie pokrývajú Excel pre Microsoft 365, Excel 2019, Excel 2016, Excel 2013, Excel 2010 a Excel pre Mac.
Postupujte spolu s návodom
Ak chcete postupovať podľa krokov v tomto návode, zadajte vzorové údaje do nasledujúcich buniek, ako je znázornené na obrázku nižšie. Riadky 3 a 4 sú ponechané prázdne, aby sa do nich zmestil vzorec poľa vytvorený počas tohto návodu. (Upozorňujeme, že tento návod nezahŕňa formátovanie zobrazené na obrázku.)
- Zadajte horný rozsah údajov do buniek D1 až F2.
- Zadajte druhý rozsah do buniek D5 až F11.
Vytvorte funkciu INDEX v Exceli
Funkcia INDEX je jednou z mála funkcií v Exceli, ktorá má viacero foriem. Funkcia má formulár poľa a referenčný formulár. Formulár Array vracia údaje z databázy alebo tabuľky údajov. Referenčný formulár obsahuje odkaz na bunku alebo umiestnenie údajov v tabuľke.
V tomto návode sa na nájdenie názvu dodávateľa titánových miniaplikácií používa formulár poľa namiesto odkazu na bunku tohto dodávateľa v databáze.
Pri vytváraní funkcie INDEX postupujte podľa týchto krokov:
- Vyberte bunku F3, aby sa z nej stala aktívna bunka. Do tejto bunky sa zadá vnorená funkcia.
-
Prejsť na Formulas.
Image - Vyberte Vyhľadať a referencie na otvorenie rozbaľovacieho zoznamu funkcií.
- Výberom INDEX otvoríte dialógové okno Vybrať argumenty.
- Vyberte array, row_num, column_num.
- Výberom OK otvoríte dialógové okno Argumenty funkcie. V Exceli pre Mac sa otvorí Formula Builder.
- Umiestnite kurzor do textového poľa Array.
-
Zvýraznite bunky D6 až F11 v pracovnom hárku a zadajte rozsah do dialógového okna.
Dialógové okno Argumenty funkcií nechajte otvorené. Vzorec nie je dokončený. Vzorec vyplníte podľa pokynov nižšie.
Image
Spustiť vnorenú funkciu MATCH
Pri vnorení jednej funkcie do druhej nie je možné otvoriť tvorca vzorcov druhej alebo vnorenej funkcie na zadanie potrebných argumentov. Vnorená funkcia musí byť zadaná ako jeden z argumentov prvej funkcie.
Pri manuálnom zadávaní funkcií sú argumenty funkcie navzájom oddelené čiarkou.
Prvým krokom na zadanie vnorenej funkcie MATCH je zadanie argumentu Lookup_value. Lookup_value je umiestnenie alebo odkaz na bunku pre hľadaný výraz, ktorý sa má nájsť v databáze.
Hodnota Lookup_value akceptuje iba jedno vyhľadávacie kritérium alebo výraz. Ak chcete vyhľadať viacero kritérií, rozšírte hodnotu Lookup_value zreťazením alebo spojením dvoch alebo viacerých odkazov na bunky pomocou symbolu ampersand (&).
- V dialógovom okne Argumenty funkcie umiestnite kurzor do textového poľa Row_num.
- Zadajte MATCH(.
- Vyberte bunku D3 a zadajte odkaz na bunku do dialógového okna.
- Zadajte & (ampersand) za odkaz na bunku D3 a pridajte odkaz na druhú bunku.
-
Vyberte bunku E3 a zadajte odkaz na druhú bunku.
-
Zadajte , (čiarka) za odkazom na bunku E3 na dokončenie zadávania argumentu Lookup_value funkcie MATCH.
Image V poslednom kroku tutoriálu budú hodnoty Lookup_values zadané do buniek D3 a E3 pracovného hárka.
Dokončiť vnorenú funkciu MATCH
Tento krok zahŕňa pridanie argumentu Lookup_array pre vnorenú funkciu MATCH. Lookup_array je rozsah buniek, ktoré funkcia MATCH hľadá, aby našla argument Lookup_value pridaný v predchádzajúcom kroku tutoriálu.
Pretože v argumente Lookup_array boli identifikované dve polia vyhľadávania, to isté sa musí urobiť pre pole Lookup_array. Funkcia MATCH vyhľadáva iba jedno pole pre každý zadaný výraz. Ak chcete zadať viacero polí, použite ampersand na zreťazenie polí.
- Umiestnite kurzor na koniec údajov v textovom poli Row_num. Kurzor sa zobrazí za čiarkou na konci aktuálneho záznamu.
-
Zvýraznite bunky D6 až D11 na zadanie rozsahu. Tento rozsah je prvé pole, ktoré funkcia hľadá.
- Za odkazy na bunky zadajte & (ampersand) D6:D11. Tento symbol spôsobí, že funkcia vyhľadá dve polia.
- Zvýraznite bunky E6 až E11 na zadanie rozsahu. Tento rozsah je druhé pole, ktoré funkcia hľadá.
-
Zadajte , (čiarku) za odkaz na bunku E3 na dokončenie zadávania argumentu Lookup_array funkcie MATCH.
Image - Ponechajte dialógové okno otvorené pre ďalší krok v návode.
Pridať argument typu ZHODY
Tretím a posledným argumentom funkcie MATCH je argument Match_type. Tento argument hovorí Excelu, ako priradiť Lookup_value k hodnotám v Lookup_array. Dostupné možnosti sú 1, 0 alebo -1.
Tento argument je voliteľný. 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á Lookup_value. Údaje Lookup_array musia byť zoradené vzostupne.
- Ak Match_type=0, MATCH nájde prvú hodnotu, ktorá sa rovná Lookup_value. Údaje Lookup_array je možné triediť v ľubovoľnom poradí.
- Ak Match_type=-1, MATCH nájde najmenšiu hodnotu, ktorá je väčšia alebo rovná Lookup_value. Údaje Lookup_array musia byť zoradené v zostupnom poradí.
Tieto kroky zadajte po čiarke zadanej v predchádzajúcom kroku v riadku Row_num vo funkcii INDEX:
- Za čiarku do textového poľa Row_num zadajte 0 (nulu). Toto číslo spôsobí, že vnorená funkcia vráti presné zhody s výrazmi zadanými v bunkách D3 a E3.
-
Zadajte ) (uzavretá okrúhla zátvorka), aby ste dokončili funkciu MATCH.
Image - Ponechajte dialógové okno otvorené pre ďalší krok v návode.
Dokončiť funkciu INDEX
Funkcia MATCH je hotová. Je čas presunúť sa do textového poľa Column_num v dialógovom okne a zadať posledný argument pre funkciu INDEX. Tento argument informuje Excel, že číslo stĺpca je v rozsahu D6 až F11. V tomto rozsahu nájde informácie vrátené funkciou. V tomto prípade dodávateľ titánových miniaplikácií.
- Umiestnite kurzor do textového poľa Column_num.
-
Zadajte 3 (číslo tri). Toto číslo hovorí, že vzorec má hľadať údaje v treťom stĺpci rozsahu D6 až F11.
Image - Ponechajte dialógové okno otvorené pre ďalší krok v návode.
Vytvoriť vzorec poľa
Pred zatvorením dialógového okna premeňte vnorenú funkciu na vzorec poľa. Toto pole umožňuje funkcii vyhľadávať viaceré výrazy v tabuľke údajov. V tomto návode sa zhodujú dva výrazy: Widgety zo stĺpca 1 a titán zo stĺpca 2.
Ak chcete vytvoriť vzorec poľa v Exceli, stlačte CTRL, SHIFT a ENTERkláves súčasne. Po stlačení je funkcia ohraničená zloženými zátvorkami, čo znamená, že funkcia je teraz pole.
- Výberom OK zatvorte dialógové okno. V Exceli pre Mac vyberte Hotovo.
- Ak chcete zobraziť vzorec, vyberte bunku F3 a potom umiestnite kurzor na koniec vzorca na paneli vzorcov.
- Ak chcete previesť vzorec na pole, stlačte CTRL+ SHIFT+ ENTER.
- V bunke F3 sa zobrazí chyba N/A. Toto je bunka, do ktorej bola zadaná funkcia.
-
V bunke F3 sa zobrazí chyba N/A, pretože bunky D3 a E3 sú prázdne. D3 a E3 sú bunky, v ktorých funkcia hľadá hodnotu Lookup_value. Po pridaní údajov do týchto dvoch buniek sa chyba nahradí informáciami z databázy.
Image
Pridať kritériá vyhľadávania
Posledným krokom je pridanie hľadaných výrazov do pracovného hárka. Tento krok sa zhoduje s výrazmi Widgety zo stĺpca 1 a titán zo stĺpca 2.
Ak vzorec nájde zhodu pre oba výrazy v príslušných stĺpcoch v databáze, vráti hodnotu z tretieho stĺpca.
- Vyberte bunku D3.
- Zadajte Widgety.
- Vyberte bunku E3.
- Napíšte Titanium a stlačte Enter.
- Názov dodávateľa, Widgets Inc., sa zobrazí v bunke F3. Toto je jediný uvedený dodávateľ, ktorý predáva titánové widgety.
-
Vyberte bunku F3. Funkcia sa zobrazí v riadku vzorcov nad pracovným hárkom.
{=INDEX(D6:F11, MATCH(D3&E3, D6:D11&E6:E11, 0); 3)}
V tomto príklade existuje iba jeden dodávateľ titánových miniaplikácií. Ak by existovalo viac dodávateľov, funkcia vráti dodávateľa uvedeného v databáze ako prvého.
Image
