Быстрое создание положительного и отрицательного Гистограмма в Excel
Если нужно сравнить несколько объектов по одинаковым критериям и наглядно показать как положительные, так и отрицательные изменения, идеальным выбором станет гистограмма с положительными и отрицательными значениями. Такая диаграмма визуально подчёркивает приросты и убытки, упрощая анализ тенденций, как в приведённом ниже примере.
Предположим, исходные данные выглядят следующим образом:
Сначала добавьте вспомогательные столбцы:
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. Выделите диаграмму, чтобы отобразить вкладку Конструктор. На вкладке Конструктор нажмите Добавить элемент диаграммы > Линии сетки > Основные горизонтальные.
При необходимости вы можете изменить цвет рядов данных и добавить заголовок диаграммы.
Этот обычный способ позволяет создать диаграмму «положительное–отрицательное» Гистограмма, но он трудоёмкий и занимает много времени. Пользователям, которые хотят быстро и легко справиться с этой задачей, рекомендуется воспользоваться приведённым ниже методом.
С помощью инструмента Положительное–Отрицательное Гистограмма из Kutools для Excel эта задача в Excel решается всего за три шага.
1. Нажмите Kutools > Диаграммы > Положительное–Отрицательное Гистограмма.
2. В появившемся диалоговом окне выберите нужный тип диаграммы, затем укажите диапазон меток оси и два значения ряда по отдельности. После этого нажмите ОК.
3. Затем появится диалоговое окно с напоминанием о том, что для хранения некоторых данных будет создан новый лист. Если вы хотите продолжить создание диаграммы, нажмите Да.
Теперь диаграмма «положительное–отрицательное» создана, а также добавлен скрытый лист для размещения рассчитанных данных.
Тип 1 
Тип 2 
Оформите созданную диаграмму на вкладках «Конструктор» и «Формат» в соответствии со своими требованиями.
Kutools для Excel— раскройте весь потенциал Excel с помощью более чем 300 незаменимых инструментов, которые ускоряют и упрощают вашу работу, а также воспользуйтесь возможностями ИИ для более разумной обработки данных и повышения продуктивности.Получить сейчас
Нажмите, чтобы скачать образец файла
Как создать термометр-индикатор цели в Excel?
Хотите визуализировать прогресс достижения цели с помощью термометра в Excel? В этом руководстве пошагово объясняется, как создать такой индикатор в Excel.
Как изменить цвет диаграммы в зависимости от значения в Excel
Иногда при построении диаграммы нужно отображать разные диапазоны значений разными цветами. Например, значения от 10 до 30 — красным, от 30 до 50 — зелёным, а от 50 до 70 — фиолетовым. В этом руководстве мы покажем, как изменить цвет диаграммы в зависимости от значения в Excel.
Использовать условное форматирование для создания столбчатой диаграммы с накоплением в Excel
В этом пошаговом руководстве показано, как создать столбчатую диаграмму с накоплением с помощью условного форматирования — как на снимке экрана ниже.
Создание интерактивной диаграммы с флажками выбора рядов в Excel
В Excel диаграммы обычно используются для наглядного представления данных. Если на диаграмме отображается несколько рядов, удобно управлять их видимостью с помощью флажков: например, при установке флажка 1 отображается ряд 1, при установке флажка 2 — ряд 2, а при включении обоих флажков — оба ряда одновременно.
Лучшие инструменты повышения продуктивности в Office
Раскройте весь потенциал Excel с помощью Kutools для Excel и ощутите эффективность как никогда раньше.Kutools для Excel предлагает более 300 расширенных функций для повышения продуктивности и Экономия времени.Нажмите здесь, чтобы получить нужную Вам функцию…
Office Tab добавляет в Office вкладки и значительно упрощает Вашу работу
- Включите редактирование и чтение во вкладках в Word, Excel, PowerPoint, Publisher, Access, Visio и Project.
- Открывайте и создавайте несколько документов во вкладках одного окна — вместо того чтобы использовать отдельные окна.
- Повышает вашу продуктивность на 50 % и экономит сотни кликов мышью каждый день!
Все надстройки Kutools — один установщик
Kutools for Office — набор надстроек для Excel, Word, Outlook и PowerPoint, а также Office Tab Pro, идеально подходящий командам, работающим с разными приложениями Office.
- Единый комплект— надстройки для Excel, Word, Outlook и PowerPoint + Office Tab Pro
- Один установщик, одна лицензия— настройка занимает считанные минуты (готово для MSI)
- Лучше работать вместе— оптимизированная продуктивность во всех приложениях Office
- 30-дневная полнофункциональная пробная версия— без регистрации и кредитной карты
- Лучшее соотношение цены и качества— экономия по сравнению с покупкой отдельных надстроек