Note: The other languages of the website are Google-translated. Back to English

Створіть таблицю бюджету проти фактичної дисперсії в Excel

У Excel діаграма Бюджет проти фактичної дисперсії використовується для порівняння двох наборів даних серії та відображення різниці або дисперсії двох рядів даних. Якщо відмінності є від'ємними значеннями, відображаються одна кольорова смужка або стовпець, якщо позитивні значення - інша кольорова смужка або стовпець, як показано нижче. У цій статті я розповім про те, як створити діаграму дисперсії порівняння та міні-діаграму дисперсії порівняння комірок у книзі Excel.


Створіть таблицю бюджету проти фактичної дисперсії в Excel

Для створення діаграми бюджету проти фактичної дисперсії виконайте такі дії:

1. Введіть формулу нижче в комірку D2, щоб вирахувати різницю між бюджетом і фактичними значеннями, а потім перетягніть маркер заповнення до потрібних комірок, див. Знімок екрана:

=C2-B2

2. Потім виберіть дані у стовпці A та стовпці D, а потім натисніть Insert > Вставити стовпчик або стовпчасту діаграму > Кластерна колонка, див. скріншот:

3. І вставляється стовпчаста діаграма, після чого ви можете видалити непотрібні елементи діаграми, як вам потрібно, наприклад, видалити заголовок діаграми, вертикальну вісь, лінії сітки, і ви отримаєте результат, як показано на скріншотах нижче:

4. Потім клацніть правою кнопкою миші горизонтальну вісь і виберіть Формат осі з контекстного меню, див. знімок екрана:

5. У відкритому Формат осі під панеллю Параметри осі вкладка, клацніть етикетки щоб розгорнути цей розділ, а потім виберіть низький від Позиція мітки випадаючий список, і горизонтальна вісь буде розміщена внизу діаграми, див. знімок екрана:

6. А потім клацніть правою кнопкою миші будь-яку з барів і виберіть Форматувати ряд даних варіант, див. скріншот:

7. У відкритому Форматувати ряд даних під панеллю Заливка та рядок на вкладці Заповнювати розділу, будь-ласка, виконайте такі операції:

  • вибрати Тверда заливка варіант;
  • перевірити Інвертувати, якщо від’ємне прапорець;
  • Вкажіть два кольори для позитивних і негативних значень окремо від колір випадаюче.

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

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

чайові:

Після створення діаграми іноді може знадобитися показати мітки даних різними кольорами, наприклад, позитивні мітки даних зеленим кольором, а негативні мітки даних оранжевим кольором, будь-ласка, зробіть так:

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

[Color10]General;[Color53] -General

Потім натисніть OK , і ви отримаєте результат, як вам потрібно, див. скріншот:

примітки: Якщо вам потрібні інші кольори, перегляньте наступну таблицю покажчиків кольорів:


Створіть міні-бюджет проти фактичної діаграми дисперсії в клітинках

Якщо ви хочете вставити мінімальний бюджет проти фактичної діаграми дисперсії в клітинки, наступні кроки можуть вам допомогти:

1. Введіть формулу нижче в комірку D2, щоб вирахувати різницю між бюджетом і фактичними значеннями, а потім перетягніть маркер заповнення до потрібних комірок, див. Знімок екрана:

=C2-B2

2. Потім, відображте від’ємні значення дисперсії, застосуйте наступну формулу до комірки E2, а потім скопіюйте формулу до інших потрібних комірок, див. Знімок екрана:

=IF(D2<0,D2,"")

3. Відобразіть додатні значення дисперсії, введіть формулу нижче в комірку F2, а потім скопіюйте формулу в інші потрібні комірки, див. Знімок екрана:

=IF(D2>0,D2,"")

4. А потім вам слід вставити конкретний символ, який буде використовуватися в якості стовпчиків діаграм, натисніть, будь ласка Insert > Symbol, і в Symbol діалогове вікно, виберіть Блокові елементи що ви можете знайти майже в кожному шрифті з Підмножина випадаючий список і клацніть на Повний блок символ, а потім натисніть Insert щоб вставити символ, див. знімок екрана:

5. Вставивши спеціальний символ, застосуйте формулу нижче в комірку G2 і перетягніть цю формулу в інші комірки, і ви отримаєте нижченаведений результат:

=IF(E2="","",E2&" "&REPT($A$16,ABS(E2)/100))
примітки: У наведеній вище формулі, E2 - комірка містить від’ємне значення дисперсії, A16 це комірка містить конкретний символ, який ви вставили, і номер 100 - це змінна інформація, яка використовується для обчислення числа символу на основі значення дисперсії, ви можете змінити його на власне.

6. Продовжуйте застосовувати формулу нижче до комірки H2, а потім перетягніть цю формулу до інших комірок, і ви отримаєте нижченаведений результат:

=IF(F2="","",REPT($A$16,F2/100)&" "&F2)
примітки: У наведеній вище формулі, F2 - комірка містить позитивне значення дисперсії, A16 це комірка містить конкретний символ, який ви вставили, і номер 100 - це змінна інформація, яка використовується для обчислення числа символу на основі значення дисперсії, ви можете змінити його на власне.

7. А потім виділіть комірки в стовпці G, а потім натисніть Головна > Вирівняти правильно щоб вирівняти дані прямо на комірках, див. знімок екрана:

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


Створіть діаграму бюджету проти фактичної дисперсії з дивовижною функцією

Kutools для Excel надає десятки спеціальних типів діаграм, яких Excel не має, наприклад Діаграма кулі, Цільова та фактична діаграма, Діаграма різниці стрілок і так далі. З його зручним інструментом- Діаграма порівняння різниць, ви можете швидко та легко створити діаграму бюджету проти фактичної дисперсії на робочому аркуші або міні-бюджет проти фактичної діаграми дисперсії в клітинках. Натисніть, щоб завантажити Kutools для Excel для безкоштовної пробної версії!


Завантажте файл зразка "Бюджет проти фактичної дисперсії"


Відео: Створення бюджету проти фактичної діаграми дисперсії в Excel


Найкращі інструменти для підвищення продуктивності офісу

Kutools для Excel - допомагає виділитися з натовпу

Бажаєте виконувати щоденну роботу швидко та якісно? Kutools для Excel надає 300 потужних розширених функцій (об’єднання робочих книг, підсумовування за кольором, розділення вмісту клітинок, перетворення дати тощо) і заощаджує 80% вашого часу.

  • Розроблений для 1500 сценаріїв роботи, допомагає вирішити 80% проблем Excel.
  • Щодня зменшуйте тисячі клацань клавіатури та миші, знімайте втомлені очі та руки.
  • Станьте експертом Excel за 3 хвилини. Більше не потрібно згадувати будь-які болісні формули та коди VBA.
  • Безкоштовна 30-денна безкоштовна пробна версія. 60-денна гарантія повернення грошей. Безкоштовне оновлення та підтримка протягом 2 років.
Стрічка Excel (із встановленим Kutools для Excel)

Вкладка Office - Увімкнути читання та редагування з вкладками в Microsoft Office (включаючи Excel)

  • Одна секунда для перемикання між десятками відкритих документів!
  • Щодня зменшуйте сотні клацань мишею, прощайте руку миші.
  • Збільшує вашу продуктивність на 50% під час перегляду та редагування декількох документів.
  • Приносить ефективні вкладки в Office (включаючи Excel), як і Chrome, Firefox та New Internet Explorer.
Знімок екрана Excel (із встановленою вкладкою Office)
Коментарі (1)
Поки немає оцінок. Оцініть першим!
Цей коментар був мінімізований модератором на сайті
Bonjour,
Коментар faire si je souhaite ajouter à chaque barre un petit trait pour montrer le dépassement ou non du budget?
мерсі
There are no comments posted here yet
Залишайте свої коментарі
Публікація як гість
×
Оцініть цю публікацію:
0   Персонажі
Рекомендовані місця

Слідуй за нами

Copyright © 2009 - WWW.extendoffice.com. | Всі права захищені. На основі ExtendOffice. | Карта сайту
Microsoft та логотип Office є товарними знаками або зареєстрованими товарними знаками Microsoft Corporation у США та / або інших країнах.
Захищений Sectigo SSL