Vyhledávání na webu

Zapomeňte na VLOOKUP v Excelu: Zde je důvod, proč používám XLOOKUP

K ilustraci mých bodů v tomto článku použiji přímé odkazy na buňky, protože jsou jasnější než strukturované odkazy. Nebudu také mluvit výslovně o HLOOKUP, protože – kromě jeho směru – funguje přesně stejným způsobem jako VLOOKUP.

Syntaxe: XLOOKUP a VLOOKUP

Než vysvětlím, proč preferuji XLOOKUP před VLOOKUP, ukážu vám, jak fungují.

XLOOKUP

XLOOKUP má šest argumentů:

=XLOOKUP(a,b,c,d,e,f)

kde

  • a (povinné) je vyhledávací hodnota,
  • b (povinné) je vyhledávací pole,
  • c (povinné) je návratové pole,
  • d (volitelné) je text, který se má vrátit, pokud vyhledávací hodnota (a) není nalezena ve vyhledávacím poli (b),
  • e (volitelné) je režim shody a
  • f (volitelné) je režim vyhledávání.

V tomto příkladu chci, aby Excel vyhledal ID zaměstnance na základě jména v buňce H1 a vrátil výsledek do buňky H2.

Za tímto účelem napíšu do buňky H2 následující vzorec:

=XLOOKUP(H1,B2:B12,A2:A12,"Invalid name",0,1)

V tomto případě buňka H1 obsahuje hodnotu, kterou Excel potřebuje vyhledat (Mary), B2 až B12 je místo, kde lze tuto hodnotu najít (jména zaměstnanců), A2 až A12 je místo, odkud bude načten odpovídající výsledek (ID zaměstnanců ) a "Neplatný název" je to, co chci, aby Excel vrátil, pokud vyhledávací hodnota není nikde ve vyhledávacím poli k nalezení.

Zvolil jsem výchozí hodnoty pro argumenty e a f, protože chci přesnou shodu a chci, aby Excel vyhledával z horní části vyhledávacího pole (více o tyto později).

SVYHLEDAT

Zde je syntaxe VLOOKUP, která má čtyři argumenty:

=VLOOKUP(a,b,c,d)

kde

  • a (povinné) je vyhledávací hodnota,
  • b (povinné) je vyhledávací a návratové pole,
  • c (povinné) je indexové číslo sloupce a
  • d (volitelné) je režim shody.

V tomto příkladu chci, aby Excel vyhledal národnost na základě ID zaměstnance v buňce H4 a vrátil výsledek do buňky H5.

K tomu napíšu do buňky H5

=VLOOKUP(H4,A2:E12,5,FALSE)

protože buňka H4 obsahuje vyhledávací hodnotu (ID 3264), buňky A2 až E12 jsou místa, kde Excel potřebuje najít tuto hodnotu a odpovídající návrat, pátý sloupec (země) je pole, ve kterém bude výsledek nalezen, a chci přesná shoda (NEPRAVDA).

Není třeba počítat sloupce

Jedním z klíčových rozdílů mezi VLOOKUP a XLOOKUP je to, že první vyžaduje, abych uvedl číslo indexu sloupce, kde bude výsledek nalezen, zatímco druhý nikoli. Je to proto, že VLOOKUP kombinuje vyhledávací a návratová pole do jednoho argumentu, zatímco XLOOKUP je definuje ve dvou samostatných argumentech.

SVYHLEDAT

Zadání indexového čísla sloupce ve VLOOKUP může vést k několika problémům:

  • Je snadné náhodně nesprávně spočítat sloupce, zvláště pokud má vaše vyhledávací pole stovky sloupců.
  • Přidání nebo odebrání sloupců může ovlivnit přesnost indexového čísla sloupce.
  • Vyhledávací pole musí být sloupec zcela vlevo a pole návratu musí být vpravo. To omezuje všestrannost funkce VLOOKUP.

XLOOKUP

Na druhou stranu XLOOKUP obsahuje vyhledávací pole jako samostatný argument, což znamená, že můžete ocenit následující výhody:

  • Není v tom žádné počítání! Jednoduše pomocí myši vyberte návratové pole, když se dostanete k této části vzorce. To šetří čas a zvyšuje přesnost.
  • Vzhledem k tomu, že návratové pole je v určeném rozsahu buněk, odebrání nebo přidání sloupců do tabulky neovlivní vzorec XLOOKUP.
  • Návratové pole může být na obou stranách vyhledávacího pole, což znamená, že XLOOKUP je všestrannější než VLOOKUP.

Více možností přibližné shody

VLOOKUP i XLOOKUP mohou vrátit přesnou shodu (přesně odpovídající hodnotu v řádku) a přibližnou shodu (blízká odpovídající hodnota).

SVYHLEDAT

Přesněji řečeno, přibližná shoda VLOOKUP (ve vzorci označena jako TRUE) prohledává vyhledávací pole směrem dolů, dokud nenajde hodnotu, která je větší než vyhledávací hodnota. Potom vrátí hodnotu, která je o řádek výše.

Vezměte si skóre studenta D 65 v příkladu níže. VLOOKUP převezme vyhledávací hodnotu 65, zkontroluje vyhledávací pole, najde první hodnotu, která je větší než vyhledávací hodnota (v tomto případě 70), a poté vrátí známku z řádku výše (stupeň C).

To představuje dvě nevýhody. Nejprve musí být vyhledávací pole uvedeno ve vzestupném pořadí. Za druhé, musím do vyhledávacího pole přidat řádek FAIL, protože ve VLOOKUP nemám možnost uvést neodpovídající argument.

XLOOKUP

Na druhou stranu XLOOKUP nabízí tři alternativy k jednorozměrné přibližné shodě VLOOKUP:

  • -1: Toto vrátí další nejmenší hodnotu ve vyhledávacím poli, pokud neexistuje přesná shoda.
  • 1: Toto vrátí další největší hodnotu ve vyhledávacím poli, pokud neexistuje přesná shoda.
  • 2: Toto používá zástupné znaky, které umožňují flexibilnější vyhledávání.

Opět si vezměte Studenta D jako příklad. Se skóre 65 Excel uvidí, že další nejmenší hodnota ve vyhledávacím poli je 60 a další největší hodnota je 70. Protože student ještě nedosáhl prahu pro známku B (70), potřebuji Excel, aby vezměte další nejmenší hodnotu ve vyhledávacím poli (60), abyste vrátili stupeň C z návratového pole. Ve vzorci tedy jako možnost shody napíšu -1.

To znamená, že vyhledávací pole nemusí být v pořádku – Excel prohledá celé vyhledávací pole, aby našel nejbližší vyšší nebo nižší hodnoty, pokud neexistuje přesná shoda. Mohu také vynechat řádek FAIL z mého vyhledávacího pole, protože pokud skóre studenta neodpovídá žádné známce, mohu použít čtvrtý argument v syntaxi XLOOKUP k vrácení slova FAIL.

Další režimy vyhledávání (trasy)

Zatímco VLOOKUP vyhledává od prvního k poslednímu a vrací první odpovídající hodnotu, XLOOKUP nabízí čtyři možnosti vyhledávání.

SVYHLEDAT

Ve většině scénářů vyhledávání vrátí vyhledávání ve vyhledávacím poli od prvního do posledního výsledky, které potřebujete. Máte-li například adresář telefonních čísel a jmen lidí, bude použití funkce VLOOKUP k vyhledání telefonního čísla na základě zadaného jména fungovat dobře, protože jméno této osoby se pravděpodobně objeví pouze jednou.

XLOOKUP

XLOOKUP vám však umožňuje zvolit orientaci vyhledávání:

  • 1: Hledejte před poslední
  • -1: Hledat od posledního k prvnímu
  • 2: Binární vyhledávání (s vyhledávacím polem ve vzestupném pořadí)
  • -2 Binární vyhledávání (s vyhledávacím polem v sestupném pořadí)

Výhodou vyhledávání od posledního k prvnímu je, že můžete najít nejnovější výskyt hodnoty ve vyhledávacím poli, které je uvedeno v pořadí podle data. Abyste toho dosáhli pomocí funkce VLOOKUP, museli byste nejprve obrátit pořadí dat.

Definujte chybový výstup

Opravdu užitečnou funkcí XLOOKUP je argument „pokud nebyl nalezen“, který VLOOKUP nemá.

SVYHLEDAT

Pokud není ve vzorci SVYHLEDAT přesná shoda nalezena hodnota, Excel vrátí obávanou chybovou zprávu #N/A. Abych to napravil, vždy jsem do funkce IFERROR vložil funkce SVYHLEDAT, abych mohl definovat výstup, pokud VLOOKUP nenajde shodu.

=IFERROR(VLOOKUP(B6,$E$2:$F$8,2,TRUE)," ")

I když je to rozumné řešení, psaní vzorců je mnohem složitější a může skrýt problémy, které by mohly ovlivnit přesnost analýzy vašich dat.

XLOOKUP

Vzhledem k tomu, že XLOOKUP je připraven s argumentem „pokud není nalezen“, můžete definovat, co se stane, pokud se hodnota neobjeví ve vyhledávání, což vám ušetří nutnost vkládat vzorec do IFERROR.

Vraťte rozlité pole

Jednou z nejodolnějších vlastností funkce VLOOKUP je, že může vrátit pouze jednu shodu, zatímco XLOOKUP může vrátit rozsah.

XLOOKUP

V tomto příkladu psaní

=XLOOKUP(I1,A2:A7,B2:F7)

vyhledá hodnotu v buňce I1 (v tomto případě Quizpicable Me), hledá tuto hodnotu v buňkách A2 až A7 a vrátí všechny odpovídající hodnoty jako pole.

SVYHLEDAT

Pokud bych se to pokusil replikovat pomocí funkce VLOOKUP, napsal bych

=VLOOKUP(I1,A2:F7,2:6)

ale toto vrátí #REF! chyba, protože třetí argument (číslo indexu sloupce) může být pouze jedna číslice, nikoli rozsah. To znamená, že XLOOKUP je mnohem přizpůsobivější, protože může vrátit buď jednu hodnotu, nebo rozsah, v závislosti na parametrech, které do vzorce přidáte.

Protože verze Excelu před rokem 2019 nepodporují XLOOKUP, nezahazujte VLOOKUP a HLOOKUP úplně! Stále mohou nastat případy, kdy je potřebujete použít, například když posíláte tabulku někomu, kdo již několik let neupgradoval svůj balík Office.

Související články