Valores más frecuentes en una matriz

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

Determinar colores en el formato personalizado de números

Uno de los blogs que sigo, Bacon Bits, trae una nota sobre cómo determinar el color de un formato personalizado de números.

En notas anteriores he explicado que podemos determinar el color de un formato de número condicionalmente.

Por ejemplo, si queremos que la fuente de todo número superior a 1000 sea verde, números menores de 500 aparezcan en rojo y los restantes en negro. podemos usar este formato personalizado

[Verde][>1000]General;[Rojo][<500]general;[negro]general

Convengamos que nuestra idea del verde es diferente de la que parece tener Microsoft.

Una solución es renunciar al verde y usar el azul



Otra solución es usar el número de índice del color en la paleta en lugar del nombre explícito del color. De esta manera tenemos total control del color de la fuente.

El único problema a resolver es saber cuál es el número de índice del color que queremos usar. Para averiguar el número de índice del color podemos usar la técnica que mostramos con las macrofunciones de XLM o una macro como ésta:

Sub nr_color()
    Dim cell As Range, rngNrColor As Range
    
    Set rngNrColor = Application.InputBox(prompt:="select range", Type:=8)
    
    For Each cell In rngNrColor
        cell.Offset(0, 1).Interior.ColorIndex = cell.Value
    Next
    
End Sub

En una hoja creamos un rango con una serie de números del 1 al 56 (por ejemplo A1:A56), corremos la macro y obtenemos esta lista (para facilitar la visualización he divido el rango en tres columnas)

Para usar el verde cuyo número de índice (en mi paleta) es el 10, modificamos el formato personalizado de esta manera

[Color10][>1000]General;[Rojo][<500]general;[negro]general>

Es decir, usamos “Color” y el número de índice.

Technorati Tags: MS Excel

Contar condicional de números con celdas en blanco

En ciertas situaciones Excel puede llevarnos a cometer errores difíciles de detectar. Este ejemplo es la “vida real”, es decir, de un compañero de trabajo.

Supongamos que tenemos un rango de nueve celdas, cinco de las cuales contienen números y las cuatro restantes en blanco

La fórmula =SUMA(B2:B10) da como resultado 150. Es decir, las celdas en blanco son consideradas ceros o ignoradas por la función SUMA.

Lo mismo sucede si usamos la función CONTAR para contar cuántos números hay en el rango. =CONTAR(B2:B10) da como resultado 5. Lo mismo con función CONTARA.

Todo esto nos puede llevar a pensar que si queremos contar cuántos números distintos de cero hay el rango, todo lo que tenemos que hacer es usar la función CONTAR.SI de esta manera

=CONTAR.SI(B2:B9,"<>0")

Esta fórmula da como resultado 9. Este resultado es un tanto extraño. Veamos qué pasa si creamos una columna auxiliar con la fórmula =B2<>0 (que copiamos a los largo del rango)

Vemos que obtenemos cinco VERDADERO y cuatro FALSO. Es decir, podríamos esperar que el resultado de CONTAR.SI en nuestro caso sea 5, pero como vemos es 9.

En nuestro caso, es muy fácil percibir que el resultado no es el esperado, pero si analizamos un rango grande, digamos mil celdas, es más que probable que aceptemos el resultado como correcto.

En caso de tener que contar condicionalmente números en un rango que contiene o puede contener celdas en blanco, podemos usar alguna de estas soluciones:

# usar la función CONTAR.BLANCO para “corregir el resultado de CONTAR.SI

=CONTAR.SI(B2:B10,"<>0")-CONTAR.BLANCO(B2:B10)

# usar la fórmula matricial

={SUMA(--(B2:B10<>0))}

(recordemos una vez más que las fórmulas matriciales deben ser introducidas apretando simultáneamente Ctrl+Mayúsculas+Enter)

# usar la función SUMAPRODUCTO:

=SUMAPRODUCTO(--(B2:B10<>0))

Technorati Tags: MS Excel