Filtraggio delle colonne orizzontali in Excel

Se non sei un utente alle prime armi, devi aver già notato che il 99% di tutto in Excel è progettato per funzionare con tabelle verticali, dove parametri o attributi (campi) passano attraverso le colonne e si trovano informazioni su oggetti o eventi nelle righe. Tabelle pivot, totali parziali, copia di formule con un doppio clic: tutto è personalizzato in modo specifico per questo formato di dati.

Non ci sono però regole senza eccezioni e con una frequenza abbastanza regolare mi viene chiesto cosa fare se una tabella con orientamento semantico orizzontale, oppure una tabella dove righe e colonne hanno lo stesso peso di significato, si imbattesse nell'opera:

E se Excel sa ancora come ordinare orizzontalmente (con il comando Dati – Ordina – Opzioni – Ordina colonne), quindi la situazione con il filtraggio è peggiore: semplicemente non ci sono strumenti integrati per filtrare le colonne, non le righe in Excel. Quindi, se ti trovi di fronte a un compito del genere, dovrai trovare soluzioni alternative di vari gradi di complessità.

Metodo 1. Nuova funzione FILTRO

Se utilizzi la nuova versione di Excel 2021 o un abbonamento a Excel 365, puoi sfruttare la funzionalità appena introdotta FILTRO (FILTRO), che può filtrare i dati di origine non solo per righe, ma anche per colonne. Per funzionare, questa funzione richiede una riga di matrice unidimensionale orizzontale ausiliaria, in cui ogni valore (TRUE o FALSE) determina se mostrare o, al contrario, nascondere la colonna successiva nella tabella.

Aggiungiamo la seguente riga sopra la nostra tabella e scriviamo lo stato di ogni colonna in essa:

• Diciamo che vogliamo sempre visualizzare la prima e l'ultima colonna (intestazioni e totali), quindi per loro nella prima e nell'ultima cella dell'array impostiamo il valore = TRUE.
• Per le colonne rimanenti, il contenuto delle celle corrispondenti sarà una formula che verifica la condizione di cui abbiamo bisogno usando le funzioni И (E) or OR (O). Ad esempio, che il totale sia compreso tra 300 e 500.

Dopodiché, resta solo da usare la funzione FILTRO per selezionare le colonne sopra le quali il nostro array ausiliario ha un valore TRUE:

Allo stesso modo, puoi filtrare le colonne in base a un determinato elenco. In questo caso, la funzione aiuterà COUNTIF (CONTA SE), che controlla il numero di occorrenze del nome della colonna successiva dall'intestazione della tabella nell'elenco consentito:

Metodo 2. Tabella pivot invece della solita

Attualmente, Excel ha un filtro orizzontale integrato per colonne solo nelle tabelle pivot, quindi se riusciamo a convertire la nostra tabella originale in una tabella pivot, possiamo utilizzare questa funzionalità integrata. Per fare ciò, la nostra tabella di origine deve soddisfare le seguenti condizioni:

• avere una riga di intestazione "corretta" di una riga senza celle vuote e unite, altrimenti non funzionerà per creare una tabella pivot;
• non contengono duplicati nelle etichette di righe e colonne: si "comprimeranno" nel riepilogo in un elenco di soli valori univoci;
• contengono solo numeri nell'intervallo di valori (all'intersezione di righe e colonne), perché la tabella pivot applicherà loro sicuramente una sorta di funzione di aggregazione (somma, media, ecc.) e questo non funzionerà con il testo

Se tutte queste condizioni sono soddisfatte, per creare una tabella pivot che assomigli alla nostra tabella originale, questa (quella originale) dovrà essere espansa dal campo incrociato a uno piatto (normalizzato). E il modo più semplice per farlo è con il componente aggiuntivo Power Query, un potente strumento di trasformazione dei dati integrato in Excel dal 2016. 

Questi sono:

1. Convertiamo la tabella in un comando dinamico “intelligente”. Home – Formatta come una tabella (Home — Formatta come tabella).
2. Caricamento in Power Query con il comando Dati – Da tabella/intervallo (Dati – Da tabella/intervallo).
3. Filtriamo la riga con i totali (il riepilogo avrà i suoi totali).
4. Fare clic con il pulsante destro del mouse sull'intestazione della prima colonna e selezionare Decomprimi altre colonne (Unpivot altre colonne). Tutte le colonne non selezionate vengono convertite in due: il nome del dipendente e il valore del suo indicatore.
5. Filtraggio della colonna con i totali che sono entrati nella colonna Attributo.
6. Costruiamo una tabella pivot in base alla tabella flat (normalizzata) risultante con il comando Home — Chiudi e carica — Chiudi e carica in... (Home — Chiudi e carica — Chiudi e carica in...).

Ora puoi utilizzare la possibilità di filtrare le colonne disponibili nelle tabelle pivot, i soliti segni di spunta davanti ai nomi e agli elementi Filtri firma (Filtri etichette) or Filtra per valore (Filtri di valore):

E, naturalmente, quando modifichi i dati, dovrai aggiornare la nostra query e il riepilogo con una scorciatoia da tastiera Ctrl+altro+F5 o squadra Dati – Aggiorna tutto (Dati — Aggiorna tutto).

Metodo 3. Macro in VBA

Tutti i metodi precedenti, come puoi facilmente vedere, non stanno esattamente filtrando: non nascondiamo le colonne nell'elenco originale, ma formiamo una nuova tabella con un determinato insieme di colonne da quella originale. Se è necessario filtrare (nascondere) le colonne nei dati di origine, è necessario un approccio fondamentalmente diverso, ovvero una macro.

Supponiamo di voler filtrare al volo le colonne in cui il nome del manager nell'intestazione della tabella soddisfa la maschera specificata nella cella gialla A4, ad esempio, inizia con la lettera "A" (ovvero, ottieni "Anna" e "Arthur " di conseguenza). 

Come nel primo metodo, implementiamo prima una riga-intervallo ausiliaria, dove in ogni cella il nostro criterio verrà verificato da una formula e verranno visualizzati i valori logici VERO o FALSO per le colonne visibili e nascoste, rispettivamente:

Quindi aggiungiamo una semplice macro. Fare clic con il pulsante destro del mouse sulla scheda del foglio e selezionare il comando Fonte (Codice sorgente). Copia e incolla il seguente codice VBA nella finestra che si apre:

Private Sub Worksheet_Change(ByVal Target As Range) Se Target.Address = "$A$4" Quindi per ogni cella nell'intervallo ("D2:O2") Se cell = True Allora cell.EntireColumn.Hidden = False Altrimenti cell.EntireColumn.Hidden = True End If Next Cell End If End Sub  

La sua logica è la seguente:

• In generale, questo è un gestore di eventi Foglio di lavoro_Cambia, cioè questa macro verrà eseguita automaticamente su qualsiasi modifica a qualsiasi cella del foglio corrente.
• Il riferimento alla cella modificata sarà sempre nella variabile Target.
• Per prima cosa, controlliamo che l'utente abbia cambiato esattamente la cella con il criterio (A4) – questo viene fatto dall'operatore if.
• Quindi inizia il ciclo Per ciascuno… per scorrere le celle grigie (D2:O2) con valori dell'indicatore VERO / FALSO per ciascuna colonna.
• Se il valore della successiva cella grigia è TRUE (true), la colonna non è nascosta, altrimenti la nascondiamo (proprietà nascosto).

•  Funzioni di matrice dinamica da Office 365: FILTRO, ORDINA e UNIC
• Tabella pivot con intestazione multilinea tramite Power Query
• Cosa sono le macro, come crearle e usarle