Уникайте пасток надбудов для аналізу даних Excel

Протягом багатьох років деякі інструменти надбудови Excel Data Analysis (наприклад, інструмент регресії) плутали діапазон введення з діапазоном виведення. Якщо ви збираєтеся прогнозувати за допомогою регресії, вам потрібні принаймні дві змінні: змінна-прогноз (наприклад, дата або рекламні долари) і прогнозована змінна (у цьому контексті щось на кшталт доходів від продажів або одиниць продажів).

Інструмент регресії звертається до значень змінної-провісника як до вхідного X діапазону, а до значень прогнозованої змінної як до діапазону входу Y.

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

Тепер, припустимо, ви робите це:

Перейдіть на вкладку «Дані» стрічки та натисніть «Аналіз даних» у групі «Аналіз».

Знайдіть і клацніть інструмент «Регресія» у списку, а потім натисніть «ОК».

Клацніть у полі «Діапазон введення Y», а потім перетягніть щось, наприклад, значення доходу від продажів на робочому аркуші.

Клацніть у полі Діапазон введення X, а потім перетягніть щось, наприклад значення дати на аркуші.
Зверніть увагу, що параметром за замовчуванням для параметрів виводу є новий аркуш.

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

Іншими словами, інструмент регресії намагається змусити вас вибрати діапазон або комірку без будь-яких даних для надання вашого діапазону Y вхідних даних — тобто значень змінної, яку потрібно передбачити.

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

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

Іншою основною проблемою надбудови Data Analysis є те, що її вихідні дані часто статичні. Інструмент регресії, наприклад, розміщує обчислені значення в клітинках, а не формули, які можна перерахувати, коли вхідні дані змінюються. Якщо ви отримаєте нові або змінені введені значення, вам доведеться повторно запустити інструмент, щоб отримати переглянуті результати.

Інші інструменти, такі як ковзне середнє та експоненційне згладжування, звітують про свої результати у вигляді формул, тож вони будуть перераховані, якщо ви зміните вхідні дані. Якщо у вас є нові значення для використання цих інструментів (наприклад, ваш діапазон введення змінюється з A1:A20 на A1:A25), вам потрібно буде скинути адресу діапазону введення; але якщо ви просто переглядаєте попереднє значення, формули будуть перераховані, а діаграми перемалюються без будь-яких додаткових зусиль з вашого боку.