cuntenutu
Sta lezzione spiega cumu si tratta rapidamente di una situazione induve una funzione VPR (VLOOKUP) ùn vole micca travaglià in Excel 2013, 2010, 2007 è 2003, è cumu identificà è risolve errori cumuni è superà e limitazioni VPR.
In parechji articuli previ, avemu scupertu e diverse facet di a funzione VPR in Excel. Se li avete lettu cù cura, avà duvete esse un espertu in questu campu. Tuttavia, ùn hè micca senza ragiuni chì parechji esperti Excel crede VPR una di e caratteristiche più cumplesse. Hà una mansa di limitazioni è funziunalità chì diventanu a fonte di parechji prublemi è errori.
In questu articulu truverete spiegazioni simplici di l'errori #AT (#N/A), #NAME? (#NAME?) è #VALU! (#VALUE!) chì appariscenu quandu travaglia cù a funzione VPR, è ancu e tecniche è i metudi di trattà cun elli. Cuminciaremu cù i casi più cumuni è i motivi più evidenti per quessa. VPR ùn viaghja micca, cusì hè megliu studià l'esempii in l'ordine in quale sò datu in l'articulu.
Fixing #N / A errore in a funzione VLOOKUP in Excel
In formule cù VPR missaghju di errore #AT (#N/A) significa micca dispunibile (senza dati) - appare quandu Excel ùn pò truvà u valore chì cercate. Questu pò accade per parechje motivi.
1. U valore desideratu hè misspelled
Bona idea di verificà questu articulu prima! Typos spessu accade quandu u travagliu cù quantità assai grande di dati, custituitu di millaie di linii, o quandu u valore chì vo circate hè scrittu in una formula.
2. Errore #N/A quandu cercate una partita apprussimata cù VLOOKUP
Sè vo aduprate una formula cù una cundizione apprussimata di ricerca di match, vale à dì argumentu range_lookup (range_lookup) hè TRUE o micca specificatu, a vostra formula pò signalà un errore # N / A in dui casi:
- U valore per circà hè menu di u valore più chjucu in u array chì hè cercatu.
- A colonna di ricerca ùn hè micca ordinata in ordine crescente.
3. Errore #N / A quandu cercate una partita esatta cù VLOOKUP
Sè vo circate un match esatta, vale à dì argumentu range_lookup (range_lookup) hè FALSE è u valore esatta ùn hè statu trovu, a formula hà ancu signalà un errore # N / A. Amparate più nantu à cumu cercà i partiti esatti è apprussimati cù una funzione VPR.
4. A colonna di ricerca ùn hè micca a manca
Cum'è probabilmente sapete, una di e limitazioni più impurtanti VPR hè chì ùn pò micca affruntà à a manca, per quessa, a colonna di ricerca in a vostra tavula deve esse più a manca. In a pratica, avemu spessu scurdate di questu, chì porta à una formula chì ùn funziona micca è un errore. # N / A.
Decisione: Se ùn hè micca pussibule di cambià a struttura di dati in modu chì a colonna di ricerca hè più a manca, pudete aduprà una cumminazione di funzioni. INDEX (INDICE) è PIÙ ESPOSTA (MATCH) cum'è una alternativa più flexible per VPR.
5. I numeri sò furmatu cum'è testu
Una altra fonte di errore # N / A in formule cun VPR sò numeri in forma di testu in a tavula principale o a tavula di ricerca.
Questu succede di solitu quandu importate l'infurmazioni da basa di dati esterni, o quandu scrive un apostrofu prima di un numeru per mantene u zero principale.
I segni più evidenti di un numeru in forma di testu sò mostrati in a figura sottu:
Inoltre, i numeri ponu esse guardati in u formatu General (Generale). In questu casu, ci hè solu una funzione notevuli - i numeri sò allinati à u latu manca di a cellula, mentre chì per difettu sò allinati à u latu drittu.
Decisione: S'ellu hè un valore unicu, cliccate nantu à l'icona di errore è selezziunate Cunvertisce à u numeru (Cunvertite à u numeru) da u menù di cuntestu.
S'ellu hè a situazione cù parechji numeri, selezziunate è cliccate right-click nant'à u spaziu sceltu. In u menù di cuntestu chì appare, selezziunate Format Celle (Format Cellule) > tab Nombre (Number)> furmatu Nombre (Numericu) è pressu OK.
6. Ci hè un spaziu à u principiu o à a fine
Questu hè u mutivu menu ovvi di l'errore. # N / A in funzione VPR, Siccomu hè visualmente difficiuli di vede questi spazii extra, soprattuttu quandu u travagliu cù grandi tavule, quandu a maiò parte di e dati sò fora di u screnu.
Soluzione 1: Spazi extra in a tavola principale (induve a funzione VLOOKUP hè)
Se i spazii extra appariscenu in a tavula principale, pudete assicurà chì e formule funzionanu currettamente chjudendu l'argumentu ricerca_valore (lookup_value) in una funzione TRIM (TRIM):
=VLOOKUP(TRIM($F2),$A$2:$C$10,3,FALSE)
=ВПР(СЖПРОБЕЛЫ($F2);$A$2:$C$10;3;ЛОЖЬ)
Soluzione 2: Spazi extra in a tabella di ricerca (in a colonna di ricerca)
Se spazii extra sò in a colonna di ricerca - modi simplici # N / A in a formula cù VPR ùn pò esse evitata. Invece di VPR Pudete aduprà una formula array cù una cumminazione di funzioni INDEX (INDICE), PIÙ ESPOSTA (MATCH) è TRIM (TRIM):
=INDEX($C$2:$C$10,MATCH(TRUE,TRIM($A$2:$A$10)=TRIM($F$2),0))
=ИНДЕКС($C$2:$C$10;ПОИСКПОЗ(ИСТИНА;СЖПРОБЕЛЫ($A$2:$A$10)=СЖПРОБЕЛЫ($F$2);0))
Siccomu hè una formula array, ùn vi scurdate di pressu Ctrl + Shift + Enter invece di u solitu Rinsignateper inserisce a formula currettamente.
Errore #VALUE ! in formule cù VLOOKUP
In a maiò parte di i casi, Microsoft Excel informa un errore #VALU! (#VALUE!) Quandu u valore utilizatu in a formula ùn currisponde micca à u tipu di dati. In quantu VPR, allura ci sò generalmente dui motivi per l'errore #VALU!.
1. U valore chì vo circate hè più di 255 caratteri
Attenti : funzione VPR ùn pò micca circà i valori chì cuntenenu più di 255 caratteri. Se u valore chì cercate supera stu limitu, riceverete un missaghju d'errore. #VALU!.
Decisione: Aduprate una mansa di funziunalità INDICE + MATCH (INDICE + MATCH). Quì sottu hè una formula chì farà bè per questu compitu:
=INDEX(C2:C7,MATCH(TRUE,INDEX(B2:B7=F$2,0),0))
=ИНДЕКС(C2:C7;ПОИСКПОЗ(ИСТИНА;ИНДЕКС(B2:B7=F$2;0);0))
2. U percorsu sanu à u libru di ricerca ùn hè micca specificatu
Sè vo ritruvà dati da un altru libru di travagliu, deve specificà u percorsu sanu à quellu schedariu. Più specificamente, duvete include u nome di u libru di travagliu (cumpresa l'estensione) in parentesi quadrate [ ], seguitu da u nome di u fogliu, seguitu da un puntu d'esclamazione. Tuttu sta custruzzione deve esse chjusu in apostrofi, in casu chì u nome di u libru o di u fogliu cuntene spazii.
Eccu a struttura cumpleta di a funzione VPR per circà in un altru libru:
=VLOOKUP(lookup_value,'[workbook name]sheet name'!table_array, col_index_num,FALSE)
=ВПР(искомое_значение;'[имя_книги]имя_листа'!таблица;номер_столбца;ЛОЖЬ)
A vera formula pò esse cusì:
=VLOOKUP($A$2,'[New Prices.xls]Sheet1'!$B:$D,3,FALSE)
=ВПР($A$2;'[New Prices.xls]Sheet1'!$B:$D;3;ЛОЖЬ)
Sta formula cercà u valore di a cellula A2 in una culonna B nantu à u fogliu Fola1 in u libru di travagliu Prezzi novi è estrae u valore currispundenti da a colonna D.
Se ogni parte di a strada di a tavola hè omessa, a vostra funzione VPR ùn funzionerà micca è signalerà un errore #VALU! (ancu se u libru di travagliu cù a tabella di ricerca hè attualmente apertu).
Per più infurmazione nantu à a funzione VPRriferimentu à un altru schedariu Excel, vede a lezzione: Ricerca di un altru libru di travagliu cù VLOOKUP.
3. Argument Column_num hè menu di 1
Hè difficiuli d'imagine una situazione induve qualchissia entre in un valore menu di 1per indicà a colonna da quale estrarà u valore. Ancu s'ellu hè pussibule se u valore di questu argumentu hè calculatu da una altra funzione Excel nidificata VPR.
Allora, s'ellu succede chì l'argumentu col_index_num (column_number) menu di 1funziunava VPR signalerà ancu un errore #VALU!.
Se l'argumentu col_index_num (column_number) hè più grande ch'è u numeru di colonne in u matrice datu, VPR signalerà un errore #REF! (#SSIL!).
Errore #NAME ? in VLOOKUP
U casu più simplice hè un sbagliu #NAME? (#NAME?) - Cumpariscerà se scrive accidentalmente un nome di funzione cù un errore.
A suluzione hè ovvia - verificate a vostra ortografia!
VLOOKUP ùn funziona micca (limitazioni, caveats è decisioni)
In più di a sintassi abbastanza cumplicata, VPR hà più limitazioni cà qualsiasi altra funzione Excel. Per via di sti limitazioni, formule apparentemente simplici cù VPR spessu portanu à risultati inaspettati. Quì sottu truverete suluzioni per parechji scenarii cumuni induve VPR hè sbagliatu.
1. VLOOKUP ùn hè micca sensible à u casu
funzioni VPR ùn distingue micca trà maiuscule è accetta i caratteri minusculi è majuscule cum'è listessi. Per quessa, s'ellu ci sò parechji elementi in a tavula chì sò diffirenti solu in casu, a funzione VLOOKUP restituverà u primu elementu truvatu, indipende di casu.
Decisione: Aduprate una altra funzione Excel chì pò fà una ricerca verticale (LOOKUP, SUMPRODUCT, INDEX, è MATCH) in combinazione cù ESATTUA chì distingue u casu. Per più dettagli, pudete amparà da a lezzione - 4 modi per fà VLOOKUP case-sensitive in Excel.
2. VLOOKUP torna u primu valore trovu
Cum'è a sapete dighjà, VPR torna u valore da a colonna data currispondente à a prima partita truvata. Tuttavia, pudete avè estratti u 2nd, 3rd, 4th, o qualsiasi altra ripetizione di u valore chì vulete. Sè avete bisognu di estratti tutti i valori duplicati, avete bisognu di una cumminazione di funzioni INDEX (INDICE), MENU (Picculu) è LINE (fila).
3. Una colonna hè stata aghjunta o eliminata à a tavula
Sfurtunatamente, e formule VPR cessà di travaglià ogni volta chì una nova colonna hè aghjuntu o sguassata à a tabella di ricerca. Questu succede perchè a sintassi VPR ci vole à specificà a gamma completa di a ricerca è u numeru di colonna specificu per l'estrazione di dati. Naturalmente, sia l'intervallu datu è u numeru di colonna cambianu quandu sguassate una colonna o inserite una nova.
Decisione: È dinò e funzioni sò in furia per aiutà INDEX (INDICE) è PIÙ ESPOSTA (MATCH). In a formula INDICE + MATCH Definite separatamente e culonni di ricerca è ricuperazione, è in u risultatu, pudete sguassà o inserisce quante colonne chì vulete senza preoccupassi di avè da aghjurnà tutte e formule di ricerca in relazione.
4. I riferimenti di cellula sò ingannati quandu copianu una formula
Questa rubrica spiega l'essenza di u prublema exhaustively, nò?
Decisione: Aduprate sempre referenze di cellula assoluta (cù u simbulu $) nantu à i registri, per esempiu $ A $ 2: $ C $ 100 or $A:$C. In a barra di formula, pudete cambià rapidamente u tipu di ligame clicchendu F4.
VLOOKUP - travaglià cù e funzioni IFERROR è ISERROR
Sè vo ùn vulete scantà utilizatori cù missaghji errore # N / A, #VALU! or #NAME?, pudete mostrà una cellula viota o u vostru missaghju. Pudete fà questu mettendu VPR in una funzione IFERROR (IFERROR) in Excel 2013, 2010 è 2007 o utilizate una mansa di funzioni SI + ISERROR (IF+ISERROR) in versioni precedenti.
VLOOKUP: travaglià cù a funzione IFERROR
sintassi di funzione IFERROR (IFERROR) hè simplice è parla per sè stessu:
IFERROR(value,value_if_error)
ЕСЛИОШИБКА(значение;значение_если_ошибка)
Vale à dì, per u primu argumentu inserite u valore per esse verificatu per un errore, è per u sicondu argumentu specificate ciò chì deve vultà si trova un errore.
Per esempiu, sta formula torna una cellula viota se u valore chì cercate ùn hè micca truvatu:
=IFERROR(VLOOKUP($F$2,$B$2:$C$10,2,FALSE),"")
=ЕСЛИОШИБКА(ВПР($F$2;$B$2:$C$10;2;ЛОЖЬ);"")
Se vulete vede u vostru propiu missaghju invece di u missaghju d'errore standard di a funzione VPR, mette in virgulette, cusì:
=IFERROR(VLOOKUP($F$2,$B$2:$C$10,2,FALSE),"Ничего не найдено. Попробуйте еще раз!")
=ЕСЛИОШИБКА(ВПР($F$2;$B$2:$C$10;2;ЛОЖЬ);"Ничего не найдено. Попробуйте еще раз!")
VLOOKUP: travaglià cù a funzione ISERROR
Dapoi a funzione IFERROR apparsu in Excel 2007, quandu travaglia in versioni precedenti, vi tuccherà à aduprà a cumminazzioni IF (IF) è EOSHIBKA (ISERROR) cum'è questu:
=IF(ISERROR(VLOOKUP формула),"Ваше сообщение при ошибке",VLOOKUP формула)
=ЕСЛИ(ЕОШИБКА(ВПР формула);"Ваше сообщение при ошибке";ВПР формула)
Per esempiu, a formula IF+ISERROR+VLOOKUP, simile à a formula IFERROR+VLOOKUPmostratu sopra:
=IF(ISERROR(VLOOKUP($F$2,$B$2:$C$10,2,FALSE)),"",VLOOKUP($F$2,$B$2:$C$10,2,FALSE))
=ЕСЛИ(ЕОШИБКА(ВПР($F$2;$B$2:$C$10;2;ЛОЖЬ));"";ВПР($F$2;$B$2:$C$10;2;ЛОЖЬ))
Hè tuttu per oghje. Spergu chì stu breve tutoriale vi aiuterà à trattà cù tutti i pussibuli sbagli. VPR è fà e vostre formule funziona bè.