se preparar para testes de excel

Preciso resolver algumas questões de excel em um processo seletivo e para isso, pesquisei alguns conteúdos que me fizessem aprender de forma rápida e direto ao ponto, para solucionar algumas questões.

Nas pesquisas que realizei, encontrei o Hashtag Treinamentos. Gostei muito da forma de passar o conteúdo.

Por isso, o conteúdo desse artigo é a forma que fiz para estudar o conteúdo cobrado, resolver as questões disponibilizadas e revisar o processo de realizar o teste.

Para testes de excel em entrevistas de processos seletivos, aqui tem oito perguntas que seriam as perguntas que são frequentemente pedidas e abordam os principais temas cobrados em uma entrevista que tenha como requisito o Excel.

Os temas que sempre estão presentes em Teste de Excel entrevista de emprego são: fórmula SE, fórmula CONT.SE, fórmula CONT.SES, fórmula SOMASE, fórmula SOMASES, fórmula PROCV, Remover Duplicatas.

As questões vão aumentando em nível de dificuldade, isto é, a primeira é bem básica, já da segunda até a sexta o nível passa a ser intermediário. Nas últimas duas o nível de conhecimento exigido já é um pouco mais avançado!

Antes de ir mexer na planilha, leia e estude esse artigo e depois vá a prática da fórmulas e resolva as questões. Ao final do artigo tem mais detalhes para ir para a prática.

Vamos a resolução.

PRIMEIRO passo é a análise das informações da planilha. Você receberá uma planilha com as tabelas envolvidas no teste.

Antes de responder as perguntas é importante analisar as informações que temos na tabela que nos foi dada para termos uma breve noção do que se trata. Em seguida essa análise já vai ajudar a responder as questões do teste.

Temos a seguinte tabela com 3500 linhas e com 6 colunas. Normalmente as colunas nos dizem quais são as informações que teremos na tabela.

Então fazendo uma análise rápida, podemos notar que é uma tabela de vendas completa, ou seja, nos mostra a data de venda do produto, o código desse produto, o vendedor, o tipo de produto que foi vendido, a marca deste produto e por fim o valor dele.

É possível observar que temos uma análise completa da venda, o que torna a organização mais fácil, pois é possível ter uma organização maior da tabela assim como facilita no caso de filtrar algumas informações.

SEGUNDO passo é a análise das questões do teste.

Questão 1 — Essa questão é simples e pergunta apenas qual o total de vendas que foram realizadas no ano de 2019.

Questão 2 — Essa questão precisa de uma análise na coluna D, que é a coluna de produtos, ou seja, nesta coluna será possível fazer a soma destes produtos.

Questão 3 — Essa questão já é um pouco mais elaborada, pois pede a quantidade de bonés da marca azul que foram vendidos em todo o período, portanto é necessária uma análise nas colunas D e E, pois os dados necessários estão nessas duas colunas.

Questão 4 — Essa questão pede a soma do valor de vendas da marca amarela, ou seja, essa análise deverá ser feita entre as colunas E e F, pois nelas temos as marcas e os valores de cada uma das marcas.

Questão 5 — Essa questão é parecida com a questão 3, no entanto neste vamos somar o valor total de saias da marca vermelha e não a quantidade vendida.

Então serão analisadas as colunas D, E e F, pois precisamos de 3 informações: o produto em questão, a marca indicada e os valores para descobrirmos o total.

Questão 6 — Essa questão é para procurar o funcionário que fez a venda do código N5E6Q9S3, portanto terá que analisar as colunas B e C para verificar qual foi esse funcionário.

Questão 7 — Essa questão é um pouco mais complexa, pois primeiramente exige que saiba quais são os funcionários existentes, ou seja, primeiramente será necessário remover os dados duplicados para saber todos os funcionários.

Em seguida terá que ser feito um ranking desses funcionários, dos 5 primeiros, para saber qual deles vendeu mais em ordem crescente. Lembrando que será utilizada uma planilha auxiliar para facilitar o procedimento.

Questão 8 — Por fim a última questão deverá ser feito um ranking dos 3 produtos mais vendidos pela marca verde, portanto será necessário verificar apenas os produtos da marca verde, fazer uma contagem desses produtos para conseguir ranqueá-los.

Nesta questão também é aconselhável a utilizar uma planilha auxiliar para obter os dados necessários.

Para conseguir resolver todas essas questões, será necessário ter o conhecimento dessas fórmulas: fórmula SE, fórmula CONT.SE, fórmula CONT.SES, fórmula SOMASE, fórmula SOMASES, fórmula PROCV, Remover Duplicatas

Conteúdo teórico

A seguir, tem o conteúdo que me explicou o que algumas fórmulas fazem e como utilizá-las.

Fórmula CONT SES

O QUE É — É uma fórmula utilizada para contar valores baseados em uma ou mais condições. Também é muito utilizada para construir resumos gerenciais, então a partir de uma base de dados com diversas informações e a partir dessa base é possível fazer uma análise mais específica no detalhe utilizando a fórmula CONT.SES.

QUANDO — Essa fórmula será utilizada quando temos que contar baseado em uma ou mais condições. Nesse caso, vamos fazer uma análise mais detalhada de alguns dados. Temos uma tabela de vendas de quatro empresas. Essa tabela possui a data de venda, o valor da venda e a empresa que fez essa venda.

Na aba de Análise temos os resumos dessa tabela, é nesta parte que iremos utilizar a fórmula CONT.SES para ter um resumo gerencial das empresas.

COMO USAR — A fórmula CONT.SES pede no mínimo dois argumentos e eles aumentam a medida em que o usuário precisa de mais detalhamento em seu resumo.

Intervalo_critérios1 — É o intervalo onde serão analisados os dados, no nosso caso queremos analisar o intervalo com todas as empresas e dentre elas contar somente as que forem Verde, então nosso intervalo de critérios1 será a coluna D da aba de vendas.

Critérios1 — É o critério utilizado para o resumo a ser feito, como estamos inicialmente resumindo os dados da empresa Verde iremos selecionar a célula B3 da aba Análise.

Teremos a seguinte fórmula para a célula C3.

Portanto, temos uma análise na coluna D da aba vendas fazendo a contagem de quantas vezes temos a empresa Verde, ou seja, cada uma dessas aparições significa uma venda dessa empresa.

Então, essa fórmula vai fazer uma contagem com uma condição, neste caso estamos contando quantas células na coluna D possuem o nome Verde. Resumindo, conta a célula se ela estiver com o texto verde escrito.

Feito isso podemos observar que temos 23 vendas da empresa Verde.

OBS: Na fórmula escrita na célula C3 temos dois argumentos diferentes do que normalmente utilizamos que são Vendas! e Análise!, esses argumentos significam que estamos pegando as células que ficam após a exclamação daquela aba em questão.

Então para Vendas!D:D estamos pegando a coluna D da aba Vendas, desta forma o Excel reconhece que a referência da coluna D está na aba vendas e não na aba atual. O mesmo acontece com Análise!B3, estamos pegando a célula B3 da aba análise. Desta forma se mudarmos de aba o Excel continuará pegando as referências corretamente.

Feito a fórmula para a empresa verde podemos selecionar a célula C3 e clicar no quadrado no canto direito inferior e arrastar a fórmula para as outras empresas.

Desta forma teremos a quantidade de venda de cada uma das empresas que estamos analisando. Portanto temos uma forma mais rápida de analisar essas quantidades sem ter que contar uma a uma dentro da tabela.

Continuando com nossas análises temos um espaço para colocarmos a média das vendas dessas empresas na célula C8. Para isso basta utilizar a fórmula MÉDIA que tem apenas um argumento que são as células ou intervalos para calcular a média daqueles valores.

Neste caso iremos selecionar toda a coluna C da aba vendas, pois é nela que se encontram os valores das vendas das empresas.

Feito isso temos a média dos valores de todas as vendas dessas empresas.

Para uma análise um pouco mais detalhada vamos escrever ao lado da quantidade de cada empresa a quantidade vendas de cada empresa que foi superior à média total de vendas, ou seja, vamos contar agora apenas os valores de venda das empresas que foram superiores à média.

Os dois primeiros argumentos serão os mesmos que serão a coluna das empresas e o qual empresa efetuou a venda, em seguida é que vamos acrescentar mais dois argumentos para deixar nossa fórmula mais completa e nossa análise mais detalhada. Então esses novos argumentos serão intervalo_critérios2 e critérios2.

O intervalo_criterios2 será a coluna com todas os valores de vendas das empresas e o argumento critérios2 será justamente o critério “maior que a média de vendas”.

É possível observar que neste caso o critério possui o sinal de maior (>) que deve ser colocado entre aspas duplas, pois é considerado como texto e em seguida temos o & que é o símbolo para concatenar, ou seja, estamos concatenando o texto (>) com a célula que contém o valor da média das vendas.

Feito isso é possível observar que das 23 vendas da empresa Verde apenas 12 foram acima da média. Para completar a análise basta arrastar a fórmula para as outras empresas e teremos a análise completa de quantas vendas foram acima da média em cada uma delas.

OBS2: Vale lembrar que neste caso para arrastar a fórmula é necessário trancar a referência da célula do valor médio, pois não queremos que a essa célula se movimente. Para isso basta entrar na fórmula, clicar na referência da célula C8 e pressionar a tecla F4, desta forma irão aparecer os símbolos $ que indicam que a célula está trancada. Feito isso o usuário poderá arrastar a célula da fórmula e conseguirá o resultado correto.

Agora temos uma análise completa das vendas dessas empresas tendo conhecimento das vendas totais e de quantas vendas são maiores do que a média total.

Portanto essa fórmula nos permite ter resumos gerenciais detalhados de situações como essa, desta forma fica mais fácil analisar todos aqueles dados quando temos um resumo. Sendo assim o usuário não precisa contar de um em um e fazer contas e mais contas para descobrir essas informações.

Essa explicação e demonstração está no link do Youtube.

Fórmula CONT PROCV

O QUE É — A fórmula ou função PROCV Excel (também conhecida como VLOOKUP em inglês) é uma fórmula muito utilizada dentro do programa, pois permite com que o usuário faça busca de informações na vertical, o que facilita muito a busca de informações, pois ela passa a ser automática e deixa de ser manual.

Com isso o usuário ganha agilidade na busca, já pode trazer mais informações com mais fórmulas e evita trazer dados errados visto que a fórmula vai trazer exatamente o que o usuário deseja.

QUANDO — Essa fórmula será utilizada sempre que o usuário precisar fazer uma busca de uma ou mais informações dentro de uma tabela, então quando precisar de uma busca rápida e precisa para seus dados essa fórmula será bastante utilizada.

COMO USAR — Nesta aula vamos aprender a fazer o PROCV passo a passo para que os usuários que ainda não saibam conheçam e saibam exatamente quando e como utilizar essa ferramenta dentro do Excel.

Aqui temos a tabela inicial para explicarmos como a fórmula PROCV funciona, veja que temos diversas informações de cada um dos vendedores.

O objetivo então é dentro das células em cinza, colocar a informação de vendedor e registro e na célula ao lado obter automaticamente o contrato e total 2019 dessa informação.

Automaticamente nesse caso quer dizer que se tivermos alguma alteração na célula em cinza a célula do lado vai ser atualizada de acordo com a informação inserida.

Essa fórmula possui 4 argumentos para que possa funcionar e obter as informações de forma correta.

Valor_procurado — É a informação que estamos buscando na tabela, seja ela texto ou número.

Matriz_tabela — É a tabela em que vamos fazer a busca tanto do valor procurado tanto da informação que será retornada.

Núm_índice_coluna — É o número da coluna (baseado no intervalo selecionado) em que vamos retornar a informação referente ao valor procurado.

[Procurar_intervalo] — Ele está entre colchetes, pois se trata de um argumento que não é obrigatório, mas dentro do PROCV ele é muito importante, pois é com ele que vamos definir se a busca vai ser exata ou aproximada. É muito importante esse argumento, pois em 99% dos casos vamos buscar pela informação exata e não algo próximo aquilo.

Para esse caso vamos utilizar o seguinte preenchimento da fórmula, lembrando que o último argumento podemos colocar 0 ou FALSO para a busca exata.

IMPORTANTE: No segundo argumento é SUPER IMPORTANTE que a seleção da coluna comece a partir da coluna em que temos a informação procurada, por esse motivo iniciamos na coluna B e não na coluna A. Outro ponto muito importante é que a seleção desse intervalo tem que ir pelo menos até a coluna onde temos os dados que queremos retornar, que é a coluna E, mas nesse caso poderíamos ter selecionado até a coluna F sem problemas.

Sabendo disso podemos voltar a fórmula para verificar o resultado de acordo com o vendedor selecionado.

Temos exatamente a informação do contrato de um determinado vendedor, no entanto, ao modificar o nome desse vendedor nós teremos esse contrato sendo atualizado para a informação do vendedor que for selecionado.

Então já temos uma fórmula automática que facilita a busca das informações, lembrando que caso o Excel não encontre o nome do vendedor ele irá retornar um erro na célula que é #N/D. Que é um erro de valor não disponível, por isso é importante colocar como 0 ou FALSO no último argumento para procurar somente pela informação exata e nada parecido.

Agora podemos repetir o mesmo procedimento para obter a informação de total 2019 baseado na busca do registro.

Aqui temos o mesmo procedimento, mas as células são um pouco diferentes, pois a busca é diferente. A célula procurada agora é a H7 e o intervalo agora começa na coluna C e vai até a coluna F.

A questão do número da coluna é sempre baseada na seleção, neste caso de C até F temos 4 colunas no total e a informação que queremos está na quarta coluna.

Feito isso temos o resultado baseado no registro de cada um dos vendedores e novamente a fórmula é automática então caso o usuário altere o registro já terá o valor sendo alterado automaticamente.

OBS: É possível fazer essa busca em uma outra tabela de outra aba, então se precisarmos buscar essa informação em outras tabelas basta seguir o mesmo procedimento, mas na hora da seleção dos dados basta ir até a tabela desejada, selecionar o intervalo e finalizar a fórmula.

Agora para treinar a fórmula PROCV vamos deixar um exercício para que possam buscar as informações de preço unitário dos produtos baseado na tabela de preços também disponibilizada.

DICA: Lembrem-se da utilização do trancamento caso façam a seleção somente de um intervalo específico, ou seja, caso não selecionem a coluna como um todo.

Essa explicação e demonstração está no link do Youtube.

Até aqui, vimos a forma como entender as duas das fórmulas que precisaremos para resolver nossas oito questões.

A seguir, listei um vídeo explicativo para cada uma das próximas seis fórmulas para compreendermos a forma como essas fórmulas funcionam e nos ajudarão a resolver nosso teste.

Minha sugestão é assistir anotando o passo a passo e depois indo resolver sozinho as tarefas que é citado no vídeo de cada fórmula.

A FÓRMULA SE e a FÓRMULA SE ALINHADO tem um vídeo muito interessante no link.

A FÓRMULA SOMASES tem um vídeo muito interessante no link.

A FÓRMULA CONT.SE tem um vídeo muito interessante no link.

A FÓRMULA SOMASE tem um vídeo muito interessante no link.

A REMOVER DUPLICADOS tem um vídeo muito interessante no link.

Ufa. Terminamos os detalhes das fórmulas e então, agora vamos ao teste que citei no início do artigo.

A planilha modelo para as questões pode ser encontrada no drive e a explicação das questões estão no link.

A resolução está no link do Youtube do canal HashtagTreinamentos.

Nesse artigo, realizei as atividades de cada um dos vídeos e ao final resolvi o teste seguindo o vídeo de resolução. Depois retornei as tarefas mas sem seguir os vídeos e consultei as anotações que fiz. Repetir esse processo umas três vezes trará muita familiaridade ao excel e conseguirá resolver qualquer problema com uma simples consulta no google para confirmar a fórmula que precisará.

De um modo geral, nossa rotina exigirá umas 12 fórmulas e essas solucioná 80% dos nossos problemas do dia a dia de trabalho.

Esse artigo ficou menor que os demais, mas a intenção é tratar situações mais específicas nesse tipo de tema técnico e prático. Tomara que tenha gostado, aplauda o artigo! Até o próximo artigo e ótimos códigos.