Анализ распределения в Excel - [15]
Элементы диаграммы — Название диаграммы
Настраиваем цвет линии на графике.
Format Data Series — Series options — Fill & Line — Line
Формат ряда данных —Параметры ряда — Заливка и границы — Линия
Line — Solid line
Линия — Сплошная линия
Color — Black
Цвет — Чёрный
Width = 0.5 pt
Ширина — 0,5 пт
Если отрезков много, то ломаная линия выглядит как гладкая кривая.
Кумулята
Настроим числовые метки на вертикальной оси, чтобы выводились целые числа:
Format Axis — Axis Options — Number — Decimal places — 0
Формат оси — Параметры оси — Число — Число десятичных знаков — 0
Целочисленные метки
Установим диапазоны значений по осям.
Вертикальная ось — метки в процентах, а границы диапазона — числа. Поэтому пределы изменения будут от 0 до 1:
Category — Percentage
Категория — Процентный
Axis Options — Bounds
Параметры оси — Границы
Minimum — 0
Минимум — 0
Maximum — 1
Максимум — 1
Горизонтальная ось — в соответствии с интервалами группировки — от 190 до 310.
Кумулята
Подгоняем размеры графика и размещаем его под гистограммой. Можно сделать это вручную.
Если захочется особой точности, поработаем через меню параметров графика (числа условные).
Format Chart Area — Chart Options — Size & Properties — Size
Формат области диаграммы — Параметры диаграммы — Размер и свойства — Размер
Height — 1.8 in
Высота — 7,62 см
Width — 5.3 in
Ширина — 12,7 см
В английской версии пакета размеры измеряются в дюймах. В русской версии — в сантиметрах. Можем установить точные значения размеров вручную.
Размер диаграммы
Окончательно совмещаем маштаб гистограммы и кумуляты: начало первого интервала 190, конец последнего интервала 310. Положения этих двух меток на обоих графиках должны совпадать.
Проблемы с масштабом решаем так. Значение 190 находится в начале интервала, обозначенного 193. Значение 310 находится в конце интервала, следующего за 303.
Гистограмма и кумулята
Группировка. Функция FREQUENCY
Следующий способ группировки — это подсчёт количества попаданий в интервалы с помощью формул и функций.
Создадим новый лист и скопируем сюда вспомогательную табличку с нижними и верхними границами интервалов группировки из предыдущего раздела.
Добавим в нашу таблицу новые столбцы:
— Середина интервала
— Частота
— Относительная частота
— Кумулята
Сделаем заголовки для каждого столбца. Для наглядности вставим новую строку под заголовком и укажем в ней условные обозначения столбцов.
Заголовки таблицы
Заполним столбец со средними значениями, как описано выше.
Будем использовать функцию
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
Формат ячеек
настраиваем формат:
