Валентин Арьков - Статистический анализ взаимосвязи в Excel

Y — результативный признак, или результат, или зависимая переменная. При моделировании мы вычисляем Yпо формуле, в которой участвуют фактор Xи случайность E. Коэффициент при случайной составляющей определяет её СИГМУ (стандартное отклонение) и, соответственно, разброс вокруг среднего.

n — объём выборки. Это количество изучаемых объектов (статистических единиц), например, людей, квартир или жёстких дисков. У каждого объекта будут свои значения Xи Y. Например, у каждого человека будет своя пара значений: роста и вес. Можно сказать, что в нашем случае объём выборки — это число строк в таблице с данными, или число записей в базе данных, или КОЛИЧЕСТВО ПАР случайных чисел {X, Y}. Для каждого объекта будет своя пара чисел Хи Y. В нашей работе объём выборки равен 200 для всех вариантов.

Оформление отчёта подробно описано в предыдущем выпуске серии. Создадим новую рабочую книгу. Сохраним отчёт в файле с коротким информативным названием. Сделаем титульный лист отчёта и заготовку оглавления.

В данной работе мы будем вначале рассматривать линейную функцию, а затем нелинейную. Соответственно, у нас имеется две таблицы с вариантами заданий.

Выберем свой вариант задания и опишем его на новом листе отчёта.

Вначале надо представить себе, что представляют собой наши данные, как будет выглядеть график. Для этого сделаем зарисовку на бумаге — как в предыдущей работе.

Нам предстоит изобразить расположение нашей линии и форму диаграммы разброса — в самых общих чертах.

Зарисовка диаграммы разброса

Изобразим оси координат и займём нужное место на листе бумаги.

Масштаб на графике необязательно должен начинаться от нуля. Главное, чтобы диаграмма разброса занимала всё поле графика. Метки на осях — «красивые», круглые числа.

В нулевом варианте задания Xизменяется в пределах от 1000 до 2000. По оси «икс» указываем крайние значения 1000 и 2000 в начале и конце оси.

Теперь оценим диапазон значений Y. Берём формулу для Y, пока без учёта случайности Е:

Y = 1400 +0,065 · X

Подставляем крайние значения X:

Y (1000) = 1400 +0,065 · 1000 = 2050

Y (2000) = 1400 +0,065 · 2000 = 2700

Выбираем масштаб по оси «игрек» от 2000 до 3000.

Получаем 2 точки, через них проводим прямую линию.

Добавим разброс вокруг линии. Для этого используем ПРАВИЛО ТРЁХ СИГМ: почти все значения случайной величины находятся в диапазоне «среднее плюс-минус три сигмы». Когда мы строим разброс вокруг линии, в роли среднего значения будет точка на линии.

В нулевом варианте случайный разброс равен 50 · Е. Случайная составляющая Е имеет единичную дисперсию. Сигма Етоже будет равна единице, потому что сигма — это квадратный корень из дисперсии. Если умножить случайную величину Ена 50, то её сигма тоже увечивается в 50 раз. Стало быть, сигма равна 50, а три сигмы равно

3 · 50 = 150.

Вокруг первой и последней точек на графике строим разброс «плюс-минус три сигмы».

2050 — 150 = 1900

2050 +150 = 2200

2700 — 150 = 2550

2700 +150 = 2850

Проводим пунктиром две параллельные линии. Это будут границы случайного разброса.

Заполняем эту «полосу» точками — случайным образом.

Вот что мы ожидаем увидеть, когда смоделируем исходные данные — см. рисунок.

Зарисовка

Зачем в этой работе мы делаем зарисовку? При любых вычислениях нужно уметь ЗАРАНЕЕ ОЦЕНИВАТЬ и МЫСЛЕННО ПРЕДСТАВЛЯТЬ себе будущие результаты. Тогда сразу будут видны ГРУБЫЕ ОШИБКИ. И эти ошибки можно будет сразу же выявить и исправить. Ну а ошибки будут всегда.

Если не оценивать будущий результат, то можно легко сказать: «Это компьютер так посчитал». Проблема в том, что исходные данные вводит человек и результаты будет использовать тоже человек. Программу тоже написал человек, и не один. Поэтому ОТВЕТСТВЕННОСТЬ за результаты расчётов несёт не компьютер, а человек.

Вторая часть задания — это нелинейная функция второго порядка. Варианты заданий приводятся в таблице. Другие названия: квадратичная функция, парабола — см. формулу.

Уравнение параболы можно записать разными способами, поэтому нужно следить за тем, в каком порядке расположены члены уравнения.

Уравнение параболы

В первом примере степени аргумента расположены по убыванию. Во втором — по возрастанию. Как записать уравнение — не так важно. Главное — правильно прочитать те результаты, которые нам выдаст программа.

На новом листе отчёта опишем свой вариант задания. Напомним, что мы в качестве примера рассматриваем нулевой вариант.

Пределы изменения факторного признака: от 1000 до 3000.

Уравнение функции:

y = 7000 — 7 · x +0,002 · x 2+200 · e

Коэффициенты уравнения:

a 0= 7000

a 1= — 7

a 2= 0,002

s = 200

Коэффициент при случайной составляющей Eобозначим буквой S, поскольку он определяет значение «сигмы».

Чтобы сделать зарисовку параболы, нужно определить два основных момента.

Вначале определим знак старшего коэффициента при второй степени фактора a 2. Если коэффициент a 2положителен, то ветви параболы напрaвлены вверх. И наоборот.

В нулевом варианте старший коэффициент равен

a 2= 0,002.

Коэффициент положительный, следовательно ветви параболы смотрят вверх.

Затем определим положение вершины параболы.

Вершина параболы

Докажите справедливость формул для нахождения координат вершины параболы, приравняв первую производную функции к нулю. Затем подставьте полученное значение х 0в уравнение параболы и упростите выражение.

Подставляем наши коэффициенты и находим координаты вершины — см. формулы.

Координаты вершины

Далее определим значения функции на границах диапазона значений — см. формулы.

Крайние значения

И наконец добавляем границы случайного разброса по «правилу трёх сигм». Сигма в нулевом варианте равна 200, соответственно, три сигмы равно 600. Добавляем и отнимаем 600 в каждой из трёх точек — см. формулы.

Делаем зарисовку и вставляем в отчёт, как описано в предыдущем выпуске. Цель этого упражнения — представить общую форму графика, а не демонстрировать художественный талант или способности к черчению.

Зарисовка

Сгенерируем исходные данные — значения двух переменных xи y — в соответствии c вариантом задания. В качестве примера разбираем нулевой вариант. Используем функцию