Conteúdo
Ao mencionar bancos de dados (DB), a primeira coisa que vem à mente, é claro, são todos os tipos de chavões como SQL, Oracle, 1C ou pelo menos Access. Claro, esses são programas muito poderosos (e caros na maioria das vezes) que podem automatizar o trabalho de uma empresa grande e complexa com muitos dados. O problema é que às vezes esse poder simplesmente não é necessário. Sua empresa pode ser pequena e com processos de negócios relativamente simples, mas você também deseja automatizá-la. E é para as pequenas empresas que isso muitas vezes é uma questão de sobrevivência.
Para começar, vamos formular o TOR. Na maioria dos casos, um banco de dados para contabilidade, por exemplo, vendas clássicas deve ser capaz de:
- manter nas tabelas informações sobre mercadorias (preço), transações concluídas e clientes e vincule essas tabelas umas às outras
- tenha confortável formulários de entrada dados (com listas suspensas, etc.)
- preencher automaticamente alguns dados formulários impressos (pagamentos, contas, etc.)
- emitir o necessário relatórios controlar todo o processo de negócio do ponto de vista do gestor
O Microsoft Excel pode lidar com tudo isso com um pouco de esforço. Vamos tentar implementar isso.
Etapa 1. Dados iniciais na forma de tabelas
Armazenaremos informações sobre produtos, vendas e clientes em três tabelas (na mesma planilha ou em diferentes – não importa). É de fundamental importância transformá-los em “tabelas inteligentes” com tamanho automático, para não pensar nisso no futuro. Isso é feito com o comando Formatar como uma tabela aba Início (Início — Formatar como Tabela). Na guia que aparece Construtor (Desenhar) dê nomes descritivos às tabelas no campo Nome da mesa para uso posterior:
No total, devemos obter três “tabelas inteligentes”:
Observe que as tabelas podem conter dados esclarecedores adicionais. Assim, por exemplo, nosso Preçocontém informações adicionais sobre a categoria (grupo de produtos, embalagem, peso, etc.) de cada produto e a tabela Cliente — cidade e região (endereço, TIN, dados bancários, etc.) de cada um deles.
mesa Vendas será usado por nós posteriormente para inserir transações concluídas nele.
Etapa 2. Crie um formulário de entrada de dados
Claro, você pode inserir dados de vendas diretamente na tabela verde Vendas, mas isso nem sempre é conveniente e acarreta o aparecimento de erros e erros de digitação devido ao “fator humano”. Portanto, seria melhor criar um formulário especial para inserir dados em uma folha separada de algo assim:
Na célula B3, para obter a data e hora atual atualizada, use a função O TDATA (AGORA). Se o tempo não for necessário, então O TDATA função pode ser aplicada HOJE (HOJE).
Na célula B11, encontre o preço do produto selecionado na terceira coluna da tabela inteligente Preço usando a função VPR (PROCV). Se você não o encontrou antes, primeiro leia e assista ao vídeo aqui.
Na célula B7, precisamos de uma lista suspensa com produtos da lista de preços. Para isso você pode usar o comando Dados - Validação de dados (Data de validade), especifique como uma restrição Lista (Lista) e depois entre no campo fonte (Fonte) link para coluna Nome da nossa mesa inteligente Preço:
Da mesma forma, uma lista suspensa com clientes é criada, mas a fonte será mais restrita:
=INDIRETO(“Clientes[Cliente]”)
função INDIRETO (INDIRETO) é necessário, neste caso, porque o Excel, infelizmente, não entende links diretos para tabelas inteligentes no campo Origem. Mas o mesmo link “embrulhado” em uma função INDIRETO ao mesmo tempo, funciona com um estrondo (mais sobre isso foi no artigo sobre como criar listas suspensas com conteúdo).
Etapa 3. Adicionando uma macro de entrada de vendas
Depois de preencher o formulário, você precisa adicionar os dados inseridos nele ao final da tabela Vendas. Usando links simples, formaremos uma linha a ser adicionada logo abaixo do formulário:
Aqueles. a célula A20 terá um link para =B3, a célula B20 terá um link para =B7 e assim por diante.
Agora vamos adicionar uma macro elementar de 2 linhas que copia a string gerada e a adiciona à tabela Sales. Para fazer isso, pressione a combinação Alt + F11 ou botão Visual Basic aba revelador (Desenvolvedor). Se esta guia não estiver visível, ative-a primeiro nas configurações Arquivo – Opções – Configuração da fita (Arquivo — Opções — Personalizar Faixa de Opções). Na janela do editor Visual Basic que se abre, insira um novo módulo vazio através do menu Inserir - Módulo e digite nosso código de macro lá:
Sub Add_Sell() Worksheets("Formulário de Entrada").Range("A20:E20").Copy 'Copia a linha de dados do formulário n = Worksheets("Vendas").Range("A100000").End(xlUp) . Row 'determina o número da última linha na tabela. Sales Worksheets("Vendas").Cells(n + 1, 1).PasteSpecial Paste:=xlPasteValues'cola na próxima linha vazia Planilhas("Input Form").Range("B5,B7,B9"). ClearContents 'clear end subformulário Agora podemos adicionar um botão ao nosso formulário para executar a macro criada usando a lista suspensa inserção aba revelador (Desenvolvedor — Inserir — Botão):
Depois de desenhá-lo, mantendo pressionado o botão esquerdo do mouse, o Excel perguntará qual macro você precisa atribuir a ele - selecione nossa macro Adicionar_Vender. Você pode alterar o texto em um botão clicando com o botão direito nele e selecionando o comando Alterar texto.
Agora, após preencher o formulário, basta clicar em nosso botão, e os dados inseridos serão automaticamente adicionados à tabela Vendase, em seguida, o formulário é limpo para inserir um novo negócio.
Etapa 4 Vinculando Tabelas
Antes de criar o relatório, vamos vincular nossas tabelas para que, posteriormente, possamos calcular rapidamente as vendas por região, cliente ou categoria. Em versões mais antigas do Excel, isso exigiria o uso de várias funções. VPR (PROCV) para substituir preços, categorias, clientes, cidades, etc. na tabela Vendas. Isso requer tempo e esforço de nós, e também “consome” muitos recursos do Excel. A partir do Excel 2013, tudo pode ser implementado de forma muito mais simples configurando relacionamentos entre tabelas.
Para isso, na aba Data (Encontro: Data) clique Relações (Relações). Na janela que aparece, clique no botão Crie (Novo) e selecione nas listas suspensas as tabelas e nomes de colunas pelos quais elas devem ser relacionadas:
Um ponto importante: as tabelas devem ser especificadas nesta ordem, ou seja, tabela vinculada (Preço) não deve conter na coluna chave (Nome) produtos duplicados, como acontece na tabela Vendas. Em outras palavras, a tabela associada deve ser uma na qual você pesquisar dados usando VPRse fosse usado.
Claro, a mesa está conectada de maneira semelhante Vendas com mesa Cliente por coluna comum Experiência e dinâmica de loja:
Após configurar os links, a janela de gerenciamento dos links pode ser fechada; não é necessário repetir este procedimento.
Etapa 5. Criamos relatórios usando o resumo
Agora, para analisar as vendas e acompanhar a dinâmica do processo, vamos criar, por exemplo, algum tipo de relatório usando uma tabela dinâmica. Definir célula ativa para tabela Vendas e selecione a guia na faixa de opções Inserir – Tabela Dinâmica (Inserir - Tabela Dinâmica). Na janela que se abre, o Excel nos perguntará sobre a fonte de dados (ou seja, tabela Vendas) e um local para fazer upload do relatório (de preferência em uma nova planilha):
O ponto vital é que é necessário habilitar a caixa de seleção Adicione esses dados ao modelo de dados (Adicionar dados ao modelo de dados) na parte inferior da janela para que o Excel entenda que queremos construir um relatório não apenas na tabela atual, mas também usar todos os relacionamentos.
Depois de clicar em OK um painel aparecerá na metade direita da janela Campos da tabela dinâmicaonde clicar no link Todos os Produtospara ver não só a atual, mas todas as “tabelas inteligentes” que estão no livro de uma só vez. E então, como na tabela dinâmica clássica, você pode simplesmente arrastar os campos que precisamos de qualquer tabela relacionada para a área filtros, Linhas, Stolbtsov or Valores – e o Excel criará instantaneamente qualquer relatório que precisarmos na planilha:
Não esqueça que a tabela dinâmica precisa ser atualizada periodicamente (quando os dados de origem mudam) clicando com o botão direito do mouse sobre ela e selecionando o comando Atualizar e salvar (Atualizar), porque não pode fazê-lo automaticamente.
Além disso, selecionando qualquer célula no resumo e pressionando o botão Gráfico Dinâmico (Gráfico Dinâmico) aba Análise (Análise) or parâmetros (Opções) você pode visualizar rapidamente os resultados calculados nele.
Etapa 6. Preencha os imprimíveis
Outra tarefa típica de qualquer banco de dados é o preenchimento automático de diversos formulários e formulários impressos (faturas, faturas, atos, etc.). Eu já escrevi sobre uma das maneiras de fazer isso. Aqui implementamos, por exemplo, o preenchimento do formulário pelo número da conta:
Assume-se que na célula C2 o utilizador irá introduzir um número (número da linha na tabela Vendas, na verdade), e então os dados de que precisamos são extraídos usando a função já familiar VPR (PROCV) e recursos ÍNDICE (ÍNDICE).
- Como usar a função VLOOKUP para pesquisar e pesquisar valores
- Como substituir VLOOKUP pelas funções INDEX e MATCH
- Preenchimento automático de formulários e formulários com dados da tabela
- Criando relatórios com tabelas dinâmicas