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

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

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

Как создать базу данных в Excel? Базой данных в программе Excel считается таблица, которая была создана с учетом определенных требований:

  1. Заголовки таблицы должны находиться в первой строке.
  2. Любая последующая строка должна содержать хотя бы одну непустую ячейку.
  3. Объединения ячеек в любых строках запрещены.
  4. Для каждой ячейки каждого столбца должен быть определен единый тип хранящихся данных.
  5. Диапазон базы данных должен быть отформатирован в качестве списка и иметь свое имя.

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

Функция БИЗВЛЕЧЬ хорошо работает с корректно отформатированными таблицами.



Примеры использования функции БИЗВЛЕЧЬ в Excel

Пример 1. В таблице, которую можно рассматривать как БД, содержатся данные о различных моделях смартфонов. Найти название бренда смартфона, который содержит процессор с минимальным числом ядер.

Вид таблиц данных и критериев:

Пример 1.

В ячейке B2 запишем условие отбора данных следующим способом:

=МИН(СТОЛБЕЦ(B1))

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

В результате получим следующее:

МИН СТОЛБЕЦ.

В ячейке A4 запишем следующую формулу:

Описание аргументов:

  • A8:F15 – диапазон ячеек, в которых хранится БД;
  • 1 – числовое указание номера поля (столбца), из которого будет выводиться значение (необходимо вывести Бренд);
  • A2:F3 – диапазон ячеек, в которых хранится таблица критериев.

Результат вычислений:

БИЗВЛЕЧЬ.

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

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

Пример 2. Используя таблицу из предыдущего примера выводить все данные для отдельных брендов, указанных в качестве критерия поиска (например, отобразить все характеристики модели бренда Apple и т. д.)

В данном случае для вывода данных о бренде Meizu запишем в ячейку A3 условие:

="=Meizu"

Пример составления запросов.

Для решения задачи можно в каждую ячейку ввести формулу БИЗВЛЕЧЬ, но это займет слишком много времени. Поэтому выделим диапазон ячеек A4:F4 и введем следующую формулу массива CTRL+SHIFT+Enter:

Запись СТОЛБЕЦ(A1:F1) интерпретируется как отдельные ссылки на ячейки из диапазона A1:F1, а функция СТОЛБЕЦ поочередно возвращает ссылки на поля данных исследуемой таблицы.

Получаем следующий результат:

формула массива с БИЗВЛЕЧЬ.

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

Описание возможностей функции БИЗВЛЕЧЬ в Excel

Функция БИЗВЛЕЧЬ имеет следующую синтаксическую запись:

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

Описание аргументов:

  • база_данных – обязательный для заполнения, принимает ссылку на диапазон ячеек, являющихся таблицей, отформатированной в соответствии с требованиями, которые предъявляют к базам данных в Excel, списком или БД;
  • поле – обязательный для заполнения, принимающий текстовые строки (наименование поля данных БД) или числовые значения, соответствующие номеру поля данных (столбца, при этом отсчет начинается с единицы: первый столбец соответствует числу 1 и т. д.); В качестве аргумента может быть указана ссылка на ячейку, в которой содержится число или текст, указывающие на поле данных БД, с которой будет работать рассматриваемая функция.
  • условия – обязательный для заполнения, принимает ссылку на диапазон ячеек с критериями поиска в таблице или БД. Минимальным диапазоном условия является ссылка на две ячейки, одна из которых содержит наименование поля БД, а вторая – условие поиска.

Примечания:

  1. Результат выполнения функции является кодом ошибки #ЗНАЧ!, если ей не удалось найти ни одной записи, удовлетворяющей поставленным критериям поиска.
  2. При нахождении двух и более записей в таблице или БД, соответствующих указанным критериям поиска, функция БИЗВЛЕЧЬ генерирует код ошибки #ЧИСЛО!
  3. Диапазон условий не рекомендуют размещать под таблицей или БД, поскольку она может пополняться новыми записями (строками данных) в дальнейшем.
  4. При составлении условий нужно учитывать следующие правила:
  • для точных совпадений текстовые условия помещают в кавычки. Пример записи: =«=male» - для поиска по половому признаку;
  • для частичных совпадений указывают текст, например Samsung. Любая строка, содержащая данную подстроку (например, Samsung S9, Samsung Galaxy) будет считаться найденным совпадением.

en ru