graficos con rangos dinamicos

Cuando uno quiere enseñar algo, primero debe aprender bien sobre esa materia, a los fines de transmitir un conocimiento válido. Einstein dijo ""No entiendes realmente algo a menos que seas capaz de explicárselo a tu abuela". Y es realmente innegable. Pero hay otra gran verdad, que la mencionó muchos años antes Cicerón: "Para aprender, hay que enseñar". Sinceramente me pasa a diario con las distintas consultas que recibo por parte de mis lectores, amigos y compañeros de trabajo: muchas cosas no las hice nunca y debo aprenderlas para poder explicarlas. Y aquí va uno de esos casos.

Me llama un jefe y me dice: "Tengo mucha información para mostrar en un gráfico. Necesito desplegar una lista con 4 opciones y que la gráfica varíe de acuerdo a la selección". Uffff, ardua tarea hasta dar con el resultado final... pero vale la pena.

Pasemos a un ejemplo sencillo, para comprender esto. Digamos que poseemos la siguiente tabla:

algo simple, pero que sirve a los efectos: la evolución de distintos productos en 4 continentes

[+/-] Ver el resto / Ocultar

Si vamos a graficar esa tabla, la seleccionamos e insertamos el gráfico en la hoja, para tener este resultado:

perfecto, funciona.

Analizando un poco la imagen de arriba nos daremos cuenta que nuestro gráfico se encuentra atiborrado de columnas, lo cual en realidad lleva a una lectura e interpretación un tanto pesada y confusa. Algo que, irónicamente, va en contra de la filosofía de dicha herramienta.

Mi jefe quiere una celda (la cual preparé con "validación de datos") desde la cual puede seleccionar un solo continente y, por ende, que el gráfico solo muestre los datos referentes a el. La tarea se resuelve con: 1) la función INDIRECTO(), 2) dar nombre a los rangos (para facilitar la tarea) y 3) algo de maña.

Vamos con el "paso a paso". Primero haré un gráfico tomando como datos, solamente, los registros correspondientes a América, de la forma común y corriente como siempre hacemos cualquier gráfico:

sencillo: tomé el rango A1:B6 (como podrán observar en la imagen de la tabla) y listo, aparece la gráfica con los datos de américa.

Analizaremos una cuestión, antes de seguir, que pocos conocen  sobre esta herramienta: hagamos click sobre la serie de datos (columnas) del gráfico y dirijamos nuestra mirada a la barra de fórmulas de Excel:

sip: una fórmula

Demos un vistazo a esa fórmula, ya que será el eje principal de este trabajo:

=SERIES(Hoja1!$B$1;Hoja1!$A$2:$A$6;Hoja1!$B2$B6;1)

Tiene 4 argumentos, y nos ocuparemos de los 3 primeros. Como habrán observado, esta función se llena automáticamente al confeccionar el gráfico, pero ello no implica que este prohibido modificarla.

1) título del gráfico / américa, en este caso, que está en la celda B1

2) serie de datos horizontal / A2:A6 en nuestro ejempo (a1, a2, a3, a4, a5)

3) la mas importante y fundamental: datos de origen, es decir, los valores que influyen en el tamaño de las columnas / B2:B6 para este gráfico.

Si analizamos lo planteado, veremos que aquí lo principal es que vaya cambiando el tercer argumento, conforme a nuestro usuario seleccione un continente u otro para mostrar.

¿Un poco confuso? No se preocupen, se va a poner peor. =)

Ahora vamos a la celda G1 y escribimos:

termino de escribir en G1 el nombre de la hoja, junto al rango de datos correspondiente a américa.

Necesito "meter" el valor de G1 en la función =SERIES() vista anteriormente. Y aquí radica todo el misterio del trabajo. Vamos a "insertar / nombre / definir" o "pestaña Fórmulas / asignar nombre" (esta ultima desde Excel 2007) y hacemos lo siguiente:

creo un rango "virtual" al que llamo "variable" en el cuadro "Hace referencia a:" le indico =INDIRECTO(G1). Presionamos Aceptar y nuestro nueva rango toma vida.

Analicemos: cada vez que dentro del libro llame al rango "variable" estaré invocando al =INDIRECTO() del valor presente en G1. Para los que no recuerdan bien a Indirecto(), vean esta función:

En la imagen superior sumo el rango "virtual" que creamos antes. ¿Que hace Excel? Toma a "variable", que es el indirecto de G1 y lo suma. Y si, la suma de los registros de américa dan como resultado 1500. Si necesitan mas ayuda sobre esta función la encontrarán dentro del blog.

Le daremos a los rangos de datos de cada continente un nombre. Seleccionamos B2:B6 y le ponemos "america" (sin comillas), como muestro en la siguiente imagen, desde el cuadro de nombres:

Repetimos la accion con cada continente:

Una vez que nombramos: america, asia, europa y africa.... modifiquemos la celda G1, agregando los nombres de estos continentes:

el título no se actualizó... pero noten que los datos sí, ya que obedecen a los de africa

Primero vamos a seleccionar, con un click, la etiqueta "america" que se encuentra a la derecha del gráfico y presionamos "suprimir", para eliminarla. Ahora hacemos click sobre el título del gráfico, en la parte superior del mismo, y en la barra de fórmulas ingresamos =G1

ahora si: cada vez que cambiemos el valor de G1 se actualizará solo el título del gráfico, como vemos en la imagen superior.

Y si, volvemos a G1 y validamos la celda, para que solo permita ingresar los nombres de cada uno de los continentes de la tabla. La hacemos sencilla y seleccionamos el encabezado de la tabla como origen de la lista:

Acomodamos un poco mas la hoja, ocultando la tabla con el mismo gráfico y hacemos uso de las herramientas visuales que nos brinda la versión 2010:

desde ya que harán un trabajo visual muy superior al mío... pero bueno, sirve para el ejemplo.

"Tapé" la tabla de origen con el gráfico, aunque esta podría haber estado en otra hoja. No lo hice así en este proyecto para que sea mas sencillo de comprender.

Y, para darle un poco de valor agregado a todo esto y presumir de nuestro trabajo... ¿que tal si con una muy simple macro hacemos que automáticamente cambie de continente, al mejor estilo Power Point? Esto, al margen de presumir, nos permitirá realizar una buena oratoria mientras los datos van cambiando. Vamos a VBA (Alt + F11), insertamos un módulo y escribimos este código:

Sub GraficoPowerPoint()
Dim Columna, X As Byte

de_nuevo:
'con este bucle me encargo de recorrer el encabezado
'de la tabla, poniendo en G1 los valores de cada
'celda
For Columna = 2 To 5
    Range("g1").Value = Cells(1, Columna).Value
    DoEvents
    'espero dos segundos para seguir:
    Application.Wait Now() + TimeValue("00:00:02")
Next Columna

If MsgBox("Desea repetir la presentación?", vbYesNo + vbQuestion, "Graficos") = vbYes Then
    GoTo de_nuevo
Else
    End
End If
End Sub

Luego asignamos esa macro a un botón y todo quedará listo. No olviden que la instrucción Application.Wait detiene la ejecución del código por el tiempo indicado. En este caso le ordeno que "aguante" 2 segundos antes de continuar, mediante: Now() (ahora) + TimeValue(). Esta segunda función acepta como argumento datos string, con formato de hora, minutos y segundos.
Aqui les dejo el link al archivo.