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

Проблемы с масштабом решаем так. Значение 190 находится в начале интервала, обозначенного 193. Значение 310 находится в конце интервала, следующего за 303.

Гистограмма и кумулята

Следующий способ группировки — это подсчёт количества попаданий в интервалы с помощью формул и функций.

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

Добавим в нашу таблицу новые столбцы:

— Середина интервала

— Частота

— Относительная частота

— Кумулята

Сделаем заголовки для каждого столбца. Для наглядности вставим новую строку под заголовком и укажем в ней условные обозначения столбцов.

Заголовки таблицы

Заполним столбец со средними значениями, как описано выше.

Будем использовать функцию

FREQUENCY (data_array, bins_array)

ЧАСТОТА (массив_данных; массив_интервалов)

data_array — диапазон ячеек исходных данных (выборки)

bins_array — диапазон верхних (правых) границ интервалов группировки

Это функция массива, и вызывать её нужно будет так, как мы уже описывади выше:

— ввести формулу в левую верхнюю ячейку диапазона

— выделить весь диапазон ячеек

— нажать F2

— нажать Ctrl + Shift + Enter.

Введём следующую формулу в первую ячейку столбца Частота:

=FREQUENCY (

Вызовем мастера функций, нажав кнопку f x слева от строки формул.

Вызов мастера функций

Мастер функций предлагает ввести аргументы в диалоговом окне

Function Arguments

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

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

Щёлкнем по строчке

Data_array

Массив_данных

Перейдём на лист с исходными данными и выделим диапазон ячеек A2:A10001.

Щёлкнем по строчке

Bins_array

Массив_интервалов

Перейдём на последний лист и выделим диапазон ячеек с верхними границами интервалов группировки. В нашем примере это будет B6:B29.

Нажимаем OK.

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

Наша формула появилась в одной ячейке. Теперь выделяем весь диапазон ячеек, где будут подсчитываться частоты. Нажимаем клавишу F2, а затем комбинацию клавиш Ctrl + Shift + Enter.

Во всех ячейках диапазона появились результаты расчётов — частота попадания в интервалы. В строке формул можно увидеть, как изменилась наша формула:

{=FREQUENCY (’04»! A2:A10001,B6:B29)}

Фигурные скобки вокруг формулы указывают, что мы имеем дело с формулой массива.

Формула массива в фигурных скобках

Подсчитаем общее количество значений, попавших в наши интервалы. Рядом с ячейкой, предназначенной для суммы напишем ВСЕГО. Выделим диапазон ячеек с частотами. Справа внизу от выделенного диапазона появляется кнопка экспресс-анализа — см. рисунок:

Quick Analysis

Быстрый анализ

Кнопка экспресс-анализа

Нажимаем кнопку

Quick Analysis

Быстрый анализ

Выбираем вкладку

Totals

Итоги

Нажимаем кнопку

Sum

Сумма

Подсчёт суммы

Проверим, что записано в итоговой ячейке. Щёлкнем по ячейке с итоговой суммой и обратим внимание на строку формул. В ячейке D30находится вызов функции вычисления суммы:

=SUM (D6:D29)

=СУММ (D6:D29)

Формула суммы

Вычислим относительные частоты. Формула для первого значения относительной частоты такая:

=D6/$D$30

Чтобы зафиксировать адрес, при вводе формулы можно выбрать ячейку и нажать клавишу F4. Символ $будет подставлен автоматически перед адресами строки и столбца. Теперь это АБСОЛЮТНЫЕ АДРЕСА, которые изменяются при копировании формулы в другие ячейки.

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

Format Cells

Формат ячеек

В диалоговом окне

Format Cells

Формат ячеек

настраиваем формат:

Number — Category — Percentage

Число — Числовые форматы — Процентный

Процентный формат

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

Подсчитаем накопленные относительные частоты. Выделяем столбец относительных честот. Нажимаем кнопку экспресс-анализа. Выбираем вкладку

Totals

Итоги

Пролистаем список функций вправо и нажмём кнопку подсчёта суммы нарастающим итогом ПО СТОЛБЦУ

Running Total

Нарастающий итог

Подсчёт итогов

Перед нами две кнопки подсчёта суммы нарастающим итогом — по строке и по столбцу. На изображении кнопок показаны строка (голубым цветом) и столбец (оранжевым цветом) — см. рисунок.

Суммы нарастающим итогом

Нас интересует работа со столбцом. Если навести курсор на кнопку суммы нарастающим итогом, на экране выводится результат. Если результат нас устраивает, нажимаем на эту кнопку.

Вызов Running Total

В столбце Кумулятапоявились накопленные частоты. Проверим, как они вычисляются. Выберем любую ячейку в этом столбце и рассмотрим формулу в строке формул:

=SUM ($E$6:E6)

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

Формула кумуляты

На этом мы заканчиваем наши расчёты и переходим к построению графиков.

Начинаем с относительных частот. Как мы уже убедились, столбиковые диаграммы в Excel имеют один недостаток: координаты по горизонтальной оси — порядковый номер столбика, а не середина интервала группировки.

Чтобы использовать на графике значения случайной величины, построим второй вид графика относительных частот под названием ПОЛИГОН. Это ломаная линия. Координаты по оси «икс» — середины интервалов, координаты по «игрек» — относительные частоты.

Строим полигон точно так же, как мы строили кумуляту в предыдущем разделе. Под полигоном строим кумуляту — по верхним границам интервалов. Настраиваем размеры и вид обеих диаграмм.

Полигон и кумулята

Задание. Самостоятельно постройте гистограмму c использованием функции

COUNTIF (range, criteria)

СЧЁТЕСЛИ (диапазон; критерий)

Сравним построенные графики распределения с теорией в соответствии с вариантом задания.

Начинаем с кумуляты. Вычислим теоретические значения функции распределения. Добавляем новый столбец и озаглавим его Функция распределения F (x). Для теоретических расчётов используем статистическую функцию

NORM. DIST (x, mean, standard_dev, cumulative)