Sadržaj
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 te kako prepoznati i popraviti uobičajene pogreš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 ovom području. Međutim, mnogi stručnjaci za Excel ne vjeruju bez razloga VPR jedna od složenijih značajki. Ima hrpu ograničenja i značajki koje postaju izvor mnogih problema i pogrešaka.
U ovom ćete članku pronaći jednostavna objašnjenja pogrešaka #AT (#N/A), #name? (#IME?) i #VRIJEDNOST! (#VRIJEDNOST!) koje se pojavljuju tijekom rada s funkcijom VPR, kao i tehnike i metode postupanja s njima. Počet ćemo s najčešćim slučajevima i najočitijim razlozima zašto. VPR ne radi, pa je bolje proučiti primjere redom kojim su navedeni u članku.
Ispravljanje pogreške #N/A u funkciji VLOOKUP u Excelu
U formulama sa VPR Poruka o pogreš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 prvo provjeriti ovu stavku! Tipfelerske pogreške često se javljaju kada radite s vrlo velikim količinama podataka, koji se sastoje od tisuća redaka, ili kada je vrijednost koju tražite zapisana u formulu.
2. #N/A pogreška prilikom traženja približnog podudaranja s VLOOKUP-om
Ako koristite formulu s približnim uvjetom traženja podudaranja, tj. argumentom raspon_potraga (range_lookup) je TRUE ili nije navedeno, vaša formula može prijaviti pogrešku # N / A u dva slučaja:
- Vrijednost koju treba tražiti manja je od najmanje vrijednosti u nizu koji se traži.
- Stupac pretraživanja nije poredan uzlaznim redoslijedom.
3. #N/A pogreška pri traženju točnog podudaranja s VLOOKUP-om
Ako tražite egzaktnu podudarnost tj. argument raspon_potraga (range_lookup) je FALSE i točna vrijednost nije pronađena, formula će također prijaviti pogrešku # N / A. Saznajte više o tome kako tražiti točna i približna podudaranja pomoću funkcije VPR.
4. Stupac za pretraživanje nije krajnji lijevi
Kao što vjerojatno znate, jedno od najznačajnijih ograničenja VPR radi se o tome da ne može biti okrenut ulijevo, stoga stupac pretraživanja u vašoj tablici mora biti krajnji lijevo. U praksi često zaboravimo na to, što dovodi do neradne formule i pogreške. # N / A.
Odluka: Ako nije moguće promijeniti strukturu podataka tako da stupac pretraživanja bude krajnji lijevi, možete koristiti kombinaciju funkcija INDEKS (KAZALO) i EKSPONIRANIJI (MATCH) kao fleksibilnija alternativa za VPR.
5. Brojevi su oblikovani kao tekst
Još jedan izvor pogreške # N / A u formulama sa VPR su brojevi u tekstualnom formatu u glavnoj tablici ili preglednoj tablici.
To se obično događa kada uvozite informacije iz vanjskih baza podataka ili kada upisujete apostrof ispred broja kako biste zadržali početnu nulu.
Najočitiji znakovi broja u tekstualnom obliku prikazani su na slici ispod:
Osim toga, brojevi se mogu pohraniti u formatu Osnovne informacije (Općenito). U ovom slučaju postoji samo jedna uočljiva značajka - brojevi su poravnati na lijevi rub ćelije, dok su prema zadanim postavkama poravnati na desni rub.
Odluka: Ako se radi o jednoj vrijednosti, samo kliknite na ikonu pogreške i odaberite Pretvori u broj (Pretvori u broj) iz kontekstnog izbornika.
Ako je ovo situacija s mnogo brojeva, odaberite ih i kliknite desnom tipkom miša na odabrano područje. U kontekstnom izborniku koji se pojavi odaberite Format Cells (Oblikuj ćelije) > tab Broj (Broj) > format Broj (Numerički) i pritisnite OK.
6. Postoji razmak na početku ili na kraju
Ovo je najmanje očit razlog pogreške. # N / A u funkciji VPR, budući da je vizualno teško vidjeti ove dodatne prostore, posebno kada radite s velikim tablicama, kada je većina podataka izvan zaslona.
Rješenje 1: Dodatni razmaci u glavnoj tablici (gdje je funkcija VLOOKUP)
Ako se u glavnoj tablici pojavljuju dodatni razmaci, možete osigurati da formule ispravno rade dodavanjem argumenta tražena_vrijednost (lookup_value) u funkciju TRIM (TRIM):
=VLOOKUP(TRIM($F2),$A$2:$C$10,3,FALSE)
=ВПР(СЖПРОБЕЛЫ($F2);$A$2:$C$10;3;ЛОЖЬ)
Rješenje 2: Dodatni razmaci u tablici pretraživanja (u stupcu pretraživanja)
Ako u stupcu za pretraživanje ima dodatnih mjesta - jednostavni načini # N / A u formuli sa VPR ne može se izbjeći. Umjesto VPR Možete koristiti formulu polja s kombinacijom funkcija INDEKS (INDEKS), EKSPONIRANIJI (UTAKMICA) i 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))
Budući da je ovo formula polja, ne zaboravite pritisnuti Ctrl + Shift + Enter umjesto uobičajenog ućikako biste ispravno unijeli formulu.
Pogreška #VALUE! u formulama s VLOOKUP-om
U većini slučajeva Microsoft Excel javlja pogrešku #VRIJEDNOST! (#VRIJEDNOST!) kada vrijednost korištena u formuli ne odgovara tipu podataka. O VPR, onda obično postoje dva razloga za grešku #VRIJEDNOST!.
1. Vrijednost koju tražite duža je 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, primit ćete poruku o pogrešci. #VRIJEDNOST!.
Odluka: Koristite hrpu značajki INDEKS+MATCH (INDEKS + MATCH). Ispod je formula koja će savršeno poslužiti za ovaj zadatak:
=INDEX(C2:C7,MATCH(TRUE,INDEX(B2:B7=F$2,0),0))
=ИНДЕКС(C2:C7;ПОИСКПОЗ(ИСТИНА;ИНДЕКС(B2:B7=F$2;0);0))
2. Potpuni put do radne knjige pretraživanja nije naveden
Ako dohvaćate podatke iz druge radne knjige, morate navesti puni put do te datoteke. Točnije, morate uključiti naziv radne knjige (uključujući ekstenziju) u uglatim zagradama [ ], nakon čega slijedi naziv lista, nakon čega slijedi uskličnik. Sve te konstrukcije moraju biti omeđene apostrofima, u slučaju da naziv knjige ili lista sadrži razmake.
Ovdje je potpuna struktura funkcije VPR za pretraživanje 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 potražiti vrijednost ćelije A2 u koloni B na plahti Sheet1 u radnoj knjižici Nove cijene i izdvojite odgovarajuću vrijednost iz stupca D.
Ako je bilo koji dio staze tablice izostavljen, vaša funkcija VPR neće raditi i prijavit će grešku #VRIJEDNOST! (čak i ako je radna knjiga s tablicom pretraživanja 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 u kojoj netko unese vrijednost manju od 1za označavanje stupca iz kojeg treba izdvojiti vrijednost. Iako je moguće ako vrijednost ovog argumenta izračunava druga Excel funkcija ugniježđena unutar VPR.
Dakle, ako se dogodi da argument col_index_num (broj_stupca) manje od 1funkcija VPR također će prijaviti grešku #VRIJEDNOST!.
Ako argument col_index_num (broj_stupca) je veći od broja stupaca u danom nizu, VPR će prijaviti grešku #REF! (#SSYL!).
Greška #NAME? u VLOOKUP-u
Najjednostavniji slučaj je pogreška #name? (#NAME?) – pojavit će se ako slučajno napišete naziv funkcije s pogreškom.
Rješenje je očito – provjerite pravopis!
VLOOKUP ne radi (ograničenja, upozorenja i odluke)
Osim prilično komplicirane sintakse, VPR ima više ograničenja od bilo koje druge Excel funkcije. Zbog tih 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 u kojima VPR krivo je.
1. VLOOKUP ne razlikuje velika i mala slova
funkcija VPR ne razlikuje velika i mala slova i prihvaća mala i velika slova kao iste. Stoga, ako postoji nekoliko elemenata u tablici koji se razlikuju samo u velikim i velikim slovima, funkcija VLOOKUP vratit će prvi pronađeni element, bez obzira na velika i mala slova.
Odluka: Koristite drugu Excel funkciju koja može izvesti okomito pretraživanje (LOOKUP, SUMPRODUCT, INDEX i MATCH) u kombinaciji s TOČNOA koji razlikuje velika i mala slova. Za više detalja možete naučiti iz lekcije – 4 načina da VLOOKUP u Excelu razlikuje velika i mala slova.
2. VLOOKUP vraća prvu pronađenu vrijednost
Kao što već znate, VPR vraća vrijednost iz zadanog stupca koja odgovara prvom pronađenom podudaranju. Međutim, možete izdvojiti 2., 3., 4. ili bilo koje drugo ponavljanje vrijednosti koju želite. Ako trebate izdvojiti sve duplicirane vrijednosti, trebat će vam kombinacija funkcija INDEKS (INDEKS), NAJMANJE (MALA) i ONLINE (RED).
3. Stupac je dodan ili uklonjen iz tablice
Nažalost, formule VPR prestati raditi svaki put kada se novi stupac doda ili ukloni iz tablice pretraživanja. To se događa jer sintaksa VPR zahtijeva da navedete cijeli raspon pretraživanja i određeni broj stupca za ekstrakciju podataka. Naravno, i zadani raspon i broj stupca mijenjaju se kada izbrišete stupac ili umetnete novi.
Odluka: I opet funkcije žure pomoći INDEKS (KAZALO) i EKSPONIRANIJI (UTAKMICA). U formuli INDEKS+MATCH Zasebno definirate stupce za pretraživanje i dohvaćanje, a kao rezultat toga možete izbrisati ili umetnuti onoliko stupaca koliko želite bez brige o ažuriranju svih povezanih formula za pretraživanje.
4. Reference ćelija su iskrivljene prilikom kopiranja formule
Ovaj naslov iscrpno objašnjava bit problema, zar ne?
Odluka: Uvijek koristite apsolutne reference ćelija (sa simbolom $) na evidenciji raspona, na primjer $A$2:$C$100 or $A:$C. Na traci formule možete brzo promijeniti vrstu veze klikom F4.
VLOOKUP – rad s funkcijama IFERROR i ISERROR
Ako ne želite plašiti korisnike porukama o greškama # N / A, #VRIJEDNOST! or #name?, možete prikazati praznu ćeliju ili vlastitu poruku. To možete učiniti postavljanjem VPR u funkciju AKO POGREŠKA (IFERROR) u Excelu 2013, 2010 i 2007 ili koristite hrpu funkcija AKO+JE POGREŠKA (IF+ISERROR) u ranijim verzijama.
VLOOKUP: rad s funkcijom IFERROR
Sintaksa funkcije AKO POGREŠKA (IFERROR) je jednostavan i govori sam za sebe:
IFERROR(value,value_if_error)
ЕСЛИОШИБКА(значение;значение_если_ошибка)
To jest, za prvi argument umetnete vrijednost za koju treba provjeriti pogrešku, a za drugi argument odredite što će se vratiti ako se pronađe pogreš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;ЛОЖЬ);"")
Ako želite prikazati vlastitu poruku umjesto standardne poruke o pogrešci funkcije VPR, stavi to pod navodnike, ovako:
=IFERROR(VLOOKUP($F$2,$B$2:$C$10,2,FALSE),"Ничего не найдено. Попробуйте еще раз!")
=ЕСЛИОШИБКА(ВПР($F$2;$B$2:$C$10;2;ЛОЖЬ);"Ничего не найдено. Попробуйте еще раз!")
VLOOKUP: rad s funkcijom ISERROR
Budući da funkcija AKO POGREŠKA pojavio u programu Excel 2007, pri radu u ranijim verzijama morat ćete koristiti kombinaciju IF (AKO) i EOSHIBKA (ISERROR) ovako:
=IF(ISERROR(VLOOKUP формула),"Ваше сообщение при ошибке",VLOOKUP формула)
=ЕСЛИ(ЕОШИБКА(ВПР формула);"Ваше сообщение при ошибке";ВПР формула)
Na primjer, formula AKO+JE POGREŠ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 pogreškama. VPR i da vaše formule rade ispravno.