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

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

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

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

Общие формулы

=СУММПРОИЗВ(--(ЧАСТОТА(ПОИСКПОЗ(диапазон;диапазон;0);СТРОКА(диапазон)-СТРОКА(диапазон.первыйячейка)+1)>0))

=СУММПРОИЗВ(1/СЧЁТЕСЛИ(диапазон;диапазон))

Аргументы

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

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

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

=СУММПРОИЗВ(--(ЧАСТОТА(ПОИСКПОЗ(D3:D16;D3:D16;0);СТРОКА(D3:D16)-СТРОКА(D3)+1)>0))

=СУММПРОИЗВ(1/СЧЁТЕСЛИ(D3:D16;D3:D16))

Примечания:

Объяснение формул

=СУММПРОИЗВ(--(ЧАСТОТА(ПОИСКПОЗ(D3:D16;D3:D16;0);СТРОКА(D3:D16)-СТРОКА(D3)+1)>0))

• ПОИСКПОЗ(D3:D16;D3:D16;0): Функция ПОИСКПОЗ получает позицию каждого элемента в диапазоне D3:D16. Если значения встречаются более одного раза в диапазоне данных, она возвращает одинаковые позиции, создавая массив, например {1;2;3;2;1;1;3;2;1;1;1;2;3;2}.
• СТРОКА(D3:D16)-СТРОКА(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}.
• СУММПРОИЗВ(--{6;5;3;0;0;0;0;0;0;0;0;0;0;0}>0):

=СУММПРОИЗВ(1/СЧЁТЕСЛИ(D3:D16;D3:D16))

• СЧЁТЕСЛИ(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.

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

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