Содержание:
Данное руководство подразумевает, как в Excel создавать линейчатую диаграмму со значениями, автоматически отсортированными по убыванию или по возрасту, как создавать линейчатую диаграмму с отрицательными значениями, как настраивать изменения полос линейной диаграммы и многое другое.
Гистограммы, наряду с круговыми диаграммами, являются одними из самых популярных диаграмм. Построить гистограмму несложно, но ее еще проще понять. Для каких данных лучше всего использовать гистограмму? Для любых числовых данных, которые необходимо сравнить: числа, проценты, температуры, частоты и другие измерения. В целом гистограмма подходит для сравнения отдельных значений нескольких категорий данных. Особый вид гистограммы, диаграмма Ганта, часто используется в приложениях для управления проектами.
В этом уроке мы рассмотрим следующие вопросы, касающиеся гистограмм в Excel:
Линейчатые диаграммы в Excel – основы
гистограмма представляет собой график, показывающий различные категории данных в виде прямоугольных столбцов (столбцов), длина которых пропорциональна значениям элементов данных, которые они представляют. Такие полосы можно располагать горизонтально (линейная диаграмма) или вертикально. Вертикальная гистограмма — это отдельный тип диаграммы в Excel, называемый гистограмма.
Чтобы упростить дальнейшее изучение данного руководства и знать наверняка, что мы правильно понимаем друг друга, давайте определимся с основными терминами, обозначающими элементы гистограммы в Excel. На следующем рисунке показана стандартная сгруппированная гистограмма, содержащая 3 ряда данных (серый, зеленый и синий) и 4 категории данных (январь, февраль, март и апрель).
Как построить гистограмму в Excel
Нет ничего проще, чем построить гистограмму в Excel. Сначала выберите данные, которые вы хотите отобразить на диаграмме, затем на вкладке Вставить (Вставить) в раздел Диаграммы (Диаграммы) Щелкните значок гистограммы и выберите подтип, который нужно создать.
В данном примере мы создаём самую низкую простейшую диаграмму – Сгруппированная линия (2-D кластеризованная полоса):
Сгруппированная гистограмма, вставленный в лист Excel, будет выглядеть примерно так:
Линейчатая диаграмма Excel, показанная на рисунке выше, отображает только одну серию данных, поскольку исходные данные содержат только один столбец чисел. Если в исходных данных имеется два и более столбца с числами, то гистограмма будет содержать несколько рядов данных, окрашенных в разные цвета:
Как просмотреть все доступные типы гистограмм
Чтобы просмотреть все типы гистограмм, доступные в Excel, щелкните ссылку Другие гистограммы (Дополнительные столбчатые диаграммы) и в открывшемся диалоговом окне Вставить диаграмму (Вставить диаграмму) выберите один из доступных подтипов диаграммы.
Выбор макета и стиля гистограммы
Если вам не нравится макет или стиль гистограммы по умолчанию, вставленной в лист Excel, выберите его, чтобы отобразить группу вкладок на ленте меню. Работа с диаграммами (Инструменты для диаграмм). После этого на вкладке Конструктор (Дизайн) вы можете сделать следующее:
- В разделе Макеты диаграмм (Макеты диаграмм) нажмите кнопку Экспресс-планировка (Быстрый макет) и попробуйте различные готовые макеты гистограмм;
- Или поэкспериментируйте со стилями гистограмм в разделе Стили диаграмм (Стили диаграмм).
Типы линейных диаграмм в Excel
При создании гистограммы в Excel вы можете выбрать один из следующих подтипов:
Сгруппированная линия
Сгруппированная гистограмма (2-D или 3-D) сравнивает значения в категориях данных. На сгруппированной гистограмме категории обычно располагаются по вертикальной оси (ось Y), а значения — по горизонтальной оси (ось X). Сгруппированная трехмерная гистограмма не отображает третью ось, а просто отображает столбцы графика 3D.
Правильный штабелированный
Столбчатая диаграмма показывает пропорции отдельных элементов по отношению к целому. Как и сгруппированная гистограмма, она может быть плоской (2-D) или 3-D (XNUMX-D):
Нормированная линейчатая с накоплением
Этот тип гистограммы аналогичен предыдущему, но показывает процентное соотношение каждого элемента по отношению к целому для каждой категории данных.
Цилиндры, конусы и пирамиды
Помимо традиционных конфигураций, для построения всех традиционных подтипов линейной диаграммы можно использовать цилиндры, конусы или пирамиды. Разница только в форме фигур, которая отображает исходные данные.
В Excel 2010 и более ранних версиях можно было создать цилиндрическую, конусную или пирамидальную диаграмму, выбрав соответствующий тип диаграммы на вкладке. Вставить (Вставить) в раздел Диаграммы (Графики).
Лента меню Excel 2013 и Excel 2016 не содержит цилиндров, конусов или пирамид. По данным Microsoft, эти типы диаграмм были удалены, поскольку слишком большое количество типов диаграмм в более ранних версиях Excel затрудняло пользователю выбор правильного типа. Однако возможность использовать цилиндр, конус или пирамиду доступна и в современных версиях Excel, хотя для этого необходимо выполнить несколько дополнительных действий.
Как использовать цилиндр, конус или пирамиду при построении диаграммы в Excel 2013 и 2016.
Чтобы использовать цилиндрическую, конусную или пирамидальную диаграмму в Excel 2013 и 2016, создайте гистограмму XNUMX-D нужного типа (сгруппированную, сложенную или нормализованную сложенную), а затем измените формы, используемые для построения ряда:
- Выделите все столбцы на графике, щелкните по ним правой кнопкой мыши и в контекстном меню нажмите кнопку Формат ряда данных (Форматировать ряд данных) или просто дважды щелкните полосу графика.
- В появившейся панели в разделе Параметры строки (Опции серии) выберите подходящую фигура (Форма столбца).
Примечание: Если на диаграмме построено несколько рядов данных, описанную процедуру необходимо просмотреть для каждого ряда отдельно.
Настройка гистограмм в Excel
Как и другие типы диаграмм Excel, гистограммы предоставляют множество возможностей настройки таких элементов, как заголовок диаграммы, оси, метки данных и многое другое. Более подробную информацию вы можете найти по ссылкам ниже:
Теперь давайте рассмотрим некоторые конкретные приемы, применимые к гистограммам в Excel.
Измените ширину столбцов и расстояние между столбцами диаграммы.
Гистограмма, созданная в Excel с настройками по умолчанию, имеет слишком много пустого пространства между столбцами. Чтобы сделать полосы шире и визуально приблизить друг к другу, выполните следующие действия. Таким же образом можно сделать полоски уже и увеличить расстояние между ними. На плоском двумерном графике столбцы могут даже перекрываться.
- В гистограмме Excel щелкните правой кнопкой мыши любой ряд данных (столбик) и в контекстном меню выберите Формат ряда данных (Формат серии данных)
- В появившейся панели в разделе Параметры строки (Опции серии) выполните одно из следующих действий:
- Для квартиро, или для диаграммы XNUMXD: чтобы изменить передние полосы и расстояния между категориями, расположите ползунок параметра Боковой зазор (Ширина зазора) или введите процентное значение от 0 до 500 в поле ввода. Чем меньше значение, тем тоньше полосы и меньше зазор между ними, и наоборот.
- Для плоской диаграммы: Чтобы изменить зазор между рядами в одной категории, укажите ползунок параметра перекрывающиеся строки (Перекрытие серий) или введите процентное значение от -100 до 100 в поле ввода. Чем больше значение, тем больше перекрытие серий. Отрицательное значение приведет к появлению разрыва между строками, как показано на рисунке ниже:
- Для диаграммы 3D (XNUMX-D): чтобы изменить интервал между рядами данных, переместите ползунок параметра Передний зазор (Глубина разрыва) или введите значение от 0 до 500 процентов. Чем больше значение, тем больше расстояние между полосами. Изменение фронтального зазора заметно отражается на большинстве линейных диаграмм Excel, но лучше всего – на объемной гистограмме, как показано на следующей картинке:
- Для квартиро, или для диаграммы XNUMXD: чтобы изменить передние полосы и расстояния между категориями, расположите ползунок параметра Боковой зазор (Ширина зазора) или введите процентное значение от 0 до 500 в поле ввода. Чем меньше значение, тем тоньше полосы и меньше зазор между ними, и наоборот.
Построение гистограммы с отрицательными значениями
Для построения гистограммы в Excel исходные значения не обязательно должны быть больше нуля. В целом в Excel нет проблем с отображением отрицательных значений на стандартной гистограмме, но внешний вид диаграммы, вставленной по умолчанию на лист Excel, заставляет задуматься о редактировании макета и дизайна.
Чтобы как-то улучшить вид диаграммы на рисунке выше, во-первых, неплохо было бы сместить метки вертикальной оси влево, чтобы они не перекрывались полосами отрицательных значений, а во-вторых, можно использовать другие цвета. для отрицательных значений.
Настраиваемая вертикальная ось
Чтобы изменить внешний вид вертикальной оси, нажмите на любую из ее меток и в контекстном меню нажмите кнопку Формат оси (Ось формата) или просто дважды щелкните по оси подписки. В правой части рабочего листа расположена панель.
Нажмите Параметры оси (Параметры оси), разверните раздел подписей (Ярлыки) и установите параметр Положение ярлыка (Положение метки) значение Внизу (Низкий).
Меняем цвет заливки для отрицательных оценок
Если вы хотите привлечь внимание к отрицательным значениям на графике, то это можно успешно сделать, если столбцы отрицательных значений окрасить в другой цвет.
Если в линейной диаграмме Excel построен только один ряд данных, то отрицательные значения, как это часто бывает, можно окрасить в красный цвет. Если рядов данных на диаграмме несколько, то отрицательные значения в каждом из них нужно будет окрасить в свой цвет. Например, можно использовать настройки цвета для постоянных результатов, а для отрицательных — их более бледные настройки.
Чтобы изменить цвет отрицательных полос, выполните следующие действия:
- Нажмите правую кнопку мыши на любую полосу ряда данных, цвет которой нужно изменить в нашей таблице (это оранжевая полоса), и нажмите в контекстном меню. Формат ряда данных (Формат серии данных).
- В появившейся панели на вкладке Затенение и границы Параметр галочки (Заливка и линия) Инверсия для чисел <0 (Инвертировать, если отрицательный)
- Сразу после этого появятся два поля выбора цвета – для положительных и для отрицательных значений.
Наконечник: Если вторая палитра цветов не отображается, нажмите на маленькую черную стрелку в единственном доступном на данный момент поле и укажите цвет для положительных значений (вы можете выбрать тот же, что и по умолчанию). Сразу после этого появится второе поле выбора цвета для отрицательных значений:
Сортировка данных в гистограммах Excel
При создании линейчатой диаграммы в Excel категории данных по умолчанию выстраиваются в обратном порядке. То есть, если исходные данные отсортированы от А до Я, то в линейной диаграмме они будут расположены от Я до А. Почему так принято в Excel? Никто не знает. Зато мы знаем, как это исправить.
Примечание переводчика: Автор не знает, почему так, хотя с точки зрения математики все вполне логично: Microsoft Excel строит график на основе начальной координаты, которая стандартно находится в портативном пульте. То, что есть первое значение, верхнее в таблице данных, будет отложено сначала и так далее. Это работает и для диаграмм, построенных на трёх осях. В этом случае первый ряд данных в таблице будет отложен ближним по оси Z и далее в порядку.
Самый простой способ изменить порядок категорий на гистограмме — это изменить сортировку исходной таблицы.
Давайте проиллюстрируем это на простом наборе данных. Рабочий лист содержит список 10 крупнейших городов мира, расположенных в порядке убывания численности населения. На гистограмме данные будут расположены сверху вниз в порядке возрастания.
Чтобы на диаграмме Excel данные ограничились большими выступами сверху вниз, достаточно упорядочить их в исходной таблице в обратном порядке, т.е. от меньших к большим:
Если по какой-либо причине сортировка данных в таблице невозможна, то ниже будет показан способ изменить порядок данных в диаграмме Excel без изменения порядка данных в исходной таблице.
Располагаем данные в гистограмме Excel по возрастанию/убыванию без сортировки исходных данных.
Если порядок исходных данных на листе имеет значение и не может быть изменен, давайте сделаем так, чтобы полосы диаграммы отображались точно в том же порядке. Все просто – нужно всего лишь включить пару дополнительных опций в настройках настроек.
- В гистограмме Excel щелкните правой кнопкой мыши любую метку вертикальной оси и в контекстном меню выберите Формат оси (Ось формата). Или просто дважды щелкните метки вертикальной оси.
- В появившейся панели в разделе Параметры оси (Параметры оси) настройте следующие параметры:
- В группе Пересечение с горизонтальной осью (Пересечения горизонтальных осей) выберите В точке с наивысшим значением категории (По максимальной категории).
- В группе Положение оси (Положение оси) выберите Обратный порядок категорий (Категории в обратном порядке).
Готово! Данные в линейчатой диаграмме Excel моментально выстраиваются в том же порядке, что и в исходной таблице, – по возрастанию или по убыванию. Если порядок исходных данных будет изменен, то данные на диаграмме автоматически повторят эти изменения.
Изменение порядка рядов данных на гистограмме
Если линейчатая диаграмма Excel содержит несколько рядов данных, они также по умолчанию строятся в обратном порядке. Обратите внимание на рисунок ниже: регионы в таблице и на диаграмме расположены в обратном порядке.
Чтобы расположить ряды данных на гистограмме в том же порядке, что и на листе, используйте параметры В точке с наивысшим значением категории (В максимальной категории) и Обратный порядок категорий (Категории в обратном порядке). Порядок построения категорий при этом также проявляется, как видно на следующем рисунке:
Если необходимо расставить ряды данных в линейчатой диаграмме в порядке, отличающемся от порядка данных на рабочем листе, то это можно сделать с помощью:
Изменение порядка рядов данных с помощью диалогового окна «Выбор источника данных»
Этот метод позволяет изменить порядок построения рядов данных на гистограмме для каждого ряда отдельно и при этом сохранить неизменным порядок исходных данных на листе.
- Выберите диаграмму, чтобы отобразить группу вкладок на ленте меню. Работа с диаграммами (Инструменты для диаграмм). Открыть вкладку Конструктор (Дизайн) и в разделе Данные (Данные) нажмите кнопку Выберите данные (Выберите данные). Или нажмите значок Фильтры диаграммы (Фильтры диаграммы) справа от диаграммы и щелкните ссылку Выберите данные (Выберите Данные) в нижней части открывшегося меню.
- В диалоговом окне Выбор источника данных (Выберите источник данных) выберите ряд данных, для которого вы хотите изменить порядок построения, и переместите его вверх или вниз с помощью стрелок:
Изменить порядок рядов данных с помощью формулы помощи
Каждый ряд данных в диаграммах Excel (не только в гистограммах, а в любой диаграмме) определяется формулой, поэтому вы можете изменить ряд данных, изменив его формулу. В рамках данной статьи нас интересует только последний аргумент этой формулы, определяющий порядок построения ряда. Подробнее о формуле ряда данных вы можете прочитать в этой статье.
Например, ряд данных серой гистограммы в следующем примере находится на третьем месте.
Чтобы изменить порядок построения данной строки данных, выделите ее в диаграмме, а затем в формуле строки, замените формулу последнего аргумента на любое другое число. В нашем примере, чтобы переместить ряд данных на позицию позиции вверх, введите «2», а чтобы сделать это первым в диаграмме, введите «1» в качестве последнего аргумента.
Как и настройка параметров во внешнем окне Выбор источника данных (Выбрать источник данных) редактирование формулы ряда данных меняет порядок рядов данных только на диаграмме; исходные данные на листе остаются неизменными.
Вот как строится гистограмма в Excel. Тем, кто хочет больше узнать о диаграммах Excel, рекомендую изучить статьи из этого раздела сайта. Спасибо за внимание!