Щоб мінімізувати функцію у треба розв’язати систему рівнянь

(17.2)

Розв'язок цієї системи дає формули для коефіцієнтів a, b.

(17.3)

Зв’язок між двома величинами визначається коефіцієнтом кореляції - КОРРЕЛ(масив1;масив2). Коефіцієнт кореляції змінюється в межах від -1 до 1 (при від’ємних значеннях – обернений зв’язок, при додатніх значеннях – приямий).

В Excel це завдання можна вирішити трьома способами.

Перший спосіб. Занесемо табличні значення в діапазон A2:B30. Для значень a та b відведемо клітини D2 та E2, а в клітині F2 розмістимо функцію у. У клітині F2 вводимо формулу: =СУММКВРАЗН(В2:В30;Е2+D2*А2:А30).

У клітині С2 вводимо формулу =$D$2*A2+$E$2 та копіюємо її в діапазон С3:С30.

Далі вибираємо команду Сервис®Поиск решения та заповнюємо діалогове вікно Поиск решения як показано на рис 17.1. Далі натискаємо кнопку Выполнить. В клітинах D2:F2 з’являються результати.

Рисунок 17.1 - Діалогове вікно Поиск решения.

Другий спосіб. Коефіцієнт а визначає нахил прямої регресії і розраховується функцєю НАКЛОН(х;у), коефіцієнт b визначає відрізок, який відсікає пряма регресії на осі OY і розраховується функцєю ОТРЕЗОК(х;у).

У клітинах D3 та E3 знайдені значення a та b відповідно за формулами: =НАКЛОН(В2:В30;А2:А30)

та ОТРЕЗОК(В2:В30;А2:А30).

Третій спосіб. Використовуючи Мастер диаграмм будуємо точковий графік по діапазону клітин А2:В30. Виділяємо точки графіка подвійним натиском миші, потім натискуємо праву кнопку миші. В контекстному меню вибираємо команду Линии тренда. В діалоговому вікні Линии тренда на вкладці Тип в групі Построение линии тренда (аппроксимация и сглаживание) вибираємо параметр Линейная, а на вкладці Параметрі встановлюємо прапорці Показывать уравнение на диаграмме та Поместить на диаграмме величину достоверности аппроксимации (R^2) (тобто на діаграмі потрібно розмістити значення квадрата коефіцієнта кореляції. По коефіцієнту кореляції можна судити про правомірність використання лінійного рівняння регресії. Якщо він належить діапазону 0,75 –1, то знайдену залежність можна використовувати для прогнозування результату.

Теоретичне значення функції у деякій фіксованій точці можна обчислити і без попереднього визначення коефіцієнтів лінійної моделі за допомогою функції ПРЕДСКАЗ(t; відомі значення у; відомі значення х), де t –точка, для якої потрібно визначити значення функції. Наприклад, у клітину E7 вводимо =80, а в клітину F7 формулу =ПРЕДСКАЗ(E7;A2:A30;B2:B30).

Вхідні дані та результати виконання роботи представлені на рис.17.2.

Рисунок 17.2 – Приклад виконання

Дата добавления: 2014-01-07