Recentemente ho scritto un articolo su come utilizzare le funzioni di riepilogo in Excel per riassumere facilmente grandi quantità di dati, ma quell'articolo teneva conto di tutti i dati del foglio di lavoro. Che cosa succede se si desidera visualizzare solo un sottoinsieme di dati e riepilogare il sottoinsieme di dati?
In Excel, è possibile creare filtri su colonne che nasconderanno le righe che non corrispondono al filtro. Inoltre, puoi anche utilizzare le funzioni speciali in Excel per riepilogare i dati utilizzando solo i dati filtrati.
In questo articolo, ti guiderò attraverso i passaggi per la creazione di filtri in Excel e anche usando i built-in funzioni per riepilogare i dati filtrati.
Crea filtri semplici in Excel
In Excel, puoi creare semplici filtri e filtri complessi. Iniziamo con semplici filtri. Quando lavori con i filtri, dovresti sempre avere una riga in alto che viene utilizzata per le etichette. Non è obbligatorio avere questa riga, ma semplifica il lavoro con i filtri.
In alto, ho alcuni dati falsi e io vuoi creare un filtro sulla colonna Città. In Excel, questo è veramente facile da fare. Vai avanti e fai clic sulla scheda Datinella barra multifunzione, quindi fai clic sul pulsante Filtro. Non è necessario selezionare i dati sul foglio o fare clic nella prima riga.
Quando si fa clic su Filtro, ogni colonna nella prima riga verrà automaticamente aggiunto un piccolo pulsante a discesa all'estrema destra.
Ora vai avanti e fai clic sulla freccia del menu a discesa Colonna della città Vedrai un paio di opzioni diverse, che spiegherò di seguito.
Nella parte superiore, puoi ordinare rapidamente tutte le righe di i valori nella colonna Città. Si noti che quando si ordinano i dati, si sposterà l'intera riga, non solo i valori nella colonna Città. Ciò assicurerà che i tuoi dati rimangano intatti come prima.
Inoltre, è una buona idea aggiungere una colonna all'inizio chiamata ID e numerarla da una a quante più righe hai nel tuo foglio di lavoro. In questo modo, puoi sempre ordinare per colonna ID e recuperare i tuoi dati nello stesso ordine in cui erano originariamente, se è importante per te.
Come puoi vedere, tutti i dati nel foglio di calcolo vengono ora ordinati in base ai valori nella colonna Città. Finora, nessuna riga è nascosta. Ora diamo un'occhiata alle caselle di controllo nella parte inferiore della finestra di dialogo del filtro. Nel mio esempio, ho solo tre valori univoci nella colonna Città e questi tre appaiono nell'elenco.
Sono andato avanti e deselezionato due città e ne ha lasciato una controllata. Ora ho solo 8 righe di dati che mostrano e il resto è nascosto. Puoi facilmente dire che stai guardando i dati filtrati se controlli i numeri di riga all'estrema sinistra. A seconda di quante righe sono nascoste, vedrai alcune linee orizzontali aggiuntive e il colore dei numeri sarà blu.
Ora diciamo che voglio filtrare su una seconda colonna per ridurre ulteriormente il numero dei risultati. Nella colonna C, ho il numero totale di membri di ogni famiglia e voglio vedere solo i risultati per le famiglie con più di due membri.
Vai avanti e fai clic sulla freccia del menu a discesa nella colonna C e vedrai le stesse caselle di controllo per ogni valore univoco nella colonna. Tuttavia, in questo caso, vogliamo fare clic su Numero di filtri, quindi fare clic su Maggiore di. Come puoi vedere, ci sono anche molte altre opzioni.
Apparirà una nuova finestra di dialogo e qui puoi digitare il valore per il filtro. Puoi anche aggiungere più di un criterio con una funzione AND o OR. Potresti dire che vuoi le righe in cui il valore è maggiore di 2 e non uguale a 5, ad esempio.
Ora sono giù per solo 5 righe di dati: famiglie solo da New Orleans e con 3 o più membri. Abbastanza facile? Tieni presente che puoi facilmente cancellare un filtro su una colonna facendo clic sul menu a discesa e quindi facendo clic sul link Cancella filtro da "Nome colonna".
Quindi questo è tutto per i filtri semplici in Excel. Sono molto facili da usare e i risultati sono piuttosto semplici. Diamo ora un'occhiata ai filtri complessi utilizzando la finestra di dialogo dei filtri Avanzate.
Crea filtri avanzati in Excel
Se desideri creare filtri più avanzati, devi utilizzare la finestra di dialogo del filtro Avanzate. Per esempio, diciamo che volevo vedere tutte le famiglie che vivono a New Orleans con più di 2 membri nella loro famiglia ORtutte le famiglie a Clarksville con più di 3 membri nella loro famiglia ANDsolo quelli con un .EDUindirizzo email finale. Ora non puoi farlo con un semplice filtro.
Per fare ciò, dobbiamo configurare il foglio Excel in modo un po 'diverso. Vai avanti e inserisci un paio di righe sopra il set di dati e copia esattamente le etichette di intestazione nella prima riga come mostrato di seguito.
Ora ecco come funzionano i filtri avanzati. Devi prima digitare i tuoi criteri nelle colonne in alto e quindi fare clic sul pulsante Avanzatein Ordina & amp; Filtronella scheda Dati.
Quindi, che cosa possiamo digitare esattamente in tali celle? OK, iniziamo dal nostro esempio. Vogliamo solo vedere i dati da New Orleans o Clarksville, quindi digitarli nelle celle E2 ed E3.
Quando si digitano valori su diversi righe, significa OR. Ora vogliamo le famiglie di New Orleans con più di due membri e famiglie di Clarksville con più di 3 membri. Per fare ciò, digita & gt; 2in C2 e & gt; 3in C3.
Poiché & gt; 2 e New Orleans sono sulla stessa riga, sarà un operatore AND. Lo stesso vale per la riga 3 sopra. Infine, vogliamo solo le famiglie con l'indirizzo email finale .EDU. Per fare ciò, digita *. Eduin D2 e D3. Il simbolo * indica un numero qualsiasi di caratteri.
Una volta fatto, fai clic su un punto qualsiasi del set di dati e poi fai clic su Avanzate. Il campo Elenco intervallodeterminerà automaticamente il set di dati da quando hai fatto clic su di esso prima di fare clic sul pulsante Avanzate. Ora fai clic sul piccolo pulsante a destra del pulsante Intervallo di criteri.
Seleziona tutto da A1 a E3 e poi fai di nuovo clic sullo stesso pulsante per tornare alla finestra di dialogo Filtro avanzato. Fai clic su OK e ora i tuoi dati dovrebbero essere filtrati!
Come puoi vedere, ora ho solo 3 risultati che corrispondono a tutti questi criteri. Nota che le etichette per l'intervallo di criteri devono corrispondere esattamente alle etichette per il set di dati in modo che funzioni.
Puoi ovviamente creare query molto più complicate usando questo metodo, quindi giocaci per ottenere i risultati desiderati. Infine, parliamo dell'applicazione delle funzioni di somma ai dati filtrati.
Riepilogo dei dati filtrati
Ora diciamo che voglio riassumere il numero di membri della famiglia sui miei dati filtrati, come farei avuto modo di farlo? Bene, cancelliamo il nostro filtro facendo clic sul pulsante Cancellanella barra multifunzione. Non preoccuparti, è molto semplice applicare nuovamente il filtro avanzato semplicemente facendo clic sul pulsante Avanzate e facendo nuovamente clic su OK.
in fondo al nostro set di dati, aggiungiamo una cella chiamata Totalee quindi aggiungiamo una funzione somma per sommare i membri della famiglia totale. Nel mio esempio, ho appena digitato = SUM (C7: C31).
Quindi, se guardo a tutti famiglie, ho 78 membri in totale. Ora andiamo avanti e riapplichiamo il nostro filtro Avanzato e vediamo cosa succede.
Whoops! Invece di mostrare il numero corretto, 11, vedo ancora il totale è 78! Perché? Bene, la funzione SUM non ignora le righe nascoste, quindi sta ancora facendo il calcolo usando tutte le righe. Fortunatamente, ci sono un paio di funzioni che puoi usare per ignorare le righe nascoste.
Il primo è SUBTOTAL. Prima di utilizzare una di queste funzioni speciali, è necessario cancellare il filtro e digitare la funzione.
Una volta eliminato il filtro, proseguire e digitare = SUBTOTAL (e dovresti vedere una finestra a comparsa con un sacco di opzioni. Usando questa funzione, per prima cosa scegli il tipo di funzione di sommatoria che vuoi usare usando un numero.
Nel nostro esempio, voglio usare SUM, quindi dovrei digitare il numero 9 o semplicemente fare clic su di esso dal menu a discesa, quindi digitare una virgola e selezionare l'intervallo di celle.
Quando premi enter, dovresti vedere che il valore di 78 è lo stesso di prima.Tuttavia, se ora applichi nuovamente il filtro, vedremo 11!
Eccellente! È esattamente ciò che vogliamo. Ora puoi regolare i tuoi filtri e il valore rifletterà sempre solo le righe che stanno attualmente mostrando.
seconda funzione che funziona praticamente esattamente come la funzione SUBTOTALE è AGGREGATE. la differenza è che esiste un altro parametro nella funzione AGGREGATE in cui devi specificare che vuoi ignorare le righe nascoste.
Il primo parametro è la funzione di sommatoria che si desidera utilizzare e, come con SUBTOTAL, 9 rappresenta la funzione SOMMA. La seconda opzione è dove devi digitare 5 per ignorare le righe nascoste. L'ultimo parametro è lo stesso ed è l'intervallo di celle.
Puoi anche leggere il mio articolo sulle funzioni di riepilogo per imparare come utilizzare la funzione AGGREGATE e altre funzioni come MODE, MEDIAN, MEDIAN, MEDIA, ecc. in modo più dettagliato.
Spero che questo articolo ti dà un buon punto di partenza per la creazione e l'uso di filtri in Excel. Se hai qualche domanda, sentiti libero di postare un commento. Buon divertimento!