Šajā tēmā ir izklāstīti visbiežāk sastopamie funkcijas VLOOKUP cēloņi kļūdainam rezultātam un sniegti ieteikumi funkcijas INDEX un MATCH lietošana.
Padoms.: Skatiet arī ātrās uzziņas karti : VLOOKUP problēmu novēršanas padomi, kas bieži rada #NA problēmas ērtā PDF failā. Varat koplietot PDF failu ar citiem vai izdrukāt savām atsaucēm.
Problēma. Uzmeklējamā vērtība nav argumenta table_array pirmajā kolonnā
VLOOKUP ierobežojums ir tāds, ka tā var meklēt tikai vērtības tabulas masīva kreisajā lielākajā kolonnā. Ja jūsu uzmeklējamā vērtība nav masīva pirmajā kolonnā, tiks parādīts #N/A kļūda.
Tālāk esošajā tabulā ir jāiegūst Kale pārdoto vienību skaits.
Kļūdas #N/A rezultāts ir šāds, jo argumenta A2:C10 otrajā kolonnā (Produkti table_array tiek rādīta uzmeklējamā vērtība "Kale". Šajā gadījumā Excel to meklē kolonnā A, nevis kolonnā B.
Risinājums: Varat mēģināt to novērst, pielāgojot savu VLOOKUP atsaucei uz pareizo kolonnu. Ja tas nav iespējams, mēģiniet pārvietot kolonnas. Tas var būt ļoti neasijami, ja jums ir lielas vai sarežģītas izklājlapas, kurās šūnu vērtības ir citu aprēķinu rezultāti, vai arī ir citi loģiski iemesli, kāpēc vienkārši nevarat pārvietot kolonnas pa kolonnām. Risinājums ir izmantot funkciju INDEX un MATCH kombināciju, kas var uzmeklēt vērtību kolonnā neatkarīgi no atrašanās vietas uzmeklēšanas tabulā. Skatiet nākamo sadaļu.
Apsveriet iespēju to vietā izmantot INDEX/MATCH
INDEX un MATCH ir labs variants daudziem gadījumiem, kad VLOOKUP neatbilst jūsu vajadzībām. INDEX/MATCH galvenā priekšrocība ir tā, ka varat uzmeklēt vērtību kolonnā jebkurā uzmeklēšanas tabulas vietā. INDEX atgriež vērtību no norādītās tabulas/diapazona atbilstoši tās pozīcijai. MATCH atgriež vērtības relatīvo pozīciju tabulā/diapazonā. Izmantojiet kopā INDEX un MATCH formulā, lai uzmeklē vērtību tabulā/masīvā, norādot vērtības relatīvo atrašanās vietu tabulā/masīvā.
Funkcijas INDEX/MATCH izmantošana VLOOKUP vietā sniedz vairākas priekšrocības:
-
Izmantojot INDEX un MATCH, atgrieztās vērtības nav jābūt tajā pašā kolonnā, kurā atrodas uzmeklēšanas kolonna. Tas atšķiras no funkcijas VLOOKUP, kurā atgrieztās vērtības diapazonam ir jābūt norādītajā diapazonā. Kā tas ir svarīgi? Izmantojot funkciju VLOOKUP, ir jāzina tās kolonnas numurs, kurā ir ietverta atgriežamā vērtība. Lai gan tas var šķist sarežģīti, tas var būt apgrūtinoši, ja jums ir liela tabula un ir jāskaita kolonnu skaits. Turklāt, ja tabulā pievienojat/noņemat kolonnu, tās konts ir jāatjaunina un col_index_num dati. Izmantojot INDEX un MATCH, nav nepieciešama skaitīšana, jo uzmeklēšanas kolonna atšķiras no kolonnas, kurā ir atgrieztā vērtība.
-
Izmantojot INDEX un MATCH, masīvā var norādīt rindu vai kolonnu vai norādīt abus. Tas nozīmē, ka vērtības varat uzmeklēs gan vertikāli, gan horizontāli.
-
INDEX un MATCH var izmantot, lai uzmeklē vērtības jebkurā kolonnā. Atšķirībā no VLOOKUP, kurā var tikai uzmeklēt vērtību tabulas pirmajā kolonnā — INDEX un MATCH darbosies, ja jūsu uzmeklējamā vērtība ir pirmajā kolonnā, pēdējā vai jebkurā vietā starp.
-
INDEX un MATCH nodrošina elastību, veidojot dinamisku atsauci uz kolonnu, kurā ir ietverta atgriežamā vērtība. Tas nozīmē, ka tabulai var pievienot kolonnas, nesasverot INDEX un MATCH. No otras puses, VLOOKUP pārtrauc darbības, ja tabulai ir jāpievieno kolonna, jo tā veido statisku atsauci uz tabulu.
-
INDEX un MATCH nodrošina lielāku elastību, izmantojot atbilstības. 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īzai vērtībai. VLOOKUP pēc noklusējuma pieņem arī to, ka tabulas masīva pirmā kolonna ir sakārtota alfabētiskā secībā un pieņemsim, ka tabula nav iestatīta šādā veidā, VLOOKUP atgriezīs pirmo tuvāko atbilstību tabulā, kas var nebūt tieši tie dati, kurus meklējat.
Sintakse
Lai izveidotu INDEX/MATCH sintaksi, ir jāizmanto masīva/atsauces arguments no funkcijas INDEX un tajā jāligzdo MATCH sintakse. Tiek veidots šāds veidlapa:
=INDEX(masīvs vai atsauce, MATCH(lookup_value,lookup_array,[match_type])
Izmantosim INDEX/MATCH, lai aizstātu VLOOKUP no iepriekš minētā piemēra. Sintakse izskatīsies šādi:
=INDEX(C2:C10,MATCH(B13,B2:B10,0))
Vienkāršā latviešu valodā tas nozīmē:
=INDEX(atgriež vērtību no C2:C10, kas būs MATCH(Kale, kas ir B2:B10 masīva vieta, kur atgrieztā vērtība ir pirmā vērtība, kas atbilst Kale))
Formula meklē pirmo vērtību šūnā C2:C10, kas atbilst Funkcijai Kale (šūnā B7) un atgriež vērtību šūnā C7 (100), kas ir pirmā vērtība, kas atbilst Kale.
Problēma. Precīza atbilstība nav atrasta
Ja range_lookup ir FALSE un VLOOKUP nevar atrast precīzu atbilstību datos — tā atgriež kļūdu #N/A.
Risinājums Ja esat pārliecināts, ka jūsu izklājlapā ir atbilstošie dati un VLOOKUP tos neiegūst, pārbaudiet, vai atsauces šūnās nav slēptu atstarpju vai nedrukājamas rakstzīmes. Pārliecinieties, vai šūnām ir pareizais datu tips. Piemēram, šūnām ar skaitļiem jābūt formatētām kā Skaitlis, nevis Teksts.
Tāpat apsveriet iespēju izmantot funkciju CLEAN vai TRIM , lai notīrītu šūnās esošās datus.
Problēma. Uzmeklējamā vērtība ir mazāka par mazāko masīva vērtību
Ja range_lookup iestatīta vērtība TRUE (un uzmeklējamā vērtība ir mazāka par mazāko masīva vērtību), tiks parādīts #N/A kļūda. TRUE meklē aptuvenu atbilstību masīvā un atgriež tuvāko vērtību mazāku par uzmeklējamo vērtību.
Šajā piemērā uzmeklējamā vērtība ir 100, bet diapazonā B2:C10 nav nevienas vērtības, kas būtu mazākas par 100; tādējādi kļūda.
Risinājums:
-
Ja nepieciešams, labojiet uzmeklējamo vērtību.
-
Ja nevarat mainīt uzmeklējamo vērtību un ir nepieciešama lielāka elastība, saskaņojot vērtības, apsveriet iespēju izmantot INDEX/MATCH, nevis VLOOKUP, skatiet iepriekš šī raksta sadaļu. Izmantojot INDEX/MATCH, varat uzmeklēt vērtības, kas lielākas, mazākas vai vienādas ar uzmeklējamo vērtību. Lai iegūtu papildinformāciju par INDEX/MATCH izmantošanu VLOOKUP vietā, skatiet šīs tēmas iepriekšējo sadaļu.
Problēma. Uzmeklēšanas kolonna nav sakārtota augošā secībā
Ja arguments range_lookup ir iestatīts kā TRUE (un viena no jūsu uzmeklēšanas kolonnām nav sakārtota augošā (A–Z) secībā), tiek parādīts #N/A kļūda.
Risinājums:
-
Mainiet funkciju VLOOKUP, lai meklētu precīzu atbilstību. Lai to izdarītu, iestatiet range_lookup vērtību FALSE. FALSE gadījumā kārtošana nav nepieciešama.
-
Izmantojiet funkciju INDEX/MATCH, lai uzmeklēt vērtību nesalasītā tabulā.
Problēma. Vērtība ir liels skaitlis ar peldošo komata vērtību
Ja šūnās ir laika vērtības vai lieli decimāldaļskaitļi, Excel peldošā komata precizitātes #N atgriež kļūdu #N/A. Peldošā komata skaitļi ir skaitļi aiz decimālzīmes. (Excel laika vērtības saglabā kā peldošā komata skaitļus.) Programma Excel nevar saglabāt skaitļus ar ļoti lieliem peldošajiem punktiem, tāpēc, lai funkcija darbotos pareizi, skaitlis ar peldošo komatu būs jānoapaļo līdz 5 decimāldaļas vietām.
Risinājums Saīsiniet skaitļus, noapaļojot tos uz augšu līdz piecām decimālvietām aiz komata, izmantojot funkciju ROUND .
Vai nepieciešama papildu palīdzība?
Vienmēr varat pajautāt speciālistam Excel tech kopienā vai saņemt atbalstu kopienās.
