Вирішення реальних задач пошуку
Свайпніть щоб показати меню
Точні співпадіння корисні для реляційних моделей, але багато бізнес-систем використовують діапазони та порогові значення замість фіксованих величин. У цьому розділі використовується приблизний пошук для побудови логіки ціноутворення за рівнями та динамічного розрахунку знижок.
Приблизний пошук з XLOOKUP
=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], -1)
lookup_value: значення для перевірки;lookup_array: порогові значення;return_array: повернуте значення;-1: повертає найбільше значення, яке менше або дорівнює шуканому значенню.
Для приблизного пошуку масив для пошуку повинен бути відсортований за зростанням.
Логіка за рівнями
Таблиці рівнів визначають мінімальні порогові значення замість точних співпадінь.
Приклад:
| Min_Units | Discount_Rate |
|---|---|
| 1 | 0% |
| 5 | 5% |
| 10 | 10% |
| 20 | 15% |
Excel визначає, куди значення Units потрапляє у структуру порогів, і повертає відповідну ставку знижки.
Створіть новий аркуш з назвою:
Pricing_Tiers
Додайте наступний набір даних:
Min_Units;Discount_Rate
1;0%
5;5%
10;10%
20;15%
Перетворіть набір даних на таблицю Excel.
Переконайтеся, що Min_Units відсортовано за зростанням.
У Sales_Data створіть новий стовпець з назвою:
Discount_Rate
У першому рядку введіть:
=XLOOKUP([@Units],Pricing_Tiers[Min_Units],Pricing_Tiers[Discount_Rate],0,-1)
[@Units]: значення Units у поточному рядку;Pricing_Tiers[Min_Units]: стовпець порогових значень;Pricing_Tiers[Discount_Rate]: повернуте значення знижки;0: значення за замовчуванням;-1: режим приблизного пошуку.
Відформатуйте результати як відсотки.
Створіть новий стовпець з назвою:
Discounted_Revenue
У першому рядку введіть:
=[@Revenue]*(1-[@Discount_Rate])
[@Revenue]: початкове значення Revenue;[@Discount_Rate]: застосований відсоток знижки.
Розрахунок тепер динамічно змінюється залежно від обсягу одиниць.
Змініть значення у Pricing_Tiers.
Переконайтеся, що всі залежні обчислення оновлюються автоматично.
Замініть попередню формулу для топ-продукту на:
=XLOOKUP(MAX(Sales_Data[Discounted_Revenue]),Sales_Data[Discounted_Revenue],Sales_Data[Product],"Not found")
Пошук тепер оцінює ефективність з урахуванням знижки, а не лише початкового доходу.
На аркуші Summary створіть секцію введення для:
Order_ID
У клітинці з результатом пошуку введіть:
=XLOOKUP(M2,Sales_Data[Order_ID],Sales_Data[Customer_ID],"Not found")
M2: введений Order_ID;Sales_Data[Order_ID]: стовпець для пошуку;Sales_Data[Customer_ID]: повернуте значення.
В іншій клітинці з результатом введіть:
=XLOOKUP(M2,Sales_Data[Order_ID],Sales_Data[Product],"Not found")
Формули тепер динамічно отримують деталі транзакції за вибраним Order_ID.
Змініть значення у M2.
Переконайтеся, що:
- Значення клієнта оновлюються автоматично;
- Значення продукту оновлюються автоматично;
- Формули завжди повертають відповідну транзакцію.
1. Що робить match_mode = -1 у XLOOKUP?
2. Чому масив для пошуку має бути відсортований при використанні приблизного співпадіння?
3. Що робить XLOOKUP придатним для зворотного пошуку?
Дякуємо за ваш відгук!
Запитати АІ
Запитати АІ
Запитайте про що завгодно або спробуйте одне із запропонованих запитань, щоб почати наш чат
