Функция БСЧЁТА() - Подсчет ТЕКСТовых значений с множественными условиями в MS EXCEL

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

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

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

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

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

БСЧЁТА(база_данных;поле;условия)

База_данных представляет собой диапазон ячеек с данными связанными логически, т.е. таблицу. В теории баз данных строки таблицы называются записями, а столбцы — полями. Верхняя строка таблицы должна содержать заголовки всех столбцов.

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

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

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

Условия  — интервал ячеек, который содержит задаваемые условия (т.е. таблица критериев).

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

Задачи

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

Задача 1 (с одним критерием). Подсчитаем все продажи продавца Белов.

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

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

  • Условия отбора должны быть записаны в специальном формате: ="=Белов" (будут подсчитываться ячейки, в которых содержится точно слово Белов (или белов, беЛОв, т.е. без учета регистра). Если имеются строки с Продавцами «Иван Белов», «Белов Иван» и пр., то они учитываться при подсчете не будут.
  • Теперь можно наконец записать саму формулу =БСЧЁТА(B8:B13;B8;B2:B3) Предполагая, что база_данных (исходная таблица) находится в B8:B13 (столбец А (Товар) можно в данном случае не включать в базу_данных, т.к. по нему не производится подсчет и он не участвует в отборе). B8 – это ссылка на заголовок столбца по которому будет производиться подсчет (Продавец). B2:B3 – ссылка на табличку критериев.

Если в качестве критерия указать не ="= Белов", а просто Белов, то, будут подсчитыватьсязначения, начинающиеся со слова Белов (например, «Белов Иван», Белов, белов).

Чтобы подсчитать продажи для «Иван Белов», необходимо в качестве критерия указать ="=*Белов". Этот критерий учитывает значения, заканчивающиеся на Белов.

Если в качестве критерия указать *Белов (или ="=*Белов*"), то будут подсчитаны ячейки, в которых содержится слово Белов. Звездочка (*) - это подстановочный знак.

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

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

 

Задача 2 (с двумя критериями, применяемых к одному столбцу, условие ИЛИ). Подсчитаем количество строк с продажами продавцов Белова ИЛИ Батурина.

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

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

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

 

Задача 3 (с двумя критериями, применяемых к разным столбцам, условие И). Найдем количество продаж фруктов продавца Белов.

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

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

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

Задача 4 (с двумя критериями, применяемых к разным столбцам, условие ИЛИ). Подсчитаем количество продаж Белова ИЛИ Товара Фрукты.

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

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

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

 

Задача 5 (Условия отбора, созданные в результате применения формулы). Подсчитаем количество продаж товара ФРУкты (с учетом РЕгиСТра).

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

Для этого введем в ячейку С3 файла примера формулу =СОВПАД("ФРУкты";A9), а в С2 вместо заголовка введем, произвольный поясняющий текст, например, «с учетом РЕгиСТра» (заголовок не должен повторять заголовки Исходной таблицы).

Записать формулу для подсчета количества продаж товара ФРУкты можно так =БСЧЁТА(A8:A13;A8;C2:C3)

Альтернативное решение - формула =СУММПРОИЗВ(--СОВПАД("ФРУкты";A9:A13))

В файле примера приведены решения вышеразобранных задач.

Связанные статьи

Похожие задачи
Прочитайте другие статьи, решающие похожие задачи в MS Excel. Это позволит Вам решать широкий класс подобных задач.
Средняя: 5 (3 оценок)