sábado, febrero 24, 2007

Graficos Excel con listas desplegables – Segunda nota

En el pasado mostramos como generar un gráfico dinámico en Excel agregándole una lista desplegable. Para esto usamos el control Cuadro Combinado de la barra de herramientas Formulario.

La tabla de datos es la siguiente




Y el gráfico el siguiente




Uno de mis lectores me pregunta cómo hacer si queremos mostrar el gráfico inverso. Es decir, los meses de ventas por línea de producto.
La lista desplegable deberá mostrar las líneas de productos y el gráfico mostrar doce columnas, una por cada mes de ventas



La técnica es muy similar a la que mostramos en la nota anterior, pero con algunas pequeñas variantes.

Los pasos son los siguientes:

1 – Ponemos la lista en una hoja a la que llamaremos "Columnas"



Para este ejemplo hemos ubicado la tabla de datos en el rango A3:D15

2 – En una nueva hoja, a la que llamaremos "Control", ponemos:

en la celda A1 la fórmula =INDICE(columnas!B3:D3;A2)

en el rango A4:A15 la lista de los meses

en el rango B4:B15 la siguiente fórmula =INDICE(columnas!$B$4:$D$15;COINCIDIR(A4;columnas!$A$4:$A$15;0);$A$2)

en el rango D4:D6 la lista de las líneas de productos

La hoja "Control" se verá así



El error #¡VALOR! En la celda A1 desaparecerá más adelante.

3 – En la hoja "Columnas" creamos el gráfico, pero basándonos en las tabla de la hoja Control



Activamos la barra de herramientas Formularios, elegimos el control Cuadro combinado



y lo ponemos sobre el gráfico, tal como mostramos en la nota anterior sobre el tema.

Abrimos el menú del control y le damos las siguientes definiciones



Cerramos el menú y elegimos una de las líneas de la lista desplegable.



Como el control esta ligado a la celda A2 de la hoja Control, el error desaparece y el gráfico empieza a funcionar como gráfico dinámico.

La diferencia en la técnica se debe a que no podemos usar un rango horizontal como referencia en el control Cuadro Combinado.



Technorati Tags:

14 comentarios:

  1. Muchas gracias por la informacion, esta propuesta es la que necesitaba, y ya la he probado y funciona, pero me gustaria llegar mas lejos si es posible. La aplicacion en la que estoy trabajando tiene rangos variables tanto en filas como en columnas, y solo he conseguido integrar esto en parte, ya que no se como poner un rango variable en las formulas indice, en coincidir si porque solo hace referencia a una fila o columna. Muchas gracias por adelantado, ya que he aprendido mucho con esta y otras notas de tu blog desde que te encontre hace unos meses.

    ResponderBorrar
  2. Una de las formas de crear rangos variables/dinámicos es con la función DESREF. Te invito a leer la nota sobre el tema.
    Aquí puedes leer una explicación detallada sobre la función DESREF.

    ResponderBorrar
  3. muchas gracias por la aportacion pero quisiera saber como cambio de tamaño de fuente dentro del combobox porque me aparece un tamaño un chico en la lista desplegable. gracias

    ResponderBorrar
  4. Hola,
    para cambiar el tamaño de la fuente, tienes que usar el combobox de la barra de Cuadro de Controles, en lugar del combobox de la barra de Formularios.
    Los controles de la barra de Cuadro de Controles son controles ActiveX, mucho más flexibles que los de Formularios. Entre otras cosas puedes definir el tipo y tamaño de fuente.
    Puedes leer mi nota sobre controles ActiveX.

    ResponderBorrar
  5. Saludos Jorge

    Una una duda; me esta marcando error la formula
    =INDICE(columnas!$B$4:$D$15;COINCIDIR(A4;columnas!$A$4:$A$15;0);$A$2)

    alguien le ha pasado lo mismo??

    ResponderBorrar
  6. ¿En qué etapa aparece el error? Si te fijas en la nota, allí señalo: El error #¡VALOR! En la celda A1 desaparecerá más adelante.

    ResponderBorrar
  7. Disculpa Jorge, Tengo la versión de Excel XP, pero cuando despliego el cuadro de control no me aparece la pestaña de Control, podrías decirme donde encontrarla, por favor?

    ResponderBorrar
  8. Hola Beto

    fijate en la imagen que aparece en la nota. Está en la barra de Formularios.

    ResponderBorrar
  9. Me ha venido genial, muchas gracias, pero ahora necesitaria (si es que se puede) con un gráfico de columna apilada, que seleccionando linea1, linea2... la columna del mes me la dividiera en los resultados del año 2008 y del 2009. ¿Crees que seria posible? ¿Como podría hacerse?
    Muchas gracias por anticipado

    ResponderBorrar
  10. Si, es posible, pero habría que ordenar los datos de otra manera.

    ResponderBorrar
  11. Vaya rapidez de contestación Jorge
    ¿Y me podrias orientar de como habira que ordenar los datos? he hecho varias pruebas pero de momento no obtengo resultados....
    Gracias!!

    ResponderBorrar
  12. Sucede que me pescas "en línea" (aprovecho los viernes para responder a los mails). Te sugiero que me mandes el archivo para que pueda fijarme cómo estás organizando los datos.
    El marco de un comentario es un tanto estrecho como para dar una explicación detallada.

    ResponderBorrar
  13. Hola Jorge, cuales son los pasos para abrir la ventana Formato de control? porque de la manera que yo lo hago no me aparece dentro de la venta la solapa Control.
    Muchas gracias.

    ResponderBorrar
  14. Con los controles de la colección Formulario, apuntamos al control y abrimos el menú con un clic al botón derecho del mouse.

    ResponderBorrar

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