Evidenziare sopra e sotto media in Excel Grafico a linee
Per vedere visivamente quando la linea sul grafico è al di sopra o al di sotto della media delle serie, possiamo semplicemente inserire una linea media sul grafico. Assomiglierà a questo.
È attraente? O questo?
Posso indovinare la tua risposta.
Allora come facciamo a creare questo grafico che evidenzi l’area quando la linea è sopra o sotto la media? Impariamo passo dopo passo.
Evidenzia l’area quando la linea è al di sopra e al di sotto della media Il modo migliore per imparare è imparare con l’esempio. Quindi iniziamo con un esempio.
Qui ho alcuni dati che raccontano le vendite totali effettuate dai singoli venditori. Voglio visualizzare questi dati in un grafico.
Passaggio 1: aggiungi tre colonne helper
Per creare un grafico del genere, avremo bisogno di tre colonne helper. Le prime colonne helper avranno dati medi. La seconda colonna helper conterrà tutti i punti in cui le vendite sono maggiori della media. E la terza colonna avrà i punti in cui le vendite sono inferiori alla media.
Quindi la formula nella colonna della media sarà:
|
=AVERAGE($B$2:$B$7) |
Scrivi questa formula nella prima cella della colonna della media e trascinala verso il basso. Assicurati che tutte le celle abbiano gli stessi valori. Abbiamo usato link: / excel-range-name-what-is-an-absolute-reference-in-excel [riferimento assoluto] in modo che i riferimenti non cambino quando la formula viene copiata.
Nella colonna successiva, abbiamo bisogno di tutti i valori UP. Sarà una differenza positiva tra i dati medi e di vendita. In altre parole, dobbiamo sottrarre i dati medi dalle vendite solo se le vendite sono maggiori della media.
La formula in D2 sarà:
|
=IF(B2>C2,B2-C2,””) |
Trascinalo verso il basso.
Nella colonna successiva, abbiamo bisogno di tutti i valori Down. Sarà una differenza negativa tra la media e i dati di vendita. In altre parole, dobbiamo sottrarre i dati medi dalle vendite solo se le vendite sono inferiori alla media.
La formula per E2 sarà:
|
=IF(B2<C2,B2-C2,””) |
Trascinalo verso il basso.
Passaggio 2: selezionare consulente di vendita e vendite e inserire un grafico a linee: nel nostro esempio, selezioniamo l’intervallo A1: C7. Vai a Inserisci → link: / tips-best-charts-in-excel-and-how-to-use-them [Charts] → Line and Area → Line with Markers. Abbiamo un grafico che sarà simile a questo.
Passaggio 3: aggiungi ancora una volta la serie media
Fare clic con il tasto destro sul grafico e fare clic sull’opzione Seleziona dati. Qui, fai clic sul pulsante Aggiungi nella sezione Legenda e voci. Seleziona l’intervallo medio C2: C7 come valori della serie. Inserirà una linea sulla linea media originale. Ne avremo bisogno nella formattazione. È importante.
Passaggio 4: Aggiungi serie su e giù ai dati
Quando hai aggiunto di nuovo la serie media, aggiungi in modo simile le serie Su e Giù una per una alla serie. Infine, avrai un grafico simile a questo. questo non assomiglia per niente a quello che vogliamo che sia, giusto? Aspetta, si evolverà.
Passaggio 5: modificare la seconda serie media, le serie su e giù in grafico ad area in pila Fare clic con il pulsante destro del mouse su qualsiasi serie nel grafico e fare clic sull’opzione Modifica tipo di grafico serie di dati. Questo cambierà molto il grafico e inizierà ad assumere una forma che vogliamo che sia questo grafico a linee Excel.
Passaggio 6: selezionare Nessun riempimento per il grafico in pila dell’area media:
Fare clic con il tasto destro sull’area della parte media e fare clic sulla serie di dati di formato. In linea con l’opzione di riempimento, vai a riempimento e seleziona l’opzione nessun riempimento.
Ora il grafico sembra esattamente come volevamo che fosse. Abbiamo solo bisogno di fare un po ‘di formattazione per adattarlo al nostro tema di dashboard / presentazione.
Passaggio 7: formatta il grafico in base al tema.
Rimuovi tutte le cose meno necessarie dal grafico. Ho rimosso la legenda media dal grafico poiché non ne ho bisogno. Ho rimosso le linee della griglia perché voglio che il mio grafico abbia un aspetto più pulito. Ho cambiato il colore dell’area in alto in verde da allora e in basso in rosso. Ho chiamato il titolo del grafico “Vendite rispetto alla media”.
Finalmente ho questo grafico.
Nota: * Questo grafico può essere utilizzato come grafico degli obiettivi e dei risultati. Basta cambiare i valori della serie media in target e boom, avrai il grafico target creativo vs risultato in Excel.
Scarica il file modello qui sotto:
| Questa è un’altra funzione essenziale della dashboard. Questo ti aiuta a riassumere i valori su condizioni specifiche.