Ok, ho pubblicato questa sfida del grafico Excel venerdì. Come te la sei cavata? Sono sicuro che la tua soluzione è migliore della mia. Questa è stata la sfida di una domanda del forum di Excel:

“Come eliminare i valori anomali nel grafico

Quando si visualizzano alcuni grafici, a volte ho bisogno di ignorare alcuni valori anomali. Vorrei sapere se è possibile fare clic sui dati anomali o sulla corrispondente x sull’asse x e il grafico verrà aggiornato senza questi dati anomali. So che posso andare al set di dati e rimuovere i valori anomali, ma voglio semplificare facendolo sul grafico. Molte grazie.”

Ecco l’immagine che questo utente ha pubblicato:

Il grafico ha 2 punti dati periferici. 1 il 4 gennaio e 1 il 7 gennaio. L’utente di Excel ha eliminato manualmente i punti dati e quindi vedrai il grafico finale nella parte inferiore dell’immagine.

Dobbiamo anche aggiungere una linea di tendenza con una formula di linea di tendenza e un valore R, ma questo può essere un passaggio manuale. I dati vanno dal 1 gennaio al 10 gennaio dell’anno 2013. Quindi lascia andare con queste ipotesi.

Quindi, come possiamo creare il grafico finale rimuovendo i punti dati da gennaio 4th e gennaio 7th senza eliminare manualmente quei punti dati? Consente di arrivare ad esso! Qui di seguito troverete una rapida ripartizione su come ho creato la mia soluzione. Di seguito è riportato un tutorial dettagliato passo-passo di questa soluzione Excel. Di seguito vedrai una dimostrazione video di questo suggerimento di Excel. Infine, di seguito troverai una copia del mio foglio di calcolo che puoi scaricare per pianificare con la tecnica dei dati e dei grafici.

Prima di tutto, lascia che ti mostri come un altro utente ha affrontato questo problema. Pete aveva un modo ingegnoso per aggirare questo problema. Prende i punti dati e crea una formula basata sulla pendenza della linea. Ecco la sua soluzione con le sue stesse parole:

Da Pete: “Stavo giocando con la tua nuova sfida del venerdì, e mi è venuta in mente una rotazione diversa sui risultati. Il mio grafico mostra i dati originali e i nuovi dati con gli outler rimossi. Dovrai sbloccare il foglio per visualizzare le formule.

in sostanza, ho preso i dati originali e utilizzato la funzione PENDENZA per trovare la pendenza dei dati, e quindi rispetto a una linea teorica basata sul valore di X usando la formula algebrica per linee Y=MX+B. Con questa formula, e calcolata la pendenza, potrei risolvere per la B. Ora ho usato e SE la formula per restituire un errore #N/se il valore reale e valore teorico differiva da un dato % scelto la convalida dei dati discesa cella. In questo modo l’utente può scegliere in quale percentuale rimuovere gli outler. Ho dovuto includere il 500% e il 100% per consentire all’utente di mostrare i dati originali poiché il punto dati “2” si trova al 710% dal valore teorico calcolato.”

Controlla la sua soluzione qui: Petes Outlyer Solution

Ora ecco la mia soluzione di base per la creazione di una linea Excel che non visualizza valori anomali. Torna domani per vedere la mia soluzione avanzata.

Composizione

1) Creare un Intervallo di Dati del Grafico

2) Designare una Cella come Outlier Livello di Tolleranza

3) Creare Outlier Formula per l’Intervallo di Dati del Grafico

4) Creare Outlier Grafico in Excel

5) Aggiungi linea di tendenza del Grafico, Trendline Formula e il Valore di R al Quadrato

6) Liscia la linea e la pulizia Grafico Spazzatura

Step-by-Step

1) Creare un Intervallo di Dati del Grafico

va Bene, consente di creare il nostro originale intervallo di dati nelle celle A1:B11. Ora consente di creare un grafico a linee rapido in Excel con marcatori per vedere come appare. Sembra quasi esattamente come il primo grafico che hai visto dalla domanda originale.

L’unica differenza è che la nostra linea non è levigata. L’ultimo passo in questo tutorial vi mostrerà come apportare questa modifica. Ma ecco come sarebbe il grafico originale con linee levigate:

2) Designare una cella come livello di tolleranza outlier

Ok, la mia soluzione utilizza una cella di foglio di calcolo per designare quale sia la tolleranza per un outlier. Quindi ho designato la cella D3 nel foglio di calcolo come il mio livello di tolleranza. Guardando i dati, sembra che un punto dati sia designato come outlier nell’esempio dell’utente Excel quando i punti differiscono di circa 7 unità.

Quindi ho inserito un valore di 7 nella cella D3:

Useremo la cella D3 nel passaggio successivo quando creeremo la nostra formula outlier.

3) Crea una formula anomala per l’intervallo di dati del grafico

In primo luogo, mettiamo le nostre date nella cella E2. Questo è semplice come mettere un = A2 nella cella E2.

Ok, questo è il passo che fa funzionare tutto. Quello che dobbiamo fare è confrontare il punto dati corrente con i punti dati sopra e sotto quello corrente. Il confronto vedrà se il punto dati corrente è al di fuori del livello di tolleranza impostato nella cella D3. Essenzialmente, sottrarremo il punto dati corrente dal punto dati di una cella sopra e vedremo se la differenza è maggiore della tolleranza nella cella D3. Quindi ripetere questo passaggio per la cella sotto il punto dati corrente. Se entrambi i punti dati sono maggiori del livello di tolleranza, allora metteremo un NA () nella cella corrente. Se entrambi i valori non sono fuori tolleranza, inseriremo il valore del punto dati corrente in questa cella. Ora, poiché la differenza potrebbe essere negativa, dobbiamo avvolgere questa sottrazione in una funzione di valore assoluto. Quindi nella cella F2 mettiamo questa formula:

=SE(E(ABS(B2-B1)>$D$3,ABS(B2-B3)>$D$3),NA(),B2)

Qui è una ripartizione della formula:

in Primo luogo ci accingiamo a iniziare con un SE la formula, in modo da andare a cella F2 e digitare:

=SE(

perché abbiamo bisogno di confrontare un valore e quindi in base al valore metteremo uno dei 2 valori in questa cella

Ora, come ho descritto sopra, è necessario confrontare i dati attuali punto con la cella e la cella di sotto. E, se entrambi i confronti sono fuori tolleranza, allora non vogliamo mostrare il punto dati. Quindi, dal momento che abbiamo a che fare con un confronto “ENTRAMBI”, dovremmo usare la funzione AND, quindi digitalo in next:

=IF(AND(

Ora come ho detto prima, a volte sottraendo i punti dati il valore finale sarà negativo. E un valore negativo sarà SEMPRE al di sotto della nostra tolleranza, quindi dobbiamo assicurarci che la nostra differenza sia sempre in unità positive poiché il nostro livello di tolleranza è in unità positive. Per fare qualcosa di sempre positivo, dobbiamo avvolgere il nostro confronto in una funzione di valore assoluto, quindi digitiamo la funzione ABS next:

=IF(AND(ABS(

Ora facciamo effettivamente il nostro 1 ° valore di confronto. Lo facciamo semplicemente sottraendo il valore corrente con il valore sopra il punto dati corrente e vediamo se è maggiore del nostro livello di tolleranza nella cella D3. Quindi digita:

=IF(AND(ABS(B2-B1)>D D 3 3

Nota che ho fatto il riferimento D3 e assoluto. Se non sai cos’è un riferimento assoluto, dai un’occhiata a questo post:

Riferendosi agli intervalli nelle formule per i tuoi modelli di dashboard di Excel

Questa è la prima parte della nostra funzione AND e la funzione and è separata da una virgola, quindi inserirai il prossimo confronto. Quindi, consente di digitare il confronto successivo dopo una virgola. Il prossimo confronto consiste nel confrontare il nostro punto dati corrente con il punto dati successivo. Quindi lascia digitare che in:

=IF(AND(ABS(B2-B1)>D D 3 3,ABS(B2-B3)>D D 3 3

Poiché questo è il nostro ultimo confronto di cui dobbiamo preoccuparci, puoi terminare la funzione AND con una parentesi a destra. Ora possiamo inserire una virgola e determinare cosa fare se entrambi i criteri E sono veri. Nel nostro caso, se entrambe le differenze dei punti dati sono maggiori della tolleranza, allora dobbiamo inserire una funzione NA (). Quindi lascia digitare che SE TRUE value:

=SE(E(ABS(B2-B1)>$D$3,ABS(B2-B3)>$D$3),NA()

Se volete saperne di più circa la funzione NA (), check out questo post:

Come Nascondere Zero Grafico a Torta Fetta o istogramma in Pila Sezione

Ora siamo pronti per la finale, se NON è VERO. Quindi inserisci una virgola e digita il valore IF FALSE. In questo caso, vogliamo inserire il valore effettivo del punto dati della nostra cella dati B2. Quindi digitare un punto e virgola e poi B2)

=SE(E(ABS(B2-B1)>$D$3,ABS(B2-B3)>$D$3),NA(),B2)

premere “enter” e il valore nella Cella F2 dovrebbe assomigliare a questa:

Ora che abbiamo le nostre formule, è possibile copiare fino all’ultimo punto dati nelle colonne a e B. Quindi copiare questa formula verso il basso e che sarà simile a questa:

si Noti che i punti dati per 1/4-1/7 sono ora mostrando un valore #N/A. E gli altri valori sono gli stessi che vedi nel nostro set di dati originale nella colonna B. È COSÌ MERAVIGLIOSO! Che bel consiglio di Excel per sbarazzarsi di valori anomali dalla nostra tendenza.

Ora siamo tutti pronti a costruire il nostro grafico Excel outlier.

4) Crea grafico outlier in Excel

Quindi consente di creare il nostro grafico evidenziando l’intervallo di celle da E1:F11. Quindi vai alla barra multifunzione Inserisci e seleziona un grafico a linee con marcatori dal pulsante Linea:

Il tuo grafico dovrebbe ora assomigliare a questo:

Sembra molto diverso da quello con i valori anomali:

5) Aggiungi Trendline al grafico, Formula Trendline e Valore R-Squared

Ora che abbiamo il nostro grafico, abbiamo solo bisogno di alcune ultime cose. Aggiungiamo una trendline al nostro grafico. Per fare ciò, fare clic sul grafico e quindi fare clic sulla linea del grafico. Da lì, vai alla barra multifunzione del layout e scegli il pulsante Linea di tendenza, quindi scegli Linea di tendenza lineare:

scegliere “Lineare” e “Visualizza l’Equazione sul grafico” e “Display valore R al quadrato sul grafico” dal Formato Trendline finestra di dialogo:

Il grafico sarà come questo:

6) Liscia la linea e la pulizia Grafico Spazzatura

Ora tutto quello che dobbiamo fare è pulire il grafico e siamo tutti a posto! In primo luogo selezionare il grafico e quindi selezionare la legenda e premere il tasto canc. Il tuo grafico dovrebbe ora assomigliare a questo:

Ora dobbiamo creare un grafico a linee smussato. Per fare ciò, selezionare il grafico e quindi la linea nel grafico. Da lì, premere il tasto CTRL + 1 per aprire la finestra di dialogo Formato serie. Da lì, seleziona gli stili di linea dal menu a sinistra e scegli Linea levigata dalle scelte di stile linea:

Il tuo grafico finale dovrebbe ora assomigliare a questo:

Non c’è molta differenza, ma quando i tuoi dati hanno cambiamenti maggiori, appariranno ancora più fluidi

.

Ora consente di confrontarlo con la richiesta originale. Sembra quasi esattamente come quello che volevamo. Tuttavia, vedrai che i punti dati dal 3 al 5 gennaio sono uniti e non rotti come il campione originale. Sam per i punti dati tra 1/6 e 1/8. Non sono sicuro di quanto sia critico NON collegare queste linee, ma non è possibile con il modo in cui Excel crea grafici a linee quando si ha un intervallo di dati che utilizza una formula.

Tuttavia, torna domani dove ti mostrerò come fare in modo che un grafico a linee mostri un gap usando le formule.

Video Tutorial

Guarda questo video dimostrativo della creazione di un grafico a linee Excel che ignora i valori anomali: