Вычисление коэффициентов регрессии осуществляется с помощью функции ЛИНЕЙН():
ЛИНЕЙН(Значения_y; Значения_x; Конст; статистика)
Значения_y - массив значений y.
Значения_x - необязательный массив значений x, если массив х опущен, то предполагается, что это массив {1;2;3;...} такого же размера, как и Значения_y.
Конст - логическое значение, которое указывает, требуется ли, чтобы константа b была равна 0. Если Конст имеет значение ИСТИНА или опущено, то b вычисляется обычным образом. Если аргумент Конст имеет значение ЛОЖЬ, то b полагается равным 0 и значения a подбираются так, чтобы выполнялось соотношение y = ax.
Статистика - логическое значение, которое указывает, требуется ли вернуть дополнительную статистику по регрессии. Если аргумент статистика имеет значение ИСТИНА, то функция ЛИНЕЙН возвращает дополнительную регрессионную статистику. Если аргумент статистика имеет значение ЛОЖЬ или опущен, то функция ЛИНЕЙН возвращает только коэффициент a и постоянную b.
Для вычисления множества точек на линии регрессии используется функция ТЕНДЕНЦИЯ.
ТЕНДЕНЦИЯ(Значения_y; Значения_x; Новые_значения_x; Конст)
Значения_y - массив значений y, которые уже известны для соотношения y = ax + b.
Значения_x - массив значений x.
Новые_значения_x - новый массив значений, для которых ТЕНДЕНЦИЯ возвращает соответствующие значения y. Если Новые_значения_x опущены, то предполагается, что они совпадают с массивом значений х.
Конст - логическое значение, которое указывает, требуется ли, чтобы константа b была равна 0. Если Конст имеет значение ИСТИНА или опущено, то b вычисляется обычным образом. Если Конст имеет значение ЛОЖЬ, то b полагается равным 0, и значения а подбираются таким образом, чтобы выполнялось соотношение y = ax. Необходимо помнить, что результатом функций ЛИНЕЙН, ТЕНДЕНЦИЯ является множество значений - массив.
Для расчета коэффициента корреляции используется функция КОРРЕЛ, возвращающая значения коэффициента корреляции:
КОРРЕЛ(Массив1;Массив2)
Массив1 - массив значений y.
Массив2 - массив значений y.
Массив1 и Массив2 должны иметь одинаковое количество точек данных.
Задание 1.
Известна табличная зависимость G(L) (Табл.1). Построить линию регрессии и вычислить ожидаемое значение в точках 0, 0.75, 1.75, 2.8, 4.5.
Таблица 1. Табличная зависимость G(L).
| L | 0,5 | 1,5 | 2,5 | 3,5 | |||||
| G | 2,39 | 2,81 | 3,25 | 3,75 | 4,11 | 4,45 | 4,85 | 5,25 |
Решение:
Введем таблицу значений в лист MS Excel и построим точечный график. Рабочий лист примет вид изображенный на рис. 8.2.
Рис. 3.2 Ввод данных в MS Excel.
Для того, чтобы рассчитать значения коэффициентов регрессии а и b выделим ячейки К2:L2, обратимся к мастеру функций и в категории Статистические выберем функцию ЛИНЕЙН. Заполним появившееся диалоговое окно так, как показано на рис. 3.3 и нажмем Ок.
Рис. 3.3 Окно функции ЛИНЕЙН
В результате вычисленное значение появится только в ячейке К2. Для того чтобы вычисленное значение появилось и в ячейке L2 необходимо войти в режим редактирования, нажав клавишу F2, а затем нажать комбинацию клавиш CTRL+SHIFT+ENTER. (рис. 3.4)
Для расчета значения коэффициента корреляции в ячейку M2 была введена следующая формула: М2 = КОРРЕЛ(B1:J1;B2:J2) (см. рис. 3.4).
Рис. 3.4 Расчет коэффициентов регрессии и коэффициента корреляции
Для вычисления ожидаемого значения в точках 0, 0.75, 1.75, 2.8, 4.5 занесем их в ячейки L9:L13. Затем выделим диапазон ячеек M10:M13 и введем формулу:
= ТЕНДЕНЦИЯ(B2:J2;B1:J1;L9:L13).
Для того чтобы вычисленные значения появились и в ячейках M10:M13 необходимо нажать комбинацию клавиш CTRL+SHIFT+ENTER.
Рис. 3.5 Построение линии регрессии с помощью точечной диаграммы.
Изобразим линию регрессии на диаграмме. Для этого выделим экспериментальные точки на графике, щелкнем правой кнопкой мыши и выберем команду Исходные данные. В появившемся диалоговом окне (см. рис. 3.5), для добавления линии регрессии щелкнем по кнопке Добавить.
В качестве имени введем Линия регрессии, в качестве Значения Х: L9:L13, в качестве Значения Y: M9:M13. Далее выделяем линию регрессии, для изменения ее типа щелкаем правой кнопкой мыши и выбираем команду Тип диаграммы. Для форматирования линии регрессии (можно изменить толщину линии, цвет, тип маркера и т.п) дважды щелкаем по ней.
После форматирования графика рабочий лист примет вид, изображенный на рис. 3.6.
Рис. 3.6 Результат построения линии регрессии
Построение различных аппроксимирующих зависимостей в MS Excel реализовано в виде свойства диаграммы - линия тренда.
Задание 2.
В результате эксперимента (табл. 2) была определена некоторая табличная зависимость. Выбрать и построить аппроксимирующую зависимость. Построить графики табличной и подобранной аналитической зависимости. Вычислить ожидаемое значение в указанных точках.
x1 = 0,1539, x2 = 0,2569, x3 = 0,28
Таблица 2 Данные, полученные в результате эксперимента
| X | 0,15 | 0,16 | 0,17 | 0,18 | 0,19 | 0,20 |
| Y | 4,4817 | 4,4930 | 5,4739 | 6,0496 | 6,6859 | 7,389 |
Решение:
Решение задачи можно разбить на следующие этапы:
1. Ввод исходных данных и построение точечного графика (см. рис. 3.7).
2. Добавление к этому графику линии тренда.
Рассмотрим этот процесс подробно.
Рис. 3.7 Вид экспериментальных данных в MS Excel
Выделим экспериментальные точки на графике, щелкнем правой кнопкой мыши и воспользуемся командой Добавить линию тренда. Появившееся диалоговое окно (см. рис. 3.8) позволяет построить аппроксимирующую зависимость. На первой вкладке этого окна указывается вид аппроксимирующей зависимости (в нашем случае необходимо выбрать полиномиальную зависимость второй степени). На второй определяются параметры построения:
· Название аппроксимирующей зависимости.
· Прогноз вперед (назад) на n единиц (этот параметр определяет, на какое количество единиц вперед (назад) необходимо продлить линию тренда).
· Показывать ли точку пересечения кривой с прямой Y = const.
· Показывать аппроксимирующую функцию на диаграмме или нет (параметр показывать уравнение на диаграмме).
· Помещать ли на диаграмму величину среднеквадратичного отклонения или нет (параметр поместить на диаграмму величину достоверности аппроксимации).
На рис. 3.9 изображена полученная диаграмма.
Рис. 3.8 Окно команды построение линии тренда
Рис. 3.9 Экспериментальные данные и линия тренда.
Для расчета ожидаемых значений в точках 0.1539, 0.2569, 0.28 введем эти значения в ячейки B4:D4. В ячейку B5 введем формулу подобранной аппроксимирующей зависимости (=371.62*B4^2-68.093*B4+6.1891) и скопируем ее в ячейки C5, D5. Фрагмент рабочего листа (рис. 3.10) примет вид:
Рис. 3.10 Фрагмент рабочего листа
Добавим полученные расчетные значения на диаграмму. Для этого на диаграмме выделим экспериментальные значения, щелкнем правой кнопкой мыши и выберем команду Исходные данные. Добавим туда Рассчитанные значения (см. рис. 3.11).
Рис. 3.11 Добавление расчетных данных на график экспериментальных данных.
В результате диаграмма примет вид изображенный на рис. 3.12. Аналогично с помощью линии тренда можно подобрать и параметры других типов зависимостей (линейной, логарифмической и экспоненциальной и т. д.).
Рис. 3.12 Результат обработки экспериментальных данных.