viernes, junio 12, 2009

Rangos dinámicos con Listas

Una tarea frecuente en Excel es crear rangos dinámicos. La técnica más difundida es crear un nombre (Insertar-Nombres-Definir) con una fórmula que combine DESREF y CONTARA.

Una técnica alternativa más sencilla es usar Listas (Excel 2003) o Tablas (Excel 2007). Esta funcionalidad es muy útil y permite simplificar nuestros modelos en Excel.

En una nota anterior mostramos como crear con facilidad un gráfico dinámico usando Listas. En esta nota mostraremos cómo crear un modelo dinámico.

Como ejemplo construiremos un modelo para manejar el inventario de un almacén/depósito. En un cuaderno Excel creamos dos hojas: “movimientos” y “saldos”. En la primera anotamos los movimientos de los productos en el almacén (entradas – salidas); en mostramos los saldos actualizados de los productos.



Rangos dinámicos con Listas

En la hoja “movimientos” tenemos ahora un cuadro de datos en el rango A1:D31. Para transformar este rango en Lista, usamos el menú Datos-Lista (o Ctrl+Q)

Rangos dinámicos con Listas


Rangos dinámicos con Listas

Al apretar Aceptar veremos que Excel selecciona todo el rango, activa Autofiltro y en la primer fila libre aparece un asterisco azul. A partir de este momento, cada vez que agreguemos datos a la lista, ésta se expandirá automáticamente.

En la celda A1 de la hoja “saldos” combinamos texto y funciones para crear un título dinámico
Rangos dinámicos con Listas

="Saldos a la fecha "&TEXTO(MAX(movimientos!C2:C31),"dd/mm/yyyy")

Como pueden ver usamos una referencia estática al rango de las fechas en la hoja “movimientos”.
Para calcular los saldos actualizados usamos la fórmula

=SUMAR.SI(movimientos!$A$2:$A$31,saldos!A4,movimientos!$D$2:$D$31)
Rangos dinámicos con Listas

También aquí usamos rangos “normales”.

Ahora agregamos los movimientos del día 08/01/2009

Rangos dinámicos con Listas

Cuando pasamos a la hoja “saldos” vemos que tanto el título como los saldos se han actualizados. Así de simple!

Rangos dinámicos con Listas

En Excel 2007, el mecanismo es similar, pero la funcionalidad Lista ha pasado a llamarse Tabla. Para convertir un rango en Tabla usamos el icono Tabla en la pestaña Insertar

Rangos dinámicos con Listas

Tanto en Excel 2003 como en Excel 2007, la forma más cómoda y eficiente de agregar datos en la lista/tabla, es usando Tab.



Technorati Tags:

10 comentarios:

  1. el arterisco no aparece en excel2007, ni tampoco el marco en azul; solo el pequeño marcador de 3 cuadritos en L invertida, abajo a la derecha de la tabla;tal como el que aparece en la sexta imagen.

    Sin embargo, al escribir en la fila en blanco inmediatamente abajo de la tabla, esta expande automáticamente su rango, aplicando los formatos que estén prediseñados.

    ResponderBorrar
  2. Jorge, como siempre, excelente artículo, solo una pregunta, qué pasa cuando los datos pasan, según tu ejemplo, la fila 31?, hay alguna manera de hacer dinámicas también las fórmulas de título de "saldos" y los saldos propiamente dichos?.

    Gracias.

    ResponderBorrar
  3. excelente explicacion Jorge no tenia bien claro el uso de tablas en excel 2007. gracias.

    ResponderBorrar
  4. Eso es justamente lo que explica la nota. Fijate en la anteúltima imagen y verás que el rango de la fórmula se ha ajustado hasta la fila 36.

    ResponderBorrar
  5. Hola...

    Como siempre Jorge, ta sacas unos secretos de excel maravillosos, sin duda alguna una forma muy eficiente de manejar listas dinamicas.

    Nuevamente Mil y un gracias.

    Saludos

    ResponderBorrar
  6. Es importante considerar, como ya habia sugerido Usted antes en otras consultas, la forma de disponer los datos. Siempre conviene organizar como si fuera una base de datos. Tal es el caso de este ejemplo que muestra, como las salidas y entradas se cargan en un campo "movimiento", algo que un usuario novato tal vez lo usaría en 2 columnas distintas.

    ResponderBorrar
  7. !!Muchas gracias por la entrada¡¡

    Es realmente útil.


    Por favor,abusando de su paciencia. En el ejemplo nos aparecen cantidades negativas. ¿Cómo podríamos hacer para evitar que se produzcan salidas cuando no hay producto suficiente?. ¿A alguién se le ocurre?.

    Muchas gracias por su atención, su tiempo y paciencia.

    Saludos desde España¡¡

    Rocío. Fuenlabrada.

    ResponderBorrar
  8. Hola Rocío
    puedes usar Validación de Datos para evitar que se introduzca un valor que convierta al saldo en negativo.

    ResponderBorrar
  9. Giulano

    tenes que usar la función INDIRECTO como muestro en esta nota.

    ResponderBorrar
  10. MUCHISIMAS GRACIAS, ACABO DE SALIR DE UN PROBLEMA ENORME!!! :D

    ResponderBorrar

Nota: sólo los miembros de este blog pueden publicar comentarios.