No Image

Функция бизвлечь в excel примеры

СОДЕРЖАНИЕ
1 просмотров
10 марта 2020

В этой статье описаны синтаксис формулы и использование функции БИЗВЛЕЧЬ в Microsoft Excel.

Описание

Извлекает из столбца списка или базы данных одно значение, удовлетворяющее заданным условиям.

Синтаксис

БИЗВЛЕЧЬ(база_данных; поле; условия)

Аргументы функции БИЗВЛЕЧЬ описаны ниже.

База_данных — обязательный аргумент. Диапазон ячеек, образующих список или базу данных. База данных представляет собой список связанных данных, в котором строки данных являются записями, а столбцы — полями. Первая строка списка содержит заголовки всех столбцов.

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

Условия — обязательный аргумент. Диапазон ячеек, который содержит задаваемые условия. В качестве аргумента "условия" можно использовать любой диапазон, который содержит хотя бы один заголовок столбца и хотя бы одну ячейку с условием, расположенную под заголовком столбца.

Замечания

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

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

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

Например, если диапазон G1:G2 содержит заголовок столбца "Доход" в ячейке G1 и значение 10 000 ₽ в ячейке G2, можно определить диапазон "СоответствуетДоходу" и использовать это имя как аргумент "условия" в функции баз данных.

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

Диапазон условий не должен перекрываться со списком.

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

Пример

Скопируйте образец данных из следующей таблицы и вставьте их в ячейку A1 нового листа Excel. Чтобы отобразить результаты формул, выделите их и нажмите клавишу F2, а затем — клавишу ВВОД. При необходимости измените ширину столбцов, чтобы видеть все данные.

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

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

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

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

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

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

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

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

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

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

Задачи

Предположим, что в диапазоне 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 вместо заголовка введем произвольный поясняющий текст, например, «ФРУКТЫ с учетом регистра» (заголовок не должен повторять заголовки исходной таблицы).

Читайте также:  Playme arton официальный сайт

Записать формулу можно так =БИЗВЛЕЧЬ(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% уверены в том, что в таблице гарантировано имеются данные удовлетворяющие критериям, причем в единственном экземпляре.

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

Примеры работы функции базы данных БИЗВЛЕЧЬ в Excel

Допустим мы располагаем базой данных, которая экспортированная в Excel так как показано ниже на рисунке:

Наша задача найти всю информацию (номер фактуры, номер клиента, сумма и т.д.), которая относится к одной конкретной фамилии определенного клиента. Для этой цели рекомендуем воспользоваться функцией Excel для работы с базами данных – БИЗВЛЕЧЬ. Данная функция на основе критериев поискового запроса, введенных в ее аргументы, по отдельности выберите все соответствующие строки из базы данных.

Функция БИЗВЛЕЧЬ примеры в Excel

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

  1. Выше базы данных добавим 4 пустых строки. Для этого достаточно выделить 4 заголовка строк листа Excel и щелкнуть правой кнопкой мышки. Из контекстного меню выбрать вставить. Или после выделения строк по заголовкам нажать комбинацию горячих клавиш CTRL+SHIFT+=.
  2. Далее скопируйте все заголовки столбцов базы данных и вставьте их в первую строку листа для вспомогательной таблицы критериев.

Пространство для заполнения критериев запросов выше данных базы.

Сначала попытаемся получить номер фактуры по фамилии клиента:

  1. В ячейке D2 введите фамилию Антонова.
  2. В ячейке A3 введите следующую формулу:
Читайте также:  Смарт часы просмотр фото

Сразу же получаем готовый результат как показано ниже на рисунке:

Формула нашла соответствующий номер фактуры для клиента с фамилией Антонова.

Разбор принципа действия функции БИЗВЛЕЧЬ для работы с базами данных в Excel:

БИЗВЛЕЧЬ – главная функция базы данных в Excel. В первом аргументе функции вводим диапазон просматриваемой базы данных вместе с заголовками. Во втором аргументе функции указываем адрес ячейки где будет возвращено значение соответствующие критериям поискового запроса. Третьим аргументом является диапазон ячеек, содержащий следующие условия: заголовок столбца БД и диапазон для поиска под этим заголовком. Вспомогательная табличка критериев поискового запроса к базе данных, должна быть так сформулирована, чтобы критерии однозначно и точно определяли данные, которые нужно найти в БД. Если же функция БИЗВЕЧЬ возвращает ошибку #ЗНАЧ! – значит в базе данных нет записей, соответствующих критериям поискового запроса. Если же возвращена ошибка #ЧИСЛО! – значит в базе данных более 1 одинаковой записи по данному критерию.

В нашем случаи функция БИЗВЕЧЬ вернула одно значение – без ошибок. Эту функцию можно так же использовать для вывода целой строки за одну операцию без копирования функции в другие ячейки с другими аргументами. Чтобы избежать необходимости указывать новый критерий для каждой ее копии составим простую формулу, в которую добавим функцию СТОЛБЕЦ. Для этого:

  1. В ячейке A3 введите следующую формулу:
  2. Скопируйте ее во все ячейки диапазона A3:E3.

Выбрана целая строка информации по конкретной фамилии определенного клиента.

Принцип действия формулы для вывода целой строки из базы данных:

В конструкции функции БИЗВЕЧЬ изменили мы только второй аргумент, значение которого вычисляется функцией СТОЛБЕЦ в место числа 1. Данная функция возвращает номер текущего столбца для текущей ячейки.

Бесспорное преимущество использования функции БИЗВЛЕЧЬ заключается в автоматизации. Достаточно лишь изменить критерий и в результате мы получаем уже новую строку информации из базы данных клиентов фирмы. Например, найдем данные теперь по номеру клиента 58499. Удаляем старый критерий вводим новый и сразу же получаем результат.

Данную задачу можно было бы решить и с помощью сложных формул с комбинациями функций ИНДЕКС, ПОИСКПОЗ, ВПР, ПРОСМОТР, но зачем изобретать велосипед? Функция БИЗВЛЕЧЬ прекрасно справляется с поставленной задачей и при этом весьма лаконична.

Обработка баз данных в Excel по нескольким критериям

Допустим нашу базу пополнил новый прибыльный клиент с таким же именем «Василий». Нам известно о нем только имя и фамилия «Василий Великий». На именины в день Василия нам нужно выслать только 1 подарок для более прибыльного клиента фирмы. Мы должны выбрать кому отдать предпочтение: Василию Веселому или Василию Великому. Для этого сравниваем их суммы транзакций:

  1. Расширьте диапазон для просматриваемой таблицы $A$5:$E$18 в параметрах формул, так как у нас добавился новый клиент и на одну запись стало больше: Теперь функция возвращает ошибку #ЧИСЛО! так как в базе более чем 1 запись по данному критерию.
  2. В поле критериев «Имя» вводим значение «Василий», а потом в поле «Фамилия» вводим значение «Великий».

В результате мы видим, что подарок получит более активный клиент Василий Великий.

Комментировать
1 просмотров
Комментариев нет, будьте первым кто его оставит

Это интересно
Adblock detector