Excel, аппроксимация, страница 4

Рис. 10.1-2

Содержимое клетки Е2 представлено в пользовательском формате вида +#,00"x";–0,00"х" с тем, чтобы отображался и знак плюс и буква Х.

В строке 14 все формулы являются суммами вышележащих ячеек в диапазоне с 4 по 13 строки.

Таким образом, если нам понадобится вычислить ожидаемое значение прибыли Y в будущем, например, при капиталовложениях в сумме 20 единиц, нужно подставить их в найденную функцию Y=0,64+1,8*20=36,64. Однако достоверность такого предположения может оказаться не достаточно высокой, ввиду того, что линейное описание процесса, возможно, слишком примитивно. Техника аппроксимации более сложными функциями будет изучена ниже. Сначала рассмотрим встроенные функции Excel (ЛИНЕЙН() и ТЕНДЕНЦИЯ()) для более быстрого нахождения коэффициентов уравнения линейной регрессии.

4ЛИНЕЙН(<известное Y>;<известное X>) – вычисляет два коэффициента линейного уравнения регрессии для множества значений независимой переменной Х и зависимой переменной Y. Результат выводится в две смежные ячейки – сначала коэффициент при Х, затем – свободный член. Ввиду этого функция должна вводиться как функция обработки массива: выделяются две ячейки для результата, вводится функция и нажимаются клавиши Ctrl+Shift+Enter (вместо обычного Enter).

Пример. Если исходные данные расположены так, как показано на рис. 10.1-3, и в В4:C4 введена функция

{=ЛИНЕЙН(B2:K2;B1:K1)},

результаты в клетках В4 и С4 можно интерпретировать как коэффициенты линейного уравнения регрессии

y = 0,6364x + 1,8.

4ТЕНДЕНЦИЯ(<известноеY>;<известноеX>;<новоеX>)

– вычисляет ожидаемое новое значение Y для нового Х, если известны некоторые опытные значения X и Y. Вычисления делаются в предположении, что Х и Y зависят линейно.

Пример: Исходные данные расположены (рис. 10.1-3) в клетках G4 и G5, результаты – в Н4 и Н5

H4=ТЕНДЕНЦИЯ($B$2:$K$2;$B$1:$K$1;G4)

H5=ТЕНДЕНЦИЯ($B$2:$K$2;$B$1:$K$1;G5).