Упражнение 5. Анализ данных с использованием метода наименьших квадратов Задача. Для заданного набора пар значений независимой переменной и функции определить наилучшие линейное приближение в виде прямой с уравнением у = ах + b и показательное приближение в виде линии с уравнением у = b*ax 9. Запустите программу Excel (Пуск ► Программы ► Microsoft Excel) и откройте рабо¬чую книгу book.xls, созданную ранее. 10. Щелчком на ярлычке выберите рабочий лист Обработка эксперимента. 11. Сделайте ячейку С1 текущей и щелкните на кнопке Изменить формулу в строке формул. Раскройте список на левом краю строки формул и выберите пункт Другие функции. 12. В окне мастера функций выберите категорию Ссылки и массивы и функцию ИНДЕКС. Б новом диалоговом окне выберите первый вариант набора параметров. 13. Установите текстовый курсор в первое поле для ввода параметров в палитре формул и снова выберите пункт Другие функции в раскрывающемся списке в строке формул. 14. С мастера функций выберите функцию ЛИНЕЙН категории Статисти¬ческие. 15. В качестве первого параметра функции ЛИНЕЙН выберите диапазон, содержащий значения функции (столбец В). 16. В качестве второго параметра функции ЛИНЕЙН выберите диапазон, содержащий значения независимой переменной (столбец А). 17. Переместите текстовый курсор в строке формул, чтобы он стоял на имени функции ИНДЕКС. В качестве второго параметра функции ИНДЕКС задайте число 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). Теперь ячейки С2 и D2 содержат, соответственно, коэффициенты а и b уравне¬ния наилучшего показательного приближения. Внимание :Для интерполяции или экстраполяции оптимальной кривой без явного определения ее параметров можно использовать функции ТЕНДЕНЦИЯ {для линейной зависимости) и РОСТ (для показательной зависимости). 4. Для построения наилучшей прямой другим дайте команду Сервис ► Анализ данных. 5. В списке Инструменты анализа выберите пункт Регрессия, после чего щелкните на кнопке ОК. 6. В поле Входной интервал Y укажите методом протягивания диапазон, содержащий значения функции (столбец В). 7. В поле Входной интервал X укажите методом протягивания диапазон, содержащий значения независимой переменной (столбец А). 8. Установите переключатель Новый рабочий лист и задайте для него имя Результат расчета. 9. Щелкните на кнопке ОК и по окончании расчета откройте рабочий лист Результат расчета. Убедитесь, что вычисленные коэффициенты (см. ячейки В17 и В18) совпали с полученными первым методом. 10. Сохраните рабочую книгу book.xls.

vo10    3   21.06.2020 20:33    23