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

Зарисовка

Сгенерируем исходные данные — значения двух переменных 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.Выбираем в меню:

Insert — Charts — Insert Scatter (X, Y) or Bubble Chart — Scatter — Scatter

Вставка — Диаграммы — Вставить точечную (X, Y) или пузырьковую диаграмму — Точечная — Точечная.

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

По умолчанию диаграмма разброса выглядит не слишком привлекательно — см. график. Настроим оформление графика.

Диаграмма разброса по умолчанию

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

Format Axis

Формат оси

выбираем раздел

Axis Options

Параметры оси.

Устанавливаем пределы по горизонтальной оси от 1000 до 2000.

Масштаб по оси

Щёлкнем по вертикальной оси и выберем такие значения, чтобы диаграмма разброса занимала всё место на графике.