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.
Matias dice
hola Norberto tengo un negocio y quiero hacer una planilla donde colocando la letra P (de pagado) se descuente del total de cuotas que debe el cliente.Me podrias enviar por email como podria hacerlo, muchas gracias. saludos Matias
Nolberto Lopez dice
Saludos Matias.
Quizá lo que usted necesita es una app de cobranza como la siguiente.
https://contabilidad.formulasexcel.com/cuentas-por-cobrar-en-excel/
marlene dice
buenos dias Ing Nolberto
ya me suscribi y quisiera por favor si me indica como obtener su archivo de calcular saldos de clientes.
me parece excelente.
Nolberto Lopez dice
Saludos Marlene.
Con la suscripción se tiene derecho a descargar el paquete de Apps que se indica justo en la caja de suscripción.
Para los ejercicios independientes como es este caso, solo es necesario compartir el articulo en alguna red social usando los botones de la sección >>Descargar el archivo de este ejemplo<< de ese modo podrá ver el enlace de descarga.
GUILLERMINA dice
Hola, intento colocar la formula y me salta error todo el tiempo, que estoy haciendo mal?
Nolberto Lopez dice
Saludos Guillermina.
Para poder ayudarle es necesario que indique cual es el error que esta obteniendo, dar todos los detalles posibles.
Abel De Leon dice
tengo carros de alquiler, algunos pagan renta por semana, otros la pagan diario, me gustaría ver en automatico el saldo de cada uno todos los días,me podrias ayudar?, disculpa pero soy nuevo en esto
Gracias de antemano.
Danilo dice
Excelente ejercicio.
Saludos!!!!
Danilo
mauricio dice
excelente aporte, gracias
gonzalo dice
quiero aprende a utilizar las formulas de condición si y solo si
Jesús M dice
Muchas gracias, es una excelente forma de organizar nuestras cuentas.
Saludos.
HERNANDO dice
quiero aprende a utilizar las formulas de condición si y solo si