EXCEL: Cuente los artículos emparejados en las combinaciones enumeradas

Para crear una tabla de resumen con un recuento de elementos emparejados que aparecen en una lista de combinaciones existentes, puede usar una columna auxiliar y una fórmula basada en la función CONTAR.SI.CONJUNTO. En el ejemplo que se muestra, la fórmula en la celda H5 es:

=SI($G5=H$4,"-",CONTAR.SI.CONJUNTO(helper,"*"&$G5&"*",helper,"*"&H$4&"*"))

donde "ayudante" es el rango nombrado E5: E24.

Nota: esta fórmula supone que los elementos no se repiten en una combinación dada (es decir, AAB, EFE no son combinaciones válidas).

Cómo funciona esta fórmula

Queremos contar con qué frecuencia los elementos en las columnas B, C y D aparecen juntos. Por ejemplo, con qué frecuencia aparece A con C, B aparece con F, G aparece con D, y así sucesivamente. Esto parecería un uso perfecto de CONTAR.SI.CONJUNTO, pero si intentamos agregar criterios buscando 2 elementos en 3 columnas, no funcionará.

Una solución simple es unir todos los elementos en una sola celda, luego usar CONTAR.SI.CONJUNTO con un comodín para contar elementos. Hacemos eso con una columna auxiliar (E) que une elementos en las columnas B, C y D usando la función CONCAT de esta manera:

=CONCAT(B5:D5)

En versiones anteriores de Excel, puede usar una fórmula como esta:

=B5&C5&D5

Como los elementos repetidos no están permitidos en una combinación, la primera parte de la fórmula excluye los elementos coincidentes. Si los dos elementos son iguales, la fórmula devuelve un guión o guión como texto:

=SI($G5=H$4,"-"

Si los elementos son diferentes, se ejecuta una función CONTAR.SI.CONJUNTO:

CONTAR.SI.CONJUNTO(helper,"*"&$G5&"*",helper,"*"&H$4&"*")

CONTAR.SI.CONJUNTO está configurado para contar "pares" de elementos. Solo cuando el elemento en la columna G y el elemento correspondiente de la fila 4 aparecen juntos en una celda, se cuenta el par. Un comodín (*) se concatena a ambos lados del elemento para garantizar que se contará una coincidencia sin importar dónde aparezca en la celda.