Excel: la Funzione CERCA.X

CERCA.X: guida all'utilizzo della nuova Funzione di ricerca e alle sue applicazioni

Fino al 2020, la scelta di quale Funzione di ricerca di Excel applicare dipendeva soprattutto dal contesto specifico.
In scenari semplici, si poteva optare per il classico CERCA.VERT o, per il CERCA.ORIZZ. In situazioni più complesse, ci si affidava all'abbinamento tra INDICE e CONFRONTA.
Il ricorrere a Funzioni così diverse fra di loro è stato superato con l'introduzione della nuova Funzione CERCA.X
CERCA.X rappresenta un'evoluzione significativa rispetto alle precedenti Funzioni.
CERCA.X è in grado di eseguire ricerche sia in direzione verticale che orizzontale, permettendo di cercare valori a sinistra o al di sopra dell'intervallo di riferimento. Inoltre, consente la ricerca con criteri multipli e può restituire non solo singoli valori, ma anche intere colonne o righe di dati.
CERCA.X è una Funzione di ricerca potente, capace di superare molte delle limitazioni presenti nel Cerca verticale.

La Funzione CERCA.X

[01] - Breve confronto introduttivo fra la nuova Funzione CERCA.X e CERCA.VERT
[02] - Sintassi della Funzione CERCA.X
[03] - Esempio base di utilizzo della Funzione CERCA.X e concatenazione dei risultati
[04] - Ricerca orizzontale dei dati 
[05] - Gestire il codice di errore #N/D con la Funzione CERCA.X
[06] - Impostare una ricerca dati per approssimazione
[07] - Ricerca con corrispondenza parziale dei dati (applicare caratteri jolly)
[08] - CERCA.X per restituire più colonne o righe (adiacenti o meno)
[09] - Inserire più criteri di ricerca nella Funzione CERCA.X
[10] - Considerazioni conclusive sulla Funzione CERCA.X

Breve confronto introduttivo fra la nuova Funzione CERCA.X e CERCA.VERT

CERCA.X e CERCA.VERT sono entrambe Funzioni di Excel utilizzate per cercare dati, ma differiscono per potenza e flessibilità.
La Funzione CERCA.X è stata rilasciata da Microsoft nel 2019. Inizialmente disponibile solo per gli utenti di Office 365 (ora Microsoft 365) che facevano parte del programma Insider, è stata poi distribuita gradualmente a tutti gli utenti di Excel nel corso del 2020, come parte di un aggiornamento generale della suite.
Questa Funzione è stata introdotta per migliorare le capacità di ricerca dei dati, andando a sostituire e ampliare le funzionalità offerte da CERCA.VERT e CERCA.ORIZZ.
CERCA.VERT è una storica Funzione di Excel che consente di cercare un valore in una colonna di sinistra di un intervallo e di restituire un valore dalla stessa riga, di una specificata colonna. Tuttavia, CERCA.VERT ha limitazioni importanti: la ricerca è solo verticale e la colonna da cui restituire i risultati deve trovarsi a destra di quella in cui avviene la ricerca. Inoltre, la Funzione può restituire il primo valore trovato, senza offrire molta flessibilità nella gestione degli errori.
CERCA.X, invece, è molto più versatile. Può cercare valori sia orizzontalmente che verticalmente, rendendola utile per una gamma più ampia di scenari. A differenza di CERCA.VERT, non è vincolata alla posizione della colonna di ricerca rispetto a quella di ritorno.
CERCA.X permette inoltre una gestione più efficiente dei casi di errore, fornendo opzioni più sofisticate per la ricerca e il confronto dei dati. In sostanza, CERCA.X rappresenta un'evoluzione di CERCA.VERT, superandone le principali limitazioni e offrendo maggiore flessibilità.

Sintassi della Funzione CERCA.X

La Funzione CERCA.X (XLOOKUP nella versione linguistica originale) prevede 6 argomenti, dei quali solo i primi tre sono obbligatori.

=CERCA.X(Valore;matrice_ricerca;matrice_restituita;[se_non_trovato];[modalità_confronto];[modalità_ricerca])

Nella quale.
Valore: è il valore che deve essere ricercato nella matrice_ricerca (La Funzione, in questo primo argomento può accettare anche valori 0, 1, FALSO, VERO per un successivo confronto logico nel secondo argomento)
matrice_ricerca: è l'intervallo o la matrice nella quale ricercare il valore.
matrice_restituita è l'intervallo o la matrice nella quale è presente il valore che si vuole venga restituito.
[se_non_trovato] in questo primo argomento facoltativo è possibile definire un codice di errore quale ad esempio "codice non trovato" senza dover ricorre alla Funzioni SE.ERRORE o SE.NON.DISP. Se l'argomento viene omesso, in caso di errore verrà restituito il codice N/D
[modalità di confronto] in questo secondo argomento facoltativo è possibile programmare i seguenti codici:
 0 = corrispondenza esatta;
-1 corrispondenza esatta o elemento successivo più piccolo; Se non viene trovata una corrispondenza esatta, viene restituito il valore immediatamente inferiore.
 1 = corrispondenza esatta o numero successivo più grande; Se non viene trovata una corrispondenza esatta, viene restituito il valore successivo più grande.
 2 = corrispondenza carattere jolly; si riferisce alla possibilità di utilizzare caratteri speciali per eseguire ricerche più flessibili. Nello specifico, Excel supporta due principali caratteri jolly per le ricerche con CERCA.X l'asterisco e il punto di domanda.
[modalità_ricerca] in questo terzo e ultimo argomento facoltativo è possibile definire attraverso codici le seguenti direzioni di ricerca:
 1 = ricerca dal primo all'ultimo dei valori presenti nell'elenco;
-1 ricerca dall'ultimo al primo dei valori presenti nell'elenco;
 2 Ricerca binaria in ordine crescente;
-2 ricerca binaria in ordine decrescente;

Esempio base di utilizzo della Funzione CERCA.X e concatenazione dei risultati

In questo primo esempio, l'attenzione è posta su un utilizzo di base della Funzione CERCA.X, limitato ai suoi primi tre argomenti, che sono obbligatori. L'approccio proposto si concentra sull'impostazione essenziale della Funzione, senza considerare opzioni avanzate
Diversamente dalla Funzione CERCA.VERT, che richiede che il valore da cercare sia sempre nella prima colonna di una matrice, la Funzione CERCA.X permette di eseguire la ricerca in qualsiasi colonna, inclusa la seconda o quelle successive. Inoltre, consente di restituire valori anche dalle colonne a sinistra di quella in cui si effettua la ricerca, offrendo una versatilità maggiore nel recupero dei dati.

Passiamo pertanto ad un primo semplice esempio di applicazione della Funzione.
Nella tabella illustrata nell'immagine, si desidera cercare il valore presente nella cella G3 e restituire il dato corrispondente dalla prima colonna della matrice, che identifica la categoria del prodotto.
La Funzione inserita sarà la seguente
=CERCA.X(G3;C3:C15;B3;B15)
nella quale il primo argomento "G3" è la cella che contiene il codice del prodotto da cercare, nel caso specifico dell'esempio il codice g4;
C3:C15 è la matrice nella quale cercare il codice;
ed infine, B3:B15 contiene i nomi delle categorie dei prodotti che si desiderano ottenere come risultato.

 

Nella tabella di cui sopra, qualora volessimo la restituzione del valore presente nella terza colonna (ovvero il modello) la Funzione andrebbe modificata in: =CERCA.X(G3;C3:C15;D3:D15)

Ovviamente le due Funzioni CERCA.X potrebbero anche essere concatenate per avere in una unica cella la restituzione sia della categoria del prodotto sia del modello. (vedi immagine sottostante)
=CERCA.X(G3;C3:C15;B3:B15)&" "&CERCA.X(G3;C3:C15;D3:D15)

 

::NOTA. Per un approfondimento sulle concatenazioni si rimanda a le concatenazioni di testo in Excel

Ricerca orizzontale dei dati

Come anticipato nell'introduzione, la Funzione CERCA.X di Excel si propone come un'alternativa versatile e potente a molte delle precedenti Funzioni di ricerca.
Tra i suoi punti di forza vi è la capacità di eseguire ricerche sia orizzontali che verticali, una caratteristica che in passato era gestita separatamente da Funzioni specifiche come CERCA.VERT e CERCA.ORIZ
Grazie alla sua flessibilità, CERCA.X  può fare entrambe le cose con la stessa impostazione di sintassi semplificando pertanto le operazioni di ricerca.

Nello specifico dell'esempio proposto nella sottostante immagine.
Nella cella K2 è stato inserito il codice da ricercare nella prima riga della tabella. Il terzo argomento della Funzione specifica la matrice A22 , da cui si vuole ottenere il risultato corrispondente alla ricerca.
La Funzione, inserita nella cella L2 è la seguente:
=CERCA.X(K2;A1:H1;A2:H2)

Per ottenere in un'unica formula il risultato presente su due righe diverse e volendo disporre la restituzione dei dati su due celle affiancate, è possibile applicare la Funzione MATR.TRASPOSTA
Per ottenere questo risultato, nella sottostante immagine, viene proposta la seguente impostazione della formula:
=MATR.TRASPOSTA(CERCA.X(K2;A1:H1;A2:H3))
Grazie a questa impostazione, il risultato anzichè essere restituito nelle due celle L2 e L3, viene affiancato sulla stessa riga.

Gestire il codice di errore #N/D con la Funzione CERCA.X

All'interno della sintassi stessa della Funzione CERCA.X, senza ricorrere alla Funzione SE.ERRORE oppure SE.NON.DISP., è possibile definire un codice di errore alternativo a #N/D
:: NOTA. Qualora non venisse definito il quarto argomento della Funzione [se_non_trovato], Il codice di errore #N/D viene restituito anche nel caso della Funzione CERCA.X

Nell'esempio illustrato nel successivo screenshot, viene cercato un codice (sn3) non presente nella matrice C3:C15. Come quarto argomento fra doppi apici è stato inserito il testo "codice inesistente"
=CERCA.X(G3;C3:C15;B3:B15;"codice inesistente")
Il risultato restituito dalla Funzione, è visibile nell'immagine

 

Impostare una ricerca dati per approssimazione

Seppur meno frequenti delle ricerche per corrispondenza esatta, a volte può rendersi necessaria impostare un ricerca per approssimazione.

:: ATTENZIONE. Contrariamente al cerca verticale, con la nuova Funzione CERCA.X, qualora non venisse impostato il 5 argomento la ricerca che viene effettuata è quella per corrispondenza esatta e non approssimativa.

Esempio. Nell'immagine sottostante, non essendo presente il codice sn3 nella tabella si vuole la restituzione del valore vicino più piccolo (sn2). per impostare questa ricerca nel 5 argomento della Funzione CERCA.X deve essere impostato il codice -1

=CERCA.X(G3;C3:C15;B3:B15;;-1)

 

:: NOTA. Quando si scrive una Funzione con argomenti facoltativi che non devono essere definiti, ma si desidera passare direttamente a un argomento successivo, è possibile lasciare vuoti quelli non necessari.
Tuttavia, è importante mantenere la corretta sintassi inserendo comunque i separatori tra gli argomenti, come il punto e virgola.
Questo garantisce che ciascun argomento occupi la posizione corretta, anche se alcuni rimangono non valorizzati.
Nello specifico dell'esempio proposto nell'immagine di cui sopra, per bypassare l'argomento [se_non_trovato] e passare al successivo [modalità di confronto] è stato inserito per due volte il separatore punto e virgola-

Ricerca con corrispondenza parziale dei dati (caratteri jolly)

La Funzione CERCA.X offre la possibilità di eseguire ricerche con corrispondenza parziale, utilizzando i caratteri jolly come "?" per un singolo carattere e "*" per una stringa di caratteri.
Questa caratteristica permette di cercare valori che contengono o corrispondono parzialmente a una stringa specifica, rendendo più flessibili le ricerche all'interno di dataset complessi.
È particolarmente utile quando non si conosce l'intero valore esatto da cercare o quando si lavora con dati che presentano variazioni minime nelle stringhe.

Nell'esempio raffigurato nella sottostante immagine, si vuole cerca nella colonna A dell'archivio una stringa che rappresenta solo una parte delle informazioni contenute nella colonna. 
La Funzione inserita nella cella E2 è la seguente.  =CERCA.X("*"&D2;A2:A14;B2:B14;;2)

::NOTA. In Excel, i caratteri jolly "*" e "?" sono strumenti potenti per eseguire ricerche flessibili e personalizzate:
Asterisco ("*"): Sostituisce qualsiasi serie di caratteri, indipendentemente dalla loro lunghezza. Ad esempio, cercare "pro*" troverà "progetto", "prova" o "programma". È utile quando si conosce solo una parte iniziale o finale del testo.
Punto interrogativo ("?"): Sostituisce un singolo carattere. Ad esempio, cercando "Scarpon?" si potranno trovare "Scarpone" o "Scarponi". Questo carattere è molto utile per ricerche all'interno di codici per definire l'esatta posizione di confronto di una stringa.
Questi caratteri jolly sono impiegati anche in altre Funzioni come CERCA.VERT o FILTRA, rendendo la ricerca di dati più flessibile.

CERCA.X per restituire più colonne o righe (adiacenti o meno)

La Funzione CERCA.X di Excel offre una soluzione per restituire dati presenti su più colonne in un foglio di lavoro. Questa Funzione consente di eseguire ricerche flessibili all'interno di insiemi di dati, migliorando significativamente le capacità di estrazione delle informazioni rispetto alle Funzioni di ricerca tradizionali come CERCA.VERT o CERCA.ORIZZ.
Nello specifico nell'esempio raffigurato nella successiva immagine, si vuole la contemporanea restituzione di tutti i valori a destra del codice (categoria prodotto, modello e prezzo)
Per ottenere questo risultato, nel terzo argomento della Funzione matrice_restituta è sufficiente individuare un intervallo che comprenda tutte e tre le colonne adiacenti B2:D14
La Funzione inserita nella cella F2 è la seguente: =CERCA.X(F2;A2:A14;B2:D14)

CERCA.X è particolarmente utile in scenari in cui è necessario cercare un valore in una colonna e ottenere dati associati da più colonne, come ad esempio in database aziendali, gestionali o reportistiche avanzate.

Restituzione di più colonne non adiacenti 

Per restituire valori da colonne non adiacenti, è possibile la Funzione FILTRO definendo nel suo secondo argomento un impostazione di matrice {1\0\1}
Rispetto le tre colonne individuate il numero 1 o il 0 definisco VERO e FALSO quali devono essere restituite, pertanto le prima e la terza.

Per un approfondimento sulla Funzione Filtro si rimanda a: La Funzione FILTRO di Excel

Inserire più criteri di ricerca nella Funzione CERCA.X 

Per inserire più criteri di ricerca nella Funzione CERCA.X, si deve ricorre all'applicazione della logica booleana.
Il primo argomento della Funzione CERCA.X accetta l'impostazione VERO FALSO, rispetto un valutazione logico comparativa che potrà essere impostata per due o più colonne del secondo argomento.
Rispetto a quanto proposto nell'esempio sottostante la Funzione CERCA.X potrà essere impostata nel seguente modo:
=CERCA.X(1;(A2:A14=E2)*(B2:B14=F2);C2:C14)
Importante osservare che nel secondo argomento i risultati comparativo delle due colonne vengono fra di loro moltiplicati.
Dal punto di vista esecutivo, la Funzione CERCA.X cercherà il valore 1 (VERO) proposto nel primo argomento. Contestualmente nel secondo argomento si crea una matrice temporanea composto da 0 (se non c’è corrispondenza) e 1 (se c’è corrispondenza) per le seguenti comparazioni (A2:A14=E2)*(B2:B14=F2) .
In pratica, la Funzione verifica il VERO del primo argomento con il risultato della moltiplicazione del confronto VERO FALSO del due colonne del secondo argomento
Se trova una corrispondenza, il confronto restituisce (vero). Se non la trova, restituisce (falso).
Quindi, moltiplicando i valori VERO e FALSO ottenuto dalla comparazione delle due colonne (che Excel interpreta come 1 e 0), si ottiene una unica matrice che contiene 1 solo per gli elementi che soddisfano tutti i criteri (sia il primo che il secondo confronto devono essere veri) oppure 0 se non tutti i criteri non vengono soddisfatti.
0*0 = 0 (FALSO)
0*1= 0 (FALSO)
1*1= 1 (VERO)
1*0 = 0 (FALSO)

 

Nell'ambito della logica matematica, si può utilizzare una matrice per verificare la corrispondenza in base a specifici criteri tra due colonne di dati .
I calcoli sulla matrice vanno a verificare che il criterio impostati per le due colonne restituiscono a seconda della corrispondenza esatta o meno il valore 1 o 0.

Considerazioni conclusive sulla Funzione CERCA.X

La Funzione CERCA.X in Excel rappresenta un'innovazione significativa rispetto alle tradizionali Funzioni di ricerca e riferimento come CERCA.VERT e CERCA.ORIZZ, offrendo maggiore flessibilità e precisione.
Tra le sue principali caratteristiche innovative spiccano:
Ricerca bidirezionale: A differenza di CERCA.VERT e CERCA.ORIZZ, che sono limitate alla ricerca verticale o orizzontale, CERCA.X consente di cercare dati in entrambe le direzioni, facilitando la gestione di tabelle complesse.
Ricerca su qualsiasi colonna: Con CERCA.X, è possibile cercare in qualsiasi colonna, senza il vincolo che la colonna di ricerca debba essere la prima, come avviene in CERCA.VERT. Questo rende più flessibile la struttura del database senza l'obbligo didisporre del campo chiave per le ricerche nella prima colonna.
Gestione dei valori non trovati: Una delle innovazioni più apprezzate è la possibilità di gestire in modo diretto i casi in cui il valore cercato non viene trovato. CERCA.X permette di definire un valore predefinito da restituire, evitando errori e semplificando la gestione di dati incompleti o mancanti.
Ricerca con corrispondenza esatta o parziale: questa Funzione consente di effettuare ricerche basate sia su una corrispondenza esatta sia su una corrispondenza parziale, come per ricerche approssimative o di caratteri simili. Ciò migliora la flessibilità e le capacità di ricerca rispetto a Funzioni come CONFRONTA o INDICE.
Restituzione di dati su più colonne: Un aspetto potente di CERCA.X è la possibilità di restituire valori da più colonne simultaneamente, anziché dover ripetere la Funzione più volte. Questo è utile, ad esempio, quando si devono estrarre più informazioni associate a un determinato valore da diverse colonne di una tabella.
In sintesi, CERCA.X va oltre le limitazioni delle Funzioni di ricerca tradizionali, rendendo più efficiente e versatile il processo di ricerca e gestione dei dati. La sua flessibilità nell'adattarsi a diverse strutture di database, unita alla capacità di restituire dati multipli e gestire errori in modo efficiente, la rende uno strumento avanzato per l'analisi dei dati in Excel.

Indice Generale Argomenti: EXCEL

[Valter Borsato: ottobre - 2020 | Completamente riscritto 21/10/2024 | Ultimo aggiornamento 24/10/2024]