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.