Быстро создайте положительную и отрицательную столбчатую диаграмму в Excel

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

Создание положительно-отрицательной столбчатой диаграммы пошагово (15 шагов) Шаг 1: Расположите данные Шаг 2: Создайте диаграмму Шаг 3: Настройте диаграмму Создайте положительно-отрицательную столбчатую диаграмму с помощью удобного инструмента (3 шага) Скачать образец файла

Предположим, исходные данные выглядят следующим образом:

Во-первых, добавьте несколько вспомогательных столбцов:

1. Вычислите разницу между двумя столбцами (Столбец C и Столбец B)

В ячейке D2 введите эту формулу

Перетащите маркер заполнения вниз, чтобы рассчитать различия.

2. Вычислите процент увеличения или уменьшения

В ячейке E2 введите эту формулу

Перетащите маркер заполнения вниз, чтобы заполнить ячейки этой формулой.

Затем, оставив ячейки выделенными, нажмите Главная вкладку и перейдите в группу Число Выберите Процентный формат кнопку для форматирования ячеек в виде процентов.

3. Вычислите положительное и отрицательное значение

В ячейке F2 введите эту формулу

E2 — это ячейка с процентом, перетащите маркер заполнения вниз, чтобы заполнить ячейки этой формулой.

В ячейке G2 введите эту формулу

E2 — это ячейка с процентом, перетащите маркер заполнения вниз, чтобы заполнить ячейки этой формулой.

Теперь выберите F2:G9, нажмите Главная > Процентный формат чтобы отформатировать эти ячейки как проценты.

Теперь создайте положительно-отрицательную столбчатую диаграмму на основе данных.

1. Выберите пустую ячейку и нажмите Вставка > Вставить столбчатую или гистограмму > Группированная гистограмма.

2. Щелкните правой кнопкой мыши по пустой диаграмме, в контекстном меню выберите Выбрать данные.

3. В диалоговом окне Выбор источника данных нажмите Добавить кнопку, чтобы открыть диалоговое окно Изменить ряд В текстовом поле Имя ряда выберите заголовок Процент затем в текстовом поле Значения ряда выберите E2:E9 что содержит проценты увеличения и уменьшения, нажмите ОК.

4. Вернитесь к диалоговому окну Выбор источника данных нажмите Добавить чтобы перейти к диалоговому окну Изменить ряд снова, выберите F1 как имя ряда и F2:F9 как значения ряда. F2:F9 это положительные значения, нажмите ОК.

5. Снова нажмите Добавить в диалоговом окне Выбор источника данных чтобы перейти к диалоговому окну Изменить ряд выберите G1 как имя ряда, G2:G9 как значения ряда. Нажмите ОК.

6. Вернитесь к диалоговому окну Выбор данных ряда нажмите кнопку Изменить в разделе Горизонтальные метки оси (категории) и выберите A2:A9 как имена меток оси в диалоговом окне Метки оси нажмите ОК > ОК чтобы завершить выбор данных.

Теперь диаграмма отображается следующим образом:

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

Затем настройте диаграмму.

1. Щелкните правой кнопкой мыши по меткам оси, затем выберите Формат оси из контекстного меню, и в открывшейся панели Формат оси поставьте галочку напротив Обратный порядок категорий в разделе Параметры оси прокрутите вниз до раздела Метки выберите Низ из выпадающего списка рядом с Положение метки.

2. Удалите сетку и ось X (горизонтальную).

3. Щелкните правой кнопкой мыши по одному из рядов, чтобы выбрать Формат точки данных из контекстного меню, и в панели Формат точки данных настройте Перекрытие рядов до 0%, Ширина зазора до 25%.

4. Щелкните правой кнопкой мыши по левому ряду и нажмите Добавить метку данных > Добавить метки данных.

5. Удалите метки #N/A по одной.

6. Выберите диаграмму, чтобы показать вкладку Дизайн и в разделе Дизайн нажмите Добавить элемент диаграммы > Линии сетки > Основные горизонтальные.

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

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

С помощью инструмента Positive Negative Bar Chart из Kutools for Excel, который требует всего 3 шага для выполнения этой задачи в Excel.

1. Нажмите Kutools > Диаграммы > Positive Negative Bar Chart.

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

3. Затем появится диалоговое окно, которое предупредит вас о создании нового листа для хранения некоторых данных. Если вы хотите продолжить создание диаграммы, нажмите Да.

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

Тип 1

Тип 2

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

Нажмите, чтобы скачать пример файла