Бизвлечь в excel примеры - новости из мира пк

Функции: Работа с базой данных (Database)¶

Функция Excel (рус.)	Функция Calc (англ.)	Назначение
ЕДДИСП	DVAR	Оценивает дисперсию генеральной совокупности по выборке,используя числа в указанном поле всех записей базы данных, удовлетворяющих критерию
ЕДДИСПП	DVARP	Вычисляет дисперсию генеральной совокупности,используя числа в указанном поле всех записей базы данных, удовлетворяющих критерию
БДПРОИЭВЕД	DPRODUCT	Перемножает значения в указанном поле всех записей, удовлетворяющих критерию
БВСУММ	DSUM	Суммирует числа в указанном поле всех записей базы данных, удовлетворяющих критерию
БИЗВЛЕЧЬ	DGET	Извлекает отдельное значение из базы данных, удовлетворяющее критерию
БСЧЁТ	DCOUNT	Подсчитывает количество ячеек, содержащих числа, во всех записях базы данных,удовлетворяющих критерию
БСЧЕТА	DC0UNTA	Подсчитывает непустые ячейки в указанном поле всех записей базы данных,удовлетворяющих критерию
ДМАКС	DMAX	Возвращает наибольшее число в указанном поле всех записей базы данных,удовлетворяющих критерию
ДМИН	DMIN	Возвращает наименьшее число в указанном поле всех записей базы данных,удовлетворяющих критерию
ДСРЗНАЧ	DAVERAGE	Возвращает среднее значение в указанном поле всех записей,удовлетворяющих критерию
ДСТАНДОТКЛ	DSTDEV	Возвращает стандартное отклонение для выборки из генеральной совокупности,используя числа в указанном поле всех записей базы данных, удовлетворяющих критерию
ДОТАНДОТКЛП	DSTDEVP	Возвращает стандартное отклонение генеральной совокупности,используя числа в указанном поле всех записей базы данных, удовлетворяющих критерию

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

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

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

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

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

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

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

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

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

Функции: Проверка свойств и значений (Information)¶

Функция Excel (рус.)	Функция Calc (англ.)	Назначение
ЕЛОГИЧ	ISLOGICAL	Возвращает логическое значение ИСТИНА, если проверяемая величинаявляется логическим значением; в противном случае возвращается логическое значение ЛОЖЬ
ЕНД	ISNA	Возвращает логическое значение ИСТИНА, если проверяемая величинаявляется значением ошибки #Н/Д; в противном случае возвращается логическое значение ЛОЖЬ
EHETEKCT	ISNOTEXT	Возвращает логическое значение ИСТИНА. если проверяемая величинане является текстом; в противном случае возвращается логическое значение ЛОЖЬ
ЕОШ	ISERR	Возвращает логическое значение ИСТИНА, если проверяемая величина являетсяодним из возможных ошибочных значений Excel,за исключением н/д;в противном случае возвращается логическое значение ЛОЖЬ
ЕОШИБКА	ISERROR	Возвращает логическое значение ИСТИНА, если проверяемая величина являетсяодним из возможных ошибочных значений Calc; в противном случае возвращается логическое значение ЛОЖЬ
ЕПУСТО	ISBLANK	Возвращает логическое значение ИСТИНА,если проверяемая величина является ссылкой на пустую ячейку;в противном случае возвращается логическое значение ЛОЖЬ
ЕССЫЛКА	ISREF	Возвращает логическое значение ИСТИНА,если проверяемая величина является ссылкой;в противном случае возвращается логическое значение ЛОЖЬ
ЕТЕКСТ	ISTEXT	Возвращает логическое значение ИСТИНА если проверяемая величина является текстом;в противном случае возвращается логическое значение ЛОЖЬ
ЕЧИСЛО	ISNUMBER	Возвращает логическое значение ИСТИНА,если проверяемая величина является числом; в противном случае возвращается логическое значение ЛОЖЬ
ИНФОРМ	INFO	Возвращает информацию о текущей операционной среде
НД	NA	Возвращает значение ошибки #H/Д. #Н/Д. которое означает, что «нет доступного значения»
ТИП	ТУРЕ	Возвращает тип значения
ТИП. ОШИБКИ	ERROR.TYPE	Возвращает номер, соответствующий одному из возможных ошибочных значений Calc
Ч	N	Возвращает значение, преобразованное в число
ЯЧЕЙКА	CELL	Возвращает информацию о формате, местоположении или содержимом левой верхней ячейки в ссылке

Команды

В Excel команды имеют следующие свойства:

Выполняют действия так же, как и пользователи.

Могут делать все, что может пользователь (в зависимости от ограничений используемого интерфейса), например изменять параметры Excel, открывать, закрывать и редактировать документы, выполнять пересчеты и т. д.

Могут вызываться, когда происходят определенные обрабатываемые события.

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

Могут вызываться при выполнении определенных действиях с объектом, например щелчке левой кнопкой мыши.

Они никогда не вызываются программой Excel во время пересчета.

Они не вызываются функциями во время пересчета.

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

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

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

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

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

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

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

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

Описание справочника

Данный справочник содержит более 150 функций Excel с примерами использования из реальной жизни. Функции представлены как единым списком, так и отдельными таблицами по категориям.

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

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

Он постоянно обновляется и дополняется новыми примерами.

Почему вам понравится этот справочник функций

Для каждой функции явно и в строгом порядке даны:

Эквивалент на английском языке – для тех, кто пользуется англоязычным пакетом Excel
Полный разбор синтаксиса
Примеры формул, не оторванные от реальности
Ограничения и ошибки, которые могут возникать
Форматирование – требования к входящим значениям и формат вывода
Волатильность (пересчет формулы на основе функции при обновлении листа)
Похожие функции
Смежные функции, с которыми она наиболее часто используется и рекомендуется к использованию в составных формулах
Примеры с использованием как формулы массива или с формулами массива на основе других функций, если это имеет смысл

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

Замечания

Если ни одна из записей не соответствует условию, ЗУММЕР возвращает # ЗНАЧ! #VALUE! Значение ошибки!.

Если критерию соответствует несколько записей, СИНИЙ возвращает # ЧИСЛО! #VALUE! Значение ошибки!.

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

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

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

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

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

Почему ИНДЕКС/ПОИСКПОЗ лучше, чем ВПР?

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

Далее я попробую изложить главные преимущества использования ПОИСКПОЗ и ИНДЕКС в Excel, а Вы решите – остаться с ВПР или переключиться на ИНДЕКС/ПОИСКПОЗ.

4 главных преимущества использования ПОИСКПОЗ/ИНДЕКС в Excel:

1. Поиск справа налево. Как известно любому грамотному пользователю Excel, ВПР не может смотреть влево, а это значит, что искомое значение должно обязательно находиться в крайнем левом столбце исследуемого диапазона. В случае с ПОИСКПОЗ/ИНДЕКС, столбец поиска может быть, как в левой, так и в правой части диапазона поиска. Пример: покажет эту возможность в действии.

2. Безопасное добавление или удаление столбцов. Формулы с функцией ВПР перестают работать или возвращают ошибочные значения, если удалить или добавить столбец в таблицу поиска. Для функции ВПР любой вставленный или удалённый столбец изменит результат формулы, поскольку требует указывать весь диапазон и конкретный номер столбца, из которого нужно извлечь данные.

Например, если у Вас есть таблица A1:C10, и требуется извлечь данные из столбца B, то нужно задать значение 2 для аргумента col_index_num (номер_столбца) функции ВПР, вот так:

Если позднее Вы вставите новый столбец между столбцами A и B, то значение аргумента придется изменить с 2 на 3, иначе формула возвратит результат из только что вставленного столбца.

Используя ПОИСКПОЗ/ИНДЕКС, Вы можете удалять или добавлять столбцы к исследуемому диапазону, не искажая результат, так как определен непосредственно столбец, содержащий нужное значение. Действительно, это большое преимущество, особенно когда работать приходится с большими объёмами данных. Вы можете добавлять и удалять столбцы, не беспокоясь о том, что нужно будет исправлять каждую используемую функцию ВПР.

3. Нет ограничения на размер искомого значения. Используя ВПР, помните об ограничении на длину искомого значения в 255 символов, иначе рискуете получить ошибку #VALUE! (#ЗНАЧ!). Итак, если таблица содержит длинные строки, единственное действующее решение – это использовать ИНДЕКС/ПОИСКПОЗ.

Предположим, Вы используете вот такую формулу с ВПР, которая ищет в ячейках от B5 до D10 значение, указанное в ячейке A2:

Формула не будет работать, если значение в ячейке A2 длиннее 255 символов. Вместо неё Вам нужно использовать аналогичную формулу ИНДЕКС/ПОИСКПОЗ:

4. Более высокая скорость работы. Если Вы работаете с небольшими таблицами, то разница в быстродействии Excel будет, скорее всего, не заметная, особенно в последних версиях. Если же Вы работаете с большими таблицами, которые содержат тысячи строк и сотни формул поиска, Excel будет работать значительно быстрее, при использовании ПОИСКПОЗ и ИНДЕКС вместо ВПР. В целом, такая замена увеличивает скорость работы Excel на 13%.

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

С другой стороны, формула с функциями ПОИСКПОЗ и ИНДЕКС просто совершает поиск и возвращает результат, выполняя аналогичную работу заметно быстрее.

Особенности использования функций ДМАКС и ДМИН в Excel

Обе рассматриваемые функции имеют однотипный синтаксис (список аргументов совпадает):

=ДМАКС(база_данных;поле;условия)

=ДМИН(база_данных;поле;условия)

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

база_данных – обязательный, может быть указан в виде ссылки на диапазон ячеек, которые соответствуют БД, списку или таблице, которая соответствует критериям, установленным для баз данных в Excel. В такой таблице строки являются записями, а столбцы – полями. В первой строке должны быть указаны наименования полей (столбцов).
поле – обязательный, принимает ссылку на ячейку, содержащую наименование столбца (поля) таблицы, списка или БД, где будет выполняться поиск наибольшего (ДМАКС) или наименьшего (ДМИН) значения соответственно. Может быть также указан в виде текстовых данных или числового значения – соответствующего номера столбца (отсчет начинается с левой части таблицы с числа 1).
условия – обязательный, принимает ссылку на диапазон ячеек, в которых указаны критерии поиска. В таком диапазоне должно содержаться хотя бы одно поле, соответствующее полю таблицы, списка или БД, в которых будет выполняться поиск максимального или минимального значения.

Примечания:

Если любой из аргументов функции указан в виде данных недопустимого типа или ссылки на диапазон пустых ячеек, результатом выполнения любой из рассматриваемых функций будет код ошибки #ЗНАЧ!.
Если в качестве аргумента поле был указан столбец (поле БД), не содержащий числовые данные, результатом выполнения функции будет значение 0 (нуль).
Для удобства указания аргумента условия рекомендуют создавать отдельную таблицу, содержащую не менее двух записей (строк). При этом первая строка должна содержать наименование полей данных, полностью соответствующих наименованиям полей таблицы (списка или БД), где выполняется поиск. Вторая (и последующие) строка должна содержать критерии поиска (логические выражения, данные для сравнения). Такую таблицу условий следует размещать над основной таблицей (БД или списком), поскольку последняя может пополняться новыми записями со временем.
При написании условий используются записи следующих видов:

=»=телевизор» – точное совпадение текстовой строки. Для неточных совпадений можно использовать замещающие знаки, например, «*» – любое число символов, «?» – один любой символ;
Для числовых данных используют знаки сравнения значений: «>», «<», «=>», «=<» «<>».

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

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

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

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

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

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

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

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

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

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

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

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

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

В ячейке D2 введите фамилию Антонова.
В ячейке A3 введите следующую формулу:

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

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

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

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

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

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

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

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

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

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

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