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

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

1. Запустите программу Excel (Пуск > Программы > Microsoft Excel) и откройте рабо­чую книгу book.xls, созданную ранее.

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

3. Сделайте ячейку С1 текущей и щелкните на кнопке Изменить формулу в строке формул. Раскройте список на левом краю строки формул и выберите пункт Другие функции.

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

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

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

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

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

9. Переместите текстовый курсор в строке формул, чтобы он стоял на имени функ­ции ИНДЕКС. В качестве второго параметра функции ИНДЕКС задайте число 1. Щелкните на кнопке ОК на палитре формул.

ГЩ Функция ЛИНЕЙН возвращает коэффициенты уравнения прямой в виде массива из двух элементов. С помощью функции ИНДЕКС выбирается нужный элемент.

Практическое занятие _______________ __________323

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

11. Сделайте текущей ячейку С2. Повторите операции, описанные в пп. 3-9, или введите вручную следующую формулу:

=ИНДЕКС(ЛГРФПРИБЛ(В1:В20;А1:А20);1).

12. Сделайте текущей ячейку D2. Повторите операции, описанные в пп. 3-9, или введите вручную следующую формулу:

=ИНДЕКС(ЛГРФПРИБЛ(В1:В20;А1:А20);2).

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

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

13. Для построения наилучшей прямой другим способом дайте команду Сервис > Анализ данных.

14. В списке Инструменты анализа выберите пункт Регрессия, после чего щелкните на кнопке ОК.

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

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

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

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

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

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