Nessa publicação vamos mostrar como criar um Relatório Automático no Excel, para isso vamos ver as fórmulas SE, PROCV, e a ferramenta de Validação de Dados.
Caso prefira esse conteúdo no formato de vídeo-aula, assista ao vídeo abaixo!
Para baixar a planilha utilizada nessa aula clique aqui!
Para essa aula o usuário terá que saber os seguintes conteúdos:
• Fórmula PROCV
• Validação de Dados
• Fórmula SE
O relatório automático é um relatório onde vamos automatizar algumas funções para que possamos melhorar e facilitar a interação do usuário com os dados de uma tabela. A planilha que vamos utilizar é essa:
Para automatizar esse relatório o objetivo será criar um botão que irá alterar na célula H2 os códigos que estão nas células de I4 até I7. Esse botão vai permitir com que o usuário selecione um dos 3 códigos dentro dessas células e coloque na célula H2. Feito isso, automaticamente vamos ter o resultado do que estiver selecionado na célula I1 aparecendo na célula I2.
Desta forma poderemos automatizar esse processo de análise entre os 3 códigos e alterando a informação que será mostrada nas células I1 e I2.
Vamos utilizar essas opções de automatização sempre que quisermos facilitar alguns procedimentos dentro de uma planilha com o objetivo de melhorar a análise e a utilização da mesma. Neste caso, vamos utilizar para facilitar a análise de três códigos e conseguir obter uma informação selecionada sem que seja necessário fazer uma busca manual dentro de toda a planilha.
Inicialmente vamos começar com a planilha na aba “Do Zero” que é onde temos informações da planilha, no entanto sem nenhuma automatização.
Vamos começar colocando manualmente um código na célula H2 para que possamos utilizar o PROCV na célula ao lado (célula I2). O código a ser utilizado será L115PH32. Inicialmente teremos a procura de Estoque do código em questão.
Utilizaremos o PROCV para conseguir a informação necessária.
Desta forma conseguiremos obter o valor de estoque do código que está na célula H2.
É possível observar na tabela que o nosso valor de 608 de estoque está correto.
O próximo passo para a automatização deste relatório é colocarmos uma Validação de Dados na célula I1 para que possamos alterar as informações obtidas pelo código selecionado, ou seja, teremos a opção de escolher entre Fábrica, Marca, Estoque e Imposto para que futuramente possamos alterar o dado que aparece na célula I2 baseado no que está escrito no cabeçalho da célula I1.
Feita a validação podemos notar que nossa célula I1 já possui uma lista com os nomes das informações que queremos.
Para que possamos fazer com que nossas informações da célula I2 se modifiquem de acordo com o que está dentro da nossa lista de validação teremos que utilizar a fórmula SE para indicar em qual coluna nosso PROCV deve procurar o dado desejado.
Portanto, vamos utilizar a fórmula SE para que, a medida em que os dados da célula I1 sejam alterados, um valor adequado seja atribuído para o argumento num_indice_coluna da fórmula PROCV. Ou seja, se quisermos encontrar a marca do código, vamos ter que analisar a coluna 2. Se quisermos encontrar o imposto vamos ter que analisar a coluna 4. Cada um dessas opções vai ter um número da sua coluna para que a fórmula PROCV funcione corretamente.
Utilizando essa fórmula estamos atribuindo o valor de cada coluna a cada uma das opções da célula I1. Então, à medida que mudamos as opções da célula I1 teremos diferentes valores dentro da célula J1.
No entanto, temos uma opção que parece não estar de acordo com a nossa tabela. Nessa opção, temos que se a célula selecionada for Imposto vamos colocar o valor 4 para a coluna e caso contrário o valor 5 (o caso contrário será a opção Fábrica que é a única opção possível se desconsiderarmos as outras), mas se olharmos para nossa tabela a quarta coluna está vazia.
Como já foi visto na aula de PROCV, essa função não consegue obter resultados de colunas que estão à esquerda do intervalo selecionado, portanto vamos ter que criar uma cópia da coluna A e colar na coluna F para que possamos obter o valor da Fábrica de cada um dos códigos.
Tendo utilizado a fórmula SE na célula J1 e feito a cópia da coluna A na coluna J podemos modificar nossa fórmula PROCV para incluir a coluna F e incluir a célula J1 no lugar do número da coluna.
Desta forma, teremos o PROCV alterando o valor da coluna de acordo com a validação feita na célula I1.
Agora nossa fórmula está incluindo a coluna de Fábrica e no lugar de alterar manualmente a coluna de onde será feita a procura vamos referenciar a célula J1 que possui a fórmula SE que está relacionada com a nossa validação de dados.
Portanto, toda vez que a lista da célula I1 for modificada para qualquer um dos dados a fórmula do PROCV automaticamente irá procurar o dado correto na coluna desejada.
Nossa parte de procura de código agora está funcionando corretamente buscando cada informação em sua devida coluna, no entanto temos duas colunas repetidas. Para que não fique um relatório “poluído” e com informações duplicadas podemos ocultar a coluna F, para isso basta clicar com o botão direito em cima dela e selecionar a opção Ocultar.
Feito isso, nossa coluna vai continuar com os dados e nossa fórmula PROCV vai continuar obtendo os dados da coluna F normalmente, o que muda é que agora a coluna está escondida.
Desta forma, ficamos com a tabela sem os dados duplicados e sem perder a funcionalidade.
Agora nosso relatório está quase completo, precisamos inserir um “botão” que nos permitirá selecionar uma das 3 opções dentre os códigos 1, 2 e 3 que estão nas células I4, I5 e I6. Para inserir esse botão vamos ter que primeiramente habilitar a opção de Inserir Controles.
Para isso, basta selecionar a guia Arquivo e em seguida Opções. Feito isso, será aberta uma nova janela.
No menu a esquerda vamos selecionar a opção Barra de Ferramentas de Acesso Rápido e nessa nova seção vamos modificar na parte de Escolher comandos em: para Guia Desenvolvedor.
Em seguida, vamos selecionar a opção Inserir Controles e vamos selecionar a opção Adicionar.
Agora basta pressionar OK para que as modificações sejam salvas.
Agora temos a caixa de controle no canto superior esquerdo do Excel.
Dentro da caixa de controle vamos escolher a opção Caixa de Listagem (5ª opção da 1ª linha) para que possamos colocar nossas 3 opções para escolhermos um dos códigos.
Para criar basta ir até algum lugar da planilha, clicar e arrastar para obter o tamanho desejado da caixa de listagem.
Para configurar essa caixa basta clicar com o botão direito em cima dela e selecionar a opção Formatar Controle.
Feito isso, será aberta uma nova janela para configurar nossa caixa de listagem.
Na parte de Intervalo de Entrada vamos selecionar o intervalo H4:H6 que serão nossas opções. Em seguida, vamos selecionar uma célula para o Vínculo de Saída (que será a célula L2), ou seja, para cada opção selecionada vamos ter um número correspondente, como temos 3 opções essa saída vai variar entre 1 e 3.
Configurada nossa caixa basta pressionar OK para verificar os resultados.
À medida em que selecionamos uma opção teremos os resultados 1, 2 e 3 dentro da célula L2, que é nossa célula de saída.
Para completarmos o nosso relatório automático precisamos que apareça na célula H2 o código que estiver selecionado na Caixa de Listagem.
Para isso, vamos novamente utilizar a fórmula SE, mas neste caso dentro da célula H2 repetindo o procedimento utilizado anteriormente. Vamos comparar a célula L2 com os números 1, 2 e 3 e para cada caso vamos igualar a célula H2 às células correspondentes aos códigos selecionados.
Desta forma, quando selecionarmos uma opção na caixa de listagem o código relacionado a seleção será automaticamente atribuído à célula H2.
É possível observar que ao selecionar uma opção na caixa de listagem a célula H2 recebe o código da célula I6 e automaticamente a célula I2 já tem seu valor modificado para a informação referente ao código em questão, ou seja, a Fábrica para o código 3 P398LH43 é São Paulo.
Nesta aula foi possível aprender como utilizar em conjunto alguns conhecimentos do Excel para automatizar um relatório.
Isso mostra que é possível utilizar várias fórmulas juntas e ainda utilizar botões para facilitar uma tarefa e assim analisar melhor os dados da tabela em questão.
Essa é uma apostila básica de Excel para que você saia do zero de forma 100% gratuita!
Para acessar outras publicações de Excel Avançado, clique aqui!
Sócio fundador e especialista em Python da Hashtag Treinamentos. Criador do Método Impressionador para ensinar pessoas comuns a crescerem na carreira e se destacarem na empresa usando o Python.
Quer sair do zero no Power BI e virar uma referência na sua empresa? Inscreva-se agora mesmo no Power BI Impressionador