В этой статье описаны синтаксис формулы и использование функции БСЧЁТА в 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)

К началу страницы

Нужна дополнительная помощь?

Нужны дополнительные параметры?

Изучите преимущества подписки, просмотрите учебные курсы, узнайте, как защитить свое устройство и т. д.

В сообществах можно задавать вопросы и отвечать на них, отправлять отзывы и консультироваться с экспертами разных профилей.