Come evidenziare valori anomali statistici in Excel

Quando si esegue l'analisi dei dati, di solito si presume che i propri valori si raggruppino attorno a un punto dati centrale (una mediana). Ma a volte alcuni dei valori cadono troppo lontano dal punto centrale. Questi valori sono chiamati valori anomali (si trovano al di fuori dell'intervallo previsto). Gli outlier possono distorcere le tue analisi statistiche, portandoti a conclusioni false o fuorvianti sui tuoi dati.

Puoi utilizzare alcune semplici formule e la formattazione condizionale per evidenziare gli outlier nei tuoi dati.

Il primo passo per identificare i valori anomali consiste nell'individuare il centro statistico dell'intervallo. Per eseguire questa individuazione, si inizia trovando il 1° e il 3° quartile. Un quartile è una divisione statistica di un insieme di dati in quattro gruppi uguali, con ogni gruppo che costituisce il 25% dei dati. Il 25% superiore di una raccolta è considerato il 1° quartile, mentre il 25% inferiore è considerato il 4° quartile.

In Excel, puoi facilmente ottenere i valori del quartile usando la funzione QUARTILE. Questa funzione richiede due argomenti: un intervallo di dati e il numero del quartile desiderato.

Nell'esempio mostrato, i valori nelle celle E3 ed E4 sono il 1° e il 3° quartile per i dati nell'intervallo B3:B20.

Prendendo questi due quartili, puoi calcolare il 50% statistico del set di dati sottraendo il 3° quartile dal 1° quartile. Questo 50 percento statistico è chiamato intervallo interquartile (IQR). La Figura 9-18 mostra l'IQR nella cella E5.

Ora la domanda è: quanto lontano dal 50 percento medio può stare un valore ed essere ancora considerato un valore "ragionevole"? Gli statistici generalmente concordano sul fatto che IQR*1.5 può essere utilizzato per stabilire una recinzione superiore e inferiore ragionevole:

La recinzione inferiore è uguale al 1° quartile – IQR*1.5.

La recinzione superiore è uguale al 3° quartile + IQR*1.5.

Come puoi vedere, le celle E7 ed E8 calcolano i recinti finali superiore e inferiore. Qualsiasi valore maggiore del limite superiore o inferiore al limite inferiore è considerato un valore anomalo.

A questo punto, la regola di formattazione condizionale è facile da implementare.

Per creare questa regola di formattazione di base, segui questi passaggi:

Seleziona le celle di dati nell'intervallo di destinazione (celle B3: B20 in questo esempio), fai clic sulla scheda Home della barra multifunzione di Excel, quindi seleziona Formattazione condizionale → Nuova regola.

Questo apre la finestra di dialogo Nuova regola di formattazione.

Nella casella di riepilogo nella parte superiore della finestra di dialogo, fare clic sull'opzione Usa una formula per determinare quali celle formattare.

Questa selezione valuta i valori in base a una formula specificata. Se un particolare valore restituisce TRUE, la formattazione condizionale viene applicata a quella cella.

Nella casella di immissione della formula, inserisci la formula mostrata qui.

Si noti che si utilizza la funzione OR per confrontare il valore nella cella di destinazione (B3) con entrambi i recinti superiore e inferiore trovati nelle celle $ E $ 7 e $ E $ 8, rispettivamente. Se la cella di destinazione è maggiore del limite superiore o minore del limite inferiore, viene considerata un valore anomalo e quindi verrà valutata come TRUE, attivando la formattazione condizionale.

=OR(B3<$e$8,b3>$E$7)

Si noti che nella formula si escludono i simboli del dollaro di riferimento assoluto ($) per la cella di destinazione (B3). Se fai clic sulla cella B3 invece di digitare il riferimento di cella, Excel rende automaticamente assoluto il riferimento di cella. È importante non includere i simboli del dollaro di riferimento assoluto nella cella di destinazione perché è necessario che Excel applichi questa regola di formattazione in base al valore di ciascuna cella.

Fare clic sul pulsante Formato.

Questo apre la finestra di dialogo Formato celle, dove hai un set completo di opzioni per formattare il carattere, il bordo e il riempimento per la tua cella di destinazione. Dopo aver completato la scelta delle opzioni di formattazione, fare clic sul pulsante OK per confermare le modifiche e tornare alla finestra di dialogo Nuova regola di formattazione.

Torna nella finestra di dialogo Nuova regola di formattazione, fare clic sul pulsante OK per confermare la regola di formattazione.

Se devi modificare la tua regola di formattazione condizionale, posiziona semplicemente il cursore in una qualsiasi delle celle di dati all'interno dell'intervallo formattato, quindi vai alla scheda Home e seleziona Formattazione condizionale → Gestisci regole. Viene aperta la finestra di dialogo Gestore regole di formattazione condizionale. Fare clic sulla regola che si desidera modificare, quindi fare clic sul pulsante Modifica regola.