Questo tutorial dimostrerà come creare grafici Excel con formattazione condizionale in tutte le versioni di Excel: 2007, 2010, 2013, 2016 e 2022.
La formattazione condizionale è la pratica di assegnare una formattazione personalizzata alle celle di Excel, colore, carattere, ecc., in base ai criteri (condizioni) specificati. La funzione aiuta ad analizzare i dati, trovare valori statisticamente significativi e identificare modelli all'interno di un determinato set di dati.
Ma sfortunatamente, Excel non offre strumenti integrati per applicare la formattazione condizionale ai grafici di Excel.
Tuttavia, con una goccia di magia del foglio di calcolo, nessun problema è insormontabile. In questo tutorial imparerai come creare un grafico dinamico con formattazione condizionale in cui i punti dati tracciati vengono formattati automaticamente in base ai rispettivi valori effettivi.
Ad esempio, dai un'occhiata a questo istogramma, che analizza le prestazioni di un responsabile delle vendite in un periodo di otto mesi. Nota come il colore della colonna si regola automaticamente in base al suo valore sottostante:
Iniziare
Considera questo set di dati di esempio contenente dati sulle prestazioni di vendita:
Prima di iniziare, sposta il set di dati in modo da avere tre righe vuote sopra la tabella con i dati non elaborati. Le righe verranno utilizzate per impostare le regole di formattazione condizionale.
Passaggio 1: prepara i dati del grafico.
Per far funzionare le cose, devi aggiungere alcuni dati extra del grafico all'equazione. Per tua comodità, dai un'occhiata a come dovrebbero apparire i dati del tuo grafico entro la fine della fase di preparazione:
Il nostro primo passo è definire i valori limite (condizioni) che dovrebbero suddividere i valori effettivi in più categorie, rendendo possibile la loro mappatura come serie di dati separate.
Dallo screenshot qui sopra, ad esempio, puoi vedere che i dati di vendita per un dato mese devono rientrare nell'intervallo da $ 90.000 a $ 150.000 per essere considerati soddisfacenti.
Per creare le regole, usa le tre righe vuote sopra il set di dati:
- Riga 1 (etichetta): Usa queste celle per etichettare le regole di formattazione.
- Riga 2 (valore minimo): I valori in questa riga definiscono il limite inferiore per ogni condizione.
- Riga 3 (valore massimo): Allo stesso modo, questi valori definiscono i limiti superiori.
Con questo metodo, puoi creare tutte le regole di formattazione condizionale che desideri, assicurati solo che gli intervalli di valori non si sovrappongano.
Man mano che vai avanti nell'impostazione del grafico, progetta gli elementi della legenda del grafico personalizzati che forniranno il contesto per il grafico. Inserisci questa formula in C4 e copialo su D4 trascinando la maniglia di riempimento:
| 1 | =C1&": da "&TEXT(C2, "$#,##")&" a "&TEXT(C3, "$#,##") |
La formula mette insieme un'etichetta dinamica dall'aspetto ordinato basata sulle regole di formattazione condizionale precedentemente impostate. La funzione TESTO formatta i valori come valuta. Ma se il tuo tipo di dati è diverso, applica invece questa formula:
| 1 | =C1&": da "&TEXT(C2, "#,##")&" a "&TEXT(C3, "#,##") |
O questo quando lavori con le percentuali:
| 1 | =C1&": da "&TEXT(C2, "#%")&" a "&TEXT(C3, "#%") |
Infine, trova i valori del grafico. Digita la seguente formula in C5, copialo fino a C12 e attraverso le celle corrispondenti in colonna D:
| 1 | =SE(E(C$2<$B5,$B5<=C$3),$B5,NA()) |
La formula confronta un dato valore effettivo in colonna B rispetto ai valori limite specificati con l'aiuto delle funzioni SE e AND. Se il valore rientra nell'intervallo, viene copiato in questa colonna. In caso contrario, la formula restituisce l'errore #N/D per impedire che lo stesso valore venga tracciato più volte.
Passaggio 2: imposta un istogramma.
Dopo aver raccolto tutti i dati del grafico, imposta un semplice grafico a colonne o un grafico a barre in alternativa:
- Evidenzia tutti i dati del grafico ad eccezione delle colonne contenenti i valori effettivi e le regole tenendo premuto il tasto Ctrl chiave (LA4:LA12 e DO4:RE12).
- Vai al Inserire scheda.
- Selezionare "Inserisci colonna o grafico a barre.”
- Scegliere "Colonna in cluster/barra in cluster.”
Di conseguenza, ti ritroverai con un grafico con punti dati tracciati in base alle regole di formattazione condizionale, proprio come promesso:
Passaggio 3: modifica i valori di Sovrapposizione e Larghezza spazio.
Prima di chiudere questo tutorial, risolvi il problema delle colonne fuori posto causate dai valori vuoti invisibili (gli errori #N/A) tracciati sul grafico.
Fare clic con il pulsante destro del mouse su una delle colonne e selezionare "Formato serie di dati” dal menu contestuale che si apre.
Nel riquadro attività, modifica la posizione e la larghezza delle colonne:
- Passa a Opzioni di serie scheda.
- Modificare "Sovrapposizione di serie" a "100%.”
- Impostare il Larghezza del gioco a "60%.”
Passaggio 4: regola la combinazione di colori.
Infine, aggiungi gli ultimi ritocchi. Senza chiudere il Formato serie di dati riquadro attività, modificare la combinazione di colori del grafico:
- Vai al Riempi e riga scheda.
- Sotto "Riempire," scegliere "Riempimento solido.”
- Clicca il "Colore di riempimento"icona e seleziona" verde dalla tavolozza dei colori.
Una volta lì, ricolora le altre serie di dati, cambia il titolo del grafico e il tuo grafico completamente dinamico con formattazione condizionale è pronto!
