Funkce VLOOKUP. Použití funkce VLOOKUP. Excel - VPR

Aplikační program

Jedna z dobře známých receptur aplikace Excel je vertikální prohlížení. Použití funkce VLOOK na první pohled vypadá poměrně komplikovaně, ale je to jen na první pohled.

Jak funguje aplikace Excel

Při práci s formulatem VLR je třeba vzít v úvahu, že hledá požadovanou hodnotu výlučně sloupci a nikoli řádky. Pro použití funkce je požadován minimální počet sloupců - dva, maximální chybí.

Funkce VLOOK vyhledává v daném kritériu libovolný formát (text, číselné údaje, měnu, datum a čas apod.) V tabulce. V případě nalezení záznamu vystupuje (nahrazuje) hodnotu zadanou ve stejném řádku, ale z požadovaného sloupce tabulky, tj. Odpovídá zadanému kritériu. Pokud nebyla nalezena požadovaná hodnota, zobrazí se chyba # N / A (v anglickém znění # N / A).

Potřeba používat

Funkce VLOOKUP přichází na pomoc operátora, když je potřeba rychle zjistit a aplikovat určitou hodnotu z větší tabulky v dalších výpočtech, analýze nebo prognózování. Hlavní věc při použití tohoto vzorce je zajistit, aby zadaná oblast hledání byla správně vybrána. Mělo by obsahovat všechny záznamy, tj. Od prvního po poslední.

Nejčastější případ použití VLF (Funkce Excel) - je to srovnání nebo doplnění údajů ve dvou tabulkách s použitím určitého kritéria. A rozsahy hledání mohou být velké a mohou obsahovat tisíce polí, umístěných na různých listech nebo knihách.

Zobrazí se funkce VLF, jak ji používat, jak provádět výpočty, jako příklad na obrázku výše. V tabulce velikostí maloobchodního prodeje se uvažuje v závislosti na regionu a manažerovi. Kritériem vyhledávání je konkrétní manažer (jeho jméno a příjmení) a požadovaná hodnota je částka jeho prodeje.

V důsledku funkce VLOOKUP je vytvořena nová tabulka, ve které konkrétní chtěný manažer rychle porovnává své prodejní částky.

Algoritmus pro vyplnění vzorce

Vzorec VLOOK je umístěn v záložce "Průvodce funkcemi" a v části "Odkazy a matice". Funkční dialog má následující vzhled:

Argumenty ve vzorci jsou zadávány v pořadí fronty:

• Vyhledávací hodnota je to, co má funkce najít, a volby jsou hodnoty buňky, jejich adresa, jméno, které jim daný operátor. V našem případě je to příjmení a jméno manažera.
• Tabulka - rozsah řádků a sloupců, ve kterých je kritérium prohledáváno.
• Číslo sloupce je jeho pořadové číslo, v němž se nachází částka prodeje, tj. Výsledek práce se vzorcem.
• Zobrazení intervalu. Obsahuje hodnotu FALSE nebo TRUE. A FALSE vrací pouze přesnou shodu, TRUE - umožňuje vyhledat přibližnou hodnotu.

Příklad použití funkce

Funkce VLOOKUP může mít následující příklad: když se jedná o firmu v tabulce aplikace Excel, sloupec A označuje název produktu a ve sloupci B odpovídající cenu. Chcete-li napsat větu ve sloupci C, musíte najít cenu pro určitý produkt, který chcete vypsat ve sloupci D.

Vzorec zapsaný v D bude vypadat takto: = VPR (C1-A1: B5- 2-0), tj. VLR (požadovaná hodnota je rozsah dat tabulky - pořadové číslo sloupce-0). Jako čtvrtý argument namísto 0 můžete použít FALSE.

Chcete-li vyplnit tabulku vět, výsledný vzorec musí být zkopírován do celého sloupce D.

Chcete-li určit plochu pracovního rozsahu dat, můžete použít absolutní reference. Chcete-li to provést, znaky $ předchází ručně před abecední a číselné hodnoty adresy nejvzdálenějších levých a pravých buněk tabulky. V našem případě má vzorec formu: = VPR (C1 - $ A $ 1: $ B $ 5- 2 - 0).

Chyby při používání

Funkce VLOOKUP nefunguje a ve výstupním sloupci se zobrazí chybová zpráva (# N / A nebo # N / A). K tomu dochází v těchto případech:

1. Formula je zaveden a sloupec požadovaných kritérií není vyplněn (v tomto případě sloupec C).
2. Sloupec C obsahuje hodnotu, která chybí ve sloupci A (v rozsahu vyhledávání dat). Chcete-li zkontrolovat dostupnost požadované hodnoty, vyberte sloupec kritérií a na kartě menu "Upravit" - "Najít" vložte tuto položku, spusťte vyhledávání. Pokud program nenajde, pak chybí.
3. Formáty buněk sloupců A a C (požadovaná kritéria) jsou různé, například jeden má text a druhý má číselný. Formát buňky můžete změnit, pokud přejdete na úpravu buněk (F2). Takové problémy obvykle vznikají při importu dat z jiných aplikací. Aby se předešlo takovým chybám, mohou být do vzorce VLOOK vkládány následující funkce: KEY nebo TEXT. Výkon těchto algoritmů automaticky převádí formát buněk.
4. V kódu funkce jsou netisknutelné znaky nebo mezery. Pak byste měli pečlivě zkontrolovat vzorec pro přítomnost vstupních chyb.
5. Je určeno přibližné hledání, tj. Čtvrtý argument funkce VLOOK má hodnotu 1 nebo TRUE a tabulka není tříděna podle vzestupné hodnoty. V tomto případě musí být sloupec požadovaných kritérií roztříděn ve vzestupném pořadí.

Při organizaci nové souhrnné tabulky mohou být zadaná vyhledávací kritéria v libovolném pořadí a pořadí a nemusí být obsažena v úplném seznamu (částečný výběr).

Vlastnosti použití jako intervalové zobrazení 1 nebo TRUE

Chyba pod číslem 5 je poměrně častá a je jasně znázorněna na obrázku níže.

V tomto příkladu se seznam jmen podle číslování třídí ne vzestupně, ale v sestupném pořadí. A jako použitý intervalový interval kritérium je TRUE (1), který okamžitě přeruší hledání, když je nalezena hodnota větší než požadovaná hodnota, takže se generuje chyba.

Při použití 1 nebo TRUE ve čtvrtém argumentu se musíte ujistit, že sloupec s požadovanými kritérii je roztříděn ve vzestupném pořadí. Použitím 0 nebo FID zmizí tato potřeba, ale také není možnost provést intervalové skenování.

Mějte na paměti, že je obzvláště důležité uspořádat intervalové tabulky. V opačném případě funkce VLOOKUP vyvede nesprávné data do buněk.

Jiné nuance při práci s funkcí VLOOKUP

Pro usnadnění práce s takovýmto vzorem můžete pojmenovat rozsah tabulky, ve které se vyhledávání provádí (druhý argument), jak je znázorněno na obrázku.

V tomto případě má nárok na oblast prodejního stolu. Pro tento účel je vybrána tabulka, s výjimkou hlaviček sloupců a pojmenováno pole s názvem (vlevo pod lištěm).

Další volba - název - znamená výběr řady dat, pak přejděte do nabídky "Insert" - "Name" - "Assign".

Chcete-li použít data umístěná na jiném listu sešitu pomocí funkce VLOOKUP, musíte zadat umístění datového rozsahu v druhém argumentu vzorce. Například = VPR (A1-List2! $ A $ 1: $ B $ 5- 2-0), kde list 2! - je odkaz na požadovaný list knihy a $ A $ 1: $ In $ 5 - adresa rozsahu načítání dat.

Příklad organizace vzdělávacího procesu s VPR

V aplikaci Excel je velmi vhodné používat funkci VPR nejen pro firmy zabývající se obchodem, ale také pro vzdělávací instituce, aby optimalizovaly proces srovnávání žáků (studentů) s jejich hodnoceními. Příklady těchto úkolů jsou uvedeny na následujících obrázcích.

K dispozici jsou dvě tabulky se seznamy studentů. Jeden s jejich odhady, druhá označuje věk. Je nutné srovnat obě tabulky tak, aby spolu s věkem studentů byly odvozeny jejich známky, tj. Zadejte další sloupec ve druhém seznamu.

Funkce VLOOK se dokonale vyrovná s řešením tohoto problému. Ve sloupci G pod nadpisem "Odhady" se zapíše odpovídající vzorec: = VPR (E4, B3: C13, 2, 0). Musí být zkopírován do celého sloupce tabulky.

V důsledku toho funkce VLR vytvoří odhady přijaté určitými studenty.

Příklad uspořádání vyhledávače s VLOOK

Dalším příkladem využití funkce VLOOK je organizace vyhledávače, pokud je v databázi podle zadaného kritéria nutné najít odpovídající hodnotu. Takže obrázek ukazuje seznam s přezdívkami zvířat a jejich příslušností k určitému druhu.

S pomocí VLP je vytvořena nová tabulka, ve které je snadné najít jméno zvířete jménem zvířete. Tyto vyhledávací stroje jsou relevantní při práci s velkými seznamy. Aby nedošlo k ručnímu revizi všech záznamů, můžete rychle použít vyhledávání a získat požadovaný výsledek.