Aprenda a fazer uma planilha inteligente com respostas automatizadas

Uma das grandes vantagens do Excel é a possibilidade de automatização que ele possibilita para seus usuários. Ainda assim, cansei de ver planilhas sem uma única fórmula ou função. São coisas extremamente simples, mas que, pela falta de conhecimento no Excel, são deixadas de lado.

Curso de Excel - Formatação Condicional

Por exemplo, já vi clientes com planilhas que ao invés de somar duas células com a função SOMA ou mesmo uma fórmula (=A1+A2), deixavam o espaço de resposta em branco, assim ele ia e somava. Imagina o trabalho? Nenhum ser humano deveria passar por esse trabalho que o seu computador pode fazer para você.

Planilha Inteligente com Respostas Automatizadas

Por isso, ter uma planilha de excel inteligente, totalmente automatizada, pode fazer toda a diferença na hora de analisar os dados e decidir quais ações deverão ser tomadas a seguir. Os resultados são apresentados de forma imediata e sem risco de erros tornando o trabalho do gestor muito mais prático e eficiente.

Automatizar uma planilha de Excel para o seu uso de forma inteligente é uma tarefa bem simples e significa economizar tempo com cálculos manuais ou outros tipos de cálculos simples, além de obter resultados com exatidão. Este post vai mostrar o quão fácil e importante é ter uma planilha de Excel automatizada para facilitar o trabalho do gestor.

Automatização dos resultados com funções

Uma célula com valor, na planilha, irá apresentar resultados automatizados e deverá divergir sempre que houver alterações nos dados inseridos para a mensuração. Funções como SOMASE e PROCV podem contribuir para a automatização da planilha de Excel.

Curso de Excel - Formatação Condicional

1 – Aplicando a função SOMASE:

Essa função é condicionada a alguns critérios, tais como: intervalo (área destinada aos dados a serem analisados), critério (uma determinada categoria a ser somada. Exemplo: valores relacionados à “Livro” descrita na planilha) e intervalo_soma (toda a área a ser somada em termos de valores numéricos).

Nesse tipo de planilha os lançamentos se definem em entradas e saídas, em espécie.

Os dados necessários para a formação da planilha estão classificados na coluna “D” e “E”, Classificação e Plano de Contas, e “H” representando as datas de pagamento. Enquanto as colunas “G” e “I”, representam o valor pago e o saldo acumulado. Assim, para saber a soma dos valores considerando o Plano de Contas, aplica-se a função SOMASE.

banco de dados em excel financeiro

Na planilha de exemplo temos a aba de “DRE” dentro de “Relatórios”. A fórmula SOMASE está aplicada para apresentar os resultados de forma automática. Abaixo da tabela principal, você poderá ver uma tabela com valores por item do plano de contas. Para ter o valor recebido em “Livro” para cada mês, a fórmula SOMASE é aplicada.

dre livros somase

Para ter o resultado final com a soma de todos os meses, pode-se utilizar a seguinte fórmula:

  • Exemplo do mês de janeiro: “=SUMIFS(JAN!$G$5:$G$605;JAN!$E$5:$E$605;$D19)”:

JAN!$G$5:$G$605 – coluna de valores na aba de janeiro
JAN!$E$5:$E$605 – coluna “Plano de Contas” na aba de janeiro
$D19 – célula onde se encontra a palavra “Livro”

Em resumo, o comando passado para o Excel é “somar todos os valores da coluna G, quando o correspondente na coluna E é Livro”. Todos os meses estão em abas diferentes, porém usando a mesma fórmula. Dessa maneira, podemos obter o resultado automático de todos eles.

Utilizamos nesse exemplo a nossa Planilha de Fluxo de Caixa.

Curso de Excel - Formatação Condicional

2 – Utilizando a função PROCV: 

Na função PROCV, será considerada a planilha inteira com uma estrutura integrada. Esta função tem por objetivo uma procura virtual.

Utilizamos como exemplo a aplicação da função PROCV em uma planilha de controle de estoque de uma empresa. Nela, adicionamos nas linhas e colunas, os dados de produtos. A coluna “Nome do Produto” será a referência para localizar a informação desejada nas demais colunas.

controle de estoque fórmula procv

Navegando na planilha, perceberemos que dentro de “Resultados Consolidados”, existe a aba “Análise Individual”. Nesta aba, o usuário escolhe um produto e a planilha deve buscar dados dele nas outras abas.

controle de estoque fórmula procv análise individual

Mantendo a coluna “Nome do Produto” sempre como a referência para esta função, inserimos “Prego” e formatamos a fórmula para buscar os outros valores.

Vamos ver o exemplo da fórmula usada para buscar o Estoque Mínimo, na célula “D9”, que ficaria dessa forma: =PROCV($D$5;PG!$D$7:$E$1006;2;FALSO)

Assim, temos:

– Valor_procurado = $D$5 (local nesta aba onde está localizado o nome do produto “Prego”);

– Matriz_tabela = PG!$D$7:$E$1006 (colunas com o nome do produto e o valor de estoque mínimo, usadas para cálculo da função na aba PG -> Cadastro de Produtos);

Curso de Excel - Formatação Condicional

– Núm_índice_coluna = 2 (Repare que ele considera a matriz com as colunas D e E, portando, E, onde está o estoque mínimo seria a segunda);

– Procurar_intervalo = FALSO (ou “0” como valor aceito).

Veja que, ao confirmar a inserção da fórmula, o estoque mínimo aparecerá de forma automática na célula designada.

Continue aplicando a fórmula para extender a função PROCV a toda planilha corretamente preenchida. Assim ela se tornará mais eficiente agregando agilidade na hora de buscar alguma informação.

3 – Automatização das respostas a partir dos resultados

Os resultados ainda podem ser demonstrados de forma automatizada dentro de um relatório. O uso da função CONCATENAR permite puxar este dado para inserir dentro de um texto e, assim, apresentar uma resposta automática.

Nesse exemplo vamos utilizar uma planilha de prospecção de clientes.

Aprenda a fazer uma planilha inteligente com respostas automatizadas

Veja na imagem abaixo como ficaria representado, em um texto de relatório, o mês que apresentou o melhor resultado de vendas, de forma automática.

Aprenda a fazer uma planilha inteligente com respostas automatizadas

Aprenda a fazer uma planilha inteligente com respostas automatizadas

Para ter uma resposta automatizada como essa, siga o passo a passo.

Curso de Excel - Formatação Condicional

Escolha a célula da planilha onde prefere que o relatório apresente o resultado. Nesse exemplo escolhemos a célula “Q17”. Agora, vá na aba “Fórmulas” e escolha o tipo de fórmula “Texto” e em seguida “CONCATENAR”. A seguinte janela abrirá.

Aprenda a fazer uma planilha inteligente com respostas automatizadas

De acordo com a nossa planilha de exemplo, os dados para a configuração dessa fórmula seria preenchida como na imagem a seguir.

Aprenda a fazer uma planilha inteligente com respostas automatizadas

Aperte a tecla “ENTER” para confirmar e terá respostas automáticas em forma de relatórios em sua planilha de Excel.

Aprenda a fazer uma planilha inteligente com respostas automatizadas

Estas funções são alguns exemplos de fórmulas que podem automatizar os resultados. No entanto, existem outras diversas formas para tornar uma planilha inteligente e automática a partir do uso da variedade de fórmulas e funções que o Excel disponibiliza para os cálculos.

Para ter estas planilhas e obter ainda mais informações sobre automatização de resultados acompanhe o nosso blog.

Curso de Excel - Formatação CondicionalEstá com dúvidas? Entre no Fórum de Excel da LUZ e envie a sua pergunta!

ARTIGOS MAIS RECENTES