miércoles, 24 de junio de 2009

Rangos dinámicos: función DESREF.

No podemos pasar por alto una de las utilidades más empleadas a la hora de trabajar en Excel: los 'rangos dinámicos'.
Definiremos primero qué entendemos por rango dinámico en Excel; sabemos que podemos nombrar los rangos que utilizamos, pero lo habitual es que estos rangos no sean fijos, es decir, no tenga siempre (a lo largo de nuestro trabajo con la Base de datos en cuestión) la misma dimensión, i.e., el mismo número de registros. Es en este punto cuando Excel nos proporciona la posibilidad de crear un rango dinámico. Y lo haremos desarrollando la función DESREF, anidando otra función importante como CONTARA en ella.
Para visualizar el resultado que obtendremos combinando ambas funciones, ejercitaremos otra herramienta de Excel como es la validación (se explicará en su momento).
Supongamos un listado de paises, que no tenemos aún finalizado, i.e., se incrementa (o decrece) con el tiempo. Lo primero que tenemos que realizar es el crear un nombre, al que asignaremos la característica de dinámico mediante la función:
=DESREF(Hoja1!$A$2;;;CONTARA(Hoja1!$A:$A)-1;1)


En esta ocasión empleamos DESREF para determinar un rango que empieza en la celda A2 y que tendrá un alto dado por la función CONTARA(A:A)-1, es decir, cuenta todas las celdas no vacias de la columna A, y le resta Uno para discriminar el rótulo de la columna.
Una vez generado el nombre 'pais', ya podremos emplearlo como se comentó anteriormente con la herramienta Validación con la característica Lista:


que aplicado en la celda C4 nos permite desplegar el listado de paises:


nada nuevo hasta aquí. La verdadera ventaja de esta función es visible cuando incrementamos el listado de paises; situación por la que no nos tendremos que procupar a la hora de redefinir rangos ni funciones, ya que tal cual quedó configurada reconocerá los nuevos elementos del listado.


Cuenta el número de elementos existentes en la columna A (le resta uno), o que nos dá a altura del rango dinámico a contar desde la celda de inicio A2.
Nuestra función DESREF, aplicada en esta forma, es igualmente válida si pretendiéramos ampliar dinámicamente tanto el alto como el ancho de nuestro rango, ya que los argumentos de la función son:
=DESREF(celda inicio rango;num filas; num columnas; alto; ancho)
Veremos en posteriores entradas otros usos de la función DESREF.

12 comentarios:

  1. Solucion practica, bien explicada.
    Un pequeño cambio dependiendo del idioma:
    Los (;) cambian por (,)

    ResponderEliminar
  2. Excelente, gracias por el aporte

    ResponderEliminar
  3. ¡Genial! Me simplifica mucho trabajo, ¡muchas gracias!

    ResponderEliminar
  4. Muchas Gracias, una gran solución a grandes problemasss!

    ResponderEliminar
  5. Esta chebre la pagina pero no tiene lo que nesesito

    ResponderEliminar
  6. Hola,
    si me dices qué necesitas, quizá pueda ayudarte...
    Slds

    ResponderEliminar
  7. Hola que tal? resulta que dejo algunas celdas en blanco en el rango dinamico, por ejemplo entre la lista de paises y no me los toma en la lista. Porque será? Muchas gracias por la ayuda! Saludos, Nora

    ResponderEliminar
    Respuestas
    1. Hola!!
      fijaté que para definir el rango dinámico usamos la función CONTARA, que cuenta celdas con contenido, el valor de conteo es que usamos luego para decir de donde hassta conde llega el rango...
      Si tienes valores con o sin contenido desde A1:A20, diez de ellos alternos vacíos, a la hora de usar DESREF con la función CONTARA estaremos diciendo que empiece en A1 y termine en A10 !!!, yta que sólo cuenta 10 elementos
      Slds

      Eliminar
  8. Hola,
    estoy intentando que de una base de datos, me extraigan todos los registros que son iguales.
    Le he intentado con buscarv, pero de esta manera solo consigo que aparezca el primero, pero no los siguientes.
    No sé si la formula desref me ayudaría en este caso o si tengo que utilizar otra formula.
    ¿Me podría indicar como?
    Muchas gracias

    ResponderEliminar
    Respuestas
    1. Hola,
      no, claro, ni BUSCARV ni DESREF pueden ayudarte a conseguir de un listado (base de datos) registros iguales.
      Quizá otro tipo de herramientas como Filtro (Autofiltro o Filtro avanzado) puedan ayudarte.
      O incluso combinar el formato condicional > Resaltar reglas de celdas > Duplicar valores, y luego aplicar un Filtro de la base de datos por color de fondo de celda pueda resultarte.

      Sin saber cómo es tu base de datos y sin conocer si esos registros únicos corresponden exclusivamente a un valor repetido poco más puedo comentarte.
      Slds

      Eliminar
  9. Buenos días: me colabora por favor, ya encontré realizar un gráfico dinámico que se actualiza automáticamente; ahora necesito sólo graficar los últimos 5 (cinco) datos de una tabla que incorpora una fila diariamente. Muchas gracias.

    ResponderEliminar
    Respuestas
    1. Hola Javier,
      en próximas fechas (la semana que viene) subiré una explicación de cómo conseguir lo que quieres...
      Slds

      Eliminar