Rangos dinámicos con Listas

viernes, junio 12, 2009

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:

11 comments:

Hanzz 12 junio, 2009 16:45  

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.

patricioc 12 junio, 2009 17:23  

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.

Anónimo,  12 junio, 2009 17:38  

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

Jorge L. Dunkelman 12 junio, 2009 18:20  

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.

Edwin Reyes 12 junio, 2009 18:23  

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

Robert Blanco,  12 junio, 2009 20:54  

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.

Anónimo,  26 junio, 2009 17:29  

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

Jorge L. Dunkelman 26 junio, 2009 17:45  

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

giuliano_ 05 agosto, 2009 01:11  

jorge,
pensé que se podría hacer una lista desplegable (validación de datos) tomando como referencia de origen el nombre que te da alguna columna de la tabla al seleccionarla, pero, lamentablemente, lo que pensé que podría ser una solución espléndida... no resultó---

ej: en origen escribía:
=Tabla6[NOMBRE DOC]
,pero me daba error de fórmula... (?)

ojalá me puedas dar alguna indicación...

gracias, giuliano_

Jorge L. Dunkelman 05 agosto, 2009 18:58  

Giulano

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

Anónimo,  23 marzo, 2012 19:50  

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

Publicar un comentario

Google+ Followers

Seguidores

Google+ Badge

Términos Legales

  © Blogger template On The Road by Ourblogtemplates.com 2009

Back to TOP