Що вам потрібно знати про XLOOKUP
Завдяки XVERWEIS Microsoft пропонує своїм користувачам Excel нову можливість швидко та легко шукати таблиці та оцінювати дані. Ця функція спочатку була доступна лише учасникам на етапі тестування, але також була доступна для версій Microsoft 365 для Windows та Mac з початку року.
По суті, XLOOKUP - це більш зручна версія VLOOKUP та HLOOKUP, яка доповнена додатковими практичними додатками. Тому його завдання також шукати таблиці, але це вже відрізняється від попередніх посилань. Фільтрація вмісту більше не прив’язана до встановленого курсу, але тепер також може бути ініційована справа наліво, зверху вниз і навпаки. Це нововведення пропонує велику перевагу в тому, що таблиці Excel тепер можна розробляти на власний розсуд і більше не прив’язувати до технічних характеристик. Завдяки новим параметрам, стали доступні додаткові опції, яких раніше не було. Корисність поширюється від дрібних деталей до примітних спрощень у використанні. Кінцевий ефект XLOOKUP повністю залежить від використовуваних параметрів.
Прості параметри XLOOKUP
Просте використання XLOOKUP вимагає лише трьох параметрів. Це:
- Критерій пошуку
- Матриця пошуку
- Матриця повернення
Нещодавно здобута свобода пояснюється, насамперед, розділенням матриці пошуку та повернення. Хоча VLOOKUP та HLOOKUP все ще вимагали від користувача вибрати всю матрицю для процесу пошуку, XLOOKUP дозволяє йому відокремити відоме та шукане значення. Це означає, що один стовпець пошуку або тепер також рядок пошуку визначається як необхідна матриця, в якій розташований обраний критерій пошуку, тоді як інший стає поверненою областю бажаного результату. Нова формула виглядає наступним чином:
= XLOOKUP (критерій пошуку; матриця пошуку; матриця повернення)
Якщо, наприклад, місячну заробітну плату пана Вагнера можна взяти з таблиці Excel для співробітників, то важливі дві колонки: перша, у якій перераховані всі працівники по іменах, та інша, в яку вноситься заробітна плата працівників. Оскільки критерієм пошуку є ім’я, відповідний стовпець пошуку вибирається як матриця. З іншого боку, результатом повернення є заробітна плата, тому відповідний стовпець тут робиться матрицею повернення.
Цей приклад показує, що нова формула робить попереднім позначення індексу конкретного стовпця в межах однієї великої матриці зайвим. Це економить додаткову детальну роботу та запобігає непотрібним помилкам.
Однак, якщо невідомо, чи працівника, якого ви шукаєте, називають «Вагнером» чи «Вегнером», нове доповнення до критерію пошуку може допомогти. Будь -яку кількість невідомих символів можна пропустити, вставляючи зірочку (*). У цьому випадку має сенс швидко змінити критерій пошуку на "* gner", щоб дістатися до потрібного пункту призначення. Однак, якщо таблиця заповнена багатьма подібними іменами, так що колега, пані Стегнер, ненавмисно виводиться, оскільки її ім’я також містить комбінацію літер, які ви шукаєте, то пошук можна ще більше уточнити. Тут починає діяти знак питання (?), Який дозволяє користувачеві замінити лише один символ. Тому критерій пошуку заповнюється "W? Gner".
Але що станеться, коли вам доведеться шукати зірочку або знак питання? У цьому випадку XLOOKUP має додаткову тильду (~), за допомогою якої стає зрозуміло, що мається на увазі не функція критерію пошуку, а вміст матриці пошуку. Отже, подвійна тильда як критерій пошуку (~~) також дозволяє здійснювати пошук одиночної тильди в матриці пошуку (~).
Повні параметри
Крім того, XVERWEIS пропонує інші функції, які вступають у дію, як тільки ці три додаткові параметри використовуються за бажанням:
- Якщо_не знайдено
- Режим порівняння
- Режим пошуку
"Якщо_не знайдено"
Окрім критерію пошуку трьох параметрів, матриці пошуку та матриці повернення, новий XVERWEIS має ще три параметри, які надають користувачеві багато переваг. Одним з таких є "If_not_ found", який діє як інтегрована функція if-error.
За допомогою цієї функції XLOOKUP дозволяє уникнути поширеної проблеми з попередніми посиланнями: Якщо результат пошуку не вдалося знайти, до цього часу відображалося лише загадкове значення помилки ("#NV"). Завдяки новому параметру тепер можна назвати цю помилку і, таким чином, легше її класифікувати, замінивши параметр, що утримує місце, словом за вашим вибором та поставив у лапки. Замість значення автоматичної помилки Excel може вказати, що результат "не знайдено" або "помилка введення". Враховуючи всю інформацію, формула XVERWEIS виглядає так:
= XLOOKUP (критерій пошуку; матриця пошуку; матриця повернення; якщо_не знайдено)
Режим порівняння
Іншим параметром є режим порівняння, який є для збільшення можливостей пошуку значень, якщо це необхідно. Спочатку VLOOKUP та HLOOKUP знали лише звернення чи помилки. Однак XLOOKUP може гнучко реагувати і, у разі неіснуючого результату, альтернативно використовувати значення, максимально наближене, щоб не просто перерахувати користувачеві помилку, а замість цього рекомендувати альтернативну. Наприклад, якщо ви шукаєте рахунок -фактуру на суму 1500 євро, який неможливо знайти, тоді значення -1 можна використати для параметра режиму порівняння для відображення наступного нижчого результату. Може виявитися, що рахунок був лише 1450 євро з самого початку. Цю інформацію можна було дізнатися лише з попередніми посиланнями за допомогою проміжних кроків. І навпаки, значення 1 можна використовувати для отримання наступного більшого результату.
Ця функція особливо корисна, коли значення відоме лише приблизно. Таким чином, рамку можна звузити, щоб полегшити пошук необхідного результату, незважаючи ні на що. Крім того, вміст таблиці більше не потрібно сортувати у порядку зростання, як у випадку VLOOKUP, оскільки XLOOKUP може знайти наступне значуще значення навіть без допомоги користувача. Це також надає додаткову свободу індивідуалізації таблиць.
Однак, якщо ці нововведення не потрібні, значення 0 можна просто використовувати, щоб продовжувати отримувати лише точні результати, як зазвичай. За допомогою загального заповнювача формула розширюється таким чином:
= XLOOKUP (критерій пошуку; матриця пошуку; матриця повернення; якщо_не знайдено; режим порівняння)
Режим пошуку
Останній параметр знову показує найпростіший і, мабуть, найяскравіший поліпшення порівняно з VLOOKUP, оскільки він вперше регулює напрямок історії пошуку. З введеним значенням 1 ви можете шукати звернення зверху вниз, тоді як зі значенням -1 все йде догори ногами. Крім того, може бути ініційований висхідний двійковий пошук зі значенням 2 і низхідний двійковий пошук зі значенням -2.
Хоча спочатку цей параметр не виглядає особливо вражаючим, він може регулярно мати позитивний ефект у поєднанні з розширеним критерієм пошуку. Тому що якщо матриця пошуку містить критерій пошуку двічі (наприклад, два співробітники з однаковим прізвищем), то повертається значення, яке хронологічно знаходиться на попередній позиції, видається за замовчуванням. Однак, якщо напрямок історії пошуку зміниться, відбувається зворотний ефект, і раніше приховане значення стає видимим. Однак цей параметр також корисний, якщо він використовується для проміжного контролю. Тому що якщо пошук зверху вниз дає інший результат, ніж пошук знизу вгору, це може означати, що могла статися помилка програми, яку тепер можна виправити на ранній стадії. Беручи до уваги цю примітку, остаточна формула XLOOKUP виглядає так:
= XLOOKUP (критерій пошуку; матриця пошуку; матриця повернення; якщо_не знайдено; режим порівняння; режим пошуку)
Чи варто переходити з VLOOKUP на XLOOKUP?
На закінчення залишається сказати, що з впровадженням XLOOKUP Microsoft зробила новий спосіб пошуку та аналізу доступним для своїх користувачів Excel, який може служити різним цілям і простий у використанні. Таким чином виконується все - від швидкого пошуку до певних вимог до результатів. Таким чином, XLOOKUP явно випереджає інші посилання, оскільки пропонує явні переваги навіть у тих функціях, які він від цього взяв на себе. Це видно з того факту, що тут дві опорні функції об’єднані і, серед іншого, доповнюються інтегрованою помилкою if.
Кожен, хто до цих пір був повністю задоволений VLOOKUP або HLOOKUP, а також не зацікавлений у звиканні до нової формули, може з чистою совістю дотримуватися перевіреного. Для всіх інших, хто хоче зробити свій підхід більш динамічним і нескладним, XVERWEIS - це бажана інновація. Нові параметри, безумовно, можна змінити та об’єднати таким чином, щоб наступне використання Excel було помітно зручнішим.
