Contenido
Al mencionar bases de datos (DB), lo primero que viene a la mente, por supuesto, es todo tipo de palabras de moda como SQL, Oracle, 1C o al menos Access. Por supuesto, estos son programas muy poderosos (y costosos en su mayor parte) que pueden automatizar el trabajo de una empresa grande y compleja con una gran cantidad de datos. El problema es que a veces ese poder simplemente no es necesario. Su empresa puede ser pequeña y con procesos comerciales relativamente simples, pero también desea automatizarla. Y es para las pequeñas empresas que esto suele ser una cuestión de supervivencia.
Para empezar, formulemos los TOR. En la mayoría de los casos, una base de datos para contabilidad, por ejemplo, ventas clásicas debería poder:
- guardar en las tablas información sobre bienes (precio), transacciones completadas y clientes y vincular estas tablas entre sí
- tener cómodo formularios de entrada datos (con listas desplegables, etc.)
- rellenar automáticamente algunos datos formularios impresos (pagos, facturas, etc.)
- emitir lo necesario informes controlar todo el proceso de negocio desde el punto de vista del gerente
Microsoft Excel puede manejar todo esto con un poco de esfuerzo. Tratemos de implementar esto.
Paso 1. Datos iniciales en forma de tablas
Almacenaremos información sobre productos, ventas y clientes en tres tablas (en la misma hoja o en diferentes, no importa). Es de fundamental importancia convertirlas en “tablas inteligentes” con tamaño automático, para no pensar en ello en el futuro. Esto se hace con el comando Formatear como una tabla de la pestaña. Inicio (Inicio — Formatear como tabla). En la pestaña que luego aparece Constructor (Diseño) dar a las tablas nombres descriptivos en el campo Nombre de la tabla para uso posterior:
En total, deberíamos obtener tres “mesas inteligentes”:
Tenga en cuenta que las tablas pueden contener datos aclaratorios adicionales. Así, por ejemplo, nuestro Preciocontiene información adicional sobre la categoría (grupo de productos, empaque, peso, etc.) de cada producto, y la tabla Cliente — ciudad y región (dirección, NIF, datos bancarios, etc.) de cada uno de ellos.
Mesa Ventas será utilizado por nosotros más tarde para ingresar transacciones completadas en él.
Paso 2. Crea un formulario de ingreso de datos
Por supuesto, puede ingresar datos de ventas directamente en la tabla verde Ventas, pero esto no siempre es conveniente y conlleva la aparición de errores y erratas debido al “factor humano”. Por lo tanto, sería mejor hacer un formulario especial para ingresar datos en una hoja separada de algo como esto:
En la celda B3, para obtener la fecha y hora actual actualizada, use la función El TDATA (AHORA). Si no se necesita tiempo, entonces en su lugar El TDATA se puede aplicar la funcion HOY (HOY).
En la celda B11, encuentre el precio del producto seleccionado en la tercera columna de la tabla inteligente Precio usando la función VPR (VLOOKUP). Si no lo ha encontrado antes, primero lea y vea el video aquí.
En la celda B7, necesitamos una lista desplegable con productos de la lista de precios. Para esto puedes usar el comando Datos – Validación de datos (Validación de datos), especificar como una restricción Lista (Lista) y luego entrar en el campo Fuente (Fuente) enlace a la columna Nombre de nuestra mesa inteligente Precio:
Del mismo modo, se crea una lista desplegable con clientes, pero la fuente será más estrecha:
= INDIRECTO ("Clientes [Cliente]")
Función INDIRECT (INDIRECTO) es necesario, en este caso, porque Excel, desafortunadamente, no entiende los enlaces directos a las tablas inteligentes en el campo Fuente. Pero el mismo enlace “envuelto” en una función INDIRECT al mismo tiempo, funciona de maravilla (más sobre esto en el artículo sobre la creación de listas desplegables con contenido).
Paso 3. Agregar una macro de entrada de ventas
Después de completar el formulario, debe agregar los datos ingresados al final de la tabla Ventas. Usando enlaces simples, formaremos una línea para agregarla justo debajo del formulario:
Aquellos. la celda A20 tendrá un enlace a =B3, la celda B20 tendrá un enlace a =B7 y así sucesivamente.
Ahora agreguemos una macro elemental de 2 líneas que copie la cadena generada y la agregue a la tabla Ventas. Para hacer esto, presione la combinación Alt + F11 o botón Visual Basic de la pestaña. revelador (Desarrollador). Si esta pestaña no está visible, habilítela primero en la configuración Archivo – Opciones – Configuración de cinta (Archivo — Opciones — Personalizar cinta). En la ventana del editor de Visual Basic que se abre, inserte un nuevo módulo vacío a través del menú Insertar – Módulo e ingrese nuestro código de macro allí:
Sub Add_Sell() Worksheets("Input Form").Range("A20:E20").Copy 'Copiar la línea de datos del formulario n = Worksheets("Sales").Range("A100000").End(xlUp) . Fila 'determina el número de la última fila de la tabla. Hojas de trabajo de ventas ("Ventas").Celdas (n + 1, 1). ClearContents 'borrar subformulario final Ahora podemos agregar un botón a nuestro formulario para ejecutar la macro creada usando la lista desplegable recuadro de la pestaña. revelador (Desarrollador — Insertar — Botón):
Después de dibujarlo, manteniendo presionado el botón izquierdo del mouse, Excel le preguntará qué macro necesita asignarle: seleccione nuestra macro Añadir_Vender. Puede cambiar el texto de un botón haciendo clic derecho sobre él y seleccionando el comando Cambiar texto.
Ahora, después de completar el formulario, simplemente puede hacer clic en nuestro botón y los datos ingresados se agregarán automáticamente a la tabla Ventas, y luego se borra el formulario para ingresar una nueva oferta.
Paso 4 Vinculación de tablas
Antes de crear el informe, vinculemos nuestras tablas para que luego podamos calcular rápidamente las ventas por región, cliente o categoría. En versiones anteriores de Excel, esto requeriría el uso de varias funciones. VPR (VLOOKUP) para sustituir precios, categorías, clientes, ciudades, etc. a la tabla Ventas. Esto requiere tiempo y esfuerzo de nuestra parte, y también “come” muchos recursos de Excel. A partir de Excel 2013, todo se puede implementar mucho más simplemente configurando relaciones entre tablas.
Para ello, en la pestaña Datos (Fecha) clic Relaciones (Relaciones). En la ventana que aparece, haga clic en el botón Crear (nuevo) y seleccione de las listas desplegables las tablas y los nombres de columna por los que deben estar relacionados:
Un punto importante: las tablas deben especificarse en este orden, es decir, tabla vinculada (Precio) no debe contener en la columna clave (Nombre) productos duplicados, como sucede en la tabla Ventas. En otras palabras, la tabla asociada debe ser una en la que buscaría datos usando VPRsi fuera usado.
Por supuesto, la mesa está conectada de manera similar. Ventas con mesa Cliente por columna común Local :
Después de configurar los enlaces, se puede cerrar la ventana de gestión de enlaces; no tiene que repetir este procedimiento.
Paso 5. Construimos informes usando el resumen
Ahora, para analizar las ventas y seguir la dinámica del proceso, creemos, por ejemplo, algún tipo de informe utilizando una tabla dinámica. Establecer celda activa en tabla Ventas y seleccione la pestaña en la cinta Insertar – Tabla dinámica (Insertar: tabla dinámica). En la ventana que se abre, Excel nos preguntará sobre la fuente de datos (es decir, tabla Ventas) y un lugar para cargar el informe (preferiblemente en una hoja nueva):
El punto vital es que es necesario habilitar la casilla de verificación Agregar estos datos al modelo de datos (Agregar datos al modelo de datos) en la parte inferior de la ventana para que Excel comprenda que queremos generar un informe no solo en la tabla actual, sino también usar todas las relaciones.
Después de hacer clic en OK aparecerá un panel en la mitad derecha de la ventana Campos de tabla dinámicadonde hacer clic en el enlace Todospara ver no solo la actual, sino todas las “tablas inteligentes” que hay en el libro a la vez. Y luego, como en la tabla dinámica clásica, simplemente puede arrastrar los campos que necesitamos de cualquier tabla relacionada al área Filtrar, filas, Stolbtsov or Valores – y Excel creará instantáneamente cualquier informe que necesitemos en la hoja:
No olvide que la tabla dinámica debe actualizarse periódicamente (cuando cambian los datos de origen) haciendo clic derecho sobre ella y seleccionando el comando Actualizar y guardar (Actualizar), porque no puede hacerlo automáticamente.
Además, seleccionando cualquier celda del resumen y pulsando el botón Gráfico dinámico (Gráfico dinámico) de la pestaña. ECONOMÉTRICOS (Análisis) or parámetros (Opciones) puedes visualizar rápidamente los resultados calculados en él.
Paso 6. Completa los imprimibles
Otra tarea típica de cualquier base de datos es el llenado automático de diversos impresos y formularios (facturas, albaranes, actas, etc.). Ya escribí sobre una de las maneras de hacer esto. Aquí implementamos, por ejemplo, llenar el formulario por número de cuenta:
Se supone que en la celda C2 el usuario ingresará un número (número de fila en la tabla Ventas, de hecho), y luego los datos que necesitamos se extraen usando la función ya familiar VPR (VLOOKUP) y características ÍNDICE (ÍNDICE).
- Cómo usar la función BUSCARV para buscar y buscar valores
- Cómo reemplazar BUSCARV con las funciones ÍNDICE y COINCIDIR
- Relleno automático de formularios y formularios con datos de la tabla.
- Creación de informes con tablas dinámicas