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

history

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


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

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

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

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

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

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

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

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

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

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

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

Задачи



Предположим, что в диапазоне A 8: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))

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


Комментарии

Только для авторизованных пользователей

Аноним, 16 января 2018 г.
Здравствуйте. Помогите мне пожалуйста посчитать сумму или количество чисел в текстовой строке. Пример: В одной ячейке значение 525mna22 а в другой ABV 224mn25. Мне нужно суммировать числа этих ячеек при том что бы числа и буквы остались как есть. В смысле я могу заменить буквы на нет и суммировать цифры, но мне не это нужно. Мне нужно что бы бувы остались как есть. Спасибо.
Михаил, 17 января 2018 г.
Ничего заменять не нужно. В соседних ячейках извлеките оба числа, избавившись от букв. Для извлечения чисел используйте идеи из статьи http://excel2.ru/articles/razbor-tekstovyh-strok-v-ms-excel Если сами не разберетесь пишите в группу https://vk.com/excel2ru
(только для авторизованных пользователей)

© Copyright 2013 - 2024 Excel2.ru. All Rights Reserved