Функція Microsoft Excel XLOOKUP є ефективним інструментом пошуку, який дозволяє знаходити певні значення в діапазоні клітинок. Він замінює попередні функції пошуку, такі як VLOOKUP, і усуває багато їхніх обмежень. Ця стаття містить покрокові інструкції щодо ефективного використання XLOOKUP.
Що таке функція XLOOKUP і як вона працює?
Excel надає кілька інструментів для пошуку в даних, зокрема вбудований інструмент пошуку та вибору та правила умовного форматування. Однак ці характеристики не роблять великої різниці у вашій електронній таблиці чи спрощують посилання на дані в інших формулах. Функції пошуку, такі як XLOOKUP, особливо корисні в таких випадках.
Чому нам потрібні функції LOOKUP
Під час аналізу даних часто виникають випадки, коли вам потрібно шукати дані за допомогою спеціальних критеріїв пошуку, які регулярно змінюються. Аналітики даних часто використовують запити, які можна легко налаштувати для отримання конкретних результатів із набору даних.
VLOOKUP і HLOOKUP — обмеження та рішення
Діапазон традиційних функцій пошуку, таких як HLOOKUP (горизонтальний пошук) і VLOOKUP (вертикальний пошук), обмежений. Серед інших обмежень їм потрібно знайти рішення для пошуку даних ліворуч або праворуч від значень. Щоб подолати ці обмеження, XLOOKUP дозволяє шукати дані в рядках і стовпцях як ліворуч, так і праворуч від значення пошуку.
Рішення — XLOOKUP
XLOOKUP може обробляти приблизні збіги, численні пошукові значення, вкладені запити, спеціальні повідомлення про помилки тощо. Він доступний лише для користувачів, які підписалися на Microsoft 365, але швидко змінив правила пошуку як базових, так і складних даних.
Як працює функція XLOOKUP у Microsoft Excel?
Щоб пояснити функцію XLOOKUP, скористаємося прикладом. Уявіть собі електронну таблицю Excel, яка містить список співробітників зі стовпцями для ідентифікатора працівника, імені, електронної адреси та посади.
Такий набір даних можна використовувати як каталог із можливістю пошуку в Excel за допомогою формули XLOOKUP. Значенням пошуку для цієї формули може бути будь-яке зі значень у цих стовпцях.
Базовий синтаксис XLOOKUP
Основний синтаксис функції XLOOKUP:
- пошукове_значення:
Значення, яке потрібно шукати.
- масив_пошуку:
Діапазон або масив для пошуку.
- return_array:
Діапазон або масив, з якого потрібно повернути результат.
- [if_not_found]:(Необов’язковий) Значення, яке повертається, якщо відповідності не знайдено.
Приклад сценарію
Розглянемо електронну таблицю Excel із чотирма стовпцями: ідентифікатор працівника, ім’я, електронна адреса та посада.
Ви хочете знайти адресу електронної пошти працівника з ідентифікатором 103. Ось як це зробити за допомогою XLOOKUP:
- Виберіть порожню комірку
Щоб вставити функцію XLOOKUP, почніть із відкриття електронної таблиці Excel і вибору порожньої клітинки. Натисніть рядок формул на стрічці, щоб почати редагування.
- Визначте критерії пошуку
Почніть вводити формулу на панелі стрічки:
- lookup_value: 103
- lookup_array: A2
(діапазон, що містить ідентифікатори працівників)
- return_array: C2
(діапазон, що містить адреси електронної пошти)
Ця формула шукатиме ідентифікатор 103 у діапазоні A2
і повернути відповідний електронний лист із діапазону C2
, тобто "jim@example.com."
Необов'язкові аргументи
- не знайдено:
Якщо значення не знайдено, ви можете вказати спеціальне повідомлення або значення.
- match_mode: Укажіть тип відповідності.
- search_mode: Вкажіть порядок пошуку.
Розширені функції XLOOKUP
XLOOKUP пропонує кілька додаткових функцій:
1. Повернення кількох стовпців
XLOOKUP може повертати кілька стовпців даних. Припустімо, вам потрібна і електронна адреса, і робоча роль для даного працівника:
Ця формула повертає масив {"jim@example.com", "Designer"}.
2. Вкладений XLOOKUP
Ви можете вкладати функції XLOOKUP для складних запитів. Наприклад, пошук посади працівника на основі його електронної пошти:
Ця формула спочатку знаходить ідентифікатор працівника за допомогою електронної пошти, а потім знаходить посаду за цим ідентифікатором.
Що потрібно врахувати перед використанням XLOOKUP
- Розташування даних:
XLOOKUP може шукати дані, розташовані як горизонтально, так і вертикально.
- Обробка помилок:
Використовуйте аргумент not_found для ефективної обробки невідповідних результатів.
- Діапазон розмірів:
Переконайтеся, що lookup_array і return_array мають однаковий розмір, щоб уникнути помилок.
- Сумісність:
XLOOKUP доступний лише в Microsoft 365 і новіших версіях Excel.
Покроковий посібник із використання XLOOKUP в Excel
Щоб створити формулу за допомогою XLOOKUP, виконайте такі дії:
1. Виберіть порожню комірку:
Відкрийте електронну таблицю Excel і виберіть порожню клітинку.
2. Введіть формулу:
Почніть вводити =XLOOKUP( у рядку формул.
3. Вставте критерії пошуку:
Укажіть значення пошуку, масив пошуку та масив повернення.
4. Додайте додаткові аргументи:
Включіть not_found, match_mode і search_mode, якщо потрібно.
5. Натисніть Enter:
Закрийте формулу круглою дужкою та натисніть Enter, щоб побачити результат.
Заключні думки
Функція XLOOKUP Excel — це універсальний і ефективний інструмент для аналізу даних. У порівнянні зі стандартними операціями пошуку, він пропонує більшу гнучкість, спрощує складні запити та покращує ефективність роботи з керування даними. Навчившись використовувати XLOOKUP, ви можете скористатися всіма перевагами функцій Excel і значно покращити свої навички аналізу даних. XLOOKUP є життєво важливим інструментом для будь-якого користувача Excel або аналітика даних, незалежно від того, чи працюють вони з простими списками чи складними наборами даних.
