Contidos
Cando se fala de bases de datos (DB), o primeiro que se me ocorre, por suposto, son todo tipo de palabras de moda como SQL, Oracle, 1C ou polo menos Access. Por suposto, estes son programas moi potentes (e caros na súa maior parte) que poden automatizar o traballo dunha empresa grande e complexa con moitos datos. O problema é que ás veces ese poder simplemente non é necesario. O teu negocio pode ser pequeno e con procesos comerciais relativamente sinxelos, pero tamén queres automatizalo. E é para as pequenas empresas que moitas veces isto é unha cuestión de supervivencia.
Para comezar, imos formular o TOR. Na maioría dos casos, unha base de datos para a contabilidade, por exemplo, as vendas clásicas deberían ser capaces de:
- manter nas táboas información sobre mercadorías (prezo), transaccións completadas e clientes e vincular estas táboas entre si
- ter cómodo formularios de entrada datos (con listas despregábeis, etc.)
- enche automaticamente algúns datos formularios impresos (pagos, facturas, etc.)
- emitir o necesario informes para controlar todo o proceso empresarial desde o punto de vista do xestor
Microsoft Excel pode xestionar todo isto cun pouco de esforzo. Imos tentar implementar isto.
Paso 1. Datos iniciais en forma de táboas
Gardaremos información sobre produtos, vendas e clientes en tres táboas (na mesma folla ou noutras distintas, non importa). É fundamentalmente importante convertelos en "mesas intelixentes" con tamaño automático, para non pensar niso no futuro. Isto faise co comando Formato como táboa aba casa (Inicio — Formato como táboa). Na pestana que aparece despois Construtor (Deseño) dea ás táboas nomes descritivos no campo Nome da táboa para uso posterior:
En total, deberíamos obter tres "mesas intelixentes":
Teña en conta que as táboas poden conter datos aclaratorios adicionais. Así, por exemplo, o noso prezocontén información adicional sobre a categoría (grupo de produtos, embalaxe, peso, etc.) de cada produto e a táboa Cliente — cidade e rexión (enderezo, NIF, datos bancarios, etc.) de cada un deles.
Táboa De vendas será usado por nós máis tarde para introducir transaccións completadas nel.
Paso 2. Crea un formulario de entrada de datos
Por suposto, pode introducir datos de vendas directamente na táboa verde De vendas, pero isto non sempre é conveniente e conleva a aparición de erros e erros tipográficos debido ao "factor humano". Polo tanto, sería mellor facer un formulario especial para introducir datos nunha folla separada de algo así:
Na cela B3, para obter a data e hora actual actualizada, use a función O TDATA (AGORA). Se o tempo non é necesario, entón O TDATA pódese aplicar a función HOXE (HOXE).
Na cela B11, busque o prezo do produto seleccionado na terceira columna da táboa intelixente prezo utilizando a función VPR (BÚSQUEDA V). Se non o atopaches antes, primeiro le e mira o vídeo aquí.
Na cela B7, necesitamos unha lista despregable con produtos da lista de prezos. Para iso podes usar o comando Datos - Validación de datos (Datos — Validación), especifíquese como restrición lista (Lista) e despois entra no campo fonte (Fonte) ligazón á columna nome da nosa mesa intelixente prezo:
Do mesmo xeito, créase unha lista despregábel con clientes, pero a fonte será máis estreita:
=INDIRECT("Clientes[Cliente]")
función INDIRECTO (INDIRECTO) é necesario, neste caso, porque Excel, por desgraza, non entende as ligazóns directas a táboas intelixentes no campo Orixe. Pero a mesma ligazón "envolta" nunha función INDIRECTO ao mesmo tempo, funciona cun estrondo (máis sobre isto estaba no artigo sobre a creación de listas despregables con contido).
Paso 3. Engadir unha macro de entrada de vendas
Despois de cubrir o formulario, cómpre engadir os datos introducidos nel ao final da táboa De vendas. Usando ligazóns sinxelas, formaremos unha liña que se engadirá xusto debaixo do formulario:
Eses. a cela A20 terá unha ligazón a =B3, a cela B20 terá unha ligazón a =B7, etc.
Agora imos engadir unha macro elemental de 2 liñas que copie a cadea xerada e engádaa á táboa de vendas. Para iso, prema a combinación Alt + F11 ou botón Visual Basic aba revelador (Desenvolvedor). Se esta pestana non está visible, actívaa primeiro na configuración Ficheiro - Opcións - Configuración da cinta (Ficheiro — Opcións — Personalizar cinta). Na xanela do editor de Visual Basic que se abre, insira un novo módulo baleiro a través do menú Inserir – Módulo e introduza alí o noso código macro:
Sub Add_Sell() Worksheets("Formulario de entrada").Range("A20:E20").Copy 'Copiar a liña de datos do formulario n = Worksheets("Sales").Range("A100000").End(xlUp) . Fila 'determine o número da última fila da táboa. Follas de traballo de vendas ("Vendas"). Celdas (n + 1, 1). Pegar Pegar especial:=xlPasteValues 'pegar na seguinte liña baleira Follas de traballo ("Formulario de entrada"). Rango ("B5, B7, B9"). ClearContents 'borrar o subformulario final Agora podemos engadir un botón ao noso formulario para executar a macro creada usando a lista despregable Inserir aba revelador (Desenvolvedor - Inserir - Botón):
Despois de debuxalo, mantendo premido o botón esquerdo do rato, Excel preguntarache que macro debes asignarlle: selecciona a nosa macro Engadir_Vender. Podes cambiar o texto dun botón facendo clic co botón dereito sobre el e seleccionando o comando Cambiar texto.
Agora, despois de cubrir o formulario, pode simplemente premer no noso botón, e os datos introducidos engadiranse automaticamente á táboa De vendas, e despois borrarase o formulario para entrar nun novo acordo.
Paso 4 Vincular táboas
Antes de crear o informe, liguemos as nosas táboas para que despois poidamos calcular rapidamente as vendas por rexión, cliente ou categoría. Nas versións anteriores de Excel, isto requiriría o uso de varias funcións. VPR (BÚSQUEDA V) por substituír na mesa prezos, categorías, clientes, cidades, etc De vendas. Isto require tempo e esforzo por parte de nós, e tamén "come" moitos recursos de Excel. Comezando con Excel 2013, todo pódese implementar moito máis simplemente establecendo relacións entre táboas.
Para iso, na pestana Data (Data) prema Relacións (Relacións). Na xanela que aparece, fai clic no botón crear (novo) e seleccione das listas despregábeis as táboas e os nomes das columnas cos que deberían relacionarse:
Un punto importante: as táboas deben especificarse nesta orde, é dicir, táboa vinculada (prezo) non debe conter na columna clave (nome) produtos duplicados, como ocorre na táboa De vendas. Noutras palabras, a táboa asociada debe ser aquela na que buscaría datos utilizando VPRse fose empregado.
Por suposto, a mesa está conectada dun xeito similar De vendas con mesa Cliente por columna común Cliente:
Despois de configurar as ligazóns, pódese pechar a xanela para xestionar as ligazóns; non tes que repetir este procedemento.
Paso 5. Elaboramos informes utilizando o resumo
Agora, para analizar as vendas e seguir a dinámica do proceso, creemos, por exemplo, algún tipo de informe mediante unha táboa dinámica. Establece a cela activa na táboa De vendas e seleccione a pestana da cinta Inserir - Táboa dinámica (Inserir — Táboa dinámica). Na xanela que se abre, Excel preguntaranos sobre a fonte de datos (é dicir, a táboa De vendas) e un lugar para cargar o informe (preferentemente nunha nova folla):
O punto vital é que é necesario activar a caixa de verificación Engade estes datos ao modelo de datos (Engadir datos ao modelo de datos) na parte inferior da xanela para que Excel entenda que queremos construír un informe non só sobre a táboa actual, senón tamén usar todas as relacións.
Despois de premer OK aparecerá un panel na metade dereita da xanela Campos da táboa dinámicaonde facer clic na ligazón todopara ver non só a actual, senón todas as “mesas intelixentes” que hai no libro á vez. E entón, como na táboa dinámica clásica, pode simplemente arrastrar os campos que necesitamos desde calquera táboa relacionada á área filtro, Liñas, Stolbtsov or Valores – e Excel creará ao instante calquera informe que precisemos na folla:
Non esquezas que a táboa dinámica debe actualizarse periodicamente (cando os datos de orixe cambian) facendo clic co botón dereito sobre ela e seleccionando o comando Actualizar e gardar (Actualizar), porque non pode facelo automaticamente.
Ademais, seleccionando calquera cela do resumo e premendo o botón Gráfico de pivote (Gráfico dinámico) aba Análise (Análise) or Parámetros (Opcións) pode visualizar rapidamente os resultados calculados nel.
Paso 6. Encha os imprimibles
Outra tarefa típica de calquera base de datos é o enchido automático de varios formularios e formularios impresos (facturas, facturas, actas, etc.). Xa escribín sobre unha das formas de facelo. Aquí implementamos, por exemplo, cubrir o formulario por número de conta:
Suponse que na cela C2 o usuario introducirá un número (número de fila na táboa De vendas, de feito), e despois recóllense os datos que necesitamos usando a función xa familiar VPR (BÚSQUEDA V) e funcións Index (ÍNDICE).
- Como usar a función BUSCAR V para buscar e buscar valores
- Como substituír BUSCAR V con funcións INDEX e MATCH
- Cubrimento automático de formularios e formularios con datos da táboa
- Creación de informes con táboas dinámicas