Гистограмма с переменной шириной столбцов в excel
Обновлено: 07.07.2024
Имеем, в качестве примера, вот такую табличку с информацией по количеству сотрудников и стоимостью четырех известных IT-компаний:
Задача: необходимо наглядно отобразить оба параметра для сравнения по всем компаниям.
Можно попытаться построить типичную в таких случаях пузырьковую (точечную) диаграмму. Можно попробовать поиграть с трехмерными диаграммами или торнадо. А можно немного "пошаманить" и сделать плоскую гистограмму со столбцами переменной ширины.
Этап 1. Подготовка данных
Половина успеха при построении любых нестандартных типов диаграмм в Excel, заключается в правильной подготовке таблицы с данными для диаграммы. В нашем случае, исходную табличку придется ощутимо переделать и превратить в нечто похожее на:
Давайте разберем все изменения, которые были сделаны.
- Каждая компания превратилась из строки в столбец.
- По каждой компании должен быть блок из 5 ячеек, где первая и последняя ячейки с нулями, а три центральных содержат одинаковые значения - стоимость каждой компании.
- Добавлены вспомогательные столбцы Пустышка и Подписи, в которых напротив центральных значений каждого блока размещены стоимости и названия компаний. Эти колонки понадобятся нам чуть позже для правильного размещения подписей к столбикам.
- Первый (неподписанный) столбец представляет из себя чуть более хитрую штуку. Не залитые голубым цветом в нем ячейки - это количества сотрудников по каждой компании (92, 126, 54, 380), но взятые последовательно с нарастающим итогом. Т.е. для Apple это 92, для Microsoft уже 92+126=218, для Google 92+126+54=272 и т.д. Голубые же ячейки будут впоследствии, своего рода, "переходниками" и содержат среднее арифметическое соседних ячеек, т.е. для Apple это (0+92)/2=46, для Microsoft (92+218)/2=155 и т.д.
Этап 2. Строим диаграмму
Выделим в нашей таблице все столбцы кроме последних двух и на вкладке Вставка (Insert) выберем для построения вариант диаграммы С областями с накоплением (Stacked Area) :
На выходе должно получиться что-то похожее:
В этом же окне можно настроить шаг делений по горизонтальной оси - для нашего примера я поменял параметр Единицы измерения с месяцев на дни и задал значение 50.
Этап 3. Добавляем подписи
Теперь неплохо бы добавить в нашу диаграмму подписи с названиями компаний. Скопируйте столбец Пустышка (выделить диапазон G11:G25 и нажать Ctrl+C), выделите диаграмму и вставьте скопированные данные прямо в нее (нажать Ctrl+V) - к нашей диаграмме должен добавиться новый ряд, выглядящий как крыши домов над нашими прямоугольниками:
Теперь щелкните правой кнопкой мыши по любой "крыше" и выберите команду Изменить тип диаграммы для ряда (Change series chart type) . В открывшемся окне измените тип диаграммы для добавленного ряда на обычный график с маркерами:
Затем щелкните правой кнопкой мыши по графику и выберите Добавить подписи данных (Add Data Labels) . Excel подпишет значения стоимости компании на каждом "пике".
Дальнейший ход действий зависит от того, какая у вас версия Excel. Если у вас последние Excel 2013 или 2016, то все будет проще. Щелкните правой кнопкой мыши по подписям, выберите команду Формат подписей (Format Data Labels) и затем включите флажок Значения из ячеек (Values from cells) . Останется выделить данные из последнего столбца в нашей таблице, чтобы они попали на диаграмму в подписи к столбикам вместо чисел:
Если же у вас более древние версии Excel, то придется либо изменять подписи вручную (несколько одиночных щелчков по подписи, пока не начнет мигать курсор, а потом вбить название компании), либо использовать специальную бесплатную надстройку XYChartLabeler (дай бог здоровьичка Rob Bovey - ее автору, спасшему много-много моих нервных клеток и времени).
Excel 2016 стал первой версией за последние 20 лет, где Microsoft, наконец-то, добавила новые типы диаграмм (водопад, Парето, Маримекко и др.) в стандартный набор. Очень хочется верить, что "лед тронулся" и в следующих обновлениях мы увидим еще больше красивых и наглядных вариантов. Возможно, когда-нибудь и описанная выше гистограмма с переменной шириной столбцов будет строиться без "шаманских танцев" из этой статьи, а за два движения. Будем надеяться :)
Обычно стандартная гистограмма отображает все столбцы одинаковой ширины. Вы можете сравнивать данные только по высоте столбцов на гистограмме в Excel. Есть ли способ создать столбчатую диаграмму переменных, чтобы столбцы могли различаться как по ширине, так и по высоте? В этом руководстве представлены пошаговые инструкции, которые помогут вам создать столбчатую диаграмму переменной ширины в Excel.
Создание столбчатой диаграммы переменной ширины в Excel
Предположим, вы хотите создать столбчатую диаграмму переменной ширины на основе данных, как показано на снимке экрана ниже, сделайте следующее, чтобы получить ее.
1. Во-первых, вам нужно вычислить положение конца каждого столбца на оси X на диаграмме с помощью следующих формул.
Примечание: Здесь мы указываем минимум оси X как 0 и максимум как 100, поэтому столбец начинается с 0 и заканчивается 100.
2. Теперь перечислите каждое значение высоты дважды (на основе высот исходного диапазона данных) в разных строках под вспомогательной строкой, которую вы только что создали.
3. Выберите весь диапазон помощников (в данном случае A8: J13), щелкните Вставить > Вставить линейную диаграмму или диаграмму с областями > Сложенная область.
4. Затем создается диаграмма с областями с накоплением, как показано на скриншоте ниже. Выберите нижнюю область (серия1 на диаграмме), а затем нажмите Удалить ключ, чтобы удалить его с графика.
5. Щелкните правой кнопкой мыши по оси X и выберите Ось формата из контекстного меню.
6. В дебюте Ось формата , выберите Ось даты вариант в Тип оси .
7. Вернитесь к диаграмме, щелкните ее правой кнопкой мыши и выберите Выберите данные в контекстном меню.
8. в Выберите источник данных диалогового окна, нажмите Редактировать в Ярлыки горизонтальной оси (категории) коробка,
9. Во всплывающем Ярлыки осей диалоговом окне выберите первую вспомогательную строку значений, созданную на шаге 1, а затем щелкните ОК.
10. Когда он вернется в Выберите источник данных диалоговое окно, нажмите OK для сохранения изменений.
Теперь диаграмма отображается, как показано на скриншоте ниже.
11. Выберите ось X и нажмите кнопку Удалить ключ, чтобы удалить его с графика.
Теперь вам нужно вычислить средние значения для каждого столбца, чтобы добавить имена серий и значения серий отдельно внизу и вверху каждого столбца, как показано на скриншоте ниже.
12. Начните с новой строки, введите значения (представляющие положение конца каждого столбца на оси X), вычисленные на шаге 1, отдельно в ячейки. Смотрите скриншот:
13. В следующей строке введите приведенную ниже формулу в первую ячейку и нажмите клавишу Enter ключ.
= 15 австралийских долларов / 2
14. Примените приведенную ниже формулу в правую пустую ячейку, затем перетащите ее Ручка автозаполнения право на получение других результатов.
15. Введите значения высоты отдельно в ячейки новой строки. Кроме того, нам еще нужна новая строка с номером 0 в ячейках.
16. Щелкните диаграмму правой кнопкой мыши и выберите Выберите данные в контекстном меню.
17. В дебюте Выберите источник данных диалогового окна, нажмите Добавить кнопку.
18. в Редактировать серию диалоговом окне введите имя в поле Название серии коробка как вам нужно, а в Значения серии выберите ячейки строки, содержащие значения высоты, затем щелкните значок OK кнопку.
19. Когда он вернется в Выберите источник данных диалогового окна, нажмите OK чтобы сохранить изменения.
Теперь диаграмма отображается, как показано на скриншоте ниже.
20. Щелкните правой кнопкой мыши любую серию на диаграмме и выберитеt Изменить тип диаграммы серии из контекстного меню.
21. в Изменить тип диаграммы диалоговом окне укажите тип диаграммы как Scatter для новой добавленной серии, а затем щелкните OK кнопку.
Диаграмма отображается следующим образом.
22. Щелкните диаграмму правой кнопкой мыши и выберите Выберите данные в контекстном меню.
23. в Выберите источник данных диалоговом окне выберите серию, которую вы добавили на шаге 18, а затем щелкните Редактировать кнопку.
24. В дебюте Редактировать серию диалоговом окне выберите ячейки, содержащие средние значения для каждого столбца, а затем щелкните значок OK кнопку.
25. Нажмите OK в Выберите источник данных диалоговое окно для сохранения изменений.
26. Выделите точки, нажмите Элементы диаграммы кнопку, а затем проверьте Этикетки данных коробка. Смотрите скриншот:
27. Теперь вам нужно скрыть все точки на графике. Не снимая выделения с точек, перейдите к Форматировать ряд данных панель настроить следующим образом.
27.3) Выберите все вариант в Параметры маркера .
28. Выберите метки данных, которые вы добавили только что, перейдите к Форматирование меток данных панель, а затем выберите Выше вариант в Положение ярлыка .
Теперь значения серии отображаются по центру над каждым столбцом, как показано на скриншоте выше.
29. Щелкните диаграмму правой кнопкой мыши и выберите Выберите данные.
30. в Выберите источник данных диалогового окна, нажмите Добавить кнопку.
31. в Редактировать серию диалоговое окно, вам необходимо:
31.1) Выберите ячейки, содержащие средние значения для каждого столбца в Значения серии X коробка; 31.2) Выберите ячейки, содержащие 0 значений в Значения серии Y коробка;
32. Когда он вернется в Выберите источник данных диалоговое окно, вы можете увидеть, что добавлена новая серия, нажмите OK для сохранения изменений.
33. Выберите новую серию на диаграмме (точки скрыты, вам просто нужно щелкнуть внизу в центре любого столбца, чтобы выбрать их).
34. Нажмите Элементы диаграммы кнопку, а затем проверьте Этикетки данных пунктом.
35. Выберите метки данных, перейдите в Форматирование меток данных панель и настройте следующим образом.
Советы: Если же линия индикатора Форматирование меток данных панель не отображается, щелкните правой кнопкой мыши метки данных и выберите Форматирование меток данных из контекстного меню.
35.1) Проверьте Значение из ячеек коробка;
36. Теперь диаграмма отображается следующим образом. Удалите легенду и измените заголовок диаграммы.
На этом столбчатая диаграмма переменной ширины готова.
Простое создание столбчатой диаграммы переменной ширины в Excel
Освободи Себя Столбчатая диаграмма переменной ширины полезности Kutools for Excel может помочь вам быстро создать столбчатую диаграмму переменной ширины в Excel с помощью всего лишь нескольких щелчков мышью, как показано ниже.
Скачайте и попробуйте прямо сейчас! 30-дневный бесплатный маршрут
Загрузите образец файла
Лучшие инструменты для работы в офисе
Kutools for Excel - поможет вам выделиться из толпы
Хотите быстро и безупречно выполнять свою повседневную работу? Kutools for Excel предлагает мощные расширенные функции 300 (объединение книг, сумма по цвету, разделение содержимого ячеек, дата преобразования и так далее . ) и экономия 80% времени для вас.
Разве не удобно было бы создавать гистограммы, столбцы которых могли бы иметь переменную ширину и высоту? Ширина и высота столбцов одновременно регулировались бы при построении графика, по вашим данным.
В Excel эта возможность напрямую не предусмотрена, однако, применив трюк с точечной диаграммой в осях XY (XY scatter chart), можно создавать очень эффективные гистограммы с переменной шириной столбцов. Точечная диаграмма по осям XY применяется для сравнения значений; таким образом, она является прекрасной основой для создания гистограммы со столбцами переменной ширины.
На рис. 5.24 показана гистограмма со столбцами переменной ширины, которая демонстрирует соотношения между процентным содержанием и стоимостью следующих расходов: газ, электричество, вода, еда, поездки и прочие. На оси X (эта ось проходит внизу диаграммы) выводится процентное содержание (%), а на оси Y (ось слева на диаграмме) выводится стоимость ($).
Рис. 5.24. Точечная диаграмма в осях XY
Чтобы создать такую диаграмму, настройте таблицу с некоторыми данными (см. рис. 5.24) и при помощи мастера диаграмм выделите диапазон D2:E8. Затем на шаге 1 мастера диаграмм выберите диаграмму Точечная (XY Scatter). Примите настройки точечной диаграммы по умолчанию (выводятся только точки) и щелкните на кнопке Далее (Next), чтобы перейти к шагу 2 мастера. Удостоверьтесь, что установлен переключатель В столбцах (Columns). Щелкните кнопку Далее (Next), чтобы перейти к третьему шагу мастера, и в поле Ось Y (значений) (Value (Y) Axis) введите Cost ($). Щелкните кнопку Далее (Next) и удостоверьтесь, что диаграмма будет создана как объект на текущем, а не на новом листе. Щелкните на кнопке Готово (Finish), чтобы увидеть точечную диаграмму.
(то же сочетание используется на Macintosh) или команду Сервис → Параметры → Вид (Tools → Options → View) и в группе Параметры окна (Window) установить флажок Формулы (Formulas).
Настало время повозиться с диаграммой и создать столбцы. Сначала удалите легенду и линии сетки (выделите их и нажмите клавишу Delete) и отформатируйте область построения диаграммы, убрав заливку. Для этого щелкните серый фон правой кнопкой мыши и в контекстном меню выберите команду Формат области построения (Format Plot Area). В группе Заливка (Area) выберите переключатель Прозрачная (None).
Дважды щелкните ось X, чтобы открыть диалоговое окно Формат оси (Format Axis). Щелкните вкладку Шкала (Scale). В области Шкала по оси X (категорий) (Value Axis Scale) для параметра Минимальное значение (Minimum) введите 0, а для параметра Максимальное значение (Maximum) введите 1. Перейдите на вкладку Вид (Pattern) и в группе Основные (Major Tick Mark type) выберите переключатель Нет (None). В группе Метки делений (Tick Mark Labels) также выберите переключатель Нет (None) и щелкните на кнопке ОК. Точечная диаграмма должна выглядеть, как на рис. 5.25.
Рис. 5.25. Модифицированная точечная диаграмма
Теперь, когда вся трудная работа сделана, настало время немного убраться и добавить подписи. Сначала в диалоговом окне Формат ряда данных (Format Data Series) перейдите на вкладку Patterns (Вид) и в поле Marker (Маркер) выберите None (Отсутствует). Результат — гистограмма со столбцами переменной ширины, созданная на основе модифицированной точечной диаграммы по осям XY (рис. 5.26).
Рис. 5.26. Гистограмма со столбцами переменной ширины
Перед использованием Chart Tools необходимо добавить новый ряд данных, чтобы создать подписи по оси X для диаграммы. Выделите диаграмму, щелкните ее правой кнопкой мыши, в контекстном меню выберите команду Исходные данные (Source Data) и перейдите на вкладку Ряд (Series). Щелкните кнопку Добавить (Add), чтобы добавить новый ряд. В поле Значения X (X Values) выделите диапазон 13:18, а в поле Значения Y (Y Values) — диапазон J3:J8. Отформатируйте новый ряд данных, убрав маркеры. Для этого нужно открыть диалоговое окно Формат рядов данных (Format Data Series), перейти на вкладку Вид (Patterns) и в группе Маркер (Marker) выбрать переключатель Отсутствует (None).
Настало время применить надстройку. Удостоверьтесь, что выбран ряд Series 2 (Ряд 2), и выберите команду Chart → JWalk Chart Tools. В открывшемся диалоговом окне выберите диапазон меток данных КЗ:К8. Чтобы добавить еще один ряд данных, из которого будут взяты подписи для столбцов диаграммы, выделите диаграмму, щелкните ее правой кнопкой мыши и в контекстном меню выберите команду Исходные данные (Source Data) и перейдите на вкладку Ряд (Series). Щелкните кнопку Добавить (Add), чтобы добавить новый ряд. В поле Значения X (X Values) выделите диапазон 13:18, а в поле Значения Y (Y Values) выделите диапазон СЗ:С8. И снова отформатируйте новый ряд данных, убрав маркеры. Для этого откройте диалоговое окно Формат рядов данных (Format Data Series), перейдите на вкладку Вид (Patterns) и в группе Маркер (Marker) выберите Отсутствует (None).
Еще раз примените надстройку. В этот раз выделите Series 3 (Ряд 3) и свяжите подписи данных с диапазоном АЗ:А8. Результат должен выглядеть, как на рис. 5.27.
Рис. 5.27. Завершенная гистограмма со столбцами переменной ширины
Удивительное свойство этого типа диаграмм состоит в том, что полосы будут расширяться или сжиматься по оси X в сторону оси Y, когда значения в столбцах % Share или Cost ($) будут изменяться, Отлично!
Гистограмма - это своего рода столбчатая диаграмма, которая может приблизительно представлять распределение числовых или категориальных данных. На гистограмме ниже показано распределение баллов по предмету. С помощью таблицы вы можете легко определить, сколько учеников набрали от 48 до 60 баллов, а сколько учеников набрали от 60 до 78 и так далее. В этом уроке мы покажем вам, как шаг за шагом создать гистограмму в Excel.
Создание гистограммы в Excel 2019 и более ранних версиях
Предположим, вы хотите создать гистограмму на основе данных оценок, как показано на скриншоте ниже, сделайте следующее, чтобы получить ее.
Примечание: Методы из этого раздела могут применяться не только в Excel 2019, но и в более ранних версиях Excel (Excel 2016, 2013 и 2010).
Создайте гистограмму в Excel 2019 и более ранних версиях с формулой
1. Во-первых, вам нужно разделить диапазон оценок на ряд интервалов (интервалов) в зависимости от ваших потребностей. В этом случае я разделяю оценки на 4 ячейки.
Советы: Обычно интервалы задаются как последовательные неперекрывающиеся интервалы переменной.
2. Подсчитайте, сколько баллов попадает в каждую ячейку.
Выберите пустую ячейку, чтобы вывести результат для первой корзины, скопируйте в нее приведенную ниже формулу и нажмите кнопку Enter ключ.
Примечание: В формуле 2 млрд долларов: 28 млрд долларов это диапазон оценок, 48 и 60 - это диапазон значений для первого интервала.
3. Примените ту же функцию СЧЁТЕСЛИ (измените диапазон значений для разных интервалов), чтобы получить частоты для других интервалов.
4. Выберите данные Bins и Frequency (включая заголовки), нажмите Вставить > Вставить столбец или гистограмму > Кластерный столбец.
Теперь создается гистограмма, как показано на скриншоте ниже.
5. Щелкните правой кнопкой мыши ряд данных и выберите Форматировать ряд данных из контекстного меню.
6. в Форматировать ряд данных панель, измените Ширина зазора в 0% под Варианты серий меню.
7. Продолжайте оставаться в Форматировать ряд данных панель и сделайте следующее.
7.1) Нажмите Заливка и линия вкладка; 7.2) Разверните Граница раздел и выберите Сплошная линия вариант;
8. Вернитесь к диаграмме и измените название диаграммы в соответствии с вашими потребностями.
На этом гистограмма готова, как показано на скриншоте ниже.
Создание гистограммы в Excel 2019 и более ранних версиях с помощью Analysis ToolPak
Здесь рекомендуем пакет инструментов анализа для создания гистограммы в Excel. Этот метод также можно применить в Excel 2019, 2016, 2013 и 2010.
1. Во-первых, вам нужно создать вспомогательный столбец и указать диапазоны ячеек на основе исходного диапазона данных.
Примечание: Как показано на скриншоте ниже, первая ячейка 48 показывает, что вы вычислите количество студентов, набравших меньше 48 баллов, вторая ячейка 60 определит, сколько учеников набрали от 49 до 60 и так далее.
2. Нажмите Файл > Параметры для открытия Параметры Excel окно.
3. в Параметры Excel окна, нажмите Надстройки на левой панели убедитесь, что Надстройки Excel опция выбрана в Менеджер раскрывающийся список, а затем щелкните Go кнопку.
4. в Надстройки диалоговое окно, проверьте Пакет инструментов анализа - VBA и нажмите OK кнопку.
Теперь Анализ группа добавляется под Данные в Excel.
5. К Данные вкладку нажмите Анализ данных в Анализ группа.
6. в Анализ данных диалоговое окно, выберите Гистограмма в Инструменты анализа раздел, а затем щелкните ОК.
7. В выскочившем Гистограмма диалоговое окно, настройте следующим образом.
1) Выберите исходный диапазон оценок в Диапазон ввода коробка; 2) Выберите диапазон бункеров, созданный на шаге 1, в Диапазон корзины коробка; 3) Выберите Выходной диапазон вариант, укажите ячейку для размещения диапазона вывода; 5) Нажмите OK кнопка. Смотрите скриншот:
Затем создается новый диапазон и гистограмма, как показано на скриншоте ниже.
8. Продолжайте правильно настраивать свои интервалы и изменять другие элементы диаграммы.
8.1) В этом случае я настраиваю интервалы для отображения непосредственно в виде интервалов. Вам просто нужно вручную изменить значения ячеек в столбце Bin, и они будут синхронизированы с горизонтальной осью диаграммы. 8.2) Удалите вторичную ось с диаграммы (просто выберите ее и нажмите Удалить ключ); 8.3) Убрать пространство между планками. Включите Форматировать ряд данных панель, а затем измените Ширина зазора в 0% под Варианты серий вкладка;Наконец, гистограмма отображается следующим образом.
Создайте гистограмму в Excel 2019 и 2016 со встроенной функцией
Если вы используете Excel 2019 или 2016, вы можете применить встроенный тип диаграммы гистограммы, чтобы легко создать диаграмму гистограммы в Excel. Пожалуйста, сделайте следующее.
1. Выделите весь диапазон таблиц и нажмите Вставить > Вставить статистическую диаграмму > Гистограмма.
Гистограмма создана, как показано на скриншоте ниже.
Примечание: Количество интервалов и их ширина рассчитываются автоматически на основе выбранных оценок.
Советы:
Если вы хотите настроить гистограмму, щелкните горизонтальную ось правой кнопкой мыши и выберите Ось формата из контекстного меню.
На открывшейся панели в правой части Excel есть несколько параметров, которые вы должны знать и настраивать самостоятельно:
1. Категории: Эта опция часто используется для текстовых категорий. Гистограмма сгруппирует те же категории и суммирует соответствующие значения на оси значений; 2. Ширина бункера: Вы можете настроить ширину корзины, выбрав этот параметр и введя новую ширину в текстовое поле; 3. Количество бункеров: Вы можете указать, сколько ячеек вы хотите, выбрав эту опцию и введя определенное число в текстовое поле; 4. Корзина переполнения: Если вы хотите создать ячейку для всех значений, превышающих определенное значение, установите флажок «Переполнение корзины» и введите определенное значение в текстовое поле; 5. Контейнер для нижнего белья: Если вы хотите создать ячейку для всех значений, меньших или равных определенному значению, установите флажок «Ячейка недополнения» и введите определенное значение в текстовое поле;
Легко создавайте гистограммы с удивительной функцией
Освободи Себя Нормальное распределение / кривая колокола полезности Kutools for Excel может помочь вам легко создать гистограмму в Excel с помощью нескольких щелчков мышью, как показано в демонстрации ниже.
Скачайте и попробуйте прямо сейчас! 30-дневный бесплатный маршрут
Загрузите образец файла
Видео: создание гистограммы в Excel
Лучшие инструменты для работы в офисе
Kutools for Excel - поможет вам выделиться из толпы
Хотите быстро и безупречно выполнять свою повседневную работу? Kutools for Excel предлагает мощные расширенные функции 300 (объединение книг, сумма по цвету, разделение содержимого ячеек, дата преобразования и так далее . ) и экономия 80% времени для вас.
Читайте также: