Tablas dinámicas con origen de datos dinámico

Un elemento fundamental de las tablas dinámica es su origen de datos. Si el origen se amplia o disminuye tendremos que cerciorarnos de que al actualizar la tabla dinámica ésta refleje correctamente los datos. Aunque podemos cambiar el origen manualmente, en esta entrada trataremos tres opciones para crear un origen de datos dinámicos. Partimos del siguiente ejemplo:

Si añadimos un nuevo registro y actualizamos la tabla, ésta mostrará los mismo resultados. Sin embargo, tenemos distintas opciones para crear un rango autoajustable como origen de datos.

Tabla como origen de datos

1. Nos situamos sobre una celda del rango.

2. En el grupo Tablas de la ficha Insertar, clic en Tabla. O presionar Ctrl+T.

Por defecto Excel selecciona el área actual alrededor de la celda activa (el área de datos delimitada por filas en blanco y columnas en blanco). Activamos la casilla de verificación La tabla tiene encabezados, pues todas las tablas dinámicas deben tener encabezados de columna.

A continuación cambiamos el origen de datos de la tabla dinámica. En Tabla o rango, escribimos el nombre de la tabla. Así la tabla dinámica incluirá, al actualizarla, los registros añadidos a la tabla de origen de datos automáticamente.

Proceso:

Rango dinámico como origen de datos: DESREF

1. Definimos un nuevo nombre. En el grupo Nombres definidos de la ficha Fórmulas, clic en Asignar nombre.

=DESREF(Hoja1!$A$1;;;CONTARA(Hoja1!$A:$A);3)
'Si desconocemos el nº de columnas:
=DESREF(Hoja1!$A$1;;;CONTARA(Hoja1!$A:$A);CONTARA(Hoja1!$1:$1))

Sintaxis: DESREF(ref; filas; columnas; [alto]; [ancho])

- Ref: Hoja1!$A$1. Celda que anclamos.
- Filas: Ø
- Columnas: Ø
- Alto: CONTARA(Hoja1!$A:$A). Nº de filas.
- Ancho: 3. Nº de columnas.

Rango dinámico como origen de datos: INDICE

1. Definimos un nuevo nombre. En el grupo Nombres definidos de la ficha Fórmulas, clic en Asignar nombre o en Administrador de nombres y en Nuevo.

=Hoja1!$A$1:INDICE(Hoja1!$1:$65535;CONTARA(Hoja1!$A:$A);3)
'Si desconocemos el nº de columnas:
=Hoja1!$A$1:INDICE(Hoja1!$1:$65535;CONTARA(Hoja1!$A:$A);CONTARA(Hoja1!$1:$1))

Sintaxis: Ref:INDICE

- Ref:Hoja1!$A$1: Celda que anclamos.
- INDICE(matriz;núm_fila;núm_columna). Devuelve la referencia de la última celda del rango.
- Matriz: Hoja1!$1:$65535. Toda la hoja: 65535 en Excel 2003, 1048576 en 2007.
- Núm_fila: CONTARA(Hoja1!$A:$A). Nº de filas.
- Núm_columna: 3. Nº de columnas

Nota: funciones volátiles

La diferencia entre usar DESREF o Ref:INDICE estriba en que DESREF es una función volátil mientras que INDICE no. Una función volátil se debe actualizar siempre que se efectúe un cálculo en cualquier celda de la hoja de cálculo. Por tanto, si usamos funciones volátiles en demasía, se ralentizará el proceso de recálculo de Excel. Otras funciones volátiles son: AHORA, HOY, ALEATORIO, ALEATORIO.ENTRE, INDIRECTO, INFO (dependiendo de los argumentos y CELDA (dependiendo de los argumentos).