Excel має багато функцій, де користувачеві потрібно вказати один або декілька критеріїв, щоб отримати результат. Наприклад, якщо потрібно підрахувати клітинки на основі кількох критеріїв, можна скористатися функціями COUNTIF або COUNTIFS в Excel.
Цей підручник охоплює різні способи використання одного або декількох критеріїв у функціях COUNTIF та COUNTIFS в Excel.
Хоча в цьому підручнику я зосереджусь, перш за все, на функціях COUNTIF і COUNTIFS, усі ці приклади можна також використовувати в інших функціях Excel, які беруть за основу кілька критеріїв (наприклад, SUMIF, SUMIFS, AVERAGEIF та AVERAGEIFS).
Вступ до функцій Excel COUNTIF та COUNTIFS
Давайте спочатку розберемося з використанням функцій COUNTIF та COUNTIFS в Excel.
Функція Excel COUNTIF (бере єдині критерії)
Функція Excel COUNTIF найкраще підходить для ситуацій, коли потрібно підрахувати клітинки на основі одного критерію. Якщо потрібно підрахувати за кількома критеріями, скористайтеся функцією COUNTIFS.
Синтаксис
= COUNTIF (діапазон, критерії)
Аргументи введення
- діапазон - діапазон клітинок, які потрібно підрахувати.
- критерії - критерії, які необхідно оцінити за діапазоном клітинок для підрахунку клітини.
Функція Excel COUNTIFS (бере кілька критеріїв)
Функція Excel COUNTIFS найкраще підходить для ситуацій, коли потрібно підрахувати клітинки на основі кількох критеріїв.
Синтаксис
= COUNTIFS (діапазон критеріїв1, критерії1, [діапазон критеріїв2, критерії2] …)
Аргументи введення
- діапазон_критеріїв1 - Діапазон клітинок, для яких потрібно оцінити за критеріями1.
- критерії1 - критерії, які потрібно оцінити для критерію_діапазону1, щоб визначити, які клітинки підраховувати.
- [діапазон_критеріїв2] - Діапазон клітинок, для яких потрібно оцінити за критеріями2.
- [критерії2] - критерії, які потрібно оцінити для діапазону_критеріїв2, щоб визначити, які клітинки підраховувати.
Тепер давайте подивимося на деякі приклади використання кількох критеріїв у функціях COUNTIF в Excel.
Використання КРИТЕРІЙ ЧИСЛА у функціях Excel COUNTIF
#1 Підраховуйте клітинки, якщо критерії дорівнюють значенню
Щоб отримати кількість клітинок, де аргумент критерію дорівнює заданому значенню, ви можете або безпосередньо ввести критерії, або використати посилання на комірку, що містить критерії.
Нижче наведено приклад, коли ми підраховуємо клітинки, які містять число 9 (що означає, що аргумент критерію дорівнює 9). Ось формула:
= COUNTIF ($ B $ 2: $ B $ 11, D3)
У наведеному вище прикладі (на малюнку) критерій знаходиться у комірці D3. Ви також можете ввести критерії безпосередньо у формулу. Наприклад, ви також можете використовувати:
= COUNTIF ($ B $ 2: $ B $ 11,9)
#2 Підраховуйте клітинки, якщо критерії ВИЩЕ, ніж значення
Щоб отримати кількість клітинок зі значенням, більшим за вказане значення, ми використовуємо оператор більший за (">"). Ми можемо або використати його безпосередньо у формулі, або використати посилання на клітинку, що має критерії.
Щоразу, коли ми використовуємо оператор у критеріях у Excel, нам потрібно поставити його у подвійні лапки. Наприклад, якщо критерій більше 10, то нам потрібно ввести “> 10” як критерій (див. Рис. Нижче):
Ось формула:
= COUNTIF ($ B $ 2: $ B $ 11, »> 10 ″)
Ви також можете мати критерії в клітинці та використовувати посилання на клітинку як критерії. У цьому випадку вам НЕ потрібно ставити критерії в подвійні лапки:
= COUNTIF ($ B $ 2: $ B $ 11, D3)
Також може бути випадок, коли ви хочете, щоб критерії знаходились у клітинці, але не хочете, щоб це було з оператором. Наприклад, вам може знадобитися, щоб клітинка D3 мала номер 10, а не> 10.
У цьому випадку вам потрібно створити аргумент критерію, який являє собою комбінацію оператора та посилання на клітинку (див. Рис. Нижче):
= COUNTIF ($ B $ 2: $ B $ 11, ”>” & D3)
ПРИМІТКА. Коли ви поєднуєте оператор і посилання на клітинку, оператор завжди в подвійних лапках. Оператор та посилання на комірку об’єднані амперсандом (&).
#3 Підраховуйте клітинки, якщо критерії МЕНШЕ, ніж значення
Щоб отримати кількість клітинок зі значенням, меншим за вказане значення, ми використовуємо оператор less than ("<"). Ми можемо або використати його безпосередньо у формулі, або використати посилання на клітинку, що має критерії.
Щоразу, коли ми використовуємо оператор у критеріях у Excel, нам потрібно поставити його у подвійні лапки. Наприклад, якщо критерієм є те, що число повинно бути менше 5, то нам потрібно ввести “<5” як критерій (див. Рис. Нижче):
= COUNTIF ($ B $ 2: $ B $ 11, "<5 ″)
Ви також можете мати критерії в клітинці та використовувати посилання на клітинку як критерій. У цьому випадку вам НЕ потрібно ставити критерії в подвійні лапки (див. Фото нижче):
= COUNTIF ($ B $ 2: $ B $ 11, D3)
Крім того, може бути випадок, коли ви хочете, щоб критерії знаходились у клітинці, але не хочете, щоб це було з оператором. Наприклад, вам може знадобитися, щоб клітинка D3 мала номер 5, а не <5.
У цьому випадку вам потрібно створити аргумент критерію, який являє собою комбінацію оператора та посилання на клітинку:
= COUNTIF ($ B $ 2: $ B $ 11, "<" & D3)
ПРИМІТКА. Коли ви поєднуєте оператор і посилання на клітинку, оператор завжди в подвійних лапках. Оператор та посилання на комірку об’єднані амперсандом (&).
#4 Підрахунок клітин з кількома критеріями - між двома значеннями
Щоб отримати підрахунок значень між двома значеннями, нам потрібно використовувати кілька критеріїв у функції COUNTIF.
Ось два способи зробити це:
СПОСІБ 1: Використання функції COUNTIFS
Функція COUNTIFS може обробляти декілька критеріїв як аргументи і підраховує клітинки лише тоді, коли всі критерії є TRUE. Для підрахунку клітинок зі значеннями між двома зазначеними значеннями (скажімо, 5 і 10) ми можемо використовувати таку функцію COUNTIFS:
= COUNTIFS ($ B $ 2: $ B $ 11, ”> 5 ″, $ B $ 2: $ B $ 11”, <10 ″)
ПРИМІТКА. У наведеній вище формулі не враховуються клітинки, які містять 5 або 10. Якщо ви хочете включити ці клітинки, використовуйте оператори, більші за рівне (> =) та менше від (<=). Ось формула:
= COUNTIFS ($ B $ 2: $ B $ 11, ”> = 5 ″, $ B $ 2: $ B $ 11,” <= 10 ″)
Ви також можете мати ці критерії в клітинках і використовувати посилання на клітинку як критерій. У цьому випадку вам НЕ потрібно ставити критерії в подвійні лапки (див. Фото нижче):
Ви також можете використовувати комбінацію посилань на клітинки та операторів (де оператор вводиться безпосередньо у формулі). Коли ви поєднуєте оператор і посилання на клітинку, оператор завжди в подвійних лапках. Оператор та посилання на комірку об’єднані амперсандом (&).
СПОСІБ 2: Використання двох функцій COUNTIF
Якщо у вас є кілька критеріїв, ви можете використовувати COUNTIFS або створити комбінацію функцій COUNTIF. Формула нижче також буде робити те ж саме:
= COUNTIF ($ B $ 2: $ B $ 11, ”> 5 ″)-COUNTIF ($ B $ 2: $ B $ 11,”> 10 ″)
У наведеній вище формулі спочатку ми знаходимо кількість клітинок, значення яких більше 5, і віднімаємо кількість клітин зі значенням більше 10. Це дасть нам результат як 5 (це кількість клітин, які мають значення більше 5 і менше 10).
Якщо ви хочете, щоб формула містила як 5, так і 10, замість цього використовуйте таку формулу:
= COUNTIF ($ B $ 2: $ B $ 11, ”> = 5 ″)-COUNTIF ($ B $ 2: $ B $ 11,”> 10 ″)
Якщо ви хочете, щоб формула виключила "5" і "10" із підрахунку, скористайтеся такою формулою:
= COUNTIF ($ B $ 2: $ B $ 11, ”> = 5 ″)-COUNTIF ($ B $ 2: $ B $ 11,”> 10 ″)-COUNTIF ($ B $ 2: $ B $ 11,10)
Ви можете мати ці критерії в клітинках і використовувати посилання на клітинки, або ви можете використовувати комбінацію операторів та посилань на клітинки.
Використання критеріїв ТЕКСТУ у функціях Excel
#1 Підраховувати клітинки, якщо критерій дорівнює визначеному тексту
Для підрахунку клітинок, які містять точну відповідність зазначеного тексту, ми можемо просто використовувати цей текст як критерій. Наприклад, у наборі даних (показано нижче на малюнку), якщо я хочу підрахувати всі клітинки з ім’ям Джо, я можу використати формулу нижче:
= COUNTIF ($ B $ 2: $ B $ 11, "Джо")
Оскільки це текстовий рядок, мені потрібно поставити текстові критерії в подвійні лапки.
Ви також можете мати критерії в комірці, а потім використовувати цю посилання на клітинку (як показано нижче):
= COUNTIF ($ B $ 2: $ B $ 11, E3)
ПРИМІТКА. Ви можете отримати неправильні результати, якщо в діапазоні критеріїв чи критеріїв є пробіли для початку/кінця. Перед використанням цих формул обов’язково очистіть дані.
#2 Підраховуйте клітинки, коли критерії НЕ РІВНІ до зазначеного тексту
Подібно до того, що ми бачили у наведеному вище прикладі, ви також можете підрахувати клітинки, які не містять зазначеного тексту. Для цього нам потрібно використовувати оператор не дорівнює ().
Припустимо, ви хочете підрахувати всі клітинки, які не містять назви JOE, ось формула, яка це зробить:
= COUNTIF ($ B $ 2: $ B $ 11, "Джо")
Ви також можете мати критерії в клітинці та використовувати посилання на клітинку як критерії. У цьому випадку вам НЕ потрібно ставити критерії в подвійні лапки (див. Фото нижче):
= COUNTIF ($ B $ 2: $ B $ 11, E3)
Також може бути випадок, коли ви хочете, щоб критерії були у клітинці, але не хочете, щоб це було з оператором. Наприклад, вам може знадобитися, щоб клітинка D3 мала ім’я Джо, а не Джо.
У цьому випадку вам потрібно створити аргумент критерію, який являє собою комбінацію оператора та посилання на клітинку (див. Рис. Нижче):
= COUNTIF ($ B $ 2: $ B $ 11, ”” та E3)
Коли ви поєднуєте оператор і посилання на клітинку, оператор завжди в подвійних лапках. Оператор та посилання на комірку об’єднані амперсандом (&).
Використання критеріїв DATE у функціях Excel COUNTIF та COUNTIFS
Excel зберігає дату та час у цифрах. Тому ми можемо використовувати його так само, як і числа.
#1 Підраховувати клітинки, коли критерії дорівнюють визначеній даті
Щоб отримати кількість клітинок, які містять зазначену дату, ми б разом з датою використовували оператор рівний (=).
Для використання дати я рекомендую використовувати функцію DATE, оскільки вона позбавляє від будь -якої можливості помилки у значенні дати. Так, наприклад, якщо я хочу використати дату 1 вересня 2015 року, я можу використовувати функцію ДАТА, як показано нижче:
= ДАТА (2015,9,1)
Ця формула поверне ту саму дату, незважаючи на регіональні відмінності. Наприклад, 01-09-2015 буде 1 вересня 2015 року відповідно до синтаксису дати США, а 09 січня 2015 року-відповідно до синтаксису дати Великобританії. Однак ця формула завжди повертається 1 вересня 2105 року.
Ось формула для підрахунку кількості клітинок, які містять дату 02-09-2015:
= COUNTIF ($ A $ 2: $ A $ 11, DATE (2015,9,2))
#2 Підраховуйте клітинки, якщо критерії до або після визначеної дати
Для підрахунку клітинок, які містять дату до або після зазначеної дати, ми можемо використовувати оператори менше/більше, ніж.
Наприклад, якщо я хочу підрахувати всі клітинки, які містять дату після 02 вересня 2015 року, я можу використати формулу:
= COUNTIF ($ A $ 2: $ A $ 11, ”>” & DATE (2015,9,2))
Так само можна підрахувати кількість клітинок до вказаної дати. Якщо ви хочете включити дату до підрахунку, скористайтесь оператором "рівно" та оператором "більше/менше".
Ви також можете використовувати посилання на клітинку, яка містить дату. У цьому випадку вам потрібно поєднати оператор (у подвійних лапках) з датою за допомогою амперсанту (&).
Дивіться приклад нижче:
= COUNTIF ($ A $ 2: $ A $ 11, ”>” & F3)
#3 Підрахунок клітин з кількома критеріями - між двома датами
Щоб отримати підрахунок значень між двома значеннями, нам потрібно використовувати кілька критеріїв у функції COUNTIF.
Ми можемо зробити це за допомогою двох методів - однієї функції COUNTIFS або двох функцій COUNTIF.
СПОСІБ 1: Використання функції COUNTIFS
Функція COUNTIFS може приймати в якості аргументів декілька критеріїв і підраховує клітинки лише тоді, коли всі критерії мають значення TRUE. Для підрахунку клітинок зі значеннями між двома зазначеними датами (скажімо, 2 вересня та 7 вересня) ми можемо використовувати таку функцію COUNTIFS:
= COUNTIFS ($ A $ 2: $ A $ 11, ”>” & DATE (2015,9,2), $ A $ 2: $ A $ 11, ”<“ & DATE (2015,9,7))
Наведена вище формула не враховує клітинки, які містять зазначені дати. Якщо ви хочете також включити ці дати, використовуйте оператори, більші за рівну (> =) і менші за рівну (<=). Ось формула:
= COUNTIFS ($ A $ 2: $ A $ 11, ”> =” & DATE (2015,9,2), $ A $ 2: $ A $ 11, ”<=” & DATE (2015,9,7))
Ви також можете мати дати в комірці та використовувати посилання на клітинку як критерій. У цьому випадку ви не можете мати оператор із датою в осередках. Вам потрібно вручну додати оператори у формулу (у подвійних лапках) та додати посилання на клітинку, використовуючи амперсанд (&). Дивіться фото нижче:
= COUNTIFS ($ A $ 2: $ A $ 11, ”>” & F3, $ A $ 2: $ A $ 11, ”<“ & G3)
СПОСІБ 2: Використання функцій COUNTIF
Якщо у вас є кілька критеріїв, ви можете або використовувати одну функцію COUNTIFS, або створити комбінацію з двох функцій COUNTIFS. Формула, наведена нижче, також зробить трюк:
= COUNTIF ($ A $ 2: $ A $ 11, ”>” & DATE (2015,9,2))-COUNTIF ($ A $ 2: $ A $ 11, ”>” & DATE (2015,9,7))
У наведеній вище формулі ми спочатку знаходимо кількість клітинок, у яких є дата після 2 вересня, і віднімаємо кількість клітинок з датами після 7 вересня. Це дасть нам результат як 7 (це кількість клітин, у яких є дати після 2 вересня та до 7 вересня або раніше).
Якщо ви не хочете, щоб формула враховувала і 2 вересня, і 7 вересня, скористайтеся такою формулою:
= COUNTIF ($ A $ 2: $ A $ 11, ”> =” & DATE (2015,9,2))-COUNTIF ($ A $ 2: $ A $ 11, ”>” & DATE (2015,9,7))
Якщо ви хочете виключити обидві дати з підрахунку, скористайтеся такою формулою:
= COUNTIF ($ A $ 2: $ A $ 11, ”>” & DATE (2015,9,2))-COUNTIF ($ A $ 2: $ A $ 11, ”>” & DATE (2015,9,7) -COUNTIF ($ A $ 2: $ A $ 11, ДАТА (2015,9,7)))
Крім того, ви можете мати дати критеріїв у клітинках та використовувати посилання на клітинки (разом з операторами у подвійних лапках, об’єднаних за допомогою амперсанда).
Використання символів WILDCARD у критеріях у функціях COUNTIF & COUNTIFS
В Excel є три символи підстановки:
- * (зірочка) - Він позначає будь -яку кількість символів. Наприклад, ex* може означати excel, excels, наприклад, експерт тощо.
- ? (знак питання) - Він символізує одного символу. Наприклад, Tr? Mp може означати Трампа або Бродягу.
- ~ (тильда) - Він використовується для ідентифікації символу підстановки (~, *,?) У тексті.
Ви можете використовувати функцію COUNTIF із символами підстановки для підрахунку клітинок, коли інша вбудована функція підрахунку не працює. Наприклад, припустимо, що у вас є набір даних, як показано нижче:
Тепер візьмемо різні приклади:
#1 Підрахувати клітинки, що містять текст
Для підрахунку клітинок із текстом ми можемо використовувати символ підстановки * (зірочка). Оскільки зірочка представляє будь -яку кількість символів, вона буде зараховувати всі клітинки, у яких є будь -який текст. Ось формула:
= COUNTIFS ($ C $ 2: $ C $ 11, ”*”)
Примітка: Формула вище ігнорує клітинки, які містять числа, порожні клітинки та логічні значення, але враховуватиме, що клітинки містять апостроф (і, отже, виглядають порожніми) або клітинки, які містять порожній рядок (= ””), які, можливо, були повернуті як частина формули.
Ось детальний підручник з обробки випадків, коли є порожній рядок або апостроф.
Ось детальний підручник з обробки випадків, коли є порожні рядки або апострофи.
Нижче наведено відео, яке пояснює різні сценарії підрахунку клітинок із текстом.
#2 Підрахунок непустих клітинок
Якщо ви плануєте використовувати функцію COUNTA, подумайте ще раз.
Спробуйте, і це може вам не вдатися. COUNTA також буде зараховувати клітинку, яка містить порожній рядок (часто повертається за формулами як = "" або коли люди вводять у клітинку лише апостроф). Клітинки, які містять порожні рядки, виглядають порожніми, але не є такими, і тому їх підраховує функція COUNTA.
COUNTA також буде зараховувати клітинку, яка містить порожній рядок (часто повертається за формулами як = "" або коли люди вводять у клітинку лише апостроф). Клітинки, які містять порожні рядки, виглядають порожніми, але не є такими, і тому їх підраховує функція COUNTA.
Отже, якщо ви використовуєте формулу = COUNTA (A1: A11), вона повертає 11, тоді як має повертати 10.
Ось виправлення:
= COUNTIF ($ A $ 1: $ A $ 11, ”?*”)+COUNT ($ A $ 1: $ A $ 11)+SUMPRODUCT (-ISLOGICAL ($ A $ 1: $ A $ 11))
Давайте зрозуміємо цю формулу, розбивши її:
- COUNTIF ($ N $ 8: $ N $ 18, ”?*”) - Ця частина формули повертає 5. Сюди входить будь -яка клітинка з текстовим символом. А? представляє один символ, а * - будь -яку кількість символів. Отже, комбінація?* У критеріях змушує Excel підраховувати клітинки, які мають принаймні один символ тексту.
- COUNT ($ A $ 1: $ A $ 11) - підраховує всі клітинки, що містять числа. У наведеному вище прикладі він повертає 3.
- SUMPRODUCT (-ISLOGICAL ($ A $ 1: $ A $ 11)) - підраховуються всі комірки, які містять логічні значення. У наведеному вище прикладі повертається 2.
#3 Підрахувати клітинки, що містять певний текст
Скажімо, ми хочемо підрахувати всі клітинки, де назва представника продажів починається з J. Це легко можна досягти за допомогою символу підстановки у функції COUNTIF. Ось формула:
= COUNTIFS ($ C $ 2: $ C $ 11, "J*")
Критерій J* визначає, що текст у комірці повинен починатися з J і може містити будь -яку кількість символів.
Якщо ви хочете підрахувати клітинки, які містять алфавіт у будь -якому місці тексту, обведіть його зірочкою з обох сторін. Наприклад, якщо ви хочете підрахувати клітинки, які містять в собі алфавіт “а”, використовуйте * а * як критерій.
Ця стаття надзвичайно довга в порівнянні з іншими моїми статтями. Сподіваюся, вам сподобалось. Дайте мені знати ваші думки, залишивши коментар.
Ви також можете знайти корисними такі підручники Excel:
- Порахуйте кількість слів у Excel.
- Підрахунок клітин на основі кольору фону в Excel.
- Як підсумувати стовпець у Excel (5 дійсно простих способів)
