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

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

1,1,2,3,7,1

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

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

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

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

2 ответа2

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

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

Например:

0

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

=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).

Всё ещё ищете ответ? Посмотрите другие вопросы с метками .