Цей підручник демонструє, як користуватисяВимел COUNTIF та COUNTIFS Funcціонів в Excel для підрахунку даних, що відповідають певним критеріям.
Огляд функції COUNTIF
Ви можете використовувати функцію COUNTIF в Excel для підрахунку клітинок, які містять певне значення, підрахунку клітинок, які більші або дорівнюють значенню тощо.
(Зверніть увагу, як виглядають вхідні дані формул)
Синтаксис та аргументи функції COUNTIF:
= COUNTIF (діапазон, критерії)
діапазон - Діапазон клітинок для підрахунку.
критерії - Критерії, які контролюють, які клітини слід зараховувати.
Що таке функція COUNTIF?
Функція COUNTIF - одна з старих функцій, що використовуються в електронних таблицях. Простіше кажучи, він чудово сканує діапазон і розповідає, скільки клітин відповідає цій умові. Ми розглянемо, як функція працює з текстом, цифрами та датами; а також деякі інші ситуації, які можуть виникнути.
Основний приклад
Почнемо з перегляду цього списку випадкових предметів. У нас є деякі цифри, порожні клітинки та рядки тексту.
Якщо ви хочете дізнатися, скільки елементів точно відповідає критеріям, ви можете вказати, що ви хочете шукати, як другий аргумент. Приклад цієї формули може виглядати так
= COUNTIF (A2: A9, "Apple")
Ця формула поверне число 3, оскільки в нашому діапазоні є 3 клітини, які відповідають цим критеріям. Крім того, ми можемо використовувати посилання на клітинку замість жорсткого кодування значення. Якби ми написали “Яблуко” у комірці G2, ми могли б змінити формулу на
= COUNTIF (A2: A9, G2)
Під час роботи з числом важливо розрізняти числа та числа, які зберігаються у вигляді тексту. Як правило, ви не ставите лапки навколо цифр під час написання формул. Отже, щоб написати формулу, яка перевіряє число 5, ви б написали
= COUNTIF (A2: A9, 5)
Нарешті, ми також можемо перевірити наявність порожніх клітинок за допомогою рядка нульової довжини. Ми б записали цю формулу так
= COUNTIF (A2: A9, "")
Примітка: Ця формула буде враховувати як справді порожні клітинки, так і ті, які є порожніми як результат формули, як -от функція IF.
Часткові збіги
Функція COUNTIF підтримує використання символів підстановки, “*” чи “?” У критеріях. Давайте подивимося на цей список смачних хлібобулочних виробів:
Щоб знайти всі елементи, які починаються з Apple, ми могли б написати “Apple*”. Отже, щоб отримати відповідь 3, наша формула в D2 така
= COUNTIF (A2: A5, "Apple*")
Примітка: Функція COUNTIF не враховує регістр, тому ви також можете написати “apple*”, якщо хочете.
Повертаючись до нашої випічки, ми також можемо захотіти дізнатися, скільки пирогів у нас є у нашому списку. Ми можемо це виявити, розмістивши підстановку на початку пошукового терміну та написавши
= COUNTIF (A2: A5, "*пиріг")
Ця формула дає результат 2.
Ми також можемо використовувати символи підстановки, щоб перевірити наявність клітинок з текстом. Повернемося до нашого початкового списку даних.
Щоб підрахувати кількість клітинок, які мають хоча б певний текст, таким чином, не рахуючи чисел чи порожньої клітинки, ми можемо записати
= COUNTIF (A2: A9, "*")
Ви бачите, що наша формула правильно повертає результат 4.
Оператори порівняння в COUNTIF
Коли ми писали критерії, ми мали на увазі, що наш оператор порівняння - "=". Насправді ми могли б написати це:
= COUNTIF (A2: A9, "= Apple")
Хоча це додатковий символ для виписування, тому його зазвичай пропускають. Однак це означає, що ви можете використовувати інші оператори, такі як більше, менше або не дорівнює. Давайте подивимось на цей список зафіксованих віків:
Якби ми хотіли дізнатися, скільки дітей принаймні 5 років, ми можемо записати порівняння «більше або дорівнює» так:
= COUNTIF (A2: A8, "> = 5")
Примітка: Оператор порівняння завжди подається у вигляді текстового рядка, і тому він повинен бути у лапках.
Так само можна перевірити наявність елементів, менших за задане значення. Якщо нам потрібно з’ясувати, скільки менше 8, ми можемо виписати
= COUNTIF (A2: A8, "<8")
Це дає нам бажаний результат 5. Тепер уявімо, що всі 6-річні діти збираються на прогулянку. Скільки дітей залишиться? Ми можемо з'ясувати це, використовуючи порівняння "не дорівнює", як це:
= COUNTIF (A2: A8, "6")
Тепер ми можемо швидко побачити, що у нас є 6 дітей, яким ще немає 6 років.
У цих прикладах порівняння ми досі важко кодували потрібні значення. Ви також можете використовувати посилання на клітинку. Хитрість полягає в тому, що вам потрібно об'єднати оператор порівняння з посиланням на клітинку. Скажімо, ми поставили число 7 у клітинку С2, і ми хочемо, щоб наша формула в D2 показала, скільки дітей молодше 7 років.
Наша формула в D2 має виглядати так:
= COUNTIF (A2: A8, "<" & C2)
Примітка: При написанні цих формул зверніть особливу увагу на те, чи потрібно вставляти елемент у лапки, чи зовні. Оператори завжди знаходяться в лапках, посилання на клітинки завжди знаходяться поза лапками. Числа знаходяться зовні, якщо ви виконуєте точну відповідність, але всередині, якщо ви робите оператор порівняння.
Робота з датами
Ми бачили, як можна надати текст або число як критерій, але як бути, коли нам потрібно працювати з датами? Ось короткий зразок списку, з яким ми можемо працювати:
Щоб підрахувати кількість дат після 4 травня, нам потрібно бути обережним. Комп’ютери зберігають дати як числа, тому нам потрібно переконатися, що комп’ютер використовує правильне число. Якби ми написали цю формулу, чи отримали б ми правильний результат?
= COUNTIF (A2: A9, "
Відповідь "можливо". Оскільки ми випустили рік із наших критеріїв, комп’ютер буде вважати, що ми маємо на увазі поточний рік. Якщо всі дати, з якими ми працюємо, - це поточний рік, то ми отримаємо правильну відповідь. Однак, якщо є якісь дати в майбутньому, ми отримаємо неправильну відповідь. Крім того, як тільки розпочнеться наступний рік, ця формула поверне інший результат. Таким чином, цього синтаксису, ймовірно, слід уникати.
Оскільки може бути важко правильно записати дати у формулі, найкраще вписати дату, яку ви хочете використати у комірці, а потім ви можете використати цю посилання на клітинку у своїй формулі COUNTIF. Отже, давайте запишемо дату 7 травня 2020 р. У клітинку С2, а потім зможемо помістити формулу в С4.
Формула в C4 така
= COUNTIF (A2: A9, "<" & C2)
Тепер ми знаємо, що результат 7 правильний, і відповідь не зміниться несподівано, якщо ми відкриємо цю таблицю колись у майбутньому.
Перш ніж ми покинемо цей розділ, під час роботи з датами зазвичай використовується функція TODAY. Ми можемо використовувати це так само, як і посилання на клітинку. Наприклад, ми могли б змінити попередню формулу таким чином:
= COUNTIF (A2: A9, "<" & TODAY ())
Тепер наша формула все ще оновлюватиметься у міру проходження реального часу, і у нас буде кількість елементів, менших, ніж сьогодні.
Кілька критеріїв і COUNTIFS
Початкова функція COUNTIF була покращена у 2007 році, коли вийшла COUNTIFS. Синтаксис між ними дуже схожий, останній дозволяє надати додаткові діапазони та критерії. Ви можете легко використовувати COUNTIFS у будь -якій ситуації, коли існує COUNTIF. Це просто гарна ідея знати, що обидві функції існують.
Давайте подивимось на цю таблицю даних:
Щоб дізнатися, скільки людей на рівнях заробітної плати від 1 до 2, ви можете написати підсумок функцій COUNTIF, наприклад:
= COUNTIF (B2: B7, "> = 1")-COUNTIF (B2: B7, "> 2")
Ця формула буде працювати, коли ви знайдете все, що вище 1, але потім відніміть кількість записів, які виходять за межі граничної точки. Крім того, ви можете використовувати COUNTIFS таким чином:
= COUNTIFS (B2: B7, "> = 1", B2: B7, "<= 2")
Остання є більш інтуїтивно зрозумілою для читання, тому ви можете скористатися цим маршрутом. Крім того, COUNTIFS є більш потужним, коли вам потрібно розглянути кілька стовпців. Скажімо, ми хочемо знати, скільки людей в управлінні та на рівні оплати праці 1. Ви не можете цього зробити лише за КОЛИЧКИМ; вам потрібно буде виписати
= COUNTIFS (A2: A7, "Управління", B2: B7, 1)
Ця формула дасть вам правильний результат 2. Перш ніж ми покинемо цей розділ, давайте розглянемо логіку типу Or. Що якби ми хотіли дізнатися, скільки людей працює в управлінні чи? Вам потрібно буде додати кілька COUNTIFS разом, але це можна зробити двома способами. Найпростіший спосіб - написати це так:
= COUNTIF (A2: A7, "HR")+COUNTIF (A2: A7, "Management")
Ви також можете використати масив і записати цю формулу масиву:
= SUM (COUNTIF (A2: A7, {"HR", "Management"}))
Примітка: Формули масиву потрібно підтвердити за допомогою клавіш "Ctrl+Shift+Enter", а не просто "Enter".
Як ця формула буде працювати, вона побачить, що ви дали масив як вхід. Таким чином, він обчислить результат для двох різних функцій COUNTIF і збереже їх у масиві. Потім функція SUM додасть усі результати нашого масиву до єдиного результату. Таким чином, наша формула буде оцінюватися так:
= SUM (COUNTIF (A2: A7, {"HR", "Management"})) = SUM ({2, 3}) = 5
Порахуйте унікальні цінності
Тепер, коли ми побачили, як використовувати масив з функцією COUNTIF, ми можемо зробити цей крок далі, щоб допомогти нам підрахувати, скільки унікальних значень у діапазоні. По -перше, давайте ще раз поглянемо на наш список департаментів.
= SUM (1/COUNTIF (A2: A7, A2: A7))
Ми бачимо, що є 6 осередків даних, але є лише 3 різні елементи. Щоб математика впоралася, нам потрібно, щоб кожен елемент оцінювався в 1/N, де N - кількість разів, коли елемент повторюється. Наприклад, якби кожен HR коштував лише 1/2, тоді, коли ви їх додасте, ви отримаєте підрахунок 1 за 1 унікальне значення.
Повернімось до нашого COUNTIF, який призначений для визначення того, скільки разів елемент з'являється в діапазоні. У D2 ми запишемо формулу масиву
= SUM (1/COUNTIF (A2: A7, A2: A7))
Як буде працювати ця формула, для кожної клітинки в діапазоні A2: A7 вона перевірить, скільки разів вона з'являється. З нашої вибірки це буде створювати масив
{2, 2, 3, 3, 3, 1}
Потім ми перетворюємо всі ці числа на дроби, виконуючи деяке ділення. Тепер наш масив виглядає так
{1/2, 1/2, 1/3, 1/3, 1/3, 1/1}
Якщо все це скласти, то отримаємо бажаний результат 3.
Countif з двома або декількома умовами - функція Countifs
Поки що ми працювали лише з функцією COUNTIF. Функція COUNTIF може обробляти лише один критерій одночасно. Для COUNTIF з кількома критеріями вам потрібно скористатися функцією COUNTIFS. COUNTIFS поводиться точно так само, як COUNTIF. Ви просто додаєте додаткові критерії. Давайте розглянемо приклад нижче.
= COUNTIFS (B2: B7, "= 130")
COUNTIF & COUNTIFS у Таблицях Google
Функція COUNTIF & COUNTIFS працює точно так само в Google Таблицях, як і в Excel:
