Filtraggio dei dati in Excel: semplice e completo

Il comando Filtro Excel può aiutare a filtrare i dati in un intervallo o in una tabella per mostrare solo i dati necessari e nascondere il resto. Puoi applicare i suoi operatori incorporati per filtrare facilmente numeri, testi o date come filtrare tutti i numeri che sono maggiori o uguali a un certo numero, filtrare il testo che inizia, finisce o contiene un carattere o una parola specifica, oppure mostra solo le righe in cui il la data di scadenza è prima o dopo una certa data e così via. Dopo aver filtrato i dati in un intervallo o in una tabella, se i dati vengono modificati, è possibile applicare nuovamente un filtro per ottenere i nuovi dati o deselezionare un filtro per visualizzare tutti i dati.

In questo tutorial, dimostreremo come aggiungere, utilizzare o rimuovere filtri in Excel. Oltre a ciò, ti guideremo su come migliorare la funzione di filtro per gestire problemi Excel più complicati.

---

1. Come aggiungere un filtro in Excel

Per filtrare i dati in un intervallo o una tabella, in primo luogo è necessario aggiungere un filtro ai dati. Questa sezione fornisce 3 modi per aggiungere un filtro in Excel.

1.1 Comando Filtro nella scheda Dati

Seleziona le celle in un intervallo o una tabella a cui desideri aggiungere un filtro, fai clic su Dati > Filtro.

1.2 Comando Filtra nella scheda Home

Seleziona le celle in un intervallo o una tabella a cui desideri aggiungere un filtro, fai clic su Casa > Ordina e filtra > Filtro.

1.3 Aggiungi filtro con scorciatoia

Selezionare le celle in un intervallo o una tabella a cui si desidera aggiungere un filtro, quindi premere Ctrl + Shift + L chiavi.

Dopo aver applicato una delle operazioni precedenti, è possibile vedere le frecce a discesa aggiunte nelle intestazioni delle colonne delle celle selezionate.

---

2. Come applicare il filtro in Excel (uno o più criteri)

Dopo aver aggiunto il filtro, è necessario applicarlo manualmente. Questa sezione ti mostrerà come applicare il filtro in una o più colonne in Excel.

2.1 Applicare il filtro a una colonna (un criterio)

Se si desidera applicare il filtro solo a una colonna, ad esempio i dati del filtro nella colonna C come mostrato nell'immagine sottostante. Per favore, vai a quella colonna e poi fai come segue.

1. 1) Fare clic sulla freccia del menu a discesa nell'intestazione della colonna.
2. 2) Specificare una condizione di filtro di cui hai bisogno.
3. 3) Fare clic su OK pulsante per avviare il filtraggio. Vedi screenshot:
   

Ora il filtro viene applicato alla colonna C. Tutti i dati che soddisfano i criteri del filtro verranno visualizzati e il resto verrà nascosto.

Dopo aver applicato il filtro, puoi vedere la freccia a discesa che si trasforma in un'icona di filtro .

È molto importante che quando passi il cursore sull'icona del filtro, i criteri di filtro che hai specificato vengano visualizzati come suggerimento sullo schermo come mostrato nell'immagine sottostante. Quindi, se dimentichi i criteri che hai specificato per un filtro, passa semplicemente il cursore sull'icona del filtro.

2.2 Applicare il filtro con più criteri a più colonne (più criteri)

2.2.1 Applicare il filtro con più criteri a più colonne una per una

Se desideri applicare il filtro a più colonne con più criteri, ripeti semplicemente il metodo sopra a più colonne una per una.

Dopo aver applicato il filtro a più colonne, è possibile vedere le frecce a discesa nelle colonne filtrate trasformate in icone di filtro.

2.2.2 Applicare contemporaneamente un filtro con più criteri a più colonne

Con il metodo sopra, è necessario applicare il filtro alle colonne una per una e il punto più importante è che questo metodo supporta solo E criteri. Qui introdurre i metodi per non solo applicare il filtro a più colonne contemporaneamente, ma anche applicare entrambi i file E ed OR criteri.

Supponendo di avere una tabella di dati come mostrato nell'immagine sottostante e di voler filtrare i dati da più colonne in base a più criteri: Prodotto = AAA-1 e ordine> 80, or Prezzo totale> 10000. Si prega di provare uno dei seguenti metodi per farlo.

2.2.2.1 Applicare il filtro a più colonne con la funzione Filtro avanzato

La funzione Filtro avanzato può aiutarti a risolvere questo problema, per favore fai come segue passo dopo passo.

1. In primo luogo, creare i criteri nel foglio di lavoro come mostrato nell'immagine sottostante.

Nota: Per i criteri AND, posizionare i valori dei criteri in celle diverse della stessa riga. E posiziona il valore dei criteri OR sull'altra riga.

2. Clic Dati > Tecnologia accendere il Filtro avanzato funzione.

3. Nel Filtro avanzato finestra di dialogo, configurare come segue.

Ora le colonne vengono filtrate simultaneamente in base ai criteri forniti come mostrato nell'immagine sottostante.

2.2.2.2 Applicare facilmente il filtro a più colonne con uno strumento straordinario

Il E ed OR i criteri di filtro non sono facili da gestire nel metodo sopra, qui consiglio vivamente il Super filtro caratteristica di Kutools for Excel. Con questa funzione, puoi applicare facilmente il filtro a più colonne con criteri AND e OR in Excel.

1. Dopo l'installazione Kutools for Excel, fare clic su Kutools Plus > Super filtro.

Poi il Super filtro riquadro viene visualizzato sul lato destro del foglio di lavoro.

Per impostazione predefinita, vengono aggiunti due gruppi di criteri vuoti con una relazione OR tra di loro nel file Super filtro Pannello. E la relazione tra i criteri nello stesso gruppo è AND. È possibile modificare la relazione tra diversi gruppi in base alle proprie esigenze.

2. Nel Super filtro riquadro, configurare i criteri di filtro come segue.

Ora solo i dati corrispondenti vengono visualizzati nell'intervallo di dati originale e il resto è nascosto. Vedi screenshot:

Suggerimenti: Con questa pratica funzione, puoi aggiungere più criteri in un gruppo, aggiungere più gruppi, salvare le impostazioni di filtro correnti come scenario per un utilizzo futuro e così via. È uno strumento indispensabile che può far risparmiare un sacco di tempo di lavoro e migliorare l'efficienza lavorativa.

Fare clic per saperne di più su questa funzione.

---

3. Come utilizzare il filtro in Excel

In questa sezione imparerai come utilizzare il comando filtro per filtrare diversi tipi di dati come testo, numeri, date e formati.

3.1 Filtrare i valori di testo

3.1.1 Filtrare le celle di testo con criteri specifici (iniziare con, finire con, contenere e così via)

In realtà, l'operatore di filtro integrato - Filtri di testo fornisce molti criteri utili per filtrare facilmente il testo. Supponendo di voler filtrare le celle che iniziano con un carattere specifico come J, per favore fai come segue per farlo.

1. Aggiungere il filtro all'intestazione della colonna dell'intervallo di dati originale. Clicca per sapere come.

2. Fare clic sulla freccia a discesa nella cella dell'intestazione per aprire il menu del filtro.

3. Clic Filtri di testo > Inizia con.

4. Nel Filtro automatico personalizzato finestra di dialogo, immettere il carattere specifico (qui digito una J) nella casella di testo, quindi fare clic OK.

Suggerimenti: Puoi aggiungerne un altro E altre ancora… or Or criteri di relazione di cui hai bisogno.

Ora tutte le celle iniziano con il carattere J vengono visualizzate nella colonna D come mostrato nell'immagine sottostante.

3.1.2 Filtro con distinzione tra maiuscole e minuscole

Sembra che sia facile filtrare le celle di testo in base a criteri specifici con l'operatore di filtro incorporato. Tuttavia, poiché la funzione Filtro non supporta il filtraggio del testo con distinzione tra maiuscole e minuscole, come possiamo eseguire un filtro con distinzione tra maiuscole e minuscole in Excel? Questa sezione ti mostrerà i metodi per ottenerlo.

3.1.2.1 Filtrare un determinato testo con distinzione tra maiuscole e minuscole per formula e il comando Filtro

Supponendo di voler filtrare tutte le maiuscole di un certo testo come "STRUMENTI DI TESTO" nella colonna B, si prega di fare come segue.

1. Crea una colonna helper oltre all'intervallo di dati originale (qui scelgo la colonna D come colonna helper). Immettere la formula seguente nella seconda cella e quindi premere il tasto Invio. Seleziona la cella del risultato, trascinala Maniglia di riempimento automatico per ottenere gli altri risultati.

= ESATTO (B2, MAIUSCOLO (B2))

Nota: Questa formula aiuta a identificare le celle maiuscole e minuscole. Se una cella contiene tutti i caratteri maiuscoli, il risultato sarà VERO, altrimenti, otterrai il risultato come FALSE.

2. Selezionare le colonne B e D (verrà selezionata la colonna C, non importa), fare clic su Data> Filtro per aggiungervi filtri.

3. Vai alla colonna B (la colonna contiene i testi che filtrerai), quindi configura come segue.

Ora solo le maiuscole e le minuscole degli "strumenti di testo" vengono visualizzate nella colonna B.

4. Fare clic sulla freccia a discesa nella colonna D, deselezionare il Seleziona tutto casella di controllo, selezionare la casella di controllo accanto al file TRUE elemento, quindi fare clic su OK pulsante.

Quindi tutte le maiuscole del testo "strumenti di testo" nella colonna B vengono filtrate come mostrato nell'immagine sottostante.

3.1.2.2 Filtra facilmente le celle con distinzione tra maiuscole e minuscole con uno strumento straordinario

Se vuoi solo filtrare tutto il testo in maiuscolo o minuscolo in una colonna, qui consigliamo il Filtro speciale caratteristica di Kutools for Excel. Con questa funzione, tutto il testo maiuscolo o minuscolo verrà filtrato facilmente con solo pochi clic.

1. Selezionare l'intervallo di colonne all'interno del quale filtrare i testi, quindi fare clic su Kutools Plus > Filtro speciale > Filtro speciale.

2. Nel Filtro speciale finestra di dialogo, eseguire le impostazioni seguenti.

3. Quindi a Kutools for Excel la finestra di dialogo si apre per dirti quante celle sono state trovate e verranno filtrate, fai clic su OK pulsante.

Ora tutte le celle maiuscole o minuscole vengono filtrate immediatamente come mostrato nell'immagine sottostante.

Fare clic per saperne di più su questa funzione.

3.1.3 Filtrare per lunghezza del testo

Se vuoi filtrare le celle in base alla lunghezza del testo, ad esempio, per filtrare le celle con una lunghezza del testo pari a 10 caratteri, cosa dovresti fare? Qui i tre metodi seguenti possono farti un favore.

3.1.3.1 Filtrare le celle in base alla lunghezza del testo con il comando Filtro

In realtà, il comando Filter ha l'operatore integrato per risolvere questo problema, si prega di fare come segue.

1. Seleziona l'intervallo di celle da filtrare (qui seleziono B1: B27), quindi aggiungi un filtro a questo intervallo di colonne facendo clic su Dati > Filtro.

2. Fare clic sulla freccia a discesa nell'intestazione della colonna, quindi fare clic su Filtri di testo > Filtro personalizzato. Vedi screenshot:

3. Nel Filtro automatico personalizzato finestra di dialogo, selezionare i criteri come uguale a, digitare 10 punti interrogativi (?) come modalità modello nella casella di testo, quindi fare clic sul OK pulsante.

Suggerimenti:: Questi 10 punti interrogativi indicano che corrisponderà alla stringa di testo la cui lunghezza è 10.

Ora tutte le celle la cui lunghezza della stringa di testo è 10 (spazi inclusi) vengono filtrate immediatamente.

3.1.3.2 Filtrare le celle in base alla lunghezza del testo con la formula e il comando Filtro

Inoltre, è possibile utilizzare la funzione LEN per calcolare la lunghezza della stringa di testo di ciascuna cella, quindi applicare il comando Filtro per filtrare le celle della lunghezza del testo necessaria in base al risultato calcolato.

1. Creare una colonna helper adiacente all'intervallo di dati originale. Inserisci la formula seguente al suo interno e quindi premi il tasto Invio. Seleziona la cella del risultato e trascinala Maniglia di riempimento automatico per ottenere gli altri risultati.

= LEN (B2)

Ora stai ottenendo la lunghezza del testo di ogni cella nella colonna specificata.

2. Selezionare la colonna helper (includi intestazione), fare clic su Dati > Filtro per aggiungervi un filtro.

3. Fare clic sulla freccia del menu a discesa, deselezionare il Seleziona tutto casella per deselezionare tutti gli elementi, quindi selezionare solo la casella accanto al numero 10 e infine fare clic su OK pulsante.

Ora tutte le celle la cui lunghezza della stringa di testo è 10 (spazi inclusi) vengono filtrate immediatamente.

3.1.3.3 Filtra facilmente le celle in base alla lunghezza del testo con uno strumento straordinario

Qui consiglio il Filtro speciale utilità di Kutools for Excel per aiutarti a filtrare facilmente le celle in base alla lunghezza del testo in Excel.

1. Selezionare l'intervallo di colonne in cui filtrare le celle in base a una determinata lunghezza del testo, fare clic su Kutools Plus > Filtro speciale > Filtro speciale. Vedi screenshot:

2. Nel Filtro speciale finestra di dialogo, configurare come segue.

3. Il Kutools for Excel la finestra di dialogo si apre per dirti quante celle sono state trovate e verranno filtrate, fai clic su OK andare avanti.

Quindi tutte le celle con una lunghezza della stringa di testo uguale a 10 vengono filtrate come mostrato nell'immagine sottostante.

3.2 Filtrare i numeri

In Excel, è anche molto facile filtrare i numeri con il comando Filtri numeri.

Supponendo di voler filtrare celle con numeri compresi tra 15000 e 20000 in una colonna (come la colonna C come mostrato nell'immagine sottostante), puoi fare come segue per ottenerlo.

1. Selezionare l'intervallo di colonne contenente i numeri da filtrare, fare clic su Dati > Filtro per aggiungere un filtro.

2. Dopo aver aggiunto il filtro, configurare come segue.

Suggerimenti: Poiché voglio filtrare le celle con numeri compresi tra 15000 e 20000, qui inserisco 15000 e 20000 separatamente nelle caselle di testo.

Ora le celle con numeri compresi tra 15000 e 20000 vengono filtrate come mostrato nell'immagine sottostante.

3.3 Filtrare le date

Per impostazione predefinita, la funzione incorporata Filtri data fornisce molti criteri comuni per filtrare le date. Come puoi vedere, non esiste un'opzione integrata per filtrare le date per giorno della settimana, fine settimana o giorno lavorativo. Questa sezione ti insegnerà come eseguire queste operazioni.

3.3.1 Filtrare le date per giorno della settimana o fine settimana

Supponendo che tu abbia una tabella di dati come mostrato nell'immagine sottostante, se desideri filtrare le date per giorno della settimana o fine settimana, applica uno dei metodi seguenti.

3.3.1.1 Filtrare le date per giorno della settimana o fine settimana con la formula e il comando Filtro

In questa sezione, applicherai la funzione WEEKDAY per calcolare il giorno della settimana di ciascuna data, quindi applicherai il filtro per filtrare il determinato giorno della settimana o i fine settimana di cui hai bisogno.

1. In una cella vuota (D2 in questo caso), inserisci la formula seguente e premi il tasto entrare chiave. Seleziona la cella del risultato e trascina il file Maniglia di riempimento automatico sopra le celle sottostanti per applicare questa formula.

= WEEKDAY (A2)

Suggerimenti:

Nota: Come puoi vedere, la formula restituisce numeri da 1 a 7, che indica il giorno della settimana da Domenica a Sabato (1 è per domenica, 7 è per sabato).

2. Selezionare tutti i risultati della formula (includere la cella di intestazione), fare clic su Dati > Filtro.

3. Fare clic sulla freccia a discesa e quindi deselezionare il Seleziona tutto casella di controllo.

Quindi tutte le celle dei fine settimana o di un determinato giorno della settimana vengono filtrate. Vedi screenshot:

3.3.1.2 Filtra facilmente le date per giorno della settimana o fine settimana con uno strumento straordinario

Se il metodo sopra non è conveniente per te, qui consigliamo il file Filtro speciale utilità di Kutools for Excel. Con questa funzione, puoi facilmente filtrare le celle contenenti qualsiasi giorno della settimana solo con diversi clic.

1. Selezionare le celle contenenti le date che si desidera filtrare in base a un determinato giorno della settimana.

2. Clic Kutools Plus > Filtro speciale > Filtro speciale.

3. Nel Filtro speciale finestra di dialogo, configurare come segue.

4. Quindi a Kutools for Excel si apre la finestra di dialogo con il numero di celle trovate e verranno filtrate, fare clic su OK andare avanti.

Ora tutte le celle dei fine settimana o di qualsiasi giorno della settimana vengono filtrate.

3.3.2 Filtra le celle in base ai giorni lavorativi con uno strumento straordinario

Oltre a filtrare le celle in base al giorno della settimana o ai fine settimana, il file Filtro speciale utilità di Kutools for Excel può anche aiutare a filtrare le celle in base ai giorni lavorativi.

1. Applicare il stessi passaggi come sopra per abilitare l'utilità Filtro speciale.

2. Nel Filtro speciale finestra di dialogo, eseguire le impostazioni seguenti.

3. Quindi a Kutools for Excel si apre la finestra di dialogo. Clic OK andare avanti.

Ora tutte le celle della giornata lavorativa vengono filtrate.

3.4 Formati di filtro

Normalmente, Excel supporta il filtraggio dei dati in base a criteri visivi come il colore del carattere, il colore della cella o i set di icone come mostrato nell'immagine sottostante.

Tuttavia, se si desidera filtrare i dati in base ad altri criteri visivi come lo stile del carattere (grassetto, corsivo), gli effetti dei caratteri (barrato) o una cella speciale (contenente formule), Excel non aiuta a ottenere. Questa sezione fornisce metodi per aiutarti a risolvere questi problemi.

3.4.1 Filtrare per testo formattato in grassetto / corsivo

Supponendo di voler filtrare i dati formattando il testo in grassetto o corsivo come mostrato nell'immagine sottostante, i seguenti metodi possono farti un favore. Si prega di fare quanto segue.

3.4.1.1 Filtrare il testo formattato in grassetto / corsivo con la formula e il comando Filtro

La combinazione della formula Get.Cell e del comando Filtro può aiutare a filtrare il testo formattato in grassetto in un intervallo di colonne.

1. Clic Formule > Definisci nome.

2. Nel Nuovo nome finestra di dialogo, è necessario:

Sintassi della formula:

=GET.CELL(type_num, reference)

Argomenti della formula

3. Selezionare una cella vuota nella stessa riga di B2, digitare la formula sottostante e quindi premere il tasto entrare chiave. Seleziona la cella del risultato, trascinala Maniglia di riempimento automatico sopra le celle sottostanti per applicare questa formula.

= Filter_Bold_Cells

4. Selezionare tutte le celle dei risultati (includere l'intestazione), fare clic su Dati > Filtro.

5. Fare clic sulla freccia del menu a discesa, selezionare solo la casella accanto a TRUE opzione, quindi fare clic OK.

Quindi tutte le celle di testo in grassetto o corsivo vengono filtrate. Vedi screenshot:

3.4.1.2 Filtrare il testo formattato in grassetto o corsivo con i comandi Trova e sostituisci e Filtra

È inoltre possibile utilizzare la combinazione dei comandi Trova e sostituisci e Filtro per ottenere.

1. Selezionare l'intervallo di colonne contenente le celle di testo in grassetto o corsivo da filtrare, quindi premere Ctrl + F chiavi.

2. Nel Trova e sostituisci finestra di dialogo, è necessario configurare come segue.

3. Ora tutte le celle di testo in grassetto o corsivo sono selezionate nell'intervallo originale, fare clic su Casa > Colore di riempimento, e quindi scegli un colore di riempimento per le celle selezionate.

4. Selezionare nuovamente l'intero intervallo di colonne, fare clic su Dati > Filtro per aggiungervi un filtro.

5. Fare clic sulla freccia del menu a discesa, selezionare filtra per colore, quindi fai clic sul colore di riempimento appena specificato sotto Filtra per colore cella. Vedi screenshot:

Quindi tutte le celle di testo in grassetto o corsivo vengono filtrate.

3.4.1.3 Filtrare facilmente il testo formattato in grassetto o corsivo con uno strumento straordinario

Come accennato in precedenza, il Filtro speciale utilità di Kutools for Excel può aiutare a filtrare facilmente con distinzione tra maiuscole e minuscole, filtrare per lunghezza del testo, filtrare le date. Qui insegneremo come applicare questa funzione per filtrare celle di testo formattate in grassetto o corsivo in Excel.

1. Seleziona l'intervallo di colonne (includi intestazione) che contiene le celle formattate in grassetto o corsivo che filtrerai.

2. Clic Kutools Plus > Filtro speciale > Filtro grassetto / Filtra corsivo. Vedi screenshot:

3. Clic OK nel saltar fuori Kutools for Excel finestra di dialogo per andare avanti (questa finestra di dialogo si apre per dirti quante celle soddisfano i criteri).

Ora tutte le celle di testo formattate in grassetto o corsivo vengono filtrate.

3.4.2 Filtra per testo formattato barrato

Supponendo che tu abbia ricevuto un elenco di persone che tagliano aggiungendo barrato a loro e che devi scoprire tutte le celle barrate filtrando, i metodi seguenti possono farti un favore.

3.4.2.1 Filtrare il testo formattato barrato con la funzione definita dall'utente e il comando Filtro

È possibile applicare la funzione definita dall'utente per identificare le celle di testo formattate barrate, quindi utilizzare il comando Filtro per filtrare tutte le celle barrate in base ai risultati.

1. premi il altro + F11 chiavi per aprire il Microsoft Visual Basic, Applications Edition finestra.

2. Nel Microsoft Visual Basic, Applications Edition finestra, fare clic inserire > Modulo. Quindi copia il codice VBA sottostante nel file Code finestra.

Function HasStrike(Rng As Range) As Boolean
HasStrike = Rng.Font.Strikethrough
End Function

3. premi il altro + Q i tasti per chiudere il file Microsoft Visual Basic, Applications Edition finestra.

4. Selezionare una cella vuota (questa cella dovrebbe essere nella stessa riga della cella che si desidera calcolare), immettere la formula seguente e premere il tasto entrare chiave. Seleziona la cella del risultato, trascinala Maniglia di riempimento automatico sopra la cella sottostante per applicare questa formula.

= HasStrike (B2)

Nota: Se la cella corrispondente ha un effetto carattere barrato, ritorna VERO, altrimenti ritorna FALSE.

5. Selezionare tutte le celle dei risultati (includere la cella di intestazione), fare clic su Dati > Filtro.

6. Quindi fare clic sulla freccia del menu a discesa> selezionare solo la casella accanto all'opzione VERO> fare clic OK. Vedi screenshot mostrato.

Ora puoi vedere tutte le celle formattate barrate sono filtrate.

3.4.2.2 Filtrare facilmente il testo formattato barrato con uno strumento straordinario

Grazie alla Filtro speciale utilità di Kutools for Excel, tutte le celle con formattazione barrata possono essere filtrate direttamente solo con diversi clic.

1. Selezionare l'intervallo di colonne in cui filtrare tutte le celle formattate barrate, fare clic su Kutools Plus > Filtro speciale > Filtro barrato.

2. Quindi a Kutools for Excel la finestra di dialogo si apre per dirti quante celle sono qualificate, fai clic su OK andare avanti.

Quindi tutte le celle formattate barrate vengono filtrate come mostrato nell'immagine sottostante.

3.4.3 Filtra per carattere o colore di sfondo

Come accennato all'inizio in questa sezione dei formati di filtro, Excel supporta il filtraggio dei dati in base a criteri visivi come il colore del carattere, il colore della cella o i set di icone con la sua funzionalità incorporata. Questa sezione mostra come applicare il filtra per colore funzione per filtrare le celle in base al carattere o al colore di sfondo nei dettagli. Nel frattempo, consigliamo una comoda funzione di terze parti per aiutare a risolvere questo problema.

3.4.3.1 Filtrare in base a un carattere o colore di sfondo con il comando Filtro

È possibile applicare direttamente la funzione Filtro per colore del comando Filtro per filtrare le celle in base a un carattere specifico o al colore di sfondo in Excel.

1. Seleziona l'intervallo di colonne in cui filtrerai le celle in base al carattere o al colore di sfondo, quindi fai clic su Dati > Filtro.

2. Fare clic sulla freccia del menu a discesa> filtra per colore. Quindi puoi vedere tutti i colori delle celle ei colori dei caratteri dell'intervallo di colonne corrente sono elencati. Fare clic su qualsiasi colore della cella o colore del carattere filtrerà tutte le celle in base ad esso.

3.4.3.2 Filtrare in base a più colori di sfondo con la funzione definita dall'utente e il comando Filtro

Se desideri filtrare le celle in base a più colori di sfondo, applica il metodo seguente.

Supponendo di voler filtrare tutte le celle con i colori di sfondo arancione e blu nella colonna B come mostrato nell'immagine sottostante. In primo luogo, è necessario calcolare l'indice di colore di queste celle.

1. premi il altro + F11 tasti contemporaneamente.

2. Nel Microsoft Visual Basic, Applications Edition finestra, fare clic inserire > Modulo. Quindi copia il codice VBA sottostante nella finestra del codice.

Codice VBA: ottieni l'indice del colore di sfondo della cella

Function GetColor(x As Range) As Integer
GetColor = x.Interior.ColorIndex
End Function

3. premi il altro + Q i tasti per chiudere il file Microsoft Visual Basic, Applications Edition finestra.

4. In una nuova colonna, digitare un'intestazione nella prima cella (questa cella dovrebbe trovarsi sulla stessa riga dell'intestazione dell'intervallo originale).

5. Seleziona una cella vuota accanto alla cella di intestazione (qui seleziono E2), inserisci la formula sottostante e premi il tasto entrare chiave. Seleziona la cella del risultato e trascinala Maniglia di riempimento automatico sopra le celle sottostanti per applicare questa formula.

= GetColor (B2)

Nota: Se la cella non ha un colore di riempimento, restituisce -4142.

6. Selezionare le celle della colonna helper (includi intestazione), fare clic su Dati > Filtro per aggiungere un filtro alla colonna.

7. Fare clic sulla freccia a discesa per aprire l'elenco a discesa, quindi configurare come segue.

Ora le celle vengono filtrate in base ai colori di sfondo specificati come mostrato nell'immagine sottostante.

3.4.3.3 Filtra facilmente per carattere o colore di sfondo con uno strumento straordinario

Indubbiamente, è facile applicare la funzione incorporata Filtra per colore per filtrare le celle in base al carattere o al colore di sfondo. Tuttavia, lo svantaggio è che la casella di riepilogo a discesa copre il contenuto dei dati originali in modo che non possiamo visualizzare i dati in qualsiasi momento per scegliere correttamente un carattere o un colore di sfondo. Per evitare questo problema, qui consigliamo il Filtro speciale utilità di Kutools for Excel.

1. Seleziona l'intervallo di colonne in cui filtrerai le celle in base al carattere o al colore di sfondo, quindi fai clic su Kutools Plus > Filtro speciale > Filtro speciale.

2. Nel Filtro speciale finestra di dialogo, configurare come segue.

Quindi tutte le celle con il colore del carattere specificato o il colore di sfondo nell'intervallo selezionato vengono filtrate.

3.4.4 Filtrare le celle che contengono le formule

Se hai un lungo elenco di dati che contiene sia valori effettivi che formule e devi solo filtrare le celle della formula, cosa puoi fare? Questa sezione fornisce due metodi per ottenerlo.

3.4.4.1 Filtrare le celle della formula con la funzione definita dall'utente e il comando Filtro

In primo luogo, è necessario scoprire tutte le celle della formula nell'elenco con la funzione definita dall'utente, quindi applicare il comando Filtro per filtrare le celle della formula in base ai risultati.

1. premi il altro + F11 chiavi per aprire il Microsoft Visual Basic, Applications Edition finestra.

2. Nel Microsoft Visual Basic, Applications Edition finestra, fare clic inserire > Modulo, quindi copia il codice VBA sottostante nella finestra del codice.

Function HasFormula(Cell)
HasFormula = Cell.HasFormula
End Function

3. premi il altro + Q i tasti per chiudere il file Microsoft Visual Basic, Applications Edition finestra.

4. Selezionare una cella vuota, questa cella dovrebbe essere nella stessa riga della cella che si desidera controllare se si tratta di una cella formula, immettere la formula seguente in essa e quindi premere il tasto entrare chiave. Seleziona la cella del risultato, trascinala Maniglia di riempimento automatico sopra le celle sottostanti per applicare questa formula.

= HasFormula (C2)

Come mostrato nell'immagine sopra, i risultati sono FALSO ed VERO, che indicano che se la cella corrispondente è una cella formula, restituisce VERO, altrimenti restituisce FALSO.

5. Selezionare le celle dei risultati (includere la cella di intestazione), fare clic su Dati > Filtro per aggiungervi un filtro.

6. Fare clic sulla freccia del menu a discesa, selezionare solo la casella accanto a TRUE casella, quindi fare clic OK.

Quindi puoi vedere tutte le celle della formula sono filtrate.

3.4.4.2 Filtra facilmente le celle delle formule con uno strumento straordinario

Qui dimostra l'utilità Filtro speciale di Kutools for Excel per aiutarti a filtrare facilmente le celle della formula in un elenco con solo diversi clic.

1. Selezionare l'elenco in cui si desidera filtrare tutte le celle della formula, quindi fare clic su Kutools Plus > Filtro speciale > Formula del filtro.

2. Il Kutools for Excel la finestra di dialogo si apre per dirti quante celle sono qualificate, fai clic su OK andare avanti.

Quindi tutte le celle della formula vengono filtrate come mostrato nell'immagine sottostante.

Inoltre, puoi utilizzare il file Filtro speciale caratteristica di Kutools for Excel per filtrare facilmente le celle con altre formattazioni, come ad esempio:

Filtra tutte le celle con commenti, clicca per saperne di più ...

Filtra tutte le celle unite in base a un determinato valore, clicca per saperne di più ...

3.5 Filtro con carattere jolly

A volte, potresti dimenticare i criteri di ricerca esatti durante il filtraggio. In questo caso, ti consigliamo di utilizzare i caratteri jolly.

Ci sono solo 3 caratteri jolly in Excel:

Carattere jolly	Descrizione	Esempio
* (asterisco)	Rappresenta un numero qualsiasi di caratteri	Per esempio, *bacca trova "Mora","fragola","Blueberry" e così via
? (punto interrogativo)	Rappresenta qualsiasi singolo carattere	Per esempio, l? ck trova "leccare","bloccare","mancanza" e così via
~ (marea) seguito da *, ?, o ~	Rappresenta un vero *,? or ~ carattere	Per esempio, Face ~ * Acquista trova "Face * Shop"

Vediamo come utilizzare i caratteri jolly nel filtraggio.

Supponendo che sia necessario filtrare tutte le celle che terminano con Market nella colonna B come lo screenshot qui sotto, si prega di fare quanto segue.

1. In primo luogo, creare un intervallo di criteri. Immettere un'intestazione uguale all'intestazione della colonna originale, quindi digitare i criteri di filtro nella cella sottostante. Vedi screenshot:

2. Clic Dati > Avanzate.

3. In apertura Filtro avanzato finestra di dialogo, configurare come segue.

Quindi tutte le celle che terminano con Market vengono filtrate. Vedi screenshot:

L'utilizzo dei caratteri jolly * e ~ nel filtraggio è lo stesso delle operazioni precedenti.

3.6 Filtro con casella di ricerca incorporata

Se utilizzi Excel 2010 o le versioni successive, potresti notare che è presente una casella di ricerca incorporata nel filtro di Excel. Questa sezione dimostrerà come utilizzare questa casella di ricerca per filtrare i dati in Excel.

Come mostrato nell'immagine sottostante, vuoi filtrare tutte le celle che contengono "Mercato", la casella di ricerca può aiutarti a farlo facilmente.

1. Selezionare l'intervallo di colonne in cui filtrare i dati, fare clic su Dati > Filtro per aggiungervi un filtro.

2. Fare clic sulla freccia del menu a discesa, immettere "Mercato" nella casella di ricerca, quindi fare clic su OK.

Puoi vedere tutti i testi qualificati sono elencati in tempo reale mentre inserisci i dati nella casella di ricerca.

Quindi tutte le celle contengono "Mercato" vengono filtrate come mostrato nell'immagine sottostante.

---

4. Copia solo i dati visibili (ignora i dati nascosti o filtrati)

Per impostazione predefinita, Excel copia sia le celle visibili che quelle nascoste. Se desideri copiare solo le celle visibili dopo il filtraggio, puoi provare uno dei metodi seguenti.

Copia i dati visibili solo con i tasti di scelta rapida

È possibile utilizzare i tasti di scelta rapida per selezionare solo le celle visibili, quindi copiarle e incollarle manualmente nella posizione necessaria.

1. Selezionare l'intervallo in cui si desidera copiare solo le celle visibili. In questo passaggio vengono selezionate sia le celle visibili che quelle nascoste.

2. premi il altro ed ; chiavi allo stesso tempo. Ora vengono selezionate solo le celle visibili.

3. premi il Ctrl + C per copiare le celle selezionate, quindi premere Ctrl + V tasti per incollarli.

Copia facilmente i dati visibili solo con uno strumento straordinario

Qui introdurre il Incolla su visibile caratteristica di Kutools for Excel per copiare facilmente i dati visibili solo in Excel. Inoltre, con questa funzione, puoi copiare e incollare i valori solo nelle celle visibili in un intervallo che è stato filtrato.

1. Selezionare l'intervallo filtrato in cui si desidera copiare solo le celle visibili, quindi fare clic su Kutools > Escursione > Incolla to Visibile > Tutti / Solo incolla valori.

Per le celle formula, scegli Tutti copia sia il risultato che le formule, scegli Solo incolla valori copia solo i valori effettivi.

2. Nel spuntare Incolla su intervallo visibile finestra di dialogo, scegliere una cella vuota per l'output delle celle copiate, quindi fare clic su OK.

Quindi solo le celle visibili nell'intervallo filtrato selezionato vengono copiate e incollate in una nuova posizione.

Nota: Se l'intervallo di destinazione è stato filtrato, i valori selezionati verranno incollati solo nelle celle visibili.

---

5. Elimina le righe nascoste o visibili dopo il filtraggio

Per un elenco filtrato, potrebbe essere necessario eliminare le righe nascoste per mantenere solo i dati visibili. Qui in questa sezione imparerai tre metodi per eliminare le righe nascoste o visibili in un elenco filtrato in Excel.

Elimina tutte le righe nascoste dal foglio di lavoro corrente con codice VBA

Il codice VBA sottostante può aiutare a eliminare tutte le righe nascoste dal foglio di lavoro corrente in Excel.

Nota: Questo VBA rimuove non solo la riga nascosta nell'elenco filtrato, ma rimuove anche le righe che hai nascosto manualmente.

1. Nel foglio di lavoro contiene righe nascoste che si desidera rimuovere, premere il tasto altro + F11 tasti contemporaneamente per aprire il file Microsoft Visual Basic, Applications Edition finestra.

2. Nel Microsoft Visual Basic, Applications Edition finestra, fare clic inserire > Modulo, e quindi copia il codice VBA sottostante nella finestra del modulo.

Codice VBA: elimina tutte le righe nascoste dal foglio di lavoro corrente

Sub RemoveHiddenRows()
    Dim xRow As Range
    Dim xRg As Range
    Dim xRows As Range
    On Error Resume Next
    Set xRows = Intersect(ActiveSheet.Range("A:A").EntireRow, ActiveSheet.UsedRange)
    If xRows Is Nothing Then Exit Sub
        For Each xRow In xRows.Columns(1).Cells
            If xRow.EntireRow.Hidden Then
                If xRg Is Nothing Then
                    Set xRg = xRow
                Else
                    Set xRg = Union(xRg, xRow)
                End If
            End If
        Next
        If Not xRg Is Nothing Then
            MsgBox xRg.Count & " hidden rows have been deleted", , "Kutools for Excel"
            xRg.EntireRow.Delete
        Else
            MsgBox "No hidden rows found", , "Kutools for Excel"
        End If
End Sub

3. premi il F5 chiave per eseguire il codice.

4. Quindi a Kutools for Excel la finestra di dialogo si apre per dirti quante righe nascoste sono state rimosse, fai clic su OK per chiuderlo.

Ora tutte le righe nascoste (incluse le righe nascoste automaticamente e le righe nascoste manualmente) vengono rimosse.

Elimina le righe visibili dopo aver filtrato con la funzione Vai a

Se desideri rimuovere solo le righe visibili in un determinato intervallo, il file Vai la funzione può aiutarti.

1. Selezionare l'intervallo filtrato in cui si desidera rimuovere le righe visibili, premere il tasto F5 chiave per aprire il Vai la finestra di dialogo.

2. Nel Vai finestra di dialogo, fare clic su Special pulsante.

3. Nel Vai a speciale finestra di dialogo, selezionare il Solo celle visibili opzione, quindi fare clic OK pulsante.

4. Ora tutte le celle visibili sono selezionate. Fare clic con il pulsante destro del mouse sull'intervallo selezionato e fare clic Elimina riga nel menu di scelta rapida.

Quindi tutte le celle visibili vengono eliminate.

Elimina facilmente le righe nascoste o visibili dopo aver filtrato con uno strumento straordinario

I metodi di cui sopra sono noiosi e richiedono molto tempo. Qui consiglio il Elimina righe e colonne nascoste (visibili) caratteristica di Kutools for Excel. Con questa funzione, puoi eliminare facilmente le righe nascoste o visibili non solo in un intervallo selezionato, ma anche nel foglio di lavoro corrente, in più fogli di lavoro selezionati o nell'intera cartella di lavoro. Si prega di fare quanto segue.

1. Selezionare un intervallo da cui si desidera rimuovere tutte le righe nascoste o visibili.

Note:

2. Clic Kutools > Elimina > Elimina righe e colonne nascoste (visibili).

3. Nel Elimina righe e colonne nascoste (visibili) finestra di dialogo, è necessario configurare come segue.

4. Quindi tutte le righe visibili o nascoste vengono eliminate contemporaneamente. Nel frattempo, viene visualizzata una finestra di dialogo per dirti il ​​numero di righe eliminate, fai clic su OK per terminare l'intera operazione.

---

6. Filtra su più fogli

Normalmente, è facile filtrare i dati in un foglio di lavoro. In questa sezione imparerai come filtrare i dati con gli stessi criteri su più fogli di lavoro che hanno una struttura dati comune.

Supponendo che una cartella di lavoro contenga tre fogli di lavoro come mostrato nell'immagine sottostante, ora vuoi filtrare i dati su questi tre fogli di lavoro contemporaneamente con gli stessi criteri "Prodotto = KTE", Il codice VBA sottostante può farti un favore.

1. premi il altro + F11 chiavi per aprire il Microsoft Visual Basic, Applications Edition finestra.

2. Nel Microsoft Visual Basic, Applications Edition finestra, fare clic inserire > Modulo, e quindi copia il codice VBA sottostante nella finestra del modulo.

Codice VBA: filtra i dati su più fogli di lavoro contemporaneamente

Sub apply_autofilter_across_worksheets()
'Updateby Extendoffice 20210518
    Dim xWs As Worksheet
    On Error Resume Next
    For Each xWs In Worksheets
        xWs.Range("A1").AutoFilter 1, "=KTE"
    Next
End Sub

Nota: La linea "Xws.Range ("A1" .AutoFilter 1, "= KTE")"Nel codice indica che filtrerai i dati nella colonna A con il criterio = KTE e il numero 1 è il numero della colonna della colonna A. Puoi modificarli secondo le tue necessità. Ad esempio, se desideri filtrare tutti i numeri maggiori di 500 nella colonna B, puoi modificare questa riga in "Xws.Range ("B1" .AutoFilter 2, "> 500")".

3. premi il F5 chiave per eseguire il codice.

Quindi le colonne specificate vengono filtrate contemporaneamente in tutti i fogli di lavoro della cartella di lavoro corrente. Vedere i risultati di seguito.

---

7. Riapplicare il filtro dopo aver modificato i dati

A volte potresti aver apportato modifiche a un intervallo filtrato. Tuttavia, il risultato del filtro rimane lo stesso indipendentemente dalle modifiche apportate all'intervallo (vedi screenshot sotto). In questa sezione, ti mostreremo due metodi per riapplicare manualmente o automaticamente il filtro sull'intervallo corrente in modo che le modifiche apportate siano incluse.

Riapplica manualmente il filtro con il comando Riapplica

Excel ha un file Riapplica funzione per aiutare a riapplicare manualmente il filtro. Puoi candidarti come segue.

Clicchi Dati > Riapplica per riapplicare il filtro nel foglio corrente.

Quindi puoi vedere che l'intervallo filtrato viene riapplicato per includere le modifiche apportate.

Riapplica automaticamente il filtro con il codice VBA

Se l'elenco filtrato deve essere modificato frequentemente, è necessario fare clic ripetutamente per applicare questa funzione Riapplica. Fornisci qui un codice VBA per aiutare a riapplicare automaticamente un filtro in tempo reale quando i dati cambiano.

1. Nel foglio di lavoro contiene il filtro che si desidera riapplicare automaticamente, fare clic con il pulsante destro del mouse sulla scheda del foglio e selezionare Visualizza codice.

2. In apertura Microsoft Visual Basic, Applications Edition finestra, copia il codice VBA sottostante nella finestra del codice.

Codice VBA: riapplica automaticamente il filtro quando si modificano i dati

Private Sub Worksheet_Change(ByVal Target As Range)
   Sheets("Sheet2").AutoFilter.ApplyFilter
End Sub

Nota: Nel codice, "Sheet2" è il nome del foglio di lavoro corrente. Puoi cambiarlo in base alle tue esigenze.

3. premi il altro + Q i tasti per chiudere il file Microsoft Visual Basic, Applications Edition finestra.

D'ora in poi, quando si modificano i dati nell'elenco filtrato, il risultato filtrato verrà regolato dinamicamente. Guarda l'immagine gif qui sotto.

---

8. Cancella o rimuovi il filtro

Abbiamo imparato come aggiungere, applicare e utilizzare il filtro nel contenuto di cui sopra. Qui impareremo come cancellare o rimuovere il filtro in Excel.

8.1 Cancella un filtro da una colonna

Dopo aver applicato il filtro in una colonna, se si desidera cancellarlo, fare clic sull'icona del filtro, quindi fare clic su Cancella filtro da "Nome intestazione" Dal menu a discesa.

8.2 Cancella tutti i filtri in un foglio di lavoro

Se hai applicato il filtro a più colonne e desideri cancellarle tutte contemporaneamente, fai clic su Dati > Chiaro.

Quindi tutti i filtri vengono cancellati come mostrato nell'immagine sottostante.

8.3 Cancella i filtri da tutti i fogli di lavoro nella cartella di lavoro corrente

Supponendo di aver applicato filtri su più fogli di lavoro in una cartella di lavoro e di voler cancellare questi filtri contemporaneamente. Il codice VBA sottostante può farti un favore.

1. Apri la cartella di lavoro, cancellerai tutti i filtri, quindi premi il tasto altro + F11 tasti contemporaneamente.

2. In apertura Microsoft Visual Basic, Applications Edition finestra, fare clic inserire > Modulo, e quindi copia il codice VBA sottostante nella finestra del modulo.

Codice VBA: cancella i filtri da tutti i fogli di lavoro nella cartella di lavoro corrente

Sub Auto_Open()
'Updated by Extendoffice 20201113
    Dim xAF As AutoFilter
    Dim xFs As Filters
    Dim xLos As ListObjects
    Dim xLo As ListObject
    Dim xRg As Range
    Dim xWs As Worksheet
    Dim xIntC, xF1, xF2, xCount As Integer
    Application.ScreenUpdating = False
    On Error Resume Next
    For Each xWs In Application.Worksheets
        xWs.ShowAllData
        Set xLos = xWs.ListObjects
        xCount = xLos.Count
        For xF1 = 1 To xCount
         Set xLo = xLos.Item(xF1)
         Set xRg = xLo.Range
         xIntC = xRg.Columns.Count
         For xF2 = 1 To xIntC
            xLo.Range.AutoFilter Field:=xF2
         Next
        Next
    Next
    Application.ScreenUpdating = True
End Sub

3. premi il F5 chiave per eseguire il codice. Quindi tutti i filtri vengono cancellati da tutti i fogli di lavoro nella cartella di lavoro corrente.

8.4 Rimuovere tutti i filtri in un foglio di lavoro

I metodi precedenti aiutano solo a cancellare lo stato filtrato ei filtri rimangono ancora nel foglio di lavoro. Se desideri rimuovere tutti i filtri da un foglio di lavoro, prova i metodi seguenti.

Rimuovere tutti i filtri in un foglio di lavoro disattivando il filtro

Clicchi Dati > Filtro per disattivare la funzione (il pulsante Filtro non è nello stato di evidenziazione).

Rimuovi tutti i filtri in un foglio di lavoro con il tasto di scelta rapida

Inoltre, puoi applicare il tasto di scelta rapida per rimuovere tutti i filtri in un foglio di lavoro.

Nel foglio di lavoro contiene i filtri che si desidera rimuovere, premere Ctrl + Shift + L tasti contemporaneamente.

Quindi tutti i filtri nel foglio di lavoro corrente vengono rimossi immediatamente.