Recentemente ho scritto un articolo su come utilizzare riepilogo funzioni in Excel facilmente sintetizzare grandi quantità di dati, ma che l’articolo ha preso in considerazione tutti i dati del foglio di lavoro. Che cosa succede se si desidera solo guardare un sottoinsieme di dati e di riassumere il sottoinsieme di dati?
In Excel, è possibile creare filtri sulle colonne per nascondere le righe che non corrispondono al filtro. In aggiunta, è anche possibile utilizzare le funzioni speciali in Excel per riepilogare i dati utilizzando solo i dati filtrati.
In questo articolo, ti guiderà attraverso i passi per la creazione di filtri di Excel e anche utilizzando built-in funzioni di riassumere i dati filtrati.
Creare Semplici Filtri in Excel
In Excel, è possibile creare semplici filtri e filtri complessi. Iniziamo con semplici filtri. Quando si lavora con i filtri, si dovrebbe sempre avere una riga in alto che viene utilizzato per le etichette. Non è un requisito per avere questa riga, ma rende il lavoro con i filtri un po ‘ più facile.
Sopra, ho alcuni dati falsi e voglio creare un filtro per la Città di colonna. In Excel, questo è veramente facile da fare. Andare avanti e fare clic su Dati scheda della barra multifunzione e quindi fare clic su Filtro pulsante. Non è necessario selezionare i dati sul foglio o fare clic nella prima riga.
Quando si fa clic sul Filtro, ogni colonna nella prima riga avrà automaticamente un piccolo pulsante a discesa aggiunto al diritto.
Ora andare avanti e fare clic sulla freccia a discesa nella Città di colonna. Vedrete un paio di opzioni diverse, che spiegherò di seguito.
In alto, è possibile ordinare rapidamente tutte le righe per i valori della Città di colonna. Nota che quando si ordina i dati, si sposta la riga intera, non solo i valori nella Città di colonna. In questo modo i vostri dati rimane intatto come era prima.
Inoltre, è una buona idea aggiungere una colonna al fronte chiamato ID e numero da uno a tuttavia molte righe del foglio di lavoro. In questo modo, si può sempre ordinare la colonna ID e ottenere i vostri dati nello stesso ordine era in origine, se è importante per voi.
Come si può vedere, tutti i dati nel foglio di calcolo è ora ordinati in base ai valori nella Città di colonna. Finora, nessun righe sono nascoste. Ora diamo un’occhiata a le caselle di controllo nella parte inferiore della finestra di dialogo del filtro. Nel mio esempio, ho solo tre valori univoci nella Città di colonna e questi tre sono visualizzate nell’elenco.
Sono andato avanti e non due città e a sinistra di una controllata. Ora ho solo 8 righe di dati che dimostrano e il resto sono nascosti. Si può facilmente dire che si stanno guardando i dati filtrati, se controllate i numeri di riga a sinistra. A seconda di quante righe sono nascosti, vedrete un paio di linee orizzontali e il colore dei numeri sarà di colore blu.
Ora diciamo che voglio filtro su una seconda colonna di ridurre ulteriormente il numero di risultati. Nella colonna C ho il numero totale di membri di ogni famiglia e voglio solo vedere i risultati anche per le famiglie con più di due soci.
Andare avanti e fare clic sulla freccia a discesa nella Colonna C e vedrete le stesse caselle di controllo per ogni valore unico nella colonna. Tuttavia, in questo caso, vogliamo fare clic sul Numero di Filtri e quindi fare clic su Maggiore. Come si può vedere, ci sono un sacco di altre opzioni.
Una nuova finestra di dialogo pop-up e qui è possibile digitare il valore per il filtro. È inoltre possibile aggiungere uno o più criteri con un E o funzione. Si potrebbe dire che si desidera che le righe in cui il valore è maggiore di 2 e non uguale a 5, per esempio.
Ora sono sceso a 5 righe di dati: le famiglie di New Orleans e con 3 o più membri. Abbastanza facile? Nota che si può facilmente eliminare un filtro su una colonna, fare clic sul menu a discesa e poi cliccando su Cancella Filtro Da Colonna “Nome” collegamento.
In modo che su di esso per i filtri semplici in Excel. Sono molto facili da usare e i risultati sono abbastanza dritto in avanti. Ora diamo un’occhiata al complesso di filtri utilizzando l’ Avanzata di dialogo filtri.
Creare Filtri Avanzati in Excel
Se si desidera creare più avanzate, filtri, è necessario utilizzare le Avanzate della finestra di dialogo del filtro. Per esempio, diciamo che volevo vedere tutte le famiglie che vivono a New Orleans con più di 2 membri della loro famiglia O le famiglie a Clarksville con più di 3 membri della loro famiglia E solo quelli con un .EDU termina indirizzo e-mail. Ora non si può fare con un semplice filtro.
Per fare questo, abbiamo bisogno di impostare il foglio di Excel un po ‘ diverso. Vai avanti e inserire un paio di righe sopra il vostro set di dati e copiare la rubrica etichette esattamente in prima fila, come mostrato di seguito.
Ora qui è come filtri avanzati di lavoro. Devi prima di digitare i criteri di colonne in alto e quindi fare clic su Avanzate pulsante sotto di Ordinamento e Filtro sui Dati di tab.
Così che cosa esattamente si può digitare in quelle celle? OK, allora partiamo con il nostro esempio. Abbiamo solo visualizzare i dati da New Orleans o Clarksville, quindi cerchiamo di tipo di quelli in celle E2 ed E3.
Quando si digita valori su righe diverse, significa O. Vogliamo ora di New Orleans, le famiglie con più di due membri e di Clarksville le famiglie con più di 3 membri. Per fare questo, digitare >2 in C2 e >3 C3.
Dal momento che >2 e New Orleans sono sulla stessa riga, sarà un operatore and. Lo stesso vale per la 3 ° riga sopra. Infine, vogliamo solo le famiglie con .EDU termina indirizzo e-mail. Per fare questo, basta digitare *.edu in entrambi i D2 e D3. Il simbolo * indica un numero qualsiasi di caratteri.
Una volta fatto questo, fare clic in qualsiasi punto del set di dati e quindi fare clic su Avanzate pulsante. L’ Elenco Range campo verrà automaticamente capire il set di dati, dal momento che si è fatto clic su di esso prima del clic sul pulsante Avanzate. Ora, fare clic sul piccolo pulsante a destra dell’ intervallo di Criteri pulsante.
Selezionare tutto da A1 a E3 e quindi fare clic sul pulsante di nuovo per tornare al dialogo Filtro Avanzato. Fare clic su OK e i dati devono ora essere filtrata!
Come si può vedere, ora ho solo 3 risultati che soddisfano tutti questi criteri. Da notare che le etichette per l’intervallo di criteri devono corrispondere esattamente con le etichette per il set di dati in ordine per questo lavoro.
Si può, ovviamente, creare un molto più complicato query utilizzando questo metodo, in modo da giocare intorno con esso per ottenere il risultato desiderato. Infine, parliamo di applicare la sommatoria di funzioni per i dati filtrati.
Riassumendo I Dati Filtrati
Ora diciamo che vorrei riassumere il numero di membri della famiglia i miei dati filtrati, come ho avuto di fare questo? Bene, vediamo di chiarire il nostro filtro cliccando su Cancella pulsante della barra multifunzione. Non preoccupatevi, è molto facile per applicare il filtro avanzato nuovamente cliccando semplicemente sul pulsante “Avanzate” e fare clic su OK di nuovo.
In fondo il nostro set di dati, aggiungiamo una cella chiamata Totale e quindi aggiungere una funzione di somma di somma il totale dei membri della famiglia. Nel mio esempio, ho appena digitato =SOMMA(C7:C31).
Quindi, se guardo tutte le famiglie, ho 78 membri del totale. Ora andiamo avanti e riapplicare il nostro filtro Avanzato e vedere cosa succede.
Whoops! Invece di mostrare il numero corretto, 11, continuo a vedere il totale è di 78! Perché è che? Beh, la funzione SOMMA non ignora le righe nascoste, quindi ancora facendo il calcolo che utilizza tutte le righe. Per fortuna, ci sono un paio di funzioni che si possono utilizzare per ignorare le righe nascoste.
Il primo è PARZIALE. Prima di utilizzare una qualsiasi di queste funzioni speciali, si desidera cancellare il filtro e poi digitare la funzione.
Una volta che il filtro è deselezionata, andare avanti e digitare =SUBTOTALE( e si dovrebbe vedere una casella a discesa visualizzato con un sacco di opzioni. Utilizzando questa funzione, è necessario prima di scegliere il tipo di funzione di somma che si desidera utilizzare con un numero.
Nel nostro esempio, voglio usare la SOMMA, quindi mi sento di 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 si preme invio, si dovrebbe vedere il valore di 78 è lo stesso del precedente. Tuttavia, se si applica il filtro nuovo, vedremo 11!
Eccellente! Che è esattamente ciò che vogliamo. Ora è possibile regolare i filtri e il valore sarà sempre riflettono solo le righe che sono attualmente in mostra.
La seconda funzione che funziona quasi esattamente lo stesso come il totale PARZIALE della funzione di AGGREGAZIONE. L’unica differenza è che c’è un altro parametro nella funzione di AGGREGAZIONE dove si deve specificare che si desidera ignorare le righe nascoste.
Il primo parametro è la sommatoria di una funzione che si desidera utilizzare e come con il totale PARZIALE, 9 rappresenta la funzione SOMMA. La seconda opzione è dove si deve digitare 5 per ignorare le righe nascoste. L’ultimo parametro è la stessa ed è l’intervallo di celle.
Si può leggere anche il mio articolo su funzioni di riepilogo per imparare a utilizzare la funzione di AGGREGAZIONE e di altre funzioni come la moda, MEDIANA, MEDIA, etc. più in dettaglio.
Speriamo che questo articolo ti dà un buon punto di partenza per la creazione e l’uso di filtri in Excel. Se avete domande, non esitate a postare un commento. Enjoy!