Rangos Dinámicos en MS Excel

By | octubre 28, 2017

Crear rangos de origen cuyas dimensiones se ajustan automáticamente.


  • Definir un rango de origen dinámico

En general, todo rango dinámico está destinado a evolucionar con el tiempo, por lo menos en lo que respecta al número de filas. Si entre el momento de la creación de la tabla dinámica y el momento que se quiere actualizar dicha tabla se han añadido filas con nuevos datos al rango de origen. La actualización en la tabla dinámica no tendrá en cuenta esas nuevas filas.

La Sintaxis de la función es la siguiente:

=DESREF(Celda;Num Fil;Num Col;Alto;Ancho)

Esta función devuelve un rango de celdas de ancho ANCHO, de alto ALTO, desplazado respecto a la celda CELDA en Num Fil líneas y Num Col Columnas.

  • Celda es el rango a partir del cual se realiza el desplazamiento.
  • Num Fil corresponde al número de filas hacia abajo (si es positivo) o hacia arriba (si es negativo) que deben tenerse en cuenta.
  • Num Col corresponde al número de columnas hacia la derecha (si es positivo) o hacia la izquierda (si es negativo) que deben tenerse en cuenta.
  • Alto es la altura en número de filas de desplazamiento del rango que se devuelve.
  • Ancho es la anchura en números de columnas del rango que se devuelve.

NOTA: La función DESREF no desplaza físicamente las celdas, sino que devuelve las coordenadas de un rango de celdas cuyas dimensiones se han modificado y/o para el que la posición se ha desplazado respecto al rango definido en el argumento Celda.

  1. Número de filas variables

Para ver cómo se usa este método , vamos a utilizar el libro Excel formaciones.xlsx que contiene en una hoja llamada Lista con las Capacitaciones realizada por los empleados de una empresa por orden cronológico. Por definición, el número de filas aumenta cuando se registra una nueva capacitación.

Primero abrimos el archivo y lo dejamos como libro activo.

La estructura se muestra a continuación. La lista de capacitaciones se termina en la fila 78.

Nuestro objetivo es obtener el número de horas de capacitación realizadas por materia y por tipo.

En primer lugar hay que definir el rango dinámico.

  • En la pestaña Fórmulas, grupo nombres definidos, hacer clic en Administrador de nombres.

En el cuadro de diálogo Administrador de nombres haga clic en el botón Nuevo.

  • Introduzca el nombre del rango ListaFormaciones e ingrese la fórmula en la zona Hace referencia a:
=DESREF(LISTA!$a$1;;;CONTARA(LISTA!$A$1:$A$1000);9)

En este ejercicio la altura es igual al número de celdas llenas de la columna A, y el ancho es fijo e igual a 9.

La fórmula

=DESREF(LISTA!$a$1;;;CONTARA(LISTA!$A$1:$A$1000);9)

Es equivalente a:

=DESREF(LISTA!$a$1;0;0;CONTARA(LISTA!$A$1:$A$1000);9)

Los 2 argumentos Num Fil y Num Col están a cero, porque no queremos hacer desplazamiento de rango.

El rango se ha limitado a la fila 1000, ya que hemos considerado que no se harán más de 999 Capacitaciones en el 2011. En todo caso el máximo es 1.048.576 que sería el total de filas en el libro Excel.

  • Hacer clic en Aceptar.

El nombre del rango se agrega  en la lista del Administrador de Nombres.

  • Haga clic en Cerrar para terminar.

A continuación crearemos la tabla dinámica:

  • Hacer clic en la lista de origen, (en cualquier parte de nuestro rango de datos) y luego en la pestaña Insertar, haga clic en Tabla dinámica. Excel seleccionará automáticamente el rango activo (donde dejamos posicionado nuestro cursor) Lista$a$1:$I$78.
  • Vamos a sustituir este conjunto de celdas de rango fijo por nuestro rango dinámico, introduzca la fórmula (nombre de rango) =ListaFormaciones en lugar de las coordenadas y haga clic en Aceptar. También se puede presionar la tecla F3 en vez de escribirlo.

Configure los campos de la tabla dinámica tomando como referencia la siguiente pantalla:

  • Modifique las etiquetas tomando como referencia la siguiente imagen:

  • Para comprobar si nuestro rango dinámico funciona, añada manualmente algunas líneas de datos al final de la hoja Lista
  • Luego haga clic en la tabla dinámica y actualice.

En la siguiente publicación veremos como dinamizamos las columnas además de las filas como las hemos realizado en este apartado.

No te olvides de compartir y darle un Me gusta a esta publicación!!

Deja un comentario

Tu dirección de correo electrónico no será publicada. Los campos obligatorios están marcados con *