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

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

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

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

— Частота

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

— Кумулята

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

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

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

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

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

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

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

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

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