sábado, marzo 01, 2008

Gráficos dinámicos – Mostrar puntos en función de valores.

Ya hemos visto una técnica para determinar cuantos puntos de una serie mostrar en un gráfico.
En esta entrada veremos como determinar la cantidad de puntos a mostrar en función de un determinado valor.

Supongamos que queremos generar un gráfico de columnas a partir de esta tabla de ventas (el archivo con el ejemplo se puede descargar aquí)

grafico dinamico

Nuestro objetivo es determinar la cantidad de puntos a exhibir en función de un determinado valor. Digamos que queremos exhibir los 5 meses con más ventas.

Empezamos por crear una columna auxiliar, con la fórmula

=JERARQUIA(B2,$B$2:$B$13)+CONTAR.SI($B$2:B2,B2)-1

grafico dinamico

Esta fórmula la otorga a cada valor un número de orden. Usamos CONTAR.SI para "desempatar" en caso que dos meses tenga la misma suma de ventas.

Ahora creamos una tabla auxiliar donde ordenamos la tabla original de mayor a menor. Para lograr esto usamos las funciones INDICE y COINCIDIR

grafico dinamico

En el rango E2:E13 ponemos la serie del 1 al 12 que nos servirá como referencia para ordenar los valores. En el rango F2:F13 ponemos la fórmula

=INDICE($A$2:$A$13,COINCIDIR(E2,$C$2:$C$13,0))

Esta fórmula usa los valores del rango E2:E13 para obtener el mes adecuado. Lo mismo hacemos en el rango G2:G13 para poner la suma del mes.

Nuestro próximo paso es crear el gráfico. En esta etapa veremos todos los meses

grafico dinamico

Ahora tenemos que crear dos rangos dinámicos usando nombres (ver la nota del enlace más arriba).
Creamos dos nombres

mes =DESREF(Hoja1!$F$2,0,0,Hoja1!$I$1,1)
suma = DESREF(Hoja1!$G$2,0,0,Hoja1!$I$1,1)

Como ven, hemos ligado los nombres a la celda I1 de la hoja. En esta celda ponemos, en esta etapa, la cantidad de puntos de la serie que queremos mostrar. Como estos nombres se refieren a la tabla auxiliar, donde hemos ordenado los datos en forma decreciente, si ponemos 5 en la celda I1, los rangos dinámicos mostrarán los primeros cinco meses de la tabla, que son los primeros 5 meses con mayores ventas.

Nuestro próximo paso es reemplazar en la función SERIES del gráfico, los rangos de los valores por los nombres que acabamos de crear.

Seleccionamos el gráfico y abrimos el menú Datos de Origen

grafico dinamico

Y reemplazamos los rangos del gráfico por los nombres

grafico dinamico

Esto también se puede hacer seleccionando la serie de datos en el gráfico y reemplazando los valores en la función SERIES que aparece en la barra de fórmulas.

Cada vez que reemplacemos el valor en la celda I1, el gráfico mostrará los valores correspondientes

grafico dinamico

Nuestro último paso es que el gráfico dependa de un valor de ventas. Por ejemplo, ponemos 60000 en una celda y que el gráfico nos muestre todos los meses con ventas mayores a 60000. Para esto agregamos una celda auxiliar, I2, donde ponemos el monto de ventas a partir del cual queremos mostrar los meses. En la celda I1 ponemos esta fórmula

=CONTAR.SI(G2:G13,">"&I2)

Ahora nuestro gráfico muestra todos los meses con ventas mayores al monto introducido en la celda I2

grafico dinamico


Technorati Tags:

3 comentarios:

  1. ¿Por que en el valor de la 'suma' me sale una pantalla de error ("La formula de esta hoja de cálculo contiene una o más referencias no validas. Compruebe que las fórmulas contienen una ruta de acceso, un nombre del rango y una referencia de celda válidos")?
    Cree el nombre suma en Insertar->nombre igual que el del mes, y el del mes no me da problemas
    Muchas gracias por tu ayuda y enhorabuena por el blog

    ResponderBorrar
  2. Alguna de las referencias en la fórmula del nombre es incorrecta. Probablemente el nombre de la hoja. Te sugiero que descargues el archivo con el ejemplo.
    También podés mandarme el archivo para que vea donde pueda estar el problema.

    ResponderBorrar
  3. Conseguido, era que dejaba uno de los valores vacio. Gracias

    ResponderBorrar

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