Buscar

Apostila Excel intermediário

Faça como milhares de estudantes: teste grátis o Passei Direto

Esse e outros conteúdos desbloqueados

16 milhões de materiais de várias disciplinas

Impressão de materiais

Agora você pode testar o

Passei Direto grátis

Faça como milhares de estudantes: teste grátis o Passei Direto

Esse e outros conteúdos desbloqueados

16 milhões de materiais de várias disciplinas

Impressão de materiais

Agora você pode testar o

Passei Direto grátis

Faça como milhares de estudantes: teste grátis o Passei Direto

Esse e outros conteúdos desbloqueados

16 milhões de materiais de várias disciplinas

Impressão de materiais

Agora você pode testar o

Passei Direto grátis

Você viu 3, do total de 14 páginas

Faça como milhares de estudantes: teste grátis o Passei Direto

Esse e outros conteúdos desbloqueados

16 milhões de materiais de várias disciplinas

Impressão de materiais

Agora você pode testar o

Passei Direto grátis

Faça como milhares de estudantes: teste grátis o Passei Direto

Esse e outros conteúdos desbloqueados

16 milhões de materiais de várias disciplinas

Impressão de materiais

Agora você pode testar o

Passei Direto grátis

Faça como milhares de estudantes: teste grátis o Passei Direto

Esse e outros conteúdos desbloqueados

16 milhões de materiais de várias disciplinas

Impressão de materiais

Agora você pode testar o

Passei Direto grátis

Você viu 6, do total de 14 páginas

Faça como milhares de estudantes: teste grátis o Passei Direto

Esse e outros conteúdos desbloqueados

16 milhões de materiais de várias disciplinas

Impressão de materiais

Agora você pode testar o

Passei Direto grátis

Faça como milhares de estudantes: teste grátis o Passei Direto

Esse e outros conteúdos desbloqueados

16 milhões de materiais de várias disciplinas

Impressão de materiais

Agora você pode testar o

Passei Direto grátis

Faça como milhares de estudantes: teste grátis o Passei Direto

Esse e outros conteúdos desbloqueados

16 milhões de materiais de várias disciplinas

Impressão de materiais

Agora você pode testar o

Passei Direto grátis

Você viu 9, do total de 14 páginas

Faça como milhares de estudantes: teste grátis o Passei Direto

Esse e outros conteúdos desbloqueados

16 milhões de materiais de várias disciplinas

Impressão de materiais

Agora você pode testar o

Passei Direto grátis

Prévia do material em texto

EXCEL INTERMEDIÁRIO 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
1. PROCV, PROCH, Se e E, Se e Ou 
 
Função ProcV (Procura Vertical) 
Definição: Localiza e compara um valor dentro de uma tabela. Use PROCV quando os 
valores da comparação estiverem posicionados em uma coluna à esquerda ou à direita 
dos dados que você deseja procurar. 
O V em PROCV significa "Vertical". 
 
Sintaxe 
PROCV(valor_procurado;matriz_tabela;núm_índice_coluna;procurar_intervalo) 
 
=PROCV(Valor que você deseja pesquisar; intervalo no qual você deseja 
pesquisar o valor; o número da coluna no intervalo contendo o valor de retorno; 
Correspondência Exata ou Correspondência Aproximada – indicado como 
0/FALSO ou 1/VERDADEIRO). 
 
Argumentos 
Valor_procurado: é o valor a ser localizado na primeira coluna da matriz de dados. 
Pode ser um valor, uma referência ou uma seqüência de caracteres de texto, desde que 
esteja entre “ (aspas). 
Importante: Deve obrigatoriamente estar na primeira coluna. 
Matriz_tabela é a tabela de informações em que os dados são procurados. 
Use uma referência para um intervalo ou nome de intervalo. 
Num_índice_coluna é o número da coluna que está o resultado que a função deverá 
retornar. O índice inicia-se em 1 (sendo a primeira coluna) e sucessivamente até a 
última coluna dos dados. 
Procurar_intervalo é o modo que a função irá retornar os dados. É um valor lógico 
(Verdadeiro ou falso), que indicará se a função retornará o valor exato ou o aproximado. 
Utilize 0 (zero) para a procura exata do valor ou 1 (um) para pesquisa aproximada. 
Para que o retorno seja EXATO (VERDADEIRO), os valores na primeira coluna de 
matriz_tabela deverão estar em ordem crescente; caso contrário, PROCV pode não 
retornar o valor correto. 
Se procurar_intervalo for FALSO, matriz_tabela não precisará ser ordenada. 
 
Exemplos: 
01) Elaborar o banco de dados abaixo, fazendo-se o que se pede: 
 
Nome Endereço Bairro Cidade Estado 
Ana Rodovia Anhanguera, km 180 Centro Leme SP 
Eduardo R. Antônio de Castro, 362 São Benedito Araras SP 
Érica R. Tiradentes, 123 Centro Salvador BA 
Fernanda Av. Orozimbo Maia, 987 Jd. Nova 
Campinas 
Campinas SP 
Gabriela Rodovia Rio/São Paulo, km 77 Praia Grande Ubatuba SP 
Helena R. Júlio Mesquita, 66 Centro Recife PE 
Katiane R. 5, 78 Jd. Europa Rio Claro SP 
Lilian R. Lambarildo Peixe, 812 Vila Tubarão Ribeirão Preto SP 
Lucimara Av. dos Jequitibas, 11 Jd. Paulista Florianópolis SC 
Maria Av. Ipiranga, 568 Ibirapuera Manaus AM 
Pedro R. Sergipe, 765 Botafogo Campinas SP 
Roberto Av. Limeira, 98 Belvedere Araras SP 
Rubens Al. dos Laranjais, 99 Centro Rio de Janeiro RJ 
Sônia R. das Quaresmeiras, 810 Vila Cláudia Porto Alegre RS 
Tatiane R. Minas Gerais, 67 Parque 
Industrial 
Poços de Caldas MG 
 
Nome A 
Endereço B 
Bairro C 
Cidade D 
Estado E 
 
FÓRMULAS: 
A = Digitar o nome da pessoa a ser procurada. 
B = PROCV(Valor que você deseja pesquisar, intervalo no qual você deseja 
pesquisar o valor, o número da coluna no intervalo contendo o valor de 
retorno, Correspondência Exata ou Correspondência Aproximada – indicado 
como 0/FALSO ou 1/VERDADEIRO). 
C =PROCV(valor procurado; 
matriz_tabela;núm_índice_coluna;[procurar_intervalo]) 
D =PROCV() 
E =PROCV() 
 
 
 
 
 
Exercícios: 
01) Encontre o preço da peça 
 
ID do 
fornecedor 
Número da peça Nome da Peça Preço da peça Status 
SP301 A001 Bom d'água 68,93 Em estoque 
SP302 A002 Alternador 380,73 Em estoque 
SP303 A003 Filtro de ar 15,4 Em estoque 
SP304 A004 Rolamento de 
roda 
35,16 Em estoque 
SP305 A005 Silenciador 160,23 Em estoque 
SP306 A006 Bandeja de óleo 101,89 Insdisponível 
SP307 A007 Pastilhas de 
Freio 
65,99 Em estoque 
SP308 A008 Discos de Freio 85,73 Insdisponível 
SP309 A009 Farol 35,19 Em estoque 
SP310 A010 Cabo de freio 15,49 Em estoque 
 
 
Número da peça A004 
 
Preço da peça 35,16 
 
 
Função ProcH (Procura Horizontal) 
Definição: Localiza um valor na linha superior de uma tabela ou matriz de valores e 
retorna um valor na mesma coluna de uma linha especificada na tabela ou matriz. Use 
PROCH quando seus valores de comparação estiverem localizados em uma linha ao 
longo da parte superior de uma tabela de dados e você quiser observar um número 
específico de linhas mais abaixo. Use PROCV quando os valores de comparação 
estiverem em uma coluna à esquerda dos dados que você deseja localizar. 
O H de PROCH significa "Horizontal." 
 
Sintaxe 
PROCH(valor_procurado, matriz_tabela, núm_índice_linha, [procurar_intervalo]) 
 
 
Argumentos 
valor_proc Obrigatório. O valor a ser localizado na primeira linha da tabela. 
Valor_procurado pode ser um valor, uma referência ou uma cadeia de texto. 
matriz_tabela Obrigatório. Uma tabela de informações onde os dados devem ser 
procurados. Use uma referência para um intervalo ou um nome de intervalo. 
Os valores na primeira linha de matriz_tabela podem ser texto, números ou valores 
lógicos. 
Se procurar_intervalo for VERDADEIRO, os valores na primeira linha de matriz_tabela 
deverão ser colocados em ordem ascendente: ...-2, -1, 0, 1, 2,... , A-Z, FALSO, 
VERDADEIRO, caso contrário, PROCH pode não retornar o valor correto. Se 
procurar_intervalo for FALSO, matriz_tabela não precisará ser ordenada. 
Textos em maiúsculas e minúsculas são equivalentes. 
Classifique os valores em ordem crescente, da esquerda para a direita. Para saber mais, 
confira Classificar dados em um intervalo ou tabela. 
núm_índice_linha Obrigatório. O número da linha em matriz_tabela de onde o valor 
correspondente deve ser retirado. Um núm_índice_lin equivalente a 1 retorna o valor da 
primeira linha na matriz_tabela, um núm_índice_lin equivalente a 2 retorna o valor da 
segunda linha na matriz_tabela e assim por diante. Se núm_índice_lin for menor do que 
1, PROCH retornará o valor de erro #VALOR!; se núm_índice_lin for maior do que o 
número de linhas na matriz_tabela, PROCH retornará o valor de erro #REF!. 
intervalo_proc Opcional. Um valor lógico que especifica se você quer que PROCH 
localize uma correspondência exata ou aproximada. Se VERDADEIRO ou omitido, 
uma correspondência aproximada é retornada. Em outras palavras, se uma 
correspondência exata não for localizada, o valor maior mais próximo que seja menor 
que o valor_procurado é retornado. Se FALSO, PROCH encontrará uma 
correspondência exata. Se nenhuma correspondência for localizada, o valor de erro 
#N/D será retornado. 
Exemplos: 
 
Para exemplificarmos vamos utilizar o exemplo a seguir 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
Neste exemplo vamos buscar o valor Total de vendas de acordo com o nome da empresa, 
como podemos ver o campo TOTAL, está em linha e não em coluna, devido isso devemos 
utilizar PROCH e não PROCV. 
 
 
 
A célula B11, será onde digitaremos o nome da empresa. A célula B12, será onde 
digitaremos a fórmula que irá retornar o total de vendas da empresa digitada na 
célula B11. 
 
Vamos aplicar a fórmula, siga os passos: 
Clique na célula B12. 
 
 
Digite a fórmula:=Proch( 
 
Clique na célula onde deverá ser digitado o nome da empresa, que neste exemplo é a 
célula B11. 
 
Pressione ponto e vírgula e selecione toda a tabela com os dados 
 
 
Pressione ponto e vírgula e diga o número da linha correspondente ao campo que deverá 
ser pesquisado, neste exemplo desejamos pesquisar o TOTAL, o mesmo está na oitava 
linhaem relação a seleção da tabela. Sendo assim, digite 8. 
 
 
Pressione ponto e vírgula e digite FALSO. Feche o parêntese e pressione ENTER. 
 
A fórmula deverá ficar assim: 
 
=PROCH(B11;B2:F9;8;FALSO) 
 
O resultado será o mesmo da função PROCV. 
 
Precisamos agora digitar o nome de uma empresa para que o TOTAL seja pesquisado. 
Na célula B11, digite o nome de uma empresa. 
 
 
Função SE 
A função se é uma das funções mais populares no Excel e ela permite fazer comparações lógicas 
entre um valor e o que você espera. Em sua forma mais simples, a função se diz: 
SE(Algo for Verdadeiro, faça tal coisa, caso contrário, faça outra coisa) 
 
Sintaxe 
SE(teste_lógico, valor_se_verdadeiro, [valor_se_falso]) 
 
Por exemplo1: 
Elaborar a planilha abaixo, fazendo-se o que se pede: 
 
Fazer uma folha de pagamento e calcular o novo salário, baseado no 
aumento. Se o salário for menor ou igual a R$ 1.000,00, aumento de 40%. Se for 
maior que R$ 1.000,00, aumento de 30%. Os valores R$ 360,00 e R$ 1.260,00 da 
primeira linha também deverão ser calculados. 
 
Nome Salário Aumento Novo Salário 
João dos Santos R$ 900,00 R$ 360,00 R$ 1.260,00 
Maria da Silva R$ 1.200,00 Até 1000,00 40% 
Manoel das Flores R$ 1.500,00 mais 1000,00 30% 
Lambarildo Peixe R$ 2.000,00 
Sebastião Souza R$ 1.400,00 
Ana Flávia Silveira R$ 990,00 
Silvia Helena 
Santos 
 R$ 854,00 
Alberto Roberto R$ 1.100,00 
 
Exemplo 2: Se aninhados 
 
02) Elaborar a planilha abaixo, fazendo-se o que se pede: 
 Projeção para o ano de 2017 
 
Receita bruta Jan-Mar Abr-Jun Jul-Set Out-Dez Total do Ano 
 140.000,00 185.000,00 209.100,00 240.000,00 
 
Despesa 
Líquida 
Jan-Mar Abr-Jun Jul-Set Out-Dez Total do Ano 
Salários 20.000,00 26.000,00 33.800,00 43.940,00 
Juros 20.000,00 15.600,00 20.280,00 26.364,00 
Aluguel 12.000,00 20.930,00 27.209,00 35.371,70 
Propaganda 16.100,00 28.870,00 33.631,00 43.720,30 
Suprimentos 19.900,00 39.000,00 50.700,00 65.910,00 
Diversos 25.000,00 32.500,00 42.250,00 54.925,00 
 
Total do Trim. 
Receita líquida 
Situação 
 Valor Acumulado do ano de despesas 
 
FÓRMULAS: 
Total do Ano Receita Bruta: Soma das receitas anuais. 
 
Total do Ano Despesa Líquida: Soma das despesas anuais. 
 
Total do Trimestre: Soma das despesas trimestrais. 
Receita Líquida: Receita Bruta menos Total do Trimestre. 
 
Valor Acumulado do ano de despesas: Soma do Total do Ano de 
Despesas 
Situação: Se Receita Líquida for menor que R$ 1.000,00, 
"Prejuízo Total"; 
 Se Receita Líquida for menor que R$ 5.000,00, "Lucro Médio";
 
 Se Receita Líquida for maior que R$ 5.000,00, "Lucro Total'. 
 =SE(B16<1000;"Prejuízo Total";SE(B16<=5000;"Lucro 
Médio";SE(B16>5000;"Lucro Total"))) 
Aplicar a formatação condicional da seguinte forma: 
 
 
Função SE com E, SE com OU 
Use a função SE junto com as funções E e OU para realizar várias avaliações se as 
condições forem verdadeiras ou falsas. 
Sintaxe 
SE(E()) - SE(E(lógico1, [lógico2], ...), valor_se_verdadeiro, [valor_se_falso])) 
SE(OU()) - SE(OU(lógico1, [lógico2], ...), valor_se_verdadeiro, [valor_se_falso])) 
 
Nome do argumento Descrição 
 
teste_lógico (obrigatório) A condição que você deseja testar. 
 
valor_se_verdadeiro(obrigatório) O valor que você deseja retornar se o resultado do teste_lógico 
for VERDADEIRO. 
 
valor_se_falso (opcional) O valor que você deseja retornar se o resultado do teste_lógico 
for FALSO. 
 
 
Analisemos algumas visões gerais sobre como estruturar as funções E 
e OU individualmente. Ao se combinar cada uma com uma instrução SE, elas são lidas 
da seguinte maneira: 
E – =SE(E(Algo for Verdadeiro, Outra coisa será Verdadeira), Valor se Verdadeiro, 
Valor se Falso) 
OU – =SE(OU(Algo for Verdadeiro, Outra coisa será Verdadeira), Valor se Verdadeiro, 
Valor se Falso) 
Exemplo 1 
A seguir exemplos de algumas instruções SE(E()) e SE(OU()) aninhadas comuns. As 
funções E e OU podem oferecer suporte a até 255 condições individuais, mas não é uma 
prática recomendável usar mais do que algumas, porque pode ser difícil compilar, testar 
e manter fórmulas complexas e aninhadas. 
 
Abaixo as fórmulas escritas por extenso de acordo com a sua lógica: 
 
 
Fórmula Descrição 
=SE(E(A2>0,B2<100),VERDADEIRO, FALSO) SE A2 (25) for maior do que 0, E B2 (75) for 
menor que 100, VERDADEIRO é retornado. Caso 
contrário, FALSO é retornado. Nesse caso, ambas 
as condições são verdadeiras, então 
VERDADEIRO é retornado. 
=SE(E(A3="Vermelho",B3="Verde"),VERDADEIRO,FALSO) Se A3 ("Azul") = "Vermelho", E B3 ("Verde") for 
igual a "Verde", VERDADEIRO é retornado. 
Caso contrário, FALSO é retornado. Nesse caso, 
somente a primeira condição é verdadeira, então 
FALSO é retornado. 
=SE(OU(A4>0,B4<50),VERDADEIRO, FALSO) SE A4 (25) for maior do que 0, E B4 (75) for 
menor que 50, VERDADEIRO é retornado. Caso 
contrário, FALSO é retornado. Nesse caso, apenas 
a primeira condição é VERDADEIRO, mas como 
OU somente requer que um argumento seja 
verdadeiro, a fórmula retorna VERDADEIRO. 
=SE(OU(A5="Vermelho",B5="Verde"),VERDADEIRO,FALSO) Se A5 ("Azul") for igual a "Vermelho", OU B5 
("Verde") for igual a "Verde", VERDADEIRO é 
retornado. Caso contrário, FALSO é retornado. 
Nesse caso, o segundo argumento é Verdadeiro, 
então a fórmula retorna VERDADEIRO. 
 
 
 
Exemplo 2 
=Ou 
 
Digamos que para se classificar um time precisa de 33 pontos OU de 8 vitórias. Note que 
o Excel vai retornar verdadeiro caso o time tenha alcançado 33 pontos no campeonato ou 
então tenha atingido 8 vitórias. A função que vamos inserir será, 
portanto =Ou(C5>33;D5>8) 
 
Uma funcionalidade interessante deste comando é que ele nos permite que trabalhemos 
com operadores de comparação, que são: > Maior que, < Menor que, >= Maior ou igual 
a <= Menor ou igual a e <> Diferente de. 
Observe que agora vamos estender a fórmula para todas as linhas e ver o resultado: 
 
Embora a função “Ou” já seja por si só bastante útil, para resultados mais específicos ela 
é costumeiramente mesclada com a função “Se” ou então a função “E”. 
Perceba que para nossa planilha ficar mais bonita esteticamente, vamos misturar a 
função Ou com a função Se, como visto anteriormente. 
Vamos digitar a seguinte sintaxe 
 =SE(OU(C5>33;D5>8);"Classificado";"Desclassificado"). Assim, caso o time 
alcance os 33 pontos OU 8 vitórias aparecerá “Classificado” ao invés de “Verdadeiro”. 
 
=E 
Já a função =E funciona de maneira um pouquinho diferente: Para ela retornar 
VERDADEIRO, todos os termos da fórmula condicional precisam ser verdadeiros. Por 
exemplo, se você colocar 100 termos e 99 foram verdadeiros e apenas 1 for falso, seu 
resultado será falso. 
A sintaxe é bastante simples: =E("Comparação 1"; "comparação 2"...). Veja que no 
exemplo abaixo também mesclamos com a fórmula =Se(). No final das contas 
utilizamos =SE(E(C5="sim";D5="Sim"); "Aprovado"; "Reprovado") 
 
 
Reparou que o Excel só retornou o valor correspondente a verdadeiro (que defini como 
"Aprovado") quando as 2 condições foram "Sim"? É assim que funciona a =E().

Outros materiais