Как построить гистограмму в MS Excel

Как построить гистограмму в MS Excel

Как построить гистограмму? Процедура довольно проста, если дело касается MS Excel, единственное, что необходимо учесть, это подготовку данных. Каково предназначение гистограмм? Различные источники согласны в том, что это представление данных в визуальной форме. Для чего представлять данные в таком формате? Чаще всего это делается для удобства сравнения различных числовых значений показателей. Цель данной статьи состоит в том, чтобы показать, как преобразовать табличные данные из числовых в графические.

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

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

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

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

Гистограмма с переменной шириной столбцов

Имеем, в качестве примера, вот такую табличку с информацией по количеству сотрудников и стоимостью четырех известных 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) :

На выходе должно получиться что-то похожее:

Основная проблема в том, что Excel на автомате интерпретирует подписи к оси X не как числа, а как текст — интервалы между подписями не соответствуют математической разнице между числами, да и сами подписи повторяются. Именно поэтому наша диаграмма еще не похожа на желаемую, но это, на самом деле, легко исправить. Щелкните правой кнопкой мыши по горизонтальной оси и выберите в контекстном меню команду Формат оси (Axis Format) . В открывшемся окне переключите Тип оси с Автовыбора на Ось дат (Date Axis) и все сразу изменится!

В этом же окне можно настроить шаг делений по горизонтальной оси — для нашего примера я поменял параметр Единицы измерения с месяцев на дни и задал значение 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, наконец-то, добавила новые типы диаграмм (водопад, Парето, Маримекко и др.) в стандартный набор. Очень хочется верить, что «лед тронулся» и в следующих обновлениях мы увидим еще больше красивых и наглядных вариантов. Возможно, когда-нибудь и описанная выше гистограмма с переменной шириной столбцов будет строиться без «шаманских танцев» из этой статьи, а за два движения. Будем надеяться 🙂

Форматирование гистограмм в Microsoft Excel

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

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

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

Изменение текста заголовка диаграммы

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

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

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

Название вашей диаграммы будет удалено после снятия флажка.

Добавление и редактирование меток осей

Чтобы добавить метки осей к линейчатой ​​диаграмме, выберите диаграмму и щелкните зеленый значок «Элементы диаграммы» (значок «+»).

В меню «Элементы диаграммы» установите флажок «Названия осей».

Метки осей должны появиться как для оси x (внизу), так и для оси y (слева). Они появятся в виде текстовых полей.

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

Если вы хотите удалить метки, выполните те же действия, чтобы снять флажок в меню «Элементы диаграммы», нажав зеленый значок «+». Удаление флажка рядом с параметром «Названия осей» немедленно удаляет метки из поля зрения.

Изменение стиля и цветов диаграммы

Microsoft Excel предлагает ряд тем диаграмм (именованных стилей), которые вы можете применить к линейчатой ​​диаграмме. Чтобы применить их, выберите свою диаграмму и затем щелкните значок «Стили диаграмм» справа, который выглядит как кисть.

Список параметров стиля станет видимым в раскрывающемся меню в разделе «Стиль».

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

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

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

Вы также можете изменить цвета, используемые в вашей диаграмме, в разделе «Цвет» меню «Стили диаграмм».

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

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

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

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

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

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

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

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

7.5 Пределы погрешностей

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

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

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

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

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

• Повторное использование чего угодно: Добавляйте наиболее часто используемые или сложные формулы, диаграммы и все остальное в избранное и быстро используйте их в будущем.
• Более 20 текстовых функций: Извлечь число из текстовой строки; Извлечь или удалить часть текстов; Преобразование чисел и валют в английские слова.
• Инструменты слияния : Несколько книг и листов в одну; Объединить несколько ячеек / строк / столбцов без потери данных; Объедините повторяющиеся строки и сумму.
• Разделить инструменты : Разделение данных на несколько листов в зависимости от ценности; Из одной книги в несколько файлов Excel, PDF или CSV; От одного столбца к нескольким столбцам.
• Вставить пропуск Скрытые / отфильтрованные строки; Подсчет и сумма по цвету фона ; Отправляйте персонализированные электронные письма нескольким получателям массово.
• Суперфильтр: Создавайте расширенные схемы фильтров и применяйте их к любым листам; Сортировать по неделям, дням, периодичности и др .; Фильтр жирным шрифтом, формулы, комментарий .
• Более 300 мощных функций; Работает с Office 2007-2019 и 365; Поддерживает все языки; Простое развертывание на вашем предприятии или в организации.

Сделайте гистограмму

Удивительный! Использование эффективных вкладок в Excel, таких как Chrome, Firefox и Safari!

Экономьте 50% своего времени и сокращайте тысячи щелчков мышью каждый день!

В Excel, если вы хотите построить гистограмму, вам нужно сначала сделать два шага.

1. Укажите ячейки данных. Масштаб бинов ограничен масштабом данных. Например, у меня есть список оценок, и шкала оценок от 0 до 100, и теперь я указываю свои ячейки, как показано на скриншоте ниже:

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

2. Затем вам нужно добавить Анализ данных добавить в. Нажмите Файл > Параметры (или щелкните Офисы Кнопка в верхнем левом углу> Параметры Excel в Excel 2007); затем в Параметры Excel диалоговое окно, нажмите Надстройки Вкладка, выберите Go в правом разделе. Смотрите скриншот:

3. в Надстройки диалог, проверьте Анализ ToolPak и нажмите OK для закрытия диалога.

Внимание: В Excel 2007 появится диалоговое окно с сообщением, что вы не установили надстройку. Просто нажмите на Да кнопку.

4. Идти к Данные вкладку и нажмите Анализ данных функция. Затем в появившемся диалоговом окне выберите Гистограмма и нажмите OK. Смотрите скриншоты:

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

6. Нажмите OK. Теперь вы можете увидеть диаграмму гистограммы, а отчет вставлен и показан, как показано ниже:

Форматирование гистограмм в Microsoft Excel

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

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

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

СВЯЗАННЫЙ: Как работать с линиями тренда в диаграммах Microsoft Excel

Изменение текста заголовка диаграммы

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

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

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

Заголовок вашего графика будет удален после снятия флажка.

Добавление и редактирование меток оси

Чтобы добавить метки осей на гистограмму, выберите свою диаграмму и нажмите зеленый значок «Элементы диаграммы» (значок «+»).

В меню «Элементы диаграммы» установите флажок «Оси осей».

Метки оси должны отображаться как для оси x (внизу), так и для оси y (слева). Они появятся в виде текстовых полей.

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

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

Изменение стиля и цвета диаграммы

Microsoft Excel предлагает несколько тем диаграмм (именованных стилей), которые вы можете применить к своей гистограмме. Чтобы применить их, выберите свою диаграмму и затем щелкните значок «Стили диаграммы» справа, который выглядит как кисть.

Список параметров стиля будет отображаться в раскрывающемся меню в разделе «Стиль».

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

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

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

Вы также можете внести изменения в цвета, используемые в диаграмме, в разделе «Цвет» меню «Стили диаграммы».

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

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

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

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

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

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

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

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