Перейти к основному содержанию

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

На листе Excel вы можете столкнуться с проблемой, связанной с подсчетом количества уникальных числовых значений на основе определенного условия. Например, как я могу подсчитать уникальные значения Qty продукта «Футболка» из отчета, как показано на скриншоте ниже? В этой статье я покажу несколько формул для решения этой задачи в Excel.


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

В Excel 2019 и более ранних версиях вы можете комбинировать функции СУММ, ЧАСТОТА и ЕСЛИ, чтобы создать формулу для подсчета уникальных значений на основе критериев, общий синтаксис:

{=SUM(--(FREQUENCY(IF(criteria_range=criteria,range),range)>0))}
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))


Пояснение к формуле:

=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.

Советы:

Если вы хотите подсчитать уникальные значения на основе более чем одного условия, вам просто нужно добавить в формулу другие критерии с символом *:

=SUM(--(FREQUENCY(IF((criteria,_range1=criteria1)* (criteria,_range2=criteria2)*…,range),range)>0))

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

В Excel 365 комбинация функций СТРОКА, УНИКАЛЬНЫЙ и ФИЛЬТР может помочь подсчитать уникальные числовые значения на основе критериев, общий синтаксис:

=ROWS(UNIQUE(FILTER(range,criteria_range=criteria)))
  • range: Диапазон ячеек с уникальными значениями для подсчета.
  • criteria_range: Диапазон ячеек, соответствующих указанным вами критериям;
  • criteria: Условие, на основании которого вы хотите подсчитать уникальные значения;

Скопируйте или введите следующую формулу в ячейку и нажмите Enter ключ для возврата результата, см. снимок экрана:

=ROWS(UNIQUE(FILTER(C2:C12,A2:A12=E2)))


Пояснение к формуле:

=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, примените следующую формулу:

=IFERROR(ROWS(UNIQUE(FILTER(C2:C12,A2:A12=E2))), 0)

2. Чтобы подсчитать уникальные значения на основе более чем одного условия, вам просто нужно добавить в формулу другие критерии с символом *, например:

=ROWS(UNIQUE(FILTER(range,(criteria_range1=criteria1)* (criteria_range2=criteria2)*…)))

Используемая относительная функция:

  • СУММА:
  • Функция СУММ в Excel возвращает сумму предоставленных значений.
  • ЧАСТОТА:
  • Функция ЧАСТОТА вычисляет, как часто значения встречаются в диапазоне значений, а затем возвращает вертикальный массив чисел.
  • РЯДЫ:
  • Функция ROWS возвращает количество строк в данной ссылке или массиве.
  • УНИКАЛЬНЫЙ:
  • Функция UNIQUE возвращает список уникальных значений в списке или диапазоне.
  • ФИЛЬТР:
  • Функция ФИЛЬТР помогает фильтровать диапазон данных на основе определенных вами критериев.

Другие статьи:

  • Подсчитайте уникальные числовые значения или даты в столбце
  • Предположим, у вас есть список чисел, которые содержат несколько дубликатов, теперь вы хотите подсчитать количество уникальных значений, иначе значения появляются в списке только один раз, как показано на скриншоте ниже. В этой статье мы расскажем о некоторых полезных формулах для быстрого и простого решения этой задачи в Excel.
  • Подсчитать все совпадения / дубликаты между двумя столбцами
  • Сравнение двух столбцов данных и подсчет всех совпадений или дубликатов в двух столбцах может быть обычной задачей для большинства из нас. Например, у вас есть два столбца с именами, некоторые имена появляются как в первом, так и во втором столбцах, теперь вы хотите подсчитать все совпадающие имена (совпадения, расположенные в любом месте в двух столбцах) между двумя столбцами, как показано на скриншоте ниже, этот учебник представит некоторые формулы для достижения этой цели в Excel.
  • Подсчитать количество ячеек равно одному из многих значений
  • Предположим, у меня есть список продуктов в столбце A, теперь я хочу получить общее количество конкретных продуктов Apple, Grape и Lemon, которые перечислены в диапазоне C4: C6 из столбца A, как показано на скриншоте ниже. Обычно в Excel простые функции СЧЁТЕСЛИ и СЧЁТЕСЛИМН не работают в этом сценарии. В этой статье я расскажу о том, как быстро и легко решить эту задачу с помощью комбинации функций СУММПРОИЗВ и СЧЁТЕСЛИ.

Лучшие инструменты для работы в офисе

Kutools for Excel - поможет вам выделиться из толпы

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

Kutools для Excel может похвастаться более чем 300 функциями, Гарантия того, что то, что вам нужно, находится на расстоянии одного клика...


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

  • Одна секунда для переключения между десятками открытых документов!
  • Уменьшите количество щелчков мышью на сотни каждый день, попрощайтесь с рукой мыши.
  • Повышает вашу продуктивность на 50% при просмотре и редактировании нескольких документов.
  • Добавляет эффективные вкладки в Office (включая Excel), как в Chrome, Edge и Firefox.
Comments (0)
No ratings yet. Be the first to rate!
There are no comments posted here yet
Please leave your comments in English
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations