Caratteri jolly in Excel: guida con esempi

I caratteri jolly di Excel sono simboli speciali che aiutano a eseguire corrispondenze parziali e operazioni flessibili sui dati in dataset di grandi dimensioni. Anche se Excel non supporta appieno le regex, puoi usare i caratteri jolly con una strutturazione creativa delle formule (funzioni come SEARCH(), TEXT() e ISNUMBER()).

Diventare competenti con i caratteri jolly è più semplice quando hai solide basi di Excel. Il nostro corso Introduzione a Excel ti guida nelle competenze essenziali, tra cui navigazione dell'interfaccia, riferimenti di cella e formule di base, creando le fondamenta per applicare in modo efficace le tecniche con i caratteri jolly trattate in questa guida.

Cosa sono i caratteri jolly in Excel?

I caratteri jolly sono simboli speciali che sostituiscono caratteri in una stringa di testo, permettendoti di cercare schemi invece di corrispondenze esatte. Excel riconosce tre caratteri jolly, ciascuno con uno scopo specifico nel pattern matching e nelle operazioni sul testo.

L'asterisco (*)

L'asterisco (*) corrisponde a qualsiasi numero di caratteri, inclusi zero. Ad esempio, App* corrisponderebbe a "Apple", "Application", "App" e "Approach".

Questo carattere jolly è utile quando conosci l'inizio di una stringa di testo ma vuoi includere tutte le variazioni successive.

Il punto interrogativo (?)

Il punto interrogativo (?) corrisponde esattamente a un carattere. Usando B?t corrisponderebbe a "Bat", "Bet", "Bit" e "But", ma non a "Boat" o "Boot".

Questa precisione lo rende ideale quando conosci la lunghezza e la maggior parte dei caratteri del testo di destinazione.

La tilde (~)

La tilde (~) funge da carattere di escape, permettendoti di cercare asterischi o punti interrogativi reali nei tuoi dati. Quando devi trovare testo che contiene letteralmente i caratteri * o ?, anteporre una tilde indica a Excel di trattarli come caratteri normali e non come jolly.

Ad esempio, ~*Sale corrisponderebbe alla stringa letterale “*Sale” invece di trattare l'asterisco come carattere jolly.

Uso dei caratteri jolly nelle formule di Excel

Diverse funzioni di Excel supportano i caratteri jolly, rendendoli utili per calcoli condizionali e operazioni di ricerca. Le funzioni più usate che accettano i caratteri jolly includono COUNTIF(), COUNTIFS(), SUMIF(), SUMIFS(), AVERAGEIF(), AVERAGEIFS(), SEARCH(), VLOOKUP(), HLOOKUP(), XLOOKUP(), MATCH() e XMATCH().

Ognuna di queste funzioni può sfruttare i caratteri jolly per effettuare corrispondenze flessibili, permettendoti di lavorare con match parziali e gestire efficacemente le variazioni nei tuoi dataset.

COUNTIF() con caratteri jolly

La funzione COUNTIF() diventa particolarmente utile se combinata con i caratteri jolly per contare le celle che corrispondono a specifici schemi. Ecco un esempio di base:

=COUNTIF(A2:A12, "*AA*")Offerto da

Questa formula conta tutte le celle nell'intervallo A2:A12 che contengono "AA" in qualsiasi punto del testo. Gli asterischi prima e dopo "AA" dicono a Excel di accettare qualsiasi carattere prima o dopo questa sequenza.

Per criteri dinamici, puoi fare riferimento a una cella che contiene il tuo pattern con caratteri jolly:

=COUNTIF(A2:A12, E1)Offerto da

In questo caso, la cella E1 potrebbe contenere "*AA*" o qualsiasi altro pattern con caratteri jolly. Questo approccio ti permette di cambiare i criteri di ricerca senza modificare la formula, rendendo il foglio di lavoro più flessibile e facile da usare.

VLOOKUP() e XLOOKUP() con caratteri jolly

Le funzioni di ricerca possono usare i caratteri jolly per corrispondenze parziali, utili quando i valori di ricerca non coincidono esattamente. Ecco esempi di entrambi gli approcci:

=VLOOKUP("John*", A:C, 2, FALSE)Offerto da

Questa formula VLOOKUP() cerca qualsiasi voce nella colonna A che inizi con "John" e restituisce il valore corrispondente dalla colonna 2. Il parametro FALSE assicura che la funzione usi la modalità di corrispondenza esatta, necessaria per il funzionamento dei caratteri jolly.

Per XLOOKUP(), la sintassi è leggermente diversa:

=XLOOKUP("*Adams", A:A, C:C, "Not found", 2)Offerto da

Il parametro della modalità di corrispondenza (2) indica a XLOOKUP() di usare il matching con caratteri jolly. Questa formula trova qualsiasi voce che termini con "Adams" e restituisce il valore corrispondente dalla colonna C. Restituisce “Not found” se non c'è alcuna corrispondenza. 

IF() con soluzione alternativa ai caratteri jolly

Sebbene la funzione IF() non supporti direttamente i caratteri jolly, puoi ottenere una funzionalità simile combinando IF() con COUNTIF():

=IF(COUNTIF(A1, "Amy*"), "Yes", "No")Offerto da

Questa formula verifica se la cella A1 contiene testo che inizia con "Amy" e restituisce "Yes" se sì, "No" altrimenti. La funzione COUNTIF() gestisce la corrispondenza con i caratteri jolly, mentre IF() elabora il risultato.

SUMIF() con caratteri jolly

La funzione SUMIF() può sommare valori in base a criteri con caratteri jolly, risultando utile per aggregare dati con schemi simili:

=SUMIF(A:A, "*a*", C:C)Offerto da

Questa formula somma tutti i valori nella colonna C in cui la cella corrispondente nella colonna A contiene la lettera "a" in qualsiasi punto del testo. Il pattern con carattere jolly "a" cattura qualsiasi testo contenente "a" indipendentemente dalla posizione.

Uso dei caratteri jolly negli strumenti di Excel

I caratteri jolly vanno oltre le formule e possono essere usati in diversi strumenti di Excel per cercare, confrontare e filtrare i dati. Queste funzionalità integrate rendono i caratteri jolly accessibili anche quando non stai scrivendo formule complesse.

Caratteri jolly in Trova e sostituisci

La finestra di dialogo Trova e Sostituisci di Excel supporta i caratteri jolly per individuare schemi variabili nei dati. Per abilitare i caratteri jolly, devi selezionare l'opzione "Usa caratteri jolly" nella finestra Trova e Sostituisci.

Puoi usare l'asterisco (*) per trovare schemi con lunghezze variabili. Ad esempio, cercando "Product*X" verrebbero trovati "Product-A-X", "Product-Premium-X" e "Product-X". Allo stesso modo, il punto interrogativo (?) aiuta a trovare schemi con posizioni di caratteri specifiche, come "20??" per trovare qualsiasi numero di quattro cifre che inizi con "20".

Quando devi cercare asterischi o punti interrogativi reali nei tuoi dati, usa la tilde (~) come carattere di escape. Cercando "~*Sale" troverai le celle che contengono il testo letterale "*Sale" invece di trattare l'asterisco come un carattere jolly.

Caratteri jolly nelle funzioni di filtro e ricerca

Le funzionalità di filtro di Excel supportano i caratteri jolly, particolarmente utili nei filtri di testo con opzioni come "contiene", "inizia con" e "termina con". Questi filtri interpretano automaticamente i caratteri jolly quando li includi nei criteri.

Ad esempio, applicando un filtro con "Jo*" nell'opzione "inizia con" verranno mostrati tutti i nomi che iniziano con "Jo", inclusi "John", "Joseph", "Joan" e "Jonathan". Questo approccio è ottimo per un'esplorazione rapida dei dati senza scrivere formule.

Tuttavia, le funzioni di ricerca integrate hanno alcune limitazioni rispetto alle opzioni di filtro avanzate. Sebbene i pattern di base funzionino bene, filtraggi complessi con più criteri possono richiedere la combinazione di caratteri jolly con formule personalizzate o l'uso del Filtro avanzato di Excel per un pattern matching più sofisticato.

Lavorare con numeri e caratteri jolly

Usare i caratteri jolly con i numeri richiede un'attenzione particolare perché Excel tratta le operazioni con caratteri jolly come funzioni basate sul testo. Quando applichi pattern con caratteri jolly a dati numerici, Excel converte automaticamente i numeri in testo, il che può portare a comportamenti di corrispondenza inattesi.

Per un matching efficace dei numeri con i caratteri jolly, hai diverse soluzioni alternative. Puoi convertire i numeri in testo usando la funzione TEXT(), la funzionalità Testo in colonne o formule specifiche pensate per questo scopo.

Un approccio pratico prevede la combinazione delle funzioni SEARCH(), ISNUMBER() e SUMPRODUCT():

=SUMPRODUCT(--(ISNUMBER(SEARCH("4", B2:B12))))Offerto da

Questa formula conta quante celle nell'intervallo B2:B12 contengono la cifra "4" in qualsiasi punto del numero. Per sommare i valori in cui i numeri contengono pattern specifici, puoi estendere questo approccio:

=SUMPRODUCT(--(ISNUMBER(SEARCH("4*", TEXT(B2:B12,"0")))), C2:C12)Offerto da

Questa formula somma i valori nella colonna C in cui i numeri corrispondenti nella colonna B contengono “4” seguito da qualsiasi cifra. 

Per il matching delle date, usa la funzione TEXT() per convertire le date in pattern di testo ricercabili:

=TEXT(C2:C12, "mmddyyyy")Offerto da

Questo converte le date in un formato di testo coerente che i caratteri jolly possono elaborare efficacemente. Puoi quindi applicare pattern con caratteri jolly per trovare componenti specifiche della data: 

=COUNTIF(TEXT(C2:C12,"mmddyyyy"),"*0525*")Offerto da

La formula conta le date che contengono “0525” (25 maggio in qualsiasi anno). 

Alcuni scenari coinvolgono numeri archiviati come testo, come CAP, ID prodotto o numeri di riferimento. In questi casi, i caratteri jolly possono confrontarli direttamente come stringhe di testo. Ad esempio, filtrare codici con pattern come "123*" corrisponderebbe a "12345", "123-AB" e "123XYZ", mentre "??-45" corrisponderebbe a "AB-45", "12-45" e "XY-45".

Limitazioni e sorprese dei caratteri jolly

Non tutte le funzioni di Excel supportano i caratteri jolly, il che può creare confusione quando ti aspetti che alcune formule funzionino con il pattern matching. La funzione IF(), ad esempio, non accetta direttamente i caratteri jolly e richiede soluzioni come la combinazione con COUNTIF(), come mostrato in precedenza.

I caratteri jolly funzionano solo con il testo nelle formule, quindi i valori numerici devono essere convertiti in testo prima che le operazioni con caratteri jolly possano elaborarli efficacemente. Questa necessità di conversione può portare a risultati inattesi se non ne sei consapevole.

Fai attenzione ai falsi positivi nelle formule di ricerca quando usi pattern con caratteri jolly troppo ampi. Ad esempio, VLOOKUP("*Smith*", ...) potrebbe restituire corrispondenze indesiderate come "Smithson", "Blacksmith" o "Goldsmith" quando volevi solo "Smith". Questo comportamento richiede una progettazione accurata dei pattern per evitare risultati non voluti.

Un'altra sorpresa comune riguarda gli spazi iniziali o finali nei dati. I caratteri jolly trattano gli spazi come caratteri, quindi "John*" non corrisponderà a " John" (con uno spazio iniziale). La pulizia dei dati diventa importante quando lavori con i caratteri jolly per garantire risultati coerenti.

Inoltre, il matching con caratteri jolly non distingue tra maiuscole e minuscole per impostazione predefinita. Questo significa che pattern come "apple*" corrisponderanno a "Apple", "APPLE" e "ApPlE". Questo comportamento è comodo per ricerche generali ma può causare corrispondenze indesiderate se ti serve distinguere tra maiuscole e minuscole. Per ottenere corrispondenze case-sensitive, valuta l'uso di funzioni come FIND() combinate con ISNUMBER(). 

Consigli quando lavori con i caratteri jolly in Excel

I caratteri jolly sono ottimi per unire dati con corrispondenze approssimative, particolarmente utile in caso di grafie incoerenti o variazioni di inserimento dati. Ad esempio, potresti avere "Homel" e "Gomel" nello stesso dataset a indicare la stessa località. Usando pattern come "*omel" puoi identificare e consolidare queste voci simili.

Il formato dei numeri di telefono spesso varia tra i dataset, con prefissi o codici paese diversi. Puoi usare i caratteri jolly per standardizzare queste incoerenze. Ad esempio, puoi usare Trova e Sostituisci per convertire i numeri in formato internazionale UK in formato nazionale:

Find: 44??????????
Replace with: 0Offerto da

Questo pattern corrisponde a qualsiasi numero UK che inizi con "44" seguito da dieci cifre, convertendo di fatto numeri come "447123456789" in "07123456789" per una formattazione nazionale coerente.

Quando devi escludere risultati specifici dalle corrispondenze con caratteri jolly, combina più funzioni SUMIF() o COUNTIF(). Questa tecnica funziona bene per sottrarre i match indesiderati:

=SUMIF(..., "00123H*") - SUMIF(..., "00123H*-SP")Offerto da

Questa formula somma tutti i valori che corrispondono a "00123H*" ma esclude quelli che terminano con "-SP", offrendoti un controllo più preciso sul matching con caratteri jolly.

Le modifiche di formattazione funzionano bene insieme ai caratteri jolly con funzioni come REPLACE(), SUBSTITUTE() o TEXT(). Queste funzioni possono pulire e standardizzare i tuoi dati prima o dopo l'applicazione dei caratteri jolly, garantendo risultati coerenti nella tua analisi.

Quando lavori con codici prodotto o numeri di riferimento, i caratteri jolly aiutano a identificare pattern ed eccezioni. Potresti usare *-TEMP per trovare tutti i codici temporanei o ??-??-???? per individuare le voci che seguono una struttura di formato specifica, rendendo più efficiente la validazione e la pulizia dei dati.

Conclusione

Che tu stia consolidando voci simili, standardizzando i formati dei numeri di telefono o filtrando grandi dataset per pattern specifici, i caratteri jolly offrono un'alternativa pratica alle corrispondenze esatte. Fanno da ponte tra ricerche testuali di base e funzionalità regex più complesse, rendendo il pattern matching accessibile nell'ambiente familiare di Excel.

Per espandere le tue competenze in Excel oltre le operazioni con i caratteri jolly, il nostro percorso di abilità Excel Fundamentals offre un percorso completo che copre tecniche di preparazione, analisi e visualizzazione dei dati. Per chi è pronto ad applicare queste competenze in contesti analitici, il nostro corso Data Analysis in Excel offre esperienza pratica con le tabelle pivot e funzioni logiche avanzate che completano le abilità di pattern matching che hai imparato qui.