Функція Excel XLOOKUP: Все, що вам потрібно знати (10 прикладів)

Перегляд відео - Функція Excel XLOOKUP (10 прикладів XLOOKUP)

Excel Функція XLOOKUP нарешті прибув.

Якщо ви використовували VLOOKUP або INDEX/MATCH, я впевнений, що вам сподобається гнучкість, яку надає функція XLOOKUP.

У цьому підручнику я висвітлю все, що потрібно знати про функцію XLOOKUP, і деякі приклади, які допоможуть вам знати, як найкраще її використовувати.

Тож почнемо!

Що таке XLOOKUP?

XLOOKUP - це нова функція Office 365, яка є новою та вдосконаленою версією функції VLOOKUP/HLOOKUP.

Він робить все, що раніше робив VLOOKUP, і багато іншого.

XLOOKUP - це функція, яка дозволяє швидко шукати значення в наборі даних (вертикальне або горизонтальне) і повертати відповідне значення в інший рядок/стовпець.

Наприклад, якщо ви отримали оцінки для студентів на іспиті, ви можете скористатися XLOOKUP, щоб швидко перевірити, скільки студент набрав, використовуючи ім’я студента.

Сила цієї функції стане ще яснішою, коли я глибоко занурюсь у деякі Приклади XLOOKUP далі в цьому підручнику.

Але перш ніж перейти до прикладів, виникає велике питання - як я можу отримати доступ до XLOOKUP?

Як отримати доступ до XLOOKUP?

Наразі XLOOKUP доступний лише для користувачів Office 365.

Отже, якщо ви використовуєте попередні версії Excel (2010/2013/2016/2019), ви не зможете використовувати цю функцію.

Я також не впевнений, чи буде це коли-небудь випущено для попередніх версій чи ні (можливо, Microsoft може створити надбудову так, як це зробили для Power Query). Але наразі ви зможете ним користуватися, лише якщо користуєтесь Office 365.

Натисніть тут, щоб оновити Office 365

Якщо ви вже користуєтесь Office 365 (домашнє, особисте або університетське видання) і не маєте до нього доступу, перейдіть на вкладку Файл, а потім натисніть Обліковий запис.

Була б програма Office Insider, і ви можете натиснути та приєднатися до програми Office Insider. Це дасть вам доступ до функції XLOOKUP.

Я очікую, що XLOOKUP незабаром стане доступним у всіх версіях Office 365.

Примітка: XLOOKUP також доступний для Office 365 для Mac та Excel для Інтернету (Excel онлайн)

Синтаксис функції XLOOKUP

Нижче наведено синтаксис функції XLOOKUP:

= XLOOKUP (lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])

Якщо ви використовували VLOOKUP, ви помітите, що синтаксис дуже схожий, звичайно з деякими приголомшливими додатковими функціями.

Не хвилюйтесь, якщо синтаксис і аргумент виглядають занадто багато. Я висвітлю це кількома простими прикладами XLOOKUP пізніше в цьому підручнику, які зроблять його кристально чистим.

Функція XLOOKUP може розповідати 6 аргументів (3 обов'язкових і 3 необов'язкових):

1. lookup_value - цінність, яку ви шукаєте
2. lookup_array - масив, у якому ви шукаєте значення пошуку
3. return_array - масив, з якого потрібно отримати та повернути значення (відповідає позиції, де знайдено значення пошуку)
4. [якщо_не знайдено] - значення, яке потрібно повернути, якщо значення пошуку не знайдено. Якщо ви не вказуєте цей аргумент, буде повернуто помилку #N/A
5. [режим_збігу] - Тут ви можете вказати бажаний тип відповідності:
   • 0 - Точна відповідність, де lookup_value має точно відповідати значенню в lookup_array. Це опція за замовчуванням.
   • -1 - Шукає точну відповідність, але якщо вона знайдена, повертає наступний менший елемент/значення
   • 1 - Шукає точну відповідність, але якщо вона знайдена, повертає наступний більший елемент/значення
   • 2 - Для часткового узгодження за допомогою символів підстановки (* або ~)
6. [search_mode] - Тут ви вказуєте, як функція XLOOKUP має здійснювати пошук у масиві lookup_array
   • 1 - Це параметр за замовчуванням, коли функція починає шукати lookup_value зверху (перший елемент) до низу (останній елемент) у lookup_array
   • -1 - Здійснює пошук знизу вгору. Корисно, якщо ви хочете знайти останнє відповідне значення в масиві lookup_array
   • 2 - Виконує двійковий пошук, де дані потрібно сортувати у порядку зростання. Якщо не відсортувати, це може дати помилку або неправильні результати
   • -2 - Виконує двійковий пошук, де дані потрібно сортувати у порядку зменшення. Якщо не відсортувати, це може дати помилку або неправильні результати

Приклади функцій XLOOKUP

Тепер перейдемо до цікавої частини - кілька практичних прикладів XLOOKUP.

Ці приклади допоможуть вам краще зрозуміти, як працює XLOOKUP, чим він відрізняється від VLOOKUP та INDEX/MATCH та деякі покращення та обмеження цієї функції.

Натисніть тут, щоб завантажити файл прикладу та продовжити

Приклад 1: Отримання значення пошуку

Припустимо, у вас є наступний набір даних, і ви хочете отримати математичний рахунок для Грега (значення пошуку).

Нижче наведена формула, яка робить це:

= XLOOKUP (F2, A2: A15, B2: B15)

У наведеній вище формулі я щойно використав обов’язкові аргументи, де шукає ім’я (зверху вниз), знаходить точну відповідність і повертає відповідне значення з B2: B15.

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

Одна миттєва перевага використання lookup_array та return_array як окремих аргументів означає, що тепер ви можете подивіться ліворуч. У VLOOKUP було це обмеження, за яким ви можете тільки подивитися вгору і знайти значення праворуч. Але з XLOOKUP це обмеження зникло.

Ось приклад. У мене такий самий набір даних, де ім’я праворуч, а діапазон return_ліворуч.

Нижче наведена формула, яку я можу використовувати, щоб отримати оцінку для Грега в математиці (що означає дивитися зліва від lookup_value)

= XLOOKUP (F2, D2: D15, A2: A15)

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

Приклад 2: Пошук і отримання цілого запису

Візьмемо ті ж дані як приклад.

У цьому випадку я не хочу просто отримувати рахунок Грега з математики. Я хочу отримати бали з усіх предметів.

У цьому випадку я можу використати формулу нижче:

= XLOOKUP (F2, A2: A15, B2: D15)

У наведеній вище формулі використовується діапазон return_array, який більше ніж стовпець (B2: D15). Отже, коли значення пошуку знаходиться в A2: A15, формула повертає весь рядок із масиву return_arry.

Також не можна видаляти лише клітинки, які є частиною масиву, які заповнювалися автоматично. У цьому прикладі не можна видалити H2 або I2. Якби ви спробували, нічого б не вийшло. Якщо вибрати ці клітинки, формула на панелі формул буде затінена (це означає, що її не можна змінити)

Ви можете видалити формулу в комірці G2 (де ми її спочатку ввели), вона видалить весь результат.

Це корисне покращення, як і раніше з VLOOKUP, вам доведеться вказувати номер стовпця окремо для кожної формули.

Приклад 3: Двосторонній пошук за допомогою XLOOKUP (горизонтальний та вертикальний пошук)

Нижче наведено набір даних, де я хочу знати оцінку Грега з математики (суб’єкт у клітині G2).

Це можна зробити за допомогою двостороннього пошуку, де я шукаю ім’я у стовпці А та назву предмета у рядку 1. Перевага цього двостороннього пошуку полягає в тому, що результат не залежить від імені учня назви предмета. Якщо я зміню назву предмета на хімію, ця двостороння формула XLOOKUP все одно працюватиме і дасть мені правильний результат.

Нижче наведена формула, яка виконає двосторонній пошук і дасть правильний результат:

= XLOOKUP (G1, B1: D1, XLOOKUP (F2, A2: A15, B2: D15))

Ця формула використовує вкладений XLOOKUP, де спочатку я використовую її для отримання всіх оцінок учня у комірці F2.

Отже, результат XLOOKUP (F2, A2: A15, B2: D15) дорівнює {21,94,81}, що є масивом оцінок, набраних Грегом у цьому випадку.

Потім це знову використовується у зовнішній формулі XLOOKUP як масив повернення. У зовнішній формулі XLOOKUP я шукаю назву предмета (яка знаходиться у комірці G1), а масив пошуку - B1: D1.

Якщо ім'я суб'єкта Math, ця зовнішня формула XLOOKUP отримує перше значення з масиву повернення - це {21,94,81} у цьому прикладі.

Це робить те саме, що до цього часу було досягнуто за допомогою комбінації INDEX та MATCH

Натисніть тут, щоб завантажити файл прикладу та продовжити

Приклад 4: Коли значення пошуку не знайдено (обробка помилок)

До формули XLOOKUP тепер додано обробку помилок.

Четвертий аргумент у функції XLOOKUP - [if_not_found], де ви можете вказати, що вам потрібно, якщо пошук не вдасться знайти.

Припустимо, у вас є набір даних, як показано нижче, де ви хочете отримати оцінку з математики на випадок, якщо збіг, і якщо ім’я не знайдено, ви хочете повернутися - «Не з'явилося»

Нижченаведена формула зробить це:

= XLOOKUP (F2, A2: A15, B2: B15, "Не з'явився")

У цьому випадку я жорстко кодую, що я хочу отримати, якщо немає відповідності. Ви також можете використовувати посилання на клітинку на клітинку або формулу.

Приклад 5: Вкладений XLOOKUP (пошук у кількох діапазонах)

Геніальність наявності аргументу [if_not_found] полягає в тому, що він дозволяє вам використовувати вкладена формула XLOOKUP.

Наприклад, припустимо, що у вас є два окремі списки, як показано нижче. Хоча у мене ці дві таблиці на одному аркуші, ви можете розмістити їх на окремих аркушах або навіть у робочих зошитах.

Нижче наведена вкладена формула XLOOKUP, яка перевірятиме ім’я в обох таблицях і повертатиме відповідне значення із зазначеного стовпця.

= XLOOKUP (A12, A2: A8, B2: B8, XLOOKUP (A12, F2: F8, G2: G8))

У наведеній вище формулі я використовував аргумент [if_not_found] для використання іншої формули XLOOKUP. Це дозволяє вам додати другий XLOOKUP до тієї ж формули та відсканувати дві таблиці з однією формулою.

Я не впевнений, скільки вкладених XLOOKUP можна використати у формулі. Я пробував до 10 і це спрацювало, потім я відмовився 🙂

Приклад 6: Знайдіть останнє відповідне значення

Цей був дуже потрібен, і XLOOKUP зробив це можливим. Тепер вам не потрібно шукати складних способів отримати останнє відповідне значення в діапазоні.

Припустимо, у вас є набір даних, як показано нижче, і ви хочете перевірити, коли остання особа була прийнята на роботу у кожному відділі та яка була дата найму.

Наведена нижче формула буде шукати останнє значення для кожного відділу та давати назву останнього найманого працівника:

= XLOOKUP (F1, $ B $ 2: $ B $ 15, $ A $ 2: $ A $ 15 ,,,-1)

Наведена нижче формула містить дату найму останньої оренди для кожного відділу:

= XLOOKUP (F1, $ B $ 2: $ B $ 15, $ C $ 2: $ C $ 15 ,,,-1)

Оскільки XLOOKUP має вбудовану функцію для визначення напрямку пошуку (перший до останнього або останній до першого), це робиться за допомогою простої формули. З вертикальними даними VLOOKUP та INDEX/MATCH завжди виглядають зверху вниз, але з XLOOKUP і можуть також вказати напрямок знизу вгору.

Приклад 7: Приблизний збіг із XLOOKUP (Знайти ставку податку)

Ще одним помітним поліпшенням XLOOKUP є те, що зараз є чотири режими відповідності (VLOOKUP має 2, а MATCH - 3).

Ви можете вказати будь -який із чотирьох аргументів, щоб вирішити, яким чином має відповідати значення пошуку:

• 0 - Точна відповідність, де lookup_value має точно відповідати значенню в lookup_array. Це опція за замовчуванням.
• -1 - Шукає точну відповідність, але якщо вона знайдена, повертає наступний менший елемент/значення
• 1 - Шукає точну відповідність, але якщо вона знайдена, повертає наступний більший елемент/значення
• 2 - Для часткового узгодження за допомогою символів підстановки (* або ~)

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

Нижче у мене є набір даних, де я хочу знайти комісію кожної людини - і комісію потрібно розрахувати за допомогою таблиці праворуч.

Нижче наведена формула, яка дозволить це зробити:

= XLOOKUP (B2, $ E $ 2: $ E $ 6, $ F $ 2: $ F $ 6,0, -1)*B2

Це просто використовує значення продажів як пошук і переглядає таблицю пошуку праворуч. У цій формулі я використав -1 як п’ятий аргумент ([match_mode]), що означає, що він буде шукати точну відповідність, а коли не знайде його, він поверне значення, яке менше, ніж значення пошуку .

І, як я вже сказав, вам не потрібно турбуватися, чи сортуються ваші дані.

Натисніть тут, щоб завантажити файл прикладу та продовжити

Приклад 8: Горизонтальний пошук

XLOOKUP може виконувати як вертикальний, так і горизонтальний пошук.

Нижче у мене є набір даних, де я маю імена студентів та їх бали у рядках, і я хочу отримати оцінку за ім’я у клітинці В7.

Нижченаведена формула зробить це:

= XLOOKUP (B7, B1: O1, B2: O2)

Це не що інше, як простий пошук (подібний до того, що ми бачили у прикладі 1), але горизонтальний.

Усі наведені мною приклади щодо вертикального пошуку також можна зробити за допомогою горизонтального пошуку за допомогою XLOOKUP (прощання з VLOOKUP та HLOOKUP).

Приклад 9: Умовний пошук (використання XLOOKUP з іншими формулами)

Це трохи вдосконалений приклад, який також показує силу XLOOKUP, коли вам потрібно виконувати складні пошуки.

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

Нижче наведена формула, яка дасть ім’я учня з найвищими оцінками з кожного предмета:

= XLOOKUP (MAX (XLOOKUP (G1, $ B $ 1: $ D $ 1, $ B $ 2: $ D $ 15)), XLOOKUP (G1, $ B $ 1: $ D $ 1, $ B $ 2: $ D $ 15), $ A $ 2: $ A $ 15)

Оскільки XLOOKUP може бути використаний для повернення цілого масиву, я використав його, щоб спочатку отримати всі оцінки для необхідного предмета.

Наприклад, для математики, коли я використовую XLOOKUP (G1, $ B $ 1: $ D $ 1, $ B $ 2: $ D $ 15), це дає мені всі бали з математики. Потім я можу скористатися функцією MAX, щоб знайти максимальну оцінку в цьому діапазоні.

Тоді цей максимальний бал стає моїм значенням пошуку, а діапазон пошуку буде масив, повернутий XLOOKUP (G1, $ B $ 1: $ D $ 1, $ B $ 2: $ D $ 15)

Я використовую це в іншій формулі XLOOKUP, щоб отримати ім’я студента, який набрав максимальну кількість балів.

А щоб підрахувати кількість студентів, які набрали більше 80 балів, скористайтеся формулою нижче:

= COUNTIF (XLOOKUP (G1, $ B $ 1: $ D $ 1, $ B $ 2: $ D $ 15), "> 80")

Цей просто використовує формулу XLOOKUP, щоб отримати діапазон усіх значень для даної теми. Потім він обертає його у функцію COUNTIF, щоб отримати кількість балів більше 80.

Приклад 10: Використання підстановки в XLOOKUP

Так само, як ви можете використовувати символи підстановки у VLOOKUP та MATCH, ви також можете зробити це за допомогою XLOOKUP.

Але є різниця.

У XLOOKUP вам потрібно вказати, що ви використовуєте символи підстановки (у п’ятому аргументі). Якщо ви не вкажете це, XLOOKUP видасть вам помилку.

Нижче наведено набір даних, де я маю назви компаній та їх ринкову капіталізацію.

Я хочу знайти назву компанії у стовпці D і отримати ринкову капіталізацію з таблиці зліва. А оскільки назви у стовпці D не є точними збігами, мені доведеться використовувати символи підстановки.

Нижче наведена формула, яка дозволить це зробити:

= XLOOKUP ("*" & D2 & "*", $ A $ 2: $ A $ 11, $ B $ 2: $ B $ 11,, 2)

У наведеній вище формулі я використовував символ підстановки зірочкою (*) як раніше, так і після D2 (він повинен бути в подвійних лапках і з'єднаний з D2 за допомогою амперсанда).

Це вказує на формулу, щоб переглянути всі клітинки, і якщо вона містить слово у комірці D2 (яка є Apple), вважайте це точним збігом. Незалежно від того, скільки і які символи є до та після тексту в комірці D2.

А щоб переконатися, що XLOOKUP приймає символи підстановки, п’ятий аргумент був встановлений на 2 (збіг символів підстановки).

Приклад 11: Знайдіть останнє значення у стовпці

Оскільки XLOOKUP дозволяє здійснювати пошук знизу вгору, ви можете легко знайти останнє значення у списку, а також отримати відповідне значення зі стовпця.

Припустимо, у вас є набір даних, як показано нижче, і ви хочете знати, яка остання компанія та яка ринкова капіталізація цієї останньої компанії.

Наведена нижче формула дасть вам назву останньої компанії:

= XLOOKUP ("*", A2: A11, A2: A11,, 2, -1)

Наведена нижче формула дасть ринкову капіталізацію останньої компанії у списку:

= XLOOKUP ("*", A2: A11, B2: B11,, 2, -1)

У цих формулах знову використовуються символи підстановки. У них я використовував зірочку (*) як значення пошуку, що означає, що це вважатиме першу клітинку, з якою вона зустрінеться, як точну відповідність (оскільки зірочка може бути будь -яким символом і будь -якою кількістю символів).

А оскільки напрямок - знизу вгору (для вертикально розташованих даних), воно поверне останнє значення у списку.

І друга формула, оскільки використовує окремий return_range для визначення ринкової капіталізації прізвища у списку.

Натисніть тут, щоб завантажити файл прикладу та продовжити

Що робити, якщо у вас немає XLOOKUP?

Оскільки XLOOKUP, ймовірно, буде доступний лише для користувачів Office 365, один із способів отримати це - оновити його до Office 365.

Якщо у вас вже є Office 365 Home, Personal або University University, ви вже маєте доступ до XLOOKUP. Все, що вам потрібно зробити, це приєднатися до програми Office Insider.

Для цього перейдіть на вкладку «Файл», натисніть «Обліковий запис», а потім - параметр «Інсайдерська версія Office». Існує можливість приєднатися до інсайдерської програми.

Якщо у вас є інші передплати на Office 365 (наприклад, Enterprise), я впевнений, що XLOOKUP та інші чудові функції (такі як динамічні масиви, формули, такі як SORT та FILTER) незабаром стануть доступними.

Якщо ви використовуєте Excel 2010/2013/2016/2019, у вас не буде XLOOKUP, і вам доведеться продовжувати використовувати комбінацію VLOOKUP, HLOOKUP та INDEX/MATCH, щоб отримати найкращі результати від формул пошуку.

Зворотна сумісність XLOOKUP

Це одне, з чим потрібно бути обережним - це XLOOKUP НЕ зворотно сумісний.

Це означає, що якщо ви створите файл і використовуєте формулу XLOOKUP, а потім відкриєте її у версії, у якій немає XLOOKUP, вона покаже помилки.

Оскільки XLOOKUP - це величезний крок вперед у правильному напрямку, я вважаю, що це стане формулою пошуку за замовчуванням, але, звичайно, знадобиться кілька років, перш ніж вона набуде широкого поширення. Зрештою, я все ще бачу деяких людей, які використовують Excel 2003.

Отже, це 11 прикладів XLOOKUP, які можуть допомогти вам швидше виконати всі пошукові та довідкові завдання, а також спростити їх використання.

Сподіваюся, вам цей підручник був корисним!