Згладжуйте дані на інформаційних панелях і звітах Excel

Тенденції дуже популярні в інформаційних панелях і звітах Excel. Тенденція є мірою дисперсії над деяким певним інтервалом - як правило , періоди часу , такі як дні, місяці або роки.

Деякі напрямки бізнесу піддаються значним коливанням даних з місяця в місяць. Наприклад, консультаційна практика може тривати місяцями без постійного потоку доходу, перш ніж укладається великий контракт і на кілька місяців підвищить показники продажів. Деякі називають ці злети і падіння сезонністю або бізнес-циклами .

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

Ось тут і з’являється концепція згладжування. Згладжування робить саме те, що звучить – воно змушує діапазон між найвищими і найнижчими значеннями в наборі даних згладжуватися до передбачуваного діапазону, не порушуючи пропорцій набору даних.

Тепер ви можете використовувати багато різних методів для згладжування набору даних. Знайдіть хвилинку, щоб ознайомитися з двома простими способами застосування згладжування.

Згладжування за допомогою функції ковзного середнього в Excel

Excel має вбудований механізм згладжування у вигляді лінії тренду ковзної середньої. Тобто лінія тренду, яка обчислює та відкладає ковзне середнє в кожній точці даних. Змінна середня статистична операція використовується для відстеження щодня, щотижня або щомісяця моделі.

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

Цей малюнок ілюструє, як лінія тренду ковзної середньої Excel може допомогти згладити нестабільні дані, підкресливши передбачуваний діапазон.

У цьому прикладі застосовано ковзне середнє за чотири місяці.

Щоб додати лінію тренду ковзної середньої, виконайте такі дії:

Клацніть правою кнопкою миші ряд даних, що представляють нестабільні дані, а потім виберіть Додати лінію тренда.

З’явиться діалогове вікно Формат лінії тренда, показане на цьому малюнку.

У діалоговому вікні «Формат лінії тренда» виберіть «Ковзне середнє», а потім вкажіть кількість періодів.

У цьому випадку Excel буде середнім чотиримісячний рухомий тренд.

Створення власного розрахунку згладжування

Як альтернативу вбудованим лініям тренду Excel, ви можете створити власний розрахунок згладжування та просто включити його як ряд даних у свою діаграму. На цьому малюнку обчислений стовпець (відповідно званий згладжуванням) надає точки даних, необхідні для створення згладженого ряду даних.

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

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