Практична робота № 1

Тема: Використання статистичних функцій MS Excel для розв’язування задач прогнозування.

Мета: Отримати навички використання засобів прогнозування та аналізу даних в MS Excel.

ТЕОРЕТИЧНІ ВІДОМОСТІ

Статистичні функції лінійної регресії

Парна регресія задається рівнянням у = ax +b.

Функція НАКЛОН повертає нахил лінії лінійної регресії коефіцієнта – a. Нахил визначається як результат ділення відстані по вертикалі на відстань по горизонталі між двома крапками прямої, тобто нахил – це швидкість зміни значень протягом прямої. Синтаксис:

=НАКЛОН ( відомі значення y; відомі значення x )

відомі значення y – масив або інтервал комірок, які містять числові залежні крапки даних;

відомі значення x — множина незалежних крапок даних.

Функція ОТРЕЗОК обчислює точку перетину лінії тренду з віссю Оу  (константу b), використовуючи відомі значення х і значення у. Точка перетину знаходиться на оптимальній лінії регресії, яка проведена через відомі значення х і відомі значення у. Синтаксис:

=ОТРЕЗОК (відомі значення x ; відомі значення y)

відомі значення y - це залежна множина даних або спостережень;

відомі значення x  - це незалежна множина даних або спостережень.

Функція ПРЕДСКАЗ знаходить прогнозоване значення для одного нового значення незалежної змінної x. Визначає або передбачує майбутнє значення по існуючим значенням. Відомі значення – це x та y, а нове значення передбачається з використанням лінійної регресії. Цю функцію можна застосовувати для передбачення майбутніх продаж, потреб у ресурсах. Синтаксис:

=ПРЕДСКАЗ (x ; відомі значення y; відомі значення x)

x  - це точка даних, для якої передбачається значення;

відомі значення y - це залежний масив або інтервал даних;

відомі значення x – це незалежний масив або інтервал даних.

Функція ТЕНДЕНЦИЯ повертає прогнозовані значення у відповідності з лінійним трендом. Синтаксис:

= ТЕНДЕНЦИЯ (відомі значення y; відомі значення x; нові значення x; конст)

відомі значення y – множина значень y, які вже відомі для відношення  y = mx + b;

відомі значення x – необов’язкова множина значень x, які вже відомі для відношення y = mx + b;

Нові значення x – нові значення x, для яких ТЕНДЕНЦИЯ повертає відповідне значення y;

конст – логічне значення, яке вказує чи потрібно щоб константа b була рівна 0. Якщо конст має значення „істина” або виключено, то b розраховується звичайним методом. Якщо конст має значення „хибно”, то b вважається рівним 0, и значення m підбираються таким чином, щоб виконувалось відношення y = mx.

Множинна регресія – у = а₁ х₁ + а₂ х₂ + а₃ х₃ +... + а_n х_n + b

Функція ЛІНЕЙН визначає статистику для ряду із застосуванням методу найменших квадратів, для того щоб визначити пряму лінію, як найкращим чином відбиває наведені дані. Функція повертає масив, який описує отриману пряму, тобто знаходить коефіцієнти множинної регресії а₁, а₂, а₃, а_n. Також функція може обчислювати деякі статистичні характеристики, що дозволяють з’ясувати адекватність обраної моделі. ЛІНЕЙН може також повертати додаткову регресивну статистику. Синтаксис:

= ЛІНЕЙН (відомі значення y; відомі значення x; конст; статистика)

Відомі значення у – множина значень у, які вже відомі для відношення y= mx+b;

Відомі значення х – необов’язкова множина значень x, які вже відомі для відношення y = mx + b;

конст — логічне значення, яке вказує, чому дорівнює константа b;

Статистика — логічне значення, яке вказує, чи потрібно повернути додаткову статистику по регресії.

Статистичні функції нелінійної регресії

Парна регресія y = a^x b

Функція РОСТ розраховує прогнозований експоненціальний приріст на основі наведених даних. Функція РОСТ повертає значення y для послідовності нових значень x, які задаються за допомогою існуючих x- і y-значень.  Синтаксис:

=РОСТ(відомі значеня y; відомі значення x; нові значення x; конст)

відомі значення у – це множина значень y, які вже відомі у відношенні y = a^x b;

відомі значення x – це необов’язкова множина значень x, які вже відомі для відношення y = a^x b;.

Нові значення x – це нові значення x, для яких РОСТ повертає відповідне значення y;

конст – це логічне значення, яке вказує значення константи b.

Множинна регресія  y = a^X1 a ^X2 a ^X3 … a ^Xn b

Функція ЛГРФПРИБЛ обчислює масив даних, якій описує експоненціальну криву. Функція повертає передбачені значення у відповідності до степової функції y = a^X1 a ^X2 a ^X3 … a ^Xn b. Синтаксис:

=ЛГРФПРИБЛ (відомі значення y; відомі значення x; конст; статистика )

відомі значення y – множина відомих значень у, які вже відомі у відношенні y = a^x b.

відомі значення x – необов’язкова множина значень x, які вже відомі для відношення y = a^x b.

конст — логічне значення, яке вказує, необхідність того щоб  константа b була рівною 1.

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

Функції для роботи з базами даних

Функція ДМАКС повертає значення, що відповідає найбільшому значенню з списку або бази даних, яке задовольняє заданим критеріям. Синтаксис: 

=ДМАКС (база даних; поле; критерій)

База даних - це інтервал комірок, що формує списки або базу даних;

Поле – визначає стовпчик, поле може бути вказано відповідно назви стовпчика, наприклад „Ставка” або „Прибутки”, або як число, яке вказує положення стовпчика в списку: 1 — для першого поля, 2 — для другого поля.

Критерій – інтервал комірок, який містить задану умову. Будь-який інтервал, який містить не менше назви одного стовпчика і не менше одної комірки під назвою стовпчика з умовою.

Функція ДМИН повертає значення найменшого числа у списку або бази даних, яке задовольняє заданим умовам. Синтаксис: 

= ДМИН ( база даних; поле; критерій)

Аргументи функції аналогічні функції ДМАКС.

Функція ДСРЗНАЧ визначає середнє значення у стовпчику списку або бази даних, які задовольняють заданим умовам. Синтаксис:

= ДСРЗНАЧ (база даних; поле; критерій)

Аргументи функції аналогічні функції ДМАКС.

Функція БДСУММ визначає суму чисел у списку або базі даних, які  задовольняють заданим умовам. Синтаксис:

= БДСУММ (база даних; поле; критерій )

Аргументи функції аналогічні функції ДМАКС.

Функція БИЗВЛЕЧЬ повертає значення з стовпчика списку або бази даних, які задовольняють заданим умовам. Синтаксис:

= БИЗВЛЕЧЬ  (база даних; поле; критерій)

Аргументи функції аналогічні функції ДМАКС.

ЗАВДАННЯ ТА ПОРЯДОК ВИКОНАННЯ РОБОТИ

1. Створити на робочому листі табличного процесора MS Excel таблицю 1.1 Надати робочому листу ім’я Вхідні дані.

Таблиця 1.1

1. Визначити товар з найдорожчою ціною (використовуючи функцію ДМАКС).
2. Визначити найдешевшу закупівельні ціни серед придбаних партій товару  в березні місяці 2007 року (функція ДМИН). Для цього треба  створити допоміжну таблицю, яка б включала умову.
3. Визначити середню закупівельну ціну товарів, які постачались фірмами “Аванта” та “Орфей” (функція ДСРЗНАЧ).
4. Визначити загальний залишок товарів, які постачались фірмою “Гера” (функція БДСУММ).
5. Створити допоміжну таблицю на другому листі, де спрогнозувати кількість товару і закупівельну ціну товару для кожної фірми на 2006 рік (функція ТЕНДЕНЦИЯ).
6. Розрахувати ПДВ для кожного товару.
7. За числовими даними, представленими на робочому листі Вхідні дані, побудувати зведену таблицю та розмістити її на листі Зведена таблиця ПДВ. До проекту зведеної таблиці висуваються наступні вимоги:

-  у категорії Стовпчик розмістити Дата отримання;

-  у категорії Рядок розмістити Код товару;

-  у полі даних розмістити суму по полю Сплата ПДВ, Кількість.

8. Створити другу копію таблиці на іншому листі, спрогнозувати значення кількості товару за допомогою функції ПРЕДСКАЗ .

9. В третій копії таблиці на іншому робочому листі спрогнозувати значення кількості товару за допомогою функції РОСТ.

10. Зберегти результати на сервері.

11. Оформити звіт, зробити висновки і відповісти на контрольні питання.

Контрольні питання

1. Для чого і яким чином можна застосовувати статистичні функції в аналізі діяльності фірми?

2. Чим відрізняються функції ТЕНДЕНЦИЯ і ПРЕДСКАЗ?

3. Поясніть особливості роботи з функціями прогнозування.

4. Поясніть особливості роботи з функціями по обробці списків даних.

5. Які відмінності у роботі функцій ДМИН, ДМАКС, ДСРЗНАЧ, БДСУММ від МИН, МАКС, СРЗНАЧ, СУММ відповідно?
   

Контрольні завдання

Виконати прогнозування значення Y для кількох нових значень Х:

З повагою ІЦ "KURSOVIKS"!