Note: The other languages of the website are Google-translated. Back to English

Підраховуйте унікальні числові значення на основі критеріїв у 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 для Excel надає 300 потужних розширених функцій (об’єднання робочих книг, підсумовування за кольором, розділення вмісту клітинок, перетворення дати тощо) і заощаджує 80% вашого часу.

  • Розроблений для 1500 сценаріїв роботи, допомагає вирішити 80% проблем Excel.
  • Щодня зменшуйте тисячі клацань клавіатури та миші, знімайте втомлені очі та руки.
  • Станьте експертом Excel за 3 хвилини. Більше не потрібно згадувати будь-які болісні формули та коди VBA.
  • Безкоштовна 30-денна безкоштовна пробна версія. 60-денна гарантія повернення грошей. Безкоштовне оновлення та підтримка протягом 2 років.
Стрічка Excel (із встановленим Kutools для Excel)

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

  • Одна секунда для перемикання між десятками відкритих документів!
  • Щодня зменшуйте сотні клацань мишею, прощайте руку миші.
  • Збільшує вашу продуктивність на 50% під час перегляду та редагування декількох документів.
  • Приносить ефективні вкладки в Office (включаючи Excel), як і Chrome, Firefox та New Internet Explorer.
Знімок екрана Excel (із встановленою вкладкою Office)
Коментарі (0)
Поки немає оцінок. Оцініть першим!
There are no comments posted here yet
Залишайте свої коментарі
Публікація як гість
×
Оцініть цю публікацію:
0   Персонажі
Рекомендовані місця

Слідуй за нами

Copyright © 2009 - WWW.extendoffice.com. | Всі права захищені. На основі ExtendOffice. | Карта сайту
Microsoft та логотип Office є товарними знаками або зареєстрованими товарними знаками Microsoft Corporation у США та / або інших країнах.
Захищений Sectigo SSL