Excel

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

Count Unique Text Values Range

Формула Excel: подсчет уникальных текстовых значений в диапазонеУниверсальная формула
= SUMPRODUCT (--( FREQUENCY ( MATCH (data,data,0), ROW (data)- ROW (data.firstcell)+1)>0))
Резюме

Чтобы подсчитать уникальные текстовые значения в диапазоне, вы можете использовать формулу, которая использует несколько функций: ЧАСТОТА , СООТВЕТСТВИЕ , РЯД а также СУММПРОИЗВ В показанном примере формула в F5 имеет следующий вид:



 
= SUMPRODUCT (--( FREQUENCY ( MATCH (B5:B14,B5:B14,0), ROW (B5:B14)- ROW (B5)+1)>0))

который возвращает 4, поскольку в B5 есть 4 уникальных имени: B14.

Примечание. Еще один способ подсчета уникальных значений - используйте функцию СЧЁТЕСЛИ . Это гораздо более простая формула, но она может работать медленно на больших наборах данных. С участием Excel 365 , вы можете использовать более простая и быстрая формула на основе УНИКАЛЬНЫЙ .





Объяснение

Эта формула более сложная, чем аналогичная формула, в которой ЧАСТОТА используется для подсчитывать уникальные числовые значения потому что FREQUENCY не работает с нечисловыми значениями. В результате большая часть формулы просто преобразует нечисловые данные в числовые данные, которые может обрабатывать FREQUENCY.

Работая наизнанку, функция ПОИСКПОЗ используется для получения позиции каждого элемента, который появляется в данных:



 
 MATCH (B5:B14,B5:B14,0)

Результат MATCH - это множество нравится:

 
{1114466699}

Поскольку ПОИСКПОЗ всегда возвращает позицию первый совпадение, значения, которые встречаются в данных более одного раза, возвращают ту же позицию. Например, поскольку «Джим» появляется в списке 3 раза, он появляется в этом массиве 3 раза как цифра 1.

Этот массив передается в FREQUENCY как data_array аргумент.В bins_array Аргумент строится из этой части формулы:

как заменить все в excel
 
 ROW (B5:B14)- ROW (B5)+1)

который строит последовательный список чисел для каждого значения в данных:

 
{12345678910}

На данный момент ЧАСТОТА настроена следующим образом:

 
 FREQUENCY ({1114466699},{12345678910})

FREQUENCY возвращает массив чисел, которые указывают количество для каждого числа в массиве данных, организованных по ячейкам. Когда число уже подсчитано, ЧАСТОТА вернет ноль. Это ключевая особенность работы этой формулы. Результатом ЧАСТОТА является такой массив:

excel, если значение находится между двумя числами, тогда
 
{30020300200} // output from FREQUENCY

Примечание. FREQUENCY всегда возвращает массив, в котором на один элемент больше, чем в bins_array .

Теперь мы можем переписать формулу так:

 
= SUMPRODUCT (--({30020300200}>0))

Затем мы проверяем значения больше нуля (> 0), что преобразует числа в ИСТИНА или ЛОЖЬ, а затем используем двойное отрицательное значение (-) для преобразования значений ИСТИНА и ЛОЖЬ в 1 и 0. Теперь у нас есть:

 
= SUMPRODUCT ({10010100100})

Наконец, СУММПРОИЗВ просто складывает числа и возвращает итог, который в данном случае равен 4.

Обработка пустых ячеек

Пустые ячейки в диапазоне приведут к тому, что формула вернет ошибку # Н / Д. Для обработки пустых ячеек можно использовать более сложную формулу массива, которая использует функцию ЕСЛИ для фильтрации пустых значений:

 
{= SUM ( IF ( FREQUENCY ( IF (data'',  MATCH (data,data,0)), ROW (data)- ROW (data.firstcell)+1),1))}

Примечание: добавление IF превращает это в формула массива что требует Ctrl-Shift-Enter.

Для дополнительной информации, см. эту страницу .

Из Майк Гивин отличная книга по формулам массивов, Control-Shift-Enter.

Другие способы подсчета уникальных значений

Если у вас есть Excel 365, вы можете использовать УНИКАЛЬНАЯ функция к подсчитывать уникальные значения с гораздо более простой формулой.

К сводная таблица также отличный способ подсчета уникальных значений.

Автор Дэйв Брунс


^