Подсчет уникальных значений с критериями в Excel
Чтобы подсчитать только уникальные значения на основе заданных критериев в другом столбце, вы можете применить формулу массива, основанную на функциях СУММ, ЧАСТОТА, ПОИСКПОЗ и СТРОКА. Это пошаговое руководство поможет вам справиться с самым нервным использованием формулы.
Как подсчитать уникальные значения с критериями в Excel?
Как показано в приведенной ниже таблице продуктов, есть несколько дублирующихся продуктов, проданных в одном магазине в разные даты, теперь я хочу получить уникальное количество продуктов, проданных в магазине A, вы можете применить приведенную ниже формулу.
Общие формулы
{=SUM(--(FREQUENCY(IF(range=criteria,MATCH(vals,vals,0)),ROW(vals)-ROW(vals.firstcell)+1)>0))}
аргументы
Примечание. Эта формула должна быть введена как формула массива. Если после применения формулы вокруг нее заключены фигурные скобки, формула массива будет успешно создана.
Как пользоваться этими формулами?
1. Выберите пустую ячейку, чтобы разместить результат.
2. Введите в нее приведенную ниже формулу и нажмите Ctrl + Shift + Enter клавиши одновременно, чтобы получить результат.
=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)):
- 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}): Теперь для каждого ИСТИННОГО значения в массиве 1 мы получим соответствующую позицию в массиве 2, а для FALSE мы получим FALSE. Здесь вы получите новый массив как {1; FALSE; FALSE; 2; FALSE; FALSE; 3; FALSE; FALSE; 1; FALSE; FALSE; 3; FALSE}.
- СТРОКА (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}.
- 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}): Здесь функция ЧАСТОТА возвращает частоту каждого числа в данном массиве: {2; 1; 2; 0; 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)):
Связанные функции
Функция СУММ в Excel
Функция СУММ в Excel складывает значения
ЧАСТОТА в Excel
Функция ЧАСТОТА Excel вычисляет, как часто значения встречаются в диапазоне значений, а затем возвращает вертикальный массив чисел.
Функция ЕСЛИ в Excel
Функция Excel ЕСЛИ выполняет простой логический тест, который зависит от результата сравнения, и возвращает одно значение, если результат ИСТИНА, или другое значение, если результат ЛОЖЬ.
Функция ПОИСКПОЗ в Excel
Функция ПОИСКПОЗ в Excel ищет определенное значение в диапазоне ячеек и возвращает относительное положение этого значения.
Функция СТРОКА в Excel
Функция Excel ROW возвращает номер строки ссылки.
Родственные формулы
Подсчитать количество видимых строк в отфильтрованном списке
В этом руководстве объясняется, как подсчитать количество видимых строк в отфильтрованном списке в Excel с помощью функции ПРОМЕЖУТОЧНЫЙ ИТОГ.
Подсчет уникальных значений в диапазоне
В этом руководстве объясняется, как подсчитывать только уникальные значения среди дубликатов в списке в Excel с указанными формулами.
Подсчет видимых строк с критериями
В этом руководстве подробно описаны шаги, которые помогут подсчитать видимые строки по критериям.
Используйте СЧЁТЕСЛИ для несмежного диапазона
В этом пошаговом руководстве показано, как использовать функцию countif для несмежного диапазона в Excel.
Лучшие инструменты для работы в офисе
Kutools for Excel - поможет вам выделиться из толпы
Kutools для Excel может похвастаться более чем 300 функциями, Гарантия того, что то, что вам нужно, находится на расстоянии одного клика...
Вкладка Office - включение чтения и редактирования с вкладками в Microsoft Office (включая Excel)
- Одна секунда для переключения между десятками открытых документов!
- Уменьшите количество щелчков мышью на сотни каждый день, попрощайтесь с рукой мыши.
- Повышает вашу продуктивность на 50% при просмотре и редактировании нескольких документов.
- Добавляет эффективные вкладки в Office (включая Excel), как в Chrome, Edge и Firefox.
