1 votos

¿Cómo puedo contar valores únicos separados por comas en Excel 2010?

Necesito construir una fórmula para recorrer una columna de valores separados por comas y contar el número de valores únicos visto. Estoy usando Excel 2010. Tengo una captura de pantalla de algunos datos de ejemplo...

Screen shot of data

La salida de la fórmula en este caso debe ser 5. Es decir, la fórmula debe contar los siguientes valores: 2.3.0, 2.4.1, 2.4.2, 2.4.3, 2.4.4

He descubierto cómo puedo contar simplemente el número bruto de valores separados por comas así...

\=SUMPRODUCT(--(M123:M127<>""),LEN(M123:M127)-LEN(SUBSTITUTE(M123:M127,",",""))+1)

Sin embargo, esa fórmula da como resultado 7 porque cuenta 2.4.3 y 2.4.4 dos veces.

No sé cómo rechazar valores repetidos separados por comas en diferentes celdas de la misma columna.

Debido a los otros sistemas que deben interactuar con la hoja de cálculo, las respuestas sólo pueden utilizar una fórmula; no deben utilizar VBA o algún tipo de filtro.

2voto

Aquí hay otra solución usando funciones VBA. Pegue lo siguiente en un módulo.

Function ListCount(list As String, delimiter As String) As Long
Dim arr As Variant
arr = Split(list, delimiter)
ListCount = UBound(arr) - LBound(arr) + 1
End Function

Function RemoveDuplicates(list As String, delimiter As String) As String
Dim arrSplit As Variant, i As Long, tmpDict As New Dictionary, tmpOutput As String
arrSplit = Split(list, delimiter)
For i = LBound(arrSplit) To UBound(arrSplit)
    If Not tmpDict.Exists(arrSplit(i)) Then
        tmpDict.Add arrSplit(i), arrSplit(i)
        tmpOutput = tmpOutput & arrSplit(i) & delimiter
    End If
Next i
If tmpOutput <> "" Then tmpOutput = Left(tmpOutput, Len(tmpOutput) - Len(delimiter))
RemoveDuplicates = tmpOutput
'housekeeping
Set tmpDict = New Dictionary
End Function

A continuación, en su hoja de cálculo, puede utilizar la siguiente fórmula:

=ListCount(RemoveDuplicates(A1,", "),", ")

donde A1 es su lista separada por comas.

NOTA: Tendrá que añadir el Microsoft Scripting Runtime referencia a su proyecto VBA (en Tools > References... en la ventana del Editor de Proyectos VBA).

1voto

chris neilsen Puntos 3175

Si puedes añadir algunas fórmulas intermedias a tu hoja, esta es una posible solución.

Basándose en los rangos de su ejemplo:

Poner en la celda N123 Esto devuelve la parte a la izquierda de , o toda la célula si no hay ,

=IFERROR(LEFT($M123,FIND(",",$M123)-1),M123)

Poner en la celda O123 Esto devuelve la parte a la derecha de , o - si no ,

=IFERROR(TRIM(MID($M123,FIND(",",$M123)+1,999)),"-")

Poner en la celda P123 Esto devuelve 1 si la célula N123 es único en la lista, y además 1 si la célula O123 es único

=AND(ROW()+1=ROW($N$123:$N$127)+MATCH($N123,$N$123:$N$127,0),$N123<>"-")
+AND(ROW()=IFERROR(MATCH($O123,$N$123:$N$127,0),""),$O123<>"-")
+AND(ISNA(MATCH($N123,$N$123:$N$127,0)),ROW()+1=IFERROR(ROW($N$123:$N$127) 
    +MATCH($N123,$O$123:$O$127,0),FALSE),$N123<>"-")
+AND(ISNA(MATCH($O123,$N$123:$N$127,0)),ROW()+1=ROW($N$123:$N$127)
    +MATCH($O123,$O$123:$O$127,0),O123<>"-")

Copiar celdas N123..P123 hasta N123..P127

El número de elementos únicos es =SUM(P123:Q127)

Esto supone que hay como máximo 2 elementos en cada celda de datos. Si no es el caso, tendrá que añadir más columnas intermedias y ampliar la fórmula en O123 en consecuencia. Si hay muchos más de 2, esto se irá rápidamente de las manos.

Por cierto, usted dice que una solución VBA no es posible, pero ¿es un VBA función definida por el usuario ¿ok?

Sería algo así como =CountUnique(M123:M127)

0voto

Mike Pennington Puntos 1088

Basándome en la aportación de Chris Neilsen, voy a intentar modificar nuestros sistemas y utilizar esta... Fuente: Mr Excel Forums - Post dated 1 Jan, 2010 at 10:04am

Function UNIQUECOUNTIF(ByRef SR As Range, _
                        ByRef RR As Range, _
                        Optional ByVal Crit As Variant, _
                        Optional NCOUNT As Boolean = False, _
                        Optional POSTCODE As Boolean = False) As Long
Dim K1, K2, i As Long, c As Long, x, n As Long
K1 = SR: K2 = RR
With CreateObject("scripting.dictionary")
    For i = 1 To UBound(K1, 1)
        If Not IsMissing(Crit) Then
            If LCase$(K1(i, 1)) = LCase$(Crit) Then
                If POSTCODE Then
                    x = Split(Replace(LCase$(K2(i, 1)), ",", " "), " ")
                Else
                    x = Split(LCase$(K2(i, 1)), ",")
                End If
                For c = 0 To UBound(x)
                    If POSTCODE Then
                        If IsNumeric(x(c)) Then
                            If Not .exists(x(c)) Then
                                .Add x(c), 1
                            ElseIf NCOUNT Then
                                .Item(x(c)) = .Item(x(c)) + 1
                            End If
                        End If
                    Else
                        If Not .exists(x(c)) Then
                            .Add x(c), 1
                        ElseIf NCOUNT Then
                            .Item(x(c)) = .Item(x(c)) + 1
                        End If
                    End If
                Next
            End If
        Else
            If POSTCODE Then
                x = Split(Replace(LCase$(K2(i, 1)), ",", " "), " ")
            Else
                x = Split(LCase$(K2(i, 1)), ",")
            End If
            For c = 0 To UBound(x)
                If POSTCODE Then
                    If IsNumeric(x(c)) Then
                        If Not .exists(x(c)) Then
                            .Add x(c), 1
                        ElseIf NCOUNT Then
                            .Item(x(c)) = .Item(x(c)) + 1
                        End If
                    End If
                Else
                    If Not .exists(x(c)) Then
                        .Add x(c), 1
                    ElseIf NCOUNT Then
                        .Item(x(c)) = .Item(x(c)) + 1
                    End If
                End If
            Next
        End If
    Next
    If .Count > 0 Then UNIQUECOUNTIF = Application.Sum(.items)
End With
End Function

EnMiMaquinaFunciona.com

EnMiMaquinaFunciona es una comunidad de administradores de sistemas en la que puedes resolver tus problemas y dudas.
Puedes consultar las preguntas de otros sysadmin, hacer tus propias preguntas o resolver las de los demás.

Powered by: