Главная » Функции Excel »

Функции баз данных

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

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

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

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

  • ДСРЗНАЧ(DAVERAGE) - Вычисляет среднее значение выбранных записей базы данных:
    =ДСРЗНАЧ(A6:E12;5;A1:F3)
    =ДСРЗНАЧ(A6:E12;"Прибыль";A1:F3)
    =DAVERAGE(A6:E12,5,A1:F3)
    вернет значение 90 000р., т.к. сумма прибыли отобранных записей равна 180 000р., а всего отобрано 2 записи. 180 000/2 = 90 000.
  • БСЧЁТ(DCOUNT) - Подсчитывает количество числовых ячеек в базе данных:
    =БСЧЁТ(A6:E12;5;A1:F3)
    =БСЧЁТ(A6:E12;"Прибыль";A1:F3)
    =DCOUNT(A6:E12,5,A1:F3)
    вернет число 2, т.к. только две строки в таблице отвечают критериям
  • БСЧЁТА(DCOUNTA) - Подсчитывает количество непустых ячеек в базе данных:
    =БСЧЁТА(A6:E12;4;A1:F3)
    =БСЧЁТА(A6:E12;"Прибыль";A1:F3)
    =DCOUNTA(A6:E12,4,A1:F3)
    вернет 2, т.е. подсчитает в отвечающих критериям строках количество непустых ячеек в столбце "Прибыль"
  • БИЗВЛЕЧЬ(DGET) - Извлекает из базы данных одну запись, удовлетворяющую заданному условию:
    =БИЗВЛЕЧЬ(A6:E12;5;A1:F3)
    =БИЗВЛЕЧЬ(A6:E12;"Прибыль";A1:F3)
    =DGET(A6:E12,5,A1:F3)
    для заданных условий вернет значение ошибки #ЧИСЛО!(#NUM!), т.к. этим условиям отвечает более одной записи. Если же указать диапазон для критерия как:
    =БИЗВЛЕЧЬ(A6:E12;5;A1:F2)то функция вернет значение 75 000р., т.е. единственную запись о прибыли для Яблонь с высотой больше 3 и меньше 6 (в данный промежуток попадает лишь строка 10 - Яблона, высота 5)
  • ДМАКС(DMAX) - Находит максимальное значение среди выделенных записей базы данных:
    =ДМАКС(A6:E12;5;A1:F3)
    =ДМАКС(A6:E12;"Прибыль";A1:F3)
    =DMAX(A6:E12,5,A1:F3)
    вернет сумму 105 000р., т.к. это максимальная прибыль из всех отвечающих критериям строк.
  • ДМИН(DMIN) - Находит минимальное значение среди выделенных записей базы данных:
    =ДМИН(A6:E12;5;A1:F3)
    =ДМИН(A6:E12;"Прибыль";A1:F3)
    =DMIN(A6:E12,5,A1:F3)
    вернет сумму 75 000р., т.к. это минимальная прибыль из всех строк, отвечающих критериям
  • БДПРОИЗВЕД(DPRODUCT) - Перемножает значения определенного поля в записях базы данных, удовлетворяющих условию:
    =БДПРОИЗВЕД(A6:E12;3;A1:F3)
    =БДПРОИЗВЕД(A6:E12;"Возраст";A1:F3)
    =DPRODUCT(A6:E12,3,A1:F3)
    вернет 210, т.к. будут перемножены все значения столбца "Возраст", отвечающие критериям(14*15=210)
  • ДСТАНДОТКЛ(DSTDEV) - Оценивает стандартное отклонение по выборке из выделенных записей базы данных:
    =ДСТАНДОТКЛ(A6:E12;4;A1:F3)
    =ДСТАНДОТКЛ(A6:E12;"Урожайность";A1:F3)
    =DSTDEV(A6:E12,4,A1:F3)
    вернет 0,707107, т.е. оценку стандартного отклонения урожайности по указанным критериям.
  • ДСТАНДОТКЛП(DSTDEVP) - Вычисляет стандартное отклонение по генеральной совокупности из выделенных записей базы данных:
    =ДСТАНДОТКЛП(A6:E12;4;A1:F3)
    =ДСТАНДОТКЛП(A6:E12;"Урожайность";A1:F3)
    =DSTDEVP(A6:E12,4,A1:F3)
    вернет 0,5, т.е. точное стандартное отклонение урожайности по указанным критериям, если считать, что данные в базе данных описывают генеральную совокупность всех деревьев в саду.
  • БДСУММ(DSUM) - Суммирует числа в поле для записей базы данных, удовлетворяющих условию:
    =БДСУММ(A6:E12;5;A1:F3)
    =БДСУММ(A6:E12;"Прибыль";A1:F3)
    =DSUM(A6:E12,5,A1:F3)
    вернет сумму прибыли всех строк, отвечающих критериям, т.е. 180 000р.
    =БДСУММ(A6:E12;5;A1:A2)
    =DSUM(A6:E12,5,A1:A2)
    вернет сумму прибыли от всех Яблонь, т.е. 225 000р.
  • БДДИСП(DVAR) - Оценивает дисперсию по выборке из выделенных записей базы данных:
    =БДДИСП(A6:E12;4;A1:A2)
    =БДДИСП(A6:E12;"Урожайность";A1:A2)
    =DVAR(A6:E12,4,A1:A2)
    вернет 0,5, что будет оценкой дисперсии урожайности по указанным критериям, если считать, что данные в таблице являются выборкой из генеральной совокупности всех деревьев в саду
  • БДДИСПП(DVARP) - Вычисляет дисперсию по генеральной совокупности из выделенных записей базы данных:
    =БДДИСПП(A6:E12;4;A1:A2)
    =БДДИСПП(A6:E12;"Урожайность";A1:A2)
    =DVARP(A6:E12,4,A1:A2)
    вернет 10,66667, т.е. точную дисперсию урожайности Яблонь и Вишень, если считать, что данные в базе данных описывают генеральную совокупность всех деревьев в саду

Статья помогла? Сделай твит, поделись ссылкой с друзьями!
Логин
Наши партнеры
Перейти
Перейти
Счетчики
Анализ сайта

Яндекс.Метрика
© 2016 Excel для всех  Войти