Data">
Apostila - Aula 02 - Fórmulas e Recursos de Mercado
Apostila - Aula 02 - Fórmulas e Recursos de Mercado
Apostila - Aula 02 - Fórmulas e Recursos de Mercado
1 INTRODUÇÃO
Olá, eu sou o professor Ítalo e estarei com vocês neste super curso, denominado Simplifica
Excel Express.
Você já deve saber que esta ferramenta está presente em quase todas as empresas, não é
mesmo? Portanto, dominá-lo, é um grande salto profissional!
Esta é a primeira das quatro apostilas que você terá acesso durante o curso Simplifica Excel
Express.
Para melhor aproveitamento dos seus estudos, utilize sempre a apostila em conjunto com as
nossas planilhas base e com as aulas!
Aproveitem!
2 FUNÇÃO SE
Descrição da Função: A função SE tem como objetivo retornar determinado valor ou texto de
acordo com um teste lógico pré-estabelecido. Ela também é chamada e função condicional, pois,
dependendo do resultado do teste lógico (Verdadeiro ou Falso) ela retorna diferentes valores.
• teste lógico: Diz respeito à comparação que iremos fazer. Qualquer valor ou expressão
que possa ser avaliada como VERDADEIRA ou FALSA pode ser inserida no teste lógico.
Por exemplo, A10=100 é uma expressão lógica; se o valor da célula A10 for igual a 100,
a expressão será considerada VERDADEIRA. Caso contrário, a expressão será
considerada FALSA.
• valor SE verdadeiro: É o valor que será retornado caso o teste lógico seja verdadeiro.
Pode ser um valor específico, um texto, uma nova fórmula.
• valor SE falso: É o valor que será retornado caso o teste lógico seja falso. Pode ser um
valor específico, um texto, uma nova fórmula.
Para compreender melhor a função SE, observe o exemplo abaixo, que atribui valores às células.
Por exemplo, em D8, ele compara SE B3=C3 (30=20). Como o resultado da expressão é FALSO,
ele escreve em F8, a letra “F”.
Em D9, ele compara SE B3>C3 (30>20). Como o resultado da expressão é VERDADEIRO, ele
escreve em F9 a letra “V”.
Considerando a planilha abaixo, imagine que o professor decidiu criar uma coluna, que deverá
informar a Situação (Status) do aluno, ou seja, se o aluno foi Aprovado ou Reprovado. Qual a
fórmula ele deve inserir? Como ficaria sua função?
SE a nota Total do Aluno for maior ou igual a 60; então ele está ”APROVADO”; caso contrário
ele está “REPROVADO”.
Observe que os pontos chaves da lógica necessária para resolver esta função, estão grifados.
Basta então adaptarmos estes pontos chaves, colocando-os em uma linguagem que o Excel
entenda, conforme mostra a imagem abaixo:
Fórmula: =SE(E3>=60;”Aprovado”;”Reprovado”)
Nota: Observando a fórmula anterior, você deve ter percebido que as palavras “APROVADO” e
“REPROVADO” aparecem entre aspas. Isto é um item necessário quando o valor retornado é
um texto. Em resumo, quando desejarmos retornar como resultado algum texto, devemos colocar
este texto entre aspas, caso contrário, o Excel apresentará um erro.
Vamos praticar:
Imagine agora que uma terceira condição seja inserida na coluna situação, trata-se do status
“Recuperação”, considerando as seguintes regras:
Nota: O tipo de situação que esse problema gera, é chamado de SE COMPOSTO. Esta questão
poderia ser resolvida também utilizando em conjunto com a estrutura função SE, a função E,
conforme veremos adiante neste material.
Obs: Verifique se as regras estão funcionando corretamente alterando os valores das Notas
Finais.
3 FUNÇÃO E, FUNÇÃO OU
Descrição da Função: Dentro do grupo de funções Lógicas do Excel, além do SE, que é a função
mais conhecida, temos também duas outras funções que, ao serem combinadas com o SE,
possuem muita utilidade. Como ótimos exemplos, temos as funções E e OU. Essas funções são
extremamente utilizadas, quando necessitamos passar mais de uma condição, para que o teste
lógico seja Verdadeiro ou Falso.
Analise as tabelas abaixo, que apresentam os resultados lógicos das funções E e OU.
→ Na função E, para que a resultante do teste lógico seja VERDADEIRA, todos os testes lógicos
devem ser verdadeiros.
→ Na função OU, para que a resultante do teste lógico seja VERDADEIRA, basta que um deles
seja verdadeiro.
Vamos agora ver as funções E e OU aplicadas na prática, em conjunto com a função SE. Para
isso, observe a planilha a seguir:
O gerente da empresa MasterFor Cursos decidiu dar uma comissão de 8% para um funcionário
caso o Valor Total de Vendas desse funcionário seja maior do que R$10.000,00 E a Quantidade
de Vendas desse funcionário seja maior do que 25.
Observando a tabela acima podemos perceber que quando tratamos da Função E, o teste lógico
só será verdadeiro se todas as condições forem verdadeiras.
Onde:
• teste_lógico: Diz respeito à comparação que iremos fazer. Qualquer valor ou expressão
que possa ser avaliada como VERDADEIRA ou FALSA pode ser inserida no teste lógico.
É possível inserir diversos testes lógicos.
Vamos agora, ver como integrar a função E à função SE. Para resolvermos a situação problema
descrita acima, teríamos a seguinte fórmula, que faria a seguinte análise.
=SE(E(C3>$B$9;D3>$C$9);8%*C3;0%*C3)
Perceba que a função E foi acrescentada no argumento TESTE LÓGICO da função SE. O
objetivo foi permitir que a função SE tenha duas condições em seu teste lógico (Quantidade de
Vendas >25 E Valor Total >=10000), para então retornar a comissão do funcionário.
Será que os resultados seriam diferentes, caso fosse utilizado a função OU? Vamos ver?
Considere então que o gerente resolveu ser menos rigoroso e decidiu dar uma comissão de 8%
para um determinado funcionário, caso o Valor Total de Vendas desse funcionário seja superior
a R$ 10.000,00 OU a Quantidade de Vendas desse funcionário seja superior a 25.
Observando a tabela acima podemos perceber que quando tratamos da Função OU, para que o
teste lógico seja verdadeiro, basta que um dos testes seja verdadeiro.
Onde:
• teste_lógico: Diz respeito à comparação que iremos fazer. Qualquer valor ou expressão
que possa ser avaliada como VERDADEIRA ou FALSA pode ser inserida no teste lógico.
É possível inserir diversos testes lógicos.
Vamos Praticar
É hora então de verificarmos quais seriam os resultados caso a função OU fosse utilizada em
conjunto com a função SE, para resolver a situação problema descrita anteriormente. Tente
aplicar esta função e compare os seus resultados com a planilha abaixo.
=SE(OU(C3>$B$9;D3>$C$9);8%*C3;0%*C3)
• Na função OU, para que a resultante seja VERDADEIRA, um ou mais testes lógicos
devem ser verdadeiros.
• Na função E, para que a resultante seja VERDADEIRA, todos os testes lógicos devem ser
verdadeiros.
4 FUNÇÃO SOMASE
Descrição da Função: A função SOMASE, tem como objetivo somar valores de acordo com
critérios pré-estabelecidos.
Nesta planilha, considere que você deseja descobrir qual o valor vendido em cada curso, que
deve ser preenchido na sub-tabela intitulada de Lucro por Curso. A Fórmula seria:
=SOMASE($D$5:$D$19;G10;$E$5:$E$19)
Inicialmente, o Excel irá procurar no intervalo de D5 até D19 (Nome do Curso), o valor contido
na célula G10 (“Excel”) e, caso encontre essa ocorrência, irá somar o valor correspondente à
mesma linha, especificado no intervalo E5 até E19.
Nesta situação então, o Excel iria somar os valores de: E6 + E10 + E11 + E13 + E14 + E15, pois
nas células D6, D10, D11, D13, D14, D15, foram encontrados o valor procurado: “Excel”.
Observação: Veja que o Intervalo em que o valor foi procurado e o intervalo de soma estão
“travados” como referência absoluta (sinal de $), para permitir que a fórmula seja “arrastada”,
visando encontrar o valor de soma dos demais cursos.
Fácil?
5 FUNÇÃO CONT.SE
Descrição da Função: A função CONT.SE conta o número de células dentro de um intervalo que
atendem a um único critério especificado.
Vamos praticar
Utilize a fórmula CONT.SE (similar ao que foi feito em SOMASE, porém, sem o intervalo de
soma) e complete a sub-tabela: Vendas por Curso. O resultado deverá ser:
=CONT.SE($D$5:$D$19;G10)
6 FUNÇÃO MÉDIASE
Descrição da Função: A função MÉDIASE, tem como objetivo calcular a média de acordo com
critérios pré-estabelecidos.
Vamos praticar
Utilize a fórmula MÉDIASE para calcular a média por empresa e por estado. Você já aprendeu a
lógica desta fórmula quando trabalhou com as funções SOMASE e CONT.SE, não é?
7 GERENCIADOR DE NOMES
O Excel permite atribuir nomes às células ou a um conjunto de células. Isto pode tornar a sua
identificação mais fácil, além de ser indispensável para a criação de Listas para Validação de
Dados.
Toda célula no Excel possui uma identificação (um nome padrão). Esse nome tem como base a
linha e a coluna da respectiva célula.
Na figura acima vemos que o nome da célula onde temos um dos registros do José da Silva é
C8, pois, a mesma, está na coluna C e na linha 8.
Para dar um nome à uma Célula ou a um Intervalo de Células, basta clicar com o botão direito
sobre ela e em Definir Nome.
Vamos Praticar
Vamos aprender a utilizar este recurso na prática, realizando novamente a função SOMASE.
Entretanto, desta vez, os intervalos serão nomeados.
Para que isso seja possível, precisaremos definir nomes para os valores contidos na coluna
Nome do Curso e Valor do Curso.
Selecione todos os Nomes dos Cursos, isto é (D5:D19) e clique com o botão direito em Definir
Nome. Defina este intervalo como Nome_Curso, conforme exemplo abaixo.
Após este procedimento, tente executar a função SOMASE novamente, porém, indicando os
nomes dos intervalos. A Fórmula ficará assim:
=SOMASE(Nome_Curso;G10;Valor_Curso)
Na prática, o Excel vai procurar no intervalo intitulado Nome_Curso, o valor de C10 (“Excel”) e,
somará o valor correspondente que estiver no intervalo intitulado Valor_Curso.
Será exibida uma lista com todos os nomes e suas respectivas referências.
Através desta opção, você pode atualizar o intervalo, renomear, excluir, criar um novo, etc.
Quanto maiores e mais complexas são as suas planilhas, mais útil esta opção se torna.
8 FUNÇÃO PROCV
O Excel permite fazer pesquisas baseadas em uma lista de dados (matriz tabela), usando
determinado argumento (valor procurado), para retornar um valor relacionado a ele. Esta procura
pode ser feita de maneiras diferentes, conforme veremos a seguir.
Quando o usuário desejar buscar uma informação em uma tabela que possui seus dados
relacionados verticalmente, ele deverá usar a função PROCV.
Descrição da Função: A função PROCV realiza a procura vertical, ou seja, quando os dados
correspondentes estão relacionados em colunas. Abaixo, um exemplo de uma tabela com este
tipo de organização.
Nota: O segredo para PROCV é organizar seus dados de modo que o valor que você procura,
por exemplo o Nome do Funcionário, esteja à esquerda do valor de retorno, por exemplo, o
registro ou um determinado mês de venda. Nesta planilha temos uma coluna de referência
(Nome) e valores que serão retornados de acordo com o nome do funcionário (Registro, E-mail,
Telefone, Vendas).
Onde:
• valor_procurado: É o argumento que deseja fornecer como base para a procura ser feita,
ou seja, é o valor de pesquisa;
• matriz_tabela: É o intervalo onde se realizará a pesquisa. Lembre-se que o valor
procurado deve estar na primeira coluna da matriz_tabela.
• num_coluna: É a coluna que contém o valor que se deseja obter como resultado,
considerando que as colunas são contadas a partir do intervalo estipulado em
matriz_tabela;
Nota: na grande maioria dos casos, a correspondência será EXATA e por isso o valor 0 ou
FALSO será indicado no último argumento. Entretanto, veremos exemplos de situações em que
iremos procurar por uma correspondência APROXIMADA, ou seja, indicando o valor 1 ou
VERDADEIRO.
Considere a planilha apresentada anteriormente, cujo nome é “BD_Func”, como uma base de
dados que apresenta informações sobre os funcionários.
Esta é a planilha principal, denominada “Consulta”, em que é necessário buscar os dados dos
funcionários, de acordo com o nome do funcionário que for digitado em G5.
Para buscar o registro do funcionário, inserindo-o na célula C9, teremos a seguinte fórmula:
=PROCV($G$5;BD_Func!$B$4:$H$8;2;0)
Analisando a fórmula, temos que: O Excel vai procurar o valor presente na célula G5 que, neste
momento é “Clovis Salgado”. O Excel irá procurar este valor na planilha BD Alunos, no intervalo
de B4 até H8, isto é, na Tabela de Funcionários, descrita abaixo:
Ao encontrar o valor de G5 (Clovis Salgado), que está na célula B8, da planinlha BD Alunos, ele
irá retornar o valor correspondente, que está na segunda coluna, considerando o intervalo
selecionado, ou seja, ele retornará o valor da célula C8. Fácil, né?
Vamos praticar:
Utilize a fórmula PROCV para retornar o e-mail, o telefone e as vendas de janeiro, fevereiro e
março. Realize testes trocando o nome do funcionário. Você deverá ver os resultados atualizando
automaticamente, conforme exemplo abaixo.
Como desafio, tente inserir este gráfico, de acordo com os meses de venda.
9 FUNÇÃO PROCH
A função PROCH é similar à função PROCV. Entretanto, ela é utilizada quando o usuário desejar
buscar uma informação em uma tabela que possui seus dados relacionados horizontalmente.
Descrição da Função: A função PROCH realiza a procura horizontal, ou seja, quando os dados
correspondentes estão relacionados em linhas.
Onde:
• valor_procurado: É o argumento que deseja fornecer como base para a procura ser feita,
ou seja, é o valor de pesquisa;
• matriz_tabela: É o intervalo onde se realizará a pesquisa. Lembre-se que o valor
procurado deve estar na primeira linha da matriz_tabela.
• num_linha: É a linha que contém o valor que se deseja obter como resultado,
considerando que as linhas são contadas a partir do intervalo estipulado em matriz_tabela;
• procurar_intervalo: É a precisão da pesquisa, podendo ser exata ou por aproximação do
valor desejado. O argumento VERDADEIRO ou 1 retorna uma correspondência
aproximada e o argumento FALSO ou 0 retorna uma correspondência exata.
Vamos praticar:
Considere a existência de uma planilha que apresenta um banco de dados de cursos. Esta
planilha possui o nome de “BD Cursos” e pode ser visualizada abaixo.
Utilize a fórmula PROCH para encontrar o código e o valor do curso digitado na célula C5. Altere
o nome do curso para confirmar que a função está funcionando adequadamente. Abaixo, o
exemplo de como será o resultado.
Resumindo, para encontrar o Código do Curso, o Excel iria buscar o valor de C5 (C5=”Word”),
na planilha BD Alunos, no intervalo de C3 até F5, retornando o valor da segunda linha (2). A
mesma lógica será aplicada para buscar o valor, porém, neste caso, o valor retornado estará na
terceira linha (3).
Onde:
• valor_procurado: É o argumento que deseja fornecer como base para a procura ser feita,
ou seja, é o valor de pesquisa;
• matriz_tabela: É o intervalo onde se realizará a pesquisa. Lembre-se que o valor
procurado deve estar na primeira coluna da matriz_tabela.
• num_coluna: É a coluna que contém o valor que se deseja obter como resultado,
considerando que as colunas são contadas a partir do intervalo estipulado em
matriz_tabela;
• procurar_intervalo: É a precisão da pesquisa, podendo ser exata ou por aproximação do
valor desejado. O argumento VERDADEIRO ou 1 retorna uma correspondência
aproximada e o argumento FALSO ou 0 retorna uma correspondência exata.
Em resumo, ele procura um único valor, em uma matriz tabela e retorna um valor correspondente.
Mas, e se tivéssemos que fazer uma procura de uma condição dupla? Veja a tabela abaixo:
O que precisaria ser feito para que o Excel buscasse o faturamento da Casas Bahia no estado
de MG?
Esta tarefa só seria possível de ser concluída, se fizermos com que o PROCV consiga buscar
um determinado valor, com base em duas condições. E, como fazer isso? Simples! Siga os
passos abaixo.
Para concluir este passo, você pode usar o &, que consegue unir (concatenar) valores de duas
células. Veja como ficará o resultado:
Passo 2: Construir o PROCV, realizando a concatenação do Valor Procurado, para que ele tenha
o mesmo padrão da coluna auxiliar.
=PROCV(G2&G3;A3:D18;4;0)
Vamos entender o que o Excel está fazendo? Ele está procurando o G2&G3, isto é: “Casas
BahiaRJ”, no intervalo de A3:D18. Ele vai encontrar este valor na célula A17, certo? Assim que
ele encontrar, o que ele faz? Retorna o valor correspondente, existente na quarta coluna, isto é,
a coluna do Faturamento, das Casas Bahia do RJ.
Funcionou?
Considerando ainda a planilha anterior, o que aconteceria se o usuário digitasse Casa Bahia ao
invés de Casas Bahia? Veja o resultado!
O que é esse #N/D? Ele indica um erro, que demonstra que o valor não está disponível. E por
que isto ocorre? Porque não existe nenhuma “Casa Bahia” na planilha.
Existem diversas formas para “corrigir” este problema, tais como: Validação de Dados e
SEERRO. Agora, veremos a função SEERRO.
Descrição da Função: A função SEERRO retorna um valor ou uma informação especificada por
você, caso o resultado da fórmula original apresente um erro.
Onde:
Nesta situação acima, vamos indicar a seguinte mensagem para o usuário: “Verificar Loja e
Estado”. Esta é uma mensagem que fará com que o usuário que inseriu o dado errado,
consiga perceber o que está acontecendo. Para isto, teremos a seguinte fórmula.
Veja como ficou o resultado da planilha agora, quando algo é digitado incorretamente.
Curtiu?
Até então, utilizamos a função PROCV e PROCH indicando no último argumento que
desejávamos uma correspondência EXATA, ou seja, estávamos procurando especificamente
um valor. Entretanto, existem situações em que a correspondência APROXIMADA é
extremamente útil. Analise a planilha a seguir:
Como isso poderia ser feito? Basta utilizar o PROCV normalmente, porém, indicando como
último argumento o valor 1 ou VERDADEIRO, que indica correspondência aproximada. Antes
de visualizarmos isso na prática, veja como ficaria a tabela se a correspondência exata fosse
indicada, através da fórmula:
=PROCV(C5;$F$4:$G$8;2;0)
Veja que, indicando correspondência EXATA, o Excel só retornará valores exatos que são
encontrados na Matriz Tabela. Seria muito trabalhoso indicar os valores de 0% à 99%, concorda?
=PROCV(C5;$F$4:$G$8;2;1)
Quando você indica correspondência APROXIMADA, o Excel pressupõe que a primeira coluna
na Matriz Tabela seja classificada numericamente ou alfabeticamente e, em seguida, procurará
o valor mais próximo.
Ou seja, no exemplo acima, inicialmente ele procura de 0 até o valor anterior ao próximo registro
da tabela, que neste caso é 60. Depois, ele procura de 60 até o valor anterior ao próximo registro
da tabela, que neste caso é 70 e assim por diante.
Então, o segredo para utilizar a correspondência APROXIMADA é ter a sua matriz tabela
ordenada corretamente.
13 ÍNDICE + CORRESP
Até então, vimos duas fórmulas de procura no Excel: PROCV e PROCH, aplicadas a diferentes
situações, certo?
Você se lembra qual um requisito básico para estas fórmulas funcionarem? Isto mesmo, o valor
procurado precisa estar na primeira coluna da Matriz Tabela (PROCV) ou na primeira linha da
Matriz Tabela (PROCH).
Mas, e se tivéssemos uma situação diferente dessa? E se quiséssemos realizar uma procura em
uma direção diferente? É possível?
Sim, para isso existem algumas outras funções, dentre elas a excelente combinação de ÍNDICE
+ CORRESP.
Nesta planilha, o objetivo é digitar o nome do time e retornar a Classificação e o Status que estão
à esquerda do valor procurado (PROCV e PROCH não conseguem) e a quantidade de Pontos,
que está à direita.
Perceba que esta é uma situação que acontece frequentemente, em tabelas de produtos, de
funcionários, de vendas, de estoque, de alunos e em inúmeras outras.
Para cumprir esta tarefa, utilizaremos as funções ÍNDICE e CORRESP combinadas. Para facilitar
a compreensão, inicialmente, separaremos as funções.
13.1 CORRESP
Onde:
Para facilitar o entendimento da função, vamos executar a seguinte fórmula na célula H7:
=CORRESP(H2;D3:D22;0)
Ela está procurando o valor de H2 (neste momento “Cruzeiro”) no intervalo de D3:D22 (coluna
em que estão localizados os nomes dos times). O zero (0) significa que estamos querendo uma
correspondência exata, isto é, só retornará o valor caso encontre “Cruzeiro”.
Veja que a função retornou o valor 17. O que isto significa? Que o Cruzeiro está na linha 17 da
Matriz selecionada (D3:D22).
Guarde esta informação, pois, precisaremos desta linha para utilizar a função ÍNDICE.
13.2 ÍNDICE
Descrição da Função: A função ÍNDICE retorna um valor dentro de uma tabela ou intervalo, de
acordo com a linha e coluna indicadas.
Onde:
Vamos agora, aplicar a fórmula índice, para obtermos a Classificação, o Status e os Pontos do
time indicado. Como ficariam as nossas funções?
• Classificação =ÍNDICE($B$3:$E$22;$H$7;1)
• Status =ÍNDICE($B$3:$E$22;$H$7;2)
• Pontos =ÍNDICE($B$3:$E$22;$H$7;4)
Vamos entender o que o Excel fez? Para isso, vamos utilizar a fórmula presente no resultado do
Status (H4):
=ÍNDICE($B$3:$E$22;$H$7;2)
A função ÍNDICE, solicitou que o Excel procurasse na matriz B3:E22 (toda a tabela), o valor que
estava na célula H7 (resultado da função CORRESP) e na coluna 2 (número da coluna que
contem os Status).
Por que foi preciso da função CORRESP? Pois, a linha em que um time está é dinâmica, logo,
ao alterar o nome do time, esse valor também irá se alterar e, não queremos ter que alterar a
fórmula, não é mesmo?
Mas... e como podemos “sumir” com a função auxiliar CORRESP da nossa planilha, para deixá-
la mais bonita? Basta inseri-la dentro da função ÍNDICE, assim:
• Classificação =ÍNDICE($B$3:$E$22;CORRESP(H2;D3:D22;0);1)
• Status =ÍNDICE($B$3:$E$22;CORRESP(H2;D3:D22;0);2)
• Pontos =ÍNDICE($B$3:$E$22;CORRESP(H2;D3:D22;0);3)
Perceba que, simplesmente trocamos o valor de H7 (que era a célula que indicava a linha) pela
função CORRESP, que tem como função indicar a linha.
Não se assuste! Essas funções a princípio parecem e são mais difíceis mesmo, hehe! Mas, com
o tempo e com exercícios, você fica fera e até acaba abandonando o PROCV e PROCH.
Acredite!
Lembra que eu comentei na primeira aula de alguns bônus especiais? Olha o primeiro aqui, a
nossa querida planilha de controle de orçamento doméstico!
Para fazer o download desta planilha, clique aqui: Controle de Orçamento Doméstico.
Para assistir a aula de desenvolvimento da planilha, clique aqui: Aula – Controle de Orçamento
Doméstico.
Curtiu?
15 SOBRE O AUTOR
Olá pessoal,
Eu sou o professor Ítalo Teotônio, que estará com vocês neste curso: Simplifica Excel Express.
Ao longo do curso, você aprenderá diversos recursos e fórmulas do Excel, que é uma das
ferramentas mais utilizadas pelas empresas atualmente, independente do segmento ou porte.
Sou professor e profissional há 12 anos e, Excel é uma das minhas paixões. Neste tempo, foram
incontáveis treinamentos, para inúmeros alunos e empresas.
Ah, e também sou certificado pela Microsoft nesta incrível ferramenta que é o Excel!
Até!