Задание 17

Для заданного набора пар значений независимой переменой и функции определить наилучшее линейное приближение в виде прямой с уравнением Y =aX+b и показательное приближение в виде линии с уравнением Y =b·a^X.

Решение

1. В столбец А, начиная с ячейки А1, введите 10 произвольных значений независимой переменой.
2. В столбец В, начиная с В1, введите 10 произвольных значений функции.
3. В диапазоне С1:D1 найдите коэффициенты а и b искомой прямой, с помощью функции ЛИНЕЙН.
4. Чтобы из полученного массива извлечь значения в отдельные ячейки, используйте функцию ИНДЕКС. Для этого выделите ячейку С3, вызовите Мастер функций, категория Ссылки и массивы, функция ИНДЕКС, в поле Массив выделите С1:D1, в поле Номер строки укажите число 1, получите значение коэффициента а.
5. Для извлечения коэффициента b, выделите ячейку D3, выполните действия аналогично предыдущему пункту, только в поле Номер строки укажите число 2.
6. В диапазоне Е1:F1 найдите коэффициенты а и b показательного приближения. Для этого выделите диапазон Е1:F1,вызовите мастер функций, категория Статистические, функция ЛГРФПРИБЛ, в поле Известные значения Y выделите диапазон В1:В10, в поле Известные значения Х выделите диапазон А1:А10, поля Конст и Статистика оставите пустыми и нажмите комбинацию клавиш Ctrl+Shift+Enter.
7. В ячейки Е3 и F3 извлеките коэффициенты а и b с помощью функции ИНДЕКС.

Задание 18

Для данных из задания 16 найти наилучшее линейное приближение с помощью процедуры Регрессия.

Решение

1. Для построения наилучшей прямой другим способом выберите Сервис – Анализ данных – Регрессия, в поле Входной интервал Y укажите диапазон, содержащих значение функции В2:В6, в поле Входной интервал Х укажите диапазон, содержащих значение независимой переменной А2:А6, установите переключатель в поле Выходной интервал и укажите ячейку А10, нажмите Ok.
2. В выходном диапазоне появятся результаты регрессионного анализа. В строке Регрессия в столбце Значимость F находится уровень значимости критерия Фишера, который должен быть меньше чем 0,05, чтобы модель была значима.
3. В строке Y-пересечение находится коэффициент b, в строке Переменная Х1 – коэффициент а, в столбце Р-значение приводится достоверность отличия соответствующих коэффициентов от нуля, если Р>0,05, коэффициент может считаться нулевым, что означает, что соответствующая независимая переменная не влияет на зависимую переменную.