Упражнение 1. Анализ данных с использованием метода наименьших квадратов

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

1. Запустите программу Excel и откройте рабочую книгу book.xls, созданную ранее.

2. Щелчком на ярлычке выберите рабочий лист Обработка эксперимента.

3. Сделайте ячейку С1 текущей и щелкните на кнопке Вставка функции в строке
формул.

4. В окне мастера функций выберите категорию Ссылки и массивы и функцию
ИНДЕКС. В новом диалоговом окне выберите первый вариант набора параметров.

5. Установите текстовый курсор в первое поле для ввода параметров в окне Аргу­енты функции и выберите в раскрывающемся списке в строке формул пункт
Другие функции.

6. С помощью мастера функций выберите функцию ЛИНЕЙН категории Статистические.

7. В качестве первого параметра функции ЛИНЕЙН выберите диапазон, содержа­щий значения функции (столбец В).

8. В качестве второго параметра функции ЛИНЕЙН выберите диапазон, содержа­щий значения независимой переменной (столбец А).

9. Переместите текстовый курсор в строке формул, чтобы он стоял на имени функ­ции ИНДЕКС. Вкачестве второго параметра функции ИНДЕКС задайте число 1.
Щелкните на кнопке ОК в окне Аргументы функции. Функция ЛИНЕЙН возвращает коэффициенты уравнения прямой линии в виде массива из двух элементов. С помощью функции ИНДЕКС выбирается нужный элемент.

10. Сделайте текущей ячейку D1. Повторите операции, описанные в пп. 3 - 9, чтобы
в итоге в этой ячейке появилась формула: = ИНДЕКС(ЛИНЕЙН(В1:В20;А1:А20);2).Ее можно ввести и вручную (посимвольно). Теперь в ячейках С1 и D1 вычислены, соответственно, коэффициенты аи bуравнения наилучшей прямой.

11. Сделайте текущей ячейку С2. Повторите операции, описанные в пп. 3 - 9, или
введите вручную следующую формулу: =ИНДЕКС(ЛГРФПРИБЛ(В1:В20;А1:А20);1).

12. Сделайте текущей ячейку D2. Повторите операции, описанные в пп. 3 - 9, или
введите вручную следующую формулу:
=ИНДЕКС(ЛГРФПРИБЛ(В1:В20;А1:А20);2).

13. Теперь ячейки С2 иD2 содержат, соответственно, коэффициенты а и bуравне­ния наилучшего показательного приближения.

14. Для интерполяции или экстраполяции оптимальной кривой без явного определения ее параметров можно использовать функции ТЕНДЕНЦИЯ (для линейной зависимости) и РОСТ (для показательной зависимости).

15. Для построения наилучшей прямой линии другим способом дайте команду Сервис -
Data Analysis (Анализ данных).

16. Откроется одноименное диалоговое окно. В списке Analysis Tools (Инструменты ана­лиза) выберите пункт Regression (Регрессия), после чего щелкните на кнопке ОК.

17. В поле Input Y Range (Входной интервал Y) укажите методом протягивания диа­азон, содержащий значения функции (столбец В).

18. В поле Input X Range (Входной интервал X) укажите методом протягивания диапазон, содержащий значения независимой переменной (столбец А}.

19. Установите переключатель New Worksheet (Новый рабочий лист) и задайте для
него имя Результат расчета.

20. Щелкните на кнопке ОК и по окончании расчета откройте рабочий лист Результат расчёта. Убедитесь, что вычисленные коэффициенты (см. ячейки В17 и В18)
совпали с полученными в первом методе.

21. Сохраните рабочую книгу book.xls.