La función INDICE con áreas

viernes, agosto 01, 2014

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 susucrsal;

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í.

2 comments:

Anónimo,  05 diciembre, 2016 21:28  

Hola Jorge gracias por tus aportes, en este ejemplo en las columnas van sucursales y en las filas están los meses, como seria si fuera lo contrario es decir, en las columnas que fueran meses y en filas que fueran nombres de personas.Saludos

Jorge Dunkelman 15 diciembre, 2016 07:49  

En ese caso COINCIDIR tendr[ia que ir como tercer argumento ("num_columna"). Tambi[en tendr[as que adaptar los rangos adecuadamente.

Publicar un comentario

Google+ Followers

Seguidores

Google+ Badge

Términos Legales

  © Blogger template On The Road by Ourblogtemplates.com 2009

Back to TOP