За допомогою функції COUNTIF, яка належить до статистичних функцій, можна порахувати кількість клітинок, які відповідають певній умові (наприклад, скільки разів якесь місто з’являється в списку клієнтів).
У найпростішому випадку COUNTIF працює за таким принципом:
- =COUNTIF(Де шукати?; Що шукати?)
Наприклад:
- =COUNTIF(A2:A5;"Київ")
- =COUNTIF(A2:A5;A4)
Синтаксис
COUNTIF(діапазон;умова)
| Ім’я аргументу | Опис |
|---|---|
| діапазон (обов’язковий аргумент) | Група клітинок, які потрібно підрахувати.
Діапазон може містити числа, масиви, іменований діапазон або посилання, які містять числа. Пусті та текстові значення ігноруються. Докладні відомості про вибір діапазонів на аркуші. |
| умова (обов’язковий аргумент) | Число, вираз, посилання на клітинку або текстовий рядок, що визначає, які клітинки потрібно підрахувати. Наприклад, можна використати число, як-от 32, порівняння, як-от ">32", клітинку, як-от B4, або слово, як-от "яблука". У функції COUNTIF використовується лише одна умова. Використовуйте функцію COUNTIFS, якщо потрібно застосувати кілька умов. |
Приклади використання функції COUNTIF у програмі Excel
Щоб скористатися цими прикладами в програмі Excel, скопіюйте дані в таблиці нижче та вставте їх у клітинку A1 нового аркуша.
| Дані. | Дані. |
|---|---|
| яблука | 32 |
| апельсини | 54 |
| кавуни | 75 |
| яблука | 86 |
| Формула | Опис |
| =COUNTIF(A2:A5;"яблука") | Рахує кількість клітинок із текстом "яблука" в клітинках від A2 до A5. У результаті отримаємо 2. |
| =COUNTIF(A2:A5;A4) | Рахує кількість клітинок із текстом "кавуни" (значення в клітинці A4) у клітинках від A2 до A5. У результаті отримаємо 1. |
| =COUNTIF(A2:A5;A2)+COUNTIF(A2:A5;A3) | Рахує кількість клітинок із текстом "яблука" (значення в клітинці A2) і "апельсини" (значення в клітинці A3) у клітинках від A2 до A5. У результаті отримаємо 3. Функцію COUNTIF використано у формулі двічі, щоб визначити кілька умов – по одній умові на вираз. Також можна скористатися функцією COUNTIFS. |
| =COUNTIF(B2:B5;">55") | Рахує кількість клітинок зі значенням, більшим за 55, у клітинках B2:B5. У результаті отримаємо 2. |
| =COUNTIF(B2:B5;"<>"&B4) | Рахує кількість клітинок зі значенням, яке не дорівнює 75, у клітинках B2:B5. Амперсанд (&) об'єднує оператор порівняння "не дорівнює" (<>) і значення в клітинці B4 для прочитання функції =COUNTIF(B2:B5;"<>75"). У результаті отримаємо 3. |
| =COUNTIF(B2:B5;">=32")-COUNTIF(B2:B5;"<=85") | Рахує кількість клітинок зі значеннями, більшими (>) або рівними (=) 32 та меншими () або< рівними (=) 85, у клітинках B2:B5. У результаті отримаємо 1. |
| =COUNTIF(A2:A5;"*") | Рахує кількість клітинок, що містять будь-який текст, у клітинках від A2 до A5. Зірочка (*) використовується як символ узагальнення для будь-яких символів. У результаті отримаємо 4. |
| =COUNTIF(A2:A5;"????ни") | Рахує кількість клітинок, які мають точно 6 символів і закінчуються буквами "ни", у клітинках від A2 до A5. Знак питання (?) використовується як символ узагальнення для заміни будь-яких окремих символів. У результаті отримаємо 1. |
Виправлення поширених помилок COUNTIF в Excel
| Проблема | Помилка |
|---|---|
| Для довгих рядків повернуто помилкове значення. | Функція COUNTIF повертає неправильні результати, якщо зіставляються рядки довжиною більше 255 символів Щоб зіставити рядки, довші за 255 символів, використовуйте функцію CONCATENATE або оператор об’єднання &. Наприклад, =COUNTIF(A2:A5;"довгий рядок"&"інший довгий рядок"). |
| Не повернуто жодного значення, коли очікувалося значення. | Переконайтеся, що аргумент умови взято в лапки. |
| Формула COUNTIF отримує помилку #VALUE! у формулах із посиланням на інший аркуш. | Це стається, коли формула, яка містить функцію, посилається на клітинки або діапазон клітинок у закритій книзі та обчислює кількість цих клітинок. Щоб ця функція працювала, потрібно відкрити іншу книгу. |
Практичні поради з використання функції COUNTIF у програмі Excel
| Зробіть це | Результат |
|---|---|
| Пам’ятайте, що функція COUNTIF ігнорує верхній і нижній регістри в текстових рядках. | Умови не чутливі до регістра. Іншими словами, рядкам "яблука" та "ЯБЛУКА" відповідатимуть одні й ті ж клітинки. |
| Використовуйте символи узагальнення. | В умові можна використовувати символи узагальнення – знак питання (?) і зірочку (*). Знак питання відповідає будь-якому одному символу. Зірочка відповідає будь-якій послідовності символів. Якщо потрібно знайти власне знак питання або зірочку, перед відповідним символом введіть тильду (~). Наприклад, =COUNTIF(A2:A5,"яблук?") порахує всі екземпляри слова "яблук" зі ще одною будь-якою буквою в кінці. |
| Переконайтеся, що ваші дані не містять помилкові символи. | Під час підрахунку текстових значень переконайтеся, що дані не містять пробілів на початку чи в кінці, а також неузгоджених прямих чи фігурних лапок або недрукованих символів. У таких випадках функція COUNTIF може повернути неочікуване значення. Спробуйте використати функцію CLEAN або TRIM. |
| Для зручності використовуйте іменовані діапазони | Функція COUNTIF підтримує іменовані діапазони у формулах (наприклад, =COUNTIF(fruit;">=32")-COUNTIF(fruit;">85"). Іменований діапазон може бути розташовано на поточному аркуші, на іншому аркуші тієї самої книги або в іншій книзі. Щоб створити посилання на іншу книгу, її також потрібно відкрити. |
Примітка.
Під час підрахунку функція COUNTIF не враховує колір фону або шрифту клітинок. Проте Excel підтримує користувацькі функції (UDF) на основі операцій із клітинками Microsoft Visual Basic for Applications (VBA), які враховують колір фону або шрифту. Нижче наведено приклад того, як можна підрахувати кількість клітинок із певним кольором за допомогою VBA.
Потрібна додаткова довідка?
Ви завжди можете поставити запитання експерту в спільноті Tech у Excel або отримати підтримку в спільнотах.