���������� �� �� ����� ����
PREFÁCIO O módulo Dashboards no MS Excel 2007 se propõe a apresentar ao leitor o aplicativo mais usado para a confecção de planilhas de cálculo do mercado mundial. O Excel 2007® permite transformar dados em informações com ferramentas eficientes para analisar, comunicar e compartilhar os resultados. Aliado ao poder do MS Excel 2007, temos os dashboards que nos permitem resumir em apenas uma página grandes volumes de dados de forma extremamente sucinta, podendo apresentar uma grande variedade de resultados em apenas uma única página. Isto permite os gestores a comparar resultados e tomar decisões, dando a eles uma visão mais acurada e rápida da situação da empresa. Infelizmente nem todas as empresas tem recursos para adquirir uma solução de dashboard. Este é o motivo pelo qual desenvolvemos este curso de Dashboards no Excel, que oferecerá, em algumas horas, (e a um custo infinitamente menor do que um sistema de dashboard) as ferramentas, dicas e técnicas para se construir dashboards tão profissionais quanto os desenvolvidos especialmente para este fim. Boa Sorte! Fábio Augusto de Almeida Abreu
[email protected]
PREFÁCIO O módulo Dashboards no MS Excel 2007 se propõe a apresentar ao leitor o aplicativo mais usado para a confecção de planilhas de cálculo do mercado mundial. O Excel 2007® permite transformar dados em informações com ferramentas eficientes para analisar, comunicar e compartilhar os resultados. Aliado ao poder do MS Excel 2007, temos os dashboards que nos permitem resumir em apenas uma página grandes volumes de dados de forma extremamente sucinta, podendo apresentar uma grande variedade de resultados em apenas uma única página. Isto permite os gestores a comparar resultados e tomar decisões, dando a eles uma visão mais acurada e rápida da situação da empresa. Infelizmente nem todas as empresas tem recursos para adquirir uma solução de dashboard. Este é o motivo pelo qual desenvolvemos este curso de Dashboards no Excel, que oferecerá, em algumas horas, (e a um custo infinitamente menor do que um sistema de dashboard) as ferramentas, dicas e técnicas para se construir dashboards tão profissionais quanto os desenvolvidos especialmente para este fim. Boa Sorte! Fábio Augusto de Almeida Abreu
[email protected]
SUMÁRIO ������ � � ������� �� ����� ����
�
���������� �����������
�
������ ������ ��
�
������ ������ �� ��������
�
������ �������
�
������ ��������
��
������ �� ������� � ���������� �������
��
������
��
�������� �� ����������
��
������ ��������
��
������ �������� ��� ������ ���������
��
��������� �� ��� ������ ��������
��
������� ��� ������ ��������
��
������� ��� ����� ����� �� ������ ��������
��
���������� � ������ ��������
��
������� ��������
��
2
MÓDULO 1 – REVISÃO DO EXCEL 2007 O objetivo deste módulo é revisar alguns dos assuntos vistos no curso de Excel Avançado e que serão muito utilizados para a criação dos Dashboards. Portanto, todas as dúvidas sobre estes assuntos devem ser sanadas, no intuito de não comprometer o entendimento do conteúdo objeto do curso (Dashboard).
FORMATAÇÃO CONDICIONAL Como o próprio nome diz, este recurso nos permite formatar um intervalo de células caso uma condição seja verdadeira. Podemos inclusive, definir diversas formatações sendo que cada uma estará associada a uma condição. Por exemplo, podemos definir que as células dentro de um determinado intervalo que possuírem valores abaixo de 50%, estarão formatadas com a cor vermelha e que as células com valores acima de 50%, estarão formatadas com a cor azul. Veremos um exemplo prático nesta lição. 1. Abra o arquivo Formatação Condicional.xls: 2. Clique na planilha Exemplo 1:
A planilha apresenta as vendas por vendedor em um determinado mês. Vamos supor que você queira formatar em vermelho, os percentuais abaixo de 50% e em azul os que estão acima de 50%. Vamos ver como fazer isto. 3. Selecione o intervalo de células ao qual queremos aplicar à Formatação Condicional. No nosso exemplo, selecione o intervalo de D7:D21. 4. Clique no menu Início, e em seguida clique no botão Formatação Condicional .
3
5. Clique a opção Nova Regra...
Nesta tela, podemos configurar todas as opções disponíveis pela Formatação condicional. 6. Clique na opção Formatar apenas células que contenham .
4 7. Na opção “está entre”, clique o drop-down e escolha a opção Menor do que. 8. Na caixa de texto ao lado, digite o valor 50%. 9. Clique o botão Formatar e escolha a cor Vermelha. 10. Clique o botão Ok da janela de formatação. 11. Clique o botão Ok da janela da regra de formatação. Observe que os valores abaixo de 50% estão formatados na cor vermelha.
12. Crie mais uma regra para o mesmo intervalo de células, onde deverão ser formatadas com a cor azul, todas as células cujo valor é superior a 50%. Ao término, sua planilha deverá ser exibida assim:
OK. Você aprendeu a utilizar o recurso da Formatação Condicional .
5
FUNÇÃO LÓGICA SE Uma das funções mais utilizadas no Excel é a função SE. Esta pertence à categoria de funções lógicas e que tem como objetivo retornar um valor se uma condição especificada for avaliada como VERDADEIRO e outro valor se for avaliada como FALSO. Use a função SE para efetuar testes condicionais com valores e fórmulas. Vejamos a sintaxe da função SE: =SE(teste_lógico;Valor_se_verdadeiro;valor_se_falso) Onde: Teste_lógico é qualquer valor ou expressão que pode ser avaliado (a) como VERDADEIRO (A) ou FALSO (A). Valor_se_verdadeiro é o valor fornecido se o teste_lógico for VERDADEIRO. Valor_se_falso é o valor fornecido se o teste_lógico for FALSO. Veremos um exemplo prático nesta lição. 1. Abra o arquivo Função SE.xls: 2. Clique na planilha Exemplo 1:
Nesta planilha, necessitamos determinar o resultado das despesas realizadas em função das previstas para cada um dos meses. Para isto, devemos utilizar a seguinte condição: Se a despesa real for maior que a despesa prevista, o resultado será “Acima do Orçamento”, senão, ou seja, caso a despesa real seja menor ou igual à prevista, o resultado será “Ok” . Para isso proceda da seguinte maneira: 1. Clique a célula D4. 2. Digite a seguinte fórmula: =SE(B4>C4;"Acima do Orçamento";"Ok") 3. Copie a fórmula para o intervalo D5:D15.
6 Com isso, sua planilha deverá ser assim exibida:
OK. Você aprendeu a utilizar a função lógica Se.
7
FUNÇÃO LÓGICA SE COMPOSTA Anteriormente vimos à função SE que, a partir de uma condição, retorna valores como VERDADEIRO ou FALSO e é utilizada para fazer testes condicionais sobre valores e fórmulas que possuam até duas condições. Mas também existirá a necessidade de retornarmos valores como VERDADEIRO ou FALSO em mais de duas condições, para isso, teremos que utilizar a função SE composta. Para entendermos melhor essa função, proceda da seguinte maneira: 1. Abra a pasta de trabalho Função Se Composta.xls, localizada na pasta da turma. 2. Clique na planilha Exemplo 1.
Nesta planilha, necessitamos determinar o valor da gratificação de cada funcionário. Para isto, devemos utilizar a seguinte condição: Se a situação do funcionário for CONTRATADO, a gratificação será de 20% sobre o SALÁRIO BRUTO, mas se a situação do funcionário for TRAINEE, a gratificação será de 10% sobre o SALÁRIO BRUTO e se a situação do funcionário for ESTAGIÁRIO, a gratificação será de 5% sobre o SALÁRIO BRUTO . Para isso proceda da seguinte maneira: 1. Clique a célula F4. 2. Digite a seguinte fórmula: =SE(D4="Contratado";E4*20%;SE(D4="Trainee";E4*10%;E4*5%)) 3. Copie a fórmula para o intervalo F5:F14. 4. Clique a célula G4 e calcule o Salário Final. 5. Copie a fórmula para o intervalo G5:G14. Com isso, sua planilha deverá ser assim exibida:
8
OK. Você aprendeu a utilizar a função lógica Se Composta.
9
FUNÇÃO SOMASES Função que permite a obtenção da soma de resultados com base em um ou mais critérios. Vejamos a sintaxe da função SOMASE: =SOMASES(intervalo_soma;intervalo_critérios1;critérios1;[intervalo_critérios2];[critérios2]...)
Onde: Intervalo_soma - Intervalo que contém os valores a serem considerados na soma. Intervalo_critérios1 - Intervalo que contém os valores a serem avaliados. Critérios1 – Condição necessária para se obter a soma. [Intervalo_critérios2] – argumento opcional. Intervalo que contém valores a serem avaliados após a avaliação do Intervalo_critérios1. [Critérios2] – argumento opcional. Condição 2 opcional para se obter a soma. Para que você conheça a utilização desta função, proceda da seguinte maneira: 1. Abra a pasta de trabalho Função Somases.xls, localiza na pasta da turma. 2. Clique a planilha Exemplo 1:
Esta planilha mostra a quantidade de acessos feitos a Internet por cada região do país em cada semestre nos anos de 2007 e 2008. Na tabela ao lado, devemos informar o total de acessos por região em cada semestre de 2007 e 2008. Para isso proceda da seguinte maneira: 1. Clique a célula G4. 2. Digite a seguinte fórmula: =SOMASES(D4:D23;A4:A23;"Nordeste";B4:B23;"1º semestre") Com isso, será exibido na célula G4, o total de acessos feitos no 1º semestre de 2007 a 2008 na região Nordeste. 3. Crie as fórmulas para as células restantes a fim de obter o total de acessos por semestre nas outras regiões.
10 Com isso, sua planilha deverá ser assim exibida:
OK. Você aprendeu a utilizar a função SOMASES.
11
FUNÇÃO CONT.SES Função que calcula o número de células não vazias em um intervalo que corresponde a um ou mais critérios. Vejamos a sintaxe da função CONT. SES: =CONT. SES(intervalo_critérios1;critérios1;[intervalo_critérios2];[critérios2]...) Onde: Intervalo_critérios1 – Intervalo de células no qual se deseja contar células não vazias mediante um critério. Critérios – É o critério na forma de um número, expressão ou texto que define quais células serão contadas. [Intervalo_critérios2] – argumento opcional. Intervalo de células no qual se deseja contar células não vazias mediante um critério, depois da avaliação do Intervalo_critérios1. [Critérios2] – argumento opcional. Critérios 2 opcional, pode estar na forma de um número, expressão ou texto que define quais células serão contadas. Para que você conheça a utilização desta função, proceda da seguinte maneira: 1. Abra a pasta de trabalho Função Cont.ses.xls, que se encontra na pasta da turma. 2. Clique a planilha Exemplo 1:
Esta planilha mostra em quais setores da empresa os funcionários estão alocados, bem como a sua situação atual. Na planilha ao lado, devemos informar a quantidade de funcionários por setor que são Contratados e quantos são Estagiários. Para isso proceda da seguinte maneira: 1. Clique a célula F4. 2. Digite a seguinte fórmula: = CONT.SES(B4:B13;"Contabilidade";C4:C13;"Contratado") Com isso, será exibido na célula F4, a quantidade de funcionários do setor de Contabilidade e que são Contratados.
12 3. Crie as fórmulas para as células restantes a fim de obter a quantidade de funcionários contratados e estagiários nos outros setores da empresa. Com isso, sua planilha deverá ser assim exibida:
OK. Você aprendeu a utilizar a função CONT.SES.
13
FUNÇÃO DE PROCURA E REFERÊNCIA (PROCV) A função PROCV procura uma informação em uma tabela apresentada na vertical (dados em colunas) e insere o valor encontrado no endereço desejado. Localiza um valor na primeira coluna de uma matriz de tabela e retorna um valor na mesma linha de outra coluna na matriz da tabela. A sintaxe da função PROCV é a seguinte: =PROCV(valor_proc; matriz_tabela; núm_índ_col; proc_intervalo) onde: PROCV é o nome da função; valor_proc é o valor a ser procurado na primeira coluna da matriz. Esse valor procurado pode ser um valor, uma referência ou uma seqüência de caracteres de texto; matriz_tabela é a tabela de informações (área) em que os dados são procurados. Você pode utilizar uma referência de células ou o nome da área; núm_índice_col é o número da coluna na matriz_tabela a partir do qual o valor correspondente deve ser retornado. Um núm_índice_col de 1 retornará o valor na primeira coluna em matriz_tabela. Um núm_índice_col de 2 retornará o valor na segunda coluna em matriz_tabela, e assim por diante. Se núm_índice_col for menor do que 1, PROCV retornará o valor de erro #VALOR!. Se núm_índice_col for maior do que o número de colunas em matriz_tabela, PROCV retornará o valor de erro #REF!; proc_intervalo é um valor lógico que especifica se você quer que o PROCV encontre a correspondência exata ou uma correspondência aproximada. Se VERDADEIRO ou omitido, uma correspondência aproximada é retornada. Ou seja, se uma correspondência exata não for encontrada, o valor maior mais próximo, que o valor_proc, retornará. Se FALSO, PROCV encontrará uma correspondência exata. Se nenhuma correspondência for encontrada, o valor de erro #N/D retornará. As regiões que contém as informações a serem pesquisadas precisam estar em ordem ascendente pela primeira coluna e sem linhas em branco. Para que você conheça a utilização desta função, proceda da seguinte maneira: 1. Abra a pasta de trabalho Função Procv.xls, que se encontra na pasta da turma. 2. Clique a planilha Exemplo 1:
14
Neste arquivo, temos duas tabelas, sendo uma a tabela de preço e a outra a tabela de venda. Na tabela de preço, estão listadas todas as mercadorias bem como os seus valores. E na tabela de venda, temos as mercadorias vendidas em um determinado período por um determinado vendedor. Devemos com isso informar o valor de cada uma das mercadorias na tabela de venda, buscando tais informações na tabela de preço. Para isso proceda da seguinte maneira: 1. Clique a célula F4. 2. Digite a seguinte fórmula: =PROCV(E4;A3:B33;2;0) Com isso, será exibido na célula F4, o valor da mercadoria da célula E4. 3. Edite a fórmula da célula F4 e inclua no intervalo A3:B33 a referência absoluta. 4. Copie a fórmula para o intervalo de F5:F10. Com isso, sua planilha deverá ser assim exibida:
15
OK. Você aprendeu a utilizar a função PROCV.
16
MACROS Você costuma efetuar tarefas comuns repetidamente no Microsoft Excel 2007? Por exemplo, você aplica a mesma combinação de formatos com freqüência? Ou toda a semana, ou todos os meses, você recebe dados que são organizados e analisados sempre da mesma forma? Use uma macro para reunir todas as etapas de uma tarefa em um único comando. Uma macro grava os cliques do mouse e os pressionamentos de teclas enquanto você trabalha e permite que você os reproduza mais tarde. Você pode usar uma macro para gravar a seqüência de comandos utilizada para efetuar determinada tarefa. Quando você executa a macro, ela reproduz exatamente aqueles comandos na mesma ordem, fazendo com que o Excel se comporte como se você tivesse digitado os comandos. As macros são fáceis de criar: inicie a gravação no Excel, execute os comandos normalmente e termine a gravação. O Excel usa uma linguagem de programação chamada Visual Basic® for Applications (VBA) para gravar suas instruções. Você não precisa ter noções de programação ou conhecer o VBA para criar e usar macros que economizarão o seu tempo e facilitarão o seu trabalho. Para que você entenda melhor a utilização das macros, proceda da seguinte maneira: 1. Abra a pasta de trabalho Macros.xls, que se encontra na pasta da turma. 2. Clique a planilha Exemplo 1:
Nesta planilha deveremos criar duas macros para possibilitar que os usuários possam trabalhar com o recurso de Auto Filtro. É claro que nem todos os usuários da planilha desconhecem tal recurso, mas devemos sempre pensar nos usuários mais leigos, ou seja, aqueles que precisam trabalhar com a planilha, mas conhecem muito pouco sobre o programa. Com isso, a primeira macro que criaremos servirá para aplicar o recurso de Auto Filtro na planilha. Para isso proceda da seguinte maneira:
17 1. Clique o menu Desenvolvedor . 2. Clique o botão Gravar Macro. Com isso, surgirá à janela Gravar Macro.
3. Na caixa de texto Nome da Macro, digite Aplicar_Filtro. 4. Clique o botão Ok. Com isso a gravação da macro estará em andamento, ou seja, qualquer ação executada sobre o Excel será gravada como instrução nesta macro. 5. Clique sobre qualquer célula da planilha. 6. Aplique o Auto Filtro. 7. Clique o botão Parar Gravação, localizado na barra de status . Com isso, sua macro chamada Aplicar_Filtro, estará criada. 8. Repita os passos de 1 a 7, para criar uma macro chamada Remover_Filtro , cuja finalidade será a de retirar o recurso de Auto Filtro da planilha. Depois de criada as macros, para poder executá-las devemos visualizar a janela que mostram as macros existentes, para isso, proceda da seguinte maneira. 1. Clique o menu Desenvolvedor . 2. Clique o botão Macros. Com isso surgirá à janela chamada Macro.
18
3. Clique o nome da macro que deseja executar. 4. Clique o botão Executar. Perceba que este processo para se executar macros, pode para alguns usuários, ser um processo muito complicado, pois estes podem não ter um conhecimento tão bom no programa. Com isso, vamos inserir na planilha, dois botões e associar as macros criadas a cada um desses botões, para que o processo de executar as macros sejam mais fáceis. Com isso, proceda da seguinte maneira: 1. Clique o menu Desenvolvedor .
2. Clique o botão Inserir . Com isso surgirá todos os controles disponíveis:
3. Clique sobre o controle chamado Botão do controle de Formulário. 4. Clique sobre a planilha para inseri-lo. Com isso surgirá a janela Atribuir Macro, para que uma macro seja associada a este botão.
19
5. Clique a macro Aplicar_Filtro. 6. Clique o botão Ok. Com isso, a macro chamada Aplicar_Filtro, já esta associada ao botão, isso quer dizer que ao clicar sobre o botão, automaticamente a macro será executada. 7. Repita os passos de 1 a 7, para atribuir a macro Remover_Filtro, ao novo botão que será criado. Pronto, com isso teremos na planilha dois botões que servirão como “gatilho” para a execução automática das macros. Observe que os botões possuem em seus rótulos, nomes que não indicam ao usuário a finalidade do botão, portanto, vamos modificar esses rótulos. Para isso proceda da seguinte maneira: 1. Clique com o botão direito, sobre o primeiro botão. 2. Clique a opção Editar Texto. 3. Digite no botão o nome Atribui Filtro. 4. Se necessário, redimensione o botão. 5. Repita os passos de 1 a 4, para atribuir o rótulo Remover Filtro ao segundo botão. Com isso, sua planilha deverá esta assim exibida:
20
OK. Você aprendeu a gravar Macros e associá-las a um controle de formulário.
21
CONTROLE DE FORMULÁRIO A criação de um formulário é uma maneira prática de tornar uma planilha fácil de usar mesmo para pessoas que não conhecem bem o Excel. Além de campos para digitação, o formulário pode incluir menus, botões deslizantes e outros recursos que facilitam a entrada de dados. Controle de formulário é um objeto da interface gráfica do usuário, como uma caixa de texto, uma caixa de seleção, uma barra de rolagem ou um botão de comando, que permite aos usuários controlar o programa. Usamos controles para exibir dados ou opções, executar uma ação ou facilitar a leitura da interface do usuário. Para visualizar os controles de formulário disponíveis no Excel, devemos acessar o menu Desenvolvedor , e clicar no botão Inserir na barra Controles .
Para inserir um controle de formulário basta clicar sobre o controle a ser utilizado e clicar em qualquer local da planilha. Para definir as propriedades de um controle de formulário existente, clique com o botão direito do mouse no controle, clique em Formatar Controle e, em seguida, clique na guia Controle . Rótulos e botões não possuem propriedades.
Rótulo: Texto que fornece informação sobre um controle ou sobre a planilha ou o formulário.
Caixa de edição: Não está disponível nas pastas de trabalho do Microsoft Excel. Esses controles são fornecidos para permitir que você trabalhe com as folhas de diálogo do Excel versão 5.0. Caixa de grupo: Controles relacionados a grupos como, por exemplo, botões de opção ou caixas de seleção. Propriedades da Caixa de Grupo Sombreamento 3D: Exibe a caixa de grupo com um efeito de sombreamento tridimensional. Botão: Executa uma macro quando clicado. Caixa de Seleção: Ativa ou desativa uma opção. Você pode marcar, de uma só vez, mais de uma caixa de seleção em uma planilha ou em um grupo. Propriedades da Caixa de Seleção
22
Valor: Determina o estado da caixa de seleção, isto é, se ela está marcada (Selecionado), desmarcada (Não selecionado) ou nenhuma dessas opções (Misturado). •
Vínculo da célula: Uma célula que retorna o estado de uma caixa de seleção. Se a caixa de seleção estiver marcada, a célula na caixa Vínculo da célula exibirá o valor lógico VERDADEIRO. Se a caixa de seleção estiver desmarcada, a célula exibirá o valor lógico FALSO. Se o estado da caixa de seleção for misturado, a célula exibirá o valor de erro #N/D. Se a célula vinculada estiver vazia, o Microsoft Excel interpretará o estado da caixa de seleção como FALSO. •
Sombreamento 3D: Exibe a caixa de seleção com um efeito de sombreamento tridimensional. •
Botão de opção: Seleciona uma opção dentro de um grupo de opções contidas em uma caixa de grupo. Os botões de opção permitem selecionar somente uma entre as várias opções possíveis. Propriedades do Botão de Opção Valor: Determina o estado do botão de opção, isto é, se ele está marcado (Selecionado) ou desmarcado (Não selecionado). •
Vínculo da célula: Retorna o número do botão de opção selecionado dentro do grupo de opções (a primeira opção é a número 1). Use a mesma célula de Vínculo da célula para todas as opções em um grupo. Você poderá então usar o número obtido em uma fórmula ou em uma macro para retornar resultados baseados na opção selecionada. •
Sombreamento 3D: Exibe o botão de opção com um efeito de sombreamento tridimensional. •
Caixa de Listagem: Exibe uma lista de itens. Propriedades da Caixa de Listagem Intervalo de entrada: Referência ao intervalo contendo os valores a serem exibidos na caixa de listagem. •
Vínculo de célula: Retorna o número do item que foi selecionado na caixa de listagem (o primeiro item na lista é o número 1). Você poderá usar esse número em uma fórmula ou em uma macro para retornar o item efetivamente selecionado no intervalo de entrada. •
Tipo de seleção: Especifica como os itens podem ser selecionados na lista. Se você definir o tipo de seleção como Múltipla ou Estendida, a célula especificada na caixa Vínculo da célula será ignorada. •
Sombreamento 3D: Exibe a caixa de listagem com um efeito de sombreamento tridimensional. •
Caixa de Combinação: É uma caixa de listagem suspensa. O item selecionado na caixa de listagem aparece na caixa de texto. Propriedades da Caixa de Combinação
23
Intervalo de entrada: Referência ao intervalo contendo os valores a serem exibidos na lista suspensa. •
Vínculo de célula: Retorna o número do item que foi selecionado na caixa de combinação (o primeiro item na lista é o número 1). Você pode usar esse número em uma fórmula ou em uma macro para retornar o item efetivamente selecionado no intervalo de entrada. •
•
Linhas suspensas: Especifica o número de linhas a serem exibidas na lista
•
Sombreamento 3D: Exibe a caixa de combinação com um efeito tridimensional.
suspensa.
Barra de Rolagem: Um controle que percorre um intervalo de valores quando você clica nas setas de rolagem ou quando arrasta a caixa de rolagem. Você pode percorrer uma página de valores, clicando entre a caixa de rolagem e uma seta de rolagem. Propriedades da Barra de Rolagem •
Valor atual: É a posição relativa da caixa de rolagem dentro da barra de rolagem.
Valor mínimo: Representa a posição mais próxima à parte superior de uma barra de rolagem vertical ou à extremidade esquerda de uma barra de rolagem horizontal. •
Valor máximo: Representa a posição mais distante da parte superior de uma barra de rolagem vertical ou da extremidade direita de uma barra de rolagem horizontal. •
Alteração incremental: É o espaço que a caixa de rolagem percorre quando a seta em qualquer uma das extremidades da barra de rolagem é clicada. •
Mudança de página: É o espaço que a caixa de rolagem percorre quando você clica entre a caixa de rolagem e uma das setas de rolagem. •
Vínculo de célula: Retorna o valor atual da caixa de rolagem. Esse número pode ser usado em uma fórmula ou em uma macro para retornar resultados baseados na posição da caixa de rolagem. •
Sombreamento 3D: Exibe a barra de rolagem com um efeito de sombreamento tridimensional. •
Botão de Rotação: Aumenta ou diminui um valor. Para aumentar um valor, clique na seta para cima; para diminuir um valor, clique na seta para baixo. Propriedades do Botão de Rotação •
Valor atual: É a posição relativa do controle giratório dentro do intervalo de valores
permitidos. •
Valor mínimo: É o menor valor permitido para o controle giratório.
•
Valor máximo: É o maior valor permitido para o controle giratório.
Alteração incremental: O valor correspondente à quantidade que o controle giratório aumenta ou diminui quando as setas são clicadas. •
24
Vínculo da célula: Retorna a posição atual do controle giratório. Esse número pode ser usado em uma fórmula ou em uma macro para retornar o valor efetivamente selecionado pelo controle giratório. •
Sombreamento 3D: Exibe o controle giratório com um efeito de sombreamento tridimensional. •
OK. Você aprendeu a trabalhar com Controles de Formulário .
25
TABELA DINÂMICA Uma 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. Veja no exemplo abaixo uma tabela dinâmica das mais simples. Os dados de origem estão na lista à esquerda.
QUANDO UTILIZAR UMA TABELA DINÂMICA?
Quando você desejar comparar totais relacionados, especialmente quando você tiver uma longa lista de valores a serem resumidos 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 acima, você pode facilmente ver como as vendas de artigos de golfe no terceiro trimestre, na célula F5, 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. ELEMENTOS DE UMA TABELA DINÂMICA
Eixo: Trata-se de uma das dimensões da tabela dinâmica, como as colunas, linhas ou páginas. Rótulos de Linha: Trata-se de um ou mais campos de uma lista ou tabela de origem que são atribuídos a uma orientação de linha em uma tabela dinâmica. Rótulos de Coluna: Trata-se de um ou mais campos de uma lista ou tabela de origem que são atribuídos a uma orientação de coluna em uma tabela dinâmica. Valores: Trata-se de um ou mais campos de uma lista ou tabela de origem que contém dados. Por padrão, os dados de texto são resumidos em uma tabela dinâmica com a função de resumo Cont. Valores, e os dados numéricos são resumidos com a função de resumo Soma.
26
Filtro de Relatório: Trata-se de um ou mais campos de uma lista ou tabela de origem que são atribuídos a uma orientação de página numa tabela dinâmica. Quando você clica num campo de página, a tabela dinâmica é alterada e exibe os dados resumidos associados àquele item. Área de Dados: Trata-se da parte da tabela que exibe os dados resumidos. Subtotais: São linhas ou colunas criadas pela tabela dinâmica para resumir os totais de cada grupo - Colunas, linhas ou ambas. Totais Globais: Trata-se de uma linha e/ou coluna criada pela tabela dinâmica para totalizar dados de linhas e/ou colunas. Itens: É uma subcategoria de um campo da tabela dinâmica. Os itens numa tabela dinâmica originam-se de entradas exclusivas em um campo de banco de dados ou de valores exclusivos numa coluna da lista do Excel. Os itens são exibidos como rótulos de linha ou de coluna ou em listas drop-down de campos de páginas. Itens de Campo de Página : Cada entrada ou valor exclusivo do campo ou coluna da lista ou tabela de origem se torna um item na lista de um campo de página. CRIANDO UMA TABELA DINÂMICA
Agora que você já sabe o que é uma tabela dinâmica e aprendeu sobre os elementos e as terminologias utilizadas, chegou à hora de criarmos uma tabela dinâmica. Para isso, proceda da seguinte maneira: 1. Abra o arquivo Tabela Dinâmica.xls, localizado na pasta da turma. 2. Clique a planilha Exemplo 1.
Neste arquivo temos um banco de dados com informações referente as vendas realizadas pela empresa em um determinado período.
27 Perceba que nesta planilha temos diversas colunas, como: Mercadoria, Seção, Data da Venda, Vendedor e Valor. Portanto, fazer algum tipo de análise nesta planilha do modo em que ela esta estruturada, será um processo um tanto quanto complicado. Nosso objetivo agora será o de criar uma tabela dinâmica que nos permita visualizar inicialmente o volume de vendas por vendedor em cada seção da empresa. 3. Selecione o intervalo de A4:E160. 4. Clique o menu Inserir. 5. Clique a opção Tabela Dinâmica.
Com isso, sugira a janela Criar Tabela Dinâmica:
6. Clique o botão Ok. Com isso uma nova planilha será criada, com a estrutura da tabela dinâmica em branco, juntamente com a Lista de campos.
28 Na lista de campos você deverá configurar o layout desejado e opções de totalização. O layout da tabela dinâmica é na verdade a estruturação básica da tabela, campos utilizados e a disposição (linha ou coluna) dos mesmos. 7. Na lista de campos, clique o campo Seção e mova-o para o campo Rótulos de Linha. 8. Na lista de campos, clique o campo Vendedor e mova-o para o campo Rótulos de Coluna. 9. Na lista de campos, clique o campo Valor e mova-o para o campo Valores. A estrutura da Lista de Campos, bem como a Tabela Dinâmica, deverá estar assim exibida:
Observe que a partir do momento em que você move os campos da tabela para os campos na Lista de Campos, as informações são automaticamente visualizadas na estrutura da tabela dinâmica na planilha que foi criada. ANÁLISE DOS DADOS DA TABELA DINÂMICA
Vamos agora verificar quais possibilidades de análise dos dados da tabela dinâmica. Observe na planilha que temos alguns cabeçalhos, como mostrados abaixo:
Observe também que quando clicamos uma seta indicadora de um cabeçalho, opções são exibidas, como mostradas a seguir:
29
A partir das opções apresentadas, podemos refinar o filtro de forma a exibir apenas os dados desejados. Para que você entenda vamos agora alterar opções de filtragem do cabeçalho Seção, de forma que a tabela dinâmica exiba apenas as informações pertinentes a seção Diversos e HD. Para isso, proceda da seguinte maneira: 1. Clique na seta para baixo do cabeçalho Seção. 2. Clique a opção Diversos e clique a opção HD. 3. Clique o botão OK. A tabela dinâmica irá então atender ao novo filtro e reajustar a planilha, de forma a exibir apenas as informações referentes à opção selecionada, como mostrada no exemplo a seguir:
FORMATANDO A TABELA DINÂMICA
Como toda planilha, a tabela dinâmica também pode ser formatada. Esta, porém, apresenta opções próprias de formatação exibidas na barra Ferramentas de Tabela Dinâmica. Esta barra é exibida quando selecionamos uma célula qualquer da tabela dinâmica e logo em seguida acessamos o menu Design. Note que, o menu Design somente será visualizado, caso pelo menos uma célula da tabela dinâmica este selecionada. Vamos agora formatar a tabela dinâmica. Para isso, proceda da seguinte maneira: 1. Posicione o cursor do Excel em qualquer célula da tabela dinâmica. 2. Clique no menu Design. Neste menu, será possível visualizar a barra Estilos de Tabela Dinâmica.
30
3. Clique o botão
para visualizar todas as formatações disponíveis.
4. Selecione o modelo Estilo de Tabela Dinâmica Média 1. Será então formatada a planilha com o modelo de formatação escolhido e exibida como no exemplo a seguir:
31
GRÁFICO DINÂMICO Tendo uma tabela dinâmica você pode criar um gráfico dinâmico. O gráfico dinâmico é um tipo de gráfico configurável, ou seja, assim como na tabela, a partir dos rótulos você poderá filtrar apenas as informações que deseja visualizar exibida no gráfico. Para que você entenda como funciona um gráfico dinâmico, vamos agora criar um. Para isso, proceda da seguinte maneira: 1. Posicione o cursor do Excel numa célula da tabela dinâmica. 2. Clique no menu Opções. 3. Clique na opção Gráfico Dinâmico.
Com isso, surgirá a caixa de diálogo Inserir Gráfico.
Nesta caixa de diálogo, devemos escolher o tipo e subtipo do gráfico dinâmico a ser criado. 4. Selecione no tipo Coluna. 5. Selecione o subtipo Cilindro Agrupado. Será então criado o gráfico dinâmico e exibido como mostrado no exemplo a seguir:
32
Observe que o gráfico dinâmico é criado na mesma planilha que contém a tabela dinâmica, caso deseje mover o gráfico dinâmico para outra planilha, proceda da seguinte maneira: 6. Selecione o gráfico dinâmico. 7. Clique no menu Design. 8. Clique na opção Mover Gráfico.
Com isso, iremos visualizar a caixa de diálogo Mover Gráfico.
9. Selecione a opção Nova planilha. 10. Se necessário, na caixa de texto digite o nome da nova planilha. 11. Clique no botão Ok. Será então mostrado o gráfico dinâmico exibido em uma nova planilha, como mostrado no exemplo a seguir:
