Caratteri jolly in Excel: *, ? e ~ in CONTA.SE e CERCA.VERT

Excel ha tre caratteri jolly per i criteri e le ricerche: * corrisponde a un numero qualsiasi di caratteri (anche nessuno), ? corrisponde esattamente a un carattere, e ~ ritrasforma il * o il ? successivo in un carattere normale. =CONTA.SE(A2:A7;"*apple*") conta le celle che contengono apple in qualsiasi punto. La tabella mostra le formule in inglese, con COUNTIF al posto di CONTA.SE, ma puoi scriverle anche in italiano, con il punto e virgola.

FormuleRipristina

D2fx

=COUNTIF($A$2:$A$7,C2)

	A	B	C	D
1	Product		Pattern	Count
2	Apple juice		*apple*	4
3	Green apple		apple*	2
4	Pineapple		*juice	2
5	Orange juice		?????	0
6	Pear		*e	4
7	Apples

Fai clic su una cella per vederne la formula. Cambia un numero o una formula e il foglio ricalcola.In Excel in italiano: =CONTA.SE($A$2:$A$7;C2)

• *apple* contiene apple: 4 corrispondenze, perché conta anche Pineapple.
• apple* inizia con apple: solo Apple juice e Apples. CONTA.SE ignora le maiuscole.
• *juice finisce con juice.
• ????? è lungo esattamente cinque caratteri. Nessuno di questi prodotti ha cinque caratteri, quindi 0; scrivi Peach in A6 e conta 1.
• *e finisce con e.

Scrivi un tuo schema nella colonna C, come *an* o P*, e il conteggio si aggiorna.

Corrispondenza parziale in CERCA.VERT e CERCA.X

CERCA.VERT (in inglese VLOOKUP) accetta caratteri jolly in modalità di corrispondenza esatta (FALSO come ultimo argomento). Unisci il carattere jolly al valore nella formula, così in D2 vanno solo le prime lettere:

FormuleRipristina

E2fx

=VLOOKUP(D2&"*",A2:B6,2,FALSE)

	A	B	C	D	E
1	Product	Price		Starts with	Price
2	Apple juice	3.5		Pin	4
3	Green apple	1.2			4
4	Pineapple	4
5	Orange juice	3.2
6	Pear	0.9

Fai clic su una cella per vederne la formula. Cambia un numero o una formula e il foglio ricalcola.In Excel in italiano: =CERCA.VERT(D2&"*";A2:B6;2;FALSO)

Entrambe le formule trovano Pineapple. Cambia D2 in juice: lo schema juice* di CERCA.VERT richiede che il testo inizi con juice e restituisce #N/D (in inglese #N/A; la tabella mostra i nomi inglesi degli errori), mentre lo schema *juice* di CERCA.X trova il primo prodotto che lo contiene, Apple juice. Come ogni corrispondenza esatta, una ricerca con caratteri jolly restituisce la prima riga che va bene, quindi rendi lo schema abbastanza specifico.

CERCA.X (in inglese XLOOKUP) tratta * e ? come caratteri jolly solo quando il quinto argomento, match_mode, è 2. Senza di esso, cerca l'asterisco alla lettera. CONFRONTA accetta caratteri jolly con match_type 0, e CONFRONTA.X con match_mode 2, proprio come CERCA.X. Vedi CERCA.VERT per il resto dei suoi argomenti.

FormuleRipristina

D2fx

	A	B	C	D
1	Product			Phone products
2	Smartphone
3	Headphones
4	Phone stand
5	Laptop bag
6	Charger

Fai clic su una cella per vederne la formula. Cambia un numero o una formula e il foglio ricalcola.

Tocca a te: In D2, conta i prodotti il cui nome contiene phone in qualsiasi punto.

VerificaMostra la risposta

Somma e media con un carattere jolly

Ogni funzione che accetta criteri li legge allo stesso modo, quindi gli stessi schemi funzionano in SOMMA.SE, SOMMA.PIÙ.SE, MEDIA.SE, MEDIA.PIÙ.SE, MAX.PIÙ.SE e MIN.PIÙ.SE:

FormuleRipristina

E2fx

=SUMIF(A2:A7,D2,B2:B7)

	A	B	C	D	E
1	Region	Sales		Pattern	Total
2	North-East	120		North*	285
3	North-West	95		*West	155
4	South	80		?????	150
5	South-West	60
6	East	110
7	North	70

Fai clic su una cella per vederne la formula. Cambia un numero o una formula e il foglio ricalcola.In Excel in italiano: =SOMMA.SE(A2:A7;D2;B2:B7)

North* somma ogni regione che inizia con North, compresa North stessa, perché * corrisponde anche a niente. ????? somma le regioni con esattamente cinque caratteri: South e North.

FormuleRipristina

E2fx

	A	B	C	D	E
1	Region	Sales			West total
2	North-East	120
3	North-West	95
4	South	80
5	South-West	60
6	East	110
7	North	70

Fai clic su una cella per vederne la formula. Cambia un numero o una formula e il foglio ricalcola.

Tocca a te: In E2, somma le vendite di ogni regione il cui nome finisce con West.

VerificaMostra la risposta

Trovare un vero asterisco o punto interrogativo con ~

Per contare il testo che contiene davvero un * o un ?, mettici davanti una tilde. La tilde stessa si scrive ~~.

FormuleRipristina

C2fx

=COUNTIF(A2:A5,"*~**")

	A	B	C
1	Note		Count
2	Rated 5*		1
3	Why?		1
4	Done		4
5	5 stars

Fai clic su una cella per vederne la formula. Cambia un numero o una formula e il foglio ricalcola.In Excel in italiano: =CONTA.SE(A2:A5;"*~**")

C2 conta le celle che contengono un asterisco letterale (solo A2), C3 quelle che contengono un punto interrogativo. C4 mostra l'altro lato: "*" da solo corrisponde a qualsiasi testo, quindi conta ogni cella di testo, qui 4. Salta numeri e celle vuote, ed è per questo che CONTA.SE(intervallo;"*") è il modo abituale per contare le celle di testo.

Quali funzioni accettano i caratteri jolly

RICERCA accetta caratteri jolly dentro una formula: =RICERCA("b?d";"a bad day") restituisce 3 in Excel (TROVA e RICERCA ha i dettagli). Per FILTRO, usa VAL.NUMERO(RICERCA(...)) come condizione invece di uno schema.

Errore comune: un carattere jolly dopo =

L'operatore = non legge mai i caratteri jolly: =A2="*apple*" chiede se A2 contiene i sette caratteri *apple*. In Excel, con Green apple in A2:

=A2="*apple*"                    FALSE
=IF(A2="*apple*","yes","no")     no

Metti invece il test in CONTA.SE, che per una cella singola restituisce 1 o 0, oppure usa RICERCA:

FormuleRipristina

B2fx

=IF(COUNTIF(A2,"*apple*"),"contains apple","no")

	A	B	C
1	Product	COUNTIF test	SEARCH test
2	Green apple	contains apple	contains apple
3	Pear	no	no

Fai clic su una cella per vederne la formula. Cambia un numero o una formula e il foglio ricalcola.In Excel in italiano: =SE(CONTA.SE(A2;"*apple*");"contains apple";"no")

SE tratta l'1 di CONTA.SE come VERO e lo 0 come FALSO. La versione con RICERCA non ha bisogno di caratteri jolly, perché RICERCA cerca già il testo in qualsiasi punto della cella.