Pri uporabi formule VLOOKUP v Excelu lahko včasih pride do grde napake #N/A. To se zgodi, ko formula ne najde iskalne vrednosti.
V tej vadnici vam bom pokazal različne načine uporabe IFERROR z VLOOKUP za obravnavo teh #N/A napak, ki se pojavijo na vašem delovnem listu.
Uporaba kombinacije IFERROR z VLOOKUP -om vam omogoča, da namesto napake #N/A prikažete nekaj smiselnega (ali katero koli drugo napako).
Preden se lotimo podrobnosti o uporabi te kombinacije, najprej hitro pojdimo skozi funkcijo IFERROR in poglejmo, kako deluje.
Pojasnjena funkcija IFERROR
S funkcijo IFERROR lahko določite, kaj naj se zgodi, če formula ali sklic na celico vrne napako.
Tu je sintaksa funkcije IFERROR.
= IFERROR (vrednost, value_if_error)
- vrednost - to je argument, ki se preveri glede napake. V večini primerov je to formula ali referenca celice. Pri uporabi VLOOKUP z IFERROR bi bila formula VLOOKUP ta argument.
- value_if_error - to je vrednost, ki se vrne, če pride do napake. Ocenjene so naslednje vrste napak: #N/A, #REF !, #DIV/0 !, #VALUE !, #NUM !, #NAME ?, in #NULL !.
Možni vzroki, da VLOOKUP vrne napako #N/A
Funkcija VLOOKUP lahko vrne napako #N/A zaradi katerega koli od naslednjih razlogov:
- Iskalne vrednosti ni mogoče najti v matriki za iskanje.
- V iskalni vrednosti (ali v matriki tabel) je začetni, zadnji ali dvojni presledek.
- V iskalni vrednosti ali vrednostih v iskalni matriki je črkovalna napaka.
Vse te vzroke napak lahko odpravite s kombinacijo IFERROR in VLOOKUP. Vendar pa bodite pozorni na vzroke #2 in #3 in jih popravite v izvornih podatkih, namesto da dovolite IFERROR, da jih obravnava.
Opomba: IFERROR bi napako obravnaval ne glede na to, kaj jo je povzročilo. Če želite le odpraviti napake, ki jih VLOOKUP ne more najti iskalne vrednosti, uporabite IFNA. Tako boste zagotovili, da se napake, razen #N/A, ne obravnavajo, zato lahko te druge napake raziščete.
S funkcijo TRIM lahko obdelate vodilne, zadnje in dvojne presledke.
Zamenjava napake VLOOKUP #N/A s smiselnim besedilom
Recimo, da imate nabor podatkov, kot je prikazano spodaj:
Kot vidite, formula VLOOKUP vrne napako, ker iskalne vrednosti ni na seznamu. Iščemo rezultat za Glen, ki ga ni v tabeli rezultatov.
Čeprav je to zelo majhen nabor podatkov, lahko dobite ogromne nabore podatkov, kjer morate preveriti pojav številnih elementov. Za vsak primer, ko vrednosti ne najdete, boste dobili napako #N/A.
Tukaj je formula, s katero lahko namesto napake #N/A dobite nekaj smiselnega.
= NAPAKA (VLOOKUP (D2, $ A $ 2: $ B $ 10,2,0), "Ni najdeno")
Zgornja formula namesto napake #N/A vrne besedilo »Ni najdeno«. Z isto formulo lahko vrnete prazno, nič ali katero koli drugo smiselno besedilo.
Gnezdenje VLOOKUP s funkcijo IFERROR
Če uporabljate VLOOKUP in je vaša iskalna tabela razdrobljena na istem delovnem listu ali različnih delovnih listih, morate vrednost VLOOKUP preveriti v vseh teh tabelah.
Na primer, v nizu podatkov, prikazanem spodaj, obstajata dve ločeni tabeli imen učencev in rezultatov.
Če moram v tem naboru podatkov najti oceno Grace, moram s funkcijo VLOOKUP preveriti prvo tabelo, če pa vrednosti v njej ni, pa še drugo.
Tukaj je ugnezdena formula IFERROR, ki jo lahko uporabim za iskanje vrednosti:
= NAPAKA (VLOOKUP (G3, $ A $ 2: $ B $ 5,2,0), IFERROR (VLOOKUP (G3, $ D $ 2: $ E $ 5,2,0), "Ni najdeno"))
Uporaba VLOOKUP z IF in ISERROR (različice pred Excelom 2007)
Funkcija IFERROR je bila predstavljena v Excelu 2007 za Windows in Excel 2016 v Macu.
Če uporabljate prejšnje različice, funkcija IFERROR v vašem sistemu ne bo delovala.
Funkcijo funkcije IFERROR lahko podvojite s kombinacijo funkcije IF in funkcije ISERROR.
Naj vam na hitro pokažem, kako uporabiti kombinacijo IF in ISERROR namesto IFERROR.
V zgornjem primeru lahko namesto IFERROR uporabite tudi formulo, prikazano v celici B3:
= IF (ISERROR (A3), »Ni najdeno«, A3)
Del formule ISERROR preveri napake (vključno z napako #N/A) in vrne TRUE, če je napaka ugotovljena, in FALSE, če ne.
- Če je TRUE (kar pomeni, da je prišlo do napake), funkcija IF vrne podano vrednost (v tem primeru »Ni najdeno«).
- Če je FALSE (kar pomeni, da ni napake), funkcija IF vrne to vrednost (A3 v zgornjem primeru).
IFERROR V primerjavi z IFNA
IFERROR obravnava vse vrste napak, IFNA pa le napako #N/A.
Pri ravnanju z napakami, ki jih povzroči VLOOKUP, se morate prepričati, da uporabljate pravo formulo.
Uporabite IFERROR ko želite odpraviti vse vrste napak. Napako lahko povzročijo različni dejavniki (na primer napačna formula, napačno črkovan obseg, iskanje vrednosti iskanja in vrnitev vrednosti napake iz iskalne tabele). Za IFERROR ne bi bilo pomembno in bi vse te napake nadomestil z določeno vrednostjo.
Uporabite IFNA ko želite obravnavati samo #N/A napake, za katere je bolj verjetno, da formula VLOOKUP ne more najti iskalne vrednosti.
Koristni vam bodo lahko tudi naslednji vadnice programa Excel:
- Kako narediti VLOOKUP občutljiv na velike in male črke.
- VLOOKUP vs. INDEX/MATCH - Razprava se konča tukaj!
- Uporabite VLookup, da dobite zadnjo številko na seznamu v Excelu.
- Kako uporabljati VLOOKUP z več merili
- Napaka #NAME v Excelu - kaj jo povzroča in kako jo odpraviti!
