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

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

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

У цій статті ми представимо, як створити діаграму стовпців, яка відображає процентну зміну між стовпцями в Excel.


Створіть діаграму стовпців із зміною відсотків за допомогою смужок помилок

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

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

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

=A2&REPT(" ",10)
чайові: Ця формула використовується для збільшення мітки та переміщення тексту вліво. В іншому випадку мітка буде розташована під центром видимих ​​і невидимих ​​стовпців стовпців.

2. Продовжуйте вводити формулу нижче в комірку D2, а потім перетягніть і скопіюйте формулу в комірку D10, див. Знімок екрана:

=B2
чайові: Ця формула посилається на номери замовлень у стовпці B.

3. Потім у комірку E2 введіть таку формулу, а потім перетягніть маркер заповнення до комірки E9, див. Знімок екрана:

=B3
чайові: Ця формула використовується для посилання на замовлення на наступний рік. Він буде використовуватися для невидимих ​​смужок, які є вершиною або основою смужок помилок.

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

=B3-B2
чайові: Ця формула буде обчислювати дисперсію або різницю між поточним та наступним роками.

5. Продовжуйте вводити наступну формулу в комірку G2 і перетягніть її в комірку G9, див. Знімок екрана:

=IF(F2>0,-F2,"")
чайові: Ця формула використовується для повернення значення дисперсії, якщо воно позитивне, і відображатимуться деякі негативні числа, які використовуються для того, щоб смуги помилок починалися у верхній частині невидимої смуги та йшли вниз.

6. Потім продовжуйте вводити наступну формулу в комірку H2 і скопіюйте цю формулу в комірку H9, див. Знімок екрана:

=IF(F2<0,F2,"")
чайові: Ця формула використовується для повернення значення дисперсії, якщо воно від’ємне, і відображатимуться деякі від’ємні числа, які використовуються для того, щоб панелі помилок починалися внизу панелі помилок і йшли вгору.

7. Тепер вставте останній допоміжний стовпець, застосуйте наведену нижче формулу до комірки I2 і перетягніть її до комірки I9, а потім відформатуйте десяткові результати у відсотковому стилі. див. скріншот:

=F2/B2
чайові: Ця формула обчислює різницю у відсотках між попереднім та наступним роками. Він буде використовуватися як мітки на невидимому стовпці під панелями помилок.

По-друге, створіть діаграму на основі даних допоміжних стовпців

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

9. А потім, вставлена ​​стовпчаста діаграма, ви можете видалити непотрібні елементи діаграми, такі як заголовок діаграми, легенда або лінії сітки, див. Скріншоти:

10. Потім клацніть рядок стовпців, на якому відображаються невидимі дані, а потім клацніть на Елементи діаграми кнопку, щоб розгорнути Елементи діаграми і виберіть Бари помилок > Додаткові параметри, див. скріншот:

11. У відкритому Формат помилок помилок під панеллю Параметри панелі помилок Вкладка:

  • вибрати обидві від Керівництво розділ;
  • Вибирати Кришка від Кінцевий стиль;
  • вибрати виготовлений на замовлення від Сума помилки, А потім натисніть Вкажіть значення, у наступному Спеціальні панелі помилок діалоговому вікні, виберіть дані з комірки G2: G10 у Позитивне значення помилки , потім виберіть H2: H10 комірки в Негативне значення помилки коробка

12. Потім натисніть кнопку OK , і ви отримаєте діаграму, як показано нижче:

13. А тепер клацніть правою кнопкою миші стовпець стовпчика, який відображає дані Замовлення 1, і виберіть Форматувати ряд даних у контекстному меню див. знімок екрана:

14. У відкритому Форматувати ряд даних під панеллю Параметри серії , змініть значення в Накладання серії і Ширина зазору розділи до 0%, див. скріншот:

15. Тепер слід приховати невидиму панель даних, клацнути правою кнопкою миші будь-яку з них, у контекстному меню, що з’явиться, вибрати Без заповнення від Заповнювати розділ, див. знімок екрана:

16. Якщо панель невидимих ​​даних все ще вибрана, натисніть Елементи діаграми кнопка, виберіть Мітки даних > Додаткові параметри, див. скріншот:

17, в Форматувати мітки даних під панеллю Параметри мітки вкладка, перевірити Значення з клітин, і в вискочив Діапазон міток даних , виберіть діапазон даних дисперсії I2: I9, див. скріншоти:

18. Потім натисніть OK, все ще в Форматувати мітки даних панель:

  • Зніміть прапорець значення і Показати лідерські лінії опції під Параметри мітки;
  • Потім вкажіть позицію етикетки як Зовнішній кінець від Позиція мітки.

19. Тепер ви можете бачити, як мітки даних були додані на діаграму, ви можете встановити негативні відсоткові мітки Всередині кінцята відформатуйте ярлики даних відповідно до своїх потреб, див. знімок екрана:


Створіть стовпчасту діаграму зі зміною відсотків за допомогою стрілок вгору вниз

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

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

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

1. Щоб вставити перші допоміжні дані, застосуйте наведені нижче формули:

C2: =REPT(A2,1)        (drag the formula to cell C10)
D2
: = B2 (перетягніть формулу в комірку D10)
E2: = B3-B2 (перетягніть формулу до комірки E9)
F2: = E2 / B2 (перетягніть формулу в клітинку F9)

2. Потім застосуйте наступні формули для створення допоміжних даних другої частини:

G2: Enter 1.5, 2.5, 3.5, 4.5…plus one to the previous cell.          (fill the data to from G2 to G9)
H2
: = IF (B3> = B2, B3, NA ()) (перетягніть формулу до комірки H9)
I2: = IF (B3

По-друге, створіть діаграму на основі даних допоміжних стовпців

3. Виберіть дані у стовпці C та D, а потім натисніть Insert > Вставити стовпчик або стовпчасту діаграму > Кластерна колонка щоб вставити графік стовпців, як показано на знімку екрана нижче:

4. Потім натисніть Ctrl + C щоб скопіювати дані у стовпці G, стовпці H, стовпці I, а потім клацніть, щоб вибрати діаграму, див. знімок екрана:

5. Вибравши діаграму, натисніть Головна > Паста > Спеціальна вставка, В Спеціальна вставка діалогове вікно, виберіть Нові серії, Параметри стовпців, а потім перевірте Назва серії в першому рядку і Категорії (мітки X) у першій колонці параметри, див. скріншоти:

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

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

8, в Змінити тип діаграми діалогове вікно, змініть обидва Augmenter і Зменшити до Точкова діаграма, потім зніміть прапорець Вторинна вісь коробка для кожного з Виберіть тип діаграми та вісь для вашої серії вікно списку. Дивіться знімок екрана:

9. А потім натисніть OK , ви отримаєте комбіновану діаграму, на якій маркери розташовані між відповідними стовпцями. Дивіться знімок екрана:

10. Потім натисніть, щоб вибрати серію збільшення (оранжева крапка), а потім натисніть Елементи діаграми кнопку, перевірте Бари помилок у вікні списку, а на діаграму додано смужки помилок, див. знімок екрана:

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

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

  • вибрати обидві опція від Керівництво;
  • вибрати Без шапки від Кінцевий стиль;
  • Від Сума помилки розділ, виберіть виготовлений на замовлення, А потім натисніть Вкажіть значення кнопку, що вискочило Спеціальні панелі помилок у діалоговому вікні Позитивне значення помилки поле, введіть = {0}, і в Негативне значення помилки Виберіть значення дисперсії E2: E9.
  • Потім натисніть кнопку OK кнопки.

13. Зараз, все ще в Формат помилок помилок панель, клацніть Заливка та рядок , виконайте такі операції:

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

14. На цьому кроці слід приховати маркери (оранжеві крапки), виділити оранжеві крапки та клацнути правою кнопкою миші, вибрати Форматувати ряд даних з контекстного меню у відкритому вікні Форматувати ряд даних під панеллю Заливка та рядок вкладка, клацніть маркер розділ, а потім виберіть ніхто від Параметри маркера, див. скріншот:

15. Повторіть вищезазначений крок 10-14, щоб вставити стрілку вниз для зменшення ряду даних і сховати сірі маркери, і ви отримаєте діаграму, як показано на знімку екрана нижче:

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

17. Потім клацніть правою кнопкою миші будь-яку мітку даних і виберіть Форматувати мітки даних з контекстного меню, в розгорнутому Форматувати мітки даних під панеллю Параметри мітки вкладка, перевірити Значення з клітин варіант, тоді в спливаючому Діапазон міток даних діалоговому вікні, виберіть комірки відсотка дисперсії (F2: F9), див. знімок екрана:

18. Клацання OK , щоб закрити діалогове вікно, все ще в Форматувати мітки даних , зніміть прапорець Значення Y і Показати лідерські лінії параметри, див. скріншот:

19. Потім вам просто потрібно повторити вищевказаний крок 16-18, щоб додати негативні відсоткові мітки (цього разу вам слід додати мітки під точками зменшення даних, виберіть Нижче у підменю Мітки даних in Елементи діаграм), і стовпчасту діаграму із зміною відсотків вдало створено, див. знімок екрана:


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

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

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

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