Baixe o app para aproveitar ainda mais
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().
Compartilhar