- Funzione filtro Excel - Sintassi
- Esempio 1: filtraggio dei dati in base a un criterio (regione)
- Esempio 2: filtraggio dei dati in base a un criterio (più o meno di)
- Esempio 3: filtraggio dei dati con criteri multipli (AND)
- Esempio 4: filtraggio dei dati con criteri multipli (OR)
- Esempio 5: filtraggio dei dati per ottenere record sopra/sotto la media
- Esempio 6: filtrare solo i record di numeri PARI (o record di numeri DISPARI)
- Esempio 7: Ordina i dati filtrati con la formula
Guarda il video - Esempi di funzioni FILTRO di Excel
Office 365 offre alcune fantastiche funzioni, come XLOOKUP, SORT e FILTER.
Quando si tratta di filtrare i dati in Excel, nel mondo pre-Office 365, dipendevamo principalmente dal filtro integrato di Excel o al massimo dal filtro avanzato o da formule SUMPRODUCT complesse. Nel caso in cui dovessi filtrare una parte di un set di dati, di solito era una soluzione complessa (qualcosa che ho trattato qui).
Ma con la nuova funzione FILTER, ora è davvero facile filtrare rapidamente parte del set di dati in base a una condizione.
E in questo tutorial, ti mostrerò quanto è fantastica la nuova funzione FILTRO e alcune cose utili che puoi fare con questa.
Ma prima di entrare negli esempi, impariamo rapidamente la sintassi della funzione FILTER.
Nel caso in cui desideri ottenere queste nuove funzionalità in Excel, puoi aggiornamento a Office 365 (unisciti al programma Insider per accedere a tutte le funzionalità/formule)Funzione filtro Excel - Sintassi
Di seguito è riportata la sintassi della funzione FILTER:
=FILTRO(array,includi,[se_vuoto])
- Vettore - questo è l'intervallo di celle in cui hai i dati e vuoi filtrare alcuni dati da esso
- includere - questa è la condizione che dice alla funzione quali record filtrare
- [se_vuoto] - questo è un argomento facoltativo in cui è possibile specificare cosa restituire nel caso in cui non vengano trovati risultati dalla funzione FILTER. Per impostazione predefinita (quando non specificato), restituisce #CALC! errore
Ora diamo un'occhiata ad alcuni sorprendenti esempi di funzioni di filtro e cose che può fare che erano piuttosto complesse in sua assenza.
Fare clic qui per scaricare il file di esempio e seguire
Esempio 1: filtraggio dei dati in base a un criterio (regione)
Supponiamo di avere un set di dati come mostrato di seguito e di voler filtrare tutti i record solo per gli Stati Uniti.
Di seguito è la formula FILTRO che farà questo:
=FILTRO($A$2:$C$11,$B$2:$B$11="USA")
La formula sopra utilizza il set di dati come matrice e la condizione è $ B $ 2: $ B $ 11 = "US"
Questa condizione farebbe sì che la funzione FILTER controlli ogni cella nella colonna B (quella che ha la regione) e solo quei record che corrispondono a questo criterio verrebbero filtrati.
Inoltre, in questo esempio, ho i dati originali e i dati filtrati sullo stesso foglio, ma puoi anche averli in fogli separati o persino cartelle di lavoro.
La funzione filtro restituisce un risultato che è un array dinamico (il che significa che invece di restituire un valore, restituisce un array che si riversa su altre celle).
Affinché ciò funzioni, è necessario disporre di un'area in cui il risultato sarebbe vuoto. In una qualsiasi delle celle in quest'area (E2:G5 in questo esempio) contiene già qualcosa, la funzione ti darà l'errore #SPILL.
Inoltre, poiché si tratta di un array dinamico, non è possibile modificare una parte del risultato. Puoi eliminare l'intero intervallo con il risultato o la cella E2 (dove è stata inserita la formula). Entrambi eliminerebbero l'intero array risultante. Ma non puoi modificare nessuna singola cella (o eliminarla).
Nella formula sopra, ho codificato il valore della regione, ma puoi anche averlo in una cella e quindi fare riferimento a quella cella che ha il valore della regione.
Ad esempio, nell'esempio seguente, ho il valore della regione nella cella I2 e questo viene quindi referenziato nella formula:
=FILTRO($A$2:$C$11,$B$2:$B$11=I1)
Ciò rende la formula ancora più utile e ora puoi semplicemente modificare il valore della regione nella cella I2 e il filtro cambierà automaticamente.
Puoi anche avere un menu a discesa nella cella I2 in cui puoi semplicemente effettuare la selezione e aggiornerebbe immediatamente i dati filtrati.
Esempio 2: filtraggio dei dati in base a un criterio (più o meno di)
Puoi anche utilizzare operatori comparativi all'interno della funzione di filtro ed estrarre tutti i record che sono più o meno di un valore specifico.
Si supponga, ad esempio, di disporre del set di dati come mostrato di seguito e di voler filtrare tutti i record in cui il valore delle vendite è maggiore di 10000.
La formula seguente può farlo:
=FILTRO($A$2:$C$11,($C$2:$C$11>10000))
L'argomento dell'array si riferisce all'intero set di dati e la condizione, in questo caso, è ($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.
Nel caso in cui desideri ottenere tutti i record inferiori a 10000, puoi utilizzare la formula seguente:
=FILTRO($A$2:$C$11,($C$2:$C$11<10000))
Puoi anche essere più creativo con la formula FILTER. Ad esempio, se desideri filtrare i primi tre record in base al valore delle vendite, puoi utilizzare la formula seguente:
=FILTRO($A$2:$C$11,($C$2:$C$11>=GRANDE(C2:C11,3)))
La formula sopra usa la funzione GRANDE per ottenere il terzo valore più grande nel set di dati. Questo valore viene quindi utilizzato nei criteri della funzione FILTER per ottenere tutti i record in cui il valore delle vendite è maggiore o uguale al terzo valore più grande.
Fare clic qui per scaricare il file di esempio e seguire
Esempio 3: filtraggio dei dati con criteri multipli (AND)
Supponiamo di avere il set di dati seguente e di voler filtrare tutti i record per gli Stati Uniti in cui il valore di vendita è superiore a 10000.
Questa è una condizione AND in cui è necessario verificare due cose: la regione deve essere negli Stati Uniti e le vendite devono essere superiori a 10000. Se viene soddisfatta solo una condizione, i risultati non devono essere filtrati.
Di seguito è riportata la formula FILTER che filtrerà i record con gli Stati Uniti come regione e vendite superiori a 10000:
=FILTRO($A$2:$C$11,($B$2:$B$11="US")*($C$2:$C$11>10000))
Nota che il criterio (chiamato argomento di inclusione) è ($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 questi due criteri. Questo restituisce un array di 0 e 1, dove viene restituito un 1 solo quando entrambe le condizioni sono soddisfatte.
Nel caso in cui non ci siano record che soddisfano i criteri, la funzione restituirà il #CALC! errore.
E nel caso in cui desideri restituire qualcosa di significativo (invece dell'errore), puoi utilizzare una formula come mostrato di seguito:
=FILTRO($A$2:$C$11,($B$2:$B$11="USA")*($C$2:$C$11>10000),"Nulla trovato")
Qui, ho usato "Non trovato" come terzo argomento, che viene utilizzato quando non vengono trovati record che corrispondono ai criteri.
Esempio 4: filtraggio dei dati con criteri multipli (OR)
Puoi anche modificare l'argomento 'include' nella funzione FILTER per verificare un criterio OR (dove una qualsiasi delle condizioni fornite può essere vera).
Si supponga, ad esempio, di disporre del set di dati come mostrato di seguito e di voler filtrare i record in cui il paese è USA o Canada.
Di seguito è la formula che farà questo:
=FILTRO($A$2:$C$11,($B$2:$B$11="USA")+($B$2:$B$11="Canada"))
Nota che nella formula sopra, ho semplicemente aggiunto le due condizioni usando l'operatore di addizione. Poiché ciascuna di queste condizioni restituisce un array di TRUE e FALSE, posso aggiungere per ottenere un array combinato in cui è TRUE se una delle condizioni è soddisfatta.
Un altro esempio potrebbe essere quando si desidera filtrare tutti i record in cui il paese è gli Stati Uniti o il valore di vendita è superiore a 10000.
La formula seguente farà questo:
=FILTRO($A$2:$C$11,($B$2:$B$11="US")+(C2:C11>10000))
Nota: quando si utilizzano i criteri AND in una funzione FILTER, utilizzare l'operatore di moltiplicazione (*) e quando si utilizzano i criteri OR, utilizzare l'operatore di addizione (+).
Esempio 5: filtraggio dei dati per ottenere record sopra/sotto la media
È possibile utilizzare le formule all'interno della funzione FILTER per filtrare ed estrarre i record in cui il valore è superiore o inferiore alla media.
Ad esempio, supponiamo di avere il set di dati come mostrato di seguito e di voler filtrare tutti i record in cui il valore di vendita è superiore alla media.
Puoi farlo usando la seguente formula:
=FILTRO($A$2:$C$11,C2:C11>MEDIA(C2:C11))
Allo stesso modo, per sotto la media, puoi usare la formula seguente:
=FILTRO($A$2:$C$11,C2:C11<>
Fare clic qui per scaricare il file di esempio e seguire
Esempio 6: filtrare solo i record di numeri PARI (o record di numeri DISPARI)
Nel caso in cui sia necessario filtrare ed estrarre rapidamente tutti i record da righe con numeri pari o righe con numeri dispari, è possibile farlo con la funzione FILTER.
Per fare ciò, è necessario controllare il numero di riga all'interno della funzione FILTRO e filtrare solo i numeri di riga che soddisfano i criteri del numero di riga.
Supponiamo di avere il set di dati come mostrato di seguito e di voler estrarre solo record pari da questo set di dati.
Di seguito è la formula che farà questo:
=FILTRO($A$2:$C$11,MOD(RIGA(A2:A11)-1,2)=0)
La formula sopra usa la funzione MOD per controllare il numero di riga di ogni record (che è dato dalla funzione ROW).
La formula MOD(ROW(A2:A11)-1,2)=0 restituisce VERO quando il numero di riga è pari e FALSO quando è dispari. Nota che ho sottratto 1 dalla parte ROW (A2: A11) poiché il primo record è nella seconda riga e questo regola il numero di riga per considerare la seconda riga come primo record.
Allo stesso modo, puoi filtrare tutti i record dispari utilizzando la formula seguente:
=FILTRO($A$2:$C$11,MOD(RIGA(A2:A11)-1,2)=1)
Esempio 7: Ordina i dati filtrati con la formula
L'uso della funzione FILTRO con altre funzioni ci consente di fare molto di più.
Ad esempio, se filtri un set di dati utilizzando la funzione FILTER, puoi utilizzare la funzione SORT con esso per ottenere il risultato che è già ordinato.
Supponiamo di avere un set di dati come mostrato di seguito e di voler filtrare tutti i record in cui il valore delle vendite è maggiore di 10000. È possibile utilizzare la funzione SORT con la funzione per assicurarsi che i dati risultanti siano ordinati in base al valore delle vendite.
La formula seguente farà questo:
=ORDINA(FILTRO($A$2:$C$11,($C$2:$C$11>10000)),3,-1)
La funzione precedente utilizza la funzione FILTER per ottenere i dati in cui il valore di vendita nella colonna C è maggiore di 10000. Questa matrice restituita dalla funzione FILTER viene quindi utilizzata all'interno della funzione SORT per ordinare questi dati in base al valore delle vendite.
Il secondo argomento nella funzione SORT è 3, che consiste nell'ordinare in base alla terza colonna. E il quarto argomento è -1 che consiste nell'ordinare questi dati in ordine decrescente.
Clicca qui per scaricare il file di esempio
Quindi questi sono 7 esempi per utilizzare la funzione FILTRO in Excel.
Spero che tu abbia trovato utile questo tutorial!
Potrebbero interessarti anche i seguenti tutorial di Excel:
- Come filtrare le celle con la formattazione dei caratteri in grassetto in Excel
- Casella di ricerca del filtro Excel dinamico
- Come filtrare i dati in una tabella pivot in Excel
