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

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

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

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

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

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

2. Затем выберите данные в столбце A и столбце D, после чего нажмите Вставка > Вставить гистограмму или график > Группированная гистограмма, как показано на скриншоте:

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

4. Затем щелкните правой кнопкой мыши по горизонтальной оси и выберите опцию Формат оси из контекстного меню, как показано на скриншоте:

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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