Notice: This page requires JavaScript to function properly.
Please enable JavaScript in your browser settings or update your browser.Вивчайте Динамічні та параметризовані пошуки | Розширені системи пошуку та реляційне моделювання
Формули 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 у підсумкових таблицях?

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

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

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

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

Секція 3. Розділ 5

Запитати АІ

expand

Запитати АІ

ChatGPT

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

Секція 3. Розділ 5
some-alt