Aprenda a criar uma planilha de gestão de compras automática no Excel e facilite seu trabalho com essa ferramenta poderosa!
Caso prefira esse conteúdo no formato de vídeo-aula, assista ao vídeo abaixo ou acesse o nosso canal do YouTube!
Na aula de hoje, eu quero te mostrar como criar uma planilha de gestão de compras automática no Excel!
Muitas pessoas ainda têm receio de usar o Excel para algumas atividades, e hoje eu vou te mostrar como você consegue utilizá-lo para facilitar o seu trabalho com essa planilha de gestão de compras.
Vamos criar do zero o registro de compras e um registro de produtos, assim você terá a base de dados das suas compras e dos produtos que compra com frequência.
Feito isso, vamos ver como usar a validação de dados para permitir o registro de compras apenas dos produtos cadastrados. E por fim, traremos essas informações de forma automática com o PROCV!
E aí, gostou dessa ideia? Então vem comigo construir sua planilha de gestão de compras automática no Excel para facilitar seu trabalho e realizar uma análise ainda melhor e mais detalhada das suas compras ou das compras da sua empresa.
Para começar nossa planilha de gestão de compras automática no Excel, vamos abrir um arquivo em branco e criar a nossa tabela de registro de compras.
Dentro dessa tabela, precisamos pensar quais são as informações relevantes para a nossa planilha e para o nosso objetivo. Essas informações serão as colunas da nossa tabela e podem variar de acordo com a sua empresa ou o objetivo da sua planilha.
Para o nosso exemplo, vamos adicionar a data da compra, o produto, a categoria, a quantidade, a unidade de medida e o valor pago.
Feito isso, podemos preencher essa tabela com algumas informações iniciais, com os registros de algumas compras. Como exemplo, desenvolverei essa planilha como se ela fosse feita para uma empresa de marcenaria. Você pode adaptar para sua realidade.
Com os valores preenchidos, podemos selecionar a coluna de valor pago e alterar a formatação dela para moeda.
Podemos selecionar nossa tabela, ir na guia Página Inicial e selecionar a opção de Formatar como Tabela.
Podemos inclusive alterar no rodapé o nome dessa planilha para Registro de Compras.
Finalizando nossa tabela de registro de compras, podemos criar uma planilha de cadastro de produtos. Essa planilha será usada para termos um maior controle sobre os produtos que são registrados na tabela de compras.
Para essa tabela, passaremos as informações Nome do Produto, Categoria e Unidade de Medida.
Além disso, vamos adicionar os nomes dos produtos, as categorias e os tipos de unidades que já temos na nossa tabela inicial. Isso porque essa tabela de Registro de Produtos irá servir para criar uma validação de dados para o nosso registro de compras.
Mas primeiro, precisamos passar as informações para ela e formatar como tabela.
Feito isso, vamos voltar para a nossa coluna de Registro de Compras e selecionaremos a coluna de Produto.
Em seguida, na guia Dados, podemos selecionar Validação de Dados.
Vamos escolher o Critério de validação como Lista e, em Fonte, vamos selecionar a coluna Nome do Produto da tabela Cadastro de Produtos.
Além disso, em Alerta de erro, dentro da validação de dados, vamos adicionar a mensagem de erro para quando a pessoa tentar adicionar um produto que não corresponde aos cadastrados.
Feito isso, quando formos adicionar um novo produto na tabela de Registro de Compras, podemos selecionar da lista que exibirá apenas os nomes de produtos cadastrados.
E caso você tente adicionar um produto que não está cadastrado, ele retornará a mensagem de erro. Vamos adicionar o produto Verniz Natural ao nosso cadastro de produtos para que possamos registrá-lo na nossa tabela de compras.
Feito isso, podemos adicioná-lo na tabela de registro de compras.
Após isso, podemos apagar os valores contidos nas colunas Categoria e Unidade Medida de todos os produtos da tabela de registro de compras. Isso porque essas informações serão obtidas automaticamente a partir da tabela de Cadastro de Produtos.
Para preenchermos automaticamente a categoria e a unidade de medida com base na tabela de cadastro de produtos, vamos precisar fazer um PROCV na planilha de registro de compras.
Vamos selecionar a primeira linha da tabela na coluna categoria e definir o PROCV, passando como argumento o nome do produto procurado, a matriz tabela onde desejamos buscar a informação, o número da coluna desejada e o número 0 como último argumento porque queremos uma correspondência exata.
=PROCV([@Produto];'Cadastro de Produtos'!B:D;2;0)
Podemos repetir o mesmo procedimento para a coluna de Unidade de Medida, substituindo o número da coluna de onde desejamos retirar a informação para 3.
=PROCV([@Produto];'Cadastro de Produtos'!B:D;3;0)
Vamos finalizar preenchendo manualmente a quantidade comprada e o valor pago.
Com isso, as duas planilhas que vão ser a nossa base de dados estão configuradas para que você possa preenchê-las conforme as necessidades do seu trabalho ou da sua empresa.
Com o tempo de uso dessas duas planilhas, você terá informações suficientes para poder construir suas análises de dados sobre os gastos e compras da sua empresa.
Para isso, utilizaremos das tabelas dinâmicas no Excel. Selecione qualquer célula da tabela de Registro de Compras e, na guia Inserir, clique em Tabela Dinâmica.
Na planilha que será aberta, teremos os campos da nossa tabela dinâmica. Defina as linhas como sendo a Data da Compra e os valores como Valor Pago.
Formate a coluna Soma de Valor Pago como moeda, e teremos a nossa primeira tabela pronta.
Para construirmos outras tabelas, podemos selecionar essa primeira tabela dinâmica, copiar e colar. Assim, criamos rapidamente uma segunda tabela na mesma planilha. Só precisamos alterar os valores em Linhas para Categoria e manter o Valor Pago em Valores.
Por fim, podemos criar uma terceira tabela dinâmica, passando os produtos como linhas.
Outra funcionalidade que podemos adicionar a essa planilha para visualizarmos as informações referentes às compras da empresa e auxiliar na nossa análise de dados é adicionando gráficos referentes a cada uma das tabelas dinâmicas.
Então podemos selecionar a primeira tabela dinâmica e adicionar um gráfico de linha, um gráfico de rosca para a segunda e um gráfico de barras para a terceira.
A criação de gráficos a partir dos dados obtidos possibilita uma melhor visualização e evolução dos gastos da empresa e de onde esses gastos estão sendo feitos.
Para melhorar a estética de cada gráfico, é essencial remover os elementos desnecessários deles como legendas, botões e fazer as formatações necessárias como suavizar linhas, adicionar rótulos de dados e ajustar os tamanhos.
Caso você queira aprender a criar gráficos a partir de tabelas dinâmicas e ajustá-los da melhor forma, vou deixar essa aula aqui para que você possa aprender tudo sobre tabela dinâmica e criação de gráficos.
Com os gráficos prontos, você ainda pode sobrepor as tabelas dinâmicas, para apenas visualizar as informações na forma de gráficos.
Feito isso, ao adicionarmos uma nova informação na nossa tabela de Registro de Compras, só precisamos vir até a planilha dos gráficos e, na guia Dados, selecionar Atualizar Tudo para que elas sejam atualizadas com as novas informações.
Na aula de hoje, eu te mostrei como criar uma planilha de gestão de compras automática no Excel!
Utilizando as ferramentas e as funcionalidades que o Excel oferece, fomos capazes de criar do zero uma planilha completa, com registro de informações, validação de dados, e ainda com gráficos e tabelas dinâmicas para análise das compras.
Ainda é possível melhorar as planilhas, os gráficos e criar um dashboard realmente muito atrativo visualmente. Mas com essa base, você já será capaz de fazer o controle e a gestão de compras da sua empresa ou do seu trabalho.
Essa é uma apostila básica de Excel para que você saia do zero de forma 100% gratuita!
Para acessar outras publicações de Excel Intermediário, clique aqui!
Expert em conteúdos da Hashtag Treinamentos. Auxilia na criação de conteúdos de variados temas voltados para aqueles que acompanham nossos canais.
Quer sair do zero no Python e virar uma referência na sua empresa? Inscreva-se agora mesmo no Python Impressionador