В данной категории собраны несколько специфические функции для работы с упорядоченными данными. Не зря категория включает в себя упоминание про базы данных: таблицы для работы этих функций должны отвечать определенным требованиям:
- таблица должна обязательно содержать заголовки столбцов. Эти заголовки должны располагаться строго в одной строке, не должны содержать объединенных и пустых ячеек.
- таблица должна быть неделимая, т.е. не должна содержать полностью пустых строк и столбцов, а так же объединенных ячеек
- в каждом столбце должна содержаться однотипная информация: если в столбце должны содержаться даты, значит кроме дат там не должно быть ничего другого; если в столбце числа(суммы, кол-во) - значит должны быть только числа. Не следует при отсутствии чисел оставлять ячейку пустой или ставить пробел. Вместо этого необходимо ставить 0.
Для разбора функции баз данных возьмем следующий пример таблицы:
Данная таблица отвечает всем требованиям для работы с функциями баз данных, однако, чтобы более удобно и гибко работать с функциями баз данных лучше сместить таблицу данных на несколько строк вниз, а выше добавить таблицу критериев, где будут формироваться условия отбора данных из основной таблицы:
Именно для этой таблицы будут приведены все примеры описаний функций. И критерии заданы следующие: отбирать из поля "Дерево" Яблони с высотой больше 3 и меньше 6 и Вишни, со значением в поле "Возраст" больше 8. Если посмотреть на таблицу данных(из которой будут отбираться данные и производится расчеты функциями), то этим критериям отвечают только две строки: строки 9 и 10 листа.
Все функции из категории баз данных имеют три одинаковых аргумента:
база_данных - ссылка на ячейки данных таблицы, включая заголовок(
поле - в данном аргументе можно записать как непосредственно текст с названием столбца в кавычках ("Дерево", "Возраст" или "Урожай"), так и число, задающее положение столбца в таблице: 1 - для первого поля(столбца) в таблице "Дерево", 2 - для второго поля "Высота", 3 - для третьего поля "Возраст" и так далее.
критерий - ссылка на диапазон ячеек с условиями отбора(
- ДСРЗНАЧ
(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) - Подсчитывает количество непустых ячеек в базе данных:
=БСЧЁТА( вернет 2, т.е. подсчитает в отвечающих критериям строках количество непустых ячеек в столбце "Прибыль"A6:E12 ;4;A1:F3 )
=БСЧЁТА(A6:E12 ;"Прибыль";A1:F3 )
=DCOUNTA( A6:E12 ,4,A1:F3 ) - БИЗВЛЕЧЬ
(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 , т.е. точную дисперсию урожайности Яблонь и Вишень, если считать, что данные в базе данных описывают генеральную совокупность всех деревьев в саду
