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