5. Використання інструментальних засобів Microsoft Excel для математичної обробки даних

Сучасний табличний процесор Microsoft Excel ХР доволі часто використовується для організації розрахунків та аналізу ділових даних.

Табличний процесор Microsoft Excel ХР має широкий набір вбудованих функцій (близько 200), які поділяються на такі категорії: фінансові, дати і часу, математичні, статистичні, перегляду та посилань, функції роботи з базами даних, логічні, інформаційні тощо.

Функції − заздалегідь визначені формули, що в певному порядку виконують обчислення за визначеними величинами – аргументами. Функції дозволяють виконувати як прості, так і складні обчислення.

Для роботи з ними звичайно використовується спеціальне діалогове вікно Мастер функций, яке з'являється на екрані після виконання команд Вставка\Функция.

6. Технології аналізу та прогнозування тенденцій на основі трендів

У програмі MS Excel для побудови регресій є дві можливості.

1. Додавання вибраних регресій (ліній тренда) в діаграму, побудовану на основі таблиці даних для досліджуваної характеристики процесу (доступно лише за наявності побудованої діаграми);

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

Для таблиці даних, що описують деякий процес і представлених діаграмою, в MS  Excel є ефективний інструмент регресійного аналізу, який дозволяє:

• будувати на основі методу найменших квадратів і додавати в діаграму п'ять типів регресій, які з тим або іншим ступенем точності моделюють досліджуваний процес;

• додавати до діаграми рівняння побудованої регресії;

• визначати ступінь відповідності вибраної регресії даним, що відображаються на діаграмі.

Достоїнствами розглянутого інструменту регресійного аналізу є:

• відносна легкість побудови на діаграмах лінії тренда без створення для неї таблиці даних;

• достатньо широкий перелік типів запропонованих ліній трендів, причому в цей перелік входять найчастіше використовувані типи регресії;

• можливість прогнозування поведінки досліджуваного процесу на довільну (в межах здорового глузду) кількість кроків вперед, а також назад;

• можливість отримання рівняння лінії тренда в аналітичному вигляді;

• можливість, при необхідності, отримання оцінки достовірності проведеної апроксимації.

До недоліків можна віднести наступні моменти:

• побудова лінії тренда здійснюється лише за наявності діаграми, побудованої на ряду даних;

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

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

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

Не можна доповнити лініями тренда ряди даних на об'ємних, нормованих, пелюсткових, кругових і кільцевих діаграмах.

Види (типи) ліній тренда:

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

Формула: де m- кут нахилу і b - координата перетину осі абсцис.

У приведеному нижче прикладі лінійне наближення показує рівномірне збільшення об'єму продажів холодильників протягом 13 років. Слід замітити, що значення R-квадрат в даному випадку складає 0,9036. Це свідчить про достатньо хороше узгодження лінії апроксимації з фактичними даними.

Логарифмічна апроксимація 

Логарифмічна апроксимація добре описує величину, яка спочатку швидко росте або убуває, а потім поступово стабілізується. Описує як позитивні, так і негативні величини.

Формула:  дез і b - константи, ln - функція натурального логарифма.

Приведений вище приклад використовує логарифмічне наближення для ілюстрації прогнозованого зростання популяції тварин на обмеженій території. У міру того, як вільного простору стає все менше, темпи зростання популяції також знижуються. Слід відмітити, що значення R² в даному прикладі рівне 0,9407; це указує на те, що апроксимуюча крива описує дані з достатньо високою мірою достовірності.

Поліноміальна апроксимація 

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

Формула:  деb і — константи.

Нижче на прикладі апроксимації поліномом другого порядку (одна вершина) показана залежність швидкості від споживання палива. Слід відмітити, що значення R² в даному випадку складає 0,9474. Це досить добре узгоджується з фактичними даними.

Степенева апроксимація 

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

Формула:  дез і b - константи.

Нижче показаний приклад залежності пройденої відстані від часу (у секундах). По степеневій лінії тренда ясно видно збільшення прискорення. Зверніть увагу, що значення R² в даному прикладі рівне 0,9923. Це говорить про високу точність використовуваного наближення.

Експоненціальна апроксимація 

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

Формула: дез і b - константи, e - основа натурального логарифма.

На приведеному нижче прикладі експоненціальне наближення ілюструє процес розпаду вуглецю 14. Слід відмітити, що значення R² тут рівне 1, тобто лінія наближення ідеально відповідає даним.

Ковзаюче середнє (лінійна фільтрація)

Використовування як наближення ковзаючого середнього дозволяє згладити коливання даних і таким чином наглядніше показати характер залежності. Така лінія тренда будується по певному числу точок (воно задається параметром Шаг). Елементи даних усереднюються, і одержаний результат використовується як середнє значення для наближення. Так, якщо Шаг рівний 2, перша точка згладжуючої кривої визначається як середнє значення перших двох елементів даних, друга точка  — як середнє наступних двох елементів і так далі. Для ковзаючого середнього значення R² не може бути відображене.

Формула:

У наступному прикладі показана залежність числа продажів впродовж 26 тижнів, одержана шляхом розрахунку ковзаючого середнього.

Додавання і редагування лінії тренда

1. Відкрийте папку. Створіть документ Призвіще.xls. За завданням викладача створіть таблицю, введіть дані, побудуйте графіки.

2. Двічі клацніть по графіку для переходу в режим редагування.

3. Виберіть ряд даних, до якого потрібно добавити лінію тренда. Виділіть цей ряд даних для побудови тренда, клацнувши по лінії графіка правою кнопкою миші (виділення буде мати вид чорних квадратиків, після клацання появиться контекстне меню), виберіть пункт меню Добавить линию тренда.  Або після виділення ряду клацанням будь-якої кнопки миші виберіть команду Добавить линию тренда в меню Диаграмма.

На екрані появиться діалогове вікно (рис. 2.4).

Рисунок 2.4 - Діалогове вікно Лінія тренда

4. На вкладці Тип виберіть потрібний тип регресійної лінії тренда. або лінії ковзаючого середнього (лінійна фільтрація).

При виборі типа Полиномиальная введіть в полі Степень найбільший ступінь для незалежної змінної.

При виборі типу Скользящее среднее (линейная фильтрация) введіть в полі Период число періодів (крапок), використовуваних для розрахунку ковзаючого середнього.

5. Для здійснення прогнозу виділите лінію тренда, клацнувши але нею мишею, потім клацніть правою кнопкою миші і виберіть пункт Форматирование линии тренда, відкрийте вкладку Параметры і встановіть необхідні параметри: Прогноз вперед на один або декілька періодів. На діаграмі буде показана лінія тренда і прогноз на один або декілька періодів. 

6. Для виведення формули і значення R² відкрийте вкладку Параметры і встановите необхідні параметри Показать уравнение на диаграмме та Поместить на диаграмму величину достоверности аппроксимации (R^2) (рис. 2.5).

Рисунок 2.5 – Діалогове вікно Формат лінії тренду

7. Лінію тренда можна форматувати. Виділіть лінію тренда, клацнувши але нею мишею, потім клацніть правою кнопкою миші і виберіть пункт Форматирование линии тренда, встановіть бажаний Вид тренда, наприклад, виберіть червоний пунктирний вид лінії.