Використання табличного процесора для оцінювання параметрів регресійної залежності
Отже, побудовану модель можна використати для прогнозування. Прогнозне значення, обчислене за допомогою функції ТЕНДЕНЦИЯ, для очікуваного значення X=17 (комірка D20) наведене в комірці D21. Зауважимо, що воно отримано безпосередньо за допомогою наведеної функції, без використання результатів розрахунків, наведених вище. Це же результат міг би бути отриманий шляхом підстановки прогнозного… Читати ще >
Використання табличного процесора для оцінювання параметрів регресійної залежності (реферат, курсова, диплом, контрольна)
Використання статистичних функції для побудови та аналізу регресійної моделі
Розрахунок параметрів лінійної регресійної залежності та її аналіз можна істотно спростити, якщо використати вбудовані засоби табличного процесора. Він має набір функцій, за допомогою яких можна розрахувати параметри моделі лінійної моделі, обчислити критичні значення критеріїв Стьюдента та Фішера, знайти прогнозні значення за лінійною або експоненціальною регресійними моделями без розрахунку параметрів самої моделі. В таблиці 7.6 наведені основні функції, призначені для вирішення наведених завдань Таблиця 7.6 — Статистичні функції табличного процесора, призначені для побудови та аналізу лінійної регресійної моделі.
Функція. | Призначення та опис параметрів. | |
ЛИНЕЙН (данные_Y; данные_X; конст; статистика). | Розраховує параметри лінійної залежності і додаткові статистичні характеристики цієї залежності. Результатом є діапазон комірок.  Опис параметрів: данные_Y — стовпчиковий або рядковий діапазон, що містить значення результуючого показника (Y) ; данные_X — стовпчиковий або рядковий діапазон, що містить значення чинника (X) ; конст — логічна константа; значення FALSE, або 0 означає, що в побудованій моделі параметр a0 буде рівний 0, інакше (для значення TRUE, або 1) він відмінний від 0; статистика — необов’язковий параметр; логічна константа; значення FALSE, або 0 або відсутність значення означає, що будуть обчислені лише параметри моделі a0 та a1, інакше (для значення TRUE, або 1) будуть розраховані також додаткові статистичні характеристики . | |
ЛГРФПРИБЛ (данные_Y; данные_X; конст; статистика). | Розраховує параметри степеневої залежності і додаткові статистичні характеристики цієї залежності. Результатом є діапазон комірок.  Опис параметрів: данные_Y — стовпчиковий або рядковий діапазон, що містить значення результуючого показника (Y) ; данные_X — стовпчиковий або рядковий діапазон, що містить значення чинника (X) ; констлогічна константа; значення FALSE, або 0 означає, що в побудованій моделі параметр a0 буде рівний 1, інакше (для значення TRUE, або 1) він відмінний від 1; статистика — необов’язковий параметр; логічна константа; значення FALSE, або 0 або відсутність значення означає, що будуть обчислені лише параметри моделі a0 та a1, інакше (для значення TRUE, або 1) будуть розраховані також додаткові статистичні характеристики . | |
ТЕНДЕНЦИЯ (данные_Y; данные_X; новые_данные_X; конст). | Розраховує сукупність прогнозних значень за лінійною моделлю. Результатом є діапазон комірок. Опис параметрів: данные_Y — стовпчиковий або рядковий діапазон, що містить значення результуючого показника (Y) ; данные_X — стовпчиковий або рядковий діапазон, що містить значення чинника (X) ; новые_данные_X — стовпчиковий або рядковий діапазон, який задає значення чинника, для яких потрібно обчислити прогноз; конят — логічна константа; значення FALSE, або 0 означає, що в побудованій моделі параметр a0 буде рівний 0, інакше (для значення TRUE, або 1) він відмінний від 0. | |
РОСТ (данные_Y; данные_X; новые_данные_X; конст). | Розраховує сукупність прогнозних значень за експоненціальною моделлю. Результатом є діапазон комірок. Опис параметрів: данные_Y — стовпчиковий або рядковий діапазон, що містить значення результуючого показника (Y) ; данные_X — стовпчиковий або рядковий діапазон, що містить значення чинника (X) ; нове_данные_X — стовпчиковий або рядковий діапазон, який задає значення чинника, для яких потрібно обчислити прогноз; конст — логічна константа; значення FALSE або 0 означає, що в побудованій моделі параметр a0 буде рівний 1, інакше (для значення TRUE або 1) він відмінний від 1. | |
ПРЕДСКАЗ (значение; данные_Y; данные_X). | Розраховує прогнозне значення за лінійною моделлю. Результатом є одне значення. Опис параметрів: значение — адреса комірки або числова константа, для якої обчислюється прогноз; данные_Y — стовпчиковий або рядковий діапазон, що містить значення результуючого показника (Y) ; данные_X — стовпчиковий або рядковий діапазон, що містить значення чинника (X) . | |
ОТРЕЗОК (данные_Y; данные_X). | Розраховує значення параметра a0 для лінійної моделі. Результатом є одне значення. Опис параметрів: данные_Y — стовпчиковий або рядковий діапазон, що містить значення результуючого показника (Y) ; данные_X — стовпчиковий або рядковий діапазон, що містить значення чинника (X). | |
НАКЛОН (данные_Y; данные_X). | Розраховує значення параметра a1 для лінійної моделі. Результатом є одне значення. Опис параметрів: данные_Y — стовпчиковий або рядковий діапазон, що містить значення результуючого показника (Y) ; данные_X — стовпчиковий або рядковий діапазон, що містить значення чинника (X). | |
КОРРРЕЛ (данные 1; данные 2). | Розраховує значення коефіцієнта кореляції для двох показників. Результатом є одне значення. Опис параметрів: данные 1 — стовпчиковий або рядковий діапазон, що містить значення одного показника ; данные 2 — стовпчиковий або рядковий діапазон, що містить значення іншого показника. | |
СТЬЮДРАСПОБР (вероятность; степени_свободы). | Розраховує t-значення розподілу Стьюдента (критичнее значення критерію Стьюдента) як функцію ймовірності та кількості ступенів вільності. Результатом є одне значення. Опис параметрів: вероятность — адреса комірки або числова константа, яка задає рівень значущості для обчислення t-значення; степени_свободы — - адреса комірки або числова константа, яка задає значення ступенів вільності . | |
FРАСПОБР (вероятность; степени_свободы1; степени_свободы2). | Розраховує F-розподіл імовірності (критичнее значення критерію Фішера). Результатом є одне значення. Опис параметрів: вероятность — адреса комірки або числова константа, яка задає рівень значущості для обчислення F-критерію.; степени_свободы1, степени_свободы2, — адреси комірок або числові константи, яка задають значення ступенів вільності дисперсій (для чисельника та знаменника). | |
Проілюструємо застосування наведених функції на прикладі.
Приклад 7.2. Для клієнтів торгівельного закладу зафіксована сума покупки Y (в гривнях) та часом розмови покупця з продавцем X (в хв.). Визначити форму залежності між Y і X та розрахувати за допомогою стандартних функцій табличного процесора параметри економетричної моделі. Вихідні дані для розрахунків наведені в таблиці 7.7. Також обчислити прогнозне значення очікуваної суми покупки Yпр при значенні Xпр=17 хв.
Таблиця 7.7.
X, хв. | |||||||||||||
Y, грн. | |||||||||||||
Розв’язок.
Побудуємо кореляційне поле (рисунок 7.1) та визначимо за ним форму зв’язку.
Рисунок 7.1 — Кореляційне поле.
Аналіз кореляційного поля показує, що в даному випадку залежність може бути подана лінійному вигляді:
Проведемо розрахунки величин, які відобразять щільність зв’язку між чинниками, параметри моделі та проведемо перевірку її адекватності. Для цього скористаємось наведеними вище функціями. Результати розрахунків в електронній таблиці представлені на рисунку 7.2.
Рисунок 7.2 — Розрахунок параметрів лінійної регресійної моделі та допоміжної статистики за допомогою статистичних функцій.
Наведемо пояснення до проведених розрахунків (деякі числові значення будуть далі наведені з округленням) Вихідні дані представлені в комірках B4: N5.
Значення коефіцієнта кореляції, розрахованого за допомогою функції КОРРЕЛ, записане в комірці D7.
Параметри регресійної моделі та допоміжна статистика, обчислена за допомогою функції ЛИНЕЙН, зі значенням четвертого параметра, рівного 1 для виводу допоміжної статистики, представлені в комірках G8: H7. При цьому:
- — в комірках G8 та H8 записані значення параметрів а1 та а0 відповідно. Отже, модель має вигляд Y= -670,86 +122,66X;
- — в комірках G9 та H9 записані значення середньоквадратичних відхилень параметрів а1 та а0 відповідно:
- — в комірці G10 записано значення коефіцієнта детермінації R2 = 0,84 436
- — в комірці H10 записано значення середньоквадратичного відхилення залишків моделі: ;
- — в комірці G11 записано емпіричне значення критерію Фішера: Fемп=59,67;
- — в комірці H11 записано значення кількості ступенів вільності k2=n-m-1 = 13−2=11;
- — в комірці G11 записано значення суми квадратів відхилень, що пояснює регресію: SSR = 1 259 208;
- — в комірці H11 записано значення суми квадратів відхилень емпіричних значень від модельних: SSE = 232 115.
В комірках B15 та E15 записані емпіричні значення критерію Стьюдента для параметрів моделі а0 та а1 відповідно, які обчислені за формулами (7.6) та (7.7). Критичне значення критерію Стьюдента, обчислене за допомогою стандартної функції СТЬЮДРАСПОБР, при рівні значущості ?=0,05 та ступенях вільності k=11 наведене в комірці F16. Оскільки емпіричні значення перевищують критичне, то коефіцієнти побудованої нами моделі є статистично значущими.
В комірці F18 наведено критичне значення критерію Фішера, розраховане для рівня значущості ?=0,05 та ступенях вільності k1=1, k2=11. Оскільки емпіричне значення (комірка G11) перевищує критичне, то модель адекватна вихідним даним.
Отже, побудовану модель можна використати для прогнозування. Прогнозне значення, обчислене за допомогою функції ТЕНДЕНЦИЯ, для очікуваного значення X=17 (комірка D20) наведене в комірці D21. Зауважимо, що воно отримано безпосередньо за допомогою наведеної функції, без використання результатів розрахунків, наведених вище. Це же результат міг би бути отриманий шляхом підстановки прогнозного значення X безпосередньо в побудовану модель.
У випадку знаходження залежності у вигляді для розрахунку її параметрів необхідно було б скористатись функціями ЛГРФПРИБЛ та РОСТ, опис яких представлений в таблиці 7.6. якщо залежність між показниками мала би інших нелінійний вигляд, то потрібно було б спочатку провести її лінеаризацію відповідно до таблиці 7.1. Потім за допомогою відповідних статистичних функцій табличного процесора потрібно розрахувати параметри лінеаризованої моделі, після чого повернутись до вихідної моделі.
Серед недоліків використання статистичних функцій варто відзначити певну громіздкість розрахунків.