La función INDICE con áreas

Transcripción más o menos real de un diálogo con un compañero de trabajo:

Compañero: Hace unos días atrás leí tu post sobre la función INDICE. Desde entonces la uso todo el tiempo; no me había dado cuenta de las posibilidades que tiene.

JLD: me alegro!

Compañero: pero, ¿qué es esa segunda sintaxis con las áreas, para qué sirve?

JLD: ah!, ¿te referís a esto?:

Aquí va la explicación.

Supongamos este cuadro de ventas por mes y sucursal

Habiendo definido nombres que se refieren a los rangos de ventas de las sucursales (Sucursal_1 se refiere al rango C3:C14, por ejemplo), podemos obtener las ventas de mes de abril de las sucursal 1 con esta fórmula

=INDICE(Sucursal_1,COINCIDIR("abril",B3:B14,0))

Si queremos obtener las ventas de otra sucursal tendríamos que cambiar el rango de referencia en la fórmula.

Si usamos la segunda sintaxis de la función INDICE, podemos crear una fórmula que se refiera a las distintas sucursales dinámicamente. Consideremos este ejemplo

La fórmulas en la celda C20 (Ventas del mes) es la siguiente:

=INDICE((Sucursal_1,Sucursal_2,Sucursal_3),COINCIDIR(C18,$B$3:$B$14,0),,C17)

donde (Sucursal_1,Sucursal_2,Sucursal_3) es la referencia a las distintas áreas, en nuestro caso el rango de las ventas de cada sucursal;

COINCIDIR(C18,$B$3:$B$14,0) calcula el número de fila en el rango correspondiente al mes buscado

y C17 se refiere a la celda que contiene el número de área de donde queremos extraer el dato..

Las áreas no deben tener necesariamente el mismo tamaño o ser adyacentes.

La celda C21 contiene la fórmula

=PROMEDIO(INDICE((Sucursal_1,Sucursal_2,Sucursal_3),,,C17))

La particularidad aquí es que INDICE usa sólo los argumentos "ref" y "num_area". Como hemos explicado, ésto hace que INDICE considere todo el rango de la referencia; así, al combinarla con la función PROMEDIO obtenemos el promedio de las ventas de la sucursal. De esta manera podemos comparar las ventas del mes con el promedio.

Como puede apreciarse, esta técnica es muy útil cuando creamos dashboards y reportes dinámicos.

Como bono adicional, podemos usar INDICE con áreas para crear gráficos dinámicos. Siguiendo con nuestro ejemplo, podemos crear un nombre definido "grfVentas" que se refiera a la fórmula

INDICE((Sucursal_1,Sucursal_2,Sucursal_3),,,C17)

Después de crear un gráfico con una de las sucursales, reemplazamos la referencia en "Modificar serie-valores  de la serie"

por el nombre definido "grfVentas"

A partir de este momento, cada vez que cambiemos la referencia a la sucursal en la celda C17, el gráfico mostrará los datos de la sucursal elegida



El cuaderno con el ejemplo puede descargarse aquí.

Comentarios en celdas - una alternativa

Todo usuario de Excel conoce la posibilidad de insertar comentarios en las celdas de la hoja. Los comentarios son una manera muy práctica de asociar observaciones al contenido de la celda. Un pequeño triángulo rojo en el ángulo superior derecho de la celda nos indica la existencia del comentario

Con el método tradicional es muy fácil  introducir y modificar comentarios, pero en ciertas situaciones pueden presentarse inconvenientes. Por ejemplo, según las deficiones por defecto, el comentario se hace visible cuando apuntamos a la celda que lo contiene. Esto puede ocultar el contenido de celdas contiguas que pueden ser necesarias para las acciones a tomar por el usuario. Otro inconveniente es que el comentario desaparece al dejar de apuntar a la celda lo cual limita su uso para dar instrucciones al usuario.

Una alternativa es usar cuadros de texto u cualquier otra forma, combinados con un poco de código Vba (macros) para hacerlos aparecer o desaparecer.

Veamos cómo aplicar esta técnica al ejemplo de la imagen más arriba.

Empezamos por agregar un icono que indique la posibilidad de recibir instrucciones usando, por ejemplo, una imagen prediseñada

Luego creamos un cuadro de texto con las instrucciones y activamos el panel de selección

Tal como hicimos con el icono de información, agregamos un icono "x" al cuadro de texto que nos servirá para que el usuario pueda cerrar (volver invisible) el cuadro.

En el panel cambiamos el nombre por defecto de los objetos por algo más explícito; por ejemplo, en lugar de "CuadroTexto 3" hacemos un clic sobre el nombre  lo reemplazamos por "ctInstrucciones". Esto nos será útil para simplificar nuestro código

Ahora tenemos que crear el código para volver visible o invisible el cuadro de texto. Activamos la grabadora de macros y ocultamos el cuadro de texto pulsando el "ojo" a la derecha del nombre del objeto en el panel de selección; hacemos lo mismo con la imagen de la "X".

El código resultante es el siguiente:

Sub ocultar_objetos()
'
' ocultar_objetos Macro
'
    ActiveSheet.Shapes.Range(Array("ctInstrucciones")).Visible = msoFalse
    ActiveSheet.Shapes.Range(Array("imCerrar")).Visible = msoFalse
End Sub

Como sucede con todo código creado por la grabadora, podemos simplificarlo a éste:

Sub ocultar_objetos()
'
'
    ActiveSheet.Shapes. _
        Range(Array("ctInstrucciones", "imCerrar")).Visible = msoFalse

End Sub

Ahora necesitamos un código para mostrar los objetos, para lo cual sencillamente copiamos el código anterior cambiando el valor "msoFalse" a "msoTrue"

Sub mostrar_objetos()
'
'
    ActiveSheet.Shapes. _
        Range(Array("ctInstrucciones", "imCerrar")).Visible = msoTrue

End Sub

El último paso es ligar la macro para mostrar los objetos al icono "i" yla macro para volverlos invisibles al icono "x"

Este video demuestra el funcionamiento

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.