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

В этом руководстве показано, как подсчитать только уникальные значения в списке Excel, содержащем дубликаты, с помощью указанных формул.

Как подсчитать количество уникальных значений в диапазоне одного столбца в Excel?

Предположим, у вас есть таблица товаров, как показано на рисунке ниже. Чтобы подсчитать только уникальные значения в столбце «Товар», воспользуйтесь одной из приведённых ниже формул.

Универсальные формулы

=SUMPRODUCT(--(FREQUENCY(MATCH(range,range,0),ROW(range)-ROW(range.firstcell)+1)>,0))

=SUMPRODUCT(1/COUNTIF(range,range))

Аргументы

Как использовать эти формулы?

1. Выберите пустую ячейку, куда будет выведен результат.

2. Введите в выбранную ячейку одну из приведённых ниже формул и нажмите клавишу Enter.

=SUMPRODUCT(--(FREQUENCY(MATCH(D3:D16,D3:D16,0),ROW(D3:D16)-ROW(D3)+1)>,0))

=SUMPRODUCT(1/COUNTIF(D3:D16,D3:D16))

Примечания:

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

=SUMPRODUCT(--(FREQUENCY(MATCH(D3:D16,D3:D16,0),ROW(D3:D16)-ROW(D3)+1)>,0))

• MATCH(D3:D16,D3:D16,0): функция ПОИСКПОЗ определяет позицию каждого элемента в диапазоне D3:D16. Если какие-либо значения встречаются в этом диапазоне более одного раза, функция возвращает одинаковые позиции и формирует массив следующего вида: {1;2;3;2;1;1;3;2;1;1;1;2;3;2}.
• ROW(D3:D16)-ROW(D3)+1:Здесь функция СТРОКА возвращает номера строк для ссылок D3:D16 и D3, и вы получите {3;4;5;6;7;8;9;10;11;12;13;14;15;16}–{3}+1.
• Каждое число в массиве сначала уменьшается на 3, затем к результату прибавляется 1, и в итоге получается массив {1;2;3;4;5;6;7;8;9;10;11;12;13;14}.
• ЧАСТОТА({1;2;3;2;1;1;3;2;1;1;1;2;3;2},{1;2;3;4;5;6;7;8;9;10;11;12;13;14}): функция ЧАСТОТА определяет, как часто каждое значение из первого массива попадает в интервалы, заданные вторым массивом, и возвращает результат в виде массива: {6;5;3;0;0;0;0;0;0;0;0;0;0;0}.
• SUMPRODUCT(--{6;5;3;0;0;0;0;0;0;0;0;0;0;0}>0):

=SUMPRODUCT(1/COUNTIF(D3:D16,D3:D16))

• COUNTIF(D3:D16,D3:D16): Функция СЧЁТЕСЛИ подсчитывает, сколько раз каждое значение из диапазона D3:D16 встречается в этом же диапазоне, используя сами значения в качестве критериев. В результате она возвращает массив вида: {6;5;3;5;6;6;3;5;6;6;6;5;3;5}, где, например, «Ноутбук» встречается 6 раз, «Проектор» — 5 раз, а «Дисплей» — 3 раза.
• 1/{6;5;3;5;6;6;3;5;6;6;6;5;3;5}:Каждое число в массиве делится на 1, и формируется новый массив: {0,166666666666667; 0,2; 0,333333333333333; 0,2; 0,166666666666667; 0,166666666666667; 0,2;
  0,333333333333333; 0,166666666666667; 0,166666666666667; 0,166666666666667; 0,333333333333333; 0,2;
  0,333333333333333}.
• СУММПРОИЗВ({0,166666666666667;0,2;0,333333333333333;0,2;0,166666666666667;0,166666666666667;)
  0,2;0,333333333333333;0,166666666666667;0,166666666666667;0,166666666666667;0,333333333333333;0,2;
  0,333333333333333;}): Затем функция СУММПРОИЗВ суммирует все числа в массиве и возвращает окончательный результат 3.

Связанные функции

Связанные формулы