Operaciones con constantes matriciales

En alguna de mis prehistóricas notas he tocado el tema de las constantes matriciales. Las constantes matriciales nos permiten simplificar nuestras fórmulas, como en el ejemplo que describo a continuación.

Supongamos que queremos calcular el promedio de los tres menores valores de esta lista, (señalados con fuente roja)

Una posibilidad es hacerlo usando una columna auxiliar con la función JERARQUIA para obtener el número de orden y luego usar PROMEDIO.SI

Pero si por alguna razón queremos evitar le uso de columnas auxiliares (por ejemplo, para impresionar al jefe), podemos combinar PROMEDIO.SI con K.ESIMO.MENOR y una constante matricial:

=PROMEDIO(K.ESIMO.MENOR(B3:B12,{1,2,3}))

Como puede apreciarse la fórmula es compacta y a pesar de que estamos usando tres criterios a la vez, no es matricial (la introducimos como toda fórmula corriente).
Si queremos, por ejemplo, calcular la suma de los 5 mayores números en la lista (señalados con fuente verde) usamos

=SUMA(K.ESIMO.MAYOR(B3:B12,{1,2,3,4,5}))

Podemos crear una constante matricial usando la función FILA, de esta manera

=SUMA(K.ESIMO.MAYOR(B3:B12,FILA(1:5)))

pero en este caso debemos usar la fórmula en forma matricial, es decir, introducirla apretando simultáneamente Ctrl.-Mayúsculas-Enter.

Otra posibilidad interesante es el uso de Tablas o nombres definidos para crear una referencia dinámica a los criterios.

Por ejemplo, creamos una tabla de criterios como ésta:

Ahora podemos usar la tabla (tblCriterios) como argumento en nuestra fórmula:

=PROMEDIO(K.ESIMO.MENOR(B3:B12,tblCriterios[#Datos]))

La ventaja de esta técnica es que podemos cambiar dinámicamente los criterios en la fórmulas sin necesidad de editarla.

2048 - la versión para Excel

¿Habrá algo que no se pueda hacer con Excel y Vba? La gente del excelente sitio Spreadsheet1.com ha publicado una versión en Excel del super adictivo juego 2048.

(La imagen la he tomado sin permiso del sitio, espero que no se enojen :))

La descarga es gratuita y como si esto fuera poco el código es totalmente accesible desde el editor de Vb, sin contraseñas.

Además hay un tip para resolver el juego y un enlace a la versión WEB del mismo.

¡Que lo disfruten!

La página de descargas de Add-Ins del sitio no tiene desperdicio (incluye una tabla predictiva del mundial 2014) lo mismo que los tutoriales.

Búsqueda horizontal con COINCIDIR

Lo más memoriosos de mis lectores seguramente recordarán la épica nota sobre búsqueda con la función COINCIDIR en columnas múltiples. En esa nota señalábamos que  la función COINCIDIR nos permite calcular la posición que ocupa un elemento determinado en un rango, pero que si el rango de búsqueda comprende más de una columna (o fila), la función da un resultado de error.
En la nota mostrábamos dos formas de realizar la búsqueda con COINCIDIR a lo largo de varias columnas: con fórmulas y con macros.

Ahora consideremos este problema: dada esta matriz de valores numéricos

queremos encontrar en qué fila aparece por primera vez el número 8. Como puede apreciarse el 8 aparece por primera vez en la fila 2.

Si aplicamos la fórmula que postulamos en la nota anterior, veremos que recibimos un resultado erróneo: ka fórmula da el resultado 3 cuando debería ser 2.

=SI.ERROR(COINCIDIR(H4,C4:C13,0),SI.ERROR(COINCIDIR(H4,D4:D13,0),SI.ERROR(COINCIDIR(H4,E4:E13,0),"ND")))

La fórmula realiza la búsqueda en el primer vector y si no encuentra una coincidencia, realiza la búsqueda en el segundo vector y así sucesivamente. Es decir, la búsuqeda en la matriz es "vertical", cuando lo que queremos hacer es una búsqueda "horizontal".

La solución obvia es usar vectores "horizontales", por ejemplo =SI.ERROR(COINCIDIR(H4,C4:E4,0),SI.ERROR(COINCIDIR(H4,C5:E5,0)....etc. El inconveniente de esta fórmula es obvio: en lugar de tres funciones COINCIDIR, tendríamos que combinar 10 funciones, tantas como las filas de la tabla. Imaginemos ahora una tabla con 1000 filas!

La solución en este caso es usar una función definida por el usuario que llamaremos "fila_celda"

Function fila_celda(Valor_Buscado, Matriz_Busqueda As Range)
    Dim rngCell As Range
  
    For Each rngCell In Matriz_Busqueda
        If rngCell.Value = Valor_Buscado Then
            fila_celda = rngCell.Row - Matriz_Busqueda(1).Row + 1
            Exit Function
        End If
    Next rngCell
   
    fila_celda = "inexistente"

End Function

Esta función usa dos argumentos: Valor_buscado y Matriz_Busqueda

La función realiza la búsqueda a lo largo de las filas como puede verse en el vector del argumento Matriz?Busqueda: 11,1,11;9,8,3;...(nótese el uso de la coma y el punto y coma).

El código de la función debe guardarse en un módulo común del editor de Vb. Para que estpe disponibles para todos los cuadernos abiertos, podemos guardarlo en el libro Personal.

Una vez guardada, la función aparecerá en el asistente de las funciones bajo la categoría "Definida por el usuario"