No Image

Как построить гистограмму распределения в excel

СОДЕРЖАНИЕ
12 просмотров
16 декабря 2019

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

Примечание: В этой статье рассматривается только создание гистограмм. Сведения о диаграммах Парето (отсортированных гистограммах) см. в статье Создание диаграммы Парето.

  • Какие версии или продукты вы используете?
  • Excel 2016 и более поздние версии
  • Excel 2007 – 2013
  • Outlook, PowerPoint, Word 2016

(Это типичный пример данных для гистограммы.)

Выберите Вставка > Вставить диаграмму статистики > Гистограмма.

Гистограмму также можно создать с помощью вкладки Все диаграммы в разделе Рекомендуемые диаграммы.

На вкладках Конструктор и Формат можно настроить внешний вид диаграммы.

Если они не отображаются, щелкните в любом месте гистограммы, чтобы добавить на ленту область Работа с диаграммами.

Правой кнопкой мыши щелкните горизонтальную ось диаграммы, выберите Формат оси, а затем щелкните Параметры оси.

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

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

Совет: Чтобы подсчитать количество появлений текстовых строк, добавьте столбец и укажите в нем значение "1", а затем отобразите гистограмму и выберите параметр По категориям.

Это вариант по умолчанию для гистограмм. Длина интервала вычисляется по формуле Скотта.

Введите положительное десятичное число, задающее количество точек данных в каждом диапазоне.

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

Выход за верхнюю границу интервала

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

Выход за нижнюю границу интервала

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

Совет: Дополнительные сведения о гистограммах и их пользе для визуализации статистических данных см. в этой записи о гисторамме, диаграммах Парето и "ящик с усами" блога группы разработчиков Excel. Дополнительные сведения о других новых типах диаграмм приведены в этой записи блога.

Вариант "Автоматическая" (формула Скотта)

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

Вариант "Выход за верхнюю границу интервала"

Вариант "Выход за нижнюю границу интервала"

Загрузите надстройку "Пакет анализа". Дополнительные сведения см. в статье Загрузка надстройки "Пакет анализа" в Excel.

В один столбец на листе введите исходные данные. При необходимости добавьте в первую ячейку подпись.

Используйте количественные числовые данные, например, количество элементов или результаты тестов. Мастер гистограмм не будет работать с такими количественными числовыми данными, как идентификационные номера, введенные в виде текста.

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

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

Откройте вкладку Данные и выберите команду Анализ данных.

Выберите пункт Гистограмма и нажмите кнопку OK.

В разделе Ввод выполните указанные ниже действия:

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

В поле Интервал карманов введите ссылку на ячейку с диапазоном, который содержит числа интервала.

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

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

Если подписи столбцов были включены в ссылки на ячейки, установите флажок Подписи.

В группе Параметры вывода выберите местоположение выходных данных.

Гистограмму можно расположить на том же листе, новом листе в текущей книге или в новой книге.

Установите один или несколько флажков:

Парето (отсортированная гистограмма) . Отображает частоту данных по убыванию.

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

Вывод диаграммы . Отображает встроенную гистограмму.

Нажмите кнопку ОК.

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

(Это типичный пример данных для гистограммы.)

На вкладке Вставка нажмите кнопку Диаграмма.

В диалоговом окне Вставка диаграммы в разделе Все диаграммы выберите пункт Гистограмма, а затем нажмите кнопку ОК.

С помощью параметров на вкладках Конструктор и Формат настройте внешний вид диаграммы.

Читайте также:  Как найти эквивалентное сопротивление цепи формула

Если они не отображаются, щелкните в любом месте гистограммы, чтобы добавить на ленту область Работа с диаграммами.

Правой кнопкой мыши щелкните горизонтальную ось диаграммы, выберите Формат оси, а затем щелкните Параметры оси.

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

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

Совет: Чтобы подсчитать количество появлений текстовых строк, добавьте столбец и укажите в нем значение "1", а затем отобразите гистограмму и выберите параметр По категориям.

Это вариант по умолчанию для гистограмм.

Введите положительное десятичное число, задающее количество точек данных в каждом диапазоне.

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

Выход за верхнюю границу интервала

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

Выход за нижнюю границу интервала

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

Совет: Дополнительные сведения о гистограммах и их пользе для визуализации статистических данных см. в этой записи о гисторамме, диаграммах Парето и "ящик с усами" блога группы разработчиков Excel. Дополнительные сведения о других новых типах диаграмм приведены в этой записи блога.

Чтобы создать гистограмму в Excel для Mac, выполните указанные ниже действия.

(Это типичный пример данных для гистограммы.)

На ленте откройте вкладку Вставка, щелкните значок ( Статистические) и в разделе Гистограмма выберите Гистограмма.

На вкладках Конструктор и Формат можно настроить внешний вид диаграммы.

Если они не отображаются, щелкните в любом месте гистограммы, чтобы добавить их на ленту.

Чтобы создать гистограмму в Excel 2011 для Mac, вам нужно скачать надстройку стороннего поставщика. Дополнительные сведения: я не могу найти пакет анализа в Excel 2011 для Mac .

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

Если у вас есть классическое приложение Excel, вы можете использовать кнопку изменить в Excel , чтобы открыть Excel на рабочем столе и создать гистограмму.

Коснитесь данных, чтобы выделить их.

Если вы находитесь на телефоне, коснитесь значка редактирования , чтобы отобразить ленту. и выберите Главная.

Выберите элементы Вставка > Диаграммы > Гистограмма.

При необходимости вы можете настроить элементы диаграммы.

Примечание: Эта функция доступна только при наличии подписки на Office 365. Если у вас есть подписка на Office 365, убедитесь, что у вас установлена последняя версия Office.

Коснитесь данных, чтобы выделить их.

Если вы используете телефон, коснитесь значка правки , чтобы отобразить ленту, а затем выберите вкладку Главная.

Выберите элементы Вставка > Диаграммы > Гистограмма.

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

Введенные данные. Это данные, которые вы хотите проанализировать с помощью мастера гистограмм.

Числовые интервалы. Они представляют диапазоны, на основании которых мастер гистограмм проводит оценку введенных данных во время их анализа.

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

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

Дополнительные сведения

Вы всегда можете задать вопрос специалисту Excel Tech Community, попросить помощи в сообществе Answers community, а также предложить новую функцию или улучшение на веб-сайте Excel User Voice.

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

Гистограмма распределения – это инструмент, позволяющий визуально оценить величину и характер разброса данных. Создадим гистограмму для непрерывной случайной величины с помощью встроенных средств MS EXCEL из надстройки Пакет анализа и в ручную с помощью функции ЧАСТОТА() и диаграммы.

Гистограмма (frequency histogram) – это столбиковая диаграмма MS EXCEL, в каждый столбик представляет собой интервал значений (корзину, карман, class interval, bin, cell), а его высота пропорциональна количеству значений в ней (частоте наблюдений).

Читайте также:  Как заставить жену думать о тебе

Гистограмма поможет визуально оценить распределение набора данных, если:

  • в наборе данных как минимум 50 значений;
  • ширина интервалов одинакова.

Построим гистограмму для набора данных, в котором содержатся значения непрерывной случайной величины. Набор данных (50 значений), а также рассмотренные примеры, можно взять на листе Гистограмма AT в файле примера. Данные содержатся в диапазоне А8:А57.

Примечание: Для удобства написания формул для диапазона А8:А57 создан Именованный диапазон Исходные_данные.

Построение гистограммы с помощью надстройки Пакет анализа

Вызвав диалоговое окно надстройки Пакет анализа, выберите пункт Гистограмма и нажмите ОК.

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

  • автоматически рассчитаны интервалы значений (карманы);
  • подсчитано количество значений из указанного массива данных, попадающих в каждый интервал (построена таблица частот);
  • если поставлена галочка напротив пункта Вывод графика, то вместе с таблицей частот будет выведена гистограмма.


Перед тем как анализировать полученный результат – отсортируйте исходный массив данных.

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

Второй интервал (отмечен на картинке серым) включает значения больше 113 и меньше или равные 216,428571428571. Можно проверить, что таких значений 11. Предпоследний интервал, от 630,142857142857 (не включая) до 733,571428571429 (включая) содержит 0 значений, т.к. в этом диапазоне значений нет. Последний интервал (со странным названием Еще) содержит значения больше 733,571428571429 (не включая). Таких значений всего одно – максимальное значение в массиве (837).

Размеры карманов одинаковы и равны 103,428571428571. Это значение можно получить так:
=(МАКС(Исходные_данные)-МИН(Исходные_данные))/7
где Исходные_данные – именованный диапазон, содержащий наши данные.

Почему 7? Дело в том, что количество интервалов гистограммы (карманов) зависит от количества данных и для его определения часто используется формула √n, где n – это количество данных в выборке. В нашем случае √n=√50=7,07 (всего 7 полноценных карманов, т.к. первый карман включает только значения равные минимальному).

Примечание: Похоже, что инструмент Гистограмма для подсчета общего количества интервалов (с учетом первого) использует формулу
=ЦЕЛОЕ(КОРЕНЬ(СЧЕТ(Исходные_данные)))+1

Попробуйте, например, сравнить количество интервалов для диапазонов длиной 35 и 36 значений – оно будет отличаться на 1, а у 36 и 48 – будет одинаковым, т.к. функция ЦЕЛОЕ() округляет до ближайшего меньшего целого (ЦЕЛОЕ(КОРЕНЬ(35))=5 , а ЦЕЛОЕ(КОРЕНЬ(36))=6) .

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

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

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

Для нашего набора данных установим размер кармана равным 100 и первый карман возьмем равным 150.

В результате получим практически такую же по форме гистограмму, что и раньше, но с более красивыми границами интервалов.

Как видно из рисунков выше, надстройка Пакет анализа не осуществляет никакого дополнительного форматирования диаграммы. Соответственно, вид такой гистограммы оставляет желать лучшего (столбцы диаграммы обычно располагают вплотную для непрерывных величин, кроме того подписи интервалов не информативны). О том, как придать диаграмме более презентабельный вид, покажем в следующем разделе при построении гистограммы с помощью функции ЧАСТОТА() без использовании надстройки Пакет анализа.

Построение гистограммы распределения без использования надстройки Пакет анализа

Порядок действий при построении гистограммы в этом случае следующий:

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

СОВЕТ: Часто рекомендуют, чтобы границы интервала были на один порядок точнее самих данных и оканчивались на 5. Например, если данные в массиве определены с точностью до десятых: 1,2; 2,3; 5,0; 6,1; 2,1, …, то границы интервалов должны быть округлены до сотых: 1,25-1,35; 1,35-1,45; …
Для небольших наборов данных вид гистограммы сильно зависит количества интервалов и их ширины. Это приводит к тому, что сам метод гистограмм, как инструмент описательной статистики, может быть применен только для наборов данных состоящих, как минимум, из 50, а лучше из 100 значений.

Читайте также:  Как поставить таймер выключения на комп

В наших расчетах для определения количества интервалов мы будем пользоваться формулой =ЦЕЛОЕ(КОРЕНЬ(n))+1 .

Примечание: Кроме использованного выше правила (число карманов = √n), используется ряд других эмпирических правил, например, правило Стёрджеса (Sturges): число карманов =1+log2(n). Это обусловлено тем, что например, для n=5000, количество интервалов по формуле √n будет равно 70, а правило Стёрджеса рекомендует более приемлемое количество – 13.

Расчет ширины интервала и таблица интервалов приведены в файле примера на листе Гистограмма . Для вычисления количества значений, попадающих в каждый интервал, использована формула массива на основе функции ЧАСТОТА() . О вводе этой функции см. статью Функция ЧАСТОТА() – Подсчет ЧИСЛОвых значений в MS EXCEL.

В MS EXCEL имеется диаграмма типа Гистограмма с группировкой, которая обычно используется для построения Гистограмм распределения.

В итоге можно добиться вот такого результата.

Примечание: О построении и настройке макета диаграмм см. статью Основы построения диаграмм в MS EXCEL.

Одной из разновидностей гистограмм является график накопленной частоты (cumulative frequency plot).

На этом графике каждый столбец представляет собой число значений исходного массива, меньших или равных правой границе соответствующего интервала. Это очень удобно, т.к., например, из графика сразу видно, что 90% значений (45 из 50) меньше чем 495.

СОВЕТ : О построении двумерной гистограммы см. статью Двумерная гистограмма в MS EXCEL.

Примечание: Альтернативой графику накопленной частоты может служить Кривая процентилей, которая рассмотрена в статье про Процентили.

Примечание: Когда количество значений в выборке недостаточно для построения полноценной гистограммы может быть полезна Блочная диаграмма (иногда она называется Диаграмма размаха или Ящик с усами).

Построим диаграмму распределения в Excel. А также рассмотрим подробнее функции круговых диаграмм, их создание.

Как построить диаграмму распределения в Excel

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

Внешне столбчатая диаграмма похожа на график нормального распределения. Построим столбчатую диаграмму распределения осадков в Excel и рассмотрим 2 способа ее построения.

Имеются следующие данные о количестве выпавших осадков:

Первый способ. Открываем меню инструмента «Анализ данных» на вкладке «Данные» (если у Вас не подключен данный аналитический инструмент, тогда читайте как его подключить в настройках Excel):

Задаем входной интервал (столбец с числовыми значениями). Поле «Интервалы карманов» оставляем пустым: Excel сгенерирует автоматически. Ставим птичку около записи «Вывод графика»:

После нажатия ОК получаем такой график с таблицей:

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

Теперь необходимо сделать так, чтобы по вертикальной оси отображались относительные частоты.

Найдем сумму всех абсолютных частот (с помощью функции СУММ). Сделаем дополнительный столбец «Относительная частота». В первую ячейку введем формулу:

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

Чтобы найти интервал карманов, нужно разность максимального и минимального значений массива разделить на количество интервалов. Получим «ширину кармана».

Представим интервалы карманов в виде столбца значений. Сначала ширину кармана прибавляем к минимальному значению массива данных. В следующей ячейке – к полученной сумме. И так далее, пока не дойдем до максимального значения.

Для определения частоты делаем столбец рядом с интервалами карманов. Вводим функцию массива:

Вычислим относительные частоты (как в предыдущем способе).

Построим столбчатую диаграмму распределения осадков в Excel с помощью стандартного инструмента «Диаграммы».

Частота распределения заданных значений:

Круговые диаграммы для иллюстрации распределения

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

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

  • имеется только один ряд данных;
  • все значения положительные;
  • практически все значения выше нуля;
  • не более семи категорий;
  • каждая категория соответствует сегменту круга.

На основании имеющихся данных о количестве осадков построим круговую диаграмму.

Доля «каждого месяца» в общем количестве осадков за год:

Круговая диаграмма распределения осадков по сезонам года лучше смотрится, если данных меньше. Найдем среднее количество осадков в каждом сезоне, используя функцию СРЗНАЧ. На основании полученных данных построим диаграмму:

Получили количество выпавших осадков в процентном выражении по сезонам.

Комментировать
12 просмотров
Комментариев нет, будьте первым кто его оставит

Это интересно
No Image Компьютеры
0 комментариев
No Image Компьютеры
0 комментариев
No Image Компьютеры
0 комментариев
Adblock detector