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

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

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

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

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

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

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

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

=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.

Порада:

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

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

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

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

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

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

=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, застосуйте таку формулу:

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

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

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