KT Богомолов / МУ / ЗАДАНИЕ_1_СТАТИСТИКА / Дополнительные материалы / Построение гистограмм в Excel_2014
Для определения оптимального количества интервалов может быть использована формула Стерджесса:
n = 1 + (3,322 × lgN )
где N — количество наблюдений. В этом случае величина интервала:
h = ( V max — V min )/ n
Поскольку количество групп не может быть дробным числом, то полученную по этой формуле величину округляют до целого большего числа.
Нижнюю границу первого интервала принимают равной минимальному значению x min . Верхняя граница первого интервала соответствует значению ( x min + h ). Для последующих групп
границы определяются аналогично, то есть последовательно прибавляется величина интервала h .
В Excel для построения гистограмм используются статистическая функция ЧАСТОТА в сочетании с мастером построения обычных диаграмм и процедура Гистограмма из пакета анализа .
Функция ЧАСТОТА (массив_данных, двоичный_массив) вычисляет частоты появления случайной величины в интервалах значений и выводит их как массив цифр, где
• Массив_данных — массив исходных данных, для которых вычисляются частоты;
• Массив_интервалов — это массив интервалов, по которым группируются значения выборки .
Перед вызовом функции ЧАСТОТА необходимо выделить столбец c числом ячеек, равным числу интервалов n , в который будут выведены результаты выполнения функции.
Вызвать Мастер функций (кнопка f x ):
и функцию ЧАСТОТА .
В поле Массив_данных ввести диапазон данных наблюдений А3:А102 (с листа ‘Расчетные данные’) . В поле Массив_интервалов ввести диапазон интервалов с того же листа ([‘Расчетные данные’!F16:F23] – в данном примере).
При завершении ввода данных нажать комбинацию клавиш Ctrl+Shift+Enter.
В предварительно выделенном столбце (C5:C12 – в данном примере) должен появиться массив
Столбец Накопленные частоты получается последовательным суммированием относительных частот (в процентном формате) в направлении от первого интервала к последнему.
В завершении с помощью Мастера диаграмм строится диаграмма абсолютных и накопленных частот с выбором типа диаграммы соотвественно гистограмма и график.
Для автоматизированного построения гистограммы средствами Excel необходимо обратиться к меню « Сервис Анализ данных» . (Excel 2003) или на вкладке Данные выбрать Анализ данных
(Excel 2007. 2010):
В появившемся списке выбрать инструмент Гистограмма и щелкнуть на кнопке ОК. Появится окно гистограммы, где задаются следующие параметры:
Входной интервал :– адреса ячеек, содержащие выборочные данные.
Интервал карманов : (необязательный параметр) – адреса ячеек, содержащие границы интервалов. Это поле предлагается оставить пустым, предоставив Excel самому вычислить границы интервалов (карманов – в терминах Excel).
Метки – флажок, включаемый, если первая строка во входных данных содержит заголовки. Если заголовки отсутствуют, то флажок следует выключить.
Выходной интервал: / Новый рабочий лист: / Новая рабочая книга.
Включенный переключатель Выходной интервал требует ввода адреса верхней ячейки, начиная с которой будут размещаться вычисленные относительные частоты j .
В положении переключателя Новый рабочий лист: открывается новый лист, в котором начиная с ячейки А1 размещаются частности j .
В положении переключателя Новая рабочая книга открывается новая книга, на первом листе которой начиная с ячейки А1 размещаются частности j .
Парето ( отсортированная гистограмма ) – устанавливается, чтобы представить j в порядке их убывания. Если параметр выключен, то j приводятся в порядке следования интервалов.
Интегральный процент – устанавливается в активное состояние для расчета выраженных в процентах накопленных относительных частот (аналог значений столбца Накопленные частоты ).
Вывод графика – устанавливается в активное состояние для автоматического создания встроенной диаграммы на листе, содержащем частоты.
Как правило, гистограммы изображаются в виде смежных прямоугольных областей. Поэтому столбики гистограммы следует расширить до соприкосновения друг с другом. Для этого необходимо щелкнуть мышью на диаграмме, далее на панель инструментов Диаграмма , раскрыть список инструментов и выбрать элемент Ряд ‘Частота’ , после чего щелкнуть на кнопке Формат ряда . В появившемся одноименном диалоговом окне необходимо активизировать закладку Параметры и в поле Ширина зазора установить значение 0 ((Excel 2003):
В Excel 2007. 2010 встать на любой столбик гистограммы и правой кнопкой мыши выбрать
Формат ряда данных:
Для построения теоретической кривой нормального распределения по эмпирическим данным необходимо найти теоретические частоты.
В Excel для вычисления значений нормального распределения используются функция НОРМРАСП, которая вычисляет значения вероятности нормальной функции распределения для указанного среднего и стандартного отклонения.
Функция имеет параметры:
НОРМРАСП (х; среднее; стандартное_откл; интегральная) , где:
х — значения выборки, для которых строится распределение; среднее — среднее арифметическое выборки; стандартное_откл — стандартное отклонение распределения;
интегральный — логическое значение, определяющее форму функции. Если интегральная имеет значение ИСТИНА(1), то функция НОРМРАСП возвращает интегральную функцию распределения; если это аргумент имеет значение ЛОЖЬ (0), то вычисляет значение функция плотности распределения.
Для получения абсолютных значений плотностей распределения (теоретических частот) достаточно найденные значения вероятности умножить на величину интервала h и количество наблюдений N = 100 по каждой строке.
Для завершения выполнения задания необходимо внести полученные значения теоретических частот на рисунок с гистограммой, добавив ряд в закладке Исходные данные и выбрав тип диаграммы
– график ((Excel 2003):
В Excel 2007. 2010 находясь в обласи гистограммы по правой кнопке мыши выбрать Выбрать данные (или по одноименной кнопке на вкладке Конструктор ):
и в появившемся окне провести манипуляции с вводом нового ряда «Теоретические частоты»:
Частотный анализ по интервалам функцией ЧАСТОТА (FREQUENCY)
При анализе данных периодически возникает задача подсчитать количество значений, попадающих в заданные интервалы «от и до» (в статистике их называют «карманы»). Например, подсчитать количество звонков определенной длительности при разборе статистики по мобильной связи, чтобы понимать какой тариф для нас выгоднее:
Для решения подобной задачи можно воспользоваться функцией ЧАСТОТА (FREQUENCY) . Ее синтаксис прост:
=ЧАСТОТА( Данные ; Карманы )
- Карманы — диапазон с границами интервалов, попадание в которые нас интересует
- Данные — диапазон с исходными числовыми значениями, которые мы анализируем
Для использования функции ЧАСТОТА нужно:
- заранее подготовить ячейки с интересующими нас интервалами-карманами (желтые F2:F5 в нашем примере)
- выделить пустой диапазон ячеек (G2:G6) по размеру на одну ячейку больший, чем диапазон карманов (F2:F5)
- ввести функцию ЧАСТОТА и нажать в конце сочетание Ctrl+Shift+Enter, т.е. ввести ее как формулу массива
Кроме того, с помощью функции ЧАСТОТА можно легко подсчитывать количество уникальных чисел в наборе с помощью простой формулы массива:
Интервал карманов excel что это
Гистограмма — это столбчатая диаграмма, которая показывает частоту повторяемости значений.
Примечание: В этой статье рассматривается только создание гистограмм. Сведения о диаграммах Парето (отсортированных гистограммах) см. в статье Создание диаграммы Парето.
- Какие версии или продукты вы используете?
(Это типичный пример данных для гистограммы.)
Выберите Вставка > Вставить диаграмму статистики > Гистограмма.
Гистограмму также можно создать с помощью вкладки Все диаграммы в разделе Рекомендуемые диаграммы.
На вкладках Конструктор и Формат можно настроить внешний вид диаграммы.
Если они не отображаются, щелкните в любом месте гистограммы, чтобы добавить на ленту область Работа с диаграммами.
Правой кнопкой мыши щелкните горизонтальную ось диаграммы, выберите Формат оси, а затем щелкните Параметры оси.
Руководствуясь приведенной ниже таблицей, вы сможете выбрать параметры, которые нужно задать в области задач Формат оси.
По категориям
Выберите этот вариант, если категории (горизонтальная ось) текстовые, а не числовые. На гистограмме одинаковые категории будут сгруппированы, а значения на оси значений — просуммированы.
Совет: Чтобы подсчитать количество появлений текстовых строк, добавьте столбец и укажите в нем значение «1», а затем отобразите гистограмму и выберите параметр По категориям.
Автоматическая
Это вариант по умолчанию для гистограмм. Длина интервала вычисляется по формуле Скотта.
Длина интервала
Введите положительное десятичное число, задающее количество точек данных в каждом диапазоне.
Количество интервалов
Введите количество интервалов гистограммы (включая интервалы для значений, выходящих за верхнюю и нижнюю границы).
Выход за верхнюю границу интервала
Установите этот флажок, чтобы создать интервал для всех значений, превышающих значение в поле справа. Чтобы изменить его, введите в поле другое десятичное число.
Выход за нижнюю границу интервала
Установите этот флажок, чтобы создать интервал для всех значений, не превышающих значение в поле справа. Чтобы изменить его, введите в поле другое десятичное число.
Совет: Дополнительные сведения о гистограммах и их пользе для визуализации статистических данных см. в этой записи о гисторамме, диаграммах Парето и «ящик с усами» блога группы разработчиков Excel. Дополнительные сведения о других новых типах диаграмм приведены в этой записи блога.
Вариант «Автоматическая» (формула Скотта)
Формула Скотта минимизирует отклонение вариационного ряда на гистограмме по сравнению с набором данных, исходя из предположения о нормальном распределении данных.
Вариант «Выход за верхнюю границу интервала»
Вариант «Выход за нижнюю границу интервала»
Загрузите надстройку «Пакет анализа». Дополнительные сведения см. в статье Загрузка надстройки «Пакет анализа» в Excel.
В один столбец на листе введите исходные данные. При необходимости добавьте в первую ячейку подпись.
Используйте количественные числовые данные, например, количество элементов или результаты тестов. Мастер гистограмм не будет работать с такими количественными числовыми данными, как идентификационные номера, введенные в виде текста.
В следующий столбец введите интервалы в возрастающем порядке. При необходимости добавьте в первую ячейку подпись.
Используйте собственные интервалы, поскольку они могут лучше соответствовать целям вашего анализа. Если вы не введете их, мастер гистограмм создаст равномерно распределенные интервалы, используя минимальное и максимальное значение во введенном диапазоне в качестве начальной и конечной точек.
Откройте вкладку Данные и выберите команду Анализ данных.
Выберите пункт Гистограмма и нажмите кнопку OK.
В разделе Ввод выполните указанные ниже действия:
В поле Формировать список по диапазону введите ссылку на ячейку с диапазоном данных, который содержит исходные числа.
В поле Интервал карманов введите ссылку на ячейку с диапазоном, который содержит числа интервала.
Если на листе использовались подписи столбцов, можно включать их в ссылки на ячейки.
Совет: Вместо того чтобы вводить ссылки вручную, щелкните , чтобы временно свернуть диалоговое окно, чтобы выбрать диапазоны на этом сайте. При повторном нажатии этой кнопки диалоговое окно опять разворачивается.
Если подписи столбцов были включены в ссылки на ячейки, установите флажок Подписи.
В группе Параметры вывода выберите местоположение выходных данных.
Гистограмму можно расположить на том же листе, новом листе в текущей книге или в новой книге.
Установите один или несколько флажков:
<c0>Парето (отсортированная гистограмма)</c0>. Отображает частоту данных по убыванию.
<c0><c1>Суммарный процент</c1></c0>. Отображает суммарные проценты и добавляет в гистограмму строку суммарных процентов.
<c0><c1>Вывод диаграммы</c1></c0>. Отображает встроенную гистограмму.
Нажмите кнопку ОК.
Если вы хотите настроить гистограмму, вы можете изменить подписи текста и щелкнуть в любом месте гистограммы, чтобы использовать кнопки Элементы диаграммы, Стили диаграмм и Фильтр диаграммы справа от диаграммы.
(Это типичный пример данных для гистограммы.)
На вкладке Вставка нажмите кнопку Диаграмма.
В диалоговом окне Вставка диаграммы в разделе Все диаграммы выберите пункт Гистограмма, а затем нажмите кнопку ОК.
С помощью параметров на вкладках Конструктор и Формат настройте внешний вид диаграммы.
Если они не отображаются, щелкните в любом месте гистограммы, чтобы добавить на ленту область Работа с диаграммами.
Правой кнопкой мыши щелкните горизонтальную ось диаграммы, выберите Формат оси, а затем щелкните Параметры оси.
Руководствуясь приведенной ниже таблицей, вы сможете выбрать параметры, которые нужно задать в области задач Формат оси.
По категориям
Выберите этот вариант, если категории (горизонтальная ось) текстовые, а не числовые. На гистограмме одинаковые категории будут сгруппированы, а значения на оси значений — просуммированы.
Совет: Чтобы подсчитать количество появлений текстовых строк, добавьте столбец и укажите в нем значение «1», а затем отобразите гистограмму и выберите параметр По категориям.
Автоматическая
Это вариант по умолчанию для гистограмм.
Длина интервала
Введите положительное десятичное число, задающее количество точек данных в каждом диапазоне.
Количество интервалов
Введите количество интервалов гистограммы (включая интервалы для значений, выходящих за верхнюю и нижнюю границы).
Выход за верхнюю границу интервала
Установите этот флажок, чтобы создать интервал для всех значений, превышающих значение в поле справа. Чтобы изменить его, введите в поле другое десятичное число.
Выход за нижнюю границу интервала
Установите этот флажок, чтобы создать интервал для всех значений, не превышающих значение в поле справа. Чтобы изменить его, введите в поле другое десятичное число.
Чтобы создать гистограмму в Excel для Mac, выполните указанные Excel для Mac.
(Это типичный пример данных для гистограммы.)
На ленте на вкладке Вставка нажмите кнопку 
(статистический значок) и в области Гистограммавыберите гистограмма.
На вкладках Конструктор и Формат можно настроить внешний вид диаграммы.
Если они не отображаются, щелкните в любом месте гистограммы, чтобы добавить их на ленту.
Чтобы создать гистограмму в Excel 2011 для Mac, необходимо скачать сторонную надстройку. Дополнительные сведения см. в Excel 2011 для Mac.
В Excel Online вы можете просмотреть гистограмму (гистограмму с частотой), но не можете создать ее, так как для нее требуется надстройка Excel, не поддерживаемая в Excel в Интернете.
Если у вас есть Excel, вы можете нажать кнопку Изменить в Excel, чтобы открыть Excel на компьютере и создать гистограмму.
Коснитесь данных, чтобы выделить их.
Если вы на телефоне, коснитесь значка редактирования 
, чтобы отдемонстрировать ленту. и нажмите Главная.
Выберите элементы Вставка > Диаграммы > Гистограмма.
При необходимости вы можете настроить элементы диаграммы.
Примечание: Эта функция доступна только при наличии подписки на Microsoft 365. Если вы являетесь подписчиком Microsoft 365, проверьте, установлена ли у вас последняя версия Office.
Коснитесь данных, чтобы выделить их.
Если вы на телефоне, коснитесь значка «Правка» ленты, а затем нажмите Главная .
Выберите элементы Вставка > Диаграммы > Гистограмма.
Чтобы создать гистограмму в Excel, необходимо предоставить данные двух типов: данные, которые нужно проанализировать, и интервалы, которые представляют интервалы для измерения частоты. Данные необходимо расположить в двух столбцах на листе. Ниже приведены типы данных, которые должны содержаться в этих столбцах.
Введенные данные. Это данные, которые вы хотите проанализировать с помощью мастера гистограмм.
Числовые интервалы. Они представляют диапазоны, на основании которых мастер гистограмм проводит оценку введенных данных во время их анализа.
При использовании инструмента Гистограмма Excel количество точек данных в каждом из них. Точка данных включается в определенный интервал, если соответствующее значение больше нижней границы интервала данных и меньше верхней. Если диапазон диапазонов диапазонов Excel создается набор равномерно распределенных диапазонов между минимальным и максимальным значениями входных данных.
Результат анализа гистограммы отображается на новом листе (или в новой книге) и содержит таблицу и гистограмму, которая отражает данные этой таблицы.
Дополнительные сведения
Вы всегда можете задать вопрос эксперту в Excel Tech Community или получить поддержку в сообществах.
Сведение и группировка статистических данных
Цель работы – получить навыки группировки данных в MS Excel.
Задание – сгруппировать статистические данные с помощью надстройки Excel “Анализ данных”
Сведение и группировка статистической информации целесообразно проводить с помощью модуля “Гистограмма” пристройки “Анализ”.
Режим “Гистограмма” служит для вычисления частот попадания данных в указанные границы интервалов, и так же для построения гистограммы интервального вариационного ряда распределения.
В диалоговом окне этого режима
задаются следующие параметры:
1. Входной интервал – вводятся ссылки на ячейки, которые содержат данные для анализа.
2. “Интервал карманов” (необязательный параметр) – вводится ссылки на ячейки, которые содержат набор граничных значений, которые определяют интервалы (карманы). Эти значения должны быть введены в возрастающем порядке. В MS Excel вычисляется число попаданий данных в сформированные интервал, при этом границы интервалов являются строгими нижними границами и нестрогими верхними.
Если диапазон карманов не был введен, то набор интервалов, равномерно распределенных между минимальными и максимальными значениями данных, будет создан автоматически.
3. Метки устанавливаются в активное положение, если первый ряд или столбец во входном диапазоне содержит заголовки. Если заголовки отсутствуют, флажок нужно деактивировать. В этом случае будут автоматически созданы стандартные названия данных выходного диапазона.
4. Выходной интервал/Новый рабочий лист/Новая рабочая книга – активируется поле, в которое необходимо ввести ссылку на левую верхнюю ячейку выходного диапазона.
5. Парето (отсортированная гистограмма) – устанавливается в активное положение, если нужно представить данные в порядке убывания частоты. Если флажок снят, то данные в выходном диапазоне будут приведены в порядке следования интервалов.
6. Интегральный процент – устанавливается в активное положение для расчета выраженных в процентах накопленных частот (накопленных частностей) и включения в гистограмму графика кумуляты.
7. Вывод графика – устанавливается в активное положение для автоматического создания встроенной диаграммы на листе, который содержит выходной диапазон.