Функции БСЧЁТ и БСЧЁТА для счета с несколькими условиями в Excel

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

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

Примеры использования функций БСЧЁТ и БСЧЁТА в Excel

Пример 1. В списке хранятся данные о количестве часов, проработанных сотрудниками предприятия 29.08.2018. Определить число работников, которые работали сверх нормы (свыше 8 часов).

Вид исходной таблицы вместе с таблицей критериев:

Пример 1.

Для поиска числа работников, проработавших более 8 часов, используем формулу:

БСЧЁТ.

Описание аргументов:

  • A10:B18 – диапазон ячеек, в котором находится исходная таблица (база данных);
  • B10 – ячейка, в которой хранится наименование столбца (поля);
  • B1:B2 – диапазон ячеек с критериями поиска.

Результат расчетов:

поиск числа работников.

Теперь при изменении числового значения в ячейке B2 автоматически обновляется число работников в ячейке C2 – соответственно.



Поиск неточного совпадения текста в Excel

Пример 2. Определить количество работников, фамилия которых начинается на «Соло». Продемонстрировать различие в использовании функции БСЧЁТ и БСЧЁТА для подсчета количества ячеек, содержащих текст.

Используем таблицу из первого примера. Создадим таблицу условий:

Пример 2.

Используем функцию БСЧЁТА:

БСЧЁТА.

В результате расчетов получим:

Поиск неточного совпадения текста.

Используем для подсчета количества работников функцию БСЧЁТ:

=БСЧЁТ(A10:B20;A10;A1:A2)

В результате вычислений функция вернет следующее значение:

Ошибка.

Такое поведение обусловлено тем, что функция БСЧЁТ предназначена для работы только с числовыми значениями. При использовании ее для подсчета количества ячеек с текстом или иными нечисловыми типами данных будет возвращен некорректный результат.

Примеры счета с несколькими условиями в Excel

Пример 3. В базе данных хранятся записи о массе отгруженных фруктов в различные страны, а также общей стоимости отправленного товара. Определить, сколько раз проводилась отгрузка яблок в Украину общей массой свыше 2000 кг, стоимость которых превышает среднюю стоимость яблок в таблице.

Вид таблицы данных:

Пример 3.

Таблица критериев имеет следующий вид:

Таблица критериев.

В ячейке D2 содержится формула:

=D11>СРЗНАЧЕСЛИ(B11:B23;"=яблоки";D11:D23)

В данном случае выполняется сравнение значения, хранящегося в ячейке D11 со средним значением стоимости отгруженных яблок, возвращаемым функцией СРЗНАЧЕСЛИ. Функция БСЧЁТА выполнит проверку этого выражения для каждого значения в столбце D:D, соответствующего установленным критериям (страна – Украина, фрукты – яблоки, масса отгрузки – свыше 2000 кг).

Для расчетов используем следующую формулу:

=БСЧЁТА(A10:D23;;A1:D2)

Второй аргумент не указан, поскольку поиск выполняется по всей таблице. Результат вычислений:

счет с несколькими условиями.

Особенности использования функций БСЧЁТ и БСЧЁТА в Excel

Рассматриваемые функции имеют схожий синтаксис:

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

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

Описание аргументов функций:

  • база_данных – обязательный для заполнения аргумент, принимающий данные в виде ссылки на диапазон ячеек, в которых находится база данных либо список;
  • поле – необязательный аргумент, принимающий текстовые данные, которые характеризуют наименование столбца (поля) для поиска. Может быть представлен в виде числа, соответствующего порядковому номеру столбца относительно первого. Для использования функции БСЧЁТ требуется, чтобы указанное поле содержало данные числового типа. Для функции БСЧЁТА это условие обязательным не является;
  • условия – обязательный аргумент, принимающий ссылку на диапазон ячеек, в которых содержатся критерии поиска.

Примечания:

  1. В качестве условия должна быть передана ссылка на диапазон из как минимум двух ячеек, в одной из которых содержится название поля базы данных, а во второй – критерий поиска.
  2. Таблицу с условиями не следует располагать под базой данных или списком, в которых выполняется поиск, поскольку они могут со временем пополняться новыми записями.
  3. Если требуется выполнить подсчет количества ячеек без какого-либо условия, можно создать таблицу с названием требуемого поля базы данных, а в качестве аргумента условия передать ссылку на ячейку с названием и расположенной под ней пустой ячейкой.
  4. База данных в Excel является списком связанных данных, содержащий поля (столбцы) и записи (строки). Наличие пустых ячеек или неименованных полей в базе данных не допускается.
  5. Столбец с непустыми ячейками в базе данных называется полем, поэтому один из аргументов рассматриваемых функций имеет название «поле». Фактически требуется передать название столбца таблицы или списка, под которым расположены ячейки для выполнения поиска.
  6. Аргумент поле обеих функций может быть не указан. В этом случае поиск будет произведен во всех полях базы данных или списка.
  7. Основным отличием функции БСЧЁТ от функции БСЧЁТА является то, что первая предназначена для работы только с ячейками, содержащими числовые значения. Вторая функция может выполнять поиск и подсчет количества ячеек, соответствующих установленному критерию, которые содержат данные других типов.

en ru