Esta nota es producto de la consulta de uno de mis lectores, Xisco, quien también, al final de cuentas, aportó una de las soluciones.
Dados un rango o matriz de valores Excel permite calcular el valor más frecuente con facilidad usando la función MODA. Por ejemplo, dada esta matriz de valores (5 columnas X 10 filas = 50 valores aleatorios entre 1 y 10)
si incluimos el rango de la matriz en el nombre “matriz”, usamos esta fórmula para calcular el valor más frecuente (10, que aparece 9 veces)
=MODA(matriz)
El valor de la celda I3 lo calculamos con =CONTAR.SI(matriz,I2).
La pregunta es cómo calculamos el segundo valor más frecuente, o el tercero, etc.
Excel no tiene una función nativa para este cálculo. Mi primera aproximación al problema fue usar columnas auxiliares (técnica que siempre recomiendo ya que hace que los modelos sean más legibles y fáciles de controlar).
Creamos un rango auxiliar que contiene todos los valores únicos que aparecen en la matriz ( en nuestro caso, de 1 al 10) y paralelamente un rango auxiliar que calcula la frecuencia de cada valor
Vemos que, efectivamente, el 10 aparece 9 veces y el segundo valor más frecuente es el 1 con 8 apariciones.
Incluimos el rango B14:B23 en el nombre “valores” y el rango C14:C23 en el nombre “frecuencia”. En la celda I5 ponemos el número de orden de frecuencia buscado (2 por ejemplo) y en la celda I4 ponemos esta fórmula
=INDICE(valores,COINCIDIR(K.ESIMO.MAYOR(frecuencia,I5),frecuencia,0))
En esta fórmula la función K.ESIMO.MAYOR busca el número más grande del rango “frecuencia” de acuerdo al parámetro introducido en la celda I5; luego busca en que fila del rango se encuentra usando la función COINCIDIR y este resultado es usado por la función INDICE para dar el valor adecuado del rango “valores”.
En esta solución hemos usado dos columnas auxiliares (“valores” y “frecuencia”).
El estimado Xisco propone una solución que use sólo una columna auxiliar (o ninguna, si los números vienen ordenados en una sola fila o columna). Dado que en nuestro ejemplo los números están dispuestos en una matriz, debemos transformarlos primero en una columna. Para esto podemos usar la técnica que hemos mostrado en la nota sobre cómo convertir datos de matriz a columna o fila en Excel.
En la celda B13 ponemos esta fórmula
=DESREF(matriz,RESIDUO(FILA()-14,FILAS(matriz)),TRUNCAR((FILA()-14)/FILAS(matriz)),1,1)
que copiamos hasta la celda B63, es decir 50 filas. Este rango lo incluimos en el nombre “valores2”. El resultado es un rango de 1 columna por 50 filas que contiene todos los calores que aparecen en las 50 celdas de la matriz.
Ahora reemplazamos la fórmula de la celda I6 por la siguiente:
=INDICE(valores2,COINCIDIR(K.ESIMO.MAYOR(FRECUENCIA(valores2,valores2),I5),FRECUENCIA(valores2,valores2),0))
La bastante obvia pregunta es: ¿se puede hacer el mismo cálculo sin columnas auxiliares? Por supuesto, con una UDF (función definida por el usuario). Pero, ¿puede hacerse sin usar Vba (macros)?
Toda sugerencia será bienvenida.
Technorati Tags: MS Excel
