Baixe o app para aproveitar ainda mais
Prévia do material em texto
EEXXCCEELL PARA ADMINISTRADORES E GESTORES 1 Este material foi desenvolvido pelos alunos Jessica Gabriele dos Santos, Vinicius Emanuel Martoni Rosa e Thiago Costa, alunos do curso de Administração da Fundação Hermínio Ometto - UNIARARAS, monitores da disciplina de Informática Aplicada I e II, entre 2009 e 2011, orientados pela Profa. Dra Raquel Gianolla Miranda, docente da disciplina mencionada acima. O objetivo deste material é auxiliar alunos de graduação e profissionais da área de Administração e Gestão a explorar os recursos básicos do aplicativo Excel em suas atividades. Agradecemos o apoio da coordenação, na pessoa dos Professores Daniel Siqueira Pitta Marques, Luiz Eduardo Gaio e dos demais professores que nos auxiliaram neste estudo, em especial à Profª Dra. Glaucia Maria Bressan, ao Prof. Esp. Aguinaldo Aparecido Gonçalves, ao Prof. Dr. Bethoveen, que contribuíram para a elaboração desta. Toda colaboração para aprofundamento deste trabalho é bem vinda. As sugestões podem ser enviadas para: raquelgm@uniararas.br Atenciosamente Profª Dra. Raquel Gianolla Miranda Aluna: Jéssica Gabriele dos Santos Aluno: Vinicius Emanuel Martoni Rosa Aluno: Thiago Costa 2 ÍNDICE CONHECENDO O EXCEL .................................................................................... 3 FUNÇÕES DE SOMA ........................................................................................... 5 FUNÇÕES ESTATÍSTICAS .................................................................................. 7 FUNÇÕES DE DATA E HORA ........................................................................... 16 FUNÇÕES DE LÓGICA ...................................................................................... 20 FUNÇÃO DE BANCO DE DADOS...................................................................... 33 FUNÇÕES FINANCEIRAS .................................................................................. 37 GRÁFICOS ......................................................................................................... 41 SOLVER ............................................................................................................. 71 MACROS ............................................................................................................ 81 3 CONHECENDO O EXCEL O Microsoft Excel é um aplicativo que trabalha com planilhas eletrônicas oferecendo recursos de cálculo, elaboração de gráficos e recursos de banco de dados. Formada por uma grande tabela contendo várias colunas e linhas, pode ilustrar de forma gráfica as variações de preços de um ou vários produtos, estatísticas de vendas mês a mês, somar valores sem a necessidade de cálculos, organizar dados por meio de suas células, por exemplo. A planilha do Excel Nas planilhas podemos também utilizar recursos de aplicação de um comando para ser executado em um grupo de células em cálculos ou formatações. Para este fim, podemos destacar a faixa onde o comando será aplicado da seguinte forma: CÉLULA G6 A CÉLULA A1 ESTÁ DESTACADA 4 AS VÁRIAS PLANILHAS SÃO ACESSADAS POR ESTAS ABAS E NOMEADAS PLAN1, PLAN2. Os arquivos do Excel são chamados de Pastas de trabalho, pois podem conter várias planilhas dentro de um mesmo arquivo. Isto é importante, pois podemos organizar nosso trabalho em grandes arquivos, divididos por problemas. Por exemplo: podemos ter um arquivo sobre os gastos de cada departamento de uma empresa, organizando para cada departamento, uma planilha diferente, dentro de um mesmo arquivo. PARA DESTACAR A COLUNA A , CLIQUE COM O MOUSE SOBRE A LETRA DA COLUNA A. PARA DESTACAR A LINHA 11 A , CLIQUE COM O MOUSE SOBRE O NÚMERO DA LINHA 11. 5 Nas empresas o aplicativo Excel é um bom aliado e pode ser utilizado em diversas áreas: Área Administrativa: Folha de Pagamento, Salários, Contabilidade, Controle de Compras, Tabelas de Preços, Saldos etc. Área Financeira: Análise de Investimentos, Custos, Projeção de Lucros, Fluxo de Caixa, Controle de Captação de Recursos, Controle de Contas a Pagar e a Receber, Simulação de Custos, etc. Produção: Controle de Produção, Controle de Produtividade, Controle de Estoque, etc. Área Comercial: Plano de Vendas, Controle de Visitas, Análise de Mercado, Controle de Notas Fiscais, Emissão de Listagem de Preços, etc. UTILIZANDO O EXCEL As células de uma planilha podem conter quatro tipos de informações: Texto: qualquer seqüência de caracteres. Número: lembre-se que para poder fazer cálculos, o Excel precisa reconhecer o conteúdo de uma célula como sendo numérico. Para isso, seu conteúdo pode conter somente números de 0 a 9 e os símbolos: ( + ou -. Lembre-se também que um número deve ser digitado sem formatação. Para formatar uma célula utilize o comando específico para tal: Formatar, Células, Números. Exemplos de formatações: R$ , %. Fórmulas: Denomina-se fórmula qualquer cálculo matemático elaborado pelo usuário e que seguem as seguintes regras: 6 SEMPRE começam com o sinal de igual. SEMPRE são executados por ordem de PRIORIDADE MATEMÁTICA, ou seja, multiplicação e divisão têm prioridade sobre soma e subtração. Cálculos contidos entre PARÊNTESES possuem prioridade sobre as demais regras. São executados DA ESQUERDA PARA A DIREITA. OPERADORES MATEMÁTICOS E OUTROS SINAIS SINAL OPERAÇÃO + Soma - Subtração * Multiplicação / Divisão ^ Exponencial OPERADORES LÓGICOS SINAL FUNÇÃO > Maior que < Menor que <> Diferente de >= Maior Igual a <= Menor Igual a = Igual a 7 FUNÇÕES Denomina-se função um cálculo matemático já pronto que o Excel possui para facilitar os cálculos dos usuários. Toda função começa com um sinal de igual, possui um comando e argumentos ou condições entre parênteses. Não pode haver espaços entre o sinal de igual, o nome da função, os parênteses e as condições. FUNÇÕES DE SOMA =SOMA( ) Permite somar um intervalo de células. Sintaxe: =SOMA(intervalo a ser somado) • Para definir um intervalo a ser somado, utiliza-se ; (ponto e vírgula) ou : (dois pontos) de acordo com os critérios abaixo: • Intervalo seguido (:) =SOMA(B2:D4) - Soma o intervalo entre B2 E D4, ou seja, considera as células B2, B3, B4, C2, C3, C4, D2, D3 e D4. • Intervalo não seguido (;) =SOMA(B2;D2). Soma as células B2 e D2 8 =SOMASE( ) Função que soma valores de uma determinada região da planilha, de acordo com um critério definido pelo usuário. Sintaxe: =SOMASE(intervalo do critério;critério;intervalo dos números a serem somados) Exemplo: Somar as faturas que já foram pagas em um determinado período. Obs.: O critério deve sempre ser colocado entre aspas. No exemplo acima, pedimos para que o Excel some todos os valores que estiverem dentro do intervalo de células B2 e B6, cujo campo situação esteja preenchido com o conteúdo “PG”. 9 =Somases( ) Função de soma de valores de uma determinada região da planilha, de acordo com um conjunto de critérios ou condições definidas pelo usuário. Sintaxe:=SOMASES(Intervalo_soma;intervalo_critérios1;critérios1; ...) Exemplo: descobrir o valor total das vendas de um determinado produto por um determinado vendedor. No exemplo acima, pedimos para que o Excel some no intervalo de B2 a B12 os valores cujo campo Produto (de C2 a C12) seja Smartphone e o campo Vendedor (de D2 a D12) seja referente a Carlos. O resultado será R$ 3.550,00, valor referente aos Smartphones vendidos por Carlos. 10 FUNÇÕES ESTATÍSTICAS =MÁXIMO( ) Retorna ao maior valor de um intervalo de células. Sintaxe: =MÁXIMO(intervalo a ser considerado o maior valor) Exemplo: Entre os jogadores de basquete de um time, qual seria o mais alto? =MÍN( ) Retorna ao menor valor de um intervalo de células. Sintaxe: =MÍN(intervalo a ser considerado o menor valor) Exemplo: Qual a criança que possui o menor peso? 11 =MÉDIA( ) Permite calcular a média aritmética de um intervalo de células. Sintaxe: =MEDIA(intervalo a ser extraída a média) Exemplo: Cálculo da média de idade dos Empregados de um departamento: =MED( ) Retorna a mediana dos números indicados. Sintaxe: =MED(intervalo a ser extraída a mediana) 12 Exemplo: Calcular a mediana da altura dos alunos de uma classe: Neste caso a mediana é de 1,68 metros. =MODO( ) Retoma o valor que mais se repete no intervalo informado, ou seja, a moda. Sintaxe: =MODO(intervalo a ser considerado para cálculo da moda) Exemplo: Qual seria a moda da seguinte lista de dados? 13 =CONT.SE( ) É utilizada para contar um determinado item dentro de uma tabela com diversos itens diferentes, baseando-se no critério dado. Sintaxe: =CONT.SE(intervalo que se deseja contar;critério) Exemplo: Supondo que após calcular a média dos alunos da classe você quisesse saber quantos alunos tiraram média superior a 7: =VAR.A( ) Calcula a variância com base numa amostra. Sintaxe: =VAR.A(intervalo a ser extraída a variância) Exemplo: A distribuição abaixo representa o consumo, em kg, de açúcar colocado em oferta em um supermercado, que limitou o consumo máximo por cliente em 5 kg. Calcule a variância: 14 Neste caso a variância é de 185,3. DESVPAD.A( ) Calcula o desvio-padrão com base numa amostra. Sintaxe: =DESVPAD.A(intervalo da amostra) Exemplo: Suponha que 10 ferramentas feitas na mesma máquina durante a produção são coletadas como uma amostra aleatória e avaliadas em termos de resistência à ruptura. Calcule o desvio padrão desta amostra: 15 =DESVQ( ) Retoma a soma dos quadrados dos desvios. Sintaxe: =DESVQ(intervalo com os desvios) Exemplo: Calcular a soma do quadrado dos seguintes desvios: 16 FUNÇÕES DE DATA E HORA =AGORA() Sintaxe: =AGORA() A função AGORA dá como resultado a data e hora atuais. É só digitar =AGORA() em uma célula qualquer que, ao apertar Enter, aparecerá a data e a hora. Confira abaixo: =HOJE() Sintaxe: =HOJE() A função HOJE dá como resultado a data atual armazenada no sistema. =MÊS( ) A função MÊS dá como resultado o número do mês da data passada como argumento. 17 Sintaxe: =MES(data) Exemplos: O argumento pode ser uma data que já exista na sua tabela. Como no exemplo abaixo: Outro exemplo interessante é usar a função AGORA como argumento da função MÊS. Ou seja, o resultado será o número do mês atual. Repare que como este texto foi escrito em junho, o resultado na imagem abaixo é 6 (pois junho é o sexto mês do ano). =DIA( ) A função DIA dá como resultado o dia da data passada como argumento. Sintaxe: =DIA(data) Exemplos: Com a data existente na tabela: 18 Combinada com a funções =AGORA(): =DIA.DA.SEMANA( ) A função DIA.DA.SEMANA dá como resultado o dia da semana da data passada como argumento. Esse resultado é expresso em forma de número. Sintaxe: =DIA.DA.SEMANA(data;[retornar tipo]) Por meio do argumento 'retornar tipo' você tem a opção de definir qual dia da semana será representado pelo número 1 e, assim, até o número 7. Essa opção não é obrigatória e, se não preenchida, o domingo será representado como dia 1. Exemplos: O argumento "data" pode ser uma data que já conste na sua tabela: Ou você pode utilizar a função para descobrir que dia da semana caiu uma data especial, como por exemplo, o dia em que você nasceu. 19 =ANO( ) A função ANO dá como resultado o ano da data passada como argumento. Sintaxe: =ANO(data) Exemplo: 20 FUNÇÕES LÓGICAS: =SE ( ) Função do tipo lógica, que permite solucionar um problema com duas possíveis soluções. Sintaxe: =SE(Teste Lógico;Valor Verdadeiro;Valor Falso) Teste Lógico: Condição proposta pelo usuário. Valor Verdadeiro: Valor que será exibido se a condição proposta pelo usuário for satisfeita. Valor Falso: Valor que será exibido se a condição proposta pelo usuário não for satisfeita. Exemplos: • Funções SE com uma condição: Suponhamos que desejasse criar um Controle de Notas de Aluno, onde ao se calcular a média, ele automaticamente especificasse se o aluno fora aprovado ou não. Então Veja o exemplo abaixo. Solução: Primeiramente, você precisa entender e definir o que deseja fazer dessa forma: Quero que no campo "SITUAÇÃO" o Excel escreva "Aprovado" somente se o aluno tirar uma nota Maior ou igual a 7 na média, caso contrário ele deverá escrever Reprovado, já que o aluno não atingiu a condição para passar. Veja como você deve escrever a fórmula utilizando a função do SE: 21 • Função SE com mais condições: Se ao invés de definir a situação do aluno como "Aprovado" e "Reprovado" você preferir definir a situação como "Boa", "Regular" ou "Ruim". Será necessário ter uma função SE encadeada. Acompanhe o raciocínio: - Se a nota for maior ou igual a 8, a situação é Boa - Senão, se a nota for maior ou igual a 5, a situação é Regular - Senão, o conceito é Ruim. Deste modo, a função no Excel ficaria assim: 22 FUNÇÕES DE BUSCA EM TABELAS =PROCV( ) Esta função realiza uma pesquisa vertical em uma tabela, ou seja, ela faz a busca de um determinado argumento usando como consulta a primeira linha da tabela. Sintaxe: =PROCV(valor_procurado; matriz_tabela; num_índice_lin; procurar_intervalo). Sendo: Valor_procurado - o argumento que deseja fornecer como base para a procura ser feita; Matriz_tabela - o intervalo onde se realizará a pesquisa; 23 Núm_índice_lin - a linha que se deseja obter o resultado, considerando que as linhas são contadas a partir do intervalo estipulado em matriz_tabela; Procurar_intervalo - a precisão da pesquisa, podendo ser FALSO para correspondências exatas, ou VERDADEIRO para correspondências aproximadas do valor desejado. A função PROCV pesquisaum valor da primeira linha de uma lista de dados especificado por você em valor procurado. Ela então procura o número de linhas que você determinou em num_linha e retorna o valor que encontrar lá. Exemplo 1 (Procurar_intervalo = FALSO): A tabela abaixo mostra a quantidade de vendas do 1º semestre de 2009 de quatro empresas famosas: Seu chefe quer que você consulte o total das vendas das empresas e ele deseja ter uma planilha automática. Como atender ao pedido do seu chefe? Através da função PROCV, realizamos uma pesquisa na tabela obtendo o valor de acordo com o campo desejado: 24 O campo “C11” será preenchido com o nome da empresa procurada, neste caso, será “Ponto Frio”. Para resolvermos o problema usaremos o campo “C12” e a seguinte função: Sendo: - Valor_procurado = C11 (Ponto Frio); - Matriz_tabela = B2:B9, ou seja, toda a área entre B2 a B9; - Núm_índice_lin = 8. Pois é o número da linha Total em nosso intervalo de pesquisa; 25 - Procurar_intervalo = FALSO. Porque estamos trabalhando com uma correspondência exata, a palavra Ponto Frio. O resultado será: Obs.: se quisermos procurar o Total para outra empresa, basta alterar o Valor da célula C11. Por exemplo, se alterarmos C11 para Marabraz, o valor da célula C12 se atualiza automaticamente. Exemplo 2 (Procurar_intervalo = VERDADEIRO): A tabela abaixo mostra como é feita a classificação por Série de um aluno numa escola infantil no momento da sua matrícula. Desejamos tornar automática a consulta sobre a Série, para nos retornar um Valor referente à Idade da criança. 26 Como o campo Idade em Matrícula de Novo Aluno pode ser preenchido com um valor que não esteja na Linha Idade da tabela de consulta, utilizaremos VERDADEIRO em Procurar_intervalo, pois assim faremos que o PROCV consiga nos retornar um resultado aproximado. Como no exemplo em que a Idade é de 5 anos: Sendo então: - Valor_procurado = B5 (a idade referente ao aluno); - Matriz_tabela = A1:E2, ou seja, toda a área entre A1 a E2; - Núm_índice_lin = 2. Pois é o número da linha Série em nosso intervalo de pesquisa; - Procurar_intervalo = VERDADEIRO. Porque estamos trabalhando com uma correspondência aproximada. O resultado será: 27 Outros exemplos: Temos abaixo duas tabelas, sendo a primeira um cadastro dos funcionários e seus respectivos cargos e a segunda usaremos como consulta para formular o PROCV do campo Descrição do Cargo Devemos encontrar a descrição de cada cargo de modo que a planilha seja automática. Usando o campo Código do Cargo como valor a ser procurado, utilizaremos a segunda tabela como intervalo onde será feita a pesquisa. Como precisão de pesquisa, utilizaremos FALSO, pois os códigos nos permitem correspondências exatas. Sendo assim, temos: 28 - Valor_procurado = B2 (o Código do Cargo); - Matriz_tabela = E1:F18, a área da nossa tabela de consulta; - Núm_índice_coluna = 2. Pois a coluna Descrição é a segunda em nosso intervalo de pesquisa; - Procurar_intervalo = FALSO. Porque estamos trabalhando com uma correspondência exata. O resultado será o seguinte: Para aplicarmos à mesma função as outras linhas, faremos os seguintes passos: 1º - Fixar a Tabela de Consulta: esse recurso serve para que a área de consulta não se mova ao usar o preencher. - Na fórmula, selecionamos a parte referente à Matriz_tabela; 29 - Então apertamos a tecla F4, a fórmula ficará assim: Basta clicar Enter e a área referente à tabela de consulta está fixada, agora podemos utilizar o recurso preencher. 2º - Preencher as demais células da coluna Descrição do Cargo: através desse recurso preencheremos com a função PROCV criada as demais células da coluna. - Selecionamos com um clique a célula onde fizemos o PROCV - Repare que existe um pequeno ponto no lado inferior direito da célula, clicando e arrastando-o para as demais células abaixo copiamos a fórmula criada para as outras linhas da planilha, automaticamente. 30 - Ao soltar o resultado será o seguinte: As células abaixo foram preenchidas corretamente. Exemplo 2 (Procurar_intervalo = VERDADEIRO): neste exemplo, vamos simular uma situação de vendas realizadas, onde se é concedido um desconto com base na quantidade comprada pelo cliente. Abaixo, temos duas tabelas, onde a primeira representa informações sobre vendas realizadas e a segunda o desconto referente à determinada quantidade. 31 Usando o campo Quantidade como valor a ser procurado, utilizaremos a tabela de Desconto para obter os índices a serem aplicados em cada situação. Neste caso, utilizaremos o parâmetro VERDADEIRO, pois as Quantidades de Vendas não coincidem exatamente com as Quantidades para cálculo de Desconto. Sendo assim, temos: 32 Onde: - Valor_procurado = D3 (Quantidade Vendida); - Matriz_tabela = C8:D11, a área da nossa tabela de consulta; - Núm_índice_coluna = 2. Pois a coluna Desconto é a segunda em nosso intervalo de pesquisa; - Procurar_intervalo = VERDADEIRO. Porque estamos trabalhando com uma correspondência aproximada. O Desconto concedido em uma venda de 7 unidades é de: Agora basta fixar a tabela de consulta e usar o preencher nas demais células da coluna Desconto Concedido para que ela fique assim: Obs.: Como foi vendida apenas uma unidade da Mesa de Vidro Temperado, não foi concedido desconto. 33 FUNÇÃO BANCO DE DADOS As funções Banco de Dados nos permitem analisar um conjunto de informações organizadas, filtrando os dados, a fim de termos uma noção da relação de um tipo de informação com outra. Por exemplo: numa lista de alunos, verificar quantos possuem mais de 25 anos e moram em São Paulo. =BDCONTARA( ) Esta função é utilizada para contar todas as células não vazias em uma coluna de uma lista ou banco de dados que coincidirem com as condições especificadas por você. Sintaxe: =BDCONTARA(banco_dados;campo;critérios) Onde: Banco_dados: É o intervalo de células que constitui a lista ou o banco de dados. A primeira linha da lista deve conter os rótulos de cada coluna. Campo: Indica a coluna que será usada na função. O campo pode ser dado como texto com o rótulo da coluna entre aspas, como "Idade" ou "Rendimento", ou como um número que represente a posição da coluna dentro da lista: 1 para a primeira coluna, 2 para a segunda coluna e assim por diante. Se você omitir o campo, BDCONTARA retornará uma contagem de todos os registros que atendam aos critérios. Se você incluir o campo, BDCONTARA retornará apenas os registros que contiverem o valor contido no campo e atendam aos critérios. 34 Critérios: É o intervalo de células que contém as condições especificadas. Você pode usar qualquer intervalo para o argumento de critérios, desde que ele inclua pelo menos um rótulo de coluna e ao menos uma célula abaixo do rótulo de coluna para especificar uma condição para a coluna. Exemplo 1: Supondo que tenhamos a seguinte tabela com os dados dos questionários de uma pesquisa feita com 10 mulheressobre a fragrância de um novo perfume: Como resolver a seguinte pergunta: Quantas mulheres gostaram do perfume e não o comprariam? Para responder a pergunta, você pode utilizar o BDCONTARA dessa forma: • Copia-se o cabeçalho da tabela • Digitam-se os critérios no novo cabeçalho, assim: 35 Após apertar Enter o BDCONTARA nos informa que 2 das mulheres entrevistadas gostaram da fragrância, porém não comprariam o novo perfume. Exemplo 2: temos a seguinte tabela que apresenta a porcentagem de acertos e o tipo de prova feita por um grupo de alunos. 36 Queremos descobrir quantos alunos conseguiram média final acima de 75%. Para responder a pergunta, você pode utilizar o BDCONTARA dessa forma: Após apertar Enter o BDCONTARA nos informa que 4 pessoas conseguiram uma porcentagem de acertos acima de 75%. 37 FUNÇÕES FINANCEIRAS As funções financeiras nos auxiliam a compreender os valores contidos nos cálculos de vendas à vista em relação aos preços praticados à prazo, financiamentos, entre outros. Algumas destas funções precisam ser incorporadas à função abaixo, para que o resultado em numero positivo. =ABS( ) Retorna ao módulo do número, ou seja, o valor absoluto sem o sinal. Sintaxe: =ABS(número) Exemplo: A função ABS será utilizada em todos os exemplos de funções financeiras, para que os valores dos exemplos sejam absolutos. LEGENDA PARA AS FUNÇÕES FINANCEIRAS: i = taxa de juros nper = número de períodos VF = valor futuro VP = valor presente pgto = pagamentos uniformes e periódicos VPL = valor presente líquido 38 Podemos perceber que as funções financeiras sempre apresentam os mesmos parâmetros, =NPER( ) Retorna o número de períodos de um investimento com base em pagamentos constantes periódicos e uma taxa de juros constante. Sintaxe: =NPER(taxa; pgto; VP; VF; tipo) Exemplo: Uma moto custa à vista R$ 3000,00. Você deseja adquirir esta moto através de um financiamento que cobra juros de 1% ao mês, porém você apenas pode pagar 110,00 pela parcela. Em quantas vezes você pode comprar a moto? =TAXA( ) Retorna a taxa de juros por período de um empréstimo ou investimento. Sintaxe: =TAXA(nper; pgto; VP; VF; tipo) Exemplo: No natal, você adquiriu uma TV de 20” em 10 parcelas. A parcela inicial era de R$ 87,00 e o total a prazo foi de R$ 2000,00. Qual foi a taxa de juros para cada parcela? 39 =PGTO( ) Calcula o pagamento de um empréstimo com base em pagamentos e em uma taxa de juros constante. Sintaxe: =PGTO(taxa; nper; VP; VF; tipo) Exemplo: Para comemorar seu aniversário, você deseja comprar um computador que vale R$ 2899,00. Após negociar com o vendedor, chegou a conclusão de parcelar a compra em 36 vezes, com juros de 1,9% ao mês. Qual será o valor da parcela inicial? =VF( ) Retorna o valor futuro (montante) de um investimento com base em pagamentos constantes e periódicos e uma taxa de juros constante. Sintaxe: =VF(taxa; nper; pgto; VP; tipo) Exemplo: Você deseja comprar um micro system em 10 prestações com juros de 2% ao mês. O vendedor disse que a parcela inicial era de R$ 38,96. Quanto será o total a prazo? 40 =VP( ) Retorna o valor presente (capital inicial) de um investimento: a quantia total atual de uma série de pagamentos futuros. Sintaxe: =VP(taxa; nper; pgto; VF; tipo) Exemplo: Você adquiriu uma câmera digital em 10 parcelas de R$45,00. Quando você comprou, disseram que sobre cada parcela seria embutido um juros de 2%. Quanto vale a câmera digital à vista? =VPL( ) Retorna o valor presente líquido atual de um investimento, com base em uma taxa de desconto “i” em uma série de pagamentos futuros (valores negativos) e renda (valores positivos). Sintaxe: =VPL(taxa; valor1; valor2; ...) 41 Exemplo: Uma empresa fez um investimento de R$ 10000,00 e obteve os seguintes retornos: 1º ano: R$ 3000,00 2º ano: R$ 4200,00 3º ano: R$ 6800,00 Com uma taxa de desconto anual de 10%, qual seria o valor presente líquido desse investimento? GRÁFICOS Um gráfico transmite uma mensagem rapidamente. Com um gráfico, é possível transformar os dados de uma planilha para mostrar comparações, padrões e tendências. Por exemplo, é possível mostrar instantaneamente se as vendas caíram ou aumentaram no presente trimestre. 42 Criar um gráfico no Excel 2010 Na figura acima, temos uma planilha mostrando a quantidade de aparelhos móveis que foram vendidos por quatro fabricantes nos anos de 2008 e 2009. É necessário um gráfico que compare o desempenho de cada marca durante os dois anos. 1. Para criar o gráfico, selecione os dados que deseja considerar, inclusive os títulos das colunas (2008, 2009) e os rótulos das linhas (as quatro fabricantes). 43 2. Em seguida, clique na guia Inserir e, no grupo Gráficos, clique no botão Colunas. Seria possível selecionar outro tipo de gráfico, mas os gráficos de colunas geralmente são usados para comparar itens e funcionarão bem para essa demonstração. 3. Depois de clicar em Colunas, será possível escolher entre vários tipos de gráficos de colunas. Clique em Colunas Agrupadas, o primeiro gráfico de colunas da lista Colunas 2D. Uma Caixa de Diálogo exibe o nome do tipo de gráfico ao posicionar o ponteiro do mouse sobre qualquer tipo de gráfico. A Caixa de Diálogo também fornece uma descrição do tipo de gráfico e informa quando cada tipo deve ser usado. Dica: Se quiser modificar o tipo do gráfico depois de criá-lo, clique dentro do gráfico. Na guia Design sob Ferramentas de Gráfico, no grupo Tipo, clique em Alterar Tipo de Gráfico e selecione outro tipo. 44 FERRAMENTAS DE GRÁFICO: Depois de criar um gráfico, é fácil adicionar novos elementos a ele. Por exemplo, é possível adicionar títulos para acrescentar informações ao gráfico ou mudar a disposição de seus elementos. Quando um gráfico é inserido na planilha, as Ferramentas de Gráfico, que incluem as guias Design, Layout e Formatar, aparecem. Essas guias contêm os comandos necessários para trabalhar com gráficos. Ao concluir o gráfico, clique fora dele. As Ferramentas de Gráfico desaparecerão. Para restaurá-las, clique dentro do gráfico. Isso fará as guias reaparecerem. Portanto, não se preocupe se alguns comandos não estiverem visíveis o tempo todo. Execute as etapas iniciais, inserindo um gráfico (usando o grupo Gráficos na guia Inserir) ou clicando dentro de um gráfico existente. Em seguida, os comandos necessários aparecerão. A altura das barras representa a quantidade vendida e a cor se refere ao ano que as mesmas representam. 45 GUIA “DESIGN” - Alterar modo de exibição do gráfico: É possível alterar a forma como o seu gráfico compara dados clicando em um botão para alternar de um modo de exibição de gráfico para outro. O gráfico acima compara as vendas de fabricantes de telefones celulares. Através do botão “ALTERNAR LINHA/COLUNA”podemos inverter a disposição dos dados em nosso gráfico. Clicando no botão, nosso gráfico ficará assim: 46 - Layout de gráfico: Através do grupo “Layout de Gráfico” alteraremos o layout geral do gráfico, podendo assim adicionar um título ao gráfico, mostrar a tabela que o gerou, diferentes formas de grade ou de disposição das informações. - Alterar a aparência do gráfico: 47 No grupo “Estilos de Gráfico”, podemos alterar o estilo visual geral do gráfico, escolhendo um dos estilos. Se clicarmos no botão “Mais” (em destaque no canto) aparecerá um menu com todos os estilos disponíveis no Excel 2010. 48 GUIA “LAYOUT” - Grupo Rótulos: Por meio do grupo “Rótulos” personalizamos de forma mais específica as partes que integram o gráfico, tais como o Título do Gráfico, Título dos Eixos, a Legenda, os Rótulos de Dados e a Tabela de Dados. Nesse grupo podemos escolher exibir ou não tais partes e modificar sua posição no gráfico. Exemplo: Com o gráfico abaixo realizaremos as seguintes alterações: 49 - Título do Gráfico – Acima do gráfico - Título do Eixo Horizontal Principal – Título abaixo do eixo - Título do Eixo Vertical Principal – Título girado - Legenda – Mostrar legenda na parte superior - Rótulos de Dados – Centro - Tabela de Dados – Mostrar tabela de dados Nosso gráfico ficará assim: O gráfico depois de alterado se apresenta mais informativo e completo. - Grupo Eixos: 50 Botão “Eixos” - usamos para alterar a formatação e o layout dos eixos horizontal e vertical do gráfico. Botão “Linhas de Grade” - nos permite ativar ou desativar as linhas de grade do gráfico. Exemplo: Utilizando o gráfico acima, faremos as seguintes alterações: Botão Eixos: - Eixo horizontal principal – mostrar eixo da Direita para a Esquerda - Eixo vertical principal – mostrar eixo em Milhares Botão Linhas de Grade: - Linhas de Grade Horizontais Principais – Linhas de grade principais e secundárias. - Linhas de Grade Verticais Principais – Linhas de grade principais e secundárias. 51 O nosso gráfico ficará assim: 52 GUIA “FORMATAR” - Estilos de Forma: No grupo “Estilos de Forma” escolhemos efeitos e estilos visuais do nosso gráfico, a cor de preenchimento e o contorno. - Estilos de WordArt: No grupo “Estilos de WordArt” você pode aplicar vários estilos visuais para qualquer texto do gráfico e editá-lo. 53 O gráfico acima teve seu visual alterado através das ferramentas da guia Formatar Estilos de Forma e WordArt. 54 ADICIONANDO O GRÁFICO A UM DOCUMENTO DO WORD OU APRESENTAÇÃO DO POWER POINT Quando o gráfico estiver exatamente com a aparência desejada, basta adicioná-lo a um documento do Word ou uma apresentação do PowerPoint para que todos possam vê-lo. Este é o procedimento: - Copie o gráfico no Excel. - Abra o Word ou o PowerPoint. - Cole o gráfico no documento ou slide desejado. Gráfico inserido em slide do Power Point O pacote Office permite a você alterar um gráfico feito no Excel que está inserido em um documento do Word ou Power Point. 55 Para isso, faça o seguinte procedimento: - Clique no gráfico - Em Ferramentas de Gráfico, vá na Guia “Design” - No grupo “Dados”, clique no botão “Editar Dados” O seu gráfico será aberto no Excel e você poderá editá-lo. Obs.: a edição dos dados de um gráfico inserido em um documento do Word ou Power Point apenas é possível se o arquivo do Excel que gerou o gráfico esteja no computador. 56 TABELAS DINÂMICAS Introdução aos relatórios de tabela dinâmica no Excel 2007 Sua planilha possui muitos dados, mas você sabe o que os números significam? Os seus dados respondem a todas as suas perguntas? Os relatórios de tabela dinâmica podem ajudá-lo a analisar os dados numéricos e responder a perguntas sobre eles. Em segundos, você pode ver quem vendeu mais, e onde. Consulte quais trimestres foram mais lucrativos e qual produto foi mais vendido. Faça perguntas, veja as respostas. Com os relatórios de tabela dinâmica, você pode ver as mesmas informações de formas diferentes com apenas alguns cliques do mouse. Os dados vão para os seus lugares, respondendo perguntas, informando- lhe o que os dados significam. Faça os seus dados trabalharem para você Figura 1 - Linhas e linhas de dados resumidos em um relatório de tabela dinâmica. 57 Imagine uma planilha do Excel de números de vendas com centenas ou milhares de linhas de dados. A planilha mostra todos os dados sobre os vendedores em dois países e quanto eles venderam em dias individuais. Mas é uma quantidade muito grande de dados para se lidar — listados linha após linha e divididos em várias colunas. Como você pode obter as informações da planilha? Como dar sentido a todos esses dados? Quem vendeu mais no geral? Quem vendeu mais por trimestre ou por ano? Que país possui mais vendas? Você pode obter respostas para todas essas perguntas com relatórios de tabela dinâmica — é como transformar uma multidão em uma banda de marcha. Um relatório de tabela dinâmica transforma todos os dados em relatórios pequenos, concisos, que o informam exatamente o que você precisa saber. Reveja os seus dados de origem Figura 2 - Uma planilha organizada do Excel. Antes de começar a trabalhar com um relatório de tabela dinâmica, dê uma olhada em sua planilha do Excel para garantir que ela está bem preparada para o relatório. 58 Quando você cria um relatório de tabela dinâmica, cada coluna dos dados de origem se torna um campo que você pode usar no relatório. Os campos resumem várias linhas de informações a partir dos dados de origem. Os nomes dos campos do relatório vêm dos títulos de coluna dos dados de origem. Verifique se você possui nomes para cada coluna na primeira linha da planilha nos dados de origem. Na imagem acima, os títulos de coluna País, Vendedor, Total do Pedido, Data do Pedido e Código do Pedido se tornam nomes de campo. Ao criar um relatório, o que fará na sessão prática no fim da lição, você saberá, por exemplo, que o campo Vendedor representa os dados do Vendedor na planilha. As linhas restantes abaixo dos títulos devem conter itens semelhantes na mesma coluna. Por exemplo, o texto deve estar em uma coluna, os números em outra coluna e as datas em outra coluna. Em outras palavras, uma coluna que contém números não deve conter texto, e assim por diante. Finalmente, não deve haver colunas vazias nos dados que você está usando no relatório de tabela dinâmica. Também recomendamos que não haja linhas vazias; por exemplo, linhas em branco que são usadas para separar um bloco de dados de outro devem ser removidas. 59 Para começar Figura 3 - A caixa de diálogo Criar Tabela Dinâmica. Quando os dados estiverem prontos, coloque o cursor em qualquer local nos dados. Isso incluirá todos os dados da planilha no relatório. Ou selecione apenasos dados que deseja usar no relatório. Em seguida, na guia Inserir, no grupo Tabelas, clique em Tabela Dinâmica e, em seguida, clique em Tabela Dinâmica novamente. A caixa de diálogo Criar Tabela Dinâmica é aberta. A opção Selecionar uma tabela ou intervalo já está selecionada para você. A caixa Tabela/Intervalo mostra o intervalo dos dados selecionados. A opção Nova Planilha também está selecionada para você como o local onde o relatório será colocado (você pode clicar em Planilha Existente se não quiser colocar o relatório em uma nova planilha). 60 Noções básicas sobre relatórios de tabela dinâmica 1. A área de layout do relatório de tabela dinâmica. 2. A Lista de Campos de Tabela Dinâmica. Isto é o que você vê na nova planilha depois de fechar a caixa de diálogo Criar Tabela Dinâmica. Em um lado está a área de layout pronta para receber o relatório de tabela dinâmica, e no outro lado está a Lista de Campos de Tabela Dinâmica. Essa lista mostra os títulos de coluna dos dados de origem. Como mencionado anteriormente, cada título é um campo: País, Vendedor, e assim por diante. Você cria um relatório de tabela dinâmica, movendo qualquer um dos campos para a área de layout do relatório de tabela dinâmica. É possível fazer isso marcando a caixa de seleção ao lado do nome do campo, ou clicando com o botão direito do mouse em um nome de campo e selecionando um local para o qual mover o campo. Se você trabalhou antes com relatórios de tabela dinâmica, pode estar se perguntando se ainda é possível arrastar campos para criar um relatório de tabela dinâmica. É possível, e você verá como fazê-lo no fim da lição. 61 Dica: se você clicar fora da área de layout (de um relatório de tabela dinâmica), a Lista de Campos de Tabela Dinâmica desaparece. Para ter a lista de campos de volta, clique dentro da área de layout ou do relatório de tabela dinâmica. Crie um relatório de tabela dinâmica Agora você está pronto para criar o relatório de tabela dinâmica. Os campos selecionados para o relatório dependem do que você deseja saber. Vamos começar descobrindo quanto cada vendedor vendeu. Para obter a resposta, você precisa de dados sobre os vendedores. Sendo assim, marque a caixa de seleção na Lista de Campos de Tabela Dinâmica ao lado do campo Vendedor. Você também precisa de dados sobre o quanto eles venderam, sendo assim, marque a caixa de seleção ao lado do campo Total do Pedido. Observe que você não precisa usar todos os campos da lista de campos para criar um relatório. Quando você seleciona um campo, o Excel coloca-o em uma área padrão do layout para você. É possível mover o campo para outra área, se você quiser. Por exemplo, se desejar que um campo fique na área de coluna em vez de na área de linha. Você verá como fazê-lo na sessão prática. Os dados no campo Vendedor (os nomes dos vendedores), que não contêm números, são automaticamente exibidos como linhas no lado esquerdo do relatório. Os dados, no campo Valor do Pedido, que não contêm números, são mostrados corretamente na área à direita. O título sobre os dados do vendedor diz "Rótulos de Linha" acima do campo. O título sobre os totais do pedido dizem "Soma de Valor do Pedido"; a parte "Soma de" do título é porque o Excel usa a função Soma para somar campos com números. Observe que não importa se você marca a caixa de seleção ao lado do campo Vendedor antes ou depois do campo Valor do Pedido. O Excel sempre os colocará automaticamente no local correto. Os campos 62 sem números ficarão à esquerda, os campos com números ficarão à direita, independentemente da ordem em que você selecioná-los. É isso. Com apenas dois cliques do mouse você sabe quanto cada vendedor vendeu. A propósito, você poderia parar com apenas uma pergunta respondida. Você pode usar um relatório de tabela dinâmica como uma forma rápida de obter a resposta para apenas uma ou duas perguntas. O relatório não precisa ser complexo para ser útil. Dica: não se preocupe se você criar um relatório incorretamente. O Excel torna mais fácil fazer experiências para ver como os dados aparecem em áreas diferentes do relatório. Se um relatório não estiver como você queria, não leva muito tempo para você colocar os dados de outra forma, para mover as peças de acordo com a sua satisfação, ou mesmo para começar do início, se você quiser. Consulte vendas por país Agora você sabe o total vendido por cada vendedor. Mas os dados de origem mostram dados sobre vendedores em dois países, o Reino Unido (RU) e os Estados Unidos da América (EUA). Sendo assim, outra pergunta que você poderia fazer seria: quais são os totais vendidos por cada vendedor por país? Para obter a resposta, adicione o campo País ao relatório de tabela dinâmica como um filtro de relatório. Use um filtro de relatório para focalizar um subconjunto de dados no relatório, geralmente uma linha de produto, uma duração ou uma região geográfica. Usando o campo País como um filtro de relatório, você pode ver um relatório separado para o RU ou os EUA, ou pode ver as vendas para ambos os países juntos. Para adicionar este campo como um filtro de relatório, clique com o botão direito do mouse no campo País na Lista de Campos de Tabela Dinâmica e clique em Adicionar ao Filtro de Relatório. O novo filtro de relatório País é adicionado ao topo do relatório. A seta ao lado do campo 63 País mostra (Todos), e você vê os dados de ambos os países. Para ver apenas os dados de RU ou EUA, clique na seta e seleciona um dos países. Para ver os dados de ambos os países novamente, clique na seta e, em seguida, clique em (Todos). Dica: para remover um campo de um relatório, limpe a caixa de seleção ao lado do nome do campo na Lista de Campos de Tabela Dinâmica. Para remover todos os campos do relatório de forma que você possa começar outra vez, na Faixa de Opções, na guia Opções, no grupo Ações, clique na seta do botão Limpar e selecione Limpar Tudo. Consulte as vendas por data Os dados de origem inicial possuem uma coluna de informações sobre Data do Pedido, sendo assim, existe um campo Data do Pedido na Lista de Campos de Tabela Dinâmica. Isso significa que você pode obter a resposta para outra pergunta: quais são as vendas por data de cada vendedor? Para obter a resposta, marque a caixa de seleção ao lado do campo Data do Pedido para adicionar o campo ao relatório. O campo Data do Pedido é automaticamente adicionado à esquerda, na orientação do rótulo da linha. Isso acontece porque o campo não contém números (datas podem parecer números, mas elas são formatadas como datas, não como números). Como o campo Data do Pedido é o segundo campo não numérico a ser adicionado ao relatório, ele é aninhado no campo Vendedor, recuado à direita. Agora o relatório mostra as vendas de cada vendedor por data individual, mas há uma enorme quantidade de dados para ver de uma só vez. Você pode facilmente obter esses dados em um modo de exibição mais gerenciável, agrupando os dados diariamente em meses, trimestres ou anos. Para agrupar os dados, clique em uma data no relatório. Em seguida, na guia Opções, no Grupo, clique em Agrupar Campo. Na caixa de diálogo Agrupamento, selecione Trimestres, o que parece uma boa solução neste caso e clique em OK. 64 Agora você vê os dados de vendas agrupados em quatro trimestres por cada vendedor. Dinamize o relatório Embora o relatório de tabela dinâmica tenha respondido às suasperguntas, dá um pouco de trabalho ler o relatório inteiro; é preciso rolar para baixo na página para ver todos os dados. Você pode dinamizar o relatório para obter um modo de exibição diferente. Para fazê-lo, mova um campo da área Rótulos de Linha para a área de coluna do relatório (chamado Rótulos de Coluna), que está em uma área do layout que você ainda não usou. Ao dinamizar um relatório, você transpõe o modo de exibição vertical ou horizontal de um campo, movendo as linhas para a área de coluna ou movendo as colunas para a área de linha. É fácil fazê-lo. Para dinamizar o relatório, clique com o botão direito do mouse em uma das linhas "Tri", aponte para Mover e clique em Mover "Data do Pedido" para Colunas. Isso moverá o campo Data do Pedido inteiro da área Rótulo de Linha para a área Rótulo de Coluna do relatório. Agora os nomes dos vendedores estão juntos, e ele diz Rótulos de Coluna acima do primeiro trimestre dos dados de vendas, que estão dispostos em colunas no relatório. Além disso, os totais gerais de cada trimestre estão no fim de cada coluna. Em vez de rolar para baixo a página para ver os dados, você pode vê-los de uma só vez. Se você quiser dinamizar o relatório de volta para o modo de exibição original, clique com o botão direito do mouse em um dos títulos "Tri" e selecione Mover "Data do Pedido" para Linhas. Isso moverá o campo Data do Pedido de volta para a área de linha do relatório. 65 Para onde foi o recurso de arrastar e soltar? Como mencionado anteriormente, se você preferir criar um relatório de tabela dinâmica usando o método arrastar e soltar, como podia fazer em versões anteriores do Excel, ainda há uma forma de fazê-lo. Existem quatro caixas na parte inferior da Lista de Campos de Tabela Dinâmica. As caixas se chamam Filtro de Relatório, Rótulos de Linha, Rótulos de Coluna e Valores. Você pode arrastar os campos para essa área. Normalmente, no Excel 2007, você adiciona os campos ao layout do relatório a partir da lista de campos, marcando as caixas de seleção ao lado dos nomes de campo, ou clicando com o botão direito do mouse no campo e selecionando um local a partir de um menu. Conforme você faz isso, os campos são automaticamente colocados no layout e eles são simultaneamente colocados na caixa apropriada na parte inferior da lista. Por exemplo, como o campo Vendedor é colocado na área Rótulos de Linha do layout do relatório, o nome do campo Vendedor também é exibido na caixa Rótulos de Linha na parte inferior da lista de campos. Mas se você preferir o método arrastar e soltar basta arrastar os campos do topo da lista de campos para as caixas na parte inferior da lista de campos. O rótulo no topo de cada caixa informa para qual área do relatório o campo deve ir. Por exemplo, se você arrastar o campo Total do Pedido para a caixa Valores, esse campo ficará na área Valores do relatório. Você também pode arrastar o campo entre as caixas para alterar os locais na área de layout do relatório, e pode arrastar os campos para fora das caixas para removê-los do relatório. 66 Tabela dinâmica II Filtrar dados do relatório de tabela dinâmica no Excel 2007 O relatório de tabela dinâmica ajuda você a desvendar o significado dos dados. Você pode tornar o relatório ainda mais claro filtrando seus dados. A filtragem mostra exatamente o que você deseja e oculta o restante dos dados temporariamente. Consulte apenas os dados que você deseja exibir nos relatórios de tabela dinâmica Figura 4 - Relatório de tabela dinâmica antes da filtragem. Imagine que você abriu um relatório de tabela dinâmica para produtos vendidos pela Adventure Works, uma empresa de artigos esportivos. O relatório resume perfeitamente mais de 6.000 linhas de dados de planilha do Excel, especificando o que significa cada um deles. Você poderá ver, por exemplo, os totais anuais de vendas de cada produto por 67 um período de três anos, além de um total geral das vendas de cada produto. Agora, você precisa ver detalhes específicos. A Adventure Works vende produtos que variam de garrafas d'água a bicicletas de estrada e roupas. Você deseja ver os totais de vendas somente das bicicletas de estrada e quais delas são mais vendidas. Você deseja saber também como as bicicletas de estrada selecionadas são vendidas durante um determinado período. Você poderá ver exatamente o que deseja filtrando os dados do relatório de tabela dinâmica. Filtrar para exibir um produto entre vários Você precisa ver somente os dados de vendas das bicicletas de estrada e ocultar todos os outros dados. Para filtrar o relatório, clique na seta ao lado de Rótulos de Linha. Você deve clicar nessa seta porque as bicicletas de estrada estão na área de linha do relatório. Ao clicar na seta, será exibido um menu com a caixa Selecionar campo na parte superior para que você use ao selecionar o local onde aplicará o filtro. Esse menu exibirá uma lista com todas as linhas de qualquer campo selecionado. Observar os itens na lista é uma forma de verificar se você selecionou o campo correto a ser filtrado. Nesse exemplo, o campo Categoria do Produto, que inclui Bicicletas de Estrada, é o que você deseja. Para filtrar o relatório, desmarque a caixa de seleção (Selecionar Tudo) na lista. Isso desmarcará as caixas de seleção ao lado de cada item da lista. Em seguida, marque a caixa de seleção ao lado de Bicicletas de Estrada. Agora, o relatório de tabela dinâmica mostra somente os dados sobre bicicletas de estrada. Os outros dados não são alterados, mas não aparecem no momento. Clique em Executar para ver o processo de aplicação de um filtro ao campo Categoria do Produto. 68 Dica: nem sempre é fácil determinar se os dados foram ou não filtrados simplesmente pela sua aparência. Para lembrá-lo de que o relatório é filtrado, é exibido um ícone de filtro na seta em que você clicou para iniciar a configuração do filtro: . Será exibido também um ícone de filtro na lista de campo de tabela dinâmica ao lado do nome de campo ao qual o filtro é aplicado. Restrinja o filtro para ver um único item Você agora definiu um filtro e o relatório mostra somente os totais de vendas das bicicletas de estrada. No entanto, a Adventure Works vende vários tipos diferentes de bicicletas de estrada e você precisa ver os totais de vendas apenas do tipo Road-350-W. Filtre o relatório para um único tipo usando o método mostrado na última seção. Na caixa Selecionar campo, escolha Nome do Produto, em vez de Categoria do Produto. Na lista de nomes de produto, marque as caixas de seleção da bicicleta Road-350-W. No entanto, há outro método que pode economizar o seu tempo: a refinação do filtro já definido. No relatório filtrado, selecione as células que possuem os dados da bicicleta Road-350-W. Em seguida, clique com o botão direito do mouse, aponte para Filtrar e clique em Manter Somente Itens Selecionados. Agora somente os dados da bicicleta Road-350-W serão exibidos. O novo filtro ocultará todos os outros tipos de bicicleta de estrada e os dados anteriores do relatório. Defina um filtro de valor para exibir produtos especificando totais Suponhamos que você deseje saber quais modelos de bicicleta de estrada apresentam totais de vendas iguais ou superiores a R$ 100.000. 69 Como solicitar que o Excel selecione essas linhas e oculte as outras? Use um filtro de valor. Primeiro aplique o filtro de modo a exibir somente as bicicletas de estrada, usandoo método mostrado na primeira animação. Em seguida, defina o filtro de valor, clique na seta do ícone de filtro ao lado de Rótulos de Linha. Na caixa Selecionar campo, escolha Nome do Produto. Você filtrará o campo Nome do Produto para ver cada modelo de bicicleta de estrada. Em seguida, aponte para Filtros de Valor. Esse filtro lerá os dados e selecionará as linhas com células que correspondam aos critérios. Clique em Maior ou Igual a e, na caixa de diálogo Filtro de Valor, digite 100.000 na caixa vazia. O relatório contém 38 modelos de bicicleta de estrada, das quais 13 apresentam um total de vendas igual ou superior a R$ 100.000. Somente essas serão exibidas agora. Para alterar o valor em reais utilizado no filtro, aponte para Filtros de Valor e repita o processo com outro valor. Você pode imaginar como esse tipo de filtragem lhe permitirá analisar os dados das mais variadas formas com um esforço mínimo se comparado aos outros métodos. Defina um filtro de data para exibir dados de um período selecionado Por fim, suponhamos que você deseje saber como foram as vendas de bicicletas de estrada durante um determinado ano, mês ou outro período. Ao definir um filtro, você pode informar ao relatório qual é o período de seu interesse e ocultar temporariamente os dados de todos os outros períodos. Filtrar os dados de um ano específico é fácil. Para ver os dados somente de 2011, clique na seta ao lado de Rótulos de Coluna. Você clicará nessa seta porque as datas são exibidas na área de coluna do relatório. Na lista exibida, desmarque a caixa de seleção ao lado de (Selecionar Tudo) e marque a caixa de seleção ao lado de 2011. O Excel ocultará os anos 2009 e 2010. É muito simples. 70 Depois, digamos que você precise saber como foram as vendas de bicicletas de estrada durante um determinado mês de 2011. Para especificar um período para o filtro de data, clique na seta ao lado de Rótulos de Coluna. Aponte para Filtros de Data. Clique em Entre. Na caixa de diálogo Filtro de Data, digite 8/11/2011 na primeira caixa vazia. Na caixa e, digite 8/12/2011. O relatório exibirá os dados sobre bicicletas referentes somente ao mês especificado. Remover filtros 1. Removendo um filtro do relatório de tabela dinâmica clicando no ícone de filtro e, em seguida, clicando em Limpar Filtro de "Categoria do Produto". 2. Removendo um filtro da Lista de Campos da Tabela Dinâmica movendo o cursor sobre o ícone de filtro ao lado do nome do campo, clicando na seta exibida e, em seguida, clicando em Limpar Filtro de "Categoria do Produto". Deseja exibir os dados ocultos novamente para ter uma visão geral? Você pode remover os filtros um de cada vez ou pode remover rapidamente todos os filtros de uma só vez. 71 A chave para remover os filtros um de cada vez é usar o ícone de filtro, que aparecerá em dois lugares diferentes: no relatório de tabela dinâmica e na lista de campos da tabela dinâmica . Para remover todos os filtros simultaneamente, use os comandos da Faixa de Opções na parte superior da janela. Remover um filtro do relatório de tabela dinâmica Para remover um filtro de um campo específico, clique no ícone de filtro no local do relatório em que aparece o campo com filtro, seja em Rótulos de Linha ou em Rótulos de Coluna, e clique em Limpar Filtro de <Nome do Campo>. Outra alternativa é marcar caixa de seleção ao lado de (Selecionar Tudo) para deixar todos os dados desse campo visível. Se o comando Limpar Filtro de do campo com filtro não for exibido: Verifique se você selecionou a área correta do relatório para limpar o filtro das linhas ou colunas. A caixa Selecionar campo está preenchida com o nome de campo correto? Essa caixa é exibida quando você clica no ícone de filtro. O nome de campo dessa caixa deve corresponder ao nome do campo cujo filtro você deseja remover. Se o nome do campo estiver incorreto na caixa, selecione o campo correto na lista exibida quando você clica na seta ao lado da caixa. Remover um filtro do relatório da lista de campos da tabela dinâmica Mova o cursor sobre o ícone de filtro ao lado do nome de campo cujo filtro será removido. Clique na seta exibida e, em seguida, clique em Limpar filtro de <Nome do Campo>. Outra alternativa é marcar a caixa de seleção ao lado de (Selecionar Tudo) para deixar todos os dados do campo visíveis. 72 Remover todos os filtros simultaneamente Na parte superior da janela, na Faixa de Opções, clique na guia Opções em Ferramentas de Tabela Dinâmica. No grupo Ações, clique em Limpar e, em seguida, clique em Limpar Filtros. Verifique se você realmente deseja fazer isso antes de desfazer todas as seleções. 73 Tabela dinâmica III Calcular dados dos relatórios de tabela dinâmica no Excel O Excel soma automaticamente os números nos relatórios de tabela dinâmica usando SOMA, que é uma função de resumo. É possível usar outras funções de resumo para calcular os números de várias formas: por exemplo, para obter a média ou fazer uma contagem. Você também pode mostrar valores como um percentual do total ou criar uma soma acumulada, usando um cálculo personalizado. Você pode, ainda, criar suas próprias fórmulas nos relatórios de tabela dinâmica. Trabalhar com números Figura 5 - Dados de vendas resumidos em um relatório de tabela dinâmica. É época de pagamento de comissões na Contoso Ltd., uma distribuidora de alimentos. Imagine que você abriu um relatório de tabela dinâmica contendo dados sobre o vendedor que trabalha para a empresa. 74 O relatório resume 800 linhas de dados da planilha do Excel. Ele mostra a soma das vendas de cada vendedor no decorrer do ano passado, trimestre por trimestre. Agora, você usará o relatório para analisar os números de diversas maneiras. Você precisa saber o número de vendas efetuado por cada pessoa neste ano e quanto cada vendedor contribuiu para o total de vendas da empresa. Quando fizer isso, você conseguirá estimar quem receberá comissões e quanto valerá cada uma delas. Resumir os dados de outra maneira Você deseja alterar o relatório: em vez de adicionar os valores de vendas, deseja realizar a contagem do número de vendas efetuado por cada vendedor no decorrer do ano. Para fazer isso, altere de SOMA para CONTAGEM a função de resumo utilizada na área Valores do relatório. Para alterar a função, clique com o botão direito do mouse em qualquer lugar da área Valores do relatório, que é a área localizada abaixo do título "Soma do Valor de Vendas". Aponte para Resumir Dados por e clique em Contagem. Os números alternarão de uma soma para uma contagem dos valores. O título acima dos números mudarão de "Soma do Valor de Vendas" para "Contagem do Valor de Vendas". Em seguida, você poderá classificar os pedidos para ver melhor quem foi responsável pela maior parte dos pedidos. Para fazer isso, clique com o botão direito do mouse no subtotal de qualquer vendedor, aponte para Classificar e clique em Classificar do Maior para o Menor. Peacock é a primeira, com um total geral de 100 pedidos, seguida por King com 74 e Leverling com 73. Dica Para retornar à soma dos pedidos, clique com o botão direito do mouse na área Valores novamente, aponte para Resumir Dados por e clique em Soma. 75 Efetuar um cálculo personalizado Agora, você precisa ver o percentual do total geral de vendas efetuado por cada vendedor. Peacock foi responsável pela maior parte dospedidos, mas o número de pedidos que ela vendeu também resultou no percentual mais alto do valor total de vendas? Para descobrir isso, use um cálculo personalizado. Dessa forma, você poderá executar tarefas como criar uma soma acumulada ou calcular o percentual de vendas de cada vendedor comparado ao total geral de todas as vendas. Clique com o botão direito do mouse na área Valores. Aponte para Resumir Dados por e clique em Mais opções. Clique na guia Mostrar valores como da caixa de diálogo Configurações do Campo de Valor exibida. Por fim, na caixa Mostrar valores como, clique na seta e selecione % do total. Peacock obteve 18% do total de vendas do ano, que é mais do que o obtido por qualquer outro vendedor. Portanto, nesse caso, o maior percentual de total de vendas não corresponde ao número mais alto de pedidos vendidos. King está em segundo lugar com 14,42%, seguido por Davolio com 12,52. Dica: para retornar os valores a um modo de exibição normal, siga as mesmas etapas e clique em Normal. Isso desativará o cálculo personalizado. Quem obterá um bônus? Agora, você determinará quem obterá um bônus e quanto ele valerá criando uma fórmula através de um campo calculado. As fórmulas criadas através de um campo calculado podem se basear em qualquer campo a partir do qual o relatório foi criado. Quando você usa um campo calculado, um novo campo é adicionado ao relatório de tabela dinâmica. Digamos que cada vendedor que venda mais de R$ 30.000 em qualquer trimestre receba um bônus de 3% nesse trimestre. 76 Para criar a fórmula, na parte superior da janela, na Faixa de Opções, clique na guia Opções em Ferramentas de Tabela Dinâmica. No grupo Ferramentas, clique na seta do botão Fórmulas e, em seguida, clique em Campo Calculado. Na caixa de diálogo Inserir Campo Calculado, digite um nome para a fórmula na caixa Nome. Na caixa Fórmula, digite a fórmula para descobrir quem obterá um bônus: ='Valor de Vendas' * SE('Valor de Vendas'>30000,3%). Por fim, clique em OK. A fórmula diz que, se o valor de vendas for superior a 30.000 em um trimestre, o bônus será de 3%, que será multiplicado pelo Valor de Vendas. Se o valor de vendas de um trimestre for inferior a 30.000, não haverá bônus nesse trimestre. Um novo campo, Soma de Bônus R$, é inserido no relatório, que mostrará os bônus que cada vendedor receberá. Você provavelmente observará um detalhe. Um bônus adicional de 3% será listado nas linhas de subtotal para o vendedor. Por quê? O Excel executa a fórmula de campo calculado linha por linha. Em cada linha de subtotal, na qual o total seja superior a 30.000, a fórmula funcionará da mesma maneira que nas outras linhas. A fórmula estará matematicamente correta na linha de subtotal, mas não dará a resposta correta. Qual é a solução? Desative os subtotais automáticos para que o relatório mostre somente o valor de cada trimestre, sem os subtotais de cada vendedor. Você verá como fazer isso na prática. 77 SOLVER A ferramenta Solver do Excel é bastante utilizada para a resolução de problemas de programação linear. Para ativá-la siga as instruções: arquivo; opções; suplementos; solver; ir; solver; ok. A ferramenta solver será ativada na barra de menu Dados. Considere o seguinte problema de programação linear. Exemplo: O diretor de uma rede de televisão local descobriu que o programa A com 20 minutos de música, 15 minutos de esporte e 1 minuto de propaganda atualmente chama a atenção de 30 000 telespectadores, enquanto o programa B, com 10 minutos de música, 20 minutos de esporte e minuto de propaganda chama a atenção de 10 000 telespectadores. No decorrer de uma semana, o patrocinador insiste no uso de no mínimo, 5 minutos para a sua propaganda e que não há verba para mais de 80 minutos de música e 100 minutos de esporte. Quantas vezes por semana, cada programa deverá ser levado ao ar para obter o número máximo de telespectadores? Solução: Primeiramente, devemos fazer a modelagem matemática do problema da seguinte maneira: Maximizar z = 21 0001000030 xx Sujeito a : 0;0 511 1002015 801020 21 21 21 21 xx xx xx xx Devemos designar uma célula da planilha do Excel para representar cada um dos itens da modelagem. 78 B5 representará o valor da função objetivo a ser maximizada; B4 e C4 representarão os valores que as variáveis de decisão terão na solução; B3 e C3 representam os coeficientes de x1 e x2, respectivamente, da função objetivo; B9 até C11 representam os coeficientes de x1 e x2, respectivamente, das três restrições; D9 até D11 representarão o lado esquerdo (LHS left hand side) das três restrições do problema; E9 até E11 representam o lado direito (RHS right hand side) das três restrições do problema. 79 Fórmulas utilizadas Célula Fórmula B5 =B3*B4+C3*C4 D9 =B9*$B$4+C9*$C$4 D10 =B10*$B$4+C10*$C$4 D11 =B11*$B$4+C11*$C$4 Agora, precisamos informar ao Excel quais células representam a função objetivo, as variáveis de decisão e as restrições do problema. Clique em dados e, na sequência solver. Aparecerá a seguinte janela. Vamos inserir os dados nessa janela. “Definir objetivo”: clique sobre B5 ($B$5); 80 Escolha “Máx”; “Alterando Células Variáveis”: clique sobre B4 e arraste até C4 ($B$4:$C$4); Clique em “Tornar Variáveis Irrestritas Não Negativas” para que a condição de não negatividade seja satisfeita. Em “Selecionar um Método de Solução”, escolha “LP Simplex” para problemas lineares. Clicando em Adicionar aparecerá, para que sejam inseridas as restrições, a seguinte janela. 81 Para a primeira restrição: “Referência de Célula”: clique sobre D9 (=$D$9); Escolha o sinal de “<=”; “Restrição”: clique sobre E9 (=$E$9). Repita o procedimento para a segunda e a terceira restrições com atenção ao sinal da desigualdade. Depois de inseridas todas as restrições do problema, clique em OK. A janela inicial reaparecerá da seguinte maneira. 82 Clique em Resolver e aparecerá a seguinte janela. 83 Clique em OK e o resultado será automaticamente exibido na planilha do Excel. 84 Os resultados que podem ser visualizados diretamente na planilha são x1 = 3, x2 = 2 e z = 110 000, isto é, respondendo à pergunta do problema, exibindo o programa A três vezes na semana e o programa B duas vezes na semana, o número total de telespectadores será de 110 000. Observe ainda que os recursos utilizados foram 80 minutos de música, 85 minutos de esporte e 5 minutos de propaganda (vide em LHS). Relatórios do Solver Na janela “Resultados do Solver”, que aparece logo após clicarmos sobre Resolver na janela inicial, marque em “Relatórios” os itens Resposta, Sensibilidade e Limites. Esses relatórios aparecerão em três abas no canto inferior esquerdo da planilha do Excel. Relatório de Resposta 85 Podemos encontrar em “Valor Final” os valores da função objetivo (110 000), x1 (3), x2 (2). Em “Status”, aparece “Associação” se o recurso utilizado coincide com o total disponível da respectiva restrição, isto é, satisfaz a igualdade; já aparecerá “Não Associação” caso haja diferença entre o total de recurso disponívele o utilizado. Essa diferença pode ser visualizada em “Margem de Atraso”. Relatório de Limites Nesse relatório podemos visualizar o valor final da função objetivo (110 000) e os limites inferior e superior de cada variável caso as outras sejam fixas. Por exemplo, o limite inferior para x1 é 3, caso x2 se mantenha fixa com valor 2, resultando em 110 000 para a função objetivo. 86 Relatório de sensibilidade Nesse relatório, pode-se observar que o coeficiente de x1 é 30 000, podendo ser aumentado de 1E+30 = 1e30 e diminuído de 10 000 unidades, isto é, se o coeficiente de x1 estiver no intervalo de otimalidade que vai de 20 000 (= 30 000 – 10 000) até 30 000 + e30, os valores finais para x1 e x2 não sofrerão alteração, desde que o coeficiente de x2 permaneça constante igual a 10 000. O raciocínio análogo se aplica à variável x2. A restrição “minuto de música” indica que o tempo total disponível é 80 minutos, podendo a este tempo, ser acrescido 20 minutos e diminuído 30 minutos, isto é, se o tempo total de propaganda estiver entre 50 e 100 minutos, os valores finais para x1 e x2 não sofrerão alteração. O raciocínio análogo se aplica às demais restrições. O “Valor Final” representa, na solução ótima, os valores para x1 = 3 e x2 = 2, já para cada uma das restrições, representa a quantidade total consumida do respectivo recurso, por exemplo, tínhamos disponíveis 100 minutos de esporte mas foram utilizados apenas 85 minutos. 87 O “preço-sombra” representa a quantidade pela qual a função objetivo se altera dado um aumento de uma unidade na constante da respectiva restrição, desde que todos os outros coeficientes e constantes permaneçam inalterados, isto é, economicamente representa até quanto estaríamos dispostos a pagar por uma unidade adicional desse recurso. Por exemplo, cada minuto adicional de música gera um acréscimo de 2 000 telespectadores na função objetivo. Observe que esse acréscimo de minutos de música está limitado superiormente em 20 minutos, para que se mantenha a solução final. Agora, cada minuto de propaganda acrescido gera uma diminuição da função objetivo em 10 000 telespectadores. O “custo reduzido” é a quantidade que devemos acrescentar ao coeficiente da função objetivo para que a respectiva variável se torne básica, isto é, tenha valor positivo na solução final. Nesse exemplo, o custo reduzido de x1 e x2 é igual a zero, pois na solução final as duas variáveis têm valores positivos (x1 = 3 e x2 = 2). Exercício Um alfaiate tem disponíveis os seguintes tecidos: 16 metros de algodão, 11 metros de seda e 15 metros de lã. Para um terno são necessários 2 metros de algodão, 1 metro de seda e 1 metro de lã. Para um vestido, são necessários 1 metro de algodão, 2 metros de seda e 3 metros de lã. Se um terno é vendido por R$ 300,00 e um vestido por R$ 500,00, responda: a) Quantas peças de cada tipo o alfaiate deve fazer, de modo a maximizar o seu lucro? b) Qual é o lucro máximo obtido? c) Qual a quantidade de cada tecido que será utilizada? d) Qual é a sobra de cada tecido? e) Qual é o intervalo de otimalidade dos coeficientes da função objetivo? f) Qual é o valor de 10 unidades adicionais de algodão? g) Qual é o preço sombra para a restrição de seda? h) Em quanto aumenta o lucro do alfaiate se adquirir mais 5 unidades de lã? 88 i) Qual é o custo reduzido para a variável que representa a quantidade de ternos que deverão ser confeccionados? Macros Para automatizar uma tarefa repetitiva, você pode gravar uma macro rapidamente no Microsoft Office Excel. Você também pode criar uma macro usando o Editor do Visual Basic, no Microsoft Visual Basic, para gravar o seu próprio script de macro ou para copiar toda a macro, ou parte dela, para uma nova. Após criar uma macro, você poderá atribuí-la a um objeto (como um botão da barra de ferramentas, um elemento gráfico ou um controle) para poder executá-la clicando no objeto. Se não precisar mais usar a macro, você poderá excluí-la. Gravar uma Macro Quando você grava uma macro, o gravador de macro grava todas as etapas necessárias para concluir as ações a serem executadas por essa macro. Observação: a Faixa de Opções é um componente da Interface de usuário Microsoft Office Fluent. 1. Se a guia Desenvolvedor não estiver disponível, faça o seguinte para exibi-la: 1.1. Clique no Botão Microsoft Office e, em seguida, clique em Opções do Excel. 1.2. Na categoria Popular, em Opções principais para o trabalho com o Excel, marque a caixa de seleção Mostrar guia Desenvolvedor na Faixa de Opções e clique em OK. 89 2. Para definir o nível de segurança temporariamente e habilitar todas as macros, faça o seguinte: 2.1. Na guia Desenvolvedor, no grupo Código, clique em Segurança de Macro. 2.2. Em Configurações de Macro, clique em Habilitar todas as macros (não recomendável; códigos possivelmente perigosos podem ser executados) e em OK. Observação: para ajudar a impedir a execução de códigos potencialmente perigosos, convém retornar para qualquer uma das configurações que desabilitam todas as macros depois de terminar de trabalhar com macros. Para obter mais informações sobre como alterar as configurações, consulte Alterar as configurações de segurança de macros no Excel. 3. Na guia Desenvolvedor, no grupo Código, clique em Gravar Macro. 3.1. Na caixa Nome da macro, insira um nome para a macro. Observação: o primeiro caractere do nome da macro deve ser uma letra. Os demais caracteres podem ser letras, números ou caracteres sublinhados. Espaços não podem ser usados em um nome de macro; um caractere sublinhado funciona da mesma forma que um separador de palavras. Se usar um nome de macro que também seja uma referência de célula, você poderá receber uma mensagem de erro informando que o nome da macro é inválido. 90 3.2. Para atribuir uma tecla de atalho de combinação com CTRL para executar a macro, na caixa Tecla de atalho, digite a letra minúscula ou maiúscula que deseja usar. Observação: a tecla de atalho substituirá todas as teclas de atalho padrão equivalentes do Excel enquanto a pasta de trabalho que contém a macro estiver aberta. Para obter uma lista de teclas de atalho de combinação com CTRL já atribuídas no Excel, consulte Teclas de atalho e de função do Excel. 4. Na lista Armazenar macro em, selecione a pasta de trabalho onde deseja armazenar a macro. Dica: se quiser que uma macro fique disponível sempre que você usar o Excel, selecione Pasta de Trabalho Pessoal de Macros. Quando você seleciona Pasta de Trabalho Pessoal de Macros, o Excel cria uma pasta de trabalho pessoal de macros oculta (Personal.xlsb), se ela ainda não existir, e salva a macro nessa pasta de trabalho. No Windows Vista, essa pasta de trabalho é salva em C:\Users\nome do usuário\AppData\Local\Microsoft\Excel\pasta XLStart. No Microsoft Windows XP, ela é salva em C:\Documents and Settings\nome do usuário\Application Data\Microsoft\Excel\pasta XLStart. As pastas de trabalho na pasta XLStart são abertas automaticamente sempre que o Excel é iniciado. Se você quiser que uma macro na pasta de trabalho pessoal de macros seja executada automaticamente em outra pasta de trabalho, deverá salvar essa pasta de trabalho na pasta XLStart, para que ambas as pastas de trabalho sejam abertas quando o Excel for iniciado. 5. Na caixa Descrição, digite uma descrição da macro.
Compartilhar