Jak používat funkci VLOOKUP aplikace Excel

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.

Přesné vs. přibližné shody

VLOOKUP lze nastavit tak, že vrátí pouze informace, které přesně odpovídají Vyhledat _value nebo jej lze nastavit tak, aby vrátily přibližné shody

Rozhodujícím faktorem je Range_lookup argument:

• nastavena na hodnotu FALSE, vrátí pouze informace týkající se přesných shod Vyhledat _value
• nastaveno na TRUE nebo vynecháno, vrátí přesné nebo přibližné informace týkající se Vyhledat _value

Ve výše uvedeném příkladu Range_lookup je nastavena na hodnotu FALSE, takže VLOOKUP musí najít přesné shody pro daný termín Widgety v datové tabulce za účelem vrácení jednotkové ceny za danou položku. Pokud není nalezena přesná shoda, vrátí se funkce # N / A.

Poznámka: VLOOKUP nerozlišuje velká a malá písmena - obě Widgety a widgety jsou přijatelné hláskování pro výše uvedený příklad.

V případě, že existuje více hodnot shody - například Widgety je jsou uvedeny více než jednou ve sloupci 1 datové tabulky - informace týkající se první hodnoty shody, která se vyskytla v horní části dolů, jsou funkcí vráceny.

Zadání Argumentů funkce VLOOKUP aplikace Excel pomocí polohování

V prvním příkladu výše uvedeného obrázku se používá následující vzorec obsahující funkci VLOOKUP, který slouží k nalezení jednotkové ceny pro Widgety v tabulce dat.

= VLOOKUP (A2, $ A $ 5: $ B $ 8,2, FALSE)

Přestože tento vzorec může být jednoduše zadán do buňky listu, další volbou, jak je používán s níže uvedenými kroky, je použít dialogové okno funkce, zobrazené výše, pro zadání jeho argumentů.

• Použití dialogového okna často usnadňuje správné zadání argumentů funkce a eliminuje nutnost zadávat mezi argumenty oddělovače čárky.

Níže uvedené kroky byly použity k zadání funkce VLOOKUP do buňky B2 pomocí dialogového okna funkce.

Otevření dialogového okna VLOOKUP

1. Klepnutím na buňku B2 ji vytvoříte jako aktivní buňku - místo, kde jsou zobrazeny výsledky funkce VLOOKUP
2. Klikněte na tlačítko Vzorce záložka.
3. Vybrat Vyhledávání a reference z pásu k otevření rozevíracího seznamu funkcí
4. Klikněte na VLOOKUP v seznamu otevřete dialogové okno funkce

Data, která vstoupila do čtyř prázdných řádků dialogového okna, tvoří argumenty pro funkci VLOOKUP.

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, bod a kliknutí - které zahrnují zvýraznění požadovaného rozsahu buněk pomocí ukazatele myši - lze je použít k jejich zadání v dialogovém okně.

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 Table_array argument, který naznačuje, ž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ňky použitý pro lookup_value - A2 - na druhou stranu , 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í.

Relativní odkazy na buňky umožňují vyhledávat více položek ve stejné tabulce dat skopírováním VLOOKUP do více umístění a zadáním různých lookup_values .

Zadání funkčních argumentů

1. Klikněte na tlačítko Vyhledat _value line v VLOOKUP dialogové okno
2. Klepnutím na buňku A2 v listu zadejte tento odkaz na buňku jako search_key argument
3. Klikněte na tlačítko Table_array řádku dialogového okna
4. Zvýrazněte buňky A5 až B8 v listu a zadejte tento rozsah jako Table_array argument - záhlaví tabulky nejsou zahrnuty
5. zmáčkni F4 na klávesnici změňte rozsah na absolutní odkazy na buňky
6. Klikněte na tlačítko Col_index_num řádku dialogového okna
7. Zadejte a 2 na tomto řádku jako Col_index_num protože diskontní sazby se nacházejí ve sloupci 2 Table_array argument
8. Klikněte na tlačítko Range_lookup řádku dialogového okna
9. Zadejte slovo Nepravdivé jako Range_lookup argument
10. zmáčkni Zadejte na klávesnici zavřete dialogové okno a vrátíte se do pracovního listu
11. Odpověď 14,76 dolarů - jednotková cena za Widget - by se měla zobrazit v buňce B2 listu
12. Když kliknete na buňku B2, úplnou funkci = VLOOKUP (A2, $ A $ 5: $ B $ 8,2, FALSE) se zobrazí ve sloupci vzorců nad tabulkou

Excel VLOOKUP chybové zprávy

Následující chybové zprávy jsou přiřazeny k VLOOKUP:

Zobrazí se chyba # N / A (chyba "hodnota není dostupná"), pokud:

• The Vyhledat _value se nenachází v prvním sloupci rozsah argument
• The Table_array argument je nepřesný. Například argument může obsahovat prázdné sloupce na levé straně rozsahu
• The Range_lookup argument je nastaven na FALSE a přesnou shodu pro search_key argument nelze nalézt v prvním sloupci rozsah
• The Range_lookup argument je nastaven na TRUE a všechny hodnoty v prvním sloupci rozsah jsou větší než search_key

A #REF! chyba se zobrazí, pokud:

• The Col_index_num argument je větší než počet sloupců v tabulkovém poli.