Рубрики
Excel Tips & Tricks

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

На практике часто приходится работать с большими диапазонами данных, содержащих повторяющиеся более одного раза значения. Иногда возникает необходимость произвести подсчет уникальных значений в Excel.

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

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

Есть много вариантов решения, все собирать в кучу не буду, приведу один, который мне кажется самый удобным. Моё решение состоит в создании пользовательской функции, которая производит подсчёт в заданном диапазоне. Диапазон и будет единственным аргументом функции.

Нажатием клавиш ALT+F11 (или FN+ALT+F11 на Mac) заходим в редактор Visual Basic. Затем нажимаем Insert — Module. Вводим следующий код:

Function UniqueValues(Rn As Range) As Long
    Dim myCell As Range, UniqueVals As New Collection
    Application.Volatile
    On Error Resume Next
    For Each myCell In Rn
        UniqueVals.Add myCell.Value, CStr(myCell.Value)
    Next myCell
    On Error GoTo 0
    UniqueValues = UniqueVals.Count
End Function

Несколько пояснений к коду:

  • Метод Application.Volatile заставляет функцию производить пересчёт при изменении данных в любой ячейке выбранного диапазона.
  • Объект Collection — это упорядоченный набор элементов, он не может содержать повторяющиеся значения.
  • Функция CStr используется для преобразования выражения в тип String.

Теперь, чтобы произвести подсчет количества уникальных значений в Excel, нажимаем «Вставить функцию», выбираем категорию «Определенные пользователем». Теперь здесь появилась наша функция «UniqueValues». Выбираем её, задаём необходимый интервал и получаем результат.

  • Метки