Ahora veremos un ejemplo sobre como calcular saldo de clientes en excel, estas formulas nos pueden ayudara a diseñar una hoja de cuentas por cobrar en excel y nuestro trabajo sera mucho mas sencillo, sobre todo tomando en cuenta que estos cálculos se realizaran en segundos y no sera necesario pasar horas realizando los cálculos para obtener los saldos.
Lo primero es diseñar de manera correcta la base de datos donde se registraran tanto las facturas emitidas como los pagos que nuestros clientes realizan, es muy importante que todos los registros se realicen en una misma hoja, esto posteriormente facilita la generación de reportes o cálculos importantes, no es muy recomendable usar 1 hoja para cada cliente o para cada mes.
Entonces tenemos una hoja de registro como esta.
Si observamos es una hoja muy simple pero cuenta con los datos mas importantes, obviamente tu puedes agregar otros que consideres importantes o necesarios.
También tenemos una hoja de clientes o catalogo de clientes, esta hoja solo la usaremos para generar una lista desplegable con los nombres de nuestros clientes ( opcional ).
Luego tenemos una hoja donde queremos calcular los saldos de clientes, la idea es que al poner el nombre del cliente, automáticamente debe calcular el saldo según los datos registrados en la base de datos.
Aquí se usa validación de datos para que salga esta lista desplegable y seleccionar el cliente, para esto solo debemos seleccionar la celda donde va la lista/ vamos a menú datos/ validación de datos/ en permitir seleccionamos la opción “Lista”/ en origen ponemos =clientes!$A$2:$A$6/ aceptar.
En cargos ponemos la formula =SUMAR.SI(bd!$C$2:$C$65536,consultas!B7,bd!$D$2:$D$65536)
En abonos ponemos =SUMAR.SI(bd!$C$2:$C$65536,consultas!B7,bd!$E$2:$E$65536)
En saldo ponemos =C7-D7
Si queremos hacer todo el calculo en una sola celda =SUMAR.SI(bd!$C$2:$C$65536,consultas!B7,bd!$D$2:$D$65536)-SUMAR.SI(bd!$C$2:$C$65536,consultas!B7,bd!E2:E65536)
Si cambiamos el cliente, de manera automática se realiza el calculo para ese, esto basado en los registros de la hoja de base de datos, por eso es importante que todos los registros se hagan en una sola hoja.
Otra opción es calcular el saldo por numero de factura.
En este caso se pone el numero de factura y debemos obtener el nombre del cliente, el importe de la factura, abonos y saldo.
Para obtener el nombre del cliente usaremos esta formula =BUSCARV(B12,bd!$A$2:$C$65536,3,FALSO)
Para el importe =SUMAPRODUCTO((bd!$A$2:$A$65536=consultas!B12)*(bd!$D$2:$D$65536))
Para los abonos =SUMAPRODUCTO((bd!$A$2:$A$65536=consultas!B12)*(bd!$E$2:$E$65536))
El saldo =D12-E12
Si deseamos calcular el saldo en una sola celda =SUMAPRODUCTO((bd!$A$2:$A$65536=consultas!B12)*(bd!$D$2:$D$65536))-SUMAPRODUCTO((bd!$A$2:$A$65536=consultas!B12)*(bd!$E$2:$E$65536))
Vemos que en esta ocasión usamos la función SUMAPRODUCTO, en realidad también podemos usar la función SUMAR.SI , solo quise mostrarles otra opción.
Cabe mencionar que con SUMAPRODUCTO es posible hacer estos cálculos con mas de 1 criterio, por ejemplo, podemos calcular los cargos y abonos de un cliente para un periodo determinado ( rango de fechas ), esto lo pueden ver en nuestro articulo sobre suma condicional en excel.
Como vemos, este es un caso muy sencillo pero seguramente les sera de gran ayuda, se que el trabajo de calcular saldo de clientes en excel puede llegar a ser muy tedioso, sobre todo cuando la información no tiene un buen orden y se encuentra por todos lados, insisto en la importancia de manejar todos los registros en una sola hoja, no importa si es de varios meses o de otros años, siempre sera mas fácil organizarla o generar reportes cuando todo esta junto.