Задача. Для заданного набора пар значений независимой переменной и функции определить наилучшие линейное приближение в виде прямой с уравнением у = ах + b и показательное приближение в виде линии с уравнением
у = b×ax.
1. Запустите программу Excel (Пуск > Программы > Microsoft Excel) и откройте рабочую книгу book.xls, созданную ранее.
2. Щелчком на ярлычке выберите рабочий лист Обработка эксперимента.
3. Сделайте ячейку С1 текущей и щелкните на кнопке Изменить формулу в строке формул. Раскройте список на левом краю строки формул и выберите пункт Другие функции.
4. В окне мастера функций выберите категорию Ссылки и массивы и функцию ИНДЕКС. В новом диалоговом окне выберите первый вариант набора параметров.
5. Установите текстовый курсор в первое поле для ввода параметров в палитре формул и снова выберите пункт Другие функции в раскрывающемся списке в строке формул.
6. С помощью мастера функций выберите функцию ЛИНЕЙН категории Статистические.
7. В качестве первого параметра функции ЛИНЕЙН выберите диапазон, содержащий значения функции (столбец В).
|
|
8. В качестве второго параметра функции ЛИНЕЙН выберите диапазон, содержащий значения независимой переменной (столбец А).
9. Переместите текстовый курсор в строке формул, чтобы он стоял на имени функции ИНДЕКС. В качестве второго параметра функции ИНДЕКС задайте число 1. Щелкните на кнопке ОК на палитре формул.
Функция ЛИНЕЙН возвращает коэффициенты уравнения прямой в виде массива из двух элементов. С помощью функции ИНДЕКС выбирается нужный элемент.
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.В поле Входной интервал Уукажите методом протягивания диапазон, содержащий значения функции (столбец В).
16.В поле Входной интервал X укажите методом протягивания диапазон, содержащий значения независимой переменной (столбец А).
17.Установите переключатель Новый рабочий лист и задайте для него имя Результат расчета.
18.Щелкните на кнопке ОК и по окончании расчета откройте рабочий лист Результат расчета. Убедитесь, что вычисленные коэффициенты (см. ячейки В17иВ18) совпали с полученными первым методом.
19.Сохраните рабочую книгу book.xls.
Мы научились анализировать с помощью программы Excel экспериментальные данные с использованием метода наименьших квадратов. Мы применили для вычислений разные средства программы Excel. Мы получили информацию, необходимую для построения графиков нужных приближений.