Как построить гистограмму по данным таблицы в excel

Метод 2: пользуемся надстройкой “Пакета “анализа”

Сначала вам нужно включить этот пакет.

Перейдите в меню «Файл”.
В списке слева внизу нажмите на пункт «Настройки”.
В открывшемся окне выберите «Плагины». В правой части окна для параметра «Управление» установите значение «Надстройки Excel» и нажмите кнопку «Перейти”.
В появившемся окне поставьте галочку напротив плагина «Пакет аналитики» и нажмите ОК.
Перейдите на вкладку «Данные» в группе инструментов «Анализ», нажмите кнопку «Анализ данных”.
Откроется окно, в котором нам нужен пункт «Гистограмма», выделив его, нажимаем ОК.
Перед нами появится окно, в котором нам необходимо настроить параметры гистограммы:
- в поле «Входной диапазон» укажите диапазон ячеек, для которого вы хотите построить гистограмму. Вы можете сделать это вручную, введя адреса с помощью клавиш клавиатуры. Также мы можем выделить нужный диапазон ячеек в самой таблице, предварительно поставив курсор в поле для ввода информации.
- внизу окна установите флажок рядом с параметром «Графический результат”.
- в группе параметров вывода можно указать, куда вставить созданную гистограмму. По умолчанию на новом листе.
- когда будете готовы, нажмите кнопку ОК.
Гистограмма сформирована и добавлена на отдельный лист, как мы и хотели.

Как построить диаграмму по таблице в Excel?

Изменение оформления диаграммы (гистограммы)

Гистограмма может быть стандартной, как на картинке, т.е

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

После этого появится меню для работы с диаграммами, в котором можно задать все параметры, начиная от наличия/отсутствия подписей осей и заканчивая цветом графических элементов самой диаграммы. На вкладке «Конструктор» можно изменить цвет диаграммы, как показано на экране.

Можно изменить макет диаграммы, в зависимости от того, как вы хотите расположить свои данные на диаграмме.

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

Для примера, вот, как это будет выглядеть.

В строке «Название диаграммы» можно указать название таблицы значений, например, или любое другое на Ваше усмотрение.На вкладке «Макет» можно изменять/удалять размещение названия диаграммы, осей, легенды (слово «ряд 1» на предыдущих примерах), подписей и таблицы данных, сетки.На вкладке «Формат» можно изменять оформление поля диаграммы и, в целом, вносить те же изменения, что и в стилях WordArt. Так, например, на картинке показан выбор фона из предложенных вариантов оформления, однако этим не ограничивается, т.к. Вы всегда можете выбрать свой цвет контура или фона из палитры, а также сделать необходимый угол или объем, настроив все это самостоятельно. Для этого нужно выбрать необходимый цвет, кликнув на «Заливка фигуры», «Контур фигуры». Для изменения объема или угла поворота диаграммы – «Эффекты фигур».

Цвет и стиль текста меняется в меню «Стили WordArt».

Кольцевая диаграмма

Как отобразить диаграмму в процентах

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

И теперь все данные отображаются в проценте соотношении.

Как видно из инструкции, сделать профессиональную диаграмму в Excel не сложно, если в первую очередь разобраться с базовыми настройками простых элементов. Программа Эксель предлагает разнообразные инструменты для систематизации данных, а как ими воспользуется пользователь – все зависит от воображения и потребностей

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

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

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

СОВЕТ: Часто рекомендуют, чтобы границы интервала были на один порядок точнее самих данных и оканчивались на 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

Типы диаграмм

График

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

Гистограмма

Подходит для сравнения нескольких объектов и изменения их отношений с течением времени. Пример сравнения показателя эффективности двух отделов поквартально:

Круговая

Цель сравнения пропорций предметов. Не могу показать динамику. Пример доли продаж каждой категории товаров от общего объема продаж:

Диаграмма с областями

Подходит для отображения динамики различий между объектами во времени

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

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

Так как нам важно видеть именно потенциал, эта строка отображается первой. Из диаграммы ниже видно, что с 11 до 16 отдел не справляется с потоком клиентов

Точечная

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

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

Поверхность

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

Биржевая

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

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

Лепестковая

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

На приведенной ниже диаграмме показано сравнение 3 организаций в 4 областях: доступность; Ценовая политика; Качество продукта; Клиентоориентированность. Видно, что компания X лидирует в первом и последнем направлении, компания Y – в качестве продукции, а компания Z дает лучшие цены.

Также можно сказать, что компания X является лидером, поскольку площадь ее фигуры на диаграмме наибольшая.

Каково использование гистограммы?

Вот некоторые из основных применений гистограмм:

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

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

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

Как объединить гистограмму и график в Экселе

Чтобы совместить разные виды объектов, необходимо использовать нестандартные наборы диаграмм.

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

Теперь выделите все строки, перейдите на вкладку «Вставка», нажмите на иконку «Гистограммы» и выберите последний вариант.

В появившемся окне перейдите на «Комбинированная».

После этого вы сможете указать тип диаграммы для каждого ряда.

Необходимо везде указать «Гистограмма с группировкой», а для ряда «Всего» – тип «Линия». При этом нужно поставить галочку в графе «Вспомогательная ось».

После нажатия на кнопку «OK», мы получим новую диаграмму, которая сочетает в себе гистограмму и график.

Дополнительные параметры форматирования гистограммы

Вы можете внести дальнейшие изменения в форматирование своей гистограммы, щёлкнув правой кнопкой мыши по диаграмме и выбрав опцию «Форматировать область диаграммы».

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

Вы также можете изменить вид и способ показа текста на диаграмме в разделе «Параметры текста», позволяя добавлять цвета, эффекты и шаблоны для меток заголовка и оси, а также изменять выравнивание текста на диаграмме.

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

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

Пример построения гистограммы в Excel

Последующие столбцы — «Год производства», например, с 2001 по 2008 гг. Далее нажмите на поле «Вставка» и команду «Гистограмма», рассмотрите первую с группировкой, выберите ее. Под вашей таблицей появится диаграмма. Переместите на отдельный лист, так она будет иметь более объемный вид.

Когда идет работа с диаграммами Excel, появляются 3 дополнительные вкладки: «Конструктор», «Макет», «Формат»:

«Макет» отвечает за работу внешнего вида. Здесь можно задать название, обозначить имя горизонтальной и вертикальной осей, вставлять рисунок из файла, готовые фигуры, блок-схемы, выноски, надписи, которые можно установить в любом месте страницы. Изменять разметки каждой оси и включать или отключать линии сетки.
«Формат» устанавливает стиль каждого элемента. В этой вкладке основными окнами являются «Формат выделенного фрагмента» (используется для уточнения форматирования) и «Восстановить форматирование стиля» (замена пользовательского выделенного элемента общим стилем оформления, что позволяет привести выделенный элемент в соответствие с общей темой рабочего документа Excel). Можно осуществлять заливку, контур фигуры и применять к выделенной фигуре эффекты оформления, такие, как тень, свечение, отражение или объемное вращение.
«Конструктор» позволяет изменить тип диаграммы или сохранить как шаблон (то есть сохранение макета в качестве шаблона), который можно применить к другим. Можно выполнять взаимную замену данных на осях. Значения, отложенные по оси Х, перемещаются на ось Y и наоборот. Возможно перемещать на другой лист или вкладку книги.
Гистограммы в Excel пригодятся для наглядного анализа динамики величин по времени

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

Сортировка данных на гистограммах в Excel

Когда вы создаете линейчатую гистограмму, по умолчанию категории данных отображаются на диаграмме в обратном порядке. То есть, если вы отсортируете данные от A до Z в электронной таблице, на гистограмме Excel они будут отображаться как от Z до A. Почему Excel всегда размещает категории данных в гистограммах задом наперед? Никто не знает. Но мы знаем, как это исправить

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

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

Чтобы гистограмма Excel была отсортирована сверху вниз, вы при помощи меню Данные – Сортировка и фильтр, просто упорядочиваете исходные данные в обратном порядке, то есть от меньшего к большему:

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

Сортировка гистограммы Excel по убыванию/возрастанию без сортировки исходных данных

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

На гистограмме Excel щелкните правой кнопкой мыши любую из меток вертикальной оси и выберите «Формат оси…» в контекстном меню. Или просто дважды щелкните метки вертикальной оси, чтобы появилась панель «Формат оси» .
На панели «Формат оси» в разделе «Параметры оси» выберите следующие параметры:

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

Готово! Ваша гистограмма Excel будет немедленно отсортирована так же, как источник данных, по убыванию или же по возрастанию. Вы можете это видеть на скриншоте выше.

Как сделать столбчатый график в excel?

Выделяем ячейку, в которую будет выводиться результат. Прежде всего, нужно учесть, что коэффициент вариации является процентным значением. В связи с этим следует поменять формат ячейки на соответствующий. Это можно сделать после её выделения, находясь во вкладке «Главная». Кликаем по полю формата на ленте в блоке инструментов «Число». Из раскрывшегося списка вариантов выбираем «Процентный». После этих действий формат у элемента будет соответствующий.

Чтобы установить пакет анализа в Excel, выберите вкладку «Файл» (а в Excel 2007 это круглая цветная кнопка слева сверху), далее — «Параметры», затем выберите раздел «Надстройки». Нажмите «Перейти» и поставьте галочку напротив «Пакет анализа».

x_i	22	23	24	25	26	27	28	29	30	31	32	33	34	35	36
n_i

Как создать гистограмму в Excel 2013, 2010 или 2007

Excel 2013 или более ранняя версия требует надстройки Analysis ToolPak для использования инструмента гистограммы. Убедитесь, что он установлен, прежде чем создавать гистограмму в Excel.

К Отправьте Вкладка, а затем выберите Опционы.
Выбрать Надстройки в навигационной панели.
Выбирайте Надстройки Excel , который относится к Управление раскрывающийся список, затем выберите Go.
Выбрать Анализ ToolPak, А затем выберите OK.
Пакет Analysis ToolPak должен быть установлен.

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

Введите данные, которые вы хотите использовать в гистограмме, в рабочий лист. Например, введите имена учащихся класса в один столбец и их результаты за тесты в другом.
Введите номера ячеек, которые вы хотите использовать, в третий столбец. Например, если вы хотите отображать результаты тестов по буквенной оценке, вы можете ввести 40, 50, 60, 70, 80, 90 и 100 в ячейки третьего столбца.
К дата Вкладка. в Анализ группа, выберите Анализ данных.
Выбрать Гистограмма в диалоговом окне Анализ данных, затем выберите OK. Откроется диалоговое окно «Гистограмма».
Выберите Диапазон ввода (которые в данном примере будут результатами тестов) и Диапазон корзины (которые являются ячейками, содержащими номера ячеек).
Выберите Выходной диапазон если вы хотите, чтобы гистограмма отображалась на том же листе. В противном случае выберите Новый рабочий лист or Новая рабочая тетрадь.
Выберите Выход графика флажок, затем выберите OK. Excel вставит статическую гистограмму на выбранный вами лист.

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

Создание гистограммы.

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

Обратите внимание, что выделяемая область не захватывает название таблицы со значениями, а только непосредственно значения! Также необходимо обратить внимание на то, что в данном случае числовые значения, которые должны располагаться по оси ординат (вертикальная ось), следует вносить во вторую (правую) колонку таблицы значений, а значения, располагающиеся по оси абсцисс (горизонтальная ось) – в первую (левую) колонку. Иначе вы получите неверную картину

Для примера, мы поменяли местами колонки и вот, что из этого получилось.

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

Создаем гистограмму с помощью функции ЧАСТОТА

В случае, когда вам нужно создать динамическую диаграмму, вам поможет функция ЧАСТОТА.

Возьмем табличку из прошлого раздела статьи.

Формула нашей функции:

После того как впишете формулу функции нажмите CTRL + SHIFT + ENTER, т.к. мы работаем с массивом данных.

Пошаговая инструкция:

Выделите ячейки рядом с колонкой промежутков;
Введите формулу нашей функции =ЧАСТОТА(B2:B41;D2:D8);

Подтвердите.

Итак, диаграмма создана.

Важная информация про функцию ЧАСТОТА:

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

Если, например, вы хотите все значения больше 80 объединить в один столбик — просто выделите на одну ячейку больше. Как указано на картинке ниже:

ГРАФИК

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

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

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

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

Альтернативы для гистограмм с накоплением – находим упущенные тренды

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

Возможно, это привлечёт внимание к эффективности региона и подтолкнёт к соответствующим действиям для исправления ситуации

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

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

Вариант №1: Панель диаграмм

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

Здесь мы видим тренды по каждому региону. Тренды, которые мы не видели на диаграмме с накоплением из-за неровных базисных линий и масштаба графика. Продажи в Северной Америке (North America) и в Европе (Europe) значительно выше, чем в Азии (Asia) и в Африке (Africa), поэтому эти два тренда теряются на фоне первых двух.

Сохранение и обмен вашей гистограммой

Теперь, когда вы создали гистограмму в Excel, важно знать, как ее сохранить и поделиться. Один из способов сделать это — экспортировать гистограмму в виде изображения или файла PDF, что позволит вам легко поделиться ею с другими

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

Экспорт гистограммы

Сохраните и поделитесь своей гистограммой в Excel, экспортировав ее в PDF-файл или файл изображения. Экспорт гистограммы позволяет вам легко поделиться ею с другими или включить ее в презентации и отчеты. Вот несколько вариантов экспорта гистограммы:

Экспортировать в PDF: Этот формат сохраняет форматирование и расположение гистограммы. Это делает его идеальным для совместного использования или печати.
Экспортировать как файл изображения: Экспорт гистограммы в виде файла изображения, например JPEG или PNG, позволяет вставлять ее в документы или загружать на веб-сайты.

Чтобы экспортировать гистограмму в Excel, просто перейдите в меню «Файл» и выберите «Сохранить как». Затем выберите желаемый формат файла (PDF или изображение) и укажите место для сохранения файла. Экспортируя гистограмму, вы можете легко распределять и анализировать тенденции в своих данных.

Совместная работа над гистограммой

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

Это позволяет улучшить сотрудничество и взаимопонимание между членами команды

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

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