Як виділити статистичні відхилення в Excel

Виконуючи аналіз даних, ви зазвичай припускаєте, що ваші значення групуються навколо деякої центральної точки даних (медіана). Але іноді деякі значення відходять занадто далеко від центральної точки. Ці значення називають викидами (вони лежать за межами очікуваного діапазону). Виброси можуть спотворити ваші статистичні аналізи, що призведе до помилкових або оманливих висновків щодо ваших даних.

Ви можете використовувати кілька простих формул і умовне форматування, щоб виділити у ваших даних відхилення.

Першим кроком у визначенні викидів є визначення статистичного центру діапазону. Щоб зробити це визначення, ви починаєте з пошуку 1-го і 3-го квартилів. Квартиль є статистичне поділ безлічі даних на чотири рівних групи, причому кожна група складаючи 25 відсотків даних. Верхні 25 відсотків колекції вважаються 1-м квартилем, тоді як нижні 25 відсотків вважаються 4-м квартилем.

У Excel можна легко отримати квартильні значення за допомогою функції КВАРТИЛЬ. Для цієї функції потрібні два аргументи: діапазон даних і потрібне число квартилю.

У наведеному прикладі значення в клітинках E3 і E4 є 1-м і 3-м квартилями для даних у діапазоні B3:B20.

Взявши ці два квартилі, ви можете обчислити статистичні 50 відсотків набору даних, віднімаючи 3-й квартиль від 1-го квартилю. Ці статистичні 50 відсотків називають міжквартильним діапазоном (IQR). На малюнку 9-18 показано IQR у комірці E5.

Тепер питання полягає в тому, як далеко від середніх 50 відсотків може бути цінність і все ще вважатися «розумною» цінністю? Статистики загалом погоджуються, що IQR*1,5 можна використовувати для встановлення розумного верхнього та нижнього забору:

Нижня огорожа дорівнює 1-му квартилю – IQR*1,5.

Верхня огорожа дорівнює 3-му квартилю + IQR*1,5.

Як бачимо, клітинки E7 і E8 розраховують кінцеві верхні і нижні паркани. Будь-яке значення, більше ніж верхнє або менше ніж нижнє, вважається викидом.

На цьому етапі правило умовного форматування легко реалізувати.

Щоб створити це основне правило форматування, виконайте такі дії:

Виберіть клітинки даних у вашому цільовому діапазоні (клітинки B3:B20 у цьому прикладі), натисніть вкладку «Головна» на стрічці Excel, а потім виберіть «Умовне форматування»→ «Нове правило».

Відкриється діалогове вікно Нове правило форматування.

У вікні зі списком у верхній частині діалогового вікна виберіть параметр Використовувати формулу для визначення клітинок для форматування.

Цей вибір оцінює значення на основі формули, яку ви вказали. Якщо певне значення має значення TRUE, до цієї комірки застосовується умовне форматування.

У полі введення формули введіть формулу, показану тут.

Зверніть увагу, що ви використовуєте функцію АБО, щоб порівняти значення цільової клітинки (B3) з верхнім і нижнім огорожами, знайденими в клітинках $E$7 і $E$8 відповідно. Якщо цільова клітинка вища за верхню межу або менша за нижню межу, вона вважається викидом і, таким чином, оцінюватиметься як TRUE, ініціюючи умовне форматування.

=АБО(B3<$e$8,b3>$E$7)

Зауважте, що у формулі ви виключаєте абсолютні еталонні символи долара ($) для цільової клітинки (B3). Якщо клацнути клітинку B3 замість того, щоб ввести посилання на клітинку, Excel автоматично зробить посилання на клітинку абсолютним. Важливо, щоб ви не включали символи долара абсолютного посилання в цільову клітинку, оскільки вам потрібен Excel, щоб застосувати це правило форматування на основі власного значення кожної клітинки.

Натисніть кнопку Формат.

Це відкриє діалогове вікно Формат клітинок, де у вас є повний набір параметрів для форматування шрифту, межі та заливки для цільової комірки. Після того, як ви завершили вибір параметрів форматування, натисніть кнопку OK, щоб підтвердити зміни та повернутися до діалогового вікна Нове правило форматування.

Повернувшись у діалогове вікно Нове правило форматування, натисніть кнопку OK, щоб підтвердити правило форматування.

Якщо вам потрібно відредагувати правило умовного форматування, просто помістіть курсор у будь-яку з клітинок даних у межах вашого форматованого діапазону, а потім перейдіть на вкладку «Головна» та виберіть «Умовне форматування»→ «Керувати правилами». Відкриється діалогове вікно Диспетчер правил умовного форматування. Клацніть правило, яке потрібно редагувати, а потім натисніть кнопку Редагувати правило.