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.
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)
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
="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)
También aquí usamos rangos “normales”.
Ahora agregamos los movimientos del día 08/01/2009
Cuando pasamos a la hoja “saldos” vemos que tanto el título como los saldos se han actualizados. Así de simple!
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
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: MS Excel
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.
ResponderBorrarSin 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.
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?.
ResponderBorrarGracias.
excelente explicacion Jorge no tenia bien claro el uso de tablas en excel 2007. gracias.
ResponderBorrarEso 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.
ResponderBorrarHola...
ResponderBorrarComo 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
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!!Muchas gracias por la entrada¡¡
ResponderBorrarEs 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.
Hola Rocío
ResponderBorrarpuedes usar Validación de Datos para evitar que se introduzca un valor que convierta al saldo en negativo.
Giulano
ResponderBorrartenes que usar la función INDIRECTO como muestro en esta nota.
MUCHISIMAS GRACIAS, ACABO DE SALIR DE UN PROBLEMA ENORME!!! :D
ResponderBorrar