Функция БСЧЁТ() - Подсчет с множественными условиями в EXCEL

Функция БСЧЁТ() , английский вариант DCOUNT(), подсчитывает строки в таблице данных, которые удовлетворяют заданным условиям.

Использование функции БСЧЁТ() доступно только "продвинутым" пользователям, поэтому при встрече с ней у неподготовленных пользователей часто возникают негативные эмоции. Однако, есть хорошие новости - без функции БСЧЁТ() можно вообще обойтись, заменив ее функциями СУММПРОИЗВ() , СЧЁТЕСЛИМН() или формулами массива .

Иногда, функция БСЧЁТ() действительно удобна, особенно при использовании сложных критериев с подстановочными знаками или критериев на основе формул. Подробно эти задачи решаются в статьях Подсчет значений с множественными критериями ( Часть 2 , Часть 3 , Часть 4 ).

Подготовка исходной таблицы

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

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

Синтаксис функции БСЧЁТ()

БСЧЁТ(база_данных; поле; критерий)

Аргумент База_данных представляет собой таблицу (см. строки 8-13 на рисунке выше), по одному из столбцов которой производится подсчет значений. Верхняя строка таблицы должна содержать заголовки столбцов.

Поле — это название заголовка столбца, по которому производится подсчет. Аргумент Поле можно заполнить введя:

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

Подразумевается, что этот столбец содержит числа, инача функция вернет 0.

Примечание : Если значения в этом столбце содержат числа, сохраненные в текстовом формате , то нужно использовать функцию БСЧЁТА() .

Примечание : В теории баз данных строки таблицы называются записями, а столбцы — полями (отсюда и необычное название аргумента).

Критерий — интервал ячеек, который содержит задаваемые условия (т.е. ссылка на таблицу критериев). См. строки 2-4 на рисунке выше. Этот интервал ячеек аналогичен таблице для Расширенного фильтра и функции БДСУММ() и, конечно, БСЧЁТА() .

Задачи

Предположим, что в диапазоне A 8:B13 имеется таблица продаж, содержащая поля (столбцы) Продавец и Продажи (см. рисунок выше и файл примера ) .

Задача 1 (с одним критерием)

Подсчитаем количество продаж на сумму  >3000. Т.е. подсчет будем вести по столбцу Продажи .

Алгоритм следующий:

• Создадим табличку критериев (желательно над исходной таблицей, чтобы она не мешала добавлению новых данных в таблицу), например в диапазоне A2:A3 (см. рисунок выше). Табличка должна состоять из заголовка (совпадает с названием заголовка столбца исходной таблицы, к которому применяется критерий) и собственно критерия (условия отбора);
• Условия отбора могут быть записаны в текстовом формате. В нашем случае, просто >3000
• Теперь можно записать саму формулу =БСЧЁТ(B8:B13;B8;A2:A3) В B8:B13 находится База_данных (исходная таблица). Столбец А (Продавец) можно в данном случае не включать в базу_данных , т.к. по нему не производится подсчет и он не участвует в отборе. B8 – это ссылка на заголовок столбца по которому будет производиться подсчет ( Продажи ), в нем должны быть числовые значения. A2:A3 – ссылка на табличку критериев.

Альтернативное решение - простая формула = СЧЁТЕСЛИ(B9:B13;A3) .

Вывод : для простых однокритериальных задач огород с функцией =БСЧЁТ() городить не стоит.

Задача 2 (Два критерия (разные столбцы), условие И)

Подсчитаем Число продаж Белова, которые меньше 3000.

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

Записать саму формулу можно так =БСЧЁТ(A8:B13;B8;A2:B3)

Альтернативное решение - простая формула = СЧЁТЕСЛИМН(A9:A13;"Белов";B9:B13;A3) .

Задача 3 (Два критерия (один столбец), условие И)

Подсчитаем число продаж в интервале >3000 и <6000.

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

Записать саму формулу можно так =БСЧЁТ(B8:B13;B8;D2:E3)

Альтернативное решение - простая формула =СЧЁТЕСЛИМН(B9:B13;">3000";B9:B13;"<6000")

Задача 4 (Два критерия (один столбец), условие ИЛИ)

Подсчитаем число продаж <3000 или >6000.

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

Записать саму формулу можно так =БСЧЁТ(B8:B13;B8;A2:A4)

Альтернативное решение - формула =СЧЁТЕСЛИ(B9:B13;"<3000")+СЧЁТЕСЛИ(B9:B13;">6000")

Задача 5 (Два критерия (разные столбцы), условие ИЛИ)

Подсчитаем продажи Белова и продажи, любого продавца, которые >6000 (также исключим возможное дублирование: продажи Белова, которые >6000).

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

Записать саму формулу можно так =БСЧЁТ(A8:B13;B8;H2:I4)

Альтернативное решение - формула =СЧЁТЕСЛИ(A9:A13;"Белов")+СЧЁТЕСЛИ(B9:B13;">6000")-СЧЁТЕСЛИМН(B9:B13;">6000";A9:A13;"Белов")

Задача 6 (Условия отбора, созданные в результате применения формулы)

Подсчитаем количество продаж с величиной выше среднего.

В качестве условия отбора можно использовать значение, вычисляемое при помощи формулы. Формула должна возвращать результат ИСТИНА или ЛОЖЬ.

Для этого введем в ячейку F 3 формулу =B9>СРЗНАЧ($B$9:$B$13) , а в F 2 вместо заголовка введем, произвольный поясняющий текст, например, « Больше среднего » (заголовок не должен повторять заголовки исходной таблицы).

Обратите внимание на то, что диапазон нахождения среднего значения введен с использованием абсолютных ссылок ( $B$9:$B$13 ), а среднее значение сравнивается с первым значением диапазона, ссылка на который задана относительной адресацией ( B9 ). Это необходимо, поскольку при вычислении функции БСЧЁТ() EXCEL увидит, что B9 — это относительная ссылка, и будет перемещаться вниз по диапазону по одной записи за раз и возвращать значение либо ИСТИНА, либо ЛОЖЬ (больше среднего или нет). Если будет возвращено значение ИСТИНА, то соответствующая строка таблицы будет учтена при подсчете. Если возвращено значение ЛОЖЬ, то строка учтена не будет.

Записать формулу можно так =БСЧЁТ(B8:B13;B8;F2:F3)

Альтернативное решение - формула = СЧЁТЕСЛИ(B9:B13;">"&СРЗНАЧ($B$9:$B$13))

Задача 7 (3 критерия)

Подсчитаем Число продаж Белова, которые выше среднего, и все продажи Батурина.

Табличка с критериями в этом случае выглядит так:

Записать формулу можно так =БСЧЁТ(A8:B13;B8;F2:G4)

Альтернативное решение - формула = СЧЁТЕСЛИМН(B9:B13;">"&СРЗНАЧ($B$9:$B$13);A9:A13;"Белов")+СЧЁТЕСЛИ(A9:A13;"Батурин")