Ako riešiť chyby VLOOKUP v Exceli

Ako

podľaBen Stockton

Posledná aktualizácia dňa13. januára 2020

Existuje niekoľko vecí, ktoré vydávajú potenie dovnútra Microsoft Excel používatelia viac, ako sa myslelo na chybu VLOOKUP. Ak nie ste príliš oboznámení s programom Excel, VLOOKUP je jednou z najťažších alebo aspoň jednou z najmenej pochopených dostupných funkcií.

Účelom programu VLOOKUP je vyhľadávať a vracať údaje z iného stĺpca tabuľky Excel. Bohužiaľ, ak zistíte, že máte nesprávny vzorec VLOOKUP, Excel vám zahodí chybu. Pozrime sa na niektoré bežné chyby VLOOKUP a vysvetlíme, ako ich riešiť.

Obmedzenia VLOOKUP

Skôr ako začnete používať program VLOOKUP, mali by ste si uvedomiť, že to nie je vždy najlepšia voľba pre používateľov Excelu.

Na začiatok sa nedá použiť na vyhľadávanie údajov vľavo od nich. Zobrazí sa iba prvá zistená hodnota, čo znamená, že VLOOKUP nie je možnosťou pre rozsahy údajov s duplikovanými hodnotami. Váš vyhľadávací stĺpec musí byť tiež najvzdialenejší ľavý stĺpec v rozsahu vašich údajov.

V nasledujúcom príklade je najvzdialenejší stĺpec (stĺpec A) sa používa ako stĺpec vyhľadávania. V rozsahu nie sú žiadne duplicitné hodnoty a údaje vyhľadávania (v tomto prípade údaje z stĺpec B) je napravo od stĺpca vyhľadávania.

Funkcie INDEX a MATCH by boli dobrými alternatívami, ak by ktorýkoľvek z týchto problémov predstavoval problém, rovnako ako nová funkcia XLOOKUP v Exceli, ktorá je momentálne v beta testovaní.

VLOOKUP tiež vyžaduje, aby boli dáta usporiadané do riadkov, aby bolo možné presne vyhľadávať a vracať údaje. Ak to tak nie je, HLOOKUP by bola dobrou alternatívou.

Ako vysvetlíme ďalej, existujú niektoré ďalšie vzorce vzorcov VLOOKUP, ktoré môžu spôsobiť chyby.

Chyby VLOOKUP a # N / A

Jednou z najčastejších chýb VLOOKUP v Exceli je # N / A chyba. Táto chyba sa vyskytuje, keď VLOOKUP nemôže nájsť hodnotu, ktorú hľadáte.

Ak chcete začať tým, hodnota vyhľadávania nemusí existovať vo vašom rozsahu údajov alebo ste pravdepodobne použili nesprávnu hodnotu. Ak vidíte chybu N / A, dvakrát skontrolujte hodnotu vo vašom vzorci VLOOKUP.

Ak je hodnota správna, vaša hodnota vyhľadávania neexistuje. To predpokladá, že na nájdenie presných zhôd používate program VLOOKUP argument typ argument bol nastavený na FALSE.

Vo vyššie uvedenom príklade hľadanie a Študentská karta s číslo 104 (v bunke G 4) vráti # N / A chyba pretože minimálne ID číslo v rozsahu je 105.

Ak je argument typ argument na konci vzorca VLOOKUP chýba alebo je nastavený na TRUE, potom VLOOKUP vráti chybu # N / A, ak rozsah údajov nie je usporiadaný vzostupne.

Ak vaša hodnota vyhľadávania je nižšia ako najnižšia hodnota v rozsahu, vráti tiež chybu # N / A.

Vo vyššie uvedenom príklade Študentská karta hodnoty sú zmiešané. Napriek hodnote 105 VLOOKUP, ktorý existuje v danom rozsahu, pomocou nástroja argument typ argument bol nastavený na TRUE pretože stĺpec A nie je usporiadané vzostupne.

Medzi ďalšie časté príčiny chýb typu N / A patrí použitie stĺpca vyhľadávania, ktorý nie je úplne vľavo, a používanie bunky referencie na hodnoty vyhľadávania, ktoré obsahujú čísla, ale sú naformátované ako text alebo obsahujú nadbytočné znaky ako priestory.

Riešenie problémov s chybami #VALUE

#VALUE Chyba je zvyčajne znakom toho, že vzorec obsahujúci funkciu VLOOKUP je nejakým spôsobom nesprávny.

Vo väčšine prípadov je to zvyčajne kvôli bunke, ktorú odkazujete ako svoju hodnotu vyhľadávania. maximálna veľkosť hodnoty vyhľadávania VLOOKUP je 255 znakov.

Ak pracujete s bunkami, ktoré obsahujú dlhšie reťazce znakov, VLOOKUP ich nebude schopný spracovať.

Jediným riešením tohto problému je nahradiť vzorec VLOOKUP kombinovaným vzorcom INDEX a MATCH. Napríklad, ak jeden stĺpec obsahuje bunku s reťazcom viac ako 255 znakov, na lokalizáciu týchto údajov sa môže namiesto toho použiť vnorená funkcia MATCH vo vnútri INDEXu.

V nižšie uvedenom príklade INDEX vráti hodnotu v bunke B4pomocou rozsahu v stĺpec A na identifikáciu správneho riadku. Používa to vnorené ZÁPAS funkcia na identifikáciu reťazca v stĺpec A (obsahujúci 300 znakov), ktorá zodpovedá bunke H4.

V tomto prípade je to bunka A4, s INDEX vrátenie 108 (hodnota B4).

Táto chyba sa objaví aj v prípade, že ste vo vzorci použili nesprávny odkaz na bunky, najmä ak používate rozsah údajov z iného zošita.

Aby vzorec fungoval správne, musia byť v hranatých zátvorkách uvedené odkazy zošita.

Ak narazíte na chybu # HODNOTA, dvakrát skontrolujte vzorec VLOOKUP a overte, či sú vaše bunkové referencie správne.

# NAME a VLOOKUP

Ak vaša chyba VLOOKUP nie je chybou # VALUE alebo # N / A, pravdepodobne ide o chybu #NÁZOV chyba. Skôr než začnete panikáriť pri pomyslení na túto, buďte si istý - je to najjednoduchšia chyba VLOOKUP, ktorú treba opraviť.

Keď ste omylom napísali funkciu v Exceli, či už je to VLOOKUP alebo iná funkcia, ako napríklad SUM, zobrazí sa chyba #NAME. Kliknite na bunku VLOOKUP a znova skontrolujte, či ste program VLOOKUP napísali správne.

Ak neexistujú žiadne ďalšie problémy, vzorec VLOOKUP bude fungovať po odstránení tejto chyby.

Používanie ďalších funkcií programu Excel

Je to odvážne tvrdenie, ale funguje podobne VLOOKUP zmení váš život. Prinajmenšom to zmení váš pracovný život, čím sa z Excelu stane výkonnejší nástroj na analýzu údajov.

Ak pre vás VLOOKUP nie je vhodný, využite ich top Excel funkcie miesto.