7

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:

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 (1560 clics)
Tamaño: 17 KB

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

¡Compartir es vivir!
Sergio
 

La destreza y el perfeccionismo quizá sean las dos virtudes que me permiten ayudar a mis clientes a facilitar las tareas administrativas de sus negocios.

Haz clic aquí para dejar un comentario 7 comentarios
Albert M

Muchísimas gracias Sergio

Responder
danilo

Gracias por compartir tus conocimientos.
Saludos
Danilo

Responder
Elizabeth

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

Responder
Santiago

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

Responder
    Sergio

    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á.

    Responder
Rodrigo

Excelente explicación. Gracias Sergio.

Responder
Cesar

Perfecto!!!…

Me ahorro muchos problemas.

Gracias y saludos.

Responder

Escribe una respuesta: