уникальное количество значений в ячейке, разделенной запятой в Excel

Question

Как получить уникальное количество значений в ячейке, разделенной запятой в Excel?

Например: ячейка B13 имеет значение

1,1,2,3,7,1

Используя формулу ниже, мы получаем количество значений, разделенных , как 6.

=1+LEN(B13)-LEN(SUBSTITUTE(B13,";",""))

Но я хочу посчитать уникальные значения в ячейке B13, которая равна 4. Может ли кто-нибудь помочь мне достичь этого?

Примечание. Этот вопрос похож на вопрос « Как подсчитать уникальные значения через запятую в Excel 2010». Однако это особый, ограничительный случай (значения представляют собой только однозначные числа), который допускает решения, которые не будут применяться к другому вопросу.

Answer 1

Установите следующую пользовательскую функцию (UDF) в стандартном модуле:

Public Function CountUnique(r As Range) As Long
    Dim c As Collection
    Set c = New Collection
    ary = Split(r.Text, ",")
    On Error Resume Next
    For Each a In ary
        c.Add a, CStr(a)
        If Err.Number = 0 Then
            CountUnique = CountUnique + 1
        Else
            Err.Number = 0
        End If
    Next a
    On Error GoTo 0
End Function

Пользовательские функции (UDF) очень просты в установке и использовании:

1. ALT-F11 открывает окно VBE
2. ALT-I ALT-M открывает новый модуль
3. вставьте материал и закройте окно VBE

Если вы сохраните книгу, UDF будет сохранен вместе с ней. Если вы используете версию Excel более поздней, чем в 2003 году, вы должны сохранить файл как .xlsm, а не .xlsx

Чтобы удалить UDF:

1. откройте окно VBE, как указано выше
2. очистить код
3. закройте окно VBE

Чтобы использовать UDF из Excel:

=CountUnique(A1)

Чтобы узнать больше о макросах в целом, смотрите:

http://www.mvps.org/dmcritchie/excel/getstarted.htm

а также

http://msdn.microsoft.com/en-us/library/ee814735(v=office.14).aspx

а подробности о UDF смотрите в:

http://www.cpearson.com/excel/WritingFunctionsInVBA.aspx

Макросы должны быть включены, чтобы это работало!

Например:

Answer 2

Из вашей формулы следует, что ваш список содержит только однозначные числа. Вот один из способов сделать это:

=10-ISERROR(FIND(0,B13))-ISERROR(FIND(1,B13))-ISERROR(FIND(2,B13))
   -ISERROR(FIND(3,B13))-ISERROR(FIND(4,B13))-ISERROR(FIND(5,B13))
   -ISERROR(FIND(6,B13))-ISERROR(FIND(7,B13))-ISERROR(FIND(8,B13))
   -ISERROR(FIND(9,B13))

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

Это начинается со всех десяти цифр (от 0 до 9), как возможных уникальных значений, которые могут присутствовать. Это выглядит в строке для каждой цифры. Если его там нет, он уменьшает число возможных уникальных значений на единицу (неудачный FIND возвращает ошибку, делая ISERROR истинным, который Excel обрабатывает как 1).