Obsah
Tato lekce vysvětluje, jak rychle řešit situaci, kdy funkce funguje VPR (VLOOKUP) nechce pracovat v Excelu 2013, 2010, 2007 a 2003 a jak identifikovat a opravit běžné chyby a překonat omezení VPR.
V několika předchozích článcích jsme prozkoumali různé aspekty funkce VPR v Excelu. Pokud jste si je pozorně přečetli, měli byste být nyní odborníkem v této oblasti. Ne nadarmo se však mnozí odborníci na Excel domnívají VPR jedna ze složitějších funkcí. Má spoustu omezení a funkcí, které se stávají zdrojem mnoha problémů a chyb.
V tomto článku najdete jednoduchá vysvětlení chyb #AT (#N/A), # Název? (#JMÉNO?) a #HODNOTA! (#VALUE!), které se objeví při práci s funkcí VPR, jakož i techniky a metody, jak se s nimi vypořádat. Začneme nejčastějšími případy a nejzřejmějšími důvody. VPR nefunguje, proto je lepší prostudovat příklady v pořadí, v jakém jsou uvedeny v článku.
Oprava chyby #N/A ve funkci VLOOKUP v Excelu
Ve vzorcích s VPR chybová zpráva #AT (#N/A) znamená není k dispozici (žádná data) – zobrazí se, když Excel nemůže najít hodnotu, kterou hledáte. To se může stát z několika důvodů.
1. Požadovaná hodnota je špatně napsaná
Dobrý nápad nejprve zkontrolovat tuto položku! Překlepy se často vyskytují, když pracujete s velmi velkým množstvím dat, která se skládají z tisíců řádků, nebo když je hledaná hodnota zapsána do vzorce.
2. Chyba #N/A při hledání přibližné shody pomocí funkce VLOOKUP
Pokud použijete vzorec s přibližnou podmínkou hledání shody, tj. argument range_lookup (range_lookup) je PRAVDA nebo není zadán, váš vzorec může hlásit chybu # N / A ve dvou případech:
- Hodnota k vyhledání je menší než nejmenší hodnota v poli, které se hledá.
- Sloupec hledání není řazen vzestupně.
3. Chyba #N/A při hledání přesné shody pomocí funkce VLOOKUP
Pokud hledáte přesnou shodu, tedy argument range_lookup (range_lookup) je FALSE a přesná hodnota nebyla nalezena, vzorec také ohlásí chybu # N / A. Přečtěte si další informace o tom, jak hledat přesné a přibližné shody s funkcí VPR.
4. Sloupec hledání není úplně vlevo
Jak asi víte, jedno z nejvýznamnějších omezení VPR jde o to, že nemůže směřovat doleva, proto musí být vyhledávací sloupec ve vaší tabulce úplně vlevo. V praxi na to často zapomínáme, což vede k nefungujícímu vzorci a chybě. # N / A.
Rozhodnutí: Pokud není možné změnit datovou strukturu tak, aby byl vyhledávací sloupec zcela vlevo, můžete použít kombinaci funkcí INDEX (INDEX) a VÍCE EXPOZOVANĚ (MATCH) jako flexibilnější alternativa pro VPR.
5. Čísla jsou formátována jako text
Další zdroj chyb # N / A ve vzorcích s VPR jsou čísla v textovém formátu v hlavní tabulce nebo vyhledávací tabulce.
K tomu obvykle dochází, když importujete informace z externích databází nebo když před číslo zadáte apostrof, abyste zachovali úvodní nulu.
Nejviditelnější znaky čísla v textovém formátu jsou znázorněny na obrázku níže:
Čísla lze navíc ukládat ve formátu obecně (Všeobecné). V tomto případě je patrná pouze jedna vlastnost – čísla jsou zarovnána k levému okraji buňky, zatímco standardně k pravému okraji.
Rozhodnutí: Pokud se jedná o jednu hodnotu, stačí kliknout na ikonu chyby a vybrat Převést na číslo (Převést na číslo) z kontextové nabídky.
Pokud je to situace s mnoha čísly, vyberte je a klikněte pravým tlačítkem myši na vybranou oblast. V zobrazené kontextové nabídce vyberte Formát buněk (Formát buněk) > karta Číslo (Číslo) > formát Číslo (Číselné) a stiskněte OK.
6. Na začátku nebo na konci je mezera
Toto je nejméně zřejmý důvod chyby. # N / A ve funkci VPR, protože je vizuálně obtížné tyto mezery navíc vidět, zejména při práci s velkými tabulkami, kdy je většina dat mimo obrazovku.
Řešení 1: Mezery navíc v hlavní tabulce (kde je funkce VLOOKUP)
Pokud se v hlavní tabulce objeví mezery navíc, můžete se ujistit, že vzorce fungují správně, uzavřením argumentu lookup_value (lookup_value) do funkce TRIM (TRIM):
=VLOOKUP(TRIM($F2),$A$2:$C$10,3,FALSE)
=ВПР(СЖПРОБЕЛЫ($F2);$A$2:$C$10;3;ЛОЖЬ)
Řešení 2: Nadbytečné mezery ve vyhledávací tabulce (ve vyhledávacím sloupci)
Pokud jsou ve vyhledávacím sloupci mezery navíc – jednoduché způsoby # N / A ve vzorci s VPR nelze se vyhnout. Namísto VPR Můžete použít maticový vzorec s kombinací funkcí INDEX (INDEX), VÍCE EXPOZOVANĚ (Zápas) a TRIM (TRIM):
=INDEX($C$2:$C$10,MATCH(TRUE,TRIM($A$2:$A$10)=TRIM($F$2),0))
=ИНДЕКС($C$2:$C$10;ПОИСКПОЗ(ИСТИНА;СЖПРОБЕЛЫ($A$2:$A$10)=СЖПРОБЕЛЫ($F$2);0))
Protože se jedná o maticový vzorec, nezapomeňte stisknout Ctrl + Shift + Enter místo obvyklých vstoupitabyste vzorec zadali správně.
Chyba #HODNOTA! ve vzorcích s funkcí VLOOKUP
Ve většině případů aplikace Microsoft Excel hlásí chybu #HODNOTA! (#VALUE!), když hodnota použitá ve vzorci neodpovídá datovému typu. Vztahující se k VPR, pak jsou obvykle dva důvody chyby #HODNOTA!.
1. Hodnota, kterou hledáte, je delší než 255 znaků
Buďte opatrní: funkce VPR nelze vyhledat hodnoty obsahující více než 255 znaků. Pokud hledaná hodnota překročí tento limit, zobrazí se chybová zpráva. #HODNOTA!.
Rozhodnutí: Použijte spoustu funkcí INDEX+MATCH (INDEX + MATCH). Níže je uveden vzorec, který pro tento úkol postačí:
=INDEX(C2:C7,MATCH(TRUE,INDEX(B2:B7=F$2,0),0))
=ИНДЕКС(C2:C7;ПОИСКПОЗ(ИСТИНА;ИНДЕКС(B2:B7=F$2;0);0))
2. Není zadána úplná cesta k sešitu vyhledávání
Pokud načítáte data z jiného sešitu, musíte zadat úplnou cestu k tomuto souboru. Přesněji řečeno, musíte uvést název sešitu (včetně přípony) v hranatých závorkách [ ], následovaný názvem listu a vykřičníkem. Celá tato konstrukce musí být uzavřena v apostrofech, pokud název knihy nebo listu obsahuje mezery.
Zde je kompletní struktura funkce VPR hledat v jiné knize:
=VLOOKUP(lookup_value,'[workbook name]sheet name'!table_array, col_index_num,FALSE)
=ВПР(искомое_значение;'[имя_книги]имя_листа'!таблица;номер_столбца;ЛОЖЬ)
Skutečný vzorec může vypadat takto:
=VLOOKUP($A$2,'[New Prices.xls]Sheet1'!$B:$D,3,FALSE)
=ВПР($A$2;'[New Prices.xls]Sheet1'!$B:$D;3;ЛОЖЬ)
Tento vzorec vyhledá hodnotu buňky A2 ve sloupci B na listu Sheet1 v sešitu Nové ceny a extrahujte odpovídající hodnotu ze sloupce D.
Pokud je některá část cesty tabulky vynechána, vaše funkce VPR nebude fungovat a ohlásí chybu #HODNOTA! (i když je aktuálně otevřený sešit s vyhledávací tabulkou).
Pro více informací o funkci VPRodkazování na jiný soubor aplikace Excel, viz lekce: Hledání v jiném sešitu pomocí funkce VLOOKUP.
3. Argument Column_num je menší než 1
Těžko si představit situaci, kdy někdo zadá hodnotu nižší než 1k označení sloupce, ze kterého se má extrahovat hodnota. I když je to možné, pokud je hodnota tohoto argumentu vypočítána jinou funkcí Excelu vnořenou uvnitř VPR.
Pokud se tedy stane, že argument col_index_num (číslo_sloupce) menší než 1funkce VPR také ohlásí chybu #HODNOTA!.
Pokud argument col_index_num (číslo_sloupce) je větší než počet sloupců v daném poli, VPR ohlásí chybu #REF! (#SSYL!).
Chyba #NAME? ve VLOOKUP
Nejjednodušší případ je omyl # Název? (#NAME?) – zobrazí se, pokud omylem napíšete název funkce s chybou.
Řešení je nasnadě – zkontrolujte si pravopis!
VLOOKUP nefunguje (omezení, upozornění a rozhodnutí)
Kromě poměrně komplikované syntaxe, VPR má více omezení než kterákoli jiná funkce Excelu. Kvůli těmto omezením, zdánlivě jednoduché vzorce s VPR často vedou k neočekávaným výsledkům. Níže naleznete řešení pro několik běžných scénářů, kde VPR je špatně.
1. VLOOKUP nerozlišuje velká a malá písmena
funkce VPR nerozlišuje velká a malá písmena a přijímá malá a velká písmena jako stejná. Pokud je tedy v tabulce několik prvků, které se liší pouze velikostí písmen, funkce SVYHLEDAT vrátí první nalezený prvek bez ohledu na velikost písmen.
Rozhodnutí: Použijte jinou funkci Excelu, která může provádět vertikální vyhledávání (LOOKUP, SUMPRODUCT, INDEX a MATCH) v kombinaci s PŘESNÝA, které rozlišuje případ. Další podrobnosti najdete v lekci – 4 způsoby, jak ve SVYHLEDAT v Excelu rozlišovat velká a malá písmena.
2. VLOOKUP vrátí první nalezenou hodnotu
Jak už víte, VPR vrátí hodnotu z daného sloupce odpovídající první nalezené shodě. Můžete však nechat extrahovat 2., 3., 4. nebo jakékoli jiné opakování požadované hodnoty. Pokud potřebujete extrahovat všechny duplicitní hodnoty, budete potřebovat kombinaci funkcí INDEX (INDEX), NEJMÉNĚ (MALÁ) a LINE (ŘÁDEK).
3. Do tabulky byl přidán nebo odebrán sloupec
Bohužel vzorce VPR přestane fungovat pokaždé, když je do vyhledávací tabulky přidán nebo odebrán nový sloupec. To se děje kvůli syntaxi VPR vyžaduje, abyste uvedli celý rozsah vyhledávání a konkrétní číslo sloupce pro extrakci dat. Při smazání sloupce nebo vložení nového se přirozeně změní daný rozsah i číslo sloupce.
Rozhodnutí: A opět funkce spěchají na pomoc INDEX (INDEX) a VÍCE EXPOZOVANĚ (ZÁPAS). Ve vzorci INDEX+MATCH Samostatně definujete sloupce pro vyhledávání a načítání a v důsledku toho můžete odstranit nebo vložit libovolný počet sloupců, aniž byste se museli starat o aktualizaci všech souvisejících vyhledávacích vzorců.
4. Při kopírování vzorce jsou odkazy na buňky zkomolené
Tento nadpis vyčerpávajícím způsobem vysvětluje podstatu problému, že?
Rozhodnutí: Vždy používejte absolutní odkazy na buňky (se symbolem $) na záznamech např. rozsah $ A $ 2: $ C $ 100 or $ A: $ C. V řádku vzorců můžete kliknutím rychle přepnout typ odkazu F4.
VLOOKUP – práce s funkcemi IFERROR a ISERROR
Pokud nechcete uživatele strašit chybovými zprávami # N / A, #HODNOTA! or # Název?, můžete zobrazit prázdnou buňku nebo vlastní zprávu. Můžete to udělat umístěním VPR do funkce IFERROR (IFERROR) v Excelu 2013, 2010 a 2007 nebo použijte spoustu funkcí IF+ISERROR (IF+ISERROR) v dřívějších verzích.
VLOOKUP: práce s funkcí IFERROR
Syntaxe funkce IFERROR (IFERROR) je jednoduchý a mluví sám za sebe:
IFERROR(value,value_if_error)
ЕСЛИОШИБКА(значение;значение_если_ошибка)
To znamená, že do prvního argumentu vložíte hodnotu, která má být zkontrolována na chybu, a do druhého argumentu určíte, co se má vrátit, pokud je nalezena chyba.
Tento vzorec například vrátí prázdnou buňku, pokud hledaná hodnota není nalezena:
=IFERROR(VLOOKUP($F$2,$B$2:$C$10,2,FALSE),"")
=ЕСЛИОШИБКА(ВПР($F$2;$B$2:$C$10;2;ЛОЖЬ);"")
Pokud chcete místo standardní chybové zprávy funkce zobrazit vlastní zprávu VPR, dejte to do uvozovek, takhle:
=IFERROR(VLOOKUP($F$2,$B$2:$C$10,2,FALSE),"Ничего не найдено. Попробуйте еще раз!")
=ЕСЛИОШИБКА(ВПР($F$2;$B$2:$C$10;2;ЛОЖЬ);"Ничего не найдено. Попробуйте еще раз!")
VLOOKUP: práce s funkcí ISERROR
Od funkce IFERROR se objevil v Excelu 2007, při práci v dřívějších verzích budete muset použít kombinaci IF (IF) a EOSIBKA (ISERROR) takto:
=IF(ISERROR(VLOOKUP формула),"Ваше сообщение при ошибке",VLOOKUP формула)
=ЕСЛИ(ЕОШИБКА(ВПР формула);"Ваше сообщение при ошибке";ВПР формула)
Například vzorec IF+ISERROR+VLOOKUP, podobně jako ve vzorci IFERROR+VLOOKUPzobrazeno výše:
=IF(ISERROR(VLOOKUP($F$2,$B$2:$C$10,2,FALSE)),"",VLOOKUP($F$2,$B$2:$C$10,2,FALSE))
=ЕСЛИ(ЕОШИБКА(ВПР($F$2;$B$2:$C$10;2;ЛОЖЬ));"";ВПР($F$2;$B$2:$C$10;2;ЛОЖЬ))
To je pro dnešek vše. Doufám, že vám tento krátký návod pomůže vypořádat se se všemi možnými chybami. VPR a zajistit, aby vaše vzorce fungovaly správně.