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

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

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

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

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

• criteria_range: Диапазон ячеек для сравнения с указанным условием;
• criteria: Условие, по которому вы хотите выполнить Подсчитать количество уникальных значений в диапазоне;
• range: диапазон ячеек с уникальными значениями, которые нужно подсчитать.

Введите приведённую ниже формулу в пустую ячейку и нажмите Ctrl + Shift + Enter, чтобы получить правильный результат (см. снимок экрана):

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

=SUM(--(FREQUENCY(IF(A2:A12=E2,C2:C12),C2:C12)>,0))

• IF(A2:A12=E2,C2:C12): Эта функция ЕСЛИ возвращает значения из столбца C, где в столбце A указано «Футболка». В результате получается массив вида: {ЛОЖЬ;300;500;ЛОЖЬ;400;ЛОЖЬ;300;ЛОЖЬ;ЛОЖЬ;ЛОЖЬ;350}.
• ЧАСТОТА(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}.
• --(ЧАСТОТА(IF(A2:A12=E2,C2:C12);C2:C12)>0)=--({0;2;1;1;1;0;0;0;0;0;0;0}>0): Проверяется, является ли каждое значение в массиве больше нуля, в результате чего получается следующий массив: {ЛОЖЬ;ИСТИНА;ИСТИНА;ИСТИНА;ИСТИНА;ЛОЖЬ;ЛОЖЬ;ЛОЖЬ;ЛОЖЬ;ЛОЖЬ;ЛОЖЬ;ЛОЖЬ}. Затем двойной унарный минус преобразует значения ИСТИНА и ЛОЖЬ в 1 и 0 соответственно, возвращая массив вида: {0;1;1;1;1;0;0;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, и возвращает следующий результат: {ЛОЖЬ;ИСТИНА;ИСТИНА;ЛОЖЬ;ИСТИНА;ЛОЖЬ;ИСТИНА;ЛОЖЬ;ЛОЖЬ;ЛОЖЬ;ИСТИНА}.
• ФИЛЬТР(C2:C12;A2:A12=E2): Функция ЧАСТОТА подсчитывает количество вхождений каждого числового значения в массиве и возвращает результат следующего вида: {0;2;1;1;1;0;0;0;0;0;0;0}.
• УНИКАЛЬНЫЕ(ФИЛЬТР(C2:C12;A2:A12=E2))=UNIQUE({300,500,400,300,350}): Здесь функция УНИКАЛЬНЫЕ извлекает уникальные значения из массива и возвращает следующий результат: {300;500;400;350}.
• СТРОКИ(УНИКАЛЬНЫЕ(ФИЛЬТР(C2:C12;A2:A12=E2)))=ROWS({300,500,400,350}): функция СТРОКИ возвращает количество строк в заданном диапазоне ячеек или массиве, поэтому результат будет равен 4.

Советы:

1. Если соответствующее значение отсутствует в диапазоне данных, возникнет ошибка. Чтобы заменить её на 0, используйте следующую формулу:

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

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

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