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

Створення діаграми дисперсії стрілки в Excel

В Excel дисперсія стрілки або інша діаграма - це тип кластерної стовпчикової або стовпчастої діаграми зі стрілкою вгору або вниз для позначення збільшення або зменшення відсотка двох наборів даних.

Наприклад, якщо ви хочете відобразити дисперсію продажу у порівнянні з попереднім роком, ви можете використовувати зелену та червону стрілки для відображення процентної зміни, як показано на знімку екрана нижче. У цій статті я розповім про те, як створити цей складний тип діаграми в Excel.


Створіть діаграму дисперсії або різниці стрілок в Excel

Для створення діаграми дисперсії стрілки для порівняння різниці процентних змін у 2020 році на основі 2019 року, спочатку слід вставити кілька допоміжних стовпців для обчислення основних даних, як показано на знімку екрана нижче:

Спочатку створіть дані допоміжних стовпців

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

=MAX(B2:C2)

2. У клітинку E2 введіть наступну формулу, а потім скопіюйте формулу в комірку E10, див. Знімок екрана:

=D2

3. Продовжуйте вводити формулу нижче в комірку F2 і скопіюйте формулу в клітинку F10, див. Знімок екрана:

=IF(C2>B2,C2-B2,NA())

4. Потім у клітинку G2 введіть формулу нижче та перетягніть її до комірки G10, щоб заповнити формулу, див. Знімок екрана:

=IF(C2<B2,B2-C2,NA())

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

5. А потім, будь-ласка, введіть наступну формулу в комірку H2 і перетягніть формулу до комірки H10, див. Знімок екрана:

=IF(ISERROR(F2),"","+" & TEXT(F2/B2,"0%"))

6. Потім продовжуйте вводити формулу нижче в комірку I2, а потім перетягніть маркер заповнення до комірки I10, див. Знімок екрана:

=IF(ISERROR(G2),"","-"&TEXT(G2/B2,"0%"))

чайові: Допоміжні стовпці H та I використовуються для додавання міток даних вгору та вниз для діаграми.
По-друге, створіть діаграму дисперсії стрілки на основі даних допоміжних стовпців

7. Створивши допоміжні дані, виберіть дані у стовпцях A, B, C, D, E, а потім натисніть Insert > Вставити стовпчик або стовпчасту діаграму > Кластерна колонка, див. скріншот:

8. І кластерна діаграма стовпців вставляється, як показано на знімку екрана нижче:

9. Потім клацніть, щоб вибрати діаграму, а потім виберіть Виберіть Дані у контекстному меню див. знімок екрана:

10. В вискочив Виберіть Джерело даних у діалоговому вікні Записи про легенду (Серія) списку, виберіть Макс ліворуч , а потім натисніть кнопку зі стрілкою вгору, щоб перемістити цю серію даних вгору, див. демонстрацію нижче:

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

12. Клацніть правою кнопкою миші Макс право рядок даних, а потім натисніть Елементи діаграми , щоб розгорнути поле списку, у полі списку поставте галочку Бари помилок, див. скріншот:

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

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

  • вибрати Мінус опція під Керівництво розділ;
  • Потім виберіть Без шапки від Кінцевий стиль розділ;
  • А потім виберіть виготовлений на замовлення від Сума помилки розділ, а потім натисніть Вкажіть значення кнопку, що вискочило Спеціальні панелі помилок діалоговому вікні, залиште поле порожнім Позитивне значення помилки і виберіть дані зі стовпчика "Стрілка вгору" в Негативне значення помилки текстове поле, а потім клацніть OK щоб закрити діалогове вікно.

15. Зберігайте Формат помилок помилок , а потім клацніть, щоб вибрати Максимальна шкала помилок праворуч з діаграми, під Параметри панелі помилок вкладка, в Вертикальна панель помилок розділ, будь ласка, налаштуйте такі операції:

  • вибрати Мінус опція під Керівництво розділ;
  • Потім виберіть Без шапки від Кінцевий стиль розділ;
  • А потім виберіть виготовлений на замовлення від Сума помилки розділ, а потім натисніть Вкажіть значення кнопку, що вискочило Спеціальні панелі помилок діалоговому вікні, залиште поле порожнім Позитивне значення помилки і виберіть дані зі стовпчика зі стрілкою вниз у полі Негативне значення помилки текстове поле, а потім клацніть OK щоб закрити діалогове вікно.

16. Потім слід відформатувати рядки даних Max Left та Max Right, невидимими, клацнути правою кнопкою миші панель даних Max Left і вибрати Форматувати ряд даних варіант, у відкритому Форматувати ряд даних під панеллю Заливка та рядок вкладка, виберіть Без заповнення від Заповнювати розділ, див. знімок екрана:

17. Виконайте ту ж операцію для приховування панелей даних Max Right, див. Знімок екрана:

18. Приховавши панелі даних Max Left і Max Right, тепер клацніть правою кнопкою миші Максимальна ліва смуга помилок Y, а потім виберіть Формат помилок помилок з контекстного меню в Формат помилок помилок під панеллю Заливка та рядок вкладку, виконайте такі дії:

  • вибрати Суцільна лінія , а потім вкажіть колір, який вам подобається, для стрілок вгору з колір випадає;
  • Потім відрегулюйте ширину рядка на 1.25 пт як вам потрібно від ширина розділ;
  • Потім виберіть стрілка значок з Почніть стрілку тип розкривний список.

19. Зберігайте Формат помилок помилок панель все ще відкривається, потім клацніть, щоб вибрати Максимальна шкала помилок праворуч, В Формат помилок помилок під панеллю Заливка та рядок вкладку, виконайте такі дії:

  • вибрати Суцільна лінія , а потім вкажіть колір пиляка, який вам подобається, для стрілок вниз із колір випадає;
  • Потім відрегулюйте ширину рядка на 1.25 пт як вам потрібно від ширина розділ;
  • Потім виберіть стрілка значок з Кінцева стрілка тип розкривний список.

20. І ви отримаєте діаграму, як показано на знімку екрана нижче:

21. Далі клацніть правою кнопкою миші панель даних Max Left і виберіть Форматувати ряд даних з контекстного меню у відкритому вікні Форматувати ряд даних під панеллю Параметри серії вкладка, виберіть Вторинна вісь опція від Сюжет серії включений розділ, а потім клацніть, щоб вибрати панель даних Max Right, виконайте ту саму операцію, як показано нижче:

22. Потім клацніть правою кнопкою миші будь-який рядок на діаграмі та виберіть Форматувати ряд даних, В Форматувати ряд даних під панеллю Параметри серії , введіть 0% і 100% в Накладання серії і Ширина зазору коробки окремо.

А потім, у Форматувати ряд даних , перейдіть до вибору Серія «Рік 2019"Або Серія «Рік 2020» від Параметри серії випадаючий список і введіть 0% і 100% в Накладання серії і Ширина зазору коробки також окремо. Дивіться демонстрацію нижче:

23. На цьому кроці ви можете додати мітки даних для діаграми, клацнути, щоб вибрати серію даних Max Left, а потім клацнути Елементи діаграми, в розширеному Елементи діаграми список, поставте галочку Мітки даних варіант, див. скріншот:

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

25. У відкритому Форматувати мітки даних під панеллю Параметри мітки вкладка, перевірити Значення з клітин варіант, і в вискочив Діапазон міток даних діалоговому вікні, виберіть клітинки даних зі стовпця "Мітка даних вгору", а потім натисніть OK , див. знімок екрана:

26. Досі в Форматувати мітки даних панель, зніміть прапорець значення і Показати лідерські лінії і ви отримаєте діаграму нижче, див. знімок екрана:

27. Потім продовжуйте додавати мітки вниз даних, клацніть правою кнопкою миші панель даних Max Right та натисніть Елементи діаграми щоб розгорнути поле списку, у вікні Елементи діаграми список, поставте галочку Мітки даних варіант, див. скріншот:

28. Потім клацніть правою кнопкою миші нові додані мітки даних і виберіть Форматувати мітки даних, у відкритому Форматувати мітки даних під панеллю Параметри мітки вкладка, перевірити Значення з клітин варіант, і в вискочив Діапазон міток даних діалоговому вікні, виберіть клітинки даних зі стовпця "Мітка даних вниз", а потім натисніть OK , див. знімок екрана:

29. Все ще в Форматувати ярлик данихs панелі, зніміть прапорець значення і Показати лідерські лінії параметри, а мітки даних про відсотки змін були додані на діаграму, див. знімок екрана:

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


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

Якщо у вас болить голова за допомогою вищезазначених складних кроків, тут я запропоную простий інструмент -Kutools для 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)
Поки немає оцінок. Оцініть першим!
Цей коментар був мінімізований модератором на сайті
Чудовий персонал
There are no comments posted here yet
Залишайте свої коментарі
Публікація як гість
×
Оцініть цю публікацію:
0   Персонажі
Рекомендовані місця

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

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