Функция БИЗВЛЕЧЬ() в EXCEL

Функция БИЗВЛЕЧЬ() , английский вариант DGET(), извлекает из столбца (списка) или таблицы отдельное значение, удовлетворяющее заданным условиям.

Функция БИЗВЛЕЧЬ() относится к наиболее редко используемым функциям и этому есть объективные причины. Рассмотрим синтаксис этой функции и причины ее непопулярности.

Синтаксис функции БИЗВЛЕЧЬ()

Для использования этой функции требуется чтобы:

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

БИЗВЛЕЧЬ( база_данных;поле;условия ) База_данных представляет собой диапазон ячеек с данными связанными логически, т.е. таблицу. Верхняя строка таблицы должна содержать заголовки всех столбцов. В Базе_данных строки называются записями, а столбцы — полями. Поле — заголовок столбца, из которого выводится значение, если выполняется условие. Аргумент Поле можно заполнить введя:

• текст, представляющий собой название одного из заголовков Базы_данных . Текст указывается в двойных кавычках, например "Возраст" или "Урожай",
• число (без кавычек), задающее положение столбца в Базе_данных : 1 — для первого столбца, 2 — для второго и т.д.
• ссылку на заголовок столбца.

Условия — интервал ячеек, который содержит задаваемые условия (т.е. таблица критериев). Структура таблицы с критериями отбора для БИЗВЛЕЧЬ() аналогична структуре для Расширенного фильтра и, например, функции БДСУММ() .

Если ни одна из записей не удовлетворяет условию, функция БИЗВЛЕЧЬ() возвращает значение ошибки #ЗНАЧ!

Если условию удовлетворяет более чем одна запись, функция БИЗВЛЕЧЬ() возвращает значение ошибки #ЧИСЛО!

Задачи

Предположим, что в диапазоне A 10:С15 имеется таблица продаж ( База_данных ), содержащая поля (столбцы) Товар , Продавец и Продажи (см. файл примера ).

Сформулируем задачи в виде вопросов.

Вопрос 1 (Продал ли Мясо Продавец Белов ? Если продал, то за сколько?). Найдем строку, в которой в столбце Товар содержится значение Мясо , а столбце Продавец содержится значение Белов . Если такая строка есть в таблице, то выведем соответствующее значение из столбца Продажи .

Алгоритм следующий:

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

• Условия отбора должны быть записаны в специальном формате: ="=Мясо" (будет производиться поиск в столбце Товар только значений точно совпадающих со словом Мясо (или мясо , МЯСО , т.е. без учета регистра). Если написать условие не в виде ="=Мясо", а просто ввести в ячейку значение Мясо , то условию будут удовлетворять текстовые строки, которые содержат слово Мясо , например, « Свежее МЯСО», «Мясо барана» и пр.)
• Предполагаем, что база_данных (исходная таблица) находится в A10:C15 . С10 – это ссылка на заголовок столбца, из которого выводится значение, если выполняется условие. B4:С5 – ссылка на табличку критериев (см. рисунок выше) Итоговая формула выглядит так =БИЗВЛЕЧЬ(A10:C15;C10;B4:C5)

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

Вроде функция не плохо справляется, но что произойдет, если в таблице нет удовлетворяющих критерию записей? Функция вернет значение ошибки #ЗНАЧ! A что произойдет, если в таблице 2 и более записи удовлетворяющие критерию? Функция вернет значение ошибки #ЧИСЛО! К сожалению, в EXCEL нет функции, умеющей различать эти ошибки: для ЕОШ() , ЕОШИБКА() , ЕСЛИОШИБКА() эти ошибки неразличимы. Т.е. пользователю нужно помнить какой вид ошибки возвращается в каждом случае и принимать в зависимости от этого решение - это не удобно.

Таким образом, функция БИЗВЛЕЧЬ() НЕ возвращает ошибку только в случае, если удовлетворяющая критерию запись единственная. Более логичным решением выглядит формула =СУММПРОИЗВ((A11:A15="Мясо")*((B11:B15)="Белов")*C11:C15) , которая возвращает ошибку только в случае, если запись удовлетворяющая критерию не обнаружена. Если обнаружено 2 и более записи, то соответствующие значения суммируются.

Вопрос 2 (Есть ли товар ФРУКТЫ (с учетом регистра)?). Выполним поиск в столбце Товар слова ФРУКТЫ с учетом регистра (например, слово фрукты не будет удовлетворять условию поиска).

В качестве условия отбора можно использовать значение, вычисляемое при помощи формулы. Формула должна возвращать результат ИСТИНА или ЛОЖЬ. Для этого введем в ячейку С3 файла примера формулу =СОВПАД("ФРУКТЫ";A11) , а в С2 вместо заголовка введем произвольный поясняющий текст, например, « ФРУКТЫ с учетом регистра » (заголовок не должен повторять заголовки исходной таблицы).

Записать формулу можно так =БИЗВЛЕЧЬ(A10:A15;A10;C2:C3) Результат - слово ФРУКТЫ (значит такое слово есть в диапазоне А10:А15 и оно единственное).

Альтернативная формула: =ЕСЛИ(СУММПРОИЗВ(--СОВПАД("ФРУКТЫ";A11:A15));"ФРУКТЫ";"Нет")

Вопрос 3 (Есть ли продавец с фамилией начинающейся на Ро ?). Выполним поиск в столбце Продавец с использованием подстановочного знака *.

В качестве условия можно записать в ячейке B3 формулу ="=Ро*"

Формула =БИЗВЛЕЧЬ(B10:B15;B10;B2:B3) вернет слово Рощин (значит в диапазоне B10:B15 имеется такая фамилия и она единственная начинается на Ро ).

В этом случае гораздо предпочтительнее выглядит формула =ВПР("Ро*";B11:B15;1;ЛОЖЬ) , т.к. не требуется создавать отдельную табличку с критериями и в случае наличия нескольких фамилий начинающихся на Ро , будет выведена первая фамилия, а не ошибка #ЧИСЛО!, как в случае с функцией БИЗВЛЕЧЬ() .

Вопрос 4 (Есть ли продавец с фамилией длиной 5 букв?). Выполним поиск в столбце Продавец с использованием подстановочного знака ?.

В качестве условия можно записать в ячейке А3 формулу ="=?????"

Формула =БИЗВЛЕЧЬ(B10:B15;B10;A2:A3) вернет ошибку #ЧИСЛО! (т.к. таких фамилий несколько).

В этом случае гораздо предпочтительнее выглядит формула =ВПР("?????";B11:B15;1;ЛОЖЬ) , по тем же причинам, что и в предыдущей задаче.

Вывод : забудьте про функцию БИЗВЛЕЧЬ() , если только Вы не на 100% уверены в том, что в таблице гарантировано имеются данные удовлетворяющие критериям, причем в единственном экземпляре.