KutoolsforOffice — Одно решение — пять мощных инструментов.Меньше усилий — больше результата.

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

АвторSiluviaДата изменения

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

doc-count-unique-with-criteria-1

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

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

doc-count-unique-with-criteria-2

Универсальные формулы

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

Аргументы

Диапазон: Диапазон ячеек, содержащий значения для проверки по критерию;
Критерий: Критерий, по которому вы хотите Подсчитать количество уникальных значений в диапазоне;
Vals: Диапазон ячеек, из которого вы хотите Подсчитать количество уникальных значений в диапазоне;
Vals.firstcell: Первая ячейка диапазона, из которого вы хотите Подсчитать количество уникальных значений в диапазоне.

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

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

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

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

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

doc-count-unique-with-criteria-3

Примечания: В этой формуле E3:E16 — диапазон со значениями для проверки по критерию, H3 — ячейка с критерием, D3:D16 — диапазон с уникальными значениями, которые нужно подсчитать, а D3 — первая ячейка этого диапазона. Вы можете изменить их по своему усмотрению.

Как работает эта формула?

{=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, и возвращается ИСТИНА, если оно найдено, и ЛОЖЬ — если нет. Вы получите массив следующего вида: {ИСТИНА;ЛОЖЬ;ЛОЖЬ;ИСТИНА;ЛОЖЬ;ЛОЖЬ;ИСТИНА;ЛОЖЬ;ЛОЖЬ;ИСТИНА;ЛОЖЬ;}.
2)MATCH(D3:D16,D3:D16,0): Функция ПОИСКПОЗ определяет первую позицию каждого элемента в диапазоне 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}): Теперь для каждого значения ИСТИНА в первом массиве вы получите соответствующий элемент из второго массива, а для значений ЛОЖЬ — значение ЛОЖЬ. В итоге сформируется новый массив: {1;ЛОЖЬ;ЛОЖЬ;2;ЛОЖЬ;ЛОЖЬ;3;ЛОЖЬ;ЛОЖЬ;1;ЛОЖЬ;ЛОЖЬ;3;ЛОЖЬ}.
  • ROW(D3:D16)-ROW(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}.
  • =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 и возвращает ИСТИНА, если больше 0, в противном случае — ЛОЖЬ. В результате вы получите массив ИСТИНА/ЛОЖЬ следующего вида: {ИСТИНА;ИСТИНА;ИСТИНА;ЛОЖЬ;ЛОЖЬ;ЛОЖЬ;ЛОЖЬ;ЛОЖЬ;ЛОЖЬ;ЛОЖЬ;ЛОЖЬ;ЛОЖЬ;ЛОЖЬ;ЛОЖЬ};
2)--{ИСТИНА;ИСТИНА;ИСТИНА;ЛОЖЬ;ЛОЖЬ;ЛОЖЬ;ЛОЖЬ;ЛОЖЬ;ЛОЖЬ;ЛОЖЬ;ЛОЖЬ;ЛОЖЬ;ЛОЖЬ;ЛОЖЬ}: Эти два знака минус преобразуют «ИСТИНА» в 1, а «ЛОЖЬ» — в 0. В результате вы получите новый массив: {1;1;1;0;0;0;0;0;0;0;0;0;0;0}.
3)СУММ{1;1;1;0;0;0;0;0;0;0;0;0;0;0}: Функция СУММ суммирует все числа в массиве и возвращает окончательный результат: 3.

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

Функция СУММ Excel
Функция СУММ Excel суммирует значения

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

Функция ЕСЛИ в Excel
Функция ЕСЛИ в Excel выполняет простую логическую проверку и возвращает одно значение, если результат — ИСТИНА, и другое — если ЛОЖЬ.

Функция ПОИСКПОЗ Excel
Функция ПОИСКПОЗ в Excel ищет заданное значение в диапазоне ячеек и возвращает его относительную позицию.

Функция СТРОКА Excel
Функция СТРОКА в Excel возвращает номер строки по указанной ссылке.

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

Подсчёт количества видимых строк в отфильтрованном списке
В этом руководстве рассказывается, как с помощью функции ПРОМЕЖУТОЧНЫЕ.ИТОГИ подсчитать количество видимых строк в отфильтрованном списке Excel.

Подсчитать количество уникальных значений в диапазоне
В этом руководстве объясняется, как подсчитать только уникальные значения среди дубликатов в списке Excel с помощью приведённых формул.

Подсчёт видимых строк с критериями
В этом руководстве — пошаговые инструкции, которые помогут вам подсчитать видимые строки с учётом заданных критериев.

Использование СЧЁТЕСЛИ для несмежных диапазонов
В этом пошаговом руководстве показано, как использовать функцию СЧЁТЕСЛИ с несмежными диапазонами в Excel.

Лучшие инструменты для повышения продуктивности в Office

Kutools для Excel — Помогает вам выделиться из толпы

🤖KUTOOLS AI Помощник: Революционизируйте Анализ данных на основе:Интеллектуальное выполнение   |  Генерация кода|  Создание пользовательские формулы  |  Анализ данных и создание диаграмм|  Вызов Расширенные функции…
Популярные функции:Поиск, выделение или Отметить дубликаты  |  Удалить пустые строки  |  Объединить столбцы или ячейки без потери данных  |  Округление без использования формул…
Расширенный VLookup:Несколько критериев  |  Несколько значений  |  По нескольким листам  |  Распознавание нечетких соответствий…
Расш. Раскрывающийся список:Простой выпадающий список  |  Зависимый выпадающий список  |  Многоэлементный выпадающий список…
Управление столбцами:Добавление определённого количества столбцов  |  Перемещение столбцов  |  Переключение видимости скрытых столбцов  |Сравнение столбцов для Выбрать одинаковые/разные ячейки…
Избранные функции:Сетка фокусировки  |  Просмотр дизайна  |  Улучшенная строка формулы  |  Управление рабочими книгами и листами|Библиотека ресурсов(автотекст)|  Выбор даты  |  Объединить листы  |  Шифрование/Расшифровать ячейки  |  Отправка писем по списку  |  Супер фильтр  |  Специальный фильтр(Фильтр ячеек с жирным шрифтом/курсив/зачёркивание…) …
Лучшие наборы инструментов 15:12 Текстовыеинструменты(Добавить текст,Удалить определенные символы…)|  50+Типыдиаграмм(Диаграмма Ганта…)|  40+ Практические формулы(Рассчитать возраст на основе даты рождения…)|  19 Инструментывставки(Вставить QR-код,Вставка изображения по пути…)|  12 Инструментыпреобразования(Преобразовать в слова,Конвертация валют…)|  7 Объединить и разделитьинструменты(Расширенное объединение строк,Разделение ячеек Excel…)|…и многое другое
Используйте Kutools на предпочитаемом языке — поддерживает английский, испанский, немецкий, французский, китайский и ещё 40+ языков!

Kutools для Excel предлагает более 300 функций,гарантируя, что всё необходимое всегда под рукой…

Office Tab — Включает вкладки для чтения и редактирования в Microsoft Office (включая Excel)

  • Переключайтесь между десятками открытых документов всего за секунду!
  • Сократите сотни кликов мышью каждый день и забудьте о «мышечной руке».
  • Повышает продуктивность на 50 % при одновременном просмотре и редактировании нескольких документов.
  • Добавляет удобство работы с вкладками в Office (включая Excel) — как в Chrome, Edge и Firefox.