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

Поэтому используем функцию округления

ROUND (number, num_digits)

ОКРУГЛ (число; число_разрядов)

Аргументы функции:

number — округляемое число;

num_digits — количество знаков после запятой.

Щёлкаем по ячейке B3и вводим формулу:

=ROUND (A3,0)

Используем автозаполнение и убеждаемся, что на этот раз округление сделано правильно:

B3: 226,91 ≈ 227

B4: 200,32 ≈ 200

B5: 244,38 ≈ 244

B6: 237,57 ≈ 238

Округление с помощью ROUND

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

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

Результаты сводки и группировки данных представляют в виде таблиц и графиков. Эти графики позволяют приблизительно оценить форму кривых распределения. Нас будут интересовать два основных графика: гистограмма и кумулята. Мы займёмся этими графиками в следующих разделах.

Гистограмма — это столбиковый график частот. Это оценка формы кривой распределения, а точнее функции плотности вероятности.

Основание каждого столбика — интервал значений случайной величины. Высота столбика — частота, то есть количество попаданий в этот интервал, обычно в процентах. Столбики должны примыкать друг к другу. Между столбиками не должно быть просветов. В пакете Excel столбики изображаются одинаковой ширины.

Гистограмма и распределение

Чтобы построить гистограмму, нужно построить интервалы значений и подсчитать число попаданий в каждый интервал. Эти действия называются ГРУППИРОВКА ДАННЫХ. Мы делим все данные на группы и считаем их количество в каждой группе.

Мы рассмотрим несколько способов построения гистограммы.

Самый простой способ построить гистограмму — использовать вставку готовой статистической диаграммы.

Выделяем столбец случайных чисел. Для этого щёлкаем по первой ячейке столбца данных (ячейка B3) и нажимаем комбинацию клавиш Shift + Ctrl + ↓. Клавиша Shiftпри перемещении курсора позволяет выделять ячейки. Комбинация Ctrl + ↓перемещает курсор в самую нижнюю заполненную ячейку столбца.

Вставляем график:

Insert — Charts — Insert Statistic Chart — Histogram

Вставка — Диаграммы — Вставить статистическую диаграмму — Гистограмма

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

В середине экрана появляется график. Теперь его предстоит правильно расположить и настроить.

Автоматические настройки

Перенесём график на новый лист. Щёлкаем правой кнопкой по графику и вырезаем его (переносим в буфер обмена), выбрав пункт

Cut:

Вырезать

Создаём новый лист, нажав кнопку (+)

New Sheet

Новый лист

в нижней части окна. Вставляем график, нажав правую кнопку мыши и выбрав пункт

Paste

Вставка

В списке

Paste Options

Параметры вставки

Выбираем вариант вставки из буфера

Use Destination Theme

Использовать конечную тему

Вставка графика

Можно поступить проще: вырезать комбинацией клавиш Ctrl + C, вставить комбинацией Ctrl + V.

Итак, мы вставили график на новый лист. Кроме нас, никто не знает, что это такое и откуда оно взялось. Поэтому делаем заголовок.

Гистограмма на новом листе

Теперь настроим оформление и параметры графика.

Щёлкаем левой кнопкой мыши по графику и справа обнаруживаем кнопку [+]

Chart Elements

Элементы диаграммы

Управление элементами графика

Нажимаем на эту кнопку и выбираем нужные элементы оформления. В нашем случае потребуются следующие элементы:

масштаб по осям координат

Axes

Оси

заголовки по осям координат

Axis Titles

Названия осей

общий заголовок графика

Chart Title

Название диаграммы

линии масштабной сетки на поле графика

Gridlines

Сетка

Выбор элементов графика

Отредактируем заголовки по осям: xи n. Установим наклонный жирный шрифт высотой 12 пунктов. Настройки шрифта по-прежнему находятся в разделе

Home — Font

Главная — Шрифт

Настройка заголовков по осям

Настроим количество столбцов в гистограмме. Для этого правой клавишей мыши нажмём на горизонтальную ось графика и выберем

Format Axis

Формат оси

Второй вариант — двойной щелчок по горизонтальной оси.

В правой части окна появится окно настройки оси

Format Axis

Формат оси

По умолчанию используется автоматический выбор параметров группировки данных:

Axis Options — Bins — Automatic

Параметры оси — Интервалы — Авто

Параметры группировки

Английское слово Binбуквально означает «корзина». Переводят его по-разному: «карманы», «корзины», «интервалы». Имеется в виду интервал значений, диапазон значений случайной величины для группировки данных. При построении гистограммы подсчитывается количество попаданий в этот интервал.

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

Как подобрать количество интервалов — с этим подходом лучше всего познакомиться на практике. Выбираем вариант

Number of bins

Количество интервалов

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

Если выбрать слишком мало интервалов, пропадут подробности на графике.

Слишком мало интервалов

Если взять слишком много интервалов, то станут заметными случайные погрешности, и на их фоне потеряется полезная информация.

Слишком много интервалов

Нам нужно найти компромисс: не слишком много и не слишком мало интервалов.

Оптимальное количество интервалов

Настроим оформление графика. В соответствии с отечественными и международными стандартами, столбики на гистограмме должны соприкасаться сторонами, прилегать друг к другу. Это объясняется тем, что при группировке данных интервалы группирования тоже соприкасаются своими границами. На гистограмме границы интервалов превращаются в границы столбиков.