Como en casi todo gráfico dinámico, el truco está en usar rangos dinámicos definidos en nombres. Por lo general, y en esta nota, lo haremos con la ayuda de la función DESREF.
Supongamos esta tabla de ventas que muestra los 12 meses de año 2009 y que hemos representado con un gráfico.
Queremos que el gráfico muestre siempre los últimos n meses, siendo n un número que el usuario defina. Por ejemplo, si agregamos los enero y febrero del 2010 queremos ver los datos desde marzo del 2009 a febrero del 2010.
Como ya hemos mostrado, vamos a reemplazar los rangos estáticos de la serie de datos y de los valores del eje de las categorías, por rangos dinámicos
Antes de definir los rangos dinámicos, definimos un nombre que cuente cuántos períodos (datos) hay en la tabla. Lo hacemos basándonos en la columna de los períodos (la columna B en nuestro ejemplo) ya que puede haber períodos sin datos
cntPeriodos = =CONTARA(dinamico!$B:$B)-1
Restamos 1 del resultado de CONTARA para eliminar la cuenta del título de la columna.
El próximo paso es elegir una celda donde ponemos el número de meses que queremos mostrar (en nuestro ejemplo H2, y no por capricho como ser verá luego). También a H2 le ponemos nombre, pmtMeses
Ahora definimos nombres dinámicos para el eje de la Y, grfY y para el eje de la X, grfX
grfX =DESREF(dinamico!$B$3;cntPeriodos-pmtMeses;0;pmtMeses;1)
grfY =DESREF(dinamico!$C$3;cntPeriodos-pmtMeses;0;pmtMeses;1)
Una vez definidos los nombres reemplazamos los rangos estáticos en la fórmula SERIES, cuidándonos de dejar el nombre del cuaderno incluido el símbolo !
La explicación de estas fórmulas es la siguiente:
dinamico!$B$3;cntPeriodos-pmtMeses calcula desde donde comienza el rango. Por ejemplo si tenemos 14 períodos en la tabla (calculados por cntPeriodos) y queremos mostrar sólo los últimos 12 (pmtMeses), cntPeriodos – pmtMeses da 2; por lo tanto nuestro rango comienza en B5 y se entiende por 12 celdas hasta B16.
Ahora podemos aprovechar la ubicación de H2 para crear un título dinámico para el grafico
Cuando reemplazamos el valor de la celda H2, el título cambia de acuerdo al contenido del gráfico.
El archivo se puede descargar aquí.
Jorge:
ResponderBorrarMuchas Gracias por tan excelente aporte.
Giovanni Collado
Lima - Peru
Buen tip. Para mis gráficos dinámicos utilizo listas de validación y barras de desplazamiento. La función desref tiene la desventaja de ser vólatil.
ResponderBorrarSaludos!!!
Israel Mendoza, México.
Excelente...¿oye cuentas con algun ejemplo sobre analisis de componentes principales?
ResponderBorrarHola Israel Mendoza, solo puntualizando, la función desref deja der ser volatil cuando se ancla con "$".
ResponderBorrarPerdón Anónimo de las 14:55, pero no entendí tu punto. Tengo entendido que aún cuando utilices referencias absolutas, la formula continua siendo vólatil. Podrias incluso utilizar un numero fijo en vez de una referencia a celdas. Puedes revisarlo usando la ventana de insertar función, donde se lee que el resultado de la formula es vólatil.
ResponderBorrarSaludos,
Israel Mendoza, México.
Sr. Jorge Dunkelman.
ResponderBorrarReciba un cordial saludo.
He leído varios de sus artículos sobre la hoja de cálculo y, en mi opinión muy particular, me parecen extrardinarios. Sencillamente por la razón de que en poco tiempo y con una gran cantidad de tips y herramientas le da solución, así como sugerencias a los distintos seguidores de su blog, explicado de manera magistral, tanto así, que para personas como su servidor, (un principiante en estas aventuras informáticas), me han resultado muy ilustrativas.
Soy Profesor de Educación Física, y tenemos un almacén deportivo donde el material que se presta a los alumnos es a través de su credencial, la cual contiene su número de matrícula y nombre, he elaborado una tabla donde aprecen:
1. Foliados en número progresivo los préstamos.
2. Número de matrícula
3. Fecha del préstamo, así como la hora del mismo.
4. Al digitar la matrícula, aparecen el nombre del alumno y su matrícula, mismos datos que están en una pestaña llamada "DATOS".
5. Después aparecen la fecha y hora de entrega del material.
6. En caso de no haberlo entregado el mismo día, causa una demora, la cual se indica en número rojos con cuantos días de atraso se encuentra el material. o, en caso de extraviar o dañar el mismo, se le indica que tiene que reponerlo.
Mis dudas son las siguientes:
- ¿Cómo construyo una función donde me diga, a partir del stock que tengo, qué cantidad de cada material me queda almacenado y que esa cifra en caso de que alguien solcite material similar, se vaya restando al total de cada categoría?
Por ejemplo si tengo 5 balones de Fútbol en stock, y hoy martes 18 de mayo vinieron 3 alumnos dostintos a solicitar el mismo material casi al mismo tiempo, entonces en mi stock quedan 2 balones.
Si uno de ellos no regresa el material porque lo dañó, (se hace la anotación de que lo debve reponer, y en el stock ya no aparecen los 5 balones, sólo 4), y en otra fecha posterior vuelve a solictar el mismo material, y sí lo devuelve.
¿Cómo genero una consulta donde aparezcan los reportes por alumno, fecha, mes, material solicitado y el stock que me queda de cada material?
Además tengo que elaborar el reporte de cuánto es lo que debe cada uno, (en caso de que así sea y mandarlo por correo a la caja de la escuela).
Si genero la expresión =HOY(), o, =AHORA(), ¿Cómo hago para que no se actualicen las fechas anteriores de los préstamos realizados? O, ¿es necesario teclear ese dato uno por uno?
¿Cómo le anexo mi tabla que elaboré? Espero haberme expresado correctamente y espero su amable y fina respuesta.
Ponte en contacto conmigo por mail privado (detalles en la pestaña Ayuda en la parte superior del blog).
ResponderBorrarUna reacción un tanto tardía: Israel Mendoza tiene razón. DESREF es y será volátil tambien si "anclamos" la referencia con una dirección absoluta.
ResponderBorrarLas funciones volátiles se convierten en un probema cuando las tenemos en cantidades industriales en una hoja. Así que hasta qué punto son una desventaja depende de la cantidad en uso (como con las fórmulas matriciales).
Estimado Sr. Jorge.
ResponderBorrarMe gustaria compartir con usted un ejemplo de gráficas dinámicas. He preparado un archivo un tanto simple que le envié a su correo electrónico. Le agradecería sus comentarios acerca de mis técnicas. Asimismo seria un honor para mi si usted compartiera mi trabajo en su blog (así los lectores podrian opinar y dar sugerencias).
Gracias,
Israel Mendoza, México
Israel,
ResponderBorrarte he envíado una respuesta por mail privado.
Se le agradece mucho por su ejemplo, sin embargo ya que veo que han tocado el tema de la función DESREF, solo tengo una consulta, cual es la cantidad máxima de funciones DESREF que podemos tener en una hoja antes de que empiecen a causar problemas, hay alguna forma de solucionarlos?, la pregunta sale debido a que trate de hacer lo de la actualización automática de gráficas con una base de datos, con aprox 40 variable(es decir habían 40 funciones DESREF en la hoja), y con una formula similar a la suya, sin embargo después de hacer las gráficas los valores de SERIES desaparecían para aparecer con valor 0. no se si sera problema de la cantidad de DESREF, por su ayuda muy agradecido.
ResponderBorrarNo hay un número específico de DESREF a partir del cual empiezan los problemas. Todo depende de la estructura general del cuaderno. Hay alternativas a DESREF, como por ejemplo usar INDICE. También puedes usar gráficos dinámicos (pivot charts).
ResponderBorrarEstimado Jorge, permítame felicitarlo por tan buen sitio y empeño que ha puesto en enseñar a todos el cómo usar Excel de manera constructiva, presentándonos herramientas que están a la vista de todos, pero que muy pocos saben ocupar. Desde hace ya algún tiempo he dedicado un par de horas semanales para aprender los "trucos" que usted muestra para aplicar, por sobre todo en gráficos y así dejar de usar macros, sin embargo, se me presenta el mismo problema que a Jorge: Cuando abro el archivo, el nombre de hoja dentro de la función DESREF se convierten en [0]. Estoy confeccionando gráficos con más de 100 variables y mi única intención es que a medida que crece la base de datos (X,Y), el gráfico se actualice en forma automática y así no tener que modificar las SERIES una por una y mes a mes. Para eso cree un contador que va dentro de todas las funciones DESREF, de la forma =desref(Hoja1!$A5;0;0;Contador;1). Contador "cuenta" la cantidad de datos a mostrar. Estuve viendo la función INDICE, pero la verdad no supe cómo usarla. Saludos cordiales
ResponderBorrarJaime.
Jaime,
ResponderBorrartal vez la técnica que muestro en esta nota sea má apropiada a tu consulta.
También puedes mandarme el archivo por mail (fijate en las condiciones en el enlace Ayuda)
Hola, mira he realizado lo que dices y me funciona todo bien, pero tengo el problema que cierro el archivo excel y cuando vuelvo a abrirlo, me aparece el gráfico pero han desaparecido las formulas de las series de datos.
ResponderBorrarEn el gráfico despues aparecen los siguientes datos:
nombre de la serie: =DATOS!$F$11
valores de la serie: =[0]!grff3
cuando deberian aparecer los datos de las formulas que habiamos introducido
Saludos y muchas gracias por tu ayuda
Francisco
Francisco, te sugiero que me mandes el archivo (fijate en el botón Ayuda, en la parte superior de la plantilla).
ResponderBorrar¿como te mando el archivo? Aqui no veo opción para enviarte
ResponderBorrarNuevamente, hacer clic al botón Ayuda en la parte superior del blog.
ResponderBorrar