Con CERCA.VERO, Excel offre una funzione versatile che ricerca nella prima colonna di un intervallo di ricerca.
Excel CERCA.VERT – utilizzo mirato, lavoro pulito
La funzione CERCA.VERT in Excel è una delle più utilizzate quando si devono estrarre singoli valori da tabelle di grandi dimensioni. Affinché ciò avvenga in modo affidabile, non basta solo la formula giusta: è importante anche la struttura dei dati e un’attenta analisi delle fonti di errore.
- La matrice deve iniziare a sinistra: la funzione CERCA.ZETA cerca solo nella prima colonna dell’area selezionata. Se, ad esempio, il numero cliente si trova nella colonna D, l’area di ricerca deve iniziare lì, quindi non “A:F”, ma “D:F”. La prima colonna viene sempre utilizzata come base di confronto. Se si ignora questo, si ottengono risultati errati o nessun risultato.
- Lavorare in modo più stabile con gli oggetti tabella: non appena si formattano i dati come “tabella” (tasto di scelta rapida: Ctrl + T), Excel reagisce automaticamente alle successive estensioni. La formula CERCA.VERT rimane valida anche se si aggiungono nuove righe. La formula =CERCA.VERT(A2;Tabella1;3;FALSO) accede quindi correttamente ai dati aggiornati, il che è ideale per elenchi in espansione.
- FALSO invece di VERO: controllare in modo mirato il comportamento di ricerca: senza una corrispondenza esatta si rischiano risultati imprecisi. La quarta cifra della formula, il cosiddetto riferimento_area, dovrebbe essere quasi sempre impostata su FALSO. Solo in questo modo Excel verifica la corrispondenza esatta. VERO è adatto solo per dati ordinati, cosa che nella pratica quotidiana raramente è garantita.
- Controllare e uniformare i formati dei dati: numero in formato testo o numero in formato numerico: entrambi hanno lo stesso aspetto, ma si comportano in modo diverso. Se la funzione CERCA.VERT non restituisce alcun risultato, anche se il valore è presente nell’elenco, quasi sempre si tratta di un conflitto di formati. Con =ISTEXT(A2) o =ISNUM(A2) è possibile individuare rapidamente il problema. Strumenti come =GLÄTTEN() o =WERT() ripuliscono automaticamente.
- Utilizzare in modo mirato i riferimenti assoluti e relativi alle celle: quando si copiano le formule, anche i riferimenti alle celle vengono adattati. Nel caso di dati a matrice, ciò può avere conseguenze fatali. Utilizzate quindi riferimenti assoluti alle celle con $ (ad es. $A$2:$D$100) per trascinare le formule in modo affidabile dall’alto verso il basso o trasversalmente alla tabella, senza che l’area “scivoli”.
- Intercettare gli errori in modo elegante: Nessuno vuole vedere un “NV” nelle lettere seriali o nei dashboard. Combinare SVERWEIS con WENNFEHLER per generare un output alternativo:=WENNFEHLER(SVERWEIS(A2;Tabella1;3;FALSCH); “Non trovato”)
- Non è una questione estetica, ma funzionale, ad esempio per le esportazioni o i processi automatizzati di dati.
- Conoscere i limiti di CERCA.VERI: non appena le colonne si spostano o la struttura della tabella cambia, CERCA.VERI restituisce risultati errati. Non è possibile nemmeno la ricerca inversa, ovvero da destra a sinistra. In questi casi si consiglia di utilizzare XVERWEIS o INDEX/VERGLEICH.
XVERWEIS, INDEX & Co. – Alternative con maggiore controllo
Da Microsoft 365 ed Excel 2021 è disponibile XVERWEIS, una variante più robusta che supera molti dei punti deboli di SVERWEIS. Anche la combinazione INDICE/CONFRONTO è considerata flessibile, soprattutto quando cambiano le versioni di Excel o i requisiti.
- XVERWEIS: ricerca inversa senza deviazioni: la differenza principale è che XVERWEIS consente anche di restituire risultati dalle colonne a sinistra del termine di ricerca. Esempio: =XVERWEIS(B2;Tabella1[Nome];Tabella1[Numero cliente];“Nessun risultato”). In questo modo è possibile cercare anche in tabelle la cui struttura non è ottimale. SVERWEIS fallirebbe in questo caso.
- L’indice di colonna viene eliminato: meno soggetto a errori: con XVERWEIS si specificano due aree di celle concrete: l’area di ricerca e l’area di restituzione. Non è necessario tenere conto del conteggio come con SVERWEIS (3ª colonna, 4ª colonna, ecc.). Ciò riduce gli errori e aumenta la chiarezza.
- Controllo mirato dei messaggi di errore: il quarto argomento “se_non_trovato” in XVERWEIS sostituisce la deviazione tramite SE.ERRORE. Facile leggibilità, meno errori di battitura, accesso diretto: la funzione reagisce in modo più snello alle celle vuote o ai valori errati.
- Controllo della direzione di ricerca e del comportamento di confronto: XVERWEIS offre due parametri aggiuntivi: “Modalità di confronto” (0, -1, 1) e “Modalità di ricerca” (1 dall’alto, -1 dal basso). Ciò consente, ad esempio, di cercare in modo mirato scaglioni di prezzo o voci duplicate, cosa impossibile con CERCA.VER.
- XVERWEIS su dispositivi mobili e sul web: in Microsoft Excel Mobile, XVERWEIS funziona in linea di principio, ma le formule dinamiche complesse sono limitate. In Excel Online, sia SVERWEIS che XVERWEIS funzionano in modo stabile, purché i formati delle celle siano corretti. Sui dispositivi mobili, si consiglia di utilizzare strutture semplici, poiché alcuni controlli sono assenti.
- INDICE/CONFRONTA: versatile e indipendente dalla versione: INDICE recupera il valore, CONFRONTA cerca la posizione corrispondente. Esempio di formula: =INDICE(D2:D100;CONFRONTA(A2;B2:B100;0))
A differenza di CERCA.VERT, questa combinazione non si interrompe se si spostano le colonne. È adatta per calcoli complessi o quando CERCA.X non è disponibile (ad es. Excel 2016).
Prestazioni con grandi quantità di dati: chi lavora con migliaia di record di dati raggiunge il proprio obiettivo più rapidamente con INDICE/CONFRONTO o CERCA.X rispetto a CERCA.VERT. Entrambe elaborano grandi quantità di dati in modo più efficiente, soprattutto in dashboard dinamici o durante l’esportazione da sistemi di terze parti.
Esempio pratico: CERCA.VERT passo dopo passo
Molti utilizzano Excel quotidianamente, ma con CERCA.VERT spesso sorgono malintesi, soprattutto se non si lavora regolarmente con le formule. L’esempio seguente mostra come creare una funzione CERCA.VERT funzionante con una struttura chiara e pochi passaggi. L’obiettivo è quello di recuperare automaticamente il numero di telefono corrispondente da un elenco di clienti.
- Strutturare la tabella di partenza: creare una tabella con tre colonne: “Numero cliente”, ‘Nome’ e “Numero di telefono”. Ogni riga contiene un solo record. Importante: la colonna con il valore cercato, in questo caso il numero cliente, si trova all’estrema sinistra. La funzione CERCA.VERIEGO confronta sempre solo con la prima colonna nell’area di ricerca. Esempio: “1002 Langer 0157 9876543”
- Impostare la seconda tabella per la query: più a destra, o in un altro foglio di lavoro, creare un piccolo elenco con due colonne: “Numero cliente” e “Numero di telefono”. Nella colonna “Numero cliente” inserisci il valore da cercare, ad esempio “1002”. La seconda colonna rimane inizialmente vuota e verrà compilata automaticamente in un secondo momento.
- Impostare correttamente la funzione CERCA.VERO: cliccare nella cella in cui deve apparire il risultato, ad esempio la cella F2. Inserire ora la seguente formula:=CERCA.VERO(E2;A2:C100;3;FALSO)
- Questa formula significa: cerca il valore dalla cella E2 (in questo caso “1002”) nella prima colonna dell’intervallo da A2 a C100. Restituisci quindi il valore dalla terza colonna (C) della stessa riga. Il “FALSO” alla fine assicura che vengano accettate solo corrispondenze esatte.
- Prova la formula e verifica il risultato: non appena premi Invio, Excel visualizza automaticamente “0157 9876543” nella cella F2, ovvero il numero di telefono del cliente cercato. Se modifichi il valore in E2 (ad esempio in “1003”), anche il risultato in F2 cambia automaticamente. La formula si adatta dinamicamente al valore inserito.
- Evitare le fonti di errore tipiche: verificare che i numeri nell’area di ricerca siano effettivamente formattati come numeri. Spesso la causa di un errore “NV” è che il valore in E2 è formattato come numero, ma nella tabella di origine è formattato come testo. Questo è difficilmente visibile, ma ha conseguenze tecniche. È possibile verificarlo con “=ISTTEXT(A2)”. Se necessario, “=WERT()” aiuta a correggere il contenuto della cella.
- Impostare i riferimenti delle celle quando si utilizzano più righe: Trascinare la formula verso il basso per interrogare altri clienti, fissare l’area con il simbolo del dollaro: =SVERWEIS(E2;$A$2:$C$100;3;FALSCH)
In questo modo l’area di ricerca rimane invariata anche se si copia o si trascina la formula verso il basso.
Intercettare i messaggi di errore, migliorare l’output
Per visualizzare un messaggio comprensibile come “Cliente non trovato” invece di “NV”, completare la formula come segue: =WENNFEHLER(SVERWEIS(E2;$A$2:$C$100;3;FALSCH); “Cliente non trovato”)
In questo modo il foglio di calcolo appare più ordinato e l’utente riconosce immediatamente quando non è disponibile alcun risultato.
