• Categories
• Tabla (Base de datos)
• Microsoft Excel
• Macro (Informática)
• Bases de datos
• Informática

Citation preview

EXPLICACION BREVE SOBRE CREACION Y FUNCIONALIDAD DEL PROYECTO FINAL DE “EXCEL AVANZADO 2013” Nuestro proyecto final del curso de “Excel Avanzado 2013”, impartido en la UNI, consiste en la creación de un sistema de facturación automatizado. Este sistema lleva como nombre de proyecto: “Sistema de Facturación TRUKS NICARAGUA R&A”. Este sistema servirá para llevar un control exacto de facturación, desde la creación de un cliente en la base de datos, hasta la creación de la factura que se le entregará al cliente una vez realizada la venta. Tal sistema contiene las siguientes pantallas:    

Elaboración de Factura Automatizada. Reporte de facturas Base de datos de Inventario Base de Datos de Clientes

El grupo está integrado por:   

Nestor Reyes Rojas Jazmín Pérez Josué Sánchez

El sistema consta de 8 pestañas: I.

SIST.FACT: En esta pestaña podremos generar y guardar la factura en la base de datos. 1.

1

2

CLIENTE:

1.1. Validación de Datos > Lista: El origen de la lista proviene de la pestaña “BD. CLIENTES”, en el rango B2:B201. No contiene mensaje de entra ni de error, por lo que no se puede escribir manualmente en la celda, porque la hoja está protegida, por lo tanto, el facturador solamente tendrá la única opción de seleccionar de la lista desplegable. 1.2. Funciones: a. Celdas de datos generales del cliente: En las celdas: ID Cliente; Dirección; Teléfono; RUC; Fecha Emisión; Vencimiento. Cumplen con una condición, donde, SI el Cliente es una celda vacía entonces que también esta celda quede como “celda vacía” de lo contrario que Busque el CLIENTE en la tabla de “B,D CLIENTES”, En el rango B2:J201, con la función BUSCARV. Por ejemplo: La Dirección cumple con la siguiente función lógica: =SI($D$15="";"";BUSCARV($D$15;'BD. CLIENTES'!$B$2:$J$201;9;FALSO)) 2.

DESCRIPCION:

2.1. Validación de Datos > Lista: El origen de la lista proviene de la pestaña “INVENTARIO”, en el rango B5:B505. No contiene mensaje de entra ni de error, por lo que no se puede escribir manualmente en la celda, porque la hoja está protegida, por lo tanto, el facturador solamente tendrá la única opción de seleccionar de la lista desplegable. 2.2. Funciones: a. Celdas de campos de detalle del producto: En las celdas: COD; DESCRIPCION; CANTIDAD; P.UNIT; VALOR; Cumplen con una condición, donde, SI el El producto de la Descripción es una celda vacía entonces que también esta celda quede como “celda vacía” de lo contrario que Busque el PRODUCTO en la tabla “INVENTARIO”, En el rango B5:J505, con la función BUSCARV y DESREF. Por ejemplo: En los campos de “COD” cumple con la siguiente función lógica: =SI(C23="";"";DESREF(INVENTARIO!B4;COINCIDIR(C23;INVENTARIO!B5:B505;0);-1))

b.

Celdas de cálculos: En los campos Valor; SubTotal; Descuento; IVA; Total a Pagar, son formulas predeterminadas con la función lógica SI.

3. TODA LA HOJA: 3.1. Macros: Toda la hoja cumple con 2 macros: a. Botón “Imprimir Factura”: Este botón tiene la funcionalidad de guardar la factura en formato PDF, elegir donde guardarla si se desea, e imprimirla manualmente desde el archivo PDF. Nombre de la macro: Imprimir_Factura_PDF b. Botón “Guardar Factura”: Este botón tiene una funcionalidad muy importante y buena, la cual permite registrar la factura en la Base de Datos de Facturas (REPORTE FACT), una vez la guarda, procede a limpiar la factura que se había creado y al mismo tiempo genera un nuevo número de factura. El número de factura generado resulta de sumar de la hoja “REPORTE FACT” el Máximo en el rango B2:501 + 1, y por ultima bloquea la hoja donde se generan las facturas. Nombre de la macro: Grabar_Factura_BD c. Botones “Nuevo Cliente” y “Nuevo Producto”: A estos botones no se les asignó macro por factor tiempo. Quedan pendientes.

II.

REPORTE FACT

Esta base de datos, no se puede llenar manualmente, esta base de datos se llena al grabar la factura con la macro Grabar_Factura_BD, en la hoja “SIST. FACT”. FORMATO COMO TABLA: con fila de totales, esta fila de totales nos permite ver los:



Totales de las columnas: “SUB TOTAL” celda I502; “DESCUENTO” celda J502; “IVA” Celda K502; “TOTAL” Celda L502, con la función “SUMA” la cual ya la tiene predeterminada.



En la Columna “FAC”, celda B502, me permite saber cuántas facturas se han generado, esto con la función “CONTAR NUMEROS” la cual se encuentra en la misma fila de totales del Formato como Tabla.

FORMATO CONDICIONAL:

 

En la columna de Descuento, rango J2:J501 resalta en amarillo a todos los montos a los cuales el descuento > 0. En la columna Cliente, rango H2:H501 resalta en verde el Cliente con el mayor monto de facturación. Se utilizó función anidada entre las funciones “SI” y “MAX”.

TABLA DINAMICA: La tabla dinámica de este reporte se encuentra en la pestaña “RESUMEN DE FACTURACION”.

    

La tabla dinámica se encuentra con un diseño de “FORMATO TABULAR”. Totales en columnas y filas. Subtotales por fechas y departamentos. Filtros de Clientes y Tipos de facturas, lo que se quiera visualizar de ambas opciones. Cuenta con una segmentación de datos de: “FECHA”; “DEPARTAMENTO”; “CLIENTE”

III.

INVENTARIO

Esta es la base de datos de Inventario (Productos disponibles para la venta), en la cual se lleva un control de Códigos del producto; Nombre del Producto; Marca (Puede que no tenga marca “S/M”); Control de precios; Clasificación del producto; También permite conocer a cuanto equivale la cantidad de todos los productos en la bodega en términos monetarios. En cuanto al código del producto se lleva un orden Ascendente, el cual no se debe repetir. Esta tabla está protegida, no se puede agregar un nuevo producto directamente en esta hoja, solamente se puede ingresar manualmente la Existencia. Para agregar un nuevo producto hay que hacer Click en el botón: “INGRESAR PRODUCTO”. Y este botón nos llevara a una nueva ventana en la que podremos ingresar todos los valores del nuevo producto de una manera vertical. En esta ventana se ingresan manualmente las especificaciones del producto; en cuanto al código, este ya está automatizado (este no se registra manualmente), ya nos muestra el código del producto a registrar (este no se debe repetir en la base de datos de Inventario; y también no puede ser mayor 2 veces o más que el Máximo código actual). Para registrar los datos ingresados, se creó un botón llamado: “Registrar Producto”, el cual contiene una macro y esta hace que todos los datos ingresados se copien en la base de datos de Inventario, luego Limpia las celdas de la ventana y genera el código para el siguiente producto. Esta ventana contiene: 



un botón llamado “Bodega” que nos permite simplemente ir a la Base de datos de Inventario, este botón contiene hipervínculo. Validación de Datos > Lista: en los campos de UNIDAD; TIPO; FAMILIA, el origen de estas listas es de la base de datos de Inventario.

IV.

BD. CLIENTES

Esta es la base de datos de Clientes, en esta se lleva un control de datos generales de cada uno de nuestros clientes. En cuanto al ID del cliente se lleva un orden Ascendente, el cual no se debe repetir. Esta tabla está protegida, no se puede agregar un nuevo cliente directamente en esta hoja. Para agregar un nuevo cliente hay que hacer Click en el botón: “INGRESAR CLIENTE”. Y este botón nos llevara a una nueva ventana en la que podremos ingresar todos los valores del nuevo cliente de una manera vertical. En esta ventana se ingresan manualmente los datos generales del cliente; en cuanto al Nº Cliente, este ya está automatizado (este no se registra manualmente), ya nos muestra el código del cliente a registrar (este no se debe repetir en la base de datos de Clientes; y también no puede ser mayor 2 veces o más que el Máximo código actual). Para registrar los datos ingresados en la ventana de “Ingresar Nuevo Cliente” a la base de datos de Clientes, se creó un botón llamado: “Grabar Cliente”, el cual contiene una macro y esta hace que todos los datos ingresados se copien en la base de datos de Clientes, luego Limpia las celdas de la ventana y genera un nuevo código para el siguiente cliente a ingresar, el cual no se repite en la base de datos. Esta ventana también contiene un botón llamado “BD. Clientes” que nos permite simplemente ir a la Base de datos de -Clientes, este botón contiene hipervínculo. VALIDACION DE DATOS > LISTAS: Esta ventana contiene una validación de datos en los campos de “DEPARTAMENTO” y “MUNICIPIO”; de los cuales los orígenes de estos se encentran en la pestaña “LISTAS”.  

El campo Departamento: Tiene una validación de datos > Lista, sencilla. El campo Municipio: Cuenta con una validación de datos > Lista, más completa, en la cual permite seleccionar solamente los municipios del departamento seleccionado, es decir, Por Ejemplo: Si selecciono el Departamento de Masaya, en el campo municipio solamente me deberán aparecer los municipios de Masaya.

V.

ANEXOS

Esta es la visualización de la Hoja “LISTAS”. Esta hoja contiene listados, cada una en “Formato como tabla”, las cuales sirven como orígenes para las Listas realizadas en las validaciones de datos de las otras Hojas. Cada listado de cada departamento cuenta con un “Nombre de rango”, esto puede verificarse en Formulas > Administrador de nombres.