Obsah
Táto lekcia vysvetľuje, ako rýchlo riešiť situáciu, keď funkcia funguje VPR (VLOOKUP) nechce pracovať v Exceli 2013, 2010, 2007 a 2003 a ako identifikovať a opraviť bežné chyby a prekonať obmedzenia VPR.
V niekoľkých predchádzajúcich článkoch sme preskúmali rôzne aspekty funkcie VPR v Exceli. Ak ste si ich pozorne prečítali, teraz by ste mali byť odborníkom v tejto oblasti. Nie je to však bez dôvodu, že mnohí odborníci na Excel veria VPR jedna zo zložitejších funkcií. Má množstvo obmedzení a funkcií, ktoré sa stávajú zdrojom mnohých problémov a chýb.
V tomto článku nájdete jednoduché vysvetlenia chýb #AT (#N/A), # Názov? (#NAME?) a #HODNOTA! (#VALUE!), ktoré sa objavia pri práci s funkciou VPRako aj techniky a metódy ich riešenia. Začneme najbežnejšími prípadmi a najzrejmejšími dôvodmi. VPR nefunguje, preto je lepšie študovať príklady v poradí, v akom sú uvedené v článku.
Oprava chyby #N/A vo funkcii VLOOKUP v Exceli
Vo vzorcoch s VPR chybové hlásenie #AT (#N/A) znamená nie je k dispozícii (žiadne údaje) – zobrazí sa, keď Excel nemôže nájsť hodnotu, ktorú hľadáte. Môže sa to stať z niekoľkých dôvodov.
1. Požadovaná hodnota je nesprávne napísaná
Dobrý nápad najprv skontrolovať túto položku! Preklepy sa často vyskytujú, keď pracujete s veľmi veľkým množstvom údajov, ktoré pozostávajú z tisícok riadkov, alebo keď je hľadaná hodnota zapísaná do vzorca.
2. Chyba #N/A pri hľadaní približnej zhody pomocou funkcie VLOOKUP
Ak použijete vzorec s približnou podmienkou vyhľadávania zhody, tj argument range_lookup (range_lookup) je PRAVDA alebo nie je zadaný, váš vzorec môže hlásiť chybu # N / A v dvoch prípadoch:
- Hodnota, ktorá sa má vyhľadať, je menšia ako najmenšia hodnota v poli, ktoré sa vyhľadáva.
- Vyhľadávací stĺpec nie je zoradený vzostupne.
3. Chyba #N/A pri hľadaní presnej zhody pomocou funkcie VLOOKUP
Ak hľadáte presnú zhodu, teda argument range_lookup (range_lookup) je FALSE a presná hodnota sa nenašla, vzorec tiež ohlási chybu # N / A. Prečítajte si viac o tom, ako hľadať presné a približné zhody s funkciou VPR.
4. Vyhľadávací stĺpec nie je úplne vľavo
Ako asi viete, jedno z najvýznamnejších obmedzení VPR je to tak, že nemôže smerovať doľava, preto musí byť vyhľadávací stĺpec vo vašej tabuľke úplne vľavo. V praxi na to často zabúdame, čo vedie k nefunkčnému vzorcu a chybe. # N / A.
Rozhodnutie: Ak nie je možné zmeniť štruktúru údajov tak, aby bol vyhľadávací stĺpec úplne vľavo, môžete použiť kombináciu funkcií INDEX (INDEX) a VIAC EXPOZOVANÉ (MATCH) ako flexibilnejšiu alternatívu pre VPR.
5. Čísla sú formátované ako text
Ďalší zdroj chýb # N / A vo vzorcoch s VPR sú čísla v textovom formáte v hlavnej tabuľke alebo vo vyhľadávacej tabuľke.
Zvyčajne sa to stane, keď importujete informácie z externých databáz, alebo keď napíšete apostrof pred číslo, aby sa zachovala úvodná nula.
Najviditeľnejšie znaky čísla v textovom formáte sú znázornené na obrázku nižšie:
Okrem toho môžu byť čísla uložené vo formáte všeobecný (Všeobecný). V tomto prípade je badateľná len jedna vlastnosť – čísla sú zarovnané k ľavému okraju bunky, pričom štandardne k pravému okraju.
Rozhodnutie: Ak ide o jednu hodnotu, stačí kliknúť na ikonu chyby a vybrať Previesť na číslo (Previesť na číslo) z kontextového menu.
Ak je to situácia s mnohými číslami, vyberte ich a kliknite pravým tlačidlom myši na vybranú oblasť. V kontextovej ponuke, ktorá sa zobrazí, vyberte formátovanie buniek (Formátovať bunky) > karta číslo (Číslo) > formát číslo (Číselný) a stlačte OK.
6. Na začiatku alebo na konci je medzera
Toto je najmenej zrejmý dôvod chyby. # N / A vo funkcii VPR, pretože je vizuálne ťažké vidieť tieto extra medzery, najmä pri práci s veľkými tabuľkami, keď je väčšina údajov mimo obrazovky.
Riešenie 1: Medzery navyše v hlavnej tabuľke (kde je funkcia VLOOKUP)
Ak sa v hlavnej tabuľke objavia medzery navyše, uzavretím argumentu môžete zabezpečiť, aby vzorce fungovali správne lookup_value (hodnota_hľadania) do funkcie TRIM (TRIM):
=VLOOKUP(TRIM($F2),$A$2:$C$10,3,FALSE)
=ВПР(СЖПРОБЕЛЫ($F2);$A$2:$C$10;3;ЛОЖЬ)
Riešenie 2: Medzery navyše vo vyhľadávacej tabuľke (v stĺpci vyhľadávania)
Ak sú vo vyhľadávacom stĺpci medzery navyše – jednoduché spôsoby # N / A vo vzorci s VPR nedá sa vyhnúť. Namiesto VPR Môžete použiť vzorec poľa s kombináciou funkcií INDEX (INDEX), VIAC 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))
Keďže ide o vzorec poľa, nezabudnite stlačiť Ctrl + Shift + Enter namiesto bežného vstúpiťsprávne zadať vzorec.
Chyba #HODNOTA! vo vzorcoch pomocou funkcie VLOOKUP
Vo väčšine prípadov program Microsoft Excel hlási chybu #HODNOTA! (#VALUE!), keď hodnota použitá vo vzorci nezodpovedá typu údajov. Čo sa týka VPR, potom sú zvyčajne dva dôvody chyby #HODNOTA!.
1. Hodnota, ktorú hľadáte, je dlhšia ako 255 znakov
Buďte opatrní: funkcia VPR nemôže vyhľadávať hodnoty obsahujúce viac ako 255 znakov. Ak hľadaná hodnota prekročí tento limit, zobrazí sa chybové hlásenie. #HODNOTA!.
Rozhodnutie: Použite množstvo funkcií INDEX+MATCH (INDEX + ZÁPAS). Nižšie je uvedený vzorec, ktorý bude pre túto úlohu fungovať dobre:
=INDEX(C2:C7,MATCH(TRUE,INDEX(B2:B7=F$2,0),0))
=ИНДЕКС(C2:C7;ПОИСКПОЗ(ИСТИНА;ИНДЕКС(B2:B7=F$2;0);0))
2. Nie je zadaná úplná cesta k zošitu vyhľadávania
Ak získavate údaje z iného zošita, musíte zadať úplnú cestu k tomuto súboru. Presnejšie povedané, musíte uviesť názov zošita (vrátane prípony) v hranatých zátvorkách [ ], za ktorým nasleduje názov hárka, za ktorým nasleduje výkričník. Celá táto konštrukcia musí byť uzavretá v apostrofoch, ak názov knihy alebo listu obsahuje medzery.
Tu je úplná štruktúra funkcie VPR hľadať v inej knihe:
=VLOOKUP(lookup_value,'[workbook name]sheet name'!table_array, col_index_num,FALSE)
=ВПР(искомое_значение;'[имя_книги]имя_листа'!таблица;номер_столбца;ЛОЖЬ)
Skutočný vzorec môže vyzerať takto:
=VLOOKUP($A$2,'[New Prices.xls]Sheet1'!$B:$D,3,FALSE)
=ВПР($A$2;'[New Prices.xls]Sheet1'!$B:$D;3;ЛОЖЬ)
Tento vzorec vyhľadá hodnotu bunky A2 v stĺpci B na liste Sheet1 v pracovnom zošite Nové ceny a extrahujte zodpovedajúcu hodnotu zo stĺpca D.
Ak je niektorá časť cesty tabuľky vynechaná, vaša funkcia VPR nebude fungovať a ohlási chybu #HODNOTA! (aj keď je práve otvorený zošit s vyhľadávacou tabuľkou).
Pre viac informácií o funkcii VPRodkaz na iný súbor programu Excel, pozrite si lekciu: Vyhľadávanie iného zošita pomocou funkcie VLOOKUP.
3. Argument Column_num je menšie ako 1
Je ťažké si predstaviť situáciu, keď niekto zadá hodnotu nižšiu ako 1na označenie stĺpca, z ktorého sa má extrahovať hodnota. Aj keď je to možné, ak je hodnota tohto argumentu vypočítaná inou funkciou Excelu vnorenou v rámci VPR.
Ak sa teda stane, že argument col_index_num (číslo_stĺpca) menšie ako 1funkcie VPR tiež oznámi chybu #HODNOTA!.
Ak argument col_index_num (číslo_stĺpca) je väčšie ako počet stĺpcov v danom poli, VPR ohlási chybu #REF! (#SSYL!).
Chyba #NAME? vo VLOOKUP
Najjednoduchší prípad je omyl # Názov? (#NAME?) – zobrazí sa, ak omylom napíšete názov funkcie s chybou.
Riešenie je zrejmé – skontrolujte si pravopis!
VLOOKUP nefunguje (obmedzenia, upozornenia a rozhodnutia)
Okrem pomerne komplikovanej syntaxe, VPR má viac obmedzení ako ktorákoľvek iná funkcia Excelu. Kvôli týmto obmedzeniam sú zdanlivo jednoduché vzorce s VPR často vedú k neočakávaným výsledkom. Nižšie nájdete riešenia pre niekoľko bežných scenárov, kde VPR je nesprávne.
1. VLOOKUP nerozlišuje veľké a malé písmená
Funkcie VPR nerozlišuje medzi malými a veľkými písmenami a akceptuje malé a veľké písmená ako rovnaké. Ak je teda v tabuľke niekoľko prvkov, ktoré sa líšia iba veľkosťou písmen, funkcia VLOOKUP vráti prvý nájdený prvok bez ohľadu na veľkosť písmen.
Rozhodnutie: Použite inú funkciu Excel, ktorá dokáže vykonávať vertikálne vyhľadávanie (LOOKUP, SUMPRODUCT, INDEX a MATCH) v kombinácii s EXACTA, ktoré rozlišuje prípad. Viac podrobností nájdete v lekcii – 4 spôsoby, ako v programe VLOOKUP rozlišovať veľké a malé písmená.
2. VLOOKUP vráti prvú nájdenú hodnotu
Ako už viete, VPR vráti hodnotu z daného stĺpca zodpovedajúcu prvej nájdenej zhode. Môžete si však nechať extrahovať 2., 3., 4. alebo akékoľvek iné opakovanie požadovanej hodnoty. Ak potrebujete extrahovať všetky duplicitné hodnoty, budete potrebovať kombináciu funkcií INDEX (INDEX), ASPOŇ (MALÁ) a LINE (RAD).
3. Do tabuľky bol pridaný alebo odstránený stĺpec
Bohužiaľ, vzorce VPR prestane fungovať vždy, keď sa do vyhľadávacej tabuľky pridá alebo odstráni nový stĺpec. To sa deje, pretože syntax VPR vyžaduje, aby ste zadali celý rozsah vyhľadávania a špecifické číslo stĺpca pre extrakciu údajov. Pri vymazaní stĺpca alebo vložení nového sa prirodzene zmení daný rozsah aj číslo stĺpca.
Rozhodnutie: A opäť sa funkcie ponáhľajú pomôcť INDEX (INDEX) a VIAC EXPOZOVANÉ (ZÁPAS). Vo vzorci INDEX+MATCH Samostatne definujete stĺpce vyhľadávania a načítania a výsledkom je, že môžete odstrániť alebo vložiť toľko stĺpcov, koľko chcete, bez toho, aby ste sa museli obávať, že budete musieť aktualizovať všetky súvisiace vzorce vyhľadávania.
4. Odkazy na bunky sú pri kopírovaní vzorca skomolené
Tento nadpis vyčerpávajúcim spôsobom vysvetľuje podstatu problému, však?
Rozhodnutie: Vždy používajte absolútne odkazy na bunky (so symbolom $) na záznamoch rozsah napr $A$2: $C$100 or $A:$C. V riadku vzorcov môžete kliknutím rýchlo zmeniť typ prepojenia F4.
VLOOKUP – práca s funkciami IFERROR a ISERROR
Ak nechcete používateľov vystrašiť chybovými hláseniami # N / A, #HODNOTA! or # Názov?, môžete zobraziť prázdnu bunku alebo vlastnú správu. Môžete to urobiť umiestnením VPR do funkcie IFERROR (IFERROR) v Exceli 2013, 2010 a 2007 alebo použite množstvo funkcií IF+ISERROR (IF+ISERROR) v starších verziách.
VLOOKUP: práca s funkciou IFERROR
Syntax funkcie IFERROR (IFERROR) je jednoduchý a hovorí sám za seba:
IFERROR(value,value_if_error)
ЕСЛИОШИБКА(значение;значение_если_ошибка)
To znamená, že do prvého argumentu vložíte hodnotu, ktorá sa má skontrolovať na chybu a do druhého argumentu určíte, čo sa má vrátiť, ak sa nájde chyba.
Tento vzorec napríklad vráti prázdnu bunku, ak sa nenájde hľadaná hodnota:
=IFERROR(VLOOKUP($F$2,$B$2:$C$10,2,FALSE),"")
=ЕСЛИОШИБКА(ВПР($F$2;$B$2:$C$10;2;ЛОЖЬ);"")
Ak chcete namiesto štandardného chybového hlásenia funkcie zobraziť vlastnú správu VPR, dajte to do úvodzoviek, takto:
=IFERROR(VLOOKUP($F$2,$B$2:$C$10,2,FALSE),"Ничего не найдено. Попробуйте еще раз!")
=ЕСЛИОШИБКА(ВПР($F$2;$B$2:$C$10;2;ЛОЖЬ);"Ничего не найдено. Попробуйте еще раз!")
VLOOKUP: práca s funkciou ISERROR
Od funkcie IFERROR sa objavil v Exceli 2007, pri práci v starších verziách budete musieť použiť kombináciu IF (IF) a EOSIBKA (ISERROR) takto:
=IF(ISERROR(VLOOKUP формула),"Ваше сообщение при ошибке",VLOOKUP формула)
=ЕСЛИ(ЕОШИБКА(ВПР формула);"Ваше сообщение при ошибке";ВПР формула)
Napríklad vzorec IF+ISERROR+VLOOKUP, podobne ako vzorec IFERROR+VLOOKUPzobrazené vyššie:
=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 na dnes všetko. Dúfam, že tento krátky návod vám pomôže vysporiadať sa so všetkými možnými chybami. VPR a aby vaše vzorce fungovali správne.