Создание диаграммы сравнения бюджета с фактическими данными в 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