módulo 01 - dashboards no excel 2007.pdf
DESCRIPTION
Modulo 1 - Propriedade: InfraDashboradTRANSCRIPT
Dashboards no MS Excel 2007
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
SUMÁRIO
MÓDULO 1 – REVISÃO DO EXCEL 2007 2
FORMATAÇÃO CONDICIONAL 2
FUNÇÃO LÓGICA SE 5
FUNÇÃO LÓGICA SE COMPOSTA 7
FUNÇÃO SOMASES 9
FUNÇÃO CONT.SES 11
FUNÇÃO DE PROCURA E REFERÊNCIA (PROCV) 13
MACROS 16
CONTROLE DE FORMULÁRIO 21
TABELA DINÂMICA 25
QUANDO UTILIZAR UMA TABELA DINÂMICA? 25
ELEMENTOS DE UMA TABELA DINÂMICA 25
CRIANDO UMA TABELA DINÂMICA 26
ANÁLISE DOS DADOS DA TABELA DINÂMICA 28
FORMATANDO A TABELA DINÂMICA 29
GRÁFICO DINÂMICO 31
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
suspensa.
• Sombreamento 3D: Exibe a caixa de combinação com um efeito tridimensional.
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:
33
OK, você aprendeu a utilizar as ferramentas de tabela e gráfico dinâmico.