Створіть діаграму із середньою / цільовою лінією в Excel
У Excel може бути загальним завданням вставити горизонтальну або вертикальну лінію на діаграму, горизонтальна або вертикальна лінія може посилатися на якесь цільове або середнє значення. Цей тип рядків дозволяє легко порівняти фактичні значення з цільовим або середнім значенням, і за допомогою цього рядка ви можете легко побачити, де значення знаходяться вище і нижче цього контрольного значення, як показано на скріншотах нижче. У цій статті я розповім про те, як створити стовпчик або стовпчасту діаграму із середнім або цільовим рядком в Excel.
| Стовпчаста діаграма з горизонтальною лінією | Гістограма з вертикальною лінією |
 |
 |
- Створіть стовпчасту діаграму із середньою або цільовою лінією по горизонталі
- Додайте цільовий рядок з різними значеннями в стовпчасту діаграму
- Створюйте стовпчасту діаграму із середньою лінією динамічно за допомогою перемикачів
- Створіть гістограму із середньою або цільовою лінією по вертикалі
- Додайте цільовий рядок з різними значеннями на гістограмі
- Додайте цільову / середню лінію до стовпця або стовпчастої діаграми за допомогою зручної функції
- Завантажте діаграму із середнім / цільовим рядком зразка файлу
Створіть стовпчасту діаграму із середньою або цільовою лінією по горизонталі
Щоб створити стовпчасту діаграму із середньою або цільовою лінією по горизонталі, виконайте такі дії:
1. Якщо ви хочете створити стовпчасту діаграму із середнім рядком, спочатку слід обчислити середнє значення списку даних, введіть формулу нижче в порожню комірку, а потім перетягніть маркер автоматичного заповнення, щоб скопіювати формулу в інший клітинки, див. скріншот:
2. Потім виберіть діапазон даних, включаючи середні значення, а потім натисніть Insert > Вставити стовпчик або стовпчасту діаграму > Кластерна колонка, див. скріншот:
3. І на аркуші вставляється стовпчаста діаграма, ви можете видалити непотрібні елементи, такі як заголовок діаграми, лінії сітки, як вам потрібно, див. Скріншоти:
 |
 |
 |
4. Потім клацніть правою кнопкою миші середній ряд даних (оранжеві смуги) та виберіть Змінити тип діаграми серії у контекстному меню див. знімок екрана:
5, в Змінити тип діаграми у діалоговому вікні під Виберіть тип діаграми та вісь для вашого ряду даних списку, виберіть Лінійний графік від Тип діаграми випадаючий список для середній серії даних, див. знімок екрана:
6. А потім натисніть OK Ви отримаєте діаграму, як показано на знімку екрана:
7. Далі ви можете додати мітку даних для цього середнього рядка, двічі клацніть, щоб вибрати найбільш правильну точку даних рядка, див. Знімок екрана:
8. А потім натисніть Елементи діаграми, перевірте Мітки даних , а мітка даних вставлена для середнього рядка, див. знімок екрана:
Додайте цільовий рядок з різними значеннями в стовпчасту діаграму
Іноді вам може знадобитися порівняти фактичні значення з цільовими значеннями, які різні для кожного рядка, у цьому випадку, щоб чіткіше візуалізувати цільові значення, нижченаведений тип діаграми може зробити вам послугу.
 |
|
 |
Щоб створити такий тип діаграми, виконайте наступне:
1. Спочатку виберіть діапазон даних, а потім натисніть Insert > Вставити стовпчик або стовпчасту діаграму > Кластерна колонка щоб вставити діаграму.
2. Потім клацніть правою кнопкою миші цільовий ряд даних (оранжеві смуги) і виберіть Змінити тип діаграми серії з контекстного меню в Змінити тип діаграми діалогове вікно, виберіть Лінійний графік для цільового ряду даних з Тип діаграми випадаючий список, див. знімок екрана:
3. Потім натисніть кнопку OK , а потім двічі клацніть цільовий рядок і виберіть Форматувати ряд даних, див. скріншот:
4. У відкритому Форматувати ряд даних під панеллю Заливка та рядок вкладка, виберіть Без рядка від Лінія розділ, див. знімок екрана:
5. Досі в Заливка та рядок , перейдіть на маркер розділ, виберіть Вбудований від Параметри маркера , а потім виберіть горизонтальну смугу в тип випадаюче, нарешті, встановіть розмір, що відповідає ширині ваших смуг у Розмір , див. знімок екрана:
6. А потім встановіть колір для цільових ліній, як вам подобається з Заповнювати розділ, див. знімок екрана:
7. Нарешті, ви можете видалити непотрібні елементи, такі як заголовок діаграми, лінії сітки, як вам потрібно.
Створюйте стовпчасту діаграму із середньою лінією динамічно за допомогою перемикачів
Якщо у вас є велика таблиця з продажами товару протягом декількох років, зараз ви хочете показати середню лінію за кожен рік на графіку. Для вирішення цього завдання ви можете використовувати діаграму із середньою лінією, натиснувши перемикач, як показано нижче. При виборі відповідного року графік із середнім рядком відображатиметься динамічно.
Щоб створити цю динамічну діаграму, спочатку слід вставити кілька перемикачів, а потім створити допоміжну таблицю даних, на основі якої базується діаграма, нарешті, вставити посилання діаграми на перемикач. Будь ласка, виконайте такі дії:
Спочатку вставте кілька перемикачів
1. Клацання Розробник > Insert > Кнопка опцій (контроль форми), а потім намалюйте чотири перемикачі та відредагуйте текст за вашими потребами, див. скріншоти:
 |
|
 |
2. Потім клацніть правою кнопкою миші перший перемикач і виберіть Контроль формату у контекстному меню див. знімок екрана:
3. У відкритому Формат об'єкта у діалоговому вікні під Контроль на вкладці, виберіть клітинку для зв’язку перемикача з Осередок посилання в текстовому полі та натисніть OK щоб закрити його, див. скріншот:
По-друге, вставте допоміжну таблицю даних для створення діаграми на основі
4. Скопіюйте назви товарів з вихідної таблиці в новий діапазон, а потім застосуйте формулу нижче в порожню комірку поруч із новими скопійованими даними, а потім перетягніть маркер заповнення, щоб отримати результат, див. Знімок екрана:
5. Тепер, коли ви натискаєте один перемикач, відповідний рік даних відображатиметься автоматично, як показано нижче:
6. А потім, ви можете розрахувати середні значення, використовуючи формулу нижче, див. Скріншот:
По-третє, створіть діаграму на основі нової таблиці допоміжних даних
Потім виберіть нові створені дані, а потім застосуйте вищезазначене крок 2 - етап 8 способу 1 для створення стовпчикової діаграми із середньою лінією, і ви отримаєте діаграму із середньою лінією динамічно. Дивіться нижче демонстрацію:
Створіть гістограму із середньою або цільовою лінією по вертикалі
У цьому розділі буде показано, як створити гістограму із середнім значенням або цільовим значенням по вертикалі. Будь ласка, зробіть наступне:
1. Спочатку створіть допоміжну таблицю даних, введіть X та Y на дві комірки, а потім, будь ласка, обчисліть середні значення в комірки D2 і D3, див. знімок екрана:
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 для безкоштовної пробної версії!
Завантажте діаграму із середнім / цільовим рядком зразка файлу
Найкращі інструменти для підвищення продуктивності офісу
Kutools для Excel - допомагає виділитися з натовпу
Kutools для Excel має понад 300 функцій, Переконайтеся, що те, що вам потрібно, знаходиться лише на відстані одного кліка...
Вкладка Office - увімкніть читання та редагування вкладок у Microsoft Office (включаючи Excel)
- Одна секунда для перемикання між десятками відкритих документів!
- Щодня зменшуйте сотні клацань мишею, прощайте руку миші.
- Збільшує вашу продуктивність на 50% під час перегляду та редагування декількох документів.
- Додає ефективні вкладки в Office (включно з Excel), як у Chrome, Edge та Firefox.
