Excel Avançado
Contatos: luizossauro@ luizos sauro@gmail gmail.com .com ... E-ma E-mailil luizossauro@ luizo ssauro@hotma hotmail.co il.com m ... MSN hh.c hh .cou outo to .. .... SK SKYP YPE E
Luiz Couto
Excel Avançado
ÍNDICE 1. Revisão Geral .......... ............... .......... ........... ........... .......... .......... ....... 3
1.1 Conceito ................................. .................................................. ................................. ................33 1.2 Conteúdo................. Conteúdo ................................... ................................... .............................. .............33 1.3 Barras de Ferramentas ................................. ........................................... ..........44 1.4 Curtas e Rápidas ................................. ................................................... .................... 5
2. Funções do do Excel .......... ............... ........... ............ ............ ......... ... 6 2.1 Funções Simples.....................................................6 2.2 Funções Condicionais.............................................9
3. Banco de Dados .......... ................ ............ ........... .......... .......... ....... 13 3.1 Criação do Banco de Dados .......... .............. ......... .......... ......... ......... .........13 13 3.2 Acessando o Banco de Dados................................15 3.3 Fórmulas do Banco de Dados ................................20
4. Tabela Dinâmica Dinâmica..... .......... ........... ............ ........... .......... .......... ....... 23
4.1 Conceito e Uso........................................................23 4.2 Elementos da Tabela Dinâmica ......... .............. .......... ......... ......... .........25 25 4.3 Criação de uma Tabela Dinâmica...........................27
5. Validar Dados .......... ............... .......... ........... ........... .......... .......... ....... 33 6. Macro..... Macro .......... .......... ........... ............ ............ ............ ........... .......... .......... ....... 35 6.1 Conceito e Uso........................................................35 6.2 Criação de uma Macro............................................35
7. Solver.......................................................39 7.1 Conceito e Uso........................................................39 7.2 Exemplo de Uso......................................................39
8. Considerações Considerações Finais Finais .......... ............... ........... ............ ......... ... 47
Luiz Couto
Excel Avançado
1. REVISÃO GERAL 1.1 Conceito O “Excel for Windows” é uma planilha eletrônica capaz de armazenar dados, executar cálculos, gerar gráficos, importar e exportar valores de banco de dados. Uma das suas principais características é a capacidade de calcular operações a partir de fórmulas criadas pelo usuário utilizando as informações digitadas na planilha e fora dela. No nosso dia a dia podemos construir uma planilha no Excel para fazer o controle do extrato bancário, controle de notas escolares, controle de estoque da empresa, despesas e receitas de casa, controlar o pagamento de funcionários e qualquer outro controle que necessite de resultados automáticos calculados.
1.2 Conteúdo Uma Planilha Eletrônica é composta de interseções feitas do encontro de linhas e colunas. Para entendermos sua funcionalidade, veremos alguns conceitos do Microsoft Excel: Linha : As linhas ocorrem na horizontal e são identificadas por números. Numa planilha do Excel existem 65536 linhas. Coluna : As colunas ocorrem na vertical e são identificadas por letras. A última coluna se chama IV (letra i e letra v). São 256 colunas. Células : É a interseção entre linhas e colunas. As células recebem os dados digitados pelo usuário ou fórmulas que calculam e retornam algum valor, dependendo das
Luiz Couto
Excel Avançado
referências usadas na fórmula. O nome da célula é a identificação da coluna mais a linha (Ex.: A1, E5). Um arquivo do Excel (a extensão é XLS) é na verdade uma pasta de planilhas. Dentro de uma pasta podemos ter quantas planilhas quisermos (Plan1, Plan2, Plan3, etc). Com o botão direito do mouse sobre o nome da planilha, podemos inserir outra planilha, excluir ou alterar o seu nome.
1.3 Barras de Ferramentas Como outros aplicativos, Word, Powerpoint, Access ou FrontPage, o Excel possui barras de ferramentas que facilitam escolher opções de formatação, manipulação de arquivos, criação e alteração de fórmulas. Mesmo Possuindo várias barras (para escolher, vá na Barra de menu em Exibir / Barras de Ferramentas, e ative a barra que quiser), as mais utilizadas são: Barra de Ferramentas Padrão: Serve para executar os comandos do Excel usados com mais frequência, como abrir ou criar uma nova planilha, copiar/colar, visualizar impressão ou imprimir a planilha, etc. A barra de menu (Arquivo, Editar, Exibir, Inserir...) possui todas as opções. Porém, com o tempo, é mais fácil escolher um botão do que navegar pelas opções do menu. Barra de Formatação: Serve para formatar os dados digitados na planilha, como tipo e tamanho da fonte; negrito, itálico e sublinhado, alinhamento, formato moeda e cores das fontes. Barra de Fórmula: Serve para introduzir informações na planilha. À medida que você se movimenta pela planilha, a Barra de Fórmula mostra o conteúdo da célula ativa. Uma célula possui valores literais (valores fixos como nome, Luiz Couto
Excel Avançado
salário, quantidade, datas) ou valores calculados, que na verdade é o resultado de uma fórmula digitada. Outras duas opções: 1º. Posicionar em uma determinada célula: Na caixa de nome, digite a referência da célula e tecle
. Você vai direto para célula informada (exemplo: digite Y200). 2º. Atribuir nome para uma ou mais células. Imagine que a célula B20 receberá o valor do dólar. Você pode chamar a célula B20 de Valor_Dolar também, digitando na caixa de nome.
1.4 Curtas e Rápidas Algumas dicas podem tornar o uso do Excel mais rápido e ágil, por isso vamos ver algumas “dicas de revisão”. - Mudar de planilha: Ctrl + PageUp ou PageDown – alterna entre Plan1, Plan2, Plan3, etc.
- Selecionar mais de uma planilha ao mesmo tempo : Selecione uma planilha, segura Shift e seleciona a última planilha. Se você formatar uma célula com mais de uma planilha selecionada, as células das outras planilhas também serão formatadas. - Editar conteúdo de célula: Selecione a célula desejada e aperte F2 (ou duplo-clique). - Inserir quebra de título: Ao digitar ou editar uma célula, tecle Alt + Enter na posição que você quer que os dados sejam divididos em mais de uma linha.
Luiz Couto
Excel Avançado
2. FUNÇÕES DO EXCEL 2.1 Funções simples
Para desenvolvermos fórmulas, podemos usar as referências das células e os operadores matemáticos para criarmos cálculos necessários na planilha. Porém, as fórmulas podem ficar complexas ou grandes, de acordo com nossa necessidade. Então podemos criar fórmulas utilizando FUNÇÕES do excel, que são comandos com um objetivo específico, enviando parâmetros para serem calculados e retornando o valor esperado. Vamos ver seis funções bem simples, mas antes veja algumas considerações: . Os parâmetros são informados dentro de parênteses: =NOME_DA_FUNÇÃO(PARÂMETROS) . Os parâmetros são separados por ponto-e-vírgula: =NOME_DA_FUNÇÃO(PARÂMETRO1; PARÂMETRO2; PARÂMETROn) . Uma faixa de valores deve ser separada por dois-pontos: =NOME_DA_FUNÇÃO(CÉLULA1:CÉLULA2) . Dependendo da função, pode haver as duas situações, ou seja, separação de parâmetros com faixa de valores: =NOME_DA_FUNÇÃO(CÉLULA1:CÉLULA2; CÉLULA3:CÉLULAn) . Se trabalhar com valores numéricos, digite-os sem formatação nenhuma (não digite R$ ou separação de milhar). Quando informar casa decimal, use a vírgula. . Quando informar um valor texto, use aspas: =NOME_DA_FUNÇÃO(1500; “FUSCA”; 567,15)
Luiz Couto
Excel Avançado
. O nome da função pode ser digitado em letras maiúsculas ou minúsculas. Função SOMA Tem o objetivo de somar o valor numérico das células enviadas como parâmetro. Veja os exemplos abaixo e o significado de cada uma:
=SOMA(B3:B10) => está somando os valores digitados das células B3 até B10. =SOMA(B3;B5;B10) => está somando os valores das células B3, B5 e B10. =SOMA(B3:B10;C3:C10) => está somando os valores das células B3 até B10 e das células C3 até C10. Poderia ser digitada assim também: =SOMA(B3:C10) Função MÉDIA Retorna a média dos valores das referências definidas como parâmetro. Os parâmetros podem ser digitados da mesma maneira utilizada pela função SOMA.
=MÉDIA(B3:B10) => Retorna o valor médio da soma das células B3 a B10. Função MÁXIMO Retorna o maior valor dos valores enviados como parâmetro.
=MÁXIMO(B3:B10) => retorna o maior valor entre as células B3 e B10.
Luiz Couto
Excel Avançado
Função MÍNIMO Retorna o menor valor dos valores enviados como parâmetro.
=MÍNIMO(B3:B10) => retorna o menor valor entre as células B3 e B10. Função MAIOR Retorna o N maior valor de um conjunto de células, onde N é uma posição desejada e informada na função. Diferente da função Máximo, que só retorna o maior valor, a função Maior pode retornar também o segundo maior valor, o terceiro maior valor e assim por diante.
=MAIOR(B3:B10;1) => retorna o maior valor. Semelhante à função MÁXIMO. =MAIOR(B3:B10;2) => retorna o segundo maior valor entre B3 e B10. Função MENOR Retorna o N menor valor de um conjunto de células, onde N é uma posição desejada e informada na função. Semelhante à função Mínimo, mas possui o segundo parâmetro, que informa qual o valor menor deve ser retornado.
=MENOR(B3:B10;1) => retorna o menor valor. Semelhante à função MÍNIMO. =MENOR(B3:B10;2) => retorna o segundo menor valor entre B3 e B10.
Luiz Couto
Excel Avançado
2.2 Funções Condicionais Função SE A função SE retorna valores ou executa alguma operação, dependendo de uma condição definida no primeiro parâmetro. Desta forma, pode-se retornar dois valores: se a condição é verdadeira ou se a condição é falsa. Para isso, a função SE possui três parâmetros:
=SE ( Condição ; Retorno_Verdadeiro ; Retorno_Falso ) A condição é qualquer comparação com valores ou células existentes na planilha. Os retornos podem ser simples valores literais, valores de células, textos ou cálculos. Exemplos: - Se a célula B3 possuir um valor maior que 500 reais, exiba a palavra OK; se a célula B3 possuir menor ou igual a 500 reais, exiba BAIXO: =SE(B3>500; “OK” ;”BAIXO”) - Se a célula C4 possuir a letra F, exiba a palavra FEMININO; caso contrário exiba a palavra MASCULINO: =SE(C4=”F”;”FEMININO”;”MASCULINO”) - Se a célula D5 for igual a V, retorne a célula F5 com um desconto de 10 reais, senão aumente 5 reais: =SE(D5=”V”; F5 – 10; F5 + 5)
Luiz Couto
Excel Avançado
Lembre-se que uma fórmula pode possuir uma combinação dos retornos de mais de uma função. Por exemplo, qual é a média do maior e do menor salário da coluna B? =MÉDIA(MÁXIMO(B3:B15); MÍNIMO(B3:B15)) No caso do SE, sempre haverá dois retornos: o verdadeiro e o falso da condição. Mas se precisarmos retornar mais de dois valores, podemos combinar um SE dentro do outro: Exiba o nome dos estados digitados na coluna C: RS-Rio Grande do Sul, PR-Paraná e SC-Santa Catarina =SE(C4=”RS”;”Rio Grande do Sul”;SE(C4=”PR”;”Paraná”;”Santa Catarina”)) . Para a função SE de dentro, os três parâmetros são: Condição: C4 = “PR” Retorno_Verdadeiro: “Paraná” Retorno_Falso: “Santa Catarina” . Para a função SE de fora, os três parâmetros são: Condição: C4 = “RS” Retorno_Verdadeiro: “Rio Grande do Sul” Retorno_Falso: SE(C4=”PR”;”Paraná”;”Santa Catarina”)
Luiz Couto
Excel Avançado
Função PROCV De acordo com a condição a ser empregada numa fórmula, e a quantidade de retornos, a função SE poderá ficar muito grande ou até impossível de ser utilizada. Podemos utilizar 7 funções SE dentro da mesma fórmula. A função PROCV, como não depende de uma condição dentro da fórmula, pode ter várias condições de igualdade ou de faixas de valores para retornar quantos valores quiser. Isso só é possível porque a função PROCV utiliza uma matriz digitada dentro da própria planilha para retornar os valores.
A função PROCV significa PROCURA VERTICAL, onde a célula definida com um valor pesquisará na primeira coluna da matriz uma comparação e, se encontrar, retornará o valor de outra coluna da matriz. A função PROCV possui quatro parâmetros: =PROCV(Célula_Comparação; Matriz_Digitada; Coluna_Retornada; Comparação_Igual) Célula_Comparação: A célula que possui um valor a ser pesquisado; Matriz_Digitada: Na própria planilha, digita-se uma coluna (ou tabela) com os valores de comparação com o primeiro parâmetro e nas outras colunas os valores que queremos retornar; Coluna_Retornada: O número da coluna da matriz que irá retornar os valores na função; Comparação_Igual: um valor lógico: a palavra Verdadeiro ou a palavra Falso. Se for verdadeiro, a comparação pode ser de igualdade ou de aproximação (igual ou maior que o valor procurado até o próximo valor de comparação). Se for Falso, só pode ser de igualdade.
Luiz Couto
Excel Avançado
Função CONT.SE A função CONT.SE tem como objetivo contar quantas células fazem parte de uma condição especificada. Podese contar qualquer tipo de célula: data, texto ou valores. Contanto que a condição seja digitada com o mesmo tipo.
=CONT.SE ( Faixa_de_Células ; Condição ) Faixa_de_Células: Identifica as células cujos valores serão contados, dependendo da condição informada. Pode ser uma faixa de mais de uma linha e/ou mais de uma coluna; Condição: Uma comparação lógica usando valor do tipo dos valores da faixa de células. Pode usar as condições de =, >, <, >=, <= e <>. É importante digitar a condição entre aspas, mas também pode ser identificada dentro de uma célula. Função SOMASE A função SOMASE é muito parecida com a CONT.SE; seu objetivo é, ao invés de contar, somar os valores numéricos referentes à condição informada.
Porém, esta função possui um parâmetro a mais. O terceiro parâmetro é quem define a faixa de células a somar, e o primeiro parâmetro, como o CONT.SE, continua sendo a faixa de células referentes à condição. =SOMASE ( Faixa_de_Células ; Condição ; Intervalo_Soma)
Luiz Couto
Excel Avançado
3. BANCO DE DADOS 3.1 Criação do Banco de Dados Um banco de dados consiste em tudo que se quer acessar mais tarde, para que não haja necessidade de refazer a digitação das informações. É usado geralmente para vendas de produtos, compra de produtos, estoques, manipulação de mercadorias e outros. Trata-se de um arquivo gerado pelo excel, comumente chamado de DB, salvo em um formato diferenciado, chamado “.dbf”. Passo a Passo: Abra uma nova planilha e digite tudo que será inserido no seu banco de dados, respeitando os campos que irá incluir. Aqui está um exemplo de como ficaria este banco de dados:
Luiz Couto
Excel Avançado
Note que é possível incluir mais campos e objetos, assim como preços de venda, compra etc. Quanto mais dados no banco de dados, maior será o arquivo *.dbf. Para atualizar o arquivo *.dbf, é preciso repetir todos os passos que serão mostrados, incluindo os dados que adicionados/removidos ou até mesmo alterados. Agora o arquivo deverá ser salvo, que ainda é uma planilha simples do Excel, mas que se tornará um arquivo do tipo DBASE.
Para isso, siga os passos abaixo: 1. Selecione todos os campos dos dados e todas as colunas:
2. Com todas as linhas e colunas da base de dados selecionados, clique em "Arquivo" e depois em "Salvar Como..." e surgirá uma tela para salvar o arquivo desejado.
Luiz Couto
Excel Avançado
Em "Salvar como tipo...", escolha "DBF 4 (dBASE IV)" como na imagem a seguir:
3. Coloque o nome desejado no arquivo do banco de dados (Neste exemplo foi salvo como "Banco de Dados.dbf"). Pronto! O banco de dados já está construído e salvo. Agora será mostrado como acessá-lo.
3.2 Acessando o Banco de Dados Para visualizar o banco de dados será necessário outra "fonte" de informação: utilizaremos uma planilha do Excel sendo que esta servirá como uma "consulta" onde apenas serão visualizados os dados do Banco de Dados - embora seja possível criar todos os tipos de relatórios, funções (como soma, multiplicação, juros, lucro, etc.) e demais ferramentas do Excel. Prosseguindo:
Luiz Couto
Excel Avançado
Abra uma nova planilha e siga os passos que serão mostrados para criar a consulta ao banco de dados: 1. Clique em "Dados" > "Obter dados externos" > "Criar nova consulta ao banco de dados". A seguinte janela deverá aparecer:
2. Deixe a opção "" marcada e clique em 'OK'. Aparecerá a seguinte tela:
Luiz Couto
Excel Avançado
3. Na primeira caixa de texto ("Qual nome deseja dar à sua fonte de dados?"), preencha com o nome do banco de dados desejado (neste tutorial é "Exemplo") 4. No menu drop-down "Selecione um driver para o tipo de banco de dados que se deseja acessar:", marque a opção "Driver do Microsoft dBase (*.dbf) e clique em "Conectar". Na próxima tela você deve selecionar no menu "Versão:" e marcar "dBase IV". Desmarque a caixa "Usar pasta atual" e clique em "Selecionar pasta...", localizando o arquivo que foi salvo da primeira vez (o arquivo de extensão *.dbf). Clique em OK.
No botão "Opções" será apenas modificado um valor: altere o valor de "Tempo limite da página" de "5" (padrão) para um número maior, como "600":
Clique em 'OK'.
Luiz Couto
Excel Avançado
Clique no menu Drop Down "Selecionar uma tabela padrão para a fonte de dados [opcional]" para listar todos os arquivos *.dbf contidos na pasta. Neste caso haverá apenas um arquivo e você deverá selecioná-lo clicando em OK em seguida:
Note que a referencia de "" foi criada com o nome de "Exemplo" (vide figura acima). Clique em 'OK'. Na tela a esquerda vemos que o arquivo está salvo (neste caso, o arquivo foi salvo como Banco de Dados.dbf) e clicando no sinal "+" aparecerão as colunas que foram selecionadas no começo desde tutorial. As setas enviam as colunas para o box do lado direito, sendo que deve-se selecioná-la individualmente: selecione uma, aperte o botão, selecione a outra, aperte o botão e assim por diante até que todas estejam na janela à direita.
Luiz Couto
Excel Avançado
Clique em "" e clicar novamente em "" e finalmente em “”.
Finalmente, o Excel pede onde inserir os novos dados da Base de Dados criada. Na planilha existente, em uma nova planilha ou em uma “Tabela Dinâmica”. O processo de “Tabela Dinâmica” será explicado posteriormente! Os dados inseridos através deste processo correspondem a tabela digitada anteriormente, e por isso serve para o propósito de consulta.
Luiz Couto
Excel Avançado
3.3 Fórmulas do Banco de Dados As fórmulas usadas para trabalhar dentro de uma Base de Dados seguem o padrão de fórmulas normais, tendo junto ao nome o prefixo BD. A lista a seguir compõe as fórmulas mais utilizadas com Banco de Dados: BDSOMA
Adiciona os números em um campo (coluna) de registros em uma lista ou banco de dados que coincidem com as condições especificadas. Sintaxe =BDSOMA(banco_dados;campo;critérios)
Lembrando que: Banco de dados é o intervalo de células da lista ou do banco de dados. Um banco de dados é uma lista de dados relacionados em que as linhas de informações relacionadas são os registros e as colunas de dados são os campos. A primeira linha da lista contém 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 (sem aspas) que represente a posição da coluna dentro da lista: 1 para a primeira coluna, 2 para a segunda coluna e assim por diante. Critérios são 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 pelo menos uma célula abaixo do rótulo de coluna para especificar uma condição para a coluna. Luiz Couto
Excel Avançado
BDMÉDIA
Calcula a média dos valores em um campo (coluna) de registros em uma lista ou banco de dados que coincidem com as condições especificadas. Sintaxe =BDMÉDIA(banco de dados,campo,critérios)
BDCONTAR
Conta as células que contêm números em um campo (coluna) de registros em uma lista ou banco de dados que coincidirem com as condições especificadas. O argumento de campo é opcional. Se o campo for omitido, BDCONTAR contará todos os registros no banco de dados que coincidirem com os critérios. Sintaxe =BDCONTAR(banco_dados;campo;critérios)
BDEXTRAIR
Extrai um único valor em uma coluna de uma lista ou banco de dados que coincide com as condições especificadas. Sintaxe =BDEXTRAIR (banco_dados;campo;critérios) Luiz Couto
Excel Avançado
BDMÁX
Retorna o maior número em um campo (coluna) de registros em uma lista ou banco de dados que coincida com as condições especificadas. Sintaxe =BDMÁX(banco_dados;campo;critérios)
BDMÍN
Retorna o menor número em um campo (coluna) de registros em uma lista ou banco de dados que coincida com as condições especificadas. Sintaxe =BDMÍN(banco_dados;campo;critérios)
BDMULTIPL
Multiplica os valores em um campo (coluna) de registros em uma lista ou banco de dados que coincidem com as condições especificadas. Sintaxe =BDMULTIPL(banco_dados;campo;critérios)
Luiz Couto
Excel Avançado
4. TABELAS DINÂMICAS 4.1 Conceito e Uso Essa é uma ferramenta muito poderosa, e de fácil utilização, para análise de dados e tomada de decisão. Com o uso de Tabelas Dinâmicas podemos, facilmente, obter múltiplas visões do mesmo conjunto de dados. As tabelas dinâmicas do Excel são conhecidas como: Relatório de Tabelas Dinâmicas . Um relatório de tabela dinâmica é uma tabela interativa que você pode usar para resumir rapidamente grandes quantidades de dados. Você pode girar suas linhas e colunas para ver resumos diferentes dos dados de origem, filtrar os dados por meio da exibição de páginas diferentes ou exibir os detalhes de áreas de interesse. Considere o exemplo da Figura a seguir:
Luiz Couto
Excel Avançado
Um exemplo de um relatório de tabela dinâmica simples. Os dados de origem estão na lista à esquerda. Nesse exemplo são exibidos os totais de vendas trimestrais por produto e filtrados para uma determinada categoria. Por exemplo, na Figura anterior, estão sendo exibidos os totais para a categoria Esporte, a qual tem os produtos Golfe e Tênis, bem com o total geral para essa categoria. Poderíamos selecionar uma outra categoria e o Excel, rapidamente, filtraria apenas os produtos daquela categoria e recalcularia os totais e o total geral. Também poderíamos filtrar as vendas para um único trimestre, selecionando o respectivo trimestre na lista de trimestres. Esse é apenas um pequeno exemplo dos relatórios dinâmicos que podem ser construídos com o uso de Tabelas Dinâmicas. Nas demais partes deste tutorial mostrarei diversos exemplos práticos do uso de tabelas dinâmicas. Use um relatório de tabela dinâmica quando você desejar comparar totais relacionados, especialmente quando você tiver uma longa lista de valores a serem resumidos/totalizados e desejar comparar vários fatos sobre cada valor. Use relatórios de tabela dinâmica quando desejar que o Microsoft Excel faça a classificação, a subtotalização e a totalização por você. No exemplo da figura anterior, você pode facilmente ver como as vendas de artigos de golfe no terceiro trimestre, na célula B5, superaram as vendas para outro esporte ou trimestre, ou as vendas totais globais. Como um relatório de tabela dinâmica é interativo, você ou outros usuários podem alterar a exibição dos dados para ver mais detalhes ou calcular resumos diferentes. Para criar um relatório de tabela dinâmica, usaremos o Assistente de tabela dinâmica e gráfico dinâmico como um guia para localizar e especificar os dados de origem que você desejar para analisar e criar a estrutura do relatório. Você pode, em seguida, usar a barra de ferramentas Tabela dinâmica para organizar os dados dentro dessa Luiz Couto
Excel Avançado
estrutura. Aprenderemos a criar relatórios de tabela dinâmica a partir de dados de fontes externas, como por exemplo, dados de um banco de dados.
4.2 Elementos da Tabela Dinâmica Campos de dados e células: Um campo de dados fornece os valores resumidos no relatório de tabela dinâmica. Funções de resumo: Para resumir os valores dos campos de dados, os relatórios de tabela dinâmica utilizam funções de resumo como Soma, Contagem ou Média. Essas funções também apresentam automaticamente os subtotais e totais gerais se você optar por exibi-los. No caso de dados de origem OLAP, o servidor OLAP fornece funções de resumo pré-calculadas. Visualização de detalhes: Na maior parte dos relatórios de tabela dinâmica, é possível ver as linhas de detalhes baseadas nos dados de origem que compõem o valor resumido de uma determinada célula de dados. Os dados de origem OLAP não são organizados em linhas que possam ser visualizadas dessa forma, mas é possível alterar o nível de detalhes exibidos no relatório de tabela dinâmica se ela for baseada em dados de origem OLAP. Campos de linha: Campos dos dados de origem subjacentes aos quais é atribuída uma orientação de linha em um relatório de tabela dinâmica. Um relatório de tabela dinâmica que tem mais de um campo de linha tem um campo de linha interno, o que está mais próximo da área de dados. Os outros campos de linha são denominados campos de linha externos. Campos de linha internos e externos têm atributos diferentes. Itens no campo mais externo são exibidos apenas uma vez, mas os itens no restante dos campos são repetidos conforme necessário. Campo de coluna: Um campo ao qual é atribuída uma orientação de coluna em um relatório de tabela dinâmica. Luiz Couto
Excel Avançado
No exemplo anterior, Trimestres é um campo de coluna com dois itens, Trim2 e Trim3. Um relatório de tabela dinâmica pode ter vários campos de coluna do mesmo modo que pode ter vários campos de linha. Item: Uma subcategoria, ou membro, de um campo de tabela dinâmica. Itens representam entradas isoladas no mesmo campo, ou coluna, nos dados de origem. Os itens aparecem como rótulos de linhas ou colunas ou nas listas suspensas dos campos de página. Campo de página: Um campo ao qual é atribuída uma orientação de página, ou filtro. No exemplo, Região é um campo de página que você pode usar para filtrar o relatório por região. Com o campo Região, você pode exibir dados resumidos apenas para a região Leste, apenas para a região Oeste, ou para outras regiões. Quando você clica em um item diferente em um campo de página, o relatório de tabela dinâmica inteiro se altera para exibir apenas os dados resumidos associados àquele item. Item de campo de página: Cada entrada ou valor isolado do campo, ou coluna, na lista ou tabela de origem se transforma em um item na lista de campos de página. No exemplo, Leste é o item atualmente selecionado para o campo de página Região, e o relatório de tabela dinâmica exibe os dados resumidos apenas para a região Leste. Campo de dados: Um campo de uma lista de origem ou de um banco de dados de origem que contém dados a serem resumidos. No exemplo, Soma das vendas é um campo de dados que resume as entradas do campo, ou da coluna, Vendas nos dados de origem.
Um campo de dados geralmente resume dados numéricos, como estatísticas e valores de vendas, mas os dados subjacentes também podem ser texto. Por padrão, o Microsoft Excel resume dados de texto em relatórios de tabela dinâmica usando a função de resumo Contagem e Luiz Couto
Excel Avançado
resume dados numéricos usando Soma. Em relatórios de tabela dinâmica baseados em dados de origem OLAP, você não pode alterar a função de resumo para um campo: funções de resumo são selecionadas pelo administrador de sistema OLAP e pré-calculadas no servidor OLAP. Área de dados: A parte de um relatório de tabela dinâmica que contém dados de resumo. As células da área de dados mostram dados resumidos para os itens nos campos de linha e coluna. Cada valor na área de dados representa um resumo de dados dos registros de origem, ou linhas.
4.3 Criação de uma Tabela Dinâmica Neste trecho da apostila vamos criar o nosso primeiro Relatório de Tabela Dinâmica. Vamos utilizar, como fonte de dados, uma lista de informações sobre vendas, de uma planilha do Excel. A lista é composta dos seguintes campos: •
•
•
•
•
•
•
•
Número do Pedido Nome da Empresa Data do Pedido Cidade de Destino País de Destino Ano Trimestre Vendas
A planilha é composta por mais de 800 registros. Parte dos dados é exibida na Figura a seguir:
Luiz Couto
Excel Avançado
Obs.: O documento original possui 830 linhas e termina na coluna N.
Na Lista da Figura anterior temos o que chamamos de uma listagem "bruta", ou seja, sem nenhuma forma de totalização, cruzamento ou agrupamento. Com base na listagem "bruta", podemos fazer algumas análises e perguntas, tais como: •
•
Qual o total de vendas por trimestre para o ano de 1997? Qual o total de Vendas para cada empresa, no segundo trimestre de 1996?
Além de responder a essas perguntas (e outras tantas), seria importante ter uma ferramenta que nos permitisse, rapidamente alterar um ou mais critérios. Por exemplo, com um simples clique de mouse eu poderia passar a exibir o total de vendas trimestrais para o ano de 1997, ao invés de
Luiz Couto
Excel Avançado
para o ano de 1996. Tudo isso é possível com o uso de Tabelas Dinâmicas??? Sim, tudo isso e ainda mais. Então vamos à pratica, criando o nosso primeiro Relatório de Tabela Dinâmica. Exemplo 1: Usar os dados da planilha “Dados para Tabela.xls”, para criar um relatório dinâmico, onde possamos analisar, facilmente, o total de vendas por Cliente, por trimestre. Usar o campo Ano como Campo de página, para que possa ser feita a Filtragem das vendas por ano.
1. Abra o Microsoft Excel. 2. Abra a planilha Dados para Tabela.xls. 3. Selecione o comando Dados > Relatório de tabela e gráfico dinâmicos... 4. Será aberto o Assistente de tabela dinâmica e gráfico dinâmico. Na primeira etapa do assistente devemos informar qual a fonte de dados que será utilizada como base para a tabela dinâmica. No nosso exemplo usaremos os dados da lista na própria planilha do Excel. Certifique-se de que a opção Banco de dados ou lista do Microsoft Excel esteja selecionada, conforme indicado na Figura a seguir:
Luiz Couto
Excel Avançado
5. Clique no botão Avançar para seguir para a próxima etapa do assistente. 6. Nessa etapa o Excel pergunta onde estão os dados que farão parte do relatório de tabela dinâmica. Observe que, por padrão, ele já sugere toda a faixa onde está a lista de dados, conforme indicado na Figura a seguir. Caso seja necessário você pode alterar a faixa de dados.
7. Clique no botão Avançar para seguir para a etapa final do assistente. 8. Nessa etapa você define se o relatório de tabela dinâmica será construído na própria planilha onde estão os dados, ou em uma nova planilha. Selecione a opção Nova planilha e clique em OK. 9. Você estará de volta ao Excel. Nesse momento o Excel exibe uma planilha com as áreas bem definidas da tabela dinâmica (veja a Parte 2 deste tutorial, para a descrição dos elementos que compõem uma Tabela Dinâmica) e também exibe a barra de ferramentas Tabela dinâmica. Nessa barra estão os campos da fonte de dados. Luiz Couto
Excel Avançado
Para construir a tabela dinâmica, basta arrastar cada campo para a respectiva posição. 10. Na Figura a seguir temos a tela inicial, onde os campos ainda não foram arrastados para as respectivas posições no relatório de tabela dinâmica.
11. No nosso exemplo vamos começar pelo campo Ano. Esse campo será um item de página, ou seja, ao selecionarmos um valor para o ano, o relatório será filtrado para exibir o total de vendas apenas para o ano selecionado. 12. Arraste o campo Ano para a área onde está escrito: Solte campos de página aqui. 13. O campo Nome da Empresa irá formar as linhas do nosso relatório, ou seja, para cada cliente diferente, teremos uma linha diferente no relatório. 14. Arraste o campo Nome da Empresa para a área onde está escrito: Solte campos de linha aqui. 15. O campo Trimestre irá formar as colunas do nosso relatório, ou seja, para cada trimestre diferente, teremos uma coluna diferente no relatório. Luiz Couto
Excel Avançado
16. Arraste o campo Trimestre para a área onde está escrito: Solte campos de coluna aqui. 17. Os itens do nosso relatório serão formados pelas vendas. 18. Arraste o campo Vendas para a área onde está escrito: Solte itens de dados aqui. 19. Observe que, rapidamente, o Excel calcula, automaticamente o total de vendas por Empresa e por trimestre, conforme indicado na Figura a seguir:
20. Observe que também é calculado o Total Global, com a soma dos quatro trimestres - coluna F, bem como o Total Global por cliente - linha 94. 21. É possível adicionar mais filtro e controles a sua planilha. Colocando os campos País de Destino e Cidade de Destino no topo (junto com Ano) podemos criar uma planilha mais elaborada.
Luiz Couto
Excel Avançado
5. VALIDAR DADOS O recursa Validar Dados é utilizado sempre que for necessário restringir, orientar ou verificar se os dados digitados estão de acordo com o contexto da planilha. No exemplo abaixo, temos uma planilha com um valor que corresponde à taxa de juros de um empréstimo. Vamos supor que esse valor terá como mínimo 1% e máximo 4%.
De acordo com o exemplo acima, clique na célula B3 e vá no Menu Dados > Validação. Na caixa de diálogo que se abre, escolha a opção Número Inteiro no menu abaixo do rótulo Permitir, e defina como critério para validação dos dados o valor entre 1 e 4, conforme na figura abaixo:
Luiz Couto
Excel Avançado
Em seguida, clique na guia Alerta de Erro, e defina o estilo, título e a mensagem que irá surgir caso o usuário digite um valor errado, conforme a figura abaixo:
De modo geral, a guia Configurações da caixa de diálogo Validação de Dados possibilita as seguintes escolhas: Qualquer valor: utilizado para validar números inteiros, textos e outros tipos de entrada. Número Inteiro: utilizado para validar somente números inteiros. Decimal: utilizado para validação de números decimais. Lista: utilizado para validação de dados que estejam em uma lista da planilha, como em um menu suspenso. Data: utilizado para validar data. Hora: utilizado para validar hora. Comprimento do Texto: utilizado para definir quantos caracteres podem ser digitados na célula. Personalizado: permite a configuração de uma validação de dados personalizada, tal como o uso de máscaras de telefone, cep. etc.
Luiz Couto
Excel Avançado
6. MACROS 6.1 Conceito e Uso Macro é que um processo automatizado que evita a repetição manual de comandos, fazendo com que eles sejam realizados de forma automatizada poupando tempo e esforço. Cada Macro é criada por um processo de gravação, que acompanha os passos do usuário e torna possível repetí-los através de um comando ou botão. Macros pode ser utilizadas para respeitar uma série de comandos e valores, definidos pelo usuário na hora de gravar suas ações e comandos.
6.2 Criação de uma Macro Nesta apostila, criaremos uma macro relativamente simples, para classificar dados em uma lista.
Note que a Classificação deveria começar no 1º e terminar no 5º - o que não ocorre pois ela não foi classificada com os critérios desejados.
Luiz Couto
Excel Avançado
Clique em "Ferramentas" > "Macro" > "Gravar nova macro..." Esta tela surgirá:
Em Nome da macro deve-se colocar o nome que quer dar à macro e em Tecla de atalho pode-se colocar um atalho (sendo do tipo + , embora neste exemplo a macro será executada a partir de botão - e não de um atalho). Em Armazenar macro em: será indicado aonde será salva a macro, sendo aconselhável deixar em "Esta pasta de trabalho" pois se for necessário mudar algo na macro, apenas a planilha onde está a macro será utilizada. Em Descrição: coloca-se a data e o nome da pessoa que criou a macro. Depois de preencher os dados da macro, clique em "OK". Um pequeno menu flutuante deverá aparecer. Esta tela está indicando que a macro já poderá ser gravada, sendo que a gravação conterá apenas comandos do Excel. Agora gravaremos os comandos para que a macro possa ser executada: volte ao exemplo anterior (não se esqueça de verificar se a macro está pronta para ser Luiz Couto
Excel Avançado
gravada) e selecione os dados da planilha, incluindo todos os campos da tabela. Agora serão definidos os critérios de classificação: clique em "Dados" > "Classificar..." e surgirá esta tela:
Em Classificar por será colocado o primeiro critério de classificação, que será a coluna "Classificação" e será definido se os dados serão crescentes ou decrescentes. Em Em seguida por deve-se colocar o segundo critério de classificação (coluna "Nome") na ordem Crescente e em E depois por podemos colocar um terceiro critério de classificação (que não existe no exemplo) e definir se os dados dali serão crescentes ou decrescentes - da mesma maneira que nos dois campos anteriores. Clique em OK (note que os dados foram classificados) - e agora é hora de parar a gravação. Clique na opção de parar gravação no menu flutuante ou vá para "Ferramentas" > "Macro" > "Parar Gravação" Agora a macro está gravada: se você desejar visualizá-la, acesse o editor do Visual Basic (ou pressione Alt+F11). A macro foi gravada na pasta "Módulos".
Luiz Couto
Excel Avançado
Agora será adicionado um botão para executar a macro: adicione o menu "Formulários" seguindo estes passos: clique em "Exibir" > "Barra de ferramentas" > "Formulários":
Clique no ícone do botão (em vermelho na figura acima) e note que o cursor virou um símbolo "+": basta clicá-lo e arrastá-lo para determinar o tamanho do botão:
Luiz Couto
Excel Avançado
Clique em "Classificar" (que foi o nome da macro gravada agora pouco) e clique em OK. Pronto! O botão apareceu na planilha. Agora basta clicar no botão para que a macro seja executada novamente.
7. SOLVER 7.1 Conceito e Uso O Solver faz parte de um conjunto de programas algumas vezes chamado de ferramentas de análise hipotética. Com o Solver você pode localizar um valor ideal para uma fórmula em uma célula - chamada de célula de destino - em uma planilha. O Solver trabalha com um grupo de células relacionadas direta ou indiretamente com a fórmula na célula de destino. O Solver ajusta os valores nas células variáveis que você especificar - chamadas de células ajustáveis - para produzir o resultado especificado por você na fórmula da célula de destino. Você pode aplicar restrições para restringir os valores que o Solver poderá usar no modelo e as restrições podem se referir a outras células que afetem a fórmula da célula de destino. Poderemos visualizar isto melhor através de exemplos. Usaremos o SOLVER para resolver Problemas de Programação Linear, como exemplos de uso e aplicação do SOLVER.
7.2 Exemplos de Uso Para familiarizarmos com o uso do SOLVER utilizaremos uma série de exemplos para a fixação de seus principais comandos.
Luiz Couto
Excel Avançado
Problema da Fábrica de Automóveis Alfa Inc. deve produzir 1000 automóveis Alfa. A empresa tem quatro fábricas. Devido a diferenças na mão-de-obra e avanços tecnológicos, as plantas diferem no custo de produção unitário de cada carro. Elas também utilizam diferentes quantidades de matéria-prima e mão-de-obra. O custo de operação, o tempo necessário de mão-de-obra e o custo de matéria-prima para produzir uma unidade de cada carro em cada uma das fábricas estão evidenciados na tabela abaixo.
Um acordo trabalhista assinado requer que pelo menos 250 carros sejam produzidas na fábrica 3. Existem 3200 horas de mão-de-obra e 4000 unidades de material que podem ser alocados às quatro fábricas. O modelo de decisão do problema é dado abaixo, onde xj representa a quantidade de automóveis a serem fabricadas na fábrica j = 1; 2; 3; 4.
Luiz Couto
Excel Avançado
Para resolvermos este PPL utilizando o Excel, devemos primeiramente designar uma célula para representar cada uma das seguintes entidades: •
•
•
Função Objetivo (FO) (Expressão a ser minimizada ou maximizada); Varáveis de Decisão (variáveis que o modelador pode alterar seu valor); Para cada restrição temos uma célula representando o lado esquerdo da restrição (LHS) e outra representando o lado direito darestrição (RHS).
A figura acima apresenta uma das possíveis maneiras de representar o PPL em uma planilha do Excel. Nesta planilha as células a seguir designarão cada uma das entidades citadas anteriormente.
Luiz Couto
Excel Avançado • •
• •
B7 irá representar o valor da FO a ser minimizada; B6 a E6 representarão os valores que as variáveis de decisão assumirão na solução; F11 a F14 irão representar os LHS das 4 restrições; G11 a G14 irão representar os RHS das 4 restrições.
Para que possamos definir cada uma das células anteriormente citadas necessitamos inserir uma série de parâmetros do nosso PPL, tais como todos os coeficientes das restrições e da FO. Para lembrar o que cada célula representa é aconselhável a colocação de títulos que especifiquem o conteúdo de cada célula (células com texto). As células B5 a E5 são utilizadas para inserir os valores dos coeficientes da FO, enquanto as células de B11 a E14 representam os coeficientes das 4 restrições. Agora devemos definir cada uma das entradas citadas anteriormente. A tabela a seguir representa as fórmulas colocadas em cada uma destas células.
Precisamos agora avisar ao Excel quais são as células que representam nossa FO, as variáveis de decisão, as restrições do modelo, e finalmente, mandar o Excel resolver para nós. Isto é feito utilizando a ferramenta SOLVER do Excel. Para tal, clique com o botão esquerdo do mouse sobre o menu FERRAMENTAS e logo em seguida em SOLVER, caso a ferramenta SOLVER não esteja disponível, clique no menu FERRAMENTAS e depois em SUPLEMENTOS e marque a opção SOLVER para que a mesma fique disponível, o Excel instalará a ferramenta tornando-a disponível para uso. Luiz Couto
Excel Avançado
Após este procedimento aparecerá na tela a janela representada pela figura abaixo. Nesta janela é que serão informadas ao software as células que representarão a FO, as variáveis de decisão e as restrições.
Na parte superior da janela da figura aparece um campo para a entrada de dados chamado "Definir célula de destino" que representará o valor da FO. Existem duas maneiras para designar esta célula. A primeira é clicar sobre o ícone que está do lado direito do campo, que levará você a planilha de dados, nesse ponto devemos clicar sobre a célula que representa a FO e pressionar a tecla ENTER para voltar a janela do SOLVER. A segunda é digitar o nome da cálula (B7 no nosso exemplo) no campo. Na linha seguinte são apresentadas as opções de maximizar, minimizar e atingir valor. Dependendo do problema devemos clicar sobre uma das três, no nosso exemplo devemos clicar sobre Min, pois nosso exemplo é de minimização. A opção "Valor de" pode ser utilizada em análise do tipo ponto de equilíbrio, onde desejamos que a função Lucro (por exemplo) atinja o valor de 0. Nos casos de Programação Linear esta opção não será utilizada.
Luiz Couto
Excel Avançado
Na próxima linha há um campo denominado "Células Variáveis". Neste campo serão inseridas as células que representarão as variáveis de decisão. Os valores podem ser inseridos como o caso da FO, isto é, clicando sobre o ícone à direita do campo e marcando as células escolhidas ou simplesmente digitando seus nomes utilizando as regras do Excel para tal. O próximo passo é designar as restrições do problema. Devemos inserir uma restrição ou um grupo de restrições (desde que as restrições tenham o mesmo sinal de restrição e estejam adjacentes) de cada vez. Para inserir a 1ª restrição devemos clicar no botão "Adicionar" para aparecer uma janela de entrada de restrições. A janela de entrada de restrições tem três campos, que representam o LHS - "Referência de célula:" (à esquerda), o sinal da restrição (ao centro), e o RHS - "Restrição" (à direita). Como já mencionado anteriormente, o LHS representa a equação do lado esquerdo da restrição (o lado esquerdo do dicionário modificado). O RHS representa o lado direito da restrição (a constante do dicionário). A figura abaixo representa a entrada da 1a e 2a restrições. Para entrar com os valores nos campos, deve-se proceder como nos casos anteriores, usando o ícone à direita ou digitando o nome da célula.
Luiz Couto
Excel Avançado
O passo seguinte será o de clicar no botão "OK", no caso de não haver nenhuma outra restrição, ou no botão "Adicionar" para confirmar esta restrição e abrir espaço para uma nova entrada. No nosso exemplo, devemos clicar em "Adicionar" e inserir as outra restrições.
Devemos agora inserir as restrições de não-negatividade e definir que o modelo é de Programação Linear, para isto, devemos clicar no botão "Opções" e marcar as opções "Presumir modelo linear" e "Presumir não negativos" e depois clique no botão "OK".
Luiz Couto
Excel Avançado
Uma vez inserido o modelo e suas características, devemos efetivamente resolvê-lo. Para tanto basta clicar no botão "Resolver" na janela dos parâmetros do SOLVER do Excel. Se o modelo foi corretamente inserido, será processado e o resultado aparecerá automaticamente na planilha.
Aparecerá uma janela como a mostrada acima. Se observarmos valores incoerentes ou inesperados, devemos neste ponto clicar na opção "Restaurar Valores Originais" para restaurar os valores iniciais do modelo. Existe ainda a opção de requisitar três tipos de relatórios (lado direito da janela). Ao clicar no botão "OK", a janela de Resultados do SOLVER será apagada e os resultados aparecerão na planilha.
Luiz Couto
Excel Avançado
8. CONSIDERAÇÕES FINAIS Espero que a apostila tenha cumprido sua função de auxiliar em seu aprendizado sobre Excel. Como ferramenta única, porém, esta apostila pode ser um tanto complicada e não-descritiva. “Volenti nihil difficile”
Referências
DODGE, Mark. Guia Autorizado Microsoft Excel 2000. São Paulo, Makron Books, 2000. FRAGA, Simone. Excel 2000 Avançado. Santa Catarina, Visual Books, 2001. ROMAN, Steven. Desenvolvendo Macros no Excel. São Paulo, Ciência Moderna, 2000.
Luiz Couto
