Excel Intermediário 1 MBF Agribusiness – Excel Intermediário

Excel Intermediário MBF Agribusiness – Excel Intermediário. 1 Excel Intermediário ÍNDICE 1. Introdução ao Microsoft Excel 2010 ................................................... 3 2. Importando dados .............................................................................. 4 3. Filtro ............................................................................................... 10 3.1. Alto Filtro .......................................................................................... 10 3.2. Filtro Avançado ................................................................................. 10 4. Subtotal ........................................................................................... 14 5. Funções ........................................................................................... 16 5.1 Condicional ........................................................................................ 17 5.2. SOMASE e ARRED ............................................................................ 21 6. Trabalhando com nomes .................................................................. 32 6.1. Formatação condicional ..................................................................... 33 6.2. Limpando a formatação ..................................................................... 35 6.3. Formatação condicional com a fórmula SE .................................... 38 6.4. Validação de dados ............................................................................ 41 6.5. Trabalhando com Gráficos ................................................................. 43 7. Ferramentas Úteis............................................................................ 50 7.1. Proteção de planilhas ......................................................................... 52 7.2. Protegendo células ............................................................................. 54 7.3. Dicas de proteção .............................................................................. 57 MBF Agribusiness – Excel Intermediário. 2 Excel Intermediário 1. Introdução ao Microsoft Excel 2010 Uma planilha é simplesmente um conjunto de linhas e colunas. Cada junção de uma linha com uma coluna chama-se célula, que é a unidade básica da planilha, onde ficam armazenados os dados. Cada célula possui um endereço próprio, formado pela letra da coluna e pelo número da linha. Exemplo: A1 identifica a célula da coluna A com a linha 1. Uma planilha é dita eletrônica por permitir a construção e gravação em meios magnéticos, o que possibilita a recuperação e alteração eficiente, confiável e veloz, além de impressão. As planilhas ficaram para a história como um dos recursos que levaram a microinformática para frente, sendo que as planilhas foram um dos motivos para o sucesso dos microcomputadores no início da década de 1980, tendo como principal representante o Visicalc, depois o Lotus 123, que foi a planilha mais utilizada nos últimos tempos. E com a criação do ambiente gráfico Windows, foi lançado o Excel, o qual dominou o mercado e cada ano aparece com mais inovações. Sem dúvida, o maior diferencial que o Excel 2007 e 2010 possuem em relação às suas versões anteriores é a substituição das Barras de Menus e Ferramentas pelas Guias que foram organizadas em: Início, Inserir, Layout da Página, Fórmulas, Dados, Revisão, Exibição e Desenvolvedor. Comenta-se que a razão desta substituição foi o fato de que muitas ferramentas, opções e atalhos presentes nas versões mais antigas do Excel eram totalmente desconhecidos por seus usuários. Isso acontecia porque, nessas versões, a Barra de Ferramentas era padronizada de acordo com a frequência de uso destes recursos e o espaço disponível para visualização, deixando assim, várias ferramentas, opções e atalhos ocultos aos usuários das versões 97, 2000, XP e 2003 do Excel. Entretanto, com a utilização de Guias, tanto a visualização quanto a utilização dos recursos acima citados tornaram-se extremamente mais fáceis e práticos para os usuários do Excel 2007 e 2010. MBF Agribusiness – Excel Intermediário. 3 Excel Intermediário 2. Importando dados Você pode importar dados para o Excel da maioria das fontes de dados ao apontar para Importar dados externos no menu Dados, clicar em Importar dados e escolher os dados que deseja importar na caixa de diálogo Selecionar fonte de dados. O Assistente para conexão de dados, disponível quando você clica em Nova fonte de dados na caixa de diálogo Selecionar fonte de dados, permite a importação de dados de conexões de dados. Primeiramente abra o Microsoft Excel 2010 e clique na guia dados: Existem várias maneiras de importar informação e manter vínculos com base origem. Será demonstrado a seguir importação de arquivos de extensão txt. Geralmente um arquivo de banco de dados emitidos por sistemas apresentam necessidade de alguns ajustes antes da importação, por isso é necessário que os dados sejam tratados antes da importação. Antes de importar, vamos verificar como está formatado o arquivo que será importado. Portanto: Vá até o documento txt. > clique em abrir: Este é um modelo de documento txt. (texto) que será tratado. MBF Agribusiness – Excel Intermediário. 4 Excel Intermediário 1 2 Os dados da tabela 1 estão ordenados e apresentam informações de fácil separação. Portanto, não depende de tratamento, porém os dados da tabela 2 estão desordenados e precisam de tratamento. O Excel entende que alguns tipos de caracteres especiais como: (/, “, *, %, $, ç, ;, etc..) como sendo a demarcação fixa delimitando os campos que desejam estas ações separam o banco de dados em coluna. Vamos usar agora o método de ponto e vírgula. Mais adiante usaremos outros métodos. Deixe o arquivo da seguinte forma, separando os dados com ponto e vírgula. MBF Agribusiness – Excel Intermediário. 5 Excel Intermediário Desta maneira, quando importar este arquivo para o Excel será possível visualizar as colunas que demarcamos com o sinal “ponto e virgula”. Para importar as informações siga sequência: 1. Vá para o Microsoft Excel na guia Dados; 2. Clique na opção De texto; 3. Aparecerá uma janela: procure o arquivo que deseja importar; 4. Selecione o arquivo; 5. Clique em importar. 1 2 3 4 5 A tela “Assistente de importação de texto” vai aparecer: MBF Agribusiness – Excel Intermediário. 6 Excel Intermediário A opção Largura fixa diz respeito às informações das colunas. Você irá determinar quais campos devem ser separados por colunas, através de uma linha onde (um clique insere a demarcação, dois cliques retira a demarcação). A opção Delimitada diz respeito a caracteres pré-estabelecidos e tabulações (separação de dados por espaço tabulado). No campo Origem do arquivo deve estar selecionado Windows (ANSI) como demonstrado no exemplo. Para separar os campos com caracteres especiais usaremos a opção Delimitada. Portanto, clique na opção > depois em avançar. A tela Assistente de importação de texto – etapa 2 de 3 vai aparecer e significa que falta pouco para terminar a importação. Selecione a opção Ponto e Vírgula e depois clique em avançar. A etapa 3 serve para definir em qual formato será o texto importado. Portanto, selecione Geral e depois Concluir. MBF Agribusiness – Excel Intermediário. 7 Excel Intermediário Se a qualquer momento desejar voltar alguma etapa para modificações, clique em <Voltar. Se quiser parar a importação, clique em Cancelar ou Fechar. A janela Importar dados mostra onde deseja que os dados importados sejam inseridos. Selecione a opção Na planilha existente > a célula A1 e então OK. Os dados já foram importados. Perceba conforme demostra a tabela XXX a opção 1 Obter Dados Externos fica desativada. Isto quer dizer que a planilha mantém um vínculo com os dados da tabela origem, ou seja, qualquer alteração que seja feita no texto ou arquivo de origem, ao ser MBF Agribusiness – Excel Intermediário. 8 Excel Intermediário atualizada será transferida automaticamente para o Excel através do botão Atualizar tudo demonstrado pelo número 2. 1. 2 Após a realização da importação, formate a planilha para que fique mais apresentável. MBF Agribusiness – Excel Intermediário. 9 Excel Intermediário 3. Filtro 3.1. Alto Filtro O Alto Filtro serve para filtrar de forma mais fácil informações de um banco de dados classificando-os em grupos comuns entres os dados. Portanto, para inserir um Alto Filtro vá na aba Dados > selecione o intervalo que deseja e depois clique em Alto filtro. Automaticamente aparecerão umas setas. Se clicar nelas, vão filtrar os dados que se deseja. Isto facilitar a filtragem de informações para análise de dados, montagem de relatórios e outras atividades que possuam. 3.2. Filtro Avançado A ferramenta de filtragem é um recurso avançado do Excel que permite filtrar uma lista de informações com mais de um critério. Filtrar é exibir dados que atendem uma certa condição e ocultar os demais. É excelente utilizar este recurso para selecionar dados numa lista grande. A grande diferença em usar o Auto Filtro e o Filtro Avançado, está em que no Auto Filtro você tem uma limitação aos critérios de filtro escolhido e no Filtro Avançado não possui limitação, ou seja, utilizando o Filtro Avançado você terá como definir quaisquer e quantos critérios desejar. MBF Agribusiness – Excel Intermediário. 10 Excel Intermediário Portanto para inserir o filtro avançado siga as etapas: a) Copie e cole os cabeçalhos da tabela original em outra célula que desejar, como exemplo foi colado na Célula J1. b) Coloque uma condição para que a busca seja feita: no caso colocou-se em % horas Apontadas a condição “< 1” Objeto Prestador Objeto Cliente Horas Apontadas % Horas Apontadas Valor % Valor Apontado <1 c) Depois clique na guia Dados e depois na ferramenta Avançado. d) Na janela Filtro avançado automaticamente o banco de dados será selecionado, se não, clique nas setas para ir até a tabela. MBF Agribusiness – Excel Intermediário. 11 Excel Intermediário a) Selecione o intervalo do banco de dados que deseja filtrar: b) Volte para a janela Filtro avançado clicando novamente na seta. c) No item Intervalo de Critérios selecione a tabela onde foi inserido os critérios. MBF Agribusiness – Excel Intermediário. 12 Excel Intermediário d) Volte para a janela Filtro Avançado. Se desejar que os critérios que mencionou sejam filtrados no próprio Banco de Dados ta tabela original, basta apenas clicar em OK, porém para manter as informações intactas do banco de dados vamos copiar para outro local. Portanto: selecione a opção Copiar para outro local, vai desbloquear o campo Copiar para: , então clique na seta para ir até a planilha. e) Selecione a célula J5, ou onde deseja que as informações filtradas sejam inseridas. MBF Agribusiness – Excel Intermediário. 13 Excel Intermediário As informações, conforme os critérios, serão transferidas para a célula informada. Caso queira Filtrar outras informações basta somente mudar o critério > Ir novamente à ferramenta Avançado, os campos informados anteriormente já estarão salvos, basta clicar em OK. 4. Subtotal A ferramenta Subtotal serve para que alguns campos em comum sejam agrupados a usando a função determinada automaticamente, pode ser por exemplo que totalizem as células com nomes em comuns. Para gera o Sub total siga os passos: 1. Primeiramente é necessário selecionar o banco de dados com os cabeçalhos se possível e depois clicar no botão Subtotal na guia Dados. MBF Agribusiness – Excel Intermediário. 14 Excel Intermediário A janela Subtotais vai aparecer: A cada alteração em: diz respeito aos grupos que vão se formar para gerar os subtotais, no caso selecione a opção Objeto ou o nome que deseja agrupar. Usar Função: Você poderá escolher as funções que gostaria que o relatório sintetizasse (Contagem, Média, Soma, entre outros). No caso selecione Soma. Adicionar subtotal a: direciona a fórmula automaticamente em quais colunas, para se formar os Subtotais. 2. No nosso exemplo selecione os itens Horas Apontadas, % Horas Apontadas, Valor, %Valor Apontado > Clique em OK. Os Subtotais vão se formar. Observe que o total se formou no nome Objeto que foi selecionado na opção A cada alteração em: e somou conforme determinado no parâmetro Usar função a colunas Horas Apontadas, % Horas Apontadas, Valor, %Valor Apontado, configurado no campo Adicionar subtotal a: MBF Agribusiness – Excel Intermediário. 15 Excel Intermediário Objeto Cliente Objeto Prestador 119 Colhedeiras de Cana 95 Oficina Mecânica 118 Implementos Agrícola 95 Oficina Mecânica 95 Total 106 Caminhões Pesado 96 Oficina Mecânica 116 Tratores de Pneus 96 Oficina Mecânica 96 Total 109 Reboque 97 Oficina Mecânica 117 Máquinas Pesadas 97 Oficina Mecânica 97 Total 98 Comboio 98 Oficina Mecânica 888 Caminhão Bombeiro 98 Oficina Mecânica 98 Total 108 Transbordo 99 Oficina Mecânica 99 Total 99 Caminhão Oficina 100 Oficina Mecânica 105 Caminhões Médios 100 Oficina Mecânica 100 Total 114 Tratores de Pneus 101 Oficina Mecânica 815 Caminhão de Apoio 101 Oficina Mecânica 101 Total 110 Equipamentos 102 Oficina Mecânica 89 Irrigação e Fertirrigação 102 Oficina Mecânica 102 Total Horas Apontadas % Horas Apontadas 26,23 201:47:00 18,89 145:20:00 45,12 347:07:00 12,94 99:34:00 12,59 96:52:00 25,53 196:26:00 7,67 58:58:00 4,23 32:31:00 11,9 91:29:00 3,71 28:29:00 2,88 22:09 6,59 02:38 2,39 18:22 2,39 18:22 1,8 13:49 1,64 12:37 3,44 02:26 1,57 12:04 0,98 07:33 2,55 19:37 0,84 06:26 0,54 04:07 1,38 10:33 % Valor Apontado Valor 26,23 12.429,84 18,89 8.952,49 45,12 21.382,33 12,94 6.133,30 12,59 5.966,98 25,53 12.100,28 7,67 3.632,33 4,23 2.003,00 11,9 5.635,33 3,71 1.755,56 2,88 1.364,43 6,59 3.119,99 2,39 1.131,39 2,39 1.131,39 1,8 851,09 1,64 777,20 3,44 1.628,29 1,57 743,32 0,98 465,09 2,55 1.208,41 0,84 397,31 0,54 253,58 1,38 650,89 Para melhor visualização dos totais, clique no botão 2 no canto superior esquerdo: 5. Funções As funções são fórmulas usadas para auxiliar na busca de informações, assim como cálculos. Entre as funções apresentadas nesta apostila, destacaremos as mais usadas no nível intermediário: MBF Agribusiness – Excel Intermediário. 16 Excel Intermediário      Condicional: SE, E, OU; Procura: PROCV; Matemática: SOMASE, ARRED; Estatística: CONT.NUM, CONT.SE, CONTAR.VAZIO, CONT.VALORES; Banco de dados: SOMA, CONTAGEM, MÁXIMO, MÍNIMO. 5.1 Condicional Para aprender a fórmula condicional é preciso aprender primeiramente os símbolos que a formam. Toda a fórmula do Excel deve essencialmente começar com o sinal “=” como exemplo: = se ($A$1 = 0 ; ”Verdadeiro” ; ”Falso”) Para a fórmula acima leríamos: igual se a célula A1 for igual a zero então verdadeiro senão Falso. Perceba que o sinal igual continua com o termo “igual”, porém está ali para representar que está iniciando uma fórmula e o ponto e vírgula se lê “então”. Para entender melhor as fórmulas estude a tabela abaixo com os Símbolos que formam as fórmulas. Símbolos Descrição Maior Função Tem a função de iniciar uma fórmula, além de colocar condição de igualdade no meio da fórmula. Tem a função de comparação maior que outro valor. = > < <> >= <= + () / * $ ; “” Igual Menor Tem a função de comparação menor que outro valor. Diferente Tem a função de comparação diferente que outro valor. Maior ou igual Tem a função de comparação maior ou igual que outro valor. Menor ou igual Tem a função de comparação menor ou igual que outro valor. Menos Mais Parêntesis Divisão Tem a função de diminuir um valor de outro. Tem a função de somar um valor de outro. Tem a função de separar as informações e dentro de uma fórmula. Tem a função de dividir um valor de outro. Vezes Tem a função de multiplicar um valor de outro. Trava Tem a função de travar linhas e colunas de uma fórmula Tem a função de separar intervalos de critérios nas fórmulas, se lê “então”. Significa nada, nenhum. Ponto e Vírgula Duas Aspas MBF Agribusiness – Excel Intermediário. 17 Excel Intermediário Condicional com uma condição Agora na planilha que você importou faça o seguinte: Crie uma coluna no final com o nome do cabeçalho condição, na segunda linha desta coluna escreva a seguinte fórmula: =SE (G2>5000;”Caro”;”Barato”) Copie a fórmula para as demais linhas até o final da tabela. Perceba que os valores que estão acima de 5000 vão aparecer com a condição Caro e os outros abaixo ficarão com a condição Barato. Isto serve para facilitar em um banco de dados a busca de informações com critérios de sua escolha, assim como na elaboração de fórmula que representa que os critérios serão outras fórmulas. Organize a planilha para que os valores caros possam ficar agrupados. Existem vários critérios de se fazer a fórmula “Se”. Mais de uma condição: Outro exemplo seria adicionar à fórmula “Se” com mais de uma condição. Para demonstrar faça o seguinte: Na célula onde foi criada a condição: aperte a tecla F2. Isso fará com que a fórmula seja editada. Clique nela e escreva a seguinte fórmula: =SE(G2>5000; "caro" ; SE ( G2>1500 ; "Normal" ; "Barato")) MBF Agribusiness – Excel Intermediário. 18 Excel Intermediário Clique e arraste para as demais células. Os valores na coluna G que estiverem acima de 5.000,00 vão aparecer Caro, os que estiverem acima de 2.000,00 vão aparecer Normal e os demais valores vão aparecer Barato. Desta forma temos mais de uma condição na mesma fórmula. Agora coloque um Alto filtro para facilitar a busca de informações. Selecione somente a opção Barato para visualizar quais os centros de custos que estão baratos. Mais de um critério: Outra maneira de fazer a condição “Se” seria com mais de um critério. Para isto deve-se adicionar na fórmula a “E” ou então “OU”. Para tanto, na célula que foi escrita a fórmula escreva o seguinte: =SE ( E ( A2 = 96 ; G2>5000 ) ; "caro" ; "") Perceba que a fórmula só obedeceu aos dois critérios, ou seja, se não existe nas células que os valores forem iguais ao centro de custo 96 e acima de 5000, então vai voltar o valor Caro senão voltará Nada, representado pelo símbolo “”. MBF Agribusiness – Excel Intermediário. 19 Excel Intermediário Objeto Prestador Objeto Cliente Horas Apontadas % Horas Apontadas Valor % Valor Apontado Mais de um critério 95 Oficina Mecânica 89 Irrigação e Fertirrigação 04:07 0,54 253,58 0,54 95 Oficina Mecânica 98 Comboio 28:29:00 3,71 1755,56 3,71 95 Oficina Mecânica 99 Caminhão Oficina 13:49 1,8 851,09 1,8 95 Oficina Mecânica 102 Automóveis e Utilitários 00:13 0,03 13,35 0,03 95 Oficina Mecânica 103 Motocicleta 01:06 0,14 67,76 0,14 95 Oficina Mecânica 105 Caminhões Médios 12:37 1,64 777,2 1,64 95 Oficina Mecânica 107 Automóveis e Utilitários 01:50 0,24 112,93 0,24 95 Oficina Mecânica 108 Transbordo 18:22 2,39 1131,39 2,39 95 Oficina Mecânica 109 Reboque 58:58:00 7,67 3632,33 7,67 96 Oficina Mecânica 110 Equipamentos 06:26 0,84 397,31 0,84 96 Oficina Mecânica 113 Carregadeiras de Cana 02:25 0,31 148,87 0,31 96 Oficina Mecânica 114 Tratores de Pneus 12:04 1,57 743,32 1,57 96 Oficina Mecânica 115 Tratores de Pneus 02:56 0,38 180,68 0,38 96 Oficina Mecânica 117 Máquinas Pesadas 32:31:00 4,23 2003 4,23 96 Oficina Mecânica 815 Caminhão de Apoio 07:33 0,98 465,09 0,98 96 Oficina Mecânica 888 Caminhão Bombeiro 22:09 2,88 1364,43 2,88 96 Oficina Mecânica 106 Caminhões Pesado 99:34:00 12,94 6133,3 12,94 caro 96 Oficina Mecânica 116 Tratores de Pneus 96:52:00 12,59 5966,98 12,59 caro 96 Oficina Mecânica 118 Implementos Agrícola 145:20:00 18,89 8952,49 18,89 caro 96 Oficina Mecânica 119 Colhedeiras de Cana 201:47:00 26,23 12429,84 26,23 caro No lugar do “E” coloque ou e copie para as células abaixo: =SE ( OU ( A2=96 ; G2>5000 ) ; "caro" ; "") Diferente da fórmula com “E” que considera os dois critérios, o “OU” considera um ou outro que se existir a condição ele retorna o valor que foi sugerido, no caso à palavra Caro. Objeto Prestador Objeto Cliente Horas Apontadas % Horas Apontadas Valor % Valor Apontado Mais de um critério 95 Oficina Mecânica 89 Irrigação e Fertirrigação 04:07 0,54 253,58 0,54 95 Oficina Mecânica 98 Comboio 28:29:00 3,71 1755,56 3,71 95 Oficina Mecânica 99 Caminhão Oficina 13:49 1,8 851,09 1,8 95 Oficina Mecânica 102 Automóveis e Utilitários 00:13 0,03 13,35 0,03 95 Oficina Mecânica 103 Motocicleta 01:06 0,14 67,76 0,14 95 Oficina Mecânica 105 Caminhões Médios 12:37 1,64 777,2 1,64 95 Oficina Mecânica 107 Automóveis e Utilitários 01:50 0,24 112,93 0,24 95 Oficina Mecânica 108 Transbordo 18:22 2,39 1131,39 2,39 95 Oficina Mecânica 109 Reboque 58:58:00 7,67 3632,33 7,67 96 Oficina Mecânica 110 Equipamentos 06:26 0,84 397,31 0,84 caro 96 Oficina Mecânica 113 Carregadeiras de Cana 02:25 0,31 148,87 0,31 caro 96 Oficina Mecânica 114 Tratores de Pneus 12:04 1,57 743,32 1,57 caro 96 Oficina Mecânica 115 Tratores de Pneus 02:56 0,38 180,68 0,38 caro 96 Oficina Mecânica 117 Máquinas Pesadas 32:31:00 4,23 2003 4,23 caro 96 Oficina Mecânica 815 Caminhão de Apoio 07:33 0,98 465,09 0,98 caro 96 Oficina Mecânica 888 Caminhão Bombeiro 22:09 2,88 1364,43 2,88 caro 96 Oficina Mecânica 106 Caminhões Pesado 99:34:00 12,94 6133,3 12,94 caro 96 Oficina Mecânica 116 Tratores de Pneus 96:52:00 12,59 5966,98 12,59 caro 96 Oficina Mecânica 118 Implementos Agrícola 145:20:00 18,89 8952,49 18,89 caro 96 Oficina Mecânica 119 Colhedeiras de Cana 201:47:00 26,23 12429,84 26,23 caro Agora crie um subtotal para os Objetos de custos 95 e 96 totalizando os custos conforme coluna G. Se não lembrar volte à página XX. MBF Agribusiness – Excel Intermediário. 20 Excel Intermediário Várias são as opções de fazer a fórmula com condição. Estes exemplos têm o intuito de entender como elas funcionam. Lembrando que as finalidades e condições são de acordo com o desejo do usuário, e para isso o Excel pode criar critérios inteligentes para que evite erros e mostre situações em que se deseja uma forma diferente de análise de informações. 5.2. SOMASE e ARRED A fórmula SOMASE serve para somar uma sequência de dados referentes a um critério específico: = SOMASE ( intervalo ; critérios; [intervalo_soma]) A sintaxe da função SOMASE tem os seguintes argumentos (argumento: um valor que fornece informações a uma ação, um evento, um método, uma função ou um procedimento): Intervalo: Obrigatório. O intervalo de células que se deseja calcular por critérios. As células em cada intervalo devem ser números e nomes, matrizes ou referências que contêm números. Espaços em branco e valores de texto são ignorados. Critérios: Obrigatório. Os critérios na forma de um número, expressão, referência de célula, texto ou função que define quais células serão MBF Agribusiness – Excel Intermediário. 21 Excel Intermediário adicionadas. Por exemplo, os critérios podem ser expressos como 32, ">32", B5, 32, "32", "maçãs" ou HOJE(). Importante: Qualquer critério de texto ou qualquer critério que inclua símbolos lógicos ou matemáticos deve estar entre aspas duplas ("). Se os critérios forem numéricos, as aspas duplas não serão necessárias. Intervalo soma: Opcional. As células reais a serem adicionadas, se você quiser adicionar células diferentes das especificadas no argumento de intervalo. Se o argumento intervalo soma for omitido, o Excel adicionará as células especificadas no argumento intervalo (as mesmas células às quais os critérios são aplicados). Para exemplificar melhor, vamos fazer um exemplo: Importe a tabela de dados Fórmula SOMASE. Formate para que fique com uma aparência de tabela. Na Célula J1 escreva Objeto e na K1 Soma. Depois na Célula K2 escreva a seguinte fórmula: =SOMASE(A:A;J2;G:G) Conforme o critério que informar na célula J2, ele somará o valor da coluna G. No caso coloque o Objeto 96. Faça simulação com outros Objetos. MBF Agribusiness – Excel Intermediário. 22 Excel Intermediário SOMASES A fórmula SOMASES funciona da mesma forma que a fórmula SOMASE, diferenciando que neste caso utiliza-se mais de um critério para fazer a soma dos valores. ARRED A fórmula ARRED serve para arredondar os valores conforme a quantidade de dígitos depois da vírgula que for informada. Para ilustrar melhor vamos à prática: Na fórmula que foi criada anteriormente do SOMASE adicione o ARRED. Se tiver dúvida, a fórmula ficará assim: =ARRED(SOMASE(A:A;J2;G:G);0) O valor que retornou na célula está arredondado considerando que fique sem dígitos depois da vírgula. É possível ver na barra de fórmula o exemplo. Para adicionar várias fórmulas dentro da outra, siga o exemplo abaixo. Edite a Fórmula da célula K2 apertando F2 depois do sinal “=” e coloque a seguinte condição: se a célula J2 for igual à nada então “informar valor” senão Somase à coluna valor da tabela com o critério da J2. Se não conseguir, a fórmula ficará da seguinte maneira: =SE(J2="";"Informar Valor";ARRED(SOMASE(A:A;J2;G:G);0)) Perceba que se a célula J2 estiver vazia voltará na Célula K2 a mensagem “Informar Valor” MBF Agribusiness – Excel Intermediário. 23 Excel Intermediário Se estiver com valor voltará a soma da coluna Valor. Coloque na célula J2 o valor 99 e veja que irá procurar na coluna A e somar os valores da coluna G. O valor vai ficar arredondado, pois informamos antes do SOMASE o ARRED. Função PROCV. A fórmula PROCV é semelhante à fórmula SOMASE. Ela procura um critério específico em um banco de dados e retorna o valor conforme o número da coluna informada na fórmula. =PROCV(valor_procurado,matriz_tabela,núm_índice_coluna,[procurar_intervalo]) Valor procurado: Obrigatório. É o valor a ser procurado na primeira coluna da tabela ou intervalo. O argumento valor procurado pode ser um valor ou uma referência. Se o valor que você fornecer para o argumento valor procurado for inferior ao menor valor da primeira coluna do argumento matriz tabela, PROCV retornará o valor de erro #N/D. Matriz tabela: Obrigatório. É o intervalo de células que contém os dados. Você pode usar uma referência a um intervalo (por exemplo, A2:D8) ou um nome de intervalo. Os valores na primeira coluna de matriz tabela são os valores procurados por valor procurado. Os valores podem ser texto, números ou valores lógicos. Textos em maiúsculas e minúsculas são equivalentes. MBF Agribusiness – Excel Intermediário. 24 Excel Intermediário Núm índice coluna: Obrigatório. É o número da coluna no argumento matriz tabela do qual o valor correspondente deve ser retornado. Um argumento núm índice coluna de 1 retornará o valor na primeira coluna em matriz tabela; um argumento núm índice coluna de 2 retornará o valor na segunda coluna em matriz tabela e assim por diante. Se o argumento núm índice coluna for:   Menor que 1, PROCV retornará o valor de erro #VALOR!. Maior do que o número de colunas em matriz tabela, PROCV retornará o valor de erro #REF!. Procurar intervalo: Opcional. É um valor lógico que especifica se você quer que PROCV localize uma correspondência exata ou aproximada.  Se procurar intervalo for VERDADEIRO, ou for omitido, uma correspondência exata ou aproximada será retornada. Se uma correspondência exata não for localizada, o valor maior mais próximo, que seja menor que o valor procurado, será retornado. IMPORTANTE: Se procurar intervalo for VERDADEIRO, ou for omitido, os valores na primeira coluna de matriz tabela deverão ser colocados em ordem ascendente; caso contrário, PROCV poderá não retornar o valor correto. Se procurar intervalo for FALSO, os valores na primeira coluna de matriz tabela não precisarão ser ordenados.  Se o argumento procurar intervalo for FALSO, PROCV encontrará somente uma correspondência exata. Se houver dois ou mais valores na primeira coluna da matriz tabela, que não coincidem com o valor procurado, o primeiro valor encontrado será utilizado. Se nenhuma correspondência exata for localizada, o valor de erro #N/D será retornado. Para ilustrar, vamos a pratica: coloque na célula K1 o nome Prestador. Ficará da seguinte forma: Objeto Prestador Agora, na célula, escreva a seguinte fórmula: MBF Agribusiness – Excel Intermediário. 25 Excel Intermediário =PROCV(J2;A:H;2) Aperte Enter para confirmar a fórmula e perceba que o valor retornou em erro. Objeto Prestador #N/D Isso acontece porque o valor informado na Célula J2 não existe. Informe o Objeto 96 e veja o que acontece. Falaremos sobre como arrumar o erro mais para frente. Na tabela procurou-se o valor correspondente a célula J2 no banco de dados A:H, que retornou o Valor da coluna 2, conforme a fórmula solicitou. Para melhorar a prática, faça a seguinte alteração nas células L1 e M1: Agora escreva na célula M2 a fórmula com os seguintes critérios: Conforme o valor informado na célula L2 o sistema deve procurar na tabela o nome correspondente ao Valor da célula, ou seja, quando inserir o valor 119, buscará Colhedoras de Cana e assim por diante. Então vamos à fórmula passo a passo. 1. Escreva =PROCV( 2. O valor procurado é o da célula L2. Clique sobre a célula, insira o ponto e vírgula para pular à próxima etapa. MBF Agribusiness – Excel Intermediário. 26 Excel Intermediário 3. Agora selecione, no banco de dados, as colunas C a D. Esta foi a matriz tabela, agora só falta o número de índice coluna. Se selecionamos apenas duas colunas, então o valor à retornar é 2. Coloque-o na fórmula, que deve ficar assim: =PROCV(L2;C:D;2) Pronto! Agora informe o valor 119 na célula L2. O valor retornado não condiz com o Objeto informado, isso porque deve-se informar se a correspondência deve ser exata ou aproximada, então, no final da fórmula adicione Falso. Ficará da seguinte forma: =PROCV(L2;C:D;2;FALSO) MBF Agribusiness – Excel Intermediário. 27 Excel Intermediário Quanto ao erro, para corrigi-lo pode ser feita uma condicional dizendo que, se na célula L2 não estiver informado nada (“”), então não voltará nada (“”) senão o PROCV. =SE(L2="";"";PROCV(L2;C:D;2)) Outra forma de corrigir o erro, um pouco mais difícil, é colocar na Fórmula SE a sintaxe ÉERROS, que ficará da seguinte forma: =SE(ÉERROS(PROCV(L2;C:D;2;FALSO));"";PROCV(L2;C:D;2;FALSO)) A fórmula acima diz que, se o PROCV der erro, então não voltará nada (“”), caso contrário, fará o PROCV. O PROCV é uma ferramenta excepcional para buscar qualquer informação de um banco de dados. Ela serve tanto para nomes como para valores, e seus recursos dependem da imaginação do usuário da planilha. Usando o PROCV e o SOMASE, pode-se, somente usando códigos, buscar as informações para analisar e montar relatório, porém, estas duas fórmulas são uma das mais complexas do Excel. Portanto, dependendo do número de informações que se deseja buscar, ela pode demorar um pouco para calcular a planilha e até mesmo travar. CONT.NÚM Calcula a quantidade de células de um intervalo que contém números, ou seja, em um intervalo de células ela só vai considerar as que tenham números, se houver texto, a fórmula não vai considera-la. Para ver sua aplicação na prática, conte os objetos da planilha a seguir: MBF Agribusiness – Excel Intermediário. 28 Excel Intermediário Selecione o intervalo de dados, no caso os objetos, e aperte Enter para confirmar. Perceba que a fórmula fez a contagem das células onde havia números. MBF Agribusiness – Excel Intermediário. 29 Excel Intermediário CONT.VALORES Semelhante a CONT.NÚM, esta fórmula conta valores considerando textos ou números, ou seja, conta o intervalo de células que não estão vazias. Para ilustrar, copie a fórmula CONT.NÚM para o lado na coluna B. Veja que ela volta o número 0. Então substitua a Fórmula por CONT.VALORES. CONTAR.VAZIO Esta fórmula conta o número de células vazias em um intervalo. Na prática, esta fórmula pode servir para conferir se algum valor foi excluído no banco de dados. Apague o valor da penúltima linha do banco de dados da coluna B, e então confira com CONTAR.VAZIO. Insira ao final da coluna B CONTAR.VAZIO e selecione o intervalo. MBF Agribusiness – Excel Intermediário. 30 Excel Intermediário CONT.SE Nesta fórmula podemos inserir critérios para que a contagem seja feita. Vamos contar os valores de % Horas Apontadas que estão abaixo de 1. Para isso siga os passos: 1. Escreva abaixo dos dados da coluna F =CONT.SE(, vai aparecer um quadro dizendo para informar o intervalo, selecione na coluna F os dados à contar. 2. Agora escreva o seguinte critério, que deve estar entre aspas, “<1” e aperte Enter para confirmar. MBF Agribusiness – Excel Intermediário. 31 Excel Intermediário 6. Trabalhando com nomes Para facilitar a elaboração de fórmulas e buscar as informações de um banco de dados, os NOMES podem auxiliar, uma vez que inseridos na planilha. Para inserir um nome basta selecionar um intervalo de dados e colocar um nome. Vamos à pratica: Na planilha, selecione os valores da célula G. Na aba fórmulas selecione a opção Definir Nome: Vai aparecer a janela Novo Nome. Perceba que o nome VALOR já foi completado e o intervalo é o que foi selecionado. MBF Agribusiness – Excel Intermediário. 32 Excel Intermediário Aperte OK para confirmar. Nada aconteceu, mas um nome foi agendado dentro da planilha. Para visualizá-lo e fazer mudanças, selecione Gerenciar Nomes. Feche a tela Gerenciador de Nome e faça uma fórmula somando os valores da coluna G, com o NOME que foi criado. No final dos valores da coluna G escreva a fórmula =SOMA(VALOR) e aperte Enter para confirmar. Veja que os valores da coluna G foram somados. Os NOMES facilitam à utilização de fórmulas, assim como a empregabilidade de dados em outras ferramentas como Validação e Formatação condicional. 6.1. Formatação condicional No Excel existem diferentes ferramentas que permitem formatar células, MBF Agribusiness – Excel Intermediário. 33 Excel Intermediário alterando, por exemplo, cor da fonte, bordas, preenchimento e etc. A partir de agora, você conhecerá formas de otimizar o formato das células automaticamente, com base em uma condição preestabelecida. Esse recurso permite alterar cor, fonte ou sombreamento da célula. Para exemplificar algumas formatações, utilizaremos a tabela a seguir: 2. Na guia Início, no grupo Estilos, clique na seta ao lado do botão Formatação Condicional para visualizar as opções de formatação condicional. Primeiro, vamos visualizar as duas formas mais simples. MBF Agribusiness – Excel Intermediário. 34 Excel Intermediário 3. Selecione a opção Barras de Dados e clique em uma das cores visualizadas. Observe o resultado nos dados. 4. Agora, selecione a opção Escalas de Cor e clique em uma das escalas Visualizadas. 5. Salve a pasta de trabalho. 6.2. Limpando a formatação Caso a formatação aplicada não fique a contento, você pode limpá-la facilmente. Para isso, faça o seguinte: 1. Selecione as células desejadas. 2. Na guia Início, no grupo Estilos, clique na seta ao lado do botão Formatação Condicional e na opção Limpar Regras. MBF Agribusiness – Excel Intermediário. 35 Excel Intermediário Critérios para formatação Na aba Página Inicial selecione a ferramenta Formatação Condicional e, então, Gerenciar Regras. Irá aparecer a janela gerenciadora de regra de Formatação Condicional. Esta janela serve para adicionar regras para a formatação, portanto, podem ser adicionadas quantas regras você quiser, porém, elas vão respeitar a primeira regra estabelecida, que ficará de cima para baixo. Antes de fazer uma formatação condicional, deve-se seleciona as células a serem formatadas. Selecione a tabela inteira. MBF Agribusiness – Excel Intermediário. 36 Excel Intermediário Clique em formatação condicional ou aperte separadamente o atalho ALT + F + D. Aperte o botão Nova Regra na janela Nova Regra de formatação; selecione a Opção Formatar apenas células que contenham, e Formate as células com: valor da célula é igual a 98. Clique no botão formatar; coloque o preenchimento vermelho e a fonte amarela; aperte OK para confirmar a formatação. Aperte novamente OK para inserir a regra na formatação condicional. MBF Agribusiness – Excel Intermediário. 37 Excel Intermediário Com isso, onde estiver o valor 98 na tabela selecionada será destacado com fundo vermelho e a letra amarela, então aperte OK para confirmar. Qualquer alteração que seja feita na formatação das células pela aba Página Inicial, tal como alteração da fonte, bordas, fundo, a planilha vai verificar primeiramente a feita pela formatação condicional. 6.3. Formatação condicional com a fórmula SE Esta ferramenta pode ser utilizada juntamente com as fórmulas do Excel. Pode colocar o critério que deseja para que a planilha visualize facilmente os valores e critérios que informar na formatação Condicional. Apague a Formatação Condicional feita anteriormente para praticar, aperte ALT+F+D, clique no botão Excluir Regra e OK para confirmar. MBF Agribusiness – Excel Intermediário. 38 Excel Intermediário Selecione a primeira linha do Banco de Dados e clique na ferramenta Formatação Condicional. Clique no botão Nova Regra e selecione a opção Usar uma Fórmula para determinar quais células devem ser formatadas; clique no botão para ir até a planilha. Escreva a seguinte fórmula. Formate o texto com preenchimento amarelo, fonte vermelha e negrito para que possa visualizar facilmente os valores que estão acima de 4.000,00. Adicionado a regra, clique em OK, e veja se a célula foi formatada conforme as informações imputadas na formatação condicional. MBF Agribusiness – Excel Intermediário. 39 Excel Intermediário Para adicionar as demais linhas aperte CTRL+C, selecione as linhas abaixo da linha 2, aperte com o botão direito do mouse, selecione a opção Colar Especial e cole Formatos. Aperte OK para confirmar e note que em todos os valores acima de 4.000,00 - conforme a condição informada - a linha inteira da tabela foi pintada automaticamente. Inúmeras as maneiras de se fazer uma formatação condicional, lembrando novamente que os pensamentos de cada um é o limite de utilização dos recursos da planilha eletrônica. MBF Agribusiness – Excel Intermediário. 40 Excel Intermediário 6.4. Validação de dados Este recurso é utilizado para que em uma célula não possa ser informado um valor diferente do critério proposto, exemplo: na tabela onde criamos o PROCV, na célula onde se informa o Código do Objeto, quero travá-la para informar somente os Objetos que estão na tabela de dados. Para isso, siga os passos: 1. Crie a Fórmula PROCV na célula K2 e formate a planilha da seguinte forma: 2. Com a célula J2 selecionada, clique na guia Dados e selecione a ferramenta Validação de Dados. 3. A janela Validação de Dados vai aparecer. Na aba Configurações, Permitir: Qualquer valor está selecionado, pois a célula aceita qualquer valor, então selecione a opção Lista. MBF Agribusiness – Excel Intermediário. 41 Excel Intermediário 4. Aparece uma opção Fonte: > Clique nela para ir até a planilha e selecione o intervalo de dados da coluna C. 5. Volte para a planilha e, na aba Alerta de Erro, insira o título Mensagem de erro e aperte OK para confirmar. Perceba que apareceu uma seta indicando que há uma lista, coloque o número 1003 na célula J2 para testar a validação de dados. Aperte cancelar e procure o numero 103 na lista, ou digite o número na célula J2. MBF Agribusiness – Excel Intermediário. 42 Excel Intermediário 6.5. Trabalhando com Gráficos Os gráficos são usados para mostrar informação variada de dados numéricos em formato gráfico, tornando os dados, e a relação entre eles, mais perceptível. Sobre os gráficos e os seus elementos Um gráfico tem muitos elementos, alguns deles são exibidos por predefinição. Os elementos do gráfico podem ser adicionados, removidos, movidos, redimensionados ou alterados. MBF Agribusiness – Excel Intermediário. 43 Excel Intermediário 1. Área do gráfico 2. Área de desenho 3. Valores das séries de dados 4. Eixos horizontal (categoria) e vertical (valor) 5. Legenda 6. Título do gráfico e do eixo 7. Rótulo de dados usado para identificar detalhes dos valores nas séries de dados. Ao clicar em qualquer parte da área do gráfico, e passar o mouse sobre um desses elementos, o Excel exibe informações sobre esse elemento, numa dica de ecrã (tela). Se clicar em qualquer parte do gráfico verá que as Ferramentas de Gráfico ficam disponíveis, acrescentando ao friso os separadores Estrutura, Esquema e Formatar. Tipos de gráficos disponíveis O Excel 2010 suporta vários tipos de gráficos que ajudam a exibir os dados de forma significativa para as pessoas. Abaixo há alguns tipos de gráficos disponíveis e utilizados com maior frequência: MBF Agribusiness – Excel Intermediário. 44 Excel Intermediário Gráficos Sparkline Pode-se utilizar gráficos Sparkline (pequenos gráficos que cabem numa célula) para resumir visualmente tendências junto dos dados. Os dados que os gráficos Sparkline mostram são especialmente úteis para pequenas reuniões ou outros contextos em que seja necessário mostrar as tendências dos valores, num formato visual de fácil compreensão. Por exemplo, a seguinte imagem mostra como um gráfico Sparkline lhe permite ver rapidamente o desempenho dos seus alunos, nas avaliações, ao longo do ano letivo. MBF Agribusiness – Excel Intermediário. 45 Excel Intermediário Insira e Formate um gráfico Sparkline 1. No separador Inserir, no grupo Gráficos Sparkline, selecione o tipo de gráfico que pretende inserir: Linhas, Colunas ou Perda/Ganho. 2. Selecione os dados que pretende representar no gráfico, escrevendo o intervalo de dados ou selecionando os mesmos com o mouse. Selecione também a célula onde pretende integrar o gráfico. Clique em OK. MBF Agribusiness – Excel Intermediário. 46 Excel Intermediário 3. Para formatar o gráfico, clique sobre ele e aparecerá no friso o separador de formatação do gráfico - Estrutura. Altere os aspectos que pretende. Por exemplo, marque o valor mais alto e o mais baixo do gráfico, na Cor de Marcador e/ou aumente a espessura da linha em Cor de Gráfico Sparkline. Criar um gráfico com dois eixos no Excel. Eixo X e Y no Excel Passo 1: Copie a tabela 1 em uma planilha do Excel. Estamos utilizando o Excel 2010, porém, poderão também ser utilizadas as versões 2003 ou 2007. Passo 2: Selecione a tabela, clique na aba Inserir, selecione o gráfico de colunas 2D. MBF Agribusiness – Excel Intermediário. 47 Excel Intermediário Criação do gráfico Passo 3: O Excel criará um gráfico com os dados das colunas selecionadas, porém, como pode-se notar, a coluna Inadimplência está abaixo do mínimo e nem sequer aparece no gráfico. Para criar o novo eixo Y, em primeiro lugar teremos que fazer com que a coluna inadimplência apareça. Para tanto, copie o valor da célula C2 para uma célula vazia, em seguida altere o seu valor para 30.000 conforme a imagem. Não esqueça de mudar o tipo de percentual para valor para que possa aparecer o gráfico. MBF Agribusiness – Excel Intermediário. 48 Excel Intermediário Passo 4: Clique com o botão direito sobre a barra da inadimplência (vermelha) e, em seguida, na opção Formatar Série de Dados; depois, selecione a opção Plotar Série no e altere para Eixo Secundário. Eixo Secundário Passo 5: Após fechar a tela de Formatar Série de Dados, clique na coluna vermelha com o botão direito do mouse e selecione a opção Alterar Tipo de Gráfico; mude para um gráfico de sua preferência. No nosso caso foi selecionado um gráfico de linha com marcadores. MBF Agribusiness – Excel Intermediário. 49 Excel Intermediário Passo 6: Retorne o valor da célula C2 que havia sido reservado e você terá o gráfico com dois eixos Y. 7. Ferramentas Úteis O Excel possui muitas ferramentas que podem ajudar na execução das atividades nesta apostila, vamos citar algumas. Cabeçalhos Através do menu Layout da Página, > selecione a opção Imprimir Títulos. MBF Agribusiness – Excel Intermediário. 50 Excel Intermediário A opção Imprimir Títulos pode ser usada para definir uma área de impressão padrão para todas as páginas, exemplo: deseja-se que todas as páginas saiam com o cabeçalho da planilha como Cód, Data Admissão, Função. Selecione na janela Configurar Página a opção Linhas a repetir na parte superior. Selecione a linha inteira do Cabeçalho, volte para a janela e clique em visualizar impressão. MBF Agribusiness – Excel Intermediário. 51 Excel Intermediário Perceba que a imagem foi mantida, porém, agora todas as páginas estão com o cabeçalho definido conforme a primeira linha da planilha. 7.1. Proteção de planilhas Uma maneira bem simples de proteger suas planilhas é fazer uso de senha para bloquear o conteúdo contra alterações indevidas. Esta funcionalidade está localizada na guia Revisão do Excel 2010, mais precisamente no grupo de ferramentas Alterações. A vantagem de inserir senha pelo grupo de ferramentas mencionado é que é permitido configurar vários itens de permissões, veja no tutorial abaixo: Para proteger uma planilha específica de uma pasta de trabalho no Excel realize os procedimentos abaixo: Em Alterações clique em Proteger Planilha: MBF Agribusiness – Excel Intermediário. 52 Excel Intermediário Digite uma senha e selecione os itens que terão permissão para serem alterados ao abrir o arquivo: Reinsira a senha e clique em Ok para finalizar: Assim a planilha estará protegida contra alterações. Para testar, experimente alterar algo na planilha e você verá um mensagem de alerta como na imagem abaixo: MBF Agribusiness – Excel Intermediário. 53 Excel Intermediário Obs: Isto significa que a planilha está devidamente protegida. Para permitir todas as alterações possíveis, basta realizar o procedimento inverso, desproteger a planilha. Quando a planilha estiver protegida com senha, automaticamente a funcionalidade Desproteger Planilha aparecerá no lugar de Proteger Planilha, veja a imagem: Ao clicar em Desproteger Planilha você deverá reinserir a senha configurada anteriormente. Automaticamente todas as alterações configuradas estarão liberadas. Lembre-se que, ao utilizar este recurso, você deverá guardar a senha em um local seguro, se o arquivo for aberto sem digitar a senha, automaticamente o mesmo assumirá o status Somente Leitura, bloqueando totalmente os itens configurados. 7.2. Protegendo células Protegendo a pasta de trabalho O Excel 2010 tem um excelente recurso para proteger sua planilha com senha. Na verdade você poderá impedir a execução de abertura do arquivo e também a alteração do seu conteúdo. Isso quer dizer que você pode utilizar as duas proteções ou somente uma. Se achar necessário que as informações contidas no seu arquivo sejam sigilosas a ponto de ninguém poder visualizar, você deverá colocar obrigatoriamente uma Senha de Proteção, que não permitirá que a sua planilha seja aberta por terceiros. Mas se você achar necessário que as pessoas devam somente visualizar as informações de sua planilha sem poder alterá-las será útil aplicar uma Senha MBF Agribusiness – Excel Intermediário. 54 Excel Intermediário de Gravação, com o objetivo de somente dispor o conteúdo como Somente Leitura. Para impedir que o arquivo possa ser aberto e alterado por terceiros, você deverá realizar o seguinte procedimento: Clique no botão Salvar. Na janela Salvar Como defina o nome do arquivo, expanda o menu Ferramentas e clique em Opções Gerais. Lembre-se que você tem dois tipos de segurança: Senha de Proteção (senha para abrir o arquivo) e Senha de Gravação (Senha para permissão de alteração do conteúdo da planilha). Essas senhas são independentes. Você poderá utilizar qualquer uma dependendo do que achar necessário proteger ou utilizar as duas ao mesmo tempo. MBF Agribusiness – Excel Intermediário. 55 Excel Intermediário Para cada uma delas você irá repetir as senhas. A janela Confirmar senha aparecerá para que você repita a Senha de Proteção. Igualmente para a Senha de Gravação. Após inserir as senhas e repeti-las o seu arquivo no Excel estará protegido contra execução e alteração. Perceba que ao executar o arquivo salvo o Excel 2010 solicitará as senhas cadastradas. Cada senha será solicitada separadamente, se quiser somente abrir o arquivo para leitura você deverá inserir somente a Senha de Proteção. MBF Agribusiness – Excel Intermediário. 56 Excel Intermediário Caso necessite alterar a planilha digite também a Senha de Gravação. 7.3. Dicas de proteção Proteger uma pasta de trabalho O Excel permite que você proteja uma pasta de trabalho ou uma planilha.    Vá em "Ferramentas", "Proteger" e "Proteger a pasta de trabalho" Clique na opção desejada Clique em OK Proteger a estrutura de uma pasta de trabalho    Vá em "Ferramentas", "Proteger" e "Proteger a pasta de trabalho" Clique na opção "Estrutura" Clique em OK MBF Agribusiness – Excel Intermediário. 57 Excel Intermediário Cuidado. Se você entrar com uma senha, não esqueça de anotá-la em algum lugar, ou de memorizá-la. Se você perder ou esquecer a sua senha, você não poderá mais alterar a estrutura. Protegendo a estrutura, por exemplo, é impossível mover as planilhas da pasta de trabalho. Em compensação, é possível alterar os dados das tabelas. Desproteger uma pasta de trabalho  Vá em "Ferramentas", "Proteger" e "Desproteger a pasta de trabalho". Se você entrou com uma senha, ao proteger a sua pasta de trabalho, o Excel vai pedir que você a digite de novo para desprotegê-la. Proteger a janela de uma pasta de trabalho    Vá em "Ferramentas", "Proteger" e "Proteger a pasta de trabalho" Clique na opção "Janelas" Clique em OK Ao proteger uma janela, você não poderá redimensioná-la ou movê-la. Proteger uma Planilha Esta opção permite que você proteja as células de uma planilha. A partir daí, qualquer alteração será impossível. A proteção das células de uma planilha é feita em duas etapas:   Definindo as células a serem bloqueadas e as células a serem desbloqueadas Ativando a proteção da planilha Bloquear e desbloquear as células Por padrão, todas as células de uma planilha são bloqueadas. Você pode alterá-las porque a proteção da planilha ainda não foi ativada. Desbloqueie as células que devem continuar modificáveis.     Selecione as células ou intervalos de células a serem desbloqueadas Vá em "Formatar", "Células" e na aba "Proteção" Desativar a casa a ser assinalada "Bloqueadas" Clique em OK MBF Agribusiness – Excel Intermediário. 58 Excel Intermediário Ativar a proteção da planilha  Vá em "Ferramentas", "Proteger" e "Proteger a planilha"  Clique na opção desejada  Clique em OK O Excel só aceita as alterações para as células desbloqueadas. A tecla Tabulação move a célula ativa para as células desbloqueadas. Desbloquear a planilha  Vá em "Ferramentas", "Proteger" e "Desproteger a planilha". Agora, todas as células da planilha podem ser alteradas. Ocultar o conteúdo das células Por padrão, todas as células de uma planilha não são ocultas. Diz-se que uma célula está oculta quando seu conteúdo não aparece na barra de fórmula. Esta opção permite ocultar as fórmulas de cálculo das suas tabelas.     Selecione as células ou intervalos de células a serem ocultos Vá em "Formatar", "Células" e na aba "Proteção" Ativar a casa a ser assinalada Ocultas Clique em OK Ativar a proteção da planilha    Vá em "Ferramentas", "Proteger" e "Proteger a planilha" Clique na opção desejada Clique em OK Proteger uma pasta de trabalho com uma senha Existem dois tipos de senha no Excel: uma protege o documento inteiro e a outra, o acesso à digitação. Você pode digitar até 15 caracteres. Sobretudo, não esqueça a sua senha (anote-a em algum lugar seguro) porque, senão, você não poderá mais abrir o seu arquivo.   Vá em "Arquivo", "Salvar como" Clique em "Ferramentas" e em "Opções gerais" MBF Agribusiness – Excel Intermediário. 59 Excel Intermediário   Digite a sua senha Clique em OK Personalizar modo de exibição O que é? Quando você possui uma planilha de Excel onde um grande número de dados é coletado e armazenado, de modo que a visualização completa deste é complexa e nem sempre necessária, costumamos usar Modos de exibição personalizados. Através deste recurso podemos alterar o modo como as planilhas são exibidas, permitindo que diferentes conjuntos de informações possam ser salvos como modos de exibição. Estes modos de exibição se diferenciam de acordo com as classes escolhidas por você, podendo ser utilizados quando você pretende trabalhar apenas com uma determinada parte dos dados presentes na tabela. O recurso Personalizar modo de exibição é semelhante ao recurso Estrutura de tópicos, porém o primeiro é mais flexível para determinar as maneiras de exibir sua planilha. Exemplo O gerente de uma loja de conveniência possui em suas mãos uma planilha com a receita de alguns bens vendidos em sua loja durante todo o ano de 2006. MBF Agribusiness – Excel Intermediário. 60 Excel Intermediário Porém, ele acha que é muito confuso olhar esta planilha como uma coisa só e decide que deve fazer uma personalização do modo de exibição da mesma, de modo que fique mais fácil a busca exata por informações de seu interesse. Esta personalização consiste em dividir esta tabela de acordo com duas classes: trimestres e produtos. Começaremos, primeiramente, a fazer a personalização de acordo com os quatro trimestres do ano. Para tanto, começando pelo 1º trimestre, devemos seguir os seguintes passos:  Devemos selecionar todas as linhas da planilha que não correspondem aos meses que queremos mostrar nesse modo. Evidentemente que a linha de título deve ser preservada.  Depois disso, devemos apertar o botão direito do mouse em cima das linhas selecionadas e clicar em Ocultar. MBF Agribusiness – Excel Intermediário. 61 Excel Intermediário  Em seguida, acesse o menu Exibir e clique em Personalizar modo de exibição.  Aparecerá uma saída onde você deve clicar em adicionar;  Logo em seguida, escreva o nome que você deseja dar para o seu modo de exibição. Dê OK. MBF Agribusiness – Excel Intermediário. 62 Excel Intermediário  Por fim, selecione a área da linha 4 até a linha 14. Aperte o botão direito e clique em Reexibir. Repita o procedimento para os trimestres seguintes, tomando cuidado na hora de selecionar os meses que deseja que sejam ocultados. Observação: sempre é bom criar um modo que abranja a tabela toda. Basta você selecionar todos os dados e criar o modo. Para fazermos a personalização de acordo com os produtos, devemos apenas trabalhar com as colunas em vez das linhas. No caso de criarmos um modo para sorvete, por exemplo, os seguintes passos devem ser seguidos:  Devemos selecionar todas as colunas (com exceção da coluna de meses) da planilha que não correspondem aos produtos que queremos mostrar nesse modo. MBF Agribusiness – Excel Intermediário. 63 Excel Intermediário Em seguida, siga os passos que já foram ensinados anteriormente e com certeza tudo dará certo. Para visualizar seus modos basta entrar novamente em Exibir e Personalizar modo de exibição. Selecione o modo que deseja ver e clique em mostrar. Usar Atingir Meta Se você conhece o resultado que deseja obter de uma fórmula, mas não tem certeza sobre o valor de entrada necessário para chegar a esse resultado, use o recurso Atingir Meta. Por exemplo, suponha que você precise de algum dinheiro emprestado. Você sabe o quanto precisa, por quanto tempo deseja pagar o empréstimo e quanto pode pagar por mês. Nesse caso, você pode usar Atingir Meta para determinar que taxa de juros precisa fixar para atender à sua meta de empréstimo. MBF Agribusiness – Excel Intermediário. 64 Excel Intermediário OBSERVAÇÃO: Atingir Meta funciona somente com um valor de entrada de variável. Se quiser aceitar mais de um valor de entrada, por exemplo, o valor do empréstimo e o valor do pagamento mensal de um empréstimo, use o suplemento Solver. Passo a passo com um exemplo Vamos examinar o exemplo anterior, passo a passo. Como você deseja calcular a taxa de juros do empréstimo necessária para atender à sua meta, use a função PGTO. A função PGTO calcula um valor de pagamento mensal. Nesse exemplo, o valor de pagamento mensal é a meta que você deseja atingir. Preparar a planilha 1. Abra uma nova planilha em branco. 2. Primeiro, adicione alguns rótulos à primeira coluna para facilitar a leitura da planilha. 3. Na célula A1, digite Valor do Empréstimo. 4. Na célula A2, digite Prazo em Meses. 5. Na célula A3, digite Taxa de Juros. 6. Na célula A4, digite Pagamento 7. Em seguida, adicione os valores que você conhece. 8. Na célula B1, digite 100000. Esse é o valor que você deseja pedir emprestado. 9. Na célula B2, digite 180. Esse é o número de meses em que você deseja liquidar o empréstimo. OBSERVAÇÃO: Embora você saiba o valor desejado para o pagamento, não o insira como valor porque ele é um resultado da fórmula. Em vez disso, adicione a fórmula à planilha e especifique o valor de pagamento em uma etapa posterior, ao usar Atingir Meta.  Em seguida, adicione a fórmula para a qual você tem uma meta. No  exemplo, use a função PGTO: Na célula B4, digite =PGTO(B3/12,B2,B1). Essa fórmula calcula o valor do pagamento. Nesse exemplo, você deseja pagar R$ 900 por MBF Agribusiness – Excel Intermediário. 65 Excel Intermediário mês, mas não insere esse valor porque pretende usar Atingir Meta para determinar a taxa de juros e Atingir Meta requer que você comece com uma fórmula. A fórmula faz referência às células B1 e B2, que contêm valores que você especificou nas etapas anteriores. A fórmula também faz referência à célula B3, que é onde você irá especificar que Atingir Meta deve inserir a taxa de juros. A fórmula divide o valor em B3 por 12 porque você especificou um pagamento mensal e a função PGTO assume uma taxa de juros anual. Como não há nenhum valor na célula B3, o Excel assume uma taxa de juros de 0% e, usando os valores no exemplo, retorna um pagamento de R$ 555,56. Você pode ignorar esse valor por enquanto. Use Atingir Meta para determinar a taxa de juros  Na guia Dados, no grupo Ferramentas de Dados, clique em Teste de Hipóteses e, em seguida, clique em Atingir Meta.  Na caixa Definir célula, insira a referência para a célula que contém a fórmula que você deseja resolver. No exemplo, essa referência é a célula B4.  Na caixa Para valor, digite o resultado da fórmula desejado. No exemplo, esse valor é -900. Observe que o número é negativo porque representa um pagamento.  Na caixa Alternando célula, insira a referência para a célula que contém o valor que você deseja ajustar. No exemplo, essa referência é a célula B3. OBSERVAÇÃO: A célula alterada por Atingir Meta deve ser referenciada pela fórmula na célula que você especificou na caixa Definir célula.  Clique em OK. O recurso Atingir Meta é executado e gera um resultado, como mostra a ilustração a seguir. MBF Agribusiness – Excel Intermediário. 66 Excel Intermediário  Por fim, formate a célula de destino (B3) de forma que ela exiba o resultado como uma porcentagem.  Na guia Página Inicial, no grupo Número, clique em Porcentagem.  Clique em Aumentar Casas Decimais ou Diminuir Casas Decimais para definir o número de casas decimais. MBF Agribusiness – Excel Intermediário. 67