Как построить гистограмму частот в excel

Примечание: Если вы хорошо знакомы с диаграммами, вы также можете щелкнуть правой кнопкой мыши определенную область на диаграмме и выбрать параметр форматирования.

Чтобы создать гистограмму, сделайте следующее:

1. В сообщении электронной почты на вкладке Вставка выберите Добавить диаграмму.
2. В диалоговом окне Вставка диаграммы выберите Столбец, выберите нужный вариант гистограммы и нажмите кнопку ОК. В разделенном окне откроется лист Excel с примером данных.
3. Замените их собственными данными.

Примечание: Если на диаграмме не отображаются данные листа, перетащите вертикальные линии вниз до последней строки таблицы.

1. Выберите Изменить данные в Microsoft Excel на панели быстрого доступа. Лист откроется в Excel.
2. Сохраните лист.

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

Вы можете при необходимости отформатировать диаграмму. Ниже описаны некоторые варианты.

Примечание: Перед применением форматирования нужно щелкнуть диаграмму.

• Чтобы применить другой макет диаграммы, на вкладке Конструктор диаграммы выберите Макет диаграммы и выберите макет.
• Чтобы применить другой стиль диаграммы, на вкладке Конструктор диаграммы выберите Стили диаграммы и выберите стиль.
• Чтобы применить другой стиль фигуры, на вкладке Формат выберите Стили фигур и выберите стиль.

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

Примечание: Если вы хорошо знакомы с диаграммами, вы также можете щелкнуть правой кнопкой мыши определенную область на диаграмме и выбрать параметр форматирования.

Знаете ли вы?

Если у вас нет подписки на Microsoft 365 или последней версии Office, вы можете попробовать ее сейчас:

Построение диаграмм с несколькими рядами данных в EXCEL

Построение диаграмм с одним рядом данных рассмотрено в статье Основы построения диаграмм в MS EXCEL . Начинающим пользователям также целесообразно перед прочтением ознакомиться со статьей Основные типы диаграмм .

ГИСТОГРАММА

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

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

MS EXCEL построит оба ряда с использованием только основных осей (чтобы в этом убедиться, дважды кликните сначала по столбцу одного ряда, затем по одному из столбцов по другого. В окне Формат ряда данных на вкладке Параметры ряда будет указано по какой оси отложены значения ряда). Т.к. значения у обоих рядов близки, то такое решение нам подходит.

Для рядов, у которых значения существенно различаются (на порядок и больше) один из рядов нужно строить с использованием Вспомогательной вертикальной оси .

Для этого достаточно выбрать для одного из рядов построение на вспомогательной оси (дважды кликнув на один из столбцов), а затем настроить ширину столбцов (боковой зазор), чтобы отображались оба ряда.

Если не настроить ширину столбцов, то эту диаграмму можно спутать с диаграммой Гистограмма с пополнением (столбцы, относящие к одной категории «ставятся» друг на друга).

Другой возможностью отображения на диаграмме двух рядов со значительно различающимися значениями, является масштабирование самих значений в таблице-источнике.

Горизонтальную вспомогательную ось можно расположить даже вверху. При этом столбцы разных рядов будут оригинально пересекаться.

Теперь изменим подписи по горизонтальной оси (категории).

В окне Выбор источника данных видно, что для обоих рядов подписи горизонтальной оси одинаковы, т.к. категории одинаковы для обоих рядов (столбец Месяц).

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

При желании это также можно изменить. В окне Выбор источника данных выделите ряд Прибыль, нажмите кнопку Изменить расположенную справа, удалите ссылку на ячейки. Теперь у ряда Прибыль вместо названия категорий будут просто порядковые числа 1, 2, 3,… Однако, они не будут отображаться на диаграмме, т.к. отображается пока только Основная горизонтальная ось .

Теперь в меню Оси (вкладка Макет , группа Оси ) выберите Вспомогательная горизонтальная ось и установите ее Слева направо . В окне формата Вспомогательной вертикальной оси измените точку пересечения оси (установите Автовыбор ). Получим вот такую диаграмму.

Хотя техническая возможность отображения 2-х различных категорий существует, конечно, таких диаграмм нужно избегать, т.к. их сложно воспринимать. Категории должны быть одинаковыми для всех рядов на диаграмме. Естественно, такой трюк сработает только для двух групп рядов, т.к. имеется всего 2 типа оси: основная и вспомогательная.

ГРАФИК

Диаграмма График во многом аналогична Гистограмме с группировкой: к ней применимы те же идеи по отображению двух рядов со значительно отличающимися значениями, что и к Гистограмме.

После размещения рядов на разных осях получим вот такую диаграмму (линии вертикальных осей выделены цветами, соответствующими цветам рядов).

Примечание . Если провести горизонтальные линии сетки к вспомогательной оси, то они, естественно, могут не совпасть с линиями к основной оси, т.к. масштабы осей (величины основных делений вертикальных осей) могут не совпадать. Это может «перегрузить» диаграмму.

Вообще, к использованию вспомогательных вертикальных осей, а тем более вспомогательных горизонтальных осей для Гистограммы и Графика нужно подходить обдуманно: ведь диаграмма должна «читаться» — быть понятной без дополнительных комментариев.

ТОЧЕЧНАЯ

Визуально Точечная диаграмма похожа на диаграмму типа График (если конечно у Точечной диаграммы точки соединены линиями).

Различие состоит в том, что для построения Графика в качестве координаты Х всегда используется последовательность 1, 2, 3, … (с подписями — категориями), а для Точечной значения по горизонтальной оси м.б. любыми числами (см. статью График vs Точечная ).

Примечание . Если для построения Точечной диаграммы не указана ссылка на значения Х (или ссылка указывает на текстовые значения), то в качестве координат по Х будет использована та же последовательность 1, 2, 3, …, что и для Графика.

Исходя из вышесказанного, при построении диаграммы Точечная целесообразно указывать числовые значения по Х. В противном случае нужно просто использовать График, т.к. для него можно задавать любые подписи по Х (в том числе и текстовые), что для Точечной диаграммы сделать нельзя (только числа).

Теперь о совмещении разных типов диаграмм с Точечной. Если для Точечной диаграммы не используется координата Х, то на диаграмме она выглядит как График.

Подписи по горизонтальной оси берутся от Графика. В окне Выбор источника данных видно, что для ряда отображаемого Точечной диаграммой подписи горизонтальной оси одинаковы изменить/ удалить нельзя.

Кроме того, График может быть только на основной оси и поменять это нельзя.

Если для Точечной диаграммы используется координата Х и оба ряда отложены по одной (основной) оси, то получается не очень красивая диаграмма.

Это связано с тем, что при совмещении с Точечной диаграммой (на одной оси и когда у Точечной указаны значения по Х), диаграмма типа График становится как бы главной:

• на горизонтальной оси отображаются подписи только для Графика;
• вертикальная сетка не отображается для отрицательных значений Х (т.к. График строится только для Х=1, 2, 3, …);
• у Графика невозможно изменить Ось с Основной на Вспомогательную (у Точечной можно).

Если Точечную построить на вспомогательной оси, то диаграмма изменится.

Подписи для Точечной (значения по Х) теперь отображаются сверху.

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

Теперь рассмотрим построение 2-х рядов данных, которые используют диаграмму Точечная.

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

Примечание . Фактически на диаграмме 4 ряда данных: точка центра представляет отдельный ряд.

Дважды кликнем по бордовому эллипсу и выберем построение ряда на вспомогательной оси (сделаем то же и центра эллипса).

Теперь координаты Y для бордового эллипса откладываются по правой вертикальной оси (можно ее для наглядности выделить также бордовым цветом).

Добавим Вспомогательную горизонтальную ось (в меню Оси (вкладка Макет , группа Оси ) выберите Вспомогательная горизонтальная ось и установите ее По умолчанию ).

Наверное, для диаграммы типа Точечная использование вспомогательных осей наиболее оправдано – это позволяет отображать на одной диаграмме различные по масштабу кривые.

КРУГОВАЯ

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

Совмещение разных типов диаграмм

Про совмещение Графика и Точечной диаграммы рассказано выше (см. раздел Точечная).

Совмещением Линейчатой и Гистограммы не стоит заниматься, т.к. выглядит это достаточно странно, почти также странно, как и совмещение Гистограммы с Круговой (см. также статью Основные типы диаграмм, раздел Круговая ).

Совет . Также можно посмотреть статью Совмещаем разные типы диаграмм .

Оригинальностью совмещения могут также похвастаться Гистограмма и Нормированная линейчатая с накоплением .

Наверное, единственными типами диаграмм, которые стоит совмещать, являются График и Гистограмма (категории должны быть одинаковыми для обоих рядов).

Для такой диаграммы постройте сначала Гистограмму с группировкой с двумя рядами (см. раздел Гистограмма в начале этой статьи). Затем выделите нужный ряд и нажмите кнопку Изменить тип диаграммы (вкладка Конструктор ). Выбрав График или График с Маркерами нажмите ОК.

Если масштабы значений у рядов существенно отличаются, то можно настроить вспомогательную вертикальную ось (см. раздел Гистограмма).

3. Построение гистограммы

В главном меню Excel выбрать Данные → Анализ данных → Гистограмма → ОК.

Далее необходимо заполнить поля ввода в диалоговом окне Гистограмма.

Входной интервал: 100 случайных чисел в ячейках $A$1: $A$100;

Интервал карманов: не заполнять;

Выходной интервал: адрес ячейки, с которой начинается вывод результатов процедуры Гистограмма;

Вывод графика – поставьте галочку.

Если поле ввода Интервал карманов не заполняется, то процедура вычисляет число интервалов группировки k и границы интервалов автоматически.

Рис. 6. Диалоговое окно Гистограмма.

В результате выполнения процедуры Гистограмма появляется таблица, содержащая границы x_i интервалов группировки (столбец – Карман) и частоту попадания случайных величин выборки m_i в i–ый интервал (столбец – Частота).

Справа от таблицы – график гистограммы.

Рис. 7. Фрагмент листа Excel с результатами процедуры Гистограмма

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

Далее, для того чтобы убедиться в правильности выбранной гипотезы (по крайней мере визуально) надо, первое – построить график гипотетического нормального закона распределения, выбрав в качестве параметров (математического ожидания и среднего квадратического отклонении) их оценки (среднее и стандартное отклонение), и совместить график гипотетического распределения с графиком гистограммы.

И, второе – используя критерий согласия Пирсона установить справедливость выбранной гипотезы.

4. Построение теоретического закона распределения

Для построения теоретического закона распределения совместно с гистограммой и проверки согласия по критерию хи-квадрат Пирсона надо заполнить таблицу, знакомую по лекции (см. ниже по тексту, таблица №1). Для построения этой таблицы надо воспользоваться таблицей карман – частота процедуры Гистограмма.

x_i – границы интервалов группировки (карманы – получены как результат выполнения процедуры Гистограмма);

m_i – количество элементов выборки, попавших в i–ый интервал (частота – получена в результате процедуры Гистограмма);

теоретическая частота

статистика U

Для построения этой таблицы в Excel к столбцам карман – частота процедуры Гистограмма надо добавить столбцы n∙p_i и

Теоретическая вероятность p_i попадания элементов выборки в i—ый интервал группировки для принятой гипотезы о нормальном распределении генеральной совокупности равна p_i = P(x_i-1 < X < x_i) = F(x_i) – F(x_i-1).

n∙p_i – теоретическая (ожидаемая) частота попадания элементов выборки в i–ый интервал группировки для принятой гипотезы о нормальном распределении генеральной совокупности.

В Excel эту величину можно вычислить, воспользовавшись функцией НОРМРАСП.

n∙p_i = (НОРМРАСП(x_i; среднее; стандартное_откл; 1) –

– НОРМРАСП(x_i-1; среднее; стандартное_откл; 1)) * n.

–статистика, являющаяся мерой расхождения между значениями эмпирической и теоретической плотности распределения;

4.1. Найдите сумму элементов выборки, попавших в карманы (n = 100), для контроля (ячейка D29, рис. 8).

Столбцу E18: E28 присвойте имя n∙p_i , поместив его в ячейку E17.

В ячейку E18 внесите формулу для вычисления значения функции нормального распределения F(x₁ = 10,544) = P(– ∞ < X ≤ x₁), умноженную на число наблюдений n. В рассматриваемом примере n =100. В ячейку E18 будет получено теоретическое (ожидаемое) число значений случайной величины, попавших в интервал ,n∙p_i = F(x₁)∙100

Рис. 8. В ячейке E18 результаты вычислений функции НОРМРАСП(C$18$;D$3$;D$7$;1)*100

Функцию НОРМРАСП вызывается следующим образом. В главном меню Excel выбирается закладка Формулы → Вставить функцию → в диалоговом окне Мастер функций – шаг 1 из 2 в категории Статистические → НОРМРАСП. ОК.

Рис. 9. Окно Мастер функций для выбора функции НОРМРАСП из категории Статистические.

В раскрывшемся окне Аргументы функции НОРМРАСП заполните поля ввода как показано далее на рис. 10.

Рис. 10. Окно ввода параметров для получения функции нормального распределения

В поле X введите адрес ячейки, в которой находится граница первого интервала группировки C18 (верхняя ячейка столбца Карманы).

В поле Среднее введите адрес ячейки, в которой находится среднее значение выборки, полученное при выполнении процедуры Описательная статистика – D3.

В поле Стандартное_откл введите адрес ячейки, в которой находится значение стандартного отклонения выборки, полученное при выполнении процедуры Описательная статистика – D7.

В поле Интегральная введите единица 1. Единица в поле Интегральная означает вычисление функции распределения F(x). ОК.

В ячейку E19 поместите формулу для вычисления теоретического (гипотетического) числа случайных величин, попавших в интервал :

n∙p₂ = n ∙ [F(x₂) – F(x₁)] = n ∙ [P(x₁ < X ≤ x₂)] = n ∙ [P(10,544 < X ≤ 11,5777)],

где p₂ = F(x₂) – F(x₁) = P(x₁ < X ≤ x₂) = P(10,544 < X ≤ 11,5777) - теоретическая вероятность попадания нормально распределенных случайных величин в промежуток .

В Excel в строку формул необходимо поместить формулу:

Рис. 11. В ячейке E19 показаны результаты вычислений функции

=(НОРМРАСП(C19;$D$3;$D$7;1) – НОРМРАСП(C18;$D$3;$D$7;1)) *100

Заполните диапазон ячеек Е20:Е27 результатами вычисления этой формулы, используя маркер заполнения.

Рис. 12. Столбец E19;E27 с результатами вычисления функции

n∙p_i = (НОРМРАСП(C32;$D$3;$D$7;1) – НОРМРАСП(C31;$D$3;$D$7;1)) *100

В ячейку E28 поместите формулу для вычисления теоретического (гипотетического) числа случайных величин, попавших в промежуток (x₁₀; ∞ ):

P(x₁₀ < x < ∞) = 1 – P(– ∞ < x ≤ x₁₀) = 1 – F(x₁₀) – вероятность попадания нормально распределенных случайных величин в промежуток (x₁₀; ∞).

В Excel в строку формул необходимо поместить формулу:

Для этого сначала необходимо вызвать функции НОРМРАСП и заполнить поля ввода

Рис. 13. Диалоговое окно функции НОРМРАСП с заполненными полями ввода

Рис. 14. Столбец n∙p_i (E18;E28) содержит результаты вычисления теоретических значений числа случайных величин попавших в каждый частичный интервал (карман) n∙p_i

Для проверки правильности вычислений просуммируйте числа в ячейках столбца E18:E28.

В ячейке Е29 показана сумма содержимого ячеек Е31:Е40. Она должна быть равна n = 100.

Рис. 15. Таблицы распределения эмпирических частот m_i – столбец Частота и теоретических частот np_i – столбец n∙p_i

4.2. В графике Гистограмма частот добавьте кривую нормального распределения, как это вы умеете.

Рис. 16. Графики гистограммы эмпирических и теоретических частот, позволяющие по виду графиков выбрать в качестве гипотезы H₀ нормальное распределение.

Для того чтобы сохранить графики гистограммы эмпирических и теоретических частот (рис. 16) необходимо скопировать таблицу на рис. 15 Карман – Частота – n∙p_i в другое место таблицы.

4.3. Скопируйте таблицу Карман – Частота – n∙p_i в свободные ячейки листа Excel, для чего, верхний левый угол копии разместите в ячейке C30, как показано далее на рис. 8.

Рис. 17. Фрагмент листа Excel с копией таблицы распределения эмпирических и теоретических частот по карманам

KT Богомолов / МУ / ЗАДАНИЕ_1_СТАТИСТИКА / Дополнительные материалы / Построение гистограмм в Excel_2014

Построение гистограмм в Microsoft Excel Перед построением гистограммы выполняется группировка данных по близким признакам. При группировании по количественному признаку все множество значений признака делится на интервалы. Для определения оптимального количества интервалов может быть использована формула Стерджесса: 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 находясь в обласи гистограммы по правой кнопке мыши выбрать Выбрать данные (или по одноименной кнопке на вкладке Конструктор ): и в появившемся окне провести манипуляции с вводом нового ряда «Теоретические частоты»: