Funkcija VLOOKUP ne radi – rješavanje problema N/A, NAME i VALUE

Ova lekcija objašnjava kako se brzo nositi sa situacijom u kojoj funkcija VPR (VLOOKUP) ne želi raditi u Excelu 2013, 2010, 2007 i 2003, i kako identificirati i popraviti uobičajene greške i prevladati ograničenja VPR.

U nekoliko prethodnih članaka istražili smo različite aspekte funkcije VPR u Excelu. Ako ste ih pažljivo pročitali, sada biste trebali biti stručnjak u ovoj oblasti. Međutim, mnogi stručnjaci za Excel ne vjeruju bez razloga VPR jedna od složenijih karakteristika. Ima gomilu ograničenja i karakteristika koje postaju izvor mnogih problema i grešaka.

Funkcija VLOOKUP ne radi - rješavanje problema N/A, NAME i VALUE

U ovom članku ćete pronaći jednostavna objašnjenja grešaka #AT (#N / A), #NAME? (#NAME?) i # VALUE! (#VRIJEDNOST!) koji se pojavljuju kada radite s funkcijom VPR, kao i tehnike i metode postupanja s njima. Počećemo s najčešćim slučajevima i najočiglednijim razlozima zašto. VPR ne radi, pa je bolje proučiti primjere redoslijedom kojim su dati u članku.

Ispravljanje greške #N/A u funkciji VLOOKUP u Excelu

U formulama sa VPR poruka o grešci #AT (#N/A) znači Nije dostupno (bez podataka) – pojavljuje se kada Excel ne može pronaći vrijednost koju tražite. To se može dogoditi iz nekoliko razloga.

1. Željena vrijednost je pogrešno napisana

Dobra ideja da prvo provjerite ovu stavku! Greške u kucanju se često dešavaju kada radite sa veoma velikim količinama podataka, koji se sastoje od hiljada redova, ili kada je vrednost koju tražite upisana u formulu.

2. #N/A greška prilikom traženja približnog podudaranja sa VLOOKUP-om

Ako koristite formulu s približnim uvjetom traženja podudaranja, tj. argumentom range_lookup (range_lookup) je TRUE ili nije navedeno, vaša formula može prijaviti grešku # N / A u dva slučaja:

  • Vrijednost koju treba potražiti je manja od najmanje vrijednosti u nizu koji se traži.
  • Kolona za pretraživanje nije sortirana uzlaznim redoslijedom.

3. #N/A greška pri traženju tačnog podudaranja sa VLOOKUP-om

Ako tražite tačno podudaranje, tj. argument range_lookup (range_lookup) je FALSE i tačna vrijednost nije pronađena, formula će također prijaviti grešku # N / A. Saznajte više o tome kako tražiti točna i približna podudaranja s funkcijom VPR.

4. Kolona za pretragu nije krajnja lijeva

Kao što vjerovatno znate, jedno od najznačajnijih ograničenja VPR to je da ne može biti okrenut prema lijevo, stoga kolona za traženje u vašoj tabeli mora biti krajnja lijevo. U praksi to često zaboravljamo, što dovodi do nefunkcionalne formule i greške. # N / A.

Funkcija VLOOKUP ne radi - rješavanje problema N/A, NAME i VALUE

Odluka: Ako nije moguće promijeniti strukturu podataka tako da kolona za pretraživanje bude krajnja lijevo, možete koristiti kombinaciju funkcija INDEX (INDEX) i IZLOŽENIJI (MATCH) kao fleksibilnija alternativa za VPR.

5. Brojevi su formatirani kao tekst

Još jedan izvor greške # N / A u formulama sa VPR su brojevi u tekstualnom formatu u glavnoj tabeli ili tabeli za pretraživanje.

Ovo se obično dešava kada uvezete informacije iz eksternih baza podataka ili kada upišete apostrof ispred broja da biste zadržali početnu nulu.

Najočigledniji znakovi broja u tekstualnom formatu prikazani su na donjoj slici:

Funkcija VLOOKUP ne radi - rješavanje problema N/A, NAME i VALUE

Pored toga, brojevi se mogu pohraniti u formatu Opšti (Općenito). U ovom slučaju postoji samo jedna uočljiva karakteristika – brojevi su poravnati sa lijevom ivicom ćelije, dok su po defaultu poravnati sa desnom ivicom.

Odluka: Ako se radi o jednoj vrijednosti, samo kliknite na ikonu greške i odaberite Pretvori u broj (Pretvori u broj) iz kontekstnog menija.

Funkcija VLOOKUP ne radi - rješavanje problema N/A, NAME i VALUE

Ako je to situacija s mnogo brojeva, odaberite ih i kliknite desnim tasterom miša na odabrano područje. U kontekstualnom meniju koji se pojavi izaberite Formatiranje ćelija (Formatiranje ćelija) > tab broj (Broj) > format broj (Numerički) i pritisnite OK.

6. Na početku ili na kraju postoji razmak

Ovo je najmanje očigledan razlog za grešku. # N / A u funkciji VPR, jer je vizuelno teško uočiti ove dodatne prostore, posebno kada radite sa velikim tabelama, kada je većina podataka van ekrana.

Rješenje 1: Dodatni razmaci u glavnoj tabeli (gdje je funkcija VLOOKUP)

Ako se u glavnoj tabeli pojavljuju dodatni razmaci, možete osigurati da formule rade ispravno tako što ćete priložiti argument lookup_value (lookup_value) u funkciju PODREZATI (PODREZATI):

=VLOOKUP(TRIM($F2),$A$2:$C$10,3,FALSE)

=ВПР(СЖПРОБЕЛЫ($F2);$A$2:$C$10;3;ЛОЖЬ)

Funkcija VLOOKUP ne radi - rješavanje problema N/A, NAME i VALUE

Rješenje 2: Dodatni razmaci u tabeli za pretraživanje (u koloni za pretraživanje)

Ako su dodatni razmaci u koloni za pretragu – jednostavni načini # N / A u formuli sa VPR ne može se izbjeći. Umjesto VPR Možete koristiti formulu niza s kombinacijom funkcija INDEX (INDEX), IZLOŽENIJI (UTAKMICA) i PODREZATI (PODREZATI):

=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))

Pošto je ovo formula niza, ne zaboravite pritisnuti Ctrl + Shift + Enter umjesto uobičajenog ućida pravilno unesete formulu.

Funkcija VLOOKUP ne radi - rješavanje problema N/A, NAME i VALUE

Greška #VALUE! u formulama sa VLOOKUP

U većini slučajeva, Microsoft Excel prijavljuje grešku # VALUE! (#VRIJEDNOST!) kada vrijednost korištena u formuli ne odgovara tipu podataka. U vezi VPR, tada obično postoje dva razloga za grešku # VALUE!.

1. Vrijednost koju tražite je duža od 255 znakova

Budite oprezni: funkcija VPR ne može tražiti vrijednosti koje sadrže više od 255 znakova. Ako vrijednost koju tražite premašuje ovo ograničenje, dobit ćete poruku o grešci. # VALUE!.

Funkcija VLOOKUP ne radi - rješavanje problema N/A, NAME i VALUE

Odluka: Koristite gomilu funkcija INDEKS+PODRŽAVANJE (INDEKS + PODRŽAVANJE). Ispod je formula koja će biti dobra za ovaj zadatak:

=INDEX(C2:C7,MATCH(TRUE,INDEX(B2:B7=F$2,0),0))

=ИНДЕКС(C2:C7;ПОИСКПОЗ(ИСТИНА;ИНДЕКС(B2:B7=F$2;0);0))

Funkcija VLOOKUP ne radi - rješavanje problema N/A, NAME i VALUE

2. Puna staza do radne knjige pretraživanja nije navedena

Ako preuzimate podatke iz druge radne knjige, morate navesti punu putanju do te datoteke. Konkretnije, morate uključiti naziv radne knjige (uključujući ekstenziju) u uglastim zagradama [ ], nakon čega slijedi naziv lista, nakon čega slijedi uskličnik. Sva ova konstrukcija mora biti ograđena apostrofima, u slučaju da naziv knjige ili lista sadrži razmake.

Ovdje je kompletna struktura funkcije VPR za pretragu u drugoj knjizi:

=VLOOKUP(lookup_value,'[workbook name]sheet name'!table_array, col_index_num,FALSE)

=ВПР(искомое_значение;'[имя_книги]имя_листа'!таблица;номер_столбца;ЛОЖЬ)

Prava formula bi mogla izgledati ovako:

=VLOOKUP($A$2,'[New Prices.xls]Sheet1'!$B:$D,3,FALSE)

=ВПР($A$2;'[New Prices.xls]Sheet1'!$B:$D;3;ЛОЖЬ)

Ova formula će tražiti vrijednost ćelije A2 u koloni B na listu List1 u radnoj svesci Nove cijene i izvući odgovarajuću vrijednost iz kolone D.

Ako je bilo koji dio putanje tablice izostavljen, vaša funkcija VPR neće raditi i prijavit će grešku # VALUE! (čak i ako je radna knjiga sa tabelom za pretraživanje trenutno otvorena).

Za više informacija o funkciji VPRpozivajući se na drugu Excel datoteku, pogledajte lekciju: Pretraživanje druge radne knjige pomoću VLOOKUP-a.

3. Argument Column_num je manji od 1

Teško je zamisliti situaciju da neko unese vrijednost manju od 1da označi kolonu iz koje treba izdvojiti vrijednost. Iako je moguće ako se vrijednost ovog argumenta izračuna pomoću druge Excel funkcije ugniježđene VPR.

Dakle, ako se desi da argument col_index_num (broj_kolone) manji od 1Funkcija VPR također će prijaviti grešku # VALUE!.

Ako je argument col_index_num (broj_kolone) je veći od broja kolona u datom nizu, VPR prijavit će grešku #REF! (#SSYL!).

Greška #NAME? u VLOOKUP

Najjednostavniji slučaj je greška #NAME? (#NAME?) – pojavit će se ako slučajno upišete ime funkcije s greškom.

Rješenje je očigledno – provjerite pravopis!

VLOOKUP ne radi (ograničenja, upozorenja i odluke)

Pored prilično komplikovane sintakse, VPR ima više ograničenja od bilo koje druge Excel funkcije. Zbog ovih ograničenja, naizgled jednostavne formule sa VPR često dovode do neočekivanih rezultata. U nastavku ćete pronaći rješenja za nekoliko uobičajenih scenarija gdje VPR je pogrešno.

1. VLOOKUP ne razlikuje velika i mala slova

funkcija VPR ne pravi razliku između velikih i malih slova i prihvata mala i velika slova kao iste. Stoga, ako postoji nekoliko elemenata u tablici koji se razlikuju samo u malim i malim slovima, funkcija VLOOKUP će vratiti prvi pronađeni element, bez obzira na velika i mala slova.

Odluka: Koristite drugu Excel funkciju koja može izvršiti vertikalno pretraživanje (LOOKUP, SUMPRODUCT, INDEX i MATCH) u kombinaciji sa TOČNOA koji razlikuje slučaj. Za više detalja, možete naučiti iz lekcije – 4 načina da napravite VLOOKUP osjetljiv na velika i mala slova u Excelu.

2. VLOOKUP vraća prvu pronađenu vrijednost

Kao što već znate, VPR vraća vrijednost iz date kolone koja odgovara prvom pronađenom podudaranju. Međutim, možete dati da izdvoji 2., 3., 4. ili bilo koje drugo ponavljanje vrijednosti koju želite. Ako trebate izdvojiti sve duplicirane vrijednosti, trebat će vam kombinacija funkcija INDEX (INDEX), LEAST (MALA) i LINE (RED).

3. Kolona je dodana ili uklonjena u tablicu

Nažalost, formule VPR prestati raditi svaki put kada se nova kolona doda ili ukloni u tabelu za pretraživanje. Ovo se dešava zbog sintakse VPR zahtijeva od vas da navedete cijeli raspon pretraživanja i određeni broj kolone za ekstrakciju podataka. Naravno, i dati opseg i broj kolone se mijenjaju kada izbrišete kolonu ili umetnete novu.

Odluka: I opet funkcije žure u pomoć INDEX (INDEX) i IZLOŽENIJI (MAČ). U formuli INDEKS+PODRŽAVANJE Zasebno definirate kolone za pretraživanje i dohvaćanje, i kao rezultat, možete izbrisati ili umetnuti onoliko kolona koliko želite bez brige da ćete morati ažurirati sve povezane formule za pretraživanje.

4. Reference ćelije su iskrivljene prilikom kopiranja formule

Ovaj naslov iscrpno objašnjava suštinu problema, zar ne?

Odluka: Uvijek koristite apsolutne reference ćelije (sa simbolom $) na bilježi raspon, na primjer $A$2:$C$100 or $A:$C. U traci formule možete brzo promijeniti vrstu veze klikom F4.

VLOOKUP – rad sa funkcijama IFERROR i ISERROR

Ako ne želite da plašite korisnike porukama o greškama # N / A, # VALUE! or #NAME?, možete prikazati praznu ćeliju ili svoju poruku. To možete učiniti postavljanjem VPR u funkciju IFERROR (IFERROR) u Excel 2013, 2010 i 2007 ili koristite gomilu funkcija IF+ISERROR (IF+ISERROR) u starijim verzijama.

VLOOKUP: rad sa funkcijom IFERROR

Sintaksa funkcije IFERROR (IFERROR) je jednostavan i govori sam za sebe:

IFERROR(value,value_if_error)

ЕСЛИОШИБКА(значение;значение_если_ошибка)

Odnosno, za prvi argument umetnete vrijednost koju treba provjeriti da li postoji greška, a za drugi argument specificirate šta da vratite ako se pronađe greška.

Na primjer, ova formula vraća praznu ćeliju ako vrijednost koju tražite nije pronađena:

=IFERROR(VLOOKUP($F$2,$B$2:$C$10,2,FALSE),"")

=ЕСЛИОШИБКА(ВПР($F$2;$B$2:$C$10;2;ЛОЖЬ);"")

Funkcija VLOOKUP ne radi - rješavanje problema N/A, NAME i VALUE

Ako želite prikazati vlastitu poruku umjesto standardne poruke o grešci funkcije VPR, stavi pod navodnike, ovako:

=IFERROR(VLOOKUP($F$2,$B$2:$C$10,2,FALSE),"Ничего не найдено. Попробуйте еще раз!")

=ЕСЛИОШИБКА(ВПР($F$2;$B$2:$C$10;2;ЛОЖЬ);"Ничего не найдено. Попробуйте еще раз!")

Funkcija VLOOKUP ne radi - rješavanje problema N/A, NAME i VALUE

VLOOKUP: rad sa funkcijom ISERROR

Od funkcije IFERROR pojavio u Excelu 2007, kada radite u starijim verzijama morat ćete koristiti kombinaciju IF (IF) i EOSHIBKA (ISGREŠKA) ovako:

=IF(ISERROR(VLOOKUP формула),"Ваше сообщение при ошибке",VLOOKUP формула)

=ЕСЛИ(ЕОШИБКА(ВПР формула);"Ваше сообщение при ошибке";ВПР формула)

Na primjer, formula IF+ISGREŠKA+VLOOKUP, slično formuli IFERROR+VLOOKUPprikazano gore:

=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 sve za danas. Nadam se da će vam ovaj kratki vodič pomoći da se nosite sa svim mogućim greškama. VPR i učinite da vaše formule rade ispravno.

Ostavite odgovor