В этой статье описаны синтаксис формулы и использование функции БСЧЁТА в Microsoft Excel.
Описание
Подсчитывает непустые ячейки в поле (столбце) записей списка или базы данных, которые удовлетворяют заданным условиям.
Аргумент "поле" не является обязательным. Если аргумент "поле" опущен, функция БСЧЁТА подсчитывает количество соответствующих условиям записей в базе данных.
Синтаксис
БСЧЁТА(база_данных; поле; условия)
Аргументы функции БСЧЁТА указаны ниже.
-
База_данных — обязательный аргумент. Диапазон ячеек, образующих список или базу данных. База данных представляет собой список связанных данных, в котором строки данных являются записями, а столбцы — полями. Первая строка списка содержит заголовки всех столбцов.
-
Поле — необязательный аргумент. Указывает столбец, используемый функцией. Введите текст с заголовком столбца в двойных кавычках, например "Возраст" или "Урожай", или число (без кавычек), задающее положение столбца в списке: 1 — для первого столбца, 2 — для второго и т. д.
-
Условия — обязательный аргумент. Диапазон ячеек, который содержит задаваемые условия. В качестве аргумента "условия" можно использовать любой диапазон, который содержит хотя бы один заголовок столбца и хотя бы одну ячейку с условием, расположенную под заголовком столбца.
Замечания
-
В качестве аргумента "условия" можно использовать любой диапазон, который содержит хотя бы один заголовок столбца и хотя бы одну ячейку с условием, расположенную под заголовком столбца.
Например, если диапазон G1:G2 содержит заголовок столбца "Доход" в ячейке G1 и значение 10 000 ₽ в ячейке G2, можно определить диапазон "СоответствуетДоходу" и использовать это имя как аргумент "условия" в функции баз данных.
-
Несмотря на то, что диапазон условий может находиться в любом месте листа, не следует помещать его под списком. Это связано с тем, что данные, добавляемые в список, вставляются в первую строку после списка. Если эта строка уже содержит данные, Excel не сможет добавить новые данные в список.
-
Диапазон условий не должен перекрываться со списком.
-
Чтобы выполнить операцию над целым столбцом базы данных, вставьте пустую строку под строкой заголовков столбцов в диапазоне условий.
Примеры
Скопируйте образец данных из следующей таблицы и вставьте их в ячейку A1 нового листа Excel. Чтобы отобразить результаты формул, выделите их и нажмите клавишу F2, а затем — клавишу Enter. При необходимости измените ширину столбцов, чтобы видеть все данные. При копировании в Excel любого из приведенных ниже примеров выделяйте все ячейки таблицы, включая самую верхнюю левую.
Дерево |
Высота |
Возраст |
Урожай |
Доход |
Высота |
="=Яблоня" |
>3 |
<5 |
|||
="=Груша" |
|||||
Дерево |
Высота |
Возраст |
Урожай |
Доход |
|
Яблоня |
18 |
20 |
14 |
105,0 |
|
Груша |
12 |
12 |
10 |
96,0 |
|
Вишня |
13 |
14 |
9 |
105,0 |
|
Яблоня |
14 |
15 |
10 |
75,0 |
|
Груша |
9 |
8 |
8 |
76,8 |
|
Яблоня |
8 |
9 |
6 |
45,0 |
|
Формула |
Описание |
Результат |
|||
=БСЧЁТА(A4:E10;"Доход";A1:F2) |
Подсчитывает строки (1), содержащие значение "Яблоня" в столбце A с высотой >3 и <5 метров. Только строка 8 удовлетворяет трем этим условиям. |
1 |
Примеры условий
-
При вводе в ячейку значения =текст Excel интерпретирует его как формулу и пытается выполнить вычисления. Чтобы значение =текст не вычислялось, используйте такой синтаксис:
=''= ввод ''
где ввод — искомый текст или значение. Например:
Вводится в ячейку |
Вычисляется и отображается |
="=Егоров" |
=Егоров |
="=3000" |
=3000 |
-
При фильтрации текстовых данных в Excel не учитывается регистр букв. Однако для поиска с учетом регистра можно воспользоваться формулой.
Примеры сложных условий отбора приведены в следующих разделах.
Несколько условий в одном столбце
Логическое выражение: (Продавец = "Егоров" ИЛИ Продавец = "Грачев")
Чтобы найти строки, отвечающие нескольким условиям для одного столбца, введите условия непосредственно одно под другим в разных строках диапазона условий.
В диапазоне данных (A6:C10) диапазон условий (B1:B3) используется для подсчета строк, содержащих значения "Егоров" или "Грачев" в столбце "Продавец".
|
Продавец |
|
="=Егоров" |
||
="=Бутусов" |
||
|
||
Категория |
Продавец |
Продажи |
Напитки |
Шашков |
5 122 ₽ |
Мясо |
Егоров |
450 ₽ |
фрукты |
Грачев |
6 328 ₽ |
Фрукты |
Егоров |
6 544 ₽ |
Формула |
Описание |
Результат |
'=БСЧЁТА(A6:C10;2;B1:B3) |
Подсчитывает число строк (3) в ячейках A6:C10, которые удовлетворяют одному из условий "Продавец" в строках 2 и 3. |
=БСЧЁТА(A6:C10;2;B1:B3) |
Несколько условий в нескольких столбцах, где все условия должны быть истинными
Логическое выражение: (Тип = "Фрукты" И Продажи > 2000)
Чтобы найти строки, удовлетворяющие нескольким условиям в нескольких столбцах, введите все условия в одной строке диапазона условий.
В диапазоне данных (A6:C12) диапазон условий (A1:C2) используется для подсчета строк, содержащих слово "Фрукты" в столбце "Категория" и значения больше 2 000 ₽ в столбце "Продажи".
Категория |
Продавец |
Продажи |
="=Фрукты" |
>2 000 |
|
Категория |
Продавец |
Продажи |
Напитки |
Шашков |
5 122 ₽ |
Мясо |
Егоров |
450 ₽ |
Фрукты |
Грачев |
935 ₽ |
Фрукты |
Егоров |
6 544 ₽ |
Напитки |
Грачев |
3 677 ₽ |
Фрукты |
Егоров |
3 186 ₽ |
Формула |
Описание |
Результат |
'=БСЧЁТА(A6:C12;;А1:С2) |
Подсчитывает количество строк (2) в ячейках A6:C12, которые удовлетворяют условиям в строке 2 (="Фрукты" и >2 000). |
=БСЧЁТА(A6:C12;;А1:С2) |
Несколько условий в нескольких столбцах, где любые условия могут быть истинными
Логическое выражение: (Тип = "Фрукты" ИЛИ Продавец = "Егоров")
Чтобы найти строки, удовлетворяющие нескольким условиям в нескольких столбцах, где любое условие может быть истинным, введите эти условия в разных строках диапазона условий.
В диапазоне данных (A6:C10) диапазон условий (A1:B3) отображает все строки, содержащие значение "Фрукты" в столбце "Тип" или значение "Егоров"
Категория |
Продавец |
|
="=Фрукты" |
||
="=Егоров" |
||
Категория |
Продавец |
Продажи |
Напитки |
Шашков |
5 122 ₽ |
Мясо |
Егоров |
675 ₽ |
фрукты |
Грачев |
937 ₽ |
Фрукты |
Грачев |
|
Формула |
Описание |
Результат |
'=БСЧЁТА(A6:C10;"Продажи";А1:B3) |
Подсчитывает количество строк (2) в ячейках A6:C10, которые удовлетворяют любому из условий в ячейках A1:C3, где поле "Продажи" не пустое. |
=БСЧЁТА(A6:C10;"Продажи";А1:B3) |
Несколько наборов условий, где в каждом наборе имеются условия для нескольких столбцов
Логическое выражение: ( (Продавец = "Егоров" И Продажи >3000) ИЛИ (Продавец = "Грачев" И Продажи > 1500) )
Для поиска строк, отвечающих нескольким наборам условий, каждый из которых содержит условия для нескольких столбцов, введите каждый набор условий в отдельной строке.
В диапазоне данных (A6:C10) диапазон условий (B1:C3) используется для подсчета строк, содержащих фамилию "Егоров" в столбце "Продавец" и значение больше 3 000 ₽ в столбце "Продажи", или строк, содержащих фамилию "Грачев" в столбце "Продавец" и значение больше 1 500 ₽ в столбце "Продажи".
Категория |
Продавец |
Продажи |
="=Егоров" |
>3 000 |
|
="=Грачев" |
>1 500 |
|
Категория |
Продавец |
Продажи |
Напитки |
Шашков |
5 122 ₽ |
Мясо |
Егоров |
450 ₽ |
фрукты |
Грачев |
6 328 ₽ |
Фрукты |
Егоров |
6 544 ₽ |
Формула |
Описание |
Результат |
'=БСЧЁТА(A6:C10;;В1:С3) |
Подсчитывает количество строк (2) в ячейках A6:C10, которые удовлетворяют всем условиям в B1:C3. |
=БСЧЁТА(A6:C10;;В1:С3) |
Несколько наборов условий, где в каждом наборе имеются условия для одного столбца
Логическое выражение: ( (Продажи > 6000 И Продажи < 6500 ) ИЛИ (Продажи < 500) )
Чтобы найти строки, удовлетворяющие нескольким наборам условий, каждый из которых содержит условия для одного столбца, используйте несколько столбцов с одинаковым заголовком.
В диапазоне данных (A6:C10) диапазон условий (C1:D3) используется для подсчета строк, содержащих значения от 6 000 ₽ до 6 500 ₽ и значения меньше 500 ₽ в столбце "Продажи".
Категория |
Продавец |
Продажи |
Продажи |
>6 000 |
<6 500 |
||
<500 |
|||
Категория |
Продавец |
Продажи |
|
Напитки |
Шашков |
5 122 ₽ |
|
Мясо |
Егоров |
450 ₽ |
|
фрукты |
Грачев |
6 328 ₽ |
|
Фрукты |
Егоров |
6 544 ₽ |
|
Формула |
Описание |
Результат |
|
'=БСЧЁТА(A6:C10;;C1:D3) |
Подсчитывает количество строк (2), которые удовлетворяют условиям в строке 2 (>6 000 и <6 500) или удовлетворяют условию в строке 3 (<500). |
=БСЧЁТА(A6:C10;;C1:D3) |
Условия отбора текстовых строк, содержащих определенные знаки
Чтобы найти текстовые значения с совпадающими знаками в некоторых из позиций, выполните одно или несколько действий, описанных ниже.
-
Для поиска строк, в которых текстовое значение в столбце начинается с определенной последовательности знаков, введите эти знаки, не используя знак равенства. Например, если ввести условие Бел, будут отобраны строки с ячейками, содержащими слова "Белов", "Беляков" и "Белугин".
-
Воспользуйтесь подстановочными знаками.
В условии сравнения можно использовать подстановочные знаки, указанные ниже.
Используйте |
Чтобы найти |
? (вопросительный знак) |
Любой символ (один) Пример: условию "стро?а" соответствуют результаты "строфа" и "строка" |
* (звездочка) |
Любое количество символов Пример: условию "*-восток" соответствуют результаты "северо-восток" и "юго-восток" |
~ (тильда), за которой следует ?, * или ~ |
Вопросительный знак, звездочку или тильду Пример: условию "ан91~?" соответствует результат "ан91?" |
В диапазоне данных (A6:C10) диапазон условий (A1:B3) используется для подсчета строк, которые в столбце "Тип" содержат текст, начинающийся на "Мя", или в столбце "Продавец" содержат текст, вторая буква которого — "р".
Категория |
Продавец |
Продажи |
Мя |
||
?р* |
||
Категория |
Продавец |
Продажи |
Напитки |
Шашков |
5 122 ₽ |
Мясо |
Егоров |
450 ₽ |
фрукты |
Грачев |
6 328 ₽ |
Фрукты |
Егоров |
6 544 ₽ |
Формула |
Описание |
Результат |
'=БСЧЁТА(A6:C10;;А1:В3) |
Подсчитывает количество строк (3), которые удовлетворяют любому из условий в ячейках A1:B3. |
=БСЧЁТА(A6:C10;;А1:В3) |
Условия, полученные в результате применения формулы
В качестве условия можно использовать значение, вычисленное при помощи формула. Обратите внимание на важные моменты, указанные ниже.
-
Формула должна возвращать результат ИСТИНА или ЛОЖЬ.
-
Поскольку используется формула, введенное строковое выражение должно иметь обычный вид, а не тот, который показан ниже:
=''= ввод ''
-
Не используйте заголовок столбца в качестве заголовка условия. Либо оставьте заголовок условия пустым, либо используйте заголовок, не являющийся заголовком одного из столбцов диапазона (в примерах ниже "Среднее арифметическое" и "Точное совпадение").
Если в формуле используется название столбца, а не относительная ссылка на ячейку или имя диапазона, в ячейке с условием будет выведено значение ошибки #ИМЯ? или #ЗНАЧ!. Эту ошибку можно не исправлять, так как она не повлияет на результаты фильтрации.
-
В формуле, используемой для создания условия отбора, для ссылки на соответствующую ячейку в первой строке необходимо использовать относительная ссылка.
-
Все остальные ссылки в формуле должны быть абсолютными.
Фильтрация значений, превышающих среднее значение диапазона данных
В диапазоне данных (A6:C10) диапазон условий (C1:C2) используется для подсчета строк со значениями в столбце "Продажи", превышающими среднее арифметическое всех значений этого столбца (C7:C10). Среднее арифметическое значений вычисляется в ячейке C4, а результат вычисления комбинируется с формулой =">"&C4 в ячейке C2 для создания используемого условия.
Продажи |
||
=СЦЕПИТЬ(">";C4) |
||
Среднее арифметическое |
||
=СРЗНАЧ(С7:C10) |
||
Категория |
Продавец |
Продажи |
Напитки |
Шашков |
5 122 ₽ |
Мясо |
Егоров |
450 ₽ |
фрукты |
Грачев |
6 328 ₽ |
Фрукты |
Егоров |
6 544 ₽ |
Формула |
Описание |
Результат |
'=БСЧЁТА(A6:C10;;С1:С2) |
Подсчитывает количество строк (3), которые удовлетворяют условию (>4611) в ячейках C1:C2. Условие в ячейке C2 создано путем сцепления =">" с ячейкой C4, рассчитанное среднее ячеек C7:C10. |
=БСЧЁТА(A6:C10;;С1:С2) |