Розширення XLOOKUP для реальних сценаріїв використання
Свайпніть щоб показати меню
Базові пошуки корисні для зв’язування таблиць, але реальні аналітичні системи часто потребують більш складної логіки отримання даних. У цьому розділі розширення XLOOKUP для підтримки багатостовпчикових результатів, динамічних робочих процесів ранжування та зворотного пошуку.
Багатостовпчиковий XLOOKUP
XLOOKUP може повертати кілька стовпців одночасно, якщо масив результату охоплює декілька стовпців.
Формула, використана в цьому розділі:
=XLOOKUP(E2,Products[Product],Products[[Category]:[Price]],"Not found")
E2: значення для пошуку;Products[Product]: стовпець для пошуку;Products[[Category]:[Price]]: масив для повернення з кількох стовпців;"Not found": значення для випадку, якщо не знайдено.
Результат розливається горизонтально на кілька стовпців.
Структура MAX
=MAX(array)
array: числові значення для оцінки.
Формула, використана в цьому розділі:
=MAX(Sales_Data[Revenue])
Повертає найбільше значення Revenue у наборі даних.
Зворотний пошук із XLOOKUP
=XLOOKUP(J2,Sales_Data[Sales_Rep],Sales_Data[Order_ID],"Not found",0,-1)
0: режим точного співпадіння;-1: пошук від останнього до першого.
Повертає найновіший відповідний запис замість першого.
На аркуші Summary створіть невеликий розділ для пошуку продукту.
У клітинці F2 введіть:
=XLOOKUP(E2,Products[Product],Products[[Category]:[Price]],"Not found")
Деталі продукту розливаються горизонтально на кілька стовпців.
Змініть назву продукту у клітинці E2.
Зверніть увагу, що всі пов’язані атрибути оновлюються автоматично.
У окремій клітинці введіть:
=MAX(Sales_Data[Revenue])
Це визначає найбільшу суму транзакції у наборі даних.
У клітинці E2 введіть:
=XLOOKUP(MAX(Sales_Data[Revenue]),Sales_Data[Revenue],Sales_Data[Product],"Not found")
MAX(Sales_Data[Revenue]): найбільше значення Revenue;Sales_Data[Revenue]: стовпець для пошуку;Sales_Data[Product]: повернутий продукт.
Формула динамічно отримує продукт, пов’язаний із транзакцією з найбільшим доходом.
У клітинці F2 введіть:
=XLOOKUP(E2,Products[Product],Products[[Category]:[Price]],"Not found")
Повний профіль продукту тепер оновлюється динамічно разом із топовим продуктом.
Створіть розділ введення Sales Rep. У клітинці з результатом пошуку введіть:
=XLOOKUP(J2,Sales_Data[Sales_Rep],Sales_Data[Order_ID],"Not found",0,-1)
Формула тепер повертає найновіше замовлення для вибраного торгового представника.
Змініть значення Sales Rep у клітинці J2.
Переконайтеся, що:
- Пошук завжди повертає останнє відповідне замовлення;
- Повторювані значення більше не повертають лише перше співпадіння.
1. Що відбувається, коли XLOOKUP використовує масив для повернення з кількох стовпців?
2. Чому функцію MAX поєднують з XLOOKUP в аналітичних моделях?
3. Який ефект має використання search_mode = -1 у XLOOKUP?
Дякуємо за ваш відгук!
Запитати АІ
Запитати АІ
Запитайте про що завгодно або спробуйте одне із запропонованих запитань, щоб почати наш чат
