jueves, octubre 06, 2011

Control de saldos de bancos con Excel.

Ariel me consulta cómo hacer para manejar en una única tabla los movimientos y saldos de varios bancos. Supongamos esta tabla



¿Cómo calculamos el saldo? Como prefiero usar una única fórmula para la columna propongo poner en la celda F2 ésta

=SUMA($D$2:D2)-SUMA($E$2:E2)

y copiarla a lo largo del campo



Préstese atención a las referencias semi-absolutas en los rangos de la fórmula.

El problema se nos presenta, obviamente, al filtrar la lista para ver los movimientos y el saldo de uno de los bancos



El saldo correcto es 2,495.00. Podemos solucionar esta situación usando SUBTOTALES. En F2 ponemos

=SUBTOTALES(9,$D$2:D2)-SUBTOTALES(9,$E$2:E2)

y la copiamos al todo el rango de la columna



SUBTOTALES suma sólo los valores de las celdas visibles, con lo que al filtrar el resultado será el correcto



Podemos mejorar notablemente nuestro modelo convirtiendo el rango de los datos en una “tabla” (o “lista” en Excel 2003).



Las tablas/listas tienen varias ventajas:


  • Formato automático
  • Las fórmulas en las columnas son copiadas automáticamente al agregar filas
  • La fila de totales
  • Actualización automática de todo objeto ( fórmulas, gráficos, tablas dinámicas) basados en la tabla



El archivo con el ejemplo se puede descargar aquí.

9 comentarios:

  1. Perfecto Jorge, sin tanto quebradero de cerebro una solución audaz al problema que tenía. Agundis.

    ResponderBorrar
  2. Hola Jorge, espero que me puedas ayudar mira lo convierto el rango de los datos en una tabla dinámica como en el último gráfico, que se visualiza pero en (Ingreso o egreso) introduzco datos numérico me suma o me resta el mismo resultados cuando le doy Enter.
    Espero tu respuesta.
    Un saludo.

    ResponderBorrar
  3. ¿Creaste una tabla o una tabla dinámica (pivot table)?
    Si se trata de una tabla/lista, fijate que la opción de cálculo de la hoja esté definida como "Automática".
    Si hiciste una tabla dinámica tienes que actualizarla para que refleje los cambios o una una técnica como la que muestro en esta nota.

    ResponderBorrar
  4. Estimado Jorge: como siempre sencillas, prácticas y eficaces soluciones.
    Muchas gracias por tus aportes.
    Charly

    ResponderBorrar
  5. Hola Jorge, te molesto por el post "Evitar borrar Validación de datos en Excel al pegar datos copiados" Quiero cambiar el rango pero no se como hacerlo. Es la primera vez que intento usar los macros. El rango que yo necesito deberia ser toda la columna B. Se puede?

    ResponderBorrar
  6. Se puede con facilidad, pero tienes que dejar el comentario en la nota correspondiente.

    ResponderBorrar
  7. Hola que tal , podrias postear el archivo.

    gracias. saludos

    ResponderBorrar
  8. A tu pedido acabo de poner un enlace para la descarga.

    ResponderBorrar
  9. hola creo que se debería usar: =SUBTOTALES(109,$D$2:D2)

    ResponderBorrar

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