Excel 2016 для передплатників Office 365 на Windows і Mac тепер підтримує нову функцію XLOOKUP, яка рекламується як значно простіша та універсальніша заміна дуже популярної (але часто зневаженої) функції вертикального пошуку, VLOOKUP (не знаю, що означає X у XLOOKUP означає; розширений, можливо?).
Для тих із вас, хто ще не знайомий з VLOOKUP (вважається третьою за часто використовуваною функцією відразу після SUM і AVERAGE), ця функція виконує пошук по вертикалі за рядком у крайньому лівому стовпці призначеної таблиці пошуку зверху вниз, поки не знайде значення в колонка пошуку, позначена номером зміщення, яке відповідає чи перевищує той, який ви шукаєте. Незважаючи на те, що функція VLOOKUP надзвичайно корисна для пошуку окремих елементів у довгому списку або стовпці таблиці даних на вашому робочому аркуші, функція VLOOKUP має кілька обмежень, які не поділяють ця нова функція пошуку, як XLOOKUP:
Функція XLOOKUP має п’ять можливих аргументів, перші три з яких є обов’язковими, а останні два — необов’язковими, використовуючи такий синтаксис:
XLOOKUP ( шукане_значення , просматріваемий_массів , return_array , [ match_mode ], [ search_mode ])
Потрібний аргумент lookup_value позначає значення або елемент, який ви шукаєте. Необхідний аргумент масиву look_up позначає діапазон комірок, які потрібно шукати для цього значення пошуку, а аргумент return_array позначає діапазон комірок, що містять значення, яке потрібно повернути, коли Excel знайде точну відповідність.
* Майте на увазі, призначаючи аргументи lookup_array і return_array у вашій функції XLOOKUP, обидва діапазони повинні мати однакову довжину, інакше Excel поверне #VALUE! помилка у вашій формулі. Це скоріше причина використовувати назви діапазонів або стовпців визначеної таблиці даних під час визначення цих аргументів, а не вказувати на них чи вводити посилання на клітинки .
Додатковий аргумент match_mode може містити будь-яке з наступних чотирьох значень:
Додатковий аргумент search_mode може містити будь-яке з наступних чотирьох значень:
Найкращий спосіб зрозуміти потужність та універсальність нової функції XLOOKUP — побачити її в дії на робочому аркуші Excel. На наступному малюнку у мене є робочий аркуш із простою таблицею даних про продажі за 2019 рік, упорядкованою за країнами. Щоб використовувати XLOOKUP для повернення загального обсягу продажів із цієї таблиці в клітинку E4 на основі країни, яку ви ввели в клітинку D4 робочого аркуша, виконайте такі дії:
Помістіть курсор комірки в клітинку E4 робочого аркуша
Клацніть параметр «Пошук і посилання» на вкладці «Формули», а потім «XLOOKUP» у нижній частині спадного меню, щоб відкрити діалогове вікно «Аргументи функцій».
Клацніть клітинку D4 на аркуші, щоб ввести посилання на клітинку в текстове поле аргументу Lookup_value.
Натисніть Tab, щоб вибрати текстове поле аргументу Lookup_array, потім клацніть клітинку A4 і утримуйте Shift, натискаючи Ctrl-стрілку вниз, щоб вибрати A4:A8 як діапазон для пошуку (оскільки діапазон A3:B8 визначається як таблиця даних Excel, Таблиця1[Країна] з’являється у текстовому полі замість діапазону A4:A8).
Натисніть Tab, щоб вибрати текстове поле аргументу Return_array, потім клацніть клітинку B4 і утримуйте Shift, натискаючи Ctrl-стрілка вниз, щоб вибрати B4:B8 як діапазон, що містить значення, які потрібно повернути на основі результатів пошуку (відображається як Таблиця 1 [Загальний обсяг продажів] у текстовому полі).
Натисніть кнопку OK, щоб ввести формулу XLOOKUP в клітинку E4.
Створення формули з XLOOKUP в клітинці E4, яка повертає продажі на основі країни, введеної в клітинку D4.
Excel вводить формулу XLOOKUP у клітинку E4 робочого аркуша та повертає 4900 як результат, оскільки Коста-Ріка наразі введена в клітинку пошуку D4, і, як ви можете бачити в таблиці продажів за 2019 рік, це справді загальний обсяг продажів для цієї країни.
Оскільки XLOOKUP працює як справа наліво, так і зліва направо, ви можете використовувати цю функцію так само добре, щоб повернути країну з цієї таблиці продажів на основі конкретного показника продажів. На наступному малюнку показано, як це зробити. Цього разу ви створюєте формулу XLOOKUP у клітинці D4 і призначаєте значення, введене в клітинку E4 (11 000, у цьому випадку), як аргумент lookup_value.
Крім того, ви вводите -1 як аргумент match_mode, щоб замінити точну відповідність функції за замовчуванням, щоб Excel повернув країну з точною відповідністю до значення продажів, введеного в комірку пошуку E4, або ту країну з наступним нижчим загальним обсягом продажів (Мексика з 10 000 доларів США в цьому випадку, оскільки в цій таблиці немає країни з 11 000 доларів США від загального обсягу продажів). Не призначаючи аргумент match_mode для цієї формули, Excel поверне #NA як результат, оскільки в цій таблиці продажів немає точної відповідності до 11 000 доларів США.
Створення формули з XLOOKUP у клітинці D4, яка повертає країну на основі продажів, введених у клітинку E4
Оскільки функція XLOOKUP однаково зручна для пошуку по горизонталі за стовпцями, як і по вертикалі за рядком, ви можете використовувати її для створення формули, яка виконує двосторонній пошук (замінюючи необхідність створення формули, яка поєднує функції INDEX і MATCH як в минулому). На наступному малюнку, що містить таблицю розкладу виробництва на 2019 рік для номерів деталей, від AB-100 до AB-103 за місяці з квітня по грудень, показано, як це робиться.
Створення формули з вкладеними функціями XLOOKUP для повернення кількості одиниць, вироблених для деталі за певний місяць
У клітинці B12 я створив таку формулу:
=XLOOKUP(part_lookup,$A$3:$A$6,XLOOKUP(date_lookup,$B$2:$J$2,$B$3:$J$6))
Ця формула починається з визначення функції XLOOKUP, яка виконує вертикальний пошук за рядком для точної відповідності запису частини, зробленому в комірці з назвою part_lookup (у цьому випадку клітинка B10) у діапазоні комірок $A$3:$A$6 виробничої таблиці. . Зауважте, однак, що аргумент return_array для цієї оригінальної функції LOOKUP сам по собі є другою функцією XLOOKUP.
Ця друга вкладена функція XLOOKUP шукає діапазон комірок $B$2:$J$2 горизонтально за стовпцями для точної відповідності запису дати, зробленому в комірці з назвою date_lookup (у цьому випадку клітинка B11). Аргументом return_array для цієї другої вкладеної функції XLOOKUP є $B$3:$J$6, діапазон комірок усіх виробничих значень у таблиці.
Ця формула працює так, що Excel спочатку обчислює результат другої вкладеної функції XLOOKUP, виконуючи горизонтальний пошук, який у цьому випадку повертає масив у діапазоні комірок D3: D6 стовпця Jun-19 (зі значеннями: 438, 153, 306 і 779) як результат. Цей результат, у свою чергу, стає аргументом return_array для вихідної функції XLOOKUP, яка виконує вертикальний пошук за рядком для точної відповідності запису номера частини, внесеному в комірку B11 (з назвою part_lookup). Оскільки в цьому прикладі ця клітинка part_lookup містить AB-102, формула повертає лише виробниче значення 19 червня, 306, з результату другої наступної функції XLOOKUP.
Ось і все! Перший погляд на XLOOKUP, потужну, універсальну та досить просту у використанні нову функцію пошуку, яка може виконувати не тільки пошук за одним значенням, що виконується функціями VLOOKUP і HLOOKUP, але також двосторонній пошук значень, що виконується комбінуванням Функції INDEX і MATCH також.
* На жаль, функція XLOOKUP це НЕ зворотна сумісність з більш ранніми версіями Microsoft Excel , які підтримують тільки функції ВПР і ГПР або сумісні з поточними версіями , які поки не включають його в якості одного зі своїх підстановок функцій, таких як Excel 2019 і Excel Online . Це означає, що якщо ви надаєте спільний доступ до книги, яка містить формули XLOOKUP, з колегами або клієнтами, які використовують версію Excel, яка не містить цієї нової функції пошуку, усі ці формули повертатимуть #NAME? значення помилок, коли вони відкривають його робочий аркуш.
Функція XLOOKUP шукає діапазон або масив, а потім повертає елемент, який відповідає першому знайденому збігу. Якщо відповідності немає, XLOOKUP може повернути найближчу (приблизну) відповідність.
=XLOOKUP(пошукове_значення, масив_пошуку, масив_повернення, [якщо_не_знайдено], [режим_збігу], [режим_пошуку])
|
Аргумент |
опис |
|---|---|
|
шукане_значення Вимагається* |
Значення для пошуку |
|
пошуковий_масив вимагається |
Масив або діапазон для пошуку |
|
масив_повернення вимагається |
Масив або діапазон для повернення |
|
[якщо_не_знайдено] Додатково |
Якщо дійсний збіг не знайдено, поверніть наданий вами текст [if_not_found]. Якщо дійсний збіг не знайдено, а [if_not_found] відсутній, #N/A повертається. |
|
[match_mode] Додатково |
Вкажіть тип відповідності: 0 - Точна відповідність. Якщо нічого не знайдено, поверніть #N/A. Це значення за умовчанням. -1 - Точна відповідність. Якщо нічого не знайдено, поверніть наступний менший елемент. 1 - Точна відповідність. Якщо нічого не знайдено, поверніть наступний більший елемент. 2 – символ підстановки, де *, ? і ~ мають особливе значення. |
|
[режим_пошуку] Додатково |
Вкажіть режим пошуку для використання: 1 - Виконайте пошук, починаючи з першого елемента. Це значення за умовчанням. -1 - Виконати пошук у зворотному напрямку, починаючи з останнього елемента. 2. Виконайте двійковий пошук, який покладається на пошуковий_масив, сортований у порядку зростання. Якщо не відсортовано, будуть повернуті недійсні результати. -2 - Виконайте двійковий пошук, який покладається на пошуковий_масив, сортований у порядку спадання. Якщо не відсортовано, будуть повернуті недійсні результати. |
Приклад 1 використовує XLOOKUP для пошуку назви країни в діапазоні, а потім повертає її телефонний код країни. Він включає lookup_value (комірка F2), lookup_array (діапазон B2: B11) і аргументи return_array (діапазон D2:D11). Він не містить аргумент match_mode , оскільки XLOOKUP створює точну відповідність за умовчанням.
Примітка. XLOOKUP використовує масив пошуку та масив повернення, тоді як VLOOKUP використовує один масив таблиці, за яким іде номер індексу стовпця. Еквівалентна формула VLOOKUP у цьому випадку буде такою: =VLOOKUP(F2,B2:D11,3,FALSE)
———————————————————————————
Приклад 2 виконує пошук інформації про співробітника за ідентифікаційним номером працівника. На відміну від VLOOKUP, XLOOKUP може повертати масив із кількома елементами, тому одна формула може повертати як ім’я співробітника, так і відділ із клітинок C5:D14.
———————————————————————————
Приклад 3 додає аргумент if_not_found до попереднього прикладу.
———————————————————————————
Приклад 4 переглядає в стовпці C особистий дохід, введений у клітинку E2, і знаходить відповідну ставку податку в стовпці B. Він установлює if_not_found аргумент для повернення 0 (нуль), якщо нічого не знайдено. Аргумент match_mode встановлюється на 1, що означає, що функція шукатиме точний збіг, і якщо не зможе його знайти, повертає наступний більший елемент. Нарешті, аргумент search_mode встановлюється на 1, що означає функцію здійснюватиме пошук від першого елемента до останнього.
Примітка: XARRAY's lookup_array стовпець знаходиться праворуч від стовпець, тоді як VLOOKUP може переглядати лише зліва направо.return_array
———————————————————————————
Приклад 5 використовує вкладену функцію XLOOKUP для виконання як вертикального, так і горизонтального збігу. Спочатку він шукає Валовий прибуток у стовпці B, потім шукає Qtr1 у верхньому рядку таблиці (діапазон C5:F5) і, нарешті, повертає значення на перетині цих двох. Це подібно до спільного використання функцій INDEX і MATCH .
Порада. Ви також можете використовувати XLOOKUP, щоб замінити функцію HLOOKUP.
Примітка. Формула в клітинках D3:F3 така: =XLOOKUP(D2,$B6:$B17,XLOOKUP($C3, $C5:$G5,$C6:$G17)).
———————————————————————————
Приклад 6 використовується функція SUM і дві вкладені функції XLOOKUP для підсумовування всіх значень між двома діапазонами. У цьому випадку ми хочемо підсумувати значення для винограду, бананів і включити груші, які знаходяться між ними.
Формула в клітинці E3 така: =СУМА(XLOOKUP(B3,B6:B10,E6:E10):XLOOKUP(C3,B6:B10,E6:E10))
Як це працює? XLOOKUP повертає діапазон, тож під час обчислення формула виглядає так: =SUM($E$7:$E$9). Ви можете самостійно побачити, як це працює, вибравши клітинку з формулою XLOOKUP, подібною до цієї, а потім виберіть Формули > Аудит формули > Оцініть формулу, а потім виберіть Оцінити , щоб покроково виконати обчислення.
Як заборонити Microsoft Word відкривати файли в режимі лише для читання в Windows. Microsoft Word відкриває файли в режимі лише для читання, що робить неможливим їх редагування? Не хвилюйтеся, методи наведено нижче
Як виправити помилки під час друку неправильних документів Microsoft Word Помилки під час друку документів Word зі зміненими шрифтами, безладними абзацами, відсутнім текстом або втраченим вмістом є досить поширеними. Однак не варто
Якщо ви використовували перо або маркер для малювання на слайдах PowerPoint під час презентації, ви можете зберегти малюнки для наступної презентації або стерти їх, щоб наступного разу, коли ви показуватимете їх, розпочали з чистих слайдів PowerPoint. Дотримуйтесь цих інструкцій, щоб стерти малюнки пером і маркером: Стирання рядків на одній з […]
Бібліотека стилів містить файли CSS, файли мови розширюваної мови таблиць стилів (XSL) та зображення, які використовуються попередньо визначеними основними сторінками, макетами сторінок та елементами керування в SharePoint 2010. Щоб знайти файли CSS у бібліотеці стилів сайту видавництва: виберіть «Дії сайту»→ «Перегляд». Весь вміст сайту. З’являється вміст сайту. Бібліотека Style знаходиться в […]
Не перевантажуйте аудиторію гігантськими цифрами. У Microsoft Excel ви можете покращити читабельність своїх інформаційних панелей і звітів, відформатувавши числа, щоб вони відображалися в тисячах або мільйонах.
Дізнайтеся, як використовувати інструменти соціальних мереж SharePoint, які дозволяють особам і групам спілкуватися, співпрацювати, обмінюватися інформацією та спілкуватися.
Юліанські дати часто використовуються у виробничих середовищах як мітка часу та швидкий довідник для номера партії. Цей тип кодування дати дозволяє роздрібним продавцям, споживачам та агентам з обслуговування визначити, коли був виготовлений продукт, а отже, і вік продукту. Юліанські дати також використовуються в програмуванні, військовій справі та астрономії. Інший […]
Ви можете створити веб-програму в Access 2016. Так що ж таке веб-програма? Ну, веб означає, що він онлайн, а додаток — це просто скорочення від «додаток». Користувацька веб-програма — це онлайн-додаток для баз даних, доступ до якого здійснюється з хмари за допомогою браузера. Ви створюєте та підтримуєте веб-програму у настільній версії […]
Більшість сторінок у SharePoint 2010 відображають список посилань навігації на панелі швидкого запуску ліворуч. Панель швидкого запуску відображає посилання на пропонований вміст сайту, наприклад списки, бібліотеки, сайти та сторінки публікації. Панель швидкого запуску містить два дуже важливі посилання: Посилання на весь вміст сайту: […]
Для простих задач Solver в Excel зазвичай швидко знаходить оптимальні значення змінної Solver для цільової функції. Але в деяких випадках Solver має проблеми з пошуком значень змінної Solver, які оптимізують цільову функцію. У цих випадках Solver зазвичай відображає повідомлення або повідомлення про помилку, що описує або обговорює проблему, яка […]
