Составьте краткое изложение этой истории. Вот краткое изложение содержания истории, основанное на фактах: Вот упрощенная версия содержания истории: Вот беззаботный взгляд на содержание истории: Исследуйте другую точку зрения: Покажите мне факты Объясните это так, будто мне 5 лет Дайте мне беззаботный пересказ
Функция XLOOKUP в Microsoft Excel наиболее известна тем, что извлекает значение из определенной строки или столбца в наборе данных. Однако вы также можете использовать этот мощный инструмент для выполнения двухстороннего поиска, возвращая значение на пересечении указанной строки и столбца.
Если вы работаете в настольном приложении Excel на ПК или Mac, вам необходимо использовать Excel 2021 или более позднюю версию (включая Excel для Microsoft 365) для доступа к функции XLOOKUP. Она также легко доступна в Excel для веб-сайта и в приложениях Excel для планшетов и мобильных устройств.
Microsoft 365 персональный
Microsoft 365 включает доступ к приложениям Office, таким как Word, Excel и PowerPoint, на пяти устройствах, 1 ТБ хранилища OneDrive и многое другое.
$99.99 в Microsoft $99.99 в Amazon Развернуть Свернуть
Использование XLOOKUP для одностороннего поиска в Excel
Ключом к пониманию того, как XLOOKUP можно использовать для выполнения двустороннего поиска, является сначала понимание того, как эта функция работает в одномерных сценариях.
Функция XLOOKUP имеет шесть аргументов:
=XПРОСМОТР(a,b,c,d,e,f)
в котором
- a (обязательно) — искомое значение,
- b (обязательно) — это массив поиска,
- c (обязательно) — возвращаемый массив,
- d (необязательно) — текст, который будет возвращен, если искомое значение (a) не найден в массиве поиска (b),
- e (необязательно) — режим соответствия (0 = точное совпадение; -1 = точное совпадение или следующий меньший элемент; 1 = точное совпадение или следующий больший элемент; 2 = подстановочное совпадение) и
- f (необязательно) — режим поиска (1 = сверху вниз или слева направо; -1 снизу вверх или справа налево; 2 = двоичный поиск с использованием массива поиска (b) в порядке возрастания; -2 = двоичный поиск с использованием массива поиска (b) в порядке возрастания.
Если аргумент d опущен, Excel возвращает ноль, если искомое значение не найдено в массиве поиска. Если аргументы e и f не учтены, применяется параметр по умолчанию (точное совпадение с аргументом e, и поиск аргумента сверху вниз или слева направо f).
В этом примере введите:
=XLOOKUP(F2,A2:A100,D2:D100,»Invalid ID»,0,1)
в ячейку G2 возвращается оценка 48.
Эта формула принимает идентификатор из ячейки F2 (аргумент a), ищет его в ячейках от A2 до A100 (аргумент b) и возвращает соответствующее значение в ячейках D2–D100 (аргумент c). Если идентификатор в ячейке F2 не указан в ячейках A2–A100, поиск возвращает термин «Недопустимый идентификатор» (аргумент d). Пятый аргумент сообщает Excel, что нужно возвращать только точные совпадения (аргумент e), а шестой гарантирует, что поиск выполняется от верхней части диапазона до нижней части диапазона (аргумент f).
Использование XLOOKUP для двухстороннего поиска в Excel
Хотя односторонние поиски полезны во многих сценариях, они не позволяют вам изменять переменную, которую вы ищете. Действительно, формула выше возвращает только оценку на основе указанного идентификатора, но что, если вы хотите увидеть возраст или пол вместо этого? Вот где в игру вступает двусторонний поиск (также известный как двумерный или матричный поиск).
Для этого вам необходимо вложить один XLOOKUP в другой:
=XПРОСМОТР(a¹,b¹,ПРОСМОТР(а²,б²,c))
в котором
- а¹ и b¹ — это искомое значение и массив для первой переменной,
- а² и б² — это искомое значение и массив для второй переменной, а
- c — это возвращаемый массив.
Вы также можете добавить аргументы d, e и f для каждого XLOOKUP точно так же, как вы бы делали при выполнении одностороннего поиска. Однако, чтобы не усложнять, я исключил их из синтаксиса выше и примеров ниже.
Исходя из этого, набираем:
=XLOOKUP(F2,A2:A100,XLOOKUP(G1,B1:D1,B2:D100))
в ячейку G2 возвращается оценка 48.
Это происходит потому, что Excel сначала ищет указанный идентификатор (аргумент а¹) в ячейках от A2 до A100 (аргумент b¹), затем он ищет указанную переменную (аргумент а²) в ячейках B1–D1 (аргумент б²) и возвращает результат на пересечении этих двух поисков в ячейках от B2 до D100 (аргумент c).
В результате, если изменить переменную в ячейке G1 на «Возраст», то получится результат 41.
Аналогично, если вы измените его на «Пол», то в результате получите М.
В двумерных формулах XLOOKUP не имеет значения, в каком направлении идут XLOOKUP. В этом примере поиск идентификатора идет первым в формуле, а поиск заголовка столбца вложен. Если бы их поменяли местами, вы бы получили тот же результат.
Совет профессионала: добавьте раскрывающиеся списки в ячейки поиска
Теперь, когда двусторонний поиск завершен, вы можете сделать еще один шаг, добавив раскрывающийся список к ячейкам, содержащим искомые значения, чтобы ускорить процесс извлечения данных.
Для этого выберите одну из ячеек подстановки (в данном случае ячейку G1) и на вкладке «Данные» на ленте нажмите «Проверка данных».
Далее в поле Разрешить диалогового окна Проверка данных выберите «Список». Наконец, активируйте поле «Источник» и выберите ячейки, содержащие значения, которые вы хотите отобразить в раскрывающемся списке. В данном случае это заголовки столбцов в ячейках B1–D1.
Нажав «ОК», вы увидите раскрывающуюся кнопку в ячейке G1, при нажатии на которую открываются параметры переменной.
Теперь повторите процесс для ячейки F2, другой ячейки поиска.
Обратите внимание, как в поле Источник я указал диапазон, который выходит за пределы размера моего набора данных, чтобы обеспечить рост (мой набор данных содержит 100 строк, но я расширил источник до ячейки A1000). Точка (также известная как оператор trim ref в этом контексте) после двоеточия сообщает Excel о необходимости обрезать все пустые ячейки в конце исходного диапазона.
В качестве альтернативы можно использовать формулу, которая подсчитывает количество ячеек, содержащих значения в столбце A, и возвращает это количество вариантов в раскрывающемся меню.
Теперь вы можете выбрать идентификатор из раскрывающегося списка в ячейке F2.
Вы также можете выполнять двусторонний поиск в Excel с помощью ИНДЕКС и ПОИСКПОЗ. Этот альтернативный вариант особенно полезен, если вы используете Excel 2019 или более раннюю версию, так как эти функции были введены в программу до XLOOKUP.
