Статистический анализ взаимосвязи в Excel - [3]
Уравнение параболы
В первом примере степени аргумента расположены по убыванию. Во втором — по возрастанию. Как записать уравнение — не так важно. Главное — правильно прочитать те результаты, которые нам выдаст программа.
На новом листе отчёта опишем свой вариант задания. Напомним, что мы в качестве примера рассматриваем нулевой вариант.
Пределы изменения факторного признака: от 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 вариантом задания. В качестве примера разбираем нулевой вариант. Используем функцию
Random Number Generation
Генерация случайных чисел
надстройки
Data Analysis
Анализ данных.
Подробности использования генератора мы уже описали в предыдущей работе. Числа округляем до целых.
Создаём столбец случайных чисел X.
Распределение — Равномерное
Левая и правая границы — 1000 и 2000.
Начальное состояние — 1234. Можно взять любые другие числа, но их нужно зафиксировать в отчёте, чтобы не использовать второй раз.
Настройки генератора
Полученные значения X округляем до целых и записываем в другой столбец. Для округления используем функцию
ROUND (number, num_digits)
ОКРУГЛ (число; число разрядов).
Обратим внимание, что в английской версии аргументы функции разделяют ЗАПЯТОЙ, а в русской — ТОЧКОЙ С ЗАПЯТОЙ. Причина в том, что в английской версии десятичный разделитель целой и дробной частей — точка, а в русской — запятая.
Пример результата генерации данных и округления можно видеть на рисунке ниже. В дальнейшей работе используются именно округлённые значения X и Y.
Сгенерированные данные
Вспомогательная случайная составляющая Eпоможет нам сформировать случайный разброс вокруг линии. Она имеет нормальное распределение с нулевым математическим ожиданием и единичным стандартным отклонением. Значения E следует сгенерировать в отдельном столбце с ДРУГИМ начальным состоянием генератора.
Программный генератор случайных чисел на самом деле создаёт ПСЕВДОСЛУЧАЙНЫЕ числа. Другими словами, они только кажутся случайными. Если задавать одно и то же начальное состояние генератора, мы получим одну и ту же последовательность «случайных» чисел.
Проведём опыт и убедимся, к чему приводят одинаковые настройки генератора. Сгенерируем столбцы Х и Е с одинаковым начальным состоянием генератора: 1234. Результат — на рисунке слева. Теперь сгенерируем Х и Е с настройками 1234 и 5678. Результат показан справа.
Влияние начального состояния
На левом графике можно видеть явную связь (точную функциональную зависимость) между случайными числами Х и Е — при одинаковой настройке генератора: 1234 и 1234. На этом графике просматривается кривая нормального распределения. Она используется для создания случайного числа с заданным распределением. Разные настройки 1234 и 5678 дают действительно независимые случайные числа. Учтём на будущее.
Выделим два столбца с готовыми данными — с заголовками. Вставим данные на новый лист. Выберем режим вставки значений из буфера обмена.
Вставка значений
При выборе режима вставки из буфера можно сразу увидеть результат на экране. Нажимаем кнопку
Values
Значения.
После вставки получаем числа вместо формул в ячейках таблицы. Теперь никакие наши действия не приведут к обновлению и изменению данных.
Диаграмма разброса
Пришло время посмотреть на график наших исходных данных. На диаграмме разброса каждая пара чисел Х и Y изображается отдельной точкой. Точки на графике НЕ СОЕДИНЯЮТ линиями. В примере «Рост — Вес» нет никакой связи между параметрами соседей по парте. Поэтому каждый человек — это отдельная точка на графике.
Выделяем два столбца с округлёнными значениями X и Y. Выбираем в меню:
