Перейти до основного матеріалу

Досліджуйте нове, Безкоштовна версія Kutools для Outlook сьогодні!

 

Створіть діаграму прогнозу в Excel

Автор: Силувій Остання зміна: 2024-07-29

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

Створіть просту діаграму прогнозу
Створіть діаграму прогнозу на основі порогового значення
Легко створюйте діаграму прогнозів за допомогою дивовижного інструменту
Завантажте зразок файлу
Відео: Створення діаграми прогнозу в Excel

Створіть просту діаграму прогнозу

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

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

Примітка: Біля останньої фактичної комірки даних введіть ті самі дані.

2. Виберіть цілі дані таблиці, натисніть Insert > Вставити діаграму лінії або області > Лінія з маркерами.

3. Клацніть правою кнопкою миші синю лінію (Фактична серія), а потім виберіть Форматувати ряд даних з меню, що клацне правою кнопкою миші.

4. На відкритті Форматувати ряд даних , будь ласка, налаштуйте наступним чином.

4.1) Клацніть на Заливка та рядок піктограма;
4.2) Клацніть Маркер;
4.3) Розгорніть Параметри маркера, виберіть Вбудований параметр, залиште символ круглої крапки виділеним у тип випадаюче, замініть початковий розмір на 10 в Розмір коробка

5. Тепер вам потрібно налаштувати ряд прогнозування. Виділіть помаранчеву лінію (ряд передбачень), а потім у Форматувати ряд даних панелі, виконайте наступне.

5.1) Клацніть на Заливка та рядок піктограма;
5.2) Клацніть Маркер;
5.3) У Параметри маркера розділ, виберіть Вбудований Залиште вибраним символ круглої крапки, а потім змініть розмір на 10 іп Розмір коробка;
5.4) У Заповнювати розділ, виберіть білий в Колір випадаючий список;

5.5) У Кордон розділ, змініть ширину межі на 1.5pt в ширина коробка;
5.6) У Тире тире випадаючий список, виберіть Площа Точка;

5.7) Перейдіть до Лінія Вкладка, а потім виберіть Квадратна точка від Тип тире випадаючий список.

6. Оскільки дані вересня є актуальними, нам потрібно налаштувати цей маркер для відображення як Фактична серія.

Клацніть на цей маркер двічі, щоб лише вибрати його, та в Форматувати точку даних окремо виберіть те саме Заповнювати колір і Кордон колір як колір Фактичний серію, а потім виберіть Solid від Тире тире випадаючий список.

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

7. Змініть заголовок діаграми, як вам потрібно. Тоді проста діаграма прогнозів заповнюється.

Створіть діаграму прогнозу на основі порогового значення

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

1. Додайте два нові стовпці до вихідного діапазону даних, які окремо містять дані прогнозування та порогове значення. Дивіться знімок екрана:

Примітка: Біля останньої фактичної комірки даних введіть те саме значення.

2. Вам потрібно створити проміжну таблицю даних на основі вихідного діапазону даних. Ця таблиця буде використана для створення діаграми прогнозу.

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

2.2) Виділіть першу порожню клітинку другого стовпця (фактичний стовпець), введіть у неї формулу нижче та натисніть Що натомість? Створіть віртуальну версію себе у ключ. Потім перетягніть Ручка автозаповнення клітинки результату вниз, поки вона не досягне нижньої частини таблиці.
= IF (B2, B2, NA ())

2.3) Виділіть першу порожню клітинку стовпця Прогнозування, введіть в неї формулу нижче та натисніть Що натомість? Створіть віртуальну версію себе у ключ. Потім перетягніть Ручка автозаповнення клітинки результату вниз, поки вона не досягне нижньої частини таблиці.
= IF (B2, IF (B3, NA (), B2), C2)

2.4) Виділіть першу порожню клітинку четвертого стовпця, введіть в неї формулу нижче та натисніть Що натомість? Створіть віртуальну версію себе у ключ. Потім перетягніть Ручка автозаповнення результату комірки до кінця таблиці.
= $ D $ 2

2.5) У п'ятому стовпці введіть формулу нижче в першу порожню клітинку, а потім натисніть Що натомість? Створіть віртуальну версію себе у ключ. Потім застосуйте цю формулу до інших комірок, перетягнувши її маркер автозаповнення до кінця таблиці.
= IF (B18> D18, NA (), B18)

Примітка: У наведених формулах усі опорні комірки належать до вихідного діапазону даних.

3. Виберіть всю таблицю проміжних даних, натисніть Insert > Вставити діаграму лінії або області > Лінія з маркерами.

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

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

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

5.1) Клацніть на Заливка та рядок піктограма;
5.2) Клацніть на маркер вкладка;
5.3) Розгорніть Параметри маркера розділ, виберіть Вбудований варіант, залиште круглу крапку виділеною в тип , а потім змініть Розмір до 10;
5.4) У Заповнювати у розділі вкажіть новий колір маркера. Тут я вибираю червоний колір, щоб перевершити дані нижче порогової лінії;
5.5) У Кордон у розділі вкажіть той самий колір, що і колір заливки;

5.6) Клацніть на Лінія , а потім виберіть Без рядка опція в Лінія .

Тепер діаграма відображається так, як показано на скріншоті нижче.

6. Виберіть порогову лінію на графіку та в Форматувати ряд даних виконайте наступне, щоб приховати маркери та змінити колір лінії.

6.1) Клацніть на Заливка та рядок піктограма;
6.2) Клацніть на маркер вкладка;
6.3) У Параметри маркера розділ, виберіть ніхто варіант;
6.4) Клацніть на Лінія , а потім виберіть новий колір для рядка. Дивіться скріншоти:

7. Змініть заголовок діаграми, як вам потрібно.

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

Легко створюйте діаграму прогнозу в Excel

Команда Діаграма прогнозу корисність Kutools для Excel може допомогти вам швидко створити просту діаграму прогнозу або діаграму прогнозу на основі порогового значення в Excel за допомогою декількох клацань лише як показано нижче.
Завантажте та спробуйте зараз! 30-денний безкоштовний трейл

Завантажте зразок файлу

Відео: Створення діаграми прогнозу в Excel

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

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

🤖 Kutools AI Aide: Революціонізуйте аналіз даних на основі: Інтелектуальне виконання   |  Згенерувати код  |  Створення спеціальних формул  |  Аналізуйте дані та створюйте діаграми  |  Викликати функції Kutools...
Популярні функції: Знайдіть, виділіть або визначте дублікати  |  Видалити порожні рядки  |  Об’єднайте стовпці або клітинки без втрати даних  |  Раунд без Формули ...
Супер VLookup: Кілька критеріїв  |  Множинне значення  |  На кількох аркушах  |  Нечіткий пошук...
Adv. Випадаючий список: Легкий спадний список  |  Залежний спадний список  |  Виберіть розкривний список, що вибирається...
Менеджер колонок: Додайте конкретну кількість стовпців  |  Перемістити стовпці  |  Перемкнути статус видимості прихованих стовпців  |  Порівняти стовпці з Виберіть однакові та різні клітини ...
Особливості: Фокус сітки  |  Перегляд дизайну  |  Велика панель формул  |  Диспетчер робочих книг і аркушів | Бібліотека ресурсів (автотекст)  |  Вибір дати  |  Об’єднайте робочі аркуші  |  Шифрування/розшифрування клітинок  |  Надсилайте листи за списком  |  Супер фільтр  |  Спеціальний фільтр (фільтр жирний/курсив/закреслений...) ...
Топ-15 наборів інструментів:  12 текст Tools (додати текст, Видалити символи ...)  |  50 + Графік типи (діаграма Ганта ...)  |  40+ Практичний Формули (Розрахуйте вік на основі дня народження ...)  |  19 вставка Tools (Вставте QR-код, Вставити зображення зі шляху ...)  |  12 Перетворення Tools (Числа до слів, Валютна конверсія ...)  |  7 Злиття та розділення Tools (Розширені комбіновані ряди, Розділити клітинки Excel ...)  |  ... і більше

Kutools для Excel має понад 300 функцій, Переконайтеся, що те, що вам потрібно, знаходиться лише на відстані одного кліка...

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

  • Одна секунда для перемикання між десятками відкритих документів!
  • Щодня зменшуйте сотні клацань мишею, прощайте руку миші.
  • Збільшує вашу продуктивність на 50% під час перегляду та редагування декількох документів.
  • Додає ефективні вкладки в Office (включно з Excel), як у Chrome, Edge та Firefox.