Функции для поиска и извлечения значений

Книга: Магия таблиц. 100+ приемов ускорения работы в Excel (и немного в Google Таблицах)

Файл с примерами. "Функции поиска"

ФУНКЦИЯ ВПР / VLOOKUP

Функция ВПР / VLOOKUP (вертикальный просмотр) нужна, чтобы связать несколько таблиц — подтянуть данные из одной в другую по какому-то ключу (например, названию товара или бренда, фамилии сотрудника или клиента, номеру транзакции):

=ВПР (что ищем; таблица с данными, где "что ищем" должно быть в первом столбце; номер столбца таблицы, из которого нужны данные; [интервальный просмотр])

У нее есть два режима работы — интервальный просмотр и точный поиск.

Интервальный просмотр — это поиск интервала, в который попадает число. Если у вас прогрессивная шкала налога или скидок, если нужно конвертировать оценку из одной системы в другую и так далее, то используется именно этот режим. Для интервального просмотра нужно пропустить последний аргумент ВПР или задать его равным единице (или ИСТИНА).

В большинстве случаев мы связываем таблицы по текстовым ключам, в таком случае нужно обязательно явным образом указывать последний аргумент «интервальный_просмотр» (range_lookup) равным нулю (или ЛОЖЬ / FALSE). Только тогда функция будет корректно работать с текстовыми значениями.

ФУНКЦИИ ПОИСКПОЗ / MATCH И ИНДЕКС / INDEX

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

В реальных условиях структура таблиц бывает разной и не всегда возможно изменить порядок столбцов. Поэтому важно уметь работать с любой структурой.

Если у вас есть функция ПРОСМОТРX / XLOOKUP (Excel 2021, Microsoft 365), то пользуйтесь ею вместо ВПР: она гораздо проще по синтаксису и не имеет этого ограничения (о ней ниже).

Если ее нет (у вас Google Таблицы, Excel 2019 или ранее), то пользуйтесь сочетанием ИНДЕКС + ПОИСКПОЗ.

Функция ПОИСКПОЗ / MATCH определяет порядковый номер значения в диапазоне. Ее синтаксис:

=ПОИСКПОЗ (что ищем; где ищем; 0)

На выходе — число (номер строки или столбца в рамках диапазона, в котором находится искомое значение).

ИНДЕКС / INDEX выполняет другую задачу — возвращает элемент по его номеру:

=ИНДЕКС(диапазон, из которого нужны данные; порядковый номер элемента)

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

Получается следующая конструкция:

=ИНДЕКС(диапазон, из которого нужны данные; ПОИСКПОЗ (что ищем; где ищем; 0))

ДВУМЕРНЫЙ ПОИСК: ИНДЕКС + ПОИСКПОЗ

Файл с примером: Двумерный поиск.xlsx

Функция ИНДЕКС работает и с двумерными массивами. Если аргумент — это диапазон (или таблица), то можно извлекать элемент по номерам строки и столбца.

А эти самые номера мы можем искать с помощью функции ПОИСКПОЗ / MATCH.

В общем виде структура формулы будет такой:

=ИНДЕКС(диапазон;ПОИСКПОЗ для поиска номера строки;ПОИСКПОЗ для поиска номера столбца;0))

Более подробно:

=ИНДЕКС(диапазон;ПОИСКПОЗ(значение, которое ищем в столбце; столбец для поиска;0);ПОИСКПОЗ(значение, которое ищем в строке; строка для поиска;0))

Давайте рассмотрим пример. Допустим, по двум оценкам поведенческих и профессиональных компетенций нужно выставить комплексную оценку.

То есть если Лемур получает оценку профессиональных компетенций A и поведенческих — C, то его комплексная оценка — B.

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

ФУНКЦИЯ ПРОСМОТРX / XLOOKUP

ПРОСМОТРX / XLOOKUP — новая функция, которой нет в версиях Excel до 2019 включительно, она доступна подписчикам Office 365, получающим обновления, и в Excel 2021.

В 2022 году она также появилась и в Google Таблицах.

У нее гораздо более простой синтаксис. Несмотря на большое количество аргументов, обязательными являются всего три:

=ПРОСМОТРX(что ищем; где ищем; диапазон, из которого нужны данные)

Не нужно указывать номер столбца, не нужно помнить про интервальный просмотр: в таком варианте ПРОСМОТРX будет искать и возвращать данные из нужных столбцов при их любом порядке.

ПРОСМОТРX по умолчанию ищет точное совпадение, в отличие от ВПР и ПОИСКПОЗ, у которых нужно указывать последний аргумент равным нулю при работе с текстом. Поиск ведется сверху вниз. По умолчанию здесь, в отличие от ВПР, символы подстановки (* и?) не работают.

Если нужны более тонкие настройки, у ПРОСМОТРX есть необязательные аргументы:

[если_ничего_не_найдено](if_not_found) — что возвращать в случае ошибки #Н/Д (когда ничего не найдено);

[режим_сопоставления](match_mode) — по умолчанию 0, то есть точный поиск, так что можно пропускать при работе с текстом, но если нужно использовать символы подстановки, то этот аргумент должен быть равен 2, −1 и 1 для интервального поиска (ближайшего числа);

[режим_поиска](search_mode) — по умолчанию 1, то есть поиск сверху вниз, −1 для поиска снизу вверх; значения -2 и 2 — это двоичный поиск, который был более быстрым до 2018 года, а после потерял актуальность, когда команда Excel обновила алгоритм поиска; кроме того, у него есть недостаток — значения должны быть отсортированы.

«Обычную» функцию ПРОСМОТР / LOOKUP (без X, из старых версий Excel) использовать не рекомендую. Она требует обязательной сортировки таблицы по алфавиту и не имеет режима точного поиска: если у вас будет ошибка в ключе, она все равно выдаст результат (неправильный), а не просигнализирует ошибкой #Н/Д, как ВПР и ПОИСКПОЗ.

ПРОСМОТРX может заменить и ГПР / HLOOKUP — функция будет работать и с горизонтальными массивами.

НЕКОТОРЫЕ АСПЕКТЫ РАБОТЫ С ФУНКЦИЯМИ ПОИСКА

• ВПР и ПОИСКПОЗ (в режиме поиска текста при последнем аргументе = 0) ищут сверху вниз, то есть если искомое значение повторяется несколько раз, то будет найдено первое вхождение.

• Все функции (ВПР, ПОИСКПОЗ, ПРОСМОТРX) ищут текст без учета регистра — "ipad" и "IPAD" для них являются одинаковыми значениями.

• В функциях ВПР и ПОИСКОЗ можно использовать символы подстановки — звездочку (*) и знак вопроса (?). Это не отменяет последнего аргумента, равного нулю, если вы ищете текст. Просто ваш запрос уже является не точным текстом, а текстом с символами подстановки. В любом случае ВПР будет искать первое точное совпадение с тем шаблоном, что задан в первом аргументе (например, если будет текст "ipad*" в качестве искомого значения, то ВПР найдет первую ячейку, начинающуюся с "ipad").

В функции ПРОСМОТРX символы подстановки тоже возможны, но для их работы нужно явно указать аргумент «режим_сопоставления» = 2. У функций ВПР и ПОИСКПОЗ они работают по умолчанию.

СРАВНЕНИЕ ФУНКЦИЙ ДЛЯ ПОИСКА ЗНАЧЕНИЙ

ПОИСК ПО НЕСКОЛЬКИМ КРИТЕРИЯМ

Файл с примером: ВПР по 2 критериям.xlsx

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

Самое простое решение: добавить вспомогательные столбцы в обеих таблицах, в которых создать уникальный ключ из двух значений. Можно объединить их с помощью амперсанда (&) или одной из текстовых функций (СЦЕП / CONCAT, СЦЕПИТЬ / CONCATENATE).

И далее использовать функцию ВПР как обычно.

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

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

Здесь мы ищем объединенное значение (Курс + Формат, A2&C2) в столбце, сделанном в формуле из двух столбцов на листе «Ставки роялти» — B и C.

В старых версиях Excel не забудьте ввести такую формулу с помощью сочетания клавиш Ctrl + Shift + Enter (это формула массива, и до Excel 2019 включительно такие нужно вводить явным образом).

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

СУММЕСЛИМН / SUMIFS для поиска чисел

Если вы ищете числа, как в данном случае (ставки роялти — числовые значения), можно обойтись вовсе без ВПР или ИНДЕКС + ПОИСКПОЗ.

Ведь есть функция СУММЕСЛИМН / SUMIFS, суммирующая данные по нескольким условиям. Так как мы ищем число, то можно суммировать по условиям — в качестве результата функция будет возвращать сумму одного-единственного (искомого) числа, что нам и нужно. Конечно, если комбинация условий встречается в исходной таблице только один раз.

Плюс в том, что с этой функцией легко работать и при большом количестве условий.

Но и минус есть: если мы ищем текст, а не числа, то подойдут только предыдущие варианты, а не СУММЕСЛИМН.

ВПР С РАЗНЫХ ЛИСТОВ

Файл с примером: ВПР с разных листов.xlsx

Если вам нужно «подтягивать» данные с помощью ВПР / VLOOKUP с разных листов (например, на каждый город/месяц/склад у вас отдельный лист с данными), можно собрать ссылку с помощью функции ДВССЫЛ / INDIRECT.

Обычная ссылка на другой лист выглядит так:

='Название_листа'!A: B

Нам нужно подставлять внутри апострофов названия разных листов.

Сначала берем апостроф (в кавычках), потом к нему добавляем название листа, справа еще один апостроф, восклицательный знак и диапазон:

="'" & ячейка с названием листа & "'!диапазон"

Чтобы превратить полученную текстовую строку в ссылку, используем функцию ДВССЫЛ / INDIRECT — она ровно для этого и используется:

=ДВССЫЛ ("'" & ячейка с названием листа & "'!диапазон")

И отправляем это внутрь ВПР'а как второй аргумент:

=ВПР(значение для поиска; ДВССЫЛ("'" & ячейка с названием листа & "'!диапазон"); номер столбца; 0)

Назад: Функции баз данных

Дальше: Генерация случайных чисел и дат