miércoles, 15 de junio de 2011

Rango Dinámico

Descargar el fichero: RangoDinamico.xlsx


Si tenemos una tabla que cambia de tamaño nos vemos obligados a tener que redefinir el rango de los datos que queremos incluir en un gráfico o en una tabla dinámica. Si la tabla contiene un número de filas que varían con frecuencia es bastante tedioso tener que cambiar el rango de datos para el gráfico, y lo mismo con la tabla dinámica. Vamos a exponer un método por el cual no tendrá que preocuparse por el tamaño de su tabla ya que tanto el gráfico como la tabla dinámica tomará el tamaño necesario de forma automática.

En nuestro fichero de ejemplo disponemos de cuatro columnas con datos generados de forma aleatoria. Si queremos ampliar el número de filas de la tabla lo único que debemos hacer es copiar su última fila hacia abajo. Para reducir su número de filas borraremos, por abajo, tantas como deseemos.

Inicialmente la tabla tiene 20 registros (filas de datos).


Deseamos crear un gráfico como el siguiente donde se muestren los 20 puntos que corresponden a los 20 datos de Facturación a lo largo del tiempo. Hemos añadido una línea de tendencia (recta de regresión).



Si queremos que el gráfico ajuste el número de puntos a los que contenga la tabla independientemente de su tamaño y de forma automática debemos definir los rangos que tomará el gráfico. La definición de rangos será un tanto curiosa ya que no nos limitaremos a seleccionar el área que deseamos asociar al rango sino que trabajaremos con fórmulas.

Vamos a utilizar la función DESREF y la función CONTARA de forma anidada.

Función DESREF

La función DESREF es una función que puede emprearse de dos maneras:

  1. Utilizando los 3 primeros argumentos permite extraer un valor de una celda que se encuentre en una tabla. Para ello debemos indicar desde que celda debemos comenzar a contar (punto de partida), indicando el número de filas hacia abajo que nos hemos de desplazar, y el número de columnas a la derecha que nos debemos mover. Esta forma de emplear DESREF no es matricial.
  2. Utilizando los 5 argumentos de la función. En este caso la función es matricial y permite extraer un bloque de celdas. Se ha de indicar el punto de partida, la celda de la esquina superior izquierda y las dimensiones del bloque.
La sintaxis de DESREF en su versión matricial es:

=DESREF(ref;filas;columnas;alto;ancho)

donde

  • ref es la celda desde la que partimos. Es el punto de partida (Km. 0). Para los que conozcan Madrid es el kilómetro cero de la Puerta del Sol
  • filas es el número de filas hacia abajo que nos hemos de desplazar para llegar a la celda que marca la equina superior izquierda del rango que deseamos extraer. Si las filas son negativas nos movemos hacia arriba, siempre tomando como punto de partida nuestro kilómetro cero
  • columnas es el número de columnas a la derecha que nos hemos de desplazar para llegar a la celda que marca la esquina superior izquierda del rango que deseamos extraer. Si las columnas son negativas indica que nos movemos a la izquierda de nuestro kilómetro cero
  • alto es el número de filas que contiene el rango que deseamos extraer
  • ancho es el número de columnas que contiene el rango que deseamos extraer
Si queremos utilizar esta fórmula debemos considerar que se trata de una fórmula matricial y que por tanto se han de dar los tres pasos que siempre requieren este tipo de fórmulas:
  1. Paso 1. Primero debemos seleccionar el rango donde la fórmula matricial dejará su resultado. Esto es importante, ya que muchas fórmula matriciales no dejan su resultado un una sola celda, sino en un rango de celdas. Este primer paso indica que este rango debemos seleccionarle.
  2. Escribimos la fórmula matricial de que se trate. En este caso sería DESREF si queremos probar con ella.
  3. Para validar la fórmula no pulse ENTER. Se deben pulsar simultáneamente las tres teclas siguientes: CONTROL + MAYUSCULAS + ENTER

Función CONTARA

Hablemos ahora de la función CONTARA. Para contar existen dos funciones:
  • CONTAR: cuenta las celdas numéricas del rango que indiquemos
  • CONTARA: Cuenta todo, números y texto
Aplicadas estas funciones sobre la columna E, donde se encuentra la facturación los resultados obtenidos son:

  • =CONTAR(E:E)      da como resultado 20, que son los registros de la tabla
  • =CONTARA(E:E)   da como resultado 21, que son los registros numéricos más la cabecera
Creación de Rangos

Vamos a crear un nombre de rango denominado laFactura que incluya todos los datos de facturación. Incluirá desde la celda E3 hacia abajo hasta el final de la columna E. Inicialmente el rango será E3:E22 ya que  la tabla inicialmente tiene 20 registros. Pero al indicar el rango que se asocia a ese nombre de rango lo haremos con fórmula para que sera automático y se ajuste a la longitud de la columna E que exista en cada momento.

La fórmula para el nombre de rango laFactura es:

=DESREF(Hoja1!$E$2;1;0;CONTARA(Hoja1!$E:$E)-1;1)

Ahora vamos a crear el nombre de rango laFecha para se ajuste al rango que marca los datos de fecha de la columna B, desde la celda B3 hasta la última fecha que se encuentre en la columna B.

La fórmula para el nombre de rango laFecha es:

=DESREF(Hoja1!$B$2;1;0;CONTARA(Hoja1!$B:$B)-1;1)


Los dos rangos anteriores se utilizaran para crear el gráfico dinámico. Para la tabla dinámica que crearemos posteriormente vamos a definir el nombre de rango Tabla que abarca toda la tabla incluida la cabecera, y las cuatro columnas que la constituyen.

La fórmula para el nombre de rango Tabla es:

=DESREF(Hoja1!$B$2;0;0;CONTARA(Hoja1!$E:$E);4)



Nombrar rangos

Ahora debemos nombrar los rangos.

  • En Excel 2003 la forma ortodoxa de definir un rango es seleccionando Insertar, Nombre, Definir. 
  • En Excel 2007 debemos ir a la pestaña de Fórmulas y pulsar sobre la opción Administrador de Nombres.


Gráfico Dinámico

Creamos un gráfico de tipo Dispersión del tipo Dispersión solo con marcadores.


Indicamos el origen de los datos indicando los nombres de rango. Para el eje X utilizamos el rango laFecha, y para el eje Y utilizamos el rango laFactura.


Importante

Al indicar el rango para el eje X e Y debemos indicar no solo el nombre del rango sino también el nombre del fichero, sin olvidar el signo de admiración (!) y el signo igual al inicio de la expresión (=).

Para los valores X de la serie debemos escribir exactamente lo siguiente:

=RangoDinamico.xls!laFecha

Para los valores Y de la serie debemos escribir exactamente lo siguiente:

=RangoDinamico.xls!laFactura

Si copiamos la última fila de la base de datos hacia abajo hasta la fila 102 habremos obtenido 100 registros que automáticamente quedarán representados en el gráfico sin necesidad de que toquemos los rangos. El resultado podría ser el siguiente.


Observamos que los puntos representados en el gráfico han aumentado automáticamente pasando de los 20 iniciales a los 100 que ahora.


Tabla Dinámica

Vamos a crear una Tabla Dinámica utilizando los 20 datos iniciales de nuestra tabla.

Al confeccionar la Tabla dinámica nos piden el rango de los datos y en ese momento indicaremos el nombre de rango Tabla que previamente hemos creado.

En Configuración de campo elegimos Cuenta.


Obtenemos una Tabla Dinámica similar a la siguiente, donde el número de elementos totales es de 20.


Al ampliar el número de filas de la tabla hasta 100 filas seguimos viendo en la Tabla dinámica que el número de elementos es de 20. Para poder ver los 100 elementos debemos actualizar la Tabla dinámica. Esto se consigue pulsando con el botón derecho del ratón sobre la propia Tabla dinámica y eligiendo en el menú contextual la opción Actualizar.


Tras actualizar la Tabla dinámica podemos observar que ahora son 100 los datos considerados.


66 comentarios:

  1. Muy, pero muy útil!, muchas gracias!

    ResponderEliminar
  2. Excelentes lo aportes que hacen en este bloger sobre excel...por favor me cuentan como hago para ampliar una celda hacia abajo cuando estoy escribiendo un texto...me explico....estoy haciendo texto en una columna y quiero separar un parrafo de otro en la misma celda y cuando doy enter me lleva a la otra celda, lo que quiero es que me quede en la misma pero separado....
    muchas gracias..

    ResponderEliminar
  3. Hola Fernando.
    Yo lo que hago es escribir los parrafos en un procesador de textos. El propio Block de notas es válido. Luego copio y pego los parrafos en la celda editada.
    Lo de 'editada' quiere decir que antes de pegar los párrafos debes estar metido dentro de la celda, en modo EDICIÓN.
    No se si existe otro método mejor, pero este te funcionará.
    Un saludo.

    ResponderEliminar
  4. Gracias por la info.

    ALT+ENTER

    funciona estupendamente.

    ResponderEliminar
    Respuestas
    1. Hola buenos días Adolfo, me podrías por favor ayudar con una macro que me copie el texto de una celda que contiene 2 párrafos(separados por un enter) para luego pegarlo en otra celda que me separe los dos párrafos? Manualmente lo que hago es copiar el texto pero seleccionándolo desde adentro de la celda y ya luego me paro en otra celda y le doy pegar y el me lo separa en dos filas, eso quiero pero automatizado a través de una macro

      Eliminar
    2. Hola Angela.
      He creado una macro para ti en el siguiente enlace.
      http://www.excelavanzado.com/2014/09/separar-en-varias-celdas-un-texto.html
      Espero que te resulte útil.

      Eliminar
  5. Hola Adolfo, tengo un pequeno problema. En un plan de cuentas tenemos este tipo de codigos "1.1.1.2.3.01" Deseo contar cuantos digitos tiene, excluyendo los puntos y y si es posible el "01" considerarlo como un solo digito. Es decir que me de como resultado '6' digitos.
    Probe con la funcion 'LEN' pero esta me cuenta los puntos.
    Necesito Ayuda Por Favor

    ResponderEliminar
  6. Hola Cristian.

    Prueba con la expresión:

    =LARGO(SUSTITUIR(A1;".";""))-1

    donde tu texto ha de estar en la celda A1.

    Un saludo.

    ResponderEliminar
  7. gracias x la respuesta,
    pero al escribir la funcion me sale un error marcandome la celda. Sera que falta algo?

    ResponderEliminar
  8. Hola Cristian.

    A mi me funciona perfectamente. Mira esta imagen:

    IMAGEN

    Es posible que tengas el Excel en inglés y en lugar de poner punto y coma, como separador de argumentos, debas poner coma.

    ResponderEliminar
  9. Buenas tardes,
    Aplico lo que dices, en mi caso 23 columnas:
    =DESREF('DATOS BASE'!$A$1;0;0;CONTARA('DATOS BASE'!$A:$A);23)

    Pero no llega hasta la última fila pero 2 antes del final. ¿Por qué ocurre esto? ¿Es la solución que aplico correcta o hay una mejor?:
    =DESREF('DATOS BASE'!$A$1;0;0;CONTARA('DATOS BASE'!$A:$A)+2;23)

    Un saludo,
    Albert

    ResponderEliminar
  10. Fantástico blog! Una pregunta sobre este tema: cuando aplico DESREF y CONTARA a un nombre de rango, después no aparece en la lista cuando desde una función pulso F3 para ver los nombres del libro en cuestión. Estoy con Excel 2010. ¿Se te ocurre alguna cosa?
    Gracias anticipadas y enhorabuena por tu blog.

    ResponderEliminar
  11. Amigo adolfo Aparicio, me tope con su foro porque tengo una duda de la formula que me vulve loco, el cual le explico de la siguiente manera, su solucion se presta solo bajo una condicion en donde hay una celda de arraque fija y en el grafico solo varia deacuerdo a las celdas que se incorporen en donde el grafico se hare enorme mientras mas informacion se vacie en la misma, cree una formula que es la siguiente:

    =DESREF(INDIRECTO(DIRECCION(4;CONTAR.SI($4:$4;">=1")-7;;;);VERDADERO);;;;11))

    En donde la celda de arranque se correra acorde vayamos insertando mas columnas en la tabla y el grafico siempre tomara las ultimas 10 columnas en cuestion, la premisa de correlacion son las mayores a 1 debido que todas las demas estaran en cero.

    La base del problema es el siguiente que al yo agregar esta formula en el "Administrador de nombres" y hacer referencia a ella en la tabla de la grafica me da un error el cual no he podido solventar sin importar cuanto he investigado y tengo dias en este dilema, al parecer la funcion "indirecto" o "Direccion" no pueden ser calculadas bajo el "administrador de nombre" de ser asi no consigo manera de que la celda de arraque sea movil y varie deacuerdo a la cantidad de columnas que agregue, me remito a ud con sus conocimientos para que me ayude con mi problema.

    ResponderEliminar
  12. Hola Cristian

    Tengo un problema con la aplicación de la función. me sale un error en el valor (#valor).
    Tengo office 2010

    ResponderEliminar
  13. Estimado:

    Muchas gracias por la ayuda, estoy tratando de ajustar el Gráfico Dinámico para uno de líneas, pero aun no me resulta... ¿cómo puedo hacerlo para un gráfico de lineas donde los datos tanto como las columnjas (series) van cambiando?
    De ante mano muchas gracias

    ResponderEliminar
  14. Buen Día Fernando, me gustaría preguntarle realizarle una pregunta sobre los gráficos dinámicos, lo que sucede es que necesito crear un gráfico de línea que me muestre los comportamientos de ventas mes a mes durante varios años, he tratado de realizar el proceso pero no sé como dejar una línea para cada año. Gracias

    ResponderEliminar
  15. Hola, muy buen blog sobre excel.
    Mira he creado un gráfico dinámico que siempre sales los últimos 12 meses, con las siguentes fórmulas:
    grff1=DESREF(DATOS!$G$9;0;CuentaH-meses;1;meses)
    grff2=DESREF(DATOS!$G$10;0;CuentaH-meses;1;meses)
    grff3=DESREF(DATOS!$G$11;0;CuentaH-meses;1;meses)
    =SERIES(DATOS!$f$10;CMIPAC.xlsx!grff1;CMIPAC.xlsx!grff2;2)
    =SERIES(DATOS!$f$11;CMIPAC.xlsx!grff1;CMIPAC.xlsx!grff3;1)
    Lo coloco todo y el gráfico se va actulizando, el problema viene cuando guardo, cierro el archivo, y cuando vuelvo a abrirlo ya no funciona, han desaparecido las formulas DESREF del gráfico.
    ¿Sabes por qué ocurre esto?

    Saludos y muchas gracias
    Francisco

    ResponderEliminar
  16. Estoy trabajando una hoja-plantilla para seguimiento semanal de proyectos, pero cuando dibujo el gráfico para un proyecto que inicia en la semana 26 y termina en la semana 48, el gráfico me inicia en la semana cero y yo quiero que inicie en la semana 26.

    Favor responder a fpovedag@gmail.com

    ResponderEliminar
  17. hola, tengo un archivo que contiene mi inventario, al cual agregue una hoja de factura. la pregunta es: como hacer para que al ir generandose las ventas se reste de mi inventario. como hago para que al ingresar un articulo reste ese mismo ariculo de la otra hoja que quizas no esta en la misma celda, me explico?
    mil gracias

    ResponderEliminar
  18. Excelente explicación. Justo estaba buscando hacer gráficas a partir de rangos dinámicos y esto me servirá muchísimo. Gracias.

    ResponderEliminar
  19. Como puedo copiar las formulas que estan en una fila y pegarlas en una columna, vale decir por cada celda que me desplace hacia abajo, que copie las celdas hacia la derecha

    ResponderEliminar
    Respuestas
    1. Hola. Puedes usar, copiar y pegar, pero no con un pegado normal, sino con Pegado Especial Transponer.

      Eliminar
  20. Buenas tardes.

    Quiero crear una lista desplegable condicionada, pero uno de los nombres de los rangos es "Juguetes, niños y bebes" y el administrador de nombres no me permite espacios. Hay alguna manera de solucionar este inconveniente. Necesariamente tiene que ir ese nombre así tal cual lo escribo. Agradezco su amable respuesta.

    ResponderEliminar
  21. Hola Alejandro.

    Los nombres de rango no aceptan espacios. Lo que se hace habitualmente es juntar palabras o mejor separarlas con guión bajo.
    Juguetes_niños_y_bebes

    Un saludo.

    ResponderEliminar
  22. hola tengo una consulta... selecciono datos, voy inicio y selecciono grafico de líneas pero no me coinciden valores de las y pero si lo hago gráfico de barras si queda bien. Pero necesito hacerlo con líneas

    ResponderEliminar
  23. Adolfo buenos días y excelentes aportes. Con el rango dinámico solucioné la mitad de un trabajo, la otra mitad es lograr graficar automáticamente sólo los últimos 5 (cinco) datos de una tabla que se actualiza diariamente. Podría por favor colaborarme. Muchas gracias,
    Javier

    ResponderEliminar
  24. Hola Javier.

    Para conseguir que el gráfico únicamente represente los últimos 5 datos de la tabla sea esta de la longitud que sea, debes cambiar los dos rangos siguientes:

    laFactura
    =DESREF(Hoja1!$E$2;CONTARA(Hoja1!$E:$E)-5;0;5;1)

    laFecha
    =DESREF(Hoja1!$B$2;CONTARA(Hoja1!$B:$B)-5;0;5;1)

    De esta forma en el gráfico de este ejemplo únicamente se representaran los últimos 5 valores, y da igual si alargamos o reducimos el número de registros (filas) de la base de datos.

    Un saludo.

    ResponderEliminar
  25. Adolfo buenas noches y muchas gracias!! Excelente resultado!! Tengo un libro con varias hojas, traté de dejar el ámbito para la hoja pero no lo tomó, me tocó manejar el ámbito del libro y renombrar para cada hoja, demorado pero se terminó.

    ResponderEliminar
  26. Hola Adolfo!
    Gracias por esta explicacion. Me gustaria preguntarle como podria utilizar estos rangos dinamicos en formulas.

    Es decir, por ejemplo, tengo una columna (A) con datos, de los cuales la cantidad de filas puede variar. Me gustaria poder en la columna B hacer la siguiente operacion: A1*A1 en la celda B1, A2*A2 en la celda B2, y asi sucesivamente para cada fila de la columna B, sabiendo que A a veces tiene 5 filas y otras puede tener 6,7 o mas. Me gustaria entonces que la segunda columna se ajuste a la cantidad de filas de la primera, igual que lo hace el grafico, añadiendo puntos cada vez que se añade un dato.

    Gracias y un saludo

    ResponderEliminar
  27. Alguien me puede ayudar?? necesito copiar celdas alternas de una misma columna a otra hoja, específicamente: copiar celdas P24, P53, P82, P111.. y así, cada 29 celdas, a otra hoja, en las celdas C68, C69, C70.. y así sucesivamente, copiarlas todas con arrastre.. es posible??

    ResponderEliminar
    Respuestas
    1. Hola David.
      Para resolver tu caso puedes ver el siguiente enlace.
      Funciones FILA, INDICE y RESIDUO
      El caso que planteas está resuelto en el tercer vídeo, pero conviene que mires los dos anteriores.
      Un saludo.

      Eliminar
  28. Hola. pero si digamos quiero sacar un rango donde la fecha sea solo los primeros 10 días de abril (sin importar año) como hago.. solo el rango..

    ResponderEliminar
    Respuestas
    1. Hola. Las fechas en Excel siempre tienen que llevar día, mes y año. Luego si quieres usar únicamente el día y el mes puedes hacerlo mediante las funciones DIA y MES. Y el rango puedes usar uno que tu crees en la hoja de cálculo.
      Un saludo.

      Eliminar
  29. Hola, muchas gracias por responder!
    La verdad eso he estado haciendo y no me da
    =Y(L20>=DIA(1); L20<=DIA(10);L20=MES(8))

    Gracias por tu ayuda.

    ResponderEliminar
    Respuestas
    1. Hola. La función DIA tiene como argumento una fecha. No puedes poner DIA(1) ya que estarías poniendo que quieres calcular el día cuya fecha es 1-enero-1900 ya que es en esa fecha cuando comienza a contar Excel las fechas.
      Un saludo.

      Eliminar
  30. Hola, quisiera que me ayudara con una formula por favor. Tengo 40 mil filas con datos, de los cuales debo agrupar cada 1440 datos (filas) y sumarlos. La idea es que yo arrastre una formula que vaya sumando cada 1440 datos. Se puede hacer esto?. Gracias

    ResponderEliminar
    Respuestas
    1. Hola Vera.
      Creo que la siguiente entrada del Blog puede dar respuesta a tu necesidad, ya que se puede aplicar la idea.
      Media Móvil a intervalos fijos
      La diferencia está en que el ejemplo de este enlace está resuelto para hacer la media y tu necesitas la suma.
      Un saludo.

      Eliminar
    2. Hola, muchas gracias por su respuesta, pero baje la planilla que tiene de ejemplo y sale #REF! en todas las celdas de la columna F y al hacer la misma fórmula en mi planilla, me pasa lo mismo. Tengo Excel 2010.
      Me podría ayudar por favor

      Eliminar
    3. Hola Vera.
      Tu pregunta es acerca de la fórmula matricial que se emplea en el artículo de la Media Móvil que te cité anteriormente. En esa fórmula se emplea la función INDIRECTO, y es una fórmula matricial.
      La función indirecto se pude usar de dos formas. Una de ellas hace referencia a las celdas con el sistema habitual, por ejemplo, la celda A1 es la primera celda de la hoja de cálculo. El segundo método, que es el que se emplea en este caso, hace referencia a la celda A1 diciendo que se trata de la celda F1C1, que significa Fila 1 y Columna 1. En idioma español se pone F por la palabra Fila, pero he descubierto que en algunas configuraciones regionales del idioma español, la F da error y se ha de poner la letra L de Linea.
      Prueba esto, ya que puede ser causa del error que comentas.
      No olvides que por tratarse de una fórmula matricial después de cambiar la F por una L debes validar, no pulsando Enter, sino pulsando tres teclas simultáneamente: CONTROL+SHIFT+ENTER.

      Eliminar
  31. Hola Adolfo, una consulta con los rangos dinámicos de los gráficos. Como puedo hacer para que el gráfico no lea las celdas que tienen formula, pero estan blancas ("").

    Saludos

    ResponderEliminar
    Respuestas
    1. Hola Vera.
      El problema que surge al hacer un gráfico donde tienes valores que son una celda vacía del tipo "" es que lo toma como si fuera cero, y lógicamente el gráfico queda muy raro, ya que en esos puntos la gráfica se va hasta el cero.
      Pero este problema no surge únicamente cuando se usa el gráfico dinámico sino siempre que realices un gráfico donde aparezcan esas celdas vacías entre medias de las otras.
      La solución es eliminar esas filas de datos, por ejemplo, reagrupando.
      Otra posible solución es hacer una macro que cuando detecte que la celda que aparentemente está vacía pero tiene una "" la convierta en una verdadera celda vacía. Esto se podría conseguir gracias a que existe la función ESBLANCO.

      Eliminar
    2. Hola Adolfo. Tienes unos conocimientos excelentes.
      Tengo una duda sobre textos con campos fijos y variables en Excel. Quisiera saber si es posible tener celdas con anchura variable. Es decir, al igual que en Word puedes insertar en cualquier parte del documento una palabra y el texto a su derecha se desplaza, quisiera saber si en Excel se puede hacer, cuando la celda en cuestión es un campo variable, vinculado a otra celda de texto donde se introducen los datos. El objetivo es hacer una carta estándar donde hay texto fijo y variable, dependiendo de los datos del cliente.
      De esta manera si el texto es más largo, la longitud de la celda se adapta, permitiéndo que se pueda leer su contenido y el del texo fijo a su derecha.

      Gracias y felices fiestas.

      Eliminar
  32. hola buen dia Adolfo!! hay manera que (tomare de ejemplo la tabla del inicio) si un valor de la columna A se repite (A2 por decir uno, y en el A12 se repite), las demas celdas de la fila 12 (B,C,D) se rellenen automaticamente con B2, C2, D2? (es decir con la fila de la celda repetida)

    ResponderEliminar
    Respuestas
    1. Hola. Puedes ver el siguiente vídeo para resolver el caso que planteas. Espero que te resulte útil.
      http://youtu.be/s_okhpgUt0Q

      Eliminar
  33. "Hola, quisiera que me ayudara con una formula por favor. Tengo 40 mil filas con datos, de los cuales debo agrupar cada 1440 datos (filas) y sumarlos. La idea es que yo arrastre una formula que vaya sumando cada 1440 datos. Se puede hacer esto?. Gracias"

    Tengo el problema este, pero no se resuelve con media movil, sino que necesito sumar eso 1440 datos y que no se vuelvan a sumar,por esto es que no me sirve la media movil.

    De antemano, gracias!!

    ResponderEliminar
    Respuestas
    1. Hola. En el siguiente enlace se resuelve el caso que planteas. Espero te resulte útil para tu trabajo.
      Media Móvil a intervalos fijos

      Eliminar
  34. Hola Adolfo
    La verdad no he podido encontrar solución a mi caso, por que estoy trabajando en una hoja de Excel donde tengo un formulario con listas desplegables que se enlazan con valores($) en otra columna, además yo ingreso solo la cantidad (otra columna), la hoja también cuenta con una celda de auto incrementación, lo que necesito es que los datos se almacenen en diferentes bases de datos dependiendo de la selección de una de las listas desplegables (Establecimiento de destino), y que esto se realice cuando envíe a imprimir o se guarde.

    Desde ya muchas gracias.

    Atento a tus comentarios, saludos cordiales.

    ResponderEliminar
  35. Hola Adolfo, enhorabuena por este blog. Tengo un problema con Excel 2007 que seguro es una tontería pero que me trae de cabeza. Cuando creo un gráfico en el que el eje de abcisas es un conjunto de fechas, el gráfico usa un rango desde el año 1900 hasta mas allá del 2100 con lo cual las fechas que me interesan se quedan muy concentradas y poco útil.
    Gracias y un saludo. Carlos

    ResponderEliminar
    Respuestas
    1. Hola Carlos.
      Debes pulsar con el botón derecho del ratón sobre el eje del gráfico que deseas ajustar, en este caso el horizontal, y ajustar la escala pulsando sobre la opción "Dar formato a eje", donde pone máxima y mínima no lo dejes en Automática y ponla Fija. Los valores mínimo y máximo los debes calcular previamente según la fecha incial y final que desees. Ya sabes que Excel asigna a cada fecha un valor. Esto lo pudes hacer poniendo una fecha en una celda y luego la pones con formato de celda General.
      Un saludo.

      Eliminar
    2. Ok, Adolfo. Todo perfecto. Muchas gracias. Carlos

      Eliminar
  36. Tengo una macro para asignar cartera a los gestores de cobranza, los rangos varian y es cansado modificarlos manualmente alguna idea para solucionarlo?

    ResponderEliminar
  37. adolfo buenas noches, quisiera si es posible me ayudaras con una formula o que debo haer para que al moemtno de insertar unos datos en excel el me arroje un indicador dentro de un grafico preestablecido como imagen o creado alli, es como por ejemplo sobre una imagen de una montaña, con los datos que yo le ingrese al excel, el ubique el punto de indicador sobre esta iamgen, en el lugar especifico, al que correspondan los valores.

    gracias

    ResponderEliminar
  38. Hola Adolfo, tengo una base de datos gigante donde se registran valores con el simbolo <1, <1, <2, etc (por celda) cuando se encuentran por debajo de un límite de detection, estos valores no son considerados como numerous por lo cual no se pueden gráficar ni realizer operaciones, la consulta es si hay alguna manera de darles la validación para que funcionen como numerous.

    ResponderEliminar
    Respuestas
    1. Hola. Si son números muy pequeños, intenta ponerlos en notación científica. Luego al representarlos gráficamente lo que sucede es que si representas números muy pequeños junto a otros que son mucho mayores, lo que sucede es que los pequeños ni se ven en el gráfico. Tendrías que representar de forma conjunta los números que estén en un mismo rango de valores para que se puedan comparar unos con otros.
      Un saludo.

      Eliminar
  39. Hola Adolfo, excelente aporte, muy interesante esta fórmula, más de una vez me he vuelto loco para solucionar el problema del tamaño de una tabla.
    Un pequeño aporte que puede servirles a aquellos que tienen tablas en las cuales no todas las columnas tinen siempre datos, por lo general la primer columna si tiene todas sus celdas ocupadas pero es muy común que la última columna tenga celdas vacías, así la formula para la tabla en lugar de ser =DESREF(Hoja1!$B$2;0;0;CONTARA(Hoja1!$E:$E);4)sería =DESREF(Hoja1!$B$2;0;0;CONTARA(Hoja1!$B:$EB);4).
    Gracias nuevamente y saludos!!


    ResponderEliminar
  40. Hola Adolfo, excelente tutorial eso si la aplique a un informe que mes a mes le cambio el noombre del mes ej: informe abril luego informe mayo y la formula se queda pegada al informe abrl no el de mayo y pierde el rango

    ResponderEliminar
  41. Hola Adolfo, buenas tardes, me puedes guiar para pegar los subtotales únicamente en documento nuevo sin que se muestre las filas anteriores que es la que generó el total de los datos, no sé sí me explique correctamente.

    ResponderEliminar
  42. Buenos días, quería saber alguna idea para lo siguiente: tengo seis celdas con los valores que extraigo de una matriz, estas celdas me generan un gráfico, quería copiar las celdas y el gráfico 365 veces, copio y pego, las celdas se me actualizan al copiar y pegar varían en función de los datos de la matriz pero los datos del gráfico siempre son los del primero, no se me actualiza el nuevo gráfico a las celdas que acabo de copiar.

    ResponderEliminar
  43. hola:
    Estoy trabajando con una base de datos y requiero que al hacer una búsqueda de un item en particular, se genere un gráfico con los datos que correspondan a ese item.O sea los datos de la gráfica ( que debe der de lineas) van a ser más o menos según el item buscado. ¿Como hago para que el gráfico modifique el número de filas que representará en función del número de datos encontrados en la base de datos para un item específico? Saludos

    ResponderEliminar
  44. Muy buen aporte, muchas gracias. Me he encontrado el siguiente problema con 365, tengo varias consultas de Power Query en el mismo libro además de algunas columnas calculadas. Al cambiar el número de filas del origen de datos las columnas calculadas no agregan las fórmulas a los nuevos registros; mi pregunta es: ¿debo crearlas en la propia consulta o puedo hacerlo en la propia tabla?

    ResponderEliminar
    Respuestas
    1. Me auto-respondo (por si le sirve a alguien). El problema era el orden de ejecución de las consultas, para que excel rellene toda la tabla de la consulta con la misma fórmula (la fórmula hace referencia a otra consulta), tiene que ejecutarse esa consulta en último lugar.

      Eliminar
  45. Hola Adolfo,
    Excelente blog, muchas gracias por tus aportaciones.
    Tengo una duda con un grafico de serie, imagínate que tienes 4 series y una de ellas hay veces que esta en blanco o tiene datos, dependerá si es elegida mediante un formulario, ¿cómo puedo evitar que cuando no hay datos se elimina esa serie del rango y asi no me aparezca la leyenda pero sin nombre?
    Quiero quitar que aparezca la leyenda pero solo lo puedo hacer manualmente entrando en opciones de grafico y excluir esa serie y quiero hacerlo automáticamente o con formulas.
    He intentado nombrar un rango con DESREF e utilizarlo en la selección de rango del gráfico pero tampoco si los datos están en blanco si apareciendo.
    Esta la solución en construir una tabla dinámica y sacar graficos sobre ella y evitar "blank"? no encuentro solución, si me puedes orientar lo agradezco. Muchas gracias.

    ResponderEliminar
  46. Hola, estimado, como seria hacer un grafico directamente de una tabla de registros (sin tabla dinámica), La tabla de registros es dinámica porque puede tener mas o menos filas dependiendo de la actividad del día. Por supuesto con VBA

    ResponderEliminar