Щоб обчислити кількість клітинок, які відповідають певній умові, скористайтеся однією зі статистичних функцій COUNTIF. Наприклад, за її допомогою можна порахувати, скільки разів певне місто з'являється в списку клієнтів.
У найпростішому випадку COUNTIF працює за таким принципом:
- =COUNTIF(Де шукати?; Що шукати?)
Наприклад:
- =COUNTIF(A2:A5;"Київ")
- =COUNTIF(A2:A5;A4)
Синтаксис
COUNTIF(діапазон;умова)
| Ім’я аргументу | Опис |
|---|---|
| діапазон (обов’язковий аргумент) | Група клітинок, які потрібно підрахувати. Діапазон може містити числа, масиви, іменований діапазон або посилання, які містять числа. Програма Excel ігнорує пусті та текстові значення. Відомості про виділення діапазонів на аркуші |
| умова (обов’язковий аргумент) | Число, вираз, посилання на клітинку або текстовий рядок, що визначають, які клітинки слід враховувати за допомогою функції 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(фрукти;">85"). Іменований діапазон може бути розташовано на поточному аркуші, на іншому аркуші тієї самої книги або в іншій книзі. Щоб створити посилання на іменований діапазон в іншій книзі, цю книгу також потрібно відкрити. |
Примітка.
Функція COUNTIF не може підрахувати клітинки за кольором фону або шрифту. Однак можна скористатися функцією User-Defined (UDF), написаною на Visual Basic for Applications (VBA), щоб підрахувати або виконати інші операції з клітинками на основі кольору фону або шрифту.
Потрібна додаткова довідка?
Ви завжди можете поставити запитання експерту в спільноті Tech у Excel або отримати підтримку в спільнотах.