Note: The other languages of the website are Google-translated. Back to English
Войти  \/ 
x
or
x
Регистрация  \/ 
x

or

Подсчет уникальных числовых значений на основе критериев в 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 - поможет вам выделиться из толпы

Хотите быстро и безупречно выполнять свою повседневную работу? Kutools for Excel предлагает мощные расширенные функции 300 (объединение книг, сумма по цвету, разделение содержимого ячеек, дата преобразования и так далее ...) и экономия 80% времени для вас.

  • Рассчитан на 1500 сценариев работы, помогает решить 80% задач Excel.
  • Уменьшите количество нажатий на клавиатуру и мышь каждый день, избавьтесь от усталости глаз и рук.
  • Станьте экспертом по Excel за 3 минуты. Больше не нужно запоминать какие-либо болезненные формулы и коды VBA.
  • 30-дневная неограниченная бесплатная пробная версия. 60-дневная гарантия возврата денег. Бесплатное обновление и поддержка 2 года.
Лента Excel (с установленным Kutools for Excel)

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

  • Одна секунда для переключения между десятками открытых документов!
  • Уменьшите количество щелчков мышью на сотни каждый день, попрощайтесь с рукой мыши.
  • Повышает вашу продуктивность на 50% при просмотре и редактировании нескольких документов.
  • Добавляет эффективные вкладки в Office (включая Excel), точно так же, как Chrome, Firefox и новый Internet Explorer.
Снимок экрана Excel (с установленной вкладкой Office)
Say something here...
symbols left.
You are guest
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.

Be the first to comment.