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

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

Для підрахунку лише унікальних значень на основі зазначених критеріїв в іншому стовпці можна застосувати формулу масиву на основі функцій SUM, FREQUENCY, MATCH та ROW. Цей покроковий посібник допоможе вам максимально зручно використовувати формулу.


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

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

Загальні формули

{=SUM(--(FREQUENCY(IF(range=criteria,MATCH(vals,vals,0)),ROW(vals)-ROW(vals.firstcell)+1)>0))}

Аргументи

Діапазон: Діапазон клітинок містить значення, яке відповідає критеріям;
Критерії: Критерії, на основі яких потрібно підраховувати унікальні значення;
вальс: Діапазон клітинок, з яких потрібно підрахувати унікальні значення;
Vals.перша клітина: Перша клітинка діапазону, з якої потрібно підрахувати унікальні значення.

Примітка: Ця формула повинна бути введена як формула масиву. Після застосування формули, якщо фігурні дужки обгорнуті навколо формули, формула масиву успішно створена.

Як користуватися цими формулами?

1. Виберіть порожню комірку, щоб розмістити результат.

2. Введіть формулу нижче, а потім натисніть Ctrl + Shift + Що натомість? Створіть віртуальну версію себе у клавіші одночасно, щоб отримати результат.

=SUM(--(FREQUENCY(IF(E3:E16=H3,MATCH(D3:D16,D3:D16,0)),ROW(D3:D16)-ROW(D3)+1)>0))

примітки: У цій формулі E3: E16 - це діапазон, що містить значення, яке проти критеріїв, H3 містить критерії, D3: D16 - діапазон містить унікальні значення, які потрібно підрахувати, а D3 - перша клітинка D3: D16. Ви можете змінити їх, як вам потрібно.

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

{=SUM(--(FREQUENCY(IF(E3:E16=H3,MATCH(D3:D16,D3:D16,0)),ROW(D3:D16)-ROW(D3)+1)>0))}

  • IF(E3:E16=H3,MATCH(D3:D16,D3:D16,0)):
1) E3: E16 = H3: Тут перевірте, чи існує значення A в діапазоні E3: E16, і повертає TRUE, якщо воно знайдене, повертає FALSE, якщо ні. Ви отримаєте такий масив {TRUE; FALSE; FALSE; TRUE; FALSE; FALSE; TRUE; FALSE; FALSE; TRUE; FALSE;}.
2) МАТЧ (D3: D16, D3: D16,0): Функція MATCH отримує перше розташування кожного елемента в діапазоні D3: D16 і повертає такий масив {1; 2; 3; 2; 1; 1; 3; 2; 1; 1; 1; 2; 3; 2}.
  • IF({TRUE;FALSE;FALSE;TRUE;FALSE;FALSE;TRUE;FALSE;FALSE;TRUE;FALSE;},{1;2;3;2;1;1;3;2;1;1;1;2;3;2}): Тепер для кожного значення TRUE у масиві 1 ми отримаємо відповідну позицію в масиві 2, а для FALSE - FALSE. Тут ви отримаєте новий масив як {1; FALSE; FALSE; 2; FALSE; FALSE; 3; FALSE; FALSE; 1; FALSE; FALSE; 3; FALSE}.
  • РЯД (D3: D16) -РОК (D3) +1: Тут функція ROW повертає номер рядка посилання D3: D16 і D3, і ви отримаєте {3; 4; 5; 6; 7; 8; 9; 10; 11; 12; 13; 14; 15; 16} -{3} +1.
  • Кожне число в масиві віднімає число 3, потім додає 1 і, нарешті, повертає {1; 2; 3; 4; 5; 6; 7; 8; 9; 10; 11; 12; 13; 14}.
  • FREQUENCY({1;FALSE;FALSE;2;FALSE;FALSE;3;FALSE;FALSE;1;FALSE;FALSE;3;FALSE},{1;2;3;4;5;6;7;8;9;10;11;12;13;14}): Тут функція FREQUENCY повертає частоту кожного числа у даному масиві: {2; 1; 2; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0}.
  • =SUM(--({2;1;2;0;0;0;0;0;0;0;0;0;0;0}>0)):
1) {2;1;2;0;0;0;0;0;0;0;0;0;0;0}>0: Кожне число в масиві порівнюється з 0 і повертає TRUE, якщо більше 0, інакше повертає FALSE. І ви отримаєте такий масив TRUE FALSE: {TRUE; TRUE; TRUE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE};
2) -{TRUE; TRUE; TRUE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE}: Ці два знаки мінус перетворюють “TRUE” на 1, а “FALSE” на 0. Тут ви отримаєте новий масив як {1; 1; 1; 0; 0; 0; 0; 0; 0; 0; 0 ; 0; 0}.
3) SUM{1;1;1;0;0;0;0;0;0;0;0;0;0;0}: Функція SUM підсумовує всі числа в масиві та повертає кінцевий результат як 3.

Супутні функції

Функція Excel SUM
Функція Excel SUM додає значення

Функція Excel ЧАСТОТА
Функція Excel ЧАСТОТА обчислює частоту значень у діапазоні значень, а потім повертає вертикальний масив чисел.

Функція Excel IF
Функція Excel IF виконує простий логічний тест, який залежно від результату порівняння повертає одне значення, якщо результат ІСТИНА, або інше значення, якщо результат ФАЛЬШЕ.

Функція Excel MATCH
Функція Excel MATCH здійснює пошук певного значення в діапазоні комірок і повертає відносне положення цього значення.

Функція Excel ROW
Функція Excel ROW повертає номер рядка посилання.


Пов’язані формули

Підрахувати кількість видимих ​​рядків у відфільтрованому списку
У цьому посібнику пояснюється, як підрахувати кількість видимих ​​рядків у відфільтрованому списку в Excel за допомогою функції SUBTOTAL.

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

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

Використовуйте COUNTIF для несуміжного діапазону
Цей покроковий посібник демонструє, як використовувати функцію coutif у несуміжному діапазоні в Excel.


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

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 (1)
No ratings yet. Be the first to rate!
This comment was minimized by the moderator on the site
Looking for the same formula but with one more criteria... I tried adding AND() after the IF() to have my two criterias but it didn't work. Do you have a solution?
There are no comments posted here yet
Please leave your comments in English
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations