La función INDICE con áreas - segundo episodio

martes, agosto 05, 2014

Unos días después de haber publicado el post sobre la función INDICE con areas, entra en mi oficina el mencionado compañero de trabajo:

Compañero - Hola Jorge. Leí el post. Muy bueno...

JLD - Hola. Muy bueno...pero?

Compañero - Mirá, no si será abusar pero quisiera que en el gráfico la columna del mes analizado aparezca con otro color y también sería bueno si hubiera una línea horizontal que muestre el promedio.

JLD - ¡Ah! Vos querés ésto:

grafico

Compañero - ¡Si! ¿Cómo se hace?

JLD - Sentate que te explico

El gráfico de la nota anterior contenía una única serie; éste contiene tres:

  1. la serie de las ventas sin el mes elegido (las columnas que aparecen en celeste)
  2. la serie que contiene el mes elegido (aparece en  verde)
  3. la serie que representa el promedio.
Como mostramos en la nota anterior, la serie de las ventas la generamos dinámicamente con un nombre definido. Ahora tenemos que crear columnas auxiliares para definir las tres series requeridas. Esto lo haccemos agregando columnas a las izquierda de la tabla de datos de manera que luego podamos ocultarlas.
Estas son las columnas:


La celda B5 contiene la fórmula =COINCIDIR(F19,E4:E15,0). El resultado, el número de orden  del mes elegido, lo usamos como parámetro en las fórmulas de las columnas A y B.

El rango A4:A15 contiene la fórmula

=SI(COINCIDIR(E4,$E$4:$E$15,0)<>$B$2,INDICE(F4:H4,,$F$18),ND())

El objetivo de esta fórmula es dar las ventas del mes de la tabla de ventas a condición que no sea el mes elegido (mayo, 5, en nuestro ejemplo). Em caso de tratarse del mes elegido el resultado es #N/A, lo que logramos con la función ND().

En modo similar ponemos en el rango B4:B15 la fórmula

=SI(COINCIDIR(E4,$E$4:$E$15,0)=$B$2,INDICE(F4:H4,,$F$18),ND())

Esta complementa la anterior: si no es el mes elegido el resultado es #N/A.

Los valores #N/A son ignorados en los gráficos de Excel (no aparecen).

El rango C4:C15 contiene sencillamente una referencia a la celda F22 donde calculamos el promedio.

La celda B17 contiene la fórmula matricial (se ingresa apretando Ctrl-Mayúsculas-Enter):

=MAX(SI(ESERROR(A4:A15),"",A4:A15))

El objetivo de esta fórmula es garantizar que ambos ejes Y en  el gráfico (el principal y el secundario) tengan la misma escala (el mismo valor máximo) para evitar distorsiones en la presentación de los valores. La técnica está explicada en esta nota.

Una vez que hemos definido las series creamos el gráfico asegurándonos de usar el eje Y principal para las ventas y el eje Y secundario para el mes elegido. y para el valor máximo.

Las series de ventas son gráficos de columnas; el promedio es un gráfico de línea y el valor máximo, para volverlo invisible, es un grafico de línea sin marcadores y sin línea




El último detalle a tener en cuenta es, por defecto, Excel no muestra los datos de series que se encuentras en filas o columnas ocultas. Para volverlos visibles tenemos que assgurarnos de cambiar la definición en "Seleccionar Datos-Celdas Ocultas y Vacías"

El archivo con el ejemplo se puede descargar aquí.

4 comments:

Esteban R. 06 septiembre, 2014 06:57  

Hola
Estoy comenzando a perfeccionarme en Excel debido a que tengo pensado dar clases de esta herramienta. Me bajé este archivo para probarlo sobre Excel 2003
Me funcionaba un poco raro cambiando la escala del mes "verde" y lo atribuí a que el campo "max" hace referencia al rango matricial A4:A15 y lo cambié por A4:B15. Igual seguí comportándose raro.
Así que probé con pasar la serie "verde" al eje principal y jugar con la "superposición" de las barras haciéndolas coincidir 100% y se solucionó el problema con la escala automática.
He leído sólo un par de tus posts
Trataré de volver seguido

Saludos

Jorge Dunkelman 06 septiembre, 2014 19:52  

Hola Esteban,

un poco difícil entender el planteo tuyo pero te sugiero que:

1 - leas la nota anterior (enlace al principio de la nota)
2 - descargues el archivo con el ejemplo para poder analilzarlo y entender como funciona.


El modelo funciona en todas las versiones de Excel (97 - 2013).

Cr. Carlos A. Secchi Munchs 02 febrero, 2015 19:04  

Jorge que pasaría si las ventas de mayo fueran 500.000, se distorsiona el gráfico porque el eje principal solo va hasta 400.000? como corregimos esta inconsistencia

Jorge Dunkelman 03 febrero, 2015 06:58  

Hola Carlos,

a menos que hayas establecido un valor fijo para el eje de las Y, Excel lo adapta al valor máximo de la serie.
Tal vez te refieras a otro problema. Como estamos usando dos ejes Y, primario y secundario, Excel no necesariamente da el mismo valor máximo a ambos. Para solucionar este problema he publicado dos soluciones: con funciones y con macros.

Publicar un comentario

Google+ Followers

Seguidores

Google+ Badge

Términos Legales

  © Blogger template On The Road by Ourblogtemplates.com 2009

Back to TOP