Два метода создания гистограммы с процентным изменением в Excel
Визуализация изменений данных с течением времени может значительно улучшить интерпретацию и представление трендов и паттернов, предоставляя четкое понимание прогресса данных. В этой статье рассматриваются два эффективных метода построения гистограмм, которые отображают процентные изменения.
- Создание диаграммы с процентным изменением с использованием вспомогательных столбцов - Сложно
- Создание диаграммы с процентным изменением с помощью Kutools для Excel - Просто
- Скачать пример файла
Создание диаграммы с процентным изменением с использованием вспомогательных столбцов
Предположим, что ваши исходные данные содержат информацию о Годе и Сумме, как показано на скриншоте ниже:
Чтобы создать диаграмму, показывающую процентные изменения на основе этих данных, сначала примените следующие формулы для создания необходимых вспомогательных столбцов.
- В столбце C используйте «Метка года» в качестве заголовка, затем в ячейке C2 введите приведенную ниже формулу и перетащите маркер заполнения вниз до появления пустой ячейки.
=REPT(A2,1)«A2» — это ячейка, содержащая номер года.
- В столбце D используйте «Сумма 1» в качестве заголовка, затем примените приведенную ниже формулу к ячейке D2 и перетащите маркер заполнения до появления пустой ячейки.
=B2«B2» — это ячейка, содержащая первое значение суммы.
- В столбце E, который используется для отображения невидимого столбца на диаграмме, «Невидимый» является заголовком, примените приведенную ниже формулу к ячейке E2, затем перетащите маркер автозаполнения по ячейкам до появления нуля, и, наконец, удалите значение нуля.
=B3«B3» — это ячейка, содержащая второе значение суммы.
- В столбце E заголовок — «Отклонение», этот столбец вычисляет разницу между прошлым годом и следующим годом. В ячейке E2 используйте приведенную ниже формулу и перетащите маркер заполнения вниз до предпоследней ячейки столбца данных.
=B3-B2«B3» — это сумма за второй год, B2 — сумма за первый год.
- В столбце G введите «Положительный» или «знак плюса» в качестве заголовка, затем в ячейке G2 используйте приведенную ниже формулу и перетащите маркер автозаполнения по ячейкам до последней ячейки диапазона данных.
=IF(F2>0,-F2,"")«F2» — это ячейка, содержащая отклонение за два года.
- В столбце H используйте «Отрицательный» или «знак минус» в качестве заголовка, затем в ячейке H2 введите приведенную ниже формулу и перетащите маркер заполнения по ячейкам до предпоследней ячейки диапазона данных.
=IF(F2<0,F2,"")«F2» — это ячейка, содержащая отклонение за два года.
- В столбце I будет отображаться процентное значение отклонения между двумя годами. Введите приведенную ниже формулу в ячейку I2, затем перетащите маркер заполнения по ячейкам до предпоследней ячейки диапазона данных и отформатируйте ячейки в процентном формате.
=F2/B2«F2» — это ячейка, содержащая отклонение первого и второго года, «B2» — сумма за первый год.
- Выберите диапазон данных столбцов C, D и E («Метка года», «Сумма 1» и «Невидимый»), нажмите «Вставка» > «Вставить гистограмму или линейчатую диаграмму» > «Группированная гистограмма».
- Нажмите на символ плюса рядом с диаграммой, чтобы отобразить меню «ЭЛЕМЕНТЫ ДИАГРАММЫ», затем снимите флажки «Линии сетки» и «Легенда». Этот шаг необязателен, он просто улучшает видимость данных.
- Нажмите на столбец, который отображает невидимые данные, затем нажмите на символ плюса, чтобы отобразить меню «ЭЛЕМЕНТЫ ДИАГРАММЫ», нажмите стрелку рядом с «Полосы ошибок» и выберите «Дополнительные параметры» в подменю.
- В панели «Формат полос ошибок» отметьте опции «Оба» и «Концы», затем отметьте опцию «Пользовательский» внизу и нажмите «Указать значение».
- В диалоговом окне «Пользовательские полосы ошибок» выберите диапазон G2:G7 в разделе «Положительное значение ошибки» и H2:H7 в разделе «Отрицательное значение ошибки». Нажмите «ОК».
Теперь диаграмма отображается следующим образом:
- Теперь щелкните правой кнопкой мыши по столбцу, который выражает значение Суммы 1, затем выберите «Формат ряда данных» из контекстного меню.
- В панели «Формат ряда данных» измените значения в обоих разделах «Перекрытие рядов» и «Ширина зазора» на ноль. Затем диаграмма будет отображаться следующим образом:
- Теперь вам нужно изменить цвет заливки невидимого столбца на без заливки. Щелкните правой кнопкой мыши по столбцам, которые указывают значения «Невидимый», затем выберите «Без заливки» из выпадающего списка «Заливка» в контекстном меню. Если вы используете версии Excel до 2013 года, просто нажмите «Формат ряда данных» из контекстного меню, и на вкладке «Заливка и линия» панели «Формат ряда данных» выберите «Без заливки» в разделе «Заливка».
- Щелкните по столбцу «Невидимый», затем нажмите символ плюса, чтобы отобразить меню «ЭЛЕМЕНТЫ ДИАГРАММЫ», нажмите стрелку рядом с «Метки данных», чтобы отобразить подменю, и выберите «Дополнительные параметры».
- В панели «Формат меток данных» отметьте чекбокс «Значение из ячеек» в разделе «Параметры меток», затем выберите процентные ячейки (столбец I) в диалоговом окне «Диапазон меток данных». Нажмите «ОК».
- Затем снимите флажки «Значение» и «Показать направляющие линии», в разделе «Положение метки» выберите нужную опцию положения.
Затем вы можете отформатировать заголовок диаграммы или цвет заливки диаграммы по своему усмотрению.
Теперь конечная диаграмма отображается следующим образом:
Создание диаграммы с процентным изменением с помощью Kutools для Excel
Если вы часто используете этот тип диаграммы, вышеупомянутый метод может оказаться трудоемким и затратным по времени. Здесь я рекомендую вам мощную группу инструментов «Kutools для Excel», которая может быстро создавать несколько сложных диаграмм одним кликом, включая диаграмму с процентным изменением.
После установки Kutools для Excel выберите «Kutools» > «Диаграммы» > «Сравнение различий» > «Гистограмма с процентным изменением», чтобы открыть диалоговое окно «Диаграмма процентного изменения». Затем выполните следующие действия:
- В разделе «Метки осей» выберите данные для отображения на оси X вашей диаграммы. Здесь я выбираю диапазон A2:A8.
- В разделе «Значения рядов» выберите данные, которые будут использоваться для значений в диаграмме, которые отображаются в виде столбцов.
- Нажмите «ОК».
Затем создается диаграмма с процентным изменением.
Чтобы узнать больше об этом инструменте диаграмм, пожалуйста, посетите эту страницу: Быстрое создание гистограммы с процентным изменением в Excel
Создание гистограмм с процентными изменениями в Excel действительно может оживить ваши данные, подчеркивая ключевые тенденции и изменения, которые в противном случае могли бы остаться незамеченными. Будь то использование вспомогательного столбца с формулами для более практического подхода или использование Kutools для Excel для более быстрого и автоматизированного процесса, оба метода помогут вам представить свои данные более эффективно.
Kutools для Excel - Усильте Excel более чем 300 необходимыми инструментами. Наслаждайтесь постоянно бесплатными функциями ИИ! Получите прямо сейчас
Скачать пример файла
Нажмите, чтобы скачать пример файла
Другие операции (статьи), связанные с диаграммами
Создание интерактивной диаграммы с выбором серий через флажки в Excel
В Excel мы обычно вставляем диаграмму для лучшего отображения данных, иногда диаграмма содержит более одного выбора серий. В этом случае вы можете захотеть показывать серии, отмечая флажки.
Условное форматирование стековой гистограммы в Excel
В этом руководстве показано, как создать условно форматированную стековую гистограмму, как показано на скриншоте ниже, пошагово в Excel.
Пошаговое создание диаграммы фактических данных и бюджета в Excel
В этом руководстве показано, как создать условно форматированную стековую гистограмму, как показано на скриншоте ниже, пошагово в Excel.
Лучшие инструменты для повышения производительности Office
Kutools для Excel решает большинство ваших проблем и увеличивает вашу продуктивность на 80%
- Супер строка формул (легкое редактирование нескольких строк текста и формул); Режим чтения (удобное чтение и редактирование большого количества ячеек); Вставка в отфильтрованный диапазон...
- Объединение ячеек/строк/столбцов с сохранением данных; Разделение содержимого ячеек; Объединение дублирующихся строк с подсчетом суммы/среднего значения... Предотвращение дублирования ячеек; Сравнение диапазонов...
- Выбор дублирующихся или уникальных строк; Выбор пустых строк (все ячейки пустые); Супер поиск и нечеткий поиск во многих книгах; Случайный выбор...
- Точное копирование нескольких ячеек без изменения ссылок на формулы; Автоматическое создание ссылок на несколько листов; Вставка маркеров, флажков и многое другое...
- Избранные и быстрая вставка формул, диапазонов, диаграмм и изображений; Шифрование ячеек с паролем; Создание списка рассылки и отправка электронных писем...
- Извлечение текста, добавление текста, удаление по позиции, удаление пробелов; Создание и печать статистики страниц; Преобразование между содержимым ячеек и комментариями...
- Супер фильтр (сохранение и применение схем фильтрации к другим листам); Расширенная сортировка по месяцу/неделе/дню, частоте и другим параметрам; Специальный фильтр по жирному шрифту, курсиву...
- Объединение книг и листов; Объединение таблиц на основе ключевых столбцов; Разделение данных на несколько листов; Пакетное преобразование xls, xlsx и PDF...
- Группировка сводной таблицы по номеру недели, дню недели и другим параметрам... Отображение разблокированных, заблокированных ячеек разными цветами; Выделение ячеек, содержащих формулы/имена...
- Включение редактирования и чтения с вкладками в Word, Excel, PowerPoint, Publisher, Access, Visio и Project.
- Открывайте и создавайте несколько документов в новых вкладках одного окна, а не в новых окнах.
- Увеличивает вашу продуктивность на 50% и экономит сотни кликов мышью каждый день!
