miércoles, 15 de junio de 2011

Rango Dinámico

Descargar el fichero: RangoDinamico.xls


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.


27 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
  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
  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