Dashboard em Excel - Volume I

Prévia do material em texto

Excel 2010 – Dashboard - Incrementando seus conhecimentos e
Aperfeiçoando a sua prática Página 1/108
Excel 2010 – Dashboard - Incrementando seus conhecimentos e
Aperfeiçoando a sua prática Algumas palavras do autor:
 
Este pequeno e-book demonstra a criação de painéis de controle
(dashboard) no Excel 2010. Nosso objetivo é prover ensinamentos para
profissionais de diversas áreas (financeira, contabilidade, economia,
administração), micro e pequenas empresas onde se busca uma melhor
análise e gerência de informações. A seguir você poderá acompanhar todo o
conteúdo desta apostila.
 
Acesse também www.ensinandoexcel.com.br
 
Um bom estudo a todos e espero, sinceramente, que este pequeno e-book
possa ajudá-los a enriquecer seus conhecimentos e proporcionar uma
melhor posição no mercado de trabalho.
 
Abraços
 
José Eduardo
Página 2/108
2
Excel 2010 – Dashboard - Incrementando seus conhecimentos e
Aperfeiçoando a sua prática
Excel 2010 – DAshboard
 
Incrementando
 
Seus conhecimentos
 
Aperfeiçoando
3
 
A sua prática
Página 3/108
Excel 2010 – Dashboard - Incrementando seus conhecimentos e
Aperfeiçoando a sua prática ÍNDICE
 
. Histórico............................................................................................ 005
. Referências........................................................................................ 008
. Nomenclaturas................................................................................... 010
. Formatações...................................................................................... 012
 
. Funções............................................................................................ 013
. ProcV....................................................................................... 013
. ProcH....................................................................................... 017
. Índice...................................................................................... 019
. Corresp.................................................................................... 021
. Escolher................................................................................... 022
. Desloc...................................................................................... 023
. Rept......................................................................................... 024
. Indireto.................................................................................... 026
 
. Botão Câmera.................................................................................... 028
4
. Inserindo Controles............................................................................. 030
. Barra de Rolagem...................................................................... 030
. Botão de Opção......................................................................... 032
. Caixa e Seleção......................................................................... 034
 
. Protegendo a planilha contra alterações sem usar senha – recurso VBA...
035
. Carregar Arquivo Texto e Retirar Itens Duplicados..................................
036
. Trabalhando com a caixa de nomes....................................................... 040
. Somando valores para diferentes planilhas.............................................
041
. Atingir Meta – Ponto de Equilíbrio........................................................ 042
. Formatação Condicional – Setas/Farol...................................................
047
. Montando Scroll de tela....................................................................... 050
. Tabelas Dinâmicas.............................................................................. 052
. Montando uma Tabela Dinâmica.................................................. 052
. Transformando a tabela em valores percentuais sobre o total......... 056
. Adicionando um campo calculado................................................. 057
. Agrupando datas....................................................................... 059
. Tabela Dinâmica utilizando várias planilhas................................... 061
. Preencher células em branco do campo chave para tabela dinâmica. 068
5
 
. Gráfico com Tabela Dinâmica............................................................... 070
. Gráfico de Colunas com Caixa de Seleção e Caixa de Combinação...........
074
. Gráfico de Termômetro........................................................................ 080
. Gráfico de Estoque Mínimo................................................................... 084
. Utilização de duas Caixas de Combinação ao mesmo tempo....................
090
. Gráfico para medir visitas em site por hora............................................
095
. Gráfico de Velocímetro........................................................................ 100
Página 4/108
6
Excel 2010 – Dashboard - Incrementando seus conhecimentos e
Aperfeiçoando a sua prática HISTÓRICO
Se nos remetermos para a formação dos contabilistas, podemos afirmar que
as planilhas têm a sua utilização já há muitos anos. Mais recente na história
temos a criação das planilhas eletrônicas.
Em 1978, Dan Bricklin, aluno da escola de administração da Universidade
de Harvard (EUA), percebeu em uma aula de controladoria, que seu
professor gastava muito tempo fazendo cálculos na lousa. Daí surgiu à
idéia.
7
Diagrama rabiscado por Bricklin
Dan, juntamente com seu colega Robert Frankston, elaboraram um
aplicativo, o qual simulava o quadro negro do professor. Nascia assim a
primeira planilha eletrônica. Posteriormente fundaram a empresa
VISICORP e lançaram a planilha que nascia com o nome de VISICALC
(VISIble CALCulator).
 
Página 5/108
Excel 2010 – Dashboard - Incrementando seus conhecimentos e
Aperfeiçoando a sua prática Em 1980, possuir um computador era muito
caro e suas funcionalidades eram muito restritas. Com a criação do
Visicalc, uma nova finalidade começou a ser percebida, havendo um
aumento nas vendas de computadores.
8
 
Abaixo temos uma visão geral de como era a tela do Visicalc.
 
Em 1983, a Lotus Corporation lançou o LOTUS 1 2 3, uma ferramenta bem
poderosa capaz de montar gráficos e trabalhar com uma base de dados,
superando assim o Visicalc. Além destas havia também Supercalc,
Multiplan e Quatto Pro.
 
Nos anos 90, a Microsoft cria o MS Windows, lançando assim a sua
planilha EXCEL, tornando-se líder de mercado.
Página 6/108
Excel 2010 – Dashboard - Incrementando seus conhecimentos e
Aperfeiçoando a sua prática
A primeira versão do EXCEL foi lançada para Mac, em 1985 e a versão
para Windows teve o seu lançamento em novembro de 1987, com o nome
de Microsoft Excel 2.0. Em pouco tempo, a Microsoft liderou o mercado
das planilhas eletrônicas e em 1990 lançou a versão 3.0.
 
O grande diferencial em relação à outros programas de sua categoria é a
flexibilidade apresentada pela formatação gráfica dos dados. Desde 1993,
o Excel tem incluído o Visual Basic for Applications (VBA), uma
linguagem de programação baseada no Visual Basic, definidas pelo usuário
através de macros.
Até a versão décima primeira versão (2003), o formato de arquivo padrão
do Excel era o .xls. A partir da décima segunda versão, o formato passou a
ser .xlsx.
 
9
Versões do Microsoft Excel para Windows
10
Ano
Versão
1987
Excel 2.0 para Windows
1990
Excel 3.0
1992
Excel 4.0
1993
Excel 5.0
1995
Excel 7.0 (Office 95)
1997
Excel 8.0 (Office 97)
1999
Excel 9.0 (Office 2000)
2001
Excel 10.0 (Office XP)
2003
Excel 11.0 (Office 2003)
2007
11
Excel 12.0 (Office 2007)
2010
Excel 14.0 (Office 2010)
 
ReferênciasDiagrama retirado de http://www. bricklin.com
http://www.weblivre.net/artigo/excel/a-historia-do-microsoft-office-excel
http://www.capitao.pro.br/apostilas/informatica/historia_planilhas.doc
Página 7/108
Excel 2010 – Dashboard - Incrementando seus conhecimentos e
Aperfeiçoando a sua prática REFERÊNCIAS
Ao copiar uma fórmula, podemos verificar que as referências contidas nas
fórmulas são alteradas automaticamente. Isso acontece em função das
Referências Relativas.
 
Há uma maneira de se criar fórmulas com Referência Absoluta ou mista
(sendo uma parte relativa e outra absoluta). O uso do caractere $ ( dólar)
utilizado no início de uma referência, a torna absoluta.
 
O exemplo abaixo visa determinar quanto cada produto representou em
12
vendas no mês, percentualmente falando. A fórmula para obter o percentual
seria dividir a quantidade individual vendida de cada produto, pelo total
vendido no mês, e depois aplicar um formato de porcentagem na célula do
resultado.
 
O problema seria que, quando se cria referências relativas nas células, ao
copiar a fórmula para as demais, o resultado seria incorreto, tendo em vista
que todas as referências das células seriam modificadas.
 
Neste caso precisamos atribuir à célula do: Total de vendas do mês, uma
propriedade que a tornasse uma referência “Absoluta”, de forma que, ao
copiar a fórmula, a referência desta célula ficaria fixa. Na própria fórmula,
selecione a área desejada e pressione F4.
 
Página 8/108
Excel 2010 – Dashboard - Incrementando seus conhecimentos e
Aperfeiçoando a sua prática TIPOS DE REFERÊNCIA
 
Tipo
13
Propriedade
Relativa (A1, B2, C3..)
Não fixa nem a linha e nem a coluna
Mista ($B2)
Fixa a coluna, havendo uma variação nas linhas
Mista (B$2)
Fixa a linha, havendo uma variação nas colunas
Absoluta ($B$2)
Fixa a coluna e a linha
Página 9/108
Excel 2010 – Dashboard - Incrementando seus conhecimentos e
Aperfeiçoando a sua prática
14
NOMENCLATURAS
Dados
São códigos que fazem parte da matéria prima que é a informação. Esta
informação diz respeito à informação bruta, ainda não tratada.
O dado quando tratado isoladamente, pode não transmitir uma mensagem ou
não representar nenhum conhecimento.
 
Informação
A informação diz respeito ao dado já tratado. Trata-se do resultado de um
determinado processamento de dados. É com as informações que decisões
são tomadas.
Se tomarmos como base uma pesquisa eleitoral, é na pesquisa que os dados
são adquiridos e quando processados, vão produzir a informação de quem
tem mais chance de ser eleito, dentre outras.
 
BI - Business Intelligence
Faz referência ao processo de coleta, organização, análise,
compartilhamento e monitoramento de informações que oferecem suporte a
gestão de negócios.
 
Relatórios
É a apresentação de dados transformados em informações, formatadas e
organizadas conforme requisitos do negócio.
15
Tabela de Indicadores
É um tipo de relatório que apresenta uma coleção de indicadores de chaves
de desempenho, também conhecidos por KPIs.
 
KPI - Key Performance Indicator (Indicador Chave de Desempenho) São
formas simples de análise de processo através de gráficos simples com
conotações universais, representando o estado de uma área, de um projeto
ou até mesmo de um processo dentro da empresa.
Ex. gráfico de velocímetro.
 
Página 10/108
Excel 2010 – Dashboard - Incrementando seus conhecimentos e
Aperfeiçoando a sua prática
16
Balanced Scorecard
Trata-se de uma técnica que visa a integração e balanceamento de todos os
principais indicadores de desempenho existentes em uma empresa.
17
Dashboard
É um "armazenador" de vários tipos de relatórios, incluindo tabelas,
indicadores.
Um Dashboard típico poderá conter uma tabela de indicadores, um relatório
analítico e um gráfico por exemplo.
Página 11/108
Excel 2010 – Dashboard - Incrementando seus conhecimentos e
Aperfeiçoando a sua prática FORMATAÇÕES
 
"C.N.P.J."" "00"."000"."000"/"0000-00
Para formatar apenas o número digite
""00"."000"."000"/"0000-00
Arredondando um valor apenas na tela
0,0;-0,00;;@
Página 12/108
Excel 2010 – Dashboard - Incrementando seus conhecimentos e
Aperfeiçoando a sua prática FUNÇÃO PROCV
 
18
 OBJETIVO
O objetivo desta função é o de procurar o primeiro valor na coluna mais a
esquerda de um conjunto de células informadas, que satisfaça uma
determinada condição.
 SINTAXE DA FUNÇÃO
PROCV(Valor_procurado
;
19
Matriz_tabela
;
Núm_índice_lin
;
Procurar_intervalo)
 Valor_procurado
Identifica o valor que se deseja procurar (o que eu quero).
 Matriz_tabela
Identifica o conjunto de valores em que se deseja efetuar a pesquisa.
 Núm_índice_lin
Identifica a coluna na qual se deseja obter o valor.
 Procurar_intervalo
Poderá ser identificado por dois valores: verdadeiro ou falso.
Verdadeiro retorna o valor mais próximo que for encontrado, já o falso
retornará o valor exato da procura.
 
Página 13/108
20
Excel 2010 – Dashboard - Incrementando seus conhecimentos e
Aperfeiçoando a sua prática EXEMPLO I
 
Na célula A7 vamos pesquisar qual foi a nota exata da Prova 1 para Pedro
Henrique. Se o nome Pedro Henrique estivesse duas vezes neste exemplo, o
primeiro seria o selecionado.
Vejamos a fórmula que foi utilizada: =PROCV(A7; A2:C5; 3; FALSO) 
A7 – Valor a ser procurado, neste caso Pedro Henrique A2:C5 –
Intervalo onde se deseja procurar o conteúdo Pedro Henrique. Note que a
primeira coluna é a coluna a ser pesquisada.
 3 – Identifica qual é a coluna que se deseja trazer o valor.
 FALSO – Identifica que a busca por este valor deva ser exata e não
aproximada.
 
O resultado obtido foi 4, equivale ao valor da célula C3, pois foi à primeira
ocorrência encontrada de Pedro Henrique na tabela. Note que no parâmetro
foi usado o termo FALSE, ou seja, irá trazer o valor exatamente igual ao
procurado.
 
21
Página 14/108
Excel 2010 – Dashboard - Incrementando seus conhecimentos e
Aperfeiçoando a sua prática EXEMPLO II
No caso abaixo, vamos encontrar o percentual de comissão baseado no
valor da venda em um determinado mês. Note que no parâmetro foi usado o
termo VERDADEIRO, ou seja, irá trazer o maior valor mais próximo do
valor procurado.
 
 EXEMPLO III
No exemplo abaixo temos uma tabela de aging de datas, baseado nos
22
vencimentos de cada linha.
 
 EXEMPLO IV
Quando a coluna estiver em ordem alfabética e o valor como
VERDADEIRO, irá retornar a última ocorrência da pesquisa.
 
Página 15/108
Excel 2010 – Dashboard - Incrementando seus conhecimentos e
Aperfeiçoando a sua prática Monte uma planilha igual à figura abaixo e
preencha os dados faltantes.
 
Página 16/108
Excel 2010 – Dashboard - Incrementando seus conhecimentos e
Aperfeiçoando a sua prática FUNÇÃO PROCH
23
 
 OBJETIVO
O objetivo desta função é o de procurar um valor em uma linha especifica
de um conjunto de células informadas, que satisfaça uma determinada
condição.
 SINTAXE DA FUNÇÃO
PROCH(Valor_procurado
;
24
Matriz_tabela
;
Núm_índice_lin
;
Procurar_intervalo)
 Valor_procurado
Identifica o valor que se deseja procurar.
 Matriz_tabela
Identifica o conjunto de valores em que se deseja efetuar a pesquisa.
 Núm_índice_lin
Identifica a linha na qual se deseja obter o valor.
 Procurar_intervalo
Poderá ser identificado por dois valores: verdadeiro ou falso.
Verdadeiro retorna o valor mais semelhante na linha inferior, já o falso
retornará o valor exato da procura.
 
Página 17/108
25
Excel 2010 – Dashboard - Incrementando seus conhecimentos e
Aperfeiçoando a sua prática EXEMPLO I
 
Na célula B10 vamos pesquisar qual foi a nota exata da Prova 1 dapesquisa que irá retornar o conteúdo da linha 8.
Vejamos a fórmula que foi utilizada: =PROCH(B6; B1:D4; 3; FALSO) 
B6 – Valor a ser procurado, neste caso Prova 1
 B1:D4 – Intervalo onde se deseja procurar o conteúdo Prova 1.
 3 – Identifica qual é a linha que se deseja trazer o valor.
 FALSO – Identifica que a busca por este valor deva ser exata e não
aproximada.
 
 EXEMPLO II
26
 
Página 18/108
Excel 2010 – Dashboard - Incrementando seus conhecimentos e
Aperfeiçoando a sua prática FUNÇÃO ÍNDICE
 
 OBJETIVO
O objetivo desta função é retornar um valor ou uma referência para um
valor dentro de uma tabela ou intervalo.
 
 SINTAXE DA FUNÇÃO
ÍNDICE(matriz ; núm_linha ; núm_coluna)
 matriz
Identifica o intervalo de células ou uma constante de matriz.
Se esta matriz contiver apenas uma linha ou coluna, o argumento núm_linha
e núm_coluna são opcionais.
 núm_linha
Seleciona a linha da matriz. Se for omitido este valor, núm_coluna será
obrigatório.
 núm_coluna
Seleciona a coluna da matriz. Se for omitido este valor, núm_linha será
obrigatório.
 
Página 19/108
27
Excel 2010 – Dashboard - Incrementando seus conhecimentos e
Aperfeiçoando a sua prática EXEMPLO
 
Página 20/108
Excel 2010 – Dashboard - Incrementando seus conhecimentos e
Aperfeiçoando a sua prática FUNÇÃO CORRESP
 
28
 OBJETIVO
O objetivo desta função é retornar a posição relativa de um item em uma
matriz que corresponda ao valor especificado.
 
 SINTAXE DA FUNÇÃO
CORRESP(Valor_procurado ; Matriz_procurada ;
Tipo_correspondência) Valor_procurado
Identifica o valor que se deseja procurar.
 Matriz_procurada
É o intervalo de células que irá conter os valores possíveis para a
procura.
 Tipo_correspondência
É um número (0,1,-1) que indica qual valor será retornado.
 EXEMPLO
 
Página 21/108
Excel 2010 – Dashboard - Incrementando seus conhecimentos e
29
Aperfeiçoando a sua prática FUNÇÃO ESCOLHER
 
 OBJETIVO
O objetivo desta função é escolher um valor a partir de uma lista de
valores, com base em um número de índice.
 
 SINTAXE DA FUNÇÃO
CORRESP(Núm_índice ; Valor1 ; ValorN .....)
 Núm_índice
Identifica qual o argumento de valor a ser identificado (vai de 1 a 254).
 Valor1
Faz parte do conjunto de referência que a função irá escolher (vai de 1 a
254).
 EXEMPLO
 
Página 22/108
Excel 2010 – Dashboard - Incrementando seus conhecimentos e
30
Aperfeiçoando a sua prática FUNÇÃO DESLOC
 OBJETIVO
Retorna o valor de uma célula que esta afastada de um determinado valor.
 SINTAXE DA FUNÇÃO
DESLOC (Ref; Lins; Cols; Altura; Largura)
 Ref
Identifica a célula na qual irá dar início ao deslocamento.
 Lins
Identifica o número de linhas acima ou abaixo a ser deslocado.
 Cols
Identifica o número de colunas acima ou abaixo a ser deslocado.
 Altura
(Opcional) Indica quantas linhas de dados devem ser retornadas.
Este número deve ser positivo.
 Largura
(Opcional) Indica quantas colunas de dados devem ser retornadas.
Este número deve ser positivo.
 EXEMPLO I
No exemplo 101 a célula A8 que apresenta o valor 101 – significa que a
partir da célula C2, irá buscar duas células abaixo e deslocar uma célula
para a esquerda.
No exemplo Maria a célula A10 que apresenta o texto Maria – significa
que a partir da célula C2, irá buscar duas células abaixo e deslocar duas
31
células para a esquerda.
 
Página 23/108
Excel 2010 – Dashboard - Incrementando seus conhecimentos e
Aperfeiçoando a sua prática FUNÇÃO REPT
 
 OBJETIVO
Esta função tem como objetivo repetir um texto em um determinado número
de vezes.
 SINTAXE DA FUNÇÃO
REPT(texto, núm_vezes)
 texto
Identifica o texto.
32
 núm_vezes
Identifica o número de vezes da repetição.
 
 EXEMPLO I
 
 EXEMPLO II
 
Página 24/108
Excel 2010 – Dashboard - Incrementando seus conhecimentos e
Aperfeiçoando a sua prática
33
EXEMPLO III
 EXEMPLO IV
 
Página 25/108
Excel 2010 – Dashboard - Incrementando seus conhecimentos e
Aperfeiçoando a sua prática FUNÇÃO INDIRETO
 OBJETIVO
O objetivo desta função é transformar o valor de um texto em uma
referência a uma célula.
 SINTAXE DA FUNÇÃO
INDIRETO(Texto_ref; A1)
 Texto_ref
É uma referência a uma célula.
 A1
É um valor lógico que especifica o tipo de referência.
34
 
 EXEMPLO I
Crie uma planilha conforme figura abaixo.
 
Selecione o range de B2 a B4 e dê o nome de idade.
 
Página 26/108
Excel 2010 – Dashboard - Incrementando seus conhecimentos e
Aperfeiçoando a sua prática Selecione o range de C2 a C4 e dê o nome de
35
gastos.
 
Na célula F9 a função indireto, substitui o conteúdo da célula E9, trazendo
para dentro de si, o range especificado anteriormente.
 
 EXEMPLO II
 
Página 27/108
36
Excel 2010 – Dashboard - Incrementando seus conhecimentos e
Aperfeiçoando a sua prática
Botão Câmera
A barra de ferramentas pode ser personalizada conforme a utilização dos
comandos que se deseja. Nela podemos colocar os comandos mais
utilizados como um atalho.
 
Para colocarmos os atalhos desejados, clique no Botão Arquivo , e
escolha a opção mais comandos. Arraste o item desejado do quadro
mais à esquerda para à direita. Após a escolha dos atalhos desejados,
clique em Ok.
 
Monte o seguinte exemplo na Plan1: Cada valor deverá ser multiplicado
pela célula F1.
 
Na Plan2:
No menu, layout de página, linhas de grade, desmarque a opção exibir
No menu, inserir, formas, retângulo, e coloque a figura entre A1 e E6.
 
37
Página 28/108
Excel 2010 – Dashboard - Incrementando seus conhecimentos e
Aperfeiçoando a sua prática Na Plan1 selecione as células de A1 a D4 e
clique na câmera e clique na A1 da Plan2 sobre a figura.
 
Página 29/108
38
Excel 2010 – Dashboard - Incrementando seus conhecimentos e
Aperfeiçoando a sua prática Inserindo Controles – Barra de Rolagem
Na Plan2 coloque:
No menu inserir, auto forma, retângulo.
Dentro deste retângulo coloque um retângulo menor e escreva Valores.
No menu Desenvolvedor, Inserir, Barra de Rolagem.
 
Coloque a barra acima dos valores conforme figura abaixo.
 
Clique com o botão direito do mouse na barra, propriedades e altere o
controle.
 
Clique em qualquer célula para desmarcar a barra e clique nas setas da
barra para direita ou para a esquerda.
Página 30/108
39
Excel 2010 – Dashboard - Incrementando seus conhecimentos e
Aperfeiçoando a sua prática
Copie e cole uma nova figura (azul).
Página 31/108
Excel 2010 – Dashboard - Incrementando seus conhecimentos e
40
Aperfeiçoando a sua prática Inserindo Controles –Botão de Opção
Insira três botões de opção.
 
Cada botão quando selecionado deverá apontar o seu valor para a célula F2
da Plan1. Clique com o botão da direita sobre o botão e altere a
propriedade Controle. Altere o texto de cada botão.
 
Página 32/108
Excel 2010 – Dashboard - Incrementando seus conhecimentos e
Aperfeiçoando a sua prática Na plan1 copie de A1 a D2 para a célula A7.
 
Digite:
A8 =SE(F2=1;A2;SE(F2=2;A3;A4))
B8 =SE(F2=1;B2;SE(F2=2;B3;B4))
41
C8 =SE(F2=1;C2;SE(F2=2;C3;C4))
D8 =SE(F2=1;D2;SE(F2=2;D3;D4))
 
Selecione as células de A7 a D8.
Clique na câmera e clique novamente na Plan2 no novo desenho.
 
Copie e cole uma nova figura (azul).
Página 33/108
42
Excel 2010 – Dashboard - Incrementando seus conhecimentos e
Aperfeiçoando a sua prática Inserindo Controles –Caixa de Seleção
Insira uma caixa de combinação.
 
Clique com o botão direito do mouse na barra, propriedades e altere o
controle.
 
Na plan1 copie de A1 a D2 para a célula A10.
Digite:
A10 =SE(F3=1;A2;SE(F3=2;A3;A4))B10 =SE(F3=1;B2;SE(F3=2;B3;B4))
C10 =SE(F3=1;C2;SE(F3=2;C3;C4))
D10=SE(F3=1;D2;SE(F3=2;D3;D4))
 
Selecione as células de A10 a D11.
43
Clique na câmera e clique novamente na Plan2 no novo desenho.
 
Página 34/108
Excel 2010 – Dashboard - Incrementando seus conhecimentos e
Aperfeiçoando a sua prática Protegendo a planilha contra alterações sem
usar senha – recurso VBA Com a planilha aberta pressione Alt + F11
 
Dê um duplo clique em EstaPasta_de_trabalho.
44
 
Selecione a opção Workbook
Digite:
 
Salve a planilha com a extensão xlsm (de macro).
Feche a planilha e abra-a novamente.
 
Para destravar (voltar atrás), pressione Alt + F11, duplo clique em
EstaPasta_de_trabalho e apague a linha do meio (Active ....).
Página 35/108
Excel 2010 – Dashboard - Incrementando seus conhecimentos e
Aperfeiçoando a sua prática CARREGAR ARQUIVO TEXTO E RETIRAR
ITENS DUPLICADOS
 
 OBJETIVO
 
Tem como finalidade separar coluna de dados e retirar itens duplicados.
 
 PROCEDIMENTO
Abra o bloco de notas (botão iniciar, executar, digite notepad e tecle enter)
45
e digite os dados conforme figura abaixo.
Os dados abaixo estão dispostos da seguinte maneira: Nome
Idade
Estado Civil
Profissão.
Nome que o nome Maria da Silva foi repetido propositalmente.
 
►Avancado.txt
Você pode copiar os dados e colar na célula A1, ou salvar o arquivo no seu
Desktop e abri-lo no Excel.
Página 36/108
46
Excel 2010 – Dashboard - Incrementando seus conhecimentos e
Aperfeiçoando a sua prática Selecione todos os dados.
Copie e cole na célula A1.
 
Selecione a coluna A.
No menu clique em Dados, Texto para Colunas.
 
Página 37/108
47
Excel 2010 – Dashboard - Incrementando seus conhecimentos e
Aperfeiçoando a sua prática Escolha a opção delimitado, Ponto e vírgula e
concluir.
 
Note que Maria da Silva apareceu 2 vezes.
 
Vamos falar para o Excel que se o nome que estiver na coluna A se repetir,
ele deverá excluir a linha que estiver em duplicidade.
Selecione as colunas de A a D.
Página 38/108
48
Excel 2010 – Dashboard - Incrementando seus conhecimentos e
Aperfeiçoando a sua prática Clique no menu, Dados, Remover Duplicadas.
 
Escolha o item que deverá ser verificada a duplicidade. Neste Exemplo,
clique na coluna A.
49
 
Página 39/108
Excel 2010 – Dashboard - Incrementando seus conhecimentos e
Aperfeiçoando a sua prática TRABALHANDO COM A CAIXA DE
NOMES
 
 OBJETIVO
Esta dica tem como finalidade mostrar como você poderá somar um grupo
de valores.
 EXEMPLO I
Crie uma planilha conforme o exemplo abaixo.
50
 
Selecione as células de A2 a F3 e dê o nome para este range de semestre1.
 
Faça o mesmo para as células de G2 a L3 e dê o nome para este range de
semestre2.
 
Na célula O2 faça a soma de semestre1. Para isso, basta digitar na barra de
fórmulas
a seguinte expressão: =SOMA(semestre1) e faça o mesmo para a célula O4
com semestre2.
 
Página 40/108
51
Excel 2010 – Dashboard - Incrementando seus conhecimentos e
Aperfeiçoando a sua prática
52
SOMANDO VALORES PARA DIFERENTES
PLANILHAS
 OBJETIVO
Neste exemplo, vamos supor que você tenha diversas planilhas de trabalho,
dentro de um único arquivo Excel e que você precise somar todo o
montante dos valores que encontram-se em uma mesma célula mas que estão
nas diferentes planilhas.
 EXEMPLO I
Na Planilha, crie as seguintes pastas: Plan1, < , Plan2, Plan3, Plan4, Plan5,
> , Plan6
 
Digite os seguintes valores na Plan1 conforme exemplo abaixo.
 
Note que na célula A1 sempre temos o texto valor e na célula B2 temos um
valor para cada planilha:
 
Plan2 = 2.000,00
Plan3 = 3.000,00
Plan4 = 4.000,00
53
Plan5 = 5.000,00
Total de 14.000,00
 
Na Plan1 na célula B1 digite =SOMA('<:>'!B1)
 
Note que todas as planilhas que estão contidas entre <> foram somadas na
célula indicada.
 EXEMPLO II
 
Página 41/108
Excel 2010 – Dashboard - Incrementando seus conhecimentos e
Aperfeiçoando a sua prática ATINGIR META – PONTO DE EQUILÍBRIO
 
 OBJETIVO
54
Atingir meta tem como finalidade obter um valor de entrada de uma
determinada fórmula para atingir um resultado conhecido.
 EXEMPLO I
Digamos que seja prestado um serviço no valor de R$ 1.000,00. Se você
tiver que pagar 27,5% de imposto, qual deveria ser o valor cobrado para
que ao final você tenha líquido o valor de R$ 1.000,00.
Na célula B2 colocamos o valor de R$ 1,00.
Na célula B3 o imposto desejado.
Na célula B4 temos o cálculo do valor de R$ 1,00 descontando o imposto.
Vamos utilizar o Atingir Meta e colocar como variável o valor final para
que atinja o valor de R$ 1.000,00
 
No menu Dados, clique em Teste de Hipóteses e depois em Atingir Meta
Página 42/108
55
Excel 2010 – Dashboard - Incrementando seus conhecimentos e
Aperfeiçoando a sua prática Definir célula: é o valor final do resultado
calculado.
Para valor: é o valor final que deseja ser atingido.
Alternando célula: é a célula do valor inicial.
 
Página 43/108
56
Excel 2010 – Dashboard - Incrementando seus conhecimentos e
Aperfeiçoando a sua prática EXEMPLO II
Vamos supor que você queira fazer um empréstimo junto ao Banco. Você
quer obter o valor de R$ 50.000,00 para pagar em 120 meses (10 anos) e
sabe que o juros será sempre de 3%.
 
Vamos ter uma prestação mensal de R$ 1.544,50. Analisando suas contas,
chegou-se à conclusão que a prestação máxima que você poderá pagar será
de R$ 1.000,00.
Vamos calcular então, qual o valor que você poderá pegar emprestado para
esta nova condição.
 
Teremos a seguinte resposta.
 
Portanto nestas condições, o valor máximo do empréstimo que você poderá
obter junto ao banco será de R$ 32.373,02.
 
Página 44/108
57
Excel 2010 – Dashboard - Incrementando seus conhecimentos e
Aperfeiçoando a sua prática EXEMPLO III
Você tem uma empresa e quer lançar um produto novo no mercado. Você
deseja saber qual o ponto de equilíbrio para as vendas deste produto.
58
 
Vamos utilizar o Atingir Meta, onde:
Definir Célula=$B$15 = Resultado do ponto de equilíbrio Para valor =0
Quando o valor for zero, as receitas serão iguais as despesas
Alternando célula: $D$4 Quantidade dos produtos vendidos.
 
Teremos a seguinte resposta: Será necessário vender 5.631 peças.
 
Página 45/108
Excel 2010 – Dashboard - Incrementando seus conhecimentos e
Aperfeiçoando a sua prática
59
60
MATRIZ
 OBJETIVO
Monte uma planilha matriz conforme figura abaixo.
 
Página 46/108
Excel 2010 – Dashboard - Incrementando seus conhecimentos e
Aperfeiçoando a sua prática FORMATAÇÃO CONDICIONAL –
Setas/Farol
 
 OBJETIVO
Montar um gráfico com setas indicando a participação dos vendedores no
61
mês de fevereiro contra janeiro.
 EXEMPLO I
Duplique a planilha matriz.
Dê o nome para a nova planilha de Form_cond
Acrescente duas colunas:
Coluna G = Fev – Jan
Coluna H = % Fev / Jan
 
Exemplo:
G2 =+F2-E2
H2 =+F2/E2-1
 
Selecione as células de H2 a H22. Entre no menu Início, Formatação
Condicional, Conjunto de ícones e escolha a opção de 3 setas.
Página 47/108
62
Excel 2010 – Dashboard - Incrementando seus conhecimentos e
Aperfeiçoando a sua prática
Entre no menu Início, Formatação Condicional, Gerenciar Regras, Editar
Regras.
Formate a regra para que fique conforme figura abaixo: No lugar de
percentual, mude para número e divida os valores por 100.
 
Página 48/108
63
Excel 2010 – Dashboard - Incrementando seus conhecimentos e
Aperfeiçoando a sua prática Vamos obter a seguinte tela:
 
Copie à partir da coluna J, as colunas C, E, F, G e H.
Selecione J2 a N22.
Entre no menu Início,Formatação Condicional, Gerenciar Regras, Editar
Regras e clique em mostrar apenas ícones.
 
Na célula P2 coloque a função hoje().
Formate a célula (Ctrl+1) para dd mmmm aaaa em personalizado.
Abra uma nova planilha, dê o nome de Form_cond_dash.
Retire as linhas de grade (Layout de página).
No menu, inserir, formas, retângulo e coloque o retângulo compreendendo
64
as células de A1 a D2. Deixe-o com a cor verde.
Clique sobre a figura e em f(x) digite =Form_cond!P1
Página 49/108
65
Excel 2010 – Dashboard - Incrementando seus conhecimentos e
Aperfeiçoando a sua prática
66
MONTANDO SCROLL DE TELA
Monte uma tabela conforme figura abaixo.
 
Vamos inserir uma barra de rolagem. Menu, desenvolvedor, inserir, barra
de rolagem.
Ela deverá ficar entre B4 a B10 e seu controle posicional deverá ficar na
Plan Form_cond célula Q1.
 
Página 50/108
Excel 2010 – Dashboard - Incrementando seus conhecimentos e
Aperfeiçoando a sua prática Digite nas células
A5 = =DESLOC(Form_cond!J1:N22;Form_cond!$Q$1;0;1;1)
Arraste de A5 a A10.
 
C5 =DESLOC(Form_cond!J1:N22;Form_cond!$Q$1;1;1;1)
 
67
D5 =DESLOC(Form_cond!J1:N22;Form_cond!$Q$1;2;1;1)
 
E5 =DESLOC(Form_cond!J1:N22;Form_cond!$Q$1;3;1;1)
 
F5 =DESLOC(Form_cond!J1:N22;Form_cond!$Q$1;4;1;1)
 
Selecione a célula N2 da planilha Form_cond, clique em formatar pincel e
clique na célula F5 da plan Form_cond_dash.
 
Selecione de C5 a F5 e arraste até a linha 10.
 
Formate os valores para 0 (zero) casas decimais e deixe os valores
negativos entre parênteses.
 
Página 51/108
68
Excel 2010 – Dashboard - Incrementando seus conhecimentos e
Aperfeiçoando a sua prática TABELAS DINÂMICAS
 
Um relatório de tabela dinâmica consiste em uma forma interativa onde
podemos resumir grandes quantidades de dados.
 
Utilizamos este tipo de tabela para análise sintética de dados.
 
Os relatórios de Tabela Dinâmica servem principalmente para: Consultar
grandes volumes de dados de várias formas diferentes.
Subtotalizar e agregar dados numéricos, resumindo dados por categorias e
subcategorias.
 
Vamos nos utilizar do seguinte exemplo
69
 
Vamos selecionar a área desejada. Neste caso será de A1 a D8.
Após esta seleção, clique na aba Inserir, Tabela Dinâmica, Tabela
Dinâmica.
 
Página 52/108
Excel 2010 – Dashboard - Incrementando seus conhecimentos e
Aperfeiçoando a sua prática Ao clicar em Tabela Dinâmica, já temos
70
preenchido o intervalo selecionado na caixa de diálogo.
Clique em Local: e clique em qualquer célula, local onde será montada a
tabela.
Ex. F1
 
Uma lista de campos será apresentada.
Estes campos são os cabeçalhos de cada
coluna selecionada.
 
Página 53/108
71
Excel 2010 – Dashboard - Incrementando seus conhecimentos e
Aperfeiçoando a sua prática Clique na caixa ao lado de cada campo para
selecioná-lo.
Arraste o campo entre as opções:
-
Filtro de Relatório
-
Rótulos de coluna
-
Rótulos de linha
-
Valores
72
 
Clique com o botão da esquerda do mouse, para fazer alteração dos
campos.
 
Página 54/108
Excel 2010 – Dashboard - Incrementando seus conhecimentos e
Aperfeiçoando a sua prática Altere o resultado conforme a sua necessidade.
Exemplo: Formatar a apresentação dos números
 
Página 55/108
73
Excel 2010 – Dashboard - Incrementando seus conhecimentos e
Aperfeiçoando a sua prática Transformando a tabela em valores percentuais
sobre o total.
Clique na célula F1 e com o botão direito do mouse escolha a opção
Resumir Dados por, Mais opções, Mostrar valores como, % do total.
 
74
Vamos voltar à tabela à sua forma normal.
Clique na célula F6 e com o botão direito do mouse escolha a opção
Resumir Dados por, Mais opções, Mostrar valores como, Normal.
Página 56/108
75
Excel 2010 – Dashboard - Incrementando seus conhecimentos e
Aperfeiçoando a sua prática
76
Adicionando um campo calculado.
Clique na tabela dinâmica para acessar o menu Ferramentas de Tabela
Dinâmica (parte superior).
 
Sobre o valor vamos acrescentar o ICMS no valor de 25% e acrescentar
também um valor líquido.
Clique na tabela dinâmica, no Menu, Ferramenta de Tabela Dinâmica (parte
superior da tela), Opções, Fórmulas e insira um campo calculado.
 
Vamos adicionar o campo ICMS e o campo Total Líquido
 
Página 57/108
Excel 2010 – Dashboard - Incrementando seus conhecimentos e
Aperfeiçoando a sua prática Veja o resultado.
77
 
Página 58/108
Excel 2010 – Dashboard - Incrementando seus conhecimentos e
Aperfeiçoando a sua prática
78
Agrupando datas.
Deixe a tabela dinâmica conforme a figura abaixo.
 
Clique na célula F2. No Menu, Ferramenta de Tabela Dinâmica, Opções,
Agrupar Seleção.
 
Página 59/108
79
Excel 2010 – Dashboard - Incrementando seus conhecimentos e
Aperfeiçoando a sua prática Em agrupamento selecione (dias, meses, ano –
faça isso segurando a tecla Control ) e digite as datas.
 
Repare em todos os campos que foram criados na tabela dinâmica.
 
Escolha apenas Valor e Meses.
 
Página 60/108
80
Excel 2010 – Dashboard - Incrementando seus conhecimentos e
Aperfeiçoando a sua prática TABELA DINÂMICA UTILIZANDO VÁRIAS
PLANILHAS
 OBJETIVO
81
Montar um relatório de tabela dinâmica utilizando vários intervalos.
OBS: Se você tiver várias planilhas, junte todas em uma única planilha.
82
EXEMPLO
Lojas.xlsx
 
Abra uma nova planilha.
 
Clique no Menu, Dados, Obter Dados Externos, De Outras Fontes, Do
Microsoft Query.
 
Página 61/108
83
Excel 2010 – Dashboard - Incrementando seus conhecimentos e
Aperfeiçoando a sua prática Escolha a opção Excel Files*
 
Vamos escolher a planilha – Lojas.xlsx
 
Se as folhas de dados não aparecerem, clique em opções e marque os itens
conforme as próximas figuras.
 
84
Página 62/108
85
Excel 2010 – Dashboard - Incrementando seus conhecimentos e
Aperfeiçoando a sua prática Marque os itens conforme figura abaixo
 
Clique na seta para a direita e adicione os campos apenas de Loja1
 
Você irá obter este resultado.
 
Clique em avançar por três vezes até que encontre a tela abaixo.
Escolha a opção Exibir dados ou editar consulta no Microsoft Query e
clique em concluir.
 
Página 63/108
86
Excel 2010 – Dashboard - Incrementando seus conhecimentos e
Aperfeiçoando a sua prática Você irá obter a seguinte tela.
Clique na caixa SQL.
87
 
Selecione todo o texto e dê um Control C para copiar para a área de
transferência.
 
Ao final escreva UNION ALL
 
Dê um Control V (colar).
 
Página 64/108
88
Excel 2010 – Dashboard - Incrementando seus conhecimentos e
Aperfeiçoando a sua prática Vamos repetir o processo.
Ao final escreva UNION ALL
 
Dê um Control V (colar).
 
Note que temos agora três partes.
Vamos alterar para ficar conforme figura abaixo, ou seja, um grupo para
cada loja.
 
Clique em Ok e na próxima tela clique em Ok novamente.
 
Página 65/108
89
Excel 2010 – Dashboard - Incrementando seus conhecimentos e
Aperfeiçoando a sua prática Vamos obter a seguinte tela
 
Feche a janela
90
 
Na próxima tela escolha relatório de Tabela Dinâmica
 
Página 66/108
Excel 2010 – Dashboard - Incrementando seus conhecimentos e
Aperfeiçoando a sua prática Monte a tabela dinâmica conforme a sua
necessidade.
 
Página 67/108
91
Excel 2010 – Dashboard - Incrementando seus conhecimentos e
Aperfeiçoando a sua prática PREENCHER CÉLULAS EM BRANCO DO
CAMPO CHAVE PARA TABELA DINÂMICA
 OBJETIVO
Como criar tabela dinamica quando as celulas chaves estiverem em branco.
 EXEMPLO
 
Como criar uma tabeladinâmica quando as células chaves estiverem em
branco.
 
92
Digamos que você recebeu um determinado relatório e necessita por algum
motivo fazer uma tabela dinâmica.
 
Se simplesmente as células B1 a D14 fossem selecionadas e aplicadas
sobre elas uma tabela dinâmica teríamos o seguinte resultado.
 
Note que a coluna J apresentou a somatória dos valores por filial cuja
coluna B estavam em branco.
 
Como resolver este problema ? Simples, usaremos a função SE.
 
Página 68/108
93
Excel 2010 – Dashboard - Incrementando seus conhecimentos e
Aperfeiçoando a sua prática Na célula A1 repita o título da célula B1 que
será nosso campo chave.
 
Na célula A2 digite o comando SE descrito abaixo e arraste-o até o final da
planilha onde possuir valores.
 
=SE(B2="";A1;B2) (aspas duplas duas vezes “”)
 
Agora sim, você poderá fazer a sua tabela dinâmica sem prejuízo dos
valores a serem apresentados.
 
Página 69/108
94
Excel 2010 – Dashboard - Incrementando seus conhecimentos e
Aperfeiçoando a sua prática GRÁFICO DE TABELA DINÂMICA
 OBJETIVO
Montar um gráfico em conjunto com uma tabela dinâmica.
 EXEMPLO I
Monte uma planilha igual à figura abaixo:
95
 
►Utilize o Arquivo 15
Vamos selecionar a área desejada. Neste caso será de A1 a F22.
Após esta seleção, clique na aba Inserir, Tabela Dinâmica, Gráfico
Dinâmico.
 
Página 70/108
Excel 2010 – Dashboard - Incrementando seus conhecimentos e
Aperfeiçoando a sua prática Clique em planilha existente e local I1.
 
Selecione a célula M1
Em f(x) coloque = k7
Retornou a função
=INFODADOSTABELADINÂMICA("Soma de
Fev";$I$3;"Regional";"RJ") Somou os valores de Fevereiro para a
Regional RJ.
96
 
Página 71/108
Excel 2010 – Dashboard - Incrementando seus conhecimentos e
Aperfeiçoando a sua prática Inserindo um controle de botão
 
97
Clique em qualquer parte da tabela dinâmica para acessar Ferramentas de
Tabela Dinâmica.
 
Clique em opções e altere o nome da tabela para tab1.
 
Menu, Desenvolvedor, Inserir, Controles ActiveX, Botão de Comando.
 
Crie um botão.
 
Página 72/108
Excel 2010 – Dashboard - Incrementando seus conhecimentos e
Aperfeiçoando a sua prática Pressione Alt + F11
 
Página 73/108
98
Excel 2010 – Dashboard - Incrementando seus conhecimentos e
Aperfeiçoando a sua prática GRÁFICO DE COLUNAS
 
 OBJETIVO
Montar um gráfico de colunas.
 EXEMPLO I
Na planilha Form_cond vamos acrescentar mais alguns dados.
 
A meta de vendas a ser atingida e quanto cada carro vendeu no mês.
 
►Utilize o Arquivo 15
Na célula:
Q4 = Meta
R4 = 400 (valor dado no problema)
 
Q6=Veículo
R6=Jan
S6=Fev
99
 
Q7=Fiesta
R7=SOMASE(J2:J22;Q7;K2:K22)
S7=SOMASE(J2:J22;Q7;L2:L22)
 
Q8=Gol
R8=SOMASE(J2:J22;Q8;K2:K22)
S8=SOMASE(J2:J22;Q8;L2:L22)
 
Q9=Sandero
R9 =SOMASE(J2:J22;Q9;K2:K22)
S9 =SOMASE(J2:J22;Q9;L2:L22)
 
Selecione de Q6 a S9.
No menu, inserir, gráfico de colunas (primeira opção).
 
Clique na Legenda do lado direito.
Com o botão direito do mouse, escolha a opção Formatar Legenda.
Em opções de Legenda clique no item inferior.
 
Retire as linhas de grade horizontais.
Retire a legenda do lado esquerdo.
100
Clique com o botão direito na área do gráfico e formate a área.
 
Em cor de borda coloque a opção sólida, cor azul e em estilo de borda
marque a opção bordas arredondadas.
 
Página 74/108
Excel 2010 – Dashboard - Incrementando seus conhecimentos e
Aperfeiçoando a sua prática
 
Página 75/108
101
Excel 2010 – Dashboard - Incrementando seus conhecimentos e
Aperfeiçoando a sua prática Inserindo Botão de Opção
 
Vamos inserir a ordem dos carros mais vendidos
 
Na planilha Form_cond_dash, coloque dois botões de opção logo abaixo do
gráfico.
Menu, desenvolvedor, Inserir, botão de opção.
 
No primeiro escreva Janeiro e no segundo Fevereiro.
 
102
Faça a opção da escolha ser direcionada para a planilha Form_cond na
célula S1
 
Na planilha Form_cond digite:
Q11 = primeiro
Q12 = segundo
Q13 = terceiro
 
R11 =MAIOR(SE(S1=1;R7:R9;S7:S9);1)
R12 =MAIOR(SE(S1=1;R7:R9;S7:S9);2)
R13 =MAIOR(SE(S1=1;R7:R9;S7:S9);3)
 
S11 =CORRESP(R11;SE(S1=1;R7:R9;S7:S9);0)
S12 =CORRESP(R12;SE(S1=1;R7:R9;S7:S9);0)
S13 =CORRESP(R13;SE(S1=1;R7:R9;S7:S9);0)
 
T11 =INDIRETO("q"&6+S11)
T12 =INDIRETO("q"&6+S12)
T13 =INDIRETO("q"&6+S13)
 
Crie 3 caixas para primeiro, segundo e terceiro lugares em
Form_cond_dash.
 
103
Clique na primeira caixa e direcione o valor para T11, a segunda para T12
e a terceira caixa para T13.
 
Página 76/108
Excel 2010 – Dashboard - Incrementando seus conhecimentos e
Aperfeiçoando a sua prática
104
Inserindo Check Box
Vamos inserir dois controles de Caixa de Seleção (Check Box) com duas
Caixas de Combinação na Planilha Form_cond_dash.
 
No Menu, Desenvolvedor, Inserir, Chebox (Caixa de seleção).
 
Clique na caixa de combinação com o botão direito do mouse e em
propriedades marque para que seu resultado seja colocado na planilha
Form_cond na célula U1.
 
Faça o mesmo para a segunda caixa, colocando seu resultado em V1.
Todas as vezes que uma delas receber um clique, seu resultado será a
palavra VERDADEIRO nas células (U1 ou V1) da planilha Form_cond.
 
Clique na caixa de seleção com o botão direito do mouse e em
propriedades marque a lista sendo as células de Q7, Q8 e Q9 e coloque o
resultado em U2 da planilha Form_cond.
 
Faça o mesmo para a segunda caixa.
 
105
Selecione as células na planilha Form_cond:
R7 e S7 e marque na caixa de nomes o valor fiesta
R8 e S8 e marque na caixa de nomes o valor gol
R9 e S9 e marque na caixa de nomes o valor sandero
R10 e S10 e marque na caixa de nomes o valor branco
 
Página 77/108
Excel 2010 – Dashboard - Incrementando seus conhecimentos e
106
Aperfeiçoando a sua prática Digite:
Aqui teremos o respectivo nome que será colocado como faixa no gráfico
U3=SE(U1=VERDADEIRO;ÍNDICE(Q7:Q9;U2);"branco")
V3=SE(V1=VERDADEIRO;ÍNDICE(Q7:Q9;V2);"branco")
Aqui teremos o nome de cada barra do gráfico
U4 =SE(U3="branco";"";U3)
V4 =SE(V3="branco";"";V3)
 
No Menu, Fórmulas, Definir Nome vamos criar 2 nomes: opcao1 e opcao2
que serão as faixas do próximo gráfico.
 
No menu, inserir gráfico de colunas (primeira opção).
Clique com o botão direito do mouse e escolha a opção Selecionar Dados.
Clique em Adicionar.
 
Para a primeira série marque
 
Página 78/108
107
Excel 2010 – Dashboard - Incrementando seus conhecimentos e
Aperfeiçoando a sua prática Para a segunda série marque
 
Para o eixo horizontal marque
108
 
Página 79/108
Excel 2010 – Dashboard - Incrementando seus conhecimentos e
Aperfeiçoando a sua prática GRÁFICO DE TERMÔMETRO
 OBJETIVO
Montar um gráfico de termômetro.
 EXEMPLO I
Monte uma tabela igual à figura abaixo:
 
No menu Inserir, escolha o gráfico de colunas, colunas empilhadas (segunda
opção).
Clique com o botão direito do mouse sobre o gráfico e escolha a opção
109
Selecionar Dados.
A primeira série será a venda (B2) e a segunda série será a meta (C2).
Retire os rótulos à direita e à abaixo e as linhas de grade ao centro.
 
Página 80/108
Excel 2010 – Dashboard - Incrementando seus conhecimentos e
110
Aperfeiçoando a sua prática Clique na primeira série (azul) com o botão
direito do mouse e altere a opção Formatar série de dados:
Opções de série
 
Preenchimento
 
Preenchimento Sólido – cor vermelha
 
Eixo do lado esquerdo
 
Clique em fechar.
 
Página 81/108
111
Excel 2010 – Dashboard - Incrementando seus conhecimentose
Aperfeiçoando a sua prática Clique na segunda série com o botão direito do
mouse e altere a opção Formatar série de dados:
Opções de série
 
Preenchimento
 
Preenchimento Sólido – cor verde
 
Eixo do lado direito
 
Clique em fechar.
 
112
Página 82/108
Excel 2010 – Dashboard - Incrementando seus conhecimentos e
Aperfeiçoando a sua prática Altere o tamanho para a forma desejada.
 
Para inserir um título, clique no gráfico, no menu, ferramenta de gráfico,
layout, título do gráfico, opção acima do gráfico. Quando aparecer o título,
clique nesta caixa e na função (fx) digite = e a célula desejada.
113
 
Página 83/108
Excel 2010 – Dashboard - Incrementando seus conhecimentos e
Aperfeiçoando a sua prática GRÁFICO DE ESTOQUE MÍNIMO
 
 OBJETIVO
Montar um gráfico de informativo de estoque mínimo.
114
 EXEMPLO I
Monte uma tabela igual à figura abaixo:
 
Selecione as células de A2 a C13. No menu Inserir, escolha o gráfico de
colunas 2D
(primeira opção). Vamos obter o gráfico conforme figura abaixo.
 
Página 84/108
115
Excel 2010 – Dashboard - Incrementando seus conhecimentos e
Aperfeiçoando a sua prática Clique com o botão direito na legenda, escolha
a Opções de Legenda, posição inferior.
 
Clique com o botão direito na área do gráfico (borda) e marque as opções:
Em formatar área do gráfico, marque Sem preenchimento no item
Preenchimento, na Cor da Borda marque sem linha e em Estilos de borda
marque cantos arredondados Selecione a série 2, com o botão direito do
mouse selecione a opção: Alterar tipo de gráfico de série. Escolha a opção
Linha e selecione Linhas com marcadores (4a.
opção).
Vamos obter o gráfico conforme figura abaixo.
 
Com o botão direito do mouse selecione a segunda série (linha) e na opção
Formatar Série de Dados, marque em opções de marcador: Interno tamanho
2.
 
Página 85/108
116
Excel 2010 – Dashboard - Incrementando seus conhecimentos e
Aperfeiçoando a sua prática Em Estilo da Linha marque a opção Linha
suavizada.
 
Clique com o botão direito no gráfico e na opção Selecionar Dados, altere
o nome das séries.
 
Selecione as linhas de grade e clique em delete.
117
 
Note que os meses de setembro e novembro, são os meses que temos um
alerta, ou seja, o estoque mínimo esta sendo sinalizado.
Vamos fazer uma faixa compreendendo todos os meses para mostrar esta
situação.
Selecione as células em que o gráfico esta compreendido e utilize a opção
câmera e cole a figura na planilha desejada.
Página 86/108
Excel 2010 – Dashboard - Incrementando seus conhecimentos e
Aperfeiçoando a sua prática Alinhe o gráfico (meses) com as células.
118
 
Vamos utilizar o espaço entre G2 e R2.
Na célula G2 colamos a fórmula: =SE(B2>C2;1;0)
 
Na célula H2 colamos a fórmula: =SE(B3>C3;1;0)
 
E assim faremos até completarmos os doze meses.
Selecione a célula G2, entre no menu Início, Formatação Condicional, em
gerenciar regra, nova regra e escolha a opção usar uma fórmula pra
determinar quais...
Página 87/108
119
Excel 2010 – Dashboard - Incrementando seus conhecimentos e
Aperfeiçoando a sua prática =G$2=1 e em formatar, preenchimento, escolha
a cor verde.
 
Repita o processo
=G$2=0 e em formatar, preenchimento, escolha a cor vermelha.
 
Selecione a célula G2, clique no pincel e arraste para as demais.
Selecione todas as células, tecle Ctrl + 1, em número, personalizado digite
;;; para ocultar os valores.
 
Teremos o seguinte efeito.
 
Selecione de G2 a R2 e clique na câmera e depois em qualquer outro lugar
da planilha.
Diminua o tamanho do desenho e arraste-o para a parte inferior do gráfico,
ao lado da legenda.
Página 88/108
120
Excel 2010 – Dashboard - Incrementando seus conhecimentos e
Aperfeiçoando a sua prática Teremos o seguinte efeito.
 
Página 89/108
121
Excel 2010 – Dashboard - Incrementando seus conhecimentos e
Aperfeiçoando a sua prática UTILIZAÇÃO DE DUAS CAIXAS DE
COMBINAÇÃO AO MESMO TEMPO
 OBJETIVO
Montar um menu hierarquizado.
 EXEMPLO I
Monte uma tabela igual à figura abaixo:
 
Note que deixamos o espaço para serem preenchidos até 7 cargos e estamos
trabalhando com 4 áreas.
 
Vamos usar como suporte a segunda parte, ou seja, das células A15 a C22
que não deverão ser alteradas.
 
Como são 4 áreas vamos selecionar 4 linhas: de A16 a A19.
 
Página 90/108
122
Excel 2010 – Dashboard - Incrementando seus conhecimentos e
Aperfeiçoando a sua prática Selecione as células de A16 a A19.
Em f(x) digite =TRANSPOR( e aperte a tecla f(x) e selecione de A2 a D2
conforme figura abaixo.
123
 
Não tecle <enter>. Pressione ao mesmo tempo Ctrl + Shift + Enter e irá
obter o resultado conforme a figura abaixo.
 
A isso damos o nome de fórmula matricial.
 
Vamos montar uma caixa de seleção para a lista de A16 a A19.
Página 91/108
124
Excel 2010 – Dashboard - Incrementando seus conhecimentos e
Aperfeiçoando a sua prática Clique em A11 e vá até o menu,
Desenvolvedor, Inserir, Controle de formulário, Caixa de combinação.
 
A lista deverá ser de A16 a A19 e o resultado deverá ser colocado em C16,
conforme figura abaixo.
 
A cada item escolhido teremos o seu valor em C16.
 
Página 92/108
125
Excel 2010 – Dashboard - Incrementando seus conhecimentos e
Aperfeiçoando a sua prática Selecione as células B16 a B22
Em f(x) digite =DESLOC( e aperte a tecla f(x) e selecione os dados
conforme figura abaixo.
 
126
Não tecle <enter>. Pressione ao mesmo tempo Ctrl + Shift + Enter e irá
obter o resultado conforme a figura abaixo.
 
Note que apareceu o valor zero. Nas células de A3 a D9, onde não houver
cargo, digite um espaço em branco.
 
Vamos montar uma caixa de seleção para a lista de B16 a A22 e o seu
resultado coloque em D16.
Página 93/108
Excel 2010 – Dashboard - Incrementando seus conhecimentos e
Aperfeiçoando a sua prática
127
 
Página 94/108
Excel 2010 – Dashboard - Incrementando seus conhecimentos e
Aperfeiçoando a sua prática GRÁFICO PARA MEDIR VISITAS EM SITE
POR HORA
 
 OBJETIVO
Montar um gráfico de informativo de visitas em site.
 EXEMPLO I
Monte uma tabela igual à figura abaixo:
 
128
No menu Inserir, escolha o gráfico de linhas, Linhas com Marcadores
(quarta opção).
Clique no gráfico com o botão da direita do mouse e escolha a opção
Selecionar Dados.
Adicione uma nova série.
Valores da série: C2 a C22
Rótulos da série (eixo horizontal): A2 a B22
Retire a legenda do lado direito.
Página 95/108
129
Excel 2010 – Dashboard - Incrementando seus conhecimentos e
Aperfeiçoando a sua prática Vamos obter o gráfico conforme figura abaixo.
 
Clique com o botão direito na série do gráfico (linha azul) e escolha a
opção: formatar série de dados, opções de marcador, mude no item interno,
desenho quadrado e tamanho 12. Na opção Preenchimento do marcador,
mude a cor para vermelho, na opção cor da linha, opção sólido coloque a
cor preta.
 
Clique com o botão direito na área do gráfico (borda) e mude em formatar
área do gráfico: preenchimento: sem preenchimento e cor de borda para
sem linha.
 
Clique sobre o gráfico. No menu em Ferramentas de Gráfico, Layout,
Linhas de grade, Linhas de grade verticais, escolha linha de grades
principais.
Vamos obter o gráfico conforme figura abaixo.
 
Página 96/108
130
Excel 2010 – Dashboard - Incrementando seus conhecimentos e
Aperfeiçoando a sua prática Para incrementar ainda mais o seu gráfico,
podemos criar pequenos gráficos de colunas, como na figura abaixo. Cada
gráfico representa um dia.
 
Para alinhar cada gráfico a cada dia, selecione as células em que o gráfico
esta compreendido e clique na câmera. Acerteo tamanho das células com o
gráfico.
 
131
Clique nos valores de cada dia e monte um gráfico de colunas. O gráfico
deverá ter o mesmo tamanho da célula. Utilize o botão câmera e cole o
gráfico embaixo de cada dia.
 
Página 97/108
Excel 2010 – Dashboard - Incrementando seus conhecimentos e
Aperfeiçoando a sua prática Vamos montar um novo gráfico.
No menu, inserir, gráfico de barra agrupada (primeira opção).
Adicionar uma nova série.
 
Adicionar série para eixo horizontal
132
 
Clique no gráfico com o botão direito do mouse, opção Formatar serie de
dados e na opção preenchimento escolha várias cores por ponto.
 
Página 98/108
133
Excel 2010 – Dashboard - Incrementando seus conhecimentos e
Aperfeiçoando a sua prática Retire as legendas.
Clique no gráfico com o botão direito do mouse, opção Formatar serie de
dados e em opções de série marque o valor 94 para largura.
 
Clique com o botão direito na área do gráfico (borda) e mude em formatar
área do gráfico: preenchimento: sem preenchimento e cor de borda para
sem linha.
 
Página 99/108
134
Excel 2010 – Dashboard - Incrementando seus conhecimentos e
Aperfeiçoando a sua prática GRÁFICO DE VELOCÍMETRO
 OBJETIVO
Montar um gráfico de velocímetro.
135
 EXEMPLO I
Monte uma tabela igual à figura abaixo:
 
Selecione as células de B2 a B5 e no menu, inserir, gráfico, escolha a
opção Rosca (primeira opção).
 
Página 100/108
136
Excel 2010 – Dashboard - Incrementando seus conhecimentos e
Aperfeiçoando a sua prática Clique sobre o gráfico e depois clique sobre a
maior fatia, com o botão direito do mouse, selecione Formatar Pontos de
Dados, em opções de série, em ângulo mude para 90.
 
Em preenchimento clique em sem preenchimento e feche a janela e vamos
obter o resultado como na figura abaixo.
 
Clique sobre o gráfico uma vez para que ele seja selecionado, botão direito
do mouse e escolha Selecionar dados.
 
Página 101/108
137
Excel 2010 – Dashboard - Incrementando seus conhecimentos e
Aperfeiçoando a sua prática Adicione uma nova série de C2 a C13:
 
Clique em adicionar novamente E2 a E5 (série da agula).
 
Clique com o botão direito sobre o gráfico, Selecionar Dados e coloque o
nome Disc para a série1, para a série2 Rotdisc e para a série3 agulha.
138
Página 102/108
Excel 2010 – Dashboard - Incrementando seus conhecimentos e
Aperfeiçoando a sua prática Dê um duplo clique na borda do gráfico para
ativar a guia Design e escolha o estilo 42.
 
139
Apague a legenda.
Clique sobre o fundo do gráfico e em formatar área do gráfico e em
preenchimento escolha sem preenchimento.
 
Clique como botão direito do mouse sobre a série rotdisc e adicione rótulo
de dados.
 
Clique novamente como botão direito do mouse sobre a série rotdisc e
escolha a opção Selecionar Dados.
 
Página 103/108
140
Excel 2010 – Dashboard - Incrementando seus conhecimentos e
Aperfeiçoando a sua prática Selecione Rotdisc, editar (do lado direito –
eixo horizontal). Inserir intervalo de D2 a D13.
Selecione Rotdisc e com o botão direito do mouse selecione Formatar
rótulo de dados.
Desmarque o valor e selecione o nome da categoria.
 
Selecione Rotdisc e com o botão direito do mouse selecione Formatar série
de dados.
Em Preenchimento coloque sem preenchimento.
Mude a cor da fonte dos rótulos de Rotdisc para preto.
 
Clique na série agulha e com o botão direito do mouse altere o tipo de
gráfico para pizza (primeira opção).
 
Página 104/108
141
Excel 2010 – Dashboard - Incrementando seus conhecimentos e
Aperfeiçoando a sua prática Clique na série agulha e com o botão direito
do mouse escolha Formatar série de dados.
 
Em opções de série, em ângulo coloque 90 e em plotar selecione eixo
secundário.
142
 
Clique sobre o gráfico e depois sobre a maior faixa, e com o botão direito
do mouse selecione Formatar ponto de dados.
Em preenchimento clique em sem preenchimento (não feche a janela).
Clique sobre a área de 60 a 100, e remova o preenchimento.
Clique sobre a área de 0 a 50, e remova o preenchimento.
 
Clique sobre o gráfico e depois sobre a maior faixa, e com o botão direito
do mouse selecione Formatar ponto de dados. A seleção deverá estar
conforme a figura abaixo, somente e maior faixa.
 
Em preenchimento clique em sem preenchimento.
Página 105/108
143
Excel 2010 – Dashboard - Incrementando seus conhecimentos e
Aperfeiçoando a sua prática
Compreendendo o gráfico
 
As células:
De A2 a A5 representam as faixas de cores do gráfico (podem ser
alteradas).
De B2 a B5 correspondem às fatias do gráfico (não altere os valores).
De C2 a C13 são os rótulos (não altere os valores).
De D2 a D13 são os rótulos do velocímetro (podem ser alteradas).
A célula E4 altera o tamanho da agulha.
144
Para alterar a cor da agulha, na célula E4 mude o valor para 10, clique
somente na agulha até que somente ela esteja selecionada.
 
Página 106/108
Excel 2010 – Dashboard - Incrementando seus conhecimentos e
Aperfeiçoando a sua prática Clique com o botão direito do mouse e
selecione Formatar ponto de dados, em preenchimento escolha
preenchimento sólido e mude a cor para preto e na célula E4
coloque o valor 1.
 
Vamos selecionar a célula F3 como sendo o valor desejado a ser mostrado
no gráfico.
145
Coloque os seguintes valores nas células:
F3 = 50.
D2 =F3&"%"
E3 = ((180/100)*F3)-1
E5 =360-SOMA(E2:E4)
 
Para finalizar, vamos clicar em cada segmento do gráfico (cada cor) e
mudar para vermelho, amarelo e verde conforme figura abaixo. Clique no
segmento com o botão direito do mouse, altere Formatar pontos de dados,
em preenchimento selecione preenchimento sólido e mude a cor.
 
No menu inserir, formas, escolha 3 retângulos e pinte-os um de cada cor
conforme a figura abaixo.
 
Página 107/108
Excel 2010 – Dashboard - Incrementando seus conhecimentos e
146
Aperfeiçoando a sua prática Abaixo temos a tela final do seu gráfico
 
Página 108/108
147