Baixe o app para aproveitar ainda mais
Prévia do material em texto
Exp!icare Treinamentos Apostila Excel Avançado www.explicare.com.br – contato@explicare.com.br http://www.explicare.com.br/ EXCEL AVANÇADO 2010 / 2013 _____________________________________________________________________________________________ Para uso de: Lucas Magalhães Garcia - (31) 98522-1533 - CPF.: 085.158.776-35 - Turma Nº 4316 Reprodução ou distribuição proibida. IN GOD WE TRUST - www.explicare.com.br - contato@explicare.com.br - (31) 4141-2882 2 PREFÁCIO A maioria das pessoas fica satisfeita tendo o que chamamos de “conhecimentos básicos” de Excel, mas muitas pessoas, como você, sabem que o Excel tem muito mais recursos para oferecer e por causa disso procuram um curso de Excel Avançado. O objetivo deste curso é superara as suas expectativas e mostrar que um curso de Excel Avançado é apenas mais um grande passo na exploração de um universo “quase num único curso: sempre há muito mais para aprender! Baseados na experiência de mais de treze anos ministrando cursos de Excel na Explicare e nas experiências de profissionais que utilizam planilhas eletrônicas há mais de 20 anos, para este curso de Excel Avançado selecionamos dezenas de ferramentas, funções e recursos que vão aumentar sua produtividade, facilitar seu trabalho e, com certeza, expandir seus horizontes dentro de uma planilha. Não satisfeitos, ainda colocamos nesta apostila dezenas de funções adicionais que normalmente não fazem parte do curso de Excel Avançado, mas que com certeza serão úteis ao seu dia a dia. Nessas funções extras, tomamos o cuidado de colar exemplos claros e simples para facilitar seu auto aprendizado. Se você acha que estamos exagerando, espera até o final do curso: você vai encarar uma planilha com outros olhos, usando recursos avançados, criando macros e querendo ver o folheto do curso de Excel com Programação em VBA – seu próximo curso! Acredito que este material vai superar suas expectativas e que será um excelente companheiro durante sua jornada de aprendizado. Espero que goste! Muito obrigado. Wenceslau Souza wenceslau@gmail.com EXCEL AVANÇADO 2010 / 2013 _____________________________________________________________________________________________ Para uso de: Lucas Magalhães Garcia - (31) 98522-1533 - CPF.: 085.158.776-35 - Turma Nº 4316 Reprodução ou distribuição proibida. IN GOD WE TRUST - www.explicare.com.br - contato@explicare.com.br - (31) 4141-2882 3 Sumário EXERCÍCIOS DE REVISÃO CONCEITOS BÁSICOS .................................................................... 6 FERRAMENTA PROTEGER ........................................................................................................... 31 PROTEÇÃO DE PLANILHA ....................................................................................................... 31 PERMITIR QUE OS USUÁRIOS EDITEM INTERVALOS ....................................................... 35 PROTEGER PASTA DE TRABALHO ......................................................................................... 37 CRIANDO UMA SENHA PARA SEU ARQUIVO ...................................................................... 38 LÓGICA DA AUTOSOMA ............................................................................................................... 39 FUNÇÕES CONDICIONAIS ........................................................................................................... 47 FUNÇÃO “SE” JUNTO COM A FUNÇÃO “E” .......................................................................... 47 FUNÇÃO SE JUNTO COM FUNÇÃO OU .................................................................................. 54 FUNÇÃO “SE”; FUNÇÃO ”E” COM A FUNÇÃO “OU” ......................................................... 58 BANCO DE DADOS ........................................................................................................................ 61 CLASSIFICAÇÃO E GERENCIAMENTO DE DADOS ............................................................ 62 FILTROS ........................................................................................................................................ 70 FILTRO AVANÇADO ................................................................................................................... 82 INSERIR TABELAS ..................................................................................................................... 88 SUBTOTAIS ...................................................................................................................................... 94 CRIANDO UMA MACRO DE CLASSIFICAÇÃO ....................................................................... 108 FUNÇÕES DE PROCURA ............................................................................................................. 113 PROCV – PROCURA VERTICAL ............................................................................................. 113 PROCH – PROCURA HORIZONTAL ....................................................................................... 119 FUNÇÃO PROC .......................................................................................................................... 120 FUNÇÃO SOMASE .................................................................................................................... 122 FUNÇÃO CONT.SE .................................................................................................................... 125 Função MÉDIASE ....................................................................................................................... 126 FUNÇÃO ÍNDICE ....................................................................................................................... 128 Função CORRESP ....................................................................................................................... 129 FUNÇÕES DE BANCO DE DADOS ............................................................................................. 131 FUNÇÃO BDCONTAR UTILZANDO UM CRITÉRIO ........................................................... 131 FUNÇÃO BDSOMA ................................................................................................................... 137 FUNÇÃO BDMÉDIA ................................................................................................................. 138 FUNÇÃO BDMÁX ..................................................................................................................... 139 EXCEL AVANÇADO 2010 / 2013 _____________________________________________________________________________________________ Para uso de: Lucas Magalhães Garcia - (31) 98522-1533 - CPF.: 085.158.776-35 - Turma Nº 4316 Reprodução ou distribuição proibida. IN GOD WE TRUST - www.explicare.com.br - contato@explicare.com.br - (31) 4141-2882 4 FUNÇÃO BDMÍN ....................................................................................................................... 140 TABELA .......................................................................................................................................... 141 UTILIZANDO A FUNÇÃO FINANCEIRA PGTO .................................................................... 142 COMO UTILIZAR O RECURSO TABELA .............................................................................. 143 COMANDO ATINGIR META ........................................................................................................ 146 TABELA DINÂMICA ..................................................................................................................... 150 SEGMENTAÇÃO DE DADOS ................................................................................................... 162 GRÁFICOS DINÂMICOS ..........................................................................................................165 TRABALHANDO COM MACROS ............................................................................................... 168 GRAVANDO UMA MACRO ...................................................................................................... 169 GRAVANDO OUTRA MACRO .................................................................................................. 175 QUATRO FORMAS DE EXECUTAR UMA MACRO .............................................................. 180 REFERÊNCIA ABSOLUTA E RELATIVA ................................................................................ 186 OUTRAS FUNÇÕES ...................................................................................................................... 191 FUNÇÃO SOMASES .................................................................................................................. 191 FUNÇÃO CONT.SES .................................................................................................................. 197 FUNÇÃO MÉDIASES ................................................................................................................ 198 GERANDO NÚMEROS ALEATÓRIOS .................................................................................... 200 TRATAMENTO DE ERROS ....................................................................................................... 200 FUNÇÃO ORDEM ...................................................................................................................... 201 FUNÇÃO REPT .......................................................................................................................... 202 FUNÇÃO MAIOR ....................................................................................................................... 202 FUNÇÃO MENOR ...................................................................................................................... 202 FUNÇÕES DE DATA E HORA ..................................................................................................... 203 FUNÇÃO AGORA ...................................................................................................................... 203 FUNÇÃO HOJE .......................................................................................................................... 203 FUNÇÃO DIA ............................................................................................................................. 203 FUNÇÃO MÊS ............................................................................................................................ 204 FUNÇÃO ANO ............................................................................................................................ 204 FUNÇÃO HORA ......................................................................................................................... 204 FUNÇÃO MINUTO .................................................................................................................... 205 FUNÇÃO SEGUNDO ................................................................................................................. 206 FUNÇÃO DIA.DA.SEMANA .................................................................................................... 206 EXCEL AVANÇADO 2010 / 2013 _____________________________________________________________________________________________ Para uso de: Lucas Magalhães Garcia - (31) 98522-1533 - CPF.: 085.158.776-35 - Turma Nº 4316 Reprodução ou distribuição proibida. IN GOD WE TRUST - www.explicare.com.br - contato@explicare.com.br - (31) 4141-2882 5 FUNÇÃO FIMMÊS ..................................................................................................................... 207 FUNÇÃO DIATRABALHOTOTAL (Cálculo de Dias úteis) ..................................................... 208 FUNÇÕES DE TEXTO ................................................................................................................... 208 FUNÇÃO CONCATENAÇÃO ................................................................................................... 208 FUNÇÃO ESQUERDA ............................................................................................................... 209 FUNÇÃO DIREITA .................................................................................................................... 210 FUNÇÃO EXT.TEXTO ............................................................................................................... 211 FUNÇÃO MAIÚSCULA ............................................................................................................ 212 FUNÇÃO MINÚSCULA ............................................................................................................ 213 FUNÇÃO PRI.MAIÚSCULA ..................................................................................................... 214 FUNÇÃO LOCALIZAR.............................................................................................................. 215 FUNÇÕES ESTATÍSTICAS ............................................................................................................ 217 FUNÇÃO DESVPAD .................................................................................................................. 217 FUNÇÃO CONT.NÚM ............................................................................................................... 218 FUNÇÃO CONTAR.VAZIO ....................................................................................................... 219 FUNÇÕES MATEMÁTICAS ......................................................................................................... 219 FUNÇÃO ARRED ....................................................................................................................... 219 FUNÇÃO ARREDONDAR.PARA.CIMA .................................................................................. 220 FUNÇÃO ARREDONDAR.PARA.BAIXO ................................................................................ 221 FUNÇÃO INT ............................................................................................................................. 222 OUTROS RECURSOS .................................................................................................................... 223 HIPERLINKS .............................................................................................................................. 223 AUDITORIA DE FÓRMULAS ................................................................................................... 225 MOSTRANDO/OCULTANDO FÓRMULAS ............................................................................ 226 AVALIAÇÃO DE FÓRMULAS .................................................................................................. 226 FORMATAÇÃO CONDICIONAL .................................................................................................. 229 REMOVENDO DUPLICATAS ....................................................................................................... 233 IMPORTANDO UMA TABELA DE MS ACCESS ........................................................................ 236 EXCEL AVANÇADO 2010 / 2013 _____________________________________________________________________________________________ Para uso de: Lucas Magalhães Garcia - (31) 98522-1533 - CPF.: 085.158.776-35 - Turma Nº 4316 Reprodução ou distribuição proibida. IN GOD WE TRUST - www.explicare.com.br - contato@explicare.com.br - (31) 4141-2882 6 EXERCÍCIOS DE REVISÃO CONCEITOS BÁSICOS Nas próximas páginas vamos desenvolver um exercício de revisão criado como objetivo de rapidamente rever diversos tópicos do Excel, bem como passar uma série de dicas que acreditamos serem bastante úteis no dia a dia de quem utiliza o Excel. Este exercício já vempré-montado, ou seja, os dados que requerem digitação já estão prontos. Para cada tópico você precisar preencher, o instrutor terá uma dica para ajudá-lo. ´E importante que todos os alunos que estão participando, do Treinamento executem o preenchimento do mesmo tempo. Caso você já conheça alguns dos tópicos, por favor, aguarde o desenvolver do exercício, pois sempre terá algo que lhe será útil a seguir. Abra o arquivo e vamos iniciar nossa revisão. Mesclar e Centralizar Em primeiro lugar, desejamos centralizar o título “Relatório Trimestral” entre as células A1 e F1. Note que não se trata de simplesmente centralizar o texto dentro de uma única célula – para isso, no Excel 2010 temos uma função que mescla e centraliza automaticamente nossas células: Selecione a aba “Página Inicial” e “Mesclar e Centralizar “. O botão Mesclar e Centralizar serve não apenas para mesclar células, mas também para retirar a mesclagem. Caso um grupo de células esteja mesclado, basta selecionar uma das células e clicar no botão Mesclar e Centralizar para retirar a mesclagem. Alça de preenchimento Na célula “C2”, temos a data “jan/14”. Precisamos completar as células ao lado com as datas até “mar/14”. Para isso, podemos utilizar a alça de preenchimento: EXCEL AVANÇADO 2010 / 2013 _____________________________________________________________________________________________ Para uso de: Lucas Magalhães Garcia - (31) 98522-1533 - CPF.: 085.158.776-35 - Turma Nº 4316 Reprodução ou distribuição proibida. IN GOD WE TRUST - www.explicare.com.br - contato@explicare.com.br - (31) 4141-2882 7 O mesmo ocorre em relação à célula B3, onde temos o PRODUTO 1. Devemos completar abaixo com PRODUTO 2, PRODUTO 3, etc., e podemos utilizar a alça de preenchimento para isso. Para variar, experimente dar simplesmente um duplo clique na alça de preenchimento em vez de clicar e arrastar, como temos uma coluna preenchida ao lado (no caso, à direita), o Excel reconhece até onde ele deve preencher, sem que tenhamos que arrastar o mouse até lá. Observe que, quando você copia algo com a alça de preenchimento, no Excel 2010, aparece a marca inteligente, trazendo algumas opões adicionais. Utilize “Copiar células”, se você simplesmente desejar copiar o que está na célula, em vez de fazer a sequência numérica. Para manter o comportamento padrão do Excel, utilize “Preencher Série” Note que existem também opões de: Preencher formatação somente, Preencher sem formatação, Preencher dias, Preencher dias da semana, Preencher Meses e Preencher anos. Autosoma EXCEL AVANÇADO 2010 / 2013 _____________________________________________________________________________________________ Para uso de: Lucas Magalhães Garcia - (31) 98522-1533 - CPF.: 085.158.776-35 - Turma Nº 4316 Reprodução ou distribuição proibida. IN GOD WE TRUST - www.explicare.com.br - contato@explicare.com.br - (31) 4141-2882 8 Agora, desejamos totalizar a tabela de receitas por linha (PRODUTO) e por coluna (MÊS). Lembre-se que poderíamos resolver isso de muitas formas diferentes, mas que o modo mais rápido e selecionar de C3 até F7 (assim mesmo, mantendo uma linha em branco vazia e uma coluna em branco vazia também selecionadas) e clicar uma vez no Autosoma. O Autosoma no Excel 2010/2013 se encontra na aba “Fórmulas” veja a seguir: Os valores são totalizados por linha e por coluna: EXCEL AVANÇADO 2010 / 2013 _____________________________________________________________________________________________ Para uso de: Lucas Magalhães Garcia - (31) 98522-1533 - CPF.: 085.158.776-35 - Turma Nº 4316 Reprodução ou distribuição proibida. IN GOD WE TRUST - www.explicare.com.br - contato@explicare.com.br - (31) 4141-2882 9 Aproveite que o texto está selecionado e aplique o formato “Contábil”, na guia Página Inicial. Veja a seguir: Verificação de erros No Excel 2010/2013 também existe a verificação de erros. Observe que as células com os totais mensais estão com um indicador verde acima e à esquerda. Se clicarmos em alguma destas células será exibida uma marca inteligente com algumas opções. Perceba que, em primeiro lugar, aparece a explicação par ao erro encontrado: a fórmula omite células adjacentes. Esse tipo de mensagem irá aparecer sempre que tivermos células próximas e fizermos uma fórmula – se soma, por exemplo – utilizando somente algumas das células. Mas não foi isso que aconteceu, na verdade, acontece que, para o Excel, as datas e os horários também são armazenados como números – a única diferença é que estão formatadas de modo diferente. O verificador de erros, entretanto, não “sabe” disso e acaba “pensando” que há um erro na fórmula, que ela deveria somar também aqueles números, que não sabemos que na verdade, são apenas datas. EXCEL AVANÇADO 2010 / 2013 _____________________________________________________________________________________________ Para uso de: Lucas Magalhães Garcia - (31) 98522-1533 - CPF.: 085.158.776-35 - Turma Nº 4316 Reprodução ou distribuição proibida. IN GOD WE TRUST - www.explicare.com.br - contato@explicare.com.br - (31) 4141-2882 10 Essa verificação de erros pode ser útil muitas vezes, apontando alguns erros mais comuns que ás vezes cometermos. Por outro lado, como qualquer correção feita por um software, nem sempre o erro que ele aponta é realmente um erro. Nesses casos, vamos utilizar a opção ignorar erro. Selecione as três células em que o indicador de erro aparece, clique na marca inteligente e escolha essa opção: Porcentagens Agora, vamos para a célula C10. Nela, precisamos calcular o valor da Comissão sobre as vendas, que será 10% do Total de recitas de cada mês. Embora algumas pessoas prefiram trabalhar com a porcentagem na forma decimal (escrevendo =C7 * 0,10), isso é absolutamente desnecessário e pode causar equívocos com números percentuais mais complexos, com algumas casas decimais. Também é desnecessário introduzir na fórmula a divisão por 100 o sinal de porcentagem (%), já faz isso. Não é errado, portanto, escrever = C7 * 10/100, mas certamente é uma complicação sem nenhuma vantagem. Façamos os seguintes cálculos: o PIS/FINSOCIAL será 2,68% e o ISS será 5% do Total de Receitas. Portanto, teremos: Alça de preenchimento, com fórmulas e números. EXCEL AVANÇADO 2010 / 2013 _____________________________________________________________________________________________ Para uso de: Lucas Magalhães Garcia - (31) 98522-1533 - CPF.: 085.158.776-35 - Turma Nº 4316 Reprodução ou distribuição proibida. IN GOD WE TRUST - www.explicare.com.br - contato@explicare.com.br - (31) 4141-2882 11 A mesma alça de preenchimento que você utilizou há pouco com datas e textos, você pode utilizar para copiar as fórmulas que acabou de criar em janeiro para fevereiro e março. Selecione de C10 a C12 e arraste, através da alça de preenchimento, até a célula E12: Veja o resultado abaixo: Para calcular os totais por despesa e por mês, deveríamos selecionar de C10 a F13 e clicar no botão Autosoma, como já fizermos anteriormente. Mas, temos uma opção melhor. Vamos expandir a seleção. Com a tecla SHIFT pressionada selecione a célula F13. Repare que a área selecionada anteriormente C10:E12 foi expandida para C10:F13. Clique no botão de Autosoma e veja que foram somadas as linhas e colunas. Aproveite que o texto está selecionado e aplique o formato: “Contábil”. No próximo bloco temos as despesas fixas. Estas despesas se repetem nos próximos meses. Então, selecione de C14 até C18 e copie arrastando pela alça de preenchimento: Arraste os valores até a célula E18. Note que os números são simplesmente copiados e não há a criação de nenhuma sequência. Se fizermos uma comparação, no caso de “ PRODUTO 1” a sequência foi criada automaticamente (“PRODUTO 1”, “PRODUTO 2”. “PRODUTO 3”, etc.) Quandotivermos texto acompanhado de número, o Excel fará sequências, quando tivermos somente números, o Excel fará uma cópia. É claro que, no Excel, você pode utilizar as marcas inteligentes para alterar estas opões: EXCEL AVANÇADO 2010 / 2013 _____________________________________________________________________________________________ Para uso de: Lucas Magalhães Garcia - (31) 98522-1533 - CPF.: 085.158.776-35 - Turma Nº 4316 Reprodução ou distribuição proibida. IN GOD WE TRUST - www.explicare.com.br - contato@explicare.com.br - (31) 4141-2882 12 Faça a soma por linhas e colunas como você já aprendeu. Deve ficar assim: Autosoma com subtotais Queremos, na linha 20, fazer a soma das despesas fixas e variáveis. Selecione de C20 até F20. Em um primeiro momento, podemos imaginar que, se utilizarmos o Autosoma, vamos somar as despesas e os subtotais para despesas variáveis e fixas, o que daria o resultado duplicado. Mas, se experimentamos utilizar o Autosoma agora, veremos que ele irá somar somente as duas linhas que já possuem a fórmula de soma: o total de despesas ficas e o total de despesas variáveis. Vejamos: Note que são somadas apenas as linhas 13 e 19, isto é, aquelas que possuem a fórmula de soma, mas não as linhas em que os números foram digitados foram digitados diretamente nas células. 8 . Outras Operações Você já teve a oportunidade de fazer multiplicações, quando trabalhou há pouco tempo com porcentagens. A ideia com subtrações, somas e divisões é a mesma. Relembre apenas os operadores usados: Operação Operador Soma + Subtração - Multiplicação * Divisão / Potência ^ EXCEL AVANÇADO 2010 / 2013 _____________________________________________________________________________________________ Para uso de: Lucas Magalhães Garcia - (31) 98522-1533 - CPF.: 085.158.776-35 - Turma Nº 4316 Reprodução ou distribuição proibida. IN GOD WE TRUST - www.explicare.com.br - contato@explicare.com.br - (31) 4141-2882 13 Agora, podemos calcular o Resultado Líquido (Total de Receitas menos Total de Despesas): Depois de feita a fórmula, arraste-a para as demais colunas. Funções SE Após calcularmos o Resultado Líquido, vamos para o Resultado Líquido após o imposto de 25%. Em cima deste Resultado, a empresa deverá pagar um imposto de 25%. O que queremos encontrar é o lucro da empresa após pagar o imposto. Veja, neste caso, você está encontrando apenas os 25% do Resultado Líquido, isto é, o valor do imposto. Queremos, na verdade, o resultado após pagamos o imposto, isto é, o próprio resultado líquido menos o imposto que você acabou de calcular. =C21 – C21 * 25% Outra solução bastante adequada e que é familiar àqueles que estão acostumados a trabalhar com cálculos de porcentagens é: = C21 * (1 – 25%) Seja qual for a solução que você empregou, arraste até março. Observe o que ocorreu no mês de fevereiro: a empresa teve prejuízo, em cima desse prejuízo, pagou um imposto de 25% - o que já é um equívoco, pois quando a empresa dá prejuízo ela não paga imposto de renda – e o prejuízo, após o pagamento do imposto, diminuiu. Na verdade, pelas regras “inventadas por nós” deveríamos pagar impostos apenas quando a empresa tem lucro. Poderíamos verificar mês a mês se a empresa teve prejuízo ou não e, nos casos em que houve prejuízo, não realizar a cobrança do imposto. Nesse caso, basta repetir o próprio resultado líquido no resultado final. EXCEL AVANÇADO 2010 / 2013 _____________________________________________________________________________________________ Para uso de: Lucas Magalhães Garcia - (31) 98522-1533 - CPF.: 085.158.776-35 - Turma Nº 4316 Reprodução ou distribuição proibida. IN GOD WE TRUST - www.explicare.com.br - contato@explicare.com.br - (31) 4141-2882 14 No entanto, se tivermos uma tabela com muitas colunas para muitos meses, seria trabalhoso ir coluna por coluna, verificando se houve prejuízo ou não. Por isso, queremos fazer uma fórmula em Janeiro, que decida se houve prejuízo ou não e calcule o resultado líquido em função disso. Essa fórmula deverá servir para todos os meses, de modo que poderemos simplesmente copiá-la. A lógica para a fórmula seria: SE Resultado Líquido for menor ou igual 0 (isto é, a empresa deu prejuízo) O Resultado Final será igual ao Resultado Líquido CASO CONTRÁRIO (isto é, a empresa deu lucro) O Resultado Final será igual ao Resultado Líquido Menos 25% Veja que se trata de realizar um teste lógico (no caso, verificar se o Resultado Líquido é menor ou igual a zero ou não) e dar uma resposta, de acordo com o teste lógico. Se o teste der VERDADEIRO (ou seja, a empresa deu prejuízo), a resposta será uma. Se o teste der FALSO, a resposta será outra. Resolvemos este tipo de problema, através da função SE. Você pode digitar a fórmula diretamente na célula ou construí-la com a ajuda do assistente. Se for digitá-la na célula, basta obedecer a esta estrutura. =SE (teste lógico; valor se verdadeiro; valor se falso) No nosso caso, o que queremos testar é se houve prejuízo ou não. Portanto, nosso teste lógico é: C21< = 0 Se este teste der VERDADEIRO, queremos que a resposta seja o próprio resultado líquido. Portanto, nosso “valor se verdadeiro” é: C21 E nosso “valor se falso” será o resultado líquido menos os impostos de 25% C21 – C21 * 25% Ou C21 * (1 – 25%) EXCEL AVANÇADO 2010 / 2013 _____________________________________________________________________________________________ Para uso de: Lucas Magalhães Garcia - (31) 98522-1533 - CPF.: 085.158.776-35 - Turma Nº 4316 Reprodução ou distribuição proibida. IN GOD WE TRUST - www.explicare.com.br - contato@explicare.com.br - (31) 4141-2882 15 A fórmula ficaria assim: Para construir a fórmula através do assistente, em vez de digitá-la diretamente na célula, clique no botão “Lógica”, na aba “Fórmulas” como na figura a seguir. Na lista de funções que será exibida, escolha a função SE. EXCEL AVANÇADO 2010 / 2013 _____________________________________________________________________________________________ Para uso de: Lucas Magalhães Garcia - (31) 98522-1533 - CPF.: 085.158.776-35 - Turma Nº 4316 Reprodução ou distribuição proibida. IN GOD WE TRUST - www.explicare.com.br - contato@explicare.com.br - (31) 4141-2882 16 Se preferir, clique no botão “Inserir Função” da figura acima e na anela que irá aparecer, escolha a categoria Lógica e na área abaixo, escolha a função SE (como na figura abaixo): Clique em OK. Agora, é só preencher a janela que será exibida com o teste lógico, o valor verdadeiro e o valor se falso e clicar em OK. Vide a figura a seguir: EXCEL AVANÇADO 2010 / 2013 _____________________________________________________________________________________________ Para uso de: Lucas Magalhães Garcia - (31) 98522-1533 - CPF.: 085.158.776-35 - Turma Nº 4316 Reprodução ou distribuição proibida. IN GOD WE TRUST - www.explicare.com.br - contato@explicare.com.br - (31) 4141-2882 17 Observe que será escrita na célula, a mesma fórmula que teríamos digitado. O assistente apenas ajuda a lembrar dos parênteses, dos pontos e vírgulas e da ordem em que os argumentos devem ser escritos na função. Bom, agora que você tem uma fórmula ”inteligente” que sabe reconhecer quando houve lucro ou prejuízo e que deduz o imposto somente quando for adequado, podemos arrastá-la para os demais meses. Tome cuidado apenas para não arrastá-la até a coluna do Total. Você deverá arrastar até março e aplicar o Autosoma para encontrar o resultado par os três meses. Na sequência do curso, teremos diversos outros exemplos de função SE. Retorno automático de texto Observe a célula B22: EXCEL AVANÇADO 2010 / 2013 _____________________________________________________________________________________________Para uso de: Lucas Magalhães Garcia - (31) 98522-1533 - CPF.: 085.158.776-35 - Turma Nº 4316 Reprodução ou distribuição proibida. IN GOD WE TRUST - www.explicare.com.br - contato@explicare.com.br - (31) 4141-2882 18 O texto dentro da célula é maior do que a largura da célula. Obviamente, poderíamos aumentar a largura da coluna até que o texto coubesse completamente. Basta ir lá no cabeçalho das colunas, lá onde estão os nomes das colunas, A, B, C, etc., posicionar o mouse entre as colunas B e C e clicar e arrastar para mudar o tamanho a coluna B. Outra opção é dar um duplo clique e deixar que o Excel faça o ajuste automático da largura, deixando exatamente o espaço necessário para caber o texto que estava “cortado”. No entanto, nem sempre é uma boa opção alterar a largura da coluna para fazer caber o conteúdo das células. Às vezes o texto dentro da célula é tão grande que é mais conveniente dividi lo em várias linhas, dentro da mesma célula. Fazer isso no Excel 2010 é muito fácil: clique na guia “Página Inicial” e clique na opção “Quebrar Texto Automaticamente” como na figura abaixo: Veja o resultado ao lado: Outra forma de se chegar a este resultado é dar um duplo clique na célula (para entrar em modo de edição), clicar exatamente no ponto em que desejamos realizar a quebra de linha e utilizar o atalho de teclado ALT + ENTER. Depois, basta dar mais um ENTER para finalizar! Ajuste de casas decimais Este é um exemplo de um texto longo EXCEL AVANÇADO 2010 / 2013 _____________________________________________________________________________________________ Para uso de: Lucas Magalhães Garcia - (31) 98522-1533 - CPF.: 085.158.776-35 - Turma Nº 4316 Reprodução ou distribuição proibida. IN GOD WE TRUST - www.explicare.com.br - contato@explicare.com.br - (31) 4141-2882 19 Podemos formatar os números da nossa tabela, de modo a deixá-los mais fáceis de visualizar. Observe na guia “Página Inicial” na caixa “ Número” as opções de formatação (ao lado). O primeiro irá formatar em modo “moeda” com os padrões R$ 00,00 com duas casas decimais, o segundo em formato “percentual” % com duas casas decimais, o terceiro em formato “contábil” ou conhecido como “milhar” com duas casas decimais, os dois últimos servem como aumento ou recuo de casas decimais. Na caixa de seleção acima temos outras opções de formatação, por exemplo: Data/Hora, Fração, Científico, etc. Veja o exemplo a seguir. Utilizamos o separador de milhares ou contábil e o botão de recuo de casas decimais, para eliminá-las: Bordas Vamos aplicar algumas bordas em partes da nossa tabela. Para deixá-lo visualmente mais organizada. Você pode utilizar o botão de bordas, na guia “Página Inicial” na caixa “ fontes“ na lista “Bordas“. EXCEL AVANÇADO 2010 / 2013 _____________________________________________________________________________________________ Para uso de: Lucas Magalhães Garcia - (31) 98522-1533 - CPF.: 085.158.776-35 - Turma Nº 4316 Reprodução ou distribuição proibida. IN GOD WE TRUST - www.explicare.com.br - contato@explicare.com.br - (31) 4141-2882 20 EXCEL AVANÇADO 2010 / 2013 _____________________________________________________________________________________________ Para uso de: Lucas Magalhães Garcia - (31) 98522-1533 - CPF.: 085.158.776-35 - Turma Nº 4316 Reprodução ou distribuição proibida. IN GOD WE TRUST - www.explicare.com.br - contato@explicare.com.br - (31) 4141-2882 21 Para obter mais opções de linhas e cores, selecione na lista suspensa a opção “Mais Bordas “, onde abrirá a opção de formatar células na guia “bordas”. Inserir / Excluir linhas Veja que a linha B está “sobrando” na nossa tabela. Clique em A8 e utilize o atalho de teclado CTRL – ou vá até a guia “Página Inicial” na caixa “Células” e selecione na lista suspensa a opção excluir células: Vemos nesta lista suspensa que o Excel nos dá mais de uma opção: EXCEL AVANÇADO 2010 / 2013 _____________________________________________________________________________________________ Para uso de: Lucas Magalhães Garcia - (31) 98522-1533 - CPF.: 085.158.776-35 - Turma Nº 4316 Reprodução ou distribuição proibida. IN GOD WE TRUST - www.explicare.com.br - contato@explicare.com.br - (31) 4141-2882 22 • Excluir células: Resume-se a exclusão somente da célula atual. • Excluir Linhas da Planilha: Exclui toda linha de onde se encontra a seleção • Excluir Colunas da Planilha: Exclui toda a coluna de onde se encontra a seleção. • Excluir Planilha: Exclui toda a planilha ativa. No nosso caso vamos escolher a opção “Excluir Linha da Planilha”, já que queremos excluir toda a linha 8. Podemos utilizar também o atalho de teclado para esta tarefa, mas o Excel nos “questionará” qual opção que desejamos realizar como na figura abaixo: EXCEL AVANÇADO 2010 / 2013 _____________________________________________________________________________________________ Para uso de: Lucas Magalhães Garcia - (31) 98522-1533 - CPF.: 085.158.776-35 - Turma Nº 4316 Reprodução ou distribuição proibida. IN GOD WE TRUST - www.explicare.com.br - contato@explicare.com.br - (31) 4141-2882 23 Nesta janela, devemos indicar o que desejamos excluir. No nosso caso, a linha inteira. Uma outra forma de fazer isso é selecionar A8 e usar o atalho de teclado SHIFT + ESPAÇO, o qual seleciona a linha toda. Desse modo, quando utilizarmos o atalho CTRL - , a linha será excluída sem que a janela mostrada anteriormente seja exibida. Podemos também clicar sobre o número da linha com o botão direito do mouse. Neste caso, é apresentado o menu que você confere ao lado. Neste caso, a linha inteira j à está selecionada. Escolha a opção Excluir. Outro atalho que é importante conhecermos é o CTRL + ESPAÇO, que seleciona toda a coluna na qual está a célula atualmente selecionada. Por exemplo, se clicarmos em G7 e utilizarmos esse atalho, será selecionada toda a coluna G. Selecionando fórmulas Vamos de alguma forma, destacar aquilo que é fórmula na nossa tabela, do que é constante – isto é , daquilo que foi digitado diretamente na planilha. Nas versões anteriores do Excel teríamos que ir até o menu “Ir para”, já na versão 2007 do Excel temos uma facilidade para esse recurso; vá até a guia “Página Iniciar“ na caixa “Edição” e clique na lista suspensa com nome “Localizar e Selecionar”. EXCEL AVANÇADO 2010 / 2013 _____________________________________________________________________________________________ Para uso de: Lucas Magalhães Garcia - (31) 98522-1533 - CPF.: 085.158.776-35 - Turma Nº 4316 Reprodução ou distribuição proibida. IN GOD WE TRUST - www.explicare.com.br - contato@explicare.com.br - (31) 4141-2882 24 Na lista de seleções temos as funções mais utilizadas nos poupando tempo, o que poderia ser feito com a seleção manual em “Ir para”, “CTRL +G” ou tecal “F5”, mas continuando ao nosso exemplo, selecione a opção “Fórmulas”, observe que foram selecionadas apenas as células que possuem fórmulas. Mude a cor da fonte para verde, para destacar essas células das demais. Basta usar a barra de ferramentas de formatação ou clicar com o botão direito do mouse sobre a seleção. EXCEL AVANÇADO 2010 / 2013 _____________________________________________________________________________________________ Para uso de: Lucas Magalhães Garcia - (31) 98522-1533 - CPF.: 085.158.776-35 - Turma Nº 4316 Reprodução ou distribuição proibida. IN GOD WE TRUST - www.explicare.com.br - contato@explicare.com.br - (31) 4141-2882 25 Selecione também os números que foram digitados na planilha (e que são a base para as fórmulas). Para selecionar duas áreas descontínuas, selecione a primeira e, mantendo a tecla CTRL, pressionada, selecione a segunda área. Altere a cor de fontepara azul, para destacar essas células em relação ao restante da planilha. Mover, renomear, copiar planilhas Primeiro vamos criar uma nova planilha: do lado direito das guias com os nomes de planilhas temos uma pequena guia para inserir uma nova planilha (conforme indicado pela seta), clique e será inserida uma nova planilha. Vamos alterar a posição das planilhas Plan1 e Plan2, Basta clicar na guia da Plan1 e arrastá-la para depois da Plan2 (ou a Plan2 para antes da Plan1). Agora, vamos mudar o nome da Plan2. Imagine que este relatório que acabamos de fazer era para a filial de Belo Horizonte da empresa. Vamos mudar o nome da planilha para BH: dê um duplo clique sobre o nome atual, digite o nome desejado e tecle ENTER. Em seguida, vamos imaginar que a empresa possui, na verdade, duas filiais: uma em Belo Horizonte e outra no Rio de Janeiro. Precisamos, então, de uma cópia da planilha BH com a mesma estrutura desta, só que com o nome de RJ. Para copiar planilhas, existem vários métodos, como inserir uma planilha em branco e utilizar a cópia e colar ou clicar com o botão direito na guia de uma planilha e usar a opção Mover ou copiar... Porém, provavelmente, você vai preferir o modo mais prático de todos: clique e arraste a guia da planilha que deseja copiar, como se fosse simplesmente mudá-la de posição (como você fez há pouco). Enquanto isso, segure a tecla CTRL: você verá que em vez de mover a planilha, você criou EXCEL AVANÇADO 2010 / 2013 _____________________________________________________________________________________________ Para uso de: Lucas Magalhães Garcia - (31) 98522-1533 - CPF.: 085.158.776-35 - Turma Nº 4316 Reprodução ou distribuição proibida. IN GOD WE TRUST - www.explicare.com.br - contato@explicare.com.br - (31) 4141-2882 26 uma cópia. Mude o nome dessa nova planilha para RJ. Totalizando duas ou mais planilhas. Agora queremos totalizar as duas planilhas. Desta forma, duplique a planilha RJ nomeando-a de Total. Utilizando o recurso que aprendemos acima, vamos selecionar as constantes desta planilha. Para tal, selecionamos de C3 até F21, vá a guia “Iniciar” e selecione na caixa “Edição” na lista suspensa de “ Localizar e Selecionar” em seguida “Constantes” como na figura ao lado. Conforme você pode verificar, somente os valores que não contém fórmulas foram selecionados. Pressione Delete para eliminá-los. Em nossa planilha, permanecem somente as fórmulas. A seguir selecione a Célula C3 e vamos calcular o total vendido do Produto 1. Para efetuarmos uma soma envolvendo dados de outras planilhas podemos proceder da seguinte forma: Digitamos o sinal de =, selecionamos a planilha BH, a seguir selecionamos a célula C3 e pressionamos o sinal de +. Acompanhe a evolução da fórmula na barra de fórmulas conforme figura abaixo: Repare que aparece o sinal de = seguido do nome da planilha (BH), o sinal de ! (Exclamação) e o endereço da célula (C3). Sempre que efetuarmos qualquer operação envolvendo dados de outras Planilhas, será apresentado neste formato. EXCEL AVANÇADO 2010 / 2013 _____________________________________________________________________________________________ Para uso de: Lucas Magalhães Garcia - (31) 98522-1533 - CPF.: 085.158.776-35 - Turma Nº 4316 Reprodução ou distribuição proibida. IN GOD WE TRUST - www.explicare.com.br - contato@explicare.com.br - (31) 4141-2882 27 A seguir, selecione a planilha RJ e a Célula C3 e pressione ENTER. Veja o resultado da fórmula na figura abaixo: Agora imagine que você tem 10 filiais e precisa efetuar a totalização delas. O procedimento é o mesmo, mas é trabalhoso. Para simplificar, podemos utilizar a função Autosoma. Apague a fórmula que acabamos de ciar. Selecione a Célula C3 e clique no botão Autosoma. Selecione a planilha SP e a célula C3. Pressione e mantenha pressionado a tecla SHIFT e selecione a planilha RJ. Não há necessidade de selecionar a célula C3 da planilha RJ. Acompanhe a fórmula na barra de fórmulas. Utilizando este método de soma pelo Autosoma tem como único limitante que todos os dados têm que estar nos mesmo endereços. Para preencher os demais dados, basta copiar arrastando. EXCEL AVANÇADO 2010 / 2013 _____________________________________________________________________________________________ Para uso de: Lucas Magalhães Garcia - (31) 98522-1533 - CPF.: 085.158.776-35 - Turma Nº 4316 Reprodução ou distribuição proibida. IN GOD WE TRUST - www.explicare.com.br - contato@explicare.com.br - (31) 4141-2882 28 Convertendo valores para Dólar Agora precisamos converter os valores para Dólar para poder enviar para a Matriz. Desta forma, duplique a planilha RJ e nomeia-a de Dólar. Selecione as constantes e elimine-as. Selecione a célula C3 e vamos criar a fórmula para efetuar a conversão para Dólar. Inicialmente vamos estimar a taxa do Dólar para os meses de Janeiro em R$ 1,68, Fevereiro em R$ 1,73 e Março em R$ 1,71. A conversão é uma função simples de divisão. Então, vamos dividir o total do Produto 1 que está na célula C3 na planilha total pelo valor do Dólar estimado que é de R$ 1,68. Nossa fórmula fica da seguinte forma: =Total!C3/2,24, e chegamos ao total em Dólar de U$$ 10.495,54. Para calcular os valores de Fevereiro e Março deve-se proceder da mesma forma. Feito isto, para estender as fórmulas para o restante da planilha basta copiar arrastando. Mas, ao apresentarmos o nosso trabalho para o Diretor, este solicita que seja feita uma simulação, estimando a taxa do Dólar em R$ 2,35. Neste caso, para alterar, temos que editar as fórmulas, alterando a taxa de R$ 2,24 para R$ 2,35, o que se mostra bem trabalhoso. Uma solução para situações deste tipo é não utilizar a taxa (variável)dentro da fórmula. O ideal é utilizá-la fora da fórmula ficando como uma referência. Podemos criar uma linha adicionar na planilha, por exemplo, inserindo-a antes dos meses e colocando as taxas de Dólar lá. EXCEL AVANÇADO 2010 / 2013 _____________________________________________________________________________________________ Para uso de: Lucas Magalhães Garcia - (31) 98522-1533 - CPF.: 085.158.776-35 - Turma Nº 4316 Reprodução ou distribuição proibida. IN GOD WE TRUST - www.explicare.com.br - contato@explicare.com.br - (31) 4141-2882 29 A elaboração da fórmula continua igual, ou seja, vamos dividir o total do Produto 1 que está na célula C3 na planilha total pelo valor do Dólar estimado que é neste caso está representado pela célula C2. Desta forma, nossa fórmula fica sendo: = Total!C3/C2 No caso de haver necessidade de alterar a taxa de Dólar, basta alterar a referência que toda a planilha se atualiza automaticamente. Bem mais simples. Fixando referências nas fórmulas Finalizando o exercício acima, para copiar nossa fórmula para os demais, meses e planilhas, basta selecionar a célula C4 (que é onde criamos nossa fórmula) e arrastar. Certo? Confira o resultado abaixo: Não funcionou. Vamos ver o que aconteceu. Selecione a célula C5 e analise a fórmula. A fórmula é a seguinte:= Total!C4/C3. Analisando a fórmula podemos verificar que estamos dividindo o total do Produto 2 pela célula C3, que não é onde temos a taxa do Dólar. Isto ocorreu porque ao copiarmos a fórmula contida na célula C4 o Excel automaticamente ajustou as referências. Nesta fórmula tínhamos duas referências. Uma que apresenta o total do Produto 1.Na fórmula inicial era representada por Total!C3. Quando copiamos para a célula de baixo, esta referência foi “ajustada” para Total!C4. Também neste caso a alteração foi efetuada automaticamente pelo Excel e está correta, pois o total do Produto 2 está nesta célula. É importante ter em conta que o Excel sempre vai proceder desta forma, ou seja, sempre que criamos uma fórmula e a copiamos paraoutras células ocorrerão estes ajustes. No caso de não EXCEL AVANÇADO 2010 / 2013 _____________________________________________________________________________________________ Para uso de: Lucas Magalhães Garcia - (31) 98522-1533 - CPF.: 085.158.776-35 - Turma Nº 4316 Reprodução ou distribuição proibida. IN GOD WE TRUST - www.explicare.com.br - contato@explicare.com.br - (31) 4141-2882 30 querermos que os ajustes sejam efetuados, temos que “avisar” ao Excel. No exemplo não queremos que o Excel ajuste a fórmula mudando o endereço de onde colocamos a taxa de Dólar. Neste caso, devemos “avisá-lo” colocando o símbolo do cifrão ($) na fórmula. A regra é a seguinte: Ao efetuarmos referência a uma célula indicamos a Coluna na qual ela se encontra e também a linha. No nosso exemplo, fazemos referência ao Dólar que está na célula C2. Se quisermos que a coluna fique fixa devemos colocar o cifrão antes da indicação da coluna. Neste caso a fórmula ficaria sendo: = Total!C4/ $C3. Se quisermos que a linha fique fixa devemos colocar o cifrão antes da indicação da linha. Neste caso a fórmula ficaria sendo: =Total!C4/C$2. Se quisermos que a coluna e a linha fiquem fixas, devemos colocar o cifrão antes da indicação da coluna e da linha. Neste caso a fórmula ficaria sendo: = Total!C4/$C$2. Em nosso exemplo, temos interesse que somente a linha fique fixa, deixando a coluna livre. Neste caso, nossa fórmula deve ser conforme abaixo: = Total!C4/C$2. Dica: uma forma prática de fixar fórmulas é utilizando a tecla F4. Para utilizá-la, basta selecionar a fórmula e clicar com o mouse sobre a parte da fórmula que se quer fixar e pressionar Repare que automaticamente o Excel fixa a Coluna e a Linha. Se você pressionar F4 outra vez somente a linha fica fixa. Pressionando F4 novamente somente a coluna fica fixa. E pressionando mais uma vez, a fórmula fica novamente livre. Experimente. Feito isto, teste sua fórmula arrastando-a para os meses de Fevereiro e Março. A seguir, aproveitando que os três meses estão selecionados, arraste a fórmula para baixo completando os produtos 2,3 e 4. O resultado deve ser igual à figura abaixo: EXCEL AVANÇADO 2010 / 2013 _____________________________________________________________________________________________ Para uso de: Lucas Magalhães Garcia - (31) 98522-1533 - CPF.: 085.158.776-35 - Turma Nº 4316 Reprodução ou distribuição proibida. IN GOD WE TRUST - www.explicare.com.br - contato@explicare.com.br - (31) 4141-2882 31 Experimente alterar a taxa de Dólar e veja a planilha atualizar-se automaticamente. Para finalizar seu exercício, basta copiar a fórmula contida na célula C3 para a célula C12. Mas, vamos copiar de outra forma, selecione a célula C3 e arraste-a com o mouse. Antes de soltar na célula C12 pressione e mantenha pressionada á tecla Ctrl. Repare que junto do ponteiro do mouse aparece o sinal de + (mais). Solte o botão do mouse. Pronto. Você criou uma cópia. Dica: Quando você arrasta algum dado com o mouse, normalmente você move de local: é o atalho para recortar e colar. Quando você faz este procedimento junto com o Ctrl, você está copiando: é o atalho para copiar e colar. FERRAMENTA PROTEGER Uma ferramenta muito útil que o Excel possui é a ferramenta “Proteger”. Com ela, você pode proteger a sua planilha ou até mesmo a sua pasta de trabalho contra mudanças indesejáveis, tais como: inserção de novas planilhas, exclusão ou inserção de linhas, alterando na formatação, modificando a estrutura da planilha original: remoção acidental de fórmulas complexas e etc. Veremos basicamente três tipos de proteção: A proteção de planilha: Permitir que os usuários editem intervalos. Proteger pasta de trabalho e Proteger o arquivo com uma senha. PROTEÇÃO DE PLANILHA A proteção de planilha é usada basicamente para proteger determinadas partes de sua planilha de remoções ou mudanças acidentais das fórmulas efetuadas. Essa proteção torna-se bastante útil, uma vez que, em alguns casos, podemos demorar horas produzindo uma fórmula e esta pose ser removida acidentalmente a qualquer momento, principalmente se a planilha for manuseada por mais de uma pessoa. Para proteger a planilha devemos primeiro decidir se queremos proteger a planilha inteira, ou parte dela. O recurso de proteção permite que se proteja parte da planilha e que se liberem outras partes, tais como áreas onde são lançados dados que precisamser “acrescentados” diariamente, EXCEL AVANÇADO 2010 / 2013 _____________________________________________________________________________________________ Para uso de: Lucas Magalhães Garcia - (31) 98522-1533 - CPF.: 085.158.776-35 - Turma Nº 4316 Reprodução ou distribuição proibida. IN GOD WE TRUST - www.explicare.com.br - contato@explicare.com.br - (31) 4141-2882 32 preservando-se (protegendo-se) as fórmulas, formatação, inclusão e exclusão de linhas, etc. Utilizando a planilha SP, selecione com o mouse somente as áreas da planilha que você não quer proteger, como no exemplo da figura abaixo. Mais uma tarefa que no Excel 2010 ficou mais prática: nas versões anteriores teríamos que ir até a opção formatar células e abrir a guia proteção e selecionar a opção desejada. Já no Excel 2010 temos a facilidade da guia “Página Inicial” e na lista suspensa “Formatar” selecionar opção “Formatar Células”. Vide figura ao lado: Explicação: originalmente toda planilha vem “Bloqueada” e quando queremos que alguma parte seja liberada, devemos entrar no modo de formatação e tirar este “Bloqueio”. Depois de feito isso, deveremos bloquear nossa planilha. Para isso iremos na aba “Revisão” e selecionaremos a opção “Proteger Planilha”, surgirá uma tela de opções como a da figura a seguir. EXCEL AVANÇADO 2010 / 2013 _____________________________________________________________________________________________ Para uso de: Lucas Magalhães Garcia - (31) 98522-1533 - CPF.: 085.158.776-35 - Turma Nº 4316 Reprodução ou distribuição proibida. IN GOD WE TRUST - www.explicare.com.br - contato@explicare.com.br - (31) 4141-2882 33 Inicialmente é apresentado o campo para inclusão de uma senha. Apesar de a senha poder ser criada de imediato, somente clique em OK após ter definido as permissões para os usuários da planilha. Podemos criar uma senha, evitando-se assim que um usuário não autorizado desproteja a planilha e efetue alterações não autorizadas. Optando-se por colocar-se uma senha, será solicitada a confirmação da mesma. Vide figura abaixo: É importante ler o aviso que é apresentado na confirmação da senha, que realça os cuidados com as senhas. A seguir, é apresentada uma relação de permissões de modificações na planilha. As permissões mais comuns são “selecionar as células bloqueadas e desbloqueadas”. Neste caso, você deve avaliar o que pode e o que não pode ser alterado na planilha. EXCEL AVANÇADO 2010 / 2013 _____________________________________________________________________________________________ Para uso de: Lucas Magalhães Garcia - (31) 98522-1533 - CPF.: 085.158.776-35 - Turma Nº 4316 Reprodução ou distribuição proibida. IN GOD WE TRUST - www.explicare.com.br - contato@explicare.com.br - (31) 4141-2882 34 Após ter indicado o que deve ser protegido, clique em OK. Pronto. Sua planilha já está protegida. Para testar a proteção, selecione uma célula que não tenha sido “destravada” e tente alterá-la. Aparecerá a seguinte mensagem: Para desproteger a planilha, basta clicar novamente na guia “Revisão” e na opção “Desproteger Planilha”. Caso você tenha criado uma senha, digite-a na área solicitada e clique em OK. Pronto. Sua planilha estará novamente desprotegida.Outra opção que temos ao proteger uma planilha é ocultar dados protegidos, tais como fórmulas. Quando iniciamos o procedimento de proteção, no menu Formatar, no mesmo menu onde determinamos se as células estariam “Bloqueadas” ou não, tínhamos a opção ”Ocultas”. Na opção de ocultas temos que ir até a guia “Página Inicial” opção “Formatar Células” e na aba “Proteção” conforme a figura a seguir. Vamos fazer um teste. Selecione as fórmulas da planilha SP onde são apresentados os totais por EXCEL AVANÇADO 2010 / 2013 _____________________________________________________________________________________________ Para uso de: Lucas Magalhães Garcia - (31) 98522-1533 - CPF.: 085.158.776-35 - Turma Nº 4316 Reprodução ou distribuição proibida. IN GOD WE TRUST - www.explicare.com.br - contato@explicare.com.br - (31) 4141-2882 35 produto, conforme figura abaixo. Clique na guia “Página Inicial”, na lista “Formatar” selecione “Formatar Células”, aba “Proteção”. Ative a opção “Oculta”. A seguir clique na aba “Revisão”, “Proteger planilha” e finalize com OK. Repare que as fórmulas não são apresentadas. Altere um valor qualquer na planilha e veja que o total é modificado. Com esta opção, conseguimos ocultar as fórmulas. PERMITIR QUE OS USUÁRIOS EDITEM INTERVALOS Outra opção de liberar dados numa planilha que será protegida é efetuar as autorizações diretamente em Revisão, opção Permitir que os usuários editem intervalos. Vide figura a seguir: Neste caso, devemos determinar quais intervalos a quais tipos de permissão, podendo inclusive acrescentar uma senha. Vide figura a seguir: EXCEL AVANÇADO 2010 / 2013 _____________________________________________________________________________________________ Para uso de: Lucas Magalhães Garcia - (31) 98522-1533 - CPF.: 085.158.776-35 - Turma Nº 4316 Reprodução ou distribuição proibida. IN GOD WE TRUST - www.explicare.com.br - contato@explicare.com.br - (31) 4141-2882 36 Clique em Novo e nomeie o intervalo que ficará liberado. Neste exemplo, vamos nomear como “Valores das receitas de produtos”. A seguir, delimite a área a ser liberada. Neste exemplo a range “C3:E6” e coloque uma senha (opcional) e clique em OK. Vide figura a seguir. Ao tentar alterar os valores na planilha, o Excel apresenta a seguinte mensagem: EXCEL AVANÇADO 2010 / 2013 _____________________________________________________________________________________________ Para uso de: Lucas Magalhães Garcia - (31) 98522-1533 - CPF.: 085.158.776-35 - Turma Nº 4316 Reprodução ou distribuição proibida. IN GOD WE TRUST - www.explicare.com.br - contato@explicare.com.br - (31) 4141-2882 37 Digite a senha e a área será liberada. Ao salvar o arquivo e abri-lo novamente, a proteção é novamente ativada. Feito isto, para ativar este recurso, deve-se proteger a planilha. PROTEGER PASTA DE TRABALHO A opção “Proteger a pasta de trabalho” serve para proteger a estrutura da pasta de trabalho, de modo que as planilhas não possam ser excluídas, movidas, ocultas, reexibidas ou renomeadas. E novas planilhas não possam ser incluídas. Você pode também impedir que as janelas de uma pasta de trabalho sejam movidas, redimensionadas, reexibidas ou fechadas. Para proteger a pasta de trabalho você deve Clicar na guia “Revisão” e na opção “Proteger Pasta de trabalho...”. Aparecerá a seguinte janela: Repare que o campo ”Estrutura”, já vem selecionado. Caso você queria proteger também as Janelas EXCEL AVANÇADO 2010 / 2013 _____________________________________________________________________________________________ Para uso de: Lucas Magalhães Garcia - (31) 98522-1533 - CPF.: 085.158.776-35 - Turma Nº 4316 Reprodução ou distribuição proibida. IN GOD WE TRUST - www.explicare.com.br - contato@explicare.com.br - (31) 4141-2882 38 de sua pasta clique no campo “Janelas”. Ao ativar a proteção ”Janelas”, você está impedindo que as planilhas sejam redimensionadas, ocultadas, reexibidas ou fechadas. Em seguida, digite uma senha (opcional) e clique em OK. Pronto. Sua pasta de trabalho já está protegida. CRIANDO UMA SENHA PARA SEU ARQUIVO Para criar uma senha num arquivo do Excel 2010/2013 é muito simples: Basta clicar na guia Arquivo e em “Salvar Como”. Seguir aparecerá a seguinte Janela: Em seguida na opção “Ferramentas” selecione a opção “Opções gerais” aparecerá a janela “Opções Gerais”. Nesta janela, você terá a opção de criar uma senha de proteção de seu arquivo e/ou uma senha de gravação de seu arquivo também. Criaremos somente uma senha de proteção. No campo “Senha de proteção”, crie uma senha de fácil memorização. Clique em OK. E confirme a senha na próxima tela. Vide figura a seguir. EXCEL AVANÇADO 2010 / 2013 _____________________________________________________________________________________________ Para uso de: Lucas Magalhães Garcia - (31) 98522-1533 - CPF.: 085.158.776-35 - Turma Nº 4316 Reprodução ou distribuição proibida. IN GOD WE TRUST - www.explicare.com.br - contato@explicare.com.br - (31) 4141-2882 39 Conforme podemos verificar acima, temos duas opções de proteção. Senha de proteção e Senha de gravação. Podemos optar por uma ou outra, ou mesmo as duas, e além disto ainda assinalar que é Recomendável somente Leitura. Ao final, quando clicar em OK. Será solicitada a confirmação das senhas digitadas. Após salvar o arquivo e fechá-lo, ao tentar abrir novamente, as senhas serão solicitadas. LÓGICA DA AUTOSOMA Para este capítulo o instrutor fornecerá um arquivo pré digitado. Ao abri-lo, teremos uma tela conforme figura abaixo: Neste arquivo em Excel, criamos uma macro que simula algumas situações, para facilitar o entendimento da lógica que o botão Autosoma [ ]possui. Na figura acima, está sendo EXCEL AVANÇADO 2010 / 2013 _____________________________________________________________________________________________ Para uso de: Lucas Magalhães Garcia - (31) 98522-1533 - CPF.: 085.158.776-35 - Turma Nº 4316 Reprodução ou distribuição proibida. IN GOD WE TRUST - www.explicare.com.br - contato@explicare.com.br - (31) 4141-2882 40 apresentado o exemplo nº 1 (botão Ex.1). Conforme vemos acima, numa tabela, são apresentados diversos números, e o cursor no centro destes números. No caso de utilizarmos o botão Autosoma para criar uma somatória, quais números serão somados? Os que estão acima? Os que estão abaixo? Os que estão a esquerda do cursor ou os que estão à direita do cursor? Experimente clicar no botão Autosoma e veja o resultado. Confira na figura a seguir. Conforme podemos conferir, quando o cursor estiver “cercado” de números por todos os lados, serão somados por padrão os valores que estiverem acima do cursor. Vamos ver agora o exemplo nº 2. Clique no botão Ex 2 e confira o resultado. Vide figura abaixo. EXCEL AVANÇADO 2010 / 2013 _____________________________________________________________________________________________ Para uso de: Lucas Magalhães Garcia - (31) 98522-1533 - CPF.: 085.158.776-35 - Turma Nº 4316 Reprodução ou distribuição proibida. IN GOD WE TRUST - www.explicare.com.br - contato@explicare.com.br - (31) 4141-2882 41 Neste segundo exemplo, novamente temos o cursor “cercado” de números por todos os lados, exceto pelos de cima, que já vimos no exemplo 1 . E agora? Se utilizarmos a função Autosoma quais nú3meros serão somados? Os da direita do cursor? Os da esquerda do cursor ou os abaixo do cursor? Vamos clicar no botão AutoSoma e ver o resultado. Confirma abaixo. Podemos constatar que não tendo números acima, a lógica é somar o que se encontra à esquerda do cursor. EXCEL AVANÇADO 2010 / 2013 _____________________________________________________________________________________________ Para usode: Lucas Magalhães Garcia - (31) 98522-1533 - CPF.: 085.158.776-35 - Turma Nº 4316 Reprodução ou distribuição proibida. IN GOD WE TRUST - www.explicare.com.br - contato@explicare.com.br - (31) 4141-2882 42 Clique no botão AutoSoma e veja o resultado. Vamos ver agora o exemplo nº 3. Clique no botão Ex. 3 e confira o resultado. Vide próxima figura. Neste terceiro exemplo, temos números à direita e a abaixo do cursor. E agora, quais números serão somados? Os da direita do cursor ou os abaixo do cursor? Confira abaixo. EXCEL AVANÇADO 2010 / 2013 _____________________________________________________________________________________________ Para uso de: Lucas Magalhães Garcia - (31) 98522-1533 - CPF.: 085.158.776-35 - Turma Nº 4316 Reprodução ou distribuição proibida. IN GOD WE TRUST - www.explicare.com.br - contato@explicare.com.br - (31) 4141-2882 43 Neste caso, o botão do Autosoma não efetua a soma automaticamente. Pela lógica de tabela, somam-se automaticamente somente números acima do cursor ou à esquerda. Mas, isto não significa que não podemos utilizar o AutoSoma. Podemos sim. Para tanto, basta selecionar os valores que devem ser somados e pressionar o ENTER, ou clicar novamente no botão AutoSoma. Vamos ver agora o exemplo nº 4. Clique no Ex 4. Confira a figura a seguir: EXCEL AVANÇADO 2010 / 2013 _____________________________________________________________________________________________ Para uso de: Lucas Magalhães Garcia - (31) 98522-1533 - CPF.: 085.158.776-35 - Turma Nº 4316 Reprodução ou distribuição proibida. IN GOD WE TRUST - www.explicare.com.br - contato@explicare.com.br - (31) 4141-2882 44 Neste exemplo, temos um mini- orçamento doméstico, com algumas somatórias já efetuadas. No primeiro bloco temos as receitas, classificados como entradas, totalizando R$ 3.600,00. No bloco abaixo, temos as despesas, divida-as em duas categorias, com valores parciais de R$ 1.150,00 e R$ 1.540,00. Neste exemplo, posicione o cursor justamente abaixo do total R$ 1.540,00, do bloco de despesas. Queremos somar os dois blocos de despesas. Podemos utilizar o botão AutoSoma para efetuar esta soma? Se o utilizarmos, que valores ele somará? Clique no botão Autosoma e confira o resultado. EXCEL AVANÇADO 2010 / 2013 _____________________________________________________________________________________________ Para uso de: Lucas Magalhães Garcia - (31) 98522-1533 - CPF.: 085.158.776-35 - Turma Nº 4316 Reprodução ou distribuição proibida. IN GOD WE TRUST - www.explicare.com.br - contato@explicare.com.br - (31) 4141-2882 45 Conforme figura acima, podemos constatar que quando o AutoSoma detecta que nos valores acima tem fórmulas, ele soma as fórmulas, não considerando os valores. O valor correspondente à receita (R$ 3.600,00) não foi somado porque tem uma linha em branco separando. Se houvesse outros blocos acima estes também seriam somados. Vamos agora para o exemplo nº 5. Clique no botão e confira a figura seguinte: EXCEL AVANÇADO 2010 / 2013 _____________________________________________________________________________________________ Para uso de: Lucas Magalhães Garcia - (31) 98522-1533 - CPF.: 085.158.776-35 - Turma Nº 4316 Reprodução ou distribuição proibida. IN GOD WE TRUST - www.explicare.com.br - contato@explicare.com.br - (31) 4141-2882 46 Temos agora uma situação diferente. Temos que efetuar a somatória de diversas colunas e diversas linhas. Queremos totalizar os meses de Janeiro a Março (colunas), e os totais referentes aos Produtos 1 a 4 (linhas). Como posso utilizar o botão AutoSoma para efetuar todas estas somas com um único clique? Experimente selecionar todos os números, selecionando também a coluna correspondente ao total e a linha de total abaixo. Como na figura abaixo. EXCEL AVANÇADO 2010 / 2013 _____________________________________________________________________________________________ Para uso de: Lucas Magalhães Garcia - (31) 98522-1533 - CPF.: 085.158.776-35 - Turma Nº 4316 Reprodução ou distribuição proibida. IN GOD WE TRUST - www.explicare.com.br - contato@explicare.com.br - (31) 4141-2882 47 Com esta área selecionada, clique no botão AutoSoma e confira o resultado. Vide figura a seguir. Com um único clique, foi possível efetuar todas as somatórias. Outras dicas: Se acima do cursor o número apresentado for uma fórmula, a lógica determina que sejam somados os números `a esquerda do cursor. Se também a esquerda contiver fórmulas, então serão somados os dados acima do curso, mesmo que contenham fórmulas. Tecla de atalho. Você pode utilizar o AutoSoma pelo ícone na barra de ferramentas, ou utilizar as teclas de atalho “ Alt =”. FUNÇÕES CONDICIONAIS FUNÇÃO “SE” JUNTO COM A FUNÇÃO “E” Já aprendemos como criar e utilizar a função condicional SE. Neste capítulo vamos aprender a utilizar a função SE junto com outras funções. Neste caso, quando juntamos diversas funções em uma mesma fórmula, dizermos que estamos “aninhando” as funções, ou são funções “aninhadas”. Abra o arquivo deste capítulo e vamos para o nosso próximo exercício: Vamos supor a seguinte situação: Problema: “Com base na tabela abaixo, definir se um aluno está “Aprovado ou “Reprovado”, tomando como base sua média anual e sua frequência. Neste caso, temos que analisar em conjunto as duas condições. Para ser aprovado o aluno necessita de nota igual ou superior a 7 e frequência mínima de 70%. Veja exemplo abaixo: EXCEL AVANÇADO 2010 / 2013 _____________________________________________________________________________________________ Para uso de: Lucas Magalhães Garcia - (31) 98522-1533 - CPF.: 085.158.776-35 - Turma Nº 4316 Reprodução ou distribuição proibida. IN GOD WE TRUST - www.explicare.com.br - contato@explicare.com.br - (31) 4141-2882 48 Vamos desenvolver a função SE em conjunto com a função E utilizando o assistente de funções do Excel. A função E é utilizada em conjunto com a função SE sendo ela que determina que duas condições devem ser atendidas. Não confunda com a condição OU, que permite que uma ou outra condição seja atendida. A função OU será estudada nos próximos tópicos. Selecione a célula D17, que é onde queremos que o resultado seja apresentado. Na guia Fórmulas clique no ícone “FX”, que fica na barra de fórmulas, para inserir a função “SE”. Ao clicar no “FX” o Excel apresenta a caixa de diálogo Inserir Função. Nesta caixa de diálogo, o Excel apresenta as dez (10) últimas funções que foram utilizadas, em ordem de utilização (as últimas em cima). Caso a função esteja nesta relação, basta selecioná-la e clica no botão OK ou ainda clicar duas vezes na função. Caso a função a ser utilizada não esteja nesta relação, você p ode localizá-la selecionando a categoria a qual ela pertence. Vide figura abaixo: EXCEL AVANÇADO 2010 / 2013 _____________________________________________________________________________________________ Para uso de: Lucas Magalhães Garcia - (31) 98522-1533 - CPF.: 085.158.776-35 - Turma Nº 4316 Reprodução ou distribuição proibida. IN GOD WE TRUST - www.explicare.com.br - contato@explicare.com.br - (31) 4141-2882 49 Em nosso exemplo, a função que estamos procurando é a SE. A função SE faz p arte da categoria de funções Lógicas. Selecione a categoria Lógica e selecione a função SE. Clicando a seguir em OK. Outra forma de localizar a função é digitar o nome dela no campo “Procure por uma função” e clicar em Ir. A seguir clique em OK para ativá-la. Quando não se lembrar do nome da função que procura e nem em que categoria está classificada é possível selecionar a categoria Todas e procurar a função, uma a uma, sendo que as funções estão listadas em ordem alfabética, independente da categoriaa que pertencem. Ao localizá-la, clique em OK. EXCEL AVANÇADO 2010 / 2013 _____________________________________________________________________________________________ Para uso de: Lucas Magalhães Garcia - (31) 98522-1533 - CPF.: 085.158.776-35 - Turma Nº 4316 Reprodução ou distribuição proibida. IN GOD WE TRUST - www.explicare.com.br - contato@explicare.com.br - (31) 4141-2882 50 A função SE é apresentada, conforme figura acima. Temos agora que preencher os argumentos da função. Relembrando nosso problema, temos juntar na função SE a função E, pois o aluno, para ser aprovado, tem que ter média igual ou superior a 7 e ao mesmo tempo , ter tido uma frequência igual ou superior a 70%. Para executar esta condição, vamos inserir no argumento “Teste_Lógico” a função E. Repare que pelo “FX” não é possível inserir, pois o “FX” não está disponível neste instante (estamos como a função SE ativa). Quando precisamos utilizar o assistente de funções para inserir uma segunda função(aninhar), o Excel transfere o acesso ao assistente de funções para a Caixa de Nome. Vide figura a seguir: Repare que na Caixa de nome a função SE é a primeira da lista, pois é a que você está utilizando. Na Caixa de nome estão as 10 últimas funções utilizadas pelo usuário, e a última linha temos acesso a “Mais funções”, abrindo a tela do assistente novamente. EXCEL AVANÇADO 2010 / 2013 _____________________________________________________________________________________________ Para uso de: Lucas Magalhães Garcia - (31) 98522-1533 - CPF.: 085.158.776-35 - Turma Nº 4316 Reprodução ou distribuição proibida. IN GOD WE TRUST - www.explicare.com.br - contato@explicare.com.br - (31) 4141-2882 51 A forma de utilização é a mesma citada anteriormente. Localize a função desejada e clique em OK. No nosso exemplo, procure pela função E e clique em OK. O resultado você pode conferir na figura abaixo: A caixa de diálogo do argumento da função é apresentada. A primeira impressão que se tem é que a função SE foi substituída pela função E. A função SE continua lá. Repare que a fórmula está sendo escrita na barra de fórmulas. Confira a figura a seguir: Para você ter acesso novamente a função SE basta clicar na barra de fórmulas sobre a palavra SE. Experimente. Veja a figura a seguir. Repare que na barra de fórmulas a função continua igual, mas a Caixa de diálogo do argumento da função foi substituído pela função SE, sendo apresentado com a função E no campo Teste_lógico. EXCEL AVANÇADO 2010 / 2013 _____________________________________________________________________________________________ Para uso de: Lucas Magalhães Garcia - (31) 98522-1533 - CPF.: 085.158.776-35 - Turma Nº 4316 Reprodução ou distribuição proibida. IN GOD WE TRUST - www.explicare.com.br - contato@explicare.com.br - (31) 4141-2882 52 Para voltar a função E proceda da mesma forma que explicamos acima, clicando na barra de fórmulas sobre a sílaba E. Veja figura abaixo: Vamos preencher as condições que queremos na função E. Na Lógica 1 devemos selecionar a célula D15 e acrescentar que deve ser maior ou igual a 7. O resultado deve ser D15>=7. A seguir pressione o TAB ou clique com o mouse no campo Lógica 2 e selecione a célula D16 indicando que deve ser maior ou igual a 70%. O resultado deve ser D16>=70%. Vide a figura abaixo: Dica: sempre que você puder, evite escrever as condições. Se for uma referência a uma célula, como no exemplo acima, selecione com o mouse a célula. Desta forma você evita erros. A função E foi integralmente preenchida. Criamos condição para a média e para a frequência. Caso tivéssemos mais alguma condição, bataria selecionar o campo Lógica 3 e preencher. À medida que você for preenchendo os campos de Lógica, outros campos vão sendo abertos. Conforme descrito EXCEL AVANÇADO 2010 / 2013 _____________________________________________________________________________________________ Para uso de: Lucas Magalhães Garcia - (31) 98522-1533 - CPF.: 085.158.776-35 - Turma Nº 4316 Reprodução ou distribuição proibida. IN GOD WE TRUST - www.explicare.com.br - contato@explicare.com.br - (31) 4141-2882 53 no Argumento da função, podemos utilizar até 30 condições. Para voltar para a função SE, clique na palavra SE na barra de fórmulas. Veja na figura a seguir como deve estar sua fórmula neste momento. Agora você só precisa preencher o argumento “Valor_se_verdadeiro”, que deve ser Aprovado e o argumento “Valor_se_falso” que deve ser Reprovado. Vide figura abaixo. Repare em alguns detalhes: Do lado direito do argumento do argumento “Teste _Lógico” é apresentado a informação = FALSO. Isto significa que o dado informado não atende a condição estipulada. Em nosso exemplo o aluno analisado teve média 7, mas a frequência foi somente 65%. Caso ele fosse aprovado, aparecerá a informação VERDADEIRO. EXCEL AVANÇADO 2010 / 2013 _____________________________________________________________________________________________ Para uso de: Lucas Magalhães Garcia - (31) 98522-1533 - CPF.: 085.158.776-35 - Turma Nº 4316 Reprodução ou distribuição proibida. IN GOD WE TRUST - www.explicare.com.br - contato@explicare.com.br - (31) 4141-2882 54 No argumento “Valor_se _verdadeiro” a informação que digitamos (Aprovado) aparece entre aspas. Quando digitamos a informação não colocamos as aspas. O Excel reconheceu que é um texto e o colocou entre aspas. Caso você digite a fórmula manualmente tem que se lembrar de colocar as aspas. No argumento “Valor_se_falso” ocorre a mesma situação que descrevemos acima. Resultado da fórmula: Neste campo aparece o resultado da fórmula. Neste caso, temos a indicação que o aluno analisado foi Reprovado. Para finalizar, clique em OK ou pressione ENTER. Dica: para alterar algum detalhe na fórmula, selecione a célula onde a fórmula está escrita e altere diretamente na barra de fórmulas ou, clique no fx. Clicando no fx o assistente é aberto com a função já preenchida. Voltamos à situação da última figura apresentada. Vide página anterior. Precisamos agora criar a mesma fórmula para os demais alunos, conforme nosso formulário. Será que precisamos escrever a fórmula novamente? Não, não é necessário escrevê-la novamente. Basta copiá-la para as células ao lado. O resultado fica conforme figura abaixo: FUNÇÃO SE JUNTO COM FUNÇÃO OU Vamos supor a seguinte situação: com base na tabela a seguir, definir se o crédito dos candidatos está “Aprovado” ou “Reprovado”, tomando como base a Renda familiar ou o tempo que o candidato tem conta em banco. Para ter o crédito aprovado, a renda do candidato dever ser igual ou superior a R$ 1.200,00, OU ter conta aberta em banco em período igual ou superior a 12 meses. EXCEL AVANÇADO 2010 / 2013 _____________________________________________________________________________________________ Para uso de: Lucas Magalhães Garcia - (31) 98522-1533 - CPF.: 085.158.776-35 - Turma Nº 4316 Reprodução ou distribuição proibida. IN GOD WE TRUST - www.explicare.com.br - contato@explicare.com.br - (31) 4141-2882 55 Selecione a planilha “SE e OU”. Selecione a célula “D12” e vamos analisar o crédito do João. Utilizando o assistente de funções insira a função SE. No argumento “Teste_lógico” insira a função OU. O procedimento para inserir esta segunda função na fórmula é a mesma que vimos no exemplo anterior. Relembrando. Com o cursor no campo “Teste_lógico” procure a função na Caixa de nome. Vide figura abaixo: Após inserir a função OU sua tela deve ser igual à da figura a seguir. EXCEL AVANÇADO 2010 / 2013 _____________________________________________________________________________________________ Para uso de: Lucas Magalhães Garcia - (31) 98522-1533 - CPF.: 085.158.776-35 - Turma Nº 4316 Reprodução ou distribuição
Compartilhar