Baixe o app para aproveitar ainda mais
Prévia do material em texto
1 APOSTILA EXCEL AVANÇADO EXCEL AVANÇADO MANAUS / 2022 Sumário Capítulo 1 5 1 Revisão de Comandos Básicos ...................................................................................... 9 1.1 Formatando Células .................................................................................................... 9 1.1.1 Fonte ........................................................................................................................ 9 1.1.2 Alinhamento ........................................................................................................... 10 1.1.3 Número .................................................................................................................. 10 1.2 Cálculos Simples – Fórmulas e Funções ................................................................... 12 1.2.1 Fórmulas ................................................................................................................ 12 1.2.2 Funções ................................................................................................................. 13 Atividades de Fixação ..................................................................................................... 15 Capítulo 2 16 2 Funções Condicionais .................................................................................................. 17 2.1 Funções: CONT.SE e SOMASE ................................................................................ 18 2.2 Função SE ................................................................................................................ 21 Atividades de Fixação ..................................................................................................... 24 2.2.1 Função SE com SE (SE Composta) ....................................................................... 25 2.2.2 Função SE com E ................................................................................................... 27 2.2.3 Função SE com OU ................................................................................................ 28 Atividades de Fixação ..................................................................................................... 29 Capítulo 3 30 3 Funções de Busca ........................................................................................................ 31 3.1 Função PROCV e PROCH ........................................................................................ 31 3.2 Função ÍNDICE ......................................................................................................... 35 3.3 Funções Banco de Dados ......................................................................................... 37 3.3.1 Função BDSOMA ................................................................................................... 38 3.3.2 Função BDMÁX ...................................................................................................... 39 3.3.3 Função BDEXTRAIR .............................................................................................. 40 3.3.4 Função BDCONTARA ............................................................................................ 41 3.4 Dicas: Funções de Erro e Data .................................................................................. 42 3.4.1 Função SEERRO ................................................................................................... 42 3.4.2 Função HOJE ......................................................................................................... 43 3.4.3 Função AGORA...................................................................................................... 44 Capítulo 4 45 4 Funções Financeiras .................................................................................................... 46 4.1 Função PGTO ........................................................................................................... 46 4.2 Função TAXA ............................................................................................................ 48 4.3 Função NPER ........................................................................................................... 49 4.4 Função VP ................................................................................................................. 51 4.5 Função VF ................................................................................................................. 53 Capítulo 5 56 5 Ordenação de Dados ................................................................................................... 57 5.1 Formatação Condicional ............................................................................................ 57 5.2 Lista Suspensa .......................................................................................................... 60 5.3 Localizar e Substituir Registro ................................................................................... 62 5.4 Classificação ............................................................................................................. 64 5.5 Subtotal ..................................................................................................................... 65 5.6 Auto Filtro .................................................................................................................. 67 5.7 Filtro Avançado.......................................................................................................... 69 Capítulo 6 80 6 Proteção de Planilhas e Células ................................................................................... 74 6.1 Proteção de Planilhas ................................................................................................ 74 6.2 Proteção de Células .................................................................................................. 75 6.3 Proteção de Pastas de Trabalho ............................................................................... 77 6.4 Proteção de Arquivo .................................................................................................. 78 Capítulo 7 80 7 Tabela e Gráfico Dinâmico ........................................................................................... 90 7.1 Tabela Dinâmica ........................................................................................................ 90 7.1.1 Conectando Tabelas Dinâmicas ............................................................................. 83 7.2 Gráfico Dinâmico ....................................................................................................... 86 Capítulo 8 90 8 Macro e Formulário .................................................................................................... 101 8.1 Macro ...................................................................................................................... 101 8.1.1 Criando Macro ...................................................................................................... 101 8.2 Formulário ............................................................................................................... 933 9 Referências 100 Capítulo 1. Revisão de Comandos Básicos 09 1.1. Formatando Células 09 1.1.1. Fonte 09 1.1.2. Alinhamento 10 1.1.3. Número 10 1.2. Cálculos Simples – Fórmulas e Funções 12 1.2.1. Fórmulas 12 1.2.2. Funções 15 1.2.2.1. Funções Simples 13 Atividades de Fixação 15 1 9 1 Revisão de Comandos Básicos Planilha básica Para montarmos uma planilha simples, a primeira coisa que faremos é formatar as células para que as informações fiquem mais visíveis. 1.1 Formatando Células Para formatar as células trabalhamoscom os seguintes blocos de formatação abaixo, encontrados na guia Página Inicial. ✓ Fonte; ✓ Alinhamento; ✓ Número. 1.1.1 Fonte Fonte – Neste grupo de formatação, encontramos os principais botões mais utilizados. Botão Função Exemplo Botão Todas as Bordas Botão Cor de Preenchimento (Sombreamento) Botão Cor da Fonte 10 1.1.2 Alinhamento Alinhamento – Neste grupo, podemos destacar alguns dos botões recomendados. Botão Função Exemplo Centralizar Alinhar no Meio Orientação Mesclar e Centralizar Quebrar Texto Automaticamente 1.1.3 Número Número – É neste grupo que podemos aplicar formatações em células que possuem em seus conteúdos números. Podendo formatar a numeração em qualquer estilo, seja num estilo monetário, data, hora ou até mesmo no estilo porcentual. 11 Botão Função Formato de Número Formato de Número de Contabilização Estilo de Porcentagem Aumentar Casas Decimais Diminuir Casas Decimais Exemplos: Formato de Número: Data Abreviada Formato de Número de Contabilização Estilo de Porcentagem Aumentar Casas Decimais Diminuir Casas Decimais 15/01/2016 R$ 15,00 15% 15,00 15,0 20/01/2016 R$ 20,00 20% 20,00 20,0 Dica: Caso deseje limpar toda a formatação aplicada na célula, utilize a ferramenta: Limpar . 12 Resultado de uma planilha formatada: 1.2 Cálculos Simples – Fórmulas e Funções 1.2.1 Fórmulas As fórmulas são cálculos trabalhados com operadores matemáticos (são símbolos utilizados na Matemática para execução de operações simples. A regra matemática da precedência de operadores também é respeitada pelo Excel). Veja tabela abaixo: Ordem Operador Descrição 1º % Porcentagem 2º ^ Exponenciação 3º (*) e (/) Multiplicação e Divisão 4º (+) e (-) Adição e Subtração Exemplos de Fórmulas Matemáticas no Excel: Fórmula Descrição =A1+B1 Soma o conteúdo das células A1 e B1 =A1-B1 Subtrai o conteúdo das células A1 e B1 =A1*B1 Multiplica o conteúdo de A1 por B1 =A1/B1 Divide o conteúdo de A1 por B1 =A1*20% Multiplica o conteúdo de A1 por 0,2 =A1^B1 Eleva o conteúdo de A1 ao conteúdo de B1 (A1B1) 13 Observação: Todas as fórmulas são obrigatoriamente precedidas do sinal de igualdade (=). O sinal de igualdade indica ao Excel que o conteúdo da célula se trata de uma fórmula e não de texto simples. 1.2.2 Funções Uma função nada mais é do que um comando que permite ao Excel executar cálculos a partir dos argumentos, retornando um resultado numérico ou lógico. Para facilitar, o Excel já vem com um conjunto predefinido de funções. As funções atuam sobre os valores especificados pelo usuário. Observação: Todas as funções são obrigatoriamente precedidas do sinal de igualdade (=), logo em seguida, o nome da função e argumentos inseridos entre parênteses (argumentos são valores ou referências as células que farão parte dos cálculos executados, nem sempre uma função requer argumentos, isso depende da sua finalidade). 1.2.2.1 Funções Simples ❖ SOMA – Calcula a soma dos valores das células. Sintaxe Resultado =SOMA(8;4;2) 14 =SOMA(A10;H6) Soma valores das células apenas de A10 e H6. =SOMA(A1:A5) Soma valores das células no intervalo de A1 até A5. =SOMA(B4:B20;D6) Soma valores das células de B4 até B20 e o valor de D6. ❖ MULT – Multiplica os valores das células. Sintaxe Resultado =MULT(2;4) 8 =MULT(A10;H6) Multiplica valores das células apenas de A10 e H6. =MULT(A1:A5) Multiplica valores das células no intervalo de A1 até A5. =MULT(B4:B20;D6) Multiplica valores das células de B4 até B20 e valor de D6. ❖ MÉDIA – Calcula a média dos valores das células. 14 Sintaxe Resultado =MÉDIA(2;4;6) 4 =MÉDIA(A10;H6) Calcula a média das células apenas de A10 e H6. =MÉDIA(A1:A5) Calcula a média das células no intervalo de A1 até A5. =MÉDIA(B4:B20;D6) Calcula a média das células de B4 até B20 e o valor de D6. ❖ MÍNIMO – Retorna o menor valor das células. Sintaxe Resultado =MÍNIMO(24;31;14) 14 =MÍNIMO(A10;H6) Retorna o menor das células A10 e H6. =MÍNIMO(A1:A5) Retorna o menor das células no intervalo de A1 até A5. =MÍNIMO(B4:B20;D6) Retorna o menor das células de B4 até B20 e de D6. ❖ MÁXIMO – Retorna o maior valor das células. Sintaxe Resultado =MÁXIMO(24;31;14) 31 =MÁXIMO(A10;H6) Retorna o maior das células A10 e H6. =MÁXIMO(A1:A5) Retorna o maior das células no intervalo de A1 até A5. =MÁXIMO(B4:B20;D6) Retorna o maior das células de B4 até B20 e de D6. ❖ CONT.VALORES – Conta o número de células não vazias. Sintaxe Resultado =CONT.VALORES(A1:A5) Conta o número de células preenchidas no intervalo de A1 até A5. O resultado é 5. ❖ CONT.NÚM – Conta o número de células que contêm números. Sintaxe Resultado =CONT.NÚM(A1:A5) Conta o número de células que contem números de A1 até A5. Se as células de A1 até A5 tiver somente números, então, o resultado é 5. 15 Atividades de Fixação 1) Elaborar a planilha abaixo, fazendo o que se pede: CÁLCULOS: 1ª Tabela: ✓ Total 1º Trimestre: soma das vendas dos meses de Jan / Fev / Mar. ✓ Máximo: calcular o maior valor entre os meses de Jan / Fev / Mar. ✓ Mínimo: calcular o menor valor entre os meses de Jan / Fev / Mar. ✓ Média: calcular a média dos valores entre os meses de Jan / Fev / Mar. 2ª Tabela: ✓ Total 2º Trimestre: soma das vendas dos meses de Abr / Mai / Jun. ✓ Máximo: calcular o maior valor entre os meses de Abr / Mai / Jun. ✓ Mínimo: calcular o menor valor entre os meses de Abr / Mai / Jun. ✓ Média: calcular a média dos valores entre os meses de Abr / Mai / Jun. ✓ Totais: soma das colunas de cada mês (1ª e 2ª tabela). ✓ Total do Semestre: soma dos totais de cada trimestre. 16 Capítulo 2. Funções Condicionais 19 2.1. Funções: CONT.SE e SOMASE 20 2.2. Função SE 23 Atividades de Fixação 26 2.2.1. Função SE com SE (SE Composta) 27 2.2.2. Função SE com E 29 2.2.3. Função SE com OU 30 Atividades de Fixação 31 2 17 2 Funções Condicionais As funções condicionais diferem das funções simples por exigirem como argumento uma comparação, que lhe permita decidir o valor a ser colocado numa célula. Antes de abordar a questão das funções condicionais, três conceitos devem ser discutidos: ✓ Operadores Lógicos: Também chamados de Operadores de Comparação ou Relacionais, são símbolos matemáticos utilizados pelo Excel na comparação lógica entre dois ou mais valores. Operador Descrição = Igual a > Maior que < Menor que >= Maior ou igual a <= Menor ou igual a <> Diferente de ✓ Teste Lógico: É uma comparação entre dois valores. Uma comparação somente resulta em valor verdadeiro ou falso. Exemplos: 1º Valor Operador Lógico 2º Valor Teste Lógico Valor do Teste Lógico 20 = 20 20=20 Verdadeiro 21 <> 21 21<>21 Falso 20 > 20 20>20 Falso 21 < 21 21<21 Falso 20 >= 21 20>=21 Falso 20 <= 20 20<=20 Verdadeiro 18 ✓ Critério: É uma comparação entre um valor e um conjunto de valores. Resulta em outro conjunto de valores. Exemplos: Operador Lógico Valor Critério Conjunto de Valores Resultado > 2 >2 0,1,2,3,4,5,6 3,4,5,6 < 3 <3 0,1,2 >= 4 >=4 4,5,6 <= 1 <=1 0,1 = Uva =Uva Maçã, Pêra, Uva Uva <> Pêra <>Pêra Maçã, Uva 2.1 Funções: CONT.SE e SOMASE ❖ CONT.SE – Conta a quantidade de vezes em que um determinado critério aparece em um intervalo de células. Sintaxe Resultado =CONT.SE(intervalo;“critérios”) Retorna a quantidade de células que atendem um determinado critério. Exemplo: Intervalo Critérios Função CONT.SE Resultado B2=Uva B3=Maçã B4=Uva “Uva” =CONT.SE(B2:B4;“Uva”) 2 Veja o próximo exemplo. 19 Na coluna A esta a lista de funcionários, enquanto na coluna B esta a informação se ele possui ou não especialização.Você deseja realizar uma contagem de quantos funcionários possuem especialização. No caso do exemplo a função ficaria =CONT.SE(B2:B16;“Sim”), ou seja, no intervalo das células B2 até B16, a função deve contar apenas as células que estão marcadas com “SIM”. Veja o exemplo na imagem abaixo: ❖ SOMASE – Calcula a soma de um intervalo de células correspondente a outro intervalo que atende um determinado critério. Esta função possui três argumentos: ✓ Intervalo (intervalo a ser verificado); ✓ Critério (valor a ser usado no critério); ✓ Intervalo Soma (intervalo a ser somado). 20 Sintaxe Resultado =SOMASE(intervalo;“critérios”;intervalo_soma) Retorna a soma da verificação do critério no intervalo. Observe o exemplo: Para calcular a Quantidade de Passageiros da Azul, faça o seguinte procedimento: 1) Digite a função: =SOMASE(B3:B10;“Azul”;C2:C10). Onde: 21 ✓ B3:B10 – Intervalo a ser verificado: Companhia; ✓ “Azul” – Valor a ser usado no critério, ou seja, especificar a companhia; ✓ C3:C10 – Intervalo a ser somado, ou seja, soma dos passageiros. Para calcular a Quantidade de Passageiros de Manaus, faça o seguinte procedimento: 1) Digite a função: =SOMASE(A3:A10;“Manaus”;C3:C10). Onde: ✓ A3:A10 – Intervalo a ser verificado: Cidade; ✓ “Manaus” – Valor a ser usado no critério, ou seja, especificar a cidade; ✓ C3:C10 – Intervalo a ser somado, ou seja, soma dos passageiros. Observação: Atente para usar a função correta, não confundindo entre os símbolos (;) e (:). E nunca esquecendo que o termo a ser buscado (ou seja, o “critério”) deve estar SEMPRE entre aspas. 2.2 Função SE ❖ SE – A função SE é sempre utilizada quando um problema apresentar duas respostas possíveis. A tarefa da função SE é comparar o valor da célula com um critério estabelecido e retornar dois resultados. Um se a comparação for verdadeira e outro se a comparação for falsa. Possui três argumentos: 22 ✓ Teste Lógico; ✓ Valor Se Verdadeiro; ✓ Valor Se Falso. Sintaxe: =SE(teste_lógico;“valor_se_verdadeiro”;“valor_se_falso”) ✓ Valor_se_verdadeiro: É o resultado retornado, caso o teste lógico tenha valor verdadeiro. ✓ Valor_se_falso: É o resultado retornado, caso o teste lógico tenha valor falso. Teste Lógico Valor Se Verdadeiro Valor Se Falso Função Se Resultado do Teste Lógico Resultado 18>=21 Maior de Idade Menor de Idade =SE(18>=21;“Maior de Idade”;“Menor de Idade”) Falso Menor de Idade No Excel comparamos uma célula (ou seja, seu conteúdo) com um determinado valor. Veja o próximo exemplo. Na coluna G, Resultado, deve exibir duas respostas, “Aprovado” e “Reprovado”, baseando-se pela média do aluno se for maior ou igual a 7. Sendo assim, 7 é o critério para aprovação. Então compara-se a média do aluno com 7. Se a comparação retornar verdadeiro, este estará “Aprovado”, caso contrário estará “Reprovado”. A função SE trata então as duas situações: quando a comparação for verdadeira e quando for falsa ou vice-versa. 23 Função Teste Lógico (comparação de média (F3) com 7). Retorna o resultado se o teste lógico for Verdadeiro. Retorna o resultado se o teste lógico for Falso. A forma de montar a função é a seguinte: =SE (Comparação; “Resultado caso Verdadeiro”; “Resultado caso Falso”). A função SE para este exemplo ficaria assim: Onde: =SE(F3>=7;“Aprovado”;“Reprovado”) 24 Atividades de Fixação 1) Resolva os seguintes cálculos: CÁLCULO: Resultado: Se a Venda for maior ou igual a 5, o resultado será "Ótimo", senão será "Regular". CÁLCULO: Resultado: Se o Preço for Maior ou Igual à 12, o resultado será "Caro", senão será "Barato". a) Quantos funcionários são do sexo masculino? b) Quantos funcionários são do sexo feminino? c) Quantos funcionários tem salário maior que R$ 2000,00? d) Qual a soma total do salário somente dos homens? e) Qual a soma total do salário somente das mulheres? 25 2.2.1 Função SE com SE (SE Composta) Quando o valor da célula pode conter mais de dois resultados é necessário criarmos a função SE composta de mais outra. Veja os exemplos. Exemplo 1 O aluno que tiver média maior ou igual a 7 estará “Aprovado”, se a média for menor que 4 estará “Reprovado” e se tiver entre 4 e 7 ficará de “Recuperação”. Note que podemos ter 3 situações para o resultado das notas do aluno. É necessário então criarmos duas funções SE para realizar duas comparações, sobrando uma última alternativa para o retorno da resposta. A forma de montar a função é a seguinte: =SE (1ª Comparação; “1º Resultado”; SE (2ª Comparação; “2º Resultado”; “3º Resultado”)). A função para este exemplo fica assim: Onde: =SE(F3>=7;“Aprovado”;SE(F3<4;“Reprovado”;“Recuperação”)) A primeira comparação, F3 > = 7 for verdadeira, retornará a resposta “Aprovado” para o aluno; caso seja falsa, será realizada uma nova função para comparar novamente o valor da célula, pois ainda não podemos afirmar que o aluno está “Reprovado” ou de “Recuperação”. A segunda comparação retornará “Reprovado”, caso a comparação de F3 < 4 seja verdadeira e retornará “Recuperação”, caso a comparação seja falsa, pois se F3 não é nem maior ou igual a 7 (1ª comparação) e nem menor que 4 (2ª comparação) só pode estar entre 4 e 7 (3ª comparação implícita), fazendo com que o aluno fique de “Recuperação”. 26 Exemplo 2 Neste exemplo precisamos criar uma função para determinar o desconto do INSS do funcionário. Imagine a descrição de desconto desta forma: ✓ Salário até R$ 900,00, terá desconto de 8% do salário; ✓ Salário acima de R$ 1300,00, terá desconto de 10% do salário; ✓ Salário entre R$ 901,00 e R$ 1300,00, terá desconto de 9% do salário. A função fica assim: Onde: =SE(B3<=900;B3*8%;SE(B3>1300;B3*10%;B3*9%)) A primeira função SE compara se o salário (B3) é menor ou igual a 900. Caso a comparação seja verdadeira, retornará o cálculo: salário * 8% (ou seja, B3 * 8%). Caso a comparação seja falsa devemos realizar um outro SE para determinar se o salário está acima de 1300 ou se está entre 901 e 1300. A segunda função SE compara se o salário (B3) é maior do que 1300. Caso a comparação seja verdadeira, retornará o cálculo: salário * 10% (ou seja, B3 * 10%). Se a comparação for falsa, entende-se que o salário está entre 901 e 1300, então, retornará o cálculo: salário * 9% (ou seja, B3 * 9%). O resultado será: 27 2.2.2 Função SE com E A função SE pode ser trabalhada junto de outras funções. Neste caso, será trabalhada com a condição E. Vejamos este exemplo. Agora você tem uma planilha na qual 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 participarão da competição. Neste caso, você utilizará a condição SE e a condição E. Por quê? É simples, porque para o aluno participar ele dever possuir a idade maior que 15 e altura maior ou igual 1,70. As duas condições devem ser verdadeiras, caso uma seja falsa, ele não participará. A forma de montar a função é a seguinte: =SE (E(1ª Condição; 2ª Condição); “Resultado caso Verdadeiro”; “Resultado caso Falso”). Veja o exemplo: Onde: =SE(E(B3>15;C3>=1,70);“Competirá”;“Não Competirá”) ✓ B3 – Refere-se a célula da idade; ✓ >15 – Refere-se à 1ª condição, ou seja, idade maior que 15; ✓ C2 – Refere-se a célula da altura; ✓ >=1,70 – Refere-se à 2ª condição, ou seja, altura maior ou igual a 1,70; ✓ “Competirá” – Resposta se as duas condições forem verdadeiras. ✓ “Não Competirá” – Resposta se uma das condições for falsa. 28 2.2.3 Função SE com OU A função SE também pode ser trabalhada junto da condição OU, da mesma forma como a condição E. Neste exemplo, basta que uma condição seja verdadeira para que o aluno participe, ou seja, não é necessárioque as duas condições sejam verdadeiras, basta uma ou outra condição. Diferente da função SE com E que necessariamente requer que as duas condições sejam verdadeiras. A forma de montar a função é a seguinte: =SE (OU(1ª Condição; 2ª Condição); “Resultado caso Verdadeiro”; “Resultado caso Falso”). Veja o exemplo: Onde: =SE(OU(B3>15;C3>=1,70);“Competirá”;“Não Competirá”) ✓ B3 – Refere-se a célula da idade; ✓ >15 – Refere-se à 1ª condição, ou seja, idade maior que 15; ✓ C2 – Refere-se a célula da altura; ✓ >=1,70 – Refere-se à 2ª condição, ou seja, altura maior ou igual a 1,70; ✓ “Competirá” – Resposta se uma das condições for verdadeira. ✓ “Não Competirá” – Resposta se as duas condições forem falsas. 29 Atividades de Fixação 1) Resolva as seguintes questões. a) Calcule % Desconto e Valor Total. Se a Valor da Compra for menor que R$ 200,00, terá o desconto de 3%; caso o Valor da Compra for maior que R$ 500,00, terá desconto de 15%. Caso contrário, terá desconto de 8%. b) Se Média for maior ou igual a 7 E as Faltas forem menor que 11, a situação do aluno será “Aprovado”, senão “Reprovado”. c) Se o Lucro for maior que R$ 90,00 OU o Gasto for menor que R$ 70,00, a situação será "OK", caso contrário, "Vamos Melhorar". 30 Capítulo 3. Funções de Busca 35 3.1. Função PROCV e PROCH 35 3.2. Função ÍNDICE 39 3.3. Funções Bando de Dados 41 3.3.1. Função BDSOMA 42 3.3.2. Função BDMÁX 43 3.3.3. Função BDEXTRAIR 44 3.3.4. Função BDCONTARA 45 3.4. Dicas: Funções de Erro e Data 46 3.4.1. Função SEERRO 46 3.4.2. Função HOJE 47 3.4.3. Função AGORA 48 3 31 3 Funções de Busca 3.1 Função PROCV e PROCH Função PROCV A função PROCV realiza uma pesquisa verticalmente, ou seja, faz a busca de um determinado argumento usando como critério as colunas da planilha. Observe a planilha abaixo. Encontrar informações em uma planilha dessas, é muito simples. Mas, se a planilha tiver 100 ou mais itens? Como podemos fazer? Para isso usamos a função PROCV. A função PROCV funciona da seguinte forma: =PROCV (valor_procurado; matriz_tabela; núm_índice_coluna; procurar_intervalo) ✓ Valor_procurado: Esse campo determina qual é o valor a ser procurado. Você deve estabelecer uma célula em branco para digitar o valor. ✓ Matriz_tabela: Local onde o valor_procurado deverá ser procurado. Eles podem ser números, textos ou valores lógicos (verdadeiro ou falso). ✓ Núm_índice_coluna: Em qual coluna (1,2,3,4 ou mais) o valor estará. Apesar da nomenclatura ser alfabética (A, B e C), o Excel ordena as colunas por número. 32 ✓ Procurar_intervalo: Esse último termo define se a fórmula deve procurar um valor exato ou próximo. No caso de um valor exato, é preciso digitar 0 (FALSO), enquanto um valor aproximado tem de se usar 1 (VERDADEIRO). Agora imagine que você deseja realizar uma busca do Nome da pessoa, Cidade e Estado Civil pelo Código. Por exemplo, quando digitarmos o código 7, deverá retornar o Nome da pessoa que representa este código. O mesmo também vale para Cidade e Estado Civil. Veja o exemplo: Onde: =PROCV(G7;A2:D11;2;0) ✓ G7 – Determina o valor a ser procurado. Neste exemplo: o código 7; ✓ A2:D11 – Determina as células, onde será procurado o valor da célula G7, ou seja, o código 7 será procurado na planilha; ✓ 2 – Determina a coluna (Nome) que retorna como resultado; ✓ 0 – Determina que o resultado é uma resposta exata. Isso se traduz como: O valor que será colocado na célula G7, será procurado nas células A2:D11 (a qual representa toda a planilha), onde deverá retornar como resposta a coluna 2 (já que esta refere-se a coluna Nome), retornando o resultado exato (por isso, finalizamos a função colocando 0). 33 O exemplo acima foi utilizado para exibir o Nome da pessoa sempre que for digitado um código. Mas o mesmo procedimento também pode ser utilizado para os campos Cidade e Estado Civil. A função que define Cidade, é: =PROCV(G7;A2:D11;3;0). A função continua a mesma, só muda o núm_índice_coluna, já que a coluna que se encontra Cidade está na terceira coluna da planilha. A função que define Estado Civil, é: =PROCV(G7;A2:D11;4;0). Apenas muda o núm_índice_coluna, já que a coluna que se encontra Estado Civil está na quarta coluna da planilha. Função PROCH A função PROCH faz o mesmo que a função PROCV, a diferença é que realiza uma pesquisa horizontalmente, ou seja, faz a busca de um determinado argumento usando como critério as linhas da planilha. 34 Observe a planilha abaixo. Quando uma planilha se encontra organizada horizontalmente e você deseja realizar uma busca, para este caso, utilizamos a função PROCH. Função PROCH: =PROCH (valor_procurado; matriz_tabela; núm_índice_linha; procurar_intervalo) ✓ Valor_procurado: Esse campo determina qual é o valor a ser procurado. Você deve estabelecer uma célula em branco para digitar o valor. ✓ Matriz_tabela: Local onde o valor_procurado deverá ser procurado. Eles podem ser números, textos ou valores lógicos (verdadeiro ou falso). ✓ Núm_índice_linha: Em qual linha (1,2,3,4 ou mais) o valor estará. ✓ Procurar_intervalo: Esse último termo define se a fórmula deve procurar um valor exato ou próximo. No caso de um valor exato, é preciso digitar 0 (FALSO), enquanto um valor aproximado tem de se usar 1 (VERDADEIRO). Agora imagine que você deseja realizar uma busca do Total, através do nome da Empresa. Por exemplo, quando digitarmos a empresa Ponto Frio, deverá retornar o Total da mesma. Veja o exemplo: 35 Onde: =PROCH(C13;A2:E9;8;0) ✓ G7 – Determina o valor a ser procurado. Neste exemplo: empresa Ponto Frio; ✓ A2:E9 – Determina as células, onde será procurado o valor da célula C13, ou seja, Ponto Frio será procurado na planilha; ✓ 8 – Determina a linha (Total) que retorna como resultado. Observação: Lembrando a seleção das células começou a partir de A2, ou seja, da segunda linha, por isso que Total está na linha 8 do campo selecionado. ✓ 0 – Determina que o resultado é uma resposta exata. Isso se traduz como: O valor que será colocado na célula C13, será procurado nas células A2:E9 (a qual representa a planilha), onde deverá retornar como resposta a linha 8 (já que esta refere-se ao Total), retornando o resultado exato (por isso, finalizamos a função colocando 0). 3.2 Função ÍNDICE Retorna um valor ou a referência da célula na interseção de uma linha ou coluna específica, em um dado intervalo. 36 Sintaxe: =ÍNDICE(matriz;núm_linha;núm_coluna) ✓ Matriz – É um intervalo de células ou uma constante de matriz; ✓ Núm_linha – Seleciona a linha na matriz ou referência de onde um valor será retornado. Quando não especificado, núm_coluna é necessári; ✓ Núm_coluna - Seleciona a coluna na matriz ou referência de onde um valor será retornado. Quando não especificado, núm_linha é necessário. Exemplo: Você deseja realizar uma busca dos Gastos determinado por Código e Trimestre. Veja a planilha abaixo: Onde: =ÍNDICE(C3:F9;B12;C12) ✓ C3:F9 – Refere-se o campo de busca da matriz; ✓ B12 – Refere-se à linha da matriz, Código; ✓ C12 – Refere-se à coluna da matriz, Trimestres. 37 3.3 Funções Banco de Dados Termos Gerais Para compreendermos as principais funções de bancos de dados no Excel é preciso conhecer inicialmente alguns detalhes que são: 1. Toda função de banco de dados inicia-se a sigla BD (exemplo: BDSOMA, BDMÉDIA, BDEXTRAIR, entre outras). 2. Todas as funções de banco de dados no Excel possuem a mesma sintaxe. Sendo: Nome_da_função (Banco_de_dados; Campo; Critérios) Onde: ✓ Banco_de_dados – Refere-se a toda matriz onde será feita a análise dos dados. ✓ Campo: Indica a coluna que será utilizada na função. Neste caso pode ser expressa pelo nome da coluna entre aspas (“”), ou ainda pelo número (sem aspas) que representea posição da coluna dentro da lista: 1 para a primeira coluna, 2 para a segunda coluna e assim por diante. ✓ Critérios: Representa o intervalo de células que possui as condições específicas a serem pesquisadas. Exemplo Prático A planilha abaixo será nosso Banco de Dados. 38 Uso das Funções Com base no exemplo acima, vamos utilizar algumas funções para manipulação dos dados do banco. Neste caso temos as seguintes funções: 3.3.1 Função BDSOMA Realiza a soma de todos os argumentos especificados. Por exemplo, se quisermos saber a soma de Total em Vendas realizadas pelos vendedores de São Paulo, temos que ter uma área de consulta com os campos: Estado e Total em Vendas e especificar o estado de São Paulo como critério, como mostra o exemplo abaixo. Neste campo é realizado a consulta que desejamos do banco de dados. Onde: =BDSOMA(A1:C15;B1;E7:E8) ✓ A1:C15 – Refere-se a toda planilha, onde é realizada a análise dos dados; ✓ B1 – Refere-se ao campo, Total em Vendas, onde será realizado a soma dos valores contidos nesta coluna; ✓ E7:E8 – Refere-se ao critério especificado, Estado: São Paulo. No exemplo, a soma do Total em Vendas somente do Estado de São Paulo é: 77. 39 3.3.2 Função BDMÁX Retorna o maior valor de acordo com o critério especificado. Neste caso, deseja-se saber qual foi o maior valor do Total em Vendas realizado por um vendedor do Rio de Janeiro. Sendo assim, basta adequar o critério e inserir a função BDMÁX conforme mostrado abaixo: Onde: =BDMÁX(A1:C15;B1;E7:E8) ✓ A1:C15 – Refere-se a toda planilha, onde é realizada a análise dos dados; ✓ B1 – Refere-se ao campo, Total em Vendas, onde é analisado o maior valor contido nesta coluna; ✓ E7:E8 – Refere-se ao critério especificado, Estado: Rio de Janeiro. No exemplo, o maior valor do Total em Vendas somente do Estado de Rio de Janeiro é: 34. Observação: No Excel existe ainda a função BDMÍN que traz o valor mínimo dentro de um critério estipulado. A utilização desta função é do mesmo modo como é utilizado o BDMÁX, onde só muda a finalidade da função. 40 3.3.3 Função BDEXTRAIR Esta função extrai do banco de dados um único registro que corresponde às condições especificadas. Neste caso, desejamos extrair o Total em Vendas e o Estado de um vendedor selecionado no campo de critérios conforme mostrado abaixo: 1º) Vamos inicialmente extrair o Total em Vendas do vendedor selecionado; Onde: 41 =BDEXTRAIR(A1:C15;B1;E7:E8) ✓ A1:C15 – Refere-se a toda planilha, onde é realizada a análise dos dados; ✓ B1 – Refere-se ao campo, Total em Vendas, onde será extraído a informação; ✓ E7:E8 – Refere-se ao critério especificado, Nome: Alex. Resultado: Total em Vendas = 25. 2º) Agora, vamos extrair o Estado. Onde: =BDEXTRAIR(A1:C15;C1;E7:F8) ✓ A1:C15 – Refere-se a toda planilha, onde é realizada a análise dos dados; ✓ C1 – Refere-se ao campo, Estado, onde será extraído a informação; ✓ E7:F8 – Refere-se aos critérios especificados, Nome: Alex e Total em Vendas: 25. Resultado: Estado = ES. 3.3.4 Função BDCONTARA Esta função conta as células que contém números e caracteres (texto e símbolos) no campo onde será definido os critérios. 42 Podemos neste caso, contar quantos vendedores possuem no Estado de Rio de Janeiro. Deste modo, basta definirmos o critério (Estado = RJ) e posteriormente inserirmos a função BDCONTARA conforme mostrado abaixo: Onde: =BDCONTARA(A1:C15;A1;E7:E8) ✓ A1:C15 – Refere-se a toda planilha, onde é realizada a análise dos dados; ✓ A1 – Refere-se ao campo, Nome, onde será contado os vendedores; ✓ E7:F8 – Refere-se ao critério especificado, Estado: RJ. Resultado: 3 vendedores do Estado do Rio de Janeiro. Observação: No Excel existe ainda a função BDCONTAR que conta as células que contém somente números em seus registros. A utilização desta função é do mesmo modo como é utilizado BDCONTARA. 3.4 Dicas: Funções de Erro e Data 3.4.1 Função SEERRO A função SEERRO é útil para a expressão de erro, conhecida pela simbologia #DIV/0!. Existem outros tipos de erro como #N/D!, #VALOR!, #REF!, #DIV/0!, #NÚM!, #NOME? ou #NULO!, todos aceitos para aplicação da função. 43 Para esses casos, usando a função SEERRO, vamos determinar a mensagem de erro que desejamos exibir. =SEERRO(valor;valor_se_erro) Onde: ✓ Valor – Corresponde ao cálculo que será realizado ou a função que será aplicada; ✓ Valor_se_erro – Expressão, número ou símbolo que deverá ser retornado caso o cálculo resultar em erro. Segue um exemplo: Observe que aparece #DIV/0!, porque o valor da célula A1 = 60 é dividido pelo valor da célula A4 = 0. Não é possível dividir qualquer número por 0. E agora como resolver isso com SEERRO: 3.4.2 Função HOJE A função HOJE mostra o data atual. Sintaxe: =HOJE() 44 3.4.3 Função AGORA A função AGORA mostra a data e a hora atual. Sintaxe: =AGORA() Observação: Não podemos esquecer de abrir e fechar parênteses, pois os argumentos devem ser vazios. Se isso não for feito, será exibida uma mensagem de erro. Capítulo 4. Funções Financeiras 51 4.1 . Função PGTO 51 4.2 . Função TAXA 53 4.3 . Função NPER 54 4.4 . Função VP 56 4.5 . Função VF 58 4 46 4 Funções Financeiras 4.1 Função PGTO PGTO calcula o pagamento de um empréstimo de acordo com pagamentos constantes e com uma taxa de juros constante. Sintaxe: =PGTO(taxa;nper;vp;[vf];[tipo]) ✓ Taxa – Obrigatório. A taxa de juros para o empréstimo. ✓ Nper – Obrigatório. O número total de pagamentos pelo empréstimo. ✓ Vp – Obrigatório. O valor presente, ou a quantia total agora equivalente a uma série de pagamentos futuros; também conhecido como principal. ✓ Vf – Opcional. O valor futuro, ou o saldo, que você deseja obter depois do último pagamento. Se VF for omitido, será considerado 0 (o valor futuro de determinado empréstimo, por exemplo, 0). ✓ Tipo – Opcional. O número 0 (zero) ou 1 e indica o vencimento dos pagamentos. Definir Tipo Para Se Os Vencimentos Forem 0 ou omitido No final do período 1 No início do período Veja o exemplo a seguir: Onde: =PGTO(B2/12;B3;B4) ✓ B2/12 – Refere-se a taxa mensal; ✓ B3 – Refere-se ao número de parcelas mensais; ✓ B4 – Refere-se à quantia do empréstimo, ou seja, ao Valor Presente. 47 Observação I: Note que a célula B2 é dividida por 12, pois a taxa é anual e na função PGTO apenas admite taxas mensais, por isso, foi necessário acrescentar: B2/12. Observação II: Note que no exemplo não foram especificados os campos VF e Tipo. Logo, não foram necessários acrescentar na função. Lembrando que se o Tipo não for especificado, subtende-se que é 0, ou seja, com vencimento no fim do período. Veja abaixo os resultados com a função no campo Tipo omitido (0) e com Tipo: 1, ou seja, com vencimento no início do período. Tipo: 0 ou omitido – Vencimento no fim do período. Tipo: 1 – Vencimento no início do período. Observação III: Quando se utiliza a função PGTO, o resultado virá destacada em vermelho, negativo e, em algumas versões do Excel, com parênteses. Mas isso é apenas uma representação de que o valor da função é um pagamento. Para que o valor seja positivo, pode acrescentar à função PGTO a função ABS. O objetivo desta é apenas tornar o valor positivo. Veja o exemplo. 48 4.2 Função TAXA Retorna a taxa de juros por período de uma anuidade. TAXA é calculada por iteração e pode ter zero ou mais soluções. Se os resultados sucessivos de TAXA não convergirem para 0,0000001 depois de 20 iterações, TAXA retornará o valor de erro #NÚM!. Sintaxe: =TAXA(nper;pgto;VP;[vf];[tipo];[estimativa]) ✓ Nper – Obrigatório. O número total de períodos de pagamentoem uma anuidade. ✓ Pgto – Obrigatório. O pagamento feito em cada período e não pode mudar durante a vigência da anuidade. Geralmente, pgto inclui o principal e os juros e nenhuma outra taxa ou tributo. Se PGTO for omitido, você deverá incluir o argumento VF. ✓ Vp – Obrigatório. O valor presente — o valor total correspondente ao valor atual de uma série de pagamentos futuros. ✓ Vf – Opcional. O valor futuro, ou o saldo, que você deseja obter depois do último pagamento. Se VF for omitido, será considerado 0 (o valor futuro de um empréstimo, por exemplo, é 0). Se VF for omitido, deve-se incluir o argumento PGTO. ✓ Tipo – Opcional. O número 0 ou 1 e indica as datas de vencimento. Definir Tipo Para Se Os Vencimentos Forem 0 ou omitido No final do período 1 No início do período ✓ Estimativa – Opcional. A sua estimativa para a taxa. • Se você omitir estimativa, este argumento será considerado 10%. • Se TAXA não convergir, atribua valores diferentes para estimativa. Em geral, TAXA converge se estimativa estiver entre 0 e 1. Veja o exemplo a seguir: 49 Onde: =TAXA(B2*12;B3;B4) ✓ B2*12 – Refere-se a número de parcelas mensais; ✓ B3 – Refere-se ao pagamento mensal, ou seja, PGTO; ✓ B4 – Refere-se à quantia do empréstimo, ou seja, ao Valor Presente. Observação I: Note que a célula B2 é multiplicada por 12, pois a número de parcelas é anual e na função TAXA apenas admite quantidade de parcelas mensais, por isso, foi necessário acrescentar: B2*12. Observação II: O campo PGTO é inserido com o valor em negativo. 4.3 Função NPER Retorna o número de períodos para investimento de acordo com pagamentos constantes e periódicos e uma taxa de juros constante. Sintaxe: =NPER(taxa;pgto;vp;[vf];[tipo]) Para obter uma descrição completa dos argumentos em NPER e sobre as funções de anuidade, consulte VP. ✓ Taxa – Obrigatório. A taxa de juros por período. ✓ Pgto – Necessário. O pagamento feito em cada período; não pode mudar durante a vigência da anuidade. Geralmente, PGTO contém o capital e os juros, mas nenhuma outra tarifa ou taxas. ✓ Vp – Obrigatório. O valor presente ou atual de uma série de pagamentos futuros. ✓ Vf – Opcional. O valor futuro, ou o saldo, que você deseja obter depois do último pagamento. Se VF for omitido, será considerado 0 (o valor futuro de um empréstimo, por exemplo, é 0). 50 ✓ Tipo – Opcional. O número 0 ou 1 e indica as datas de vencimento. Definir Tipo Para Se Os Vencimentos Forem 0 ou omitido No final do período 1 No início do período Veja o exemplo a seguir: Onde: =NPER(B2/12;B3;B4;B5;B6) ✓ B2/12 – Refere-se a taxa de juros mensal; ✓ B3 – Refere-se ao pagamento mensal, ou seja, PGTO; ✓ B4 – Refere-se ao Valor Presente; ✓ B5 – Refere-se ao Valor Futuro; ✓ B6 – Refere-se Tipo do Vencimento do Período (início do período). Observação I: Note que a célula B2 é dividida por 12, pois a taxa está anual e na função NPER apenas admite taxas mensais, por isso, foi necessário inserir: B2/12. Resultado: 51 Observação: Os campos PGTO e VP (Valor Presente) são inseridos com o valor em negativo. 4.4 Função VP VP calcula o valor presente de um empréstimo ou investimento com base em uma taxa de juros constante. Você pode usar VP com pagamentos periódicos e constantes (como uma hipoteca ou outro empréstimo) ou um valor futuro que é sua meta de investimento. Sintaxe: =VP(taxa;nper;pgto;[vf];[tipo]) ✓ Taxa – Necessário. A taxa de juros por período. Por exemplo, se você tiver um empréstimo para um automóvel com taxa de juros de 10% ano e fizer pagamentos mensais, sua taxa de juros mensal será de 10%/12 ou 0,83%. Você deveria inserir 10%/12 ou 0,83%, ou 0,0083, na fórmula como taxa. ✓ Nper – Necessário. O número total de períodos de pagamento em uma anuidade. Por exemplo, se você conseguir um empréstimo de carro de quatro anos e fizer pagamentos mensais, seu empréstimo terá 4*12 (ou 48) períodos. Você deveria inserir 48 na fórmula para NPER. ✓ Pgto – Necessário. O pagamento feito em cada período e não pode mudar durante a vigência da anuidade. Geralmente, PGTO inclui o principal e os juros e nenhuma outra taxa ou tributo. Por exemplo, os pagamentos mensais de R$ 10.000 de um empréstimo de quatro anos para um carro serão de R$ 263,33. Você deveria inserir -263,33 na fórmula como pgto. Se PGTO for omitido, você deverá incluir o argumento VF. ✓ Vf – Opcional. O valor futuro, ou o saldo, que você deseja obter depois do último pagamento. Se VF for omitido, será considerado 0 (o valor futuro de um empréstimo, por exemplo, é 0). Por exemplo, se você deseja economizar R$ 50.000 para pagar um projeto especial em 18 anos, então o valor futuro será de R$ 50.000. Você poderia então fazer uma estimativa conservadora na taxa de juros e concluir quanto economizaria por mês. Se VF for omitido, você deverá incluir o argumento PGTO. ✓ Tipo – Opcional. O número 0 ou 1 e indica as datas de vencimento. 52 Definir Tipo Para Se Os Vencimentos Forem 0 ou omitido No final do período 1 No início do período Veja o exemplo a seguir: Onde: =VP(B3/12;B4*12;-B2;0;0) ✓ B3/12 – Refere-se a taxa de juros mensal; ✓ B4*12 – Refere-se ao número de parcelas mensal, ou seja, NPER; ✓ -B2 – Refere-se ao PGTO; ✓ 0 – Refere-se ao Valor Futuro; ✓ 0 – Refere-se Tipo do Vencimento do Período (fim do período). Observação I: Note que a célula B3 é dividida por 12, pois a taxa é anual e na função VP apenas admite taxas mensais, por isso, foi necessário acrescentar: B3/12. Observação II: Note que a célula B4 é multiplicada por 12, pois a número de parcelas é anual e na função VP apenas admite quantidade de parcelas mensais, por isso, foi necessário acrescentar: B4*12. Observação III: O campo PGTO pode ser inserido com o valor em negativo (-B2). 53 Resultado: 4.5 Função VF VF calcula o valor futuro de um investimento com base em uma taxa de juros constante. Você pode usar VF com pagamentos periódicos e constantes ou um pagamento de quantia única. Sintaxe: =VF(taxa;nper;pgto;[vp];[tipo]) Para obter uma descrição completa dos argumentos em VF e para obter mais informações sobre as funções de anuidade, consulte VP. ✓ Taxa – Obrigatório. A taxa de juros por período. ✓ Nper – Obrigatório. O número total de períodos de pagamento em uma anuidade. ✓ Pgto – Obrigatório. O pagamento feito a cada período; não pode mudar durante a vigência da anuidade. Geralmente, PGTO contém o capital e os juros e nenhuma outra tarifa ou taxas. Se PGTO for omitido, você deverá incluir o argumento VP. ✓ Vp – Opcional. O valor presente ou a soma total correspondente ao valor presente de uma série de pagamentos futuros. Se VP for omitido, será considerado 0 (zero) e a inclusão do argumento PGTO será obrigatória. ✓ Tipo – Opcional. O número 0 ou 1 e indica as datas de vencimento dos pagamentos. Se tipo for omitido, será considerado 0. 54 Definir Tipo Para Se Os Vencimentos Forem 0 ou omitido No final do período 1 No início do período Veja o exemplo a seguir: Onde: =VF(B2/12;B3;B4;B5;B6) ✓ B2/12 – Refere-se a taxa de juros mensal; ✓ B3 – Refere-se ao número de parcelas mensal, ou seja, NPER; ✓ B4 – Refere-se ao valor do pagamento, ou seja, PGTO; ✓ B5 – Refere-se ao Valor Presente; ✓ B6 – Refere-se Tipo do Vencimento do Período (início do período). Observação I: Note que a célula B2 é dividida por 12, pois a taxa é anual e na função VF apenas admite taxas mensais, por isso, foi necessário acrescentar: B2/12. Resultado: 55 Observação II: Note que os campos PGTO e VP (Valor Presente) já estão com os valores negativos. Capítulo 5. Ordenação de Dados 63 5.1. Formatação Condicional 63 5.2. Lista Suspensa 66 5.3. Localizar e Substituir Registro 68 5.4. Classificação 70 5.5. Subtotal71 5.6. Auto Filtro 73 5.7. Filtro Avançado 75 5 57 5 Ordenação de Dados Vamos conhecer agora alguns procedimentos que podem ser adotados no Excel para melhor a visualização e compreensão dos dados da planilha. 5.1 Formatação Condicional No Excel, há a possibilidade de aplicar formatação de acordo com uma condição. Veja o exemplo: Para realçar os valores com cores do mais baixo ao mais alto, você pode usar a formatação condicional. 1) Selecione os valores. Na guia Página Inicial, clique no botão Formatação Condicional e em Escala de Cor, escolha a opção desejada. Como sugestão, a segunda opção da Escala de Cor fará com que os preços já fiquem realçados. 2) Aqui está o resultado da imagem: 58 A formatação condicional também pode ser aplicada a textos, como é o caso do exemplo com um projeto onde duas etapas estão atrasadas. 1) Selecione as células do campo Status. Na guia Página Inicial, clique no botão Formatação Condicional, em Realçar Regras das Células, escolha a opção: É Igual a. 2) Na janela, digite: Atrasado e clique na setinha do campo Preenchimento Vermelho Claro e Texto Vermelho Escuro para abrir as outras opções de formatações. 59 3) Clique na opção: Formato Personalizado. 4) Na janela que será aberta, você deverá definir que cor a célula ficará destacada. Você pode definir as formatações que desejar. Neste exemplo, foi utilizado Preenchimento laranja. Clique em OK para confirmar. Aqui está o resultado: Observação: Caso deseje retirar a formatação, no botão Formatação Condicional, clique em Limpar Regras e escolha a opção: Limpar Regras das Células Selecionadas. 60 5.2 Lista Suspensa O Excel permite criar uma lista suspensa para controlar o tipo de dados ou os valores que os usuários inserem em uma célula. Por exemplo, se você possui uma lista de funcionários e pretende atribuir um departamento ao lado de cada nome dos empregados, pode-se utilizar uma lista suspensa ao invés de digitar os departamentos. Abaixo está um exemplo de como usar esta opção de Validação de Dados. 1) Na Planilha 1, digite como na figura abaixo. Após digitar, selecione o intervalo (A1:A7), digite “Departamento” na Caixa de Nome e pressione ENTER para confirmar. 2) Na Planilha 2, faça a seguinte planilha abaixo e selecione as células do campo Departamento (D3:D10). https://dl.dropboxusercontent.com/u/17327855/erredoze/postagens/validacao_lista/valida01.PNG 61 3) Na guia Dados, clique no botão Validação de Dados. 4) Na janela Validação de Dados, na guia Configurações, clique no campo: Permitir e escolha a opção: Lista; no campo: Fonte, digite o nome do intervalo que você criou anteriormente, =Departamento. Depois confirme. 5) Insira as informações dos funcionários. Para escolher o departamento selecione na lista suspensa de departamentos que será mostrada. 62 Observação: Se você tentar inserir um departamento que não faça parte da lista de Departamentos, o sistema não aceitará e aparecerá uma mensagem de erro. É possível personalizar a mensagem de erro e o tipo de alerta acessando a guia Alerta de Erro na tela de Validação de Dados. 5.3 Localizar e Substituir Registro Estes recursos são utilizados quando se deseja localizar um dado dentro da planilha (imagina uma planilha com 500 linhas e você tivesse que localizar onde a expressão “Rio de Janeiro” aparece, olhar uma a uma é totalmente inviável) ou alterar um valor pelo outro (esses valores podem ser texto, números, porcentagem, datas). Antes de começar, vamos “adiantar” os atalhos do teclado: CTRL + L para Localizar e CTRL + U para Substituir. É possível ainda selecionar esta opção na guia Página Inicial. No final da mesma aparece uma opção chamada Localizar e Selecionar (tem o desenho de Lupa), e nele existem as opções de Localizar e Substituir: http://www.feranoexcel.com/2012/02/trabalhando-com-datas-no-excel/ http://www.feranoexcel.com/2012/09/teclas-de-atalho-excel-parte-1/ http://www.feranoexcel.com/2012/09/teclas-de-atalho-excel-parte-1/ http://www.feranoexcel.com/2012/09/teclas-de-atalho-excel-parte-1/ http://www.feranoexcel.com/2015/01/como-criar-guias-personalizadas-excel/ 63 Note que as duas opções estão na mesma janela, em guias diferentes. Caso deseje alternar entre uma e outra, não há necessidade de fechar e abrir a outra. Basta mudar de guia. Localizar – Podemos achar dados dentro da planilha. Você precisa digitar o que deseja buscar na Caixa Localizar. Após isso, clique em Localizar Próxima ou em Localizar Tudo. ✓ Localizar Próxima – Cada vez que se clica nesta opção o Cursor irá para a próxima célula que contém o valor procurado, caso haja. Se não houver, o Excel exibe uma mensagem dizendo que não há mais o valor procurado; ✓ Localizar Tudo – Exibe um “relatório” na própria janela com todas as células, uma em cada linha. Substituir – Permite que você troque valores rapidamente na planilha. Vamos supor que você quer substituir “São Paulo” por “SP”. Você digita o valor “São Paulo” na caixa Localizar e “SP” na caixa Substituir por. Após isso, dependendo do que você deseja fazer, você irá selecionar um dos 4 botões: ✓ Localizar Próxima – É usado em conjunto com o Substituir, para casos que você não deseja substituir todos os valores. Ex: um valor aparece 10 vezes e você deseja substituir apenas 6. Então você clica em Localizar Próxima. Irá exibir o primeiro resultado. Se você desejar alterar esse, clique em Substituir. 64 Caso contrário clique em Localizar Próxima novamente e este valor irá permanecer como está e avançará para o próximo registro. Você irá fazer um por um nesse caso. ✓ Localizar Tudo – O Excel irá exibir os valores localizados como explicado anteriormente. Após isso, pode-se selecionar o(s) valor(es) desejado(s), usando-se o CTRL ou o SHIFT (o primeiro para seleções não sequenciais e o segundo para seleções contínuas, onde todos os valores irão ser selecionados). Depois clique no Substituir. ✓ Substituir Tudo – Troca TODOS os valores localizados na planilha. No final ele mostra o total de substituições feitas. 5.4 Classificação Para melhor visualização e compreensão, podemos ainda utilizar a ferramenta Classificar e Filtrar localizada na guia Página Inicial. Deste modo, basta selecionar qualquer célula com valores na tabela e escolher a opção para ordenar de formar crescente (Classificar de A a Z) ou decrescente (Classificar de Z a A). Se o campo a ser classificado for de números, estão aparecerá: Classificar do Menor para o Maior e Classificar do Maior para o Menor. Veja o exemplo: 65 Ao executar a classificação: Classificar do Menor para o Maior, note que a tabela é ordenada de acordo com a ordem de chegada de cada atleta. Observação: Além da classificação Crescente e Decrescente, há uma terceira classificação: Personalizar Classificação. O qual permite ao usuário adicionar mais um nível e ordem de classificação. 5.5 Subtotal Vamos fazer a planilha a seguir para uma melhor compreensão. 1) Selecione a sua planilha a partir dos cabeçalhos e clique na guia Dados, em seguida: Subtotal. 66 2) Agora escolha os itens que usará para criar o subtotal. No exemplo, determinamos que será realizado um subtotal: a) A cada alteração em: Produto; b) Adicionar subtotal a: Valor. Observe como ficou a planilha: Observação: Para remover o subtotal, basta selecionar sua planilha, voltar ao botão Subtotal e clicar na opção: Remover todos. 67 5.6 Auto Filtro O primeiro passo é verificar se os dados da planilha estão organizados em forma de tabela, ou seja, todas as colunas possuem um título. Para que o filtro funcione corretamente é importante certificar-se de que não existe nenhuma coluna ou linha totalmente em branco. 1) Para aplicar oAuto Filtro clique sobre um dos títulos da planilha e então na guia Dados, na seção Classificar e Filtrar clique no botão Filtro. O Filtro também pode ser encontrado na guia Página Inicial. 2) Então as setas de Auto Filtro aparecem à direita do título de cada coluna. Desta forma basta clicar sobre uma das setas para ver as opções de filtragem. 68 3) Após aplicar o Auto Filtro o Excel permite filtrar as colunas individualmente. Por exemplo, vamos filtrar os clientes de uma região específica, clique na seta de Auto Filtro na coluna Região para ver uma lista alfabética dos registros desta coluna; 4) Clique em Selecionar Tudo para desmarcar todas as regiões; 5) Clique sobre a Região que deseja filtrar. Por exemplo, escolha a região: Nordeste; 6) Clique em OK para visualizar apenas os dados da região selecionada. 69 Observação I: Após a execução do filtro é possível observar algumas mudanças na planilha: a) A seta de Auto Filtro na coluna Região mudou para um símbolo de filtro, com uma pequena seta à direita da coluna; b) Os números das linhas visíveis da planilha mudaram indicando que foram filtradas. Dica: Depois de filtrar uma coluna, o Excel permite refinar os resultados obtidos. Para isso, basta aplicar o auto filtro à uma ou mais colunas até encontrar a visualização desejada. Observação II: Para limpar um filtro, clique na seta de Auto Filtro da coluna que quer remover o filtro, em seguida, clique em Limpar Filtro. 5.7 Filtro Avançado Agora começaremos a configurar o filtro avançado utilizando a planilha abaixo. 70 1) Primeiro copie o cabeçalho da base de dados e cole em 2 locais diferentes. O primeiro local será onde vamos fazer a busca e o segundo local será a busca resultante. 2) Selecione o primeiro local copiado e mais a linha vazia abaixo dele, depois vá na guia Dados e em seguida, Filtro Avançado. 3) Na caixa que abrir vamos selecionar o seguinte: a) Intervalo de Lista: Selecione o conjunto de dados onde será feita a busca, inclusive o cabeçalho, ou seja, selecione toda sua planilha; 1 2 71 b) Intervalo de Critérios: Aqui vamos selecionar o cabeçalho da primeira “colada”, ou seja, aquela que, no passo 1, corresponde à busca e, mais uma linha abaixo dela; c) Copiar para: Para que esta caixa de seleção fique disponível selecione em Copiar para outro local, e então, selecione a segunda “colada” do passo 1, e aqui, somente o cabeçalho, sem a linha seguinte. Observação: Ao confirmar a janela do Filtro Avançado, você observou que na busca apareceu todos os resultados, não é mesmo? Isso porque deixamos os critérios de busca em branco. 4) Limpe a área de resultado e vamos inserir um nome como critério de busca e fazer o mesmo processo, voltando à janela do Filtro Avançado. As opções da caixinha já estarão preenchidas, então é só clicar em OK. Veja o resultado: 1 3 2 72 5) Quer mais de um critério? Limpe a área de resultado, coloque outros critérios no campo de Critério e volte a janela do Filtro Avançado. O filtro avançado suporta até 6 deles. Caracteres matemáticos também funcionam, veja o exemplo: Procuraremos por valores maiores que 2800 reais no mês de janeiro do vendedor Márcio. Capítulo 6. Proteção de Planilhas e Células 81 6.1. Proteção de Planilhas 81 6.2. Proteção de Células 82 6.3. Proteção de Pastas de Trabalho 84 6.4. Proteção de Arquivos 85 6 74 6 Proteção de Planilhas e Células 6.1 Proteção de Planilhas Nesta modalidade de restrição, o usuário poderá visualizar o arquivo, porém ficará restrito de realizar alterações. Veja como aplicar este tipo de proteção. 1) Com a planilha aberta, acesse a guia Revisão e, em seguida, a opção: Proteger Planilha. 2) Será aberta uma nova janela conforme abaixo: Note que apesar da proteção com senha, ainda é possível criar exceções de proteção marcando as caixas de permissões. Ou seja, podemos permitir que o usuário formate células, porém, sem excluir linhas, por exemplo. Para o exemplo acima, vamos deixar todas as caixas desmarcadas e clicar em OK para aplicar a proteção. 3) Será solicitada a confirmação da senha, sendo necessário inseri-la novamente. Clique em OK. 75 Note que não é possível realizar nenhuma modificação na planilha. Deste modo, para esta modalidade de proteção o usuário tem somente acesso à visualização do arquivo. Para remover a proteção: 1) Basta acessar a guia Revisão e selecionar a opção: Desproteger Planilha. 2) Será solicitada novamente a senha que foi inserida, bastando inseri-la no campo indicado e clicar em OK. A planilha estará liberada para edição. 6.2 Proteção de Células Há casos em que uma mesma planilha é dividida por diversos setores de uma empresa. Deste modo, torna-se viável a proteção de células para que estas não possam ser editadas por outros departamentos. Sendo assim, podemos aplicar proteções individuais nas células desejadas. Veja o procedimento. 1) O primeiro passo é selecionar as células que estão liberadas para os demais usuários. Deste modo, após selecionarmos, basta clicar com o botão direito do mouse sobre as células selecionadas e escolher a opção: Formatar Células. 76 2) Com a janela de formatação de células aberta, acesse a guia Proteção, desmarque a opção: Bloqueadas e clique em OK. 3) Em seguida, acesse a guia Revisão e clique na opção: Proteger Planilha. 4) Na tela que se abrirá, insira uma senha, marque a opção: Selecionar células desbloqueadas e clique em OK. 77 5) Será solicitada a confirmação da senha, insira novamente e clique em OK. Deste modo, note que toda planilha estará bloqueada para edição, exceto as células que definimos como desbloqueadas. Este recurso é muito útil quando se deseja bloquear também fórmulas nas planilhas, fazendo com que o acesso seja restrito. 6.3 Proteção de Pastas de Trabalho É possível ainda restringir a criação, exclusão, edição e posição das guias de planilhas conhecidas também como pastas de trabalho. 1) Para isso, basta acessar a guia Revisão e escolher a opção: Proteger Pasta de Trabalho. 2) Abrirá uma nova janela, onde deve-se inserir uma senha caso necessário e clicar em OK. 78 3) Aparecerá a janela para confirmar a senha. Após a confirmação, clique em OK. Deste modo, suas pastas de trabalho estão bloqueadas para criar, excluir, editar e posicionar as guias de trabalho. Para remover a proteção, basta voltar a guia Revisão, clicar novamente no botão: Proteger Pasta de Trabalho e confirmar a senha. 6.4 Proteção de Arquivo 1) Na guia Arquivo, clique em Salvar como. Na janela Salvar como, clique na opção Ferramentas, em seguida, clique em Opções gerais. 2) Na janela Opções Gerais, há 2 tipos de senha que podem ser adicionadas ao arquivo: 79 a) Senha de Proteção: Impede o usuário de abrir o arquivo. b) Senha de Gravação: Impede o usuário de alterar o arquivo. Neste exemplo, será utilizada a senha de proteção, que impede o usuário de abrir e até mesmo visualizar o arquivo, há não ser, claro, que tenha a senha. 3) Após digitar a senha de proteção, clique em OK. Depois basta confirmar a senha. 4) Feche o arquivo e abra-o novamente, observe que a senha é requisitada. Como podemos observar, o Excel traz diversas opções de proteção para planilhas, células e pastas de trabalho. Deste modo, o uso de tais recursos trará maior segurança das informações contidas nos arquivos e podem ser aplicados de acordo com a necessidade do usuário. Capítulo 7. Tabela Dinâmica e Gráfico Dinâmico 89 7.1. Tabela Dinâmica 89 7.1.1. Conectando Tabelas Dinâmicas 91 7.2. Gráfico Dinâmico 94 7 81 7 Tabela e Gráfico Dinâmico 7.1 Tabela Dinâmica Tabelas dinâmicassão um dos recursos mais poderosos do Excel. A tabela dinâmica é um resumo do conjunto de dados de uma planilha muito grande, permitindo que você visualize as informações de forma clara e detalhada. Para demonstrar uma visão geral das tabelas dinâmicas, iremos utilizar o exemplo o exemplo abaixo. 1) Clique em qualquer célula do conjunto de dados. 2) Na guia Inserir, clique em Tabela Dinâmica. 82 3) A caixa de diálogo será exibida. O Excel seleciona automaticamente os dados para você. O local padrão para uma nova tabela dinâmica é numa nova planilha. Clique em OK. 4) A lista de Campos da Tabela Dinâmica aparecerá. Para saber o valor total exportado de cada produto, clique e arraste os seguintes campos: a) Arraste o campo Produto para a área Linhas; b) Arraste o campo Valor para a área Valores; c) Arraste o campo País para a área Filtros. 83 5) Temos a seguinte visualização da Tabela Dinâmica: 6) Com a tabela dinâmica você pode formatar os valores e aplicar filtros nos campos desejados, por exemplo, visualizar os dados somente de Chile. 7.1.1 Conectando Tabelas Dinâmicas No Excel podemos conectar duas ou mais Tabelas Dinâmicas da mesma fonte ou de outras segmentações de dados. Vamos continuar com a mesma tabela dinâmica criada anteriormente e gerar uma nova tabela dinâmica a partir da mesma fonte de dados. 1) Volte para a planilha original e clique em qualquer célula da planilha para inserir uma nova Tabela Dinâmica; 2) Na guia Inserir, clique em Tabela Dinâmica; 84 3) Na caixa de diálogo, Criar Tabela Dinâmica, marque a opção: Planilha Existente e clique em alguma célula da planilha onde foi gerada a primeira tabela dinâmica. Logo em seguida, clique em OK. 4) Na lista de Campos da Tabela Dinâmica clique e arraste os seguintes campos: a) Arraste o campo Data para a área Linhas; d) Arraste o campo Valor para a área Valores; e) Arraste o campo País para a área Filtros. Nessas tabelas, temos o total vendido de cada Produto e o total vendido de cada Mês. Vamos simular uma situação em que precisamos das informações das duas tabelas filtradas por Categoria. 85 5) Clique em qualquer lugar da Tabela Dinâmica; 6) Em seguida, na guia Analisar, clique na opção: Inserir Segmentação de Dados; 7) Na caixa de diálogo exibida, marque o campo: Categoria e clique em OK. 8) Aparecerá a seguinte janela, contendo os botões das Categorias. 9) Note que, quando algum botão da segmentação de dados é acionado, apenas uma das tabelas dinâmicas é alterada. Para alterar nas duas tabelas dinâmicas, vá na guia Opções (Ferramentas de Segmentação de Dados) e clique no botão Conexões de Relatório. 86 10)Na janela seguinte, marque as duas tabelas dinâmicas e clique em OK. 11)Pronto! Clique em qualquer categoria da segmentação adicionada. Observe que agora as duas tabelas dinâmicas serão afetadas pela filtragem. 7.2 Gráfico Dinâmico Vamos utilizar como exemplo a planilha abaixo para a criação de um Gráfico Dinâmico. 87 1) Selecione os dados da planilha. 2) Clique na guia Inserir, clique na opção Gráfico Dinâmico. 88 3) Aparecerá uma nova janela mostrando os dados selecionados e para a escolha do local para a criação do Gráfico Dinâmico. Selecione Nova Planilha. 4) A lista de Campos do Gráfico Dinâmico aparecerá. Utilize apenas alguns dos campos para tornar mais interessante a criação do gráfico. Por exemplo, clique e arraste os seguintes campos: a) Arraste o campo Vendedor para a área Eixo (Categorias); b) Arraste o campo Quantidade Vendida para a área Valores; c) Arraste o campo Cidade para a área Legenda (Série). 89 5) O gráfico está pronto, basta agora verificar e filtrar as informações se achar necessário. 90 Capítulo 8. Macro e Formulário 99 8.1. Macro 99 8.1.1. Criando Macro 99 8.2. Formulário 101 8 91 8 Macro e Formulário 8.1 Macro Macros também podem ser aplicadas para uma planilha, assim como uma macro pode ser aplicada à um documento no Word. Elas são ações graváveis que facilitam ou aceleram o uso de certas ações ou conjunto de propriedades que podem ser adicionadas a uma planilha. 1) Antes de começar a gravar uma macro, devemos verificar se a guia Desenvolvedor está visível na faixa de opções. Por padrão, a guia Desenvolvedor não permanece visível. Sendo assim, devemos exibi-lá. Clique na guia Arquivo, clique em Opções e depois clique na categoria Personalizar Faixa de Opções. 2) Em Personalizar a Faixa de Opções, na lista Guias Principais, clique em Desenvolvedor e, logo após, em OK. 8.1.1 Criando Macro 1) Na guia Desenvolvedor, clique no botão: Gravar Macro. 2) Digite um nome para macro e caso queira defina um atalho para a mesma. Logo após, clique em OK. 92 3) Neste momento a macro está sendo gravada, esperando a sua ação. Neste exemplo, queremos que a macro, apague qualquer valor na célula A1. Sendo assim, clique na célula A1 e pressione DELETE em seu teclado para a macro entender a ação. Pronto, agora é necessário Parar a gravação, para que nenhuma outra ação seja incorporada a esta Macro. 4) Na guia Desenvolvedor, clique no botão: Parar Gravação. 5) Pronto! A macro foi gravada. Para deixá-la mais interessante, podemos utilizar botões de formulário ou simplesmente formas para executar a macro. Neste exemplo, vamos utilizar um botão de formulário. Na guia Desenvolvedor, clique em Inserir e, em seguida, na opção: Botão (Controle de Formulário). 6) Clique e arraste na planilha para desenhar um botão. Após isso, aparecerá uma tela, onde você poderá encontrar a Macro criada previamente. 7) Clique na Macro e depois em OK. 93 8) O botão está vinculado a Macro. Altere o texto do botão, clicando com o botão direito do mouse sobre e, em seguida, na opção: Editar Texto. 9) Agora vamos testar. Digite algo na célula A1, clique no botão e veja o resultado. 8.2 Formulário Formulário, seja impresso ou online, é um documento projetado com uma estrutura e um formato padrão que facilita a captura, a organização e a edição de informações. Você pode usar formulários e adicionar diversos controles e objetos para melhorar a entrada de dados de forma significativa e aprimorar o modo de exibição das planilhas. Controles de Formulário e ActiveX Uma planilha é um tipo de formulário com o qual você pode inserir e exibir dados na grade, e existem diversos recursos ao estilo de controles já integrados a planilhas do Excel, como comentários e validação de dados. Células se assemelham a caixas de texto no sentido de que você pode inseri-las e formatá-las de várias maneiras. 94 Células são muitas vezes usadas como rótulos e, ao mesclar células e ajustar sua altura e largura, você pode fazer com que uma planilha se comporte como um formulário de entrada de dados simples. Outros recursos ao estilo de controles, como Comentários de células, Hiperlinks, Imagens de Fundo, Validação de Dados, Formatação Condicional, Gráficos inseridos e o Filtro Automático, podem fazer com que uma planilha se comporte como um formulário avançado. Para obter mais flexibilidade, você pode adicionar controles e outros objetos de desenho à tela de desenho de uma planilha e combiná-los e coordená-los com as células dessa planilha. Por exemplo, você pode usar um controle de caixa de listagem para permitir que um usuário faça uma seleção mais facilmente a partir de uma lista de itens. Mas se preferir, use um controle de botão de rotação para facilitar a inserção de um número. Como controles e objetos são armazenados na tela de desenho, é possível exibi-los ou visualizá-los junto com o texto associado, que não depende de limites de linha e coluna, sem alteraro layout de uma grade ou tabela de dados na planilha. Na maioria das vezes, muitos desses controles também podem ser vinculados a células na planilha. Você pode definir propriedades que determinam se um controle flutua livremente (Controles ActiveX) ou se é movido e redimensionado junto com uma célula (Controles de Formulário). Por exemplo, talvez exista uma caixa de seleção que você deseja mover junto com a célula subjacente quando o intervalo é classificado. Entretanto, se houver uma caixa de listagem que você deseja manter sempre em um local específico, provavelmente não será interessante movê-la junto com sua célula subjacente. O Excel tem dois tipos de controles: Controles de Formulário e Controles ActiveX. Além desses conjuntos de controles, você também pode adicionar objetos das ferramentas de Desenho, como Formas, WordArt, um gráfico SmartArt ou Caixas de Texto. Controles de Formulário Controles de formulário são os controles originais que são compatíveis com versões anteriores do Excel, começando com a versão 5.0. Esses controles também foram projetados para uso em planilhas de macros XLM. 95 Use Controles de Formulário quando desejar fazer referência a dados de célula e interagir com eles facilmente. Resumo de Controles de Formulário Nome do Botão Exemplo Descrição Botão Permite ao usuário realizar uma ação da macro quando clica no botão. Um Botão também é chamado de Botão de Ação. Caixa de Combinação Combina uma caixa de texto com uma caixa de listagem de modo a criar uma caixa de listagem suspensa. Uma caixa de combinação é mais compacta do que uma caixa de listagem, mas requer que o usuário clique na seta para baixo para exibir a lista de itens. Caixa de Seleção Você pode marcar mais de uma caixa de seleção em uma planilha ou em uma caixa de grupo. Uma caixa de seleção pode ter um destes três estados: marcada (ativada), desmarcada (desativada) e mista, significando uma combinação dos estados ativado e desativado (como em uma seleção múltipla). Botão de Rotação Aumenta ou diminui um valor, como um incremento de número, um horário ou uma data. Para aumentar o valor, clique na seta para cima e, para diminuí-lo, clique na seta para baixo. Normalmente, um usuário também pode digitar um valor de texto diretamente em uma célula ou caixa de 96 texto associada. Caixa de Listagem Exibe uma lista de um ou mais itens de texto na qual um usuário pode escolher uma opção. Use uma caixa de listagem para exibir diversas opções que variam em número ou conteúdo. Existem três tipos de caixas de listagem: ✓ Seleção Única – Permite apenas uma seleção. ✓ Seleção Múltipla – Permite uma opção ou opções contíguas (adjacentes). ✓ Seleção Estendida – Permite uma opção, opções contínua e opções não contínuas (ou disjuntas). Botão de Opção Permite uma única opção dentro de um conjunto limitado de opções exclusivas. Um botão de opção está geralmente contido em uma caixa de grupo ou em um quadro, podendo ter um destes três estados: marcado (ativado), desmarcado (desativado) e misturado, significando uma combinação dos estados ativado e desativado (como em uma seleção múltipla). Um botão de opção também é chamado de botão de rádio. Caixa de Grupo Controles relacionados a grupos em uma unidade visual de um retângulo com um rótulo adicional. Em geral, botões de opção, caixas de seleção ou itens de conteúdo 97 intimamente relacionados são agrupados. Rótulo Identifica a finalidade de uma célula ou caixa de texto ou exibe texto descritivo (como títulos, legendas, imagens) ou breves instruções. Barra de Rolagem Percorre um intervalo de valores quando você clica nas setas de rolagem. Normalmente, um usuário também pode digitar um valor de texto diretamente em uma célula ou caixa de texto associada. Observação: Os modos de uso dos Controles de Formulário são bastante simples. Após utilizar o controle desejado, vá na guia Desenvolvedor e clique no botão Propriedades para configurá-lo. Adicionando uma Caixa de Combinação 1) Crie a seguinte planilha e renomeie a guia da pasta de trabalho para o nome Cadastro; 2) Em seguida crie em outra guia a planilha abaixo com o nome Resumo; Perceba, que nesta imagem, criamos uma planilha de controle de carros, com modelo, marca, combustível utilizado, preço e as regras para aumento de preços baseadas no tipo de combustível usado para cada modelo do carro. No espaço maior iremos inserir uma 98 Caixa de Combinação e nos outros espaços em brancos as funções necessárias. 3) Clique na guia Desenvolvedor e escolha a opção: Inserir, em seguida Caixa de Combinação (Controle de Formulário). 4) Crie sua caixa de controle em qualquer parte da planilha. Se quiser, pode desenhá-la no espaço maior destacado na planilha; 5) Clique no botão Propriedades para editar caixa de controle; 6) Use a Caixa de Combinação e Controle de Formulário, para selecionar sua busca: a) Intervalo de entrada: selecione os registros da coluna Nome, da planilha Cadastro; b) Vínculo da célula: clique na célula A1 da planilha Resumo. Logo 99 após, confirme a janela. 7) Nas células em branco, insira as seguintes fórmulas: a) Marca: Na célula B2, digite: =PROCV(A1;Cadastro!A5:F9;5;0) b) Combustível: Na célula B3, digite: =PROCV(A1;Cadastro!A5:F9;3;0) c) Preço: Na célula B4, digite: =PROCV(A1;Cadastro!A5:F9;4;0) d) Preço com Aumento: Na célula B5, digite: =PROCV(A1;Cadastro!A5:F9;6;0) e) Aumento (%): Na célula B6, digite: =PROCV(B3;Cadastro!H5:I8;2;0) f) Aumento (R$): Na célula B7, digite: =B4*B6 Veja o resultado: Observação: Nós criamos uma caixa de controle de formulário, para selecionar o item de referência para nossa pesquisa. Outra opção é a Validação de Dados (do tipo Lista) que pode substituir a Caixa de Combinação (Controle de Formulário). 100 9 Referências Disponível em: http://blog.luz.vc/excel/funcao-se-excel/>. Acesso em 15 Abr. 2021. Disponível em: http://www.aprenderexcel.com.br/2013/tutoriais/funcao-se-/>. Acesso em 30 Abr. 2021. Disponível em :http://www.cavalcanteassociados.com.br/article.php?id=11/> Acesso em 26 Abr. 2021 Disponível em: http://www.tecmundo.com.br/excel/790-excel-usando-a-formatacao- condicional-de-celulas.htm/> Acesso em 20 Abr. 2021. Disponível em:http://www.tecmundo.com.br/excel/1056-excel-aprenda-a-criar- tabelas-dinamicas.htm/> Acesso em 19 Abr. 2021. Disponível em http://bloginformaticamicrocamp.com.br/ http://blog.luz.vc/excel/funcao-se-excel/ http://www.aprenderexcel.com.br/2013/tutoriais/funcao-se- http://www.cavalcanteassociados.com.br/article.php?id=11 http://www.tecmundo.com.br/excel/790-excel-usando-a-formatacao-condicional-de-celulas.htm http://www.tecmundo.com.br/excel/790-excel-usando-a-formatacao-condicional-de-celulas.htm http://www.tecmundo.com.br/excel/1056-excel-aprenda-a-criar-tabelas-dinamicas.htm http://www.tecmundo.com.br/excel/1056-excel-aprenda-a-criar-tabelas-dinamicas.htm http://bloginformaticamicrocamp.com.br/
Compartilhar