apostila-excel-intermediario1606313682

Prévia do material em texto

1 
 
Excel Intermediário – Antonio Branco 
Apostila Excel - Intermediário 
 
Requisito: ter feito o curso Básico de Excel 
 
 
 Formatação condicional 
 Classificar e Filt rar, Personalizar Classificação 
 Comentários nas células 
 M últ iplas Planilhas 
 Validação de Dados 
 Funções: Data e Hora, Se, Cont . Num., Cont .se, Se, Contar.Vazio, Arred, M od, 
M áximo, M ínimo 
 M acros 
 Rast rear Fórmulas 
 Função At ingir M eta 
 Fixar Células 
 
 
 
 
 
 
 
 
 
 
 
2 
 
Excel Intermediário – Antonio Branco 
Para começar a brincadeira, digite a planilha abaixo para ut ilizarmos em 
alguns exemplos. 
 
 
 
:: Formatação Condicional 
 
A formatação simples, todo mundo conhece: você formata uma célula com 
texto vermelho, fonte Arial, tamanho 12… Já a formatação condicional, irá obedecer 
uma condição verdadeira ou falsa. Caso a condição seja verdadeira, a célula irá assumir 
uma formatação “ x” ; caso seja falsa, a célula irá assumir uma formatação “ y” . 
Como exemplo, iremos criar uma legenda de cores, onde determinada cor 
representa vendedores que at ingiram média maior que R$ 10.500,00 e out ra 
determinada cor, vendedores com média menor que R$ 10.500,00. 
 
Relembrando rapidamente a fórmula da M édia. Figura abaixo: 
 
 
 
3 
 
Excel Intermediário – Antonio Branco 
Digitando uma legenda de cores: 
 
 
Agora selecione as células com os valores das M édias e clique em Formatação 
Condicional, Realçar Regras das Células, É M aior do que… 
 
 
 
 
 
4 
 
Excel Intermediário – Antonio Branco 
Você verá uma caixa de diálogo, onde no primeiro campo será digitado o valor 
condicional - no nosso caso R$ 10.500,00. No segundo campo, você define a cor que a 
célula irá assumir ao obedecer a primeira condição. 
 
 
 
Vamos escolher a segunda opção de cor “ Preenchimento Amarelo e Texto 
Amarelo Escuro” , clique OK. 
 
 
 
Pronto, agora o Excel irá realçar todos com média de vendas maior que R$ 
10.500,00. Vamos fazer um teste e alterar o valor da venda de Janeiro do M arcos Paulo 
para R$ 5.000,00. Automat icamente ele vai perder o realce amarelo. 
 
5 
 
Excel Intermediário – Antonio Branco 
 
 
Só para t reinar, vamos colocar a formatação com out ra cor. Selecione os 
valores da M édia, clique Formatação Condicional, Realçar Regras das Células, É M aior 
do que… Preencha o primeiro campo com o nosso valor de R$ 10.500,00, e escolha 
“ Preenchimento Verde e Texto Verde Escuro” . 
 
 
 
Ok, ut ilizamos formatações condicionais pré-definidas do Excel. Vamos agora 
fazer uma formatação condicional personalizada. 
Repita a operação e agora escolha a opção Formato Personalizado. 
 
 
 
6 
 
Excel Intermediário – Antonio Branco 
Teremos uma tela bem familiar no Word. 
 
 
 
Agora vamos personalizar da seguinte forma: o vendedor(a) que t iver média 
menor que R$ 10.000,00, terá o conteúdo da célula em Negrito itálico, cor vermelha, 
preenchimento cinza. Clique OK. Pronto, nós personalizamos a nossa Formatação 
Condicional. 
 
 
Para f inalizar o assunto Formatação Condicional, vamos ver a condição “ É 
menor do que…” . Bem simples de entender, é o cont rário da condicional anterior que 
vimos. Agora vamos realçar quem t iver média menor que R$ 10.500,00. Vamos usar a 
formatação pré configurada do Excel mesmo “Preenchimento Amarelo e Texto 
Amarelo Escuro” . 
7 
 
Excel Intermediário – Antonio Branco 
 
 
 
 
 
Pronto, agora quem t iver a média menor que R$ 10.500,00, o conteúdo da 
célula será realçado em amarelo. Tranquilo não é? 
 
 
 
:: Classificar e Filtrar 
 
 
Podemos colocar agora os dados da nossa planilha em ordem alfabét ica ou 
mesmo ordem crescente de M édia. Vamos primeiro à ordem alfabét ica. Ut ilizando a 
nossa planilha já digitada, clique no nome M aria Flores, Classificar e Filt rar, Classificar 
de A a Z. 
8 
 
Excel Intermediário – Antonio Branco 
 
 
Pronto, teremos os dados da nossa planilha organizados em ordem crescente. 
Perceba que os valores respect ivos de cada funcionário acompanham, o Excel não 
ordena somente os nomes, os valores também são ordenados. 
 
 
 
Detalhe: nós usamos como referência o nome da M aria Flores, ou seja, a 
coluna de nomes dos vendedores. M as podíamos ut il izar qualquer coluna como 
referência. Por exemplo, vamos colocar em ordem crescente de M édia agora. Clica na 
média da Ana Cláudia, R$ 10.375,00, Classificar e Filt rar, Classificar de A a Z. Teremos 
agora a planilha organizada pelo valor da M édia. 
 
 
 
9 
 
Excel Intermediário – Antonio Branco 
Podemos também personalizar este ordenamento. Clica Classificar e Filt rar, 
Personalizar Classif icação. 
 
 
 
Na caixa de diálogo que é exibida, podemos fazer out ras classificações. 
 
 
Não vamos mexer nesta opção Personalizada, foi só para apresentar. 
Cont inuando. 
Opção Filtro . Clica Classif icar e Filt ro. Perceba que agora nas 
colunas da nossa planilha aparecem uns ícones com uma seta para baixo. 
 
 
 
 
10 
 
Excel Intermediário – Antonio Branco 
Clicando na seta de Vendedor(a), teremos a seguinte tela: 
 
 
Vamos desmarcar Ana Cláudia, João Carlos e M aria Crist ina, clica OK. O Excel 
agora só irá exibir Antonio Branco, M arcos Paulo e M aria Flores. Ou seja, o Excel 
Filt rou e exibiu somente as informações selecionadas pelo usuário. 
 
 
 
Para voltar à exibição normal: Clica no funil de Vendedor(a), marca a opção 
Selecionar tudo, OK. Pront o, volta à exibição normal. 
 
11 
 
Excel Intermediário – Antonio Branco 
 
 
Para desat ivar o Filt ro: Clica Classif icar e Filt rar, . 
 
:: Opção Inserir Comentários 
 
Aqui temos uma função bem rápida do Excel e bem út il. Exemplo: vamos 
colocar um comentário na M aria Flores, informando que a funcionária estará ausente 
no próximo mês por mot ivo de saúde. 
Clica com o botão direito na célula da M aria Flores, Inserir comentário. 
 
 
 
Irá ser exibida uma pequena janela para você inserir o seu comentário. Basta 
digitar. 
12 
 
Excel Intermediário – Antonio Branco 
Obs: posicionando o mouse nas bordas, você pode arrastar a posição da 
pequena janela. 
 
 
 
Para alterar o comentário, basta clicar com o botão direito na célula, 
 
Para Excluir o comentário, basta clicar com o botão direito na célula, 
 
 
 
:: M últiplas Planilhas 
 
Como sabemos desde o curso Básico de Excel, há a possibilidade de inserir 
várias planilhas em uma pasta de t rabalho, beleza. M as digamos que você tenha várias 
planilhas que uma depende de um valor “ x” da out ra, ou seja, se você alterar um valor 
da “ planilha 1” , terá que alterar os cálculos da “ planilha 2” , “ planilha 3” , … 
Vamos logo ao exemplo para ficar mais fácil de entender. 
Vamos renomear a nossa planilha para “Principal” e criar uma segunda 
planilha chamada “Comissões” . 
 
 
Na planilha comissões, vamos digitar o seguinte: 
 
13 
 
Excel Intermediário – Antonio Branco 
 
 
E agora sim, vamos ao que interessa: as comissões serão calculadas com base 
na média de vendas de cada vendedor(a). Cada vendedor irá ganhar 15% de comissão 
em relação a sua média de vendas. Lembrando que a M édia está na planilha 
“ Principal” . 
Vamos t ransformar este raciocínio em Fórmula realizando este cálculo 
primeiro para a M aria Crist ina. 
Na planilha Comissões, célula B5, digite a seguinte fórmula, tecle Enter: 
 
 
 Principal: nome da planilha que contém o valor referido; 
 !F5: é a célula da planilha (Principal) que contém o valor referido. Neste caso, a 
média da M aria Crist ina; 
 15% : valor da comissão em porcentagem. 
14 
 
Excel Intermediário – Antonio Branco 
 
Calculamos que a M aria Crist ina tem direito a R$ 1387,50 de comissão. 
 
 
 
Vamos fazer um teste rápido: na planilha Principal, altere o valor da venda de 
Janeiro da M aria Crist ina para R$ 25.000,00. Perceba que automat icamente a Média 
dela irá aumentar e por conseguinte, a comissão também. 
 
 
 
 
15 
 
Excel Intermediário – Antonio Branco 
Agora basta repet ir a fórmula para as outras comissões dos out ros 
vendedores(as) e sempre que a M édia de um vendedor for alterada, automat icamente 
a sua comissão também o será. 
 
 
 
 
:: Validação de Dados 
 
O Excel permite que nós possamos impedir que dados não desejáveis sejam 
inseridos em determinadas células. Vamos ao exemplo: 
 
Em nossa planilha “ Principal” , vamos supor que nenhum funcionário possa 
vender menos que R$ 3.000,00 por mês, caso um valor abaixo de 3000 seja inserido, o 
Excel exibirá uma mensagem de Erro. 
Para não sair da nossa rot ina, vamos novamente ut il izar o valor da venda de 
Janeiro da M aria Crist ina. 
Clica na célula B5, aba Dados, Validação de Dados, Validação de Dados 
novamente. 
 
16 
 
Excel Intermediário – Antonio Branco 
 
 
Na caixa de diálogo que se abre, na opção Permit ir, escolha Decimal e em 
seguida, na opção Dados, escolha “ é maior do que” . 
 
 
 
 
 
Na opção “ M ínimo” , preencha com o valor 3000 e clique OK. 
17 
 
Excel Intermediário – Antonio Branco 
 
 
 
Beleza, agora tente digitar o valor de 2000 para a M aria Crist ina em Janeiro 
(célula B5). O Excel irá exibir uma mensagem de erro. Agora estamos cont rolando os 
dados que ent ram nesta célula, ninguém pode digitar valor inferior a R$ 3000,00 na 
célula B5. 
 
 
 
Vamos repet ir o processo para os out ros vendedores(as) de Janeiro? 
 
Seleciona de B6 a B10, aba Dados, Validação de Dados, Validação de Dados 
novamente. Permit ir, Decimal, é maior do que, M ínimo 3000. 
Pronto, todos os funcionários tem que ter venda de mais de R$ 3000,00 em 
Janeiro. Caso cont rário, pega uma suspensão :) . 
18 
 
Excel Intermediário – Antonio Branco 
:: Funções: Data e Hora, Se, Cont. Num., Cont.se, Contar.Vazio, Arred, M od, M áximo, 
M ínimo 
Vamos falar agora de out ras funções do Excel: 
=Hoje(): retorna a data atual, mas vai atualizando automat icamente conforme 
os dias; 
=M ês(Hoje()): retorna o mês atual; 
 
 
=Ano(Hoje()): retorna o ano atual; 
=Agora(): retorna a data e hora atuais; 
 
 
Vamos ut ilizar a função =Agora() para Personalizar Formatar Células. No 
exemplo fica melhor de entender. 
Eu vou ut ilizar a função =Agora() na minha célula H4, mas qualquer célula 
vazia serve. 
Digite =Agora() na célula H4, tecle Enter, botão direito, Formatar células. 
 
 
19 
 
Excel Intermediário – Antonio Branco 
 
Aba Número, Personalizado. Escolha esta opção de data e hora em destaque 
azul. 
 
 
 Logo acima vamos mexer no t ipo padronizado do Excel. Onde tem “ / ” , vamos 
colocar “ -” e a hora:minuto será ent re “ ( )” . Veja figura abaixo. 
 
 
 
Pronto, personalizamos o formato da função =Agora(). 
 
 
=Arred(): bem intuit ivo diante do nome da função. Ela arredonda um valor 
para o número de casas decimais especif icadas. Exemplo: na célula H4 temos o valor 
de 12,35687, vamos ut ilizar a função Arred() para arredondar o valor com duas casas 
decimais e teremos assim o valor final de 1236. 
20 
 
Excel Intermediário – Antonio Branco 
 
 
Obs: esta função arredonda número de 1 a 4 para baixo e de 5 a 9 para cima. 
 
=M od(): calcula o resto da divisão. Na figura a seguir, calculamos o resto da 
divisão do conteúdo da célula H4 por 2 (vai dar 0) e o resto da divisão do conteúdo da 
célula H5 por 6 (vai dar 4). 
 
 
 
=Cont.Núm(): retorna o número de células que contém valor numérico, não 
reconhece se for texto. 
 
 
 
=Contar.Vazio(): retorna o número de células vazias de um intervalo. 
 
21 
 
Excel Intermediário – Antonio Branco 
 
 
=Se(): esta é uma função condicional. Vamos aplicar uma condição, se tal 
condição for sat isfeita, o Excel apresenta um resultado “X” , se não for sat isfeita, 
apresenta um resultado “ Y” . Exemplo: ut ilizando a nossa planilha, caso o funcionário 
tenha at ingido uma média de vendas maior que R$ 10.500,00, o Excel exibirá a 
mensagem “ Boa média” , caso a média de vendas do funcionário seja menor que R$ 
10.500,00, o Excel exibirá a mensagem “ Precisa melhorar” . 
 
Perceba a sintaxe da fórmula: primeiro a condição, depois separados por “ ;” o 
resultado se a condição for verdadeira e o resultado se a condição for falsa. No caso, o 
M arcos Paulo e a Ana Paula tem média menor que R$ 10.000,00, ou seja, “ Precisa 
melhorar” . 
 
 
 
Ut ilizando a alça de preenchimento, repita a fórmula para as out ras médias. 
 
22 
 
Excel Intermediário – Antonio Branco 
 
 
 
=Cont.Se(): retorna a quant idade de ocorrências de determinado critério. Por 
exemplo, queremos saber quantos funcionários obt iveram "Boa média". 
 
 
 
Ao teclar Enter, teremos o resultado "4" , ou seja, 4 vendedores obt iveram "Boa 
média" . 
 
 
=M áximo(): retorna o maior valor do intervalo. Vamos testar quem tem a 
maior média de vendas. 
23 
 
Excel Intermediário – Antonio Branco 
 
Ao teclar Enter, veremos que a maior média é a da M aria Flores. Lembrando 
que caso alguma média aumente, automat icamente a função M áximo atualiza o seu 
valor também. 
 
=M ínimo(): retornar o menor valor do intervalo. Vamos testar quem tem a 
menor média de vendas. 
 
 
Ao teclar Enter, veremos que a menor média é a do M arcos Paulo. 
 
:: M acro no Excel 
 
O que é uma M acro? Resumidamente podemos entender M acro como um 
conjunto de tarefas automat izadas. Por exemplo, você t rabalha como contador(a) e 
precisa sempre está ut ilizando uma sequência de comandos repet idamente para cada 
cliente - o valor do imposto de renda de cada cliente deverá estar na fonte Arial, 
tamanho 14, cor vermelha, negrito e cent ralizado. Ou seja, são 5 comandos que você 
irá repet ir sempre. Você pode criar uma M acro e com apenas um clique o Excel 
aplicará essas 5 configurações de uma só vez. 
24 
 
Excel Intermediário – Antonio Branco 
 
Gravando a primeira M acro 
Ut ilizando a nossa planilha já digitada, vamos gravar a nossa primeira macro 
onde ela aplicará essas 5 formatações citadas acima nos t ítulos da nossa planilha. Vai 
ser bacana como exemplo. 
 
Guia Exibição, M acros, Gravar macros. 
 
 
Na janela que será exibida, dê um nome sua macro, defina uma let ra para 
servir de atalho (eu escolhi “ m” )e clique OK. 
 
Agora que você clicou OK, tudo o que fizer no Excel será gravado. Vamos fazer 
uma formatação para uma das células dos t ítulos, vamos fazer somente a primeira, 
25 
 
Excel Intermediário – Antonio Branco 
“ Vendedor(a)” . Já com a célula selecionada, vamos formatar com fonte Arial, Fundo 
azul, Cor Branca, Tamanho 12. 
 
Beleza, feitas essas formatações acima, agora podemos parar a gravação da 
M acro. Tem duas maneiras para isso: 
 Aba Exibição, M acros, Parar gravação. 
 
 
 No canto inferior esquerdo tem um botão de stop. 
 
 
Escolha uma das opções e pare a gravação. 
Pronto, gravamos a macro e o conteúdo da célula está atualmente formatado 
assim: 
 
 
26 
 
Excel Intermediário – Antonio Branco 
Agora vamos testar a M acro. Lembra do atalho? Ct rl + m. Clica em Janeiro e 
depois atalho CTRL + m. 
 
 
Podemos selecionar Fevereiro, M arço, Abril e M édia e aplicar CTRL + m. 
 
 
Bacana não é? Você pode fazer uma M acro bem mais complexa, com 15 
comandos de uma vez, por exemplo. 
 
:: Rastrear fórmulas 
 
Imagine que você está no seu t rabalho e o funcionário responsável pela 
alimentação da planilha de custos da empresa foi demit ido e agora você ficará no lugar 
dele. Detalhe: a planilha já existe há 8 anos e tem mais de 50 mil l inhas com dados e 
fórmulas. Você vai ter que se virar para entender esta planilha. 
27 
 
Excel Intermediário – Antonio Branco 
Às vezes, nesta planilha, os dados são inseridos manualmente, mas out ras 
vezes são obt idos por fórmulas, ou seja, dependem de dados de out ras células e vocêtem que saber de onde vêm esses dados. 
 
Vamos ao exemplo. Vamos fazer um cálculo rápido e simples para demonst rar 
o recurso e depois vamos ut il izá-lo em nossa planilha. Clique em qualquer célula em 
branco da nossa planilha e digite a seguinte fórmula e depois tecle Enter: 
 
 
Agora clique aba Fórmulas, Rast rear precedentes. 
 
 
O Excel irá marcar com setas todas as células que part icipam deste cálculo, ou 
seja, todos os seus precedentes. 
 
 
28 
 
Excel Intermediário – Antonio Branco 
Vamos fazer este procedimento para um dos vendedores(as) da nossa 
planilha. 
Clica no valor da média da Ana Cláudia, aba Fórmulas, Rast rear precedentes. 
 
 
Perceba que agora vemos marcadas as células que part icipam do cálculo da 
média da Ana Cláudia. 
Imagina isso em uma planilha com 30 mil l inhas e 50 colunas? Não tem como 
ficar procurando célula por célula. Ut iliza o Rast reio de Fórmulas para facilitar 
visualmente. 
 
Para remover as setas e voltar ao normal basta clicar Remover setas. 
 
 
Seguindo neste raciocínio, temos também a função Rast rear Dependentes, 
onde o Excel most ra quais células serão afetadas caso se altere o valor. 
 
 
29 
 
Excel Intermediário – Antonio Branco 
Por exemplo, na nossa planilha, caso se altere o valor da venda de Janeiro da 
Ana Cláudia para R$ 12.000,00, automat icamente a média dela também será alterada, 
óbvio. Vamos ver o Excel avisando isso. 
 
Clica na célula B7, aba Fórmulas, Rast rear dependentes. 
 
 
Pronto, agora o Excel está avisando que se o conteúdo da célula B7 for 
alterado, o conteúdo da célula F7 também será. 
 
 
:: Função Atingir M eta 
 
A função At ingir M eta é muito interessante. Você irá definir para o Excel a 
meta que tal célula deverá at ingir e o Excel irá retornar o valor que falta para at ingir tal 
meta. Vamos ao exemplo. 
 
Para o nosso exemplo, vamos criar uma nova planilha. 
 
 
Nesta planilha, vamos inserir dados (só um exemplo) referentes às vendas de 
determinado produto este mês. 
30 
 
Excel Intermediário – Antonio Branco 
 
 
 
Conteúdo das células: 
 
 A5: quant idade de unidades vendidas dos produt os; 
 B5: valor unitário do produto; 
 C5: curso de fabricação do produto; 
 D5: custo total de fabricação do produto, ou seja, custo unidade mult iplicado 
pelo vendas unidade (=C5* A5); 
 E5: Total de vendas em reais, ou seja, a quant idade de produtos vendidos vezes 
o valor bruto da unidade (=A5* B5); 
 F5: o lucro será calculado subt raindo o total de vendas pelo custo (=E5-D5). 
 
Até aqui nada demais, raciocínio simples, cálculos igualmente simples. 
Perceba que o nosso lucro foi de R$ 600,00, porém o meu chefe achou pouco 
e quer aumentar esse lucro para R$ 850,00 no mês que vem. O que seria feito 
normalmente? Vai testando cálculo até chegar nesse valor, para saber o quanto as 
vendas teriam que aumentar para chegar neste lucro. Blz! 
M as vamos deixar esta tarefa de ir testando cálculos para o Excel. Como? 
 
Clica na célula que você quer chegar a tal resultado, no caso a célula do lucro 
(F5), deixa ela selecionada, aba Dados, Teste de hipóteses, At ingir meta. 
31 
 
Excel Intermediário – Antonio Branco 
 
 
 
Na caixa de diálogo que se segue, o campo Definir célula já vem preenchido 
com a célula selecionada, deixa assim mesmo. Na opção Para o valor, vamos colocar o 
valor da nossa meta, ou seja, 850. 
Na caixa Alternando célula, iremos definir qual célula contém o valor que 
deverá aumentar para podermos at ingir a meta de lucro de 850, ou seja, temos que 
vender mais unidades. Clica na célula A5 e OK. Será exibida uma caixa de diálogo com 
um pequeno resumo, clique OK. 
 
 
 
Perceba que agora o Excel informa quantas unidades devem ser vendidas para 
at ingir o lucro de R$ 850,00. Temos que vender 1700 unidades. Então você não precisa 
ir testando valores, realizando os cálculos para chegar a tal conclusão, basta informar a 
meta para o Excel e ele faz o resto. 
 
 
 
32 
 
Excel Intermediário – Antonio Branco 
Fizemos um exemplo simples, mas imagine isto em uma planilha que tenha 
várias metas e não somente o Lucro para se levar em consideração. É uma mão na 
roda. 
 
:: Fixar Células 
 
Este assunto é mais fácil explicar direto no exemplo. Vamos lá. 
 
Digamos que os nossos vendedores irão ganhar um Bônus de R$ 500,00 para 
o Natal. Vamos somar a sua média e este Bônus. Realizando este cálculo primeiro para 
a M aria Crist ina: 
 
 
 
Nada demais. O valor do bônus está na célula H3 e a média da M aria Crist ina 
célula F5, soma os dois e t ranquilo, nenhuma novidade. A média da M aria Crist ina foi 
de R$ 12500,00 + 500 do bônus temos o valor final de R$ 13.000,00. 
Agora, como de costume, vamos puxar pela alça de preenchimento para 
realizar o mesmo cálculo para os out ros vendedores(as). 
 
33 
 
Excel Intermediário – Antonio Branco 
 
 
Perceba que agora a soma não ocorreu. Por quê? Quando puxamos a alça de 
preenchimento, o Excel vai atualizando a referência da célula - F5, F6, F7... (células das 
médias, beleza) e atualiza também as células do bônus H13, H14, H15. M as o bônus 
está somente em uma única célula, H13, não é desejável esta atualização. Queremos 
que o H13 fique Fixo, então vamos fixar esta célula ut ilizando o operador “$”. A nossa 
fórmula ficará assim: 
 
 
 
Ou seja, quando eu puxar pela alça de preenchimento, a linha 13 ficará fixa. 
Pronto, vamos poder realizar o cálculo os out ros vendedores(as). 
 
 
 
O mesmo raciocínio se aplica quando for o caso de fixar a coluna. 
 
Só para dar uma misturada nos assuntos, vamos clicar na Soma Bônus do 
Antonio Branco e Rast rear precedentes para ver o “ caminho” que a fórmula faz. 
 
34 
 
Excel Intermediário – Antonio Branco