CURSO EXCEL AVANÇADO

Prévia do material em texto

CURSO EXCEL AVANÇADO 
PROF.MS.DOUGLAS MANDAJI 
Ano 2012 
A função SE é muito importante, pois 
pode atribuir à sua planilha a capacidade 
de tomar decisões. Você vai utilizar essa 
função do mesmo modo que utiliza 
condições em seu dia-a-dia. 
FÓRMULA DA CONDIÇÃO SE 
; - quer dizer então faça 
( ) – quer dizer leia 
“TEXTO” – quer dizer escreva. 
Sempre que desejar escrever texto coloque entre aspas. No caso ele 
escreverá TEXTO. 
“ “ – as duas aspas seguintes dão o sentido de vazio. Ou seja, se caso 
estiver vazio. 
Vamos criar uma tabela de controle de Notas de alunos, onde 
podemos calcular a média, automaticamente identifica se o 
aluno foi aprovado ou não. 
Segue o raciocínio... 
Primeiramente, precisamos entender o que desejamos fazer. 
-Queremos que no campo situação ele escreva Aprovado somente 
se o aluno tirar uma nota maior ou igual a 7 na média, caso 
contrário ele deverá escrever Reprovado, já que o aluno não 
atingiu a condição para passar. 
Aprovada Reprovado 
? 
Utilizamos a fórmula do SE> 
 
Observem o EX-1 
FÓRMULA DA CONDIÇÃO SE 
Ex-1 
Vamos ver no exercício - 1 
FÓRMULA DA CONDIÇÃO SE 
Arraste a janela 
ResultadoEx-1 
=SE(C3>=7;”Aprovado”;”Reprovado”) 
Exercício- 1 E3 =SE(C3>=7;”Aprovado”;”Reprovado”) 
Reprovado 
FÓRMULA DA CONDIÇÃO SE 
Veja mais um exemplo do SE com mais de uma condição. 
Vamos descrever o aproveitamento do aluno quanto a 
média, colocando Ótimo para uma média maior ou igual a 
9, Bom para uma média maior ou igual a 8, Regular para 
uma média maior ou igual a e Insuficiente para média 
menor que 7. 
 SE(C3>=9;"Ótimo";SE(C3>=8;"Bom";SE(C3>=7;"Regular";"Insuficiente"))) 
Ex2 
FÓRMULA DA CONDIÇÃO SE 
 SE(C3>=9;"Ótimo";SE(C3>=8;"Bom";SE(C3>=7;"Regular";"Insuficiente"))) 
Resposta do Ex-2 
Arraste a janela E3 
Ex-3 
Utilizando SE, ou seja, em vez de escrevermos algo para resposta V 
ou F, faremos um cálculo: 
Na planilha o cálculo do imposto de Renda, será efetuado só para 
aqueles funcionários com o valor de 650, se o salário for maior de 
R$650, então deverá se (x) uma taxa de 5% em cima do salário 
Bruto, caso contrário deverá ficar com valor zero. 
FÓRMULA DA CONDIÇÃO SE 
C2 
Onde: 
 
B2- refere-se ao endereço do Salário Bruto 
>650- refere-se a condição para que seja feito o cálculo 
B2 *5% - refere-se a resposta se for V, ou seja, se no endereço B2 conter 
um valor maior que 650, então ele (x) o VL do Salário Bruto (B2) por 5% 
(taxa do Imposto de Renda) 
0 (zero) - refere-se a resposta se for F, ou seja, caso o endereço B2 não 
tenha um valor maior que 650, então não haverá cálculo, ele colocará 
0(zero). 
FÓRMULA DA CONDIÇÃO SE 
Arraste a janela 
Ex-3 
FÓRMULA DA CONDIÇÃO SE e E 
Agora você tem uma planilha onde tem a idade e altura de seus 
alunos. Haverá uma competição e somente aqueles que tem idade 
Maior que 15 e Altura maior ou igual que 1,70 participaram da 
competição. Neste caso você utilizará a condição SE e a condição E. 
Ex-4 
Onde: 
 
B2- Refere-se ao endereço da Idade 
>15 – Refere-se a condição, ou seja, se a idade for maior que 15 
C2 – Refere-se ao endereço da Altura 
>=1,70 – Refere-se a condição, ou seja, se a altura for maior ou igual a 1,70 
“Competirá” – resposta se as duas condições forem verdadeiras. 
“Não Competirá”- resposta se caso as duas respostas não forem 
verdadeiras. 
FÓRMULA DA CONDIÇÃO SE e E 
Arraste a janela 
Resultado 
Ex-4 
FÓRMULA DA CONDIÇÃO SE e OU 
Neste exemplo basta que uma condição seja verdadeira para que o aluno 
participe da condição. 
Ex-4 
Arrastar 
CONDICIONAIS - SE = 
CONDIÇÃO - SE = 
=SOMA(A21:D21) A) =SE(A21>10000;5%*A21;0) B) =SE(A18>B18;1,1*A18;”SEM BONUS”) C) =B21/E21 D) SE(C18<2000;15%*C18;25%*C18) 
E) =SE(C18<=2000; 0; SE(C18<=3500; 15%*C18; SE(C18<=5000; 25%*C18; 
SE(C18<=8000; 30%*C18; 35%*C18)))) F) SE(E(A18>B18;E21>50000);110%*C18;C18) 
G) =SE(OU(A21>20000; B21>20000; C21>20000; D21>20000); "Meta 
OK"; "") 
Exercício- 5 
FÓRMULA DA CONDIÇÃO CONT.VALORES 
Essa função conta a quantidade de valores contida na lista de 
argumentos ou no intervalo das células especificadas como 
argumento. 
Essa função aceita de 1 a 30 argumentos. Os argumentos devem 
ser números, ou matrizes ou referências que contenham números. 
 
Sintaxe: =CONT.VALORES(valor1;valor2;intervalo1;...) 
 
Exemplo: 
 
Se todas as células em A1:A10 contiverem dados, quer sejam 
números, textos ou qualquer outro dado, exceto a célula A3, 
então: 
 
=CONT.VALORES(A1:A10) --> resulta 9 
FÓRMULA DA CONDIÇÃO CONT.SE 
Esta função calcula o número de 
células não vazias em um intervalo que 
corresponde a determinados critérios 
Vamos fazer o exercício - 6 
FÓRMULA DA CONDIÇÃO CONT.SE 
Agora, vamos verificar, os Vendedores (as) que venderam mais lanches. 
Acima de 7 lanches vendidos concorre a um (Bônus) de R$ 5,00. 
Onde: 
 
(B2:B6) – Refere-se ao endereço das células onde vc deseja contar 
; - refere-se como parte da sintaxe para separar 
“>=7” – refere-se a condição, ou seja, esta fórmula só irá contar as células 
que contém valores maiores ou igual a 7. 
Ex-6 
2 
B8 
FÓRMULA DA CONDIÇÃO SOMASE 
A Função Somase, como o nome sugere 
executa o somatório das células 
indicadas se uma determinada condição 
for satisfeita. 
Onde: 
 
= SOMASE – É o nome da Fórmula 
(C2:C6) – refere-se ao endereço inicial e final de células onde vc digita a 
palavra PG, especificando se está paga ou não. 
“PG”- é o critério para somar, ou seja ,só somará se neste intervalo de 
célula de C2 até C6, conter alguma palavra PG. O critério deverá ser 
colocado entre aspas. 
B2:B6 – refere-se ao intervalo de célula onde será somado, mediante a 
condição, ou seja, ele somará somente aqueles valores que na coluna C vc 
digitou PG. 
FÓRMULA DA CONDIÇÃO SOMASE 
Ex-7 
R$57.320,66 
C7 
Somase – Soma Condicional 
Digite um valor 
de conta ”G4” 
1 
2 
120 
R$ 5.679,16 
Ex8 
FÓRMULA DA CONDIÇÃO CONTAR VAZIO 
Contar as células que estão vazias. 
Ex-9 
3 
B10 
=ESQUERDA() 
 
Essa função atua em valores do tipo texto. A função esquerda 
retorna um determinado número de caracteres a partir da 
esquerda (início) de uma String de Texto. 
 
Sintaxe: =ESQUERDA(String ou Endereço;Número de Caracteres) 
 
 
FUNÇÃO ESQUERDA 
Ex10: 
Conta 7 espaços 
Curso A 
FUNÇÃO DIREITA 
=DIREITA() 
 
Essa função atua em valores do tipo texto. A função direita 
retorna um determinado número de caracteres a partir da direita 
(final) de uma String de Texto. 
 
Sintaxe: =DIREITA(String ou Endereço;Número de Caracteres) 
EX11: 
da aula 
Conta 7 espaços 
=DIREITA(C5;7) 
FUNÇÃO CONCATENAR 
Agrupar várias seqüências de caracteres de texto em uma única seqüência 
Ex:12 
AL.Madeira 258,CJ 1905,Barueri/SP-CEP0654-010 
FÓRMULA DA CONDIÇÃO PROCV 
A função PROCV é útil quando você precisa procurar uma 
informação em uma tabela. 
Ex 13: 
Sinal 
Função 
1º.Coluna 
Resultado 
Intervalo Coluna 
Correspondência 
EXATA 
1 
Vamos testar Ex-14 ? 
=PROCV(C11;$B$4:$C$8;2;Falso) 
FÓRMULA DA CONDIÇÃO PROCV 
A função PROCV é útil quando você precisa procurar uma 
informação em uma tabela. 
Ex 14: 
Muito fácil 
ES 1 
2 
=PROC(G5;A5:A31;B5:B31) 
Vitória 
3 Natal 
4 
=PROC(G8;B5:B31;C5:C31) 
3.443.825 
5 22.000,00 
6 
=PROC(G11;D5:D31;C5:C31) 
São Paulo 
Digite! 
FÓRMULA DA CONDIÇÃO PROCV 
EX15 
FÓRMULA DA CONDIÇÃO PROCV EX16 
WV 1 
2 
=PROCV(H4;D5:E18;2;0) 
212.358,00 
=ÍNDICE(C5:C18;CORRESP(H4;D5:D18;0)) 
3 LESTE 
4 
=PROC(H4;D5:D18;B5:B18) 
13/07/2002FÓRMULA DA CONDIÇÃO PROCV EX16 
Vamos ver O EXERCÍCIO 
PROC-AV – já montado. 
FÓRMULA DA CONDIÇÃO PROC-AV 
Vamos ver O EXERCÍCIO 17 
Segue a mesmas regras 
FÓRMULA DA CONDIÇÃO PROCH 
FÓRMULA DA CONDIÇÃO PROCH 
EX17 E 6 =PROCH(D6;$C$15:$F$16;2) 
F 6 
=D6*E6 
ÍNDICE - =ÍNDICE 
ÍNDICE($L$8:$R$12;$C$12;$F$12) 
EX18 
ÍNDICE - =ÍNDICE 
=ÍNDICE($S$19:$S$48;$C$18) 
EX18 
ÍNDICE - =ÍNDICE 
=ÍNDICE($C$25:$N$25;$G$29) 
456 
EX18 
função - =CORRESP 
=CORRESP($E$13;$E$16:$E$27;1) 
74 EX19 
função - =CORRESP 
=CORRESP($K$13;$K$16:$K$27;0) 
78 EX19 
função - =CORRESP 
=CORRESP($Q$13;$Q$16:$Q$27;-1) 
75 EX19 
FILTRO AVANÇADO 
A ferramenta Filtragem é boa para selecionar 
dados numa lista grande. Filtrar é exibir dados 
que atendem uma certa condição e ocultar os 
demais. 
 
Vamos ver o exemplo - 20 
FILTRO 
Selecione a célula (A3:D20) 
Clique da seta 
1 
2 
3 
4 
5 
Ex-20 
Procedimento 
FILTRO 
Selecione a célula 
Clique da seta 
1 
2 
3 
4 
5 
Ex-21 
Procedimento 
FILTRO 
1 
Ex-21 
No mesmo exercício, crie uma tabela 
Utilize as funções abaixo: 
2 
Procedimento 
F4 
G4 
 FILTRO AVANÇADO 
Ex22 
Planilha Dados 
Criar relatórios 
separados por 
departamentos 
 de TI e Vendas. 
 
1 
Ex-22 FILTRO AVANÇADO 
3 Os Critérios da separações por departamentos precisam ser 
colocados na “PLANILHA CRITÉRIOS” 
4 Na “PLANILHA DADOS”, selecione C1 até C19 , copie e cole para 
colocar na “ PLANILHA CRITÉRIOS” 
5 
6 
7 
Ex22 
 FILTRO AVANÇADO 
Planilha Critérios 8 
Vá para a PLANILHA DEP TI 9 
10 11 
12 
Selecione o Intervalo da lista 13 
14 
Vá para a PLANILHA Dados 
Faça a seleção A1 até D19. 
15 
16 
17 
Ex22 
 FILTRO AVANÇADO 
18 
Vá para a PLANILHA Critérios 
Faça a seleção A1 até A2. 
19 
20 
21 
21 
Vá para a PLANILHA DEP TI 22 
23 
24 
25 
PLANILHA DEP TI 
Tabela Dinâmica 
Permite selecionar diferentes visualizações 
dos dados facilmente 
 
Utilizado para analisar grandes quantidades 
de dados 
Vamos ver o exemplo – 23 
e onde usar a TAB.DIN. 
Exemplo: Pedidos de venda 
 
Campos: Empresa, funcionário, data do pedido, Meio de 
entrega 
 
Como comparar as vendas realizadas pelos funcionários? 
 
Como identificar qual funcionário realizou o maior 
volume de vendas? 
 
Como identificar qual o meio de entrega preferido pelos 
clientes? 
 
Tabela Dinâmica 
Tabela Dinâmica 
 
Criando o relatório de tabela Dinâmica e tbém 
SubTotais. 
 
Objetivo: Elaborar um relatório referente aos 
dados encontrados na tabela. 
 
Tarefas: Criar a opção de agrupar os dados do 
curso. 
Ex- 23 
Como exemplo: 
Supermercados 
Tabela Dinâmica Ex- 23 
Tabela Dinâmica 
1 
2 
3 
4 
Ex- 23 
Tabela Dinâmica 
É só escolher os campos que iram compor a tabela dinâmica arrastar 
e soltar no campo correto 
Ex- 23 
Clique do 
“DIA” e 
ARRASTE 
Tabela Dinâmica Ex- 23 
Tabela Dinâmica 
Ainda dentro do assunto, temos a possibilidade de criar 
subtotais de uma planilha, que são mostrados para análise de 
decisão 
SUB TOTAIS 
O Excel calcula valores de subtotal com uma função de 
resumo, como Soma ou Média.Você pode exibir subtotais em 
uma lista com mais de um tipo de cálculo de cada vez. 
Como exemplo: 
Vamos fazer o 
Ex 24 
Tabela Dinâmica 
SUB TOTAIS 
Ex- 24 
Procedimento 
Antes, precisamos classificar em ordem 
crescente a nossa tabela, escolha qual a 
coluna que queira direcionar para o 
subtotal. Selecione A3 até D17. 
1 
2 
Tabela Dinâmica 
SUB TOTAIS 
Curso 
Visualização da nova Tabela 
3 
4 
Ex- 24 
Tabela Dinâmica 
SUB TOTAIS 
Selecione A3 até D17 na tabela 5 
6 
7 
8 
 9 
10 
11 
Ex- 15 
Tabela Dinâmica 
SUB TOTAL - Soma 
Ex- 15 
Média 
Agora que você aprendeu a 
fazer a Soma do Sub 
Total, faça a média do Sub 
Total, aplique a mesma 
propriedade 
Resultado 
Tabela Dinâmica 
SUB TOTA L 
Selecione A3 até D17 na tabela 1 
2 
3 
4 
 5 
6 
 8 
 7 
Ex- 15 
Para que a Soma e a Média 
fiquem na mesma planilha, 
desmarque este item. 
Tabela Dinâmica 
SUB TOTAL - média 
Ex- 15 
Resultado 
Fácil !!! 
Terminamos o Excel Avançado. 
Agora que vc assimilou as 
informações, vamos fazer as 
Lições de Casa 
CURSO EXCEL Avançado