4.1. Встроенная функций Microsoft Excel линейн

Формат функции: ЛИНЕЙН(<известное >;<известное >)

Функция вычисляет коэффициенты линейного уравнения регрессии y=a+bx для множества значений независимой переменной и зависимой переменной . Результаты выводятся в две смежные ячейки – сначала коэффициент при х т.е. - b, затем a - свободный член,.

Поскольку и являются массивами, то функция вводится как функция обработки массивов.

Задание 4.1. Для заданных экспериментальных данных на рис. 1.8 найдите коэффициенты линейной регрессии y=a+bx, используя функцию ЛИНЕЙН. Вычисления делаются в предположении, что и зависят линейно.

Последовательность действий.

1. Введите исходные данные как показано на рис.1.8 в ячейки В3:С12.

2. Выделите ячейки В16:С16, где будут получены соответственно свободный член a и коэффициент при х т.е.–. b

3. Ведите формулу: =ЛИНЕЙН(C3:С12;В3:В12),

4. Нажмите клавиши Ctrl+Shift+Enter.

Если все проделано верно, то в ячейках В16:С16 будут получены коэффициенты линейной регрессии y=1,0667+1,1697x.

Рис.1.8. Обработка экспериментальных данных с помощью функций ЛИНЕЙН и ТЕНДЕНЦИЯ

4.2. Встроенная функций Microsoft Excel тенденция

Формат функции:

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

Функция вычисляет ожидаемое новое значение у_i для нового х_i, если известны некоторые опытные значения и .

Задание 4.2. Для исходных данных примера 4.1, приведенных на рис.1.8 в таблице 1 вычислите ожидаемое новое значение для нового значения используя функцию ТЕНДЕНЦИЯ. Вычисления делаются в предположении, что х_i и у_i зависят линейно.

Последовательность действий.

1. Новые значения запишите в ячейках E3:E27, табл.2 на рис.1.8. Для этого можно использовать автозаполненние.

2. Результаты вычислений новых значений будем записывать в ячейки F3:F27. Для этого введите в ячейку F3 с помощью Мастера функций следующую формулу: =ТЕНДЕНЦИЯ($C$3:$C$12;$B$3:$B$12;E3).

3. Нажмите клавиши Ctrl+Shift+Enter.

4. Скопируйте формулу вниз до конца таблицы для всех новых значений .

5. Если все проделано верно, то вы получите таблицу 2, как показано на рис. 1.8.

А сейчас попробуем разместить на одной координатной плоскости экспериментальные данные табл. 1 и полученные расчетные данные табл. 2 приведенные на рис.1.8. Диапазон изменения независимой переменной х в обеих таблицах различен. Поэтому возникает задача наложения одного графика на другой.

Последовательность наложения одного графика функции на график другой функции

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

2. Активизируйте уже построенный график и выберите команду меню Диаграмма\Добавить данные

3. В окне Новые данные укажите данные x_i, y_i из таблицы 2 рис.1.8.

4. В появившемся окне Специальная вставка установите флажки в полях:

• новые ряды,

• категории (значение оси х) в первом столбце.

Если все проделано верно, то вы получите множество экспериментальных точек и аппроксимирующую их линейную зависимость как показано на рис.1.9.

Рис.1.9. Множество экспериментальных точек и аппроксимирующая их линейная зависимость