sábado, junio 29, 2013

Matrices de valores únicos en funciones de Excel

El origen de esta nota es esta consulta:

"tengo una tabla de alumnos con las calificaciones de nueve asignaturas. Quiero extraer las tres mejores notas en orden decreciente, sin repetición".

Para ejemplificar veamos parte de esta tabla:



La función que nos permite extraer el valor de la lista de acuerdo a su jerarquía en la lista es K.ESIMO.MAYOR (orden decreciente) y K.ESIMO.MENOR (orden ascendente). La sintaxis de estas funciones es sencilla:

=K.ESIMO.MAYOR(matriz de valores, número de orden)

Así, para el alumno 1 (fila 2) la fórmula

=K.ESIMO.MAYOR($B2:$J2,1)

da como resultado 95.

El problema con esta función es que no ignora los valores repetidos. Si usamos ahora

=K.ESIMO.MAYOR($B2:$J2,2)

para obtener la segunda mejor notas en orden decreciente, el resultado será nuevamente 95, cuando el resultado que buscamos es 86.

Para obtener los resultados esperados necesitamos que el argumento "matriz de valores" de la función sea una matriz de valores únicos.

En esta nota mostraré dos soluciones posibles a este problema: 1) usar columnas auxiliares; 2) usar una FDU (función definida por el usuario).

Solución con columnas auxiliares.

A la izquierda de la matriz insertamos un número de columnas equivalente al número de columnas de la matriz



La celda A2 contiene la fórmula

 =SI(CONTAR.SI($L2:L2,L2)=1,L2,"#")

Esta fórmula evalúa si el valor de la celda L2 aparece por primera vez en la fila; en caso afirmativo da el valor de la celda L2; en caso negativo da como resultado el símbolo #.

EL valor 95 aparece por segunda vez en la celda N2 y por eso el valor resultante en la celda C2 es #.

En la función CONTAR.SI anclamos la columna en la referencia a la primer celda del rango ($L2) de manera que al copiar la fórmula a lo largo de las columnas el rango de evaluación se va extendiendo ($L2:M2 en la celda B2, $L2:N2 en la celda C2 y así sucesivamente).

Ahora podemos usar los rangos de las columnas A:I para nuestros cálculos



En la fórmula

=K.ESIMO.MAYOR($A2:$I2,V$1) 

usamos los valores de la fila 1 como el argumento de orden, fieles al principio de no evitar el uso de constantes en las fórmulas.

Podemos mejorar esta solución funcionalmente convirtiendo todo el rango (columnas auxiliares, matriz de datos y columnas con resultados) en una tabla (Insertar-Tablas-Tabla). De esta manera al agregar alumnos, todas las fórmulas se copian automáticamente



Solución FDU (función definida por el usuario – macro).

La función valUnicos crea una matriz de valores únicos que podemos usar dentro de las funciones de Excel, en nuestro caso en K.ESIMO.MAYOR.

Esta función usa la técnica que ya mostramos en la nota sobre cómo extraer valores únicos.

El código de la función es el siguiente:

Function valUnicos(rngValores As Range)
    Dim iX As Integer
    Dim arrTemp()
    Dim collUnicos As New Collection
    Dim vcollItem As Variant
    Dim rngCell As Range
   
    On Error Resume Next
    For Each rngCell In rngValores
        collUnicos.Add rngCell, CStr(rngCell)
    Next rngCell
    On Error GoTo 0
       
    ReDim arrTemp(collUnicos.Count)
    For iX = 1 To collUnicos.Count
        arrTemp(iX - 1) = collUnicos.Item(iX)
    Next iX
   
    valUnicos = arrTemp
   
End Function


Como siempre recomiendo, guardamos el código en un módulo del cuaderno Personal.xls(b) de manera de poder usar la función en todo cuaderno abierto.

Esta función utiliza un único argumento: un rango de la hoja. En la función usamos el objeto Collection para descartar los valores repetidos en el rango; luego pasamos los ítems de la colección a una matriz (array).

En nuestro ejemplo, combinamos esta función con K.ESIMO.MAYOR para obtener los resultados deseados



De la misma manera podemos combinarla con otras funciones (K.ESIMO.MENOR, CONTAR, etc). Por ejemplo

=CONTAR(valUnicos(B2:J2))

da como resultado 7 ya que el 95 y el 86 se repiten.


El cuaderno con el código y los ejemplos se puede descargar aquí.

2 comentarios:

  1. Hola, tengo una duda y espero que me puedan ayudar. En el trabajo manejo el Excel 2003. Estoy creando una planilla con fechas de entrega de productos. Necesito que esa planilla cuente con una celda donde me avise que el producto de esa fila está atrasado y hay que reclamar. No tengo problemas con la Función SI, ya que puedo lograr que me deje cambiar el texto de "EN FECHA" a "RECLAMAR" [=SI(HOY()>=$F94, "RECLAMAR","EN FECHA")]sin problemas. Ahora viene mi problema, ya que me gustaría que el texto "RECLAMAR" me salga con el fondo rojo y el "EN FECHA" con otro color (azul por ej). Hay alguna manera de hacerlo? Ya probé con el formato condicional y no me salió.
    Espero vuestra ayuda. Abrazo

    ResponderBorrar
  2. No hay ningún motivo para que no funcione con formato condicional. Tenés que tener presente que la fórmula usada en formato condicional debe dar un valor VERDADERO o FALSO. En tu caso la fórmula de formato condicional sería

    =A1="EN FECHA"

    y para el otro caso

    =A1="RECLAMAR"

    ResponderBorrar

Nota: sólo los miembros de este blog pueden publicar comentarios.