Cómo crear un rango dinámico con DESREF y CONTARA

Hoy te voy a mostrar un ejemplo de cómo evitar errores en la presentación de una tabla dinámica y asegurarte de que estás trabajando con el origen de datos adecuado.

En una empresa de suministro de material de papelería se preparan los envíos que llegarán a las tiendas para su venta.

Cada trabajador debe preparar y valorar el material que suministra entregando un informe al coordinador y éste lo introduce en una base de datos en Excel.

El coordinador elabora mensualmente un informe de envíos que presenta a sus superiores. Se trata de una tabla dinámica como la de la imagen:

Cada mes, el coordinador agrega nuevos datos, por lo que tiene que modificar el rango de celdas para que la tabla dinámica los aplique en el informe.

Antes de empezar

¿Qué es un rango dinámico?

Un rango de Excel es un conjunto de celdas que opera bajo un mismo nombre. Cuando tengas que hacer referencia a todo el conjunto, puedes sustituir la referencia del rango por su nombre. Simplifica mucho el trabajo porque es más fácil recordar una palabra que una referencia…

Ahora bien, cuando este conjunto de celdas aumenta o disminuye su tamaño pueden producirse problemas o errores porque las referencias no son capaces de adaptarse a su nueva dimensión. Aquí entran en acción los rangos dinámicos.

Gracias a los rangos dinámicos podremos hacer referencia a un conjunto de celdas que se ajustará de forma automática al agregar o eliminar datos en celdas contiguas

¿Cómo evitar modificar el rango de datos?

La mejor forma de no tener que modificar el rango nunca más es hacer que la tabla dinámica extraiga los datos de una base de datos que se ajuste automáticamente al tamaño que tenga.

Te mostraré paso a paso cómo hacerlo. Para eso voy a emplear las funciones DESREF y CONTARA.

La función DESREF

La función DESREF de puede utilizar de dos formas diferentes:

• Usando los tres primeros argumentos sirve para extraer el valor de una celda que se encuentra dentro de una tabla. Para utilizarla tienes que darle una referencia o punto de partida desde el cual comenzarán a contar un número de columnas y de filas hasta dar con el valor deseado.
• Si utilizas los cinco argumentos que tiene DESREF, la función se convierte en matricial, ya que además de desplazar la referencia un número determinado de filas y columnas, devuelve los valores de varias celdas a la vez (ancho y alto).

La versión que nos interesa es la matricial. Su sintaxis es:

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

La función CONTARA

La función CONTARA cuenta todas las celdas de un rango que no estén vacías. Se diferencia de CONTAR en que ésta solo cuenta las celdas que contienen números.

La sintaxis de CONTARA es la siguiente:

=CONTARA(valor1;[valor2];…)

Creación del rango dinámico

Una vez que conoces para qué sirven DESREF y CONTARA, es hora de crear el rango dinámico y asignárselo a un nombre (que he llamado Tabla) para que incluya no solo desde la celda A1 hasta la D78, sino para que se adapte a los nuevos tamaños que tendrá en el futuro.

• Haz clic en la ficha Fórmulas y a continuación selecciona Administrador de nombres.
• Pulsa en el botón Nuevo.
• En el cuadro de diálogo que aparece, escribe el nombre que desees asignar al nombre (en mi caso, lo he nombrado como Tabla) y en Hace referencia a, escribe la siguiente fórmula:

=DESREF(Datos!$A$1;0;0;CONTARA(Datos!$A:$A);CONTARA(Datos!$1:$1)) 

¿Y ahora qué?

Ahora simplemente crea la tabla dinámica introduciendo el nombre Tabla en el cuadro de diálogo que aparece al seleccionar la ficha Insertar y pulsar en Tabla dinámica.

Descárgate el ejemplo:

¿Te ha resultado útil? ¡¡Comparte!!