Notice: This page requires JavaScript to function properly.
Please enable JavaScript in your browser settings or update your browser.Вивчайте Динамічний механізм KPI | Формули Excel
Формули Excel

Динамічний механізм KPI

Свайпніть щоб показати меню

Робоча книга вже підтримує звітність, моделювання сценаріїв та аналіз чутливості. У цьому розділі ці системи об'єднуються в централізований KPI-двигун, де всі результати динамічно реагують на введення користувача.

Структура KPI-двигуна

Динамічна система KPI розділяє робочу книгу на три рівні:

  • Вхідні дані: вибір користувача;
  • Логіка: обчислення та формули;
  • Вихідні дані: відображені KPI.

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

Структура SUMIFS і COUNTIFS

=SUMIFS(sum_range, criteria_range1, criteria1, ...)
=COUNTIFS(criteria_range1, criteria1, ...)

Ці функції агрегують значення динамічно на основі вибраних фільтрів.

Логіка FILTER, MAX та XLOOKUP

Обчислення для топ-продукту поєднують кілька функцій:

FILTER → MAX → XLOOKUP
  • FILTER: ізолює відповідні рядки;
  • MAX: визначає найбільше значення;
  • XLOOKUP: отримує відповідну мітку.
Крок 1 Створення KPI-панелі
expand arrow

На аркуші Summary створіть новий розділ:

Dynamic KPI Panel
Крок 2 Створення вхідного рівня
expand arrow

Побудуйте таку структуру введення:

ВведенняДжерело
Активний сценарійScenarios!B7
Обраний регіонReference_Lists dropdown
Обраний місяцьЦіле число (1–12)
Крок 3 Обчислення відфільтрованого доходу
expand arrow

Введіть:

=SUMIFS(Sales_Data[Revenue],Sales_Data[Region],[SelectedRegion],Sales_Data[Month],[SelectedMonth])
  • Sales_Data[Region]: фільтр за регіоном;
  • Sales_Data[Month]: фільтр за місяцем.

Тепер KPI динамічно реагує на обидва вибори.

Крок 4 Обчислення відфільтрованих замовлень
expand arrow

Введіть:

=COUNTIFS(Sales_Data[Region],[SelectedRegion],Sales_Data[Month],[SelectedMonth])

KPI повертає кількість відповідних транзакцій.

Крок 5 Обчислення середньої вартості замовлення
expand arrow

Введіть:

=IFERROR([FilteredRevenue]/[FilteredOrders],0)
  • [FilteredRevenue]: відфільтрований KPI доходу;
  • [FilteredOrders]: відфільтрована кількість замовлень;
  • 0: резервне значення, якщо замовлень немає.
Крок 6 Пошук топ-продукту
expand arrow

Введіть:

=XLOOKUP(MAX(FILTER(Sales_Data[Revenue],Sales_Data[Region]=[SelectedRegion])),Sales_Data[Revenue],Sales_Data[Product],"No data")
  • FILTER(...): ізолює обраний регіон;
  • MAX(...): визначає найбільше значення доходу;
  • XLOOKUP(...): повертає відповідний продукт.
Крок 7 Побудова прогнозованого доходу
expand arrow

Введіть:

=SUMIFS(Sales_Data[Revenue],Sales_Data[Region],[SelectedRegion],Sales_Data[Month],[SelectedMonth]) * Scenarios!Active Price Multiplier * Scenarios!Active Volume Multiplier
  • Базовий дохід: відфільтрований транзакційний результат;
  • Множник ціни: коригування активного сценарію;
  • Множник обсягу: коригування активного сценарію.

Тепер KPI моделює прогнозований дохід динамічно.

Крок 8 Тестування повної системи KPI
expand arrow

Змініть:

  • Регіон;
  • Місяць;
  • Сценарій.

Переконайтеся, що:

  • Дохід оновлюється автоматично;
  • Кількість замовлень оновлюється автоматично;
  • Середня вартість замовлення оновлюється автоматично;
  • Топ-продукт оновлюється автоматично;
  • Прогнозований дохід перераховується миттєво.

1. Яка основна мета KPI-двигуна?

2. Чому використовується IFERROR у розрахунках KPI, таких як Середня вартість замовлення?

3. Чому використовується FILTER у розрахунку Топ продукту?

question mark

Яка основна мета KPI-двигуна?

Виберіть правильну відповідь

question mark

Чому використовується IFERROR у розрахунках KPI, таких як Середня вартість замовлення?

Виберіть правильну відповідь

question mark

Чому використовується FILTER у розрахунку Топ продукту?

Виберіть правильну відповідь

Все було зрозуміло?

Як ми можемо покращити це?

Дякуємо за ваш відгук!

Секція 1. Розділ 25

Запитати АІ

expand

Запитати АІ

ChatGPT

Запитайте про що завгодно або спробуйте одне із запропонованих запитань, щоб почати наш чат

Динамічний механізм KPI

Робоча книга вже підтримує звітність, моделювання сценаріїв та аналіз чутливості. У цьому розділі ці системи об'єднуються в централізований KPI-двигун, де всі результати динамічно реагують на введення користувача.

Структура KPI-двигуна

Динамічна система KPI розділяє робочу книгу на три рівні:

  • Вхідні дані: вибір користувача;
  • Логіка: обчислення та формули;
  • Вихідні дані: відображені KPI.

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

Структура SUMIFS і COUNTIFS

=SUMIFS(sum_range, criteria_range1, criteria1, ...)
=COUNTIFS(criteria_range1, criteria1, ...)

Ці функції агрегують значення динамічно на основі вибраних фільтрів.

Логіка FILTER, MAX та XLOOKUP

Обчислення для топ-продукту поєднують кілька функцій:

FILTER → MAX → XLOOKUP
  • FILTER: ізолює відповідні рядки;
  • MAX: визначає найбільше значення;
  • XLOOKUP: отримує відповідну мітку.
Крок 1 Створення KPI-панелі
expand arrow

На аркуші Summary створіть новий розділ:

Dynamic KPI Panel
Крок 2 Створення вхідного рівня
expand arrow

Побудуйте таку структуру введення:

ВведенняДжерело
Активний сценарійScenarios!B7
Обраний регіонReference_Lists dropdown
Обраний місяцьЦіле число (1–12)
Крок 3 Обчислення відфільтрованого доходу
expand arrow

Введіть:

=SUMIFS(Sales_Data[Revenue],Sales_Data[Region],[SelectedRegion],Sales_Data[Month],[SelectedMonth])
  • Sales_Data[Region]: фільтр за регіоном;
  • Sales_Data[Month]: фільтр за місяцем.

Тепер KPI динамічно реагує на обидва вибори.

Крок 4 Обчислення відфільтрованих замовлень
expand arrow

Введіть:

=COUNTIFS(Sales_Data[Region],[SelectedRegion],Sales_Data[Month],[SelectedMonth])

KPI повертає кількість відповідних транзакцій.

Крок 5 Обчислення середньої вартості замовлення
expand arrow

Введіть:

=IFERROR([FilteredRevenue]/[FilteredOrders],0)
  • [FilteredRevenue]: відфільтрований KPI доходу;
  • [FilteredOrders]: відфільтрована кількість замовлень;
  • 0: резервне значення, якщо замовлень немає.
Крок 6 Пошук топ-продукту
expand arrow

Введіть:

=XLOOKUP(MAX(FILTER(Sales_Data[Revenue],Sales_Data[Region]=[SelectedRegion])),Sales_Data[Revenue],Sales_Data[Product],"No data")
  • FILTER(...): ізолює обраний регіон;
  • MAX(...): визначає найбільше значення доходу;
  • XLOOKUP(...): повертає відповідний продукт.
Крок 7 Побудова прогнозованого доходу
expand arrow

Введіть:

=SUMIFS(Sales_Data[Revenue],Sales_Data[Region],[SelectedRegion],Sales_Data[Month],[SelectedMonth]) * Scenarios!Active Price Multiplier * Scenarios!Active Volume Multiplier
  • Базовий дохід: відфільтрований транзакційний результат;
  • Множник ціни: коригування активного сценарію;
  • Множник обсягу: коригування активного сценарію.

Тепер KPI моделює прогнозований дохід динамічно.

Крок 8 Тестування повної системи KPI
expand arrow

Змініть:

  • Регіон;
  • Місяць;
  • Сценарій.

Переконайтеся, що:

  • Дохід оновлюється автоматично;
  • Кількість замовлень оновлюється автоматично;
  • Середня вартість замовлення оновлюється автоматично;
  • Топ-продукт оновлюється автоматично;
  • Прогнозований дохід перераховується миттєво.
Все було зрозуміло?

Як ми можемо покращити це?

Дякуємо за ваш відгук!

Секція 1. Розділ 25
some-alt