Создать диаграмму отклонения бюджета от фактического в Excel

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

  • Создать диаграмму отклонения бюджета от фактического в Excel
  • Создайте в ячейках мини-бюджет и фактическую диаграмму отклонения.
  • Создайте диаграмму отклонения бюджета и фактического отклонения с помощью замечательной функции
  • Загрузить образец файла диаграммы отклонения бюджета и фактического отклонения.
  • Видео: создание диаграммы отклонения бюджета и фактического отклонения в Excel

Создать диаграмму отклонения бюджета и фактического отклонения в Excel

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

1 . Введите приведенную ниже формулу в ячейку D2, чтобы вычислить разницу между бюджетным и фактическим значениями, а затем перетащите маркер заполнения в нужные ячейки, см. Снимок экрана:

2 . Затем выберите данные в столбце A и столбце D, а затем нажмите Вставить > Вставить столбец или гистограмму > Кластерный столбец , см. снимок экрана:

3 . После вставки столбчатой ​​диаграммы вы можете удалить ненужные элементы диаграммы по своему усмотрению, например удалить заголовок диаграммы, вертикальную ось, линии сетки, и вы получите результат, как показано на скриншотах ниже:

4 . Затем щелкните правой кнопкой мыши горизонтальную ось и выберите в контекстном меню параметр Ось формата , см. Снимок экрана:

. В открывшейся панели Ось формата на вкладке Параметры оси щелкните Ярлыки , чтобы развернуть этот раздел, а затем выберите Low из раскрывающегося списка Положение метки , а горизонтальная ось будет размещена внизу диаграммы, см. снимок экрана:

6 . Затем щелкните правой кнопкой мыши любую из полос и выберите параметр Форматировать ряд данных , см. Снимок экрана:

7 . В открывшейся панели Форматировать ряд данных под областью Заливка и линия на вкладке Заливка выполните следующие операции:

  • Выберите Сплошная заливка ;
  • Установите флажок Инвертировать, если отрицательный ;
  • Укажите два цвета для положительных и отрицательных значений отдельно от Раскрывающийся список Цвет .

8 . После установки цветов для полосок следует добавить метки данных, щелкните, чтобы выбрать полосы на диаграмме, а затем нажмите Элементы диаграммы , затем отметьте Метки данных в списке Элементы диаграммы , см. снимок экрана:

9 . Полосы столбца настолько узкие, что выглядят не красиво, в этом случае вы c и отрегулируйте ширину полосы по своему усмотрению, щелкните правой кнопкой мыши любую полосу и выберите Форматировать ряд данных на панели Форматировать ряд данных , на вкладке Параметры серии измените Ширина зазора по своему усмотрению, в этом случае я изменю его на 80% , см. снимок экрана:

Советы :

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

Выберите данные в столбце «Разница» из исходной таблицы данных, а затем нажмите Ctrl + 1 , чтобы открыть В диалоговом окне Форматирование ячеек в диалоговом окне Форматирование ячеек выберите Пользовательский в списке Категория . , а затем введите ниже код в текстовое поле Тип :

Затем нажмите кнопку OK , и вы получите нужный результат, см. снимок экрана:

Примечание : если вам нужны другие цвета, просмотрите следующую таблицу индекса цветов:


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

Если вы хотите вставить мини-бюджет и фактическую дисперсию диаграммы в ячейках, вам могут помочь следующие шаги:

1 . Введите приведенную ниже формулу в ячейку D2, чтобы вычислить разницу между бюджетным и фактическим значениями, а затем перетащите маркер заполнения в нужные ячейки, см. Снимок экрана:

2 . Затем отобразите отрицательные значения дисперсии, примените следующую формулу в ячейку E2, а затем скопируйте формулу в другие ячейки, которые вам нужны, см. Снимок экрана:

3 . Отобразите положительные значения дисперсии, введите приведенную ниже формулу в ячейку F2, а затем скопируйте формулу в другие нужные ячейки, см. Снимок экрана:

4 . Затем вы должны вставить определенный символ, который будет использоваться в качестве столбцов диаграммы, нажмите Insert > Symbol и в В диалоговом окне «Символ выберите Элементы блока , которые можно найти почти в каждом шрифте из раскрывающегося списка Подмножество , и нажмите Full Block , затем нажмите Insert , чтобы вставить символ, см. снимок экрана:

. После вставки специального символа примените приведенную ниже формулу в ячейку G2 и перетащите эту формулу в другие ячейки, и вы получите следующий результат:

Примечание : в приведенной выше формуле E2 — это ячейка, содержащая отрицательное значение дисперсии, A16 — ячейка содержит конкретный символ, который вы вставили, а число 100 — это переменные данные, которые используются для вычисления номера символа на основе значения дисперсии, вы можете измените его на свой.

6 . Продолжайте применять приведенную ниже формулу в ячейку H2, а затем перетащите эту формулу в другие ячейки, и вы получите следующий результат:

Примечание . В приведенной выше формуле F2 означает, что ячейка содержит положительное значение дисперсии, A16 — это ячейка содержит конкретный символ, который вы вставили, а число 100 — это переменные данные, которые используются для вычисления номера символа на основе значения дисперсии, вы можете изменить его на свое .

7 . Затем выберите ячейки в столбце G, а затем нажмите Home > Выровнять по правому краю , чтобы выровнять данные прямо по ячейкам, см. Снимок экрана:

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


Создайте диаграмму отклонения бюджета от фактического отклонения с помощью замечательной функции

Kutools for Excel предоставляет десятки специальных типов диаграмм, которых нет в Excel, таких как Bullet Chart , Целевая и фактическая диаграмма , Стрелочная диаграмма разницы и т. Д. С помощью удобного инструмента — Таблица сравнения различий , вы можете быстро и легко создать диаграмму отклонения бюджета и фактического отклонения на листе или мини-бюджетную диаграмму отклонения фактического отклонения в ячейках. Нажмите, чтобы загрузить Kutools for Excel в бесплатную пробную версию!


Загрузить файл примера диаграммы бюджета и фактического отклонения


Видео: создание диаграммы бюджетных и фактических отклонений в Excel


-> Лучшие инструменты для повышения производительности офиса


Оцените статью
Frestage.ru
Добавить комментарий