Hoje vamos dar início aos Exercícios de Excel Parte1 para que você possa fixar os conhecimentos das funções SE e PROCV!
Para receber a planilha que usamos na aula no seu e-mail, preencha:
Nessa aula nós vamos te mostrar alguns exercícios de Excel para que você possa praticar e fixar melhor o conteúdo.
Vamos abordar os seguintes exercícios:
Como você deve saber para fixar melhor o que aprende no Excel é necessário praticar. Por esse motivo nós criamos uma série para que você possa praticar o que aprendeu.
Nessa primeiro momento, vamos abordar exercícios sobre a função SE e PROCV, que são duas funções muito utilizadas no Excel!
Vamos ao Excel na prática!
Lembrando que o arquivo para você começar a praticar está disponível para download.
Então já baixe o arquivo para começar! O primeiro exercício é da função PROCV!
Aqui você vai puxar as informações de setor e filiar com base no nome do funcionário, e em seguida vai trazer o e-mail e salário com base no CPF do funcionário.
O segundo exercício também é sobre a função PROCV, mas nesse caso nós vamos trazer a região em relação as filiais de cada funcionário.
Isso será feito com a ajuda da tabela ao lado, onde temos todas as unidades federativas com suas respectivas regiões.
Agora vamos aos exercícios da função SE no Excel!
Nesse primeiro exercício vamos categorizar os funcionários em Nível 1 e Nível 2 com base no salário de cada um deles.
Lembrando que se você ainda não sabe como usar função SE no Excel basta clicar aqui que temos uma publicação explicando como utilizá-la!
Para o segundo exercício da função SE vamos ter praticamente o mesmo problema, só que agora ao invés de apenas 2 categorias nós vamos ter 4 categorias.
Aqui você vai ter que utilizar o SE aninhado, ou seja, vai colocar uma função SE dentro da outra para que consiga mais resultados.
Isso é necessário, pois a função SE por padrão só retorna 2 resultados. Ao acrescentar uma dentro da outra você aumenta em 1 resultado a cada nova função.
Agora vamos para os exercícios de formatação como tabela dentro do Excel que é um recurso muito útil e que vai te poupar muito trabalho.
Além disso vamos abordar a tabela dinâmica, que é uma tabela muito poderosa e vai te permitir fazer diversos cálculos sem precisar de nenhuma fórmula.
Caso prefira esse conteúdo no formato de vídeo-aula, assista ao vídeo abaixo ou acesse o nosso canal do YouTube!
Para receber a planilha que usamos na aula no seu e-mail, preencha:
Como são duas ferramentas muito importantes é interessante que você treine para fixar bem o que aprendeu sobre elas.
Antes de começar de te mostrar os exercícios que você terá que fazer, é importante que você dê uma olhada na base de dados.
É sempre interessante visualizar as informações que tem antes de começar qualquer projeto, até para que você saiba o que pode analisar, quais informações são úteis para colocar em um gráfico ou em um indicador.
OBS: Vale lembrar que tanto a base de dados quanto essa lista de tarefas estão disponíveis para download!
A primeira tarefa é para fazer a formatação de CPF dentro do Excel que é algo muito útil principalmente para facilitar a visualização dessas informações.
Se estiver com dúvidas pode acessar essa publicação sobre Formatação Personalizada que falamos tanto da formatação de CPF quanto CNPJ.
A segunda tarefa é bem tranquila também, é apenas para separar os servidores de e-mail de cada um dos funcionários.
Para essa tarefa você pode utilizar várias fórmulas de texto, mas algumas são mais interessantes como as funções TEXTOANTES e TEXTODEPOIS, que são funções novas do Excel.
Outra ferramenta bem interessante para executar essa tarefa é o Preenchimento Relâmpago.
A terceira tabela é para Formatar como Tabela e criar uma Segmentação de Dados.
A quarta tarefa é para criar uma Tabela Dinâmica para analisar o valor total de salário para cada setor da empresa no formato percentual.
A quinta tarefa é parecida com a anterior, mas vamos fazer uma análise (com Tabela Dinâmica) de quantas pessoas utilizam um determinado servidor de e-mail em cada um das filiais da empresa.
Aqui eu vou te dar mais 3 exercícios bônus para que você possa praticar e ficar ainda melhor no Excel!
Caso prefira esse conteúdo no formato de vídeo-aula, assista ao vídeo abaixo!
Para baixar a planilha utilizada nesta publicação, clique aqui!
Este é um post de exercícios do Excel para que os alunos possas praticar a resolução de problemas. Para isso temos 3 exercícios divididos em 3 abas diferentes para que o aluno possa analisar situações mais próximas do que pode encontrar no ambiente de trabalho.
Desta forma vamos propor os exercícios com problemas mais complexos e com isso além dos alunos praticarem para fixar melhor o conteúdo, estarão também se preparando para os testes de Excel que são cada vez mais cobrados em entrevistas de emprego.
Para o primeiro exercício temos a seguinte tabela com sua legenda logo ao lado.
Neste exercício o aluno terá que inserir os sinais da legenda na coluna de estoque de acordo com as condições da coluna E, ou seja, se o estoque de um produto estiver com uma quantidade igual ou superior a 800 teremos que colocar um sinal verde para indicar que o estoque está bom.
Caso o estoque esteja entre 800 e 550 já temos um estoque médio e por fim se tivermos um estoque menor que 550 já temos uma situação crítica.
O primeiro passo é entender que será necessário utilizar uma formatação condicional. Isso se deve ao fato de que precisamos formatar as células (com os sinais) baseado em uma condição, que é a condição dos valores da legenda.
Vamos então selecionar a coluna B (basta clicar em cima da letra B), em seguida vamos até a guia Página Inicial, depois vamos selecionar Formatação Condicional, Conjuntos de Ícones e por fim as Formas que temos na legenda, que são 3 círculos (verde, amarelo e vermelho).
Ao fazer essa seleção é possível notar que o Excel já faz uma formatação dos dados da coluna B.
No entanto não sabemos os critérios que foram utilizados para tal formatação. Neste caso vamos até a opção de Formatação Condicional e em seguida em Formatar Regras.
Como temos apenas uma regra atribuída ela que irá aparecer dentro do gerenciador de regras. Aqui basta selecionar essa regra e clicar na opção Editar Regra.
Aqui o usuário poderá editar a regra que foi colocada para as células selecionadas. Então poderá alterar os estilos dos ícones, o ícone para cada faixa, os intervalos e o tipo.
Neste caso como estamos analisando valores vamos modificar a opção de Tipo para Número e vamos inserir os valores que temos na legenda para que possamos ter o mesmo resultado em todos os valores.
Feitas as modificações basta pressionar OK e na janela do gerenciador de regras basta selecionar a opção Aplicar e fechar a janela.
É possível observar que todos os dados da coluna B foram formatados conforme as informações da legenda ao lado da tabela.
Com esse exercício foi possível aprender como fazer uma formatação condicional de forma rápida para mostrar ao usuário de uma forma visual o status do seu estoque, ou seja, olhando apenas para os símbolos o usuário saberá se aquele é um produto com estoque crítico ou não.
Desta forma não será necessário verificar na legenda produto por produto para saber se o estoque está ou não em um nível crítico. Apenas olhando é possível ter uma noção prévia do status de cada um dos produtos.
Para o segundo exercício temos uma atividade um pouco mais complexa. Neste caso precisamos construir um Quadro Gerencial para analisar o valor total faturado de cada grife por fábrica.
Esta é a tabela que será utilizada. Temos a descrição de cada produto, a grife correspondente, a fábrica em que esse produto foi feito e por fim a quantidade faturada deste produto.
Este é o quadro gerencial que deverá ser preenchido com o valor total de cada grife por fábrica.
Neste exercício temos que obter o valor total de cada grife por fábrica, para isso será necessário fazer uma soma com duas condições, ou seja, precisamos no primeiro caso somar somente os produtos da grife Lira Batom que foram feitos na Fábrica RJ.
A fórmula que vai nos auxiliar neste problema é a SOMASES, pois com ela será possível fazer uma soma com mais de uma condição, ou seja, vamos conseguir abranger a grife e a fábrica.
Essa é uma fórmula bem simples, precisamos apenas de 3 argumentos iniciais e mais 2 a cada nova condição. Como temos 2 condições vamos utilizar 5 argumentos.
O primeiro é o intervalo da soma que será a coluna D (Faturado), o segundo é o intervalo do primeiro critério que neste caso será a coluna C (Fábrica) e o terceiro argumento é o critério que para o primeiro caso é Fábrica RJ (célula F4).
Para a segunda condição temos mais 2 argumentos (neste caso não temos mais o intervalo de soma, pois ele é o mesmo). O quarto argumento é o intervalo do segundo critério, que é a coluna B (Grife) e por fim o último argumento que é o critério Lira Batom (célula G3).
Essa será a fórmula utilizada para a célula G4. Aqui temos alguns trancamentos para que seja possível arrastar a fórmula sem que o Excel movimente nossas referências de modo a obter dados errados.
O trancamento dos intervalos é necessário para que o Excel não mude esses intervalos, pois eles são os mesmos para todos os cálculos. Já nos critérios fizemos um trancamento parcial, nas fábricas fizemos um trancamento da coluna, desta forma ao arrastar a fórmula para a direita o Excel não vai permitir que a referência saia da coluna F.
Já para as grifes o trancamento parcial foi feito na linha, pois ao arrastar a fórmula para baixo a linha se manterá como 3, desta forma não irá alterar a grife.
Com esses dois trancamentos parciais é possível arrastar a fórmula para toda a tabela sem que as referências fiquem incorretas, ou seja, vão pegar somente as fábricas e as grifes sem obter nenhum outro dado incorreto.
Esse é o resultado após arrastar a fórmula para as outras células da tabela. É possível observar que fizemos a soma total de cada fábrica por grife sem precisar verificar uma a uma dentro da tabela.
OBS: Temos uma célula vazia não por erro da fórmula, mas porque não temos nenhum produto vendido que foi feito pela Fábrica RJ com a grife Sabom Paes Leme.
Essa é uma fórmula muito útil principalmente nesses casos quando é necessário criar um quadro resumo com as informações da própria tabela. Isso permite ao usuário fazer uma análise mais rápida e fácil sem a necessidade de procurar ou filtrar os dados na tabela sempre que precisar de uma informação nova.
Esse é um exercício muito comum quando fazemos a importação de dados, pois geralmente eles não são importados separados cada um em sua respectiva coluna.
Neste caso temos a importação de dados que foi feita em uma única coluna, mas temos que separar essas informações para que possamos analisar corretamente esses dados e até trabalhar com eles posteriormente como fizemos no exercício anterior.
Então esse exercício é justamente para que o aluno saiba como tratar essas informações e separá-las de forma eficiente para que esses dados possam ser analisados.
O primeiro passo para resolver esse tipo de problema é verificar o padrão que temos dentro desses dados. É possível observar que as informações que queremos são sempre separadas pelo underline (_), ou seja, podemos utilizar esse símbolo como delimitador das informações.
Sabendo disse vamos utilizar algumas fórmulas de texto para obter informações e então separar esses dados corretamente. A primeira fórmula que vamos utilizar é a fórmula PROCURAR, pois com ela poderemos fazer a busca deste símbolo dentro do nosso texto e identificar em que posição ele se encontra.
Com essa informação saberemos quantos caracteres temos em cada informação e assim conseguiremos separar esses dados.
Para o primeiro símbolo vamos utilizar a fórmula desta maneira, pois o Excel retorna à primeira ocorrência deste símbolo.
O resultado para o primeiro texto é 9, isso quer dizer que o símbolo está na 9ª posição dentro do texto. Agora se quisermos encontrar o segundo símbolo teremos que utilizar o terceiro argumento (opcional) da fórmula PROCURAR, pois ele nos permite colocar um valor inicial para começar a procurar.
Como essa fórmula retorna à primeira ocorrência desse símbolo se colocarmos somente os dois primeiros argumentos novamente a resposta será a mesma. Agora que sabemos a posição do primeiro símbolo basta começar no caractere seguinte a ele para que o Excel não conte o símbolo que já encontramos.
Para o segundo símbolo vamos utilizar a mesma fórmula só acrescentando o terceiro argumento que será o número da posição do primeiro símbolo + 1, desta forma o Excel irá buscar pelo segundo símbolo.
Agora temos as posições dos dois símbolos e com isso podemos utilizar outras fórmulas de texto para podermos extrair as informações para separar nas 3 colunas.
A fórmula ESQUERDA serve para obter dados que começa da esquerda de uma célula. O primeiro argumento é a célula que contém o texto e o segundo argumento é o tamanho do texto que queremos obter, neste caso é a posição do primeiro símbolo – 1, para não obtermos o símbolo dentro do texto. Assim podemos obter a informação de Código.
Para a informação de Fábrica vamos utilizar a fórmula acima. Neste caso estamos utilizando uma fórmula extra que é NÚM.CARACT que serve para contar o número de caracteres de uma célula. Isso foi feito para saber o tamanho total e poder obter a quantia exata pela direita para que o nome da fábrica fique correto, ou seja, vamos pegar o tamanho total e subtrair da posição do segundo símbolo para obter o tamanho da última informação desejada.
Como aqui temos apenas 2 fábricas e as duas possuem 10 caracteres, podemos ao invés de utilizar essa fórmula extra colocar apenas o número 10 após o ponto e vírgula que irá funcionar da mesma maneira.
Por fim para obter a informação de Marca vamos utilizar a fórmula EXT.TEXTO que funciona para extrair a informação de um texto. Como o que queremos obter está no meio teremos que utilizar essa fórmula.
O primeiro argumento é onde se encontra o texto, o segundo argumento é onde se inicial o texto que será extraído e por fim o último argumento é o tamanho do texto. Neste caso vamos subtrair as posições dos dois símbolos e subtrair 1 para que não seja obtido o símbolo no processo da extração.
Ao finalizar as 3 fórmulas teremos esse resultado. É possível observar que todas as informações foram separadas corretamente e de forma automática, ou seja, mesmo que não tenha um padrão de tamanho será possível efetuar essa separação de forma eficiente.
Agora basta arrastar as fórmulas tanto da tabela quanto das posições dos símbolos para que o Excel possa obter as informações de todos os dados.
Caso o usuário não queira que as colunas G e H fiquem a mostra podemos executar duas ações. Uma delas seria colocar essas fórmulas já dentro das fórmulas de texto que utilizamos ou o usuário poderá selecionar essas duas colunas, clicar com o botão direito do mouse e selecionar a opção Ocultar.
Ao ocultar uma coluna os dados não ficarão visíveis, mas o Excel ainda consegue utilizar esses dados para utilizar nas fórmulas normalmente.
Essas ferramentas desse exercício são muito úteis porque muitas das vezes que importamos alguns dados eles são apresentados dessa forma, e para que o usuário não tenha que fazer essa separação, principalmente pela quantia de dados, basta utilizar essas fórmulas e automatizar o processo.
Os exercícios feitos nesse post são para que os alunos possam treinar para testes de Excel que possam vir a fazer e conhecer novas ferramentas para resolver problemas do dia a dia.
Caso tenha interesse, fizemos uma parceria com o Vagas.com para explicar um pouco mais sobre os temas de Excel mais cobrados em processos seletivos.
Nessa publicação eu te mostrei alguns exercícios sobre função SE, função PROCV, Formatar como Tabela e Tabela Dinâmica.
Esses são recursos e funções muito utilizados no dia a dia de quem usa Excel e eles vão te ajudar muito não só a te deixar mais produtivo como a facilitar e melhorar o seu trabalho com essa ferramenta.
Além disso, é importante que você pratique sempre, pois assim consegue fixar muito melhor o que aprendeu. Se quiser ler tudo de uma vez e depois partir pra prática não tem problema. O importante é que continue praticando!
Deixei mais 3 exercícios para que você possa praticar outras ferramentas e funções importantes do Excel!
Essa é uma apostila básica de Excel para que você saia do zero de forma 100% gratuita!
Para acessar outras publicações de Excel Intermediário, clique aqui!
Expert em conteúdos da Hashtag Treinamentos. Auxilia na criação de conteúdos de variados temas voltados para aqueles que acompanham nossos canais.
Quer sair do zero no Power BI e virar uma referência na sua empresa? Inscreva-se agora mesmo no Power BI Impressionador