Excel Filtri automatici e Filtri avanzati

Convertire un intervallo di celle in una tabella Excel

Autore: Andrea Pacchiarotti
Ultimo aggiornamento: 20 Maggio 2021
Categoria: Microsoft Office Excel Filtri automatici e Filtri avanzati

Excel riferimenti con i $
Excel Filtri automatici e Filtri avanzati

I filtri in un foglio di lavoro Excel consentono di trovare in modo rapido i valori desiderati. È possibile filtrare una o più colonne per controllare i dati da mostrare e da escludere. In Excel esistono sia i filtri automatici che i filtri avanzati.

Sommario Excel Filtri automatici e Filtri avanzati

Excel Filtro automatico o, semplicemente, Filtro

Quando si filtrano i dati con il filtro automatico (nelle ultime versioni di Excel chiamato semplicemente Filtro), alcune righe vengono nascoste se i valori in una o più colonne non soddisfano i criteri impostati. È possibile filtrare i valori numerici o di testo (a seconda del tipo dei dati contenuti nella colonna, Microsoft Excel visualizza Filtri per numeri o Filtri per testo), oppure filtrare in base al colore per le celle che hanno sfondo o testo formattato.

Per impostare un filtro in Excel:

  1. Selezionare i dati da filtrare o selezionare una cella dell’intervallo dati
  2. Nel gruppo Ordina e filtra della scheda Dati cliccare Filtro (oppure cliccare l’icona Ordina e filtra e poi Filtro dal gruppo Modifica della scheda Home. È anche possibile filtrare i dati a cui è stato applicato Formatta come tabella e anche le tabelle pivot)
  3. Cliccare sulla freccia nell'intestazione di colonna per visualizzare un elenco che consente di selezionare le opzioni per il filtro
  4. Filtrare in base a uno dei seguenti metodi
    1. Scrivendo nella casella di ricerca Cerca (è possibile utilizzare caratteri jolly, ad esempio un asterisco * o un punto interrogativo ?. Premere INVIO per ottenere i risultati. Non c’è distinzione tra maiuscole e minuscole)
    2. Selezionando e deselezionando le caselle di controllo
    3. Utilizzando le voci presenti in Filtra per numeri o in Filtra per testo (a seconda che si stiano filtrando numeri o stringhe); viene visualizzato un menu che consente di filtrare secondo varie condizioni (nelle scelte che implicano l’AND e l’OR considerare che cliccando l’opzione AND si impostano due o più criteri che devono essere soddisfatti contemporaneamente, laddove OR specifica invece che va soddisfatta almeno una delle condizioni definite
    4. Selezionando Filtra per colore (a patto che i valori siano prima stati formattati)
Excel Filtro per numeri, Filtro per testo, Filtro per colore
Excel Filtro per numeri, Filtro per testo, Filtro per colore
Excel Filtro automatico AND e Or
Excel Filtro automatico AND e Or
Esempi di filtraggio con caratteri jolly

Esempi di filtraggio per valori testuali

=”=abc”     restituisce le celle il cui contenuto è uguale ad “abc”
<>abc        restituisce le celle il cui contenuto è diverso da “abc”
abc            restituisce le celle il cui contenuto inizia con “abc”
>abc          restituisce le celle il cui contenuto è alfabeticamente successivo ad “abc”
*abc*        restituisce le celle il cui contenuto contiene “abc”
abc*abc    restituisce le celle il cui contenuto inizia con “abc” e contiene una seconda occorrenza “abc”
?abc          restituisce le celle il cui contenuto inizia con un qualsiasi singolo carattere, seguito da “abc”
=”=???”     restituisce le celle il cui contenuto contiene tre caratteri

Per filtrare i dati in una tabella o in una tabella pivot:

  1. Cliccare in un punto qualsiasi della tabella o della tabella pivot
  2. Nella scheda Home passare a Inserisci filtro dei dati
  3. Nella finestra che si apre selezionare le caselle di controllo per i campi da visualizzare e cliccare OK
  4. Nella finestra che si apre, se si clicca su uno dei pulsanti del filtro dei dati, il filtro verrà applicato automaticamente alla tabella o alla tabella pivot (per selezionare più voci, tenere premuto CTRL e selezionare le voci interessate; per cancellare i filtri, selezionare l’icona in alto a destra: Cancella filtro)

Per contare i valori filtrati è possibile usare la funzione SUBTOTALE. Ad esempio se si fossero filtrati i dati e si volessero contare gli stipendi presenti in colonna D di un intervallo iniziale (e quindi non filtrato) A1:E20, basterebbe inserire la funzione SUBTOTALE(2;A1:A20) dove il primo argomento (num_funzione) può assumere uno dei seguenti valori (il primo numero include i valori nascosti, il secondo li esclude)
1 (o 101) MEDIA
2 (o 102) CONTA.NUMERI
3 (o 103) CONTA.VALORI
4 (o 104) MAX
5 (o 105) MIN
6 (o 106) PRODOTTO
7 (o 107) DEV.ST
8 (o 108) DEV.ST.POP
9 (o 109) SOMMA
10 (o 110) VAR
11 (o 111) VAR.POP

Excel Filtro avanzato

Il filtro avanzato di Excel è utile per impostare filtri più complessi rispetto al Filtro automatico di Excel, permette infatti di strutturare criteri di selezione più articolati applicabili contemporaneamente a diverse colonne. Questi criteri sono specificati nello stesso foglio di calcolo dell’intervallo da filtrare, e non in un menu a discesa come per il filtro automatico. Dunque per eseguire il filtro avanzato di Excel, bisogna specificare un intervallo:

Intervallo criteri e Intervallo elenco devono avere le stesse intestazioni  (per evitare di sbagliare nello scrivere l'etichetta del campo è consigliabile copiarla e incollarla; la loro posizione è indifferente: nell’esempio che segue c’è prima l’Intervallo criteri e poi, separato da una riga vuota, l’Intervallo elenco) e i criteri da applicare alle righe di Intervallo elenco devono essere posizionati sotto ognuna di tali voci.
Un’altra particolarità del filtro avanzato è che c’è la possibilità di filtrare l’elenco sul posto (come fa il filtro automatico) oppure in un’altra posizione.
Anche nei filtri avanzati è possibile usare i caratteri jolly e non c’è distinzione tra maiuscole e minuscole.
Ad esempio si supponga di avere i seguenti dati

Excel Filtro avanzato
Excel Filtro avanzato

Per impostare un filtro avanzato si deve ragionare come nelle query di Access.
Immaginando di voler estrarre dall’intervallo elenco gli ORDINI dal 01/01/22 al 10/01/22 con PREZZI >=100, <>142, <>147, oppure ordini di qualsiasi data con 80 di prezzo e tutti i nomi che iniziano per M, bisogna

  1. scrivere le informazioni relative all’intervallo criteri secondo questi dettami:
    1. se due o più criteri sono sulla stessa riga è come se fossero collegati tramite AND (cioè dovranno essere vere tutte le condizioni per restituire un risultato)
    2. se due o più criteri sono su righe diverse è come se fossero collegati tramite OR (il che le rende indipendenti gli uni dagli altri)
      Excel Filtro avanzato
      Excel Filtro avanzato
    3. Cliccare Ok per ottenere i dati filtrati

Purtroppo modificando i dati dell’Intervallo criteri, i risultati del filtro non si aggiornano, in tal caso viene in aiuto la nuova funzione FILTRO.

La nuova funzione FILTRO di Excel

La funzione FILTRO di Excel filtra un intervallo di dati in base a criteri definiti e ha la seguente sintassi:
=FILTRO(matrice;includi;[se_vuoto])
matrice: obbligatorio. È l'intervallo di dati da filtrare
includi: obbligatorio.  Rappresenta i criteri da filtrare e da applicare all'intervallo dati
se_vuoto: facoltativo. Restituisce una stringa se non ci sono risultati ("" restituiscono una stringa vuota)
La funzione FILTRO restituisce un insieme di valori (matrice) nelle celle adiacenti alla cella dov’è stata inserita la formula: tale comportamento è definito espansione.
Considerando l’Intervallo elenco precedente, scrivendo Sara in H19 e impostando in I19 la funzione
=FILTRO(A9:F21;F9:F21=H19;""), verranno restituiti tutti i record per Sara e, se non c’è nessuna Sara, verrà restituita una stringa vuota ("").
Se in H19 si cambia nome, i dati restituiti si aggiorneranno!
Ecco un altro esempio con due criteri: se in H19 si lascia Sara e si aggiunge un secondo criterio in H20, ad esempio 147, scrivendo in I19 la seguente funzione:
=FILTRO(A9:F21;(F9:F21=H19)*(E9:E21=H20);""), verranno restituiti tutti i record per Sara con valore 147.
Durante l’espansione dei risultati dopo aver dato invio, se sul foglio di lavoro non ci fossero celle libere per estrarre tutti valori corrispondenti ai criteri impostati, nella cella contenente la funzione apparirebbe il messaggio di errore #ESPANSIONE!

È anche possibile ordinare in maniera crescente o decrescente il contenuto di un intervallo o una matrice con la nuova funzione DATI.ORDINA di Excel che ha la seguente sintassi:
=DATI.ORDINA(matrice;[indice_ordinamento];[tipo_ordinamento];[per_col])
matrice: obbligatorio. È la matrice o intervallo dati da ordinare
indice_ordinamento: facoltativo. È la colonna rispetto la quale ordinare i dati (indice 1 la prima colonna della tabella, indice 2 la seconda e così via)
tipo_ordinamento: facoltativo. È possibile impostare l'ordinamento in maniera crescente (codice 1) o decrescente (codice -1)
per_col: facoltativo. Valore logico che indica la direzione di ordinamento desiderata. FALSO per ordinare per riga (impostazione predefinita), VERO per ordinare per colonna

Ad esempio volendo ordinare in senso decrescente in base al primo campo, un ipotetico risultato scaturito da FILTRO (immaginando si abbiano 100 risultati ottenuti dal filtro avanzato), si deve scrivere
=DATI.ORDINA(FILTRO(A9:F21;(F9:F21=H19)*(E9:E21=H20);"");1;-1)

Per ordinare i dati rispetto più colonne, i riferimenti ai loro indici vanno tra parentesi graffe {1.2.3} intervallati con un punto, nella sequenza in cui vanno ordinati. Ad esempio:
=DATI.ORDINA(A9:F21;{1.2.3};1)

Ordinamento dati Excel

Per ordinare dati in un foglio di lavoro di Excel

  1. Selezionare i dati da ordinare (un intero intervallo di dati, una sola colonna o una singola cella nella colonna da ordinare)
  2. Nel gruppo Ordina e filtra della scheda Dati cliccare l’icona dell'ordinamento crescente, ovvero dalla A alla Z o decrescente (ZA)

Un errore che alcuni utenti di Excel fanno è di selezionare una singola colonna durante l'ordinamento e scegliere l'opzione errata nella finestra popup successiva che ha le scelte per Espandere la selezione (scegli questo!) o Continuare con la selezione corrente.

Excel Ordinamento
Excel Ordinamento

Per ordinare specificando più criteri di ordinamento

  1. Selezionare una singola cella in un punto qualsiasi dell'intervallo da ordinare
  2. Nel gruppo Ordina e filtra della scheda Dati cliccare Ordina per visualizzare la finestra Ordina (è possibile raggiungere la medesima finestra dalla scheda Home, gruppo Ordina e filtra, icona Ordinamento personalizzato...)
  3. Nell'elenco a discesa Ordina per selezionare la prima colonna in base alla quale eseguire l'ordinamento
  4. Nell'elenco Ordina in base a scegliere Valori, Colore cella, Colore carattere o Icona cella
  5. Nell'elenco Ordine scegliere il criterio di ordinamento
Excel Ordinamento personalizzato
Excel Ordinamento personalizzato

Per un ordinamento ancora più personalizzato, clicca Opzioni…

Possibili problemi


Per saperne di più su Microsoft Excel potrebbero interessarti i video corsi Excel o questi libri:


Se vuoi approfondire alcuni dei temi trattati, visita la pagina con le mie pubblicazioni cartacee e online.

Se l'articolo ti è piaciuto, condividilo!

Segui l'hashtag #AndreaPacchiarotti