Основные сведения

Синтаксис функции: ТЕНДЕНЦИЯ (Изв_знач_у, Изв_знач_х, Нов_знач_х, Константа).

Использование данной функции позволяет определить значения результативного признака у_i, рассчитанные по построенному линейному уравнению регрессии с помощью функции ЛИНЕЙН (лабораторная работа №4).

При этом в построенное уравнение регрессии вида:

у = b₀ + b₁х₁ + b₂х₂ +...

подставляют необходимые значения переменных (факторов) х₁, х₂, … и т.д., и получают значения функции (результативного признака у), соответствующие этим переменным.

Окно статистической функции ТЕНДЕНЦИЯ имеет следующий вид (рис. 2.5.1):

Рис. 2.5.1.

где Изв_знач_у — это множество значений у;

Изв_знач_х — это необязательное множество значений х; Нов_знач_х — это новые значения х, для которых функция ТЕНДЕНЦИЯ определяет соответствующие значения у;

Константа — это логическое значение, которое указывает, требуется ли, чтобы коэффициент b₀ был равен 0.

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

Если массив Изв_знач_у имеет одну строку, то каждая строка массива Изв_знач_х интерпретируется как отдельная переменная.

Массив Изв_знач_х может содержать одно или несколько множеств переменных. Если используется только одна переменная, то Изв_знач_у и Изв_знач_х могут быть массивами любой формы, при условии, что они имеют одинаковую размерность. Если используется более одной переменной, то Изв_знач_у должны быть вектором (т.е. интервалом высотой в одну строку или шириной в один столбец).

Если Изв_знач_х опущены, то предполагается, что это массив {1; 2; 3;...} такого же размера, как и Изв_знач_у.

Нов_знач_х должны содержать столбец (или строку) для каждой независимой переменной, так же как Изв_знач_х.

Таким образом, если Изв_знач_у имеет один столбец, то Изв_знач_х и Нов_знач_х должны иметь одинаковое количество столбцов. Если Изв_знач_у имеет одну строку, то Изв_знач_х и Нов_знач_х должны иметь одинаковое количество строк.

Если Нов_знач_х опущены, то предполагается, что они совпадают с Изв_знач_х.

Если опущены оба массива и Изв_знач_х и Нов_знач_х, то предполагается, что это массив {1; 2; 3; ...} такого же размера, что и Изв_ знач_у.

Если Константа имеет значение ИСТИНА или опущена, то коэффициент b₀ вычисляется обычным образом.

Если Константа имеет значение ЛОЖЬ, то коэффициент b₀ полагается равным 0.

Можно использовать функцию ТЕНДЕНЦИЯ для аппроксимации полиномиальной кривой, проводя регрессионный анализ для той же переменной, возведенной в различные степени. Например, пусть столбец А содержит значения у, а столбец В содержит значения х. Можно ввести х² в столбец С, х³ в столбец D, и так далее, а затем провести регрессионный анализ столбцов от В до D со столбцом А.

Формулы, которые возвращают массивы, должны быть введены как формулы массивов.

При вводе массива констант в качестве аргумента, такого как Изв_знач_х, следует использовать точку с запятой для разделения значений в одной строке и двоеточие для разделения строк.

Пример 2.5.1. В условиях примера 2.4.1 (лабораторная работа 4) определить прочность ткани, рассчитанный по уравнению регрессии (2.5.1) для опытных значений соответствующих переменных х₁ и х₂.

у = -20,4137 - 0,3136х₁ + 1,4357х₂. (2.5.1)

Решение.

В полученное уравнении регрессии последовательно подставляем значениях, х₁ и х₂ и получаем значения у, рассчитанные по уравнению регрессии. Например

у₁ = -20,4137 - 0,3736 • 37 + 1,4357 • 39 = 23,97

у₂ = -20,4137 - 0,3736 • 33 + 1,4357 • 40 = 26,66

………………………………………………………

у₅ = -20,4137 - 0,3736 • 31 + 1,4357 • 51 = 43,08

Сравнивая опытные данные у_i и рассчитанные по уравнению регрессии _i , можно вычислить остатки ряда:

е₁ = у_{1 - 1} = 26- 23,97 = 2,03

е₁ = у_{2 - 2} = 33 - 26,66 = 6,34

………………………………………

Е₁₅ = у_{15 - 15} = 45 – 43,08 = 1,92.

При необходимости можно вычислить значение результативного признака по любым значениям факторов, например, при х₁ = 25, х₂ = 30

= -20,4137 - 0,3736 • 25 + 1,4357 • 30 = 14,81.

Решим данный пример с использованием статистической функции ТЕНДЕНЦИЯ.

Алгоритм действий следующий.

1. Формируем таблицу исходных данных (табл. 2.5.1).

Таблица 2.5.1

	А	В	С	D
1	№ опыта	Прочность ткани, уi	Прочность нитей, х1	Заполнение ткани, х2
2	1	26	37	39
3	2	33	33	40
4	3	24	15	35
5	4	29	36	48
6	5	42	26	53
7	б	24	24	42
8	7	52	15	54
9	8	56	33	54
10	9	26	44	50
11	10	45	34	53
12	11	27	63	46
13	12	54	8	50
14	13	34	44	43
15	14	48	43	55
16	15	45	31	51

2. Выбираем (выделяем) ячейку, в которую будет выведен результат вычислений (Е2).

3. Вызываем Мастер функций, нажав кнопку fx на панели инструментов. В Мастере функций из категории Статистические выбираем функцию ТЕНДЕНЦИЯ —> ОК.

4. В левом верхнем углу листа Excel появится окно функции ТЕНДЕНЦИЯ. Нажав кнопку в поле Изв_знач_у, переходим на рабочий листе исходными данными и выделяем его мышью (В2:В16). Затем, повторно нажав эту же кнопку, возвращаемся к окну функции ТЕНДЕНЦИЯ. Нажав кнопку в поле Изв_знач_х, переходим на рабочий лист с исходными данными и выделяем его мышью (C2:D16). Затем, повторно нажав эту же кнопку, возвращаемся к окну функции ТЕНДЕНЦИЯ.

5. Поле Нов_знач_х оставляем пустым. Если Нов_знач_х опущены, то предполагается, что они совпадают с Изв_знач_х.

6. Поле Константа оставляем пустым. Если Константа имеет значение ИСТИНА или опущена, то коэффициент b₀ вычисляется обычным образом.

7. Набираем комбинацию клавиш CTRL+SHIFT+ENTER (ввод формулы массива).

В окне функции ТЕНДЕНЦИЯ и в ячейке Е2 появится результат решения (первый элемент массива). Выделяем ячейку Е2, в сроке функции появится формула, в которой надо сделать В16 и D16 абсолютными значениями, ставим знак ($). Формула в строке функции будет иметь вид:

=ТЕНДЕНЦИЯ(В2:В$16; D2:D$16).

Выделяем ячейкуЕ2 и за крестик тянем до Е16, копируя формулу. В ячейках появится результат вычислений (табл. 2.5.2).

Таблица 2.5.2

№ опыта	Е
1	23,9731397
2	26,19737844
3	22,15154161
4	35,99404247
5	48,26701634
б	30,18412343
7	55,14822435
8	48,59542887
9	37,51127713
10	46,91530767
11	28,43039567
12	53,37690625
13	34,5212400
14	48,4506400
15	45,4308872

Вывод: Таким образом, используя функцию ТЕНДЕНЦИЯ, без уравнения регрессии, мы определили какой прочностью будет обладать ткань если изменять прочность нитей (х₁) и заполнение ткани (х₂). Наибольшее значение прочности ткани 55,14 даН у образца №7, следовательно, оптимальные условия получения прочной ткани: прочность нитей 15 сН/текс, линейное заполнение ткани 54%.