Apostila Excel - Intermediário

Apostila Excel - Intermediário

Requisito: ter feito o curso Básico de Excel

 Format ação condicional

 Classificar e Filt rar, Personalizar Classificação

 Coment ários nas células

 M últ iplas Planilhas

 Validação de Dados

 Funções: Dat a e Hora, Se, Cont . Num., Cont .se, Se, Cont ar.Vazio, Arred, M od, M áximo, M ínimo

 M acros

 Rast rear Fórmulas

 Função At ingir M et a

 Fixar Células

Para começar a brincadeira, digit e a planilha abaixo para ut ilizarmos em alguns exemplos.

:: Formatação Condicional

A format ação simples, t odo mundo conhece: você format a uma célula com t ext o vermelho, font e 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 format ação “ x” ; caso seja falsa, a célula irá assumir uma format ação “ y” .

Como exemplo, iremos criar uma legenda de cores, onde det erminada cor represent a vendedores que at ingiram média maior que R$ 10.500,00 e out ra det erminada cor, vendedores com média menor que R$ 10.500,00.

Relembrando rapidament e a fórmula da M édia. Figura abaixo:

Digit ando 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…

Você verá uma caixa de diálogo, onde no primeiro campo será digit ado 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 Text o Amarelo Escuro” , clique OK.

Pront o, agora o Excel irá realçar t odos com média de vendas maior que R$

10.500,00. Vamos fazer um t est e e alt erar o valor da venda de Janeiro do M arcos Paulo para R$ 5.000,00. Aut omat icament e ele vai perder o realce amarelo.

Só para t reinar, vamos colocar a format açã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

“ Preenchiment o Verde e Text o Verde Escuro” .

Ok, ut ilizamos format ações condicionais pré-definidas do Excel. Vamos agora fazer uma format ação condicional personalizada.

Repit a a operação e agora escolha a opção Format o Personalizado.

Teremos uma t ela bem familiar no Word.

Agora vamos personalizar da seguint e forma: o vendedor(a) que tiver média maior que R$ 10.500,00, terá o cont eúdo da célula em Negrit o it álico, cor vermelha, preenchimento cinza. Clique OK. Pronto, nós personalizamos a nossa Formatação Condicional.

Para finalizar o assunt o Format ação Condicional, vamos ver a condição “É menor do que…” . Bem simples de ent ender, é o cont rário da condicional ant erior que vimos. Agora vamos realçar quem t iver média menor que R$ 10.500,00. Vamos usar a format ação pré configurada do Excel mesmo “ Preenchiment o Amarelo e Text o Amarelo Escuro” .

Pront o, agora quem t iver a média menor que R$ 10.500,00, o cont eú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 crescent e de M édia. Vamos primeiro à ordem alfabét ica. Ut ilizando a nossa planilha já digit ada, clique no nome M aria Flores, Classificar e Filt rar, Classificar de A a Z.

Pront o, t eremos os dados da nossa planilha organizados em ordem crescent e.

Perceba que os valores respect ivos de cada funcionário acompanham, o Excel não ordena soment e os nomes, os valores t ambém são ordenados.

Det alhe: 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 ilizar qualquer coluna como referência. Por exemplo, vamos colocar em ordem crescent e 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.

Podemos t ambém personalizar est e ordenament o. Clica Classificar e Filt rar, Personalizar Classificação.

Na caixa de diálogo que é exibida, podemos fazer out ras classificações.

Não vamos mexer nest a opção Personalizada, foi só para apresent ar.

Cont inuando.

Opção Filtro . Clica Classificar e Filt ro. Perceba que agora nas colunas da nossa planilha aparecem uns ícones com uma set a para baixo.

Clicando na set a de Vendedor(a), t eremos a seguint e t ela:

Vamos desmarcar Ana Cláudia, João Carlos e M aria Cristina, clica OK. O Excel agora só irá exibir Ant onio Branco, M arcos Paulo e M aria Flores. Ou seja, o Excel Filt rou e exibiu soment e as informações selecionadas pelo usuário.

Para volt ar à exibição normal: Clica no funil de Vendedor(a), marca a opção Selecionar t udo, OK. Pront o, volt a à exibição normal.

Para desat ivar o Filt ro: Clica Classificar e Filt rar, .

:: Opção Inserir Comentários

Aqui t emos 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 est ará ausent e no próximo mês por mot ivo de saúde.

Clica com o bot ão direit o na célula da M aria Flores, Inserir coment ário.

Irá ser exibida uma pequena janela para você inserir o seu coment ário. Bast a digit ar.

Obs: posicionando o mouse nas bordas, você pode arrast ar a posição da pequena janela.

Para alt erar o coment ário, bast a clicar com o bot ão direit o na célula,

Para Excluir o coment ário, bast a clicar com o bot ão direit o 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 past a de t rabalho, beleza. M as digamos que você t enha várias planilhas que uma depende de um valor “ x” da out ra, ou seja, se você alt erar um valor da “ planilha 1” , t erá que alt erar os cálculos da “ planilha 2” , “ planilha 3” , …

Vamos logo ao exemplo para ficar mais fácil de ent ender.

Vamos renomear a nossa planilha para “ Principal” e criar uma segunda planilha chamada “ Comissões” .

Na planilha comissões, vamos digit ar o seguint e:

E agora sim, vamos ao que int eressa: 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 est e raciocínio em Fórmula realizando est e cálculo primeiro para a M aria Crist ina.

Na planilha Comissões, célula B5, digit e a seguinte fórmula, t ecle Ent er:

 Principal: nome da planilha que cont ém o valor referido;

 !F5: é a célula da planilha (Principal) que cont ém o valor referido. Nest e caso, a média da M aria Cristina;

 15% : valor da comissão em porcent agem.

Calculamos que a M aria Cristina t em direit o a R$ 1387,50 de comissão.

Vamos fazer um test e rápido: na planilha Principal, alt ere o valor da venda de Janeiro da M aria Crist ina para R$ 25.000,00. Perceba que aut omat icament e a M édia dela irá aument ar e por conseguint e, a comissão também.

Agora bast a 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 alt erada, aut omat icament e a sua comissão t ambém o será.

:: Validação de Dados

O Excel permit e que nós possamos impedir que dados não desejáveis sejam inseridos em det erminadas 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 novament e ut ilizar 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 novament e.

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.

Beleza, agora t ent e digit ar o valor de 2000 para a M aria Crist ina em Janeiro (célula B5). O Excel irá exibir uma mensagem de erro. Agora est amos cont rolando os dados que ent ram nest a célula, ninguém pode digit ar 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 novament e. Permit ir, Decimal, é maior do que, M ínimo 3000.

Pront o, t odos os funcionários t em que t er venda de mais de R$ 3000,00 em Janeiro. Caso cont rário, pega uma suspensão :) .

:: 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(): ret orna a dat a at ual, mas vai at ualizando aut omat icamente conforme os dias;

=M ês(Hoje()): ret orna o mês at ual;

=Ano(Hoje()): ret orna o ano at ual;

=Agora(): ret orna a dat a e hora at uais;

Vamos ut ilizar a função =Agora() para Personalizar Format ar Células. No exemplo fica melhor de ent ender.

Eu vou ut ilizar a função =Agora() na minha célula H4, mas qualquer célula vazia serve.

Digit e =Agora() na célula H4, t ecle Ent er, bot ão direit o, Format ar células.

Aba Número, Personalizado. Escolha est a opção de dat a e hora em dest aque azul.

Logo acima vamos mexer no t ipo padronizado do Excel. Onde t em “ / ” , vamos colocar “ -” e a hora:minut o será ent re “ ( )” . Veja figura abaixo.

Pront o, personalizamos o format o da função =Agora().

=Arred(): bem int uit ivo diant e do nome da função. Ela arredonda um valor para o número de casas decimais especificadas. Exemplo: na célula H4 t emos o valor de 12,35687, vamos ut ilizar a função Arred() para arredondar o valor com duas casas decimais e t eremos assim o valor final de 1236.

Obs: est a função arredonda número de 1 a 4 para baixo e de 5 a 9 para cima.

=M od(): calcula o rest o da divisão. Na figura a seguir, calculamos o rest o da divisão do cont eúdo da célula H4 por 2 (vai dar 0) e o rest o da divisão do cont eúdo da célula H5 por 6 (vai dar 4).

=Cont.Núm(): ret orna o número de células que cont ém valor numérico, não reconhece se for t ext o.

=Contar.Vazio(): ret orna o número de células vazias de um intervalo.

=Se(): est a é uma função condicional. Vamos aplicar uma condição, se t al condição for sat isfeit a, o Excel apresent a um result ado “ X” , se não for sat isfeit a, apresent a um result ado “ Y” . Exemplo: ut ilizando a nossa planilha, caso o funcionário t enha 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 sint axe da fórmula: primeiro a condição, depois separados por “ ;” o result ado se a condição for verdadeira e o result ado se a condição for falsa. No caso, o M arcos Paulo e a Ana Paula t em média menor que R$ 10.000,00, ou seja, “ Precisa melhorar” .

Ut ilizando a alça de preenchiment o, repit a a fórmula para as out ras médias.

=Cont.Se(): retorna a quant idade de ocorrências de det erminado crit ério. Por exemplo, queremos saber quant os funcionários obt iveram "Boa média".

Ao t eclar Ent er, t eremos o result ado " 4", ou seja, 4 vendedores obt iveram "Boa média".

=M áximo(): ret orna o maior valor do int ervalo. Vamos t est ar quem t em a maior média de vendas.

Ao t eclar Ent er, veremos que a maior média é a da M aria Flores. Lembrando que caso alguma média aument e, aut omat icament e a função M áximo at ualiza o seu valor t ambém.

=M ínimo(): ret ornar o menor valor do int ervalo. Vamos t est ar quem t em a menor média de vendas.

Ao t eclar Ent er, veremos que a menor média é a do M arcos Paulo.

:: M acro no Excel

O que é uma M acro? Resumidament e podemos ent ender M acro como um conjunt o de t arefas aut omat izadas. Por exemplo, você t rabalha como cont ador(a) e precisa sempre est á ut ilizando uma sequência de comandos repet idament e para cada client e - o valor do impost o de renda de cada client e deverá est ar na font e Arial, t amanho 14, cor vermelha, negrit o 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.

Gravando a primeira M acro

Ut ilizando a nossa planilha já digitada, vamos gravar a nossa primeira macro onde ela aplicará essas 5 format ações cit adas acima nos t ít ulos da nossa planilha. Vai ser bacana com o 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 at alho (eu escolhi “ m” )e clique OK.

Agora que você clicou OK, t udo o que fizer no Excel será gravado. Vamos fazer uma format ação para uma das células dos t ít ulos, vamos fazer soment e a primeira,

“ Vendedor(a)” . Já com a célula selecionada, vamos format ar com font e Arial, Fundo azul, Cor Branca, Tamanho 12.

Beleza, feit as essas format açõ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 cant o inferior esquerdo t em um bot ão de st op.

Escolha uma das opções e pare a gravação.

Pront o, gravamos a macro e o cont eúdo da célula est á at ualment e format ado assim:

Agora vamos t est ar a M acro. Lembra do at alho? Ct rl + m. Clica em Janeiro e depois at alho 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 aliment ação da planilha de cust os da empresa foi demit ido e agora você ficará no lugar dele. Det alhe: a planilha já exist e há 8 anos e t em mais de 50 mil linhas com dados e fórmulas. Você vai t er que se virar para ent ender est a planilha.

Às vezes, nest a planilha, os dados são inseridos manualment e, mas out ras vezes são obt idos por fórmulas, ou seja, dependem de dados de out ras células e você t em 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 ilizá-lo em nossa planilha. Clique em qualquer célula em branco da nossa planilha e digit e a seguint e fórmula e depois t ecle Ent er:

Agora clique aba Fórmulas, Rast rear precedent es.

O Excel irá marcar com set as t odas as células que part icipam dest e cálculo, ou seja, t odos os seus precedent es.

Vamos fazer este procediment o para um dos vendedores(as) da nossa planilha.

Clica no valor da média da Ana Cláudia, aba Fórmulas, Rast rear precedent es.

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 linhas e 50 colunas? Não t em como ficar procurando célula por célula. Ut iliza o Rast reio de Fórmulas para facilit ar visualment e.

Para remover as set as e volt ar ao normal bast a clicar Remover set as.

Seguindo nest e raciocínio, t emos t ambém a função Rast rear Dependent es, onde o Excel most ra quais células serão afet adas caso se alt ere o valor.

Por exemplo, na nossa planilha, caso se alt ere o valor da venda de Janeiro da Ana Cláudia para R$ 12.000,00, aut omat icament e a média dela t ambém será alt erada, óbvio. Vamos ver o Excel avisando isso.

Clica na célula B7, aba Fórmulas, Rast rear dependent es.

Pront o, agora o Excel est á avisando que se o cont eúdo da célula B7 for alt erado, o cont eúdo da célula F7 t ambém será.

:: Função Atingir M eta

A função At ingir M et a é muit o int eressant e. Você irá definir para o Excel a met a que t al célula deverá at ingir e o Excel irá ret ornar o valor que falt a para at ingir t al met a. Vamos ao exemplo.

Para o nosso exemplo, vamos criar uma nova planilha.

Nest a planilha, vamos inserir dados (só um exemplo) referent es às vendas de det erminado produt o est e mês.

Cont eúdo das células:

 A5: quant idade de unidades vendidas dos produt os;

 B5: valor unitário do produt o;

 C5: curso de fabricação do produt o;

 D5: cust o t ot al de fabricação do produt o, ou seja, cust o unidade mult iplicado pelo vendas unidade (=C5* A5);

 E5: Tot al de vendas em reais, ou seja, a quant idade de produt os vendidos vezes o valor brut o da unidade (=A5* B5);

 F5: o lucro será calculado subt raindo o t ot al de vendas pelo cust o (=E5-D5).

At é aqui nada demais, raciocínio simples, cálculos igualment e simples.

Perceba que o nosso lucro foi de R$ 600,00, porém o meu chefe achou pouco e quer aument ar esse lucro para R$ 850,00 no mês que vem. O que seria feit o normalment e? Vai t est ando cálculo até chegar nesse valor, para saber o quant o as vendas t eriam que aument ar para chegar nest e lucro. Blz!

M as vamos deixar est a t arefa de ir t est ando cálculos para o Excel. Como?

Clica na célula que você quer chegar a t al result ado, no caso a célula do lucro (F5), deixa ela selecionada, aba Dados, Test e de hipót eses, Atingir met a.

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 met a, ou seja, 850.

Na caixa Alt ernando célula, iremos definir qual célula cont ém o valor que deverá aument ar para podermos at ingir a met a de lucro de 850, ou seja, t emos 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 quant as 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 t est ando valores, realizando os cálculos para chegar a t al conclusão, bast a informar a met a para o Excel e ele faz o rest o.

Fizemos um exemplo simples, mas imagine ist o em uma planilha que t enha várias met as e não soment e o Lucro para se levar em consideração. É uma mão na roda.

:: Fixar Células

Este assunt o é mais fácil explicar diret o no exemplo. Vamos lá.

Digamos que os nossos vendedores irão ganhar um Bônus de R$ 500,00 para o Nat al. Vamos somar a sua média e est e Bônus. Realizando est e cálculo primeiro para a M aria Cristina:

Nada demais. O valor do bônus est á na célula H3 e a média da M aria Cristina 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 t emos o valor final de R$ 13.000,00.

Agora, como de cost ume, vamos puxar pela alça de preenchiment o para realizar o mesmo cálculo para os out ros vendedores(as).

Perceba que agora a soma não ocorreu. Por quê? Quando puxamos a alça de preenchiment o, o Excel vai at ualizando a referência da célula - F5, F6, F7... (células das médias, beleza) e at ualiza t ambé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 est a at ualização. Queremos que o H13 fique Fixo, ent ão vamos fixar est a célula ut ilizando o operador “$”. A nossa fórmula ficará assim:

Ou seja, quando eu puxar pela alça de preenchiment o, a linha 13 ficará fixa.

Pront o, 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 mist urada nos assunt os, vamos clicar na Soma Bônus do Ant onio Branco e Rast rear precedent es para ver o “ caminho” que a fórmula faz.