3 funzioni di Excel, 1 cella: come creare un motore di dashboard "professionale"

Smetti di perdere ore a ordinare, deduplicare e filtrare manualmente i tuoi dati in Excel. Invece, combina FILTRO, UNICI e ORDINA.PER per creare un motore di dati auto-pulente che fa tutto il lavoro da una sola cella e non ha mai bisogno di aggiornamenti.

Come funziona il trio FILTRA-UNICO-ORDINA PER

Per creare un elenco in Excel che sia filtrato, privo di duplicati e ordinato alfabeticamente tutto in una volta, è necessario combinare insieme queste tre funzioni:

=SORTBY(
UNIQUE(FILTER(array,include,[if_empty])),
UNIQUE(FILTER(array,include,[if_empty])),
[sort_order]
)

A prima vista, potresti notare che una parte della formula si ripete. Non è un errore. Per far funzionare questo motore senza errori, devi riflettere la logica. Spiegherò esattamente perché dobbiamo farlo nel Passo 4 qui sotto.

Fase 1. Il nucleo: FILTRO

Tutto inizia con FILTER, la sala macchine che identifica i dati grezzi che vuoi estrarre:

FILTER(array,include,[if_empty])

• array è l'intervallo di celle o la tabella che vuoi filtrare.
• include è il criterio che indica a Excel cosa mantenere nel filtro.
• [if_empty] (opzionale) è dove si specifica cosa Excel dovrebbe mostrare se non vengono trovate corrispondenze.

Invece di guardare una tabella enorme e disordinata, FILTRO identifica solo le righe che soddisfano i tuoi criteri. Ignora i rumori e passa solo i dati rilevanti al passaggio successivo. Inoltre, a differenza del pulsante filtro standard, questo non nasconde le righe nella tua tabella principale, ma le estrae in una nuova posizione.

Passo 2. Lo strato intermedio: UNICO

Successivamente, UNIQUE agisce come il "buttafuori", eliminando ogni duplicato dall'array filtrato che ha appena ricevuto:

UNIQUE(FILTER(...))

In questo trio, l'intero risultato della funzione FILTER serve come unico argomento di cui UNIQUE ha bisogno. Quando questo livello è completato, hai una lista pulita in cui ogni elemento appare esattamente una volta. Questo è meglio che usare lo strumento Rimuovi Duplicati perché non altera la tua tabella originale e non richiede un aggiornamento manuale se la tua fonte cambia.

Passo 3. Il guscio esterno: SORTBY

L'ultimo passaggio è il confezionamento. Mentre UNIQUE è ottimo per rimuovere i duplicati, mantiene i dati nel loro ordine originale. È qui che SORTBY diventa utile:

=SORTBY(array,sort_array,[sort_order])

• array è il risultato dei due passaggi precedenti.
• sort_array è la logica che Excel utilizza per ordinare l'elenco (vedi Passaggio 4 qui sotto).
• [sort_order] (opzionale) indica a Excel la direzione dell'ordinamento. Usa 1 per crescente (A-Z) o -1 per decrescente (Z-A). In alternativa, lascialo vuoto per attivare il default (crescente).

Fase 4. La logica dello specchio

Excel ha una regola severa: i dati che stai ordinando e i criteri in base ai quali li ordini devono avere esattamente la stessa altezza. Se la tua lista unica filtrata è alta cinque righe, anche le istruzioni di ordinamento devono essere alte cinque righe. Quindi, se provassi a ordinare quei cinque nomi usando la tua tabella originale, le altezze non corrisponderebbero e la formula si interromperebbe.

Ecco perché devi replicare la logica ripetendo la catena UNIQUE(FILTER(...)) come secondo argomento: così ti assicuri che le dimensioni corrispondano perfettamente ogni volta.

=SORTBY(
UNIQUE(FILTER(array,include,[if_empty])), <-- the data to sort 
UNIQUE(FILTER(array,include,[if_empty])), <-- the instructions to sort by 
[sort_order]
)

Il trio magico in azione: La lista dei clienti

Ora che ti ho mostrato il progetto, vediamo il motore in azione. Immagina di avere una tabella Excel chiamata T_Sales e di voler ottenere un elenco live, in ordine alfabetico, dei clienti unici per la regione selezionata nella cella G2.

Quando digiti questa formula nella cella G4, la formula distribuisce i suoi risultati nelle celle sottostanti:

=SORTBY(
UNIQUE(FILTER(T_Sales[Company],T_Sales[Region]=G2,"No Matches")),
UNIQUE(FILTER(T_Sales[Company],T_Sales[Region]=G2,"No Matches")),
1
)

Poiché il set di dati originale è in una tabella Excel, il motore dei dati è a prova di futuro. Se aggiungi nuove righe di dati di vendita in fondo alla tabella, la formula rileva automaticamente l'intervallo esteso. Questo è uno dei vantaggi dell'utilizzo di funzioni che generano array dinamici dalla cella in cui hai scritto la formula. Inoltre, puoi fare riferimento all'intero risultato altrove semplicemente aggiungendo un cancelletto (#)—noto anche come operatore di intervallo generato—al riferimento:

G4#

Trappole finali da evitare

Per mantenere il tuo nuovo motore di dati funzionante senza problemi, tieni a mente queste tre regole:

• Libera il percorso: Le formule di array dinamici hanno bisogno di spazio vuoto per riversare i loro risultati verso il basso. Se un pezzo di testo sconosciuto o un'altra tabella si trova sulla strada, Excel mostrerà un errore #SPILL!.
• Niente formule all'interno delle tabelle: Anche se dovresti usare una tabella come fonte dei dati, la tua formula deve trovarsi in una cella normale. Questo perché le tabelle di Excel non supportano lo spilling dinamico.
• Lasciare una colonna di buffer: Quando si analizzano dati al di fuori di una tabella di Excel, lascia sempre una colonna di spazio tra la tabella e il punto in cui stai scrivendo. Altrimenti, Excel penserà che tu stia aggiungendo altri dati alla tabella e quindi "prenderà" la tua analisi.

Allontanandoti dagli strumenti manuali di Excel e adottando gli array dinamici, costruisci sistemi che crescono insieme ai tuoi dati. Questo trio è il punto di partenza perfetto per esplorare altre combinazioni di funzioni di Excel—come INDEX con XMATCH, IF con AND e OR, ed EOMONTH con SEQUENCE—che possono automatizzare ulteriormente le tue attività quotidiane.