ФункціяБСЧЁТ (), англійський варіант DCOUNT (), підраховує рядки в таблиці даних, які задовольняють заданим умовам.
Використання функції БСЧЁТ () є тільки "просунутим" користувачам, тому при зустрічі з нею у непідготовлених користувачів часто виникають негативні емоції. Однак, є хороші новини - без функції БСЧЁТ () можна взагалі обійтися, замінивши її функціями СУММПРОИЗВ (). СЧЁТЕСЛІМН () або формулами масиву.
Підготовка вихідної таблиці
Для використання цієї функції потрібна щоб:
- вихідна таблиця мала заголовки стовпців;
- критерії були оформлені вигляді невеликої окремої таблиці з заголовками;
- заголовки таблиці критеріїв збігалися з заголовками вихідної таблиці (якщо критерій не задається формулою).
Синтаксис функції БСЧЁТ ()
БСЧЁТ (база_данних; поле; критерій)
Аргумент База_данних є таблицею (див. Рядки 8-13 на малюнку вище), по одному з стовпців якої проводиться підрахунок значень. Верхній рядок таблиці повинна містити заголовки стовпців.
Поле - це назва заголовка стовпця, по якому проводиться підрахунок. Аргумент Поле можна заповнити ввівши:
- текст із заголовком стовпця в подвійних лапках, наприклад "Продавець" або "Продажі",
- число (без лапок), що задає положення стовпчика в таблиці (зазначеної в аргументі база_данних): 1 - для першого стовпця, 2 - для другого і т.д.
- посилання на заголовок стовпчика.
Мається на увазі, що цей стовпець містить числа, інакше функція поверне 0.
Примітка. Якщо значення в цьому стовпці містять числа, збережені в текстовому форматі. то потрібно використовувати функцію БСЧЁТА ().
Примітка. В теорії баз даних рядка таблиці називаються записами, а стовпці - полями (звідси й незвичайна назва аргументу).
Припустимо, що в діапазоні A8: B13 є таблиця продажів, що містить поля (стовпці) Продавець і Продажі (див. Малюнок вище і файл прикладу).
Задача1 (з одним критерієм)
Підрахуємо кількість продажів на суму> 3000. Тобто підрахунок вестимемо по стовпцю Продажі.
Альтернативне рішення - проста формула = СЧЁТЕСЛІ (B9: B13; A3).
Висновок. для простих однокритеріальних завдань город з функцією = БСЧЁТ () городити не варто.
Задача2 (Два критерію (різні стовпці), умова І)
Підрахуємо Число продажів Бєлова, які менше 3000.
В цьому випадку, критерії відбору повинні розміщуватися під заголовками, назви яких збігаються з заголовками стовпців початкової таблиці, за якими буде проводитися підрахунок (стовпці Продавець і Продажі). Критерії повинні розташовуватися в одному рядку (див. Діапазон на малюнку вище A2: B3).
Записати саму формулу можна так = БСЧЁТ (A8: B13; B8; A2: B3)
Альтернативне рішення - проста формула = СЧЁТЕСЛІМН (A9: A13; "Белов"; B9: B13; A3).
Задача3 (Два критерію (один стовпець), умова І)
Підрахуємо число продажів в інтервалі> 3000 і <6000.
В цьому випадку, критерії відбору повинні розміщуватися під заголовками, назви яких збігаються з заголовками стовпців початкової таблиці, за якими буде проводитися пошук (стовпець Продажі). Критерії повинні розташовуватися в одному рядку.
Записати саму формулу можна так = БСЧЁТ (B8: B13; B8; D2: E3)
Альтернативне рішення - проста формула = СЧЁТЕСЛІМН (B9: B13; "> 3000"; B9: B13; "<6000")
Задача4 (Два критерію (один стовпець), умова АБО)
Підрахуємо число продажів <3000 или>6000.
В цьому випадку, критерії відбору повинні розміщуватися під одним заголовком (стовпець Продажі). Критерії повинні розташовуватися в різних рядках.
Записати саму формулу можна так = БСЧЁТ (B8: B13; B8; A2: A4)
Альтернативне рішення - формула = СЧЁТЕСЛІ (B9: B13; "<3000")+СЧЁТЕСЛИ(B9:B13;">6000 ")
Задача5 (Два критерію (різні стовпці), умова АБО)
Підрахуємо продажу Бєлова та продажу, будь-якого продавця, які> 6000 (також виключимо можливе дублювання: продажу Бєлова, які> 6000).
В цьому випадку, критерії відбору повинні розміщуватися під заголовками, назви яких збігаються з заголовками стовпців початкової таблиці, за якими буде проводитися підрахунок (стовпці Продавець і Продажі). Критерії повинні розташовуватися в різних рядках.
Записати саму формулу можна так = БСЧЁТ (A8: B13; B8; H2: I4)
Альтернативне рішення - формула = СЧЁТЕСЛІ (A9: A13; "Белов") + СЧЁТЕСЛІ (B9: B13; "> 6000") - СЧЁТЕСЛІМН (B9: B13; "> 6000"; A9: A13; "Белов")
Завдання 6 (Умови відбору, створені в результаті застосування формули)
Підрахуємо кількість продажів з величиною вище середнього.
Як умова відбору можна використовувати значення, що обчислюється за допомогою формули. Формула повинна повертати результат ІСТИНА або БРЕХНЯ.
Для цього введемо в осередок F3 формулу = B9> СРЗНАЧ ($ B $ 9: $ B $ 13). а в F2 замість заголовка введемо, довільний пояснювальний текст, наприклад, «Більше середнього» (заголовок не повинен повторювати заголовки вихідної таблиці).
Записати формулу можна так = БСЧЁТ (B8: B13; B8; F2: F3)
Альтернативне рішення - формула = СЧЁТЕСЛІ (B9: B13; ">" СРЗНАЧ ($ B $ 9: $ B $ 13))