Schede KPI ed evidenziazioni condizionali

Una scheda KPI è un singolo numero in evidenza che comunica al lettore l'informazione più importante: il fatturato totale, gli ordini di oggi, il valore medio dell'ordine. Le dashboard iniziano solitamente con una riga di queste schede.

Una buona scheda combina tre elementi: un'etichetta chiara, il numero principale ottenuto da una formula e spesso un piccolo confronto, ad esempio la variazione rispetto al periodo precedente.

In questa lezione costruirà numeri KPI con formule di aggregazione, aggiungerà confronti tra periodi e utilizzerà la logica condizionale per evidenziare i valori che richiedono attenzione.

Inizi dalla metrica fondamentale. Il fatturato totale dal foglio Sales è semplicemente un SUM sulla colonna degli importi.

Inserisca questo valore nella cella del valore della scheda:

Per contare gli ordini, utilizzi COUNTA su una colonna che contiene sempre un valore, come l'ID dell'ordine. L'etichetta della scheda, una semplice cella di testo sopra il valore, indica il significato del numero. Mantenga ogni scheda su un solo numero, così sarà leggibile all'istante.

Molti KPI sono rapporti. Il valore medio dell'ordine divide il fatturato totale per il numero di ordini. Lo costruisca a partire da due aggregazioni.

Un modo compatto consiste nell'utilizzare direttamente AVERAGE:

Se preferisce ricavarlo, divida il totale della scheda per il conteggio della scheda. In entrambi i casi, questa scheda mostra ora il valore tipico di una vendita e si aggiorna ogni volta che cambiano i dati.

Un KPI è più significativo se confrontato con un obiettivo. Supponga che l'obiettivo mensile di fatturato si trovi nella cella B1. Calcoli di quanto il valore corrente sia superiore o inferiore all'obiettivo.

Lo scostamento è il valore corrente meno l'obiettivo:

Può anche esprimerlo come percentuale dell'obiettivo con =SUM(Sales!C2:C500)/B1. Visualizzare 1.12 indica al lettore che ha raggiunto il 112 per cento dell'obiettivo. Il confronto trasforma un numero grezzo in una storia.

I lettori amano vedere l'andamento. Calcoli la variazione percentuale rispetto a un periodo precedente. Supponga che il totale di questo periodo si trovi in D2 e quello del periodo precedente in D3.

La formula della crescita è:

Se il valore di questo periodo è 120 e quello precedente era 100, ottiene 0,2, cioè una crescita del 20 per cento. Formatti la cella come percentuale. Un piccolo indicatore di aumento o diminuzione come questo trasforma una scheda statica in un andamento leggibile a colpo d'occhio.

Può mostrare la direzione con parole o simboli usando IF. Legga la cella della variazione e scelga un'etichetta.

Questo restituisce un indicatore di aumento o diminuzione con la percentuale:

Quando la variazione in E1 è positiva, viene visualizzata un'indicazione di crescita; altrimenti ne viene visualizzata una di diminuzione. Unire testo e numero formattato mantiene la scheda compatta e leggibile, senza bisogno di un grafico.

La formattazione condizionale cambia il colore di una cella in base a una regola, così i valori importanti risaltano. Selezioni le celle KPI, apra Formattazione condizionale e aggiunga una regola basata su una formula.

Per segnalare in rosso ogni scheda inferiore all'obiettivo, utilizzi una regola formula come:

Le celle per cui la regola restituisce TRUE ricevono il formato scelto. È così che una dashboard diventa rossa quando le vendite non raggiungono l'obiettivo e verde quando lo superano, senza che nessuno debba esaminare attentamente i numeri.

A volte si desidera visualizzare l'indicatore come testo nella cella stessa, non solo come colore. IF con gli operatori di confronto crea una parola di stato.

Per classificare le prestazioni rispetto all'obiettivo:

Viene visualizzato In linea quando l'obiettivo è raggiunto e In ritardo quando non lo è. Una colonna di stato di questo tipo si abbina bene alla formattazione condizionale che colora la parola, offrendo sia un segnale visivo sia uno testuale.

I KPI reali hanno spesso più di due stati: positivo, attenzione, critico. La funzione IFS controlla le condizioni nell'ordine e restituisce la prima corrispondente; il risultato è più leggibile rispetto a tanti IF annidati.

Per classificare le prestazioni in tre livelli:

IFS verifica le condizioni dall'alto verso il basso, quindi inserisca per prima quella più restrittiva. Abbini ogni livello a una regola di colore e le schede comunicheranno lo stato a colpo d'occhio.

Oltre alle singole regole, le scale di colore applicano una sfumatura graduale a un intervallo di numeri, così i valori più alti e più bassi risaltano senza bisogno di formule. Sono ideali per una colonna di totali regionali o di dati giornalieri.

Selezioni l'intervallo, apra Formattazione condizionale e scelga una scala di colore, ad esempio dal verde al rosso.

• I valori più alti risulteranno verdi, quelli più bassi rossi e quelli intermedi avranno sfumature intermedie.
• La scala si ricalcola automaticamente quando cambiano i dati.

Le scale di colore si abbinano perfettamente alle schede KPI: le schede mostrano il dato principale, mentre la colonna con la scala indica la posizione di ciascun valore nell'intervallo.

Disponga le schede in una riga ordinata nella parte superiore della dashboard. Ogni scheda è un piccolo blocco composto da una cella per l'etichetta, una formula per il valore e un confronto o stato sottostante.

• Scheda 1: Fatturato totale, =SUM(Sales!C2:C500), con uno scostamento dall'obiettivo.
• Scheda 2: Ordini, =COUNTA(Sales!A2:A500).
• Scheda 3: Valore medio dell'ordine, =AVERAGE(Sales!C2:C500), con una percentuale di crescita.

La formattazione condizionale colora le celle dei confronti. La riga offre un riepilogo immediato prima che il lettore scorra i dettagli.

Metta alla prova la sua comprensione della creazione di un indicatore di stato a più livelli.

Ha creato il livello principale di una dashboard:

• Le formule di aggregazione come SUM, COUNTA e AVERAGE hanno prodotto i numeri KPI principali.
• Le formule di scostamento e crescita hanno aggiunto il contesto del confronto con un obiettivo e con un periodo precedente.
• IF e IFS hanno trasformato i numeri in parole di stato come In linea, Da monitorare e Critico.
• La formattazione condizionale con regole basate su formule ha colorato automaticamente le celle per segnalare ciò che conta.

Insieme alle tabelle riepilogative, alle tabelle pivot basate su formule e ai menu a discesa interattivi delle lezioni precedenti, ora può creare una dashboard completa, aggiornata in tempo reale e gestita dalle formule.