Izmantojot programmā Excel VLOOKUP formulu, dažreiz var rasties neglīta kļūda #N/A. Tas notiek, ja jūsu formula nevar atrast uzmeklēšanas vērtību.
Šajā apmācībā es parādīšu dažādus veidus, kā izmantot IFERROR ar VLOOKUP, lai apstrādātu šīs #N/A kļūdas, kas parādās jūsu darblapā.
Izmantojot IFERROR kombināciju ar VLOOKUP, kļūdas #N/A (vai jebkuras citas kļūdas) vietā varat parādīt kaut ko nozīmīgu.
Pirms iedziļināties šīs kombinācijas izmantošanas detaļās, vispirms ātri apskatīsim funkciju IFERROR un redzēsim, kā tā darbojas.
IFERROR funkcija izskaidrota
Izmantojot funkciju IFERROR, varat norādīt, kas jānotiek, ja formula vai šūnas atsauce atgriež kļūdu.
Šeit ir funkcijas IFERROR sintakse.
= IFERROR (vērtība, vērtība_ja_kļūda)
- vērtība - tas ir arguments, kas tiek pārbaudīts, vai nav kļūdas. Vairumā gadījumu tā ir formula vai šūnas atsauce. Izmantojot VLOOKUP ar IFERROR, šī argumentācija būtu VLOOKUP formula.
- value_if_error - šī ir vērtība, kas tiek atgriezta kļūdas gadījumā. Novērtēti šādi kļūdu veidi: #N/A, #REF !, #DIV/0!, #VALUE !, #NUM !, #NAME ?, un #NULL !.
Iespējamie VLOOKUP cēloņi Atgriežot kļūdu #N/A
Funkcija VLOOKUP var atgriezt #N/A kļūdu šādu iemeslu dēļ:
- Meklēšanas vērtība nav atrodama uzmeklēšanas masīvā.
- Uzmeklēšanas vērtībā (vai tabulas masīvā) ir vadošā, beigu vai dubultā atstarpe.
- Uzmeklēšanas vērtībā vai uzmeklēšanas masīva vērtībās ir pareizrakstības kļūda.
Jūs varat tikt galā ar visiem šiem kļūdu cēloņiem, apvienojot IFERROR un VLOOKUP. Tomēr jums vajadzētu sekot līdzi 2. un 3. cēlonim un izlabot tos avota datos, nevis ļaut IFERROR tos risināt.
Piezīme: IFERROR apstrādātu kļūdu neatkarīgi no tā, kas to izraisījis. Ja vēlaties tikai novērst kļūdas, ko izraisījusi VLOOKUP, neatrodot uzmeklēšanas vērtību, izmantojiet IFNA. Tas nodrošinās, ka netiek apstrādātas citas kļūdas, nevis #N/A, un jūs varat izpētīt šīs citas kļūdas.
Izmantojot TRIM funkciju, varat apstrādāt sākuma, beigu un dubultās atstarpes.
VLOOKUP #N/A kļūdas aizstāšana ar jēgpilnu tekstu
Pieņemsim, ka jums ir datu kopa, kā parādīts zemāk:
Kā redzat, VLOOKUP formula atgriež kļūdu, jo uzmeklēšanas vērtība nav sarakstā. Mēs meklējam rezultātu Glenam, kas nav rezultātu tabulā.
Lai gan šī ir ļoti maza datu kopa, jūs varat iegūt milzīgas datu kopas, kurās jāpārbauda daudzu vienumu parādīšanās. Katrā gadījumā, kad vērtība netiek atrasta, tiks parādīta kļūda #N/A.
Šeit ir formula, kuru varat izmantot, lai iegūtu neko jēgpilnu kļūdas #N/A vietā.
= IFERROR (VLOOKUP (D2, $ A $ 2: $ B $ 10,2,0), "Nav atrasts")
Iepriekš minētā formula atgriež tekstu “Not Found”, nevis kļūdas #N/A vietā. Varat arī izmantot to pašu formulu, lai atgrieztu tukšu, nulli vai citu nozīmīgu tekstu.
VLOOKUP ligzdošana ar IFERROR funkciju
Ja izmantojat VLOOKUP un jūsu uzmeklēšanas tabula ir sadrumstalota tajā pašā darblapā vai dažādās darblapās, jums ir jāpārbauda VLOOKUP vērtība, izmantojot visas šīs tabulas.
Piemēram, tālāk redzamajā datu kopā ir divas atsevišķas studentu vārdu un punktu tabulas.
Ja man šajā datu kopā ir jāatrod Grace rādītājs, man jāizmanto funkcija VLOOKUP, lai pārbaudītu pirmo tabulu, un, ja vērtība tajā nav atrodama, pārbaudiet otro tabulu.
Šeit ir ligzdotā IFERROR formula, kuru varu izmantot, lai meklētu vērtību:
= IFERROR (VLOOKUP (G3, $ A $ 2: $ B $ 5,2,0), IFERROR (VLOOKUP (G3, $ D $ 2: $ E $ 5,2,0), "Nav atrasts"))
VLOOKUP izmantošana kopā ar IF un ISERROR (versijas pirms Excel 2007)
Funkcija IFERROR tika ieviesta programmā Excel 2007 operētājsistēmai Windows un Excel 2016 operētājsistēmā Mac.
Ja izmantojat iepriekšējās versijas, funkcija IFERROR jūsu sistēmā nedarbosies.
Jūs varat atkārtot IFERROR funkcijas funkcionalitāti, izmantojot IF funkcijas un ISERROR funkcijas kombināciju.
Ļaujiet man ātri parādīt, kā IF un ISERROR kombināciju izmantot IFERROR vietā.
Iepriekš minētajā piemērā IFERROR vietā varat izmantot arī formulu, kas parādīta šūnā B3:
= JA (ISERROR (A3), “Not Found”, A3)
Formulas daļa ISERROR pārbauda kļūdas (ieskaitot kļūdu #N/A) un atgriež TRUE, ja tiek atrasta kļūda, un FALSE, ja tā nav.
- Ja tā ir TRUE (tas nozīmē, ka ir kļūda), funkcija IF atgriež norādīto vērtību (šajā gadījumā “Not Found”).
- Ja tas ir FALSE (tas nozīmē, ka nav kļūdas), funkcija IF atgriež šo vērtību (A3 iepriekš minētajā piemērā).
IFERROR pret IFNA
IFERROR apstrādā visu veidu kļūdas, savukārt IFNA - tikai #N/A kļūdu.
Rīkojoties ar VLOOKUP izraisītām kļūdām, jums jāpārliecinās, vai izmantojat pareizo formulu.
Izmantojiet IFERROR kad vēlaties izturēties pret visa veida kļūdām. Tagad kļūdu var izraisīt dažādi faktori (piemēram, nepareiza formula, kļūdaini uzrakstīts nosauktais diapazons, uzmeklēšanas vērtības neatrašana un kļūdas vērtības atgriešana no uzmeklēšanas tabulas). IFERROR tam nebūtu nozīmes, un tas visas šīs kļūdas aizstātu ar norādīto vērtību.
Izmantojiet IFNA ja vēlaties novērst tikai #N/A kļūdas, kuras, visticamāk, izraisa VLOOKUP formula, nespējot atrast uzmeklēšanas vērtību.
Jums var noderēt arī šādas Excel apmācības:
- Kā padarīt VLOOKUP reģistrjutīgu.
- VLOOKUP vs. INDEKSS/MATCH - Debates beidzas šeit!
- Izmantojiet VLookup, lai Excel iegūtu pēdējo numuru sarakstā.
- Kā lietot VLOOKUP ar vairākiem kritērijiem
- #NAME kļūda programmā Excel - kas to izraisa un kā to novērst!
