Подсчет уникальных значений в excel - Учим Эксель

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

Чтоб подсчитать неповторимые текстовые значения в спектре, вы сможете применять формулу, которая употребляет несколько функций: ЧАСТОТА, ПОИСКПОЗ, СТРОКА и СУММПРОИЗВ. В показанном примере формула в 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.

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

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

Если у вас есть Excel 365, вы сможете применять функцию UNIQUE для подсчета уникальных значений при помощи еще наиболее обычной формулы.

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

Ссылка на основную публикацию
Adblock
detector