La funzione Filtro di Excel

Con questo post apro una serie dedicata alle funzioni matriciali di Excel, o almeno alle più utili, quelle che devi conoscere per semplificarti notevolmente la vita. In questo primo post vediamo la funzione FILTRO, come funziona, come si usa e le sue applicazioni più utili e interessanti.

Le formule sono strumenti di calcolo che oltre alle operazioni matematiche, usano funzioni per elaborare valori in celle e tabelle. Le funzioni sono scorciatoie, automazione, programmi che elaborano i parametri inseriti per restituire in modo veloce un risultato complesso.

Le funzioni matriciali elaborano intere matrici di dati per ottenere il risultato che stiamo cercando e possono restituire singoli valori come intere matrici di valori.

Negli ultimi anni in Excel 365 sono state introdotte nuove funzioni matriciali, diverse però dalle formule matriciali classiche. In questo primo post dedicato a queste funzioni matriciali dinamiche, parliamo della funzione Filtro, forse la funzione più utile tra quelle introdotte.

Come dice il nome questa funzione estrae una porzione filtrata di una basedati esistente secondo condizioni precise anche complesse, per consultare in modo agevole i dati o per creare una nuova basedati a cui agganciare altri strumenti di elaborazione o di visualizzazione con cui realizzare report o dashboard.

Le funzioni matriciali dinamiche di Excel

Le funzioni matriciali dinamiche sono riconoscibili per le seguenti caratteristiche:

espansione automatica: se il risultato della formula o funzione è una matrice, questa verrà estesa in automatico nelle celle necessarie, in basso e destra della cella in cui abbiamo inserito la formula; se nell’intervallo di celle previsto sono presenti celle con valori o formule, la formula restituirà un errore #Espansione e attenderà di essere spostata o che vengano rimosse o spostare le celle che ostacolano la visualizzazione della matrice.
riferimenti a matrici di dati: i parametri delle funzioni di solito si riferiscono non a singole celle, ma a interi campi, intere tabelle o parti di esse.
criteri multipli: in presenza di parametri che indicano condizioni, di solito permettono l’impostazione anche di criteri multipli, attraverso l’uso di operatori logici come * (AND) e + (OR), come le formule matriciali classiche.

Sintassi della Funzione Filtro

La sintassi della funzione filtro di Excel è la seguente:

=FILTRO(matrice, includi, [se_vuoto])

matrice: si riferisce all’intervallo di dati che desideri filtrare. Può essere un intervallo di celle, una colonna o una tabella intera. Per efficienza e semplicità si consiglia di usare tabelle excel per contenere i dati, ma si possono usare riferimenti classici o puntare a matrici restituite da altre funzioni matriciali dinamiche.
includi: questo parametro serve a indicare le condizioni del filtro, di solito in forma di espressioni che restituiscono “vero” o “falso”.

Ad esempio: C3:C300=AJ2 indica di filtrare le righe con valori uguali alla cella AJ2. Gli intervalli di celle indicati nelle condizioni devono avere le stesse dimensioni della matrice, cioè del primo parametro. Non è necessario che questi intervalli siano contenuti nella matrice precedente.

Le condizioni impostabili per il filtraggio dei dati possono essere multiple e complesse, cioè si possono usare molte espressioni collegate tra loro con operatori logici, citati più sopra.

[se_vuoto]: è un parametro opzionale che serve a specificare cosa deve restituire la formula se non ci sono risultati che soddisfano i criteri del filtro.

Classicamente si indicano testi tra virgolette, come ad es. “Nessun dato trovato”, o un valore numerico, per esempio zero.

Esempi della Funzione Filtro

Vediamo alcuni esempi dell’uso della funzione filtro:

semplice

=FILTRO(AnagraficaClienti; AnagraficaClienti[Nazione] =”Italia”; “Non Trovato”)

In questo esempio, la funzione filtra la tabella excel “AnagraficaClienti”, visualizzando solo i clienti italiani, cioè i record che nel campo “Nazione” presentano la stringa “Italia”.

La condizione impostata: AnagraficaClienti[Nazione] =”Italia”, indica che la funzione verificherà ogni record della tabella e se nella cella del campo “nazione” troverà “Italia” lo mostrerà nella matrice risultante.

Piuttosto semplice non credi?

condizioni multiple:

=FILTRO(FatturatoClienti; (FatturatoClienti[Cliente]=C3)*(FatturatoClienti[DataFattura]>D3); “Nessun record trovato”)

In questo secondo esempio, la funzione filtra la tabella excel “FatturatoClienti”, visualizzando i dati delle fatture di un singolo cliente con data successiva a una specifica data. Cliente e data sono inserite nelle celle C3 e D3 della pagina, probabilmente con l’aiuto di elenchi, controlli all’inserimento e date picker.

La condizione impostata: “(FatturatoClienti[Cliente]=C3) * (FatturatoClienti[DataFattura]>D3)”, specifica che la funzione verificherà ogni record della tabella e se nella cella del campo “cliente” troverà il nominativo presente nella cella C3 e se nella cella del campo “DataFattura” troverà una data successiva a quella indicata nella cella D3, allora mostrerà il record nella matrice risultante.

Da sottolineare che le espressioni sono 2, sono racchiuse tra parentesi e sono legate da un operatore, nel nostro caso ”*” che viene interpretato non come un moltiplicatore, ma come l’operatore logico “AND”. Quindi verrà restituito “vero” solo in presenza di entrambe le condizioni.

Gli operatori logici utilizzati sono “*” (AND) e “+” (OR) e possono essere concatenate più condizioni che grazie all’uso corretto delle parentesi e degli operatori, possono restituire condizioni multiple anche complesse.

Il Parametro Se_Vuoto

Cosa succede se la funzione non trova record che corrispondono alle condizioni impostate?

Che la formula restituirà un errore “#CALC!”.

È possibile evitarlo?

Certo, come spiegato sopra, il parametro se_vuoto è utile proprio per questo. Come già accennato il parametro viene usato per restituire una stringa esplicativa, o dove non è opportuno restituire un valore “neutro”, come uno 0.

In realtà il parametro può essere usato in modo più evoluto, per esempio inserendo una formula che può restituisce comunque i valori ricercati, attingendo per esempio da un’altra tabella, oppure che restituisce dati che si avvicinano a quelli richiesti o altro ancora.

Qual è lo scopo della funzione Filtro?

Come hai già capito la funzione filtro permette di estrarre in modo semplice ed efficiente una porzione di dati da una tabella, anche solo una lista di valori, per presentarla in una altra posizione. La matrice risultante può essere usata per la consultazione o come base per elaborazioni successive o per visualizzazioni.

In sostanza fornisce la stessa funzione del filtro automatico, anche se questo è localizzato sulla tabella, o del filtro avanzato che invece permette di ottenere filtri anche molto complessi e posizionarne il risultato dove vuoi tu.

Perché allora usare la funzione filtro?

Perché come hai visto è piuttosto semplice da usare.
Una volta impostato si aggiorna in automatico come tutte le formule.
Non devi spendere tempo per interagire con i controlli del filtro automatico o avanzato.
Perché le condizioni possono essere collegate a celle della pagina con controlli ed elenchi che permettono di fornire alla funzione i parametri in modo comodo ed efficiente.

Questo permette non solo di applicare filtri in pochi secondi, ma di creare vere e proprie maschere di ricerca anche a parametri multipli.

Errori restituiti dalla Funzione Filtro

L’utilizzo della funzione Filtro di Excel può talvolta generare errori. Comprendere questi errori e sapere come risolverli è fondamentale se vuoi usarla in modo esteso.

Quali sono gli errori principali che possono comparire quando si utilizza la funzione Filtro:

#ESPANSIONE!: come citato all’inizio si verifica per tutte le funzioni matriciali dinamiche restituiscono una matrice di dati, quando non c’è spazio sufficiente per espandere i risultati della matrice dinamica.
#CALC!: questo errore appare quando non ci sono dati che soddisfano i criteri specificati.
#VALORE!: questo invece compare quando la formula non è impostata correttamente, per esempio perché non abbiamo rispettato la regola della grandezza degli intervalli, o non abbiamo usato le parentesi dove necessario, o altro ancora.

Come si risolvono gli errori?

#ESPANSIONE!

Questo errore si risolve:

liberando le celle necessarie per la visualizzazione dell’intera matrice
spostando le celle che ostacolano la visualizzazione in un’altra posizione, per esempio usando CTRL+X e CTRL+V
spostando la formula in una posizione adeguata o in un foglio dedicato

In realtà è sempre opportuno valutare anticipatamente dove posizionare la formula in relazione alla matrice restituita. Una soluzione sempre valida è dedicarle un foglio vuoto.

#CALC!

Questo errore si risolve e previene usando il parametro “se_vuoto”, come già spiegato sopra. Quindi impara a usarla sempre.

#VALORE!

Questo errore si risolve facendo una verifica della formula per individuare la causa dell’errore. Le cause più frequenti sono:

l’indicazione nei parametri di intervalli della tabella dei dati diversi come dimensione, tipico è l’indicazione della tabella con le intestazioni ([#Tutti]) mentre l’indicazione delle colonne nelle condizioni è fatta senza intestazioni; che siano riferimenti tradizionali o delle tabelle Excel è necessario indicare dimensioni coerenti;
l’assenza di parentesi per racchiudere le condizioni in presenza di condizioni multiple; all’inizio spesso ci si dimentica di racchiudere le espressioni tra parentesi;
operatori o riferimenti sbagliati o parametri invertiti.

Uso avanzato della funzione filtro

La funzione filtro() può essere usata in modo avanzato, questo significa per esempio:

usare serie di condizioni con logica And e Or ampie e complesse, che possono anche prevedere l’uso di tabelle di appoggio;
usarla per preparare una basedati adeguata all’analisi successiva o al report che si vuole realizzare, quindi realizzata filtrando anche le colonne con Scegli.Col(), ottenuta dall’unione con i dati presi da altre tabelle con funzioni come Stack.Oriz(), Cerca.X(), Indiretto(), Unici(), Dati.Ordina(), Filtro() stesso e altre ancora;
usare la funzione Filtro() all’interno di una formula-funzione più grande, come parametro per ottenere un valore o una matrice di valori; mi riferisco a funzioni comuni, come Cerca.x(), Somma.più.se() o Conta.più.se(), ma anche funzioni avanzate come Raggruppaper() e Pivotper() o altre;
usare la funzione Filtro() in modo annidato, per offrire una o più alternative nel caso non restituisca record.

E altro ancora. Parleremo più approfonditamente dell’uso avanzato della funzione Filtro() in un altro post dedicato, dopo aver spiegato le altre funzioni citate sopra.

Condizioni complesse

Abbiamo visto esempi di condizioni multiple, dove due e più espressioni vengono racchiuse tra parantesi e correlate l’una all’altra tramite operatori logici And e Or espressi nella formula come “*” e “+”.

Cosa si intende con condizioni “complesse”?

La stessa cosa, ma con numerose condizioni impostate secondo precisi bisogni o in modo da rendere la formula dinamica attraverso controlli esterni.

Per esempio, per ottenere l’elenco degli articoli a magazzino da gestire si può usare una funzione come questa:

=FILTRO(bdGiacenze; (bdGiacenze[DataScadenza]=Oggi()-60)+(bdGiacenze[Stato]=”Smaltire”)+(bdGiacenze[Categoria]=”Campioni”)+((bdGiacenze[DataCarico]>Oggi()-3)*(bdGiacenze[Posizione]=”Transito”)) +((bdGiacenze[NuovoProdotto]=Vero)*(bdGiacenze[Collaudo]=Falso)); “Nessun record trovato”)

In questo esempio, la funzione filtra i record delle giacenze per estrarre quelli che richiedono attività da parte degli operatori, come i prodotti in scadenza, quelli da smaltire, quelli in transito da movimentare, eccetera.

Le condizioni sembrano complesse, ma leggendo con maggiore attenzione sono solo le condizioni delle diverse tipologie che vogliamo visualizzate nell’elenco, collegate da “+” operatore Or. Importante in questi casi è impostare le parentesi con precisione. In pratica dove una tipologia richiede 2 o più condizioni, è necessario racchiuderle in una coppia aggiuntiva di parentesi.

Come impostare una maschera di ricerca con la funzione filtro

Un uso complesso della funzione filtro è il suo impiego nella realizzazione di una maschera di ricerca su pagina. Questa in sostanza prevede di collegare i riferimenti inseriti nella funzione ai controlli su pagina con cui l’operatore indica uno o più parametri di ricerca, per esempio: date, categorie, importi, eccetera.

I controlli non sono altro che celle impostate con elenchi della convalida estratti dalla stessa basedati e controlli all’inserimento. La relativa funzione filtro potrebbe essere questa:

=FILTRO(TabellaDatiPratiche;INDIRETTO(“TabellaDatiPratiche[” & C8 & “]”)=D8;””)

In questo esempio molto semplice, la funzione filtro() permette di impostare 2 parametri, uno per indicare il valore da cercare e uno per indicare il campo in cui cercarlo. In questo caso la complicazione non è nella formula sopra indicata, ma nell’impostazione di elenchi a comparsa della convalida dei dati dipendenti tra loro.

La condizione impostata: “INDIRETTO(“TabellaDatiPratiche[” & C8 & “]”)=D8”, specifica che la funzione verificherà ogni record della tabella e se nella cella del campo indicato nella cella C8 troverà il valore impostato nella cella D8. La funzione Indiretto() è usata per rendere dinamico il riferimento alla tabella Excel. Se non la conosci ti invito a leggere i post o la guida alle funzioni che trovi sul sito.

Uso della funzione filtro all’interno di una funzione di elaborazione

Come accennato in precedenza, la funzione filtro è utile per estrarre dati da basedati più grandi e preparare basedati intermedie più comode per la preparazione di report, visualizzazioni interattive o altro.

Nella maggior parte dei casi, sempre quando alla stessa basedati vengono collegati più strumenti, formule e pivot, la funzione viene posizionata in una pagina dedicata e gli strumenti collegati indicando i riferimenti necessari.

In certi casi, quando il calcolo è singolo, la funzione filtro può essere indicata all’interno di formule o funzioni di elaborazione. Vedremo alcuni esempi nel post sull’uso avanzato della funzione Filtro. Per ora ti invito a non usarla in questo modo, soprattutto in campi calcolati, in quanto Filtro() è una funzione che richiede notevoli risorse e replicarla in migliaia di righe può incidere sulle prestazioni del foglio.

Uso della funzione filtro per l’estrazione di elenchi

Come abbiamo visto nei post dedicati alla funzione Unici() e nei post dedicati alla convalida dei dati, di solito per generare elenchi non è necessaria la funzione Filtro() a meno che l’elenco non abbiamo bisogno di filtrarlo. In tal caso Filtro() si rivela una soluzione comoda ed efficiente, permettendo di indicare un solo campo come matrice e di indicare nelle espressioni delle condizioni campi diversi della stessa tabella.

Per esempio:

=FILTRO(AnagraficaArticoli[Descrizione]; (AnagraficaArticoli[Obsoleto]=”No”)*((AnagraficaArticoli[Categoria]=D4)+(AnagraficaArticoli[Categoria]=E4)); “Nessun record trovato”)

In questo esempio, la funzione filtra il campo Descrizione dell’Anagrafica degli articoli, visualizzando solo le descrizioni degli articoli in vendita delle categorie indicate. Le categorie vengono inserite nelle celle D4 e E4 della pagina, con l’aiuto di elenchi, e se può indicare uno o due a seconda del bisogno.

La condizione impostata: “(AnagraficaArticoli[Obsoleto]=”No”)*((AnagraficaArticoli[Categoria]=D4)+(AnagraficaArticoli[Categoria]=E4))”, specifica che la funzione verificherà ogni record della tabella e se nella cella del campo “obsoleto” troverà “No” e se nella cella del campo “Categoria” troverà almeno una delle due presenti nelle celle indicate, allora mostrerà il record nella matrice risultante. Ti faccio notare l’uso dell’operatore “+” per indicare l’alternativa.

L’elenco ottenuto può essere richiamato dove serve, in formule e funzioni o come semplice riferimento, tramite indicazione della cella della formula con l’aggiunta del simbolo cancelletto “#”. Per esempio: C7#.

Come si filtrano le colonne?

Una delle domande più frequenti degli studenti è: la funzione filtro permette di filtrare le righe, ma come filtro le colonne che voglio estrarre?

Ci sono vari modi, ma il più semplice è usare la funzione Scegli.Col() che permette appunto di visualizzare solo le colonne indicate. Quindi non devi fare altro che racchiudere la funzione filtro in Scegli.Col() e indicare nei parametri le colonne che vuoi vedere.

In realtà sarebbe più corretto filtrare le colonne a monte, ma approfondiremo l’argomento nei prossimi post.

Conclusioni

La funzione filtro è una delle funzioni di Excel 365 che meritano di essere apprese e usate in modo esteso. Come abbiamo visto con filtro possiamo ottenere un elenco filtrato sempre aggiornato di una basedati, ottima soluzione per realizzare strumenti di controllo e consultazione per le nostre attività.

Ma come abbiamo visto la funzione filtro permette di ottenere basedati intermedie autoaggiornanti, che rendono più semplice la preparazione di report e altri strumenti, liste dinamiche per i nostri elenchi, semplificano in diversi casi le funzioni avanzate e permette di realizzare maschere di ricerca efficienti e comode.

Approfondiremo l’uso avanzato della funzione filtro in un altro post e nei prossimi vedremo altre funzioni dinamiche matriciali che ti semplificano la vita e che dovresti conoscere. A presto.

Se vuoi imparare a realizzare i tuoi fogli automatizzati posso insegnartelo

Puoi farlo imparando l’automazione di Excel. Anche per questo ho realizzato questo corso:

Vuoi una mano a realizzare o migliorare i tuoi strumenti?

Se hai bisogno di aiuto per realizzare o migliorare i tuoi strumenti forse posso darti una mano:

PS: Se il post ti è piaciuto, condividi questa pagina con gli amici e sui social.
Puoi farlo attraverso i pulsanti qui di seguito.

La funzione Filtro di Excel