Jak řešit chyby VLOOKUP v Excelu

Jak

PodleBen Stockton

Poslední aktualizace dne13. ledna 2020

Existuje několik věcí, které vyzařují pot Microsoft Excel uživatelé víc, než si mysleli na chybu VLOOKUP. Pokud nejste s Excelem příliš obeznámeni, je VLOOKUP jednou z nejobtížnějších nebo alespoň jednou z nejméně pochopitelných dostupných funkcí.

Účelem VLOOKUP je vyhledávat a vracet data z jiného sloupce v tabulce aplikace Excel. Bohužel, pokud máte špatný vzorec VLOOKUP, Excel na vás hodí chybu. Podíváme se na některé běžné chyby VLOOKUP a vysvětlíme, jak je odstranit.

Omezení VLOOKUPu

Než začnete používat VLOOKUP, měli byste si být vědomi, že to není vždy nejlepší volba pro uživatele Excelu.

Nejprve jej nelze použít k vyhledávání dat vlevo od něj. Zobrazí také pouze první nalezenou hodnotu, což znamená, že VLOOKUP není možnost pro rozsahy dat s duplicitními hodnotami. Váš vyhledávací sloupec musí také být nejvzdálenějším levým sloupcem v datovém rozsahu.

V níže uvedeném příkladu je nejvzdálenější sloupec (sloupec A) se používá jako sloupec vyhledávání. V rozsahu nejsou žádné duplicitní hodnoty a data vyhledávání (v tomto případě data z sloupec B) je napravo od sloupce vyhledávání.

Funkce INDEX a MATCH by byly dobrými alternativami, pokud by některý z těchto problémů byl problém, stejně jako nová funkce XLOOKUP v Excelu, nyní v beta testování.

VLOOKUP také vyžaduje, aby byla data uspořádána do řádků, aby bylo možné přesně vyhledávat a vracet data. HLOOKUP by byla dobrá alternativa, pokud tomu tak není.

Jak vysvětlíme dále, existují některá další omezení ve vzorcích VLOOKUP, která mohou způsobit chyby.

Chyby VLOOKUP a # N / A

Jednou z nejčastějších chyb VLOOKUP v Excelu je # N / A chyba. K této chybě dochází, když VLOOKUP nemůže najít hodnotu, kterou hledáte.

Začněte tím, že ve vašem rozsahu dat nemusí být vyhledávaná hodnota, nebo jste použili nesprávnou hodnotu. Pokud vidíte chybu N / A, znovu zkontrolujte hodnotu ve vzorci VLOOKUP.

Pokud je tato hodnota správná, pak vaše vyhledávací hodnota neexistuje. Předpokládá se, že k vyhledání přesných shod používáte VLOOKUP range_lookup argument nastaven na NEPRAVDIVÉ.

Ve výše uvedeném příkladu hledání a ID studenta s číslo 104 (v cele G4) vrátí # N / A chyba protože minimální ID číslo v rozsahu je 105.

Pokud range_lookup argument na konci vzorce VLOOKUP chybí nebo je nastaven na SKUTEČNÝ, pak VLOOKUP vrátí chybu # N / A, pokud není rozsah dat seřazen vzestupně.

Pokud je hodnota vyhledávání menší než nejnižší hodnota v rozsahu, vrátí také chybu # N / A.

Ve výše uvedeném příkladu ID studenta hodnoty jsou smíšené. Navzdory hodnotě 105 VLOOKUP, který existuje v rozsahu, nemůže pomocí nástroje range_lookup argument nastaven na SKUTEČNÝ protože sloupec A není seřazeno vzestupně.

Mezi další běžné důvody chyb # N / A patří použití vyhledávacího sloupce, který není nejvzdálenější, a použití buňky odkazy na hodnoty hledání, které obsahují čísla, ale jsou formátovány jako text nebo obsahují nadbytečné znaky jako mezery.

Odstraňování chyb #VALUE

#HODNOTA Chyba je obvykle znamením, že vzorec obsahující funkci VLOOKUP je nějakým způsobem nesprávný.

Ve většině případů je to obvykle kvůli buňce, kterou odkazujete jako svou hodnotu vyhledávání. maximální velikost hodnoty vyhledávání VLOOKUP je 255 znaků.

Pokud pracujete s buňkami, které obsahují delší řetězce znaků, VLOOKUP je nebude schopen zpracovat.

Jediným řešením tohoto problému je nahrazení vzorce VLOOKUP kombinovaným vzorcem INDEX a MATCH. Například tam, kde jeden sloupec obsahuje buňku s řetězcem více než 255 znaků, lze k nalezení těchto dat použít místo vnořené funkce MATCH uvnitř INDEXu.

V níže uvedeném příkladu INDEX vrátí hodnotu v buňce B4, pomocí rozsahu v sloupec A identifikovat správný řádek. To používá vnořené ZÁPAS funkce k identifikaci řetězce v sloupec A (obsahující 300 znaků), která odpovídá buňce H4.

V tomto případě je to buňka A4, s INDEX vracející se 108 (hodnota B4).

Tato chyba se také objeví, pokud jste ve vzorci použili nesprávný odkaz na buňky, zejména pokud používáte datový rozsah z jiného sešitu.

Aby vzorec fungoval správně, je třeba vložit odkazy do sešitu v hranatých závorkách.

Pokud narazíte na chybu #VALUE, znovu zkontrolujte vzorec VLOOKUP a potvrďte správnost odkazů na buňky.

#NAME a VLOOKUP

Pokud vaše chyba VLOOKUP není chybou # VALUE nebo # N / A, je to pravděpodobně #NÁZEV chyba. Než začnete panikařit při pomyšlení na tuto, buďte ujištěni - je to nejjednodušší opravit chybu VLOOKUP.

Chyba #NAME se objeví, když jste v aplikaci Excel chybně zapsali pravdu, ať už jde o VLOOKUP nebo jinou funkci, například SUM. Klikněte na buňku VLOOKUP a znovu zkontrolujte, zda jste VLOOKUP ve skutečnosti napsali správně.

Pokud neexistují žádné další problémy, váš vzorec VLOOKUP bude fungovat, jakmile bude tato chyba opravena.

Použití dalších funkcí aplikace Excel

Je to odvážné prohlášení, ale funguje podobně VLOOKUP změní váš život. Přinejmenším to změní váš pracovní život a učiní z Excelu výkonnější nástroj pro analýzu dat.

Pokud pro vás VLOOKUP není vhodný, využijte výhod těchto možností top Excel funkce namísto.