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

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

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

Примечание. Очередной метод подсчета уникальных значений — применять функцию СЧЁТЕСЛИ. Это еще наиболее обычная формула, но она может медлительно работать с большенными наборами данных. В Excel 365 вы сможете применять наиболее ординарную и резвую формулу, основанную на UNIQUE.

Разъяснение

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

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

Результатом MATCH является таковой массив:

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

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

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

На этом шаге FREQUENCY настроен последующим образом:

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

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

Интересно почитать: Как в excel убрать лишние символы

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

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

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

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

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

Примечание: добавление IF превращает это в формулу массива, требующую control-shift-enter.

Для получения доборной инфы см. Эту страничку.