Вирішення реальних задач пошуку
Свайпніть щоб показати меню
Точні збіги корисні для реляційних моделей, але багато бізнес-систем використовують діапазони та порогові значення замість фіксованих величин. У цьому розділі використовується приблизний пошук для побудови логіки цінових рівнів і динамічних розрахунків знижок.
Приблизний збіг із 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")
Тепер пошук оцінює ефективність із урахуванням знижки, а не лише Revenue.
На аркуші 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 зручний для зворотного пошуку?
Дякуємо за ваш відгук!
Запитати АІ
Запитати АІ
Запитайте про що завгодно або спробуйте одне із запропонованих запитань, щоб почати наш чат
