Подсчет уникальных числовых значений на основе критериев в Excel
В рабочем листе Excel вам может понадобиться решить задачу подсчета количества уникальных числовых значений на основе определенного условия. Например, как можно подсчитать уникальные значения количества (Qty) для продукта «Футболка» из отчета, как показано на скриншоте ниже? В этой статье я покажу несколько формул для выполнения этой задачи в Excel.
- Подсчет уникальных числовых значений на основе критериев в Excel 2019, 2016 и более ранних версиях
- Подсчет уникальных числовых значений на основе критериев в Excel 365
Подсчет уникальных числовых значений на основе критериев в Excel 2019, 2016 и более ранних версиях
В Excel 2019 и более ранних версиях вы можете объединить функции СУММ, ЧАСТОТА и ЕСЛИ для создания формулы подсчета уникальных значений на основе критериев. Общий синтаксис следующий:
Массивная формула, следует нажать клавиши Ctrl + Shift + Enter вместе.
- criteria_range: Диапазон ячеек для соответствия указанным критериям;
- criteria: Условие, на основе которого вы хотите подсчитать уникальные значения;
- range: Диапазон ячеек с уникальными значениями, которые нужно подсчитать.
Пожалуйста, примените следующую формулу в пустую ячейку и нажмите клавиши Ctrl + Shift + Enter, чтобы получить правильный результат, см. скриншот:
Объяснение формулы:
=СУММ(--(ЧАСТОТА(ЕСЛИ(A2:A12=E2,C2:C12),C2:C12)>0))
- ЕСЛИ(A2:A12=E2,C2:C12): Эта функция ЕСЛИ возвращает значение в столбце C, если продукт в столбце A — «Футболка», результат будет массивом, например: {ЛОЖЬ;300;500;ЛОЖЬ;400;ЛОЖЬ;300;ЛОЖЬ;ЛОЖЬ;ЛОЖЬ;350}.
- ЧАСТОТА(ЕСЛИ(A2:A12=E2,C2:C12),C2:C12)= ЧАСТОТА({ЛОЖЬ;300;500;ЛОЖЬ;400;ЛОЖЬ;300;ЛОЖЬ;ЛОЖЬ;ЛОЖЬ;350},{200;300;500;350;400;450;300;550;200;260;350}): Функция ЧАСТОТА используется для подсчета каждого из числовых значений в списке массивов и возвращает результат следующим образом: {0;2;1;1;1;0;0;0;0;0;0;0}.
- --(ЧАСТОТА(ЕСЛИ(A2:A12=E2,C2:C12),C2:C12)>0)=--({0;2;1;1;1;0;0;0;0;0;0;0}>0): Проверяет, больше ли каждое значение в массиве 0, и получает результат следующим образом: {ЛОЖЬ;ИСТИНА;ИСТИНА;ИСТИНА;ИСТИНА;ЛОЖЬ;ЛОЖЬ;ЛОЖЬ;ЛОЖЬ;ЛОЖЬ;ЛОЖЬ;ЛОЖЬ}. Затем двойной знак минус преобразует ИСТИНУ и ЛОЖЬ в 1 и 0, возвращая массив следующего вида: {0;1;1;1;1;0;0;0;0;0;0;0}.
- СУММ(--(ЧАСТОТА(ЕСЛИ(A2:A12=E2,C2:C12),C2:C12)>0))=СУММ({0;1;1;1;1;0;0;0;0;0;0;0}): Наконец, используйте функцию СУММ, чтобы сложить эти значения и получить итоговое число: 4.
Советы:
Если вы хотите подсчитать уникальные значения на основе нескольких условий, просто добавьте другие критерии в формулу с помощью символа *:
Подсчет уникальных числовых значений на основе критериев в Excel 365
В Excel 365 комбинация функций СТРОКИ, УНИКАЛЬНЫЕ и ФИЛЬТР может помочь подсчитать уникальные числовые значения на основе критериев. Общий синтаксис следующий:
- range: Диапазон ячеек с уникальными значениями, которые нужно подсчитать.
- criteria_range: Диапазон ячеек для соответствия указанным критериям;
- criteria: Условие, на основе которого вы хотите подсчитать уникальные значения;
Пожалуйста, скопируйте или введите следующую формулу в ячейку и нажмите клавишу Enter, чтобы получить результат, см. скриншот:
Объяснение формулы:
=СТРОКИ(УНИКАЛЬНЫЕ(ФИЛЬТР(C2:C12,A2:A12=E2)))
- A2:A12=E2: Это выражение проверяет, существует ли значение в ячейке E2 в диапазоне A2:A12, и возвращает такой результат: {ЛОЖЬ;ИСТИНА;ИСТИНА;ЛОЖЬ;ИСТИНА;ЛОЖЬ;ИСТИНА;ЛОЖЬ;ЛОЖЬ;ЛОЖЬ;ИСТИНА}.
- ФИЛЬТР(C2:C12,A2:A12=E2): Функция ЧАСТОТА используется для подсчета каждого из числовых значений в списке массивов и возвращает результат следующим образом: {0;2;1;1;1;0;0;0;0;0;0;0}.
- УНИКАЛЬНЫЕ(ФИЛЬТР(C2:C12,A2:A12=E2))=УНИКАЛЬНЫЕ({300;500;400;300;350}): Здесь функция УНИКАЛЬНЫЕ используется для извлечения уникальных значений из списка массивов, чтобы получить такой результат: {300;500;400;350}.
- СТРОКИ(УНИКАЛЬНЫЕ(ФИЛЬТР(C2:C12,A2:A12=E2)))=СТРОКИ({300;500;400;350}): Функция СТРОКИ возвращает количество строк на основе диапазона ячеек или массива, поэтому результат равен: 4.
Советы:
1. Если совпадающее значение не существует в диапазоне данных, вы получите ошибочное значение. Чтобы заменить ошибочное значение на 0, пожалуйста, примените следующую формулу:
2. Для подсчета уникальных значений на основе нескольких условий просто добавьте другие критерии в формулу с помощью символа *, например:
Используемые связанные функции:
- СУММ:
- Функция СУММ в Excel возвращает сумму предоставленных значений.
- ЧАСТОТА:
- Функция ЧАСТОТА вычисляет, как часто значения встречаются в диапазоне значений, а затем возвращает вертикальный массив чисел.
- СТРОКИ:
- Функция СТРОКИ возвращает количество строк в заданной ссылке или массиве.
- УНИКАЛЬНЫЕ:
- Функция УНИКАЛЬНЫЕ возвращает список уникальных значений в списке или диапазоне.
- ФИЛЬТР:
- Функция ФИЛЬТР помогает фильтровать диапазон данных на основе определенных критериев.
Дополнительные статьи:
- Подсчет уникальных числовых значений или дат в столбце
- Предположим, у вас есть список чисел, содержащих некоторые дубликаты, и вы хотите подсчитать количество уникальных значений или значения, которые встречаются только один раз в списке, как показано на скриншоте ниже. В этой статье мы поговорим о некоторых полезных формулах для быстрого и легкого решения этой задачи в Excel.
- Подсчет всех совпадений / дубликатов между двумя столбцами
- Сравнение двух столбцов данных и подсчет всех совпадений или дубликатов в этих двух столбцах может быть обычной задачей для большинства из нас. Например, у вас есть два столбца имен, некоторые имена присутствуют как в первом, так и во втором столбцах, и вы хотите подсчитать все совпадающие имена (совпадения могут находиться где угодно в двух столбцах), как показано на скриншоте ниже. В этом руководстве мы представим несколько формул для достижения этой цели в Excel.
- Подсчет количества ячеек, равных одному из многих значений
- Предположим, у меня есть список продуктов в столбце A, и теперь я хочу получить общее количество конкретных продуктов Apple, Grape и Lemon, перечисленных в диапазоне C4:C6, из столбца A, как показано на скриншоте ниже. Обычно простые функции СЧЁТЕСЛИ и СЧЁТЕСЛИМН в Excel не будут работать в этом случае. В этой статье я расскажу, как быстро и легко решить эту задачу с помощью комбинации функций СУММПРОИЗВ и СЧЁТЕСЛИ.
Лучшие инструменты для повышения производительности Office
Kutools для Excel - Помогает вам выделиться из толпы
Kutools для Excel имеет более 300 функций, гарантируя, что то, что вам нужно, находится всего в одном клике...
Office Tab - Включите работу с вкладками в Microsoft Office (включая Excel)
- Одна секунда для переключения между десятками открытых документов!
- Уменьшите сотни кликов мышью каждый день, попрощайтесь с болью в руке от использования мышки.
- Увеличивает вашу продуктивность на 50% при просмотре и редактировании нескольких документов.
- Привносит эффективные вкладки в Office (включая Excel), как в Chrome, Edge и Firefox.