Как в excel пользоваться формулами в: 10 формул Excel, которые пригодятся каждому

Содержание

Формулы Excel все особенности, тонкости и трюки в одном видео!

содержание видео

Рейтинг: 4.0; Голоса: 1

В этом видеоуроке мы за тридцать минут рассмотрим с тобой все самые важные основы работы с формулами и функциями в Excel. Узнав и практикуя все советы из этого видео, ты сможешь вывести свой уровень работы с формулами в Excel на абсолютно новый уровень, по-настоящему профессиональный уровень.
В этом видео я тебе расскажу как самые основы работы с формулами в Excel, такие как, например, относительность и абсолютность ссылок, использование смешанных ссылок, как прописывать значения для формул и как происходит вычисление формул и так далее, так и советы для уже продвинутых пользователей, такие, как, например, использование шорткатов и комбинаций клавиш, в том числе мнемоников, как быстро прописывать формулы и как в принципе понять функционирование формул. Мы рассмотрим, что из себя представляет специальная вставка при копировании, как варианты специальной вставки существуют и в каких ситуациях их использовать.
Все это мы будем рассматривать для того, чтобы разбор отдельных функций в последующих видео этого плейлиста проходил намного эффективнее и результативнее
Дата: 2021-09-02

← Автозаполнение Формуляров и Документов на основе Excel

Простейшие функции Excel: СУММ, СРЗНАЧ, СЧЁТ, СЧЁТЗ, МИН, МАКС, СТОЛБЕЦ и т. д. — Функции Excel (3) →

Похожие видео

Как забыть или удалить Wi-Fi сеть Windows 11?

• Дневник Сисадмина

Как исправить Без доступа в интернет, защищено в Windows 11?

• Дневник Сисадмина

Квартал в Excel. Необычная формула с функцией ВПР

• Обучение Microsoft Office

Как разделить текст в ячейке по нескольким столбцам

• Обучение Microsoft Office

Как преобразовать MBR в GPT без потери данных Windows 11?

• Дневник Сисадмина

Wi-FI в Windows 11? Как включить wi-fi, почему нет кнопки wi-fi, нет настроек wi-fi адаптера?

• Дневник Сисадмина

Комментарии и отзывы: 5

Hemul
2: 57 небольшая поправка. Если вы до целевой ячейки дошли при помощи TAB, то при нажатии ENTER, программа переместит курсор на ячейку ниже стартовой. Пример Курсор находится в ячейке С3, вы три раза нажимаете ТАБ, курсор становится в F3. Вы пишете формулу или статичное значение и нажимаете ENTER. Курсор становится в ячейку С4. Это бывает очень удобно при заполнении таблиц, и перемещении по строке при помощи ТАБ, а при нажатии на ENTER курсор становится в начало строки на строку ниже.

Денис
Мне кажется полезнее было бы рассказать как пользоваться формулами через кнопку вставить функцию. Оттуда можно выбрать любую формулу, там и описание как ей пользоваться, нужно как раз рассказать как правильно понимать логику описаний. А прописывать формулы вручную — это приходит с опытом, после того как разберёшься как работает формула. Но, твоя работа в любом случае полезна, спасибо.

Paradise
Здравствуйте! Подскажите пожалуйста формулу.
Есть пять поставщиков с разными ценами, надо чтобы в итоговой ячейке был поставщик с наименьшей ценой(именно наименование поставщика, но не цена его)
Буду очень благодарен!

Anmagro
Супер видеоуроки, если ваши уроки действительно принесут мне пользу на работе, обещаю спонсировать ваш канал) Классно, понятно, а главное всё развёрнуто не остаётся ни одного вопроса. Ещё раз палец вверх

Ragnars
Likeза работу! Но видео очень долгое. Нехочу учить но даказано что интерес и концентрация эффективны в 15 минутных роликах.
Но всеравно спасибо за видео.

Как использовать ChatGPT для создания формул Excel

«»

ChatGPT, чат-бот с искусственным интеллектом (AI), разработанный OpenAI, может помочь вам при работе в таких программах, как Excel и Google Sheets. Тада Изображения/Shutterstock

С тех пор, как ChatGPT был запущен для общего пользования, его приложения варьировались от отладки кода до списывания эссе и перевода цитат из вашего любимого шоу на шекспировский английский (серьезно, попробуйте). Но одним из наиболее практичных и недооцененных способов использования программного обеспечения является создание формул для использования в Excel и Google Sheets. Узнайте, как с помощью ChatGPT стать настоящим мастером электронных таблиц.

Реклама

Содержание

  1. Что такое ChatGPT?
  2. Как использовать ChatGPT для создания формул в Excel и Google Sheets
  3. Советы по использованию ChatGPT для создания электронных таблиц

htm»>

Что такое ChatGPT?

ChatGPT использует алгоритм машинного обучения, основанный на огромном количестве текстовых данных, чтобы генерировать человеческие ответы на запросы на естественном языке. Другими словами, ChatGPT — это программа искусственного интеллекта (ИИ), которая отвечает на ваши вопросы простыми для понимания ответами. ChatGPT может давать рекомендации, генерировать текст и даже поддерживать беседу — и это полезно для таких приложений, как написание кода или создание формул Excel.

Реклама

Как использовать ChatGPT для создания формул в Excel и Google Sheets

При использовании ChatGPT для создания электронных таблиц начните с простого и постепенно усложняйте. Если в какой-то момент вы почувствуете себя потерянным, вы всегда можете попросить ИИ перефразировать или более подробно разобрать свое объяснение. Начните с того, что попросите ChatGPT показать вам пример типа таблицы, которую вы хотите создать, и приступайте к наполнению вашей электронной таблицы формулами оттуда.

Шаг 1. Попросите ChatGPT показать вам шаблон

Допустим, вы хотите составить бюджет, но даже не знаете, с чего начать. ChatGPT может помочь вам, смоделировав пример типа электронной таблицы, которую вы хотите создать. Например, вы можете попросить ChatGPT сгенерировать образец семейного бюджета, например:

Реклама

«»

ChatGPT может помочь вам создать личный бюджет в Google Таблицах.

ЧатGPT

Используйте этот шаблон для создания базового бюджета в собственной электронной таблице. Очистите содержимое строки «Итого» и столбца «Разница» — вы будете использовать формулы для автоматического расчета этих значений.

Шаг 2. Попросите ChatGPT сгенерировать формулы

На данный момент таблица, созданная ChatGPT, не содержит рабочих формул. Чтобы сделать его пригодным для использования, вам нужно добавить формулу для расчета значений в столбце «Разница» в вашей собственной электронной таблице. Если у вас есть «Бюджет» в столбце B и «Фактический» в столбце C, вы можете попросить ChatGPT сгенерировать формулу для нахождения разницы между ними:

«»

ChatGPT может генерировать формулы для вас, когда вы получаете правильные подсказки.

ChatGPT

Кроме того, вам понадобится формула для вычисления суммы каждого столбца. ChatGPT может сгенерировать для вас это:

«»

ChatGPT может генерировать полезные формулы на основе предоставленной вами информации.

ЧатGPT

Скопируйте и вставьте эту формулу в строку 10 столбца B под вашим бюджетом для каждой категории. Это рассчитает сумму всего столбца. Используйте версию этой же формулы для расчета суммы значений в столбцах C и D. После того, как вы это сделаете, у вас будет элементарный рабочий бюджет.

Шаг 3. Попросите ChatGPT объяснить, как использовать функции

Зачем останавливаться на формулах? ChatGPT может помочь вам добавить дополнительные функции в вашу электронную таблицу. Допустим, вы хотите выделить категории, в которых ваши фактические расходы превысили бюджет, красным цветом, а категории, в которых вы уложились в бюджет (или не выходили за рамки бюджета), — зеленым. Вы можете попросить ChatGPT показать вам, как это сделать, задав такой вопрос:

«»

ChatGPT может помочь вам точно определить, что вам нужно для достижения определенного результата в таблицах Excel/Google.

ChatGPT

После всего этого ваша результирующая таблица в Google Sheets должна выглядеть так:

«»

Конечным результатом будет таблица, которую вы можете настроить в соответствии со своими параметрами.

ЧатGPT

По мере продвижения вы можете запрашивать у ChatGPT все более сложные формулы для использования в вашей таблице. Даже если вы точно не знаете, о чем просить, ИИ может подсказать вам нужную формулу, если вы зададите достаточно уточняющих вопросов.

Шаг 4. Спросите, какие дополнительные функции вам следует включить

Наконец, вы даже можете попросить ChatGPT порекомендовать дополнительные функции для вашего бюджета:

«»

ChatGPT может даже дать вам некоторые идеи, которые вы, возможно, не рассматривали.

ЧатGPT

Если вы хотите узнать больше о конкретной формуле и о том, как использовать ее в электронной таблице, просто попросите ChatGPT уточнить. Вы можете использовать основные шаги, описанные здесь, для создания формул Excel для широкого спектра электронных таблиц — от планов тренировок до графиков амортизации.

Реклама

Советы по использованию ChatGPT для создания электронных таблиц

Путем проб и ошибок вы обнаружите, что использование ChatGPT для управления созданием электронных таблиц является интуитивно понятным и простым. Если вы новичок в программном обеспечении, помните о следующих рекомендациях:

  • Будьте точны. Чем конкретнее вы можете указать свой запрос, тем больше вероятность того, что ChatGPT даст вам точный ответ. Когда вы просите ChatGPT сгенерировать формулы, укажите точные ячейки, которые вы хотите включить в формулу. Например, вы можете попросить чат-бота «Создать формулу Excel для усреднения значений в ячейках с B2 по B9»..»
  • Перефразируйте или отредактируйте свой вопрос. ChatGPT может неправильно понять ваш вопрос или предоставить неточный результат. Если формула, созданная ChatGPT, не работает должным образом, вернитесь к чат-боту и попросите его снова создать формулу — но на этот раз попробуйте перефразировать свой вопрос. Вы также можете отредактировать вопрос, который вы уже отправили, нажав значок «Изменить» в правом верхнем углу вашего запроса в интерфейсе ChatGPT.
  • Запросить объяснение. Еще одна полезная информация Особенностью ChatGPT является его способность объяснять, как работает данная формула Excel или Google Sheets.Если вы скопировали электронную таблицу откуда-то из Интернета и хотите понять, как генерируются определенные значения, вы можете попросить ChatGPT объяснить это для вас. формулу из ячейки и вставьте ее в ChatGPT, попросив ИИ показать вам, как работает формула.0012
  • Устранение неполадок с формулами, которые не работают. Если вы написали формулу самостоятельно, и она не работает, вы можете скопировать и вставить формулу в ChatGPT и попросить чат-бота объяснить, что с ней не так.

Реклама

​

Процитируйте это!

Пожалуйста, скопируйте/вставьте следующий текст, чтобы правильно цитировать эту статью HowStuffWorks.com:

Томас Харландер
«Как использовать ChatGPT для создания формул Excel»
31 мая 2023 г.
HowStuffWorks.com.
17 июня 2023 г.

Citation

Как использовать функцию XLOOKUP в Excel [шаг за шагом]

Функция XLOOKUP в Microsoft Excel — это мощный инструмент поиска, позволяющий находить определенные значения в диапазоне ячеек. Он заменяет более ранние функции поиска, такие как ВПР, устраняя многие ограничения. В этом руководстве мы объясним, как использовать XLOOKUP шаг за шагом.

Как следует из названия, функции поиска, такие как XLOOKUP , извлекают соответствующие данные (точные или ближайшие доступные совпадения) из набора данных в Excel. Если имеется более одного совпадающего результата, возвращается первый или последний результат в наборе, в зависимости от используемых аргументов.

XLOOKUP действует как настраиваемый инструмент поиска, гибкий к требованиям различных типов данных в настраиваемых электронных таблицах Excel. XLOOKUP — это относительно новая функция, которая станет доступна для пользователей Microsoft 365 только в 2020 году. Хотя XLOOKUP имеет сходство с другими функциями поиска, необходимо учитывать важные отличия.

В этой статье мы объясним:

  1. Что такое функция XLOOKUP и как она работает?
  2. Как работает функция XLOOKUP в Microsoft Excel?
  3. На что обратить внимание перед использованием функции XLOOKUP в Excel
  4. Как использовать XLOOKUP в формуле Excel: пошаговое руководство

Вы пытаетесь разобраться с функцией XLOOKUP? Тогда продолжайте читать. В этом посте вы найдете все, что вам нужно знать.

1. Что такое функция XLOOKUP и как она работает?

В Excel уже есть ряд инструментов для поиска в данных, от правил условного форматирования до встроенного инструмента поиска и выбора. Эти функции, однако, на самом деле не меняют вашу электронную таблицу значимым образом. Например, вы не можете легко ссылаться на отформатированную ячейку по цвету в другой формуле автоматически.

Что делать, если у вас есть данные, которые вам нужно быстро найти, с пользовательскими критериями поиска, которые вам нужно регулярно менять? Это нередкий сценарий для аналитиков данных, которым может понадобиться искать и возвращать отдельные результаты из набора данных с запросами, которые можно быстро настроить.

Почтовые адреса, результаты классных экзаменов, нормы прибыли — если вам нужны отдельные результаты из подобных наборов данных, лучше всего использовать функцию поиска. Функции поиска являются основным продуктом сложных электронных таблиц Excel, причем VLOOKUP и HLOOKUP являются одними из наиболее распространенных.

Проблема с формулами ВПР (вертикальный поиск) и ГПР (горизонтальный поиск) заключается в том, что, как следует из удлиненных имен, они довольно ограничены по объему, что вынуждает вас использовать обходные пути для работы с данными слева или справа от ценности (среди прочего).

Некоторые из этих ограничений можно обойти с помощью других функций (таких как ИНДЕКС и ПОИСКПОЗ), но этот процесс неэффективен. XLOOKUP — это решение этой проблемы, позволяющее искать данные в столбцах и строках как слева, так и справа от искомого значения.

Он может обрабатывать приблизительные совпадения, несколько значений поиска, вложенные запросы (с использованием нескольких функций XLOOKUP в одной формуле), настраиваемые сообщения об ошибках и многое другое. XLOOKUP (в настоящее время) доступен только для Подписчики Microsoft 365 , но как для простого, так и для сложного поиска данных это абсолютно меняет правила игры.

2. Как работает функция XLOOKUP в Microsoft Excel?

Чтобы правильно объяснить функцию XLOOKUP, мы будем использовать пример сценария. Пример электронной таблицы Excel содержит список сотрудников небольшой компании, содержащий идентификаторы, имена, адреса электронной почты и обязанности.

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

XLOOKUP может найти значение (совпадающее полностью или в ближайшем приближении) из диапазона ячеек (или массива поиска) и вернуть его. Для этого формула, использующая XLOOKUP, имеет ряд аргументов, которые необходимо использовать для создания запроса.

К ним относятся lookup, lookup_array, return_array, not_found (необязательно), match_mode (необязательно) и search_mode (необязательно). Для создания формулы XLOOKUP требуется следующая структура:

=XLOOKUP(просмотр, просматриваемый_массив, возвращаемый_массив, не найдено, режим_поиска, режим_поиска )

Обязательные аргументы (просмотр, просматриваемый_массив, возвращаемый_массив)

В этом примере поиск относится к значению поиска, которое мы ищешь. Например, если мы ищем идентификационный номер, мы будем использовать само значение (например, 101 ) или ссылку на ячейку, содержащую это значение (например, E3 ).

Чтобы найти эти значения, нам нужно определить диапазоны ячеек для lookup_array и return_array . В этом случае lookup_array — это столбец или строка, содержащая значение lookup (например, A2:A10 для идентификационных номеров в столбце A ).

return_array — это отдельный столбец или строка, содержащая данные, которые вы хотите вернуть. Например, если мы ищем идентификационные номера, но вместо этого хотим вернуть имя сотрудника, нам потребуется правильный диапазон ячеек, содержащий эти имена (например, 9).0007 B2:B10 для имен сотрудников в столбце B ).

Необязательные аргументы (not_found, match_mode, search_mode)

Если вы хотите вернуть пользовательское значение при отсутствии совпадающих результатов, вам нужно добавить аргумент not_found . Это может быть число, текстовая строка (в кавычках), логическое значение ( TRUE или FALSE ) или ссылка на ячейку (например, 1, «не найдено», или G1 ). Если вы не укажете это, Excel вернет #Н/Д Ошибка для несовпадающих значений.

XLOOKUP по умолчанию ищет точные совпадения со значением поиска . Если вы хотите изменить это, вы можете указать необязательный аргумент match_mode в формуле XLOOKUP. Это числовое значение, где 0 используется для точного совпадения, -1 используется для точного совпадения (или следующего за ним значения), 1 для точного совпадения (или следующего за ним значения) и 2 для подстановочных знаков.

Если вы решите использовать 2 для match_mode , вы можете использовать операторы подстановки, такие как звездочка (*), вопросительный знак ( ? ) или тильда ( ~ ) с поиском значений. В результате Excel вернет первое доступное совпадение со значением поиска .

Наконец, вы можете определить порядок поиска возвращаемых значений с помощью аргумента search_mode . XLOOKUP будет немедленно соответствовать первому значению по умолчанию ( 1 значение для search_mode , если вы хотите или должны указать его), или вы можете изменить это, чтобы использовать последнее совпадающее значение с -1 .

Вы также можете использовать двоичный поиск в порядке возрастания с 2 или в порядке убывания с -2 . Бинарный поиск — это, по сути, быстрый тип рекурсивного поиска с использованием упорядоченного набора данных, поэтому вам необходимо убедиться, что вы отсортировали данные lookup_array и return_array , прежде чем использовать такой поиск.

3. На что следует обратить внимание перед использованием функции XLOOKUP в Excel

Функция XLOOKUP имеет значительные улучшения по сравнению с более ранними функциями поиска, но есть несколько моментов, которые необходимо учесть перед ее использованием. К ним относятся:

  • В отличие от ВПР или ГПР, ВПР может использовать данные как по горизонтали (в строках), так и по вертикали (в столбцах).
  • Функция XLOOKUP может возвращать один или несколько результатов в зависимости от используемой формулы. Для такого запроса вам, вероятно, потребуется использовать вложенные функции XLOOKUP в одной формуле.
  • Если функция XLOOKUP не может найти соответствующий результат и вы хотите увидеть специальное сообщение об ошибке, вам потребуется использовать аргумент not_found (используя числа, текстовые строки, логические значения или ссылки на ячейки). В противном случае Excel вернет ошибку #N/A .
  • Хотя для значения аргумента return_array можно использовать другой диапазон, он должен быть того же размера (то же количество ячеек), что и lookup_array . Если это не так, Excel вернет #ЗНАЧ ошибка.
  • XLOOKUP можно использовать для возврата значений между различными электронными таблицами Excel, но для его работы должны быть открыты обе электронные таблицы. В противном случае Excel вернет #ССЫЛКА! ошибка .
  • XLOOKUP возвращает в качестве результата ссылку на ячейку, а не значение. Например, если значение поиска ячейки A2 является числом 4 , то XLOOKUP технически вернет A2, , а не 4 (хотя вы увидите только 4 ). Это технический момент, который не должен влиять (или быть полезным) для большинства запросов XLOOKUP, но может оказаться полезным для более сложных поисков.
  • Возможны вложенные формулы (содержащие несколько функций XLOOKUP), которые заменяют комбинации ИНДЕКС и ПОИСКПОЗ в сложных поисковых запросах. Они выполняются поэтапно (поэтому, если X = 1 и Y = 2, вернуть Z). Вы также можете использовать XLOOKUP с другими функциями (такими как SUM или SUMIF).
  • Если вы пытаетесь использовать функцию XLOOKUP в версии Excel, которая ее не поддерживает, вы увидите Возвращена ошибка #NAME . Вам потребуется переключиться на поддерживаемую версию, например последнюю версию Microsoft 365, или с помощью Excel Online.

4. Как использовать функцию XLOOKUP в формуле Excel: пошаговое руководство

Если вы хотите создать формулу с помощью функции XLOOKUP в Excel, вам необходимо выполнить следующие действия.

Шаг 1. Выберите пустую ячейку

Чтобы вставить функцию XLOOKUP в новую формулу, начните с открытия электронной таблицы Excel, содержащей набор данных, и выбора пустой ячейки. Либо откройте новую электронную таблицу, оставив электронную таблицу, содержащую ваши данные, свернутой в другом окне.

После выбора ячейки нажмите строку формул на ленточной панели, чтобы начать ее редактирование.

Ячейка активна и готова к редактированию, когда на ленточной панели виден мигающий курсор. Если это так, вы готовы начать вставку новой формулы XLOOKUP.

Шаг 2: Определите критерии поиска (поиска)

Как мы объяснили выше, новая формула, использующая функцию XLOOKUP, имеет особый синтаксис и структуру, которой необходимо следовать. Первый шаг — начать вводить формулу. Для этого введите =XLOOKUP( в строке ленты.

Следующим шагом является вставка критерия поиска . Это данные, которые вы ищете для поиска. Хотя функция XLOOKUP поддерживает числа и текстовые строки, большинству пользователей потребуется чтобы указать ссылку на ячейку (например, A2 ). Это позволит вам изменять критерии поиска (и, следовательно, результаты) по желанию, не изменяя напрямую формулу XLOOKUP.

Например, используя наш пример со списком сотрудников, = XLOOKUP(E3 будет использовать значение в ячейке E3 в качестве значения поиска. В этом случае ячейка E3 содержит значение 103 , соответствующее идентификационному номеру сотрудника.

XLOOKUP по умолчанию попытается найти точное совпадение. Чтобы изменить это, обязательно добавьте необязательный аргумент match_mode позже (см. шаг 5 ниже).

Шаг 3. Определите набор данных с помощью lookup_array и return_array

После того, как вы вставили поиск (ваше значение поиска или ссылка на ячейку, содержащая его), вам необходимо определить диапазоны ячеек lookup_array и return_array .

lookup_array — это строка или столбец, которые могут содержать ваш поисковый запрос (или ближайший приблизительный результат). return_array — это строка или столбец, которые вы хотели бы видеть возвращенными в результате.

Это может быть ячейка в другом столбце в той же строке (например, сопоставление идентификационных номеров с должностями в электронной таблице сотрудников).

В нашем примере использование =XLOOKUP(E3,A2:A10,D2:D10 означает, что используемый lookup_array является вертикальным диапазоном ячеек A2:A10 , а return_array 9 0008 — вертикальная ячейка диапазон D2:D10 .

На этом этапе, если вы не заинтересованы в добавлении пользовательского сообщения об ошибке, использовании другого типа поиска (например, приблизительное совпадение или совпадение с подстановочными знаками) или изменении порядка значений поиска, вы можете закрыть формулу с закрывающей скобкой. В противном случае продолжайте добавлять в формулу необязательные аргументы, следуя инструкциям ниже.

Шаг 4 (необязательно): добавьте пользовательское сообщение об ошибке, используя not_found

Если вы пытаетесь найти точное совпадение со значением поиска , но оно не может быть найдено в диапазоне lookup_array , Excel возвращает ответ об ошибке #N/A . Чтобы преодолеть это, вам нужно добавить необязательный аргумент not_found в формулу XLOOKUP.

Аргумент not_found может быть числовым значением (например, 1 ), текстовой строкой в ​​кавычках (например, «не найдено» ), логическое значение ( TRUE или FALSE ) или ссылка на ячейку (например, A2 ).

Например, =XLOOKUP(E3,A2:A10,D2:D10,»не найдено», гарантирует, что Excel вернет сообщение не найдено , если точное совпадение со значением в E3 может быть Поскольку идентификационные номера в ячейке A2 — A10 находятся в диапазоне от 101 до 109, и поиск 9Значение 0008 в ячейке E3 равно 110 , как следствие, Excel возвращает не найдено .

Поскольку каждый аргумент рассматривается последовательно, вам потребуется добавить пользовательский аргумент not_found , если вы планируете добавлять в формулу дополнительные аргументы match_mode и search_mode ниже.

Шаг 5 (необязательно): задайте точные, приблизительные или подстановочные совпадения при поиске с помощью match_mode

Хотя функция XLOOKUP предназначена для поиска точных совпадений по умолчанию, это можно изменить, добавив необязательное значение match_mode значение аргумента для вашей формулы.

Возможны четыре значения: 0 для точного совпадения (значение по умолчанию, если оно не добавлено), – 1 для точного совпадения или следующего за ним значения, 1 для точного совпадения или следующее значение над ним, или 2 для частичного совпадения с использованием звездочек, тильд или подстановочных знаков.

Используя наш пример электронной таблицы, частичная формула =XLOOKUP(E3,B2:B10,C2:C10,»не найдено»,2 гарантирует, что XLOOKUP поддерживает запрос с подстановочными знаками. В этом случае функция XLOOKUP находит ближайшее значение, соответствующее запросу с подстановочным знаком ( ji *) в диапазоне lookup_array B2:B10 , а затем возвращает соответствующее значение в той же строке из диапазона return_array C2: C10 (адрес электронной почты).

В качестве подстановочного знака это будет первое, самое близкое приближение. Если вы хотите изменить порядок поиска (например, используя последнее возможное значение, а не первое), вам нужно добавить search_mode следующий аргумент.

Шаг 6 (необязательно): Определите порядок возврата значений с помощью search_mode

Необязательный аргумент search_mode является последним поддерживаемым аргументом в формуле XLOOKUP. Это изменяет порядок, в котором функция XLOOKUP получает соответствующий результат. По умолчанию Excel попытается сопоставить первое возможное значение.

Например, в простом запросе с подстановочными знаками, где J * — значение поиска , массив, содержащий имена James, Jack, Jim, Jess, Julia и Jane означают, что 101 — это обычно возвращаемое значение, поскольку James — это первое значение в запросе, где все значения потенциально совпадают.

Если вы хотите изменить это поведение, чтобы вместо этого найти последнее возможное значение, вам нужно изменить аргумент search_mode .

Существует четыре возможных значения аргумента search_mode : 1 ищет первое возможное значение (это опция по умолчанию), а -1 меняет это на поиск последнего возможного значения. Кроме того, вы можете выполнить двоичный поиск в порядке возрастания с помощью 2 или обратный двоичный поиск в порядке убывания с помощью -2 .

Двоичный поиск может выполняться быстрее, уменьшая количество операций поиска в массиве данных для поиска правильного значения (и, таким образом, уменьшая общее влияние запроса на память, особенно при работе с большими наборами данных), но вам потребуется сортировать данные. сначала, прежде чем использовать его.

Большинство пользователей, вероятно, захотят придерживаться 1 или -1 вместо search_mode , но если вы работаете с большим объемом данных, 2 или -2 уменьшит влияние.

![Электронная таблица Excel, содержащая четыре столбца данных: имя сотрудника, идентификатор, адрес электронной почты и должность. В этом случае в формулу XLOOKUP был добавлен аргумент search_mode.](https://d3mm2s9r15iqcv.cloudfront.net/en/wp-content/uploads/old-blog-uploads/excel-xlookup-final.png Электронная таблица Excel, содержащая четыре столбца данных: имя сотрудника, идентификатор, адрес электронной почты и должность. В этом случае в формулу XLOOKUP был добавлен аргумент search_mode».0003

Например, в формуле =XLOOKUP(E3,A2:A10,D2:D10,»не найдено»,-1,1) Excel находит точное совпадение или следующее и наименьшее значение , соответствующее поиску значение в ячейке E2 и возвращает его. Если search_mode установлен как 1 , Excel будет искать в порядке возрастания , возвращая первое совпадающее значение в диапазоне.

В этом случае значение в E2 установлено как 110 , и при отсутствии значений, предлагающих точное соответствие, следующим и наименьшим результатом будет ID номер 109 .

Читайте также: