En este ejemplo te mostrare como realizar un Formato de Cotización en Excel con formulas y vba. De antemano te digo que este mismo caso puede aplicar a muchos ejemplos de formatos contables. La idea es que de una manera muy rápida y sencilla puedas generar cotizaciones a tus clientes y ademas poder crear un PDF de cada una.
Formato de cotización
Actualización 21-11-2023
- Actualización automática del numero de folio al generar PDF.
- Se puede seleccionar el nombre de cliente desde una lista desplegable.
- Seleccionar el producto desde una lista.
- Configurar cualquier moneda en el total con letras.
Lo primero es crear un formato para la cotización, en este formato pondremos las formulas que deberán llenar los datos que corresponden a los clientes y los productos.
Para este caso te sugiero ver el ejemplo de diseño de factura en excel que publique hace tiempo.
Base de datos de clientes para formato de cotización
Para poder rellenar automáticamente los campos que correspondan a los clientes, es necesario contar con los datos requeridos.
Base de datos de productos
Al ingresar código de productos en el formato, los otros datos se deben llenar automáticamente, así que también necesitamos una tabla con estos datos.
Formulas para llenar automáticamente el formato de cotización
Ahora que ya tenemos el formato de cotización, lo que sigue es ingresar las formulas que realizaran la parte importante. Consiste en obtener automáticamente los datos de clientes y productos al momento de ingresar el dato clave.
Formula para la fecha actual
En el campo de la fecha del documento podemos poner la siguiente formula, esto si queremos obtener la fecha del día y no tener que capturarla.
=HOY()
Formula para obtener datos del cliente en una cotización
Si se ingresa un código de cliente, podemos obtener su nombre en base a este código con la siguiente formula.
=SI($G$6="","",BUSCARV($G$6,bdclientes,2,FALSO))
El código de cliente se ha puesto en G6 y la búsqueda de ese código se hace en el rango que se ha llamado bdclientes, ya en otra ocasión vimos como nombrar rangos en excel.
Con la siguiente formula se obtiene la dirección del cliente.
=SI($G$6="","",BUSCARV($G$6,bdclientes,3,FALSO))
Con esta otra obtenemos el RFC.
=SI($G$6="","",BUSCARV($G$6,bdclientes,4,FALSO))
Si observas la formula, es prácticamente igual en todos los casos, solo cambia el tercer argumento en la función BUSCARV, el cual representa el numero de columna dentro de la base de datos que contiene el dato que se debe devolver.
Probablemente tu no quieres obtener los datos en base al código de cliente. En ese caso simplemente debes nombrar el rango bdclientes teniendo como columna inicial el nombre del cliente.
Formulas para obtener los datos de los productos en formato de cotización.
Para el caso de los productos la historia es la misma, primero debes nombrar el rango de la tabla de datos de productos.
Formula para obtener descripción del producto
=SI(E12="","",BUSCARV(E12,bdproductos,2,FALSO))
Al ver la formula, podemos ver que es la misma lógica que en los casos anteriores. Solo cambiamos la referencia a la matriz donde la búsqueda sera realizada.
Formula para obtener el precio unitario del producto.
=SI(E12="","",BUSCARV(E12,bdproductos,3,FALSO))
Formula para obtener el importe
=SI(B12="","",SI(E12="","",B12*W12))
En todos los casos estamos usando la Función SI para comprobar si las celdas de referencia están vacías.
Formula para obtener el importe total de la cotización
=SUMA(AA12:AD41)
Calcular el IVA ( opcional )
=AA42*0.16
Obtener el total
=AA42+AA43
Generar el total en letras en la cotización
=letras(AA44,1)
En este caso se hace la conversión de números a letras mediante el uso de un código VBA, ya se incluye el código en este ejemplo.
Generar un PDF de cada Formato de Cotización
Como puedes notar, realizar un Formato de Cotización en Excel no es tan dificil, pero Adicional-mente, puedes crear un PDF de la cotización elaborada. Esto lo puedes lograr usando el ejemplo que vimos anteriormente para Generar PDF con Macros, en este ejemplo de cotizaciones ya se incluye la macro para pdf.