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

Створіть діаграму із середньою / цільовою лінією в Excel

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

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

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

Щоб створити стовпчасту діаграму із середньою або цільовою лінією по горизонталі, виконайте такі дії:

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

=AVERAGE($B$2:$B$7)
чайові: Якщо ви хочете створити діаграму з цільовою лінією, вам просто потрібно ввести цільове значення в комірки.

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

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

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

5, в Змінити тип діаграми у діалоговому вікні під Виберіть тип діаграми та вісь для вашого ряду даних списку, виберіть Лінійний графік від Тип діаграми випадаючий список для середній серії даних, див. знімок екрана:

6. А потім натисніть OK Ви отримаєте діаграму, як показано на знімку екрана:

7. Далі ви можете додати мітку даних для цього середнього рядка, двічі клацніть, щоб вибрати найбільш правильну точку даних рядка, див. Знімок екрана:

8. А потім натисніть Елементи діаграми, перевірте Мітки даних , а мітка даних вставлена ​​для середнього рядка, див. знімок екрана:

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

Додайте цільовий рядок з різними значеннями в стовпчасту діаграму

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

Щоб створити такий тип діаграми, виконайте наступне:

1. Спочатку виберіть діапазон даних, а потім натисніть Insert > Вставити стовпчик або стовпчасту діаграму > Кластерна колонка щоб вставити діаграму.

2. Потім клацніть правою кнопкою миші цільовий ряд даних (оранжеві смуги) і виберіть Змінити тип діаграми серії з контекстного меню в Змінити тип діаграми діалогове вікно, виберіть Лінійний графік для цільового ряду даних з Тип діаграми випадаючий список, див. знімок екрана:

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

4. У відкритому Форматувати ряд даних під панеллю Заливка та рядок вкладка, виберіть Без рядка від Лінія розділ, див. знімок екрана:

5. Досі в Заливка та рядок , перейдіть на маркер розділ, виберіть Вбудований від Параметри маркера , а потім виберіть горизонтальну смугу в тип випадаюче, нарешті, встановіть розмір, що відповідає ширині ваших смуг у Розмір , див. знімок екрана:

6. А потім встановіть колір для цільових ліній, як вам подобається з Заповнювати розділ, див. знімок екрана:

7. Нарешті, ви можете видалити непотрібні елементи, такі як заголовок діаграми, лінії сітки, як вам потрібно.


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

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

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

Спочатку вставте кілька перемикачів

1. Клацання Розробник > Insert > Кнопка опцій (контроль форми), а потім намалюйте чотири перемикачі та відредагуйте текст за вашими потребами, див. скріншоти:

2. Потім клацніть правою кнопкою миші перший перемикач і виберіть Контроль формату у контекстному меню див. знімок екрана:

3. У відкритому Формат об'єкта у діалоговому вікні під Контроль на вкладці, виберіть клітинку для зв’язку перемикача з Осередок посилання в текстовому полі та натисніть OK щоб закрити його, див. скріншот:

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

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

=VLOOKUP(G2,$A$2:$E$8,$O$1+1,0)
примітки: У наведеній вище формулі, G2 - комірка списку товарів, який ви вставили, A2: E8 - діапазон даних вихідної таблиці даних, O1 - зв’язана комірка перемикача. Ця формула використовується для повернення стовпця даних на основі вибраного перемикача.

5. Тепер, коли ви натискаєте один перемикач, відповідний рік даних відображатиметься автоматично, як показано нижче:

6. А потім, ви можете розрахувати середні значення, використовуючи формулу нижче, див. Скріншот:

=AVERAGE($H$3:$H$8)

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

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


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

У цьому розділі буде показано, як створити гістограму із середнім значенням або цільовим значенням по вертикалі. Будь ласка, зробіть наступне:

1. Спочатку створіть допоміжну таблицю даних, введіть X і Y на дві комірки, а потім, будь ласка, обчисліть середні значення в комірки D2 і D3, див. знімок екрана:

=AVERAGE($B$2:$B$7)
чайові: Якщо ви хочете створити діаграму з цільовою лінією, вам просто потрібно ввести цільове значення в комірки.

2. Потім введіть номер 0 і 1 для комірок E2 та E3 для значень Y, див. знімок екрана:

3. Потім виберіть вихідний діапазон даних і натисніть кнопку Insert > Вставити стовпчик або стовпчасту діаграму > Кластерний бар, див. скріншот:

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

5. В вискочив Виберіть Джерело даних натисніть діалогове вікно додавати , див. знімок екрана:

6. А потім, у Редагувати серію діалогове вікно, введіть назву серії в Назва серії , а потім виділіть комірки зі значеннями X (D2: D3 у цьому прикладі) у Значення серії текстове поле, див. знімок екрана:

7. Клацання OK > OK , щоб закрити діалогові вікна, і новий ряд даних тепер додано до вашої гістограми (дві помаранчеві смужки), а потім клацніть правою кнопкою миші помаранчеву смужку та виберіть Змінити тип діаграми серії, див. скріншот:

8. В вискочив Змінити тип діаграми діалогове вікно, виберіть Розсіяння з плавними лініями від Тип діаграми спадне меню для середній серії даних, див. знімок екрана:

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

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

11. Потім, Редагувати серію вікно підказки вискакує в Значення серії X виберіть дві клітинки X (D2: D3), а потім у Значення серії Y виберіть дві клітинки Y (E2: E3), див. знімок екрана:

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

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

14. У відкритому Формат осі під панеллю Параметри осі вкладка, в Параметри осі розділ, тип 1.0 в Максимальний і ви отримаєте діаграму, як показано нижче:

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

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

Додайте цільовий рядок з різними значеннями на гістограмі

Щоб додати цільовий рядок з різними значеннями на гістограмі, як показано на скріншотах нижче, виконайте наступні кроки за кроком:

1. Виберіть діапазон даних, а потім клацніть Insert > Вставити стовпчик або стовпчасту діаграму > Кластерний бар щоб вставити гістограму, див. знімок екрана:

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

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

4. Досі в Форматувати ряд даних , переключіться на Заливка та рядок вкладка, виберіть Без заповнення від Заповнювати розділ, див. знімок екрана:

5. Далі перейдіть до Ефекти на вкладці тінь розділ, будь ласка, встановіть такі операції:

  • Вкажіть колір, який вам потрібен із колір випадає;
  • Встановіть для розмиття значення 0 пт;
  • Потім відрегулюйте кут на 0 °;
  • Нарешті, змініть відстань на 2pt як вам потрібно.

6. Тепер перейдіть на Параметри серії ще раз, виберіть Вторинна вісь від Параметри серії і змініть ширину зазору на 90% як вам потрібно. Дивіться знімок екрана:

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


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

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

doc kte хід кругової діаграми 1


Завантажте діаграму із середнім / цільовим рядком зразка файлу


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

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

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

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