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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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