El último día te expliqué cómo hacer facturas automatizadas con Excel, para los casos en los que solo se factura un producto o un servicio.
Hoy me voy a meter de lleno en el otro tipo de facturas, aquellas en las que necesitamos facturar más de una unidad de más de un producto (tornillos y alcayatas).
Como la parte más engorrosa y complicada, que era automatizar la cabecera de la factura, ya la solucionamos en el último post, la cosa será más sencilla…
…
¿Vamos a por ello?
Contenidos
Antes de empezar quiero decirte una cosa.
Puedes pensar que tengo cierta tendencia a hacer las cosas un poco más complicadas de lo habitual.
Y quizás no te falte razón.
Pero no lo hago porque me guste complicarme la vida, lo hago porque así puedo cubrir un abanico más amplio de opciones y esta explicación también le puede servir a gente con casuísticas más complejas.
Por eso, en el ejemplo que voy a utilizar hoy, no solo trabajaremos con los productos, sino que estos productos estarán clasificados por categorías.
Usaremos las categorías para localizar los productos de una manera más ágil y, en base a estas categorías y productos, generaremos la factura.
Si después necesitas una versión simple, sin categorías, no pasa nada porque lo podrás adaptar sin ninguna dificultad.
Dicho esto, vamos a meternos en harina.
Las hojas que te recomiendo tener para automatizar este tipo de facturas
De la misma manera que el otro día que te aconsejaba tener tres hojas de Excel para generar las facturas de un solo producto o servicio, para las facturas que vamos a hacer hoy, necesitaremos cuatro.
Hoja de clientes
Esta hoja es la misma. En ella tendremos una tabla con el listado de nuestros clientes con los datos que vamos a necesitar para generar las facturas.
Evidentemente, si queremos guardar cualquier otro dato podemos, de hecho, deberíamos añadirlo también a esta tabla.
Hoja de productos y precios
En esta hoja, evidentemente, vamos a poner todos los productos con sus respectivos precios.
La puedes hacer tan sencilla o tan complicada como te parezca.
De ella, los únicos datos que, sí o sí, vamos a necesitar son el nombre del producto y su precio.
Hoja de categorías y productos
Esta es la hoja más rara de todas.
Es rara porque vamos a poner tablas enganchadas la una a la otra, es decir, sin dejar columnas vacías entre tabla y tabla.
Pondremos tantas tablas como categorías de productos tengamos y, en cada una de las tablas, los productos que pertenecen a esa categoría.
Es muy importante que sean tablas oficiales de Excel.
No me vayas a meter rangos de datos, ¿eh?
Para que el sistema que vamos a utilizar hoy funcione correctamente, es imprescindible que cada una de las tablas tenga el mismo nombre que el encabezado de la tabla.
Es decir, que la tabla con los productos de la categoría Atletismo, se llame Atletismo, que la tabla con los productos de la categoría Baloncesto, se llame Baloncesto, etc.
(Cuidadín con los acentos. Fíjate que aquí tenemos Musculacion y Natacion y, como puedes ver, no les he puesto acento expresamente)
No te preocupes si de momento no lo entiendes, lo verás muy claro más adelante, cuando te explique cómo vamos a usar estas tablas.
Hoja de factura
De nuevo, el diseño de la factura es cosa tuya.
Yo seguiré con el mismo que el otro día.
La cabecera sigue siendo la misma porque no necesita cambios, pero al cuerpo le he aplicado alguna pequeña modificación para que se adapte al tipo de factura que estamos haciendo hoy.
Cómo automatizar este tipo de factura con Excel
Como puedes imaginar, hoy no te voy a explicar cómo automatizar la cabecera de la factura, porque está muy detallado en el post del otro día y sería explicar lo mismo, por tanto, me voy a centrar únicamente en el cuerpo de la factura.
Empezaré por la parte más complicadilla, porque lo demás van a ser todo fórmulas y son muy fáciles, tanto de explicar como de entender.
Columnas de categoría y producto
Aunque en la imagen anterior no lo ves, las celdas que están en las columnas de categoría y producto, tienen asignada una validación de datos dependiente.
¿Qué quiere decir esto?
Que cuando nos situamos con el ratón en cualquiera de las celdas de estas dos columnas, nos aparece una lista desplegable.
Pero…
Si no rellenamos la celda que corresponde a la categoría, la lista desplegable de los productos no se va a desplegar o aparecerá vacía, es decir, el contenido de la celda del producto depende del contenido de la celda de la categoría.
¿Complicado?
No, no lo es tanto.
Vamos a ver cómo se hace.
¿Recuerdas cómo creábamos las listas desplegables el otro día? Pues lo que vamos a hacer hoy es lo mismo, pero de una forma un poco distinta.
Para esto hemos creado la hoja con las tablas de categorías y productos.
Las categorías.
Para crear la validación de datos de las categorías, en lugar de utilizar una columna con los datos que contiene (como hacíamos el otro día), usaremos los títulos de las tablas que hemos creado en la hoja de categorías y productos.
Vamos por pasos:
1º Seleccionamos la primera celda de la plantilla de la factura en la que queremos que nos salga la lista desplegable (fíjate en la imagen y verás que en mi caso es C16).
2º Clicamos sobre la opción “Validación de datos” en la pestaña “Datos”.
3º En la ventana emergente, seleccionamos la opción “Lista” en “Permitir”
4º En “Origen” ponemos un igual, vamos a la hoja de categorías y productos (la mía se llama Categorias-Productos) y seleccionamos el rango que contiene los títulos de las tablas de las categorías.
(yo las tengo en las celdas de la A1 a la F1)
5º Clicamos en aceptar.
6º Con esto que hemos hecho, ya tenemos asignada la lista a esa celda (en mi caso, la C16).
6º Arrastramos el contenido de la celda hasta la última fila en la que vamos a introducir productos a facturar (en mi caso, C23).
Así, ya tenemos todas las celdas en las que tenemos que consignar las categorías con sus correspondientes listas desplegables.
Los productos
Para crear la lista desplegable de los productos, también necesitamos una validación de datos.
Pero en este caso, es mucho más sencillo.
Lo haremos con la fórmula INDIRECTO
Venga, los pasos:
1º Nos situamos en la primera de las celdas en las que queremos que aparezcan los productos (en mi caso la D16).
2º De nuevo, vamos a la pestaña “Datos” y seleccionamos la opción “Validación de datos”
3º Volvemos a escoger la opción “Lista” en “Permitir”
4º En “Origen” ponemos la fórmula INDIRECTO de la siguiente manera:
=INDIRECTO(C16)
5º Volvemos a arrastrar hasta la última fila en la que vamos a poner los productos a facturar (en mi caso la D23).
¡Y listos! ¡Ya lo tenemos!
Cómo poner la referencia de los productos
Como te he dicho antes, todo lo que queda a partir de ahora, es cuestión de un poco de formulación.
Y no es nada complicado.
Para la referencia de los productos, dependiendo de la versión de Office que tengas, vas a poder o no utilizar la fórmula BUSCARX.
Si tu versión es anterior a la 2019…
Muec…
No la vas a tener y te vas a tener que apañar con BUSCARV tal y como expliqué en el último post.
Como de la fórmula BUSCARX aún no he hablado, me ha parecido que este es un buen momento para hacerlo.
Así que…
¡Vamos allá!
Vale, ya sabes que, en mi caso, el nombre del producto lo tengo en la columna D y el primero de todos, en la fila 16.
Partiendo de esto, te muestro mi fórmula:
=BUSCARX(D16;TablaProductos[Productos];TablaProductos[Referencia];»»;0)
¿Qué quiere decir todo esto?
Vamos a verlo.
La fórmula BUSCARX tiene 5 argumentos imprescindibles y uno opcional.
Te voy a explicar lo que yo he hecho con ella:
El primer argumento es el valor buscado que, en este caso, es lo que se encuentra en la celda del producto, D16.
El segundo es la matriz de búsqueda, es decir, en la tabla de productos, la columna en la que tenemos los nombres de los productos, TablaProductos[Productos].
El tercero es la matriz de lo que nos devuelve que, vuelve a ser, en la tabla de productos, lo que se encuentra en la columna de las referencias, TablaProductos[Referencia].
El cuarto es lo que nos tiene que devolver en caso de no encontrar lo que le hemos dicho que busque (el nombre del producto) que, en este caso, es un vacío, representado por dos comillas dobles, “”.
El quinto es el modo de coincidencia. Hemos puesto un 0 porque queremos que nos devuelva exactamente lo que pone en la columna de las referencias.
Cómo poner los precios de los productos
Para buscar los precios usaremos una fórmula anidada.
En primer lugar, para evitar que nos salgan errores, usaremos la fórmula SI.ERROR.
Esta fórmula tiene dos argumentos, el valor que tiene que devolver (en este caso el resultado de la fórmula BUSCARV) y el valor si se encuentra con un error (un vacío representado, de nuevo, por las dos comillas dobles).
=SI.ERROR(BUSCARV(D16;TablaProductos[[#Todo];[Productos]:[Precio]];2;FALSO);»»)
Vamos a ver la fórmula anidada, la fórmula BUSCARV.
BUSCARV(D16;TablaProductos[[#Todo];[Productos]:[Precio]];2;FALSO)
Volvemos a buscar el valor que se encuentra en la celda D16.
¿Dónde?
Dentro de la tabla productos, en la que solo hemos seleccionado las columnas de Productos y precios.
Para entenderlo, fíjate bien en los corchetes y los separadores, punto y coma y dos puntos.
TablaProductos[[#Todo];[Productos]:[Precio]].
El [#Todo] significa que hemos seleccionado todas las filas de la tabla y el rango [Productos]:[Precio] significa que, de esa tabla, sólo hemos seleccionado esas dos columnas.
Después, como queremos que nos devuelva lo que se encuentra en la segunda columna de las que hemos seleccionado, es decir, la de los precios, ponemos el 2.
Finalmente, como que también queremos que nos devuelva exactamente lo que pone en la columna del precio, ponemos el argumento FALSO que significa que queremos una coincidencia exacta.
Unidades vendidas y cálculo de los importes
Evidentemente, el número de unidades vendidas lo vamos a tener que poner a mano y el cálculo de los importes no es más que una multiplicación de las unidades por el precio por unidad.
Pero, nuevamente, para evitar que nos salgan errores cuando tenemos líneas de facturación vacías, usamos la fórmula SI.ERROR.
=SI.ERROR(E16*F16;»»)
El primer argumento es el resultado de la multiplicación y el segundo, en caso de error, el vacío.
Subtotales, IVA y total factura
Bueno, esto ya no hace falta que lo explique, pero… por no dejarlo a medias te pongo la captura de pantalla de las fórmulas, aunque éstas son muy evidentes…
Analizando la automatización de la factura en Excel
Vale, ya tenemos la factura automatizada.
La hemos creado:
1º Clicando un botón para la numeración automática.
2º Con la fórmula =HOY() para la fecha.
3º Con una lista desplegable para buscar al cliente.
4º Con fórmulas BUSCARV que nos devuelven los datos del cliente.
5º Con otra lista desplegable para las categorías.
6º Con una lista dependiente de la anterior, para buscar los productos.
7º Con una fórmula BUSCARX que nos devuelve su referencia.
8º Con otra fórmula BUSCARV que nos devuelve los precios.
9º Poniendo a mano las unidades vendidas.
10º Calculando los subtotales, totales e IVA con multiplicaciones, sumas y porcentajes.
Con todo ello, estamos generando la factura clicando un botón, seleccionando datos de 3 listas desplegables y poniendo a mano las unidades de productos vendidos.
Todo lo demás se hace de forma automática.
Cuando acabamos de facturar, que la cabecera quede llena no nos importa porque al generar una nueva factura los datos se modificarán automáticamente al seleccionar a un nuevo cliente, pero que quede lleno el cuerpo de la factura es un poco incordio.
Tenemos que borrarlo a mano.
Vamos a evitar eso con un nuevo botón.
Limpiar la factura una vez hemos acabado
Para ello, tenemos que crear una macro, a la que yo he llamado LimiparFactura, con un poco de código VBA.
El procedimiento para crear la macro es el mismo que usamos al crear la cabecera de la factura, que puedes repasar del post anterior.
Pusimos fuera del área de impresión unas celdas que contenían el año y el número de la factura y un botón para crear el nuevo número de factura. Ahora, debajo de ese botón, crearemos otro.
Yo le he llamado “Limpiar”.
Este botón no es más que una forma rectangular insertada desde la pestaña “Insertar” y el botón “Formas”, a la que le he añadido el texto «Limpiar».
Teniendo en cuenta que los datos que rellenamos en el cuerpo de mi factura están en las celdas C16 a C23, D16 a D23 y F16 a F23, el código que yo pondré será el siguiente:
Sub LimpiarFactura()
Application.ScreenUpdating = False
Range("C16:D23").Select
Selection.ClearContents
Range("F16:F23").Select
Selection.ClearContents
Range("F8").Select
Application.ScreenUpdating = True
End Sub
Te dejo aquí los el código de las dos macros que hemos usado para estos dos botones:
Ahora, solo te toca adaptarlo a las celdas que tengas en tu plantilla.
