Funzione FILTRO in Excel: più criteri, E e O (FILTER)

=FILTRO(A2:C7;B2:B7="North") restituisce ogni riga di A2:C7 la cui regione nella colonna B è North. La scrivi in una cella e le righe che corrispondono si espandono nelle celle sotto e a destra. Cambia una regione nella colonna B in North, o un North in South, e l'elenco si aggiorna. In inglese FILTRO si chiama FILTER, ed è così che la mostra la tabella: =FILTER(A2:C7,B2:B7="North"). Puoi scrivere le formule anche in italiano, con il punto e virgola.

FormuleRipristina

E2fx

=FILTER(A2:C7,B2:B7="North")

	A	B	C	D	E	F	G
1	Name	Region	Sales		Name	Region	Sales
2	Ann	North	120		Ann	North	120
3	Ben	South	80		Cara	North	200
4	Cara	North	200		Finn	North	60
5	Dan	East	150
6	Eve	South	95
7	Finn	North	60

Fai clic su una cella per vederne la formula. Cambia un numero o una formula e il foglio ricalcola.In Excel in italiano: =FILTRO(A2:C7;B2:B7="North")

Solo E2 contiene una formula. Le altre celle piene in E:G sono il suo risultato espanso: fai clic su F3 e vedi che appartiene alla formula in E2. Se in quell'area è scritto qualcosa, FILTRO mostra #ESPANSIONE! (in inglese #SPILL!; la tabella mostra i nomi inglesi degli errori) invece delle righe (vedi errori #ESPANSIONE!).

Sintassi di FILTRO

=FILTER(array, include, [if_empty])

• array è ciò che vuoi ottenere: una colonna, più colonne o l'intera tabella.
• include è una condizione con un VERO o FALSO per ogni riga di array, come B2:B7="North". Deve avere esattamente tante righe quante array. (Per filtrare le colonne, dagli invece un valore per colonna.)
• if_empty (se_vuoto) è ciò che va mostrato quando nessuna riga corrisponde. Senza di esso, un risultato vuoto è l'errore #CALC!.

FILTRO richiede Excel 2021, Excel 2024 o Microsoft 365. In Excel 2019 e precedenti mostra #NOME?, e lì per filtrare si usa il pulsante Filtro della scheda Dati. Anche Google Sheets ha FILTER, e lì ogni condizione può essere data anche come argomento separato.

I confronti di testo non distinguono le maiuscole: B2:B7="north" trova North. FILTRO mantiene le righe nel loro ordine originale; ordinare il risultato è un passaggio a parte, mostrato più avanti.

Filtrare in base al valore di una cella

Scrivere "North" nella formula significa modificare la formula ogni volta. Metti il valore in una cella e confronta invece con la cella. Scegli un'altra regione in F1 e il risultato la segue:

FormuleRipristina

E3fx

=FILTER(A2:C7,B2:B7=F1,"No sales")

	A	B	C	D	E	F	G
1	Name	Region	Sales		Region	North▾
2	Ann	North	120
3	Ben	South	80		Ann	North	120
4	Cara	North	200		Cara	North	200
5	Dan	East	150		Finn	North	60
6	Eve	South	95
7	Finn	North	60

Fai clic su una cella per vederne la formula. Cambia un numero o una formula e il foglio ricalcola.In Excel in italiano: =FILTRO(A2:C7;B2:B7=F1;"No sales")

West non ha righe, quindi sceglierla mostra il testo di if_empty, No sales.

I numeri funzionano allo stesso modo. C2:C7>=F1 con 100 in F1 tiene ogni riga con vendite di almeno 100, e C2:C7>F1 la rende strettamente maggiore.

FILTRO con più criteri (E)

Per tenere una riga solo quando due condizioni sono entrambe vere, moltiplicale. Questa formula restituisce le righe North con vendite oltre 100:

FormuleRipristina

E2fx

=FILTER(A2:C7,(B2:B7="North")*(C2:C7>100))

	A	B	C	D	E	F	G
1	Name	Region	Sales		Name	Region	Sales
2	Ann	North	120		Ann	North	120
3	Ben	South	80		Cara	North	200
4	Cara	North	200
5	Dan	East	150
6	Eve	South	95
7	Finn	North	60

Fai clic su una cella per vederne la formula. Cambia un numero o una formula e il foglio ricalcola.In Excel in italiano: =FILTRO(A2:C7;(B2:B7="North")*(C2:C7>100))

Ann (120) e Cara (200) passano. Finn è North ma il suo 60 non supera 100, quindi resta fuori.

Perché moltiplicare: ogni condizione è una colonna di VERO e FALSO, e nell'aritmetica VERO vale 1 e FALSO 0. Una riga ottiene 1 solo quando ogni fattore è 1, quindi * funziona come E. Ogni condizione vuole le sue parentesi, e puoi concatenarne quante vuoi: (B2:B7="North")*(C2:C7>100)*(C2:C7<500).

E() qui non funziona. E(B2:B7="North";C2:C7>100) riduce l'intero intervallo a un solo VERO o FALSO invece di uno per riga, quindi FILTRO riceve la forma sbagliata.

FILTRO con O

Somma le condizioni per tenere una riga quando almeno una è vera. Questa formula restituisce le righe North ed East:

FormuleRipristina

E2fx

=FILTER(A2:C7,(B2:B7="North")+(B2:B7="East"))

	A	B	C	D	E	F	G
1	Name	Region	Sales		Name	Region	Sales
2	Ann	North	120		Ann	North	120
3	Ben	South	80		Cara	North	200
4	Cara	North	200		Dan	East	150
5	Dan	East	150		Finn	North	60
6	Eve	South	95
7	Finn	North	60

Fai clic su una cella per vederne la formula. Cambia un numero o una formula e il foglio ricalcola.In Excel in italiano: =FILTRO(A2:C7;(B2:B7="North")+(B2:B7="East"))

Una riga che soddisfa entrambe le condizioni dà 2, e FILTRO tiene ogni riga il cui risultato non è 0, quindi la somma funziona come O. Puoi combinare le due: ((B2:B7="North")+(B2:B7="East"))*(C2:C7>100) significa (North o East) e oltre 100. Qui restituisce Ann, Cara e Dan.

FILTRO restituisce #CALC! quando nulla corrisponde

Quando nessuna riga passa, FILTRO non ha nulla da restituire. Senza un terzo argomento è l'errore #CALC!; con un terzo argomento, ottieni il tuo testo:

FormuleRipristina

E2fx

=FILTER(A2:A7,B2:B7="West")

	A	B	C	D	E	F
1	Name	Region	Sales		No if_empty	With if_empty
2	Ann	North	120		#CALC!	No match
3	Ben	South	80
4	Cara	North	200
5	Dan	East	150
6	Eve	South	95
7	Finn	North	60

#CALC! Il calcolo non ha un risultato, per esempio un FILTRO che non trova nulla.In Excel in italiano: =FILTRO(A2:A7;B2:B7="West")

E2 mostra #CALC! e F2 mostra No match. Cambia B3 da South a West ed entrambe le formule restituiscono Ben. Per non mostrare nulla, usa una stringa vuota: =FILTRO(A2:A7;B2:B7="West";"").

Questa tabella mostra anche come filtrare una sola colonna: array è A2:A7, quindi tornano solo i nomi. Per ottenere alcune colonne di una tabella ma non tutte, racchiudi il risultato in SCEGLI.COL (in inglese CHOOSECOLS): =SCEGLI.COL(FILTRO(A2:C7;B2:B7="North");1;3) restituisce nomi e vendite senza la regione. SCEGLI.COL richiede Microsoft 365 o Excel 2024.

Ordinare il risultato di FILTRO

FILTRO restituisce le righe nell'ordine in cui compaiono nella tabella. Racchiudila in DATI.ORDINA (in inglese SORT) per ordinare il risultato: qui le righe North ordinate per vendite, dalla più grande. Il 3 è la colonna del risultato in base a cui ordinare, e -1 significa decrescente.

FormuleRipristina

E2fx

=SORT(FILTER(A2:C7,B2:B7="North"),3,-1)

	A	B	C	D	E	F	G
1	Name	Region	Sales		Name	Region	Sales
2	Ann	North	120		Cara	North	200
3	Ben	South	80		Ann	North	120
4	Cara	North	200		Finn	North	60
5	Dan	East	150
6	Eve	South	95
7	Finn	North	60

Fai clic su una cella per vederne la formula. Cambia un numero o una formula e il foglio ricalcola.In Excel in italiano: =DATI.ORDINA(FILTRO(A2:C7;B2:B7="North");3;-1)

Cara (200) viene per prima, poi Ann (120) e Finn (60). Per restituire solo le prime righe, racchiudila ancora una volta in INCLUDI (in inglese TAKE): =INCLUDI(DATI.ORDINA(FILTRO(A2:C7;B2:B7="North");3;-1);2) tiene le prime due (INCLUDI richiede Microsoft 365 o Excel 2024). DATI.ORDINA e DATI.ORDINA.PER spiega le altre opzioni di ordinamento.

FILTRO sulle righe che contengono un testo

FILTRO non ha caratteri jolly, quindi B2:B7="*th*" cerca il testo letterale *th*. Per tenere le righe il cui nome contiene un certo testo, verifica ogni cella con RICERCA, che restituisce una posizione quando il testo viene trovato e un errore quando no, e racchiudila in VAL.NUMERO:

FormuleRipristina

E2fx

=FILTER(A2:C7,ISNUMBER(SEARCH("an",A2:A7)))

	A	B	C	D	E	F	G
1	Name	Region	Sales		Name	Region	Sales
2	Ann	North	120		Ann	North	120
3	Ben	South	80		Dan	East	150
4	Cara	North	200
5	Dan	East	150
6	Eve	South	95
7	Finn	North	60

Fai clic su una cella per vederne la formula. Cambia un numero o una formula e il foglio ricalcola.In Excel in italiano: =FILTRO(A2:C7;VAL.NUMERO(RICERCA("an";A2:A7)))

Restituisce Ann e Dan: RICERCA ignora le maiuscole, quindi "an" corrisponde anche all'An di Ann. Usa TROVA invece di RICERCA per una corrispondenza che distingue le maiuscole.

Esercizio: FILTRO con due condizioni

FormuleRipristina

E2fx

	A	B	C	D	E	F	G
1	Name	Region	Sales		Name	Region	Sales
2	Ann	North	120
3	Ben	South	80
4	Cara	North	200
5	Dan	East	150
6	Eve	South	95
7	Finn	North	60

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

Tocca a te: In E2, restituisci le righe (tutte e tre le colonne) dei venditori South con vendite oltre 85.

VerificaMostra la risposta

Esercizio: FILTRO da una cella, con un valore di riserva

FormuleRipristina

F3fx

	A	B	C	D	E	F
1	Name	Region	Sales		Region	North
2	Ann	North	120
3	Ben	South	80		Names
4	Cara	North	200
5	Dan	East	150
6	Eve	South	95
7	Finn	North	60

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

Tocca a te: In F3, elenca i nomi (solo la colonna A) dei venditori della regione scritta in F1. Se non ce ne sono, mostra None.

VerificaMostra la risposta

Errori comuni con FILTRO

• Intervalli di altezze diverse. =FILTRO(A2:C7;B2:B6="North") verifica 5 righe per una tabella di 6 righe, ed Excel restituisce #VALORE!. Fai iniziare e finire include sulle stesse righe di array.
• Zeri dove l'origine è vuota. FILTRO restituisce 0 per una cella vuota di array. Sostituisci le celle vuote con testo vuoto prima di filtrare: =FILTRO(SE(A2:C7="";"";A2:C7);B2:B7="North").
• Colonne intere. =FILTRO(A:C;B:B="North") funziona, ma se la formula stessa si trova nelle colonne da A a C fa riferimento a se stessa. Metti il risultato accanto alla tabella, oppure usa un intervallo fisso come A2:C1000.
• Virgolette attorno ai numeri. C2:C7>"100" confronta numeri con testo e non tiene nulla. Scrivi C2:C7>100.
• Aspettarsi il pulsante Filtro. FILTRO copia le righe che corrispondono in un altro punto e lascia la tabella com'è. Per nascondere righe nella tabella stessa, usa Dati > Filtro.