Підраховуйте унікальні значення в Excel за допомогою функції COUNTIF

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

Як підрахувати унікальні значення в Excel

Скажімо, у нас є набір даних, як показано нижче:

Для цілей цього підручника я буду називати діапазон A2: A10 НАЗВАМИ. У подальшому ми будемо використовувати цей іменований діапазон у формулах.

Дивіться також: Як створити іменовані діапазони в Excel.

У цьому наборі даних повторюється діапазон NAMES. Щоб отримати кількість унікальних імен із цього набору даних (A2: A10), ми можемо використовувати комбінацію функцій COUNTIF та SUMPRODUCT, як показано нижче:

= SUMPRODUCT (1/COUNTIF (НАЗВИ, НАЗВИ)))

Як працює ця формула?

Давайте розберемо цю формулу, щоб краще зрозуміти:

• COUNTIF (НАЗВА, НАЗВА)
  • Ця частина формули повертає масив. У наведеному вище прикладі це буде {2; 2; 3; 1; 3; 1; 2; 3; 2}. Цифри тут вказують, скільки разів значення зустрічається в даному діапазоні клітинок.
    Наприклад, ім’я - Боб, яке зустрічається двічі у списку, отже, воно повертало б номер 2 для Боба. Аналогічно, Стів зустрічається тричі, і тому Стіву повертається 3.
• 1/COUNTIF (НАЗВА, НАЗВА)
  • Ця частина формули повертає масив - {0,5; 0,5; 0,333333333333333; 1; 0,333333333333333; 1; 0,5; 0,333333333333333; 0,5}
    Оскільки ми поділили 1 на масив, він повертає цей масив.
    Наприклад, перший елемент масиву, повернутий вище, був 2. Коли 1 ділиться на 2, він повертає .5.
• ПІДПРИЄМСТВО (1/КІЛЬКІСТЬ (НАЗВА, НАЗВА))
  • SUMPRODUCT просто додає всі ці числа. Зауважте, що якщо Боб зустрічається двічі у списку, вищевказаний масив повертає .5 скрізь, де ім’я Боба з’явилося у списку. Так само, оскільки Стів тричі з'являється у списку, масив повертає .3333333 щоразу, коли з'являється ім'я Стіва. Коли ми додаємо числа до кожного імені, воно завжди повертає 1. А якщо ми додамо всі числа, це повертає загальну кількість унікальних імен у списку.

Ця формула працює добре, поки у діапазоні немає порожніх клітинок. Але якщо у вас є порожні клітинки, він поверне #DIV/0! помилка.

Як поводитися з клітинами BLANK?

Давайте спочатку зрозуміємо, чому він повертає помилку, якщо в діапазоні є порожня клітинка. Припустимо, у нас є набір даних, як показано нижче (клітинка А3 порожня):

Тепер, якщо ми використовуємо ту саму формулу, яку ми використовували вище, частина формули COUNTIF повертає масив {2; 0; 3; 1; 3; 1; 2; 3; 1}. Оскільки у клітинці A3 немає тексту, його кількість повертається як 0.

А оскільки ми ділимо 1 на весь цей масив, він повертає #DIV/0! помилка.

Щоб виправити цю помилку поділу у разі порожніх клітинок, використовуйте формулу нижче:

= SUMPRODUCT ((1/COUNTIF (НАЗВА, НАЗВИ & ””)))

Однією з змін, які ми внесли до цієї формули, є частина критеріїв функції COUNTIF. Ми використовували NAMES & ”” замість NAMES. Роблячи це, формула повертає кількість порожніх клітинок (раніше вона повертала 0, де була порожня клітинка).

ПРИМІТКА. Ця формула враховуватиме порожні клітинки як унікальне значення і повертатиме його в результаті.

У наведеному вище прикладі результат має бути 5, але він повертає 6, оскільки порожня клітинка зараховується як одне з унікальних значень.

Ось формула, яка дбає про порожні клітинки і не враховує її в кінцевому результаті:

= SUMPRODUCT ((NAMES ””)/COUNTIF (NAMES, NAMES & ””))

У цій формулі замість 1 як чисельника ми використовували НАЗВИ ””. Це повертає масив TRUE та FALSE. Він повертає FALSE, коли є порожня клітинка. Оскільки TRUE дорівнює 1, а FALSE дорівнює 0 у розрахунках, порожні клітинки не зараховуються, оскільки чисельник дорівнює 0 (FALSE).

Тепер, коли у нас є базовий скелет формули, ми можемо піти ще далі і порахувати різні типи даних.

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

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

Ось формула, яка буде рахувати унікальні текстові значення в Excel:

= SUMPRODUCT ((ISTEXT (NAMES)/COUNTIF (NAMES, NAMES & ””))))

Все, що ми зробили, - це формула ІСТЕКСТ (НАЗВА) як чисельник. Він повертає TRUE, якщо клітинка містить текст, і FALSE, якщо його немає. Він не буде рахувати порожні клітинки, але буде рахувати клітинки з порожнім рядком (“”).

Як порахувати унікальні значення в Excel, які є числовими

Ось формула, яка буде рахувати унікальні числові значення в Excel

= SUMPRODUCT ((ISNUMBER (NAMES))/COUNTIF (NAMES, NAMES & ””))

Тут ми використовуємо ISNUMBER (NAMES) як чисельник. Він повертає TRUE, якщо комірка містить числовий тип даних, і FALSE, якщо його немає. Він не враховує порожні клітинки.