Трюк №66. Как правильно просуммировать и подсчитать ячейки в Excel 2010

Ошибочные значения — это полезные предупреждения, но иногда необходимо выполнить вычисления, несмотря на ошибки. В этом помогут функции, допускающие ошибки. Если диапазон ячеек содержит одно или несколько ошибочных значений, большинство формул, ссылающихся на этот диапазон, возвращают ошибку. Эту проблему можно преодолеть при помощи функции БДСУММ (DSUM).

Предположим, есть длинный список чисел, для которых необходимо вычислить общую сумму. Но одна из ячеек по какой-то причине возвращает ошибку #N/А. Введите некоторые данные (рис. 6.8).

Рис. 6.8. Данные, которые будут выдавать сообщение об ошибке #N/А

Рис. 6.8. Данные, которые будут выдавать сообщение об ошибке #N/А

Чтобы генерировать ошибку #N/А, введите формулу =#N/А() в ячейки А2 и В2. В ячейке А12 находится стандартная функция СУММ (SUM), суммирующая ячейки А2:А11, но, так как ячейка А2 возвращает ошибку #М/А, функция СУММ (SUM) также возвращает ошибку #N/А. Диапазону D1:D2 присвоено имя Criteria и он используется как последний аргумент функции БДСУММ (DSUM) в ячейке В12. Синтаксис функции БДСУММ (DSUM) (и всех остальных функций баз данных) =DSUM(database.field; criteria), в русской версии Excel =БДСУММ(база_данных;поле.критерий).

Аргумент база_данных (database) — это диапазон ячеек, формирующих список или базу данных. В диапазоне базы данных строки связанной информации считаются записями, а столбцы данных считаются полями. Первая строка содержит подписи для всех столбцов.

Аргумент поле (field) определяет столбец, используемый функцией. Столбец может быть задан по имени (по подписям, которые находятся наверху столбцов) или по позиции. Позиция первого столбца равна 1, четвертого — 4 и т. д.

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

Если данные, которые нужно суммировать, могут содержать ошибки различного типа, может потребоваться функция БДСУММ (DSUM) с набором критериев для всех возможных ошибок. Однако всегда лучше устранить источник ошибки, чем пытаться обойти ее. Чтобы обойти ошибку, необходимо снова применить функцию БДСУММ (DSUM), но на этот раз указать критерий, охватывающий четыре столбца, расширив именованный диапазон для критерия на ячейки D1:G2 при помощи команды Формулы → Имя → Присвоить (Formulas → Name → Define).

В Excel 2010 предусмотрен широкий набор функций баз данных, и подобным образом можно применять любую. Попробуйте использовать тот же метод для функций СЧЁТ (DCOUNT), БСЧЁТА (DCOUNTA), ДМАКС (ОМАХ), ДМИН (DMIN), БДПРОИЗВЕД (DPRODUCT) и т. д.

Top