#N/A kļūdas izlabošana funkcijā VLOOKUP

Šajā tēmā ir aprakstīti biežāk lietotie VLOOKUP iemesli, kāpēc funkcijai ir kļūdains rezultāts, kā arī sniegti ieteikumi par indeksa un atbilstības lietošanu.

Padoms.: Skatiet arī norādi uz ātrās uzziņas karti: VLOOKUP problēmu novēršanas padomi , kas parāda vispārējus iemeslus, kādēļ #NA problēmas ir ērtā PDF failā. Varat kopīgot PDF failu ar citiem lietotājiem vai izdrukāt savas atsauces.

Problēma: uzmeklēšanas vērtība nav argumenta table_array pirmajā kolonnā

Viens VLOOKUP ierobežojums ir tas, ka tas var meklēt tikai vērtības tabulas masīva kreisās puses kolonnā. Ja uzmeklēšanas vērtība nav masīva pirmajā kolonnā, tiks parādīta #N/A kļūda.

Šajā tabulā mēs vēlamies izgūt to vienību skaitu, kas pārdotas lapu kāpostiem.

#NA kļūda funkcijā VLOOKUP: uzmeklēšanas vērtība nav tabulas masīva pirmajā kolonnā

#N/A kļūdu rezultāti, jo table_array argumenta a2: C10 otrajā kolonnā (produkti) parādās uzmeklēšanas vērtība "kāposti" . Šajā gadījumā Excel meklē to kolonnā A, nevis kolonnā B.

Risinājums: varat mēģināt to novērst, PIELĀGOjot VLOOKUP, lai uzrādītu pareizo kolonnu. Ja tas nav iespējams, mēģiniet novietot kolonnas. Tas, iespējams, ir ļoti neizpildāms, ja jums ir lielas vai sarežģītas izklājlapas, kur šūnu vērtības ir citu aprēķinu rezultāti, vai varbūt ir arī citi loģiski iemesli, kāpēc jūs vienkārši nevarat pārvietot kolonnas apkārt. Risinājums ir izmantot funkciju INDEX un MATCH, kas var uzmeklēt vērtību kolonnā neatkarīgi no tās atrašanās vietas, kas atrodas uzmeklēšanas tabulā. Skatiet nākamo sadaļu.

Apsveriet iespēju izmantot INDEKSU/atbilstību

Index un Match ir labas iespējas daudziem gadījumiem, kad funkcija VLOOKUP neatbilst jūsu vajadzībām. Galvenā indeksa/atbilstības funkcija ir tāda, ka varat uzmeklēt vērtību kolonnā jebkurā meklēšanas tabulas vietā. INDEX atgriež vērtību no noteiktas tabulas/diapazona — atbilstoši tā vietai. Funkcija MATCH atgriež tabulas/diapazona vērtības relatīvo pozīciju. Izmantojiet INDEX un MATCH kopā formulā, lai uzmeklētu vērtību tabulā/masīvā, norādot vērtības relatīvo pozīciju tabulā/masīvā.

Ir vairākas priekšrocības, ko sniedz INDEX/MATCH, nevis VLOOKUP:

  • Izmantojot INDEX un MATCH, atgriežamā vērtība nedrīkst būt tajā pašā kolonnā, kurā ir uzmeklēšanas kolonna. Tas atšķiras no VLOOKUP, kur atgrieztajai vērtībai ir jābūt norādītajam diapazonam. Kā tas ir? Izmantojot funkciju VLOOKUP, ir jāzina tās kolonnas numurs, kurā ir atgrieztā vērtība. Kaut gan tas, iespējams, nav apgrūtinošs, varat būt traucējošs, ja jums ir liela tabula un jāskaita kolonnu skaits. Turklāt, ja tabulai pievienojat/noņemat kolonnu, ir jāpārskaita un jāatjaunina col_index_num arguments. Izmantojot INDEX un MATCH, nav nepieciešams veikt uzskaiti, jo uzmeklēšanas kolonna atšķiras no kolonnas, kurai ir atgrieztā vērtība.

  • Izmantojot INDEX un MATCH, varat norādīt rindu vai kolonnu masīvā, vai norādīt abus. Tas nozīmē, ka vērtības var uzmeklēt gan vertikāli, gan horizontāli.

  • INDEX un MATCH var izmantot, lai uzmeklētu vērtības jebkurā kolonnā. Atšķirībā no VLOOKUP, kurā varat tikai skatīt vērtību pirmajā tabulas kolonnā — INDEX un MATCH darbojas, ja uzmeklēšanas vērtība ir pirmajā kolonnā, pēdējā vai jebkurā vietā starp tām.

  • INDEX un MATCH piedāvā elastīgumu, lai padarītu dinamisku atsauci uz kolonnu, kurā ir atgriežamā vērtība.Tas nozīmē, ka tabulai varat pievienot kolonnas, nepārkāpjot INDEKSUs un SASKAŅOjot tās. No otras puses, VLOOKUP pārtrauc, ja tabulai ir jāpievieno kolonna, jo tajā ir statiska atsauce uz tabulu.

  • INDEX un MATCH piedāvā lielāku elastību attiecībā uz atbilstībām.INDEX un MATCH var atrast precīzu atbilstību vai vērtību, kas ir lielāka vai mazāka par uzmeklējamo vērtību. VLOOKUP meklēs tikai tuvāko atbilstību vērtībai (pēc noklusējuma) vai precīzā vērtībā. VLOOKUP arī pieņem pēc noklusējuma, ka tabulas masīva pirmā kolonna ir sakārtota alfabētiskā secībā, un pieņemsim, ka tabula nav iestatīta tādā veidā, VLOOKUP atgriezīs pirmo tuvāko atbilstību tabulā, kas, iespējams, nav meklējamie dati.

Sintakse

Lai izveidotu sintakses/salīdzināšanas sintaksi, lietojiet masīva/atsauces argumentu no funkcijas INDEX un ligzdojiet tajā atbilstošo sintaksi. Šādā veidā:

= INDEX (masīvs vai atsauce, atbilstība (lookup_value, lookup_array, [match_type])

Izmantojiet INDEX/MATCH, lai aizstātu VLOOKUP no iepriekš norādītā parauga. Sintakse izskatīsies šādi:

= INDEX (C2: C10, MATCH (B13; B2: B10,0))

Vienkāršā angļu valodā tas nozīmē:

= INDEX (atgriezt vērtību no C2: C10, kas sakritīs (lapu kāposti, kas ir šūnā B2: B10), kur atgrieztā vērtība ir pirmā vērtība, kas atbilst lapu kāpostiem)

Funkciju INDEX un MATCH var izmantot kā aizstāšanu funkcijā VLOOKUP

Formula meklē pirmo vērtību šūnā C2: C10, kas atbilst kāpostiem (šūnā B7), un atgriež vērtību C7 (100), kas ir pirmā vērtība, kas atbilst lapu kāpostiem.

Problēma: precīza atbilstība nav atrasta

Ja argumenta range_lookup vērtība ir FALSE — un VLOOKUP nevar atrast precīzu atbilstību datos — tā atgriež #N/a kļūdu.

Risinājums: ja esat pārliecināts, ka jūsu izklājlapā pastāv atbilstošie dati un VLOOKUP to nenoķer, nepieciešams laiks, lai pārliecinātos, vai atsaucēs norādītajās šūnās nav paslēptas atstarpes vai nedrukājamās rakstzīmes. Pārliecinieties arī, vai šūnas atbilst pareizam datu tipam. Piemēram, šūnas ar skaitļiem jāformatē kā skaitlis, nevis teksts.

Tāpat apsveriet iespēju izmantot funkciju Clean vai trim , lai notīrītu datus šūnās.

Problēma: uzmeklēšanas vērtība ir mazāka par masīva mazāko vērtību

Ja argumenta range_lookup iestatījums ir patiess, un uzmeklēšanas vērtība ir mazāka par masīva mazāko vērtību, redzēsit #N/a kļūdu. TRUE meklē aptuvenu atbilstību masīvā un atgriež tuvāko vērtību, kas ir mazāka par uzmeklējamo vērtību.

Šajā piemērā uzmeklējamā vērtība ir 100, taču šūnā B2 nav nevienas vērtības: C10 diapazons ir mazāks par 100. līdz ar to kļūda.

Kļūda VLOOKUP, ja uzmeklēšanas vērtība ir mazāka par masīva mazāko vērtību

Risinājums:

  • Pēc vajadzības izlabojiet uzmeklējamo vērtību.

  • Ja nevarat mainīt uzmeklējamo vērtību un jums ir nepieciešams lielāks elastīgums ar atbilstošajām vērtībām, apsveriet iespēju izmantot INDEKSU/atbilstību, nevis funkciju VLOOKUP — skatiet sadaļu iepriekš šajā rakstā. Izmantojot INDEX/MATCH, varat uzmeklēt vērtības, kas lielākas par, mazāks vai vienāds ar uzmeklējamo vērtību. Lai iegūtu papildinformāciju par indeksa/atbilstības izmantošanu funkcijā VLOOKUP, skatiet šīs tēmas iepriekšējo sadaļu.

Problēma: uzmeklēšanas kolonna nav kārtota augošā secībā

Ja argumenta range_lookup iestatījums ir patiess — un viena no jūsu uzmeklēšanas kolonnām nav kārtota augošā secībā (A-Z), tiks parādīta #N/a kļūda.

Risinājums:

  • Mainiet funkciju VLOOKUP, lai atrastu precīzu atbilstību. Lai to izdarītu, range_lookup iestatiet argumentu range_lookup aplams. Nav nepieciešama kārtošana APLAMam.

  • Izmantojiet funkciju INDEX/MATCH, lai uzmeklētu vērtību nesakārtotā tabulā.

Problēma: vērtība ir liels peldošā komata skaitlis

Ja šūnās ir laika vērtības vai lielie decimāldaļas skaitļi, programma Excel atgriež #N/A kļūdu, jo tā ir peldoša punkta precizitāte. Peldošā punkta numuri ir skaitļi, kas seko pēc komata. (Excel saglabā laika vērtības kā peldošā komata skaitļus.) Programma Excel nevar saglabāt skaitļus ar ļoti lieliem peldošiem punktiem, tāpēc, lai funkcija darbotos pareizi, peldošā punkta numuri ir jānoapaļo līdz 5 decimāldaļas vietām.

Risinājums: saīsiniet skaitļus, noapaļojot tos līdz pat piecām decimāldaļu vietām, izmantojot funkciju Round .

Vai jums ir jautājums par kādu konkrētu funkciju?

Jautājuma publicēšana Excel kopienas forumā

Palīdzība Excel uzlabošanā

Vai jums ir ieteikumi, kā mēs varam uzlabot nākamo programmas Excel versiju? Ja tā ir, lūdzu, skatiet tēmas sadaļā Excel User Voice.

Papildinformācija

Piezīme.:  Šī lapa ir tulkota automatizēti, un tajā var būt gramatiskas kļūdas un neprecizitātes. Mūsu nolūks ir šo saturu padarīt jums noderīgu. Vai jūs varat mūs informēt, vai informācija bija noderīga? Šeit ir raksts angļu valodā jūsu atsaucei.​

Paplašiniet savas Office prasmes
Iepazīties ar apmācību
Esiet pirmais, kas saņem jaunās iespējas
Pievienoties Office Insider programmai

Vai šī informācija bija noderīga?

Paldies par jūsu atsauksmēm!

Paldies par atsauksmēm! Šķiet, ka jums varētu būt noderīgi sazināties ar kādu no mūsu Office atbalsta speciālistiem.

×