В Эксель есть несколько функций поиска и наиболее известной, но не самой удобной является функция ВПР. Ее более мощным и простым аналогом является функция ПРОСМОТРХ, но данная функция появилась относительно недавно и недоступна пользователям Excel 2019 и более ранних версий. В этой статье речь пойдет еще об одной малоизвестной функции, которая может быть полезна в некоторых ситуациях при решении задач поиска. Это функция БИЗВЛЕЧЬ (англ. DGET). Русскоязычное название довольно громоздко и не особо отражает суть функции: извлечение данных из базы данных на основе заданных критериев.
Синтаксис функции БИЗВЛЕЧЬ
Итак, функция БИЗВЛЕЧЬ позволяет извлечь данные из таблицы или диапазона на основе заданных критериев.
У функции три аргумента.
Первый (база_данных) — это диапазон ячеек таблицы, то есть эта вся область табличной базы данных, включая заголовки. Во втором аргументе (поле) указывается столбец, из которого нужно извлечь данные. И третий аргумент (условие) определяет диапазон ячеек с условиями для поиска.
Пример использования
Рассмотрим простейший пример. Есть таблица с товарами, их категориями и ценами. Необходимо найти цену определенного товара.
Поскольку функция работает с табличной базой данных, то и запрос должен формироваться должным образом. Это означает, что таблица, в которой возвращается значение, должна иметь аналогичную структуру табличной базы данных, то есть иметь те же заголовки, что и основная таблица. Нас интересуют два поля исходной таблицы - Товар и Цена, поэтому таблица-отчет должна их также содержать. Искать будем цену бананов.
Далее обращаемся к функции БИЗВЛЕЧЬ. В качестве первого аргумента указываем всю таблицу целиком. Затем определяем поле, по которому нужно получить свойство элемента базы данных. Нас интересует столбец с заголовком «Цена». Можем указать его текстом, либо сослаться на соответствующую ячейку таблицы-отчета. И последний аргумент определяет критерии. В нашем случае критериев два - это поле «Товар» и наименование конкретного товара - «Банан».
Эту же задачу можно решить с помощью функции ВПР. При этом мы должны цифрой указать столбец, из которого значение нужно вернуть.
Если же в исходной таблице появится еще один столбец или изменится критерий поиска, то формулу с функцией ВПР придется изменять, указывая новый столбец. Функция БИЗВЛЕЧЬ обращается к полям, то есть к заголовкам столбцов, поэтому она лишена данного недостатка.
Ну и функцию БИЗВЛЕЧЬ можно использовать для поиска по нескольким критериям. Просто добавим необходимый столбец в отчет и в качестве критерия выберем весь диапазон "Товар-Категория".
Поиск в двух направлениях
Также функцию БИЗВЛЕЧЬ удобно использовать для поиска в двух направлениях.
Например, есть данные по объектам за несколько месяцев и нужно получить отчет по второму объекту за март. Эту задачу легко решить двумя вложенными функциями ПРОСМОТРХ.
Но, пожалуй, самое простое решение можно реализовать с помощью функции БИЗВЛЕЧЬ. Действуем по аналогии - указываем базу данных, затем поле, из которого нужно вернуть значение, и в конце определяем критерий поиска. Вот так простой формулой решаем задачу.
Если нужны данные по объекту за несколько указанных месяцев, то формула будет такой же, просто перед ее копированием на соседний столбец не забываем с помощью клавиши F4 зафиксировать диапазоны.
Недостатки и ограничения
Итак, функция БИЗВЛЕЧЬ может быть полезной при решении задач поиска, но она имеет ряд ограничений, о которых обязательно нужно знать.
Во-первых, как уже упоминалось ранее, таблица-отчет должна иметь точно такую же структуру, как и исходная таблица. Это означает, что данные в отчете должны быть представлены в виде таблицы с заголовками. Нельзя построить отчет в транспонированном виде, когда поля будут находиться в одном столбце, а искомые значения в столбце рядом.
Такой отчет можно построить с помощью других функции поиска, однако функция БИЗВЛЕЧЬ для этого не годится.
Также важно, чтобы заголовки столбцов таблицы-отчета имели те же названия, что и заголовки исходной таблицы.
Ну и поскольку функция возвращает запись из базы данных, то мы можем за раз получить лишь одну запись, соответствующую указанным критериям. Нельзя получить выборку данных из базы по каким-то условиям.
Ошибки
При использовании функции БИЗВЛЕЧЬ можно столкнуться с ошибками.
Если в исходной таблице не будет найдена запись с указанными критериями, то появится ошибка ЗНАЧ.
Если же в таблице есть несколько строк, соответствующих критериям, то появится ошибка ЧИСЛО.
Несмотря на ограничения данной функции, она вполне может быть отличным инструментом при решении некоторых задач. Например, ее удобно использовать совместно с выпадающими списками, позволяющими быстро сформировать условия поиска и вывести необходимую запись из табличкой базы данных.
Чтобы скачать курс по основам Microsoft Excel нажмите на эту ссылку
Ссылки на мои ресурсы по Excel
★ YouTube-канал по Excel и Word
★ Телеграм