Функция БСЧЁТА() , английский вариант DCOUNTA(), подсчитывает текстовые значения в таблице данных, которые удовлетворяют заданным условиям.
Без функции БСЧЁТА() можно вообще обойтись, заменив ее функциями СУММПРОИЗВ() , СЧЁТЕСЛИ() , СЧЁТЕСЛИМН() или формулами массива . Но, иногда, функция БСЧЁТА() действительно удобна, особенно при использовании сложных критериев с подстановочными знаками или критериев на основе формул. Подробно эти задачи решаются в статьях Подсчет ТЕКСТовых значений с единственным критерием , Подсчет значений с множественными критериями ( Часть 2 , Часть 3 , Часть 4 ).
Для использования этой функции требуется чтобы:
БСЧЁТА(база_данных;поле;условия)
База_данных представляет собой диапазон ячеек с данными связанными логически, т.е. таблицу. В теории баз данных строки таблицы называются записями, а столбцы — полями. Верхняя строка таблицы должна содержать заголовки всех столбцов.
Поле — Заголовок столбца, по которому производится подсчет. Аргумент Поле можно заполнить введя:
Для функции БСЧЁТА() значения этого столбца могут быть текстовыми или числовыми. Для функции БСЧЁТ() значения этого столбца могут быть только числовыми. Вывод: функция БСЧЁТА() полностью перекрывает функционал БСЧЁТ() , которая получается не нужна - все что можно подсчитать БСЧЁТ() можно подсчитать функции БСЧЁТА() (причем с теми же аргументами, просто заменив имя функции БСЧЁТ на БСЧЁТА).
Условия — интервал ячеек, который содержит задаваемые условия (т.е. таблица критериев).
Структура таблицы с условиями отбора для БСЧЁТА() аналогична структуре для Расширенного фильтра и функций БДСУММ() и БСЧЁТ() .
Предположим, что в диапазоне A 8:B13 имеется таблица продаж, содержащая поля (столбцы) Товар и Продавец (см. файл примера ) .
Задача 1 (с одним критерием). Подсчитаем все продажи продавца Белов .
Алгоритм следующий:
Если в качестве критерия указать не ="= Белов" , а просто Белов , то, будут подсчитыватьсязначения, начинающиеся со слова Белов (например, « Белов Иван», Белов, белов ).
Чтобы подсчитать продажи для « Иван Белов », необходимо в качестве критерия указать ="=*Белов". Этот критерий учитывает значения, заканчивающиеся на Белов.
Если в качестве критерия указать *Белов (или ="=*Белов*") , то будут подсчитаны ячейки, в которых содержится слово Белов. Звездочка ( *) - это подстановочный знак .
Альтернативное решение - простая формула = СЧЁТЕСЛИ(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))
В файле примера приведены решения вышеразобранных задач.
© Copyright 2013 - 2024 Excel2.ru. All Rights Reserved
Комментарии