В этой статье описаны синтаксис формулы и использование функции DCOUNTA в Microsoft Excel.
Описание
Подсчитывает непустые ячейки в поле (столбце) записей списка или базы данных, которые удовлетворяют заданным условиям.
Аргумент "поле" не является обязательным. Если аргумент "поле" опущен, функция БСЧЁТА подсчитывает количество соответствующих условиям записей в базе данных.
Синтаксис
БСЧЁТА(база_данных; поле; условия)
Аргументы функции БСЧЁТА указаны ниже.
- Базы данных Обязательно. Диапазон ячеек, образующих список или базу данных. База данных представляет собой список связанных данных, в котором строки данных являются записями, а столбцы — полями. Первая строка списка содержит заголовки всех столбцов.
- Поле Дополнительные. Столбец, используемый функцией. Введите текст с заголовком столбца в двойных кавычках, например "Возраст" или "Урожай", или число (без кавычек), задающее положение столбца в списке: 1 — для первого столбца, 2 — для второго и т. д.
- Критерии Обязательно. Диапазон ячеек, который содержит задаваемые условия. В качестве аргумента "условия" можно использовать любой диапазон, который содержит хотя бы один заголовок столбца и хотя бы одну ячейку с условием, расположенную под заголовком столбца.
Замечания
- В качестве аргумента "условия" можно использовать любой диапазон, который содержит хотя бы один заголовок столбца и хотя бы одну ячейку с условием, расположенную под заголовком столбца.
Например, если диапазон G1:G2 содержит заголовок столбца "Доход" в ячейке G1 и значение 10 000 ₽ в ячейке G2, можно определить диапазон "СоответствуетДоходу" и использовать это имя как аргумент "условия" в функции баз данных. - Несмотря на то, что диапазон условий может находиться в любом месте листа, не следует помещать его под списком. Это связано с тем, что данные, добавляемые в список, вставляются в первую строку после списка. Если эта строка уже содержит данные, Excel не сможет добавить новые данные в список.
- Диапазон условий не должен перекрываться со списком.
- Чтобы выполнить операцию над целым столбцом базы данных, вставьте пустую строку под строкой заголовков столбцов в диапазоне условий.
Примеры
Скопируйте образец данных из следующей таблицы и вставьте его в ячейку A1 нового листа Excel. Чтобы отобразить результаты формул, выделите их и нажмите клавишу F2, а затем — клавишу ВВОД. При необходимости измените ширину столбцов, чтобы видеть все данные. При копировании в Excel любого из приведенных ниже примеров выделяйте все ячейки таблицы, включая самую верхнюю левую.
| Дерево | Высота | Возраст | Урожай | Доход | Высота |
|---|---|---|---|---|---|
| ="=Яблоня" | >10 | <16 | |||
| ="=Груша" | |||||
| Дерево | Высота | Возраст | Урожай | Доход | |
| Яблоня | 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), содержащие "Apple" в столбце A с высотой >10 и <16. Только строка 8 удовлетворяет трем этим условиям. | 1 |
Примеры условий
- При вводе в ячейку значения =текст Excel интерпретирует его как формулу и пытается выполнить вычисления. Чтобы значение =текст не вычислялось, используйте такой синтаксис:
=''=entry'' '
Где запись — это текст или значение, которое вы хотите найти. Например:
| Вводится в ячейку | Вычисляется и отображается |
|---|---|
| ="=Егоров" | =Егоров |
| ="=3000" | =3000 |
- При фильтрации текстовых данных в Excel не учитывается регистр букв. Однако для поиска с учетом регистра можно воспользоваться формулой.
Примеры сложных условий отбора приведены в следующих разделах.
Несколько условий в одном столбце
Логическая логика: (Salesperson = "Davolio" OR Salesperson = "Buchanan")
Чтобы найти строки, отвечающие нескольким условиям для одного столбца, введите условия непосредственно одно под другим в разных строках диапазона условий.
В диапазоне данных (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) |
Несколько условий в нескольких столбцах, где все условия должны быть истинными
Логическая логика: (Type = "Produce" AND Sales > 2000)
Чтобы найти строки, удовлетворяющие нескольким условиям в нескольких столбцах, введите все условия в одной строке диапазона условий.
В диапазоне данных (A6:C12) диапазон условий (A1:C2) используется для подсчета строк, содержащих слово "Фрукты" в столбце "Категория" и значения больше 2 000 ₽ в столбце "Продажи".
| Категория | Продавец | Продажи |
|---|---|---|
| ="=Фрукты" | >2000 | |
| Категория | Продавец | Продажи |
| Напитки | Шашков | 5 122 ₽ |
| Мясо | Егоров | 450 ₽ |
| Фрукты | Грачев | 935 ₽ |
| Фрукты | Егоров | 6 544 ₽ |
| Напитки | Грачев | 3 677 ₽ |
| Фрукты | Егоров | 3 186 ₽ |
| Формула | Описание | Результат |
| '=БСЧЁТА(A6:C12;;А1:С2) | Подсчитывает количество строк (2) в A6:C12, соответствующих условиям в строке 2 (="Производить" и >2000). | =БСЧЁТА(A6:C12;;А1:С2) |
Несколько условий в нескольких столбцах, где любые условия могут быть истинными
Логическая логика: (Type = "Produce" OR Salesperson = "Davolio")
Чтобы найти строки, удовлетворяющие нескольким условиям в нескольких столбцах, где любое условие может быть истинным, введите эти условия в разных строках диапазона условий.
В диапазоне данных (A6:C10) диапазон условий (A1:B3) отображает все строки, содержащие значение "Фрукты" в столбце "Тип" или значение "Егоров"
| Категория | Продавец | |
|---|---|---|
| ="=Фрукты" | ||
| ="=Егоров" | ||
| Категория | Продавец | Продажи |
| Напитки | Шашков | 5 122 ₽ |
| Мясо | Егоров | 675 ₽ |
| фрукты | Грачев | 937 ₽ |
| Фрукты | Грачев | |
| Формула | Описание | Результат |
| '=БСЧЁТА(A6:C10;"Продажи";А1:B3) | Подсчитывает количество строк (2) в ячейках A6:C10, которые удовлетворяют любому из условий в ячейках A1:C3, где поле "Продажи" не пустое. | =БСЧЁТА(A6:C10;"Продажи";А1:B3) |
Несколько наборов условий, где в каждом наборе имеются условия для нескольких столбцов
Логическая логика: ( (Salesperson = "Davolio" AND Sales >3000) OR (Salesperson = "Buchanan" AND Sales > 1500) )
Для поиска строк, отвечающих нескольким наборам условий, каждый из которых содержит условия для нескольких столбцов, введите каждый набор условий в отдельной строке.
В диапазоне данных (A6:C10) диапазон условий (B1:C3) используется для подсчета строк, содержащих фамилию "Егоров" в столбце "Продавец" и значение больше 3 000 ₽ в столбце "Продажи", или строк, содержащих фамилию "Грачев" в столбце "Продавец" и значение больше 1 500 ₽ в столбце "Продажи".
| Категория | Продавец | Продажи |
|---|---|---|
| ="=Егоров" | >3000 | |
| ="=Грачев" | >1500 | |
| Категория | Продавец | Продажи |
| Напитки | Шашков | 5 122 ₽ |
| Мясо | Егоров | 450 ₽ |
| фрукты | Грачев | 6 328 ₽ |
| Фрукты | Егоров | 6 544 ₽ |
| Формула | Описание | Результат |
| '=БСЧЁТА(A6:C10;;В1:С3) | Подсчитывает количество строк (2) в ячейках A6:C10, которые удовлетворяют всем условиям в B1:C3. | =БСЧЁТА(A6:C10;;В1:С3) |
Несколько наборов условий, где в каждом наборе имеются условия для одного столбца
Логическая логика: ( (Продажи > 6000 И Продажи < 6500 ) OR (Продажи < 500) )
Чтобы найти строки, удовлетворяющие нескольким наборам условий, каждый из которых содержит условия для одного столбца, используйте несколько столбцов с одинаковым заголовком.
В диапазоне данных (A6:C10) диапазон условий (C1:D3) используется для подсчета строк, содержащих значения от 6 000 ₽ до 6 500 ₽ и значения меньше 500 ₽ в столбце "Продажи".
| Категория | Продавец | Продажи | Продажи |
|---|---|---|---|
| >6000 | <6500 | ||
| <500 | |||
| Категория | Продавец | Продажи | |
| Напитки | Шашков | 5 122 ₽ | |
| Мясо | Егоров | 450 ₽ | |
| фрукты | Грачев | 6 328 ₽ | |
| Фрукты | Егоров | 6 544 ₽ | |
| Формула | Описание | Результат | |
| '=БСЧЁТА(A6:C10;;C1:D3) | Подсчитывает количество строк (2), которые соответствуют условиям в строке 2 (>6000 и <6500) или соответствуют условию в строке 3 (<500). | =БСЧЁТА(A6:C10;;C1:D3) |
Условия отбора текстовых строк, содержащих определенные знаки
Чтобы найти текстовые значения с совпадающими знаками в некоторых из позиций, выполните одно или несколько действий, описанных ниже.
- Для поиска строк, в которых текстовое значение в столбце начинается с определенной последовательности знаков, введите эти знаки, не используя знак равенства. Например, если ввести условие Бел, будут отобраны строки с ячейками, содержащими слова "Белов", "Беляков" и "Белугин".
- Воспользуйтесь подстановочными знаками.
В условии сравнения можно использовать подстановочные знаки, указанные ниже.
| Используйте | Чтобы найти |
|---|---|
| ? (вопросительный знак) | Любой символ Пример: условию "стро?а" соответствуют результаты "строфа" и "строка" |
| Звездочка (*) | Любое количество символов Пример: условию "*-восток" соответствуют результаты "северо-восток" и "юго-восток" |
| ~ (тильда), за которой следует ?, * или ~ | Вопросительный знак, звездочка или тильда Например, fy91~? соответствует результат "ан91?" |
В диапазоне данных (A6:C10) диапазон условий (A1:B3) используется для подсчета строк, которые в столбце "Тип" содержат текст, начинающийся на "Мя", или в столбце "Продавец" содержат текст, вторая буква которого — "р".
| Категория | Продавец | Продажи |
|---|---|---|
| Мя | ||
| ?р* | ||
| Категория | Продавец | Продажи |
| Напитки | Шашков | 5 122 ₽ |
| Мясо | Егоров | 450 ₽ |
| фрукты | Грачев | 6 328 ₽ |
| Фрукты | Егоров | 6 544 ₽ |
| Формула | Описание | Результат |
| '=БСЧЁТА(A6:C10;;А1:В3) | Подсчитывает количество строк (3), которые удовлетворяют любому из условий в ячейках A1:B3. | =БСЧЁТА(A6:C10;;А1:В3) |
Условия, полученные в результате применения формулы
В качестве условия можно использовать значение, вычисленное с помощью формулы. Обратите внимание на важные моменты, указанные ниже.
- Формула должна возвращать результат ИСТИНА или ЛОЖЬ.
- Поскольку используется формула, введенное строковое выражение должно иметь обычный вид, а не тот, который показан ниже:
=''=entry'' ' - Не используйте заголовок столбца в качестве заголовка условия. Либо оставьте заголовок условия пустым, либо используйте заголовок, не являющийся заголовком одного из столбцов диапазона (в примерах ниже "Среднее арифметическое" и "Точное совпадение").
Если в формуле используется метка столбца вместо ссылки на относительную ячейку или имя диапазона, Excel отображает значение ошибки, например #NAME? или #VALUE!, в ячейке, содержащей условие. Эту ошибку можно не исправлять, так как она не повлияет на результаты фильтрации. - Формула, используемая для условий, должна использовать относительную ссылку для ссылки на соответствующую ячейку в первой строке.
- Все остальные ссылки в формуле должны быть абсолютными.
Фильтрация значений, превышающих среднее значение диапазона данных
В диапазоне данных (A6:C10) диапазон условий (C1:C2) используется для подсчета строк со значениями в столбце "Продажи", превышающими среднее арифметическое всех значений этого столбца (C7:C10). Среднее значение вычисляется в ячейке C4, а результат объединяется в ячейке C2 с формулой =">"&C4 для создания используемых условий.
| Продажи | ||
|---|---|---|
| =CONCATENATE(">",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) |