Итак, в третей статье серии материалов по подсчету уникальных значений мы будем считать текстовые значения. Напоминаем, что ранее мы рассмотрели:
- Элегантную универсальную формулу, которая отлично считает, как текстовые значения, так и числовые, однако, в основе формулы лежит функция СЧЁТЕСЛИ, поэтому при больших объемах данных могут возникнуть проблемы с производительностью.
- Относительно простую, быструю формулу для подсчета только числовых значений.
Смотрите также видеоверсию статьи «Формула подсчета уникальных текстовых значений в Excel».
Сейчас мы рассмотрим формулу для подсчета текстовых значений. Конечно, эту формулу можно использовать и для подсчета числовых значения, однако, если вы сравните формулу предыдущего материала по подсчету числовых значений с текущей, все станет понятно.
=СУММПРОИЗВ(--(ЧАСТОТА(ПОИСКПОЗ(данные;данные;0);СТРОКА(данные)-СТРОКА(первая_ячейка_данных)+1)>0))
По традиции запишем ее для английской версии Excel:
=SUMPRODUCT(--(FREQUENCY(MATCH(data;data;0),ROW(data)-ROW(firstcell_data)+1)>0))
Например, посмотрим на диапазон с текстовыми данными, изображенный на рисунке ниже.
Как можно заметить из строки формул, для наших данных формула подсчета уникальных текстовых значений следующая:
=СУММПРОИЗВ(--(ЧАСТОТА(ПОИСКПОЗ(B2:B77;B2:B77;0);СТРОКА(B2:B77)-СТРОКА(B2)+1)>0))
На этом все, дальше идет детальный разбор указанной формулы по этапам.
Работа формулы подсчета уникальных текстовых значений в Excel
Принцип работы формулы подсчета уникальных текстовых значений очень схож с подсчетом уникальных числовых значений, отличие заключается в том, что, в случае числовых значений мы могли использовать числовые значения самих ячеек, а здесь нужно текстовые значения перевести в числовые, которые использовать в качестве аргументов функции ЧАСТОТА.
Итак, посмотрим эту часть формулы:
=ПОИСКПОЗ(B5:B80;B5:B80;0)
Она отображает номер строки каждой текстовой записи, то, что это сквозной номер на листе, а не номер записи в диапазоне абсолютно не важно, главное, что одинаковые текстовые записи помечаются одинаковыми числовыми (по первому вхождению, для неуникальных значений).
Смотрим на следующую часть формулы:
=СТРОКА(B2:B77)-СТРОКА(B2)+1
Эта конструкция создает нумерованный диапазон равный количеству элементов исходного.
Остальная часть вычисления сводится к уже знакомому нахождения частоты вхождения элементов первого диапазона во второй, потом, с помощью сравнения с нулем, преобразования диапазона в булевый, т.е. со значениями «ИСТИНА» – любое значение больше нуля и «ЛОЖЬ» – если ноль.
Затем идет обратное преобразование в нули и единицы с помощью двойного отрицания (одинарное отрицание сделает тоже самое, но число получится с минусом) и, наконец, суммирование диапазона состоящего с нулей и единиц.
Здесь, как и в случае с числовыми значениями, можно использовать функцию СУММПРОИЗВ, а можно просто СУММ:
=СУММ(--(ЧАСТОТА(ПОИСКПОЗ(B2:B77;B2:B77;0);СТРОКА(B2:B77)-СТРОКА(B2))>0))
