Baixe o app para aproveitar ainda mais
Prévia do material em texto
Objetivos Capacitar o aluno a: Criar planilhas, analisando necessidades específicas, obtendo informações por meio de consultas e importações de vários tipos de Bancos de Dados; Criação de tabelas e gráficos dinâmicos para consolidação de dados; Elaboração de relatórios gerencias utilizando os vários tipos de funções; Simular situações diferenciadas com uma planilha base prevendo o impacto na realidade; Validar planilhas, visando a padronização da digitação e aplicações de funções; Criar formulários para maior agilidade nos processos de cálculo; Criar macros assegurando a integridade dos dados, visando minimizar o excesso de trabalho nas operações rotineiras. Pré-Requisito Possuir conhecimentos básicos de sistemas operacionais (Windows, Linux, etc.), e ter concluído o curso de Excel Standard ou possuir conhecimentos equivalentes. Excel Business Escola de Excel Fone: (11) 3477-4051 - contato@excelbusiness.com.br http://www.excelbusiness.com.br http://www.facebook.com/ExcBusiness Histórico do Excel Em 1978, um aluno da Escola de Administração da Universidade de Harvard, chamado Daniel Bricklin percebeu que seu mestre de finanças gastava muito tempo para modificar e realizar, no quadro negro, novos cálculos, que estavam dispostos em colunas e linhas, criando desta forma uma tabela e, e quando ele alterava uma variável, todos os dados referentes deveriam ser atualizados também! Neste momento o professor tinha de calcular cada fórmula, o que provocava bastante demora. Bricklin, juntamente com seu amigo e programador Robert Frankston, elaborou um programa que simulava o quadro negro do professor. Tratava-se da primeira Planilha Eletrônica! Os dois então fundaram a empresa VisCorp, em que o produto desenvolvido era o VisiCalc. 1987: Excel 2.0 para Windows 1990: Excel 3.022 1992: Excel 4.0 1993: Excel 5.0 (Office 4.2 e 4.3, também uma versão de 32 bits para o Windows NT somente) 1995: Excel 7.0 (Office 95) 1997: Excel 8.0 (Office 98) 1999: Excel 9.0 (Office 2000) 2001: Excel 10.0 (Office XP) 2003: Excel 11.0 (Office 2003) 2007: Excel 12.0 (Office 2007) 2010: Excel 14.0 (Office 2010) 2013: Excel 15.0 (Office 2013) Naquele momento, os micros eram vistos como brincadeira ou hobbies e suas vendas cresciam muito pouco, mas com o VisiCalc houve um repentino crescimento nas vendas, pois percebeu-se que poderia ser utilizado para assuntos mais sérios e práticos do cotidiano. Não só VisiCalc surgiu, mas também aparecem outros programas de Planilhas Eletrônicas disputando espaço num mercado em grande expansão. Em 1983 ocorreu o lançamento de um programa integrado chamado 1.2.3, criado pela empresa Lótus Development Corporation, hoje pertencente a IBM. O 1.2.3 além de ser planilha gerava Gráficos e tratava os dados como uma ferramenta de base de dados. Desbancou o VisiCalc da sua posição de líder. Nos anos 80, a Lótus seria o líder de mercado, concorrendo com outras Planilhas (SuperCalc, Multiplan e Quattro Pro). Já nos anos 90, é lançado o MS Windows pela Microsoft e, em seguida, aparece uma Planilha Eletrônica que rodava neste ambiente (MS – Excel 3.0) que se tornou líder em seu segmento, ainda que concorrendo com os posteriores: Quattro Pro for Windows e Lótus 1.2.3 for Windows. Definição de Planilha Eletrônica, Finalidade e Aplicação A Planilha é uma folha de cálculo disposta em forma de tabela, na qual poderão ser efetuados rapidamente vários tipos de cálculos matemáticos, simples ou complexos. De acordo com uma filosofia matricial, pode ser utilizada por qualquer pessoa de qualquer setor profissional que tenha no seu trabalho a necessidade de efetuar cálculos financeiros, estatísticos ou científicos. A Planilha Eletrônica é o programa que impulsionou e revolucionou o mercado da informática. Em sua evolução, a humanidade sempre tentou criar ferramentas para suprir as novas necessidades que foram aparecendo. Com a Planilha Eletrônica não foi diferente. Microsoft Excel A Planilha Eletrônica Microsoft Excel está caracterizada como um dos mais importantes aplicativos da área para uso em microcomputadores. O nome Excel vem da abreviatura da Excelent, ou seja, Excelente. O termo Excel em inglês significa primar, superar, ser superior. A operação do Microsoft Excel e das demais Planilhas Eletrônicas, mesmo tendo passado mais de 20 anos, continua similar. Mas claro que com o passar dos anos, foram acontecendo melhorias, porém a estrutura principal de operação continua a mesma. Como Carregar o Excel Para acessar Excel é necessário executar no Windows a seqüência de comandos idêntica aos demais programas do pacote Office: Clicar no Botão Iniciar e apontar para Programas. Levar até o atalho do Microsoft Excel (que poderá estar agrupado dentro de um outro menu chamado Microsoft Office). Todos os direitos reservados. É proibida a reprodução, mesmo parcial, por qualquer processo, seja mecânico, eletrônico, fotocópia, digitalização ou outros sem prévia autorização do autor. Microsoft, MS, Office, Excel, Access, Word, Internet Explorer e Visual Basic são de propriedade da Microsoft Corporation nos Estados Unidos da América e em outros Países. Elaborado por Ricardo Ferreira Nepomuceno ÍNDICE CAP 01 - Importações e Consultas ............................................................... 1 Importação de arquivos de texto ............................................................... 2 Importação de arquivo de dados ............................................................... 7 Importação de dados da Web ............................................................... 9 Consultando com o Microsoft Query ............................................................... 12 CAP 02 – Formatação Condicional ............................................................... 19 Barras de Dados ............................................................... 21 Escalas de Cor ............................................................... 21 Conjuntos de Ícones ............................................................... 21 Criando uma nova regra ............................................................... 22 Formatação condicional com Datas ............................................................... 23 Formatação condicional com Fórmulas ............................................................... 24 CAP 03 - Funções ............................................................... 27 Funções de Data e Hora ............................................................... 28 Funções Lógicas e Condicionais ............................................................... 38 Funções de Pesquisa e Referência ............................................................... 47 Funções Financeiras ............................................................... 53 CAP 04 - Ferramentas de Dados ............................................................... 61 Trabalhando com Tabelas Inteligentes ............................................................... 62 Filtro Avançado ............................................................... 67 Congelando Painéis ............................................................... 70 Texto para Colunas ............................................................... 72 Validação de Dados ............................................................... 74 Compartilhando Pastas de Trabalho ............................................................... 79 Atingir Meta (vs. Solver) ............................................................... 85 Subtotais ............................................................... 91CAP 05 – Tabelas Dinâmicas ............................................................... 95 Cálculos com Tabelas Dinâmicas ............................................................... 101 Criando campo Calculado ............................................................... 102 Agrupando Datas ............................................................... 104 Relações entre tabelas ............................................................... 106 Mostrando rótulos de linha lado a lado ............................................................... 109 Segmentação de Dados ............................................................... 111 Linha do Tempo ............................................................... 113 Gráfico Dinâmico ............................................................... 114 CAP 06 - Formulários Personalizados ............................................................... 117 A planilha Sorveteria ............................................................... 118 Exibindo a guia Desenvolvedor ............................................................... 119 Inserindo Controles de Formulário ............................................................... 120 CAP 07 - Trabalhando com Macros ............................................................... 131 Ligando o Gravador ............................................................... 133 Alterando a tecla de atalho e excluindo macros ............................................................... 138 Gravando um movimento relativo ............................................................... 139 Atribuindo macros a botões ............................................................... 145 Atribuindo macros a figuras ............................................................... 147 Onde são armazenadas as macros? ............................................................... 147 Excel Business Página 1 Excel Business Página 2 Importação de arquivos de texto Inicialmente importar arquivos para o Excel pode parecer desnecessário, mas atualmente é muito comum o uso de sistemas com possibilidades de exportar dados para .txt, .mdb, .csv e outros mais. Com isso a possibilidade de importação para o Excel proporciona segurança e facilidade ao elaborar relatórios e cálculos para gerenciamento e administração de dados e informações diversas. Para realizar esta atividade você utilizará o arquivo base.txt disponível na pasta Treinamento. Ele contém os nomes dos funcionários de todas as empresas de um grupo empresarial, cuja matriz está localizada na cidade de São Paulo. Para que você possa elaborar relatórios e consultas, o primeiro passo é importar este arquivo para o Excel, e assim dar continuidade às próximas etapas. 1. Abra uma nova pasta de trabalho em branco: 2. Na guia Dados, grupo Obter Dados Externos, clique na opção De Texto. Excel Business Página 3 3. Aparecerá a seguinte janela: 4. Selecione o arquivo de texto e clique em importar. Será exibida a seguinte janela: Observe que nesta janela é possível se fazer as seguintes configurações: Delimitado: utilizado para separar as colunas usando como critério a vírgula, a tabulação ou algum outro delimitador. Largura Fixa: utilizado para especificar as colunas no lugar onde for necessário. Iniciar Importação na linha: permite escolher a partir de que linha do arquivo de texto a importação será feita. O padrão é linha 1. Origem do Arquivo: permite a escolha do sistema operacional e idioma. A melhor alternativa é deixar a padrão. 5. Mantenha os padrões mostrados na figura anterior e clique em Avançar. Será exibida a janela a seguir: Excel Business Página 4 Observe que na caixa de opções Delimitadores, existem alguns itens onde você escolherá os caracteres que serão utilizados para separar uma coluna de outra. Exemplo: Se entre duas palavras existir um TAB (tabulação) para separá-las, significa que cada palavra ficará em uma coluna. Uma outra opção bastante útil é a Considerar delimitadores consecutivos como um só. Se ativada, por exemplo, e existirem dois delimitadores seguidos, será considerado apenas um. Quando esta opção não é acionada, se existir mais de um delimitador, serão geradas colunas em branco dentro da base de dados. 6. Escolha a opção “vírgula”, pois nesse caso o que separa um campo do outro é a vírgula. 7. Clique no botão Avançar e será exibida a janela a seguir: Excel Business Página 5 Para esta etapa você pode especificar o formato de dados de cada coluna, clique em qualquer uma delas na visualização de dados e selecionando em Formato dos dados da coluna, o formato desejado. Você pode, também, simplesmente clicar em Concluir, pois a opção Geral é selecionada por padrão e tem a finalidade de converter valor numérico em número, valor de data em data e os demais em texto. 8. Clique em Concluir para a exibição da próxima etapa. Na janela Importar dados será exibido a célula onde iniciará a importação. Clique no botão ao lado da caixa para definir outra célula, ou selecione a opção Na nova planilha para que a importação seja efetuada em uma nova planilha em branco. 9. Selecione a célula A1, caso não esteja selecionada, e clique em OK. O resultado deverá ser o mostrado a seguir: Após importar, qualquer alteração ou inclusão deverá ser feita dentro do arquivo driver.txt, por ser o arquivo principal que deu origem à planilha da figura anterior. 10. Abra o arquivo base.txt no Bloco de Notas ou outro editor que melhor lhe convier, e faça as seguintes alterações: Nome do módulo Nome Alterado 1394ohci Peça1 ACPI Peça2 AcpiPmi Peça3 adp94xx Peça4 adpahci Peça5 Excel Business Página 6 11. Salve o arquivo e volte ao Excel para dar continuidade. Se você fechou o Excel antes de fazer alterações no arquivo de texto, ao abrí-lo novamente no Excel a planilha estará atualizada. 12. Na guia Dados, grupo Conexões, clique em Atualizar tudo. Será exibida a seguinte janela: Podem haver várias importações em uma mesma pasta, portanto esta janela é aberta e é nela que deverá ser confirmado o arquivo para atualização. 13. Selecione o arquivo driver.txt e clique em importar. Veja o resultado. 14. Salve a pasta de trabalho com o nome de Base de dados. Encerre o Excel. 15. Abra novamente o arquivo que foi salvo e verifique a mensagem a seguir: Excel Business Página 7 16. Como forma de segurança, aparecerá essa mensagem para avisar que existe uma vínculo externo. 17. Clique em “Habilitar Conteúdo”. Dessa forma você terá o vínculo disponível para eventuais atualizações. Importação de arquivo de Dados Ao importar de arquivos de dados para o Excel, utilize arquivos feitos no Access salvos com as extensões .accdb ou .mdb. Este procedimento é muito semelhante para outras bases de dados. Temos o arquivo Grupo Empresarial.mdb que você irá importar para o Excel. Existem duas maneiras de importar uma base de dados para o Excel. Nesta atividade abordaremos a importação total da base de dados. Após a compreensão da atividade, vamos as fases de exercício e desenvolvimento. 1. Abra uma nova pasta de trabalho em branco: 2. Na guia Dados, grupo Obter dados externos e clique na opção Do Access: 3. Dentro da opção clicada aparecerá a tela a seguir: Excel Business Página 8 4. Deverá selecionar a base de dados BDPortaria.mdb. Clique em Abrir para prosseguir. Teremos a tela abaixo: 5. Na janela anterior é possível escolher o modo que os dados serão mostrados, como também a partir de qual célula o resultado será mostrado. Deixe a opção padrão ativada e clique em OK. Confira o resultado conforme abaixo: Excel Business Página 9 6. Veja que o resultado aparecerá formatado. Este é um recurso padrão da versão 2007.No Excel 2007 e 2010 para trocar a formatação da planilha, assim que o resultado for exibido, aparecerá a guia Design que mostrará as possibilidades de formatação, conforme figura a seguir: Importação de dados da Web Poucas pessoas sabem, mas é possível obter dados de uma determinada página da internet dentro do Excel. As páginas Web contêm frequentemente informação que é ideal para análise no Excel. Dependendo das necessidades, é possível obter dados atualizados via Web em uma planilha do Excel entre outras coisas. Um bom exemplo seria se você precisasse efetuar cálculos que dependam do valor da cotação do Dólar, sempre com a última atualização. Seria muito mais difícil ficar procurando sempre sites na internet com essa informação, já que é possível ter esse valor no Excel sempre atualizado de acordo com o site de referência. Vamos lá! 1- Aberto o Excel, acione a guia Dados e no grupo Obter Dados Externos, acione a opção Da Web. 2- Em “Endereço”, digite a página WEB tenha a informação necessária e depois clique no botão “Ir”: http://www.criandomsn.com/?p=3583 Excel Business Página 10 3- Para aumentar o microbrouser (tela inteira) clique duas 2 vezes sobre a barra superior. Para voltar ao normal, faça a mesma coisa 4-Você poderá selecionar o quadro que tenha a informação que lhe seja mais conveniente clicando em um botão que esteja ao lado da tabela desejada. Após a seleção da tabela, clique no botão “Importar”, indique uma célula de destino e clique OK. Veja o resultado: Excel Business Página 11 Para ter um valor sempre atualizado, de acordo com a página da Web referenciada, selecione a célula com o valor importado, vá até a guia Dados, e no grupo Conexões na seta Atualizar Tudo e vá em Propriedades da Conexão. Escolha o período em que a informação será atualizada com o site, e se deseja atualizar a informação ao abrir o arquivo. Controle da Atualização Habilitar atualização em segundo plano Marque essa caixa de seleção para executar uma consulta em segundo plano. Desmarque essa caixa de seleção para executar a consulta enquanto espera. A execução de uma consulta em segundo plano permite que você use o Excel enquanto a consulta é executada. Atualizar a cada n minutos Marque essa caixa de seleção para habilitar a atualização automática de dados externos em intervalos de tempo regulares e insira o número de minutos entre cada operação de atualização. Desmarque essa caixa de seleção para desabilitar a atualização automática de dados externos. Excel Business Página 12 Atualizar dados ao abrir o arquivo Marque essa caixa de seleção para atualizar automaticamente os dados externos ao abrir a pasta de trabalho. Desmarque essa caixa de seleção para abrir imediatamente a pasta de trabalho sem atualizar os dados externos. Remover dados do intervalo de dados externos antes de salvar a pasta de trabalho Marque essa caixa de seleção se desejar salvar a pasta de trabalho com a definição da consulta, mas sem os dados externos. Desmarque essa caixa de seleção para salvar a definição da consulta e os dados com a pasta de trabalho. Essa caixa de seleção torna-se disponível depois que você marca a caixa de seleção Atualizar dados ao abrir o arquivo. Consultando com o Microsoft Query A base de dados BDPortaria será importada para o Excel no modo Consulta (Query), escolhendo somente alguns campos como: Transportadora, Data e Status que satisfaçam a condição de pertencerem a Transportadora Bruxelas. 1. Abra uma pasta de trabalho em branco: 2. Vá à guia Dados, grupo Obter Dados Externos e clique na opção De Outras Fontes 3. Aparecerá o seguinte menu: 4. Na Janela anterior clique na opção Do Microsoft Query. Aparecerá tela a seguir onde você deverá selecionar a opção Banco de dados do MS Access. Clique em OK para prosseguir. Excel Business Página 13 5. Apresentará a seguinte tela: Lembre-se que esta janela difere das demais do Windows, e a unidade em questão deve ser selecionada na caixa Unidades. 6. Nesse caso, usaremos o banco de dados BDPortaria, que nos servirá de exemplo para essa demonstração. Clique em OK para prosseguir. Aparecerá a seguinte janela: 7. Na tela Assistente de consulta, aparece a indicação + TB_LOGISTICA, onde TB_LOGISTICA representa o nome da tabela. Clicando no sinal “mais”, você terá acesso a todos os campos que compõem esta tabela. Clique e confira com a imagem abaixo: Excel Business Página 14 8. Será possível a visualização de todos os campos que formam a tabela TB_LOGISTICA. Nesta janela existem diversas possibilidades: a. Ao clicarmos no botão , com o nome da tabela selecionada, todos os campos irão para o lado direito para serem utilizados na consulta. b. Ao clicarmos no botão , com o nome de um dos campos selecionado, apenas o campo indicado irá para o lado direito para ser utilizado na consulta. c. Ao quando selecionarmos qualquer campo clicarmos no botão , o conteúdo do campo selecionado poderá ser visualizado, como segue: 9. Agora selecione os seguintes campos : Transportadora, Data, Status: Excel Business Página 15 10. Clique em Avançar para obter a tela a seguir: 11. O enunciado da atividade apresenta a restrição de importação para somente a transportadora Bruxelas. Portanto, serão necessárias diversas adaptações. Na caixa Coluna a filtrar, selecione Transportadora: 12. Na caixa Transportadora selecione o parâmetro “igual”: Excel Business Página 16 13. Como serão exibidos somente os dados relativos a Transportadora Bruxelas, selecione adequadamente na caixa que mostra os nomes da transportadoras: 14. Clique em Avançar e terá a seguinte tela: 15. Na tela a seguir você pode ou não escolher uma ordem de classificação para o resultado. Neste caso não usará o nome da Transportadora, pois será utilizado o mesmo conteúdo para todos os registros encontrados. Devemos escolher a classificação por Data, como segue: Excel Business Página 17 16. Clique em Avançar e terá o resultado a seguir: 17. Agora, clique em Concluir para obter a janela Importar dados. Nesta tela é possível selecionar qual apresentação de dados e a partir de qual célula o resultado será exibido. Clique em OK: 18. Dessa forma você obterá o seguinte resultado: Excel Business Página 18 19. Apenas ressaltando, que foi a partir de uma base de dados que geramos a planilha anterior. Portanto, qualquer alteração ou inclusão que precise fazer, deverá ser no arquivo na base de dados, dentro do Access. A partir disso retornamos ao Excel, acessamos a guia Design, grupo Dados De Tabela Externa, botão Atualizar. Excel Business Página 19 Excel Business Página 20 Formatação condicional A Formatação Condicional é uma ferramenta extremamente útil do Excel que nos permite formatar um conjunto de células de uma planilha a partir de condições pré-estabelecidas pelo usuário. Isso permite criar planilhas com um aspecto gráfico que seja útil para que o usuário consiga analisar com facilidade, as informações contidas em sua planilha, já que a formatação mostrará a ele se a condição adotada foi atingida ou não. Anteriormente à versão 2007 do Excel, a Formatação Condicional permitia ao usuário escolher somente cores de fundo e de fonte para aplicar como formatação das células, no entanto, a partir da versão 2007 os usuários ganharam uma nova forma de utilizar esta ferramenta, a partir da utilização de ícones que representam se uma condição foi atingida parcialmente, completamente, ou não atingida. Antes, era um pouco trabalhoso aplicar formatação condicional, como barras e escalas de cores, aos seus dados. Mas, agora, existe um novo atalho com o qual você pode fazer operações, como realçar os números mais altos, em apenas dois cliques. O segredo é o novo botãoLentes de Análise Rápida, que é exibido quando você seleciona seus dados. 1. Selecione as células contendo os dados que você quer formatar. 2. Clique no botão Lentes de Análise Rápida exibido no canto inferior direito dos dados selecionados (ou pressione CRTL + Q). 3. Em Formatação, selecione as opções desejadas. Em caso de dúvida sobre qual opção escolher, basta apontar para cada uma para ter uma visualização. Obs: não gostou da formatação condicional que você escolheu? Basta clicar em Limpar. Excel Business Página 21 Talvez você perceba que as opções de formatação disponíveis na lente nem sempre são as mesmas. Isso acontece porque elas mudam com base no tipo de dados que você selecionou na sua planilha. Por exemplo, se você selecionar números, terá um conjunto de opções de formatação. Se selecionar texto, terá um conjunto diferente de opções. Barras de Dados Esse modo cria uma espécie de gráfico dentro da própria célula. Escalas de Cor Esse modo cria uma escala de cor de acordo com o intervalo de números usados. Conjuntos de Ícones Essa forma exibe um conjunto ícones de acordo com os itens das células. http://office.microsoft.com/pt-br/excel-help/redir/HA102809669.aspx?CTT=5&origin=HA102809668 http://office.microsoft.com/pt-br/excel-help/redir/HA102809335.aspx?CTT=5&origin=HA102809668 http://office.microsoft.com/pt-br/excel-help/redir/HA102809335.aspx?CTT=5&origin=HA102809668 Excel Business Página 22 Criando uma nova regra Agora iremos criar uma regra para o status usando as palavras “bom”, “ótimo” e “regular” cada uma delas ficará com uma formatação diferente e automática. 1 - Selecione o intervalo, clique em “formatação condicional” e “nova regra”. 2 - Selecione o “formatar apenas células que contenham”, escolha “é igual a” e ao lado escreva a palavra que deseja aplicar a regra. 3 - Clique em “formatar”, e nas guias acima escolha quais formatações deseja aplicar e depois “ok”; 4 - Repita o processo com as outras palavras no nosso caso, “Ruim” e “regular”. Além dessa opção você pode criar uma regra usando um dos modelos de formatação condicional (conjunto de ícones, barra de dados e escala de cor). Para isso clique em “formatação condicional”, escolha o modelo e “mais regras”. Caso queira limpar as formatações feitas, selecione o intervalo, “formatação condicional” e “limpar regras”. Excel Business Página 23 Formatação Condicional com Datas (aviso de vencimento) Uma necessidade muito frequente, é a de que o Excel “avise” quando houver uma data vencida, ou para vencer. No exemplo abaixo, há na coluna A números de Notas Fiscais e na coluna B, as datas de vencimento. Note que também foi inserida a função =HOJE() na célula D2 (falaremos sobre funções de Data e Hora mais adiante): Como a função HOJE faz com que a data da célula seja atualizada automaticamente, usaremos uma Formatação Condicional, de modo que se o valor da célula for Menor ou Igual a data atual (que está na célula D2), então o preenchimento da célula ficará vermelho: Veja o resultado, que será dinamicamente atualizado! Excel Business Página 24 Formatação Condicional com Fórmulas É possível usar trabalhar com fórmulas nas regras de Formatação Condicional. Mas porquê usar??? Todas as vezes que a regra que determinará a formatação não estiver na mesma coluna que será formatada, o processo de formatação condicional somente será possível se for por meio de uma fórmula. Neste exemplo, quando a média estiver entre 3 e 7, a cor de preenchimento será Amarela; quando a média for menor do que 3 a cor será Vermelha, e quando a média for maior do que 7 a cor será Verde. Na janela de formatação condicional, deve-se marcar a opção “Usar uma fórmula para determinar quais células devem ser formatadas”. A fórmula para a primeira regra será a seguinte: =F2>7 Repita a operação para as demais regras, usando as seguintes fórmulas: =F2<3 Vermelho =F2>=3 Amarelo Nesse caso, será fundamental determinar a ordem na qual as regras serão aplicadas, pois se a primeira regra for a =F2>=3 (amarela) uma matéria com média 10 receberá a formatação amarela. Em outras palavras, o Excel testará as regras de acordo com a ordem em que foram criadas, da última para a primeira, sendo que essa ordem poderá ser editada pelos botões destacados abaixo: Deixe a ordem de acordo com a figura a seguir, clique em “Aplicar”, e o resultado será o seguinte: Excel Business Página 25 No exemplo a seguir, a Função DIA.DA.SEMANA foi usada para preencher as células selecionadas em Vermelho, quando o dia da semana era Sábado (6) ou domingo (7). Obs.: Nesse caso, a função dia da semana usou em seu segundo argumento ([retornar_tipo]) o nº 2, que significa Números de 1 (segunda-feira) a 7 (domingo). Fórmula aplicada: = DIA.DA.SEMANA($A2;2)>=6 Também é importante salientar que nesse caso, ao usar a referência da célula com a data (A2), foi necessário fixar somente a coluna, deixando a linha como referência relativa, já que a data deverá ser sempre pesquisada na coluna A, e na linha em que for aplicada a formatação. Excel Business Página 26 Excel Business Página 27 Excel Business Página 28 Funções de Data e Hora Cálculos com horas Entendendo como são interpretadas as horas fica mais fácil de compreender como são feitos os cálculos baseados em horas. Para calcular o tempo decorrido entre um horário e outro, basta um horário pelo outro. Se em C1 houver 16:51 e em C2 12:00, então a fórmula a seguir retorna 4:51, que é a diferença entre os dois horários: =C1 – C2 Se houver o formato de número Geral, o valor dessa fórmula é 0,202083, que é a fração das 24 horas do dia. Assim como as datas, quando for digitar horas diretamente em fórmulas, estas devem estar entre aspas como em: =”12:00” – “8:00” Resultando 4:00. Para calcular a jornada de trabalho de um funcionário, basta saber o seu horário de entrada, intervalo para almoço e saída. Veja a figura a seguir: Calcule a diferença de horários subtraindo um pelo outro A coluna A registra o horário de entrada, as colunas B e C o intervalo para almoço e na coluna D há o horário de saída. Para calcular o tempo total de horas trabalhadas no dia, subtraímos do horário de saída para o almoço a entrada, calculando o tempo decorrido antes do almoço. Depois somamos a este valor a diferença entre a saída e o retorno do almoço, que é o tempo trabalhado após o almoço, ficando a fórmula na célula E2: =B2-A2 + D2-C2 O formato das células da coluna E é hh:mm. Vamos supor que esses trabalhadores ganhem R$ 20,00 por hora de trabalho. Para calcular o valor a pagar para cada um inserimos mais um campo na coluna F rotulando de Total a pagar e o valor hora que está em H2. A fórmula da célula F2, que calcula o valor a pagar para as 9:00H de trabalho é: =E2*$H$2 Excel Business Página 29 Ao confirmar esta fórmula você leva um susto. Mais assustado ainda vai ficar aquele que for receber o pagamento. Veja que estranho. Receber R$ 7,81 por nove horas de trabalho, sendo que o valor hora é em R$ 20,00. O que aconteceu? Não culpe o Excel. Ele não está ficando louco, nem significa que há vírus no seu computador. O cálculo do Total a pagar multiplica 09:00, que é uma fração do dia correspondente a 9/24 ou 0,375, por 20,00 gerando 7,50. O Excel não está louco. É simples entender a questão. Quando multiplicamos o valor de E2 (9:22) por H2 (20,00) na realidade estamos multiplicando 20 por 0,3903 são 7,50. Para solucionar esta questão, transformarmos 9:00 (0,1375) em 9 inteiros. Como um horário é a fração do dia, precisamos multiplicar esse horário por 24, que são o total de horas de um dia. Então, a operação 9:22 (0,375) vezes 24 é igual a 9, que é diferente de 9:00. Portanto a fórmula deve ser corrigida para: =E2 * 24 * H$2 Neste casofoi preciso multiplicar o total de horas por 24 para retornar o valor correto do total a pagar Quando trabalhar com horários acima de 24h pode haver outra surpresa. Se o resultado de uma fórmula for maior que 24:00 e a célula estiver formatada como hh:mm, então será exibida a diferença correspondente ao valor da célula para 24:00 ou para o seu múltiplo mais próximo, como 26:30 serão exibidas como 02:30. Isso não afeta os valores das células que dependam desta, é só uma questão de formatação. Para calcular o número de horas de em um determinado projeto, uma empresa mediu o tempochegando inicialmente a um resultado de 02:00 (horas). Muito estranho, e o Excel não tem culpa. Veja porque isso acontece: deveria ser 50:00. Porém devido ao formato da célula surge 02:00 que é a diferença de 50 para 48. Por que 48? Porque como o valor 50 é maior que 24 então será exibida a diferença de 50 para 24, que é 26. Como 26 ainda é maior que 24, então serão subtraídas 24 unidades de 26, Excel Business Página 30 resultando em 02. Para existir 50:00 basta mudar o formato, inserindo a letra h entre colchetes, como [hh]:mm. O quadro da esquerda exibe o total com o formato hh:mm e o da direita possui o formato [hh]:mm, exibindo o horário acima de 24 horas Outra possibilidade (e talvez a mais fácil) seria o uso da janela formatar células, escolhendo o formato conforme a imagem a seguir: Datas e Horas juntas em fórmulas Uma vez que compreendido o conceito de interpretação das datas e horas, trabalhe com as duas medidas na mesma fórmula quando for necessário. Para determinar a quantidade de horas trabalhadas por um funcionário que entrou no dia 10/01/2014 às 21h e saiu no dia 11/01/2014 às 05h, basta somar a data e hora de saída e subtrair com a soma da data e hora de entrada. Se for digitar data e hora na mesma célula coloque um espaço entre elas, como 10/10/2013 12:00 e se digitá-las em fórmula insira-as entre aspas, como em “10/10/2013 12:00”. Excel Business Página 31 Funções DIA, MÊS e ANO Existem funções da categoria de data e hora que extraem cada item da data ou da hora. As funções DIA, MÊS e ANO retornam cada um desses elementos de uma data. A função DIA retorna um número inteiro entre 1 e 31, correspondente ao dia de uma data. Por exemplo, o dia da data de 15/12/1999 é 15. A função MÊS retorna um número inteiro entre 1 (Janeiro) e 12 (Dezembro), equivalente ao mês de uma data. ANO retorna um número inteiro entre 1900 e 9999 referente ao ano de uma data. Essas 3 funções possuem o mesmo argumento, que é: Núm_série: é o número de série da data da qual se deseja extrair o DIA, MÊS ou ANO. Isso significa que deve servir de argumento qualquer data, seja no formato de número ou data. Portando, utilize as mesmas regras utilizadas nos cálculos com datas, ou seja, forneça como argumento uma célula que contenha uma Dara em qualquer formato ou digite a mesma entre aspas. Na célula B1 desta figura há a data de 26/03/2014 e o intervalo B4:B7 contém as seguintes fórmulas respectivamente: =DIA (B1) Retornando 26 =MÊS (B1) Retornando 3 =ANO (B1) Retornando 2014 No intervalo B4:B7 há o dia, o mês e o ano da data da célula B1 Essas 3 funções podem ser aninhadas à função HOJE para retornar a uma data atualizada, conforme você verá a seguir. Excel Business Página 32 Função HOJE e AGORA Sempre que precisar ter em sua planilha a data do dia atual, use a função HOJE. Basta digitar em uma célula =HOJE() e você terá uma data que se atualizará de modo automático, diariamente. Já a função AGORA, atualizará além da data atual, também a hora atual do sistema (entenda sistema como a data e hora configurada no painel de controle do computador). Basta digitar =AGORA(). Veja os exemplo abaixo: FUNÇÃO HOJE FUNÇÃO AGORA Função DIA.DA.SEMANA Você precisa inserir uma coluna em sua tabela que mostre o dia da semana para cada uma das datas listadas sem ter que fazer isso na mão? A seguir vamos explicar como você resolverá facilmente este problema. O exemplo que usaremos é bastante simples, uma coluna para a data e outra para o dia da semana. A sua tabela poderá ser bem mais elaborada que essa, mas na prática, o resultado final será o mesmo. Feito isso, vamos para o passo seguinte. Usaremos a função chamada "DIA.DA.SEMANA" do Excel para que ele nos retorne o número do dia da semana para cada uma das datas. Preencha a fórmula na célula B2 conforme a imagem abaixo e posteriormente use a alça de preenchimento até a célula B10. Se você quiser que a semana inicie em um dia diferente do Domingo, sua fórmula deverá conter mais um parâmetro. Excel Business Página 33 DIA.DA.SEMANA(DATA; TIPO) O parâmetro chamado TIPO não é obrigatório e oferece as seguintes possibilidades de resultados: =DIA.DA.SEMANA("01/05/2012") --> de 1 a 7 iniciando no Domingo e terminando no Sábado O valor retornado será 3. =DIA.DA.SEMANA("01/05/2012"; 1) --> de 1 a 7 iniciando no Domingo e terminando no Sábado O valor retornado será 3. =DIA.DA.SEMANA("01/05/2012"; 2) --> de 1 a 7 iniciando na Segunda e terminando no Domingo O valor retornado será 2. =DIA.DA.SEMANA("01/05/2012"; 3) --> de 0 a 6 iniciando na Segunda e terminando no Domingo O valor retornado será 1. Uma vez que tudo esteja configurado e o número do dia da semana já está aparecendo nas células pode ser necessário que no lugar do número seja exibido o nome do dia da semana. Vejamos então como fazer isso: Selecione as células do intervalo B2 a B10, clique com o botão direito do mouse e em seguida escolha a opção "Formatar Células". Na aba chamada "Número" clique na categoria "Número" e logo em seguida na categoria "Personalizado". No campo "Tipo" informe o texto "dddd" (sem aspas) e clique em OK para salvar. Sua tabela deverá ter ficado conforme figura a seguir: Excel Business Página 34 Função FIMMÊS Em algumas analises você precisará utilizar o último dia do mês. Em alguns bancos de dados o fechamento do mês é gravado com o último dia do mês vigente. A sintaxe desta função é simples: =FIMMÊS($A$5;0): Onde A$5$ é uma data inicial (referência). Neste caso coloquei 01/01/2013. 0 zero “0” na função indica de qual mês eu quero o fim do mês. Ficará assim: =FIMMÊS($A$5;0) => =FIMMÊS(“01/01/2013”;0) Resultado: 31/01/2013 Outros Casos: =FIMMÊS($A$5;-1) ) => =FIMMÊS(“01/01/2013”;-1) Resultado: 31/12/2012 =FIMMÊS($A$5;1) ) => =FIMMÊS(“01/01/2013”;1) Resultado: 28/02/2013 Dica: Para automatizar o essa função para obter o fim de todos os meses deste ano por exemplo, utilize a função MÊS() junto com a função FIMMÊS(). Exemplo: =FIMMÊS($A$5;MÊS(D6)-1). Baixe o arquivo e veja como utilizar. Função DATAM Quando é necessário adicionar ou subtrair meses em uma data, usamos a função DATAM. Sua sintaxe é =DATAM(data_inicial;meses), que retornará uma data, quando se indica a data inicial e o número de meses. Após montar os valores na planilha do Excel, aplique, na primeira célula da coluna Datas Recalculadas a função =DATAM(A3;B3), a fim de somar os meses da célula B3 a data da célula A3. Feito isto expanda a função às demais células da mesma coluna. http://excelparanegociosdotcom.files.wordpress.com/2013/07/fimmes01.jpg Excel Business Página 35 Veja o resultado: Função DIATRABALHO Retorna um número que representa uma data que é o número indicado de dias úteis antes ou após uma data (a data inicial). Os dias úteis excluem fins de semana e quaisquer datas identificadas como feriados. Use DIATRABALHO para excluir os fins de semana ou feriados ao calcular as datas de vencimento de fatura, horas de entrega esperadas ou o número de dias de trabalho executado. Sintaxe DIATRABALHO(data_inicial, dias, [feriados]) A sintaxe da função DIATRABALHO tem os seguintes argumentos: PARÂMETRO TIPO DESCRIÇÃO Data_inicial Obrigatório Uma data que representa a datainicial. Dias Obrigatório O número de dias úteis antes ou depois de data_inicial. Um valor positivo para gera uma data futura; um valor negativo gera uma data passada. Feriados Opcional Uma lista opcional com uma ou mais datas a serem excluídas do calendário de trabalho, como feriados estaduais, federais e flutuantes. A lista pode ser um intervalo de células que contém as datas ou uma constante de matriz dos números de série que representam as datas. Exemplo: Excel Business Página 36 Obs.: Nesse exemplo os feriados estão cadastrados em uma planilha com o nome “Feriados”. Função DIATRABALHO.INTL Retorna o número de série da data antes ou depois de um número específico de dias úteis com parâmetros de fim de semana personalizados. Parâmetros de fim de semana indicam quais e quantos dias são de fim de semana. Dias de fim de semana e dias especificados como feriado não são considerados dias úteis. Sintaxe DIATRABALHO.INTL(data_inicial, dias, [fim de semana], [feriados]) A sintaxe da função DIATRABALHO.INTL tem os seguintes argumentos: PARÂMETRO TIPO DESCRIÇÃO Data_inicial Obrigatório A data de início, truncada para que apareça como um número inteiro. Dias Obrigatório O número de dias úteis antes ou depois de data_inicial. Um valor positivo gera uma data futura; um valor negativo gera uma data passada; um valor zero gera uma data_inicial. O deslocamento de dia é truncado para que apareça como um número inteiro. Fim de Semana Opcional Indica os dias da semana que são de fim de semana e não são considerados dias úteis. Fim de Semana é um número de fim de semana ou uma cadeia de caracteres que especificam quando ocorrem os fins de semana. Feriados Opcional Um conjunto opcional de uma ou mais datas que serão excluídas do calendário de dias úteis. Feriados devem ser um intervalo de células que contenham as datas ou uma constante de matriz dos valores de série que representam essas datas. A ordem de datas ou valores consecutivos em feriados pode ser arbitrária. Valores numerais de fim de semana indicam os dias de fim de semana (folgas) a seguir: Excel Business Página 37 Exemplo: Função DIATRABALHOTOTAL.INTL Retorna o número de dias úteis inteiros entre duas datas usando parâmetros para indicar quais e quantos dias são dias de fim de semana. Dias de fim de semana e quaisquer dias especificados como feriados não são considerados como dias úteis. Sintaxe DIATRABALHOTOTAL.INTL(data_inicial, data_final, [fim de semana], [feriados]) A sintaxe da função DIATRABALHOTOTAL.INTL tem os seguintes argumentos: PARÂMETRO TIPO DESCRIÇÃO Data_inicial Obrigatório As datas para as quais a diferença deve ser calculada. Data_inicial pode ser anterior, igual ou posterior a data_final. Dias Obrigatório Indica os dias da semana que são dias de fim de semana e que não estão incluídos no número de dias úteis inteiros entre data_inicial e data_final. Fim de semana é um número de fim de semana ou uma cadeia de caracteres que especifica quando ocorrem fins de semana. Fim de Semana Opcional Indica os dias da semana que são de fim de semana e não são considerados dias úteis. Fim de Semana é um número de fim de semana ou uma cadeia de caracteres que especificam quando ocorrem os fins de semana. Excel Business Página 38 Feriados Opcional Um conjunto opcional de uma ou mais datas que devem ser excluídas do calendário de dias úteis. Feriados devem ser um intervalo de células que contêm as datas ou uma constante de matriz dos valores de série que representam essas datas. A ordem de datas ou valores de série em feriados podem ser arbitrários. Valores numerais de fim de semana indicam os dias de fim de semana (folgas) a seguir: Exemplo: Funções Lógicas e Condicionais Ao construir suas planilhas e as fórmulas que a compõem, você vai perceber a necessidade de criar condições para controlar se um cálculo deve ser feito ou não. Numa planilha simples de controle de notas, por exemplo, você pode apresentar a situação do aluno no final do semestre: se ele foi aprovado, reprovado ou se ficou de recuperação. Para fazer essa análise, a média final do aluno é testada, e dependendo do seu valor, uma das três mensagens é exibida. A função usada para realizar essa tarefa é a conhecida função SE. Construa uma planilha para controle de notas com 4 colunas contendo os valores das notas dos 4 bimestres. Utilize, por exemplo, da célula B2 até a célula E2. Em seguida, gere a média na quinta coluna. Na próxima célula você deve colocar a situação do aluno. Excel Business Página 39 A estrutura da função SE é a seguinte: =SE(teste lógico; resultado se verdadeiro; resultado se falso). Onde o teste lógico é a condição ou o teste a ser verificado, o resultado se verdadeiro é o que deve ser feito caso a condição for verdadeira e resultado se falso é o que deve ser feito caso a condição for falsa. O ponto-e-vírgula serve para separar um item do outro. Neste exemplo de controle de notas, o teste lógico será a verificação da média do aluno, se passar ou for igual a cinco, a mensagem de “Aprovado” deve aparecer, se não a mensagem de “Reprovado” deve aparecer. Sendo assim, a fórmula ficaria: =SE(F2>=5;"Aprovado";"Reprovado") Mas, dessa maneira há apenas duas possibilidades de determinar a situação do aluno: ou ele foi aprovado ou foi reprovado. Para acrescentar mais possibilidades nesta fórmula, você deve criar o que recebe o nome de funções aninhadas, ou seja, uma função dentro da outra. Observe que se o aluno tem uma média maior que cinco, o Excel exibe direto a mensagem de Aprovado. Porém este aluno pode ter média exatamente igual a cinco ou dez. Tanto um quanto o outro exibe a mesma mensagem de aprovado, mas o correto é mostra recuperação para médias até 7 e só desse valor em diante mostrar “Aprovado”. No lugar da mensagem “Aprovado” na sua fórmula, você deve criar outra função SE, pois é preciso fazer outro teste (maior que sete ou não). A fórmula final ficaria assim: =SE(F2>7;"Aprovado";SE(F2<5;"Reprovado";"Recuperação")) Uma outra possibilidade para completar a fórmula, seria aninhar mais uma função para atingir esse resultado. Para isso você deve usar a função E: =SE(E(F2>=5;F2<=7);"Recuperação";SE(F2<5;"Reprovado";"Aprovado")) Função E Retornará VERDADEIRO se todos os argumentos forem verdadeiros; retornará FALSO se um ou mais argumentos forem falsos. Sintaxe E(lógico1;lógico2; ...) Lógico1; lógico2;... são de 1 a 30 condições que você deseja testar e que podem ser VERDADEIRO ou FALSO. Comentários Excel Business Página 40 • Os argumentos devem ser avaliados para valores lógicos, como VERDADEIRO ou FALSO, ou devem ser matrizes ou referências que contêm valores lógicos. • Se um argumento de uma matriz ou referência contiver texto ou células vazias, esses valores serão ignorados. • Se o intervalo especificado não contiver valores lógicos, E retornará o valor de erro #VALOR. Exemplo de Função SE aninhada com E: =SE(E(B2>=5;C2>=5);"Aprovado";"Reprovado") Função OU Retorna VERDADEIRO se qualquer argumento for VERDADEIRO; retorna FALSO se todos os argumentos forem FALSOS. Sintaxe OU(lógico1;lógico2;...) Lógico1;lógico2,... são de uma a 30 condições que você deseja testar e que podem resultar em VERDADEIRO ou FALSO. Comentários • Os argumentos devem ser avaliados como valores lógicos, como VERDADEIRO ou FALSO, ou em matrizes ou referências que contenham valores lógicos. • Se um argumento de uma matriz ou referência contiver texto ou células vazias, esses valores serão ignorados. • Se o intervalo especificado não contiver valores lógicos, OU retornará o valor de erro #VALOR!. • Você pode usar uma fórmula de matriz OU para verificar se um valor ocorre em uma matriz. Para inserir uma fórmula de matriz, pressione CTRL+SHIFT+ENTER. Exemplo de Função SE aninhada com OU: =SE(OU(B2>7;C2>7);”Aprovado”;”Exame”). Abaixo,uma planilha com exemplos de função SE aninhada coma função E, e com a função OU. Excel Business Página 41 Planilha com as fórmulas exibidas (Ctrl + J) Função SOMASE Adiciona as células especificadas por um determinado critério. Sintaxe SOMASE(intervalo;critérios;intervalo_soma) Intervalo é o intervalo de células que se deseja calcular. Critérios são os critérios na forma de um número, expressão ou texto, que define quais células serão adicionadas. Por exemplo, os critérios podem ser expressos como 32, "32", ">32", "maçãs". Intervalo_soma são as células que serão realmente somadas. Comentários • As células em intervalo_soma são somadas somente se suas células correspondentes em intervalo coincidirem com os critérios estipulados. • Se intervalo_soma for omitido, as células em intervalo serão somadas. Excel Business Página 42 • O Microsoft Excel fornece funções adicionais que podem ser usadas para analisar seus dados com base em uma condição. Por exemplo, para contar o número de ocorrências de uma sequência de caracteres de texto ou um número contido em um intervalo de células, use a função CONT.SE. Para que a fórmula retorne um entre dois valores com base em uma condição, como uma bonificação de vendas baseada em um valor de vendas especificado, use a função SE. Exemplo: A função SOMASE retorna os resultados de: Resumo por Região, Resumo Mensal e Resumo por Vendedor. Função CONT.SE Calcula o número de células não vazias em um intervalo que corresponde a determinados critérios. Sintaxe CONT.SE(intervalo;critérios) Intervalo é o intervalo de células no qual se deseja contar células não vazias. Critérios é o critério na forma de um número, expressão ou texto que define quais células serão contadas. Por exemplo, os critérios podem ser expressos como 32, "32", ">32", "maçãs". Exemplo: Excel Business Página 43 Comentários O Microsoft Excel fornece funções adicionais que podem ser usadas para analisar seus dados com base em uma condição. Por exemplo, para calcular uma soma baseada em uma sequência de caracteres de texto ou em um número contido em um intervalo, use a função de planilha SOMASE. Para que a fórmula retorne um de dois valores com base em uma condição, como uma bonificação de vendas baseada em um valor de vendas especificado, use a função de planilha SE Função MÉDIASE Retorna a média (média aritmética) de todas as células em um intervalo que satisfazem um determinado critério. Sintaxe MÉDIASE(intervalo, critérios, [intervalo_média]) A sintaxe da função MÉDIASE tem os seguintes argumentos: PARÂMETRO TIPO DESCRIÇÃO Intervalo Obrigatório Uma ou mais células a serem usadas para o cálculo da média, incluindo números ou nomes, matrizes ou referências que contêm números. Critérios Obrigatório Os critérios na forma de um número, uma expressão, uma referência de célula ou um texto que define quais células serão usadas para o cálculo da média. Por exemplo, os critérios podem ser expressos como 32, "32", ">32", "maçãs" ou B4. Intervalo_Média Opcional O conjunto real de células que será usado para calcular a média. Se omitido, será usado o intervalo. Exemplo: Excel Business Página 44 Função SOMASES Adiciona as células em um intervalo (intervalo: duas ou mais células em uma planilha. As células de um intervalo podem ser adjacentes ou não adjacentes.) que atendem a vários critérios. Por exemplo, se você quiser somar os números no intervalo A1:A20 apenas se os números correspondentes em B1:B20 forem maior do que zero (0) e os números correspondentes em C1:C20 forem menores do que 10, poderá usar a seguinte fórmula: Sintaxe SOMASES(intervalo_soma, intervalo_critérios1, critérios1, [intervalo_critérios2, critérios2], …) A sintaxe da função SOMASES tem os seguintes argumentos: PARÂMETRO TIPO DESCRIÇÃO intervalo_soma Obrigatório Uma ou mais células para somar, incluindo números ou nomes, intervalos ou referências de célula (referência de célula: o conjunto de coordenadas que a célula abrange em uma planilha. Por exemplo, a referência da célula que aparece na interseção da coluna B e linha 3 é B3.) que contêm números. Valores em branco e de texto são ignorados. intervalo_critérios1 Obrigatório O primeiro intervalo no qual avaliar os critérios associados. critérios1 Obrigatório Os critérios no formato de um número, uma expressão, uma referência de célula ou um texto que define quais células no argumento intervalo_critérios1 serão adicionadas. Por exemplo, os critérios podem ser expressos como 32, ">32", B4, "maças" ou "32." intervalo_critérios2, critérios2, … Opcional Intervalos adicionais e seus critérios associados. Até 127 intervalo/critérios pares são permitidos. Exemplo: Excel Business Página 45 Função CONT.SES Aplica critérios a células em vários intervalos e conta o número de vezes que todos os critérios são atendidos. Sintaxe CONT.SES(intervalo_criterios1;critérios1;[intervalo_critérios2, critérios2], …) A sintaxe da função CONT.SES tem os seguintes argumentos: PARÂMETRO TIPO DESCRIÇÃO intervalo_critérios1 Obrigatório O primeiro intervalo no qual avaliar os critérios associados. critérios1 Obrigatório Os critérios no formato de um número, uma expressão, uma referência de célula ou um texto que define quais células serão contadas. Por exemplo, os critérios podem ser expressos como 32, ">32", B4, "maçãs" ou "32". intervalo_critérios2, critérios2, … Opcional Intervalos adicionais e seus critérios associados. Até 127 intervalo/critérios pares são permitidos. Exemplo: Função MÉDIASES Retorna a média (média aritmética) de todas as células que satisfazem vários critérios. Sintaxe MÉDIASES(intervalo_média, intervalo_critérios1, critérios1, [intervalo_critérios2, critérios2], …) A sintaxe da função MÉDIASES tem os seguintes argumentos: PARÂMETRO TIPO DESCRIÇÃO Intervalo_média Obrigatório Uma ou mais células a serem usadas para o cálculo da média, incluindo números ou nomes, matrizes ou referências que contêm números. Excel Business Página 46 Intervalo_critérios1, intervalo_critérios2, … Intervalo1_critérios é obrigatório, intervalos_critérios subsequentes são opcionais. Os intervalos de 1 a 127 para avaliar os critérios associados. Critérios1, critérios2, … Critérios1 é obrigatório, critérios subsequentes são opcionais. Os critérios de 1 a 127 na forma de um número, uma expressão, uma referência de célula ou um texto que define quais células serão usadas para calcular a média. Por exemplo, os critérios podem ser expressos como 32, "32", ">32", "maçãs" ou B4. Exemplo: Função SEERRO Função do Excel usada para o tratamento de erros. Possui a seguinte sintaxe: =SEERRO(valor;valor_se_erro) Onde: No primeiro argumento é selecionado o processo (célula ou fórmula), e no segundo o resultado que você deseja mostrar em caso de erro. Quando trabalhamos com funções ou fórmulas que podem resultar em erros, podemos “ocultar”, ou mesmo definir outro parâmetro (frase ou valor) melhorando assim a funcionalidade e o aspecto geral da planilha. Suponha que a fórmula =A1/A2 retorne um erro de divisão por 0 (#DIV/0!). Use a função SEERRO da seguinte maneira: =SEERRO((A1/A2);"") Nesse caso, a célula ficará em branco caso a função fosse retornar um #DIV/0! Outro Exemplo: Excel Business Página 47 =SEERRO((PROCV(A1;C2:E40;2;0); “Produto não cadastrado”) Dessa forma você poderá manipular e controlar os erros de suas fórmulas. Nota: Os erros do Excel poderão ser os seguintes: ERRO SIGNIFICADO #DIV/0! Acontece ao dividir um número por zero. #NOME? Acontece ao inserir um nome que não está definido, ou ao introduzir um texto sem estar entre aspas, ou não escreveu os dois pontos (:) ao definir um intervalo de células. #VALOR! Acontece ao introduzir uma fórmula matemática e a utilizar um operador que é texto, ou ao indicarum intervalo de células quando é esperado um valor único. #REF! Acontece ao apagar células que fazem parte de uma fórmula. #N/D Não está disponível a informação para o cálculo que quer executar. #NÚM! Acontece ao utilizar um argumento que não é valido para a função, ou a função que utilizando não encontrou resultado nenhum, ou introduziu uma fórmula cujoresultado é demasiado pequeno ou demasiado grande para ser representado na folha de cálculo. #NULO! Utilização de um operador de intervalos ou de uma referência de célula incorretos. Por exemplo: na intersecção de intervalos quando não há células comuns. Funções de Pesquisa e Referência Função PROC A função PROC retorna um valor de um intervalo de uma linha ou uma coluna ou de uma. A função PROC tem duas formas de sintaxe: a forma vetorial e a forma matricial. Forma vetorial Um vetor é um intervalo de apenas uma linha ou uma coluna. A forma vetorial de PROC procura em um intervalo de uma linha ou uma coluna (chamado de vetor) por um valor e retorna um valor da mesma posição em um segundo intervalo de uma linha ou uma coluna. Use essa forma da função PROC quando quiser especificar o intervalo que contém os valores que você deseja corresponder. A outra forma de PROC procura automaticamente a primeira coluna ou linha. Sintaxe PROC(valor_proc, vetor_proc, [vetor_result]) A sintaxe da forma vetorial da função PROC tem os seguintes argumentos: Excel Business Página 48 PARÂMETRO TIPO DESCRIÇÃO valor_procurado Obrigatório Um valor que PROC procura no primeiro vetor. valor_procurado pode ser um número, um texto, um valor lógico ou um nome ou uma referência associado a um valor. vetor_proc Obrigatório Um intervalo que contém apenas uma linha ou uma coluna. Os valores em vetor_proc podem ser um texto, números ou valores lógicos. vetor_result Opcional Um intervalo que contém apenas uma linha ou coluna. O argumento vetor_result deve ter o mesmo tamanho de vetor _proc. Exemplo Forma matricial A forma matricial de PROC procura na primeira linha ou coluna de uma matriz (matriz: usada para criar fórmulas únicas que produzem vários resultados ou que operam em um grupo de argumentos organizados em linhas e colunas. Um intervalo de matrizes compartilha uma fórmula comum; uma constante de matriz é um grupo de constantes usado como um argumento.) pelo valor especificado e retorna um valor da mesma posição, na última linha ou coluna da matriz. Use essa forma de PROC quando os valores que você deseja corresponder estiverem na primeira linha ou coluna da matriz. Use a outra forma de PROC quando quiser especificar o local da coluna ou linha. Dica Em geral, convém usar a função PROCH ou PROCV no lugar da forma matricial da função PROC. Essa forma de PROC é fornecida por questões de compatibilidade com outros programas de planilha. Sintaxe PROC(valor_procurado,matriz) A sintaxe da forma matricial da função PROC tem os seguintes argumentos: PARÂMETRO TIPO DESCRIÇÃO valor_procurado Obrigatóri o Um valor que PROC pesquisa em uma matriz. O argumento valor_procurado pode ser um número, um texto, um valor lógico ou um nome ou uma referência associado a um valor. Excel Business Página 49 matriz Obrigatóri o Um intervalo de células que contém texto, números ou valores lógicos que se deseja comparar com valor_procurado. Exemplo Função PROCV Localiza um valor na primeira coluna à esquerda de uma tabela e retorna um valor na mesma linha de uma coluna especificada na tabela. Use PROCV em vez de PROCH quando os valores da comparação estiverem posicionados em uma coluna à esquerda ou à direita dos dados que você deseja procurar. O V em PROCV significa "Vertical". Sintaxe PROCV(valor_procurado;matriz_tabela;núm_índice_coluna;procurar_intervalo) Valor_procurado é o valor a ser localizado na primeira coluna da matriz. Valor_procurado pode ser um valor, uma referência ou uma sequência de caracteres de texto. Matriz_tabela é a tabela de informações em que os dados são procurados. Use uma referência para um intervalo ou nome de intervalo, tal como Banco de dados ou Lista. • Se procurar_intervalo for VERDADEIRO, os valores na primeira coluna de matriz_tabela deverão ser colocados em ordem ascendente: ..., -2, -1, 0, 1, 2, ... , A-Z, FALSO, VERDADEIRO; caso contrário, PROCV pode não retornar o valor correto. Se procurar_intervalo for FALSO, matriz_tabela não precisará ser ordenada. • Você pode colocar os valores em ordem ascendente escolhendo o comando Classificarna guiaDados e selecionando Crescente. • Os valores na primeira coluna de matriz_tabela podem ser texto, números ou valores lógicos. • Textos em maiúsculas e minúsculas são equivalentes. Núm_índice_coluna é o número da coluna em matriz_tabela a partir do qual o valor correspondente deve ser retornado. Um núm_índice_coluna de 1 retornará o valor na primeira coluna em matriz_tabela; um núm_índice_coluna de 2 retornará o valor na segunda coluna em matriz_tabela, e assim por diante. Se núm_índice_coluna for menor do que 1, PROCV retornará o valor de erro #VALOR!; senúm_índice_coluna for maior do que o número de colunas em matriz_tabela, PROCV retornará o valor de erro #REF!. Excel Business Página 50 Procurar_intervalo é um valor lógico que especifica se você quer que PROCV encontre a correspondência exata ou uma correspondência aproximada. Se VERDADEIRO ou omitida, uma correspondência aproximada é retornada; em outras palavras, se uma correspondência exata não for encontrada, o valor maior mais próximo que é menor que o valor_procurado é retornado. Se FALSO, PROCV encontrará uma correspondência exata. Se nenhuma correspondência for encontrada, o valor de erro #N/D é retornado. Comentários • Se PROCV não localizar valor_procurado e procurar_intervalo for VERDADEIRO, ela usará o maior valor que for menor do que ou igual avalor_procurado. • Se valor_procurado for menor do que o menor valor na primeira coluna de matriz_tabela, PROCV fornecerá o valor de erro #N/D. • Se PROCV não localizar valor_procurado e procurar_intervalo for FALSO, PROCV fornecerá o valor #N/D. Exemplo 1: Utilize a função PROCV para montar a planilha abaixo. Quando vamos ao mercado, e percebemos que em um dos produtos não há um preço normalmente nos dirigimos ao scanner mais próximo com a finalidade de fazermos uma consulta usando o código de barras. A rotina que a planilha acima utiliza tem a funcionalidade similar. A chave da consulta será o código de barras (que está no produto). Dessa forma, será feita uma busca no banco de dados do estabelecimento com base no código, e quando este for encontrado ele retornará as colunas que contém os dados referentes a descrição do produto e o valor Na célula C4 (que está mesclada) insira a seguinte função: =PROCV(C16;B23:E83;3;0) Na célula C10 (que está mesclada) insira a seguinte função: =PROCV(C16;B23:E83;4;0) Alterando o código, a função PROCV retornará a Descrição do produto e o Valor automaticamente. Exemplo 2: Excel Business Página 51 Utilize as funções PROCV e SE para montar a planilha abaixo. Na coluna “IMC”, insira uma fórmula que divida o pela altura elevada ao quadrado. Lembre-se que, para elevar ao quadrado, o Excel utiliza o símbolo ^2 para indicar essa potência (=peso/altura²). Na coluna “Resultado” utilize a função PROCV e busque o resultado na tabela auxiliar. Obs.: Nesse caso, a função PROCV utilizada empregou em seu último argumento (procurar_intervalo) o número 1, ou VERDADEIRO, pois a correspondência não foi exata. Na coluna “Regime”, utilize a função SE para informar se a pessoa necessita ou não de regime, de acordo com o índice de IMC obtido. Função PROCH Localiza um valor específico na linha superior de uma tabela ou matriz de valores e retorna um valor na mesma coluna de uma linha especificada na tabela ou matriz. Use PROCH quando seus valores de comparação estiverem localizadosem uma linha ao longo da parte superior de uma tabela de dados e você quiser observar um número específico de linhas mais abaixo. Use PROCV quando os valores de comparação estiverem em uma coluna à esquerda dos dados que você deseja localizar. O H de PROCH significa "Horizontal." Sintaxe PROCH(valor_procurado;matriz_tabela;núm_índice_lin;procurar_intervalo) Valor_procurado é o valor a ser localizado na primeira linha da tabela. Valor_procurado pode ser um valor, uma referência ou uma sequência de caracteres de texto. Matriz_tabela é uma tabela de informações onde os dados devem ser procurados. Use uma referência para um intervalo ou um nome de intervalo. • Os valores na primeira linha de matriz_tabela podem ser texto, números ou valores lógicos. • Se procurar_intervalo for VERDADEIRO, os valores na primeira linha de matriz_tabela deverão ser colocados em ordem ascendente: ...-2, -1, 0, 1, 2,... , A-Z, FALSO, VERDADEIRO, caso contrário, PROCH pode não retornar o valor correto. Se procurar_intervalo for FALSO, matriz_tabela não precisará ser ordenada. Excel Business Página 52 • Textos em maiúsculas e minúsculas são equivalentes. • Você pode colocar valores em ordem ascendente, da esquerda para a direita, selecionando os valores, escolhendo o comando Classificar na guia Dados. Clique em Opções, clique em Classificar da esquerda para a direita e, em seguida, em OK. Em Classificar por, clique na linha da lista e depois em Crescente. Núm_índice_lin é o número da linha em matriz_tabela de onde o valor correspondente deve ser retirado. Um núm_índice_lin equivalente a 1 retorna o valor da primeira linha na matriz_tabela, um núm_índice_lin equivalente a 2 retorna o valor da segunda linha na matriz_tabela, e assim por diante. Se núm_índice_lin for menor do que 1, PROCH retornará o valor de erro #VALOR!; senúm_índice_lin for maior do que o número de linhas na matriz_tabela, PROCH retornará o valor de erro #REF!. Procurar_intervalo é um valor lógico que especifica se você quer que PROCH localize uma correspondência exata ou aproximada. Se VERDADEIRO ou omitido, uma correspondência aproximada é retornada. Em outras palavras, se uma correspondência exata não for localizada, o valor maior mais próximo que seja menor que o valor_procurado é retornado. Se FALSO, PROCH encontrará uma correspondência exata. Se nenhuma correspondência for localizada, o valor de erro #N/D será retornado. Comentários • Se PROCH não localizar valor_procurado, e procurar_intervalo for VERDADEIRO, ela usará o maior valor que é menor do que o valor_procurado. • Se o valor_procurado for menor do que o menor valor na primeira linha de matriz_tabela, PROCH retornará o valor de erro #N/D. Função ÍNDICE e função CORRESP Essas funções que assim como PROC, PROCV e PROCH estão também na categoria das funções de pesquisa e referencia, são extremamente úteis, pois há casos em que há a necessidade de fornecer dois parâmetros de pesquisa dentro de uma tabela: Excel Business Página 53 Note que essa é uma tabela que mostra a distância entre cidades. E se houver a necessidade de se criar um pesquisador, onde se insere a cidade de Origem e Destino, e automaticamente ser retornado a distância? Para isso, será necessário localizar a posição da cidade de origem nas linhas (se é linha 1, 2, etc.), e da mesma forma descobrir a posição da cidade de destino dentro da tabela (que por sua vez representará a posição de coluna). A função CORRESP tem essa missão! Uma vez que se tenha a posição de linha e de coluna, basta cruzar esses dados usando a função ÍNDICE. Agora, basta digitar a cidade de origem e de destino, que será retornado automaticamente a distância. Funções Financeiras As funções financeiras no Excel podem ser úteis em várias ocasiões, ex: Realizar cálculos financeiros; calcular montante, juros simples e compostos e taxa de juros; fazer análises de movimentação monetária com base no fluxo de caixa; trabalhar com regimes de capitalização e taxas equivalentes; trabalhar com juros exatos e comerciais; entender os conceitos utilizados pelos bancos para calcular juros de cheque especial e para concessão de empréstimos; usar as funções corretas para aplicação de descontos simples e compostos. Função PGTO Retorna o pagamento periódico de uma anuidade de acordo com pagamentos constantes e com uma taxa de juros constante. Sintaxe PGTO(taxa;nper;vp;vf;tipo) Excel Business Página 54 Para obter uma descrição mais completa dos argumentos em PGTO, consulte a função VP. Taxa é a taxa de juros por período. Nper é o número total de pagamentos pelo empréstimo. Vp é o valor presente, o valor total presente de uma série de pagamentos futuros. Vf é o valor futuro, ou o saldo de caixa, que você deseja obter depois do último pagamento. Se vf for omitido, será considerado 0 (o valor futuro de determinado empréstimo, por exemplo, 0). Tipo é o número 0 ou 1 e indica as datas de vencimento. Definir tipo para Se os vencimentos forem 0 ou omitido No final do período 1 No início do período Comentários • O pagamento retornado por PGTO inclui o principal e os juros e não inclui taxas, pagamentos de reserva ou tarifas, às vezes associados a empréstimos. • Certifique-se de que esteja sendo consistente quanto às unidades usadas para especificar taxa e nper. Se fizer pagamentos mensais por um empréstimo de quatro anos com juros de 12% ao ano, utilize 12%/12 para taxa e 4*12 para nper. Se fizer pagamentos anuais para o mesmo empréstimo, use 12% para taxa e 4 para nper. Função VP Retorna o valor presente de um investimento. O valor presente é o valor total correspondente ao valor atual de uma série de pagamentos futuros. Por exemplo, quando você pede dinheiro emprestado, o valor do empréstimo é o valor presente para quem empresta. Sintaxe VP(taxa;nper;pgto;vf;tipo) Taxa é a taxa de juros por período. Por exemplo, se você obtiver um empréstimo para um carro com uma taxa de juros de 10% ao ano e fizer pagamentos mensais, a sua taxa de juros mensal será 10%/12, ou 0,83%. Você deve inserir 10%/12, ou 0,83%, ou 0,0083, na fórmula como taxa. Excel Business Página 55 Nper é o número total de períodos de pagamento de uma anuidade. Por exemplo, se você obtiver um empréstimo de quatro anos e fizer pagamentos mensais, o empréstimo terá 4*12 (ou 48) períodos. Você deve inserir 48 na fórmula para nper. Pgto é o pagamento feito a cada período e não pode mudar durante a vigência da anuidade. Geralmente, pgto inclui o principal e os juros, e não há outras tarifas ou taxas. Por exemplo, os pagamentos mensais por um empréstimo para o carro de R$ 10.000 de quatro anos a 12% são R$ 263,33. Você deve inserir - 263,33 na fórmula como pgto. Se pgto for omitido, você deverá incluir o argumento vf. Vf é o valor futuro, ou um saldo de caixa, que você deseja obter depois do último pagamento. Se vf for omitido, será considerado 0 (o valor futuro de determinado empréstimo, por exemplo, é 0). Por exemplo, se quiser economizar R$ 50.000 para pagar um projeto especial em 18 anos, então R$ 50.000 é o valor futuro. Você pode então calcular a taxa de juros e determinar quanto deverá economizar a cada mês. Se vf for omitido, você deverá incluir o argumento pgto. Tipo é o número 0 ou 1 e indica as datas de vencimento. Definir tipo para Se os vencimentos forem 0 ou omitido No final do período 1 No início do período Comentários • Certifique-se de que esteja sendo consistente quanto às unidades usadas para especificar taxa e nper. Se fizer pagamentos mensais de um empréstimo de quatro anos com taxa de juros de 12% ao ano, use 12%/12 para taxa e 4*12 para nper. Se você fizer pagamentos anuais para o mesmo empréstimo, use 12% para taxa e 4 para nper. Excel Business Página 56 Função VF Retorna o valor futuro de um investimento de acordo com os pagamentos periódicos e constantes e com uma taxade juros constante. Sintaxe VF(taxa;nper;pgto;vp;tipo) Para obter uma descrição completa dos argumentos em VF e para obter mais informações sobre as funções de anuidade, consulte VP. Taxa é a taxa de juros por período. Nper é o número total de períodos de pagamento em uma anuidade. Pgto é o pagamento feito a cada período; não pode mudar durante a vigência da anuidade. Geralmente, pgto contém o capital e os juros e nenhuma outra tarifa ou taxas. Se pgto for omitido, você deverá incluir o argumento vp. Vp é o valor presente ou a soma total correspondente ao valor presente de uma série de pagamentos futuros. Se vp for omitido, será considerado 0 (zero) e a inclusão do argumento pgto será obrigatória. Tipo é o número 0 ou 1 e indica as datas de vencimento dos pagamentos. Se tipo for omitido, será considerado 0. Definir tipo para Se os vencimentos forem 0 No final do período 1 No início do período Comentários • Certifique-se de que esteja sendo consistente quanto às unidades usadas para especificar taxa e nper. Se fizer pagamentos mensais de um empréstimo de quatro anos com taxa de juros de 12% ao ano, use 12%/12 para taxa e 4*12 para nper. Se você fizer pagamentos anuais para o mesmo empréstimo, use 12% para taxa e 4 para nper. • Todos os argumentos, saques, tais como depósitos em poupança, serão representados por números negativos; depósitos recebidos, tais como cheques de dividendos, serão representados por números positivos. Excel Business Página 57 Função NPER Retorna o número de períodos para investimento de acordo com pagamentos constantes e periódicos e uma taxa de juros constante. Sintaxe NPER(taxa;pgto;vp;vf;tipo) Para obter uma descrição completa dos argumentos em NPER e sobre as funções de anuidade, consulte VP. Taxa é a taxa de juros por período. Pgto é o pagamento feito em cada período; não pode mudar durante a vigência da anuidade. Geralmente, pgto contém o capital e os juros, mas nenhuma outra tarifa ou taxas. Vp é o valor presente ou atual de uma série de pagamentos futuros. Vf é o valor futuro, ou o saldo, que você deseja obter depois do último pagamento. Se vf for omitido, será considerado 0 (o valor futuro de um empréstimo, por exemplo, é 0). Tipo é o número 0 ou 1 e indica as datas de vencimento. Definir tipo para Se os vencimentos forem 0 ou omitido No final do período 1 No início do período Excel Business Página 58 Função TAXA Retorna a taxa de juros por período de uma anuidade. TAXA é calculado por iteração e pode ter zero ou mais soluções. Se os resultados sucessivos de TAXA não convergirem para 0,0000001 depois de 20 iterações, TAXA retornará o valor de erro #NÚM!. Sintaxe TAXA(nper;pgto;vp;vf;tipo;estimativa) Consulte VP para obter uma descrição completa dos argumentos nper, pgto, vp, vf e tipo. Nper é o número total de períodos de pagamento em uma anuidade. Pgto é o pagamento feito em cada período e não pode mudar durante a vigência da anuidade. Geralmente, pgto inclui o principal e os juros e nenhuma outra taxa ou tributo. Se pgto for omitido, você deverá incluir o argumento vf. Vp é o valor presente — o valor total correspondente ao valor atual de uma série de pagamentos futuros. Vf é o valor futuro, ou o saldo, que você deseja obter depois do último pagamento. Se vf for omitido, será considerado 0 (o valor futuro de um empréstimo, por exemplo, é 0). Tipo é o número 0 ou 1 e indica as datas de vencimento. Definir tipo para Se os vencimentos forem 0 ou omitido No final do período 1 No início do período Excel Business Página 59 Estimativa é a sua estimativa para a taxa. • Se você omitir estimativa, este argumento será considerado 10%. • Se TAXA não convergir, atribua valores diferentes para estimativa. Em geral, TAXA converge se estimativa estiver entre 0 e 1. Comentários Certifique-se de que esteja sendo consistente quanto às unidades usadas para especificar estimativa e nper. Se você fizer pagamentos mensais por um empréstimo de quatro anos com juros de 12% ao ano, utilize 12%/12 para estimativa e 4*12 para nper. Se fizer pagamentos anuais para o mesmo empréstimo, utilize 12% para estimativa e 4 para nper. IMPORTANTE! Comparação Excel X Calculadora HP A tabela abaixo mostra as funções Financeiras no Excel, e suas correspondentes na calculadora HP 12C. Excel HP 12C OBJETIVO VF FV Retornar o valor futuro (Resgate) VP PV Retornar o valor presente (Entrada) TAXA I Retornar a taxa de juros NPER N Retornar a quantidade de períodos PGTO PMT Retornar o valor das parcelas TIPO 1 Pagamento no início do período TIPO 0 Pagamento no final do período Excel Business Página 60 Excel Business Página 61 Excel Business Página 62 Trabalhando com Tabelas Inteligentes A partir da versão 2007, a Microsoft lançou o recurso Tabelas (anteriormente conhecido como lista) permitindo assim gerenciar e analisar os dados dessa tabela independentemente dos dados fora dela. Por exemplo, é possível filtrar as colunas da tabela, adicionar uma linha para totais, aplicar formatação de tabela e publicar uma tabela em um servidor que executa o Windows SharePoint Services 3.0 ou o Microsoft SharePoint Foundation 2010. Quando usar uma Tabela Todas as vezes que você utilizar uma planilha que deverá se comportar como um Banco de Dados para quaisquer tipos de análise, ela poderá ser uma Tabela. O uso desse recurso permitirá análises muito mais rápidas e eficientes! PLANILHA DE DADOS INDICADO PLANILHA DE EXIBIÇÃO NÃO INDICADO Como criar uma Tabela (inteligente)? Para se beneficiar dos recursos oferecidos por uma Tabela inteligente, basta selecionar os dados e na guia Inserir (grupo Tabelas), clicar em Tabela, conforme imagem a seguir: Obs: você poderá também usar as de atalho Ctrl + Alt + T O assistente de criação de tabelas mostrará a seguinte mensagem, para verificar se na sua tabela há cabeçalhos, ou se eles deverão ser ciados automaticamente: Excel Business Página 63 Caso a tabela possua cabeçalhos, usando os padrões da mensagem anterior, os cabeçalhos serão reconhecidos, e a tabela será formatada conforme a imagem a seguir: Características importantes de uma Tabela Inteligente Deve-se observar que ao criar uma Tabela no Excel, além dos típicos modelos de formatação com linhas em cores alternadas (isso não é necessariamente uma regra), cabeçalhos destacados e filtros ativados, surge uma nova guia na faixa de opções: FERRAMENTAS DE TABELA (ela só aparecerá se a seleção estiver em uma célula da tabela). Convertendo um intervalo de dados em tabela e vice-versa É possível fazer uma planilha com o recurso Tabela ativo se voltar a ser uma tabela normal do Excel. Para isso, basta clicar na guia Design (em FERRAMENTAS DE TABELA), e no grupo Ferramentas clicar em . Surgirá uma mensagem de confirmação, e ao clicar em Sim, a tabela perderá as funcionalidades do recurso. Obs: as formatações irão se manter. Trabalhando com Linha de Totais O recurso Linha de Totais poderá ser um aliado importante para vários tipos de análises rápidas, principalmente que envolvam cálculos por categorias. Para ativar uma linha de totais basta marcar essa opção na guia FERRAMENTAS DE TABELA: Excel Business Página 64 Surgirá uma linha ao final da tabela, conforme a seguir: Com isso, é possível usar funções do Excel para geração rápida de resultados, de acordo com necessidades específicas de resultados (SOMA, CONTAGEM, MÉDIA, etc.). No exemplo a seguir foi usada a função SOMA, de modo que a soma total do campo Meta Mensal foi mostrada: Excel Business Página 65 O mais interessante, é que a função usada é sensível aos filtros. Isso significa que ao filtrar somente o cargo Consultor de Compras,
Compartilhar