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

Чтобы подсчитать только уникальные значения на основе определенного критерия в другом столбце, вы можете применить формулу массива, основанную на функциях СУММ, ЧАСТОТА, СООТВ и СТРОКА. Это пошаговое руководство поможет вам разобраться с наиболее сложным использованием формулы.

Как подсчитать уникальные значения с критериями в Excel?

Как показано в таблице продуктов ниже, некоторые дублирующиеся продукты были проданы из одного и того же магазина в разные даты. Теперь я хочу получить количество уникальных продуктов, проданных магазином A. Для этого можно применить следующую формулу.

Общие формулы

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

Аргументы

Примечание: Эта формула должна быть введена как формула массива. После применения формулы, если вокруг неё появились фигурные скобки, значит формула массива успешно создана.

Как использовать эти формулы?

1. Выберите пустую ячейку для размещения результата.

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

=СУММ(--(ЧАСТОТА(ЕСЛИ(E3:E16=H3;СООТВ(D3:D16;D3:D16;0));СТРОКА(D3:D16)-СТРОКА(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))}

• ЕСЛИ(E3:E16=H3;СООТВ(D3:D16;D3:D16;0)):

• ЕСЛИ({ИСТИНА;ЛОЖЬ;ЛОЖЬ;ИСТИНА;ЛОЖЬ;ЛОЖЬ;ИСТИНА;ЛОЖЬ;ЛОЖЬ;ИСТИНА;ЛОЖЬ;};{1;2;3;2;1;1;3;2;1;1;1;2;3;2}): Теперь для каждого значения ИСТИНА в массиве 1 мы получим соответствующее положение в массиве 2, а для ЛОЖЬ — ЛОЖЬ. Здесь вы получите новый массив, например, {1;ЛОЖЬ;ЛОЖЬ;2;ЛОЖЬ;ЛОЖЬ;3;ЛОЖЬ;ЛОЖЬ;1;ЛОЖЬ;ЛОЖЬ;3;ЛОЖЬ}.
• СТРОКА(D3:D16)-СТРОКА(D3)+1: Здесь функция СТРОКА возвращает номер строки ссылки 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}.
• ЧАСТОТА({1;ЛОЖЬ;ЛОЖЬ;2;ЛОЖЬ;ЛОЖЬ;3;ЛОЖЬ;ЛОЖЬ;1;ЛОЖЬ;ЛОЖЬ;3;ЛОЖЬ};{1;2;3;4;5;6;7;8;9;10;11;12;13;14}): Здесь функция ЧАСТОТА возвращает частоту каждого числа в заданном массиве: {2;1;2;0;0;0;0;0;0;0;0;0;0;0}.
• =СУММ(--({2;1;2;0;0;0;0;0;0;0;0;0;0;0}>0)):

Связанные функции

Связанные формулы