Continuando con el curso de excel, ahora vamos a ver como llenar automáticamente un formato, lo agradecerían quienes manejan formatos contables ( factura, remisión, nota de venta…etc, etc ), la idea es que al ingresar los datos clave, de manera automática se obtengan los datos relacionados y también haga los cálculos correspondientes si aplica.
Un ejemplo seria un formato de factura, aunque esto mismo aplica para cualquier formato que se alimenta de información desde una base de datos, como lo es en el caso de una factura, remisión o nota de venta, las cuales se pueden llenar automáticamente tomando información de la base de datos de clientes y productos.
En la primera clase vimos cual es la forma mas practica y rápida de diseñar un formato, así que usaremos ese mismo formato para esta clase.
Extraer datos de cliente al poner el código de cliente en el formato.
Primero debemos hacer que en la celda donde se pone el código de cliente, este se pueda seleccionar desde una lista desplegable, esto con el fin de agilizar aun mas la captura y llenado del formato.
Creando lista desplegable para seleccionar código de cliente.
Como las listas de cliente van creciendo, es necesario que la lista desplegable que pongamos en la celda pueda ir creciendo automáticamente y no tener que estar modificando el rango al que esta ligada esa lista.
Para hacer la lista necesitamos crear un nombre de rango de datos, ya vimos como hacerlo en clases pasadas, pero ahora lo vamos a hacer con formula para que ese rango sea dinámico y se actualice solo al ir creciendo la lista.
La formula de rango dinámico.
=DESREF('pte3-bdclientes'!$A$2,,,CONTARA('pte3-bdclientes'!$A$2:$A$65536))
Debemos ir a menú formulas/ administrador de nombres/ asignar un nombre al rango/ en se refiere a: debemos poner esa formula.
Debemos hacer eso mismo para crear la lista de códigos de producto, solo debemos cambiar la referencia a la hoja donde estan los productos.
=DESREF('pte3-bdproductos'!$A$2,,,CONTARA('pte3-bdproductos'!$A$2:$A$65536))
Ponerle un nombre al rango de las bases de datos de clientes y productos
En las formulas del formato con las que vamos a extraer la información, es necesario hacer referencia a las bases de datos donde esta la información, esas bases de datos también van creciendo, así que también debemos crear nombres de rango que sean dinámicos.
Formula para rango dinámico en las bases de datos.
Para clientes: =DESREF('pte3-bdclientes'!$A$2,,,CONTARA('pte3-bdclientes'!$A$2:$A$65536),4)
Para productos: =DESREF('pte3-bdproductos'!$A$2,,,CONTARA('pte3-bdproductos'!$A$2:$A$1048576),3)
Si vemos las formulas, son casi iguales que las usadas para crear las listas desplegables de códigos de cliente y productos, solo que en este caso no sera solo una columna sino toda la tabla de datos.
Poner la lista desplegable en el formato.
En este punto debemos seleccionar la celda donde estará la lista desplegable/ click en menú datos/ validación de datos/ en permitir seleccionar “lista”/ en origen pondremos el nombre que le dimos al rango, en este caso le puse “listclientes”
Formulas para extraer datos de clientes
Descripción: =SI($G$6="","",BUSCARV($G$6,bdclientes,2,FALSO))
Dirección: =SI($G$6="","",BUSCARV($G$6,bdclientes,3,FALSO))
RFC: =SI($G$6="","",BUSCARV($G$6,bdclientes,4,FALSO))
Esa formula obtiene el nombre del cliente, se debe copiar a las otras celdas para obtener dirección, RFC, etc., solo sera necesario cambiar el numero 2 que es el numero de columna dentro de la base de datos que indica la ubicación del dato que devolverá, en este caso el nombre esta en la columna 2, si se pone el 3 devolverá la dirección, esto de acuerdo a como yo acomode los datos en la base de datos de clientes.
Lista desplegable de productos.
En esta parte se hace lo mismo que para la lista de clientes, solo que aquí se debe seleccionar todo el rango de celdas donde se debe poner código de producto, después click en menú datos/ validación de datos/ en permitir seleccionar “lista”/ en origen pondremos el nombre que le dimos al rango, en este caso “listproductos”
Formulas para obtener datos de productos.
Descripción: =SI(E12="","",BUSCARV(E12,bdproductos,2,FALSO))
P. Unitario: =SI(E12="","",BUSCARV(E12,bdproductos,3,FALSO))
Importe: =SI(B12="","",SI(E12="","",B12*W12))
Formulas para obtener subtotal, iva, total.
Subtotal: =SUMA(AA12:AD41)
IVA: =AA42*0.16
TOTAL: =AA42+AA43
Obtener el total en letras
Para obtener el total en letras se requiere de una función personalizada, en el archivo les dejo el código que hace esa tarea.
Formula para convertir a letras: =letras(AA44,1)
Espero tu comentario ¿ crees que este ejemplo te servirá de algo en tu trabajo…?
