Excel 2020: Дванадцять переваг XLOOKUP - Поради Excel

Нова функція XLOOKUP розповсюджується на Office 365, починаючи з листопада 2019 року. Джо Макдейд з команди Excel розробив XLOOKUP, щоб об'єднати людей, які використовують VLOOKUP, і людей, які використовують INDEX / MATCH. У цьому розділі буде розглянуто 12 переваг XLOOKUP:

  1. Точне збіг є типовим.
  2. Третій аргумент VLOOKUP на основі цілих чисел тепер є належним посиланням.
  3. IFNA вбудована для обробки відсутніх значень.
  4. XLOOKUP не має проблем з лівим рухом.
  5. Знайдіть наступний менший або наступний більший збіг без сортування таблиці.
  6. XLOOKUP може робити HLOOKUP.
  7. Знайдіть останній збіг, шукаючи знизу.
  8. Підстановочні символи за замовчуванням вимкнено, але ви можете знову їх увімкнути.
  9. Поверніть усі 12 місяців за єдиною формулою.
  10. Може повернути посилання на клітинку, якщо XLOOKUP знаходиться поруч із двокрапкою, наприклад XLOOKUP (); XLOOKUP ()
  11. Може зробити двосторонній збіг, як це може INDEX (, MATCH, MATCH).
  12. Може підсумовувати всі пошуки в одній формулі, як це може зробити LOOKUP.

Ось синтаксис: = XLOOKUP (Lookup_Value, Lookup_Array, Results_Array, (if_not_found), (match_mode), (search_mode)).

XLOOKUP Перевага 1: Точний збіг за замовчуванням

99% моїх формул VLOOKUP закінчуються, FALSE або, 0, щоб вказати точну відповідність. Якщо ви завжди використовуєте версію VLOOKUP з точною відповідністю, ви можете почати залишати match_mode від функції XLOOKUP.

На наступному малюнку ви шукаєте W25-6 з комірки A4. Ви хочете шукати цей предмет у L8: L35. Коли він знайдений, вам потрібна відповідна ціна зі стовпця N. Немає необхідності вказувати False як match_mode, оскільки XLOOKUP за замовчуванням має точну відповідність.

XLOOKUP значення в A4. Подивіться на L8: L35. Поверніть відповідну ціну з N8: N35.

Вигода XLOOKUP 2: масив Results_Array є посиланням замість цілого числа

Подумайте про формулу VLOOKUP, яку ви використовували б до XLOOKUP. Третім аргументом було б число 3, яке б означало, що ви хочете повернути 3-й стовпець. Завжди існувала небезпека того, що невідомий колега вставив (або видалив) стовпець у вашу таблицю. З додатковим стовпцем у таблиці, VLOOKUP, який повертав ціну, почне повертати опис. Оскільки XLOOKUP вказував на посилання на клітинку, формула переписує себе, щоб продовжувати вказувати на ціну, яка зараз знаходиться в стовпці O.

Старий VLOOKUP не вдався, якщо хтось вставив новий стовпець у таблицю пошуку. XLOOKUP продовжує працювати.

Перевага XLOOKUP 3: IFNA вбудований як необов’язковий аргумент

Страшна помилка # N / A повертається, коли значення пошуку не знайдено в таблиці. Раніше, щоб замінити # N / A чимось іншим, вам довелося б використовувати IFERROR або IFNA, обгорнуті навколо VLOOKUP.

Коли предмет не знайдено, він повертає # N / A від VLOOKUP або XLOOKUP …

Завдяки пропозиції Ріко на моєму каналі YouTube команда Excel включила необов’язковий четвертий аргумент для if_not_found. Якщо ви хочете замінити ці помилки # N / A нулем, просто додайте 0 як четвертий аргумент. Або ви можете використати якийсь текст, наприклад "Значення не знайдено".

Необов’язковим четвертим аргументом у XLOOKUP є "якщо не знайдено". Поставте там 0 або "Не знайдено".

XLOOKUP Перевага 4: Немає проблем, дивлячись ліворуч від ключового поля

VLOOKUP не може дивитися ліворуч від ключового поля, не вдаючись до VLOOKUP (A4, CHOOSE ((1,2), G7: G34, F7: F34), 2, False). З XLOOKUP не виникає проблем із наявністю масиву Results_ зліва від масиву Lookup_arrokup.

З XLOOKUP немає проблем із поверненням категорії зі стовпця F під час пошуку номерів деталей у стовпці G. Це завжди було слабкою стороною VLOOKUP: вона не могла дивитися ліворуч.

XLOOKUP Перевага 5: Наступний-менший або наступний-більший збіг без сортування

VLOOKUP мав можливість шукати точну відповідність або просто менше значення. Ви можете або залишити четвертий аргумент поза VLOOKUP, або змінити False на True. Щоб це працювало, таблицю підстановки потрібно було сортувати за зростанням.

Приклад приблизної версії VLOOKUP. Будь-який продаж від 10 тисяч до 20 тисяч отримує бонус у розмірі 12 доларів.

Але VLOOKUP не мав можливості повернути точну відповідність або наступний більший елемент. Для цього вам довелося перейти на використання MATCH з -1 як режим match_mode, і ви повинні були бути обережними, щоб таблиця пошуку була відсортована за спаданням.

Необов’язковий п’ятий аргумент XLOOKUP match_mode може шукати лише точну відповідність, рівну або просто меншу, рівну або просто більшу. Зверніть увагу, що значення в XLOOKUP мають більше сенсу, ніж у MATCH:

  • -1 знаходить значення, рівне або просто менше
  • 0 знайти точну відповідність
  • 1 знаходить значення, рівне або просто більше.

Але найдивовижніша частина: таблицю пошуку не потрібно сортувати, і будь-який match_mode буде працювати.

Нижче, match_mode з -1 знаходить наступний менший елемент.

П’ятим аргументом XLOOKUP є Match_Mode. 0 - для точного збігу. Негативний використовується для точного збігу або наступного меншого предмета. Позитив 1 - для точного збігу або наступного більшого елемента. 2 - для Wildcard Match. Щоб відобразити те, що буде робити VLOOKUP з True в четвертому аргументі, поставте від’ємний аргумент match_mode у XLOOKUP.

Тут match_mode 1, знаходить, який транспортний засіб потрібен, залежно від кількості людей у ​​партії. Зверніть увагу, що таблиця пошуку не сортується за пасажирами, а назва транспортного засобу знаходиться зліва від клавіші.

XLOOKUP може зробити те, чого не може зробити VLOOKUP: знайти точну відповідність або просто більшу. У цьому випадку у туристичної компанії є список бронювань. Виходячи з кількості пасажирів, таблиця пошуку показує, який транспортний засіб вам потрібен для цих людей.

У таблиці написано:

  • Автобус вміщує 64 людини
  • Автомобіль вміщує 4 людини
  • Мотоцикл вміщує 1 людину
  • Tour Van вміщує 12 осіб
  • Фургон вміщують 6 чоловік.

Як бонус, дані сортуються за транспортним засобом (у старому рішенні, використовуючи MATCH, таблицю доведеться сортувати за зменшенням місткості. Також: транспортний засіб знаходиться ліворуч від місткості.

Перевага XLOOKUP 6: XLOOKUP збоку замінює HLOOKUP

Масив пошуку та масиву результатів може бути горизонтальним за допомогою XLOOKUP, що полегшує заміну HLOOKUP.

Тут таблиця пошуку горизонтальна. Раніше для цього потрібен був HLOOKUP, але XLOOKUP може мати справу зі столом, який йде вбік.

XLOOKUP Перевага 7: Шукайте знизу останній збіг

У мене є старе відео на YouTube, яке відповідає на запитання британського кінного господарства. Вони мали автопарк. Кожного разу, коли транспортний засіб приїжджав за паливом чи послугою, вони реєстрували транспортний засіб, дату та пробіг у таблиці. Вони хотіли знайти останній відомий пробіг для кожного автомобіля. Незважаючи на те, що MAXIFS ери Excel-2017 може вирішити це сьогодні, багато років тому рішенням була арканна формула з використанням LOOKUP і передбачала ділення на нуль.

Сьогодні необов’язковий шостий аргумент XLOOKUP дозволяє вказати, що пошук слід починати з нижньої частини набору даних.

Знайдіть останній збіг у списку.

Примітка

Хоча це є значним покращенням, воно дозволяє лише знайти перший або останній збіг. Деякі люди сподівались, що це дозволить вам знайти другий чи третій збіг, але це не є наміром аргументу search_mode.

Обережно

На малюнку вище видно, що існують режими пошуку, що використовують старий двійковий пошук. Джо Макдейд не рекомендує використовувати їх. По-перше, вдосконалений алгоритм пошуку з 2018 року досить швидкий, щоб не було значної переваги в швидкості. По-друге, ви ризикуєте невмілим колегою відсортувати таблицю пошуку та ввести неправильні відповіді.

Перевага XLOOKUP 8: За замовчуванням підстановочні символи "вимкнені"

Більшість людей не усвідомлювали, що VLOOKUP розглядає зірочку, знак запитання та тильду як символи підстановки, як описано в "# 51 Використовуйте підстановочний знак у VLOOKUP" на сторінці 143. З XLOOKUP, підстановочні символи вимкнені за замовчуванням. Якщо ви хочете, щоб XLOOKUP розглядав ці символи як підстановочні символи, використовуйте 2 як Match_Mode.

Дуже мало людей усвідомлювали, що VLOOKUP розглядає зірочки у значенні пошуку як підстановочний знак. За замовчуванням XLOOKUP не використовує символи підстановки, але ви можете змусити його поводитися як VLOOKUP, якщо ви використовуєте режим збігу 2: збіг символів підстановки.

Вигода XLOOKUP 9: Поверніть усі 12 місяців в одній формулі!

Це справді перевага Dynamic Arrays, але це моя улюблена причина любити XLOOKUP. Коли вам потрібно повернути всі 12 місяців під час пошуку, одна формула, введена в B6 з прямокутним масивом return_array, поверне кілька результатів. Ці результати потраплять у сусідні комірки.

На малюнку нижче одна формула, введена в B7, повертає всі 12 відповідей, показаних у B7: M7.

Один XLOOKUP у стовпці за січень повертає цифри за січень - грудень. Це робиться шляхом вказівки масиву результатів з 12 стовпцями.

Перевага XLOOKUP 10: Може повернути посилання на клітинку, якщо поруч із двокрапкою

Цей складний, але гарний. Раніше було сім функцій, які змінювались би від повернення значення комірки до повернення посилання на клітинку, якщо функція торкалася двокрапки. Для прикладу див. Використання A2: INDEX () як енергонезалежного зміщення. XLOOKUP - це функція восьми, яка пропонує таку поведінку, приєднуючись ВИБЕРИ, ЯКЩО, ПІД, ІНДЕКС, НЕПРЯМО, ЗМІСТ та ПЕРЕКЛЮЧЕННЯ.

Розглянемо наступний малюнок. Хтось вибирає Вишню в Е4 і Рис в Е5. Вам потрібна формула, яка підсумовуватиме все від B6 до B9.

На малюнку показано дві формули XLOOKUP у двох клітинках. Перший повертає 15 з комірки B6. Другий повтор 30 з B9. Але потім у третій комірці є формула, яка поєднує дві формули XLOOKUP двокрапкою, а потім обертає їх у функцію SUM. Результатом є сума B6: B9, оскільки XLOOKUP може повернути посилання на клітинку, якщо функція відображається поруч із оператором, таким як двокрапка. Щоб довести, що це працює, наступні кілька малюнків покажуть цю формулу у діалоговому вікні «Оцінити формулу».

На малюнку вище ви можете бачити, що XLOOKUP E4 поверне 15 з комірки B6. XLOOKUP E5 поверне 30 із B9. Однак, якщо взяти дві функції XLOOKUP із комірок D9 і D10 і скласти їх разом із двокрапкою між ними, поведінка XLOOKUP зміниться. Замість повернення 15 перший XLOOKUP повертає адресу комірки B6!

Щоб довести це, я вибрав D7 і використовую формули, оцінюй формулу. Після натискання «Оцінити» двічі, наступною частиною, яку потрібно обчислити, є XLOOKUP («Вишня», A4: A29, B4: B29), як показано тут.

Це показує діалогове вікно "Оцінити формулу" безпосередньо перед оцінкою першого XLOOKUP. Цей XLOOKUP з’являється безпосередньо перед двокрапкою.

Натисніть Оцінити ще раз і вражаюче, формула XLOOKUP повертає $ B $ 6 замість 15, що зберігаються в B6. Це трапляється тому, що безпосередньо за цією формулою XLOOKUP є двокрапка.

Натисніть Оцінити, і перший XLOOKUP поверне $ B $ 6 замість 15.

Натисніть Оцінити ще два рази, і проміжна формула матиме значення = SUM (B6: B9).

Після оцінки другого XLOOKUP проміжна формула = SUM (B6: B9).

Це дивовижна поведінка, про яку більшість людей не знає. Excel MVP Чарльз Вільямс каже мені, що його можна активувати за допомогою будь-якого з цих трьох операторів поруч із XLOOKUP:

  • Колон
  • Простір (оператор перетину)
  • Кома (оператор Союзу)

XLOOKUP Перевага 11: Двосторонній матч типу INDEX (, MATCH, MATCH)

Для всіх моїх друзів по VLOOKUP люди з INDEX / MATCH чекали, чи зможе XLOOKUP обробляти двосторонні матчі. Чудова новина: вона може це зробити. Погана новина: методологія дещо інша, ніж очікували б фанати INDEX / MATCH. Це може бути трохи над їхніми головами. Але я впевнений, що вони можуть підійти до цього методу.

Для двостороннього збігу потрібно знайти, який рядок містить номер рахунку A621, показаний у J3. Отже, XLOOKUP починається досить легко: = XLOOKUP (J3, A5: A15. Але тоді вам потрібно вказати result_array. Ви можете використовувати той же фокус, що і в XLOOKUP Перевага 9: Поверніть усі 12 місяців в одній формулі вище, але використовуйте його для повернення вертикального вектора. Внутрішній XLOOKUP шукає місяць J4 у заголовках місяців у B4: G4. Масив return_array вказується як B5: G15. Результатом є те, що внутрішній XLOOKUP повертає масив, подібний до того, як показано в I10 : I20 нижче. Оскільки A621 знайдено в п’ятій комірці масиву lookup_array, а 104 - у п’ятій комірці масиву results_array, ви отримаєте правильну відповідь із формули. Нижче J6 показує старий спосіб. J7 повертає новий шлях.

XLookup J3 у списку рахунків у A5: A15. Для масиву результатів використовуйте XLOOKUP (J4, B4: G4, B5: G15). У цій формулі B4: G4 - це перелік місяців. B5: G15 - це прямокутний масив значень для всіх рахунків за всі місяці. В іншій комірці лише внутрішній XLOOKUP показує, як він повертає весь стовпець значень за травень.

Перевага XLOOKUP 12: Підсумуйте всі значення пошуку в одній формулі

Древня функція LOOKUP запропонувала два дивні трюки. По-перше, якщо ви намагаєтеся визначити загальну суму бонусних витрат, яку потрібно нарахувати, ви можете попросити LOOKUP шукати всі значення в одній формулі. На зображенні нижче LOOKUP (C4: C14 виконує 11 пошуків. Але функція LOOKUP не пропонує точного збігу і вимагає сортування таблиці пошуку.

Знайдіть 13 значень і підсумуйте їх. Раніше це працювало з LOOKUP, але також працює з XLOOKUP. В якості першого аргументу вкажіть усі значення пошуку C4: C14. Оберніть XLOOKUP функцією SUM.

За допомогою XLOOKUP ви можете вказати діапазон, оскільки lookup_value і XLOOKUP повернуть усі відповіді. Перевага полягає в тому, що XLOOKUP може робити точні збіги.

Фокус використання LOOKUP для підсумовування всіх результатів пошуку працював лише з наближеною версією Lookup. Тут XLOOKUP виконує точну відповідність усім іменам у L4: L14 і отримує загальну кількість усіх результатів.

Бонусна порада: А як щодо Twisted LOOKUP?

Excel MVP Майк Гірвін часто показує фокус функції LOOKUP, де Lookup_Vector вертикальний, а Result_Vector горизонтальний. XLOOKUP не підтримує цей трюк. Але, якщо ви трохи обдурите і обернете масив results_array у функцію TRANSPOSE, ви зможете керувати крученим пошуком.

Тут масив підстановки вертикальний, а масив результатів горизонтальний. Стара функція LOOKUP може це впоратись ,, але, щоб зробити це з XLOOKUP, вам потрібно обернути будь-який масив у TRANSOSE.

Цікаві статті...