Viacnásobné VLOOKUP - vyhľadávanie podľa kritérií

Máte tieto možnosti

Najrýchlejší a najlepší spôsob, ako vyhľadať konkrétne kritériá, je použiť funkciu VLOOKUP. S ním je možné nájsť hodnotu v programe Microsoft Excel v milisekundách.

VLOOKUP - takto to funguje s kritériom vyhľadávania

Aby používatelia programu Excel pochopili, ako funguje VLOOKUP s viacerými kritériami vyhľadávania, musia najskôr porozumieť tomu, ako je funkcia VLOOKUP v zásade štruktúrovaná. Nasledujúci príklad ukazuje počet dočasných zamestnancov zamestnaných v spoločnosti v rôznych mesiacoch kalendárneho roka.

VLOOKUP by mal slúžiť na to, aby ukázal, koľko brigádnikov bolo zamestnaných v marci a decembri.

Vzorec VLOOKUP v príklade za december je:

VLOOKUP (D8, A: B, 2, 0)

Vzorec hľadá zodpovedajúce čísla pre dočasných zamestnancov nasledovne:

1. Nájdite hodnotu D8 (mesiac december) v stĺpcoch A a B.
2. Nájdite hodnotu v stĺpci B. Toto je reprezentované číslom 2 vo vzorci.
3. 0 vo vzorci znamená, že je potrebné vyhľadať presnú hodnotu.

Funkcia VLOOKUP hľadá jediné kritérium vyhľadávania. Matica vzorca je oblasť, v ktorej sa nachádzajú príslušné hodnoty. Stĺpec VLOOKUP musí navyše obsahovať hodnotu, ktorú hľadáte.

Dôležité:

Pri funkcii VLOOKUP musí byť kritérium vyhľadávania vždy v stĺpci 1. Vpravo od vyhľadávacieho kritéria sa musia zobraziť aj súvisiace hľadané hodnoty. Ak je tabuľka štruktúrovaná odlišne, funkcia VLOOKUP nebude fungovať. V tomto prípade môže byť pri vytváraní požadovaného dotazu užitočná kombinácia funkcií INDEX a COMPARE.

VLOOKUP s viacerými kritériami vyhľadávania - ako integrovať funkciu IF

V uvedenom príklade sa nás pýtali, koľko brigádnikov pracovalo v spoločnosti v určitý mesiac. Pomocou funkcie VLOOKUP je možné konkrétne hodnoty ľahko odvodiť. V nasledujúcom príklade môžete vidieť rozsiahly zoznam článkov s 36 položkami [1], z ktorého chcete rýchlo a jednoducho filtrovať vhodné čísla článkov na základe niekoľkých kritérií.

Kritériá vyhľadávania sú uvedené v tabuľke vpravo. Nájdené číslo článku by sa malo objaviť v bunke H8.

Prvým krokom je zahrnutie vzorca do funkcie VLOOKUP, ktorý môžete použiť na nájdenie čísla položky na základe kritéria vyhľadávania. Tento vzorec potom rozbalíte krok za krokom pre ďalšie kritériá vyhľadávania.

Na nájdenie čísla položky pre veľkosť z bunky H3 použite nasledujúci vzorec v bunke H8: [2]

= VLOOKUP (H3, A3: E40, 5

Vzorec hľadá zodpovedajúce číslo článku nasledujúcim spôsobom:

1. Nájdite hodnotu H3 (veľkosť 142) v stĺpcoch A až E.
2. Nájdite hodnotu v stĺpci E (číslo článku). Toto je reprezentované číslom 5 vo vzorci.

Stručne povedané, funkcia VLOOKUP odovzdáva ako prvý argument kompletný zoznam, v ktorom hľadáte obsah. Kritérium vyhľadávania, ktoré sa má hľadať v prvom stĺpci prenesenej oblasti, je zamerané ako druhý argument. Tretí argument definuje stĺpec, z ktorého sa má vrátiť výsledok.

Odošlite zoznam zoradený vzostupne na základe prvého stĺpca, aby Excel mohol nájsť ďalšiu nižšiu hodnotu. Vzorec vyhľadá číslo článku 2.253 z bunky E16. Pretože veľkosť 142 z bunky H3 nie je v zozname, nájde sa nasledujúca nižšia hodnota 139.

Zahrňte druhé kritérium vyhľadávania

V ďalšom kroku chcete tiež vyhľadať skupinu v bunke H4 ako druhé kritérium. Malo by byť nájdené iba číslo článku, v ktorom sa nachádza skupina, ktorú hľadáte.

Za týmto účelom pridajte do vzorca v bunke H8 funkciu IF a vložte vzorec ako maticový vzorec:

= VLOOKUP (H3; IF (B3: B40 = H4; A3: E40; „“); 5)

Info:

V programe Microsoft Excel sa rozlišuje medzi konvenčnými vzorcami a výkonnejšími maticovými vzorcami. Maticové vzorce umožňujú používateľom Excelu vykonávať komplexné výpočty. Tieto nie je možné vykonať pomocou štandardných vzorcov. Pretože po zadaní vzorca je potrebné stlačiť kombináciu klávesov „CTRL + SHIFT + ENTER“, maticové vzorce sú medzinárodne známe ako vzorce alebo CSE (CTRL + SHIFT + ENTER).

Po zadaní vzorca potvrďte kombináciu klávesov Ctrl + Shift + Enter ako maticový vzorec. [3]

Výsledkom je dodanie položky číslo 1.188. Vzorec nájde v stĺpci „Veľkosť“ hodnotu 126. Ide o najväčšiu hodnotu v tabuľke. Je menšia alebo rovná vyhľadávanej hodnote 142 a má obsah C2 vo vzťahu k skupine.

Funkcia IF vo vzorci kontroluje zhodu s obsahom bunky H4 s každou bunkou v rozsahu B3: B40. Hneď ako dôjde k zhode, zodpovedajúci riadok v oblasti B3: E40 sa odošle do funkcie VLOOKUP.

Kedykoľvek môžete jednoducho pridať ďalšie kritériá vyhľadávania

Rovnakým spôsobom môžete pridať ďalšie vyhľadávacie kritériá. To dosiahnete vnorením viacerých IF funkcií do vzorca. Aby vyhovovali všetkým štyrom kritériám vyhľadávania, zahrňte do vzorca v bunke H8 ďalšie dve vnorené funkcie IF:

= VLOOKUP (H3; IF (B3: B40 = H4; IF (C3: C40 = H5; IF (D3: D40 = H6; A3: E40; „“); „“); „“); 5)

Po potvrdení maticového vzorca pomocou kombinácie klávesov Ctrl + Shift + Enter funkcia VLOOKUP vráti číslo článku 1.748. Toto je číslo z riadku 14 pre veľkosť 125. Je to riadok s najvyššou hodnotou v stĺpci „Veľkosť“, v ktorom sú splnené všetky tri ďalšie kritériá. [4]

Prvá funkcia IF neprekročí ihneď príslušný riadok funkcii VLOOKUP, ak je kontrola kladná. Namiesto toho iná funkcia IF kontroluje, či existuje zhoda s druhým dodatočným kritériom. Ak je aj táto kontrola pozitívna, použije sa tretia funkcia IF. Iba vtedy, keď všetky tri funkcie IF vrátia kladnú kontrolu, tretia funkcia IF odovzdá zodpovedajúci riadok funkcii VLOOKUP. Týmto spôsobom môžete vnoriť až sedem funkcií IF, a teda skontrolovať až osem kritérií.

Zhrnutie: Toto je účinný spôsob hľadania viacerých kritérií v programe Excel

Používatelia programu Microsoft Excel sa pri svojej každodennej práci neustále stretávajú s otázkou, ako čítať údaje a informácie z rozsiahlych alebo vnorených tabuliek. Používatelia Excelu nájdu najefektívnejšiu referenčnú funkciu vo funkcii VLOOKUP. Hlavnou výhodou funkcie VLOOKUP a ďalších referenčných funkcií je to, že v predvolenom nastavení vyhľadávajú nasledujúcu nižšiu hodnotu, akonáhle nenájdu hľadanú hodnotu.

Pretože funkcia VLOOKUP je v predvolenom nastavení obmedzená na jeden hľadaný výraz, pre viacero kritérií vyhľadávania je potrebné použiť rozšírený vzorec. Na nájdenie niekoľkých hľadaných výrazov pomocou funkcie VLOOKUP je vhodný maticový vzorec s funkciami IF. Kontroluje jednotlivé funkcie IF jednu za druhou a ak je výsledok pozitívny, odkazuje na VLOOKUP. To zaisťuje, že pre rozsiahle tabuľky s rôznymi hľadanými výrazmi sa zobrazí správny výsledok.

FAQ

Čo môže funkcia VLOOKUP robiť?

Funkcia VLOOKUP v programe Microsoft Excel je jednou z najdôležitejších a najúčinnejších funkcií v programe Excel. Prostredníctvom VLOOKUP môžete automaticky vyhľadávať v rozsiahlej tabuľke konkrétnu hodnotu. Výsledok sa upraví a vloží do inej bunky.

Kedy používate funkciu IF v programe EXCEL?

Funkcia IF je jednou z najpoužívanejších funkcií v programe Microsoft Excel. V prvom kroku sa na definovanie podmienky používa funkcia IF. V druhom kroku prepojíte IF napríklad s funkciou THEN a určíte, ktoré informácie sa majú odosielať. Na určenie alternatívneho výsledku je možné použiť aj funkciu ELSE. Praktický príklad: AK zamestnanec A odpracoval viac ako 240 dní v roku POTOM bonus A INÝ bonus B.

Aký je rozdiel medzi vzorcami poľa a štandardnými vzorcami v programe Excel?

V programe Excel sú najlepšie známe konvenčné štandardné vzorce. V programe Excel sú maticové vzorce výkonné vzorce, pomocou ktorých je možné vykonávať komplexné výpočty. Aby bol vzorec poľa spustený, musí byť vzorec poľa po zadaní potvrdený kombináciou klávesov „CTRL + SHIFT + ENTER“. Klávesová skratka ukazuje programu Excel, že ide o vzorec poľa.