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

Rango dinámico Excel

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:

Tabla dinámica Excel

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.

Excel funciones CONTARA y CONTAR

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.

Tabla dinámica rango dinámico

Descárgate el ejemplo:

Optimizar tabla con rangos dinámicos
Título: Optimizar tabla con rangos dinámicos (288 clics)
Tamaño: 17 KB

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

Cómo llegar a fin de mes con menos estrés gracias a Excel

Antes del día 15 puedes tener todos los objetivos mensuales completados. Y los que no, dejarlos programados para que se cumplan sin requerir de tu tiempo.

He preparado 7 de mis mejores trucos explicándote cómo lo hago. Deja tu correo abajo y te enviaré el primero de ellos.

9 comentarios en “Cómo crear un rango dinámico con DESREF y CONTARA”

  1. Hola buenas tardes
    ¿que pasa si mi base de datos comienza en A3 y no en A1?
    Gracias!

  2. Hola
    Me gustaria saber como seleccionar los 3,4,5 o los elementos que sean, de una tabla,y que queden seleccionados
    Gracias

    1. Santiago, lo mejor que puedes hacer es aplicar un filtro a la tabla seleccionando los datos que deseas que se muestren. El resto se ocultará.

  3. buen día Sergio
    estoy tratando de hacer dos pivot con las datas separadas en la misma hoja y quiero hacerlo con una macro con rangos dinámicos ya que no siempre son la misma cantidad de filas ¿como podría hacerlo? ejemplo es 1 data débitos 2 data créditos las pivot se utilizarían para hacer la comparación de la data1 vs data2
    gracias quedo atento a tus comentarios

Los comentarios están cerrados.

Sergio

Sergio

Experto formador en Excel y Power BI con más de dos décadas de experiencia. Capacito a profesionales para optimizar su trabajo y ser más eficientes. Con un enfoque práctico y cercano, mi objetivo es ayudarte a dominar estas herramientas esenciales. Descubre mis formaciones.MVP de Microsoft 5 años consecutivos.

Cómo llegar a fin de mes con menos estrés gracias a Excel

Antes del día 15 puedes tener todos los objetivos mensuales completados. Y los que no, dejarlos programados para que se cumplan sin requerir de tu tiempo.

He preparado 7 de mis mejores trucos explicándote cómo lo hago. Deja tu correo abajo y te enviaré el primero de ellos.

Buscar

Últimos posts

¿De qué hablo aquí?

Cómo llegar a fin de mes con menos estrés gracias a Excel

Antes del día 15 puedes tener todos los objetivos mensuales completados. Y los que no, dejarlos programados para que se cumplan sin requerir de tu tiempo.

He preparado 7 de mis mejores trucos explicándote cómo lo hago. Deja tu correo abajo y te enviaré el primero de ellos.