Come funzionano i filtri in excel

di formule In questa breve lezione imparerai come filtrare in Excel in modo dinamico con le formule. Esempi per filtrare duplicati, celle contenenti un determinato testo, con più criteri e altro ancora.

Come si filtra di solito in Excel? Nella maggior parte dei casi, utilizzando il filtro automatico e in scenari più complessi con il filtro avanzato. Essendo veloci e potenti, questi metodi hanno uno svantaggio significativo: non si aggiornano automaticamente quando i dati cambiano, il che significa che dovresti ripulire e filtrare di nuovo. L'introduzione della funzione FILTRO in Excel 365 diventa un'alternativa tanto attesa alle funzionalità convenzionali. A differenza di loro, le formule di Excel si ricalcolano automaticamente ad ogni modifica del foglio di lavoro, quindi dovrai impostare il filtro solo una volta!

Funzione FILTRO

di Excel La funzione FILTRO di Excel viene utilizzata per filtrare un intervallo di dati in base al criteri specificati.

La funzione appartiene alla categoria delle funzioni Dynamic Arrays. Il risultato è una matrice di valori che si riversa automaticamente in un intervallo di celle, a partire dalla cella in cui si immette una formula.

La sintassi della funzione FILTRO è la seguente:

Dove:

• Array (obbligatorio) - l'intervallo o l'array di valori che si desidera filtrare.
• Include (obbligatorio) - i criteri forniti come array booleano (valori TRUE e FALSE).
  La sua altezza (quando i dati sono in colonne) o larghezza (quando i dati sono in righe) deve essere uguale a quella dell'argomento della matrice.
• If_empty (facoltativo): il valore da restituire quando nessuna voce soddisfa i criteri.

La funzione FILTRO è disponibile solo in Excel per Microsoft 365 ed Excel 2021. In Excel 2019, Excel 2016 e versioni precedenti, non è sostenuto.

Formula di base del FILTRO di Excel

Per cominciare, discutiamo un paio di casi molto semplici solo per capire meglio come funziona una formula di Excel per filtrare i dati.

Dal set di dati sottostante, supponendo di voler estrarre i record con un valore specifico nella colonna Gruppo , ad esempio il gruppo C. Per farlo, forniamo l'espressione B2:B13="C" all'argomento include, che produrrà un array booleano richiesto, con TRUE corrispondente ai valori "C".

In pratica, è più comodo inserire i criteri in una cella separata, ad esempio F1, e utilizzare un riferimento di cella invece di codificare il valore direttamente nella formula:

a differenza della funzione Filtro di Excel, la funzione non apporta alcuna modifica ai dati originali. Estrae i record filtrati nel cosiddetto intervallo di spill (E4:G7 nello screenshot qui sotto), a partire dalla cella in cui viene inserita la formula:

Se nessun record corrisponde ai criteri specificati, la formula restituisce il valore inserito nel if_empty

Se in questo caso non si preferisce restituire nulla, fornire una stringa vuota ("") per l'ultimo argomento:

Nel caso in cui i dati siano organizzati orizzontalmente da sinistra a destra come mostrato nello screenshot seguente, anche la funzione FILTRO funzionerà bene. Assicurati solo di definire gli intervalli appropriati per l'array e di includere gli argomenti, in modo che l'array di origine e l'array booleano abbiano la stessa larghezza:

Funzione FILTRO di Excel - note sull'utilizzo

Per filtrare efficacemente in Excel con le formule, ecco un paio di punti importanti da tenere presenti:

• La funzione FILTRO distribuisce automaticamente i risultati verticalmente o orizzontalmente nel foglio di lavoro, a seconda di come sono organizzati i dati originali. Quindi, assicurati di avere sempre abbastanza spazio vuoto celle in basso e a destra, altrimenti otterrai un errore di #SPILL.
• I risultati della funzione FILTRO di Excel sono dinamici, il che significa che si aggiornano automaticamente quando i valori nel set di dati originale cambiano. Tuttavia, l'intervallo fornito per l'argomento della matrice non viene aggiornato quando vengono aggiunte nuove voci ai dati di origine. Se si desidera che l'array venga ridimensionato automaticamente, convertirlo in una tabella di Excel e creare formule con riferimenti strutturati oppure creare un intervallo denominato dinamico.

Come filtrare in Excel - esempi di formule

Ora che sai come funziona una formula di filtro di base di Excel, è il momento di ottenere alcune informazioni su come potrebbe essere estesa per risolvere attività più complesse.

Filtra con più criteri (logica AND)

Per filtrare i dati con più criteri, fornisci due o più espressioni logiche per l'argomento include:

L'operazione di moltiplicazione elabora gli array con la logica AND , assicurando che vengano restituiti solo i record che soddisfano tutti i criteri. Tecnicamente, funziona in questo modo:

il risultato di ogni espressione logica è un array di valori booleani, dove TRUE equivale a 1 e FALSE a 0. Quindi, gli elementi di tutti gli array nelle stesse posizioni vengono moltiplicati. Poiché moltiplicando per zero si ottiene sempre zero, solo gli elementi per i quali tutti i criteri sono TRUE entrano nell'array risultante e, di conseguenza, vengono estratti solo quegli elementi.

Gli esempi seguenti mostrano questa formula generica in azione.

Esempio 1. Filtra più colonne in Excel

Estendendo ulteriormente la nostra formula di base FILTER di Excel, filtriamo i dati in base a due colonne: Gruppo (colonna B) e Vittorie (colonna C).

Per questo, impostiamo secondo i seguenti criteri: digitare il nome del gruppo target in F2 ( criterio1 ) e il numero minimo richiesto di vittorie in F3 ( criterio2 ).

Dato che i nostri dati di origine sono in A2:C13 ( array ), i gruppi sono in B2:B13 ( range1 ) e le vittorie sono in C2:C13 ( range2 ), la formula assume questa forma:

Come risultato, si ottiene un elenco di giocatori nel gruppo A che hanno ottenuto 2 o più vittorie:

Esempio 2. Filtra i dati tra le date

Prima di tutto, va notato che non è possibile creare una formula generica per filtrare per data in Excel. In situazioni diverse, sarà necessario creare criteri in modo diverso, a seconda che si desideri filtrare in base a una data specifica, al mese o all'anno. Lo scopo di questo esempio è quello di illustrare l'approccio generale.

Ai nostri dati di esempio, aggiungiamo un'ulteriore colonna contenente le date dell'ultima vincita (colonna D). E ora, noi estrarrà le vincite avvenute in un determinato periodo, diciamo tra il 17 maggio e il 31 maggio.

Si noti che in questo caso, entrambi i criteri si applicano allo stesso intervallo:

dove G2 e G3 sono le date tra cui filtrare.

Filtrare con più criteri (logica OR)

Per estrarre i dati in base a più condizioni OR, si utilizzano anche espressioni logiche come illustrato negli esempi precedenti, ma invece di moltiplicarle, le si somma. Quando gli array booleani restituiti dalle espressioni vengono sommati, l'array risultante avrà 0 per le voci che non soddisfano alcun criterio (cioè tutti i criteri sono FALSE) e tali voci verranno filtrate. Verranno estratte le voci per le quali almeno un criterio è TRUE.

Ecco la formula generica per filtrare le colonne con la logica OR:

Come un Ad esempio, estraiamo un elenco di giocatori che hanno questo o quel numero di vittorie.

Supponendo che i dati di origine siano in A2:C13, le vittorie siano in C2:C13 e i numeri di vincita di interesse siano in F2 e F3, la formula sarebbe la seguente:

Come risultato, sai quali giocatori hanno vinto tutte le partite (4) e quali non ne hanno vinta nessuna (0):

filtra in base a più criteri AND e OR

In una situazione in cui è necessario applicare entrambi i tipi di criteri, ricorda questa semplice regola: unisci i criteri AND con l'asterisco (*) e i criteri OR con il segno più (+).

Ad esempio, per restituire un elenco di giocatori che hanno un determinato numero di vittorie (F2) E appartengono al gruppo menzionato in E2 O E3, costruisci la seguente catena di espressioni logiche:

E otterrai il seguente risultato:

Come filtrare i duplicati in Excel

Quando si lavora con fogli di lavoro di grandi dimensioni o si combinano dati da fonti diverse, c'è spesso la possibilità che alcuni duplicati si intrufolino.

Se stai cercando di filtrare i duplicati ed estrarre elementi unici, usa la funzione UNIQUE come spiegato nel tutorial linkato sopra.

Se il tuo obiettivo è filtrare i duplicati , cioè estrarre le voci che si verificano più di una volta, usa la funzione FILTER insieme a COUNTIFS.

L'idea è quella di ottenere i conteggi delle occorrenze per tutti i record ed estrarre quelli maggiori di 1. Per ottenere i conteggi, fornisci lo stesso intervallo per ogni coppia criteria_range / criteri di COUNTIFS in questo modo:

esempio, per filtrare le righe duplicate dai dati in A2:C20 in base ai valori in tutte e 3 le colonne, ecco la formula da utilizzare:

Come filtrare gli spazi vuoti in Excel

Una formula per filtrare le celle vuote è, infatti, una variante della formula FILTRO di Excel con più criteri AND. In questo caso, controlliamo se tutte (o in particolare) le colonne contengono dati ed escludiamo le righe in cui almeno una cella è vuota. Per identificare le celle non vuote, si utilizza l'operatore "diverso da" (<>) insieme a una stringa vuota ("") in questo modo:

Con i dati di origine in A2:C12, per filtrare le righe contenenti una o più celle vuote, viene inserita la seguente formula in E3:

Filtrare le celle contenenti testo specifico

Per estrarre le celle che contengono un determinato testo, è possibile utilizzare la funzione FILTRO insieme al classico Se la cella contiene una formula:

Ecco come funziona:

• La funzione SEARCH cerca una stringa di testo specificata in un determinato intervallo e restituisce un numero (la posizione del primo carattere) o #VALUE! Errore (testo non trovato).
• La funzione NUM.VAL converte tutti i numeri in TRUE e gli errori in FALSE e passa la matrice booleana risultante all'argomento include della funzione FILTER.

Per questo esempio, abbiamo aggiunto i cognomi dei giocatori in B2:B13, digitato la parte del nome che vogliamo trovare in G2 e quindi utilizzato la seguente formula per filtrare i dati:

Come risultato, la formula recupera i due cognomi contenenti "han":

Filtra e calcola (Somma, Media, Min, Max, ecc.)

Una cosa interessante della funzione FILTRO di Excel è che non solo può Estrai i valori con le condizioni, ma riepiloga anche i dati filtrati. A tale scopo, combinare FILTER con funzioni di aggregazione come SUM, AVERAGE, COUNT, MAX o MIN.

Ad esempio, per aggregare i dati per un gruppo specifico in F1, utilizzare le seguenti formule:

Vittorie totali:

Vittorie medie:

Vittorie massime: Vittorie massime

: Vittorie minime:

Prestare attenzione al fatto che, in tutte le formule, utilizziamo zero per l'argomento if_empty, quindi le formule restituirebbero 0 se non vengono trovati valori che soddisfano i criteri.

Formula FILTRO con distinzione tra maiuscole e minuscole

Una formula FILTRO standard di Excel non fa distinzione tra maiuscole e minuscole, ovvero non fa distinzione tra caratteri minuscoli e maiuscoli. Per distinguere le maiuscole e minuscole del testo, nidificare la funzione EXACT nell'argomento include. Questo costringerà FILTER a fare un test logico in modo sensibile alle maiuscole:

Supponiamo che tu abbia entrambi i gruppi A e a e desideri estrarre i record in cui il gruppo è la "a" minuscola. Per farlo, usa la seguente formula, dove A2:C13 sono i dati di origine e B2:B13 sono i gruppi da filtrare:

Come al solito, puoi inserire il gruppo di destinazione in una cella predefinita, ad esempio F1, e utilizzare quel riferimento di cella invece del testo hardcoded:

Come FILTRARE i dati e restituire solo colonne specifiche

Per la maggior parte, Filtrare tutte le colonne con una singola formula è ciò che gli utenti di Excel desiderano. Tuttavia, se la tabella di origine contiene decine o addirittura centinaia di colonne, è consigliabile limitare i risultati a pochi dei più importanti.

Esempio 1. Filtrare alcune colonne adiacenti

Nella situazione in cui si desidera che alcune colonne adiacenti vengano visualizzate in un risultato FILTER, includere solo tali colonne nell'array perché è questo argomento che determina quali colonne da restituire.

Nell'esempio di formula FILTER di base, supponiamo che tu voglia restituire le prime 2 colonne ( Nome e Gruppo ). Quindi, fornisci A2:B13 per l'argomento della matrice:

Come risultato, otteniamo un elenco di partecipanti del gruppo target definito in F1:

Esempio 2. Filtrare le colonne non adiacenti

Per fare in modo che la funzione FILTRO restituisca colonne non contigue, utilizzare questo trucco intelligente:

1. creare una formula FILTRO con le condizioni desiderate utilizzando l'intera tabella per l'array .
2. Nidificare la formula precedente all'interno di un'altra funzione FILTRO. Per configurare la funzione "wrapper", utilizzare una costante di matrice di valori TRUE e FALSE o 1 e 0 per l'argomento include, dove TRUE (1) contrassegna le colonne da mantenere e FALSE (0) contrassegna le colonne da escludere.

Ad esempio, per restituire solo Nomi (1a colonna) e Wins (3a colonna), stiamo usando {1,0,1} o {TRUE,FALSE,TRUE} per l'argomento include della funzione FILTER esterna:

O

Come limitare il numero di righe restituite dalla funzione FILTER

Se la tua formula FILTER trova un bel po' di risultati, ma il tuo foglio di lavoro ha uno spazio limitato e non puoi eliminare i dati sottostanti, è quindi possibile limitare il numero di righe restituite dalla funzione FILTER.

Vediamo come funziona su un esempio di una semplice formula che estrae i giocatori dal gruppo target in F1:

La formula sopra produce tutti i record che trova, 4 righe nel nostro caso. Ma supponiamo che tu abbia spazio solo per due. Per restituire solo le prime 2 righe trovate, è necessario eseguire questa operazione:

• Inserire la formula FILTRO nell'argomento array della funzione INDICE .
• Per l'argomento row_num di INDICE, utilizzare un array verticale costante come {1; 2}. Determina quante righe restituire (2 nel nostro caso).
• Per l'argomento column_num, utilizzare una costante di matrice orizzontale come {1,2,3}. Specifica le colonne da restituire (le prime 3 colonne in questo esempio).
• Per evitare possibili errori quando non vengono trovati dati corrispondenti ai criteri, è possibile racchiudere la formula nella funzione SE.ERRORE.

La formula completa assume la seguente forma:

quando si lavora con tabelle di grandi dimensioni, la scrittura manuale delle costanti di matrice può essere piuttosto complessa. Nessun problema, la funzione SEQUENCE può generare automaticamente i numeri sequenziali:

la prima SEQUENCE genera un array verticale contenente tanti numeri sequenziali quanti specificati nel primo (e unico) argomento. La seconda SEQUENCE utilizza la funzione COLUMNS per contare il numero di colonne nel set di dati e produce una matrice orizzontale equivalente.

La funzione FILTRO di Excel non funziona

Nella situazione in cui la formula FILTRO di Excel genera un errore, molto probabilmente si verificherà uno dei seguenti:

Errore #CALC!

Si verifica se il if_empty facoltativo Il motivo è che attualmente Excel non supporta gli array vuoti. Per evitare tali errori, assicurarsi di definire sempre il valore if_empty nelle formule.

#VALUE errore

Si verifica quando l'array e l'argomento include hanno dimensioni incompatibili.

#N/A, #VALUE, ecc.

Possono verificarsi errori diversi se un valore nell'argomento include è un errore o non può essere convertito in un valore booleano.

#NAME errore

Si verifica quando si tenta di utilizzare FILTER in una versione precedente di Excel. Ricorda che si tratta di una nuova funzione, disponibile solo in Office 365 ed Excel 2021.

Nel nuovo Excel, si verifica un errore di #NAME se si digita accidentalmente in modo errato il nome della funzione.

Errore #SPILL

Nella maggior parte dei casi, questo errore si verifica se una o più celle nell'intervallo di spill non sono completamente vuote. Per risolverlo, basta cancellare o eliminare le celle non vuote. Per indagare e risolvere altri casi, consultare #SPILL! errore in Excel: cosa significa e come risolverlo.

#REF! si

verifica quando una formula FILTER viene utilizzata tra diverse cartelle di lavoro e la cartella di lavoro di origine viene chiusa.

Ecco come filer i dati in Excel in modo dinamico. Vi ringrazio per la lettura e spero di vedervi sul nostro blog la prossima settimana!

Scarica la cartella di lavoro pratica

Filtra in Excel con formule (file .xlsx)

Potrebbe interessarti anche