Материал: Лекция 13

13.4 Алгоритм определения параметров эмпири еской формулы методом наименьших квадратов в Excel

1. Перенести в таблицу Excel результаты экспериментальных данных и построить точечный график для определения вида уравнения регрессии (рисунок 13.3).

Рисунок 13.3 – Построение графика экспериментальных данных

2.Включить в Excel надстройку “Поиск решения”.

3.Выделить в Excel ячейки для постоянных коэффициентов регрессии и записать само уравнение регрессии используя относительные (для xi) и абсолютные (для a и b) ссылки (рисунок 13.4).

Рисунок 13.4 – Заполнение таблицы Excel

6 Сохраняем полученное решение (рисунок 13.7).

Рисунок 3.7 – Сохранение решения

7. Добавить на область Диаграммы полученную теоретическую линию регрессии (рисунок 13.8).

Рисунок 13.8 – Результаты построения линейной регрессии

8. Проверить квадратичную зависимость (см. п. 3-7) (рисунок 13.9)

Рисунок 13.9 – Результаты построения параболической регрессии

13.5 Определение уравнений регрессии с помощью функций Excel

Так как задача отыскания регрессионной зависимости очень важна, в Excel введен набор функций, которые позволяют решать эту задачу. Эти функции основаны на методе наименьших квадратов. В качестве результата выдаются не только коэффициенты аппроксимирующей функции, но и статистические характеристики полученных результатов.

ВExcel имеется несколько функций для построения линейной регрессии,

вчастности: ЛИНЕЙН, НАКЛОН и ОТРЕЗОК.

А также несколько функций для построения экспоненциальной линии тренда, в частности: РОСТ, ЛГРФПРИБЛ.

Достоинствами инструмента встроенных функций для регрессионного анализа являются:

достаточно простой однотипный процесс формирования рядов данных исследуемой характеристики для всех встроенных статистических функций;

стандартная методика построения линий тренда на основе сформированных рядов данных;

возможность прогнозирования поведения исследуемого процесса на необходимое количество шагов вперед или назад.

А к недостаткам относится то, что в Excel нет встроенных функций для создания других (кроме линейного и экспоненциального) типов линий тренда. Это обстоятельство часто не позволяет подобрать достаточно точную мо-

дель исследуемого процесса, а также получить близкие к реальности прогнозы.

Функция ЛИНЕЙН

Функция ЛИНЕЙН вычисляет коэффициенты а и b прямой линии y=ax+b, которая наилучшим образом аппроксимирует имеющиеся данные, а также дополнительную регрессионную статистику. Функция возвращает массив данных, который описывает полученную прямую. Синтаксис функции:

ЛИНЕЙН(известные_y, [известные_x], [константа], [статистика])

Известные_y. – Обязательный аргумент. Множество значений y, которые уже известны для соотношения y=ax+b.

Известные_x. Необязательный аргумент. Множество значений x, которые уже известны для соотношения y=ax+b

Константа. Необязательный аргумент. Логическое значение. Если аргумент Константа = 0, то b принудительно полагается равным нулю,

т.е. y=ax.

Статистика. Необязательный аргумент. Логическое значение. Если аргумент Статистика = 0 или опущен, то вычисляются только коэффициенты a и b, а если = 1, то выдаются дополнительные статистические характеристики.

На рисунке 13.10 показан пример использования функции ЛИНЕЙН для решения задач.

Рисунок 13.10 – Пример использования функции ЛИНЕЙН