Упражнение 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.
Sub Zadanie()
ReDim C(5, 6)
k_pos = 0
mini = LBound(C, 1)
minj = LBound(C, 2)
For i = LBound(C, 1) To UBound(C, 1)
For j = LBound(C, 2) To UBound(C, 2)
C(i, j) = Round(Rnd * 10)
if C(i, j) > 0 then k_pos = k_pos + 1
if C(i, j) < C(mini, minj) then
mini = i
minj = j
end if
Next
Next
MsgBox "min C(" & mini & ", " & minj & ")=" & C(mini, minj) & vbNewLine & "c>0: " & k_pos
End Sub
Call Zadanie()