Kako odpraviti napake VLOOKUP v Excelu

Kako

AvtorBen Stockton

Nazadnje posodobljeno dne13. januar 2020

Malo je stvari, ki izvabijo znoj Microsoft Excel uporabniki bolj kot mislijo o napaki VLOOKUP. Če niste preveč seznanjeni z Excelom, je VLOOKUP ena najtežjih ali vsaj ena najmanj razumljenih funkcij, ki je na voljo.

Namen VLOOKUP je iskanje in vračanje podatkov iz drugega stolpca v Excelovi preglednici. Na žalost, če napačno razumete formulo VLOOKUP, bo Excel na vas vrgel napako. Pojdimo skozi nekaj običajnih napak VLOOKUP in razložimo, kako jih odpraviti.

Omejitve VLOOKUP

Preden začnete uporabljati VLOOKUP, se morate zavedati, da ni vedno najboljša možnost za uporabnike Excela.

Za začetek ga ni mogoče uporabiti za iskanje podatkov levo od njega. Prikazal bo tudi prvo vrednost, ki jo najde, kar pomeni, da VLOOKUP ni možnost za obsege podatkov z podvojenimi vrednostmi. Vaš stolpec iskanja mora biti tudi najbolj oddaljeni levi stolpec v vašem podatkovnem območju.

V spodnjem primeru je najbolj oddaljen stolpec (stolpec A) se uporablja kot stolpec za iskanje. V območju ni podvojenih vrednosti in iskalnih podatkov (v tem primeru podatkov iz stolpec B) je desno od iskalnega stolpca.

Funkciji INDEX in MATCH bi bile dobre možnosti, če bi bila katera od teh težav težava, kot bi bila tudi nova funkcija XLOOKUP v Excelu, ki je trenutno v beta testiranju.

VLOOKUP zahteva tudi, da so podatki razporejeni v vrstice, da lahko natančno iščejo in vrnejo podatke. HLOOKUP bi bil dobra alternativa, če temu ne bi šlo.

Nekaj ​​dodatnih omejitev formul VLOOKUP lahko povzroči napake, kot bomo pojasnili v nadaljevanju.

Napake VLOOKUP in # N / A

Ena najpogostejših napak VLOOKUP v Excelu je # N / A napaka. Do napake pride, ko VLOOKUP ne najde vrednosti, ki jo iščete.

Za začetek vrednost iskanja v vašem območju podatkov morda ne obstaja ali pa ste uporabili napačno vrednost. Če opazite napako N / A, dvakrat preverite vrednost v formuli VLOOKUP.

Če je vrednost pravilna, vrednost iskanja ne obstaja. To pomeni, da uporabljate VLOOKUP za iskanje natančnih ujemanj z domen_pozor argument nastavljen na LAŽNO.

V zgornjem primeru iskanje a Študentska izkaznica s številka 104 (v celici G4) vrne # N / Napaka ker je najmanjša ID številka v območju 105.

Če je domen_pozor argument na koncu formule VLOOKUP manjka ali nastavljen na PRAV, bo VLOOKUP vrnil napako # N / A, če obseg podatkov ni razvrščen v naraščajočem vrstnem redu.

Prav tako bo vrnil napako # N / A, če je vrednost iskanja manjša od najnižje vrednosti v razponu.

V zgornjem primeru je Študentska izkaznica vrednosti so zmešane. Kljub vrednosti 105 obstoječ v razponu, VLOOKUP ne more pravilno iskati z domen_pozor argument nastavljen na PRAV Ker stolpec A ni razvrščeno po naraščajočem vrstnem redu.

Drugi pogosti razlogi za napake # N / A vključujejo uporabo stolpca za iskanje, ki ni najbolj levo levo, in uporabo celice reference za iskalne vrednosti, ki vsebujejo številke, vendar so oblikovane kot besedilo ali vsebujejo presežne znake, kot so presledki.

Odpravljanje napak # VALUE Napake

The # VALUE Napaka je ponavadi znak, da je formula, ki vsebuje funkcijo VLOOKUP, na nek način napačna.

V večini primerov je to običajno zaradi celice, ki jo omenjate kot svojo iskalno vrednost. The največja velikost vrednosti za iskanje VLOOKUP je 255 znakov.

Če imate opravka s celicami, ki vsebujejo daljše nizove znakov, VLOOKUP ne bo mogel z njimi ravnati.

Edino rešitev za to je zamenjati formulo VLOOKUP s kombinirano formulo INDEX in MATCH. Na primer, če en stolpec vsebuje celico z nizom več kot 255 znakov, se lahko namesto teh podatkov uporabi nameščena funkcija MATCH znotraj INDEX-a.

V spodnjem primeru INDEX vrne vrednost v celici B4, z uporabo obsega v stolpec A za prepoznavanje pravilne vrstice. Ta uporablja ugnezdene Ujemanje funkcija za prepoznavanje niza v stolpec A (ki vsebuje 300 znakov), ki ustreza celici H4.

V tem primeru je to celica A4, s INDEX vračanje 108 (vrednost B4).

Ta napaka se bo prikazala tudi, če ste v formuli uporabili napačno sklicevanje na celice, še posebej, če uporabljate obseg podatkov iz drugega delovnega zvezka.

Za pravilno delovanje formule je treba priložiti sklice na delovne zvezke v kvadratne oklepaje.

Če naletite na napako # VALUE, dvakrat preverite formulo VLOOKUP in potrdite, da so vaše sklice na celice pravilne.

#NAME in VLOOKUP

Če napaka VLOOKUP ni napaka # VALUE ali napaka # N / A, potem je verjetno # NAME napaka. Preden se zapletete ob misli na to, bodite prepričani - to je najlažjo napako VLOOKUP, ki jo je mogoče odpraviti.

Napaka #NAME se pojavi, ko ste napačno črkovali funkcijo v Excelu, ne glede na to, ali gre za VLOOKUP ali drugo funkcijo, kot je SUM. Kliknite svojo celico VLOOKUP in dvakrat preverite, ali ste VLOOKUP dejansko črkovali pravilno.

Če ni drugih težav, bo formula VLOOKUP delovala, ko bo ta napaka odpravljena.

Uporaba drugih funkcij Excela

To je krepka izjava, vendar deluje kot VLOOKUP vam bo spremenil življenje. Vsaj to bo spremenilo vašo življenjsko dobo in Excel postal močnejše orodje za analizo podatkov.

Če VLOOKUP ni pravi za vas, izkoristite to top Excel funkcije namesto tega.