FÓRMULAS DE EXCEL

Contar parejas de ítems en listas combinadas en Excel

Contar parejas de ítems en listas combinadas en Excel

Funciones utilizadas: SI, CONTAR.SI.CONJUNTO, CONCAT

Suponiendo que disponemos de un rango de datos con encabezados en A1:C10 donde consideraremos a cada fila un registro, y queremos obtener en el rango F2:M9 una matriz con la cantidad de veces que aparece cada combinación de 2 ítems por fila de registro del rango objetivo, podemos utilizar un método útil y fácil para resolverlo:

MÉTODO SI + CONTAR.SI.CONJUNTO

La sintaxis que debemos aplicar en la celda G3 y que luego debemos arrastrar hacia la derecha y hacia abajo dentro de la matriz es la siguiente:

=SI($F3=G$2;"-";CONTAR.SI.CONJUNTO(auxiliar;"*"&$F3&"*";auxiliar;"*"&G$2&"*"))

Donde «auxiliar» corresponde al nombre de un rango de columna auxiliar en D2:D10 que debe contener la función CONCAT aplicado a todos los ítems por fila de registros. «F3» corresponde al primer encabezado del eje vertical de la matriz y «G2» corresponde al primer encabezado del eje horizontal de la matriz.

Es importante mencionar que la fórmula sólo funcionará para combinaciones de valores no repetidos. Es decir, que por ejemplo si en una fila de registros contiene los ítems «A», «B», «A», la fórmula no contará de forma correcta dicho registro. La primera igualdad dentro de la sintaxis permite evitar la cuenta de valores repetidos en un mismo registro de fila, devolviendo en dichos casos el símbolo «-«.

La función CONTAR.SI.CONJUNTO permite contar un registro cuando ambos valores de sus ejes aparecen en dicho registro por fila, utilizando la referencia de los 2 ejes por ítem en la matriz.

Por último, los asteriscos «*» se utilizan como comodines para concatenar ambos lados de cada ítem asegurando que una coincidencia sea contada sin importar su ubicación.

De esta forma, logramos obtener de forma rápida y fácil una matriz con la cantidad de veces que se repite cada combinación de 2 ítems por fila de registros para un rango determinado.

Si deseas conocer más acerca de los métodos más efectivos para concatenar celdas (en el video utilizamos la función CONCAT), puedes ver los siguientes artículos:

Función CONCAT y UNIRCADENAS en Excel

Función CONCATENAR en Excel

 

Te recomiendo ver el siguiente link con más videos de Fórmulas ingeniosas de Excel AQUÍ.

Salvo aclaración, todas las fórmulas y macros de este sitio están configuradas para aplicarse sobre la celda A1. Algunas fórmulas se encuentran encerradas entre llaves {} debido a que son fórmulas matriciales. Estas llaves no deben introducirse tecleándolas, sino que se generan automáticamente al aceptar la fórmula pulsando Control+Shift+Enter al mismo tiempo. Las fórmulas de este sitio son compatibles con versiones de Microsoft Excel® 2010 o superiores.