1584103943BásicoIntermediárioExcel8 0

Excel Intermediário

•

UNIASSELVI

Rozangela Mendes

05/09/2023

Esta é uma pré-visualização de arquivo. Entre para ver o arquivo original

BásicoIntermediárioExcel8.0/Basico_Intermediario/Módulo 2 - Manipulação de Planilha.pdf
Excel Básico ao Intermediário Versão 8.0

Módulo 2 - Manipulação de Planilhas

Excel Básico ao Intermediário Versão 8.0

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

Módulo 2 – Manipulação de Planilhas
2.1 Planilhas
Como visto anteriormente, as tabelas são o instrumento de trabalho do Excel. Podemos
trabalhar com mais de uma tabela ao mesmo tempo, através de suas abas, recurso que permite
que analisemos e façamos cálculos diferentes de forma rápida e inter-relacionada.

Muitas pessoas confundem a terminologia. O arquivo em Excel se chama Pasta de Trabalho. A
região acima que se chama aba ou planilha. Para inserir uma nova aba ou planilha, basta utilizar
o atalho Shift + F11 ou clicar em:

2.2 Navegação pelas planilhas
Geralmente possuímos várias bases de dados ou informações diferentes, devendo essas ser
colocadas em abas ou planilhas diferentes. Para navegar entre as abas ou planilhas, pode-se
clicar com o botão esquerdo sobre cada uma delas ou utilizar o atalho CTRL + PgDn para avançar
ou CTRL + PgUp para recuar.
2.3 Desenvolvendo mais de uma planilha ao mesmo tempo
Várias vezes você já deve ter desenvolvido tabelas iguais em planilhas diferentes em que apenas
os dados internos variavam. Esse tipo de situação é bem comum para pessoas que trabalham
com controles periódicos (diários, semanais, mensais). Vejamos como trabalhar esse tipo de
situação a seguir.

A empresa Voitto Metais fabrica insumos para construções de pequeno porte e possui três
turnos de trabalho. O gerente geral Thiago Coutinho pretende levantar os dados da produção
de todas as linhas e turnos. Com base nos dados fornecidos abaixo, vamos criar a planilha para
tal feito.
Exemplo 2.1 – Voitto Metais
Excel Básico ao Intermediário Versão 8.0

Vamos renomear as planilhas, clicando com o botão direito (ou clicando duas vezes com o botão
esquerdo) no título de cada uma, com os nomes: “Painel”, “Resumo da Produção”, “Turno 1”,
“Turno 2”, “Turno 3” e “Gráficos”, respectivamente.

Para selecionar todas as abas, mantenha a tecla SHIFT pressionada e clique nas planilhas
desejadas. Vejamos esta aplicação:
 Clique na planilha “Resumo da Produção” e, com a tecla SHIFT pressionada, clique na
planilha “Turno 3”. Com todas as abas selecionadas, iremos confeccionar a tabela
abaixo. Esta deverá estar presente em todas as quatro guias citadas acima.
Com as quatro planilhas selecionadas, crie uma tabela a partir da célula B5 até a célula I9, e
formate-a conforme a figura a seguir:

 Pronto! Agora que já foi criada a tabela, navegue entre as planilhas “Resumo da
Produção”, “Turno 1”, “Turno 2” e “Turno 3” para ver o que aconteceu. Caso tenha
seguido os passos corretamente, as quatro planilhas devem apresentar a mesma tabela
criada.

Quando estiver trabalhando com várias abas/planilhas, uma opção para separar áreas
afins é colocar cores diferentes nas guias clicando com o botão direito do mouse em
cima da guia, em seguida clicando em Cor da Guia e escolha cores diferentes para cada
uma delas.
Dica Voitto:
Excel Básico ao Intermediário Versão 8.0

Segue a aplicação desse procedimento:
 Clicar com o botão direito na planilha “Resumo da Produção” e selecionar em Mover ou
Copiar.

 Clique em (mover para o final) e marque a caixa de diálogo abaixo criar uma cópia.

Caso você tenha se esquecido de selecionar todas as planilhas no início, e ainda assim
precise repetir a mesma estrutura, não se preocupe.
Selecione a planilha que você deseja copiar a estrutura e clique com o botão direito >
Mover ou Copiar > Marque a opção “Criar uma Cópia” > Escolha o lugar que se deseja
inserir.
Veja também:
Excel Básico ao Intermediário Versão 8.0

Pronto, foi criada uma cópia da planilha de forma idêntica. Agora é só alterar o nome e
prosseguir com os trabalhos.

2.4 Hiperlink (CTRL + K)
Continuando o trabalho com os dados da produção da nossa fábrica, vamos agora aplicar um
modo mais ágil e eficiente de navegar entre nossas planilhas. A primeira aba da planilha “Painel”
deve possuir hiperlinks que direcionam para os três turnos, para o resumo e para os gráficos,
facilitando a navegação.
Os hiperlinks são atribuídos com maior frequência a três tipos de referências: células, formas e
imagens.

Vamos trabalhar agora esses três tipos de referência.

Quando vamos trabalhar com várias planilhas ao mesmo tempo, um erro muito comum
é esquecer-se de segurar o SHIFT na seleção de todas as abas. Sendo assim, todo o
procedimento que você aplicar a uma planilha, não se repetirá para as outras. Outro
erro comum também é realizar o procedimento correto de seleção, porém clicar em
outra planilha fora desse intervalo, acabando com o vínculo.
. Logo, o Excel entende que é para aplicar o recurso apenas naquele intervalo e não na
Principais Erros:
Excel Básico ao Intermediário Versão 8.0

Primeiramente, vamos inserir uma forma e uma imagem à nossa planilha. Seguiremos o
procedimento: Inserir > Ilustrações > Formas > Retângulo de cantos arredondados.

Posicionar a forma no campo delimitado por linhas tracejadas e ajustá-la de acordo com essa
delimitação.

Para melhorar a estética da planilha, pode ser feita uma formatação da forma inserida.

A forma pode ser alterada recorrendo à Guia Ferramentas de Desenho > Formatar >
Estilos da forma. Nessa guia podem ser escolhidos formatos pré-definidos ou você
mesmo pode criar um formato a partir da seleção do preenchimento, contorno e efeito
de forma.
Dica Voitto:
Excel Básico ao Intermediário Versão 8.0

Após aplicar uma formatação, basta adicionar o nome “Resumo da Produção” à forma. Isso pode
ser feito clicando com o botão direito > editar texto ou apenas clicar com o botão esquerdo na
forma e digitar o nome. Os demais ajustes de posicionamento do texto podem ser feitos na
página inicial. O resultado é:

Por último, vamos inserir uma imagem no campo delimitado. Para isso, basta clicar em Inserir >
Imagem > Exemplo2.png. Assim como foi feito o ajuste da forma, será feito o ajuste da imagem
ao contorno delimitado pelas linhas tracejadas.

Ao trabalhar com a inserção de imagens baixadas da internet, priorize a utilização de
ícones, pois eles apresentam uma aparência mais clean e são mais didáticos. Para
encontrar ícones, basta digitar o nome de interesse composto por ícone (exemplo:
finanças ícone). Imagens que possuem plano de fundo poluem o visual a planilha.
Dica Voitto:
Excel Básico ao Intermediário Versão 8.0

Para inserir hiperlinks para (“Turno 1”, “Turno 2”, “Turno 3”, “Resumo da Produção” e
“Imagem”), podemos:
 Selecionar item desejado e utilizar atalho Ctrl+K;
 Selecionar item desejado > Guia Inserir > Link.

 Clicar com botão direito no item desejado e selecionar “Link...”

Após a abertura da janela, selecionar
“Colocar neste documento” > atribuir referência aos
respectivos nomes.

Excel Básico ao Intermediário Versão 8.0

Repetir esse procedimento para todas as referências.

A inserção do hiperlink deve ser feita da mesma forma, porém agora todos serão direcionados
para a aba “Painel”. Para isso, clicaremos em Inserir > Ilustrações > Formas > Setas Largas > Seta
para a esquerda. Seguiremos a formatação Formatar > Estilos de Forma > Efeito Intenso – Preto
escuro, Ênfase 1.

2.5 Auto cálculo
Este próximo recurso é muito simples e útil. Várias vezes quando você está trabalhando em uma
planilha, pode necessitar de obter cálculos (como soma, média, valor mínimo ou valor máximo)
apenas para ter uma ideia dos valores, não necessitando que os mesmos estejam contidos na
sua tabela de dados. Para isso, existe um recurso no Excel que apresenta esses cálculos de forma
rápida e prática.
Antes de utilizar este recurso, vamos preencher os dados de produção das tabelas “Turno 1, 2 e
3”, conforme as imagens abaixo.

Para ter numa navegação completa pela nossa planilha, devemos criar hiperlinks nas
demais abas que retornam para o Painel. Eles devem ser inseridos dentro das
delimitações tracejadas. penas naquele intervalo e não na planilha inteira.
Veja também:

Um erro usual é manter a seleção múltipla das planilhas. Quem tentou inserir o hiperlink
e todas as planilhas estavam selecionadas, a opção estava desabilitada. Por isso,
certifique-se que a planilha esteja fora de um intervalo de seleção.
Principais Erros:
Excel Básico ao Intermediário Versão 8.0

Na aba “Turno 1”, selecione o intervalo de B5 a I9. Observe agora que no canto inferior direito
da tela do Excel irão aparecer cálculos referentes aos valores selecionados.

2.6 Referência 3D
Para realizarmos a operação de SOMA do total produzido de cada peça em todas as planilhas
simultaneamente, pode-se repetir o procedimento realizado na criação dessas.

Se precisar de outros cálculos, clique com o botão direito do mouse sobre qualquer um
desses valores e veja quantas outras opções você pode obter.

Veja também:
Excel Básico ao Intermediário Versão 8.0

 Selecionar a aba “Turno 1” > Pressionar a tecla SHIFT > Clicar com o botão esquerdo na
aba “Turno 3”;
 Digitar na célula G6: =SOMA(C6:F6);
 Repetir o mesmo procedimento para as outras três linhas abaixo, que representam os
produtos: parafuso, arruela e grampo.

O “Resumo da Produção” dos três turnos é simplesmente a somatória de todos os itens comuns.
Por exemplo, o “Resumo da Produção” de pregos na Linha 1 é 1.000 + 1.200 + 700 = 2.900.
Para fazer essa soma com mais facilidade, utilizaremos um recurso chamado Referência 3D, que
vai fazer justamente essa soma de cada item na tabela “Resumo da Produção”.

Assim como criamos tabelas iguais em várias planilhas e utilizamos a função SOMA,
podemos utilizar quaisquer outras opções de formatação, funções e operações
matemáticas com o auxílio do SHIFT. Experimente depois utilizar as funções MÉDIA,
DESVPAD.A e outras.
Veja também:
Excel Básico ao Intermediário Versão 8.0

 Selecione a célula C6 e digite “=SOMA(“ > vá à planilha “Turno 1” e selecione a célula C6
> Com a tecla Shift pressionada, selecione a planilha “Turno 3” e tecle ENTER. Verifique
se sua fórmula ficou igual à da figura abaixo:

 Para finalizar, basta copiar a fórmula até a célula G9.

Utilizando os hiperlinks para navegação ente as abas da planilha, vá até a aba de gráficos e veja
se os resultados em seu Dashboard de gestão possuem os seguintes resultados:

Excel Básico ao Intermediário Versão 8.0

2.7 Autopreenchimento
Este é um recurso que agiliza muito o preenchimento de células que têm algum tipo de
sequência. Vamos entendê-lo praticando:
Selecionar a aba “Resumo da Produção” > Pressionar Shift > Selecionar a aba “Turno 3”. Em
seguida, digitar o valor de 95% na célula H6. Esse valor nos diz que apenas 95% das peças está
pronta para ser utilizada. Sendo assim, ocorre uma perda de 5% da produção.
Aproxime o mouse do canto da célula H6 até aparecer uma cruz preta:

Assim que aparecer a “cruz preta”, use o botão esquerdo para arrastar até embaixo. O resultado
será esse:

Você acaba de realizar o autopreenchimento, colocando o valor de “95%” em todas as outras
células. Esse recurso pode ser usado para autopreencher fórmulas, datas, tendências e muitas
outras variantes. Clicando na estrutura destacada, abrirão opções para a forma de
preenchimento.

Quando trabalhamos com a função SOMA entre várias abas com o auxílio do SHIFT,
muitos alunos antes finalizar o cálculo, retornam à aba Resumo da Produção. Com isso,
a referência da célula começa a ter como base essa aba. Portanto, execute toda a
operação com as abas dos TURNOS selecionadas e aperte ENTER. Ao fazer isso, o Excel
já retorna automaticamente para a aba Resumo da Produção.

Principais Erros:
Excel Básico ao Intermediário Versão 8.0

Se você quiser poderá realizar o clique-arraste com o botão direito, podendo utilizar outras
opções de autopreenchimento. Quando o botão direito é acionado a imagem fica como a que
segue:

Quando trabalhamos com planilhas que possuem grande quantidade de linhas, realizar
o clique arraste de células se torna um pouco mais demorado, principalmente quando
estamos utilizando o touchpad do notebook.
Para isso, podemos aproximar o mouse do canto inferior direito da célula e quando
aparecer a “cruz preta” e realizar o duplo clique. Realizando essa operação, o Excel irá
aplicar o autopreenchimento até a última célula do intervalo.
Dica Voitto:
Excel Básico ao Intermediário Versão 8.0

Agora que aprendemos as opções de autopreenchimento, vamos para aplicar esse recurso.
Vejamos a aplicação para a coluna “Quantidade Útil”:

A “Quantidade Útil” é dada multiplicando-se o “Total Produzido” pelo “% Aproveitável”. Para
fazemos a operação de multiplicação, devemos:
 Digitar o operador “=”;
 Selecionar a célula G6;
 Digitar “*”;
 Selecionar a célula H6;
 Apertar tecla ENTER;
 Finalizar com o autopreenchimento para as demais células.

2.8 Gabarito
Na aba “Resumo de Produção” os valores das células são conforme a imagem a seguir:

Quando utilizamos o duplo clique para autopreenchimento, caso haja uma célula vazia
no meio do intervalo o Excel para o recurso, conforme já vimos no módulo anterior.
Outro problema é quando queremos manter um valor fixo de referência e aplicar este
às outras células. A seguir veremos a solução para o segundo caso.
Principais Erros:
Excel Básico ao Intermediário Versão 8.0

2.9 Travando células (F4)
Muitas vezes é necessário realizar cálculos envolvendo uma célula específica, fixando suas linhas
e/ou colunas. Esse recurso é conhecido como travamento de células. O Excel faz referência às
células, ou seja, a célula C4 está uma coluna à direita e duas linhas abaixo de B2.

Analisando o exemplo “=B4+C4”, se realizarmos o autopreenchimento para uma linha abaixo,
teremos “=B5+C5”, pois haverá o deslocamento de uma linha para baixo nas colunas B e C.

No exemplo acima foi interessante aplicar a mesma operação de soma, porém em alguns casos
precisamos utilizar a mesma célula em várias operações ou realizar um autopreenchimento com
deslocamento apenas de linhas ou colunas.
Para utilizar travamento, utilizamos o “$”. No caso da célula B4, sabe-se que a coluna é
representada
por B e a linha por 4. Dessa forma temos as seguintes opções:
 Quando queremos travar ambos, escrevemos: $B$4;
 Para travar apenas linhas: B$4;
 Para travar apenas colunas: $B4.

Produtos/Produção Linha 1 Linha 2 Linha 3
Prego =SOMA('Turno 1:Turno 3'!C6) =SOMA('Turno 1:Turno 3'!D6) =SOMA('Turno 1:Turno 3'!E6)
Parafuso =SOMA('Turno 1:Turno 3'!C7) =SOMA('Turno 1:Turno 3'!D7) =SOMA('Turno 1:Turno 3'!E7)
Arruela =SOMA('Turno 1:Turno 3'!C8) =SOMA('Turno 1:Turno 3'!D8) =SOMA('Turno 1:Turno 3'!E8)
Grampo =SOMA('Turno 1:Turno 3'!C9) =SOMA('Turno 1:Turno 3'!D9) =SOMA('Turno 1:Turno 3'!E9)
Linha 4 Total Produzido % Aproveitável Quantidade útil
=SOMA('Turno 1:Turno 3'!F6) =SOMA(C6:F6) 0,95 =G6*H6
=SOMA('Turno 1:Turno 3'!F7) =SOMA(C7:F7) 0,95 =G7*H7
=SOMA('Turno 1:Turno 3'!F8) =SOMA(C8:F8) 0,95 =G8*H8
=SOMA('Turno 1:Turno 3'!F9) =SOMA(C9:F9) 0,95 =G9*H9
Excel Básico ao Intermediário Versão 8.0

Nicolas trabalha na empresa Voitto Atacadista. Essa empresa trabalha com um estoque de 500
diferentes produtos e por operar nos mercados americano e europeu, necessita saber de forma
rotineira o valor de suas mercadorias nessas outras moedas também.
Para realizar a conversão das moedas, basta dividir os campos da coluna “Valor em Reais” pela
respectiva cotação. Vamos selecionar a célula D6 > digitar a fórmula =C6/H7 > Apertar ENTER.

Como são muitas linhas, a melhor opção é fazer o autopreenchimento, porém ao realizar essa
operação, notamos o seguinte erro:

Para não cometer erros, você pode gravar o seguinte:
 Evitar o deslocamento horizontal de referência (Colunas) > coloque o “$” antes
da letra da célula ($C4);
 Evitar o deslocamento vertical de referência (Linhas) > coloque o “$” antes do
número da célula (C$4).
A tecla F4 permite navegar entre as opções, quando a tecla é apertada em sequência.
Para isso, basta selecionar a célula de referência e utilizar o atalho.
Dica Voitto:
Exemplo 2.2 – Preço do Estoque
Excel Básico ao Intermediário Versão 8.0

O erro ocorrido é muito comum quando trabalhamos com o autopreenchimento. Podemos ver
que para o valor funcionou perfeitamente, porém os demais valores tiveram um erro de divisão
por zero, pois é matematicamente impossível.
Vamos ver como foi feito o cálculo de uma célula que apresentou o erro #DIV/0!

Conforme explicamos anteriormente, o autopreenchimento sempre segue a sequência lógica
das funções e referências aplicadas às células.
Portanto, a célula D7 utilizou como numerador C7, porém o denominador foi a H8, que está
vazia. O desejável era que continuássemos com a H7.
Para corrigir esse erro, podemos selecionar o H7 na fórmula e apertar a Tecla F4, obtendo o
seguinte resultado:

Podemos perceber o aparecimento de cifrões do lado da letra (coluna) e número (linha). Esse
cifrão indica que esses itens estão fixados. Caso você use o autopreenchimento, essa referência
ficará fixa. Arraste por toda a coluna e perceba que apenas mudam os valores referentes à
coluna “Valores em Reais”, ao passo que a cotação do dólar permanece fixa.
Excel Básico ao Intermediário Versão 8.0

Conforme podemos ver, os cifrões estão inseridos antes da letra $H (coluna) e $7 número (linha),
representando o congelamento da célula. Portanto, em nossa fórmula, os únicos valores que
variaram foram os “valores em reais”, permanecendo o mesmo valor da célula “Dólar”.

Trabalhando melhor esse conceito, podemos perceber que congelar totalmente a célula
H7 não é a melhor das opções, pois podemos utilizar o autopreenchimento horizontal para
a coluna “Valor em Euro”. Sendo assim, podemos fazer o congelamento apenas da linha,
resultando em: H$7.
Para completar o congelamento das nossas referências, devemos congelar os valores da
coluna “Valor em Reais”, pois ao fazer o autopreenchimento lateral para a coluna “Valor
em Euro”, ocorrerá o deslocamento do numerador.
C4
Veja também:
Excel Básico ao Intermediário Versão 8.0

2.10 Gabarito
Na aba “Preço do Estoque” os valores das células serão conforme a imagem a seguir:
Matéria Prima Valor em Reais Valor em Dólar Valor em Euro
Produto 001 39047,8047076976 =$C6/H$7 =$C6/I$7
Produto 002 23070,9359940573 =C7/$H$7 =$C7/I$7
Produto 003 23941,0074923978 =C8/$H$7 =$C8/I$7
Produto 004 14884,4038770002 =C9/$H$7 =$C9/I$7
Produto 005 9097,42548950455 =C10/$H$7 =$C10/I$7
Produto 006 13854,3404763369 =C11/$H$7 =$C11/I$7

2.11 Resumo do Módulo 1
 Nomeação de Planilhas e definição de cor;
 Utilização do SHIFT para seleção múltipla de planilhas para criação de planilhas, utilização
de formatações e funções. Utilização da função SOMA + SHIFT.

Portanto, iremos congelar o movimento horizontal dos valores da coluna “Valor em
Reais”, permitindo apenas o autopreenchimento vertical, resultando em: $C6.
Veja a configuração final e resultados obtidos após autopreenchimento:

Veja também:
Excel Básico ao Intermediário Versão 8.0

 Hiperlink (CTRL + K);
 Utilização do autopreenchimento e travamento de células (tecla F4).
2.12 Gabarito do Exercício Extra:
Na aba “Relatório Geral”, os resultados obtidos com Receita, Despesa, Saldo e Lucro totais com
as fábricas foram por meio das seguintes fórmulas:

Mês Receita Despesa
Janeiro =SOMA('Fábrica ES:Fábrica SP'!C6) =SOMA('Fábrica ES:Fábrica SP'!D6)
Fevereiro =SOMA('Fábrica ES:Fábrica SP'!C7) =SOMA('Fábrica ES:Fábrica SP'!D7)
Março =SOMA('Fábrica ES:Fábrica SP'!C8) =SOMA('Fábrica ES:Fábrica SP'!D8)
Abril =SOMA('Fábrica ES:Fábrica SP'!C9) =SOMA('Fábrica ES:Fábrica SP'!D9)
Maio =SOMA('Fábrica ES:Fábrica SP'!C10) =SOMA('Fábrica ES:Fábrica SP'!D10)
Junho =SOMA('Fábrica ES:Fábrica SP'!C11) =SOMA('Fábrica ES:Fábrica SP'!D11)
Julho =SOMA('Fábrica ES:Fábrica SP'!C12) =SOMA('Fábrica ES:Fábrica SP'!D12)
Agosto =SOMA('Fábrica ES:Fábrica SP'!C13) =SOMA('Fábrica ES:Fábrica SP'!D13)
Setembro =SOMA('Fábrica ES:Fábrica SP'!C14) =SOMA('Fábrica ES:Fábrica SP'!D14)
Outubro =SOMA('Fábrica ES:Fábrica SP'!C15) =SOMA('Fábrica ES:Fábrica SP'!D15)
Novembro =SOMA('Fábrica ES:Fábrica SP'!C16) =SOMA('Fábrica ES:Fábrica SP'!D16)
Dezembro =SOMA('Fábrica ES:Fábrica SP'!C17) =SOMA('Fábrica ES:Fábrica SP'!D17)
Mês Saldo Imposto Lucro
Janeiro =SOMA('Fábrica ES:Fábrica SP'!E6) 0,15 =E6-(E6*$F$6)
Fevereiro =SOMA('Fábrica ES:Fábrica SP'!E7) =E7-(E7*$F$6)
Março =SOMA('Fábrica ES:Fábrica SP'!E8) =E8-(E8*$F$6)
Abril =SOMA('Fábrica ES:Fábrica SP'!E9) =E9-(E9*$F$6)
Maio =SOMA('Fábrica ES:Fábrica SP'!E10) =E10-(E10*$F$6)
Junho =SOMA('Fábrica ES:Fábrica SP'!E11) =E11-(E11*$F$6)
Julho =SOMA('Fábrica ES:Fábrica SP'!E12) =E12-(E12*$F$6)
Agosto =SOMA('Fábrica ES:Fábrica SP'!E13) =E13-(E13*$F$6)
Setembro =SOMA('Fábrica ES:Fábrica SP'!E14) =E14-(E14*$F$6)
Outubro =SOMA('Fábrica ES:Fábrica SP'!E15) =E15-(E15*$F$6)
Novembro =SOMA('Fábrica ES:Fábrica SP'!E16) =E16-(E16*$F$6)
Dezembro =SOMA('Fábrica ES:Fábrica SP'!E17) =E17-(E17*$F$6)
=SOMA('Fábrica ES:Fábrica SP'!E18) =E18-(E18*$F$6)Total
Excel Básico ao Intermediário Versão 8.0

Na aba “Fábrica ES” as fórmulas utilizadas foram:

As fórmulas utilizadas nas demais abas são as mesmas da tabela acima.
Mês Receita Despesa
Janeiro 3.787,11R$ 247,44R$
Fevereiro 6.295,89R$ 486,81R$
Março 4.510,26R$ 363,00R$
Abril 6.503,50R$ 320,12R$
Maio 8.917,75R$ 109,48R$
Junho 3.837,30R$ 424,75R$
Julho 2.535,41R$ 381,04R$
Agosto 7.978,90R$ 979,15R$
Setembro 4.901,65R$ 146,55R$
Outubro 7.595,61R$ 742,16R$
Novembro 5.694,82R$ 401,50R$
Dezembro 8.405,99R$ 497,96R$
Total
Saldo
=C6-D6
=C7-D7
=C8-D8
=C9-D9
=C10-D10
=C11-D11
=C12-D12
=C13-D13
=C14-D14
=C15-D15
=C16-D16
=C17-D17
=SOMA(E6:E17)
BásicoIntermediárioExcel8.0/Basico_Intermediario/Módulo 3 - Organização de Dados.pdf
Excel Básico ao Intermediário Versão 8.0

Módulo 3 - Organização de Dados

Excel Básico ao Intermediário Versão 8.0

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

Módulo 3 – Organização de Dados
http://www.voitto.com.br/
Excel Básico ao Intermediário Versão 8.0

Neste módulo trabalharemos com o tratamento e organização dos dados.
3.1 Congelar Painéis
No Excel, é comum trabalharmos com planilhas muito grandes e constantemente precisamos
rolar a janela para baixo a ponto de não visualizar cabeçalho. Para solucionar esse problema,
existe um recurso bem simples chamado Congelar Painéis, que veremos a seguir.
Quando trabalhamos com base de dados, por exemplo: cadastro de clientes, registro de vendas,
controle de estoque e outros é quase que obrigatório esse recurso, pois ele auxilia a
identificação das informações do cabeçalho e evita erros de referência.

Uma pequena loja, cujo negócio é vender artigos para uma empresa de peças, pretende analisar
suas vendas. Para isso, os dados foram compilados em um registro de vendas com as principais
informações que o gerente julgou importante.
Para utilizarmos esse recurso: Selecione a célula C5 > clique na Guia Exibição > Congelar Painéis
> Congelar Painéis:

Podemos agora navegar pelos dados da planilha e notar que o cabeçalho continua fixo,
facilitando a identificação dos dados contidos na tabela.

Exemplo 3 – Venda Artigos (Aba 1)
Excel Básico ao Intermediário Versão 8.0

3.2 Classificação em Níveis
Muitas vezes recebemos uma base de dados na qual é necessário fazer uma ordenação dos
dados. Essa ordenação pode ser alfabética, numérica (crescente ou decrescente), por data e
outros. O Excel nos permite fazer uma classificação em vários níveis, sendo que a classificação
ocorre do primeiro para o último nível.

Trabalhando ainda exemplo “Venda Artigos”, iremos classificar nossa base de dados. Para isso:
 Selecione a Guia Dados > Classificar;

No item congelar painéis, podemos ver outras duas opções: Congelar linha superior e
congelar primeira coluna (congelam apenas a primeira linha e a primeira coluna,
respectivamente). No caso da primeira opção, congela todas as linhas e colunas acima e
à esquerda da célula selecionada.
Caso queira voltar ao normal, na guia Exibir marque a opção Congelar Painéis >
Descongelar Painéis.
Dica Voitto:

A classificação de níveis é muito utilizada para organizar a sua base de dados. Logo, é
interessante utilizá-la para “agrupar” os seus clientes, ordenar os valores de
faturamento ou quantidade vendida.
Dica Voitto:
Exemplo 3 – Venda Artigos (Aba 1)
Excel Básico ao Intermediário Versão 8.0

 No item Classificar por escolha a opção “Produto”;
 Clique em Adicionar Nível;
 Em seguida, na opção E depois por selecione “Quantidade”, Ordem “Do Menor para o
Maior” e clique em OK.

Você verá que os dados selecionados foram ordenados de forma crescente com base em
“Produto” como critério principal e em “Quantidade” como critério secundário, como mostrado
abaixo.

3.3 Autofiltro (Ctrl + Shift + L)
Filtrar é um meio fácil de localizar e trabalhar com um subconjunto de dados de uma lista. Ao
utilizar o filtro, o Excel exibe apenas as linhas que contêm um determinado valor ou que
atendam a determinadas condições de pesquisa denominadas “critérios”. O autofiltro serve
para filtrar uma lista rapidamente, ajustando o conteúdo de uma célula ou usando critérios
simples de comparação.

Excel Básico ao Intermediário Versão 8.0

No exemplo “Vendas Artigos”, o dono da loja pretende verificar o desempenho dos vendedores.
Além de entender melhor o processo das vendas, ele pretende estipular uma comissão com base
no valor total vendido.
Para criar o “Autofiltro” selecione qualquer célula dentro da tabela que se deseja trabalhar, vá
em Guia Dados > Filtro:

Observe que, ao clicar no botão, apareceram algumas setas ao lado do título de cada coluna.

Clique na seta da coluna “Vendedor” e veja as opções de filtragem. Selecione, por exemplo,
apenas o Vendedor “André Saraiva”.

Quando utilizamos o filtro, deve ficar claro que os dados não mudam de ordem. Ocorre
apenas uma visualização diferente do “total”. Sendo assim, ao coletar informações
específicas de, por exemplo, um cliente, faça o filtro e depois utilize o Ctrl+C e cole
“valores” em outro espaço. Fazendo isso, você garante que não venham informações
a mais, além de fórmulas e referências da tabela filtrada.

Dica Voitto:
Exemplo 3 – Venda Artigos (Aba 1)
Excel Básico ao Intermediário Versão 8.0

Dê OK e repare que em nossa tabela, aparecerão apenas as vendas do vendedor selecionado.
Navegue pelas setas de filtragem e veja as opções de filtragem já existentes no Excel.
Para desfazer os filtros, basta seguir o mesmo caminho para a criação destes: Dados > Filtro ou
clicar em Limpar.

Os filtros aparecem de acordo com os dados na base e cada coluna apresenta uma
característica diferente. No entanto, você pode recorrer a filtros que não são
padronizados no software. Esse recurso está disponível em Filtros de Texto, Filtros de
Número ou qualquer classe esteja trabalhando.

Veja também:
Excel Básico ao Intermediário Versão 8.0

3.4 Validação de Dados
Ao trabalhar com planilhas, é comum cometermos erros de digitação ou mesmo a inserção de
um dado não compatível pelo usuário. Para minimizar esses erros, o Excel possui uma
ferramenta chamada “Validação de Dados”. Como desenvolvedor é possível impor algumas
regras de digitação e ainda enviar mensagens de alerta sobre o erro cometido pelo usuário.

Uma empresa que trabalha com a venda de vários tipos de produtos deseja fazer o controle de
estoques para saber quanto possui de cada produto em seu estoque. No exemplo abaixo, a
primeira tabela demonstra o controle de entradas e saídas e a segunda tabela o estoque total
da empresa. De acordo com as movimentações, o estoque é atualizado.
Para garantir que o controle seja realizado, a inserção de produtos, tipo de movimentação e
data da operação devem seguir algumas regras. Os produtos devem ser os mesmos contidos na
tabela de estoque total, a movimentação só pode ser entrada ou saída e a data deve ser maior
ou igual à hoje.

O principal erro na Classificação de Dados e Filtros ocorre quando é feita a seleção de um
determinado conjunto de células de forma não proposital. Quando isso acontece, o Excel
entende que o filtro e/ou classificação devem ser aplicados apenas naquele intervalo.
Portanto, se for aplicar na tabela toda, selecione qualquer célula dentro da
tabela que o
Excel já entende que o recurso será aplicado na tabela toda.
Principais Erros:

Se você deseja restringir o preenchimento a uma lista de produtos, procedimentos,
meses e outros, pode ser criada uma lista com esses critérios em algum espaço da
planilha para ser utilizada como referência para a validação de dados. Isso ficará mais
claro no exemplo a seguir.
Dica Voitto:
Exemplo 3 – Controle de Estoque – Aba 2
Excel Básico ao Intermediário Versão 8.0

Vamos inserir no campo de produto (B6:B23) uma validação de dados do tipo lista com os
produtos no estoque total (H7:H15). Para isso, selecione o intervalo (B6:B23) > selecione a Guia
Dados > Validação de Dados > Validação de Dados;

Veja que abriu a caixa de diálogo Validação de Dados na guia Configurações, cujos critérios
definiremos:

A configuração acima permite que no intervalo selecionado B6:B23 sejam inseridos apenas os
argumentos contidos na lista H7:H15. Sendo assim, nosso controle de entradas e saídas garante
que só serão inseridos produtos contidos no estoque.

Excel Básico ao Intermediário Versão 8.0

Para inserir o tipo de movimentação, trabalharemos com apenas dois tipos: Entradas, que
aumentam o estoque total, e Saídas, que reduzem o estoque total. Para inserir esses dois
argumentos, vamos selecionar o intervalo C6:C23 e clicar novamente na Guia Dados > Validação
de Dados > Validação de Dados. Em seguida, vamos definir a seguinte configuração:

Perceba que a opção de lista pode ser inserida a partir de uma lista já elaborada, semelhante à
o que fizemos anteriormente ou pode ser redigida, com os argumentos separados por ponto e
vírgula.

Como estamos trabalhando com produtos por unidade, não é possível retirar ou adicionar ao
nosso estoque partes fracionadas de produto, por exemplo, 0,5 Produto 1. Outra restrição é a
movimentação de produtos negativos, por exemplo, -1 Produto 1. Sendo assim, colocaremos
uma validação de dados que garanta a inserção apenas de valores inteiros e positivos.
Selecionaremos o intervalo D6:D23 e seguiremos o mesmo procedimento, porém as
configurações serão:

Excel Básico ao Intermediário Versão 8.0

Perceba que ao tentar inserir valores fracionados, a validação de dados não permite.

Essa mensagem de erro pode ser personalizada. Vamos criar o nosso próprio alerta de erro:
 Selecionar o intervalo (D6:D23) > Dados > Validação de Dados > Validação de Dados;
 Selecionar a aba “Alerta de Erro” e preencher os campos com os seguintes dados:

No campo “Estilo” existem três opções. Para o nosso caso, utilizaremos a opção “Parar”. Essa
condição não permite que sejam inseridos valores que não atendam a validação de dados
inserida.

Os outros dois tipos “Aviso” e “Informações” alertam sobre a não conformidade, porém
permitem a inserção dos dados.

Veja também:
Excel Básico ao Intermediário Versão 8.0

Para finalizar, a data da movimentação deve ser igual ou inferior a hoje, não podendo registrar
uma movimentação futura. Selecionaremos agora o intervalo F6:F23 e aplicaremos a seguinte
validação:

Para orientar os usuários, podemos inserir um alerta que auxilia no preenchimento. Ainda na
janela demonstrada acima, utilizaremos a aba “Mensagem de Entrada”.

Ao clicar em qualquer célula do intervalo selecionado, aparecerá o aviso para o preenchimento
correto:

Excel Básico ao Intermediário Versão 8.0

Agora vamos inserir alguns valores para ver o funcionamento do nosso controle de estoque e
olhar a variação do estoque final e valor em estoque.

3.5 Proteger Planilhas
A proteção de planilha é usada basicamente para proteger determinadas partes do arquivo
contra remoções ou mudanças acidentais das fórmulas inseridas. Essa proteção torna-se
bastante útil, uma vez que, em alguns casos, podemos demorar horas produzindo uma fórmula
e esta pode ser removida acidentalmente a qualquer momento, principalmente se a planilha for
manuseada por mais de uma pessoa.
Para proteger a planilha, devemos primeiro decidir se queremos proteger a planilha inteira, ou
parte dela. O recurso de proteção permite que se proteja parte da planilha e que se liberem
outras partes, tais como áreas onde são lançados dados que precisam ser “acrescentados”
diariamente a um banco de dados, preservando-se as fórmulas, formatação, inclusão, exclusão
de linhas e colunas e etc.

A validação de dados não permite que sejam inseridos dados que não esteja nos critérios
definidos. No entanto, existem duas formas de a validação de dados não funcionar. A
primeira é quando você copia o conteúdo e uma célula e cola no intervalo da validação
de dados. A segunda é quando você cria a validação de dados em um intervalo já
preenchido. Logo, o dado permanecerá, mesmo que incorreto.
Principais Erros:

Muitos acreditam que a proteção de planilha com uso de uma senha resguarda
informações sigilosas presentes. No entanto, pessoas que possuem conhecimento um
pouco mais avançado podem desbloquear a planilha sem possuir a senha.
Isso torna-se possível com o uso do Visual Basic for Applications (apresentado no
módulo 6). Para quem já tiver conhecimento, basta pesquisar no Google por: “Macro
para desbloqueio de planilha” > copiar no ambiente VBA > executar.
Dica Voitto:
Excel Básico ao Intermediário Versão 8.0

A empresa “Guloseimas” fabrica 20 tipos de biscoitos diariamente e precisa registrar e
acompanhar os custos e a produção. Em uma simples planilha é anotado, pelo pessoal do chão
de fábrica, a quantidade de biscoitos fabricados por dia para que se possa levantar corretamente
os custos dos mesmos.
Todas as células do Excel são bloqueadas por padrão. Logo, devemos selecionar o intervalo de
células o qual não desejamos proteger, que nesse caso é apenas a quantidade produzida, pois,
este campo deverá ser preenchido frequentemente pelo chão de fábrica. Neste caso, os outros
campos ficam inalteráveis, pois são dados fixos da empresa ou dados calculados.
 Selecione o intervalo C6:C25;
 Clique com o botão direito do mouse sobre a seleção e escolha a opção Formatar
Células;
 Acesse a Guia Proteção e desmarque a opção Bloqueadas, clicando em OK em seguida.

3.5.1 Protegendo a Planilha Inteira
Na guia Revisão selecione a opção Proteger Planilha:

Vamos colocar uma senha de fácil memorização para aprender o conceito. Na janela a seguir,
digite a senha 1234:
Exemplo 3 – Rateio – Aba 3
Excel Básico ao Intermediário Versão 8.0

Você pode simplesmente proteger a planilha, sem a utilização de senha. Veja que você pode
editar o que será permitido que o usuário faça com as células bloqueadas. Em nosso caso,
permitiremos que o usuário apenas selecione as células.
Portanto, clique em “OK”, redigite a mesma senha e clique em “OK” novamente. Faça um teste
agora tentando editar as células que não foram desbloqueadas e a células que foram
desbloqueadas. Desproteja a planilha para que possamos continuar nosso trabalho!

3.5.2 Protegendo uma planilha de modo que usuários específicos possam alterar campos
específicos
Outro recurso bastante interessante é o de bloquear a planilha e permitir que apenas alguns
usuários possam editar certos intervalos, ou seja, é como se criássemos usuários para um
arquivo com senhas diferentes.

Hoje em dia, grande parte das empresas possuem um sistema intranet, em que vários
usuários acessam o mesmo arquivo. Em alguns casos, nem todas os departamentos
devem possuir acesso à todas as informações e ainda
poder editar essas. Portanto, este
tipo de recurso pode ser utilizado para criar uma senha para cada departamento
(financeiro, comercial, recursos humanos e outros).
Dica Voitto:
Excel Básico ao Intermediário Versão 8.0

Consideremos que a coluna “Preço de Venda” possa ser editada apenas pelo escritório de
vendas. Para isso criaremos uma senha de edição que será disponibilizada apenas para esse
escritório.
Ainda na guia Revisão clique no botão Permitir a Edição de Intervalos;

Na tela a seguir, clique em Novo e preencha os campos conforme a figura abaixo, colocando a
senha 123:

Clique em OK, redigite a senha e OK novamente. Veja que a permissão foi criada:

Exemplo 3 – Rateio (Aba 3)
Excel Básico ao Intermediário Versão 8.0

Clique em Proteger planilha e coloque a senha 1234 e finalize a proteção.
Agora, tente modificar qualquer célula da coluna “Preço de Venda”. Percebeu a diferença?
Agora o Excel permite que o usuário modifique a célula apenas com a senha criada para o
intervalo.

3.5.3 Protegendo uma pasta de trabalho
Você pode também proteger toda sua pasta de trabalho. Ao proteger uma pasta de trabalho,
você estará bloqueando a estrutura da pasta, de modo que as planilhas não possam ser
excluídas, movidas, ocultas, reexibidas, renomeadas e novas planilhas não possam ser incluídas.
Você pode também impedir que as janelas de uma pasta de trabalho sejam movidas,
redimensionadas, reexibidas ou fechadas.
Selecione a guia Revisão > Proteger Pasta de Trabalho;

Coloque a senha 1234 novamente e dê OK. Agora, tente modificar a estrutura da pasta de
trabalho, criando uma nova planilha por exemplo.

Desproteja nossa pasta de trabalho, clicando em “Proteger Pasta de Trabalho” e desmarcando
a opção “Proteger Estrutura e Janelas”.
Excel Básico ao Intermediário Versão 8.0

3.5.4 Protegendo um arquivo com senha
Ainda temos uma última opção de proteção, que é proteger o arquivo com senha. Esse é o
formato de proteção mais seguro, o qual limita o acesso ao arquivo. Vejamos:
 Selecione o menu Arquivo > Informações > Proteger Pasta de Trabalho > Criptografar
com Senha.

 Criptografar o conteúdo com a senha 123.
Dê um OK confirmando as senhas e salve o arquivo por cima do arquivo existente. Feche o
arquivo e tente abri-lo novamente. Agora, o Excel pede senha para abrir o arquivo.
Para retirar a senha, basta ir ao mesmo lugar, apagar a senha e salvar novamente por cima do
arquivo existente.

3.6 Consolidar Dados
Para resumir e relatar os resultados de planilhas separadas, você pode consolidar dados de cada
planilha separada em uma planilha principal. As planilhas podem estar na mesma pasta de
trabalho que a planilha principal ou em outras pastas de trabalho. Ao consolidar dados, você
está montando dados para que possa atualizá-los e agregá-los com mais facilidade.

Lembre-se de que para utilizar este recurso, os dados a serem consolidados devem estar
em matrizes de mesmo tamanho e na mesma posição.
Dica Voitto:
Excel Básico ao Intermediário Versão 8.0

Abaixo temos o mesmo caso da Voitto Metais que vimos no Módulo 2, porém utilizaremos um
novo recurso para fazer o somatório das produções das peças.
 Selecione a célula C6;

 Na Guia Dados, clique no ícone consolidar. Veja a tela que vai aparecer:

 Em Referência, selecionaremos os dados a serem consolidados, que são os valores
contidos nas planilhas “Turno 1”, “Turno 2” e “Turno 3”.
 Selecione cada intervalo (um por um), sempre clicando em Adicionar, até que todos os
três estejam no campo Todas as referências. Marque também a opção de Criar Vínculo
com os Dados de Origem.
Exemplo 3 – Resumo da Produção (Aba 4)
Excel Básico ao Intermediário Versão 8.0

 Clique em OK e veja o que vai acontecer.

Todos os valores foram somados (consolidados) na tabela “Resumo da Produção”. Veja agora
que interessante, apesar de parecer o mesmo resultado do nosso exemplo com “Referência 3D”,
existe um diferencial em consolidar os dados.
Repare que no lado direito do nome das linhas, apareceram 4 símbolos de “+”. Clique em um
deles para ver o que acontece.

Excel Básico ao Intermediário Versão 8.0

Veja que ao clicar, os dados que estão consolidados aparecem na planilha principal. Essa é a
grande vantagem de consolidar os dados, pois agora você pode exibir ou ocultar sempre que
quiser os dados detalhados ou consolidados.

3.7 Subtotais
Existe uma maneira muito prática de organizar planilhas muito extensas e gerar informações
dentro do Excel: é o comando Subtotais, encontrado na guia Dados. Ele organiza as planilhas por
blocos de informação, estes podem ser exibidos ou não. Quando ocultos, a planilha fica
simplificada e de fácil leitura. Quando exibidos, permitem observar os dados com mais detalhes.

Caso deseje apenas realizar a consolidação dos dados sem a visualização dos símbolos
“+”, basta desmarcar a opção criar vínculos com os dados de origem. Sendo assim, o
resultado será bem parecido com o que vimos no módulo 2.

Veja também:

Por tratar-se de uma consolidação de dados da tabela toda, muitas pessoas selecionam
todo o intervalo, porém só é necessária a primeira célula. Outro erro é tentar trabalhar
com tabelas de tamanhos diferentes e em posições diferentes.

Principais Erros:

Ao utilizar o recurso de subtotais, os dados que você deseja calcular devem estar
ordenados, em outras palavras, devem estar classificados (recurso aprendido neste
módulo). Portanto, primeiro passo é verificar se os dados estão classificados e
agrupados para depois utilizar os subtotais.
Dica Voitto:
Excel Básico ao Intermediário Versão 8.0

A empresa “Pneumáticos Ltda.” possui operações em todas as regiões do Brasil. O gerente de
finanças deseja efetuar uma análise nos relatórios de vendas por região e por mês, mas essa
informação tem que ser entregue em menos de 10 minutos. Vamos agir a fim de obter esses
resultados de maneira ágil.
Temos aqui alguns dados referentes à venda de pneus tais como, região, mês e valor da venda.
Não é difícil de perceber que podemos juntar nossos dados em dois tipos de grupos: o grupo
“Região” e o grupo “Mês”.
Primeiramente, vamos fazer os agrupamentos por região:
 Assim como explicado anteriormente, pode ser selecionada apenas uma célula dentro
do intervalo que o Excel já entende que o recurso será aplicado na tabela toda;
 Em seguida, selecione a guia Dados > Estrutura de Tópicos > Subtotal e preencha os
campos conforme imagem abaixo:

Observe que vamos adicionar o subtotal em Vendas e separada por Região.

Exemplo 3 – Venda de Pneus (Aba 8)
Excel Básico ao Intermediário Versão 8.0

Um erro muito comum é o não agrupamento da categoria que se deseja calcular os
subtotais. Com os dados desorganizados, são calculados vários subtotais, pois este
recurso faz a soma de termos até aparecer um item diferente abaixo, ou seja, sem
agrupamento possuem diversos itens diferentes.
Outro principal erro é quando é feita a seleção incorreta da tabela, não aparecendo os
itens para o cálculo do subtotal. Portanto, sempre selecione apenas uma célula
qualquer na tabela que o próprio software irá expandir o intervalo de seleção.
Principais Erros:

Vimos acima o cálculo de subtotais por região. Agora, vamos testar os subtotais por mês.
Para isso, temos que fazer uma reorganização na base de dados de modo a ter
uma
classificação por mês e não mais por região.
 Para isso, utilize o recurso Classificar na Guia Dados e escolha a coluna mês;
 Repita o procedimento de criar Subtotais;
 Como ficam os resultados com a nova classificação?
Veja também:
Excel Básico ao Intermediário Versão 8.0

3.8 Resumo do Módulo 3
• Congelar Painéis para fixação das informações no cabeçalho de banco de dados;
• Recurso de Classificar para ordenação e agrupamento de dados;
• Filtro para facilitar a localização de dados ou visualização de dados específicos;
• Bloqueio de planilhas;
• Consolidação de dados com a função SOMA (semelhante ao recurso do módulo 2);
• Subtotais para fazer cálculo de grupos de dados. Lembrar de classificar antes.

A empresa Voitto Materiais de Escritório possui três principais produtos que são vendidos
em quantidades consideráveis em suas quatro lojas. O gerente da loja solicitou a você que
seja criada uma restrição ao fornecimento de desconto para um valor máximo de 6%. A
segunda solicitação é apresentar o somatório do total vendido por loja.
l antes de aplicar o recurso. Logo, o Excel entende que é para aplicar o recurso apenas
Desafio Voitto:
BásicoIntermediárioExcel8.0/Basico_Intermediario/Módulo 4 - Funções Básicas do Excel 2.pdf
Excel Básico ao Intermediário Versão 8.0

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

Excel Básico ao Intermediário Versão 8.0

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

http://www.voitto.com.br/
Excel Básico ao Intermediário Versão 8.0

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.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

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

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

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

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

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

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

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

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

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ção SOMASE (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

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

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

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

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

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, muitas
pessoas 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

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

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

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

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

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

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

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

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