Какие функции позволяют извлекать из базы данных нужное значение в excel

Обновлено: 06.01.2025

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

БДДИСП

Результат: Дисперсия [рассеяние] генеральной совокупности, определенная путем оценки выборки относительно всех выбранных значений.

база_данных - интервал ячеек, формирующих базу данных;
поле - аргумент типа Text, который должен содержать имя столбца (поля) базы данных;
критерий - интервал ячеек, который содержит критерий поиска.

БДДИСПП

Результат: Дисперсия генеральной совокупности относительно всех выбранных значений.

См. описание функции БДДИСП.

БДПРОИЗВЕД

Результат: Произведение всех выбранных значений.

См. описание функции БДДИСП.

БДСУММ

Результат: Сумма всех выбранных значений.

См. описание функции БДДИСП.

БИЗВЛЕЧЬ

См. описание функции БДДИСП.

БСЧЕТ

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

См. описание функции БДДИСП.

БСЧЕТА

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

См. описание функции БДДИСП.

ДМАКС

Результат: Наибольшее число в соответствующих критериям поиска записях данных поля поле в базе данных.

См. описание функции БДДИСП.

Результат:
Наименьшее число в соответствующих критериям поиска записях данных поля поле в базе данных.

См. описание функции БДДИСП.

ДСРЗНАЧ

Результат: Среднее значение, которое вычисляется для значений в указанном поле всех записей, удовлетворяющих критерию.

См. описание функции БДДИСП.

ДСТАНДОТКЛ

Результат: Стандартное отклонение генеральной совокупности, определенное посредством оценки выборки для выбранных значений.

См. описание функции БДДИСП.

ДСТАНДОТКЛП

Результат: Стандартное отклонение генеральной совокупности относительно выбранных значений.

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

таблица должна обязательно содержать заголовки столбцов. Эти заголовки должны располагаться строго в одной строке, не должны содержать объединенных и пустых ячеек.
таблица должна быть неделимая, т.е. не должна содержать полностью пустых строк и столбцов, а так же объединенных ячеек
в каждом столбце должна содержаться однотипная информация: если в столбце должны содержаться даты, значит кроме дат там не должно быть ничего другого; если в столбце числа(суммы, кол-во) - значит должны быть только числа. Не следует при отсутствии чисел оставлять ячейку пустой или ставить пробел. Вместо этого необходимо ставить 0.

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

Данная таблица отвечает всем требованиям для работы с функциями баз данных, однако, чтобы более удобно и гибко работать с функциями баз данных лучше сместить таблицу данных на несколько строк вниз, а выше добавить таблицу критериев, где будут формироваться условия отбора данных из основной таблицы:

Именно для этой таблицы будут приведены все примеры описаний функций. И критерии заданы следующие: отбирать из поля "Дерево" Яблони с высотой больше 3 и меньше 6 и Вишни, со значением в поле "Возраст" больше 8. Если посмотреть на таблицу данных(из которой будут отбираться данные и производится расчеты функциями), то этим критериям отвечают только две строки: строки 9 и 10 листа.
Как видно, в качестве критериев можно указывать выражение в виде: >6 , , <>0 (не равно нулю), >=7 , . Так же знаки равенства и сравнения можно применять и с текстовыми данными, например: <>"Яблоня" .

Все функции из категории баз данных имеют три одинаковых аргумента:
Функция(база_данных; поле; критерий)
база_данных - ссылка на ячейки данных таблицы, включая заголовок( A6:E12 ).
поле - в данном аргументе можно записать как непосредственно текст с названием столбца в кавычках ("Дерево", "Возраст" или "Урожай"), так и число, задающее положение столбца в таблице: 1 - для первого поля(столбца) в таблице "Дерево", 2 - для второго поля "Высота", 3 - для третьего поля "Возраст" и так далее.
критерий - ссылка на диапазон ячеек с условиями отбора( A1:F3 ). Функция отберет данные из таблицы, которые удовлетворяют условиям, указанным в ячейках критериев. В ссылке на критерии обязательно должны быть включены названия столбцов, для которых выполняется отбор данных.

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

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

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

Примеры условий:

При наличии для одного столбца двух и более условий отбора введите эти условия отбора непосредственно друг под другом в отдельные строки. Например, следующий диапазон условий отбора отбирает строки, содержащие в столбце «Субконто кредита1» значения «Белов», «Батурин» или «Рощин».

Для того чтобы найти в нескольких столбцах данные, отвечающие одному условию отбора, введите все условия отбора в одну строку диапазона условий отбора. Например, следующий диапазон условий отбора возвращает все строки, содержащие значения «20» в столбце «Дебет», «Белов» в столбце «Субконто кредита 1» и сумму проводки более 1 000 руб.

Для того чтобы найти данные, отвечающие одному условию, в одном столбце, или отвечающие другому условию, в другом столбце, введите условия отбора в разные строки диапазона условий отбора. Например, следующий диапазон условий отбора отображает все строки, содержащие значение «20» в столбце «Дебет», «Белов» в столбце «Субконто кредита 1», либо сумму проводки, превышающую 1 000 руб.

Для того чтобы найти строки, отвечающие одному из двух наборов условий, каждый из которых содержит условия более чем для одного столбца, введите эти условия отбора в отдельные строки. Например, следующий диапазон условий отбора отображает строки, содержащие как значение «Белов» в столбце «Субконто кредита 1», так и сумму более 3 000 руб., а также строки по сотруднику Батурину с суммами более 1 500 руб.

Для того чтобы найти строки, отвечающие более чем двум наборам условий, включите несколько столбцов с одинаковыми заголовками. Например, следующий диапазон условий отбора возвращает проводки суммой от 5 000 руб. до 8 000 руб., а также проводки суммой менее 500 руб.

Между условиями отбора в одной строке можно мысленно поставить логический оператор «И». Между строками условий – оператор «ИЛИ». (Аналогичная схема работает в конструкторе запросов Access).

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

ДСРЗНАЧ - возвращает среднее значение поля для выбранных по критериям записей базы данных;
ДМАКС - возвращает максимальное значение среди выделенных записей базы данных;
ДМИН - возвращает минимальное значение среди выделенных записей базы данных.
БИЗВЛЕЧЬ - извлекает из базы данных одну запись, удовлетворяющую заданному условию.
БДСУММ - суммирует значения поля для записей, соответствующих критериям отбора.

Совет! Механизм отбора записей с использованием области критериев, описанный выше, можно использовать для фильтрации списка с помощью меню «Данные – Фильтр – Расширенный фильтр…».

= Мир MS Excel/Статьи об Excel

Функции листа. Функции баз данных [1]

Функции листа. Функции даты и времени [5]

Функции листа. Функции инженерные [1]

Функции листа. Функции информационные [2]

Функции листа. Функции логические [10]

Функции листа. Функции математические [4]

Функции листа. Функции статистические [1]

Функции листа. Функции текстовые [1]

Функции листа. Функции финансовые [1]

Функции листа. Функции ссылок и массивов [2]

Функции листа. Функции аналитические [1]

Функции листа. Функции определённые пользователем [1]

Функция диаграмм [1]

Макрофункции [37]

Список - это упорядоченный набор данных, построенный по определённым правилам. Обычно список состоит из строки заголовков и строк данных. Заголовки столбцов необязательны, но при работе с функциями баз данных весьма желательны, иначе первая строка данных будет интерпретирована функциями как строка заголовка и данные из неё не будут учитываться в возвращаемом результате. Список можно считать табличной частью базы данных, чем он, в сущности, и является. При работе с функциями баз данных используется терминология баз данных: столбцы называются полями, а строки - записями.

Все функции баз данных имеют одинаковый синтаксис: =ФУНКЦИЯ(база_данных;поле;критерий)

Читайте также: