Curso de Excel parte 13 – Calcular Saldo de Proveedores

Saludos amigos, pues ahora veremos algo que me han pedido mucho, formula para calcular saldo de proveedores, en realidad lo pueden hacer con la función sumaproducto siguiendo la lógica de la clase anterior sobre como calcular existencias de un almacén.

Descargar MegaPack Contables en Excel

En este ejemplo también veremos como hacer un pequeño buscador de facturas, la idea es poner un numero de factura en una celda y automáticamente en otras celdas debe obtener los datos de esa factura, la suma de los abonos realizados y su saldo.

1.- La forma correcta de registrar los cargos y abonos de las facturas de proveedores

Como ya he indicado en otros casos, la forma correcta o la mas idónea es usar una fila para poner los nombres de campo en cada columna, eso ayudara a que en el futuro se pueda procesar mas fácilmente la información para generar reportes o consultas.

2.- Cuales son los datos clave para calcular el saldo de proveedores.

Los datos clave serian el numero de factura, fecha, nombre o código de proveedor, cargos y abonos.

Este punto es muy importante, es por eso que se debe poner especial atención al momento de crear la base de datos.

3.- Formula para calcular la suma de cargos.

=SUMAPRODUCTO((proveedorbd=$B6)*(cargosprv))

Si observas en la formula, hay unos nombres de rango definidos, “proveedorbd” y  “cargosprv”.

Esos nombres fueron definidos desde la opción administrador de nombres en la ficha formulas, eso ayuda a que la implementacion de las formulas sea mas fácil y entendible.

En esa formula básicamente se esta buscando al proveedor que se puso en la celda B6, la búsqueda se hace en la columna de proveedores de la base de datos, cuando lo encuentra, sumara la cantidad que en esa misma fila se haya puesto en la columna de cargo.

4.- Formula para calcular suma de abonos.

=SUMAPRODUCTO((proveedorbd=$B6)*(abonosprv))

En este caso es la misma lógica que para la suma de los cargos, solo es necesario cambiar el nombre de rango donde están los abonos, en este caso ese rango se definió con el nombre de “abonosprv”.

5.- Formula para calcular el saldo.

=SUMAPRODUCTO((proveedorbd=$B6)*(cargosprv))-SUMAPRODUCTO((proveedorbd=$B6)*(abonosprv))

Para el caso del saldo es sencillo, simplemente se resta el resultado de los cargos menos los abonos.

Pero si deseas obtener el resultado directamente en la misma celda, sin calcular previamente cargos y abonos, la formula seria como se muestra en la imagen de arriba.

6.- Buscador de facturas de proveedores.

En este caso la idea es hacer un formulario donde al ingresar un numero de factura, en otras celdas arroje los datos de esa factura, también debe dar la suma de los abonos que se han registrado y calcular el saldo.

Esta es una muy buena opción ya que de ese modo se tiene información rápida y no es necesario recorrer la tabla de datos para obtener esa información sino que en un instante la puedes ver en ese buscador.

Formula para obtener fecha de factura

=BUSCARV(H$5,bdfacturasprv,2,FALSO)

Formula para obtener nombre de proveedor

=BUSCARV(H$5,bdfacturasprv,3,FALSO)

Formula para obtener importe de factura

=SUMAPRODUCTO((facturasprv=H$5)*(cargosprv))

Formula para obtener abonos de factura

=SUMAPRODUCTO((facturasprv=H$5)*(abonosprv))

Formula para obtener el saldo de factura.

=H8-H9

En este ultimo caso, el saldo de la factura buscada también se puede obtener directamente en la celda sin tener que hacer la resta de las celdas de cargos menos abonos.

=SUMAPRODUCTO((facturasprv=H$5)*(cargosprv))-SUMAPRODUCTO((facturasprv=H$5)*(abonosprv))

Como has podido observar, en realidad es muy sencillo calcular los saldos de proveedores, también lo es el crear un buscador que te ahorrara mucho tiempo al poder hacer una consulta rápida de cualquier factura que ya este registrada en la base de datos.