В Excel диаграмма «Бюджет против фактического отклонения» используется для сравнения двух наборов данных ряда и отображения разницы или дисперсии двух рядов данных. Если различия являются отрицательными значениями, отображаются полосы или столбцы одного цвета, если значения положительные, отображаются полосы или столбцы другого цвета, как показано ниже. В этой статье я расскажу о том, как создать диаграмму дисперсии сравнения и мини-диаграмму дисперсии сравнения ячеек в книге 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
-> Лучшие инструменты для повышения производительности офиса
Источник: