Tutorial: Como Criar uma Ordem de Compra no Excel

AGRADEÇA AO AUTOR COMPARTILHE!

Opa, pessoal!

Um tempo atrás recebi um e-mail de um cliente perguntando se eu sabia como criar uma planilha de pedido de compra para ele, mas que teria como selecionar um item de uma lista de produto e já trouxesse o preço do produto. 

Então surgiu a idéia de fazer este tutorial contendo também o cadastro de clientes.

Como não sou muito bom em design, deixo esta parte para vocês customizarem a gosto.

Para ganhar tempo, já criei as três planilhas que irei usar. Uma é o próprio layout do pedido de compra, outra é o de produtos onde iremos ter o cadastro de todos os produtos e a outra é a de cliente que possui o cadastro dos clientes da empresa conforme as imagens aseguir.

Captura de Tela 2016-06-28 às 15.56.40

Fiz um layout bem simples da ordem de compra, apenas para mostrar o conceito de criar uma planilha inteligente a partir de dados cadastrados. 

Captura de Tela 2016-06-28 às 16.31.07

 Na planilha de produtos, foi cadastrado apenas quatro produtos com seus valores respectivos.

Captura de Tela 2016-06-28 às 15.54.08

 Na planilha de clientes foram cadastrados também quatro clientes com algumas informações básicas de contato.

Captura de Tela 2016-06-28 às 15.54.47

Agora que as planilhas estão criadas, voltaremos a planilha de pedido de compra para poder colocar todas as informações possíveis para podermos trabalhar com ela.

Como na hora que abrimos esta tabela já queremos que apareça a data atual, na célula F4 que corresponde a data do dia, colocaremos a função =HOJE().

Captura de Tela 2016-06-28 às 16.35.15

Verifique que, após apertar enter depois da função digitada, irá aparecer a data do dia que estamos criando a planilha. Não liguem para a data, pois toda vez que abrir a planilha para fazer um pedido, aparecerá o dia atual.

Agora iremos selecionar a célula B6 referente ao nome do cliente. Reparem que aqui foi mesclada as células, B6, C6 e D6. Após selecionarmos a célula, iremos clicar na guia Dados, que fica localizada na parte de cima do Excel. Dentro da guia Dados, iremos clicar em Validação de Dados.

Captura de Tela 2016-06-28 às 16.35.49

Em Validação de Dados, na guia Configuração, iremos selecionar a opção Lista dentre as opções que ficam em Permitir e que este faz parte do Critério de validação. Reparem também se a opção Menu suspenso na célula está marcada.

Agora iremos clicar no final do campo Fonte (no ícone pequeno que tem uma planilha com uma seta) para selecionarmos de onde irá buscar as informações.

Captura de Tela 2016-06-28 às 16.36.29

E então iremos clicar na tabela Clientes e com o mouse selecionar todas as células que correspondem ao nome do cliente, que aqui no caso é a coluna A sem o título. Reparem que em cima está um campo da Validação de dados que está preenchida com =Clientes!$A$3:$A$6. Isso se refere aos dados que estamos buscando, que estão na tabela Clientes e entre as células A3 até A6. O $ (cifrão) significa para fixar a busca dos dados neste campos,caso por ventura em decida copiar a configuração de lista suspensa para outra célula.

Captura de Tela 2016-06-28 às 16.37.30

Verifique que depois que selecionarmos as células que irá pesquisar o respectivo dado, voltará para a tela de Validação de Dados e então clicaremos em OK.

Captura de Tela 2016-06-28 às 16.38.00

Agora, como queremos que após selecionarmos o nome do cliente apareça o restante dos seus dados, iremos usar a função PROCV.

Na célula F6 referente ao nome do contato, iremos digitar =PROCV(B6; e depois do “;” (ponto e vírgula) iremos com mouse selecionar todos os campos referente aos dados do cliente.. 

Captura de Tela 2016-06-28 às 16.39.14

Para isso iremos clicar na tabela Clientes e selecionar todos os campos dos dados dos clientes exceto os títulos conforme a imagem a seguir. 

Captura de Tela 2016-06-28 às 16.40.02

Reparem que no campo destinado a função ficou =PROCV (B6;Clientes!A3:D6).

Captura de Tela 2016-06-28 às 16.40.30

Continuando a escrever a função, depois de D6, colocamos ; (ponto e vírgula) e então colocaremos o número da coluna referente ao nome do contato que aqui no caso é a coluna 2 da tabela Clientes seguido de ) (parênteses), para fechar a função ficando =PROCV (B6; Clientes!A3:D6;2) e por fim clicaremos em enter.

Captura de Tela 2016-06-28 às 16.41.02

O mesmo processo que fizemos para contatos iremos fazer para email e telefone. Iremos apenas alterar o último número que se referente a coluna que contém a informação que aqui no caso, os dados referente ao email do cliente ficam na coluna 4 da tabela Clientes, ficando assim a célula B7 ficaria =PROCV (B6; Clientes!A3:D6; 4) e em Telefone ficaria =PROCV (B6; Clientes!A3:D6; 3) onde o número 3 é referente a coluna da tabela Clientes que possui a informação do telefone.

Captura de Tela 2016-06-28 às 16.44.09

Agora em relação aos produtos, será da mesma forma. Selecionaremos a célula A10 referente a descrição do produto, clicaremos na guia Dados e depois em Validação de Dados.

Captura de Tela 2016-06-28 às 16.46.19

Na guia Configurações, dentro das opções Permitir, que faz parte de Critério de Validação, selecionamos Lista e deixamos marcado Menu suspenso na célula.

Se preferirem, podem desmarcar a opção Ignorar em branco, pois, com esta opção desmarcada, poderá selecionar uma primeira linha em branco de cada tabela (Clientes e Produtos) e assim, sempre que abrir a planilha, a mesma estará limpa para a seleção dos dados. Clicaremos novamente no ícone no fim do campo Fontes para selecionarmos os campos que validaremos para a lista.

Captura de Tela 2016-06-28 às 16.36.29

Clicaremos na tabela produtos e selecionaremos todos os nomes dos produtos que constam na coluna A. Como mencionamos anteriormente, podemos deixar a primeira linha depois dos títulos em branco (esqueci de fazer aqui).

Captura de Tela 2016-06-28 às 16.48.12

Após selecionarmos os campos referente ao nome dos produtos cadastrados, irá voltar para a tela de Validação de dados e então clicaremos em OK

Captura de Tela 2016-06-28 às 16.47.22

Reparem que se clicarmos na célula A10, aparecerá a lista suspensa com todos os produtos cadastrados. 

Captura de Tela 2016-06-28 às 16.48.54

Na sequência, queremos que apareça o preço do produto que selecionamos na coluna A10. Para isso, iremos selecionar o campo D10 e digitar =PROCV (A10; então com o mouse clicaremos na tabela Produtos

Captura de Tela 2016-06-28 às 16.49.27

Selecionaremos com o mouse todos os campos referente aos dados dos produtos e preços exceto o título, ficando selecionado aqui no caso das células A2 à B5 conforme a imagem a seguir.

Captura de Tela 2016-06-28 às 16.49.57

Na sequência e após a seleção, no campo destinado a função da célula colocaremos ; (ponto e vírgula) caso ainda não tenha e então colocaremos o número da coluna referente aos preços da tabela Produtos, que aqui no caso é a coluna 2, e finalizamos a função com 0 (parênteses). Podemos colocar também o $ entre as letras das células para fixar a pesquisa naqueles dados caso copiarmos a função para a outra célula.

Captura de Tela 2016-06-28 às 16.53.15

Irá ficar #N/D devido não ter nenhum dado para pesquisar em relação ao Produto.

Captura de Tela 2016-06-28 às 16.53.39

Para não fazermos todo este processo novamente, podemos copiar as células da linha 10 que já tem as funções para as outras linhas. Para isso podemos arrastar através da cruz que aparece bem na extremidade dos cantos das células. Ou então usar o famoso CTRL+C e CTRL+V. Conforme as duas imagens a seguir.

Captura de Tela 2016-06-28 às 16.54.51

Captura de Tela 2016-06-28 às 16.55.15

Na coluna referente ao Valor Total, usaremos a fórmula =D10*E10 (nas outras linhas o 10 muda para a sua linha correspondente) para multiplicarmos o valor do produto (que aparecerá após selecionar um produto) pelo número colocado na coluna de quantidade (Qtde.). 

Captura de Tela 2016-06-28 às 16.56.21

Como ainda não possui nenhuma informação para o cálculo ficará #N/D.

Captura de Tela 2016-06-28 às 16.56.36

Na célula F19 que se refere ao Valor total da Compra, colocaremos a fórmula para somar o total referente à coluna do Valor Total de cada produto. Neste caso a fórmula ficará =SOMA(F10:f17).

Captura de Tela 2016-06-28 às 16.57.01

Agora que a planilha está pronta, faremos um teste. Selecionaremos uma empresa e ao selecioná-la na lista suspensa que aparece, os outros dados referentes ao contato, email e telefone da empresa deverão aparecer. 

Captura de Tela 2016-06-28 às 16.57.53

Na descrição do produto iremos também selecionar um produto. 

Captura de Tela 2016-06-28 às 16.58.08

Ao selecionar o produto, o valor referente ao produto aparece, sumindo assim o #N/D do Valor e do Valor Total.

Captura de Tela 2016-06-28 às 16.58.24

Ao colocar a quantidade, automaticamente irá multiplicar no Valor total a quantidade pelo valor do produto. Reparem que o Valor Total da Compra continuará como #N/D, pois as outras linhas permanecem com #N/D.

Captura de Tela 2016-06-28 às 16.59.34

Vamos incluir outro produto e, caso não tenha mais nenhum produto comprado por este cliente, poderemos deletar a informação das outras células e assim o cálculo de tudo aparecerá no Valor Total da Compra.

Captura de Tela 2016-06-28 às 17.00.17

Agora que testamos, podemos deixar com aparência mais profissional. Podemos ocultar as colunas referente aos dados cadastrados. É só clicar com o botão direito em cima do nome da planilha Clientes e Produtos e depois clicar em Ocultar. Caso queira incluir novos itens aos cadastros, é só clicar com o botão direito do mouse em cima da planilha Pedido de Compra e depois clicar em Reexibir.

Captura de Tela 2016-06-28 às 17.01.00

Pronto! Planilha pronta para uso :)

Captura de Tela 2016-06-28 às 17.01.34

O design está pobre, mas o que queria demonstrar aqui é como fazer uma planilha inteligente de consulta de dados. Existem outros modos de fazer planilhas inteligentes como, por exemplo, digitar um nome em um campo de pesquisa sem usar uma lista suspensa. Mas neste caso já aplicaria no uso de macros.

Espero ter ajudado aos amigos com esta planilha, espero que tenha explicado de uma forma detalhada e que não tenha ficado confuso.

Abraços e até a próxima.

AGRADEÇA AO AUTOR COMPARTILHE!

Luciano Gusso

Mais artigos deste autor »

Analista de Sistemas. Há mais de 15 anos trabalhando em consultoria a usuários finais e à empresas.


4 Comentários

Cristhian
1

Otimo tutorial
como poderia fazer uma planilha para ordens de serviço, nao somente para manutençao de pc? Tem alguma sugestao?

Luciano GussoLuciano Gusso Autor do Post
2

Obrigado Cristhian!

Na realidade coloquei um modelo sendo de ordem de compra. Mas para o processo de ordens de serviço não modifica muito. O que irá mudar será o layout. É claro que ser quiser usar botões e campos de pesquisa já englobam o uso de macros.

Lucas Meng Alves
3

Muito bom esse post!! Parabéns pelo trabalho Luciano Gusso.
Gostaria de saber se tem a possibilidade de disponibilizar para download?

Luciano GussoLuciano Gusso Autor do Post
4

Obrigado Lucas! Infelizmente não tenho. Esta planilha foi feita no momento em que estava fazendo este tutorial. Foi feita só para demonstrar de forma didática mesmo.

Deixe seu comentário

Seu endereço de e-mail não será publicado. Campos com * são obrigatórios!

Você pode usar estas tags e atributos de HTML: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code class="" title="" data-url=""> <del datetime=""> <em> <i> <q cite=""> <strike> <strong> <pre class="" title="" data-url=""> <span class="" title="" data-url="">