Gráficos Dinámicos. Seleccionar datos a graficar

Con el siguiente truco veremos cómo aprovechar aún más la potencia de la función DESREF para poder cambiar entre los distintos datos que tengamos para graficar:

Tenemos los siguientes datos:

Se trata de la evolución de las ventas en valor y en número de unidades de una empresa. La idea es que en el mismo gráfico, podamos cambiar los datos para presentar ya sean las ventas o el número de unidades con la aplicación de la función DESREF en un rango dinámico. En la celda G2 tenemos una validación de datos donde se puede elegir “Ventas $$” o “Nro. Unidades”:

Y en la celda H2 tenemos una función COINCIDIR con la cual obtenemos el número de columna después de la B, en la que se encuentra la información seleccionada en la validación de datos:

D esta manera, si se selecciona en la celda G2 Ventas $$, el valor de la celda H2 será 1 o 2 si se selecciona Nro. Unidades.

Ahora, el truco para definir el rango como dinámico es aplicar en el parámetro COLUMNAS de la función DESREF el valor de la celda H2 que corresponde a la columna en la que se encuentra la información seleccionada en la validación de datos de la celda G2. Como en este gráfico dinámico vamos a presentar todos los valores, es decir, incluso los nuevos registros que se agreguen a la tabla, también debemos aplicar sobre el parámetro ALTO, la CANTIDAD de filas no vacías en la columna B para lo cual podemos utilizar la función CONTAR.

Ahora con esta claridad, vamos a definir el nombre del rango dinámico de la siguiente manera:

=DESREF(Hoja1!$B$2,1,Hoja1!$H$2,CONTAR(Hoja1!$B:$B),1)

Agregamos el rango dinámico para las categorías o las fechas que van en el eje horizontal del gráfico:

=DESREF(Hoja1!$B$2,1,0,CONTAR(Hoja1!$B:$B),1)

Ahora que ya tenemos los rangos dinámicos para el gráfico, procedemos a insertar un gráfico de líneas en blanco y luego abrimos la ventana para Seleccionar origen de datos (HERRAMIENTAS DE GRAFICOS, ficha DISEÑO, Seleccionar datos). Agregamos una nueva serie que tendrá por nombre Ventas y los datos de entrada serán los que obtenidos con el rango dinámico DATOSAGRAFICAR:

Nota: Es necesario que la referencia de los valores de la serie, el nombre DATOSAGRAFICAR este antecedido por el Ámbito que definimos al crear el rango dinámico, en este caso el Ámbito es Hoja1

Luego editamos el rango para las etiquetas del eje Horizontal, es decir las fechas:

La ventana de Seleccionar origen de datos queda de la siguiente manera:

Y en la hoja Excel el resultado es:

Si cambiamos el valor de la celda F2 por Ventas $$:

Descargar archivo de trabajo.

MÁS SOBRE

Gerencie.com en su correo.

Suscríbase y nosotros colocaremos en su bandeja de entrada la mejor información que generamos diariamente.

Siéntase libre de opinar

Una opinión
  1. Marian dice:

    genial!, me funcionó muy bien con unos graficos que estoy haciendo, pero me gustaria saber como hago para que en lugar de traer una columna me traiga 2 o 3. Sucede que tengo 3 columnas que se rellenan por formulas segun la seleccion que que haga, pero en una de éstas selecciones una de las columnas debe quedar en blanco. Lo que me gustaría es que cuando se haga ésta seleccion en especifico, el rango de la gráfica solo contemple 2 columnas.

    Gracias!

En Gerencie.com está permitido opinar, criticar, discutir, controvertir, disentir, etc. Lo que no está permitido es insultar o escribir palabras ofensivas o soeces. Si lo hace, su comentario será rechazado por el sistema o será eliminado por el administrador. Por último, trate de no escribir en mayúscula sostenida, resulta muy difícil leerle.