#Excel – 6 motivi per cui il vostro CERCA.VERT non funziona

La funzione CERCA.VERT() è la più popolare funzione di ricerca e riferimento di Excel (eccone la scheda tecnica). Ma può anche essere piuttosto complicata e il temuto messaggio di errore #N/D può fare capolino quando meno ce lo si aspetta.

In questo articolo esamineremo le 6 ragioni più comuni per cui il vostro CERCA.VERT() potrebbe non funzionare come desiderate.

Cercare una corrispondenza esatta

L’ultimo argomento della funzione CERCA.VERT(), conosciuto come [intervallo], definisce se si desidera una corrispondenza approssimativa oppure esatta.

Nella maggior parte dei casi le persone sono alla ricerca di un particolare prodotto, ordine, dipendente o cliente e richiedono quindi una corrispondenza esatta. Quando siete alla ricerca di un valore univoco, come valore per l’argomento [intervallo], dovete inserire FALSO.

Questo argomento è facoltativo, ma se lasciato vuoto, viene utilizzato il valore VERO. Il valore VERO per lavorare necessita che i dati siano ordinati in ordine crescente.

L’immagine sotto mostra un CERCA.VERT con l’argomento [intervallo] omesso, …e la restituzione di un valore errato.

Cerca vert restituisce un valore errato perchè l'argomento intervallo è stato omesso

Soluzione

Se cercate un valore univoco, inserite FALSO come ultimo argomento. Il CERCA.VERT qua sopra dovrebbe essere inserito come =CERCA.VERT(G2;A2:E10;2;FALSO).

Bloccare i riferimenti alla matrice_tabella

Forse state cercando di utilizzare più CERCA.VERT per restituire informazioni diverse su uno stesso record. Se avete intenzione di copiare il CERCA.VERT in più celle, è necessario bloccare i riferimenti alla matrice_tabella.

LEGGI:  #Excel - Come usare la formattazione condizionale di Excel - Parte 1

L’immagine qui sotto mostra un CERCA.VERT inserito in modo errato. L’intervallo di celle sbagliato è quello inserito per l’argomento matrice_tabella. La cella in H4 restituirebbe un errore di #N/D.

Cerca vert inserito in modo erroneo

Soluzione

La tabella che la funzione CERCA.VERT() utilizza per cercare e restituire le informazioni è conosciuta come matrice_tabella. Per poter copiare il CERCA.VERT in altre celle, i riferimenti a questa tabella devono essere inseriti come assoluti.

Cliccate sui riferimenti all’interno della formula e premete il tasto F4 per cambiare il riferimento da relativo ad assoluto. La formula dell’esempio dovrebbe essere inserita come =CERCA.VERT(G2;$A$2:$E$10;2;FALSO).

È stata inserita una colonna

Il numero della colonna, o indice, viene utilizzato dalla funzione CERCA.VERT per definire quale informazione restituire in merito ad un record.

Poiché questo argomento è inserito sotto forma di un numero, non è molto “persistente”. L’inserimento di una nuova colonna nella tabella, potrebbe causare malfunzionamenti nel nostro CERCA.VERT. L’immagine qui sotto mostra un tale scenario.

Cerca vert potrebbe smettere di funzionare all'inserimento di una nuova colonna nella tabella

La Frutta era in colonna 2, ma dopo l’inserimento di una nuova colonna è passata in colonna 3. Tuttavia, il CERCA.VERT non si è aggiornato automaticamente.

Soluzione 1

Una soluzione potrebbe essere quella di proteggere il foglio di lavoro in modo che gli utenti non possano inserire colonne. Se gli utenti devono invece essere in grado di poterlo fare, chiaramente non è una soluzione praticabile.

Soluzione 2

Un’altra opzione potrebbe essere quella di inserire la funzione CONFRONTA() nell’argomento indice del CERCA.VERT.

La funzione CONFRONTA() può essere usata per cercare e restituire il numero di colonna desiderata. Questo rende l’argomento indice dinamico, in modo che gli inserimenti di nuove colonne non influiscano più sul CERCA.VERT.

LEGGI:  #Excel - Come calcolare agevolmente la differenza tra due date

La formula di seguito potrebbe essere inserita in questo esempio, per evitare che il problema mostrato sopra si verifichi.

=CERCA.VERT(H2;$A$2:$F$10;CONFRONTA(I1;A1:F1;0);FALSO)

La tabella è diventata più grande

Mano a mano che le righe vengono aggiunte alla tabella, potrebbe essere necessario aggiornare il CERCA.VERT per garantire che queste righe aggiunte siano incluse. L’immagine qua sotto mostra un CERCA.VERT che non controlla l’intera tabella per l’elemento “Frutta” e restituisce un errore di #N/D.

Cerca vert non controlla le nuove righe

Soluzione

Valutate la trasformazione dell’intervallo come una tabella vera e propria (da Excel 2007 o superiori), o come un nome di intervallo dinamico. Queste tecniche faranno in modo che la funzione CERCA.VERT controlli sempre l’intera tabella.

Per formattare l’intervallo come una tabella, selezionate l’intervallo di celle che desiderate utilizzare come matrice_tabella e fate clic su HOME > “Formatta come tabella” selezionando uno stile dalla galleria. Fate clic sulla scheda PROGETTAZIONE sotto STRUMENTI TABELLA e assegnate un nome alla tabella utilizzando la casella presente nell’estrema sinistra.

Il CERCA.VERT qua sotto mostra l’utilizzo di una tabella denominata Elenco_frutta.

Usate gli intervalli denominati nei vostri cerca vert

CERCA.VERT non può cercare alla sua sinistra

Una limitazione della funzione CERCA.VERT è che non può cercare alla sua sinistra. Cercherà lungo la colonna più a sinistra di una tabella e restituirà le informazioni andando a destra.

Soluzione

La soluzione a questo problema non comporta l’utilizzo del CERCA.VERT. L’utilizzo di una combinazione delle funzioni INDICE() e CONFRONTA() di Excel è una comune alternativa a CERCA.VERT. Ed è anche molto più versatile.

L’esempio seguente mostra questa tecnica utilizzata per restituire informazioni presenti a sinistra della colonna contenente il valore ricercato.

Usate INDICE e CONFRONTA al posto di CERCA VERT

La formula utilizzata nell’esempio è la seguente:

=INDICE(A2:A12;CONFRONTA(G2;B2:B12;0))

La vostra tabella contiene duplicati

La funzione CERCA.VERT può restituire un solo record. Restituirà il primo record che corrisponde al valore cercato.

LEGGI:  #Excel - Perché SUBTOTALE() è una funzione speciale

Se la tabella contiene valori duplicati allora difficilmente CERCA.VERT potrà essere all’altezza del compito.

Soluzione 1

E’ corretto che la vostra tabella contenga duplicati? Se la risposta fosse no, considerate la loro rimozione. Un modo rapido per ottenere questo risultato è indicato nel seguente articolo: #Excel – Come rimuovere duplicati in Excel.

Soluzione 2

Ok, così è corretto che la vostra tabella contenga duplicati. In questo caso un CERCA.VERT non è quello che vi serve. Invece una tabella pivot sarebbe perfetta per selezionare un valore ed elencare i risultati.

La tabella che segue è un elenco di ordini. Diciamo che volete vengano restituiti tutti gli ordini per un particolare tipo di frutta.

Tabella con valori duplicati

Nell’immagine seguente vedete come è stata utilizzata una tabella pivot per consentire ad un utente di selezionare un tipo di frutta dal filtro del rapporto pivot, facendo così apparire l’elenco di tutti gli ordini relativi.

Usate una tabella pivot per restituire un elenco di record

Per approfondire ulteriormente i possibili scenari di utilizzo di questa potente funzione di Excel, è disponibile in questa pagina la sua scheda tecnica completa.

Copyright © 2014-2017  Office Academy. Tutti i diritti riservati.
Vai alla barra degli strumenti