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…?
Julio Rodolfo dice
Hola, tengo un problema que no lo puedo resolver, tengo un listado de inventario de 77000 mil registros aprox. y su verificacion debe de ser periodica ya que con tantos items no podria realizarlo de una sola vez, hay cerca de 500 posibles ubicaciones donde los podria poner, pero la verificacion, la hago x racks, donde ingreso la clave o descripcion de productos, niveles, fechas, y cantidad en diferentes dias o en diferentes ubicaciones. El ingreso lo hago x documentos. fechas descripcion de productos o claves x decirlo asi. y su verificacion de existencia la hago con una hoja en blanco que necesite que me muestre al poner una letra las posiblidades del producto a introducir, asi mismo hacer lo mismo con las ubicaciones, ya que desplegar una lista de 77000 items seria imposible buscarlo o buscar 400 posibles ubicaciones. Muchas veces al ingresar las claves o descripcion de productos, los inventaristas colocan un espacio una raya o una “O u o” en lugar de cero y me cambio la clave del producto y genera una nuevo, como puedo hacer tante verificacion de todo esto sin volverme loco por favor.
Ademas hay veces que me llegan tantos productos que es imposible ubicarlos en los espacios determinados y tengo que inventar espacios dentro del almacen para posteriormente moverlos, pero necesito registrar una posible ubicacion transitoria para esto, como podria hacer, este trabajo adicional, Me puedes ayudar porfa, dejo mis datos para tu posible respueta, si ni te es imposible. Esto lo hago como un cachuelo una vez al mes, me toman para realizarlos y debo hacer ese trabajo en total en 4 dias. UUUU, me estoy volviendo loco. Gracias
Nolberto Lopez dice
Saludos Julio.
Entiendo que básicamente necesita automatizar lo más posible sus procesos, en este sentido, es importante poder ver la información que está trabajando.
Si me envía una copia de su archivo podré hacer un análisis y en su caso encontrar una solución. Al enviar su archivo, favor de especificar en qué hojas y columnas se tiene que hacer cada cosa que necesita.
Enviar archivo a [email protected]
Estaré atento.
Rigoberto Mejía Lacayo. dice
Estimado:
Muy explicativo la parte tres, relacionado con el llenado automático de formato, te comento que no pude bajar el archivo.
ANDERSON MARTINEZ dice
Esta super interesante, me gustó bastante, ya que ayuda bastante con futuros proyectos y muestra profesionalismo en el mismo. te lo agradezco, espero sigas subiendo contenido de calidad.
Humberto Renteria G dice
Hola Nolberto, buenas tardes, excelentes tus cursos, entiendo que para la conversión del importe a letras se requiere el programa (Rutina), crees posible me la puedas enviar? de antemano gracias.
Salvador Garcia dice
Buena tarde
agradesco que compartas tus conociemientos, tengo algo de duda yo trabajo mucho con listados y de ellos se desprende la elaboracion de tarjeta de identificacion del personal y seguimiento , algo sencillo con los siguientes datos matricula, nombre, categoria,turno ,descansos , inicio de labores , horarios, debo confesar que no se mucho de este tema , pero quiero aprender puedes ayudarme con una explicacion mas definidad que me ayude como puedo agilizar este trabajo.
Gracias por tu fina y amble atencion.
Valeria Alejandrina dice
Buenos Días, me gustaría explicara mas a detalle la parte en la conversión de números a letras.
Saludos.
Miguel dice
Estos aportes son de gran ayuda. Gracias.
J Manuel Tinoco dice
Gracias Excelente Aportación.
Germán Chiock dice
La clase de seleccion, extraordinaria.
RENE dice
muchas gracias , muy didáctico
martin dice
La verdad que estoy demasiado agradecido! muchisimas gracias y exitos! me sirvió de mucho
Danilo dice
Excelente ejercicio.
Gracias !!!!!
por compartir tus conocimientos
Danilo
Henry dice
Gracias, son de gran ayuda estos cursos.
Saludos,
eleuterio dice
Gracias, he aprendido mucho de Excel con tus tutoriales.