Prévia do material em texto

<p>2013 Pág. 1</p><p>Idiomas e Informática</p><p>Excel Avançado ............................................................................................................. 3</p><p>Fórmulas ....................................................................................................................... 4</p><p>Operadores aritméticos ................................................................................................. 4</p><p>O uso de parênteses ...................................................................................................... 5</p><p>Exercício 1 – Montar a planilha abaixo. ....................................................................... 6</p><p>Exercício 2 – Montar a planilha abaixo. ....................................................................... 6</p><p>Funções ............................................................................................................................. 8</p><p>Funções Estatísticas ...................................................................................................... 9</p><p>Função Média ........................................................................................................... 9</p><p>Função Soma .......................................................................................................... 10</p><p>Função Mínimo e Máximo ..................................................................................... 11</p><p>Função Maior e Menor ........................................................................................... 11</p><p>1.5-Função Cont.se ................................................................................................. 12</p><p>1.6- Função CONT.SES ......................................................................................... 13</p><p>1.7- Função Cont.Valores ....................................................................................... 14</p><p>1.8- Função Contar.vazio........................................................................................ 14</p><p>1.9- Função Cont.Núm ........................................................................................... 14</p><p>1.9- Função Cont.Núm ........................................................................................... 15</p><p>1.9- Função Cont.Núm ........................................................................................... 17</p><p>Funções Lógicas ......................................................................................................... 18</p><p>2.1- Função Se ........................................................................................................ 18</p><p>2.2- Função Seerro .................................................................................................. 20</p><p>2.2- Função Seerro .................................................................................................. 20</p><p>2.2- Função Seerro .................................................................................................. 22</p><p>Funções Datas e Horas ............................................................................................... 22</p><p>3.2- Função Agora() ............................................................................................... 22</p><p>3.3- Função Dias360 ............................................................................................... 23</p><p>3.4- Função Dias – Só Versão 2013 ....................................................................... 23</p><p>3.5- Função Dia.da.Semana .................................................................................... 24</p><p>3.6- Funções Dia, Mês, Ano ................................................................................... 25</p><p>3.8- Função Data.valor ........................................................................................... 25</p><p>3.9- Funções Hora, Minuto e segundo .................................................................... 26</p><p>Funções Texto ............................................................................................................ 26</p><p>Função Arrumar ...................................................................................................... 26</p><p>Função Concatenar ................................................................................................. 26</p><p>Função Direita e Esquerda ...................................................................................... 27</p><p>Função Num.Caract ................................................................................................ 28</p><p>Funções Exato ........................................................................................................ 28</p><p>Funções Pri.Maiuscula ........................................................................................... 28</p><p>Função Sustituir ...................................................................................................... 29</p><p>Funções Matemática e Trigonometria ........................................................................ 30</p><p>5.1-Função ABS() ................................................................................................... 30</p><p>5.2-Função Arred() ................................................................................................. 30</p><p>5.2-Função Trucar() ................................................................................................ 30</p><p>5.2-Função Mod() ................................................................................................... 31</p><p>Sintaxe da função MOD ......................................................................................... 31</p><p>6.-Funções de Pesquisa e Referência .......................................................................... 31</p><p>Função Procv e Proch ............................................................................................. 31</p><p>6.1-Como usar função PROCV no Excel ............................................................... 31</p><p>6.2- O que faz a função PROCH?............................................................................... 35</p><p>2013 Pág. 2</p><p>Idiomas e Informática</p><p>Exemplo .................................................................................................................. 36</p><p>6.3- Função Corresp ............................................................................................... 38</p><p>6.4- Função Deslcoc ............................................................................................... 40</p><p>6.5- Função Índice .................................................................................................. 43</p><p>7- Funções Financeiras ............................................................................................. 45</p><p>7.1- Função PGTO ................................................................................................ 45</p><p>7.2- Função Taxa .................................................................................................. 46</p><p>7.3- Função Valor Futuro - VF ............................................................................ 48</p><p>7.4- Função Valor Atual -VP ............................................................................... 49</p><p>Tabela Dinâmica ............................................................................................................. 51</p><p>Exercício 2 – Montar a planilha abaixo. ......................................................................... 56</p><p>Exercício 2 – Montar a planilha abaixo. ......................................................................... 58</p><p>Exercício Procv............................................................................................................... 61</p><p>Exercício Proch............................................................................................................... 62</p><p>Exercício Procv e Proch ................................................................................................. 63</p><p>Exercício Função Contar.Valores , Contar.Vazio .....................................................</p><p>2013 Pág. 57</p><p>Idiomas e Informática</p><p>Renomear a planilha para Boletim Escolar e gravar o arquivo (Pasta)</p><p>Fazer:</p><p>d) Na coluna F, calcular a média usando a Fórmula, Não usar função Média</p><p>e) Na coluna G, calcular a média usando a função média. Fazer usando o assistente de</p><p>funções e digitando a função</p><p>f) Na coluna H, Criar a condição. Se Média maior ou igual a 6, o aluno está</p><p>aprovado, senão o aluno esta reprovado. Fazer usando o assistente de funções e</p><p>digitando a função</p><p>g) Na coluna I, preencher usando a função PROCV, usar a tabela Conceito – Notas.</p><p>h) Usar a formatação condicional: Aprovado cor Azul</p><p>Reprovado cor Vermelha</p><p>i) Usar a formatação condicional: Média >=6; Azul, menor que 6, vermelho. Fazer nas</p><p>duas colunas Médias e nas notas dos bimestres e também na tabela classificação</p><p>j) Usar a formatação na coluna conceito.</p><p>Péssimo Vermelho</p><p>Ruim Turquesa Claro</p><p>Bom Azul Céu</p><p>Excelente Azul Escuro</p><p>2013 Pág. 58</p><p>Idiomas e Informática</p><p>k) Criar um gráfico de colunas com a coluna Nome e notas do 1º Bim. Mover o</p><p>gráfico para uma nova planilha.</p><p>l) Criar um gráfico de Barras com a coluna Nome e notas do 2º Bim. Mover o gráfico</p><p>para uma nova planilha.</p><p>m) Criar um gráfico de Linhas com a coluna Nome e notas do 3º Bim. Mover o</p><p>gráfico para uma nova planilha.</p><p>n) Criar um gráfico de Pizza 3D com a coluna Nome e notas do 4º Bim. Mover o</p><p>gráfico para uma nova planilha.</p><p>o) Criar um gráfico de Colunas com a coluna Nome e notas do Média. Mover o</p><p>gráfico para uma nova planilha.</p><p>p) Criar um gráfico de Colunas com a coluna Nome, todas a notas e uma Média.</p><p>Mover o gráfico para uma nova planilha.</p><p>q) Inserir Rótulos em todos os gráficos</p><p>r) Alterar o gráfico de barras, para um outro tipo de gráfico.</p><p>Exercício 2 – Montar a planilha abaixo.</p><p>Fazer:</p><p>2013 Pág. 59</p><p>Idiomas e Informática</p><p>a) Fazer 5 cópias da planilha, para outras novas planilhas</p><p>b) Renomear a primeira planilha para Folha 1 e as outras para Folha 2, e assim por</p><p>diante.</p><p>c) Na planilha Folha 1, Calcular 10% para o imposto de Renda – (Valor IR)</p><p>d) Calcular o salário Líquido.</p><p>e) Na planilha Folha 2, na coluna Valor IR calcular:</p><p>Se salário for menor ou igual 900, calcular 5%, senão 10%</p><p>f) Na planilha Folha 3, na coluna Valor IR calcular:</p><p>Se Salário<=900 mostrar “Isento”</p><p>Se Salário<=1500 calcular 5%; senão 10%</p><p>g) Calcular o salário Líquido.</p><p>h) Na planilha Folha 4, na coluna Valor IR calcular:</p><p>Se Salário<=900 mostrar “Isento”</p><p>Se Salário<=1500 calcular 5%</p><p>Se Salário<=2000 Calcular 10%</p><p>Se salário <=3500 calcular 15%, senão 20%</p><p>i) Calcular o salário Líquido.</p><p>j) Usar a formatação condicional e colocar Isento com a cor Vermelha e não Isento</p><p>com a cor Azul. Fazer em todas as planilhas</p><p>k) Na planilha Folha 1, classificar a coluna nome em ordem alfabética em ordem</p><p>crescente</p><p>l) Na planilha Folha 1, classificar a coluna nome em ordem alfabética em ordem</p><p>decrescente</p><p>m) Na planilha Folha 1, classificar a coluna Setor em ordem alfabética em ordem</p><p>crescente</p><p>n) Na planilha Folha 1, classificar a coluna Setor em ordem alfabética em ordem</p><p>decrescente</p><p>o) Na planilha Folha 1, classificar a coluna Sal Bruto em ordem alfabética em ordem</p><p>crescente</p><p>p) Na planilha Folha 1, classificar a coluna Sal Bruto em ordem alfabética em ordem</p><p>decrescente</p><p>q) Na planilha Folha 2, fazer um filtro para mostrar o setor de Vendas</p><p>r) Alterar o filtro para mostrar o setor de vendas e Financeiro</p><p>2013 Pág. 60</p><p>Idiomas e Informática</p><p>s) Retirar o filtro</p><p>t) Fazer um filtro para mostrar o Sal. Bruto maiores ou igual a R$ 1000,00</p><p>u) Fazer um filtro para mostrar o Sal. Bruto menores que R$ 2.000,00</p><p>v) Fazer um filtro para mostrar os Sal. Bruto que estão entre R$ 600,00 e R$ 2000,00</p><p>w) Fazer um filtro para mostrar somente os funcionários Isento</p><p>x) Na Folha 3 criar um Subtotal da coluna Setor</p><p>y) Mostrar os 3 níveis do subtotal</p><p>z) Retirar o Subtotal</p><p>aa) Na Folha 4, Criar um Subtotal pela coluna setor</p><p>bb) Completar a Folha 3, conforme abaixo:</p><p>cc) Usar a função Cont.se, na coluna Quantidade de Funcionários, contar quantos</p><p>funcionários tem por setor.</p><p>dd) Usar a função Somase, somar os salários pelo Setor.</p><p>ee) Em uma planilha em branca, fazer a tabela dinâmica Abaixo:</p><p>2013 Pág. 61</p><p>Idiomas e Informática</p><p>Exercício Procv</p><p>1- Montar a Planilha abaixo:</p><p>2- Renomear a Plan1 para Lista de Clientes</p><p>3- Ir para uma nova Planilha e digitar a planilha abaixo.</p><p>2013 Pág. 62</p><p>Idiomas e Informática</p><p>4- Renomear a planilha para Procurar Clientes</p><p>5- Fazer um copia das duas planilhas para 2 novas planilhas e renomear</p><p>Lista de Clientes1</p><p>Procurar Clientes1</p><p>6- Usar a função Procv para preencher os dados da Planilha Procurar Clientes.</p><p>Para nomear o intervalo a ser pesquisado (Matriz Tabela)</p><p>a) Clique na guia Lista de Clientes1 e selecione o intervalo de A1:F11;</p><p>b) Nomear este intervalo para Clientes;</p><p>c) Clicar na planilha ProcurarClientes1</p><p>d) Nomear a Célula E4, para Código</p><p>e) Digite as fórmulas abaixo nas demais células e veja o resultado.</p><p>Posicione na célula C7 e digite = Procv(Código;clientes;3;0)</p><p>Posicione na célula C8 e digite = Procv(Código;clientes;4;0)</p><p>Posicione na célula C9 e digite = Procv(Código;clientes;5;0)</p><p>Posicione na célula C10 e digite = Procv(Código;clientes;6;0)</p><p>f) Criar caixa de Combinação para a planilha.</p><p>Exercício Proch</p><p>1) Montar a Planilha Abaixo: Renomear a planilha para Produção</p><p>2- E outra planilha digitar dos dados abaixo: Renomear para Consulta de Produção</p><p>2013 Pág. 63</p><p>Idiomas e Informática</p><p>3- Fazer um copia das duas planilhas para 2 novas planilhas e renomear</p><p>Produção 1</p><p>Consulta Produção 1</p><p>Para nomear o intervalo a ser pesquisado (Matriz Tabela)</p><p>a) Na planilha Produção 1 renomear o intervalo de A3:M6, para Produção;</p><p>b) Na Planilha Consulta Produção 1, renomear a Célula B3 para Mês</p><p>f) Digite as fórmulas abaixo nas demais células e veja o resultado.</p><p>Posicione na célula B6 e digite = Proch(Mês;Produção;2;0)</p><p>Posicione na célula B7 e digite = Proch(Mês;Produção;3;0)</p><p>Posicione na célula B8 e digite = Proch(Mês;Produção;4;0)</p><p>Posicione na célula B9 e digite = Proch(Mês;Produção;5;0)</p><p>Exercício Procv e Proch</p><p>1- Montar a Planilha abaixo e renomear com o nome Reservas</p><p>2013 Pág. 64</p><p>Idiomas e Informática</p><p>2- Montar em outra planilha os dados abaixo e renomear para Tabela</p><p>3- Fazer os seguintes Cálculos</p><p>Destino => Utilize a função PROCH</p><p>Descrição => Utilize a função PROCV</p><p>Preço Base => Utilize a função PROCH</p><p>2013 Pág. 65</p><p>Idiomas e Informática</p><p>Exercício Função Contar.Valores , Contar.Vazio</p><p>Mesclando funções e fórmulas</p><p>Agora que já foram vistas as fórmulas e funções que utilizamos em nossas</p><p>planilhas, aprenderemos com fazer para mesclar fórmulas e funções, ou seja, duas</p><p>funções, ou fórmulas, em uma.</p><p>Estão mesclados uma</p><p>fórmula e uma função</p><p>2013 Pág. 66</p><p>Idiomas e Informática</p><p>No exemplo a seguir, observe uma mesclagem de duas funções:</p><p>No próximo exemplo, teremos que encontrar um Novo Valor, acrescentado 1%</p><p>sobre o valor somente para veículos com valor superior a R$ 15.000,00 e frete superior</p><p>a R$ 100,00.</p><p>SE(E(B4>15000;C4>100);B4*1,01;B4)</p><p>Exercícios:</p><p>1- Monte as planilhas abaixo:</p><p>Função que testa se o</p><p>valor contido em B4 é</p><p>superior a 15000 e o de</p><p>C4 superior a 100</p><p>Valor a ser apresentado</p><p>caso o teste seja</p><p>verdadeiro</p><p>Valor a ser</p><p>apresentado caso</p><p>o</p><p>teste seja falso</p><p>2013 Pág. 67</p><p>Idiomas e Informática</p><p>� Renomear as planilhas para Desconto INSS e Folha de pagamento.</p><p>1. O vale transporte será calculado da Seguinte Forma: Se o salário do funcionário for</p><p>menor o igual a 900, será descontado do salário 6%, Senão Zero, ou seja o</p><p>funcionário não pega vale transporte.</p><p>2. Na coluna D, devem ser especificados os valores referentes ao desconto do vale</p><p>refeição. Este desconto será de 3.5% independente de seu salário.</p><p>3. Na coluna E, devem ser especificados os valores referentes ao desconto do convênio</p><p>médico. Este desconto será de 1.5% para salários até R$ 1000,00 e 2% para salários</p><p>superiores a R$ 1000,00. Utiliza a função Se para calcular os valores.</p><p>4. Na coluna F, utilizando a função Procv, calcule o desconto em R$ para o INSS,</p><p>utilizando como base a tabela de descontos localizada na planilha Faixa Salarial.</p><p>5. Na coluna G, devem ser especificados os valores referentes ao salário líquido. Este</p><p>valor corresponde ao salário bruto com a subtração de todos os descontos.</p><p>6. Calcule a soma de cada coluna.</p><p>7. Calcule a média de cada coluna.</p><p>8. Calcule o maior valor de cada coluna, usar a função Máximo.</p><p>9. Calcule o menor valor de cada coluna, usar a função Mínimo.</p><p>10. Calcule o 2º maior valor de cada coluna, usar a função Maior.</p><p>11. Calcule o 2 menor valor de cada coluna, usar a função mínimo.</p><p>12. Calcular o total do INSS, mas somente dos valores < 100, usar a função a função</p><p>SomaSE.</p><p>13. Vamos fazer uma pequena alteração na planilha para calcular o Convênio Médico,</p><p>igual que foi feito para calcular o INSS. Isto é usando a função Procv.</p><p>2013 Pág. 68</p><p>Idiomas e Informática</p><p>� Renomear a planilha para convênio Médico.</p><p>2- Monte a planilha abaixo:</p><p>1- Calcule o valor da venda de cada funcionário.</p><p>2- Calcule o valor Total de Vendas.</p><p>3- Utilizando em conjunto as funções SE e E, calcule a comissão seguindo os</p><p>seguintes critérios:</p><p>� O funcionário só receberá 10% de comissão sobre suas vendas, se o valor de suas</p><p>vendas for maior que R$2.000,00 e o valor total das vendas for maior que R$</p><p>40.000,00.</p><p>SE(E(E3>2000;$E$12>40000);E3*10%;0)</p><p>4- Resolver o restante das questões.</p><p>Exercícios Função Arrumar</p><p>A Função ARRUMAR remove os espaços indesejados de uma seqüência de</p><p>caracteres de texto exceto os espaços únicos entre palavras. Sintaxe:</p><p>=ARRUMAR(texto) texto: é o texto que contém os espaços para serem removidos.</p><p>2013 Pág. 69</p><p>Idiomas e Informática</p><p>Com a Função ARRUMAR é possível apagar todos os espaçamentos duplos que</p><p>ficam entre as palavras. Este tipo de problema é bastante comum se você copia e cola</p><p>muitas informações de sites, arquivos txt, dados copiados do sistema, etc.</p><p>Obs: Copiar o conteúdo da coluna B, para a Coluna D</p><p>Exercício Função Corresp</p><p>A Função CORRESP retorna a posição relativa de um item em uma lista que</p><p>coincide com um valor procurado em determinada ordem Sintaxe: =CORRESP</p><p>(valor_procurado;matriz_procurada;[tipo_correspondencia]))</p><p>A Função CORRESP retorna o número da posição em que o item pesquisado se</p><p>encontra ao invés do valor contido na tabela. A Função CORRESP é muito útil em</p><p>conjunto com outras funções de pesquisa tais como as Funções ÍNDICE, DESLOC e</p><p>PROCV.</p><p>2013 Pág. 70</p><p>Idiomas e Informática</p><p>Exercício Função Seerro</p><p>Usada para tratar o erro de algum processamento. Por exemplo, de dividir um</p><p>número por zero, retornara um erro.</p><p>Exercícios.</p><p>1- Abre a tabela Funcionários</p><p>2- Observe os nomes das colunas A e B.</p><p>3- Queremos saber se todos os nomes que estão na A estão na B.</p><p>Para isso: Usar a função Procv.</p><p>4- Vai notar quando a função procv não achar o nome volta um erro.</p><p>5- Vamos tratar esse erro usando a função Seerro.</p><p>6- Depois também fazer em outra coluna a mesma questão usando a função Se e</p><p>Serros.</p><p>Exercício. Funções Dias360, Se, Máximo e Mínimo</p><p>2013 Pág. 71</p><p>Idiomas e Informática</p><p>a) Formatar as células para estilo de moeda</p><p>b) Calcular os valores 3x e 2x</p><p>c) Inserir a função Dias360 para calcular dias de atraso</p><p>d) Usar a função Dias para calcular os dias de atraso.</p><p>e) Inserir a função SE para informar a situação (Se dias de atraso=0, “Em dia”, Se</p><p>dias de atraso > 30, “Registrar no SPC” senão “Enviar carta de cobrança”)</p><p>f) Inserir a função Máximo para verificar o maior valor de compras</p><p>g) Inserir a função Maior para verificar o 2º maior valor de compras</p><p>h) Inserir a função Mínimo para inserir o menor valor de compras</p><p>i) Inserir a função Menor para inserir o 2º menor valor de compras</p><p>j) Salvar o arquivo</p><p>k) Qual a diferenças entre as funções Dias360 e Dias?</p><p>Exercício. Testando um CPF se o valor e Correto o Inválido. Usando Função MOD,</p><p>SE, E e a ou.</p><p>Obs: Inserir as funções Se, e, ou, digitando e usando o assistente de função.</p><p>Exercício. Função concatenar</p><p>6- Montar a planilha abaixo:</p><p>2013 Pág. 72</p><p>Idiomas e Informática</p><p>a) Usar a função concatenar montar o Email.</p><p>b) Visualizar a impressão</p><p>c) Centralizar a planilha no centro da página</p><p>d) Aplicar cabeçalho e rodapé</p><p>INTRODUÇÃO A FÓRMULAS MATRICIAIS</p><p>Um dos mais interessantes recursos do Excel é poder trabalhar com fórmulas e</p><p>funções matriciais. Se você entender os conceitos de fórmulas matriciais, com certeza</p><p>você estará apto a criar fórmulas que muitas vezes não é possível criar com fórmulas</p><p>comuns.</p><p>Neste primeiro artigo sobre fórmulas matriciais, eu irei abordar os conceitos</p><p>básicos.</p><p>ENTENDENDO AS FÓRMULAS MATRICIAIS</p><p>Uma matriz é um grupo de valores que pode ser operado em conjunto ou</p><p>individualmente. No Excel podemos trabalhar com matrizes em até duas dimensões. As</p><p>dimensões correspondem às linhas e colunas da matriz. Uma matriz horizontal (com</p><p>apenas 1 linha) ou vertical (com apenas uma coluna) são consideradas unidimensionais.</p><p>Já uma matriz com duas ou mais linhas, e duas ou mais colunas é considerada</p><p>bidimensional.</p><p>Uma matriz não pode ser armazenada em uma célula apenas, pois cada célula</p><p>pode armazenar apenas um valor. Porém, você pode manipular cálculos com matrizes</p><p>dentro de uma única célula, desde que este cálculo retorne um único valor.</p><p>Para você entender melhor como funciona tais fórmulas, veja que existem dois</p><p>tipos de fórmulas com matrizes:</p><p>2013 Pág. 73</p><p>Idiomas e Informática</p><p>Fórmula matricial multicelular</p><p>Uma fórmula matricial multicelular é aquela que retorna outra matriz como</p><p>resultado. Como cada célula pode armazenar apenas um único valor, para mostrar o</p><p>valor produzido por uma fórmula matricial multicelular, você deverá selecionar a</p><p>quantidade de células necessárias para mostrar o resultado do cálculo.</p><p>Normalmente, para calcular o valor total (Coluna E) você digitaria na célula E2</p><p>a fórmula =C2*D2 e depois copiaria para as células abaixo. Ao fazer isso, perceba que</p><p>cada célula que você copiou a fórmula, criou uma fórmula</p><p>diferente. =C2*D2, =C3*D3, e assim por diante.</p><p>Ao utilizar uma fórmula matricial, você pode criar uma única fórmula onde deseja</p><p>calcular o Valor Total, e esta fórmula ocupará as 4 células com o resultado. Proceda da</p><p>seguinte forma para criar a fórmula matricial multicelular:</p><p>� Selecione o intervalo E2:E5 que receberá os resultados. Você deve proceder</p><p>assim, porque não pode armazenar vários valores em uma única célula.</p><p>� Digite a seguinte fórmula: =C2:C5*D2:D5 (Você está multiplicando duas</p><p>matrizes de tamanho 5×1, o que resultará em outra matriz 5×1).</p><p>� Pressione [CTRL] + [SHIFT] + [ENTER] para finalizar.</p><p>Ao trabalhar com fórmulas comuns, você pressiona [ENTER] ao finalizar a</p><p>fórmula. Com fórmulas matriciais você</p><p>deve pressionar [CTRL] + [SHIFT] + [ENTER],</p><p>ou não funcionará.</p><p>Note agora que, em qualquer uma das células do intervalo E2:E5, na barra de</p><p>fórmulas a fórmula exibida é {=C2:C5*D2:D5}. As chaves indicam que esta é uma</p><p>fórmula matricial, e são colocadas automaticamente ao pressionar [CTRL] + [SHIFT] +</p><p>[ENTER].</p><p>Um ponto importante, é que ao utilizar uma fórmula matricial multicelular, você</p><p>não conseguirá alterar uma célula da matriz individualmente já que todas as células</p><p>2013 Pág. 74</p><p>Idiomas e Informática</p><p>contém a mesma fórmula. Para alterar (ou apagar) a fórmula, você deverá selecionar</p><p>todo o intervalo onde está armazenada a matriz.</p><p>Fórmula Matricial Unicelular</p><p>Uma fórmula matricial unicelular é aquela que faz cálculos com matrizes, mas</p><p>retorna um único valor como resultado. Sendo assim, pode ser armazenada em uma</p><p>única célula.</p><p>Suponha que na planilha do exemplo anterior você não tenha a coluna Valor Total</p><p>calculada para cada um dos itens. Agora você quer calcular o valor total geral (a soma</p><p>dos totais de cada item).</p><p>Você já sabe que a fórmula {=C2:C5*D2:D5} retorna como resultado uma matriz</p><p>com o Valor Total de cada item. O valor do total geral nada mais é, do que a soma de</p><p>todos os elementos desta matriz. Para isso, siga os seguintes passos:</p><p>� Selecione a célula onde deseja exibir o resultado. Note que, diferente do exemplo</p><p>anterior, agora você pode exibir o resultado em apenas uma célula.</p><p>� Digite a seguinte fórmula: =SOMA(C2:C5*D2:D5) (Você está somando a</p><p>multiplicação de duas matrizes, o que produz um valor único)</p><p>� Pressione [CTRL] + [SHIFT] + [ENTER] para finalizar.</p><p>Se tudo foi feito corretamente, a barra de fórmulas deverá exibir a fórmula com as</p><p>chaves, e o resultado deverá ser R$ 869,50</p><p>Introdução a Macros: O que são Macros?</p><p>Uma macro é uma seqüência de comandos (cliques de mouse ou toques de</p><p>teclado) que são gravados em um Módulo VBA e podem ser executados, sempre que</p><p>necessário. A grande vantagem de gravarmos uma seqüência de comandos é que</p><p>poderemos utilizá-la sempre que necessário. Para isso basta executar a macro na qual foi</p><p>gravada a seqüência de comandos.</p><p>As Macros são uma excelente opção para automatizar tarefas repetitivas. Com</p><p>o uso de Macros temos um ganho de produtividade considerável, ao evitar que</p><p>tenhamos que executar manualmente, os diversos passos de uma tarefa, passos estes que</p><p>podem ser automatizados através do uso de uma macro.</p><p>2.1- Os Tipos de Macros</p><p>As Macros podem ser divididas em Três tipos: Macros de Comando, Macros de</p><p>funções definidas pelo usuário e macro de sub-rotina.</p><p>Macros de Comando: Este tipo mais comum de procedimento. Geralmente possui</p><p>equivalência a comandos de menu e caixas de diálogo. O efeito de uso tipo de</p><p>procedimento ocorre somente nas regiões selecionadas de uma pasta de trabalho e suas</p><p>2013 Pág. 75</p><p>Idiomas e Informática</p><p>planilhas. È como se você estivesse executando várias vezes os mesmos comandos do</p><p>menu.</p><p>Macros de Funções Definidas pelo usuário: Este tipo de procedimento trabalha de</p><p>forma idêntica às funções incorporadas ao Excel. A diferença existente é que você cria a</p><p>sua própria função. O efeito de trabalho deste tipo de macro não ocorre em regiões</p><p>previamente selecionadas, sendo que elas sempre retornam um determinado valor.</p><p>Macro de Sub-Rotina: Este tipo de procedimento é uma junção dos dois tipos</p><p>anteriores, pois tanto permite efetuar alterações em regiões previamente selecionadas,</p><p>como também permite retornar valores. Caracteriza-se pela possibilidade de ser</p><p>chamada de dentro de outras macros, permitindo desta forma minimizar o uso de tarefas</p><p>repetidas, tornando o código de programação mais leve.</p><p>2.2- Necessidade de Planejar uma Macro</p><p>Jamais elabore uma macro sem planejá-la e rascunhá-la, pois todo aquele que age</p><p>desta forma passa a ser um candidato à frustração e ao trabalho dobrado. Tenha este</p><p>conceito sempre em mente. Tudo o que é feito em um computador necessita de antemão</p><p>ser planejado, por mais experiência que você tenha.</p><p>Uma construtora jamais ergue uma edificação sem sua respectiva planta, por mais</p><p>tecnologia e conhecimento que ela tenha sobre o assunto. Sendo assim, aja da mesma</p><p>forma. O processo de planejamento é fácil e muito útil, pois basta que você descreva</p><p>todas as etapas a serem cumpridas para atingir o ponto final do problema. Para tanto,</p><p>siga estes passos básicos:</p><p>1- Efetue o entendimento do problema a ser resolvido, fazendo uma análise de cada</p><p>parte, a fim de ter uma sólida idéia do que deve ser feito.</p><p>2- Efetue o planejamento do procedimento manualmente, rascunhado em um papel.</p><p>Em alguns casos, é aconselhável até a elaboração de diagrama de blocos para solucionar</p><p>mais rapidamente a tarefa a ser executada.</p><p>3- Somente executados os passos anteriores é que você passa a efetuar a codificação do</p><p>procedimento na planilha. Em alguns casos, esta tarefa pode ser simplificada com a</p><p>utilização do recurso de gravação de toques no teclado e mouse.</p><p>4- Você deve atribuir sempre um nome a um procedimento, que será utilizado para sua</p><p>identificação e execução posterior, devendo sempre ser mais sugestivo possível a sua</p><p>execução.</p><p>2.3-Maneiras de Criar um macro</p><p>Existem duas maneiras distintas para a criação de uma macro:</p><p>Podemos usar o gravador de Macros: Nesse caso o Excel grava cada uma das</p><p>ações que farão parte da Macro e transforma essas ações nos comandos VBA</p><p>equivalentes. Quando a macro for executada, os comandos VBA é que serão</p><p>2013 Pág. 76</p><p>Idiomas e Informática</p><p>efetivamente executados. Cada comando VBA corresponde a uma ação efetiva da</p><p>macro.</p><p>Criar a Macro usando VBA: A partir do momento em que você domina a</p><p>linguagem VBA, poderá criar à macro digitando os comandos VBA necessários. Isso é</p><p>feito usando o Editor de VBA, conforme veremos nas próximas lições.</p><p>Melhor do que definições é a prática!!</p><p>Melhor do que uma série de definições é ver uma Macro em ação. Vamos a</p><p>um exemplo simples, onde criaremos uma Macro. Em seguida vamos executá-la. Na</p><p>próxima lição analisaremos o código VBA criado pelo gravador de macros.</p><p>Exemplo 1: Criar uma macro usando o Gravador de Macros. A macro deverá</p><p>formatar a célula atual com Negrito, cor de fonte Vermelha, com fundo cinza. Gravar a</p><p>macro com o nome de FormataVermCinza.</p><p>1. Abra o Excel.</p><p>2. Clique na célula A1.</p><p>3. Agora vamos iniciar a gravação da Macro.</p><p>4. Selecione o Menu Exibição e a Guia Macro -> Gravar nova macro.</p><p>5. Será exibida a janela Gravar Macro.</p><p>2013 Pág. 77</p><p>Idiomas e Informática</p><p>6. No campo Nome da macro digite: Nome</p><p>7. No campo Tecla de atalho digite L. Observe que o Excel troca para Ctrl+Shift+L.</p><p>Isso acontece porque a combinação Ctrl+L já deve estar associada com algum</p><p>comando do Excel. Com isso estamos associando a combinação Ctrl+Shift+L com a</p><p>macro Nome, ou seja, cada vez que quisermos executar essa macro basta pressionar</p><p>Ctrl+Shift+L.</p><p>8. O campo descrição é simplesmente uma descrição da funcionalidade da macro.</p><p>Digite o texto indicado na Figura abaixo:</p><p>09. Clique em OK. A gravação da Macro será iniciada. Todos os comandos que você</p><p>executar, durante a gravação da Macro, farão parte da Macro.</p><p>10- Na Célula A1, digitar seu nome</p><p>11-.Clique no botão ( ) para aplicar Negrito.</p><p>12.-Na lista de Cores da fonte ( ) selecione Vermelho.</p><p>13.-Na lista de Cores de fundo ( ) selecione Cinza.</p><p>14- Mesclar a linha até a coluna G</p><p>15- Tamanho da fonte 26</p><p>16- Agora temos que parar a gravação da Macro.</p><p>Menu Exibição/Macro/Para Macro</p><p>2013 Pág. 78</p><p>Idiomas e Informática</p><p>2.4- Executando um macro</p><p>Existem varias maneiras de executar uma Macro.</p><p>2.4.1.- Executando um macro usando a tecla de atalho.</p><p>Para executar a macro Nome faça o seguinte:</p><p>1.- Inserir</p><p>uma nova Planilha.</p><p>2.-Pressione Ctrl+Shift+L.</p><p>3.- A macro Nome é executada e as formatações definidas pela macro (Negrito, fonte</p><p>Vermelha e fundo Cinza) são automaticamente aplicadas na Célula A1 da nova planilha.</p><p>Veja que com um simples comando de teclado, executo uma série de comandos (nesse</p><p>exemplo: três comandos de formatação). Esse exemplo nos dá uma pequena idéia do</p><p>poder e facilidade do uso das macros.</p><p>2.4.2- Executando a Macro usando o Menu Ferramentas</p><p>1.-Inserir uma nova célula B5.</p><p>2.-Selecione o comando Exibição/Macro/Exibir Macro</p><p>3.-Será exibida a janela Macro, onde são listadas todas as macros existentes na pasta de</p><p>trabalho atual (no arquivo carregado no Excel), conforme indicado na Figura abaixo:</p><p>2013 Pág. 79</p><p>Idiomas e Informática</p><p>4.-Clique na Macro Nome para selecioná-la.</p><p>5.-Clique no botão Executar.</p><p>6.-A Macro será executada e as respectivas formatações serão aplicadas à célula A1.</p><p>7.- A sua planilha deve estar conforme indicado na Figura.</p><p>2.4.3- Criando um Menu</p><p>1- Menu arquivo/Opções/Personalizar Faixas de opções</p><p>2- Clicar em Nova Guia</p><p>3- Digitar o Seu nome para a nova Guia.</p><p>4- Selecionar o Novo Grupo da nova Guia criada</p><p>5- Selecionar Macro em Escolher Comandos Em</p><p>6- Clicar na Macro Nome e Clicar no Botão Adicionar</p><p>7- Clicar no botão renomear e trocar o ícone.</p><p>2013 Pág. 80</p><p>Idiomas e Informática</p><p>2.4.4- Criando um botão de comando</p><p>1- Menu arquivo/Opções/Barra de Ferramentas de Acesso Rápido</p><p>2- Clicar em Escolher comando Em.</p><p>3- Escolher Guia do Desenvolvedor</p><p>4- Selecionar Inserir controle e clicar em Adicionar</p><p>5- Clicar em Ok.</p><p>6 – Desenhar o Botão na Planilha e Escolher a macro a ser executada.</p><p>Exercícios Criar a Macro Abaixo:</p><p>Exemplo 2: Criar uma macro usando o Gravador de Macros. A macro deverá</p><p>formatar a célula atual com Negrito, cor de fonte Vermelha, com fundo cinza. Gravar a</p><p>macro com o nome de FormataVermCinza.</p><p>9. Abra o Excel.</p><p>10. Clique na célula A1.</p><p>11. Agora vamos iniciar a gravação da Macro.</p><p>12. Selecione o Menu Exibição/Macro/Gravar Macro</p><p>2013 Pág. 81</p><p>Idiomas e Informática</p><p>13. Será exibida a janela Gravar Macro.</p><p>14. No campo Nome da macro digite: FormataVermCinza.</p><p>15. No campo Tecla de atalho digite L. Observe que o Excel troca para Ctrl+Shift+L.</p><p>Isso acontece porque a combinação Ctrl+L já deve estar associada com algum</p><p>comando do Excel. Com isso estamos associando a combinação Ctrl+Shift+L com a</p><p>macro FormataVermCinza, ou seja, cada vez que quisermos executar essa macro basta</p><p>pressionar Ctrl+Shift+L.</p><p>16. O campo descrição é simplesmente uma descrição da funcionalidade da macro.</p><p>Digite o texto indicado na Figura abaixo:</p><p>10. Clique em OK. A gravação da Macro será iniciada. Todos os comandos que</p><p>você executar, durante a gravação da Macro, farão parte da Macro.</p><p>11-.Uma nova barra ( ) é exibida na planilha do Excel. Essa barra é utilizada para parar</p><p>a gravação da Macro. Agora devemos escolher os comandos que farão parte da macro.</p><p>Após ter executado os comandos que farão parte da macro, basta clicar no botãopara</p><p>encerrar a gravação da Macro.</p><p>12-.Clique no botão ( ) para aplicar Negrito.</p><p>13.-Na lista de Cores da fonte ( ) selecione Vermelho.</p><p>14.-Na lista de Cores de fundo ( ) selecione Cinza.</p><p>15.-Clique no botão ( ) para encerrar a gravação da Macro.</p><p>16.-Agora a macro FormataVermCinza foi criada e podemos utilizá-la sempre que</p><p>necessário.</p><p>2.5- Conhecendo do que é feita uma Macro</p><p>2013 Pág. 82</p><p>Idiomas e Informática</p><p>Na Lição anterior podemos ver uma macro em ação. Criamos uma macro</p><p>chamada Nome. Essa macro é composta por três comandos de formatação. Cada vez</p><p>que a macro é executada, os três comandos de formatação são novamente executados e</p><p>aplicados à célula onde esta o cursor. Até aqui tudo OK. Nenhuma novidade. A</p><p>pergunta que pode surgir é:</p><p>Como é que o Excel faz isso?</p><p>Ou de outra forma:</p><p>Do que é feita uma macro?</p><p>Conforme veremos, uma macro é gravada no Excel como uma seqüência de</p><p>comandos VBA. Por exemplo, ao clicarmos no botão ( ), para a aplicação de negrito,</p><p>o Excel gera um comando VBA que faz a formatação em negrito. Nessa lição</p><p>aprenderemos a acessar o código VBA gerado pelo Excel (embora ainda não sejamos</p><p>capazes de entender esse código).</p><p>Para acessar os comandos VBA associado com uma macro, siga os seguintes</p><p>passos:</p><p>1.-Selecione o comando Ferramentas -> Macro -> Macros.</p><p>2.-Será exibida a janela Macro.</p><p>3.-Clique na macro FormataVermCinza para seleciona-la.</p><p>4.-Clique no botão Editar.</p><p>5.-O Editor do VBA será carregado e serão exibidas as seguintes linhas de código:</p><p>Esses são os comandos VBA (Por enquanto não entendemos o que significam)</p><p>que formam a macro FormataVermCinza. Apenas para adiantar um pouco o assunto, a</p><p>seguir descrevo o que faz cada um dos principais comandos dessa Macro:</p><p>Selection.Font.Bold = True</p><p>2013 Pág. 83</p><p>Idiomas e Informática</p><p>Esse comando aplica a formatação em Negrito para a célula onde está o cursor (ou no</p><p>conjunto de células selecionadas), quando a macro é executada.</p><p>Selection.Font.ColorIndex = 3</p><p>Esse comando aplica cor de fonte Vermelha para a célula onde está o cursor (ou no</p><p>conjunto de células selecionadas), quando a macro é executada.</p><p>With Selection.Interior</p><p>.ColorIndex = 15</p><p>.Pattern = xlSolid</p><p>End With</p><p>Esses comandos aplicam a cor de fundo cinza, na célula onde está o cursor (ou no</p><p>conjunto de células selecionadas), quando a macro é executada.</p><p>Esses são comandos da linguagem VBA. Com o uso do VBA temos acesso a</p><p>todos os comandos e funções do Microsoft Excel. Tudo o que você faz usando o teclado</p><p>e o mouse, também é possível de ser feito com o uso do VBA, porém de uma maneira</p><p>automatizada. O uso de macros é especialmente indicado naquelas situações em que</p><p>temos um conjunto de comandos que precisam ser executados, repetidamente, em</p><p>diferentes situações. Nesses casos é muito mais prático criar uma macro composta pelo</p><p>conjunto de comandos e, cada vez que os comandos precisarem ser executados, executar</p><p>a macro.</p><p>Linhas de Comentários: São linhas iniciadas ‘ (apóstrofo). Elas indicam para o Excel</p><p>que são linhas somente de comentários e não serão processadas. São muito úteis pois</p><p>exibem o nome do procedimento, a data da sua criação, o nome de quem as fez e a</p><p>descrição de sua finalidade. Essas mensagens só serão apresentadas quando informadas</p><p>no momento da gravação.</p><p>Os comandos Sub/End Sub: Estas palavras indicam o início e o final de um</p><p>procedimento de comandos e, por este motivo, são denominadas Procedimentos Sub.</p><p>Se o procedimento for do tipo função definida pelo usuário, serão apresentadas as</p><p>palavras Function/End Funciton, que recebem o nome técnico de palavras-chave.</p><p>Nome do Procedimento: O nome do procedimento é apresentado à frente da palavra-</p><p>chave seguido de parênteses, os quais são obrigatórios e podem ser usados para a</p><p>definição de argumentos no caso de procedimentos de funções definidas pelo usuário.</p><p>2.6- Tipos de Macros</p><p>Na macro Nome que criamos anteriormente, ele só e executada na célula em</p><p>que foi criada, ou seja na célula A1. Essa macro é conhecida com Absoluta, ela</p><p>só funciona onde foi criada.</p><p>Existe a macro chamada Referência relativa¸ esse tipo de Macro é executada</p><p>em qualquer célula.</p><p>2013 Pág. 84</p><p>Idiomas e Informática</p><p>Criando em Macro Relativa.</p><p>1- Selecione o Menu Exibição e a Guia Macro -> Usar Referência Relativa</p><p>2- Repetir a Macro do Seu nome.</p><p>2.7- Exercícios:</p><p>1- Gravar uma macro como nome Altera_Fonte_Cor_Fundo. Coloque na macro tecla</p><p>de atalho.</p><p>a)</p><p>Estando na célula A1, abra a caixa de fontes e selecione a fonte Impact;</p><p>b) Clique no botão Cor do preenchimento e escolha um das cores;</p><p>c) Clique no botão de Cor da Fonte e escolha outra cor;</p><p>d) Clique sobre o botão Para Gravação;</p><p>e) Agora sua macro já está gravada, basta executá-la.</p><p>2- Criar um macro para aplicar o Estilo de moeda e altera a cor na fonte em qualquer</p><p>área selecionada. Para criar à macro, siga os passos abaixo.</p><p>a) No arquivo aberto, clique em uma planilha em branco;</p><p>b) Clique no menu Ferramentas, escolha Macro e clique no comando Gravar Nova</p><p>macro;</p><p>c) Na caixa nome da macro digite Formatto_Moeda;</p><p>d) Digite a descrição da macro e atribua a tecla de atalho desejada;</p><p>e) Clique no botão OK e será exibida a tela Parar gravação;</p><p>f) Defina as ações abaixo para a macro Formato_Moeda;</p><p>• Clique sobre o botão Cor da fonte e escolha a cor vermelha;</p><p>• Clique sobre o botão Estilo de Moeda;</p><p>• Clique sobre o botão Para gravação;</p><p>• Para Executar a macro, seja os passos abaixo:</p><p>a) Clique em uma planilha em branco;</p><p>b) Selecione o intervalo de C7:E12;</p><p>c) Execute a macro Formato_Moeda.</p><p>3- Abra uma planilha em branco e digite a tabela abaixo seguindo seus</p><p>endereçamentos:</p><p>2013 Pág. 85</p><p>Idiomas e Informática</p><p>4- Crie um macro como o nome de Pintar para executar as seguintes funções;</p><p>• Pintar o fundo da planilha de cinza;</p><p>• Definir a cor da fonte para azul;</p><p>• Finalizar a macro.</p><p>5- Crie uma macro com o nome de Moeda_Cor para executar as seguintes funções:</p><p>• Definir a cor da fonte para vermelho;</p><p>• Aplicar formato de moeda;</p><p>• Finalizar a macro.</p><p>6-Crie uma macro com o nome de Centralizar para que execute as seguintes funções:</p><p>• Centralizar os dados;</p><p>• Definir a cor da fonte para vermelho em Arial, tamanho 12 e negrito;</p><p>• Definir a cor de preenchimento para amarelo;</p><p>7- No exercício criado no item 3. (Planilha de carros). Faça o que se pede abaixo:</p><p>• Marque o intervalo de A1:F6 e execute a macro com o nome Pintar;</p><p>• Marque o intervalo de A1:F1 e execute a macro com o nome de centralizar;</p><p>• Marque o intervalo de E2:F6 e execute a macro com o nome de Moeda_Cor.</p><p>• Salve o arquivo de Macro.</p><p>Macros com Programação</p><p>Nesta lição será feita uma introdução ao conceito de uso e construção de macros</p><p>utilizando a linguagem de programação VBA – Visual Basic For Applications.</p><p>Você aprenderá também a fazer uso do ambiente de desenvolvimento Microsoft</p><p>Visual Basic for Application. A versão incluída no Excel é o Visual Basic 6.</p><p>O ambiente de desenvolvimento Microsoft Visual Basic é um segundo aplicativo</p><p>associado às planilhas do Excel, bem como aos outros aplicativos do pacote Office,</p><p>porém somente executado por meio das teclas de atalho <ALT>+<F11>, ou pelo</p><p>comando do menu:</p><p>• Ferramentas</p><p>• Macro</p><p>• Editor do Visual Basic</p><p>O aplicativo Microsoft Visual Basic caracteriza-se por ser um ambiente completo</p><p>de desenvolvimento de programas (macros).</p><p>2013 Pág. 86</p><p>Idiomas e Informática</p><p>Sua tela possui alguns componentes importantes. Todo aplicativo Windows possui</p><p>uma barra e título, uma barra de menu e uma barra de ferramentas, porém o foco central</p><p>de trabalho são as três áreas no centro da tela, designadas: Janela de Projetos, Janela</p><p>Propriedades e Janela de Código, que podem estar em exibição ou ocultas.</p><p>A janela de Projetos é na verdade um</p><p>Explorer (Com funcionamento semelhante ao</p><p>Explorer do Windows), tendo por finalidade a</p><p>função de exibir uma lista hierárquica dos</p><p>projetos e todos os seus itens, tais como</p><p>Formulários e Módulos. Essa janela possui três</p><p>botões (elementos), que executam,</p><p>respectivamente, da esquerda para a direita</p><p>Visualizar código, Visualizar objeto e Alternar</p><p>pastas.</p><p>A janela Propriedades (Figura abaixo) tem por finalidade listar as propriedades</p><p>da faze de projeto dos objetos selecionados e suas definições atuais. Podem essas</p><p>propriedades da fase de projeto ser alteradas. Quando são selecionadas múltiplos</p><p>controles, a janela Propriedades contém uma lista das propriedades comuns a todos os</p><p>controles selecionados.</p><p>Essa janela possui três elementos: Caixa</p><p>objeto que tem por função listar o objeto</p><p>selecionado. Somente os objetos do formulário ativo</p><p>estão visíveis. Se você selecionar múltiplos objetos,</p><p>as propriedades comuns aos objetos e suas</p><p>definições, baseadas no primeiro objeto selecionado,</p><p>aparecerão nas guias de listagem d propriedades</p><p>(Guias: Alfabético e categorizado).</p><p>• Alfabético apresenta uma lista, em ordem</p><p>alfabética, de todas as propriedades do objeto</p><p>selecionado que podem ser alteradas na fase de</p><p>projeto, assim com as suas definições atuais. Para</p><p>alterar a definição da propriedade, selecione o nome</p><p>da propriedade e digite ou selecione a nova</p><p>definição.</p><p>• Categorizado apresenta uma lista, por categoria, de todas as propriedades do objeto</p><p>selecionado. Por exemplo, BackColor, Caption e Forecolor se encontram na categoria</p><p>Aparência. Nessa guia é possível reduzir a lista a fim de visualizar as categorias ou</p><p>expandir uma categoria para visualizar as propriedades. Quando uma categoria é</p><p>expandida ou reduzida, ela é indicada com um sinal de adição (+) ou subtração (-) à</p><p>esquerda do nome da categoria.</p><p>2013 Pág. 87</p><p>Idiomas e Informática</p><p>A janela de código (figura abaixo) é utilizada para gravar, exibir e editar código</p><p>de programa em Visual Basic. Uma Janela de Código pode ser aberta a partir das janelas</p><p>de Projeto, Formulário ou Módulos, com um duplo-clique ou por meio do botão</p><p>visualizar código da janela de projetos. Essa janela possui seis elementos, que executam</p><p>as seguintes tarefas:</p><p>Caixa objeto: Esse elemento tem por finalidade exibir o nome do objeto selecionado.</p><p>Clique no botão Drop-Down, situado à direita da caixa de listagem, para exibir uma</p><p>lista de todos os objetos associados ao formulário.</p><p>Caixa Procedimentos/Eventos: Esse elemento tem por finalidade listar os eventos</p><p>reconhecidos pelo Visual Basic de um formulário ou controle exibido na caixa Objeto.</p><p>Quando um evento é selecionado, o procedimento associado a ele é exibido na janela</p><p>Código. Caso (geral) seja exibida na caixa objeto, a caixa Procedimento listará as</p><p>declarações e todos os procedimentos gerais que tenham sido criados para o formulário.</p><p>Caso o código de um módulo esteja sendo editado, a caixa Procedimento irá listar todos</p><p>os procedimentos gerais no módulo. Em ambos os casos, o procedimento selecionado</p><p>na caixa Procedimento é exibido na janela de código. Todos os procedimentos de um</p><p>módulo aparecem em uma lista de rolagem, classificada em ordem alfabética pelo</p><p>nome. A seleção de um procedimento nas caixas de listagem suspensas na parte superior</p><p>da janela Código move o cursor para a primeira linha de código no procedimento</p><p>selecionado.</p><p>Barra de divisão: Quando esta barra é arrastada para baixo, divide a janela código em</p><p>dois painéis horizontais, que podem ser rolados de modo independente. Em seguida,</p><p>podem ser visualizadas diferentes partes do código ao mesmo tempo. As informações</p><p>Caixa</p><p>objeto Caixa</p><p>procedimento</p><p>Barra do</p><p>indicador de</p><p>margem</p><p>Barra de</p><p>Divisão</p><p>Ícone: Visualizar</p><p>procedimento e visualizar</p><p>código completo</p><p>2013 Pág. 88</p><p>Idiomas e Informática</p><p>que aparecem na caixa Objeto e na caixa Procedimentos/Eventos se aplicam ao código</p><p>no painel que tem o foco. Para fechar um painel, arraste a barra para a parte superior ou</p><p>inferior da janela ou clique duas vezes sobre ela.</p><p>Barra do Indicador de Margem: Situado no lado esquerdo inferior da janela código,</p><p>esse botão tem por finalidade exibir o procedimento selecionado. A janela Código só</p><p>exibe um procedimento de cada vez.</p><p>Ícone Visualizar Módulo Completo: Situado no lado esquerdo inferior da janela</p><p>Código, esse botão tem por finalidade todo o código do módulo.</p><p>Criando a primeira Macro através</p><p>do código Visual Basic</p><p>1 -Selecione o Menu Exibição/Macro/Exibir Macro</p><p>2- Clicar no botão Editar, aparecerá a tela do Editor do visual Basic.</p><p>Obs: As macros criadas ficarão armazenadas em uma área chamada Módulos. Para</p><p>Criar um módulo, entre no Menu Inserir e escolha opção Módulo. Observe que será</p><p>criado um item na janela de projeto Chamado Módulo1 e uma janela de código vazia</p><p>aparecerá. É nesta janela que você criará sua macros.</p><p>3- Criar a macro Para “Selecionar Células”</p><p>a) Menu Inserir</p><p>b) Opção Procedimento</p><p>c) A Janela abaixo Aparecerá</p><p>2013 Pág. 89</p><p>Idiomas e Informática</p><p>Na caixa nome: Digite o nome do procedimento, por exemplo:</p><p>Selecionar_Células.</p><p>Na caixa Tipo defina como será o procedimento (Selecionando Sub), mais tarde</p><p>vamos ver como criar uma função.</p><p>d) Digitar o código abaixo:</p><p>• Para referenciar a uma única célula, o comando a ser usado é o Cells.</p><p>Sintaxe: Cells(Linha, Coluna).select</p><p>Este comando seleciona a célula da linha e coluna especificada. É o mesmo que dar um</p><p>clique sobre a célula.</p><p>1- Crie a macro abaixo:</p><p>• Selecionando um intervalo:</p><p>Para referenciar um intervalo e células, o comando a ser usado é Range.</p><p>Sintaxe: Range (intervalo).Select.</p><p>Este comando seleciona o intervalo de células especificado. Insira um novo</p><p>procedimento como o nome de selecionar_Intervalo, e digite o seguinte código:</p><p>2013 Pág. 90</p><p>Idiomas e Informática</p><p>2- Criar a macro abaixo:</p><p>Se você selecionar uma determinada planilha para que a macro seja executado</p><p>somente neta, o comando a ser utilizado é o Sheets.</p><p>Sintaxe: Sheets(Planilha).Select</p><p>Obs: O nome da Planilha deverá estar entre aspas duplas</p><p>3- Criar a macro abaixo:</p><p>• Escrevendo nas células</p><p>O comando para digitar algo em alguma célula é o comando já visto</p><p>anteriormente, o Cells.</p><p>Cells.</p><p>Sintaxe: Cells(linha,coluna)=valor</p><p>Se o valor for um texto, este deverá estar entre aspas. Se o valor for numérico, as aspas</p><p>não são necessárias. Não é necessário selecionar a célula.</p><p>Execute a macro e observe que o Excel preenche as células especificadas com o</p><p>texto, independente da localização da célula ativa.</p><p>4- Crie a macro abaixo:</p><p>2013 Pág. 91</p><p>Idiomas e Informática</p><p>• Pode-se também escrever algo na célula que está ativa quando a macro for</p><p>executada.</p><p>A palavra ActiveCell representa a célula ativa.</p><p>Para saber em qual linha a célula ativa, usa-se a expressão: ActiveCell.Row</p><p>Para saber e coluna da célula ativa usa-se ActiveCell.Column.</p><p>4.1.10- A utilização de Variáveis</p><p>Os Procedimentos e funções criados no Excel podem necessitar de valores que</p><p>venham a ser fornecidos durante a execução de uma macro. Quando se possui esta</p><p>necessidade, o melhor é trabalhar com variáveis, sendo estas uma região d memória</p><p>com um apelido e que conterá um valor pro vez. Esse valor pode ser um dado numérico,</p><p>lógico ou alfanumérico.</p><p>Para fazer uso de variáveis, é necessário que sejam definidas com o comando Dim</p><p>no início de cada procedimento Sub ou Function. Além da definição do nome da</p><p>variável, é aconselhável estabelecer o tipo de dado que a variável vai receber, como o</p><p>comando AS. Portanto, a sintaxe para a definição de uma variável é Dim variável As</p><p>Tipo.</p><p>O nome de uma variável deve, por definição, ser iniciado com letras minúsculas,</p><p>para não se confundir com as instruções do código Visual Basic, tais como: Nomes de</p><p>métodos, propriedades e objetos. O tipo deve ser uma das seguintes palavras-chave:</p><p>Tipos Descrição</p><p>Array Definir um tipo matriz. Este tipo é um conjunto de variáveis em que cada</p><p>elemento é referenciado por um índice. Este tipo de variável ocupa um</p><p>tamanho na matriz.</p><p>Boolean Definir um tipo lógico de dados, podendo ser um de dois valores lógicos:</p><p>True e False. Este tipo de dado ocupa um espaço de 2 bytes de memória.</p><p>Currency Definir um tipo monetário ou o estabelecimento de decimais fixos. Deve</p><p>ser utilizado quando se necessitar de uma precisão de cálculo bastante</p><p>grande. Este tipo de dado consegue fazer que uma variável venha a</p><p>trabalhar com valores que estejam no intervalo de -</p><p>922.337.203.685.477,5808 (Negativo) até 922.337.203.685.477,5808</p><p>(Positivo),ocupando um espaço de memória de 8 bytes. Este tipo de dado</p><p>possui o sinal @ (Arroba) como caractere de declaração de tipo.</p><p>2013 Pág. 92</p><p>Idiomas e Informática</p><p>Date Definir um tipo para datas e armazenar valores correspondente ao intervalo</p><p>de 1º de janeiro de 0100 até 31 de dezembro de 9999. Este tipo de dados</p><p>ocupa um espaço de 8 bytes de memória.</p><p>Double Definir a utilização de ponto flutuante de dupla precisão, ou seja, valores</p><p>decimais com até 16 casas. Este tipo de dado ocupa um espaço de 8 bytes,</p><p>permitindo que uma variável consiga trabalhar com valores negativos na</p><p>faixa de -1,79769313486232e308 até -4,94065645841247e-324. Os</p><p>valores positivos aceitos estão entre a faixa de 4,940656645841247e-324 e</p><p>1,79769313486232e308. Possui o sinal # (Tralha) como caractere de</p><p>declaração de tipo.</p><p>Integer Definir o tipo de valores inteiros pequenos, estando esses valores</p><p>concentrados no intervalo de -32768 (negativos) até 32767 (Positivo). Este</p><p>tipo de dado ocupa um espaço de 2 bytes de memória. Tem como caractere</p><p>de declaração de tipo o sinal % (Porcentagem)</p><p>Long Definir o tipo de valores inteiros grandes, estando esses valores</p><p>concentrados no intervalo de -2.147.483.648 (negativo) até 2.147.483.647</p><p>(Positivos). Este tipo de dado ocupa 4 bytes de memória tem como</p><p>caractere de declaração de tipo o sinal & (E comercial)</p><p>Object Este tipo de dado estabelece apenas referência a objetos, ocupando um</p><p>espaço de memória de 4 bytes.</p><p>Single Definir a utilização de ponto flutuante de precisão simples, ou seja, valores</p><p>decimais com até oito casas. Este tipo de dado ocupa um espaço de 4</p><p>bytes, permitindo que uma variável consiga trabalhar com valores</p><p>negativos aceitos estão entre a faixa de 1,401298 e 3,402823e38. Possui o</p><p>sinal! (Exclamação) como caractere de declaração de tipo.</p><p>String Definir a utilização de valores alfabéticos como: letras, pontos, números e</p><p>sinais do teclado. Este tipo de dado pode configurar uma variável de forma</p><p>que ela aceite até 64k por seqüência de string, ocupando o espaço de 1 byte</p><p>pro caractere. Possui o sinal de $ (Cifrão ou dólar) como caractere de</p><p>declaração de tipo.</p><p>Variant Utilizando para receber qualquer tipo de dado.</p><p>Byte Valores inteiros entre 0 a 255.</p><p>Para trabalhar com os tipos de dados e variáveis de forma adequada, é necessário</p><p>ter em mente alguns detalhes:</p><p>1- Caso seja declarada uma variável sendo de um tipo, e se tentar armazenar nela um</p><p>valor diferente do estabelecido, será retornada uma mensagem de erro. Para evitar este</p><p>tipo de transtorno, o Visual Basic possui característica de estabelecer um caractere para</p><p>ser utilizado na declaração de tipo no final do nome da variável. Deste forma será</p><p>estabelecido de que tipo é a variável em uso, além de servir como lembrete incorreto.</p><p>Por exemplo, uma variável, para receber um nome, pode ser indicada como Nome$. O</p><p>caractere $ estabelece que a variável nome é do tipo string.</p><p>2- Ao fazer uso de variáveis em macros e não efetuar a definição se seu tipo de dados, o</p><p>visual Basic automaticamente estabelece que tenham o tipo variante.</p><p>3-Caso você queira definir um tipo de dados de um argumento de uma função definida</p><p>pelo usuário, ele deve ser definido com a instrução As dentro da lista de argumentos da</p><p>2013 Pág. 93</p><p>Idiomas e Informática</p><p>função. Um exemplo possível é estabelecer para a função definida pelo usuário RQTotal</p><p>(Valor) da pasta de trabalho Funções Definidas pelo Usuário, que o argumento Valor</p><p>seja apenas de números inteiros, sendo assim a sintaxe da função passa a ser Function</p><p>RQTotal(Valor</p><p>as Integer)</p><p>4- A partir deste momento, a função retornará o cálculo dos valores da raiz quadrada</p><p>apenas da parte inteira de um número fornecido. Neste caso, se for pedida a raiz do</p><p>valor 2,456, será retornado 1,41421362 que é o valor da raiz quadrada do número 2.</p><p>5- Assim como é possível estabelecer o tipo de um valor a ser fornecido para a função,</p><p>pode-se também definir o tipo de dados do valor de retorno. Para que isso seja possível,</p><p>o comando As deve ser utilizado Dora da lista de argumentos da função.</p><p>6- Se houver a necessidade de atribuir a uma variável um objeto, deve ser feito como o</p><p>comando Set, tendo sintaxe Set variável = objeto, desde que a variável seja</p><p>previamente definida como do tipo Object.</p><p>7- Um outro detalhe importante a ser considerado com relação à variável é o fato de</p><p>digitá-la sempre de forma correta, o que nem sempre acontece. Aqui é apontado um dos</p><p>erros mais freqüentes em macros Visual Basic, que consiste em declarar uma variável e</p><p>depois escrevê-la errado. Por exemplo, você declara a variável Totalgeral e digita</p><p>totalgerl é uma nova variável e não tem absolutamente relação de nome correto</p><p>TotalGeral. Dessa foram, o procedimento será executado e, no final, irá apresentar o</p><p>seu resultado de forma errada.</p><p>8- Isto ocorre pelo dato de o Visual Basic suportar declarações implícitas de uma</p><p>variável, o que significa que durante o processamento, se for encontrado um nome de</p><p>variável não conhecida pelo Visual Basic, ele a considera como uma nova variável. Há</p><p>momentos em que isto se torna um verdadeiro transtorno.</p><p>9-Para evitar este tipo de problema, pode-se “dizer” ao Visual Basic para que verifique</p><p>e acuse um erro sempre que encontrar um nome não declarado previamente com as</p><p>instrução Dim, fazendo uso da instrução Option Explict. Você deve digitar a instrução</p><p>Option Explict antes do primeiro procedimento.</p><p>1- Em alguns casos, é necessário saber previamente o valor de uma propriedade antes</p><p>de efetuar alguma alteração nela. Neste caso, as variáveis podem ser bastante úteis,</p><p>bastando fazer uso da sintaxe variável = Objeto.propriedade. Por exemplo, poder-se-</p><p>ia atribuir à variável vir_atual o valor de uma célula ativa por meio da sintaxe:</p><p>Vlr_atualActiveCell.value.</p><p>4.2-Exercícios</p><p>5- Criar a macro abaixo: Nome da macro somar</p><p>2013 Pág. 94</p><p>Idiomas e Informática</p><p>6- Digitar valores para a célula A1 e A2</p><p>7- Executar a macro</p><p>8- Fazer mais quatro procedimentos para executar as outras operações. Sempre</p><p>alterando as células de origem dos números.</p><p>9- Nas células A1 e A2 digitar os valores 300 e 200.</p><p>10- Executar a macro somar. Verifique que deu um erro.</p><p>11- Prepara a macro somar para realizar os o calculo da questão 5.</p><p>12- Nas células A1 e A2 digitar os valores 2,8 e 3,1.</p><p>13- Execute a macro somar. Verifique o resultado foi inteiro. Prepare macro somar para</p><p>trabalhar com decimais.</p><p>14- Criar mais uma macro para executara potência 2³</p><p>15- Criar mais uma macro para executar a potência de 2^32</p><p>16- Criar a macro abaixo e procurar entender o código fonte</p><p>2013 Pág. 95</p><p>Idiomas e Informática</p><p>4.3-Criando caixa de Entrada</p><p>Mesmo cirando uma variável, como no exemplo anterior, o valor da variável</p><p>continua constante. Seria mais interessante se o Excel perguntasse para você o valor da</p><p>variável, ao invés de deixar fixo no código do procedimento. Para tanto, você usará o</p><p>comando InputBox.</p><p>O texto digitado dentro da caixa de entrada será armazenado na variável, que</p><p>deverá ser do tipo String. Caso o usuário cancele a janela, o texto retornado será vazio.</p><p>Exemplo: Crie o procedimento abaixo:</p><p>4.4-Criando caixa de Mensagem</p><p>O uso de mensagens por meio da barra de status passa muitas vezes despercebido,</p><p>pois o usuário na o olha para esse local com muita freqüência. Dessa forma, o recurso</p><p>não é muitas vezes tão eficiente quanto o esperado, principalmente, se for alguma tarefa</p><p>que necessita de maior atenção. Para este caso pode-se fazer uso da função MsgBox,</p><p>vista superficialmente em um capitulo anterior, a qual a seguinte sintaxe:</p><p>Msgbox (Mensagem, botões, título)</p><p>Em que:</p><p>• Mensagem: É a mensagem que será exibida dentro da caixa de mensagem.</p><p>• Botões: Número que especifica o tipo de botão (Código de 0 a 5), tipo de ícone</p><p>(Códigos 0, 256 ou 512). Veja tabela adiante.</p><p>2013 Pág. 96</p><p>Idiomas e Informática</p><p>• Título: A mensagem que deve ser exibida na barra de título da caixa de mensagem.</p><p>Uma caixa de diálogo apresenta uma mensagem juntamente com um ou mais</p><p>botões de comando.</p><p>Os valores para os códigos podem ser:</p><p>Botão Valor</p><p>OK 0</p><p>OK e Cancelar 1</p><p>Anular, Repetir e Ignorar 2</p><p>Sim, Não e Cancelar 3</p><p>Sim e Não 4</p><p>Repetir e Cancelar 5</p><p>Além dos botões. É possível Colocar Ícone na janela:</p><p>Ícone</p><p>Valor 16 32 48 64</p><p>4.5- Exemplo de caixa de mensagem:</p><p>Vamos criar uma macro que contenha os botões Sim e Não e o ícone da interrogação.</p><p>2013 Pág. 97</p><p>Idiomas e Informática</p><p>A função Msgbox retorna o código do botão selecionado conforme a tabela abaixo:</p><p>Quando quisermos que seja exibida apenas uma mensagem com um botão OK,</p><p>podemos utilizar o MsgBox da seguinte Forma:</p><p>MsgBox “Operação Concluída”, 64, “Atenção”</p><p>• Exemplo botão Sair</p><p>If MsgBox("Confirma a Saída? ", vbCritical + vbYesNo, "Atenção") = vbYes Then</p><p>End</p><p>End If</p><p>ou</p><p>If MsgBox("Confirma a Saída? ", 16 + 4, "Atenção") = vbYes Then</p><p>End</p><p>End If</p><p>4.6- Criando Funções</p><p>Até agora nós criamos Sub procedimentos, ou seja macros que executam funções</p><p>especificas. Você pode criar uma função para ser usado em uma fórmula, como uma</p><p>função Soma ou Média. Este tipo de procedimento é chamado de Function.</p><p>Criando uma Função: Exemplo 1</p><p>a) Voltar para o Editor do Visual Basic;</p><p>b) Clicar em Inserir e escolher a opção Procedimento, será inserida a janela abaixo:</p><p>Botão Selecionado Valor Retornado</p><p>OK 1</p><p>Cancelar 2</p><p>Anular 3</p><p>Retornar 4</p><p>Ignorar 5</p><p>Sim 6</p><p>Não 7</p><p>2013 Pág. 98</p><p>Idiomas e Informática</p><p>c) Neste exemplo, o nome do procedimento será o seu nome. Deixe selecionada a opção</p><p>Function para que o Excel crie uma função ao invés de Sub-Procedimento.</p><p>e) Clicar em OK, as seguintes linhas de código aparecerão:</p><p>As funções devem receber valores que serão processados para realizar uma saída.</p><p>Neste exemplo, a função Seu nome receberá dois valores e retornará a média dos</p><p>mesmos. Isto deve ser declarado na primeira linha da função da seguinte maneira:</p><p>Parâmetros: São as variáveis que a função receberá. Deve ser declarado dentro dos</p><p>parênteses da primeira linha da função.</p><p>Valor retornado: É o tipo de dado que a função retorna. Deve ser declarado no final da</p><p>primeira linha da função.</p><p>2013 Pág. 99</p><p>Idiomas e Informática</p><p>Executando uma função criada</p><p>Montar a planilha abaixo:</p><p>a) Após criar a função, você deverá:</p><p>b) Na guia Fórmulas e no botão Inserir Função</p><p>c) Escolha a categoria, Definida pelo usuário</p><p>d) Clique na função criada por você</p><p>e) Informa os valores para a função e depois OK.</p><p>Exemplo 2</p><p>a) Criar a planilha abaixo:</p><p>b) Criar a macro abaixo:</p><p>Valores recebidos</p><p>Valor1 e Valor2</p><p>Valores Retornado</p><p>2013 Pág. 100</p><p>Idiomas e Informática</p><p>4.7- Exercício – Criar a função Metro</p><p>Criar uma função para transformar centímetros em Metros.</p><p>Referências</p><p>Referências são endereços ou conjunto de endereços que o Excel utilizará</p><p>como base para nossos cálculos. Elas permitem a utilização do recurso</p><p>AutoPreenchimento</p><p>(cópia de células pela alça) também para fórmulas, quando houver</p><p>cálculos repetitivos. Há três tipos de referências:</p><p>2013 Pág. 101</p><p>Idiomas e Informática</p><p>Existem 3 tipos de referências</p><p>D5 Referência Relativa: Não fixa nem colunas e nem linhas</p><p>$D$5 Referência Absoluta: Fixa a coluna e a linha</p><p>$D5 Referência Mista: Fixa apenas a coluna, permitindo a variação da linha</p><p>D$5 Referência Mista: Fixa apenas a linha, permitindo a variação da coluna.</p><p>Referências Relativas</p><p>Observe o exemplo a seguir:</p><p>� Observe, a seguir, que por se tratar de uma fórmula, o Excel atualiza os endereços</p><p>na cópia.</p><p>No exemplo anterior, a fórmula busca encontrar o valor em dólar do produto</p><p>pneu. Para calcular o próximo produto (HD) seria necessário utilizar a fórmula =B5*C5,</p><p>o que teoricamente não é uma cópia da fórmula anterior. Porém se copiarmos a fórmula</p><p>para a célula D5, automaticamente os endereços serão atualizados.</p><p>Referências Absolutas</p><p>Há cálculos, em que cópias de fórmulas não são corretas através da alça de</p><p>preenchimento.</p><p>Isso ocorre quando um dos valores envolvidos na fórmula permanecem sempre</p><p>em uma mesma célula. Observe o exemplo:</p><p>Esta fórmula</p><p>calcula o valor</p><p>em dólar do</p><p>produto</p><p>gabinete</p><p>2013 Pág. 102</p><p>Idiomas e Informática</p><p>� Observe que neste caso, a cópia da primeira fórmula não funciona, pois devido a</p><p>atualização dos endereços, a célula C3, na Segunda fórmula, passou a ser C4, mas</p><p>deveria permanecer como C3, afinal o valor do dólar permanece sempre nesta célula.</p><p>Então, qual seria a solução? Digitar fórmula por fórmula? Bem acho que não!</p><p>Neste caso, devemos informar ao Excel para não alterar o endereço no momento da</p><p>cópia.</p><p>� Utilizaremos o caráter $ para fixar o endereço da célula C3.</p><p>Ex:</p><p>$C$3 Fixa a coluna e a linha.</p><p>Referência Mista</p><p>Há casos em que teremos que trabalhar com fórmulas que terão somente linhas</p><p>ou colunas fixadas.</p><p>No próximo exemplo para poder copiar a fórmula, tanto para os próximos</p><p>meses como para todos os itens, é necessário fixar algumas linhas e colunas</p><p>cuidadosamente.</p><p>A fórmula deve ser fixada da seguinte maneira:</p><p>=(B$12*$B2)+B$12</p><p>No valor base foi fixada somente a linha, para que na cópia, do próximo mês,</p><p>(vertical) o número da linha sofra alteração. Porém, na cópia para o próximo produto,</p><p>(horizontal) a coluna sofrerá alteração para C, portanto, referindo-se corretamente ao</p><p>valor base do automóvel seguinte.</p><p>Neste caso, a</p><p>atualização está</p><p>incorreta devido ao</p><p>valor do dólar</p><p>permanecer sempre na</p><p>mesma célula (C3)</p><p>2013 Pág. 103</p><p>Idiomas e Informática</p><p>=(B$12*$B2)+B$12</p><p>Na porcentagem, foi fixada somente a coluna para que, na cópia para o</p><p>próximo produto, (horizontal) a coluna não seja alterada, obedecendo o valor respectivo</p><p>de cada mês, porém na cópia para o próximo mês, a linha será alterada obedecendo a</p><p>porcentagem referente ao mês.</p><p>2013 Pág. 104</p><p>Idiomas e Informática</p><p>Avaliação de Excel</p><p>1- Marque a fórmula que representa corretamente o resultado de soma e média</p><p>considerando a tabela Soma e Média.</p><p>a) =B3+B4+B5+B6 | = C8/4</p><p>b) =SOMA(C3:C6) | MÉDIA(C3:C6)/4</p><p>c) =SOMA(C3:C6) | MÉDIA(C3:C6)</p><p>d) A,B,C estão erradas</p><p>2- Observando as afirmativas abaixo, marque a opção correta.</p><p>a) As abas (plan1, plan2, plan3) podem mudar de nomes.</p><p>b) As colunas e linhas possuem uma largura e altura padrão, porém é possível</p><p>modificar.</p><p>c) A cor de fundo da célula pode ser alterada automaticamente conforme seu conteúdo.</p><p>d) Mesclar células, formato Moeda, Escrever na diagonal são comandos do Excel.</p><p>A) Somente a opção (a) está correta</p><p>B) Somente as opções (a e b) estão corretas</p><p>C) Somente as opções (a, b, c) estão corretas</p><p>D) Todas as opções estão corretas</p><p>3- Considerando a formula abaixo será arrastado para baixo e para direita,</p><p>marque a alternativa que representa as formulas que serão geradas.</p><p>=A2+$B2+C$3+$D$4</p><p>a) A formula ficara a mesma, pois não se alteram ao arrastar.</p><p>b) Para baixo será =A2+ $B3 + C$3 +$D$4. Para esquerda =B1 + $B2 +D$3 + $D$4</p><p>c) A Fórmula está errada e o cifrão deverá ser retirado.</p><p>d) Para baixo será =A2+$C3+C$4+$D$5. Para a esquerda será =B1+$C2+D$3+$E$4</p><p>2013 Pág. 105</p><p>Idiomas e Informática</p><p>4- Observando a planilha abaixo, responda qual a formula esta correta, para</p><p>mostrar o valor total do Mês digitado na célula E13.</p><p>a) = SOMASES(F12;F22:E12;E22:H13)</p><p>b) =SOMASES(F12:F22;E12:E22;H13)</p><p>c) =SOMASES(H13;F12:F22;E12:E22)</p><p>d) =F12+F14+F19+F21+F22</p><p>e) Nenhuma das opções anteriores</p><p>5- Observando o texto abaixo marque a letra correspondente ao comando</p><p>necessário para processar a planilha.</p><p>“Tenho uma planilha com aproximadamente 15.000 linha e 50 colunas. Nela contém</p><p>dados relacionados a matriculas de alunos. Existem colunas como Mês da Matricula,</p><p>Curso, Turma entre outras. Preciso saber a quantidade de alunos em cada mês,</p><p>separados turma, curso.</p><p>2013 Pág. 106</p><p>Idiomas e Informática</p><p>a) Validação</p><p>b) Filtro</p><p>c) Remover Duplicadas</p><p>d) Mesclar planilha</p><p>e) Tabela Dinâmica</p><p>6- Sobre Gráficos no Excel, Marque a alternativa errada</p><p>a) É possível ter um gráfico em uma aba e a tabela em outra aba</p><p>b) Em um gráfico de coluna, o comando alternar linha/coluna faz a legenda ir par ao</p><p>eixo e o eixo para a legenda.</p><p>c) É possível o Excel calcular a linha de tendência</p><p>d) O procv não pode ser usado na tabela que será usado pelo gráfico</p><p>e) O Excel faz gráficos de pizzas contendo duas pizzas.</p><p>7- Considerando os comandos do Excel marque a alternativa errada</p><p>a) As células A1:A200 podem receber o nome VALORES e a fórmula</p><p>=SOMA(Valores) estará somando os valores.</p><p>b) Um resultado de fórmula pode aparecer dentro de uma imagem ou caixa de texto.</p><p>c) A Fórmula =SOMA(PLAN1:PLAN3!A1) está correta.</p><p>d) É Possível no Excel através de uma única fórmula calcular os dias úteis entre duas</p><p>datas fazendo a quarta-feira, não ser um dia útil e a os demais dias ser dia útil.</p><p>e) A parte inteira de um número representa datas e a parte decimal representa as horas.</p><p>f) É possível classificar automaticamente as abas em ordem decrescente.</p><p>65</p><p>Mesclando funções e fórmulas ..................................................................................... 65</p><p>Exercícios Função Arrumar ............................................................................................ 68</p><p>Exercício Função Corresp .............................................................................................. 69</p><p>Exercício Função Seerro ................................................................................................. 70</p><p>Exercício. Funções Dias360, Se, Máximo e Mínimo .................................................... 70</p><p>Exercício. Testando um CPF se o valor e Correto o Inválido. Usando Função MOD,</p><p>SE, E e a ou. ................................................................................................................... 71</p><p>Exercício. Função concatenar ......................................................................................... 71</p><p>INTRODUÇÃO A FÓRMULAS MATRICIAIS ........................................................... 72</p><p>Introdução a Macros: O que são Macros? ................................................................. 74</p><p>2.1- Os Tipos de Macros ........................................................................................... 74</p><p>2.2- Necessidade de Planejar uma Macro ............................................................... 75</p><p>2.3-Maneiras de Criar um macro ............................................................................ 75</p><p>2.4- Executando um macro ...................................................................................... 78</p><p>2.4.1.- Executando um macro usando a tecla de atalho. ................................... 78</p><p>2.4.2- Executando a Macro usando o Menu Ferramentas ................................ 78</p><p>2.4.3- Criando um Menu ...................................................................................... 79</p><p>2.4.4- Criando um botão de comando ................................................................. 80</p><p>Exercícios Criar a Macro Abaixo: .......................................................................... 80</p><p>2.5- Conhecendo do que é feita uma Macro ........................................................... 81</p><p>2.6- Tipos de Macros ................................................................................................ 83</p><p>2.7- Exercícios: .......................................................................................................... 84</p><p>Macros com Programação ....................................................................................... 85</p><p>Criando a primeira Macro através do código Visual Basic .................................. 88</p><p>4.2-Exercícios ............................................................................................................ 93</p><p>4.3-Criando caixa de Entrada ................................................................................. 95</p><p>4.4-Criando caixa de Mensagem ............................................................................. 95</p><p>4.6- Criando Funções .................................................................................................. 97</p><p>Referências ................................................................................................................... 100</p><p>Existem 3 tipos de referências ...................................................................................... 101</p><p>Referências Relativas ............................................................................................... 101</p><p>Referências Absolutas .............................................................................................. 101</p><p>Referência Mista ....................................................................................................... 102</p><p>2013 Pág. 3</p><p>Idiomas e Informática</p><p>Excel Avançado</p><p>Se você está começando a conhecer O Microsoft Excel agora, talvez não saiba que</p><p>ele foi lançado em 1985 e cresceu até se tornar, sem dúvida, o programa de computador</p><p>mais importante nos locais de trabalho em todo o mundo. Não importa se você estiver</p><p>criando um orçamento, organizando uma lista de vendas, ou planejando uma reunião de</p><p>negócios, o Excel é a ferramenta indicada para esta tarefa.</p><p>Se você estiver em um departamento de finanças ou contabilidade ou em qualquer</p><p>escritório corporativo, certamente você irá ver telas de computadores cheios de</p><p>planilhas do Excel demonstrando os resultados financeiros, orçamentos, previsões e</p><p>dados para tomadas de decisões.</p><p>A área de finanças certamente é uma das áreas que mais se beneficiam das</p><p>planilhas de Excel. Fórmulas avançadas podem transformar processos manuais que</p><p>antes levariam semanas para serem concluídos, em algo que leva apenas alguns minutos</p><p>para ser concluído.</p><p>A maioria dos usuários sabem que o Excel pode fazer cálculos como adição e</p><p>multiplicação, mas a verdade é que o Excel pode fazer muito mais que isso. Funções</p><p>como SE, PROCV, ÍNDICE, CORRESP e DESLOC, recursos como Tabelas Dinâmicas</p><p>e Gráficos são apenas alguns exemplos que podem surpreender os usuários mais</p><p>desavisados.</p><p>Mas não pense que apenas os setores financeiros se beneficiam do Excel. Áreas</p><p>como Marketing e Produção também utilizam o Excel no dia-a-dia. Uma equipe de</p><p>vendas pode fazer a gestão da equipe, das metas e da força e planos de vendas.</p><p>Utilizando uma tabela dinâmica, o supervisor pode rápida e facilmente resumir dados</p><p>dos clientes, agrupando-os por categoria de produtos, datas, vendedores ou outro</p><p>critério que desejar, tudo isso com apenas um rápido clique, ou arrastando e soltando a</p><p>informação.</p><p>Com o Excel também é possível trabalhar com dados provenientes de fontes</p><p>externas e outros sistemas de banco de dados, como Oracle, SAP, SQL Server, e</p><p>gerenciar folhas de pagamentos e informações de funcionários, exportando esses dados</p><p>em Excel, e com isso analisar tendências, resumir despesas e horas trabalhadas por</p><p>período, e utilizar essas informações para replicar a remuneração dos seus funcionários,</p><p>por exemplo.</p><p>A grande verdade é que você pode fazer qualquer coisa em uma planilha. Usando</p><p>o Excel no negócio quase não tem limites de aplicação. O Excel pode ser aplicado</p><p>inclusive na vida pessoal, como para controlar custos da residência, mesada dos filhos,</p><p>lista de tarefas diárias, e outras quase infinitas aplicações.</p><p>Conhecer e dominar o Excel é vital para a maioria dos profissionais com base em</p><p>um escritório hoje, e as habilidades mais fortes do Excel pode abrir a porta para a</p><p>promoção e oportunidades de liderança.</p><p>O Excel é uma ferramenta poderosa, mas não pode funcionar sozinho. É preciso</p><p>um usuário experiente que possa tirar proveito de tudo que o Excel tem a oferecer, e</p><p>assim obter os melhores resultados para sua vida, seja no mundo dos negócios ou no</p><p>âmbito pessoal.</p><p>E é exatamente neste ponto que queremos ajudar você.</p><p>2013 Pág. 4</p><p>Idiomas e Informática</p><p>Para dizer que possui Excel Avançado, a frase "Excel Avançado" é suficiente,</p><p>você terá chances de explicar na Entrevista, e até provar através de testes teóricos e</p><p>práticos.</p><p>A palavra "noção" realmente não é usada, porque denota que você sabe o que é</p><p>Excel, mas não necessariamente sabe abrí-lo e criar uma planilha. Ao invés disso use</p><p>básico ou intermediário, de acordo com sua Auto-Avaliação.</p><p>O nível de conhecimento de Excel é baixo no mercado de trabalho, sendo assim se</p><p>você sabe formatar uma planilha, fazer somas, médias, tabelas dinâmicas e procv's você</p><p>já pode se considerar avançado.</p><p>Agora o que se pode considerar verdadeiramente avançado / expert - coisa que eu</p><p>raras vezes vejo por aí (sei avaliar por ter estes conhecimentos).</p><p>Domínio de Fórmulas e Recursos do Excel (Se, Procv, Proch, Éerros, E, OU e</p><p>etc).</p><p>Gráficos - Manipulação Dinâmica de DataSeries e DataPoints, Exibição de Eixos</p><p>em Milhares,Milhões e etc, formatação, manipulação de Horas e gráficos multi-escopo.</p><p>- VBA - Manipulação de Texto</p><p>(Mid, Left, Right, Len, Chr e etc)</p><p>- VBA - Loops (For, While, Do)</p><p>VBA - Seletores de Código (Select Case, If, Elseif, On Errors)</p><p>- Retrieve de informações da Internet via AJAX (HTTP.Response.Send).</p><p>- Integração com Aplicativos do Office - Envio de E-mails, gerenciamento de</p><p>Apresentações, Leitura de Recordsets.</p><p>- Manipulação de SGBD's via ADO, DAO ou ODBC (Access, SQL Server,</p><p>Oracle). - Integração com aplicativos com API (ex: Avaya CMS, ferramentas SAP)</p><p>- Leitura de TXT's em batch e manipulação de arquivos usando Windows Script</p><p>Runtime.</p><p>Fórmulas</p><p>As fórmulas devem ser precedidas pelo sinal de igual (=), porém se você estiver</p><p>utilizando o Assistente de função o Excel se encarrega de incluir o sinal de igual.</p><p>As fórmulas são mecanismos de uma planilha que permite a você calcular</p><p>resultados a partir de dados na planilha. São permitidos os seguintes operadores.</p><p>Operadores aritméticos</p><p>Os operadores aritméticos estão sempre acompanhados por constantes ou por</p><p>referências às células. Chamamos de operandos os valores sobre os quais o operador</p><p>age. Existem dois tipos de operadores aritméticos:</p><p>• Os operadores unários, que atuam sobre um único elemento, admitindo assim</p><p>um único operando, situado à direita do operador. Ex: =-A6</p><p>• Os operadores binários, que atuam sobre dois elementos (admitindo dois</p><p>operandos, situados a cada lado do operador). Ex: =B4+A6</p><p>Os operadores aritméticos são:</p><p>2013 Pág. 5</p><p>Idiomas e Informática</p><p>Operador Denominação Tipo Descrição</p><p>+ Operador de adição Binário Adiciona dois valores</p><p>- Operador de subtração Binário Substrai dois valores</p><p>- Operador de inversão Unaire Dá o valor oposto de um valor</p><p>* Operador de multiplicação Binário Multiplica dois valores</p><p>/ Operador de divisão Binário Divide dois valores</p><p>^ Operador de potência Binário Eleva o operando da esquerda</p><p>à potência especificada pelo</p><p>operando da direita</p><p>% Operador de módulo Binário Dá o resto da divisão entre 2</p><p>números</p><p>O uso de parênteses</p><p>Os operadores aritméticos tem uma ordem de prioridade. Para alterá-la, você</p><p>pode usar parênteses.</p><p>A tabela abaixo mostra a prioridade dos operadores em ordem decrescente:</p><p>Operador Descrição</p><p>() Parênteses</p><p>:(dois-pontos),(vírgula)espaço</p><p>simples</p><p>Operadores de referência</p><p>- Negação (como -1)</p><p>% Porcentagem</p><p>^ Expoente</p><p>* e / Multiplicação e divisão</p><p>+ e - Adição e subtração</p><p>& Concatenação de duas cadeias</p><p>de texto</p><p>= < > <= >= <> Operadores de comparação</p><p>2013 Pág. 6</p><p>Idiomas e Informática</p><p>Exercício 1 – Montar a planilha abaixo.</p><p>a) Criar um pasta dentro de documento com seu nome e Salvar a Pasta</p><p>b) Renomear a planilha para Funcionários</p><p>c) Centralizar a planilha no centro de página</p><p>d) Aplicar cabeçalho e rodapé na planilha</p><p>e) Copiar essa planilha para mais 2 abas</p><p>f) O cálculo da comissão será 10% do valor do salário</p><p>g) O cálculo do total será o valor do salário + a comissão</p><p>Exercício 2 – Montar a planilha abaixo.</p><p>2013 Pág. 7</p><p>Idiomas e Informática</p><p>a) Calcule o Preço, a venda e o lucro de cada Produto</p><p>b) Renomear a Planilha para Produtos</p><p>c) Centralizar a planilha no centro de página</p><p>d) Aplicar cabeçalho e rodapé na planilha</p><p>2013 Pág. 8</p><p>Idiomas e Informática</p><p>Funções</p><p>Uma função é uma fórmula predefinida que realiza cálculos usando valores</p><p>específicos adicionados por você. Uma das principais vantagens de usar estas funções, é</p><p>que podemos economizar bastante nosso tempo pois elas já estão prontas e não é</p><p>necessário que você a digite totalmente.</p><p>Uma função é uma fórmula especial, pré-definida, que toma um ou mais valores</p><p>(os parâmetros), executa uma operação e produz um valor ou valores. As funções</p><p>podem ser usadas isoladamente ou como bloco de construção de outras fórmulas. O uso</p><p>de funções simplifica as planilhas, especialmente aquelas que realizam cálculos</p><p>extensos e complexos. Por exemplo, ao invés de digitar a</p><p>fórmula =A1+A2+A3+A4+...+A200 , você pode usar a função SOMA(A1:A200) , para</p><p>calcular a soma das células do intervalo entre a célula A1 e a célula A200.</p><p>Se uma função aparecer no início de uma fórmula, anteceda-a com um sinal de</p><p>igual, como em qualquer fórmula. Os parênteses informam ao Excel onde os</p><p>argumentos iniciam e terminam, lembre-se de que não pode haver espaço antes ou</p><p>depois dos parênteses. Os argumentos podem ser números, textos, valores lógicos ou</p><p>referências.</p><p>Para usar uma função, basta incluí-la nas fórmulas da planilha. A sequência de</p><p>caracteres usada em uma função é chamada sintaxe. Todas as funções têm basicamente</p><p>a mesma sintaxe. Se você não seguir esta sintaxe, o Microsoft Excel exibirá uma</p><p>mensagem indicando que há um erro na fórmula. Seja qual for o tipo de fórmula, ao</p><p>incluir uma função no início de uma fórmula, inclua um sinal de igual antes da função.</p><p>Os argumentos são especificados sempre dentro dos parênteses. Os argumentos</p><p>podem ser números, texto, valores lógicos, matrizes, valores de erro ou referências a</p><p>uma célula ou faixa de células. Para que o argumento seja válido, é preciso que ele gere</p><p>um valor válido. Algumas funções aceitam argumentos opcionais, não necessários para</p><p>que a função execute os cálculos.</p><p>2013 Pág. 9</p><p>Idiomas e Informática</p><p>1- Funções Estatísticas</p><p>1.1- Função Média</p><p>Objetivo:</p><p>Retornar a média (aritmética) dos argumentos que podem ser números ou nomes,</p><p>matrizes ou referências que contêm números.</p><p>Sintaxe:</p><p>MÉDIA(núm1;núm2;...) até 255 argumentos numéricos</p><p>ð núm1 argumento numérico cuja média se pretende obter.</p><p>Na célula B10 temos a seguinte fórmula: Média(B3:B7), ou seja, a média dos</p><p>valores equivale à sua somatória dividida pelo número de itens somados: 15.000,00/5 =</p><p>3.000,00.</p><p>A média aritmética simples também é conhecida apenas por média. É a medida de</p><p>posição mais utilizada e intuitiva de todas.</p><p>Ela está presente em nosso dia-dia que qualquer pessoa entende seu significado e a</p><p>utiliza com freqüência</p><p>A média de um conjunto de valores numéricos é calculada somando-se todos estes</p><p>valores e dividindo-se o resultado pelo número de elementos somados, que é igual ao</p><p>número de elementos do conjunto, ou seja, a média de n números é sua soma dividida</p><p>por n.</p><p>2013 Pág. 10</p><p>Idiomas e Informática</p><p>1.2- Função Soma</p><p>Somando Células Horizontais</p><p>Façamos, por exemplo, uma planilha de gastos mensais, a fim de descobrir o valor</p><p>gasto por uma determinada pessoa no final de um mês (usaremos valores simples para</p><p>melhor compreensão). Após transpor os valores para a planilha no Excel, proceda da</p><p>seguinte maneira:</p><p>Clique na última célula da primeira linha da planilha, e escreva a</p><p>função =SOMA(B2:E2), onde irá ser somado os valores das células B2 até E2.</p><p>2. Pressione a tecla ENTER e observe que a célula F2 apresentará a soma dos valores.</p><p>Somando Células Verticais</p><p>Após lançadas as despesas de todos os meses, iremos encontrar o valor total</p><p>anual, ou seja, somando-se os totais de cada mês e criando uma nova célula para o valor</p><p>total.</p><p>Primeiramente expanda a função da célula F2 para as demais células desta coluna:</p><p>dando um duplo clique na célula, observe que há no canto inferior direito um ponto,</p><p>arraste-o para baixo, até a última célula que será utilizada da coluna.</p><p>2013 Pág. 11</p><p>Idiomas e Informática</p><p>1.3- Função Mínimo e Máximo</p><p>Talvez em algum momento você já precisou descobrir o valor máximo ou mínimo</p><p>de algum dado em uma planilha do Excel, seja relacionada à venda, notas… e para isso</p><p>podemos facilmente utilizar as funções máximo e mínimo, existente no Excel.</p><p>1.4- Função Maior e Menor</p><p>Vamos supor que você tem a necessidade de encontrar o terceiro maior valor do</p><p>intervalo que vai de B2 até B9, o que você faria hoje? Imagino que, os que nunca</p><p>aprenderam profundamente o Excel, iriam usar o recurso de Classificação Crescente,</p><p>para visualmente indicar o terceiro maior valor, estou certo?</p><p>Você não precisa fazer isso. Basta você usar uma função muito simples do nosso</p><p>Microsoft Excel: A função Maior. A sintaxe dela se divide em duas partes: o intervalo e</p><p>a classificação desejada, no nosso exemplo, nós teríamos a seguinte montagem da</p><p>fórmula:</p><p>=MAIOR(B2:B9;3)</p><p>Onde a primeira parte antes do ponto e vírgula corresponde ao intervalo que você</p><p>vai analisar e a segunda parte corresponde à classificação que você deseja encontrar o</p><p>valor. No nosso caso ele encontrará o terceiro maior valor do intervalo que vai da</p><p>célula B2 até a célula B9.</p><p>2013 Pág. 12</p><p>Idiomas e Informática</p><p>1.5-Função Cont.se</p><p>A Função CONT.SE permite contar a quantidade de elementos contidos em um</p><p>conjunto de dados que atendem aos mesmos critérios de seleção. Sintaxe: =CONT.SE</p><p>(intervalo;critérios)</p><p>Em uma planilha de clientes podemos usar a Função CONT.SE para descobrir:</p><p>quantos clientes são homem/mulher? Quantos clientes compraram mais de 2 vezes? Ou</p><p>ainda saber quantos clientes compraram menos de R$ 2000,00.</p><p>2013 Pág. 13</p><p>Idiomas e Informática</p><p>1.6- Função CONT.SES</p><p>A função CONT.SES faz parte da versão 2010 do Excel, e é uma evolução da</p><p>função CONT.SE.</p><p>Enquanto que a função CONT.SE permitia apenas um único critério, o</p><p>CONT.SES permite um total de 127 critérios.</p><p>Montar a planilha abaixo:</p><p>2013 Pág. 14</p><p>Idiomas e Informática</p><p>1.7- Função Cont.Valores</p><p>A função CONT.VALORES conta o número de células que não estão vazias em</p><p>um intervalo.</p><p>A função Cont.valores, permite contar dentro de um intervalo, apenas células que</p><p>possuem valores ou seja células que não são vazias.</p><p>A sintaxe da fórmula é:</p><p>=Cont.valores(valor1;[valor2])</p><p>1.8- Função Contar.vazio</p><p>A função Contar.vazio permite contar dentro de um intervalo a quantidade de</p><p>células que possuem valores vazios.</p><p>A sintaxe da fórmula é:</p><p>=Contar.vazio(Intervalo)</p><p>1.9- Função Cont.Núm</p><p>A função CONT.NÚM conta o número de células que contêm números e conta os</p><p>números na lista de argumentos. Use a função CONT.NÚM para obter o número de</p><p>entradas em um campo de número que esteja em um intervalo ou uma matriz de</p><p>2013 Pág. 15</p><p>Idiomas e Informática</p><p>números. Por exemplo, você pode inserir a seguinte fórmula para contar os números no</p><p>intervalo A1:A20: =CONT.NÚM(A1:A20)</p><p>Para responder a primeira questão acima, “Total de inscritos?“, clique na célula</p><p>onde deseja que seja exibido o resultado. Para nosso exemplo, selecione a célula “H2″ e</p><p>digite a fórmula:</p><p>=CONT.VALORES(B3:B16)</p><p>Onde, “B2:B21“, corresponde ao intervalo na planilha onde serão listados os</p><p>nomes dos participantes do curso.</p><p>Agora, para saber “Quantos participantes ainda não pagaram a matrícula?“, faça</p><p>assim na célula “F3″:</p><p>=CONT.SE(B3:B16;”Não”)</p><p>Onde, “B3:B16“, corresponde ao intervalo onde está descrito quem já pagou ou</p><p>ainda falta pagar a matrícula. E o termo após o “;” (ponto-e-vírgula) corresponde ao</p><p>critério a ser localizado e contado.</p><p>E, por fim, para saber “Quantas vagas ainda estão disponíveis?“, digite na célula</p><p>“F4″:</p><p>=CONTAR.VAZIO(A2:A20)</p><p>Fórmula esta que, no intervalo “A2:A20“, conta todas as células que ainda estão</p><p>vazias.</p><p>1.10- Função MédiaSe</p><p>A função é uma mistura da função Média com a função SOMASE. Não é à toa</p><p>que o nome dela é MÉDIASE. Ela faz o cálculo de médias, porém com condições.</p><p>2013 Pág. 16</p><p>Idiomas e Informática</p><p>Temos do lado esquerdo uma lista de nomes, idades e salários. Caso quiséssemos</p><p>saber a média do salário de todo mundo, bastaria usar a função MÉDIA que estaria</p><p>resolvido. Porém no exemplo queremos saber, separadamente, qual a média salarial das</p><p>pessoas que têm até 30 anos (inclusive) e das que têm acima de 30. Ou seja, é</p><p>uma média baseada em critério (idade, neste caso). Sendo assim, vamos usar</p><p>afunção MÉDIASE.</p><p>De forma resumida, funciona assim: em intervalo, vamos definir onde queremos</p><p>que seja feita a verificação do critério. Como o critério é idade, então a verificação será</p><p>feita na coluna B (de B2 a B10). O critério é qual a condição para o cálculo da média.</p><p>No caso de F7, é idade maior que 30. O critério deve ser inserido entre aspas. Sendo</p><p>assim, neste caso ficará ” >30″.</p><p>O intervalo média é o intervalo no qual a médias das linhas “selecionadas” será</p><p>calculada. Esta parte é opcional (por isso aparece entre colchetes acima). Se for omitida,</p><p>o Excel usará como intervalo de média o primeiro intervalo.</p><p>Para o cálculo da média de quem ganha acima de 30 anos (célula F7), a fórmula</p><p>ficará assim:</p><p>=MÉDIASE(B2:B10;”>30″;C2:C10)</p><p>2013 Pág. 17</p><p>Idiomas e Informática</p><p>1.11- Função MédiaSes</p><p>Quando você deseja a média (aritmética) dos argumento, utilize a função MÉDIA.</p><p>Caso queira a média de um intervalo de células, mas que satisfazem um</p><p>determinado critério, usa-se a função MÉDIASE</p><p>Já se quiser uma média de todas as células que satisfazem vários critérios, use a</p><p>função MÉDIASES.</p><p>Como usar ?</p><p>MÉDIA (núm 1; [núm 2]; �)</p><p>MÉDIASE (intervalo; critérios; [intervalo_média])</p><p>MÉDIASES (intervalo_média; intervalo_critérios 1; critérios 1;</p><p>[intervalo_critérios 2]; �)</p><p>Onde :</p><p>“núm 1” – O primeiro número, referência de célula ou intervalo para o qual você</p><p>deseja a média.</p><p>“intervalo” – Uma ou mais células a serem usadas para o cálculo da média,</p><p>incluindo números ou nomes, matrizes ou referências que contêm números.</p><p>“critérios” – Os critérios na forma de um número, uma expressão, uma referência</p><p>de célula ou um texto que define quais células serão usadas para o cálculo da média.</p><p>“intervalo_média” – (Opcional) O conjunto real de células que será usado para</p><p>calcular a média. Se omitido, será usado o intervalo.</p><p>“intervalo_critérios 1” – Os intervalos de 1 a 127 para avaliar os critérios</p><p>associados.</p><p>“critérios 1” – critérios de 1 a 127 na forma de um número, uma expressão, uma</p><p>referência de célula ou um texto que define quais células serão usadas para calcular a</p><p>média.</p><p>2013 Pág. 18</p><p>Idiomas e Informática</p><p>Sisntaxe Somases</p><p>2- Funções Lógicas</p><p>As funções condicionais no Excel nos ajudam nas tomadas de decisões. Elas</p><p>podem ser combinadas e aninhadas (você pode colocar uma função dentro de outra</p><p>função) dependendo da situação.</p><p>2.1- Função Se</p><p>A função SE, a partir dos valores nela adicionados, avalia os dados da célula e</p><p>retorna a expressão escolhida caso o resultado for falso ou verdadeiro. Muito utilizada</p><p>como complemento em outras funções, ela apresenta, em seu quadro de argumentos, a</p><p>sequência SE(teste_lógico;[valor_se_verdadeiro];[valor_se_falso]), onde:</p><p>a) teste_lógico - Argumento obrigatório que consiste no valor ou expressão</p><p>que será avaliado como VERDADEIRO ou FALSO.</p><p>b) valor_se_verdadeiro - Argumento opcional, onde o usuário pode escolher</p><p>qual valor ou expressão retornará caso o resultado do teste_lógico for verdadeiro.</p><p>c) valor_se_falso - Argumento opcional que possibilita a escolha do valor ou</p><p>expressão caso o teste_lógicofor falso.</p><p>Observação: Na criação do argumento se o usuário somente designar uma</p><p>expressão para o valor_se_verdeiro, por exemplo, deixando o restante em branco e,</p><p>2013 Pág. 19</p><p>Idiomas</p><p>e Informática</p><p>caso o resultado não condizer com a verdade, a função retornará automaticamente a</p><p>expressão FALSO, e assim vice e versa.</p><p>Como exemplo, utilizaremos uma planilha contendo nomes de alguns alunos e</p><p>suas respectivas médias escolares, onde se deseja conhecer qual sua situação, levando-</p><p>se em consideração de que a média de aprovação é de 6,5. Veja como proceder no passo</p><p>a passo abaixo:</p><p>Na primeira célula referente aos resultados, aplique a função</p><p>=Se(B2>=6,5;”Aprovado”;”Reprovado”), a qual significa que, se o valor de célula B2</p><p>for maior ou igual a 6,5, o resultado será Aprovado e caso contrário, retornará</p><p>Reprovado.</p><p>2.2- Função Seerro</p><p>A Função SEERRO verifica se uma expressão ou valor resulta em erro. Se assim</p><p>for, o resultado será um valor alternativo (valor_se_erro), caso contrário o resultado será</p><p>a própria expressão.</p><p>Tratar um erro significa dar opções de correções para uma fórmula que</p><p>inevitavelmente irá retornar um erro como resposta. No exemplo abaixo a coluna D</p><p>calcula o percentual que o frete representou em relação ao valor da nota fiscal. A célula</p><p>D2 contém a fórmula: =C2/B2</p><p>2013 Pág. 20</p><p>Idiomas e Informática</p><p>2.3- Função E</p><p>Devolve VERDADEIRO se todos os argumentos devolverem VERDADEIRO;</p><p>devolve FALSO se um ou mais argumentos devolverem FALSO.</p><p>Uma utilização comum para a função E consiste em expandir a utilidade das</p><p>outras funções que executam testes lógicos. Por exemplo, a função SE executa um teste</p><p>lógico e, em seguida, devolve um valor se o teste devolver VERDADEIRO e outro</p><p>valor se o teste devolver FALSO. A utilização da função E como</p><p>argumento teste_lógico da função SE permite testar várias condições diferentes em vez</p><p>de apenas uma.</p><p>Exemplo: No Exemplo abaixo o aluno será aprovado se a média for maior ou igual a 6 e</p><p>o número de faltas menor que 10.</p><p>Obs: Fazer a questão abaixo digitando a função e depois usando o assistente de funções</p><p>2.4- Função OU</p><p>Devolve VERDADEIRO se apenas um dos argumentos devolverem</p><p>VERDADEIRO; devolve FALSO se todos os argumentos devolverem FALSO.</p><p>A sintaxe da fórmula OU é: OU(lógico1, [lógico2], ...). Ela pode ser escrita com</p><p>valores que vão de 1 a 255. Assim como em outras funções do Excel, você pode usar os</p><p>sinais de igual (=), Maior que (>), menor que (<), maior ou igual que (>=) ou menor ou</p><p>igual que (=<).</p><p>2013 Pág. 21</p><p>Idiomas e Informática</p><p>Sendo assim a fórmula usada fica da seguinte forma na coluna</p><p>D2: =OU(B2>=100;C2>=8). Ao aplica-la na planilha, o Excel automaticamente define</p><p>se o resultado é VERDADEIRO ou FALSO, como mostrado na imagem.</p><p>O único porém da função OU é retornar apenas VERDADEIRO ou FALSO. Para</p><p>que mostre um resultado diferente, deve uni-la com outra função, como a SE, que</p><p>também possui um tutorial no BABOO.</p><p>Ainda no mesmo exemplo, observe a função OU usada em conjunto com a SE é</p><p>introduzido e a fórmula retorna “Aprovado” ou “Não</p><p>Aprovado”: =SE(OU(B2>=100;C2>=8);"Aprovado";"Não Aprovado")Ou</p><p>seja: SE o valor da célula B2 ou o valor da Célula C2 for maior ou igual que</p><p>100 OU igual ou maior que 8, o Excel deverá retornar "Aprovado". Caso não, deverá</p><p>retornar "Não Aprovado"</p><p>2013 Pág. 22</p><p>Idiomas e Informática</p><p>2.5- Função SENÃODISP</p><p>Retorna o valor de erro #N/D. #N/D é o valor de erro que significa "não há</p><p>nenhum valor disponível." Use NÃO.DISP para marcar células vazias. Ao inserir #N/D</p><p>nas células onde estão faltando informações, você pode evitar o problema de incluir,</p><p>não intencionalmente, células vazias nos seus cálculos. Quando uma fórmula se refere a</p><p>uma célula que contém #N/D, a fórmula retornará o valor de erro #N/D.</p><p>3- Funções Datas e Horas</p><p>São funções que manipulam as datas e as horas.</p><p>3.1- Função Hoje()</p><p>É uma função bem simples e bem auto-explicativa. Esse função retorna</p><p>a data exibida pelo sistema operacional, que supõe-se ser a data atual. Se a data do</p><p>sistema estiver incorreta, a função exibirá a “data atual” de maneira incorreta.</p><p>Esta função não requer parâmetro, ou seja, não é necessário informar nada dentro</p><p>dos parênteses. Mesmo assim, eles são necessários, pois é regra do Excel que TODAS</p><p>as funções tenham parênteses após o seu nome.</p><p>Essa função permite que se façam operações de soma e subtração com datas. Isso</p><p>quer dizer que posso somar ou diminuir dias. Tendo por base a data deste post, e eu</p><p>quiser que apareça uma data sempre 15 dias a frente, basta digitar =HOJE()+15. Ao</p><p>pressionar ENTER, aparecerá a data 03 de abril de 2012, que equivale a 15 dias após 19</p><p>de março:</p><p>Digitar da célula: =hoje()</p><p>Ou usar o tecla de atalho: CTRL + ;</p><p>3.2- Função Agora()</p><p>A função AGORA é útil quando você precisa exibir a data e a hora atuais em uma</p><p>planilha ou calcular um valor com base na data e na hora atuais e ter esse valor</p><p>atualizado sempre que abrir a planilha.</p><p>=agora()</p><p>2013 Pág. 23</p><p>Idiomas e Informática</p><p>• O Excel armazena datas como números de série sequenciais para que elas possam</p><p>ser usadas em cálculos. Por padrão, 1º de janeiro de 1900 é o número de série 1,</p><p>enquanto 1º de janeiro de 2008 é o número de série 39448, porque é 39.447 dias depois</p><p>de 1º de janeiro de 1900.</p><p>• Os números à direita da vírgula decimal no número de série representam a hora; os</p><p>números à esquerda representam a data. Por exemplo, o número de série 0,5 representa</p><p>a hora 12:00 meio-dia.</p><p>• Os resultados da função AGORA apenas serão alterados quando a planilha for</p><p>calculada ou quando uma macro que contém essa função for executada. A função não é</p><p>atualizada continuamente.</p><p>3.3- Função Dias360</p><p>A função DIAS360 retorna o número de dias entre duas datas com base em um</p><p>ano de 360 dias (doze meses de 30 dias). Use essa função para ajudar no cálculo de</p><p>pagamentos, se o seu sistema contábil estiver baseado em doze meses de 30 dias.</p><p>Sintaxe</p><p>DIAS360(data_inicial,data_final,[método])</p><p>A sintaxe da função DIAS360 tem os seguintes argumentos:</p><p>Data_inicial, data_final Obrigatório. As duas datas entre as quais você deseja</p><p>saber o número de dias. Se data_inicial ocorrer depois de data_final, a</p><p>função DIAS360 retornará um número negativo. As datas devem ser inseridas com o</p><p>uso da função DATA, ou devem ser derivadas dos resultados de outras fórmulas ou</p><p>funções. Por exemplo, use DATA(2008,5,23) para retornar o vigésimo terceiro dia de</p><p>maio de 2008. Poderão ocorrer problemas se as datas forem inseridas como texto.</p><p>Método Opcional. Um valor lógico que especifica o método a ser usado cálculo,</p><p>o americano ou o europeu.</p><p>3.4- Função Dias – Só Versão 2013</p><p>Retorna o número de dias entre duas datas</p><p>Sintaxe</p><p>DIAS(data_final, data_inicial)</p><p>A sintaxe da função DIAS tem os seguintes argumentos.</p><p>2013 Pág. 24</p><p>Idiomas e Informática</p><p>• Data_final Obrigatório. Data_inicial e Data_final são as duas datas entre as quais</p><p>você deseja saber o número de dias.</p><p>• Data_inicial Obrigatório. Data_inicial e Data_final são as duas datas entre as quais</p><p>você deseja saber o número de dias.</p><p>OBSERVAÇÃO : O Excel armazena as datas como números de série sequenciais para</p><p>que possam ser usadas em cálculos. Por padrão, 1º de janeiro de 1900 é o número de</p><p>série 1 e 1º de janeiro de 2008 é o número de série 39448, porque é 39447 dias depois</p><p>de 1º de janeiro de 1900.</p><p>• Se os dois argumentos de data forem números, DIAS usará DataFinal–DataInicial</p><p>para calcular o número de dias entre as duas datas.</p><p>• Se um dos argumentos de data for texto, esse argumento será tratado como</p><p>VALORDEDATA(texto_de_data) e retornará uma data inteira em vez de um</p><p>componente de tempo.</p><p>• Se os argumentos de data forem valores numéricos que estão fora do intervalo de</p><p>datas válidas, a DIAS</p><p>retornará o valor de erro #NUM!.</p><p>• Se os argumentos de data forem cadeias de caracteres que não podem ser</p><p>analisados como datas válidas, a DIAS retornará o valor de erro #VALUE!.</p><p>3.5- Função Dia.da.Semana</p><p>Retorna o dia da semana correspondente a uma data. O dia é dado como um</p><p>inteiro, variando de 1 (domingo) a 7 (sábado), por padrão.O Microsoft Excel armazena</p><p>datas como números de série seqüenciais para que eles possam ser usados em cálculos.</p><p>Por padrão, 1° de janeiro de 1900 é o número de série 1 e 1° de janeiro de 2008 é o</p><p>número de série 39448 porque está 39.448 dias após 1° de janeiro de 1900.</p><p>2013 Pág. 25</p><p>Idiomas e Informática</p><p>3.6- Funções Dia, Mês, Ano</p><p>Retorna de uma determinada data o Dia, o Mês e o ano.</p><p>3.7- Função Núm.Semana</p><p>Retira de uma data o número da semana no ano.</p><p>3.8- Função Data.valor</p><p>A função DATA.VALOR converte uma data armazenada como texto em um</p><p>número de série que o Excel reconhece como data. Por exemplo, a</p><p>fórmula =DATA.VALOR("1/1/2008") retorna 39448, o número de série da data</p><p>1/1/2008. Lembre-se, no entanto,de que a configuração de sistema de data de seu</p><p>computador pode fazer com que os resultados da função DATA.VALOR difiram deste</p><p>exemplo.</p><p>A função DATA.VALOR é útil nos casos em que uma planilha contém datas em</p><p>um formato de texto que você deseja filtrar, classificar ou formatar como datas ou usar</p><p>em cálculos de data.</p><p>2013 Pág. 26</p><p>Idiomas e Informática</p><p>3.9- Funções Hora, Minuto e segundo</p><p>Retorna de uma determinada Hora, a Hora, o Minuto e os Segundos.</p><p>4- Funções Texto</p><p>4.1- Função Arrumar</p><p>Remover os espaços de uma seqüência de caracteres de texto, com exceção dos</p><p>espaços simples entre palavras</p><p>4.2- Função Concatenar</p><p>A Função CONCATENAR() É uma função muito útil para unir ou juntar o</p><p>conteúdo de 2 ou mais células em apenas 1 célula.</p><p>2013 Pág. 27</p><p>Idiomas e Informática</p><p>= CONCATENAR(A4 ; ” “ ; B4)</p><p>Onde:</p><p>A4 = conteúdo da primeira célula;</p><p>“ “= espaço a ser dado para unir o conteúdo da célula seguinte.</p><p>B4 = Conteúdo da segunda célula a ser unido.</p><p>4.3- Função Direita e Esquerda</p><p>Retorna um número de caracteres da esquerda e direita de uma cadeia de caracteres</p><p>Para extrair os seis dígitos do código do Caderno, escolha a célula onde deseja que</p><p>os dígitos apareçam e execute a função =ESQUERDA(B3;6). O número seis serve para</p><p>indicar a quantidade de caracteres extraídos à esquerda da célula.</p><p>2013 Pág. 28</p><p>Idiomas e Informática</p><p>4.4- Função Num.Caract</p><p>Retorna o número de caráteres de uma cadeia de texto.</p><p>4.5- Funções Maiúscula e Minúscula</p><p>Converte todas as letras em uma cadeia de texto em minúsculas e em Maiúsculas</p><p>4.6- Funções Exato</p><p>Verifica se duas cadeias são exatamente iguais e retorna verdadeiro ou falso. Exato</p><p>diferencia maiúsculas e minúsculas.</p><p>4.7- Funções Pri.Maiuscula</p><p>Converte a primeira letra em uma sequência de texto e quaisquer outras letras no</p><p>texto que seguem qualquer caractere além de uma letra. Converte todas as outras letras</p><p>em letras minúsculas</p><p>2013 Pág. 29</p><p>Idiomas e Informática</p><p>4.8- Função Sustituir</p><p>A Função SUBSTITUIR coloca novo_texto no lugar de texto_antigo em uma</p><p>cadeia de texto. Use SUBSTITUIR quando quiser substituir texto específico em uma</p><p>cadeia de texto</p><p>Sintaxe:=SUBSTITUIR(texto;texto_antigo;novo_texto; úm_da_ocorrencia).</p><p>Com a Função SUBSTIUIR é possível retirar pontos e traços de CNPJ e CPF,</p><p>remover parênteses e hifens de números telefônicos e traços indesejados muito comuns</p><p>em importações de dados externos. Esta solução simples pode aumentar de forma</p><p>significativa a sua produtividade no Excel.</p><p>2013 Pág. 30</p><p>Idiomas e Informática</p><p>5- Funções Matemática e Trigonometria</p><p>5.1-Função ABS()</p><p>A função abs() serve para trazer o valor absoluto. Normalmente é usada quando se</p><p>tem uma operação de soma/subtração que pode dar um valor positivo ou negativo mas</p><p>queremos sempre o valor positivo.</p><p>5.2-Função Arred()</p><p>Função usada para arredondar valores, com opção de casas decimais. Seu uso é</p><p>muito simples. Basta chamar a função e definir os seus dois argumentos, o valor a ser</p><p>arredondado e o número de casas decimais, que é opcional.</p><p>Sintaxe da função: =ARRED(valor;casas decimais)</p><p>Uso no exemplo: = ARRED(MÉDIA(B2:E2;1)</p><p>5.2-Função Truncar()</p><p>Truncar tem um uso muito parecido com ARRED, só que agora a função irá</p><p>retirar os decimais que forem especificados na função. Logo se for truncado um valor</p><p>como 8,99 para uma casa decimal será exibido 8,9. Se truncarmos para casa decimal</p><p>nenhuma, inserindo 0 no segundo argumento ou deixando em branco, retornará 8.</p><p>Sintaxe da função: =TRUNCAR(valor; casas decimais)Uso no exemplo: =</p><p>2013 Pág. 31</p><p>Idiomas e Informática</p><p>TRUNCAR(A1;1)</p><p>5.2-Função Mod()</p><p>A função MOD é uma função bem simples, que dá como resultado o resto (ou</p><p>sobra) de uma divisão. Como a gente aprendeu na escola, uma divisão tem os seguintes</p><p>elementos: dividendo, divisor, quociente e resto (figura abaixo). O dividendo é dividido</p><p>pelo divisor, dando como resultado o quociente e o resto.</p><p>Sintaxe da função MOD</p><p>Sintaxe: MOD(número; divisor)</p><p>O primeiro argumento é o dividendo, e o segundo argumento é o divisor. O</p><p>resultado da função é o resto da divisão de número pelo divisor.</p><p>6.-Funções de Pesquisa e Referência</p><p>Função Procv e Proch</p><p>A função PROCV no Excel pode ser ótima para quem tem um banco de dados</p><p>seja com informações de pessoas, produtos ou até mesmo peças de sua empresa. O</p><p>tutorial mostra como você pode usar o procv no Excel.</p><p>6.1-Como usar função PROCV no Excel</p><p>Observe a tabela abaixo, a coluna A possui um código, a coluna B um nome,</p><p>a C a cidade e por fim na D o estado civil.</p><p>2013 Pág. 32</p><p>Idiomas e Informática</p><p>Encontrar informações com uma coluna dessas é super simples, mas se a</p><p>coluna tiver 100 ou mais itens? Para isso você usa o PROCV.</p><p>O PROCV utiliza a seguinte fórmula, entenda como ela funciona:</p><p>PROCV(valor_procurado;matriz_tabela;���� um_índice_coluna;procurar_intervalo)</p><p>Valor_procurado: Esse campo determinará qual é o valor procurado, você deve</p><p>estabelecer uma célula em branco para digitar o valor.</p><p>Matriz_tabela: Local onde o valor_procurado deverá ser procurado. Eles podem ser</p><p>números, textos ou valores lógicos (verdadeiro ou falso).</p><p>���� um_índice_coluna: Em qual coluna (1,2,3,4 ou mais) o valor estará. Apesar da</p><p>nomenclatura de A, B e C o Excel ordena as colunas por números.</p><p>Procurar_intervalo: Esse último termo define se a fórmula deve procurar um valor</p><p>exato ou próximo. No caso de um valor exato, é preciso digitar 0 (FALSO) enquanto</p><p>um valor aproximado tem de se usar 1(VERDADEIRO).</p><p>Agora imagine que você quer o nome da pessoa relacionada ao código, como ficaria o</p><p>PROCV no exemplo?</p><p>=PROCV(B19;A2:D14;2;0)</p><p>Isso se traduz como: O valor que será colocado na célula B19 deve retornar o valor que</p><p>está na segunda coluna (B) na interseção de células que vão de A2 a D14 e quero o</p><p>valor exato que está mostrado.</p><p>Para isso foi montado na mesma tabela uma área separada com as seguintes</p><p>configurações:</p><p>2013 Pág. 33</p><p>Idiomas e Informática</p><p>Como o código 13 está na linha 14 e a segunda coluna da linha 14 (B14) mostra</p><p>Melissa, o nome que aparecerá será Melissa.</p><p>Já que essa formula define o nome, a fórmula que definiria a cidade poderia ser a</p><p>mesma, apenas alteraria o número 2 para o 3, já que está na coluna C, sendo assim:</p><p>=PROCV(B19;A2:D14;3;0)</p><p>2013 Pág. 34</p><p>Idiomas e Informática</p><p>2013 Pág. 35</p><p>Idiomas e Informática</p><p>Por fim, para receber o dado do Estado Civil, seria alternado para o número 4 por</p><p>estar na coluna D</p><p>=PROCV(B19;A2:D14;4;0)</p><p>Vale apontar também que foi usado o mesmo local de valor de entrada para fins de</p><p>organização. Você pode definir qualquer local para inserir o valor de entrada, mas tem</p><p>sempre de escolher a área onde o valor procurado se encontra.</p><p>6.2- O que faz a função PROCH?</p><p>Esta função realiza uma pesquisa horizontalmente, ou seja ela faz a busca de um</p><p>determinado argumento usando como critério linhas.</p><p>A função PROCH pesquisa um valor da primeira linha de uma lista de dados</p><p>especificado por você em valor_procurado. Ela então procura o número de linhas que</p><p>você determinou em num_linha e retorna o valor que encontrar lá.</p><p>2013 Pág. 36</p><p>Idiomas e Informática</p><p>Exemplo</p><p>Vamos aplicar a função PROCH na célula B12, onde ela irá avaliar a célula B11, e</p><p>assim irá procurar este valor no intervalo B2:F9, e ao encontra-lo irá retornar o valor da</p><p>linha 8 (Total) correspondente a ele, siga os passos abaixo:</p><p>Para aplicarmos a função PROCH siga os passos abaixo:</p><p>• Selecione a célula B12;</p><p>• Utilize o assistente de função para facilitar, clique em fx ou acesse o menu Inserir e</p><p>clique no item Função;</p><p>• Selecione a categoria Procura e referência;</p><p>2013 Pág. 37</p><p>Idiomas e Informática</p><p>• Clique na função PROCH;</p><p>• Relacione os dados da planilha na janela “Argumentos da função”, veja abaixo:</p><p>Observe:</p><p>• valor_procurado: é a célula B11;</p><p>• matriz_tabela é o intervalo de B2:F9;</p><p>2013 Pág. 38</p><p>Idiomas e Informática</p><p>• num_linha: é 8, pois o intervalo começa na linha 2, e os valores a serem retornados</p><p>estão na linha 9 (Total do semestre);</p><p>• procurar_intervalo: é 0 "zero", pois queremos o valor exato da pesquisa.</p><p>Veja o resultado abaixo:</p><p>6.3- Função Corresp</p><p>Retorna a posição relativa de um item em uma matriz que coincide com um valor</p><p>especificado em uma ordem específica. Use CORRESP em vez de uma das funções</p><p>PROC quando você precisar da posição de um item em um intervalo ao invés do item</p><p>propriamente dito.</p><p>Sintaxe:</p><p>CORRESP(valor_procurado;matriz_procurada;tipo_correspondência)</p><p>Valor_procurado é o valor utilizado para localizar o valor desejado em uma tabela.</p><p>• Valor_procurado é o valor que você deseja coincidir com matriz_procurada. Por</p><p>exemplo, quando você procura o número do telefone de alguém em uma lista telefônica,</p><p>você está usando o nome da pessoa como o valor da procura, mas o número do telefone</p><p>é o valor que você deseja.</p><p>• Valor_procurado pode ser um valor (número, texto ou valor lógico) ou uma</p><p>referência de célula de um número, texto ou valor lógico.</p><p>Matriz_procurada é um intervalo contíguo de células que contêm valores possíveis</p><p>de procura. Matriz_procurada precisa ser uma matriz ou uma referência de matriz.</p><p>2013 Pág. 39</p><p>Idiomas e Informática</p><p>Tipo_correspondência é o número -1, 0 ou 1. Tipo_correspondência especifica como</p><p>o Microsoft Excel corresponde a valor_procurado com os valores contidos em</p><p>matriz_procurada.</p><p>• Se tipo_correspondência for 1, CORRESP localizará o maior valor que for menor</p><p>do que ou igual a valor_procurado. Matriz_procurada deve ser posicionada em ordem</p><p>ascendente: ...-2, -1, 0, 1, 2,...A-Z, FALSO, VERDADEIRO.</p><p>• Se tipo_correspondência for 0, CORRESP localizará o primeiro valor que for</p><p>exatamente igual a valor_procurado. Matriz_procurada pode ser colocada em qualquer</p><p>ordem.</p><p>• Se tipo_correspondência for -1, CORRESP localizará o menor valor que seja</p><p>maior ou igual a valor_procurado. Matriz_procurada deve ser posicionada em ordem</p><p>decrescente: VERDADEIRO, FALSO, Z-A,...2, 1, 0, -1, -2,... e assim por diante.</p><p>• Se tipo_correspondência for omitido, será equivalente a 1.</p><p>Comentários</p><p>• CORRESP retorna a posição do valor coincidente em matriz_procurada, e não o</p><p>valor propriamente dito. Por exemplo: CORRESP("b";{"a"."b"."c"};0) retorna 2, a</p><p>posição relativa de "b" na matriz {"a"."b"."c"}.</p><p>• CORRESP não faz distinção entre letras maiúsculas e minúsculas quando estiver</p><p>fazendo a correspondência entre valores de texto.</p><p>• Se CORRESP não conseguir localizar um valor coincidente, ele fornecerá o valor</p><p>de erro #N/D.</p><p>• Se tipo_correspondência for 0 e valor_procurado for um texto, valor_procurado</p><p>poderá conter os caracteres curinga asterisco (*) e ponto de interrogação (?). Um</p><p>asterisco corresponde a qualquer seqüência de caracteres; um ponto de interrogação</p><p>corresponde a qualquer caractere individual.</p><p>Exemplo 1</p><p>Exemplo 2</p><p>2013 Pág. 40</p><p>Idiomas e Informática</p><p>Exemplo 3</p><p>Exemplo 4:</p><p>Exercício:</p><p>1- Monte o exemplo abaixo: Você vai digitar o valor e a função Corresp vai trazer o</p><p>nível.</p><p>6.4- Função Deslcoc</p><p>Como se sabe, as funções no Excel foram desenvolvidas com o objetivo de</p><p>facilitarem o trabalho dos usuários em qualquer que seja a tarefa a ser desempenhada.</p><p>O que poucas pessoas sabem é que estas funções podem ser combinadas umas</p><p>com as outras quando se deseja realizar operações de maior complexidade ou ainda</p><p>quando se pretende criar determinados mecanismos para facilitar ainda mais os</p><p>2013 Pág. 41</p><p>Idiomas e Informática</p><p>trabalhos no Excel. Um exemplo disso são as funções DESLOC e CORRESP.</p><p>Acompanhe a seguir maiores detalhes sobre o assunto:</p><p>Antes de conhecermos melhor o uso das funções desloc e corresp juntas, vamos</p><p>analisar e compreender a finalidade e o uso de cada uma delas individualmente.</p><p>A função Desloc faz uma referência para um intervalo que é especificado por</p><p>linhas e colunas de certa célula ou intervalo de células.</p><p>Para melhor compreensão, pode-se pensar na função desloc como um conjunto de</p><p>coordenadas para se chegar a determinado destino. Neste caso, iremos utilizar um</p><p>exemplo prático para melhor análise:</p><p>Na imagem acima temos uma planilha com uma célula Início e outra de Fim como</p><p>se fossem um caminho a ser tracejado para se chegar ao destino. Sendo assim,</p><p>utilizando a função Desloc, iremos tracejar o caminho para se chegar da célula Início a</p><p>célula Fim.</p><p>Insira a função DESLOC em um local da planilha conforme sintaxe abaixo:</p><p>2013 Pág. 42</p><p>Idiomas e Informática</p><p>Ref: Represente o início a ser percorrido;</p><p>Lins: Representa o número de linhas a ser percorrida para se chegar ao destino;</p><p>Cols: Representa o número de colunas a ser percorrida para se chegar ao destino;</p><p>[altura]: Número de linhas que o destino procurado ocupa;</p><p>[largura]: Número de colunas que o destino procurado ocupa;</p><p>Para o exemplo citado, temos:</p><p>ref = célula Início</p><p>lins= 11 linhas a serem percorridas</p><p>cols= 4 colunas a serem percorridas</p><p>Altura= 1</p><p>Largura=1</p><p>Ao final da função, tecle Enter e verifique que o resultado será Fim. Ou seja, com</p><p>a função desloc demos uma rota e um destino para se chegar em determinada célula.</p><p>2013 Pág. 43</p><p>Idiomas e Informática</p><p>6.5- Função Índice</p><p>Ela serve principalmente para retornar valores específicos e ordenados. Pode ser</p><p>bastante útil, por exemplo, para gestores de RH que possuem várias informações para</p><p>um</p><p>mesmo funcionário e não podem perder tempo procurando algo único nas diversas</p><p>informações agrupadas.</p><p>Agora imaginemos o gestor de uma grande empresa, com muitas outras colunas</p><p>contendo informações variadas, como por exemplo, se fez capacitação, números de</p><p>identificação profissional, número dentro da empresa, e-mail, etc. Agora imagine ele ter</p><p>que pegar o nome no início da planilha e depois ter que navegar até a coluna 20 ou 30</p><p>para checar um dado, imagine ele fazer isso com vários funcionários. Bastante</p><p>trabalhoso, certo? Certo, mas não se ele souber utilizar a função Índice =) Bom, vamos</p><p>ensinar como nosso amigo, gestor de RH, pode fazer isso de uma forma bem simples.</p><p>2013 Pág. 44</p><p>Idiomas e Informática</p><p>A função Índice requer bastante atenção, pois embora seja fácil, é maior do que as</p><p>demais e demanda maior atenção na hora de digitar as referências a serem buscadas.</p><p>Esta é a sua sintaxe:=ÍNDICE(matriz,núm_linha,núm_coluna).</p><p>Digamos que este gestor queira saber o salário de algum funcionário específico,</p><p>neste caso Armando. Neste exemplo ele selecionará uma célula vazia e escreverá a</p><p>função da seguinte maneira=ÍNDICE(A2:K8;3;6), onde, A2:K8 corresponde à matriz,</p><p>ou seja, a área onde o Excel irá fazer a busca, num_linha corresponde à linha do</p><p>funcionário Armando e num_coluna ao que desejamos saber, neste caso a coluna do</p><p>salário.</p><p>Repare em algo muito importante: O número da linha e da coluna da</p><p>matriz NÃO TEM RELAÇÃO com o número da linha e da coluna da planilha. Notem</p><p>que, no nosso exemplo, a linha 2 da MATRIZ está, fisicamente, na linha 6 da</p><p>PLANILHA. O mesmo procedimento ocorre com o número relativo á coluna.</p><p>Note também que é possível deixar tudo mais automatizado, podendo, por</p><p>exemplo, referenciar a coluna e linha a uma célula fixa e não a um número de linha ou</p><p>coluna em si. Vejamos no exemplo abaixo para ficar mais claro.</p><p>Agora digamos que este mesmo gestor tenha dois grupos distintos em sua</p><p>planilha, será possível fazer uma busca escolhendo o grupo a ser buscado? Certamente,</p><p>veja o próximo exemplo:</p><p>Com a configuração de área pronta e também fixada a uma célula específica (no</p><p>nosso caso à E27) os valores exibidos podem ser mudados da mesma forma que os</p><p>valores de linha e colunas fixados a uma célula específica, conforme aprendemos lá</p><p>atrás. Repare que ao mudar o valor da área de 1 para área 2, ela passa a mostrar o</p><p>número de telefone do funcionário que se encontra na linha 3, coluna 5 da nova área</p><p>selecionada, neste caso a dos estagiários.</p><p>Lembre-se que não há limite para a quantidade de áreas que podem ser criadas,</p><p>apenas coloque-as todas dentro de um parêntese interno ao parêntese principal e separá-</p><p>las com ponto e vírgula. Sua numeração obedecerá a ordem em que forem criadas</p><p>dentro da sintaxe da função.</p><p>2013 Pág. 45</p><p>Idiomas e Informática</p><p>7- Funções Financeiras</p><p>7.1- Função PGTO</p><p>Retorna o pagamento periódico de uma anuidade de acordo com pagamentos</p><p>constantes e com uma taxa de juros constante.</p><p>Sintaxe</p><p>PGTO(taxa;nper;vp;vf;tipo)</p><p>Taxa: é a taxa de juros por período.</p><p>Nper: é o número total de pagamentos pelo empréstimo.</p><p>Vp: é o valor presente—o valor total presente de uma série de pagamentos futuros.</p><p>Vf: é o valor futuro, ou o saldo de caixa, que você deseja obter depois do último</p><p>pagamento. Se vf for omitido, será considerado 0 (o valor futuro de determinado</p><p>empréstimo, por exemplo, 0).</p><p>Tipo: é o número 0 ou 1 e indica as datas de vencimento.</p><p>• O pagamento retornado por PGTO inclui o principal e os juros e não inclui taxas,</p><p>pagamentos de reserva ou tarifas, às vezes associados a empréstimos.</p><p>• Certifique-se de que esteja sendo consistente quanto às unidades usadas para</p><p>especificar taxa e nper. Se fizer pagamentos mensais por um empréstimo de quatro anos</p><p>com juros de 12% ao ano, utilize 12%/12 para taxa e 4*12 para nper. Se fizer</p><p>pagamentos anuais para o mesmo empréstimo, use 12% para taxa e 4 para nper.</p><p>Dica: Para encontrar o total pago no período da anuidade, multiplique o valor PGTO</p><p>retornado por nper.</p><p>1- Faça de conta que vai comprar um carro cujo valor e R$ 12.500,00, você dar de</p><p>entrada R$ 1.250,00 e vai pagar o restante em 48 meses ao juro de 7% ao ano. Qual será</p><p>o valor da mensalidade?</p><p>2013 Pág. 46</p><p>Idiomas e Informática</p><p>7.2- Função Taxa</p><p>Retorna a taxa de juros por período de uma anuidade. TAXA é calculado por</p><p>iteração e pode ter zero ou mais soluções. Se os resultados sucessivos de TAXA não</p><p>convergirem para 0,0000001 depois de 20 iterações, TAXA retornará o valor de erro</p><p>#NÚM!.</p><p>Sintaxe</p><p>TAXA(nper;pgto;vp;vf;tipo;estimativa)</p><p>Nper: é o número total de períodos de pagamento em uma anuidade.</p><p>Pgto: É o pagamento feito em cada período e não pode mudar durante a vigência da</p><p>anuidade. Geralmente, pgto inclui o principal e os juros e nenhuma outra taxa ou</p><p>tributo. Se pgto for omitido, você deverá incluir o argumento vf.</p><p>Vp: É o valor presente — o valor total correspondente ao valor atual de uma série de</p><p>pagamentos futuros.</p><p>Vf: é o valor futuro, ou o saldo, que você deseja obter depois do último pagamento. Se</p><p>vf for omitido, será considerado 0 (o valor futuro de um empréstimo, por exemplo, é 0).</p><p>Tipo: é o número 0 ou 1 e indica as datas de vencimento.</p><p>Definir tipo para Se os vencimentos forem</p><p>0 ou omitido No final do período</p><p>2013 Pág. 47</p><p>Idiomas e Informática</p><p>1 No início do período</p><p>Estimativa é a sua estimativa para a taxa.</p><p>• Se você omitir estimativa, este argumento será considerado 10%.</p><p>• Se TAXA não convergir, atribua valores diferentes para estimativa. Em geral,</p><p>TAXA converge se estimativa estiver entre 0 e 1.</p><p>Comentários</p><p>Certifique-se de que esteja sendo consistente quanto às unidades usadas para especificar</p><p>estimativa e nper. Se você fizer pagamentos mensais por um empréstimo de quatro anos</p><p>com juros de 12% ao ano, utilize 12%/12 para estimativa e 4*12 para nper. Se fizer</p><p>pagamentos anuais para o mesmo empréstimo, utilize 12% para estimativa e 4 para</p><p>nper.</p><p>2- Faça de conta que vai comprar um carro cujo valor e R$ 12.500,00, você dar de</p><p>entrada R$ 1.250,00 e vai pagar o restante em 48 meses, vai dar na entrada R$ 1.250,00.</p><p>Qual a taxa de juros anual?</p><p>2013 Pág. 48</p><p>Idiomas e Informática</p><p>OBS: Tem que multiplicar por 12 para ter o juros anual.</p><p>7.3- Função Valor Futuro - VF</p><p>Retorna o valor futuro de um investimento de acordo com os pagamentos</p><p>periódicos e constantes e com uma taxa de juros constante.</p><p>Sintaxe</p><p>VF(taxa;nper;pgto;vp;tipo)</p><p>Taxa: É a taxa de juros por período.</p><p>Nper: é o número total de períodos de pagamento em uma anuidade.</p><p>Pgto: É o pagamento feito a cada período; não pode mudar durante a vigência da</p><p>anuidade. Geralmente, pgto contém o capital e os juros e nenhuma outra tarifa ou taxas.</p><p>Se pgto for omitido, você deverá incluir o argumento vp.</p><p>Vp: É o valor presente ou a soma total correspondente ao valor presente de uma série</p><p>de pagamentos futuros. Se vp for omitido, será considerado 0 (zero) e a inclusão do</p><p>argumento pgto será obrigatória.</p><p>Tipo: É o número 0 ou 1 e indica as datas de vencimento dos pagamentos. Se tipo for</p><p>omitido, será considerado 0.</p><p>Definir tipo para Se os vencimentos forem</p><p>0 No final do período</p><p>1 No início do período</p><p>Comentários</p><p>• Certifique-se de que esteja sendo consistente quanto às unidades usadas para</p><p>especificar taxa e nper. Se fizer pagamentos mensais de um empréstimo de quatro anos</p><p>com taxa de juros de 12% ao ano, use 12%/12 para taxa e 4*12 para nper. Se você fizer</p><p>pagamentos anuais para o mesmo empréstimo, use 12% para taxa e 4 para nper.</p><p>• Todos os argumentos, saques, tais como depósitos em poupança, serão</p><p>representados por números negativos; depósitos recebidos, tais</p><p>como cheques de</p><p>dividendos, serão representados por números positivos.</p><p>3- Faça de conta que vai comprar um carro em um prazo de 4 anos, a uma taxa de juros</p><p>anual de 7%, e vai dar de entrada R$ 1250,00. Qual o valor do carro?</p><p>2013 Pág. 49</p><p>Idiomas e Informática</p><p>7.4- Função Valor Atual -VP</p><p>Retorna o valor presente de um investimento. O valor presente é o valor total</p><p>correspondente ao valor atual de uma série de pagamentos futuros. Por exemplo, quando</p><p>você pede dinheiro emprestado, o valor do empréstimo é o valor presente para quem</p><p>empresta.</p><p>Sintaxe</p><p>VP(taxa;nper;pgto;vf;tipo)</p><p>Taxa: É a taxa de juros por período. Por exemplo, se você obtiver um empréstimo para</p><p>um carro com uma taxa de juros de 10% ao ano e fizer pagamentos mensais, a sua taxa</p><p>de juros mensal será 10%/12, ou 0,83%. Você deve inserir 10%/12, ou 0,83%, ou</p><p>0,0083, na fórmula como taxa.</p><p>Nper: É o número total de períodos de pagamento de uma anuidade. Por exemplo, se</p><p>você obtiver um empréstimo de quatro anos e fizer pagamentos mensais, o empréstimo</p><p>terá 4*12 (ou 48) períodos. Você deve inserir 48 na fórmula para nper.</p><p>Pgto: É o pagamento feito a cada período e não pode mudar durante a vigência da</p><p>anuidade. Geralmente, pgto inclui o principal e os juros, e não há outras tarifas ou taxas.</p><p>Por exemplo, os pagamentos mensais por um empréstimo para o carro de R$ 10.000 de</p><p>quatro anos a 12% são R$ 263,33. Você deve inserir -263,33 na fórmula como pgto. Se</p><p>pgto for omitido, você deverá incluir o argumento vf.</p><p>2013 Pág. 50</p><p>Idiomas e Informática</p><p>Vf: é o valor futuro, ou um saldo de caixa, que você deseja obter depois do último</p><p>pagamento. Se vf for omitido, será considerado 0 (o valor futuro de determinado</p><p>empréstimo, por exemplo, é 0). Por exemplo, se quiser economizar R$ 50.000 para</p><p>pagar um projeto especial em 18 anos, então R$ 50.000 é o valor futuro. Você pode</p><p>então calcular a taxa de juros e determinar quanto deverá economizar a cada mês. Se vf</p><p>for omitido, você deverá incluir o argumento pgto.</p><p>Tipo: é o número 0 ou 1 e indica as datas de vencimento.</p><p>Definir tipo para Se os vencimentos forem</p><p>0 ou omitido No final do período</p><p>1 No início do período</p><p>Comentários</p><p>• Certifique-se de que esteja sendo consistente quanto às unidades usadas para</p><p>especificar taxa e nper. Se fizer pagamentos mensais de um empréstimo de quatro anos</p><p>com taxa de juros de 12% ao ano, use 12%/12 para taxa e 4*12 para nper. Se você fizer</p><p>pagamentos anuais para o mesmo empréstimo, use 12% para taxa e 4 para nper.</p><p>4- Faça de conta que vai comprar um carro em um prazo de 4 anos, a uma taxa de juros</p><p>anual de 7%, vai pagar uma mensalidade de R$ 269,40. Qual será o valor da entrada?</p><p>2013 Pág. 51</p><p>Idiomas e Informática</p><p>Tabela Dinâmica</p><p>"Tabela dinâmica" (em inglês PIVOT TABLE) é uma tabela que pode ser</p><p>organizada dinamicamente; por exemplo, a partir de uma lista de vendas, você monta</p><p>uma tabela inicialmente organizada por vendedor. Ao simples "clicar e arrastar", essa</p><p>mesma tabela passa a ser organizada por produtos. Ela permite ainda classificação,</p><p>filtros e resumos.</p><p>Vamos aprender a criar tabelas dinâmicas. Este tipo de tabela é imprescindível</p><p>para quem faz tabelas mais longas, com centenas, as vezes até milhares de linhas de</p><p>dados, pois ela pode facilmente acabar com a poluição visual e tornar muito mais fácil</p><p>encontrar os dados requisitados pelo usuário.</p><p>No entanto, ela não serve apenas para tabelas longas, não. Como veremos ela será</p><p>usada numa tabela mais curta, apenas para visualizarmos sua incrível.</p><p>2013 Pág. 52</p><p>Idiomas e Informática</p><p>Reparem que no nosso exemplo acima temos uma pequena planilha sobre um</p><p>grupo de vendas. Através de uma análise mais aprofundada o gestor que a observa</p><p>poderia ver qual seu vendedor mais rentável, qual o estado onde há maior lucro, qual a</p><p>equipe de trabalho está vendendo melhor, qual o produto mais lucrativo, etc. Vejam</p><p>quantas informações úteis podem ser extraídas desta simples planilha, agora</p><p>amplifiquem e imaginam para um grande negócio. Realmente útil, certo? Mas</p><p>convenhamos que da forma como estão dispostos os dados, esta análise não seria nem</p><p>um pouco fácil. E será justamente nesta hora em que entra em campo a nossa tabela</p><p>dinâmica.</p><p>Para criá-la não é nem um pouco difícil, apenas faça o seguinte: Clique em</p><p>qualquer ponto dos dados e então clique em 'Inserir' e depois em 'tabela dinâmica'. Se</p><p>você fez como sugerimos e antes de inseri-la clicou em qualquer parte da tabela, veja</p><p>que na próxima janela o Excel reconhecerá a área onde será feita a captura dos valores.</p><p>Importante ressaltar que embora estejamos utilizando um formato de tabela na</p><p>nossa aula, seria perfeitamente possível gerar uma tabela dinâmica a partir de uma fonte</p><p>de dados externa, como outros arquivos Excel, dados do Microsoft SQL Server,</p><p>Microsoft Access, bancos de dados de arquivos de texto e até dados de um site da Web.</p><p>Agora que ela está criada temos de planejar como exibiremos os dados da forma</p><p>que seja mais útil para nós. Neste momento não há uma fórmula, depende realmente das</p><p>necessidades de cada usuário. Analise bem o que se encaixa melhor para a sua situação</p><p>e personalize seu trabalho. Continuando:</p><p>2013 Pág. 53</p><p>Idiomas e Informática</p><p>Percebam que nessa nova planilha aberta pelo Excel há 4 áreas: Filtros de</p><p>Relatório, Rótulos de Linha, Rótulos de Coluna e Valores. Poderemos utilizar 2 ou mais</p><p>áreas para podermos analisarmos nossos dados. Pronto, sua tabela dinâmica está</p><p>praticamente pronta, agora você deve decidir o que quer saber e com isso o Excel irá</p><p>montar os dados de forma organizada para você: Vamos começar com nossos exemplos:</p><p>Digamos que você quer, nesse caso, saber o valor das vendas por produto específico, a</p><p>primeira opção que será marcada será a categoria 'produto'. Repare que aquela categoria</p><p>que for marcada primeiro será sempre a principal, o "rótulo de linha" na linguagem do</p><p>Excel. Agora para saber o valor das vendas por produto vamos marcar Vendas/Jan,</p><p>Vendas/Fev e Vendas/Mar. Veja que o Excel nos poupa um trabalhão, pois soma todas</p><p>as vendas que dizem respeito a celular, por exemplo, para aquele período. Bom,</p><p>rapidamente o gestor já saberia que Celular é o que suas equipes mais vendem.</p><p>Mas e falando em equipes, e se ele quisesse saber qual equipe vendeu mais de cada</p><p>produto? Muito simples, apenas arraste a guia 'equipe' para a caixa de 'Rótulos de</p><p>Linha'. Veja o resultado:</p><p>2013 Pág. 54</p><p>Idiomas e Informática</p><p>E mais, vejam que se clicar naquela seta ao lado de 'Rótulos de Linha' irá aparecer uma</p><p>lista de selecionáveis onde poderá ser escolhido somente um dos itens para uma análise</p><p>mais minuciosa.</p><p>E agora, qual estado está dando mais lucro? Fácil:</p><p>E estado com divisão por produto e por vendedor? Fácil também =)</p><p>2013 Pág. 55</p><p>Idiomas e Informática</p><p>Legal, certo? Veja que com uma tabela dinâmica não há limites para o que pode ser</p><p>criado com ela. Com as informações dispostas na sua tabela você poderá criar centenas</p><p>de combinações e cenários variados.</p><p>E para finalizar, mais uma dica. Repare que se nosso último exemplo colocarmos a guia</p><p>'Equipe' na caixa 'Filtro de Relatório' aparecerá no auto da nossa TD uma célula escrito</p><p>"Tudo" e uma flechinha.</p><p>2013 Pág. 56</p><p>Idiomas e Informática</p><p>Veja que agora selecionamos nessa flechinha para que nos fosse mostrado somente as</p><p>infos relacionadas à equipe Gama:</p><p>Exercício 2 – Montar a planilha abaixo.</p>