Tutorial su XMATCH in Excel: panoramica, sintassi e applicazioni pratiche

Durante le tue analisi dei dati ci sono molte situazioni in cui avrai bisogno di trovare la posizione relativa di un elemento in una matrice o in un intervallo di celle dato un certo valore o modello. 

Il modo più efficace per eseguire operazioni di pattern matching in Excel è la funzione XMATCH. Semplice ma potente, XMATCH offre diverse funzionalità che consentono corrispondenze precise e flessibili. 

In questo tutorial, vedremo le basi della funzione XMATCH di Excel, analizzeremo la sua sintassi e i diversi parametri e forniremo esempi pratici per illustrarne le capacità. Confronteremo anche XMATCH con MATCH, un’altra funzione molto usata per compiti analoghi.  Infine, vedremo gli errori più comuni quando si usa XMATCH e indicheremo le best practice per garantirne un uso corretto ed efficiente.

Se stai imparando Excel e vuoi saperne di più, dai un’occhiata alla nostra skill track Excel Fundamentals per imparare tutto l’essenziale. 

Che cos’è XMATCH?

XMATCH è una funzione flessibile e robusta per operazioni di corrispondenza. XMATCH() ti consente di eseguire tutti i tipi di ricerche, da quelle verticali e orizzontali alle corrispondenze esatte, approssimate e parziali. 

Se usi Excel da tempo, probabilmente conosci la funzione MATCH. Prima dell’aggiornamento di Excel che ha introdotto XMATCH, MATCH era la funzione di riferimento per le operazioni di ricerca. Puoi saperne di più nel nostro tutorial sulle funzioni di ricerca in Excel. 

Tuttavia, come vedremo più avanti, MATCH può risultare limitata in alcuni scenari. In questo senso, XMATCH può essere vista come un aggiornamento migliorato e atteso della tradizionale MATCH, offrendo ricerche in qualsiasi direzione e più tipi di corrispondenza, rendendola più semplice e adatta a una gamma più ampia di casi d’uso.

Nota che la funzione è disponibile solo in Excel per Microsoft 365 ed Excel 2021. Per chi usa versioni precedenti di Excel, MATCH resta l’unica funzione disponibile.

Vediamo ora la sintassi e i parametri di XMATCH. 

Sintassi e parametri di XMATCH

Ecco la sintassi di base della funzione XMATCH:

=XMATCH(lookup_value, lookup_array, [match_mode], [search_mode])Offerto da

La funzione prevede due argomenti obbligatori (ossia lookup_value e lookup_array) e due facoltativi (match_mode e search_mode) che offrono possibilità di operazioni più precise e flessibili.

Di seguito trovi una descrizione dei quattro parametri:

• Lookup_value: È il valore da cercare.
• Lookup_array: È la matrice o l’elenco in cui effettuare la ricerca. Può essere una matrice di ricerca verticale o orizzontale.
• Match_mode: consente di scegliere il tipo di corrispondenza. Ci sono quattro modalità, tra cui:
• 0: Corrispondenza esatta (modalità predefinita)
• -1: Corrispondenza esatta o elemento immediatamente più piccolo
• 1: Corrispondenza esatta o elemento immediatamente più grande
• 2: Corrispondenza con caratteri jolly, come * e ?.
• Search_mode: consente di specificare la modalità di ricerca, inclusa la direzione e l’algoritmo. Sono possibili quattro valori:
• 1: Ricerca dall’inizio alla fine (valore predefinito)
• -1: Ricerca dalla fine all’inizio (ricerca inversa).
• 2:  Ricerca binaria in ordine crescente. La ricerca viene eseguita assumendo che la matrice di ricerca sia ordinata in ordine crescente, altrimenti restituisce risultati non validi.
• -2:  Ricerca binaria in ordine decrescente. La ricerca viene eseguita assumendo che la matrice di ricerca sia ordinata in ordine decrescente, altrimenti restituisce risultati non validi.

Modalità di corrispondenza: precisione e flessibilità

XMATCH include un parametro facoltativo match_mode che consente di scegliere il tipo di corrispondenza. Queste opzioni sono molto utili perché permettono sia corrispondenze precise sia corrispondenze approssimative. Vediamo nel dettaglio le modalità disponibili:

Corrispondenza esatta

La corrispondenza esatta (match_mode = 0) è la modalità predefinita di XMATCH. Significa che Excel troverà una corrispondenza esatta del valore di ricerca nella matrice di ricerca, altrimenti restituirà un errore NA. La corrispondenza esatta è essenziale quando accuratezza e precisione sono fondamentali, ad esempio per trovare voci specifiche in un database.

Ecco un esempio di base su come usare XMATCH per trovare la posizione esatta di “Excel” in un elenco di tecnologie presenti nel catalogo corsi di DataCamp. Forniamo il valore di ricerca in D3 e la matrice di ricerca (A3:A14), ed Excel restituisce correttamente la posizione 6. 

Corrispondenza approssimata

Potrebbero esserci situazioni in cui serve una corrispondenza flessibile, magari perché non conosci esattamente il valore di ricerca o perché stai cercando risultati approssimativi. 

Fortunatamente, XMATCH offre due modalità per restituire il valore esatto o il più vicino immediatamente più piccolo e il valore esatto o il più vicino immediatamente più grande. 

Nel seguente esempio usiamo la corrispondenza approssimata per trovare i conteggi di corsi più vicini al valore di ricerca 15. 

Usando il più piccolo successivo (match_mode = -1), Excel restituisce la posizione 6, perché 12 corsi è il numero più vicino inferiore nell’elenco. Nel caso del più grande successivo, Excel restituisce 5, perché 16 corsi è il numero più vicino superiore.

Usiamo anche la corrispondenza esatta per illustrare cosa succede quando Excel non trova una corrispondenza esatta.

Caratteri jolly e regex

Una funzione innovativa di XMATCH è la possibilità di usare caratteri jolly di base quando imposti match_mode a 2, oltre a espressioni regolari più avanzate se impostato a 3. Queste modalità funzionano solo con dati testuali.

XMATCH consente di usare due caratteri jolly molto diffusi:

• ?: per far corrispondere un singolo carattere qualsiasi.
• *: per far corrispondere qualsiasi sequenza di caratteri.

Di seguito trovi vari esempi di come usare le corrispondenze regex. Nel primo esempio cerchiamo tecnologie che iniziano con E. Nel secondo, cerchiamo tecnologie che contengono una A nel nome. Infine, cerchiamo tecnologie con tre lettere.

Nota che XMATCH restituirà sempre la prima occorrenza nella matrice di ricerca, a meno che non specifichiamo diversamente (vedi sezione successiva).

Se vuoi usare espressioni regolari più complesse, imposta match_mode su 3. Le espressioni regolari, spesso abbreviate in "regex" o "regexp", sono un modo per definire un modello di ricerca, utile per varie operazioni sul testo come ricerca, parsing e/o sostituzione. 

Per maggiori dettagli sul pattern matching, il nostro Excel Regex Tutorial: padroneggia il pattern matching con le espressioni regolari è un ottimo punto di partenza.

Ecco un esempio piuttosto semplice: vogliamo trovare la posizione di un corso il cui codice include tre lettere all’inizio, tre alla fine e due numeri nel mezzo (31).

Modalità di ricerca di XMATCH: direzione ed efficienza

XMATCH introduce un nuovo parametro che ti permette di controllare la direzione della ricerca. Questa funzione è ottima quando ti interessa trovare l’ultima occorrenza anziché la prima, ad esempio in alcune attività di analisi di serie temporali. 

Esploriamo nel dettaglio le diverse modalità di ricerca.

Ricerca inversa

Per effettuare una ricerca inversa, puoi impostare il parametro search_mode a -1. Ad esempio, sotto puoi vedere come trovare il primo e l’ultimo corso completato dall’utente Peter.

Ricerca binaria

Infine, il parametro search_mode offre anche la possibilità di usare un algoritmo di ricerca binaria per eseguire l’operazione di corrispondenza. La ricerca binaria è un algoritmo di ordinamento che funziona dividendo ripetutamente a metà l’intervallo di ricerca. È uno degli algoritmi più efficienti, ma richiede che i dati in input siano già ordinati. 

XMATCH ti consente di sfruttare la ricerca binaria su una matrice ordinata sia in ordine crescente (search_mode = 2) sia decrescente (search_mode = -2). Se la matrice non è ordinata, la funzione restituirà un errore.

Applicazioni pratiche ed esempi di XMATCH

XMATCH è adatta a una vasta gamma di attività in Excel, da quelle di base a quelle avanzate. In questa sezione vediamo alcune delle applicazioni pratiche più interessanti.

Ricerche bidimensionali con INDEX/XMATCH

Le funzioni INDEX e XMATCH possono lavorare insieme per eseguire ricerche bidimensionali, cioè cercare contemporaneamente in righe e colonne. Combinando queste due funzioni, possiamo creare una ricerca dinamica molto utile in ambiti come l’analisi finanziaria, dove è molto comune confrontare variabili tra categorie. 

Ad esempio, nell’immagine seguente combiniamo INDEX e MATCH per estrarre il numero di utenti iscritti ai corsi di Excel durante il Q2. INDEX qui prende tre argomenti: la matrice in cui trovare il valore, il numero di riga e il numero di colonna. I valori degli ultimi due argomenti sono forniti da più funzioni XMATCH.

 Se vogliamo cambiare la tecnologia o il trimestre, basta modificare i valori di ricerca nelle funzioni XMATCH e il risultato di INDEX cambierà di conseguenza.

La combinazione INDEX/XMATCH funziona in modo molto simile a INDEX/MATCH. Se vuoi conoscere questo potente abbinamento con esempi pratici, consulta il nostro tutorial su come usare INDEX MATCH con criteri multipli in Excel .

Ricerche su più colonne

Un altro ottimo utilizzo di XMATCH è combinare più criteri per creare ricerche su più colonne. Potrebbe non sembrare intuitivo, poiché XMATCH richiede un valore di ricerca e una matrice di ricerca. Ma possiamo usare la logica booleana per creare una matrice temporanea all’interno della formula XMATCH che contiene zeri e uno per rappresentare le righe che soddisfano i criteri specificati. 

Per esempio, supponi di voler trovare la prima tecnologia nell’elenco con meno di 1000 utenti iscritti in ogni trimestre. Puoi combinare le condizioni unendo i risultati di ogni colonna usando il simbolo *. Excel troverà sotto il cofano la matrice contenente tutti uno, nel nostro caso Tableau, quindi la posizione 5.

Filtro con matrici dinamiche

XMATCH può anche lavorare insieme alle funzioni FILTER e ISNUMBER per migliorare il filtraggio con matrici dinamiche.

Di seguito puoi vedere come funziona. Useremo queste funzioni per filtrare le informazioni dei corsi completati dagli utenti in base al nome utente. 

Per prima cosa usiamo XMATCH per calcolare la posizione relativa di ogni utente nella tabella in base alla matrice di ricerca con i nomi che useremo per filtrare. Questo ci fornirà una colonna con la posizione relativa di ogni nome, inclusi valori null per gli utenti non inclusi nell’elenco di filtro.

Poi convertiamo la colonna con posizioni relative e valori null in TRUE o FALSE usando la funzione ISNUMBER.

Infine, usiamo FILTER per selezionare tutte le colonne che vogliamo visualizzare e la colonna con TRUE e FALSE per filtrare le righe.

Gestione dei valori null con ISNA

Quando XMATCH non trova alcuna corrispondenza, restituisce un errore NA. Sebbene sia già informativo, puoi rendere i risultati più leggibili ed evitare fraintendimenti usando le funzioni ISNA e IF.

Immagina di avere una tabella con i corsi completati da Sam e Peter e di voler sapere quali corsi hanno in comune. Se usiamo solo XMATCH, Excel restituirà la posizione relativa dei corsi comuni e valori null altrimenti. Se passiamo XMATCH come argomento a ISNA, la funzione restituirà invece TRUE o FALSE. Poi, usando IF, possiamo sostituire i valori booleani con altri messaggi.

Ricerca binaria per dataset di grandi dimensioni

Abbiamo già menzionato che XMATCH consente di usare un algoritmo di ricerca binaria durante le operazioni di corrispondenza. Questa funzione può fare la differenza con dataset di grandi dimensioni. A patto che la matrice di ricerca sia ordinata, scegliere il motore di ricerca binaria può velocizzare notevolmente le operazioni.

XMATCH vs MATCH: differenze chiave

Come già detto, XMATCH è stata progettata per sostituire la tradizionale MATCH. Hanno lo stesso scopo, ma XMATCH introduce nuove funzionalità che la rendono più potente di MATCH. Di seguito trovi un elenco delle principali differenze tra XMATCH e MATCH:

Errori comuni e soluzioni

Gli utilizzi di XMATCH sono numerosi. Tuttavia, è importante menzionare i problemi comuni che potresti incontrare usandola. Ecco un elenco di alcune insidie frequenti e consigli per affrontarle.

• Controlla i tipi di dati: XMATCH può avere un comportamento imprevisto se la applichi a un intervallo con tipi di dati errati. Quindi, prima di usare la funzione, assicurati di aver impostato i tipi di dati corretti per le tue colonne.
• Coerenza degli intervalli: Sebbene XMATCH consenta intervalli di ricerca orizzontali e verticali, mescolarli può portare a problemi imprevisti e cali di prestazioni. Quando possibile, cerca di essere coerente e scegli intervalli orizzontali o verticali.
• Sfrutta le modalità di corrispondenza: XMATCH offre diverse modalità di corrispondenza. La modalità predefinita è quella esatta, ma puoi ottimizzare le ricerche usando corrispondenze approssimate e operazioni regex.
• Meglio con matrici ordinate: In generale, le prestazioni di XMATCH saranno sempre migliori se la applichi a matrici ordinate. Questo vale per la funzione predefinita ed è un prerequisito obbligatorio se vuoi usare la ricerca binaria. 
• Gestione degli errori N/A:  Come già menzionato, XMATCH restituirà un errore NA se non c’è alcuna corrispondenza nelle tue ricerche. Il modo migliore per gestirlo e migliorare la leggibilità è passare i risultati a una combinazione di funzioni IF e ISNA, o alla funzione IFNA.

Considerazioni sulle prestazioni

XMATCH non è l’unica funzione per eseguire ricerche in Excel. Ad esempio, potresti valutare l’uso di VLOOKUP, HLOOKUP o persino funzioni legacy come MATCH. Tuttavia, in termini di velocità di ricerca, XMATCH è probabilmente la più rapida, soprattutto se la applichi a matrici ordinate, dove puoi anche sfruttare la potenza della ricerca binaria.

Vale anche la pena ricordare che XMATCH è progettata come formula con matrici dinamiche, il che significa che può restituire matrici di dimensioni variabili a seconda dei dati in input. Le formule con matrici dinamiche sono particolarmente adatte quando si lavora con dataset di grandi dimensioni o calcoli complessi. 

Conclusione

Congratulazioni per essere arrivato fino in fondo. XMATCH è una funzione potente e non può mancare nelle tue analisi dati in Excel. Da sola o in combinazione con altre funzioni di Excel, XMATCH porterà le tue operazioni di ricerca a un livello superiore. 

Ma nel meraviglioso mondo di Excel c’è molto altro da scoprire. Dai un’occhiata ai nostri corsi, blog e tutorial dedicati per diventare un mago di Excel:

• XLOOKUP() vs. VLOOKUP(): un confronto per chi usa Excel
• Cheat sheet: manipolazione dei dati in Excel 
• Excel | Corso Master Advanced Techniques
• Intermediate Power Query in Excel 
• Cheat sheet: scorciatoie di Excel 
• Cheat sheet: formule di Excel 
• Come usare INDEX MATCH con criteri multipli in Excel 
• Data wrangling con INDEX-MATCH nei fogli di calcolo 
• Excel Regex Tutorial: padroneggiare il pattern matching con le espressioni regolari