Pri uporabi formule VLOOKUP v Excelu lahko včasih naletite na grdo napako #N/A. Do tega pride, ko formula ne more najti iskane vrednosti.
V tej vadnici vam bom pokazal različne načine uporabe IFERROR in VLOOKUP za obravnavo teh #N/A napak na delovnih listih.
Uporabite IFERROR v kombinaciji z VLOOKUP, da prikažete nekaj pomembnega namesto napake #N/A (ali katere koli druge napake).
Preden se poglobimo v podrobnosti o uporabi te kombinacije, si oglejmo na hitro funkcijo IFERROR, da vidimo, kako deluje.
vsebina
Opis funkcije IFERROR
S funkcijo IFERROR lahko določite, kaj naj se zgodi, ko formula ali sklic na celico vrne napako.
To je sintaksa funkcije IFERROR.
=IFERROR(vrednost, vrednost_če_napaka)
- vrednost - To je parameter za preverjanje napak.V večini primerov gre za formulo ali sklic na celico.Če uporabljate VLOOKUP z IFERROR, bo ta parameter formula VLOOKUP.
- vrednost_če_napaka – To je vrednost, vrnjena, ko pride do napake.Ocenjene so bile naslednje vrste napak: #N/A, #REF!, #DIV/0!, #VALUE!, #NUM!, #NAME? in #NULL!.
Možni razlogi, da VLOOKUP vrne napake #N/A
Funkcija VLOOKUP lahko vrne napako #N/A iz katerega koli od naslednjih razlogov:
- Iskalne vrednosti ni bilo mogoče najti v iskalnem nizu.
- V iskalni vrednosti (ali matriki tabele) so začetni, končni ali dvojni presledki.
- V iskalni vrednosti ali iskalni vrednosti v matriki je tipkarska napaka.
Za obravnavo vseh teh vzrokov napak lahko uporabite funkcijo IFERROR in VLOOKUP. Vendar pa bodite pozorni na vzroka št. 2 in št. 3 ter odpravite te težave v izvornih podatkih, namesto da bi jih pustili obravnati funkciji IFERROR.
Opomba: Funkcija IFERROR bo obravnavala napake ne glede na njihov vzrok. Če želite obravnavati le napake, ki jih povzroči funkcija VLOOKUP, ki ne najde iskalne vrednosti, uporabite IFNA. S tem boste zagotovili, da se ne bodo obravnavale druge napake, razen #N/A, in da boste lahko te druge napake raziskali.
Funkcijo TRIM lahko uporabite za obdelavo začetnih, končnih in dvojnih presledkov.
Napake VLOOKUP #N/A zamenjajte s smiselnim besedilom
Recimo, da imate nabor podatkov, ki izgleda takole:
Kot lahko vidite, formula VLOOKUP vrne napako, ker iskalne vrednosti ni na seznamu. Iščemo Glenov rezultat, ki ga ni v tabeli rezultatov.
Čeprav gre za zelo majhen nabor podatkov, lahko dobite ogromen nabor podatkov, v katerem morate preveriti prisotnost številnih elementov. Za vsak primer, ko vrednost ni najdena, boste prejeli napako #N/A.
To je formula, ki jo lahko uporabite, da dobite nekaj smiselnega in ne #N/A narobe.
=ČE NAPAKA(VLOOKUP(D2,$A$2:$B$10,2,0),"Ni najdeno")
Zgornja formula namesto napake #N/A vrne besedilo »Ni najdeno«. Isto formulo lahko uporabite tudi za vrnitev praznega polja, ničle ali katerega koli drugega smiselnega besedila.
Ugnezdeni VLOOKUP s funkcijo IFERROR
Če uporabljate VLOOKUP in so vaše iskalne tabele razporejene po istem delovnem listu ali različnih delovnih listih, morate preveriti vrednost VLOOKUP skozi vse te tabele.
Na primer, v spodnjem nizu podatkov sta dve ločeni tabeli z imeni in rezultati študentov.
Če moram v tem naboru podatkov najti rezultat Grace, moram uporabiti funkcijo VLOOKUP, da preverim prvo tabelo, in če vrednosti ni v njej, preverite drugo tabelo.
Tukaj je ugnezdena formula IFERROR, ki jo lahko uporabim za iskanje vrednosti:
=IFERROR(VLOOKUP(G3,$A$2:$B$5,2,0),IFERROR(VLOOKUP(G3,$D$2:$E$5,2,0),"Not Found"))
Uporaba VLOOKUP s IF in ISERROR (pred Excelom 2007)
Funkcija IFERROR je bila predstavljena v Excelu 2007 za Windows in Excel 2016 v Macu.
Če uporabljate prejšnjo različico, funkcija IFERROR ne bo delovala v vašem sistemu.
Funkcionalnost funkcije IFERROR lahko ponovite tako, da združite funkcijo IF s funkcijo ISERROR.
Naj vam na hitro pokažem, kako namesto IFERROR uporabite kombinacijo IF in ISERROR.
V zgornjem primeru lahko namesto uporabe IFERROR uporabite tudi formulo, prikazano v celici B3:
=ČE(ISERROR(A3),"Ni najdeno",A3)
Del formule ISERROR preveri napake (vključno z napakami #N/A) in vrne TRUE, če je bila najdena napaka, FALSE drugače.
- Če je TRUE (kar kaže na napako), funkcija IF vrne določeno vrednost (v tem primeru "ni najdeno").
- Če je FALSE (kar pomeni, da ni napake), bo funkcija IF vrnila to vrednost (A3 v zgornjem primeru).
IFERROR in IFNA
IFERROR obravnava vse vrste napak, medtem ko IFNA obravnava samo napake #N/A.
Pri obravnavanju napak, ki jih povzroča VLOOKUP, morate zagotoviti, da uporabljate pravilno formulo.
Ko morate obravnavati različne napake, uporabite funkcijo IFERROR. Napake lahko zdaj povzročijo različni dejavniki (kot so napačne formule, napačno črkovani obsegi poimenovanj, nenajdene iskalne vrednosti in vrednosti napak, vrnjene iz iskalnih tabel). Funkcija IFERROR ni pomembna; vse te napake nadomesti z navedeno vrednostjo.
Ko želite obravnavati samo napake #N/A , je uporaba funkcije IFNA najverjetneje posledica tega, da formula VLOOKUP ne najde iskane vrednosti.
