Módulo 4 - Funções Básicas do Excel 2

Prévia do material em texto

Excel Básico ao Intermediário Versão 8.0 
 
1 
 
 
 
 
 
 
Módulo 4 - Funções Básicas do Excel 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
Excel Básico ao Intermediário Versão 8.0 
 
2 
 
 
Direitos desta edição reservados 
A Voitto Treinamento e Desenvolvimento 
www.voitto.com.br 
 
 
Supervisão editorial: Thiago Coutinho de Oliveira 
 
 
 
 
Versão 8.0 
 
É PROIBIDA A REPRODUÇÃO 
Nenhuma parte desta obra poderá ser reproduzida, copiada, transcrita ou 
mesmo transmitida por meios eletrônicos ou gravações sem a permissão, 
por escrito, do editor. Os infratores serão punidos pela Lei n° 9.610/98. 
 
 
 
http://www.voitto.com.br/
Excel Básico ao Intermediário Versão 8.0 
 
3 
 
Módulo 4 – Funções Básica do Excel 
O Excel é uma ferramenta com elevado potencial devido à sua capacidade de cálculo, 
armazenamento e eficiência no tratamento de dados. Grande parte dessa capacidade pode ser 
atribuída aos recursos de fórmula, ou seja, expressão que utiliza as operações e funções do Excel 
para calcular um resultado. 
Conforme introduzido no módulo 1, entende-se por função uma estrutura que executa cálculos, 
confere condições, retorna valores lógicos, dentre outros. O programa conta com inúmeras 
funções em sua biblioteca, na guia Fórmulas, conforme demonstrado abaixo. 
 
Além dessa alternativa, para utilizar essas funções, existem outras opções para inseri-las: 
 Caso já conheça a função, basta digitar o sinal “=” e o nome da função na célula; 
 Ao clicar no ícone “fx”, ao lado da barra de fórmulas, uma janela irá surgir na qual poderá 
escolher a função a ser utilizada. 
 
 
 
 
 
 
 
 
 
Excel Básico ao Intermediário Versão 8.0 
 
4 
 
 
 
 
 
 
 
 
 
 
 
 
 
4.1 Funções Lógicas 
4.1.1 Função SE (teste lógico; valor se verdadeiro; valor se falso) 
A função SE é muito utilizada em diversas situações. Ela traz a seguinte ideia: para um 
determinado teste lógico, se esse for atendido, ou seja, se a condição for verdadeira, a função 
retorna um determinado valor, caso contrário ela retorna um outro valor, ou seja, caso a 
condição seja falsa. 
Logo, a função pode retornar dois valores: “valor se verdadeiro” e “valor se falso”. Vamos 
exemplificar com um caso muito conhecido: A condição para que um aluno seja aprovado em 
determinada disciplina é a de sua nota ser igual ou superior a 6 (ou 60%). Se isso for atendido, 
ele está aprovado. Caso contrário ele será reprovado. Podemos representar visualmente como: 
 
Caso conheça apenas um dos argumentos ou característica da função, pode utilizar o 
campo de “pesquisa”, digitando a palavra chave. Vamos supor que você precise calcular 
os juros de um empréstimo realizado: Basta digitar a palavra chave “juros” no campo 
de pesquisa e o Excel trará opções de função que podem ser úteis: 
 
Perceba que, quando clicamos na função, aparecem as variáveis da função, bem como 
sua descrição. Podemos ir além, apertando “OK”. Ao fazer isso, irá aparecer outra janela 
com o detalhamento das variáveis a serem preenchidas. 
Dica Voitto: 
Excel Básico ao Intermediário Versão 8.0 
 
5 
 
 
 
 
 
 
 
 
A empresa Minimercados Vende Mais possui operação nas 100 cidades com maior PIB no Brasil. 
Ela possui os levantamentos dos valores vendidos em cada uma dessas cidades. Vamos verificar 
se as metas, que são as mesmas para cada cidade, foram alcançadas ou não. 
Na planilha temos o faturamento total de cada cidade representado pelo intervalo G6:G105 e a 
meta traçada para cada uma dessas cidades, representada pelo intervalo H6:H105. Uma vez que 
a meta é a mesma para todas as cidades, podemos utilizar a função SE para testar se os valores 
de faturamento total são maiores ou iguais à meta. 
Para a primeira célula, testaremos se G6 é maior ou igual a H6. Se isso for verdade, a função 
deve retornar o texto “Batemos a Meta”, caso contrário “Não batemos a Meta”. 
 
 
 
Conforme puderam perceber, a função SE é limitada a apenas um teste lógico e duas 
repostas: uma para “valor se verdadeiro” e outra para “valor se falso”. Caso deseje 
aumentar o número de respostas, basta adicionar outra função SE no lugar da resposta 
“valor se falso”. 
 =SE(H7=H8;"igual";SE(H7>H8;"maior";"menor")) 
Dica Voitto: 
 Exemplo 4 – Minimercados Vende Mais (Aba 1) 
Excel Básico ao Intermediário Versão 8.0 
 
6 
 
 
Aplique o autopreenchimento e vamos conferir o resultado das primeiras unidades. 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
E se tivermos o seguinte caso: 
 Abaixo de 800.000 deve aparecer “Não Batemos a Meta”; 
 Entre 800.000 e 900.000 deve aparecer “Próximo da Meta”; 
 Igual ou superior a 900.000 aparecer “Batemos a Meta”. 
Como poderíamos criar essa função condicional? 
 
Veja também: 
Excel Básico ao Intermediário Versão 8.0 
 
7 
 
 
 
 
 
 
 
 
 
4.1.2 Função E (teste lógico 1; teste lógico 2; ...) 
A função E pode apresentar como saída apenas dois valores: VERDADEIRO ou FALSO. Os 
argumentos inseridos na função são testes lógicos. Se todos esses testes forem atendidos, ou 
seja, a condições forem verdadeiras, a função retorna “VERDADEIRO”. No entanto, se pelo 
menos um dos testes lógicos for falso, a função retorna “FALSO”. 
4.1.2 Função OU (teste lógico 1; teste lógico 2; ...) 
A função OU também pode apresentar como saída apenas dois valores: VERDADEIRO ou FALSO. 
Os argumentos inseridos na função são testes lógicos. Diferentemente da função E, se pelo 
menos um desses testes for atendido, a função retorna “VERDADEIRO”. A função só irá retornar 
o valor “FALSO” se todos os testes lógicos forem falsos. 
Esse exemplo é dividido em duas partes. Vamos olhar a primeira para entender o funcionamento 
das funções E/OU primeiramente. Depois iremos trabalhar com a aplicação prática dessas. 
 
 
Essa é a primeira parte do material que estamos trabalhando com funções um pouco 
mais complexas, ou seja, que possuem argumentos de caráter diferente. Logo, estes 
devem ser inseridos na ordem solicitada pela função. O erro na ordem de 
preenchimento ocasiona erro no resultado. 
Outro erro bem comum é achar que no espaço para teste lógico podem ser inseridos 
vários testes separados por ponto e vírgula. Exemplo: =SE(x1>5; x2<10...). Conforme dito 
acima, só podemos ter 1 teste lógico. Para demais casos, confira novamente o Dica 
Voitto no início deste tópico. 
Principais Erros: 
 Exemplo 4 – E & OU (Aba 2) 
Excel Básico ao Intermediário Versão 8.0 
 
8 
 
Perceba que fizemos três testes. No primeiro, ambos os testes foram verdade, ou seja, 4 é maior 
do que 2 e 5 é menor do que 8. Já nos outros casos, isso não acontece. Vamos chamar a atenção 
que essa função serve para operações matemáticas, comparações de texto e ainda pode ser 
utilizada complementando outras funções, como a função SE. 
 
Podemos notar que, diferentemente da função E, para os mesmos testes lógicos o valor 
retornado foi “VERDADEIRO”, pois pelo menos uma das condições é verdadeira. 
Vamos observar agora uma aplicação prática. Neste exemplo, tem-se uma lista de maquinistas 
da ferrovia “Ferro nos Trilhos”. As caixas-pretas das locomotivas e as câmeras de segurança das 
cabines permitem verificar três itens importantes: a velocidade em determinado ponto do 
trajeto, se houve acionamento da buzina naquele ponto e se o maquinista estava usando EPI. 
Esses três fatores são usados para avaliar o desempenho do maquinista, verificar aplicação de 
penalidades e também compõem as remunerações dele e da equipe. O gerente de operações 
deseja saber como estão os maquinistas de sua região nos últimos meses e, para tal, tomou uma 
base de dados. Com base nesses dados e nas informações, verifique a situação das penalidades 
e dos bônus dos maquinistas. 
 
No campo “Existe Penalidade?”, verificaremos se algum item foi transgredido. Caso qualquer 
um, ou os três, tenham sido desrespeitados, existirá penalidade. Caso contrário,não existirá a 
penalidade. Portanto, podemos colocar três testes lógicos: 
 
No primeiro caso não houve penalidade, pois não foi ultrapassada a Velocidade Máxima, a 
Buzina foi acionada e o EPI foi utilizado, ou seja, nenhuma das regras foi infringida. Sendo assim, 
a função OU retornou o valor “FALSO”. Para a determinação da coluna “Existe Bônus? ”, esse só 
Excel Básico ao Intermediário Versão 8.0 
 
9 
 
existirá se todas as condições forem satisfeitas simultaneamente. Dessa forma, naturalmente 
utilizaremos o operador lógico E. 
 
4.1.3 Gabarito 
Na aba “E & OU”, a primeira atividade foi para entender o funcionamento das funções E/OU. As 
fórmulas relativas a esse exercício para a coluna “E” são: 
 
Para a coluna “OU” obtivemos o seguinte resultado: 
 
Já no segundo exercício, a ideia era encontrar quais motoristas haviam cometido infrações no 
trânsito e quis deveriam receber bônus por sua atuação. O resultado obtido foi: 
 
4.1.4 Função SEERRO (Valor; Valor se Erro) 
Muitas vezes nos deparamos com alguns erros em nossas planilhas, por exemplo, #N/D, 
#VALOR!, #REF!, #DIV/0 e outros. Em alguns momentos você não tinha consciência do erro e o 
Excel lhe alertou. Sendo assim, podemos contornar essa situação e diversas outras com a função 
Seerro. Ela retorna um valor determinado caso ocorra um erro. A função não atrapalha o 
funcionamento de nenhuma fórmula e só é ativada quando ocorre erro no resultado da célula. 
E
=E(4>2;5<8)
=E(8+4=12;4-5=2)
=E("Campinas"="São Paulo";"Rio de Janeiro"="Rio de Janeiro")
Teste
(4>2;5<8)
(8+4=12;4-5=2)
("Campinas"="São Paulo";"Rio de Janeiro"="Rio de Janeiro")
OU
=OU(4>2;5<8)
=OU(8+4=12;4-5=2)
=OU("Campinas"="São Paulo";"Rio de Janeiro"="Rio de Janeiro")
Teste
(4>2;5<8)
(8+4=12;4-5=2)
("Campinas"="São Paulo";"Rio de Janeiro"="Rio de Janeiro")
Maquinista Velocidade (km/h)Buzina EPI Existe Penalidade? Existe Bônus?
Thales 61 1 1 =OU(D12>70;E12=0;F12=0) =E(D12<70;E12=1;F12=1)
Eduardo 54 1 1 =OU(D13>70;E13=0;F13=0) =E(D13<70;E13=1;F13=1)
Murilo 60 1 1 =OU(D14>70;E14=0;F14=0) =E(D14<70;E14=1;F14=1)
Ryan 61 1 1 =OU(D15>70;E15=0;F15=0) =E(D15<70;E15=1;F15=1)
Matheus 66 0 0 =OU(D16>70;E16=0;F16=0) =E(D16<70;E16=1;F16=1)
Hugo 59 0 0 =OU(D17>70;E17=0;F17=0) =E(D17<70;E17=1;F17=1)
Vinicius 62 0 0 =OU(D18>70;E18=0;F18=0) =E(D18<70;E18=1;F18=1)
Julio 55 1 1 =OU(D19>70;E19=0;F19=0) =E(D19<70;E19=1;F19=1)
Excel Básico ao Intermediário Versão 8.0 
 
10 
 
 
 
 
 
 
 
 
 
A empresa Farmácia Silva possui um controle de vendas de produtos fármacos. Existem 
basicamente dois tipos de produtos em seu estoque: fármacos normais e amostras grátis dos 
medicamentos mais comuns. O controle de vendas realiza a margem de vendas, fazendo a 
divisão entre o preço de venda e o preço de custo, porém quando o produto apresenta preço 
de custo nulo, aparece o erro #DIV/0. Vejamos: 
 
A fórmula escrita é a divisão do preço de venda pelo preço de custo e subtraindo 1, para que 
possamos encontrar a porcentagem da margem de venda. 
Para contornar esse erro, utilizaremos a função SEERRO. Basta selecioná-la e no primeiro 
argumento manter a fórmula acima e no segundo argumento digitar o texto: “Amostra Grátis”, 
ou seja, se ocorrer um erro, saberemos que o produto não apresenta preço de custo. Portanto, 
ela é amostra grátis. Vejamos: 
 
 
Ao lado do código de erro terá um botão que possui uma série de opções relacionadas 
ao erro identificado. Segue abaixo os erros mais comuns que podem ocorrer: 
 #####: indica que o tamanho da coluna não é suficiente para exibir seu valor. 
 #VALOR!: indica que a fórmula possui um tipo errado de argumento. 
 #NOME?: Indica que a fórmula possui um texto que o Excel não reconhece. 
 #REF!: esse erro indica que na fórmula existe a referência para uma célula que 
não existe. 
 #DIV/0!: esse erro indica que a fórmula está tentando dividir um valor por 0. 
Dica Voitto: 
 Exemplo 4 – Farmácia Silva 
Excel Básico ao Intermediário Versão 8.0 
 
11 
 
 
4.1.5 Gabarito 
Conforme o exercício realizado, os valores das células são: 
 
Abaixo teremos um exemplo que tem a junção das funções trabalhadas nesse módulo. Vejamos: 
A empresa Jeans & Cia realiza testes de qualidade em seus lotes de produtos (calça, bermuda e 
casaco). Os testes realizados são: resistência da costura, fixação da tintura e resistência dos 
botões. Se um produto possuir pelo menos uma nota abaixo de 6 em algum desses testes, ele 
será rejeitado. Sua missão como gestor é verificar as notas em cada teste e preencher no 
intervalo G4:G36 se o produto foi APROVADO ou REPROVADO. 
Para resolver esse problema, iremos utilizar a função E dentro da função SE. Se o produto tiver 
nota maior ou igual a 6 no teste resistência da costura, fixação da tintura e resistência dos botões 
ele estará APROVADO. Caso contrário ele estará REPROVADO. Vejamos: 
 
Código Preço de Custo Preço de Venda Margem de Venda
COD 144 44 48 =SEERRO(D6/C6-1;"AMOSTRA GRÁTIS")
COD 187 0 0 =SEERRO(D7/C7-1;"AMOSTRA GRÁTIS")
COD 177 42 46 =SEERRO(D8/C8-1;"AMOSTRA GRÁTIS")
COD 110 15 22 =SEERRO(D9/C9-1;"AMOSTRA GRÁTIS")
COD 128 30 34 =SEERRO(D10/C10-1;"AMOSTRA GRÁTIS")
COD 135 0 0 =SEERRO(D11/C11-1;"AMOSTRA GRÁTIS")
COD 108 43 51 =SEERRO(D12/C12-1;"AMOSTRA GRÁTIS")
COD 105 20 23 =SEERRO(D13/C13-1;"AMOSTRA GRÁTIS")
 Exemplo 4 – Jeans & Cia 
Excel Básico ao Intermediário Versão 8.0 
 
12 
 
 
 
 
 
 
 
 
 
 
 
 
 
4.2 Funções Matemáticas e Estatística Básica 
4.2.1 Função CONT.SE (Intervalo; Critérios) 
Esta função é utilizada para contar argumentos que se repetem dentro de um determinado 
intervalo, caso os critérios estabelecidos sejam atendidos. Esta função segue a mesma lógica da 
função SE, ou seja, se o teste lógico for atendido, ocorre a contagem. Se não for atendido, não 
ocorre a contagem. 
 
 
 
 
 
 
 
Perceba que estamos cada vez mais aprofundando em funções, criando um raciocínio 
lógico para nos ajudar a resolver problemas rotineiros. Quando for montar uma 
sintaxe dessas, sempre identifique quais funções são as mais apropriadas e 
principalmente a lógica aplicada para que não ocorram “falsos verdadeiros”. 
Voltando ao caso acima, e se a condição para que o produto seja aprovado for ser 
aprovado (nota igual ou superior a 6) em pelo menos 1 teste. Como ficaria? 
 
Veja também: 
 
Quando trabalhamos com funções combinadas, um erro muito comum consiste na 
colocação de parêntese. Este é utilizado para iniciar e fechar uma função. Logo, alguns 
parênteses devem ser abertos e fechados dentro de uma função. Quando estes são 
esquecidos, o Excel não consegue executar a função. 
Principais Erros: 
 
Aprenderemos mais algumas funções que fazem contagem e soma condicional. O 
segredo para que essas possam funcionar é ter uma base de dados padronizada, ou seja, 
dados que apresentam algum tipo de ordenação, classificação ou agrupamento. Erros 
bobos fazem essa função não funcionar. 
Por exemplo: se você tiver os nomes João e Joao, o Excel entende que são argumentos 
diferentes. Portanto, uma dica é trabalhar com validação de dados para evitar esse tipo 
de problema no preenchimento. 
Dica Voitto: 
Excel Básico ao Intermediário Versão 8.0 
 
13 
 
A Empresa Alliance Confecções precisa realizar um levantamento das quantidades vendidas para 
obter informações de produtos que mais são vendidos, faturamento gerado e itens que devem 
ter seu estoque reposto. Primeiramente, utilizaremos a função Cont.se. 
Na tabela de resumo de vendas, temos três tipos de produtos e precisamos contá-los em uma 
base de dados. Sendo assim, devemos utilizar a função Cont.se para determinar quantas vezes 
o produto apareceu no intervalo. Vejamos: 
 
No intervalo D7:D550 estão contidos os produtos e o critério H7 é produto testado em cada uma 
das células do intervalo. O resultado é o número de vezes que o critério aparece no intervalo. 
 
4.2.2 FunçãoSOMASE (Intervalo; Critérios; [Intervalo Soma]) 
Perceba que agora nós temos um dos argumentos da função entre colchetes. Isso significa que 
este argumento é facultativo, ou seja, não é obrigatório ser inserido na função. Caso ele não seja 
inserido, teremos o mesmo resultado da função CONT.SE. 
Voltando ao mesmo exemplo, queremos que, dentro de um intervalo, os critérios estabelecidos 
sejam encontrados e contados e seus respectivos valores monetários, presentes em outro 
intervalo, sejam somados. Logo, temos dois intervalos: um para realizar a busca de acordo com 
o critério estabelecido e outro intervalo para realizar a soma dos valores. Vejamos: 
 
 Exemplo 4 – Alliance Confecções (Aba 4) 
 Exemplo 4 – Alliance Confecções (Aba 4) 
Excel Básico ao Intermediário Versão 8.0 
 
14 
 
Perceba a função: queremos que dentro do intervalo D7:D550, que diz respeito aos produtos, 
sejam contadas todas as vezes que a palavra calça, referente à célula H7, aparece e ainda some 
seus respectivos valores. 
 
 
 
 
 
 
 
 
 
4.2.3 Gabarito 
Conforme a atividade realizada nos tópicos sobre CONT.SE e SOMASE, utilizamos as seguintes 
fórmulas: 
 
 
4.2.4 Função CONT.SES (Intervalo Critérios 1; Critérios 1; Intervalo Critérios 2, Critérios 2; ...) 
Em algumas ocasiões, ter apenas um critério e um intervalo de busca não é o suficiente para 
solucionar nossos problemas. Sendo assim, seguindo o mesmo raciocínio da função CONT.SE, 
apresentaremos a função CONT.SES. Esta apresenta a mesma lógica, porém, ela nos permite ter 
vários intervalos com seus respectivos critérios. 
 
Produto Qtde Total
Calça =CONT.SE($D$7:$D$550;D5) =SOMASE($D$7:$D$550;D5;$E$7:$E$550)
Sapato =CONT.SE($D$7:$D$550;D6) =SOMASE($D$7:$D$550;D6;$E$7:$E$550)
Jaqueta =CONT.SE($D$7:$D$550;D7) =SOMASE($D$7:$D$550;D7;$E$7:$E$550)
Resumo de Vendas
 
Assim como vimos as funções de SOMA e de CONTAGEM, temos também a função 
MÉDIASE. Ela possui o mesmo tipo de funcionamento da SOMASE, porém realiza a 
média dos valores. Vamos calcular agora a média ao invés da soma no exemplo acima. 
Veja também: 
 
Um erro comum é a falha na definição de variáveis. Por exemplo, ser inserido no campo 
de “intervalo de critérios” o critério e vice-versa. Quando isso ocorre, o resultado da 
função é zero. Para o caso da SOMASE, muitas pessoas trocam a ordem do intervalo de 
critérios e o intervalo de soma. 
Principais Erros: 
Excel Básico ao Intermediário Versão 8.0 
 
15 
 
A empresa “FresaMais”, fabricante de peças para máquinas de lavar, possui operadores que 
confeccionam as peças. A cada momento que um operador conclui uma peça, o sistema registra 
o nome e a peça concluídos. Como acontece em muitos casos da vida real, o sistema não 
organiza esses dados, sendo que ao final do dia tem-se um enorme banco de dados apenas com 
os dados do nome do operador e tipo de peça. O objetivo é determinar quantas peças de cada 
tipo foram produzidas pelos operadores Joel e Fernando. 
Temos a seguinte situação: dois intervalos de busca relativos a operadores e peças. Logo, temos 
que buscar os nomes de Joel e Fernando dentro desse intervalo de operadores e código das 
peças produzidas por esses dois operadores no intervalo de peças. 
 
Analisando a função escrita acima, temos o intervalo de operadores B6:B1024 e seu critério de 
busca E10, relativo ao nome do Joel. Isso ainda não é o bastante, devendo ser inserido um 
segundo intervalo de busca com os códigos das peças C6:C1024 e o critério relativo a cada peça 
F10. Para o caso do Fernando, é a mesma situação, basta alternar a célula E10 por E11. Vejamos: 
 Exemplo 4 – FresaMais (Aba 5) 
Excel Básico ao Intermediário Versão 8.0 
 
16 
 
 
4.2.5 Gabarito 
No exercício anterior utilizamos a função CONT.SES. Para ambos os operadores, os resultados 
foram: 
 
 
Nome Tipo de Peça Peças Joel
Joel A3.500 =CONT.SES($C$6:$C$1024;$F43;$B$6:$B$1024;$E$10)
Fernando B3.500 =CONT.SES($C$6:$C$1024;$F44;$B$6:$B$1024;$E$10)
C3.500 =CONT.SES($C$6:$C$1024;$F45;$B$6:$B$1024;$E$10)
D3.500 =CONT.SES($C$6:$C$1024;$F46;$B$6:$B$1024;$E$10)
E3.500 =CONT.SES($C$6:$C$1024;$F47;$B$6:$B$1024;$E$10)
F3.500 =CONT.SES($C$6:$C$1024;$F48;$B$6:$B$1024;$E$10)
G3.500 =CONT.SES($C$6:$C$1024;$F49;$B$6:$B$1024;$E$10)
H3.500 =CONT.SES($C$6:$C$1024;$F50;$B$6:$B$1024;$E$10)
I3.500 =CONT.SES($C$6:$C$1024;$F51;$B$6:$B$1024;$E$10)
J3.500 =CONT.SES($C$6:$C$1024;$F52;$B$6:$B$1024;$E$10)
Nome Tipo de Peça Peças Fernando
Joel A3.500 =CONT.SES($C$6:$C$1024;$F43;$B$6:$B$1024;$E$11)
Fernando B3.500 =CONT.SES($C$6:$C$1024;$F44;$B$6:$B$1024;$E$11)
C3.500 =CONT.SES($C$6:$C$1024;$F45;$B$6:$B$1024;$E$11)
D3.500 =CONT.SES($C$6:$C$1024;$F46;$B$6:$B$1024;$E$11)
E3.500 =CONT.SES($C$6:$C$1024;$F47;$B$6:$B$1024;$E$11)
F3.500 =CONT.SES($C$6:$C$1024;$F48;$B$6:$B$1024;$E$11)
G3.500 =CONT.SES($C$6:$C$1024;$F49;$B$6:$B$1024;$E$11)
H3.500 =CONT.SES($C$6:$C$1024;$F50;$B$6:$B$1024;$E$11)
I3.500 =CONT.SES($C$6:$C$1024;$F51;$B$6:$B$1024;$E$11)
J3.500 =CONT.SES($C$6:$C$1024;$F52;$B$6:$B$1024;$E$11)
Excel Básico ao Intermediário Versão 8.0 
 
17 
 
4.2.6 Função SOMASES (Intervalo Soma; Intervalo Critérios 1; Critérios 1; Intervalo Critérios 2; 
Critérios 2; ...) 
A função SOMASES segue a mesma lógica da função SOMASE, porém assim como a CONT.SES, 
apresenta mais de um intervalo de critérios. Logo, é muito utilizado quando queremos somar 
valores com diversos critérios de seleção. Vejamos: 
A empresa Mineradora Steel possui dois tipos de produtos: Minério de Ferro bruto e a Pelota. O 
minério de ferro é encontrado na natureza na forma de rochas, misturado a outros elementos. 
Por meio de diversos processos industriais com tecnologia de ponta, o minério é beneficiado 
para, posteriormente, ser vendido para as indústrias siderúrgicas. As pelotas são pequenas 
bolinhas de minério de ferro usadas na fabricação do aço. Elas são feitas com uma tecnologia 
que utiliza os finos gerados durante a extração do minério, antes considerados resíduos. A 
siderúrgica que irá comprar deseja todos os lotes de minério de ferro e pelotas com peso igual 
ou acima de 150 toneladas e deseja saber o preço da compra de cada produto. 
 
Na situação acima temos dois tipos de produtos que devem ser separados, caracterizando duas 
compras. Ainda temos que selecionar os lotes que apresentam quantidade superior a 150 
toneladas e fazer a soma em dólares desses valores. 
 
Observando a função acima, temos o intervalo F6:F813 que representa o valor total dos lotes, 
um intervalo dividido entre minério de ferro e pelota C6:C813, o critério de seleção H6, que 
 Exemplo 4 – Mineradora Steel (Aba 6) 
 
Veja também: 
Excel Básico ao Intermediário Versão 8.0 
 
18 
 
nesse caso representa o minério de ferro e por último o intervalo com as quantidades D6:D813 
no qual vamos selecionar todos acima de 150 toneladas. 
 
 
 
 
 
 
 
 
 
4.2.7 Gabarito 
No exercício anterior, vimos como utilizar a função SOMASES. Dessa forma, as fórmulas 
utilizadas na aba “Mineradora Steel” foram as seguintes: 
 
4.3 Formatação Condicional 
Neste tópico vamos aprender os três tipos de formatação mais utilizados: Cores, Barras de 
Dados e Ícones. A formatação condicional é muito utilizada para complementar as funções 
condicionais E, OU, Se. Sua principal característica é tornar a planilha mais organizada 
visualmente, pois muitas das vezes nossas bases de dados são muito grandes, ficando difícil 
identificar os valores e a real representação destes, quando comparados com uma meta, por 
exemplo. 
 
 
Produto Critérios Valor (U$)
Minério de Ferro Maior ou igual 150 ton =SOMASES($F$6:$F$813;$C$6:$C$813;S43;$D$6:$D$813;">=150")
Pelota Maior ou igual 150 ton =SOMASES($F$6:$F$813;$C$6:$C$813;S44;$D$6:$D$813;">=150")
 
Quando trabalhamos com as funções SOMASES e MÉDIASES, o intervalo para 
soma/média é o primeiro argumento da função e não mais o último. Logo, muitaspessoas costumam confundir a ordem desses. Os erros apresentados anteriormente 
nesse módulo para a SOMASE, CONT.SE também se aplicam aqui. 
Principais Erros: 
 
A formatação condicional serve para auxiliar a análise de dados e enfatizar resultados. 
No entanto, muitas pessoas acabam exagerando na quantidade de cores e ícones na 
planilha, tornando o visual muito poluído. Com isso, o recurso acaba atrapalhando ao 
invés de ajudar. 
Dica Voitto: 
Excel Básico ao Intermediário Versão 8.0 
 
19 
 
 
 
 
 
Uma rede de franquias chamada Pizza Twist faz o controle do faturamento mensal e anual de 
todas as suas lojas espalhadas pelo país. Alguns parâmetros são avaliados para saber o 
desempenho das lojas no que diz respeito ao faturamento. São controlados os faturamentos 
mensais de cada loja, o faturamento anual dessas, dadas as suas respectivas metas. 
Primeiramente, vamos trabalhar os faturamentos mensais. Devemos identificar quais lojas 
possuíram faturamento mensal inferior a R$ 90.000,00 e aplicar uma formatação condicional. 
Para isso: 
 Selecionar o intervalo C7:N250; 
 Vá em Guia Página Inicial> Formatação Condicional > Realçar Regras das Células > É 
menor do que... 
 
 
 Exemplo 4 – Pizza Twist (Aba 8) 
Excel Básico ao Intermediário Versão 8.0 
 
20 
 
 
O resultado esperado é que todas as células que possuam valor inferior a R$ 90.000,00 estejam 
realçadas em vermelho, facilitando a identificação as lojas e os meses que tiveram um 
desempenho ruim. Deve ficar claro que esse valor digitado poderia ter sido um texto, ou seja, 
haver o destaque da célula toda vez que determinado texto ocorra. Além disso, ainda 
poderíamos ter referenciado a uma célula, ao invés de digitar. 
 
Acabamos de aprender uma das formas de formatação condicional. Ao abrir a opção de 
Formatação Condicional, visualizamos vários tipos de formatação já predefinidos. 
Vamos aprender agora a opção Barra de Dados: 
 Vamos selecionar o intervalo O7:O250; 
 Selecionar a primeira opção da Barra de Dados > Preenchimento Gradual > Barra de 
Dados Azul. 
Excel Básico ao Intermediário Versão 8.0 
 
21 
 
 
O resultado será: 
 
A opção de barra de dados define o valor máximo como a maior barra e as demais são feitas 
proporcionalmente. Com isso, podemos ter uma comparação entre valores de faturamento. 
 
 
 
 
 
Quando trabalhamos com valores muito próximos, a formatação do tipo Barra de Dados 
não apresenta grande funcionalidade, pois não é possível perceber a diferença de tamanho 
entre elas. P5:P248 para identificarmos quais lojas bateram a meta anual de R$ 
Principais Erros: 
Excel Básico ao Intermediário Versão 8.0 
 
22 
 
Para treinarmos a função SE, vamos utilizá-la no intervalo P7:P250 para identificarmos quais 
lojas bateram a meta anual de R$ 1.600.000,00. Se o faturamento anual for igual ou superior a 
esse valor, a meta foi batida, caso contrário, a meta não foi batida. Vejamos como fica: 
 
 
 
 
 
É fácil! Veja: 
Com o intervalo de P7:P250 selecionado: 
 Na aba Página Inicial vá até em Formatação Condicional > Nova Regra... 
 
 
É possível complementar com uma formatação condicional na Coluna P (Meta Anual) 
para o texto “Batemos a Meta” aparecer em azul e o texto “Não Batemos a Meta” 
aparecer em vermelho. Como podemos fazer isso? 
Veja também: 
Excel Básico ao Intermediário Versão 8.0 
 
23 
 
Ao selecionar a opção acima, a seguinte caixa irá se abrir na tela, onde deverá ser 
selecionado as opções abaixo: 
 No tipo de regra, “Formatar apenas células que contenham”; 
 Na edição da regra, o “Valor da célula” “é igual a” “Batemos a Meta”. Assim, estaremos 
editando todas as células que contenham o texto “Batemos a Meta”. 
 
Para definir qual será a formatação da célula, basta clicar em “Formatar” e editar conforme 
necessário. No exemplo, deixamos a fonte em Negrito e em cor Azul: 
 
Excel Básico ao Intermediário Versão 8.0 
 
24 
 
Aprenderemos agora uma formatação que é bem útil em painéis de controle que apresentam, 
na maioria das vezes, três opções. Para o nosso caso abaixo, iremos calcular a porcentagem de 
adesão à meta para o faturamento anual, ou seja, o quanto ficamos próximos do desejado. Claro 
que se esse valor for maior do que 100% é melhor, pois faturamos mais do que a meta. 
Primeiramente, vamos calcular a porcentagem de adesão à meta, fazendo a divisão do 
faturamento anual pela meta. Vamos referenciar a meta contida na célula P6. Lembre-se de 
congelar a célula para que não ocorram erros no autopreenchimento. Agora vamos utilizar a 
opção de formatação condicional. Para isso: 
 Selecione o intervalo Q7:Q250; 
 Conjunto de Ícones > Formas > 3 Semáforos (Não Coroados). 
 
O resultado obtido é: 
 
 
 
 
 
Excel Básico ao Intermediário Versão 8.0 
 
25 
 
Notamos um pequeno erro a princípio, pois adesões iguais e superiores a 100% ficaram com 
farol amarelo. O Excel considera por padrão: Verde (maior ou igual a 67%), Amarelo (entre 66 e 
33%) e Vermelho (inferior a 33%). Seria um parâmetro interessante, porém nosso valor máximo 
é superior a 100%. Logo, esse padrão não é válido para o nosso caso. Para corrigir isso, iremos 
alterar essa formatação condicional. 
 Selecione novamente o intervalo Q7:Q250; 
 Vá em Formatação Condicional > Gerenciar Regras: 
 
Ao selecionar essa opção, aparecerão todas as formatações condicionais contidas no intervalo. 
No nosso caso, só temos uma. 
 
Para modificá-la iremos utilizar a opção: Editar Regra. Ao clicar nessa opção, teremos a seguinte 
janela: 
Excel Básico ao Intermediário Versão 8.0 
 
26 
 
 
 
Nós iremos alterar os dois campos de valor para 1 e 0,9 e o tipo para “Número”. Para ficar 
visualmente mais “limpo”, iremos selecionar a opção para apenas aparecer o ícone. Vejamos 
como fica: 
 
 
Excel Básico ao Intermediário Versão 8.0 
 
27 
 
Com essas alterações, teremos o farol verde para uma adesão à meta igual ou superior a 100%, 
amarelo para uma adesão entre 90% e 99% e vermelho para uma adesão inferior a 90%. 
Vejamos: 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
O Excel trabalha com o padrão quanto maior, melhor. Por exemplo: com o campo de 
formatação condicional tipo ícone, a sete verde voltada para cima aparecerá para os 
maiores valores. 
O recurso funciona perfeitamente para o caso acima, pois quanto maior a adesão à 
meta, melhor, pois estamos tratando de faturamento. No entanto, se tivermos o caso 
de atraso de projeto esse recurso deve atuar de forma inversa. Para isso, utilize o botão 
“ordem inversa de ícones”. 
Veja também: 
 
Acima já apresentamos um principal erro que é na definição do intervalo que a formatação 
condicional irá atuar. Outro erro que pode ocorrer em formatações condicionais de modo 
geral é o conflito de regras entre uma ou mais formatações. Por exemplo, o mesmo valor 
atende aos requisitos de duas formatações condicionais aplicadas no mesmo intervalo. 
Fora isso, este recurso é bem simples de ser utilizado. 
Principais Erros: 
Excel Básico ao Intermediário Versão 8.0 
 
28 
 
4.4 Resumo do Módulo 4 
 Conhecemos a função SE que apresenta apenas 1 teste lógico e 2 respostas, sendo uma 
verdadeira e outra falsa. Para aumentar o número de testes lógicos nós utilizamos as funções 
E/OU dentro da função SE. Para aumentarmos os números de respostas nós utilizamos uma 
função SE dentro da outra, geralmente colocando no lugar da resposta SE FALSO; 
 Conhecemos as funções E/OU que sempre apresentam dois tipos de resposta apenas: 
VERDADEIRO ou FALSO; 
 Aprendemos também a funções CONT.SE, SOMASE e suas funções compostas que possuem 
um acréscimo de na quantidade de intervalo de critérios e critérios de seleção. Lembrem de 
ter sempre uma base de dados padronizada para que possamos trabalhar com apenas um 
critério de seleção, pois qualquer erro no preenchimento gera erros na sua fórmula. Porisso, 
utilize validação de dados no campo de preenchimento; 
 As formatações condicionais são bem simples de ser utilizadas, o único ponto de atenção é 
com a definição dos parâmetros, conforme vimos com o recurso tipo ícone. Apesar de ter 
aparecido o ícone, podemos dizer que foi um resultado “falso positivo”, pois estava errado. 
4.5 Gabarito do Exercício Extra: 
Na aba “Relatório Geral”, na tabela Festival Sertanejo, o primeiro cálculo realizado foi referente 
ao valor obtidos com os ingressos a cada venda. Para isso, na coluna “Valor” utilizamos as 
seguintes fórmulas: 
 
Após, calculamos quais foram os valores obtidos por meio das vendas em grupos: Quantos 
ingressos masculinos e femininos foram vendidos, com diferenciação entre ingressos inteiros e 
de estudantes. Para isso, as seguintes fórmulas foram utilizadas: 
 
Valor
=SE(E(D5="Inteira";E5="Masculino");150;SE(E(D5="Inteira";E5="Feminino");120;SE(E(D5="Estudante";E5="Masculino");75;60)))
=SE(E(D6="Inteira";E6="Masculino");150;SE(E(D6="Inteira";E6="Feminino");120;SE(E(D6="Estudante";E6="Masculino");75;60)))
=SE(E(D7="Inteira";E7="Masculino");150;SE(E(D7="Inteira";E7="Feminino");120;SE(E(D7="Estudante";E7="Masculino");75;60)))
=SE(E(D8="Inteira";E8="Masculino");150;SE(E(D8="Inteira";E8="Feminino");120;SE(E(D8="Estudante";E8="Masculino");75;60)))
=SE(E(D9="Inteira";E9="Masculino");150;SE(E(D9="Inteira";E9="Feminino");120;SE(E(D9="Estudante";E9="Masculino");75;60)))
Masculino
=CONT.SE($D$7:$D$477;H5)
Inteira =SOMASES($E$7:$E$477;$D$7:$D$477;H5;$C$7:$C$477;H7)
Estudante =SOMASES($E$7:$E$477;$D$7:$D$477;H5;$C$7:$C$477;H8)
Feminino =CONT.SE($D$7:$D$477;H10)
Inteira =SOMASES($E$7:$E$477;$D$7:$D$477;H10;$C$7:$C$477;H12)
Estudante =SOMASES($E$7:$E$477;$D$7:$D$477;H10;$C$7:$C$477;H13)
Público
Excel Básico ao Intermediário Versão 8.0 
 
29 
 
 
Já com relação ao último cálculo realizado, buscamos visualizar os valores conforme o ponto 
de venda utilizado para compra, podendo este ser via site, shopping ou representantes. Dessa 
forma obtivemos as seguintes fórmulas: 
 
Assim, foram utilizadas as funções SE, E, SOMASES, CONT.SE e SOMASE. 
Masculino
=CONT.SE($D$7:$D$477;H5)
Inteira =SOMASES($E$7:$E$477;$D$7:$D$477;H5;$C$7:$C$477;H7)
Estudante =SOMASES($E$7:$E$477;$D$7:$D$477;H5;$C$7:$C$477;H8)
Feminino =CONT.SE($D$7:$D$477;H10)
Inteira =SOMASES($E$7:$E$477;$D$7:$D$477;H10;$C$7:$C$477;H12)
Estudante =SOMASES($E$7:$E$477;$D$7:$D$477;H10;$C$7:$C$477;H13)
Público
Site =SOMASE($B$7:$B$477;K5;$E$7:$E$477)
Shopping =SOMASE($B$7:$B$477;K6;$E$7:$E$477)
Representantes =SOMASE($B$7:$B$477;K7;$E$7:$E$477)
Pontos de Venda