☰

Comparación de varias columnas para devolver el valor más común

tag-icon • tag-icon microsoft-excel
Comparación de varias columnas para devolver el valor más común

Tengo 6 columnas de cadenas y estoy buscando la cadena más común entre las 6 columnas.

Cualquier ayuda con esto sería muy apreciada.

columna1 columna2 columna3 columna4 columna5 columna6
Manzana Naranja Banana kiwi Duende Brócoli
Naranja Banana kiwi Duende Brócoli
Banana kiwi Duende Brócoli
kiwi Duende Brócoli
Duende Brócoli
Brócoli

El resultado sería brócoli. Si la columna 1 y la fila 6 no contenían brócoli, el resultado sería Brownie/Brócoli.

columna1 columna2 columna3 columna4 columna5 columna6
Arrurruz Alcachofa Rúcula Espárragos Brotes de bambú Frijoles
remolacha Zanahorias Apio Duende Brócoli
Banana kiwi Zanahorias Ajo
Chips de chocolate Pan Queso
Jugo Estallido
Papas fritas

El resultado serían Zanahorias.

Respuesta1

Lo siguiente funcionaría:

=LET(Source,A2:F7,
     ShortList,UNIQUE(FILTERXML("<Outer><Inner>"&SUBSTITUTE(TEXTJOIN(",",TRUE,Source),",","</Inner><Inner>")&"</Inner></Outer>","/Outer/Inner")),
     Occurrences,COUNTIF(Source,ShortList),

 TEXTJOIN("/",TRUE,SORT(IF(Occurrences=MAX(Occurrences),ShortList,""))))

Suele TEXTJOIN()juntar la lista toda, perdiendo espacios en blanco. Luego, FILTERXML()el truco es convertirlo en HTML y dividirlo en una matriz que Excel reconozca como tal. UNIQUE()luego obtiene una lista de instancias únicas de cada valor presente.

Luego COUNTIF()se utiliza para obtener recuentos para cada elemento único, MAX()obtiene el valor más alto de la lista de esos recuentos y IF()prueba el recuento de cada elemento único con el valor máximo para encontrar resultados calificados. SORT()coloca esos resultados calificados en orden alfabético.

Finalmente, TEXTJOIN()toma los resultados calificados y los formatea para la cadena de salida deseada.

(Si no desea (o no es necesario) ordenar, simplemente edite esa función. Supuse que sería deseable (pensando que la cadena "Brownie/Brócoli" no estaba blindada, solo una muestra rápida del resultado deseado) y pensé en ponerla en y editarlo es más claro que no incluirlo, simplemente decir "Ordenarlo entonces" y dejar que usted lo resuelva).

Está LET()organizado para facilitar y lógica. "Facilidad" en el sentido de que el rango en el que se va a trabajar se produce al inicio inmediato y sólo allí es fácil de editar. Nada más varía en la versión actual, por lo que pasa al cálculo intermedio. Nombres en una presentación de abajo hacia arriba (tal vez mejor descrita como "de adentro hacia afuera" para describir su precedencia en la fórmula). Y finalmente, la fórmula de trabajo resultante.

Respuesta2

También puedes hacer esto con una función definida por el usuario, escrita en VBA.

Ingresar esta UDF es fácil:

Para ingresar esta función definida por el usuario (UDF),

  • <alt-F11>abre el editor de Visual Basic.
  • Asegúrese de que su proyecto esté resaltado en la ventana del Explorador de proyectos.
  • Luego, en el menú superior, seleccioneInsert => Module
  • pegue el siguiente código en la ventana que se abre.

Para utilizar esta función definida por el usuario (UDF), ingrese una fórmula como =mostFrequent(A1:F6)en alguna celda.

Option Explicit
Function mostFrequent(r As Range) As Variant()
    Dim arr As Variant, dict As Object
    Dim v
    Dim result(1)
    
'read range into vba array for faster processing
arr = r
Set dict = CreateObject("Scripting.Dictionary")
    dict.CompareMode = TextCompare
    
'read into dictionary and get the count of each item
For Each v In arr
    If Len(v) > 0 Then
        If Not dict.Exists(v) Then
            dict.Add Key:=v, Item:=1
        Else
            dict(v) = dict(v) + 1
        End If
    End If
Next v

'find max count
For Each v In dict.Keys
    If dict(v) > result(1) Then
        result(0) = v
        result(1) = dict(v)
    End If
Next v

'return most frequent string and it's count
mostFrequent = result
    
End Function

ingrese la descripción de la imagen aquí

Algoritmo

  • Lea el rango en una matriz VBA para un procesamiento más rápido
  • Ingrese cada cadena en un diccionario donde
    • Clave = la cadena
    • Valor = el recuento de esa cadena
  • Devuelve la cadena que tiene el recuento más alto
  • En el siguiente código, en realidad devolvemos una matriz de 2 elementos donde el segundo elemento es el recuento del elemento.
    • Opcionalmente, puede devolver esto usando, según su versión de Excel, la función de matriz dinámica o la función Índice.

información relacionada