La funzione CERCA.VERT non funziona: risoluzione dei problemi N/D, NOME e VALORE

Questa lezione spiega come affrontare rapidamente una situazione in cui una funzione VPR (VLOOKUP) non vuole funzionare in Excel 2013, 2010, 2007 e 2003 e come identificare e correggere errori comuni e superare i limiti VPR.

In diversi articoli precedenti, abbiamo esplorato le varie sfaccettature della funzione VPR in Excel. Se li hai letti attentamente, ora dovresti essere un esperto in questo campo. Tuttavia, non è senza motivo che molti esperti di Excel credono VPR una delle caratteristiche più complesse. Ha un sacco di limitazioni e funzionalità che diventano la fonte di molti problemi ed errori.

In questo articolo troverai semplici spiegazioni degli errori #at (#N / A), # NOME? (#NOME?) e #VALORE! (#VALORE!) che appaiono quando si lavora con la funzione VPR, nonché le tecniche e i metodi per affrontarli. Inizieremo con i casi più comuni e le ragioni più ovvie per cui. VPR non funziona, quindi è meglio studiare gli esempi nell'ordine in cui sono riportati nell'articolo.

• Correzione del bug #N/D
• Correggere il #VALORE! nelle formule con VLOOKUP
• Errore #NOME? in CERCA.VERT
• VLOOKUP non funziona (problemi, limitazioni e soluzioni)
• VLOOKUP – lavorare con le funzioni IFERROR e ISERROR

Correzione dell'errore #N/D nella funzione CERCA.VERT in Excel

Nelle formule con VPR messaggio di errore #at (#N/D) significa non disponibile (nessun dato) – appare quando Excel non riesce a trovare il valore che stai cercando. Questo può accadere per diversi motivi.

1. Il valore desiderato è scritto in modo errato

Buona idea controllare prima questo articolo! Gli errori di battitura si verificano spesso quando si lavora con quantità di dati molto grandi, costituite da migliaia di righe, o quando il valore che si sta cercando viene scritto in una formula.

2. Errore #N/D durante la ricerca di una corrispondenza approssimativa con VLOOKUP

Se si utilizza una formula con una condizione di ricerca di corrispondenza approssimativa, ad es. argomento ricerca_intervallo (range_lookup) è VERO o non specificato, la tua formula potrebbe segnalare un errore #N / A in due casi:

• Il valore da cercare è inferiore al valore più piccolo nell'array da cercare.
• La colonna di ricerca non è ordinata in ordine crescente.

3. Errore #N/D durante la ricerca di una corrispondenza esatta con VLOOKUP

Se stai cercando una corrispondenza esatta, ad esempio un argomento ricerca_intervallo (range_lookup) è FALSE e il valore esatto non è stato trovato, la formula segnalerà anche un errore #N / A. Ulteriori informazioni su come cercare corrispondenze esatte e approssimative con una funzione VPR.

4. La colonna di ricerca non è all'estrema sinistra

Come probabilmente saprai, uno dei limiti più significativi VPR è che non può essere rivolto a sinistra, quindi la colonna di ricerca nella tabella deve essere all'estrema sinistra. In pratica, spesso ce ne dimentichiamo, il che porta a una formula non funzionante e a un errore. #N / A.

Decisione: Se non è possibile modificare la struttura dei dati in modo che la colonna di ricerca sia più a sinistra, è possibile utilizzare una combinazione di funzioni INDICE (INDICE) e PIÙ ESPOSTO (MATCH) come alternativa più flessibile per VPR.

5. I numeri sono formattati come testo

Un'altra fonte di errore #N / A nelle formule con VPR sono numeri in formato testo nella tabella principale o nella tabella di ricerca.

Questo di solito accade quando si importano informazioni da database esterni o quando si digita un apostrofo prima di un numero per mantenere lo zero iniziale.

I segni più evidenti di un numero in formato testo sono mostrati nella figura seguente:

Inoltre, i numeri possono essere memorizzati nel formato Generale (Generale). In questo caso, c'è solo una caratteristica evidente: i numeri sono allineati al bordo sinistro della cella, mentre per impostazione predefinita sono allineati al bordo destro.

Decisione: Se si tratta di un valore singolo, fai semplicemente clic sull'icona di errore e seleziona Converti in numero (Converti in numero) dal menu contestuale.

Se questa è la situazione con molti numeri, selezionali e fai clic con il pulsante destro del mouse sull'area selezionata. Nel menu contestuale che appare, seleziona formato celle (Formatta celle) > scheda Numero (Numero) > formato Numero (numerico) e premere OK.

6. C'è uno spazio all'inizio o alla fine

Questa è la ragione meno ovvia dell'errore. #N / A in funzione VPR, poiché è visivamente difficile vedere questi spazi extra, soprattutto quando si lavora con tabelle di grandi dimensioni, quando la maggior parte dei dati è fuori schermo.

Soluzione 1: spazi extra nella tabella principale (dove si trova la funzione CERCA.VERT)

Se nella tabella principale vengono visualizzati spazi aggiuntivi, puoi assicurarti che le formule funzionino correttamente racchiudendo l'argomento valore di ricerca (valore_ricerca) in una funzione TRIM (ORDINARE):

=VLOOKUP(TRIM($F2),$A$2:$C$10,3,FALSE)

=ВПР(СЖПРОБЕЛЫ($F2);$A$2:$C$10;3;ЛОЖЬ)

Soluzione 2: spazi extra nella tabella di ricerca (nella colonna di ricerca)

Se ci sono spazi extra nella colonna di ricerca, modi semplici #N / A nella formula con VPR non può essere evitato. Invece di VPR È possibile utilizzare una formula di matrice con una combinazione di funzioni INDICE (INDICE), PIÙ ESPOSTO (PARTITA) è TRIM (ORDINARE):

=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))

Poiché si tratta di una formula di matrice, non dimenticare di premere CTRL + MAIUSC + INVIO invece del solito entrareper inserire correttamente la formula.

Errore #VALORE! nelle formule con VLOOKUP

Nella maggior parte dei casi, Microsoft Excel segnala un errore #VALORE! (#VALORE!) quando il valore utilizzato nella formula non corrisponde al tipo di dati. Riguardo VPR, di solito ci sono due ragioni per l'errore #VALORE!.

1. Il valore che stai cercando è più lungo di 255 caratteri

Attenzione: funzione VPR non può cercare valori contenenti più di 255 caratteri. Se il valore che stai cercando supera questo limite, riceverai un messaggio di errore. #VALORE!.

Decisione: Usa un sacco di funzioni INDICE+PARTENZA (INDICE + PARTITA). Di seguito è una formula che andrà bene per questo compito:

=INDEX(C2:C7,MATCH(TRUE,INDEX(B2:B7=F$2,0),0))

=ИНДЕКС(C2:C7;ПОИСКПОЗ(ИСТИНА;ИНДЕКС(B2:B7=F$2;0);0))

2. Il percorso completo della cartella di lavoro di ricerca non è specificato

Se stai recuperando dati da un'altra cartella di lavoro, devi specificare il percorso completo di quel file. In particolare, è necessario includere il nome della cartella di lavoro (compresa l'estensione) tra parentesi quadre [ ], seguito dal nome del foglio, seguito da un punto esclamativo. Tutta questa costruzione deve essere racchiusa tra apostrofi, nel caso in cui il nome del libro o del foglio contenga spazi.

Ecco la struttura completa della funzione VPR per cercare in un altro libro:

=VLOOKUP(lookup_value,'[workbook name]sheet name'!table_array, col_index_num,FALSE)

=ВПР(искомое_значение;'[имя_книги]имя_листа'!таблица;номер_столбца;ЛОЖЬ)

La vera formula potrebbe assomigliare a questa:

=VLOOKUP($A$2,'[New Prices.xls]Sheet1'!$B:$D,3,FALSE)

=ВПР($A$2;'[New Prices.xls]Sheet1'!$B:$D;3;ЛОЖЬ)

Questa formula cercherà il valore della cella A2 in una colonna B sul foglio Sheet1 nella cartella di lavoro Nuovi prezzi ed estrarre il valore corrispondente dalla colonna D.

Se una parte qualsiasi del percorso della tabella viene omessa, la tua funzione VPR non funzionerà e segnalerà un errore #VALORE! (anche se la cartella di lavoro con la tabella di ricerca è attualmente aperta).

Per ulteriori informazioni sulla funzione VPRfacendo riferimento a un altro file Excel, vedere la lezione: Ricerca in un'altra cartella di lavoro utilizzando VLOOKUP.

3. Argomento Column_num è minore di 1

È difficile immaginare una situazione in cui qualcuno inserisce un valore inferiore a 1per indicare la colonna da cui estrarre il valore. Sebbene sia possibile se il valore di questo argomento viene calcolato da un'altra funzione di Excel nidificata all'interno VPR.

Quindi, se succede che l'argomento col_index_num (colonna_numero) minore di 1function VPR segnalerà anche un errore #VALORE!.

Se l'argomento col_index_num (column_number) è maggiore del numero di colonne nella matrice data, VPR segnalerà un errore #RIF! (#SSIL!).

Errore #NOME? in CERCA.VERT

Il caso più semplice è un errore # NOME? (#NAME?) – apparirà se scrivi accidentalmente il nome di una funzione con un errore.

La soluzione è ovvia: controlla l'ortografia!

VLOOKUP non funziona (limitazioni, avvertenze e decisioni)

Oltre alla sintassi piuttosto complicata, VPR ha più limitazioni rispetto a qualsiasi altra funzione di Excel. A causa di queste limitazioni, formule apparentemente semplici con VPR spesso portano a risultati inaspettati. Di seguito troverai soluzioni per diversi scenari comuni in cui VPR è sbagliato.

1. VLOOKUP non fa distinzione tra maiuscole e minuscole

Funzione VPR non distingue tra maiuscole e minuscole e accetta caratteri minuscoli e maiuscoli allo stesso modo. Pertanto, se nella tabella sono presenti più elementi che differiscono solo nel caso, la funzione CERCA.VERT restituirà il primo elemento trovato, indipendentemente dal caso.

Decisione: Utilizzare un'altra funzione di Excel in grado di eseguire una ricerca verticale (CERCA, SOMMAPRODOTTO, INDICE e CONFRONTA) in combinazione con ESATTOA che distingue caso. Per maggiori dettagli, puoi imparare dalla lezione: 4 modi per fare distinzione tra maiuscole e minuscole VLOOKUP in Excel.

2. CERCA.VERT restituisce il primo valore trovato

Come già sai, VPR restituisce il valore dalla colonna data corrispondente alla prima corrispondenza trovata. Tuttavia, puoi fare in modo che estragga la 2a, 3a, 4a o qualsiasi altra ripetizione del valore che desideri. Se devi estrarre tutti i valori duplicati, avrai bisogno di una combinazione di funzioni INDICE (INDICE), MENO (PICCOLO) e LINE (RIGA).

3. Una colonna è stata aggiunta o rimossa alla tabella

Purtroppo le formule VPR smette di funzionare ogni volta che viene aggiunta o rimossa una nuova colonna alla tabella di ricerca. Questo accade perché la sintassi VPR richiede di specificare l'intero intervallo della ricerca e il numero di colonna specifico per l'estrazione dei dati. Naturalmente, sia l'intervallo specificato che il numero di colonna cambiano quando elimini una colonna o ne inserisci una nuova.

Decisione: E ancora le funzioni hanno fretta di aiutare INDICE (INDICE) e PIÙ ESPOSTO (INCONTRO). Nella formula INDICE+PARTENZA Definisci separatamente le colonne di ricerca e recupero e, di conseguenza, puoi eliminare o inserire tutte le colonne che desideri senza preoccuparti di dover aggiornare tutte le formule di ricerca correlate.

4. I riferimenti di cella vengono confusi durante la copia di una formula

Questa voce spiega l'essenza del problema in modo esauriente, giusto?

Decisione: Utilizzare sempre riferimenti di cella assoluti (con il simbolo $) su registra l'intervallo, ad esempio $ A $ 2: $ C $ 100 or $ A: $ C. Nella barra della formula, puoi cambiare rapidamente il tipo di collegamento facendo clic F4.

VLOOKUP – lavorare con le funzioni IFERROR e ISERROR

Se non vuoi spaventare gli utenti con messaggi di errore #N / A, #VALORE! or # NOME?, puoi mostrare una cella vuota o il tuo messaggio. Puoi farlo posizionando VPR in una funzione IFERROR (IFERROR) in Excel 2013, 2010 e 2007 o utilizzare una serie di funzioni SE+ERRORE (IF+ISERROR) nelle versioni precedenti.

VLOOKUP: lavorare con la funzione SE ERRORE

Sintassi della funzione IFERROR (IFERROR) è semplice e parla da sé:

IFERROR(value,value_if_error)

ЕСЛИОШИБКА(значение;значение_если_ошибка)

Cioè, per il primo argomento si inserisce il valore da verificare per un errore, e per il secondo argomento si specifica cosa restituire se viene trovato un errore.

Ad esempio, questa formula restituisce una cella vuota se non viene trovato il valore che stai cercando:

=IFERROR(VLOOKUP($F$2,$B$2:$C$10,2,FALSE),"")

=ЕСЛИОШИБКА(ВПР($F$2;$B$2:$C$10;2;ЛОЖЬ);"")

Se si desidera visualizzare il proprio messaggio invece del messaggio di errore standard della funzione VPR, mettilo tra virgolette, in questo modo:

=IFERROR(VLOOKUP($F$2,$B$2:$C$10,2,FALSE),"Ничего не найдено. Попробуйте еще раз!")

=ЕСЛИОШИБКА(ВПР($F$2;$B$2:$C$10;2;ЛОЖЬ);"Ничего не найдено. Попробуйте еще раз!")

VLOOKUP: lavorare con la funzione ISERROR

Dal momento che la funzione IFERROR apparso in Excel 2007, quando si lavora con versioni precedenti sarà necessario utilizzare la combinazione IF (SE) e EOSHIBKA (ISERROR) in questo modo:

=IF(ISERROR(VLOOKUP формула),"Ваше сообщение при ошибке",VLOOKUP формула)

=ЕСЛИ(ЕОШИБКА(ВПР формула);"Ваше сообщение при ошибке";ВПР формула)

Ad esempio, la formula SE+ERRORE+VLOOKUP, simile alla formula SE ERRORE + CERCA.VERTmostrato 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;ЛОЖЬ))

È tutto per oggi. Spero che questo breve tutorial ti aiuti ad affrontare tutti i possibili errori. VPR e fai in modo che le tue formule funzionino correttamente.