У цій статті наведено синтаксис формули та описано, як у програмі Microsoft Excel використовувати функцію DCOUNTA .
Опис
Обчислює кількість непустих клітинок у полі (стовпці) записів у списку або базі даних, які відповідають указаним умовам.
Аргумент «Поле» необов’язковий. Якщо значення аргументу «Поле» не вказано, функція DCOUNTA обробляє всі записи бази даних, які відповідають указаним умовам.
Синтаксис
DCOUNTA(база_даних;поле;умови)
Функція DCOUNTA має такі аргументи:
- Бази даних Необхідні. База даних – це список пов’язаних даних, у якому рядки пов’язаних відомостей є записами, а стовпці даних – полями. Перший рядок списку містить підписи для всіх стовпців. Перший рядок списку містить підписи для всіх стовпців.
- Поле Необов'язково. Визначає, який стовпець використовуватиметься у функції. Введіть підпис стовпця в подвійних лапках, наприклад "Вік" або "Урожай". Можна також ввести число (без лапок), яке відповідає номеру стовпця у списку: 1 – для першого стовпця, 2 – для другого тощо.
- Критерії Необхідні. Можна вказувати будь-який діапазон для аргументу «Умови», якщо цей аргумент містить принаймні один підпис стовпця та принаймні одну клітинку під цим підписом, у якій указано умови для відповідного стовпця. (Обов'язковий аргумент.)
Примітки
- Можна вказувати будь-який діапазон для аргументу «Умови», якщо він містить принаймні один підпис стовпця та принаймні одну клітинку під цим підписом, у якій вказано умови для відповідного стовпця.
Наприклад, якщо діапазон G1:G2 містить підпис стовпця «Прибуток» у G1 і суму 10 000 грн. у G2, можна визначити діапазон як «ЗбігПрибутку» й використовувати це ім’я як значення аргументу «Умови» у функціях бази даних. - Хоча діапазон умов може бути розташовано в довільному місці аркуша, не розташовуйте діапазон умов під списком. У разі додавання нової інформації до списку її буде додано до першого рядка під списком. Якщо рядок під списком не пустий, програма Excel не зможе додати нову інформацію.
- Переконайтеся, що діапазон умов не перекриває список.
- Щоб виконати операцію над усім стовпцем бази даних, введіть пустий рядок під підписами стовпців у діапазоні умов.
Приклади
Скопіюйте дані прикладу з наведеної нижче таблиці та вставте їх у клітинку A1 нового аркуша Excel. Щоб відобразити результат обчислення формул, виберіть їх, натисніть клавішу F2, а потім – клавішу Enter. За потреби можна змінити ширину стовпців, щоб відобразити всі дані. Якщо до програми 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 | |
| Формула | Опис | Результат | |||
| =DCOUNTA(A4:E10, "Прибуток", A1:F2) | Рахує рядки (1), що містять "Яблуко" у стовпці A з висотою >10 і <16. Цим трьом умовам відповідає лише рядок 8. | 1 |
Приклади умов
- Коли в клітинку вводиться значення =текст, Excel інтерпретує його як формулу, і намагається її обчислити. Щоб ввести значення =текст так, щоб Excel не намагався обчислити його, дотримуйтесь синтаксису:
='=entry''
Де запис – це текст або значення, які потрібно знайти. Наприклад:
| Дані, що вводяться у клітинку | Програма Excel визначає та відображає |
|---|---|
| ="=Давидова" | =Давидова |
| ="=3 000" | =3 000 |
- Фільтруючи текстові дані, Excel не розрізняє великі та малі букви. Проте, пошук виразу з урахуванням регістру можна виконати за допомогою формули.
Нижче надано приклади складних умов.
Кілька умов в одному стовпці
Логічний вираз: (Торговий представник = "Давидова" АБО Торговий представник = "Пустовіт")
Щоб знайти рядки, які відповідають кільком умовам для одного стовпця, введіть умови безпосередньо одну під одною в окремі рядки діапазону умов.
У діапазоні даних (A6:C10) діапазон умов (B1:B3) використовується для підрахунку рядків, які містять значення ''Давидова'' або ''Пустовіт'' у стовпці ''Продавець''.
| Продавець | ||
|---|---|---|
| ="=Давидова" | ||
| ="=Пустовіт" | ||
| Категорія | Продавець | Продаж, грн. |
| Напої | Семенів | 5 122 |
| М’ясо | Давидова | 450 грн. |
| Овочі | Пустовіт | 6 328 |
| Овочі | Давидова | 6 544 |
| Формула | Опис | Результат |
| '=DCOUNTA(A6:C10,2,B1:B3) | Рахує кількість рядків (3) у діапазоні A6:C10, що відповідають будь-якій з умов «Продавець» у рядках 2 та 3. | =DCOUNTA(A6:C10,2,B1:B3) |
Кілька умов для кількох стовпців, для всіх умов обов’язкове значення «істина»
Логічний вираз: (Тип = "Овочі" І Продажі > 2000)
Щоб знайти рядки, які відповідають кільком умовам у кількох стовпцях, введіть усі умови в один рядок діапазону умов.
У діапазоні даних (A6:C12) діапазон умов (A1:C2) використовується для підрахунку рядків, які містять ''Овочі'' у стовпці ''Категорія'' і значення, більші за 2000 грн. у стовпці ''Продажі''.
| Категорія | Продавець | Продаж, грн. |
|---|---|---|
| ="=Продукти" | >2000 | |
| Категорія | Продавець | Продаж, грн. |
| Напої | Семенів | 5 122 |
| М’ясо | Давидова | 450 грн. |
| Продукти | Пустовіт | $935 |
| Продукти | Давидова | 6 544 |
| Напої | Пустовіт | 3 677 |
| Продукти | Давидова | 3 186 |
| Формула | Опис | Результат |
| '=DCOUNTA(A6:C12,,A1:C2) | Рахує кількість рядків (2) у діапазоні A6:C12, які відповідають умовам у рядку 2 (="Овочі" та >2000). | =DCOUNTA(A6:C12,,A1:C2) |
Кілька умов для кількох стовпців, будь-яка умова може мати значення «істина»
Логічний вираз: (Тип = "Овочі" АБО Продавець = "Давидова")
Щоб знайти рядки, які відповідають кільком умовам у кількох стовпцях, коли будь-яка умова може бути істиною, введіть умови в різних рядках діапазону умов.
У діапазоні даних (A6:C10) діапазон умов (A1:B3) відображає всі рядки, які містять слово «Овочі» у стовпці «Тип» або «Давидова»
| Категорія | Продавець | |
|---|---|---|
| ="=Продукти" | ||
| ="=Давидова" | ||
| Категорія | Продавець | Продаж, грн. |
| Напої | Семенів | 5 122 |
| М’ясо | Давидова | 675 грн. |
| продукти | Пустовіт | 937 грн. |
| Продукти | Пустовіт | |
| Формула | Опис | Результат |
| '=DCOUNTA(A6:C10,"Продаж, грн.",A1:B3) | Рахує кількість рядків (2) у діапазоні A6:C10, що відповідають будь-якій умові в діапазоні A1:C3, де поле «Продаж, грн.» не пусте. | =DCOUNTA(A6:C10,"Продаж, грн.",A1:B3) |
Кілька наборів умов, кожний набір містить умови для кількох стовпців
Логічний вираз: ( (Торговий представник = "Давидова" І Продаж >3000) АБО (Торговий представник = "Пустовіт" І Продажі > 1500) )
Щоб знайти рядки, які відповідають кільком наборам умов, коли кожний набір містить умови для кількох стовпців, введіть кожний набір умов в окремі рядки.
У наведеному нижче діапазоні даних (A6:C10) діапазон умов (B1:C3) використовується для підрахунку стовпців, які містять одночасно ''Давидова'' у стовпці ''Торговий представник'' і значення більше за 3 000 грн. у стовпці ''Продаж'', або рядки, які містять ''Пустовіт'' у стовпці ''Продавець'' і значення, більше за 1 500 грн. у стовпці ''Продаж''.
| Категорія | Продавець | Продаж, грн. |
|---|---|---|
| ="=Давидова" | >3000 | |
| ="=Пустовіт" | >1500 | |
| Категорія | Продавець | Продаж, грн. |
| Напої | Семенів | 5 122 |
| М’ясо | Давидова | 450 грн. |
| Овочі | Пустовіт | 6 328 |
| Овочі | Давидова | 6 544 |
| Формула | Опис | Результат |
| '=DCOUNTA(A6:C10,,B1:C3) | Рахує кількість рядків (2) у діапазоні A6:C10, які відповідають усім умовам у діапазоні B1:C3. | =DCOUNTA(A6:C10,,B1:C3) |
Кілька наборів умов, кожний набір містить умови для одного стовпця
Логічний вираз: ( (Продаж > , 6000 І Продаж < , 6500 ) АБО (Продаж < 500) )
Щоб знайти рядки, які відповідають кільком наборам умов, в яких кожний набір містить умови для одного стовпця, включіть кілька стовпців з одним заголовком.
У діапазоні даних (A6:C10) діапазон умов (C1:D3) використовується для підрахунку рядків, які містять значення між 6 000 грн. та 6 500 грн. і значення, менші за 500 грн. у стовпці ''Продажі''.
| Категорія | Продавець | Продаж, грн. | Продажі |
|---|---|---|---|
| >6000 | <6500 | ||
| <500 | |||
| Категорія | Продавець | Продаж, грн. | |
| Напої | Семенів | 5 122 | |
| М’ясо | Давидова | 450 грн. | |
| Овочі | Пустовіт | 6 328 | |
| Овочі | Давидова | 6 544 | |
| Формула | Опис | Результат | |
| '=DCOUNTA(A6:C10,,C1:D3) | Рахує кількість рядків (2), які відповідають умовам у рядку 2 (>6000 і <6500) або відповідають умові в рядку 3 (<500). | =DCOUNTA(A6:C10,,C1:D3) |
Умови для пошуку текстових значень із певними однаковими символами
Щоб знайти текстові значення, що містять кілька спільних символів (але не всі), виконайте одну або кілька таких дій:
- Введіть один або кілька символів без знака рівності (=), щоб знайти рядки з текстовим значенням у стовпці, який починається цими символами. Наприклад, якщо використати текст Дав як умову, програма Excel знайде "Давидова", "Давид" і "Давиденко".
- Використайте символи узагальнення.
Як умови порівняння можна застосовувати такі символи узагальнення:
| Використовуйте | Щоб знайти |
|---|---|
| ? (знак питання) | Будь-який символ Наприклад, sm?th знаходить слова "коваль" і "смит" |
| * (зірочка) | Будь-яка кількість символів Наприклад, *схід знаходить "Північний схід" і "Південний схід" |
| ~ (тильда) зі знаком ?, * або ~ в кінці | Знак питання, зірочка або тильда Наприклад, фр91~? Наприклад, за умовою "фр91~?" буде знайдено "фр91?". |
У діапазоні даних (A6:C10) діапазон умов (A1:B3) використовується для підрахунку рядків, які містять ''М’я'' як перші символи у стовпці ''Тип'', або рядки, другий символ яких відповідає ''е'' у стовпці ''Продавець''.
| Категорія | Продавець | Продаж, грн. |
|---|---|---|
| М’я | ||
| ?в* | ||
| Категорія | Продавець | Продаж, грн. |
| Напої | Семенів | 5 122 |
| М’ясо | Давидова | 450 грн. |
| Овочі | Пустовіт | 6 328 |
| Овочі | Давидова | 6 544 |
| Формула | Опис | Результат |
| '=DCOUNTA(A6:C10,,A1:B3) | Рахує кількість рядків (3), які відповідають будь-якій умові в діапазоні A1:B3. | =DCOUNTA(A6:C10,,A1:B3) |
Умови, утворені як результат обчислення формули
Обчислюване значення, отримане як результат формули, можна використовувати як умову. Слід пам’ятати про такі важливі моменти:
- Формула має повертати результат TRUE або FALSE.
- Оскільки використовується формула, потрібно вводити її звичайним способом. Не вводьте її як вираз, тобто:
='=entry'' - Не використовуйте заголовок стовпця як заголовок умови; залиште умову без заголовка або використайте заголовок, який не є заголовком стовпця в діапазоні (у нижченаведених прикладах, «Обчислене середнє значення» та «Точна відповідність»).
Якщо замість відносного посилання на клітинку або імені діапазону використати підпис стовпця, excel відобразить значення помилки, наприклад #NAME? або #VALUE!, у клітинці, яка містить умову. Ця помилка не критична, оскільки не впливає на фільтрування діапазону. - Формула, яка використовується для умов, має використовувати відносне посилання для посилання на відповідну клітинку в першому рядку.
- Решта посилань у формулі мають бути абсолютні.
Фільтрування для значень, більших за середнє всіх значень діапазону даних
У діапазоні даних (A6:C10) діапазон умов (C1:C2) відображає рядки, які у стовпці ''Продажі'' мають значення, більші за середнє всіх значень ''Продажі'' (C7:C10). Середнє значення обчислюється в клітинці C4, і результат об'єднується в клітинці C2 з формулою =">"&C4 , щоб створити умови, які використовуються.
| Продажі | ||
|---|---|---|
| =CONCATENATE(">;C4) | ||
| Обчислене середнє значення | ||
| =AVERAGE(C7:C10) | ||
| Категорія | Продавець | Продаж, грн. |
| Напої | Семенів | 5 122 |
| М’ясо | Давидова | 450 грн. |
| Овочі | Пустовіт | 6 328 |
| Овочі | Давидова | 6 544 |
| Формула | Опис | Результат |
| '=DCOUNTA(A6:C10,,C1:C2) | Рахує кількість рядків (3), які відповідають умові (>4611) у діапазоні C1:C2. Умова в клітинці C2 створюється об'єднанням =">" з клітинкою C4, яка є обчислюваним середнім значенням C7:C10. | =DCOUNTA(A6:C10,,C1:C2) |