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: MS Excel

Ocultar gráficos en Excel

En las últimas notas hemos hablado sobre cómo ocultar y mostrar hojas en un cuaderno Excel. Siguiendo con esta onda del "ocultismo", veamos hoy cómo ocultar un gráfico que hemos insertado en una hoja Excel.
Supongamos esta tabla de datos con la cual construimos un gráfico



Una forma de ocultar el gráfico sería ocultar las filas de la hoja sobre las cuales "flota" el gráfico. Pero si queremos ubicar el gráfico a la izquierda de la tabla, no podremos hacerlo sin ocultar, al mismo tiempo, la tabla misma.

La solución consiste en ligar el gráfico a una imagen y crear un nombre que contenga una fórmula con una condición para mostrar u ocultar la imagen. Veamos la solución por partes.

Empezamos por mudar el gráfico cortándolo y pegándolo en una nueva hoja. Nos aseguramos que en la nueva hoja, la ubicación del gráfico coincida con un rango determinado. En nuestro ejemplo, el gráfico está ubicado sobre el rango A1:F16.



Seleccionamos el rango sobre el cual se encuentra el gráfico y le damos un nombre. En nuestro caso lo llamaremos "grafico"



Esto puede hacerse usando el cuado de nombres (como muestro en la imagen) o con el menú Insertar—Nombres—Definir

Ahora seleccionamos una celda en blanco (por ejemplo, H1) y la damos el nombre "ocultar".

Volvemos a la Hoja1, copiamos (Ctrl+C) una celda en blanco cualquiera, por ejemplo J1. Seleccionamos la celda que será el extremo superior izquierdo del gráfico, en nuestro caso D1. Mientras apretamos la tecla Mayúsculas (Shift) abrimos el menú Edición—Pegar vínculo de imagen



Esta opción sólo aparece si apretamos la tecla Mayúsculas (Shift) al abrir el menú Edición. Hemos creado una imagen que está vinculada a la celda J1.



Reemplazamos el vínculo de la imagen, $J$1, por el nombre "grafico" que contiene el rango sobre el cual se encuentra el gráfico



Inmediatamente aparece el gráfico de la Hoja2 que hemos vinculado a la imagen.

Nuestro próximo paso consiste en crear un nombre con una fórmula condicional que muestre u oculte el gráfico y una celda con un valor que sirva de parámetro a esta fórmula.

Creamos una lista desplegable con dos valores, si y no, en la celda C1. Para esto usamos Validación de Datos



Ahora creamos un nombre, mostrar_grafico, conteniendo la siguiente fórmula:

=SI(Hoja1!$C$1="si",grafico,ocultar)

Seleccionamos la imagen en la Hoja1 y reemplazamos el vínculo al nombre "grafico" en la barra de fórmulas por el nombre "mostrar_grafico"



A partir de este momento, cuando seleccionamos "si" en la celda C1, veremos el gráfico. Si seleccionamos "no", el gráfico desaparecerá.

Aquí pueden descargar el archivo con el ejemplo.

Technorati Tags: MS Excel

Excel – Mostrar hojas ocultas, segunda nota

En la nota anterior sobre hojas ocultas en Excel, mostramos una pequeña macro que nos permite mostrar de un golpe todas las hojas ocultas de un cuaderno Excel.
El próximo paso es desarrollar una macro que nos permite escoger qué hojas del cuaderno descubrir y cuales ocultar, sin necesidad de abrir y cerrar cada vez el menú Formato.

En realidad no hace falta desarrollar la macro porque la gente de GR Bussines Process ya lo han hecho y lo ofrecen, gratis, en su página.
Una vez instalado el complemento con la macro, podemos ocultar y mostrar las hojas de un cuaderno a través de un formulario.
Por ejemplo, en este cuaderno he ocultado las hojas Hoja2 y Hoja3. Al activar la macro se abre este formulario




La ventana de la izquierda muestra las hojas visibles; la ventana de la derecha las ocultas. Los botones ubicados entre las ventanas permiten pasar cada hoja de un estado al otro con facilidad.

En el sitio hay un enlace que permite descargar la documentación de la macro.

Recomendado!

Technorati Tags: MS Excel