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

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

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

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

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

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

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

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

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

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

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

Задачи

Предположим, что в диапазоне A10:С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% уверены в том, что в таблице гарантировано имеются данные удовлетворяющие критериям, причем в единственном экземпляре. 

Связанные статьи

Похожие задачи
Прочитайте другие статьи, решающие похожие задачи в MS Excel. Это позволит Вам решать широкий класс подобных задач.
Средняя: 4.8 (5 оценок)

Комментарии

Орус (не проверено)

а можно ли без таблицы критериев, все засунуть в одну формулу?

Creator

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