01 z 03
Najděte přibližné shody s daty s VLOOKUPem aplikace Excel
Jak funguje funkce VLOOKUP
Funkce VLOOKUP aplikace Excel, která slouží pro vertikální vyhledávání , může být použita k vyhledání specifických informací umístěných v tabulce dat nebo databáze.
VLOOKUP normálně vrací jako výstup jeden datový prostor. Jak to dělá:
- Zadáte název nebo vyhledávací_hodnotu, která udává VLOOKUP , v jakém řádku nebo záznamu datové tabulky hledá požadovaná data
- Zadáte číslo sloupce - známo jako col_index_num - požadovaných dat
- Funkce vyhledá hodnotu lookup_value v prvním sloupci datové tabulky
- VLOOKUP pak vyhledá a vrátí požadované informace z jiného pole stejného záznamu pomocí dodaného čísla sloupce
Nejdříve třídění dat
Ačkoli to není vždy vyžadováno, je obvykle nejlépe nejdříve zoradit rozsah dat, které VLOOKUP vyhledává ve vzestupném pořadí, pomocí prvního sloupce rozsahu pro klíč řazení.
Pokud data nejsou seřazeny, VLOOKUP může vrátit nesprávný výsledek.
Syntaxe a argumenty funkce VLOOKUP
Syntaxe funkce odkazuje na rozložení funkce a obsahuje název funkce, závorky a argumenty .
Syntaxe funkce VLOOKUP je:
= VLOOKUP (vyhledávací_hodnota, tabulka_array, kol_index_num, rozsah_lookup)
vyhledávací hodnota _value - (povinná) hodnota, kterou hledáte - např. množství prodané na obrázku výše
table_array - (povinné) Jedná se o tabulku dat, která VLOOKUP hledá, aby našla informace, které jste po.
- Table_array musí obsahovat alespoň dva sloupce dat
- První sloupec obvykle obsahuje hodnotu lookup_value
col_index_num - (povinné) číslo sloupce požadované hodnoty.
- Číslování začíná sloupcem search_key jako sloupec 1
- Pokud je index_count_indexu nastaven na číslo větší než počet sloupců vybraných v argumentu table_array a #REF ! chyba je vrácena funkcí
range_lookup - (volitelně) označuje, zda je rozsah seřazen ve vzestupném pořadí.
- Údaje v prvním sloupci se používají jako klíč řazení
- Booleovská hodnota - TRUE nebo FALSE jsou pouze přijatelné hodnoty
- Při vynechání je tato hodnota ve výchozím nastavení nastavena na TRUE
- Pokud je hodnota TRUE nebo je vynechána a první sloupec rozsahu není roztříděn ve vzestupném pořadí, může dojít k nesprávnému výsledku
- Pokud je nastavena hodnota TRUE nebo je vynechána a přesná shoda pro vyhledávací _value není nalezena, nejbližší shoda, která je menší než velikost nebo hodnota, se používá jako tlačítko search_key
- Je-li nastaven na hodnotu FALSE, VLOOKUP přijímá přesnou shodu pro vyhledávací _value . Pokud existuje více hodnot shody, vrátí se první shodná hodnota
- Je-li nastavena hodnota FALSE a není nalezena žádná odpovídající hodnota pro vyhledávací klíč , vrátí funkce # N / A chyba
Příklad: Najděte slevový poměr pro zakoupenou částku
Příklad na obrázku výše používá funkci VLOOKUP k nalezení diskontní sazby, která se liší v závislosti na množství zakoupených položek.
Příklad ukazuje, že sleva na nákup 19 položek je 2%. Důvodem je, že sloupec Množství obsahuje rozsahy hodnot. Výsledkem je, že VLOOKUP nemůže najít přesnou shodu. Místo toho musí být nalezena přibližná shoda, aby se vrátila správná diskontní sazba.
Chcete-li najít přibližné shody:
- uspořádat data ve sloupci table_array ve vzestupném pořadí;
- nastavte argument range_lookup na hodnotu TRUE
V následujícím příkladu se používá následující vzorec obsahující funkci VLOOKUP pro zjištění slevy na množství zakoupeného zboží.
= VLOOKUP (C2, $ C $ 5: $ D $ 8,2, TRUE)
I když tento vzorec může být jednoduše zadán do buňky listu, další volbou, jak se používá s níže uvedenými kroky, je použít dialogové okno funkce pro zadání jeho argumentů.
- Použití dialogového okna často usnadňuje správné zadání argumentů funkce.
Otevření dialogového okna VLOOKUP
Kroky používané pro zadání funkce VLOOKUP zobrazené na obrázku B2 do buňky jsou:
- Klepnutím na buňku B2 ji vytvoříte jako aktivní buňku - místo, kde jsou zobrazeny výsledky funkce VLOOKUP
- Klikněte na kartu Vzorce .
- Z rozbalovací nabídky vyberte položku Vyhledání a reference a otevřete rozbalovací seznam funkcí
- Klikněte na VLOOKUP v seznamu pro vyvolání dialogového okna funkce
02 z 03
Zadání Argumentů funkce VLOOKUP aplikace Excel
Ukazuje na odkazy na buňky
Argumenty pro funkci VLOOKUP se zadávají do samostatných řádků dialogového okna, jak je znázorněno na obrázku výše.
Odkazy na buňky, které mají být použity jako argumenty, mohou být zadány do správné čáry nebo, jak je to provedeno v níže uvedených krocích, ukazující, které zahrnují zvýraznění požadovaného rozsahu buněk pomocí ukazatele myši, lze je použít pro zadání do dialogového okna .
Výhody použití bodování zahrnují:
- Je to rychlejší než psaní;
- Došlo k méně chyb při zadávání správných odkazů na buňky.
Použití relativních a absolutních buněčných odkazů s argumenty
Není neobvyklé používat vícenásobné kopie VLOOKUP k navrácení různých informací ze stejné tabulky dat. Pro usnadnění tohoto postupu lze často VLOOKUP zkopírovat z jedné buňky do druhé. Když jsou funkce zkopírovány do jiných buněk, je třeba dbát na to, aby výsledné odkazy na buňky byly správné vzhledem k nové poloze funkce.
Ve výše uvedeném obrázku dolarové značky ( $ ) obklopují odkazy na buňky pro argument table_array označující, že jsou absolutní odkazy na buňky , což znamená, že se nezmění, pokud je funkce zkopírována do jiné buňky. To je žádoucí, protože více kopií VLOOKUP by všichni odkazovali na stejnou tabulku dat jako zdroj informací.
Odkaz na buňku použitý pro vyhledávací_hodnotu, na druhé straně , není obklopen znaky dolaru, což z něj činí relativní odkaz buňky. Relativní odkazy na buňky se mění, když jsou zkopírovány, aby odrážely svou novou polohu vzhledem k poloze údajů, na které odkazují.
Zadání funkčních argumentů
- Klepněte na řádek Lookup _value v dialogovém okně VLOOKUP
- Klepnutím na buňku C2 v listu zadejte tuto buňku jako argument search_key
- Klepněte na řádek Table_array v dialogovém okně
- Zvýrazněte buňky C5 až D8 v listu a zadejte tento rozsah jako argument Table_array - záhlaví tabulky nejsou zahrnuty
- Stisknutím klávesy F4 na klávesnici změníte rozsah na absolutní odkaz na buňky
- Klepněte na řádek Col_index_num dialogového okna
- Zadejte hodnotu 2 na tento řádek jako argument Col_index_num , protože diskontní sazby se nacházejí ve sloupci 2 argumentu Table_array
- Klikněte na řádek Range_lookup v dialogovém okně
- Zadejte slovo True jako argument Range_lookup
- Stisknutím klávesy Enter na klávesnici zavřete dialogové okno a vrátíte se do pracovního listu
- Odpověď 2% (diskontní sazba pro zakoupené množství) by se měla zobrazit v buňce D2 listu
- Když klepnete na buňku D2, zobrazí se v řádku vzorců nad pracovním listem úplná funkce = VLOOKUP (C2, $ C $ 5: $ D $ 8,2, TRUE)
Proč VLOOKUP vrátil 2% jako výsledek
- V příkladu sloupec Množství neobsahuje přesnou shodu pro hodnotu search_key hodnotu 19.
- Vzhledem k tomu, že argument is_sorted je nastaven na TRUE, VLOOKUP nalezne přibližnou shodu s hodnotou search_key .
- Nejbližší hodnota ve velikosti, která je stále menší než hodnota search_key 19, je 11.
- Společnost VLOOKUP proto hledá procentní sazbu v řádku obsahujícím 11 a v důsledku toho vrací diskontní sazbu ve výši 2%.
03 ze dne 03
Excel VLOOKUP nefunguje: # N / A a #REF chyby
VLOOKUP Chybové zprávy
Následující chybové zprávy jsou přiřazeny k VLOOKUP.
A # N / A ("hodnota není k dispozici") Zobrazí se chyba Pokud:
- Hodnota _vyhledávání se nenachází v prvním sloupci argumentu rozsahu
- Argument Table_array je nepřesný. Například argument může obsahovat prázdné sloupce na levé straně rozsahu
- Argument Range_lookup je nastaven na hodnotu FALSE a přesná shoda pro argument search_key nemůže být nalezena v prvním sloupci rozsahu
- Argument Range_lookup je nastaven na TRUE a všechny hodnoty v prvním sloupci rozsahu jsou větší než klíč search_key
A #REF! ("odkaz mimo rozsah") Zobrazí se chyba Pokud:
- Argument Col_index_num je větší než počet sloupců v table_array