Profissionais TI - Pra quem respira informação

Um tempo atrás recebi um e-mail de um cliente perguntando se eu sabia como criar uma ordem/pedido de compras no Excel, mas que teria como selecionar um item de uma lista de produto e já trouxesse o preço do produto.

Então surgiu a ideia 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.

1. Criar a planilha de pedido de compras no Excel

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 a seguir.

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

2. Criar a planilha com seus produtos/serviços

Na planilha de produtos, foram cadastrados apenas quatro produtos com seus valores respectivos. Logicamente, você pode colocar mais produtos/serviços.

3. Criar a planilha com o cadastro de clientes

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

4. Como configurar e gerar o pedido de compras

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().

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 de compras, 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.

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.

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.

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.

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.

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.

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

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.

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.

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.

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.

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).

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.

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

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.

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.

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.

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

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.

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.).

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

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).

Agora que a planilha de pedido de compras 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.

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

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

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.

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.

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.

Pronto! Planilha de Ordem/Pedido de Compra no Excel pronta para uso 🙂

O design em si 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 a criar esta planilha pedido de compras no Excel e que eu tenha explicado de uma forma detalhada e que não tenha ficado confuso.

Abraços e até a próxima.