Валентин Арьков - Анализ распределения в Excel

После освоения методов обработки данных мы возьмём самые что ни на есть реальные данные на сайтах онлайн магазинов или биржевых брокеров. Здесь можно будет увидеть, как рассмотренные методы работают и насколько полученные результаты отличаются от «красивых», идеальных, приглаженных учебных примеров. Ещё мы на своём опыте почувствуем неприятности, которые подстерегают тех, кто «тупо жмёт на кнопки», не понимая сущности используемых методов.

Для знакомства со стандартными распределениями и статистическим методами смоделируем исходные данные. Генератор случайных чисел позволяет создать выборку заданного объёма, сгенерированную по одному из законов распределения в зависимости от варианта задания. Данные будем располагать в столбцах таблицы. Как мы уже говорили, обработка табличных данных традиционно ориентирована на работу со столбцами, чтобы подсчитывать суммы в нижней строке.

Озаглавим первую колонку x. Каждую колонку лучше озаглавить так, чтобы было понятно, где и что находится. Это полезно при автоматическом формировании графиков и при обработке данных. Читателю такой заголовок поможет понять, что находится в этой колонке. Ну а составителю не помешает такой заголовок, чтобы вспомнить, что он делал неделю или месяц назад. К тому же, при защите работы гораздо проще обсуждать с преподавателем использованную методику и полученные результаты.

Вызываем меню инструментов анализа и выбираем генератор случайных чисел:

Data — Analysis — Data Analysis — Random Number Generation

Данные — Анализ — Анализ данных — Генерация случайных чисел

Вызов генератора

Установим необходимые настройки генератора в диалоговом окне

Random Number Generation

Генерация случайных чисел

Настройка генератора

Нам понадобятся следующие настройки.

Number of Variables

Число переменных

Это количество столбцов, которые будут заполняться случайными числами. Как мы уже говорили, данные в электронной таблице обычно расположены по столбцам. Нам нужен один столбец. Вводим 1.

Number of Random Numbers

Число случайных чисел

Здесь задаём объём выборки n . В нашей таблице это количество строк. В нулевом варианте это 10000.

Distribution — Normal

Распределение — Нормальное

Выбираем нужное распределение из выпадающего списка.

Далее идёт группа настроек

Parameters

Параметры

Это параметры распределения. Для нормального распределения нужно задать среднее значение и сигму.

Mean

Среднее

Задаём среднее значение. Более красивое и грамотное название этого параметра — математическое ожидание. Обозначается греческой буквой «мю». В нулевом варианте среднее равно 250.

Standard deviation

Стандартное отклонение

Указываем значение сигмы. Другие названия этого параметра — стандартное отклонение, среднее квадратическое (квадратичное) отклонение, сокращённо с. к. о. Обозначается греческой буквой «сигма». В нулевом варианте сигма равна 20.

Random Seed

Случайное рассеивание

Вводим любые четыре цифры, например, 1234. Это число определяет начальное состояние генератора псевдослучайных чисел. Так можно определить всю последовательность псевдослучайных чисел. Задавая одно и то же значение параметра, мы каждый раз будем получать один и тот же набор случайных чисел. Поэтому при генерировании новых случайных переменных используйте другие начальные состояния генератора.

СЛУЧАЙНОЕ РАССЕИВАНИЕ — ещё один пример неудачного перевода с английского языка. Это название ни о чём не говорит пользователю, да ещё и сбивает с толка. В данном случае английское слово SEED означает «начальное состояние генератора псевдослучайных чисел». Переводы других терминов тоже будут «креативными». К сожалению, неудачные переводы путешествуют из одной версии пакета в другую почти без изменений. Это особенность программных продуктов. Программы изменяются так быстро, что разработчики физически не успевают исправить все недочёты.

Задание. Поэкспериментируйте с начальной настройкой генератора. Определите максимальное количество десятичных цифр, а затем и максимальное значение этого числа. Попробуйте вводить положительные и отрицательные числа. Сообразите, сколько бит нужно для кодирования такого числа.

Далее идёт группа настроек

Output options

Параметры вывода

Указываем, куда вывести столбец случайных чисел. Выбираем вариант

Output Range

Выходной интервал

Здесь нужно указать интервал ячеек для вывода случайных чисел. Вручную вводить не будем. Достаточно щёлкнуть мышкой по нужной ячейке. Щёлкаем по кнопке со стрелкой, направленной вверх.

Появляется диалоговое окно Random Number Generationдля выбора диапазона ячеек. В этот момент нужно щёлкнуть первую ячейку диапазона. Пусть это будет ячейка A3.

Ячейки А1и А2уже заняты. Как вы помните, в первой строке у нас должен быть заголовок листа. Затем идёт заголовок столбца — имя переменной X.

Итак, в качестве выходного интервала указываем только первую ячейку — весь диапазон указывать не требуется. В результате Excel сам вписывает адрес ячейки: $A$3. Кстати говоря, значок $в Excel фиксирует адрес строки или столбца, чтобы он не изменялся автоматически при копировании формул. Это так называемый АБСОЛЮТНЫЙ АДРЕС.

Диапазон для вывода

После выбора первой ячейки диапазона возвращаемся в предыдущее окно, нажав кнопку со стрелкой, направленной вниз.

Настройка генератора завершена. Нажимаем кнопку ОКи получаем множество случайных чисел в указанном столбце. Перейдём в последнюю заполненную строку, нажав комбинацию клавиш Ctrl + ↓. Оказываемся в строке 10002. Возвращаемся в первую ячейку: Ctrl + Home.

Сгенерированные числа

Рассмотрим полученные числа поподробнее. Щёлкнем по ячейке А3и в окне редактирования формул увидим много знаков после точки. Или после запятой, если установлен русифицированный пакет. В общем, будет много знаков после десятичного разделителя целой и дробной частей. Чтобы увидеть много разрядов в самóй ячейке, щёлкнем по ней правой кнопкой мыши и выберем в контекстном меню следующий пункт:

Format Cells

Формат ячеек

Выясняется, что для сгенерированных чисел установлен общий формат вывода на экран:

Format Cells — Number — General

Формат ячеек — Число — Общий

Установим числовой формат вывода: