Перейти до основного матеріалу

Підраховуйте унікальні числові значення на основі критеріїв у Excel

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

Підраховуйте унікальні числові значення на основі критеріїв у Excel 2019, 2016 та раніших версіях

У Excel 2019 та попередніх версіях можна об’єднати функції SUM, FREQUENCY та IF, щоб створити формулу для підрахунку унікальних значень на основі критеріїв, загальний синтаксис:

{=SUM(--(FREQUENCY(IF(criteria_range=criteria,range),range)>0))}
Array formula, should press Ctrl + Shift + Enter keys together.
  • criteria_range: Діапазон клітинок, що відповідає вказаним вами критеріям;
  • criteria: Умова, на основі якої потрібно підрахувати унікальні значення;
  • range: Діапазон клітинок з унікальними значеннями для підрахунку.

Будь ласка, застосуйте формулу нижче до порожньої клітинки і натисніть Ctrl + Shift + Enter клавіші, щоб отримати правильний результат, дивіться скріншот:

=SUM(--(FREQUENCY(IF(A2:A12=E2,C2:C12),C2:C12)>0))

Пояснення формули:

=SUM(--(FREQUENCY(IF(A2:A12=E2,C2:C12),C2:C12)>0))

  • IF(A2:A12=E2,C2:C12): Ця функція IF повертає значення у стовпці C, якщо продукт у стовпці A-"Футболка", результатом є такий масив: {FALSE; 300; 500; FALSE; 400; FALSE; 300; FALSE; FALSE; НЕВІРНО; 350}.
  • FREQUENCY(IF(A2:A12=E2,C2:C12),C2:C12)= FREQUENCY({FALSE;300;500;FALSE;400;FALSE;300;FALSE;FALSE;FALSE;350},{200;300;500;350;400;450;300;550;200;260;350}): Функція FREQUENCE використовується для підрахунку кожного з числових значень у списку масивів та повернення результату таким чином: {0; 2; 1; 1; 1; 0; 0; 0; 0; 0; 0; 0} .
  • --(FREQUENCY(IF(A2:A12=E2,C2:C12),C2:C12)>0)=--({0;2;1;1;1;0;0;0;0;0;0;0}>0): Перевірте, чи кожне значення в масиві більше 0, і отримайте такий результат: {FALSE; TRUE; TRUE; TRUE; TRUE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE}. Потім подвійний від’ємний знак перетворює істини та хибність у значення 1 та 0, повертаючи такий масив: {0; 1; 1; 1; 1; 0; 0; 0; 0; 0; 0; 0}.
  • SUM(--(FREQUENCY(IF(A2:A12=E2,C2:C12),C2:C12)>0))=SUM({0;1;1;1;1;0;0;0;0;0;0;0}): Нарешті, використовуйте функцію SUM, щоб додати ці значення і отримати загальну кількість: 4.

Порада:

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

=SUM(--(FREQUENCY(IF((criteria,_range1=criteria1)* (criteria,_range2=criteria2)*…,range),range)>0))

Підраховуйте унікальні числові значення на основі критеріїв у Excel 365

У Excel 365 поєднання функцій ROWS, UNIQUE та FILTER може допомогти підрахувати унікальні числові значення на основі критеріїв, загальний синтаксис:

=ROWS(UNIQUE(FILTER(range,criteria_range=criteria)))
  • range: Діапазон клітинок з унікальними значеннями для підрахунку.
  • criteria_range: Діапазон клітинок, що відповідає вказаним вами критеріям;
  • criteria: Умова, на основі якої потрібно підрахувати унікальні значення;

Скопіюйте або введіть таку формулу в клітинку та натисніть Що натомість? Створіть віртуальну версію себе у ключ, щоб повернути результат, див. знімок екрана:

=ROWS(UNIQUE(FILTER(C2:C12,A2:A12=E2)))

Пояснення формули:

=ROWS(UNIQUE(FILTER(C2:C12,A2:A12=E2)))

  • A2: A12 = E2: Цей вираз перевіряє, чи існує значення в комірці E2 в діапазоні A2: A12, і отримує такий результат: {FALSE; TRUE; TRUE; FALSE; TRUE; FALSE; TRUE; FALSE; FALSE; FALSE; TRUE}.
  • FILTER(C2:C12,A2:A12=E2): Функція FREQUENCE використовується для підрахунку кожного з числових значень у списку масивів та повернення результату таким чином: {0; 2; 1; 1; 1; 0; 0; 0; 0; 0; 0; 0} .
  • UNIQUE(FILTER(C2:C12,A2:A12=E2))=UNIQUE({300;500;400;300;350}): Тут функція UNIQUE використовується для вилучення унікальних значень з масиву списку для отримання такого результату: {300; 500; 400; 350}.
  • ROWS(UNIQUE(FILTER(C2:C12,A2:A12=E2)))=ROWS({300;500;400;350}): Функція ROWS повертає кількість рядків на основі діапазону клітинок або масиву, тому результат: 4.

Порада:

1. Якщо відповідне значення не існує в діапазоні даних, ви отримаєте значення помилки. Щоб замінити значення помилки на 0, застосуйте таку формулу:

=IFERROR(ROWS(UNIQUE(FILTER(C2:C12,A2:A12=E2))), 0)

2. Щоб підрахувати унікальні значення на основі декількох умов, вам просто потрібно додати до формули інші критерії з таким символом *:

=ROWS(UNIQUE(FILTER(range,(criteria_range1=criteria1)* (criteria_range2=criteria2)*…)))

Використана відносна функція:

  • Сума:
  • Функція Excel SUM повертає суму поданих значень.
  • Частота:
  • Функція FREQUENCY обчислює частоту значень у діапазоні значень, а потім повертає вертикальний масив чисел.
  • РЯДИ:
  • Функція ROWS повертає кількість рядків у даному посиланні або масиві.
  • Унікальний:
  • Функція UNIQUE повертає список унікальних значень у списку або діапазоні.
  • ФІЛЬТР:
  • Функція ФІЛЬТР допомагає фільтрувати діапазон даних на основі визначених вами критеріїв.

Більше статей:

  • Порахуйте унікальні числові значення або дати в стовпці
  • Припустимо, у вас є список чисел, які містять деякі дублікати, тепер ви хочете підрахувати кількість унікальних значень або значення відображаються лише один раз у списку, як показано на скріншоті нижче. У цій статті ми розповімо про деякі корисні формули для швидкого та легкого вирішення цього завдання в Excel.
  • Порахуйте всі збіги / дублікати між двома стовпцями
  • Порівняння двох стовпців даних і підрахунок усіх збігів або дублікатів у двох стовпцях може бути звичайним завданням для більшості з нас. Наприклад, у вас є два стовпці імен, деякі імена відображаються як у першому, так і в другому стовпцях, тепер ви хочете підрахувати всі відповідні імена (збіги, розташовані в будь -якому місці двох стовпців) між двома стовпцями, як показано нижче на скріншоті, цей підручник представить деякі формули для досягнення цієї мети в Excel.
  • Кількість клітин дорівнює одному з багатьох значень
  • Припустимо, у мене є список продуктів у колонці А, тепер я хочу отримати загальну кількість конкретних продуктів Яблуко, Виноград та Лимон, які перераховані в діапазоні С4: С6 зі стовпця А, як показано на скріншоті нижче. Зазвичай у програмі Excel прості функції COUNTIF та COUNTIFS не працюватимуть у цьому сценарії. У цій статті я розповім про те, як швидко та легко вирішити цю роботу за допомогою комбінації функцій SUMPRODUCT та COUNTIF.

Найкращі інструменти для підвищення продуктивності офісу

Kutools для Excel - допомагає виділитися з натовпу

🤖 Kutools AI Aide: Революціонізуйте аналіз даних на основі: Інтелектуальне виконання   |  Згенерувати код  |  Створення спеціальних формул  |  Аналізуйте дані та створюйте діаграми  |  Викликати функції Kutools...
Популярні функції: Знайдіть, виділіть або визначте дублікати  |  Видалити порожні рядки  |  Об’єднайте стовпці або клітинки без втрати даних  |  Раунд без Формули ...
Супер VLookup: Кілька критеріїв  |  Множинне значення  |  На кількох аркушах  |  Нечіткий пошук...
Adv. Випадаючий список: Легкий спадний список  |  Залежний спадний список  |  Виберіть розкривний список, що вибирається...
Менеджер колонок: Додайте конкретну кількість стовпців  |  Перемістити стовпці  |  Перемкнути статус видимості прихованих стовпців  |  Порівняти стовпці з Виберіть однакові та різні клітини ...
Особливості: Фокус сітки  |  Перегляд дизайну  |  Велика панель формул  |  Диспетчер робочих книг і аркушів | Бібліотека ресурсів (автотекст)  |  Вибір дати  |  Об’єднайте робочі аркуші  |  Шифрування/розшифрування клітинок  |  Надсилайте листи за списком  |  Супер фільтр  |  Спеціальний фільтр (фільтр жирний/курсив/закреслений...) ...
Топ-15 наборів інструментів:  12 текст Tools (додати текст, Видалити символи ...)  |  50 + Графік типи (діаграма Ганта ...)  |  40+ Практичний Формули (Розрахуйте вік на основі дня народження ...)  |  19 вставка Tools (Вставте QR-код, Вставити зображення зі шляху ...)  |  12 Перетворення Tools (Числа до слів, Валютна конверсія ...)  |  7 Злиття та розділення Tools (Розширені комбіновані ряди, Розділити клітинки Excel ...)  |  ... і більше

Kutools для Excel має понад 300 функцій, Переконайтеся, що те, що вам потрібно, знаходиться лише на відстані одного кліка...

Опис

Вкладка Office - увімкніть читання та редагування вкладок у Microsoft Office (включаючи Excel)

  • Одна секунда для перемикання між десятками відкритих документів!
  • Щодня зменшуйте сотні клацань мишею, прощайте руку миші.
  • Збільшує вашу продуктивність на 50% під час перегляду та редагування декількох документів.
  • Додає ефективні вкладки в Office (включно з Excel), як у Chrome, Edge та Firefox.
Comments (0)
No ratings yet. Be the first to rate!
There are no comments posted here yet
Please leave your comments in English
Guest
Posting as Guest
×
Rate this post:
Reset
0   Characters
Suggested Locations