Blog

Postado em em 25 de junho de 2019

Relatório Automático – Como impressionar com seus relatórios no Excel

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 que é o Relatório Automático?

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:

Relatório Automático
Relatório Automático

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.

Relatório Automático Finalizado
Relatório Automático Finalizado

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.

Quais são as aplicações de um Relatório Automático?

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.

Como criar uma planilha dessa forma?

Inicialmente vamos começar com a planilha na aba “Do Zero” que é onde temos informações da planilha, no entanto sem nenhuma automatização.

Aba "Do Zero"
Aba “Do Zero”

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.

Código Procurado
Código Procurado

Utilizaremos o PROCV para conseguir a informação necessária.

Fórmula PROCV
Fórmula PROCV

Desta forma conseguiremos obter o valor de estoque do código que está na célula H2.

Valor de Estoque do código
Valor de Estoque do código

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

Validação de Dados
Validação de Dados

Feita a validação podemos notar que nossa célula I1 já possui uma lista com os nomes das informações que queremos.

Célula com Validação de Dados
Célula com Validação de Dados

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.

Fórmula SE
Fórmula SE

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.

Alterando parâmetros no Relatório Automático
Alterando parâmetros no Relatório Automático
Alterando parâmetros no Relatório Automático
Alterando parâmetros no Relatório Automático

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.

Copiando a coluna A
Copiando a coluna A

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.

Fórmula PROCV Atualizada
Fórmula PROCV Atualizada

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.

Relatório Automático Parcialmente Finalizado
Relatório Automático Parcialmente Finalizado

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.

Alterando o valor da célula no Relatório
Alterando o valor da célula no Relatório

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.

Ocultando a Coluna F
Ocultando a Coluna F

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.

Aparência do Relatório Automático
Aparência do Relatório Automático

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.

Opções do Excel
Opções do Excel

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.

Barra de Ferramentas
Barra de Ferramentas

Em seguida, vamos selecionar a opção Inserir Controles e vamos selecionar a opção Adicionar.

Adicionando Inserir Controles
Adicionando Inserir Controles

Agora basta pressionar OK para que as modificações sejam salvas.

Agora temos a caixa de controle no canto superior esquerdo do Excel.

Inserir Controles Habilitado
Inserir Controles Habilitado

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.

Caixa de Listagem
Caixa de Listagem

Para criar basta ir até algum lugar da planilha, clicar e arrastar para obter o tamanho desejado da caixa de listagem.

Caixa de listagem vazia
Caixa de listagem vazia

Para configurar essa caixa basta clicar com o botão direito em cima dela e selecionar a opção Formatar Controle.

Formatar Controle
Formatar Controle

Feito isso, será aberta uma nova janela para configurar nossa caixa de listagem.

Caixa de Listagem no Relatório Automático
Caixa de Listagem no Relatório Automático

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.

Caixa de Listagem Pronta
Caixa de Listagem Pronta

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

Fórmula SE
Fórmula SE

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.

Relatório Automático Finalizado
Relatório Automático Finalizado

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