Як порахувати кількість з умовою в Excel

0 Comments

Підрахунок функцій у Excel: Count, Counta, Countif, Countifs

Excel – це широко використовувана комп’ютерна програма. Це необхідно для проведення розрахунків, аналізу даних, прогнозування, складання графіків погашення, таблиць та діаграм, обчислення простих і складних функцій. Це частина офісного набору Microsoft.

  • Як працювати з Countif з кількома критеріями в Excel?
  • Countif (Countif)
  • Використання граф
  • Підрахуйте кількість клітин, що містять лише ряд.
  • Підрахуйте кількість неланківських клітин
  • Підрахуйте кількість клітин, значення яких більші або менше, ніж певне значення.
  • Клітин з кількома критеріями
  • (Або) критерії в графікації
  • Як розглянути кілька критеріїв?
  • Приклад лічильника з кількома критеріями
  • Навіщо використовувати ліцензію?
  • Часті Запитання

Як працювати з Countif з кількома критеріями в Excel?

Excel – це широко використовувана комп’ютерна програма. Це необхідно для проведення розрахунків, аналізу даних, прогнозування, складання графіків погашення, таблиць та діаграм, обчислення простих і складних функцій. Це частина офісного набору Microsoft.

Функція підрахунку Excel легко допоможе вам з точністю здійснити всі свої розрахунки. Вам потрібно зрозуміти функції Excel, і тоді ви можете збільшити свій професіоналізм.

Підрахунок у Excel є однією з найбільш часто використовуваних функцій. Більшість ділових вимог вимагають підрахунку даних. Наприклад, якщо ви запускаєте бізнес, вам може знадобитися знати, скільки людей використовує певну марку шампуню. Щоб розрахувати ці дані, потрібно використовувати формулу підрахунку, таку як кількість, графіка, графифи, графа та ін. Ви також можете використовувати поворотну таблицю для підрахунку, але вивчення таблиці повороту вимагає деяких зусиль, тоді як вищезгадані формули досить прості .

Але як щодо використання умов графіка та декількох функцій (з кількома критеріями) у Excel? Давайте подивимося з цим.

Countif (Countif)

Countif використовується для підрахунку кількості клітин у діапазоні на основі певних критеріїв. Основний синтаксис для огляду:

 = Countif (діапазон, критерії)

У вікні діапазону потрібно вказати діапазон, в якому ви хочете підрахувати кількість клітин. У полі критеріїв ви повинні вказати критерії. Критерії можуть бути числами, рядками, клітинними посиланнями або виразами. На підставі вказаних критеріїв функція огляду досліджує кожну клітину в діапазоні та підраховує клітини, що містять критерії.

Наприклад, припустимо, що ви хочете підрахувати кількість людей, які отримують зарплату понад 50 000 рублів. Ви можете використовувати формулу Countif в діапазоні. Функція Countifs у Excel

Як ви можете бачити в наведеному вище зображенні, у нас є дані працівників у таблиці, а у другому стовпчику ми маємо відповідну зарплату кожного працівника. Якщо вам потрібно підрахувати кількість працівників, які заробляють понад 5000 доларів, ви можете використовувати формулу Countif, вказавши зарплату в поле діапазону та > 5000 у полі критеріїв. Функція огляду буде підраховувати кількість відповідних клітин.

Використання граф

Підрахуйте кількість клітин, що містять лише ряд.

Якщо вам потрібно підрахувати кількість клітин у діапазоні, що містить рядок (не числа, дати або рази), ви можете використовувати функцію огляду з критеріями, позначеними зірочкою (*). Синтаксис:

 = Countif (діапазон, *)

Підрахуйте кількість неланківських клітин

Якщо ви хочете підрахувати кількість клітин, що містять текст, дати або номери, ви можете використовувати формулу:

 = Countif (діапазон)

Ця формула підраховує всі нелландські клітини.

Підрахуйте кількість клітин, значення яких більші або менше, ніж певне значення.

Ви можете використовувати більший символ у поле критерію для підрахунку клітин, які перевищують певне вказане значення. Наприклад:

Countif (діапазон; "> 10")

Для підрахунку клітин, значення яких менше, ніж певне значення, ви можете використовувати менше, ніж символ у полі критерію. Наприклад:

Підрахуйте кількість клітин, значення яких дорівнюють або не дорівнює певному значенню.

Ви можете використовувати рівний символ у поле критерію, щоб підраховувати клітини, які дорівнюють числі. Наприклад:

Countif (діапазон; "= 10")

Ви можете використовувати нерівний символ у поле критерію, щоб підраховувати клітини, значення яких не дорівнюють числі. Наприклад:

СЧЕТЕСЛІ (діапазон; "10")

Клітин з кількома критеріями

Якщо є більше одного діапазону та критеріїв, ви можете використовувати функцію огляду. Він працює так само, як і графіка, але вона використовується з кількома критеріями.

Синтаксис – це ліцензія:

 = Countifs (діапазон 1, критерій 1, діапазон 2, критерій 2 ..)

Ви можете вибрати кілька діапазонів та застосувати критерії. Остаточна кількість буде відображатися на основі різних критеріїв.

Продовжуючи нашим попереднім прикладом, припустимо, що ми хочемо визначити певний ріст корпорації. Припустимо, що критерій заохочення полягає в наступному: заробітна плата повинна бути меншою, ніж 50 000 рублів, а присутність працівника повинно бути більше 85%.

Стати Excel Pro: Приєднуйтесь до нашого курсу!

Підніміть свої навички від початківців до героя з нашим курсом Excel 365, розробленим для того, щоб зробити вас досвідченими лише на кількох сеансах.

У наведеному вище прикладі ми маємо ім’я працівника як дані в першому стовпчику, дані заробітної плати у другому стовпчику та дані відвідуваності в третьому стовпчику. Тепер ми розраховуємо кількість працівників з зарплатою менш ніж 50 000 рублів. і понад 85% відвідуваності. Оскільки дані згадуються у двох різних стовпцях, ми повинні вказати відповідний діапазон та застосувати критерії. Це дасть нам кількість клітин, які відповідають всім критеріям.

Важливо знати!

За замовчуванням Coftifs застосовується та логіка до різних зазначених критеріїв.

Countifs повертає кількість рядків, клітини яких відповідають вказаним критеріям.

Countif і Countif також будуть працювати, якщо клітини не є суміжними.

Ви також можете використовувати спеціальні символи, такі як *, тощо, залежно від вашої вимоги.

Для цієї функції немає ярлика. Ви повинні ввести формулу, починаючи з рівного знака, введіть діапазони та критерії та натисніть клавішу Enter.

(Або) критерії в графікації

Оскільки графика використовує за замовчуванням та логіка, вам доведеться використовувати інший метод для виконання або операції в графікації. У наведеному вище прикладі, якщо ви хочете підрахувати кількість працівників, які мають зарплату менше 5000 доларів, або відвідуваність більше 85%, нам потрібно застосувати або логіку тут.

Базовий синтаксис або логіка в аналізі:

 = Countif (angy_1, критерій_1) + countif (adjase_2, критерій_2)

У цьому прикладі ми працюємо з однаковими даними, які вже використовувалися як приклад оліфікації вище. Але тут ми використовуємо або логіку замість того, щоб і (за замовчуванням виводяться література, а логіка становить 2 за замовчуванням; і вихідні дані з або логікою 9).

Тут ми додаємо два різних функцій графіка для обробки декількох критеріїв. Результат першого огляду (де критерієм є заробітна плата менше 5000 доларів США), становить 2, а результат другого графіка (де критерій відвідуваність вище 85%) становить 7. Таким чином, ми можемо досягти логіки Або в графікації. І зверніть увагу, що для того, щоб отримати правильну відповідь у цьому випадку, нам потрібно відняти кількість людей, що належать до обох категорій.

Незважаючи на те, що існує безліч додатків для огляду та ліцензій, ці функції легко вчитися і пам’ятають. Я надавав кілька прикладів, які ви можете спробувати безпосередньо в Microsoft Excel. Після завершення цих прикладів ви можете створити свій власний приклад, збираючи випадкові дані з Інтернету. Як тільки ви розумієте, як олівець працює з кількома критеріями, а досить практика, ви можете розраховувати клітини, які містять будь-які дані!

Як розглянути кілька критеріїв?

Формула Excel для підрахунку декількох критеріїв: = Countifs. S в кінці робить його множиною і, отже, означає, що існують кілька критеріїв (2 або більше).

У прикладах та ілюстраціях нижче в цьому підручнику ви дізнаєтеся, як використовувати ліцензію в Excel.

Як використовувати кілька критеріїв огляду?

  • Крок 1: Документування критеріїв або умов, які ви хочете перевірити.
  • Крок 2: Введіть = Countifs ( та виберіть діапазон, в якому ви хочете перевірити перший критерій).
  • Крок 3: Введіть тест на критерії.
  • Крок 4. Виберіть діапазон другого підрахунку, який ви хочете перевірити (він може бути такий самий діапазон знову або новий).
  • Крок 5: Введіть тест на критерії.
  • Крок 6: Повторіть вищезазначене правило стільки разів, скільки потрібно.
  • Крок 7: Закрийте кронштейн та натисніть клавішу Enter.

Приклад лічильника з кількома критеріями

Як ви побачите, кожен з критеріїв перевіряється, а Excel обчислює, скільки разів випливає результат тесту. Це, як використовувати Excel, щоб підрахувати кілька критеріїв.

Навіщо використовувати ліцензію?

Є багато випадків, коли ви хотіли б підрахувати, скільки разів було виконано декілька критеріїв. У фінансуванні це можна використовувати для вибору компаній, які відповідають певному профілю. Як у випадку, як вище, ми хотіли знати, скільки компаній перевищують певну ринкову кришку або інші економічні показники. Використання або логіка в аналізі Landif / Countif може зробити його більш ефективним і заощадити вам проблему написання довгих формул.

Часті Запитання

Як працює Countif в Excel? Якщо вам потрібно порахувати кількість комірок у діапазоні, що містить рядок (не числа, дати чи часи), ви можете використовувати функцію Countif з критеріями, позначеними зірочкою (*).

Стати Excel Pro: Приєднуйтесь до нашого курсу!

Підніміть свої навички від початківців до героя з нашим курсом Excel 365, розробленим для того, щоб зробити вас досвідченими лише на кількох сеансах.

COUNTIF (функція COUNTIF)

За допомогою функції COUNTIF, яка належить до статистичних функцій, можна порахувати кількість клітинок, які відповідають певній умові (наприклад, скільки разів якесь місто з’являється в списку клієнтів).

У найпростішому випадку COUNTIF працює за таким принципом:

COUNTIF(діапазон;умова)

Ім’я аргументу

діапазон (обов’язковий аргумент)

Група клітинок, які потрібно підрахувати. Аргумент діапазон може містити числа, масиви, іменовані діапазони або посилання з числами. Пусті та текстові значення ігноруються.

умова (обов’язковий аргумент)

Число, вираз, посилання на клітинку або текстовий рядок, що визначає, які клітинки потрібно підрахувати.

Наприклад, можна використати число, як-от 32, порівняння, як-от “>32”, клітинку, як-от B4, або слово, як-от “яблука”.

У функції COUNTIF використовується лише одна умова. Використовуйте функцію COUNTIFS, якщо потрібно застосувати кілька умов.

Приклади

Щоб скористатися цими прикладами в програмі Excel, скопіюйте дані в таблиці нижче та вставте їх у клітинку A1 нового аркуша.

Рахує кількість клітинок із текстом “яблука” в клітинках від A2 до A5. У результаті отримаємо 2.

Рахує кількість клітинок із текстом “кавуни” (значення в клітинці A4) у клітинках від A2 до A5. У результаті отримаємо 1.

Рахує кількість клітинок із текстом “яблука” (значення в клітинці A2) і “апельсини” (значення в клітинці A3) у клітинках від A2 до A5. У результаті отримаємо 3. Функцію COUNTIF використано у формулі двічі, щоб визначити кілька умов – по одній умові на вираз. Також можна скористатися функцією COUNTIFS.

Рахує кількість клітинок зі значенням, більшим за 55, у клітинках B2:B5. У результаті отримаємо 2.

Рахує кількість клітинок зі значенням, яке не дорівнює 75, у клітинках B2:B5. Амперсанд (&) об’єднує оператор порівняння “не дорівнює” (<>) і значення в клітинці B4 для прочитання функції =COUNTIF(B2:B5;”<>75″). У результаті отримаємо 3.

Рахує кількість клітинок зі значеннями, більшими (>) або рівними (=) 32 та меншими (<) або рівними (=) 85, у клітинках B2:B5. У результаті отримаємо 1.

Рахує кількість клітинок, що містять будь-який текст, у клітинках від A2 до A5. Зірочка (*) використовується як символ узагальнення для будь-яких символів. У результаті отримаємо 4.

Рахує кількість клітинок, які мають точно 6 символів і закінчуються буквами “ни”, у клітинках від A2 до A5. Знак питання (?) використовується як символ узагальнення для заміни будь-яких окремих символів. У результаті отримаємо 1.

Поширені проблеми

Для довгих рядків повернуто помилкове значення.

Функція COUNTIF повертає неправильні результати, якщо зіставляються рядки довжиною більше 255 символів

Щоб зіставити рядки, довші за 255 символів, використовуйте функцію CONCATENATE або оператор об’єднання &. Наприклад, =COUNTIF(A2:A5;”довгий рядок”&”інший довгий рядок”).

Не повернуто жодного значення, коли очікувалося значення.

Переконайтеся, що аргумент умова взято в лапки.

Формула COUNTIF отримує #VALUE! під час звернення до іншого аркуша.

Це стається, коли формула, яка містить функцію, посилається на клітинки або діапазон клітинок у закритій книзі та обчислює кількість цих клітинок. Щоб ця функція працювала, потрібно відкрити іншу книгу.

Практичні поради

Пам’ятайте, що функція COUNTIF ігнорує верхній і нижній регістри в текстових рядках.

Умови нечутливі до регістра. Іншими словами, рядкам “яблука” та “ЯБЛУКА” відповідатимуть одні й ті ж клітинки.

Використовуйте символи узагальнення.

Символи узагальнення – знак питання (?) і зірочку (*) – можна використовувати для умов. Знак питання відповідає будь-якому одному символу. Зірочка відповідає будь-якій послідовності символів. Якщо потрібно знайти власне знак питання або зірочку, перед відповідним символом введіть тильду (~).

Наприклад, =COUNTIF(A2:A5;”яблуко?”) підрахує всі екземпляри “яблука” з останньою буквою, яка може відрізнятися.

Переконайтеся, що ваші дані не містять помилкові символи.

Під час підрахунку текстових значень переконайтеся, що дані не містять пробілів на початку чи в кінці, а також неузгоджених прямих чи фігурних лапок або недрукованих символів. У таких випадках функція COUNTIF може повернути неочікуване значення.

Спробуйте використати функцію CLEAN або TRIM.

Для зручності використовуйте іменовані діапазони

Функція COUNTIF підтримує іменовані діапазони у формулі (наприклад, =COUNTIF(фрукти;”>=32″)-COUNTIF(фрукти;”>85″). Іменований діапазон може бути розташовано на поточному аркуші, на іншому аркуші тієї самої книги або в іншій книзі. Щоб створити посилання на іншу книгу, її також потрібно відкрити.

Примітка.: Під час підрахунку функція COUNTIF не враховує колір фону або шрифту клітинок. Проте Excel підтримує користувацькі функції (UDF) на основі операцій із клітинками Microsoft Visual Basic for Applications (VBA), які враховують колір фону або шрифту. Нижче наведено приклад того, як можна підрахувати кількість клітинок із певним кольором за допомогою VBA.

Потрібна додаткова довідка?

Ви завжди можете поставити запитання експерту в спільноті Tech у розділі Excel чи отримати підтримку в спільнотах.