Главная
Бизвлечь в excel примеры: Функция БИЗВЛЕЧЬ – Служба поддержки Майкрософт

Бизвлечь в excel примеры: Функция БИЗВЛЕЧЬ – Служба поддержки Майкрософт

Функция БИЗВЛЕЧЬ – Служба поддержки Майкрософт

Excel для Microsoft 365 Excel для Microsoft 365 для Mac Excel для Интернета Excel 2021 Excel 2021 for Mac Excel 2019 Excel 2019 для Mac Excel 2016 Excel 2016 для Mac Excel 2013 Excel 2010 Excel 2007 Excel для Mac 2011 Excel Starter 2010 Еще…Меньше

В этой статье описаны синтаксис формулы и использование DGET  в Microsoft Excel.

Описание

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

Синтаксис

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

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

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

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

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

Замечания

  • Если ни одна запись не соответствует условиям, DGET возвращает #VALUE! значение ошибки #ЗНАЧ!.

  • Если условиям соответствуют несколько записей, то DGET возвращает #NUM! значение ошибки #ЗНАЧ!.

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

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

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

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

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

Пример

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

Дерево

Высота

Возраст

Урожай

Доход

Высота

=”=Яблоня”

>3

<5

=”Груша”

>3

Дерево

Высота

Возраст

Урожай

Доход

Яблоня

18

20

14

105 ₽

Груша

12

12

10

96 ₽

Вишня

13

14

9

105 ₽

Яблоня

14

15

10

75 ₽

Груша

9

8

8

77 ₽

Яблоня

8

9

6

45 ₽

Формула

Описание

Результат

=БИЗВЛЕЧЬ(A4:E10;”Урожай”;A1:A3)

Возвращает #NUM! из-за того, что условиям удовлетворяет несколько записей (любое яблоня или груша).

#ЧИСЛО!

=БИЗВЛЕЧЬ(A4:E10;”Урожай”;A1:A3)

Возвращает 10 (урожай яблони в строке 9), так как это единственная запись, удовлетворяющая условию в ячейках A1:F3.

10

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

Выполнение поиска по огромным таблицам с тысячами позиций информации о товарах или объемах продаж – это непростой вызов для большинства пользователей 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. В поле критериев «Имя» вводим значение «Василий», а потом в поле «Фамилия» вводим значение «Великий».

Скачать пример работы функции БИЗВЛЕЧЬ с базой данных

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

Как извлечь подстроку в Excel (используя формулы ТЕКСТА)

В Excel есть набор функций ТЕКСТА, которые могут творить чудеса. С помощью этих функций вы можете выполнять все виды операций с текстовыми фрагментами и кубиками.

Одной из распространенных задач для людей, работающих с текстовыми данными, является извлечение подстроки в Excel (т. е. получение psrt текста из ячейки).

К сожалению, в Excel нет функции подстроки, которая могла бы легко это сделать. Однако это все равно можно сделать с помощью текстовых формул, а также некоторых других встроенных функций Excel.

В этом уроке рассматриваются:

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

Текстовые функции Excel

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

  • ПРАВАЯ функция: извлекает указанное количество символов справа от текстовой строки.
  • Функция LEFT: извлекает указанное количество символов слева от текстовой строки.
  • Функция MID: извлекает указанное количество символов из указанной начальной позиции в текстовой строке.
  • Функция НАЙТИ: Находит начальную позицию указанного текста в текстовой строке.
  • Функция LEN: возвращает количество символов в текстовой строке.

Извлечение подстроки в Excel с помощью функций

Предположим, у вас есть набор данных, как показано ниже:

Это некоторые случайные (но супергеройские) идентификаторы электронной почты (кроме моего), и в приведенных ниже примерах я’ Я покажу вам, как извлечь имя пользователя и доменное имя с помощью текстовых функций в Excel.

Пример 1. Извлечение имен пользователей из идентификаторов электронной почты

При использовании текстовых функций важно определить шаблон (если он есть). Это позволяет очень легко построить формулу. В приведенном выше случае шаблон представляет собой знак @ между именем пользователя и именем домена, и мы будем использовать его в качестве ссылки для получения имен пользователей.

Вот формула для получения имени пользователя:

 =ЛЕВОЕ(A2,НАЙТИ("@",A2)-1)

Приведенная выше формула использует функцию ВЛЕВО для извлечения имени пользователя путем определения позиции знака @ в идентификаторе. Это делается с помощью функции НАЙТИ, которая возвращает позицию символа @.

Например, в случае [email protected] функция НАЙТИ(“@”,A2) вернет 11, то есть позицию в текстовой строке.

Теперь мы используем функцию LEFT для извлечения 10 символов слева от строки (на один меньше, чем значение, возвращаемое функцией LEFT).

Пример 2 – извлечение доменного имени из идентификаторов электронной почты

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

Вот формула, которая сделает это:

 =ПРАВИЛЬНО(A2,ДЛСТР(A2)-НАЙТИ("@",A2))

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

Давайте снова возьмем пример [email protected]. Функция НАЙТИ возвращает позицию знака @, которая в данном случае равна 11. Теперь нам нужно извлечь все символы после @. Итак, мы определяем общую длину строки и вычитаем количество символов до символа @. Это дает нам количество символов, которые охватывают доменное имя справа.

Теперь мы можем просто использовать функцию ПРАВИЛЬНО, чтобы получить доменное имя.

Пример 3. Извлечение доменного имени из идентификаторов электронной почты (без .com)

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

Например, в приведенном ниже примере, чтобы получить доменное имя без части .com, маркером будет @ (который находится прямо перед доменным именем) и . (что сразу за ним).

Вот формула, которая извлечет только доменное имя:

 =MID(A2,НАЙТИ("@",A2)+1,НАЙТИ(".",A2)-НАЙТИ("@",A2)- 1) 

Функция Excel MID извлекает указанное количество символов из указанной начальной позиции. В приведенном выше примере НАЙТИ(“@”,A2)+1 указывает начальную позицию (которая находится сразу после символа @), а НАЙТИ(“.,A2)-НАЙТИ(“@”,A2)-1 определяет начальную позицию. количество символов между ‘ @ ‘ и ‘ . ‘

Обновление: Один из читателей William19 упомянул, что приведенная выше формула не будет работать, если в идентификаторе электронной почты есть точка (.) (например, [email protected]). Итак, вот формула для таких случаев:

 =СРЕДН(A1,НАЙТИ("@",A1)+1,НАЙТИ(".",A1,НАЙТИ("@",A1))-НАЙТИ(" @",A1)-1)

Использование текста в столбцах для извлечения подстроки в Excel

Преимущество использования функций для извлечения подстроки в Excel состоит в том, что они являются динамическими. Если вы измените исходный текст, формула автоматически обновит результаты.

Если вам это не нужно, то использование функции «Текст в столбцы» может быть быстрым и простым способом разбить текст на подстроки на основе заданных маркеров.

Вот как это сделать:

  • Выберите ячейки, в которых у вас есть текст.
  • Перейдите в раздел «Данные» -> «Инструменты данных» -> «Текст в столбцы».
  • В мастере преобразования текста в столбцы, шаг 1, выберите «С разделителями» и нажмите «Далее».
  • На шаге 2 установите флажок «Другое» и введите @ в поле справа от него. Это будет наш разделитель, который Excel будет использовать для разделения текста на подстроки. Вы можете увидеть предварительный просмотр данных ниже. Нажмите Далее.
  • В этом случае общие настройки на шаге 3 работают нормально. Однако вы можете выбрать другой формат, если вы разбиваете числа/даты. По умолчанию в ячейке назначения находятся исходные данные. Если вы хотите сохранить исходные данные без изменений, измените это на другую ячейку.
  • Нажмите «Готово».

Это мгновенно даст вам два набора подстрок для каждого идентификатора электронной почты, используемого в этом примере.

Если вы хотите дополнительно разделить текст (например, разделить batman. com на batman и com), повторите с ним тот же процесс.

Использование ПОИСКА и ЗАМЕНЫ для извлечения текста из ячейки в Excel

ПОИСК и ЗАМЕНА может быть мощным методом при работе с текстом в Excel. В приведенных ниже примерах вы узнаете, как использовать функции НАЙТИ и ЗАМЕНИТЬ с подстановочными знаками, чтобы делать удивительные вещи в Excel.

   См. также:   Узнайте все о подстановочных знаках в Excel.

Возьмем те же примеры идентификаторов электронной почты.

Пример 1. Извлечение имен пользователей из идентификаторов электронной почты

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

  • Скопируйте и вставьте исходные данные. Поскольку функция «Найти и заменить» работает и изменяет данные, к которым она применяется, лучше иметь резервную копию исходных данных.
  • Выберите данные и перейдите в раздел «Главная» —> «Редактирование» —> «Найти и выделить» —> «Заменить» (или используйте сочетание клавиш Ctrl + H).
  •  В диалоговом окне “Найти и заменить” введите следующее:
    • Найдите что: @*
    • Замените на: (оставьте это поле пустым)
  • Нажмите «Заменить все».

Это мгновенно удалит весь текст перед @ в идентификаторах электронной почты. Вы получите результат, как показано ниже:

Как это работает?? — В приведенном выше примере мы использовали комбинацию @ и *. Звездочка (*) — это подстановочный знак, представляющий любое количество символов. Следовательно, @* будет означать текстовую строку, которая начинается с @ и может иметь любое количество символов после нее. Например, в [email protected] @* будет @batman.com. Когда мы заменяем @* пробелом, удаляются все символы после @ (включая @).

Пример 2 – Извлечение доменного имени из идентификаторов электронной почты

Используя ту же логику, вы можете изменить критерии «Найти что», чтобы получить доменное имя.

Вот шаги:

  • Выберите данные.
  • Перейдите на главную —> редактирование —> найти и выделить —> заменить (или используйте сочетание клавиш Ctrl + H).
  •  В диалоговом окне “Найти и заменить” введите следующее:
    • Что найти: *@
    • Заменить на: (оставьте это поле пустым)
  • Нажмите «Заменить все».

Это мгновенно удалит весь текст перед @ в идентификаторах электронной почты. Вы получите результат, как показано ниже:

Вам также могут понравиться следующие учебные пособия по Excel:

  • Как подсчитать ячейки, содержащие текстовые строки.
  • Извлечение имен пользователей из идентификаторов электронной почты в Excel [2 метода].
  • Функции Excel (примеры + видео).
  • Получите больше от поиска и замены в Excel.
  • Как сделать первую букву текстовой строки заглавной в Excel
  • Как извлечь первое слово из текстовой строки в Excel
  • Разделить текст и числа в Excel

Как использовать функцию Excel MID (примеры + видео)