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

Итак, в третей статье серии материалов по подсчету уникальных значений мы будем считать текстовые значения. Напоминаем, что ранее мы рассмотрели:

  1. Элегантную универсальную формулу, которая отлично считает, как текстовые значения, так и числовые, однако, в основе формулы лежит функция СЧЁТЕСЛИ, поэтому при больших объемах данных могут возникнуть проблемы с производительностью.
  2. Относительно простую, быструю формулу для подсчета только числовых значений.

Сейчас мы рассмотрим формулу для подсчета текстовых значений. Конечно, эту формулу можно использовать и для подсчета числовых значения, однако, если вы сравните формулу предыдущего материала по подсчету числовых значений с текущей, все станет понятно.

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

По традиции запишем ее для английской версии Excel:

=SUMPRODUCT(--(FREQUENCY(MATCH(data;data;0),ROW(data)-ROW(firstcell_data)+1)>0))

Например, посмотрим на диапазон с текстовыми данными, изображенный на рисунке ниже.

image

Как можно заметить из строки формул, для наших данных формула подсчета уникальных текстовых значений следующая:

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

На этом все, дальше идет детальный разбор указанной формулы по этапам.

Работа формулы подсчета уникальных текстовых значений в Excel

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

Итак, посмотрим эту часть формулы:

=ПОИСКПОЗ(B5:B80;B5:B80;0)

Она отображает номер строки каждой текстовой записи, то, что это сквозной номер на листе, а не номер записи в диапазоне абсолютно не важно, главное, что одинаковые текстовые записи помечаются одинаковыми числовыми (по первому вхождению, для неуникальных значений).

image

Смотрим на следующую часть формулы:

=СТРОКА(B2:B77)-СТРОКА(B2)+1

Эта конструкция создает нумерованный диапазон равный количеству элементов исходного.

image

Остальная часть вычисления сводится к уже знакомому нахождения частоты вхождения элементов первого диапазона во второй, потом, с помощью сравнения с нулем, преобразования диапазона в булевый, т.е. со значениями "ИСТИНА" – любое значение больше нуля и "ЛОЖЬ" – если ноль.

image

Затем идет обратное преобразование в нули и единицы с помощью двойного отрицания (одинарное отрицание сделает тоже самое, но число получится с минусом) и, наконец, суммирование диапазона состоящего с нулей и единиц.

Здесь, как и в случае с числовыми значениями, можно использовать функцию СУММПРОИЗВ, а можно просто СУММ:

=СУММ(--(ЧАСТОТА(ПОИСКПОЗ(B2:B77;B2:B77;0);СТРОКА(B2:B77)-СТРОКА(B2))>0))