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

Динамічні та параметризовані пошуки

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

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

Структура SUMPRODUCT

=SUMPRODUCT(array1 * array2 * ...)
  • array1: перший масив для обчислення;
  • array2: другий масив для обчислення;
  • TRUE: перетворюється на 1;
  • FALSE: перетворюється на 0.

Це дозволяє виконувати логічні умови та агрегування в межах однієї формули.

Структура INDIRECT

=INDIRECT(ref_text, [a1])
  • ref_text: текст, що перетворюється на активне посилання;
  • [a1]: необов’язковий аргумент стилю посилання.

INDIRECT дозволяє формулам динамічно змінювати посилання залежно від значень у клітинках.

Крок 1 Створення підсумку за категоріями
expand arrow

На аркуші Summary додайте такі заголовки:

Category
Total_Revenue
Total_Cost
Total_Profit
Крок 2 Генерація списку категорій
expand arrow

У клітинці A10 введіть:

=UNIQUE(Products[Category])

Список категорій тепер автоматично розширюється при додаванні нових категорій.

Крок 3 Обчислення доходу за категорією
expand arrow

У клітинці B10 введіть:

=SUMPRODUCT((XLOOKUP(Sales_Data[Product],Products[Product],Products[Category],"")=A10)*Sales_Data[Revenue])
  • XLOOKUP(...): отримує значення категорії для кожного продукту;
  • =A10: перевіряє, чи співпадає категорія;
  • Sales_Data[Revenue]: значення для агрегування.

Протягніть формулу вниз по стовпцю.

Крок 4 Обчислення витрат за категорією
expand arrow

У клітинці C10 введіть:

=SUMPRODUCT((XLOOKUP(Sales_Data[Product],Products[Product],Products[Category],"")=A10)*XLOOKUP(Sales_Data[Product],Products[Product],Products[Cost],0)*Sales_Data[Units])

Формула динамічно обчислює загальні витрати за категорією.

Крок 5 Обчислення прибутку
expand arrow

У клітинці D10 введіть:

=B10-C10

Протягніть формулу вниз і відформатуйте всі значення відповідно.

Крок 6 Створення селектора сценарію
expand arrow

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

Active Pricing Scenario

Застосуйте перевірку даних із такими опціями:

Pricing_Tiers
Pricing_Tiers_Promo
Крок 7 Створення динамічного пошуку знижки
expand arrow

У таблиці Sales_Data замініть попередню формулу знижки на:

=XLOOKUP([@Units],INDIRECT(Summary!$F$9&"[Min_Units]"),INDIRECT(Summary!$F$9&"[Discount_Rate]"),0,-1)
  • Summary!$F$9: вибрана таблиця сценарію;
  • INDIRECT(...): перетворює текст на активні посилання на таблиці;
  • -1: режим приблизного співпадіння.

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

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

Змініть вибране значення у випадаючому списку сценаріїв.

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

  • Discount_Rate оновлюється автоматично;
  • Discounted_Revenue оновлюється автоматично;
  • Усі залежні обчислення реагують на обрану цінову модель.

1. Яка роль функції SUMPRODUCT у цьому уроці?

2. Чому використовується INDIRECT у параметризованих моделях?

3. Яка основна перевага використання UNIQUE разом із SUMPRODUCT у підсумкових таблицях?

question mark

Яка роль функції SUMPRODUCT у цьому уроці?

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

question mark

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

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

question mark

Яка основна перевага використання UNIQUE разом із SUMPRODUCT у підсумкових таблицях?

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

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

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

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

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

Запитати АІ

expand

Запитати АІ

ChatGPT

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

Динамічні та параметризовані пошуки

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

Структура SUMPRODUCT

=SUMPRODUCT(array1 * array2 * ...)
  • array1: перший масив для обчислення;
  • array2: другий масив для обчислення;
  • TRUE: перетворюється на 1;
  • FALSE: перетворюється на 0.

Це дозволяє виконувати логічні умови та агрегування в межах однієї формули.

Структура INDIRECT

=INDIRECT(ref_text, [a1])
  • ref_text: текст, що перетворюється на активне посилання;
  • [a1]: необов’язковий аргумент стилю посилання.

INDIRECT дозволяє формулам динамічно змінювати посилання залежно від значень у клітинках.

Крок 1 Створення підсумку за категоріями
expand arrow

На аркуші Summary додайте такі заголовки:

Category
Total_Revenue
Total_Cost
Total_Profit
Крок 2 Генерація списку категорій
expand arrow

У клітинці A10 введіть:

=UNIQUE(Products[Category])

Список категорій тепер автоматично розширюється при додаванні нових категорій.

Крок 3 Обчислення доходу за категорією
expand arrow

У клітинці B10 введіть:

=SUMPRODUCT((XLOOKUP(Sales_Data[Product],Products[Product],Products[Category],"")=A10)*Sales_Data[Revenue])
  • XLOOKUP(...): отримує значення категорії для кожного продукту;
  • =A10: перевіряє, чи співпадає категорія;
  • Sales_Data[Revenue]: значення для агрегування.

Протягніть формулу вниз по стовпцю.

Крок 4 Обчислення витрат за категорією
expand arrow

У клітинці C10 введіть:

=SUMPRODUCT((XLOOKUP(Sales_Data[Product],Products[Product],Products[Category],"")=A10)*XLOOKUP(Sales_Data[Product],Products[Product],Products[Cost],0)*Sales_Data[Units])

Формула динамічно обчислює загальні витрати за категорією.

Крок 5 Обчислення прибутку
expand arrow

У клітинці D10 введіть:

=B10-C10

Протягніть формулу вниз і відформатуйте всі значення відповідно.

Крок 6 Створення селектора сценарію
expand arrow

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

Active Pricing Scenario

Застосуйте перевірку даних із такими опціями:

Pricing_Tiers
Pricing_Tiers_Promo
Крок 7 Створення динамічного пошуку знижки
expand arrow

У таблиці Sales_Data замініть попередню формулу знижки на:

=XLOOKUP([@Units],INDIRECT(Summary!$F$9&"[Min_Units]"),INDIRECT(Summary!$F$9&"[Discount_Rate]"),0,-1)
  • Summary!$F$9: вибрана таблиця сценарію;
  • INDIRECT(...): перетворює текст на активні посилання на таблиці;
  • -1: режим приблизного співпадіння.

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

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

Змініть вибране значення у випадаючому списку сценаріїв.

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

  • Discount_Rate оновлюється автоматично;
  • Discounted_Revenue оновлюється автоматично;
  • Усі залежні обчислення реагують на обрану цінову модель.
Все було зрозуміло?

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

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

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