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

Всех приветствую на WiFiGid! В Excel нет отдельной быстрой функции, которая поможет сделать подсчет уникальных значений. Поэтому в этом уроке мы решили показать вам разные стратегии, которые помогут узнать их количество. Придется немного потрудиться, но по-другому никак.

Исходная таблица

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

Как видно, здесь всего 4 автора, поэтому будем пытаться как-то получить эту цифру

Решение 1 – Удаление дубликатов

Предлагаю начать с чего-то простого – попробуем удалить неуникальные значения и посчитаем их.

1. Копируем исходные данные куда-нибудь на отдельный лист (потому что часть этих данных будет удалена).

2. Выделяем нужные нам данные, переходим на вкладку «Данные», находим и нажимаем на кнопку «Удалить дубликаты» (это может быть непросто, но просто наводите мышью на все кнопки и проверяйте, что на них написано).

3. Подтверждаем удаление из нашего столбца.

4. Появится сообщение об удаленных значениях. И обращайте внимание, что все оставшиеся данные были совмещены (пустые строки пропадают).

5. Теперь пользуемся стандартным способом подсчета значений. Текущие данные уже выделены, поэтому просто смотрим на строку состояния внизу. Там и будет написан ответ.

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

Удаление дубликатов в Excel

Решение 2 – Формула (Excel 2021 и новее)

Способ выше хорош, если нам нужно просто и один раз для себя посчитать это значение. Но если нужно посчитать количество уникальных значений в Excel на лету при обновлении данных, тут поможет только формула. Раньше нужно было строить сложную формулу (смотрите раздел ниже), но на новом Office 2021 появилась возможность все упростить. Новые варианты формул:

=СЧЁТЗ((УНИК(A2:A13)))
=COUNTA((UNIQUE(A2:A13)))

Решение 3 – Формула (все версии)

А раньше мы должны были использовать вот такую непростую формулу для подсчета уникальных значений (привожу примеры для русскоязычного и англоязычного варианта формулы):

=СУММ(ЕСЛИ(ЕПУСТО(A2:A13);0;1/СЧЁТЕСЛИ(A2:A13;A2:A13)))
=SUM(IF(ISEMPTY(A2:A13);0;1/COUNTIF(A2:A13;A2:A13)))

Вместо A2:A13 подставляем свои диапазоны ячеек.

Краткий разбор формулы:

1. СЧЁТЕСЛИ(A2:A13;A2:A13) – здесь мы подсчитываем количество включений каждой строки в каждый диапазон. Т.е. если наш Хомяк в списке встречается 6 раз, напротив каждой его строки (в памяти, мы это не выводим в таблицу) будет прописано число 6.
2. 1/СЧЁТЕСЛИ(A2:A13;A2:A13) – здесь мы делим 1 на результат функции СЧЁТЕСЛИ. Продолжая предыдущий пример, для ячейки нашего хомяка мы получим в каждой строке значение 1/6. Это делается для того, чтобы уникальные значения имели вес 1, а повторяющиеся – дробный вес (1/количество повторений). Сразу держим в голове, что если теперь мы бы просуммировали значения всех шести строк с Хомяком, мы бы получили число 1 – т.е. в сумме все неуникальные строки дают единицу и останется только посчитать число этих единиц.
3. ЕСЛИ(ЕПУСТО(A2:A13);0;…) – функция ЕСЛИ проверяет, является ли каждая ячейка в диапазоне A2:A13 пустой. Если ячейка пустая, то функция возвращает 0. Если ячейка не пустая, то функция возвращает результат выражения 1/СЧЁТЕСЛИ(A2:A13;A2:A13) для этой ячейки. Это дополнение на случай, если в нашем диапазоне будут пустые строки. Хотя можно было бы обойтись и без этого, просто перестраховка.
4. СУММ(…) – функция СУММ суммирует все значения, возвращаемые функцией ЕСЛИ для каждой ячейки в диапазоне A2:A13. В результате мы получаем сумму весов каждого уникального значения в диапазоне, что равно количеству уникальных значений.

Возможно, кому-то покажется сложным, но очень рабочий вариант. И вникнуть можно меньше чем за 5 минут.

Решение 4 – Макросы

Если вы умеете использовать макросы Excel, сразу привожу готовый код под наш пример. Просто создаем новый макрос и применяем его на листе:

Function UniqueCount(dataRange As Range)

Dim coll As New Collection

Dim cell As Range

On Error Resume Next

For Each cell In dataRange

If Not IsEmpty(cell) Then coll.Add cell.Value, CStr(cell.Value)

Next cell

DistinctCount = coll.Count

End Function

А уже на ячейку мы ставим формулу в привычном формате =UniqueCount(A2:A13).

Видео по теме