Come utilizzare la funzione FILTRO di Excel

Office 365 offre alcune fantastiche funzionalità come XLOOKUP, SORT e FILTER.

Quando si filtrano i dati in Excel, nel mondo precedente a Office 365, ci siamo basati principalmente sui filtri integrati di Excel o su filtri più avanzati o formule SUMPRODUCT complesse.Questa è spesso una soluzione complicata se devi filtrare parte del set di dati.

Ma con la nuova funzione FILTER, ora è davvero facile filtrare rapidamente parti di un set di dati in base alle condizioni.

In questo tutorial, ti mostrerò quanto sia fantastica la nuova funzione FILTRO e alcune cose utili che puoi fare con essa.

Ma prima di entrare nell'esempio, diamo una rapida occhiata alla sintassi della funzione FILTRO.

Se vuoi ottenere queste nuove funzionalità in Excel, puoi farloEsegui l'aggiornamento a Office 365(Unisciti al programma interno per accedere a tutte le funzionalità/formule)

Funzione filtro Excel – Sintassi

Quella che segue è la sintassi della funzione FILTRO:

=FILTRO(array,include,[se_vuoto])

• schieramento - questo è l'intervallo di celle in cui hai i dati e desideri filtrare alcuni dati da esso
• includere - Questa è la condizione che indica alla funzione quali record filtrare
• [se_vuoto] – Questo è un parametro facoltativo in cui è possibile specificare cosa restituire se la funzione FILTRO non trova risultati.Di default (quando non specificato) restituisce #CALC!errore

Ora diamo un'occhiata ad alcuni incredibili esempi di funzioni di filtro e cosa può fare che prima era molto complicato senza di essa.

Esempio 1: filtrare i dati in base a una condizione (regione)

Supponiamo di avere un set di dati come quello di seguito e di voler filtrare solo tutti i record negli Stati Uniti.

Ecco la formula FILTRO che fa questo:

=FILTRO($A$2:$C$11,$B$2:$B$11="US")

La formula precedente prende il set di dati come una matrice e la condizione è $B$2:$B$11="US"

Questa condizione farà sì che la funzione FILTRO esamini ogni cella nella colonna B (celle che hanno l'intervallo) e filtri solo i record che soddisfano questa condizione.

Inoltre, in questo esempio, metto i dati originali e filtrati sullo stesso foglio, ma puoi anche inserirli in fogli separati o persino cartelle di lavoro.

La funzione di filtro restituisce una matrice dinamica di risultati (il che significa che invece di restituire un valore, restituisce una matrice che trabocca in altre celle).

Per questo, è necessario disporre di un'area in cui il risultato è vuoto.C'è già qualcosa in qualsiasi cella nell'intervallo (E2:G5 in questo esempio) e la funzione ti darà l'errore #SPILL.

Inoltre, poiché si tratta di un array dinamico, non è possibile modificare parte del risultato.È possibile eliminare l'intero intervallo con il risultato o la cella E2 (dove viene inserita la formula).Entrambi elimineranno l'intero array di risultati.Ma non puoi cambiare nessuna singola cella (o eliminarla).

Nella formula sopra, ho codificato il valore dell'intervallo, ma puoi anche inserirlo in una cella e fare riferimento a quella cella con il valore dell'intervallo.

Ad esempio, nell'esempio seguente, ho il valore dell'intervallo nella cella I2 e quindi faccio riferimento ad esso nella formula:

=FILTER($A$2:$C$11,$B$2:$B$11=I1)

Questo rende la formula ancora più utile, ora devi solo cambiare il valore dell'intervallo nella cella I2 e il filtro cambierà automaticamente.

Puoi anche avere un menu a discesa nella cella I2 in cui puoi semplicemente effettuare una selezione e aggiornerà immediatamente i dati filtrati.

Esempio 2: filtrare i dati in base a un criterio (maggiore o minore di)

È inoltre possibile utilizzare gli operatori di confronto nelle funzioni di filtro ed estrarre tutti i record maggiori o minori di un determinato valore.

Ad esempio, supponiamo di avere il set di dati mostrato di seguito e di voler filtrare tutti i record con vendite superiori a 10000.

La seguente formula può farlo:

=FILTER($A$2:$C$11,($C$2:$C$11>10000))

L'argomento array si riferisce all'intero set di dati, in questo caso la condizione ($C$2:$C$11>10000).

La formula controlla ogni record per il valore nella colonna C.Se il valore è maggiore di 10000 viene filtrato, altrimenti viene ignorato.

Se vuoi ottenere tutti i record inferiori a 10000, puoi utilizzare la seguente formula:

=FILTER($A$2:$C$11,($C$2:$C$11<10000))

Puoi anche diventare più creativo con la formula FILTRO.Ad esempio, se desideri filtrare i primi tre record in base alle vendite, puoi utilizzare la seguente formula:

=FILTER($A$2:$C$11,($C$2:$C$11>=LARGE(C2:C11,3)))

La formula precedente utilizza la funzione GRANDE per ottenere il terzo valore più grande nel set di dati.Quindi utilizzare questo valore in una condizione della funzione FILTRO per ottenere tutti i record con vendite maggiori o uguali al terzo valore più grande.

Esempio 3: filtraggio dei dati utilizzando più condizioni (AND)

Si supponga di disporre del seguente set di dati e di voler filtrare tutti i record negli Stati Uniti con un valore di vendita maggiore di 10000.

Questa è una condizione AND, devi controllare due cose: la regione deve essere negli Stati Uniti e le vendite devono essere superiori a 10000.Se viene soddisfatta una sola condizione, i risultati non devono essere filtrati.

Ecco la formula di filtro che filtrerà i record con gli Stati Uniti come regione e con oltre 10000 vendite:

=FILTER($A$2:$C$11,($B$2:$B$11="US")*($C$2:$C$11>10000))

請注意，標準（稱為包含參數）是 ($B$2:$B$11=”US”)*($C$2:$C$11>10000)

Poiché sto usando due condizioni e ho bisogno che entrambe siano vere, ho usato l'operatore di moltiplicazione per combinare le due condizioni.Questo restituisce una matrice di 0 e 1, dove 1 viene restituito solo se vengono soddisfatte entrambe le condizioni.

Se non ci sono record corrispondenti, la funzione restituirà #CALC!Errore.

Se vuoi restituire qualcosa di significativo (non un errore), puoi usare una formula come questa:

=FILTER($A$2:$C$11,($B$2:$B$11="USA")*($C$2:$C$11>10000),"Nothing Found")

Qui, ho usato "non trovato" come terzo parametro, che viene utilizzato quando non viene trovato alcun record corrispondente.

Esempio 4: filtraggio dei dati utilizzando più criteri (OR)

È inoltre possibile modificare il parametro "contiene" nella funzione FILTRO per verificare la presenza di condizioni OR (in cui una determinata condizione può essere vera).

Ad esempio, supponiamo che tu abbia il set di dati mostrato di seguito e desideri filtrare i record in cui il paese è gli Stati Uniti o il Canada.

Ecco la formula per farlo:

=FILTER($A$2:$C$11,($B$2:$B$11="US")+($B$2:$B$11="Canada"))

Nota che nella formula sopra, sto solo aggiungendo le due condizioni usando l'operatore di addizione.Poiché ciascuna di queste condizioni restituisce un array di VERO e FALSO, posso aggiungere un array combinato che sarà VERO se viene soddisfatta una delle condizioni.

Un altro esempio potrebbe essere quando si desidera filtrare tutti i record in cui il paese è gli Stati Uniti o il valore delle vendite è superiore a 10000.

La seguente formula farà questo:

=FILTER($A$2:$C$11,($B$2:$B$11="US")+(C2:C11>10000))

NOTA: quando si utilizza la condizione AND nella funzione FILTRO, utilizzare l'operatore di moltiplicazione (*) e quando si utilizza la condizione OR, utilizzare l'operatore di addizione (+).

Esempio 5: filtrare i dati per i record sopra/sotto la media

Puoi utilizzare le formule nella funzione FILTRO per filtrare ed estrarre record con valori superiori o inferiori alla media.

Ad esempio, supponiamo che tu abbia il set di dati mostrato di seguito e desideri filtrare tutti i record con un valore di vendita superiore alla media.

Puoi farlo con la seguente formula:

=FILTER($A$2:$C$11,C2:C11>AVERAGE(C2:C11))

Anche in questo caso, per un valore inferiore alla media è possibile utilizzare la seguente formula:

=FILTER($A$2:$C$11,C2:C11<AVERAGE(C2:C11))

Esempio 6: filtra solo record pari (o record dispari)

Se è necessario filtrare ed estrarre rapidamente tutti i record nelle righe pari o dispari, è possibile utilizzare la funzione FILTRO per farlo.

Per fare ciò, è necessario controllare il numero di riga nella funzione FILTRO e filtrare solo i numeri di riga che corrispondono alla condizione del numero di riga.

Supponiamo che tu abbia un set di dati come di seguito e voglio solo estrarre anche i record da questo set di dati.

Ecco la formula per farlo:

=FILTER($A$2:$C$11,MOD(ROW(A2:A11)-1,2)=0)

La formula precedente utilizza la funzione MOD per controllare il numero di riga (dato dalla funzione RIGA) di ogni record.

La formula MOD(ROW(A2:A11)-1,2)=0 restituisce TRUE se il numero di riga è pari e FALSE se è dispari.Nota che ho sottratto 2 dalla parte ROW(A11:A1) perché il primo record si trova nella seconda riga, che regola i numeri di riga per trattare la seconda riga come il primo record.

Allo stesso modo, puoi filtrare tutti i record dispari con la seguente formula:

=FILTER($A$2:$C$11,MOD(ROW(A2:A11)-1,2)=1)

Esempio 7: ordinamento dei dati filtrati utilizzando una formula

L'utilizzo della funzione FILTRO con altre funzioni ci consente di fare di più.

Ad esempio, se si utilizza la funzione FILTRO per filtrare un set di dati, è possibile utilizzare la funzione ORDINA per ottenere risultati ordinati.

Supponiamo di avere un set di dati come mostrato di seguito e di voler filtrare tutti i record con vendite superiori a 10000.È possibile utilizzare la funzione ORDINA con questa funzione per assicurarsi che i dati risultanti siano ordinati in base alle vendite.

La seguente formula farà questo:

=SORT(FILTER($A$2:$C$11,($C$2:$C$11>10000)),3,-1)

La funzione precedente utilizza la funzione FILTRO per ottenere i dati nella colonna C con vendite superiori a 10000.Quindi utilizzare la matrice restituita dalla funzione FILTRO nella funzione ORDINA per ordinare i dati in base alle vendite.

Il secondo parametro nella funzione ORDINA è 3, che consiste nell'ordinare in base alla terza colonna.Il quarto parametro è -1, che ordina i dati in ordine decrescente.

Quindi questi sono 7 esempi di utilizzo della funzione FILTRO in Excel.

Spero che tu abbia trovato utile questo tutorial.