147

Excel 2010 – Dashboard - infobitsolucoes.com em Excel - Volum… · Excel 2010 – Dashboard - Incrementando seus conhecimentos e Aperfeiçoando a sua prática Página 1/108 Excel

  • Upload
    vandan

  • View
    300

  • Download
    13

Embed Size (px)

Citation preview

Page 1: Excel 2010 – Dashboard - infobitsolucoes.com em Excel - Volum… · Excel 2010 – Dashboard - Incrementando seus conhecimentos e Aperfeiçoando a sua prática Página 1/108 Excel
Page 2: Excel 2010 – Dashboard - infobitsolucoes.com em Excel - Volum… · Excel 2010 – Dashboard - Incrementando seus conhecimentos e Aperfeiçoando a sua prática Página 1/108 Excel

Excel 2010 – Dashboard - Incrementando seus conhecimentos eAperfeiçoando a sua prática Página 1/108

Excel 2010 – Dashboard - Incrementando seus conhecimentos eAperfeiç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 paraprofissionais de diversas áreas (financeira, contabilidade, economia,administração), micro e pequenas empresas onde se busca uma melhoranálise e gerência de informações. A seguir você poderá acompanhar todo oconteúdo desta apostila.

Acesse também www.ensinandoexcel.com.br

Um bom estudo a todos e espero, sinceramente, que este pequeno e-bookpossa ajudá-los a enriquecer seus conhecimentos e proporcionar umamelhor posição no mercado de trabalho.

Abraços

José Eduardo

Página 2/108

2

Page 3: Excel 2010 – Dashboard - infobitsolucoes.com em Excel - Volum… · Excel 2010 – Dashboard - Incrementando seus conhecimentos e Aperfeiçoando a sua prática Página 1/108 Excel

Excel 2010 – Dashboard - Incrementando seus conhecimentos eAperfeiçoando a sua prática

Excel 2010 – DAshboard

Incrementando

Seus conhecimentos

Aperfeiçoando

3

Page 4: Excel 2010 – Dashboard - infobitsolucoes.com em Excel - Volum… · Excel 2010 – Dashboard - Incrementando seus conhecimentos e Aperfeiçoando a sua prática Página 1/108 Excel

A sua prática

Página 3/108

Excel 2010 – Dashboard - Incrementando seus conhecimentos eAperfeiç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

Page 5: Excel 2010 – Dashboard - infobitsolucoes.com em Excel - Volum… · Excel 2010 – Dashboard - Incrementando seus conhecimentos e Aperfeiçoando a sua prática Página 1/108 Excel

. 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

Page 6: Excel 2010 – Dashboard - infobitsolucoes.com em Excel - Volum… · Excel 2010 – Dashboard - Incrementando seus conhecimentos e Aperfeiçoando a sua prática Página 1/108 Excel

. 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

Page 7: Excel 2010 – Dashboard - infobitsolucoes.com em Excel - Volum… · Excel 2010 – Dashboard - Incrementando seus conhecimentos e Aperfeiçoando a sua prática Página 1/108 Excel

Excel 2010 – Dashboard - Incrementando seus conhecimentos eAperfeiçoando a sua prática HISTÓRICO

Se nos remetermos para a formação dos contabilistas, podemos afirmar queas planilhas têm a sua utilização já há muitos anos. Mais recente na históriatemos a criação das planilhas eletrônicas.

Em 1978, Dan Bricklin, aluno da escola de administração da Universidadede Harvard (EUA), percebeu em uma aula de controladoria, que seuprofessor gastava muito tempo fazendo cálculos na lousa. Daí surgiu àidéia.

7

Page 8: Excel 2010 – Dashboard - infobitsolucoes.com em Excel - Volum… · Excel 2010 – Dashboard - Incrementando seus conhecimentos e Aperfeiçoando a sua prática Página 1/108 Excel

Diagrama rabiscado por Bricklin

Dan, juntamente com seu colega Robert Frankston, elaboraram umaplicativo, o qual simulava o quadro negro do professor. Nascia assim aprimeira planilha eletrônica. Posteriormente fundaram a empresaVISICORP e lançaram a planilha que nascia com o nome de VISICALC(VISIble CALCulator).

Página 5/108

Excel 2010 – Dashboard - Incrementando seus conhecimentos eAperfeiçoando a sua prática Em 1980, possuir um computador era muitocaro e suas funcionalidades eram muito restritas. Com a criação doVisicalc, uma nova finalidade começou a ser percebida, havendo umaumento nas vendas de computadores.

8

Page 9: Excel 2010 – Dashboard - infobitsolucoes.com em Excel - Volum… · Excel 2010 – Dashboard - Incrementando seus conhecimentos e Aperfeiçoando a sua prática Página 1/108 Excel

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 bempoderosa 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 suaplanilha EXCEL, tornando-se líder de mercado.

Página 6/108

Excel 2010 – Dashboard - Incrementando seus conhecimentos eAperfeiçoando a sua prática

A primeira versão do EXCEL foi lançada para Mac, em 1985 e a versãopara Windows teve o seu lançamento em novembro de 1987, com o nomede Microsoft Excel 2.0. Em pouco tempo, a Microsoft liderou o mercadodas planilhas eletrônicas e em 1990 lançou a versão 3.0.

O grande diferencial em relação à outros programas de sua categoria é aflexibilidade apresentada pela formatação gráfica dos dados. Desde 1993,o Excel tem incluído o Visual Basic for Applications (VBA), umalinguagem de programação baseada no Visual Basic, definidas pelo usuárioatravés de macros.

Até a versão décima primeira versão (2003), o formato de arquivo padrãodo Excel era o .xls. A partir da décima segunda versão, o formato passou aser .xlsx.

9

Page 10: Excel 2010 – Dashboard - infobitsolucoes.com em Excel - Volum… · Excel 2010 – Dashboard - Incrementando seus conhecimentos e Aperfeiçoando a sua prática Página 1/108 Excel

Versões do Microsoft Excel para Windows

10

Page 11: Excel 2010 – Dashboard - infobitsolucoes.com em Excel - Volum… · Excel 2010 – Dashboard - Incrementando seus conhecimentos e Aperfeiçoando a sua prática Página 1/108 Excel

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

Page 12: Excel 2010 – Dashboard - infobitsolucoes.com em Excel - Volum… · Excel 2010 – Dashboard - Incrementando seus conhecimentos e Aperfeiçoando a sua prática Página 1/108 Excel

Excel 12.0 (Office 2007)

2010

Excel 14.0 (Office 2010)

Referências

Diagrama retirado de http://www. bricklin.com

http://www.weblivre.net/artigo/excel/a-historia-do-microsoft-office-excelhttp://www.capitao.pro.br/apostilas/informatica/historia_planilhas.docPágina 7/108

Excel 2010 – Dashboard - Incrementando seus conhecimentos eAperfeiçoando a sua prática REFERÊNCIAS

Ao copiar uma fórmula, podemos verificar que as referências contidas nasfórmulas são alteradas automaticamente. Isso acontece em função dasReferê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

Page 13: Excel 2010 – Dashboard - infobitsolucoes.com em Excel - Volum… · Excel 2010 – Dashboard - Incrementando seus conhecimentos e Aperfeiçoando a sua prática Página 1/108 Excel

vendas no mês, percentualmente falando. A fórmula para obter o percentualseria dividir a quantidade individual vendida de cada produto, pelo totalvendido no mês, e depois aplicar um formato de porcentagem na célula doresultado.

O problema seria que, quando se cria referências relativas nas células, aocopiar a fórmula para as demais, o resultado seria incorreto, tendo em vistaque todas as referências das células seriam modificadas.

Neste caso precisamos atribuir à célula do: Total de vendas do mês, umapropriedade que a tornasse uma referência “Absoluta”, de forma que, aocopiar 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 eAperfeiçoando a sua prática TIPOS DE REFERÊNCIA

Tipo

13

Page 14: Excel 2010 – Dashboard - infobitsolucoes.com em Excel - Volum… · Excel 2010 – Dashboard - Incrementando seus conhecimentos e Aperfeiçoando a sua prática Página 1/108 Excel

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 eAperfeiçoando a sua prática

14

Page 15: Excel 2010 – Dashboard - infobitsolucoes.com em Excel - Volum… · Excel 2010 – Dashboard - Incrementando seus conhecimentos e Aperfeiçoando a sua prática Página 1/108 Excel

NOMENCLATURAS

Dados

São códigos que fazem parte da matéria prima que é a informação. Estainformação diz respeito à informação bruta, ainda não tratada.

O dado quando tratado isoladamente, pode não transmitir uma mensagem ounão representar nenhum conhecimento.

Informação

A informação diz respeito ao dado já tratado. Trata-se do resultado de umdeterminado processamento de dados. É com as informações que decisõessão tomadas.

Se tomarmos como base uma pesquisa eleitoral, é na pesquisa que os dadossão adquiridos e quando processados, vão produzir a informação de quemtem 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 agestão de negócios.

Relatórios

É a apresentação de dados transformados em informações, formatadas eorganizadas conforme requisitos do negócio.

15

Page 16: Excel 2010 – Dashboard - infobitsolucoes.com em Excel - Volum… · Excel 2010 – Dashboard - Incrementando seus conhecimentos e Aperfeiçoando a sua prática Página 1/108 Excel

Tabela de Indicadores

É um tipo de relatório que apresenta uma coleção de indicadores de chavesde desempenho, também conhecidos por KPIs.

KPI - Key Performance Indicator (Indicador Chave de Desempenho) Sãoformas simples de análise de processo através de gráficos simples comconotações universais, representando o estado de uma área, de um projetoou até mesmo de um processo dentro da empresa.

Ex. gráfico de velocímetro.

Página 10/108

Excel 2010 – Dashboard - Incrementando seus conhecimentos eAperfeiçoando a sua prática

16

Page 17: Excel 2010 – Dashboard - infobitsolucoes.com em Excel - Volum… · Excel 2010 – Dashboard - Incrementando seus conhecimentos e Aperfeiçoando a sua prática Página 1/108 Excel

Balanced Scorecard

Trata-se de uma técnica que visa a integração e balanceamento de todos osprincipais indicadores de desempenho existentes em uma empresa.

17

Page 18: Excel 2010 – Dashboard - infobitsolucoes.com em Excel - Volum… · Excel 2010 – Dashboard - Incrementando seus conhecimentos e Aperfeiçoando a sua prática Página 1/108 Excel

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órioanalítico e um gráfico por exemplo.

Página 11/108

Excel 2010 – Dashboard - Incrementando seus conhecimentos eAperfeiç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 eAperfeiçoando a sua prática FUNÇÃO PROCV

18

Page 19: Excel 2010 – Dashboard - infobitsolucoes.com em Excel - Volum… · Excel 2010 – Dashboard - Incrementando seus conhecimentos e Aperfeiçoando a sua prática Página 1/108 Excel

OBJETIVO

O objetivo desta função é o de procurar o primeiro valor na coluna mais aesquerda de um conjunto de células informadas, que satisfaça umadeterminada condição.

SINTAXE DA FUNÇÃO

PROCV(Valor_procurado

;

19

Page 20: Excel 2010 – Dashboard - infobitsolucoes.com em Excel - Volum… · Excel 2010 – Dashboard - Incrementando seus conhecimentos e Aperfeiçoando a sua prática Página 1/108 Excel

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 falsoretornará o valor exato da procura.

Página 13/108

20

Page 21: Excel 2010 – Dashboard - infobitsolucoes.com em Excel - Volum… · Excel 2010 – Dashboard - Incrementando seus conhecimentos e Aperfeiçoando a sua prática Página 1/108 Excel

Excel 2010 – Dashboard - Incrementando seus conhecimentos eAperfeiçoando a sua prática EXEMPLO I

Na célula A7 vamos pesquisar qual foi a nota exata da Prova 1 para PedroHenrique. Se o nome Pedro Henrique estivesse duas vezes neste exemplo, oprimeiro 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 aprimeira 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ãoaproximada.

O resultado obtido foi 4, equivale ao valor da célula C3, pois foi à primeiraocorrência encontrada de Pedro Henrique na tabela. Note que no parâmetrofoi usado o termo FALSE, ou seja, irá trazer o valor exatamente igual aoprocurado.

21

Page 22: Excel 2010 – Dashboard - infobitsolucoes.com em Excel - Volum… · Excel 2010 – Dashboard - Incrementando seus conhecimentos e Aperfeiçoando a sua prática Página 1/108 Excel

Página 14/108

Excel 2010 – Dashboard - Incrementando seus conhecimentos eAperfeiçoando a sua prática EXEMPLO II

No caso abaixo, vamos encontrar o percentual de comissão baseado novalor da venda em um determinado mês. Note que no parâmetro foi usado otermo VERDADEIRO, ou seja, irá trazer o maior valor mais próximo dovalor procurado.

EXEMPLO III

No exemplo abaixo temos uma tabela de aging de datas, baseado nos

22

Page 23: Excel 2010 – Dashboard - infobitsolucoes.com em Excel - Volum… · Excel 2010 – Dashboard - Incrementando seus conhecimentos e Aperfeiçoando a sua prática Página 1/108 Excel

vencimentos de cada linha.

EXEMPLO IV

Quando a coluna estiver em ordem alfabética e o valor comoVERDADEIRO, irá retornar a última ocorrência da pesquisa.

Página 15/108

Excel 2010 – Dashboard - Incrementando seus conhecimentos eAperfeiçoando a sua prática Monte uma planilha igual à figura abaixo epreencha os dados faltantes.

Página 16/108

Excel 2010 – Dashboard - Incrementando seus conhecimentos eAperfeiçoando a sua prática FUNÇÃO PROCH

23

Page 24: Excel 2010 – Dashboard - infobitsolucoes.com em Excel - Volum… · Excel 2010 – Dashboard - Incrementando seus conhecimentos e Aperfeiçoando a sua prática Página 1/108 Excel

OBJETIVO

O objetivo desta função é o de procurar um valor em uma linha especificade um conjunto de células informadas, que satisfaça uma determinadacondição.

SINTAXE DA FUNÇÃO

PROCH(Valor_procurado

;

24

Page 25: Excel 2010 – Dashboard - infobitsolucoes.com em Excel - Volum… · Excel 2010 – Dashboard - Incrementando seus conhecimentos e Aperfeiçoando a sua prática Página 1/108 Excel

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 falsoretornará o valor exato da procura.

Página 17/108

25

Page 26: Excel 2010 – Dashboard - infobitsolucoes.com em Excel - Volum… · Excel 2010 – Dashboard - Incrementando seus conhecimentos e Aperfeiçoando a sua prática Página 1/108 Excel

Excel 2010 – Dashboard - Incrementando seus conhecimentos eAperfeiç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ãoaproximada.

EXEMPLO II

26

Page 27: Excel 2010 – Dashboard - infobitsolucoes.com em Excel - Volum… · Excel 2010 – Dashboard - Incrementando seus conhecimentos e Aperfeiçoando a sua prática Página 1/108 Excel

Página 18/108

Excel 2010 – Dashboard - Incrementando seus conhecimentos eAperfeiçoando a sua prática FUNÇÃO ÍNDICE

OBJETIVO

O objetivo desta função é retornar um valor ou uma referência para umvalor 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_linhae 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

Page 28: Excel 2010 – Dashboard - infobitsolucoes.com em Excel - Volum… · Excel 2010 – Dashboard - Incrementando seus conhecimentos e Aperfeiçoando a sua prática Página 1/108 Excel

Excel 2010 – Dashboard - Incrementando seus conhecimentos eAperfeiçoando a sua prática EXEMPLO

Página 20/108

Excel 2010 – Dashboard - Incrementando seus conhecimentos eAperfeiçoando a sua prática FUNÇÃO CORRESP

28

Page 29: Excel 2010 – Dashboard - infobitsolucoes.com em Excel - Volum… · Excel 2010 – Dashboard - Incrementando seus conhecimentos e Aperfeiçoando a sua prática Página 1/108 Excel

OBJETIVO

O objetivo desta função é retornar a posição relativa de um item em umamatriz 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 aprocura.

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

Page 30: Excel 2010 – Dashboard - infobitsolucoes.com em Excel - Volum… · Excel 2010 – Dashboard - Incrementando seus conhecimentos e Aperfeiçoando a sua prática Página 1/108 Excel

Aperfeiçoando a sua prática FUNÇÃO ESCOLHER

OBJETIVO

O objetivo desta função é escolher um valor a partir de uma lista devalores, 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 a254).

EXEMPLO

Página 22/108

Excel 2010 – Dashboard - Incrementando seus conhecimentos e

30

Page 31: Excel 2010 – Dashboard - infobitsolucoes.com em Excel - Volum… · Excel 2010 – Dashboard - Incrementando seus conhecimentos e Aperfeiçoando a sua prática Página 1/108 Excel

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 apartir da célula C2, irá buscar duas células abaixo e deslocar uma célulapara a esquerda.

No exemplo Maria a célula A10 que apresenta o texto Maria – significaque a partir da célula C2, irá buscar duas células abaixo e deslocar duas

31

Page 32: Excel 2010 – Dashboard - infobitsolucoes.com em Excel - Volum… · Excel 2010 – Dashboard - Incrementando seus conhecimentos e Aperfeiçoando a sua prática Página 1/108 Excel

células para a esquerda.

Página 23/108

Excel 2010 – Dashboard - Incrementando seus conhecimentos eAperfeiçoando a sua prática FUNÇÃO REPT

OBJETIVO

Esta função tem como objetivo repetir um texto em um determinado númerode vezes.

SINTAXE DA FUNÇÃO

REPT(texto, núm_vezes)

texto

Identifica o texto.

32

Page 33: Excel 2010 – Dashboard - infobitsolucoes.com em Excel - Volum… · Excel 2010 – Dashboard - Incrementando seus conhecimentos e Aperfeiçoando a sua prática Página 1/108 Excel

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 eAperfeiçoando a sua prática

33

Page 34: Excel 2010 – Dashboard - infobitsolucoes.com em Excel - Volum… · Excel 2010 – Dashboard - Incrementando seus conhecimentos e Aperfeiçoando a sua prática Página 1/108 Excel

EXEMPLO III

EXEMPLO IV

Página 25/108

Excel 2010 – Dashboard - Incrementando seus conhecimentos eAperfeiçoando a sua prática FUNÇÃO INDIRETO

OBJETIVO

O objetivo desta função é transformar o valor de um texto em umareferê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

Page 35: Excel 2010 – Dashboard - infobitsolucoes.com em Excel - Volum… · Excel 2010 – Dashboard - Incrementando seus conhecimentos e Aperfeiçoando a sua prática Página 1/108 Excel

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 eAperfeiçoando a sua prática Selecione o range de C2 a C4 e dê o nome de

35

Page 36: Excel 2010 – Dashboard - infobitsolucoes.com em Excel - Volum… · Excel 2010 – Dashboard - Incrementando seus conhecimentos e Aperfeiçoando a sua prática Página 1/108 Excel

gastos.

Na célula F9 a função indireto, substitui o conteúdo da célula E9, trazendopara dentro de si, o range especificado anteriormente.

EXEMPLO II

Página 27/108

36

Page 37: Excel 2010 – Dashboard - infobitsolucoes.com em Excel - Volum… · Excel 2010 – Dashboard - Incrementando seus conhecimentos e Aperfeiçoando a sua prática Página 1/108 Excel

Excel 2010 – Dashboard - Incrementando seus conhecimentos eAperfeiçoando a sua prática

Botão Câmera

A barra de ferramentas pode ser personalizada conforme a utilização doscomandos que se deseja. Nela podemos colocar os comandos maisutilizados 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 quadromais à esquerda para à direita. Após a escolha dos atalhos desejados,clique em Ok.

Monte o seguinte exemplo na Plan1: Cada valor deverá ser multiplicadopela célula F1.

Na Plan2:

No menu, layout de página, linhas de grade, desmarque a opção exibirNo menu, inserir, formas, retângulo, e coloque a figura entre A1 e E6.

37

Page 38: Excel 2010 – Dashboard - infobitsolucoes.com em Excel - Volum… · Excel 2010 – Dashboard - Incrementando seus conhecimentos e Aperfeiçoando a sua prática Página 1/108 Excel

Página 28/108

Excel 2010 – Dashboard - Incrementando seus conhecimentos eAperfeiçoando a sua prática Na Plan1 selecione as células de A1 a D4 eclique na câmera e clique na A1 da Plan2 sobre a figura.

Página 29/108

38

Page 39: Excel 2010 – Dashboard - infobitsolucoes.com em Excel - Volum… · Excel 2010 – Dashboard - Incrementando seus conhecimentos e Aperfeiçoando a sua prática Página 1/108 Excel

Excel 2010 – Dashboard - Incrementando seus conhecimentos eAperfeiç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 ocontrole.

Clique em qualquer célula para desmarcar a barra e clique nas setas dabarra para direita ou para a esquerda.

Página 30/108

39

Page 40: Excel 2010 – Dashboard - infobitsolucoes.com em Excel - Volum… · Excel 2010 – Dashboard - Incrementando seus conhecimentos e Aperfeiçoando a sua prática Página 1/108 Excel

Excel 2010 – Dashboard - Incrementando seus conhecimentos eAperfeiçoando a sua prática

Copie e cole uma nova figura (azul).

Página 31/108

Excel 2010 – Dashboard - Incrementando seus conhecimentos e

40

Page 41: Excel 2010 – Dashboard - infobitsolucoes.com em Excel - Volum… · Excel 2010 – Dashboard - Incrementando seus conhecimentos e Aperfeiçoando a sua prática Página 1/108 Excel

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 F2da Plan1. Clique com o botão da direita sobre o botão e altere apropriedade Controle. Altere o texto de cada botão.

Página 32/108

Excel 2010 – Dashboard - Incrementando seus conhecimentos eAperfeiç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

Page 42: Excel 2010 – Dashboard - infobitsolucoes.com em Excel - Volum… · Excel 2010 – Dashboard - Incrementando seus conhecimentos e Aperfeiçoando a sua prática Página 1/108 Excel

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

Page 43: Excel 2010 – Dashboard - infobitsolucoes.com em Excel - Volum… · Excel 2010 – Dashboard - Incrementando seus conhecimentos e Aperfeiçoando a sua prática Página 1/108 Excel

Excel 2010 – Dashboard - Incrementando seus conhecimentos eAperfeiç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 ocontrole.

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

Page 44: Excel 2010 – Dashboard - infobitsolucoes.com em Excel - Volum… · Excel 2010 – Dashboard - Incrementando seus conhecimentos e Aperfeiçoando a sua prática Página 1/108 Excel

Clique na câmera e clique novamente na Plan2 no novo desenho.

Página 34/108

Excel 2010 – Dashboard - Incrementando seus conhecimentos eAperfeiçoando a sua prática Protegendo a planilha contra alterações semusar senha – recurso VBA Com a planilha aberta pressione Alt + F11

Dê um duplo clique em EstaPasta_de_trabalho.

44

Page 45: Excel 2010 – Dashboard - infobitsolucoes.com em Excel - Volum… · Excel 2010 – Dashboard - Incrementando seus conhecimentos e Aperfeiçoando a sua prática Página 1/108 Excel

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 emEstaPasta_de_trabalho e apague a linha do meio (Active ....).

Página 35/108

Excel 2010 – Dashboard - Incrementando seus conhecimentos eAperfeiçoando a sua prática CARREGAR ARQUIVO TEXTO E RETIRARITENS 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

Page 46: Excel 2010 – Dashboard - infobitsolucoes.com em Excel - Volum… · Excel 2010 – Dashboard - Incrementando seus conhecimentos e Aperfeiçoando a sua prática Página 1/108 Excel

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 seuDesktop e abri-lo no Excel.

Página 36/108

46

Page 47: Excel 2010 – Dashboard - infobitsolucoes.com em Excel - Volum… · Excel 2010 – Dashboard - Incrementando seus conhecimentos e Aperfeiçoando a sua prática Página 1/108 Excel

Excel 2010 – Dashboard - Incrementando seus conhecimentos eAperfeiç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

Page 48: Excel 2010 – Dashboard - infobitsolucoes.com em Excel - Volum… · Excel 2010 – Dashboard - Incrementando seus conhecimentos e Aperfeiçoando a sua prática Página 1/108 Excel

Excel 2010 – Dashboard - Incrementando seus conhecimentos eAperfeiçoando a sua prática Escolha a opção delimitado, Ponto e vírgula econcluir.

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

Page 49: Excel 2010 – Dashboard - infobitsolucoes.com em Excel - Volum… · Excel 2010 – Dashboard - Incrementando seus conhecimentos e Aperfeiçoando a sua prática Página 1/108 Excel

Excel 2010 – Dashboard - Incrementando seus conhecimentos eAperfeiç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

Page 50: Excel 2010 – Dashboard - infobitsolucoes.com em Excel - Volum… · Excel 2010 – Dashboard - Incrementando seus conhecimentos e Aperfeiçoando a sua prática Página 1/108 Excel

Página 39/108

Excel 2010 – Dashboard - Incrementando seus conhecimentos eAperfeiçoando a sua prática TRABALHANDO COM A CAIXA DENOMES

OBJETIVO

Esta dica tem como finalidade mostrar como você poderá somar um grupode valores.

EXEMPLO I

Crie uma planilha conforme o exemplo abaixo.

50

Page 51: Excel 2010 – Dashboard - infobitsolucoes.com em Excel - Volum… · Excel 2010 – Dashboard - Incrementando seus conhecimentos e Aperfeiçoando a sua prática Página 1/108 Excel

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 desemestre2.

Na célula O2 faça a soma de semestre1. Para isso, basta digitar na barra defórmulas

a seguinte expressão: =SOMA(semestre1) e faça o mesmo para a célula O4com semestre2.

Página 40/108

51

Page 52: Excel 2010 – Dashboard - infobitsolucoes.com em Excel - Volum… · Excel 2010 – Dashboard - Incrementando seus conhecimentos e Aperfeiçoando a sua prática Página 1/108 Excel

Excel 2010 – Dashboard - Incrementando seus conhecimentos eAperfeiçoando a sua prática

52

Page 53: Excel 2010 – Dashboard - infobitsolucoes.com em Excel - Volum… · Excel 2010 – Dashboard - Incrementando seus conhecimentos e Aperfeiçoando a sua prática Página 1/108 Excel

SOMANDO VALORES PARA DIFERENTESPLANILHAS

OBJETIVO

Neste exemplo, vamos supor que você tenha diversas planilhas de trabalho,dentro de um único arquivo Excel e que você precise somar todo omontante dos valores que encontram-se em uma mesma célula mas que estãonas 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 umvalor para cada planilha:

Plan2 = 2.000,00

Plan3 = 3.000,00

Plan4 = 4.000,00

53

Page 54: Excel 2010 – Dashboard - infobitsolucoes.com em Excel - Volum… · Excel 2010 – Dashboard - Incrementando seus conhecimentos e Aperfeiçoando a sua prática Página 1/108 Excel

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 nacélula indicada.

EXEMPLO II

Página 41/108

Excel 2010 – Dashboard - Incrementando seus conhecimentos eAperfeiçoando a sua prática ATINGIR META – PONTO DE EQUILÍBRIO

OBJETIVO

54

Page 55: Excel 2010 – Dashboard - infobitsolucoes.com em Excel - Volum… · Excel 2010 – Dashboard - Incrementando seus conhecimentos e Aperfeiçoando a sua prática Página 1/108 Excel

Atingir meta tem como finalidade obter um valor de entrada de umadeterminada 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 paraque 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 paraque 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

Page 56: Excel 2010 – Dashboard - infobitsolucoes.com em Excel - Volum… · Excel 2010 – Dashboard - Incrementando seus conhecimentos e Aperfeiçoando a sua prática Página 1/108 Excel

Excel 2010 – Dashboard - Incrementando seus conhecimentos eAperfeiçoando a sua prática Definir célula: é o valor final do resultadocalculado.

Para valor: é o valor final que deseja ser atingido.

Alternando célula: é a célula do valor inicial.

Página 43/108

56

Page 57: Excel 2010 – Dashboard - infobitsolucoes.com em Excel - Volum… · Excel 2010 – Dashboard - Incrementando seus conhecimentos e Aperfeiçoando a sua prática Página 1/108 Excel

Excel 2010 – Dashboard - Incrementando seus conhecimentos eAperfeiç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) esabe 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 paraesta 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

Page 58: Excel 2010 – Dashboard - infobitsolucoes.com em Excel - Volum… · Excel 2010 – Dashboard - Incrementando seus conhecimentos e Aperfeiçoando a sua prática Página 1/108 Excel

Excel 2010 – Dashboard - Incrementando seus conhecimentos eAperfeiç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

Page 59: Excel 2010 – Dashboard - infobitsolucoes.com em Excel - Volum… · Excel 2010 – Dashboard - Incrementando seus conhecimentos e Aperfeiçoando a sua prática Página 1/108 Excel

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 eAperfeiçoando a sua prática

59

Page 60: Excel 2010 – Dashboard - infobitsolucoes.com em Excel - Volum… · Excel 2010 – Dashboard - Incrementando seus conhecimentos e Aperfeiçoando a sua prática Página 1/108 Excel

60

Page 61: Excel 2010 – Dashboard - infobitsolucoes.com em Excel - Volum… · Excel 2010 – Dashboard - Incrementando seus conhecimentos e Aperfeiçoando a sua prática Página 1/108 Excel

MATRIZ

OBJETIVO

Monte uma planilha matriz conforme figura abaixo.

Página 46/108

Excel 2010 – Dashboard - Incrementando seus conhecimentos eAperfeiç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

Page 62: Excel 2010 – Dashboard - infobitsolucoes.com em Excel - Volum… · Excel 2010 – Dashboard - Incrementando seus conhecimentos e Aperfeiçoando a sua prática Página 1/108 Excel

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çãoCondicional, Conjunto de ícones e escolha a opção de 3 setas.

Página 47/108

62

Page 63: Excel 2010 – Dashboard - infobitsolucoes.com em Excel - Volum… · Excel 2010 – Dashboard - Incrementando seus conhecimentos e Aperfeiçoando a sua prática Página 1/108 Excel

Excel 2010 – Dashboard - Incrementando seus conhecimentos eAperfeiçoando a sua prática

Entre no menu Início, Formatação Condicional, Gerenciar Regras, EditarRegras.

Formate a regra para que fique conforme figura abaixo: No lugar depercentual, mude para número e divida os valores por 100.

Página 48/108

63

Page 64: Excel 2010 – Dashboard - infobitsolucoes.com em Excel - Volum… · Excel 2010 – Dashboard - Incrementando seus conhecimentos e Aperfeiçoando a sua prática Página 1/108 Excel

Excel 2010 – Dashboard - Incrementando seus conhecimentos eAperfeiç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, EditarRegras 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

Page 65: Excel 2010 – Dashboard - infobitsolucoes.com em Excel - Volum… · Excel 2010 – Dashboard - Incrementando seus conhecimentos e Aperfeiçoando a sua prática Página 1/108 Excel

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

Page 66: Excel 2010 – Dashboard - infobitsolucoes.com em Excel - Volum… · Excel 2010 – Dashboard - Incrementando seus conhecimentos e Aperfeiçoando a sua prática Página 1/108 Excel

Excel 2010 – Dashboard - Incrementando seus conhecimentos eAperfeiçoando a sua prática

66

Page 67: Excel 2010 – Dashboard - infobitsolucoes.com em Excel - Volum… · Excel 2010 – Dashboard - Incrementando seus conhecimentos e Aperfeiçoando a sua prática Página 1/108 Excel

MONTANDO SCROLL DE TELA

Monte uma tabela conforme figura abaixo.

Vamos inserir uma barra de rolagem. Menu, desenvolvedor, inserir, barrade rolagem.

Ela deverá ficar entre B4 a B10 e seu controle posicional deverá ficar naPlan Form_cond célula Q1.

Página 50/108

Excel 2010 – Dashboard - Incrementando seus conhecimentos eAperfeiç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

Page 68: Excel 2010 – Dashboard - infobitsolucoes.com em Excel - Volum… · Excel 2010 – Dashboard - Incrementando seus conhecimentos e Aperfeiçoando a sua prática Página 1/108 Excel

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 eclique 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 valoresnegativos entre parênteses.

Página 51/108

68

Page 69: Excel 2010 – Dashboard - infobitsolucoes.com em Excel - Volum… · Excel 2010 – Dashboard - Incrementando seus conhecimentos e Aperfeiçoando a sua prática Página 1/108 Excel

Excel 2010 – Dashboard - Incrementando seus conhecimentos eAperfeiçoando a sua prática TABELAS DINÂMICAS

Um relatório de tabela dinâmica consiste em uma forma interativa ondepodemos 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: Consultargrandes volumes de dados de várias formas diferentes.

Subtotalizar e agregar dados numéricos, resumindo dados por categorias esubcategorias.

Vamos nos utilizar do seguinte exemplo

69

Page 70: Excel 2010 – Dashboard - infobitsolucoes.com em Excel - Volum… · Excel 2010 – Dashboard - Incrementando seus conhecimentos e Aperfeiçoando a sua prática Página 1/108 Excel

Vamos selecionar a área desejada. Neste caso será de A1 a D8.

Após esta seleção, clique na aba Inserir, Tabela Dinâmica, TabelaDinâmica.

Página 52/108

Excel 2010 – Dashboard - Incrementando seus conhecimentos eAperfeiçoando a sua prática Ao clicar em Tabela Dinâmica, já temos

70

Page 71: Excel 2010 – Dashboard - infobitsolucoes.com em Excel - Volum… · Excel 2010 – Dashboard - Incrementando seus conhecimentos e Aperfeiçoando a sua prática Página 1/108 Excel

preenchido o intervalo selecionado na caixa de diálogo.

Clique em Local: e clique em qualquer célula, local onde será montada atabela.

Ex. F1

Uma lista de campos será apresentada.

Estes campos são os cabeçalhos de cada

coluna selecionada.

Página 53/108

71

Page 72: Excel 2010 – Dashboard - infobitsolucoes.com em Excel - Volum… · Excel 2010 – Dashboard - Incrementando seus conhecimentos e Aperfeiçoando a sua prática Página 1/108 Excel

Excel 2010 – Dashboard - Incrementando seus conhecimentos eAperfeiç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

Page 73: Excel 2010 – Dashboard - infobitsolucoes.com em Excel - Volum… · Excel 2010 – Dashboard - Incrementando seus conhecimentos e Aperfeiçoando a sua prática Página 1/108 Excel

Clique com o botão da esquerda do mouse, para fazer alteração doscampos.

Página 54/108

Excel 2010 – Dashboard - Incrementando seus conhecimentos eAperfeiç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

Page 74: Excel 2010 – Dashboard - infobitsolucoes.com em Excel - Volum… · Excel 2010 – Dashboard - Incrementando seus conhecimentos e Aperfeiçoando a sua prática Página 1/108 Excel

Excel 2010 – Dashboard - Incrementando seus conhecimentos eAperfeiçoando a sua prática Transformando a tabela em valores percentuaissobre o total.

Clique na célula F1 e com o botão direito do mouse escolha a opçãoResumir Dados por, Mais opções, Mostrar valores como, % do total.

74

Page 75: Excel 2010 – Dashboard - infobitsolucoes.com em Excel - Volum… · Excel 2010 – Dashboard - Incrementando seus conhecimentos e Aperfeiçoando a sua prática Página 1/108 Excel

Vamos voltar à tabela à sua forma normal.

Clique na célula F6 e com o botão direito do mouse escolha a opçãoResumir Dados por, Mais opções, Mostrar valores como, Normal.

Página 56/108

75

Page 76: Excel 2010 – Dashboard - infobitsolucoes.com em Excel - Volum… · Excel 2010 – Dashboard - Incrementando seus conhecimentos e Aperfeiçoando a sua prática Página 1/108 Excel

Excel 2010 – Dashboard - Incrementando seus conhecimentos eAperfeiçoando a sua prática

76

Page 77: Excel 2010 – Dashboard - infobitsolucoes.com em Excel - Volum… · Excel 2010 – Dashboard - Incrementando seus conhecimentos e Aperfeiçoando a sua prática Página 1/108 Excel

Adicionando um campo calculado.

Clique na tabela dinâmica para acessar o menu Ferramentas de TabelaDinâmica (parte superior).

Sobre o valor vamos acrescentar o ICMS no valor de 25% e acrescentartambém um valor líquido.

Clique na tabela dinâmica, no Menu, Ferramenta de Tabela Dinâmica (partesuperior 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 eAperfeiçoando a sua prática Veja o resultado.

77

Page 78: Excel 2010 – Dashboard - infobitsolucoes.com em Excel - Volum… · Excel 2010 – Dashboard - Incrementando seus conhecimentos e Aperfeiçoando a sua prática Página 1/108 Excel

Página 58/108

Excel 2010 – Dashboard - Incrementando seus conhecimentos eAperfeiçoando a sua prática

78

Page 79: Excel 2010 – Dashboard - infobitsolucoes.com em Excel - Volum… · Excel 2010 – Dashboard - Incrementando seus conhecimentos e Aperfeiçoando a sua prática Página 1/108 Excel

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

Page 80: Excel 2010 – Dashboard - infobitsolucoes.com em Excel - Volum… · Excel 2010 – Dashboard - Incrementando seus conhecimentos e Aperfeiçoando a sua prática Página 1/108 Excel

Excel 2010 – Dashboard - Incrementando seus conhecimentos eAperfeiç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

Page 81: Excel 2010 – Dashboard - infobitsolucoes.com em Excel - Volum… · Excel 2010 – Dashboard - Incrementando seus conhecimentos e Aperfeiçoando a sua prática Página 1/108 Excel

Excel 2010 – Dashboard - Incrementando seus conhecimentos eAperfeiçoando a sua prática TABELA DINÂMICA UTILIZANDO VÁRIASPLANILHAS

OBJETIVO

81

Page 82: Excel 2010 – Dashboard - infobitsolucoes.com em Excel - Volum… · Excel 2010 – Dashboard - Incrementando seus conhecimentos e Aperfeiçoando a sua prática Página 1/108 Excel

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

Page 83: Excel 2010 – Dashboard - infobitsolucoes.com em Excel - Volum… · Excel 2010 – Dashboard - Incrementando seus conhecimentos e Aperfeiçoando a sua prática Página 1/108 Excel

EXEMPLO

Lojas.xlsx

Abra uma nova planilha.

Clique no Menu, Dados, Obter Dados Externos, De Outras Fontes, DoMicrosoft Query.

Página 61/108

83

Page 84: Excel 2010 – Dashboard - infobitsolucoes.com em Excel - Volum… · Excel 2010 – Dashboard - Incrementando seus conhecimentos e Aperfeiçoando a sua prática Página 1/108 Excel

Excel 2010 – Dashboard - Incrementando seus conhecimentos eAperfeiç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 itensconforme as próximas figuras.

84

Page 85: Excel 2010 – Dashboard - infobitsolucoes.com em Excel - Volum… · Excel 2010 – Dashboard - Incrementando seus conhecimentos e Aperfeiçoando a sua prática Página 1/108 Excel

Página 62/108

85

Page 86: Excel 2010 – Dashboard - infobitsolucoes.com em Excel - Volum… · Excel 2010 – Dashboard - Incrementando seus conhecimentos e Aperfeiçoando a sua prática Página 1/108 Excel

Excel 2010 – Dashboard - Incrementando seus conhecimentos eAperfeiç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 eclique em concluir.

Página 63/108

86

Page 87: Excel 2010 – Dashboard - infobitsolucoes.com em Excel - Volum… · Excel 2010 – Dashboard - Incrementando seus conhecimentos e Aperfeiçoando a sua prática Página 1/108 Excel

Excel 2010 – Dashboard - Incrementando seus conhecimentos eAperfeiçoando a sua prática Você irá obter a seguinte tela.

Clique na caixa SQL.

87

Page 88: Excel 2010 – Dashboard - infobitsolucoes.com em Excel - Volum… · Excel 2010 – Dashboard - Incrementando seus conhecimentos e Aperfeiçoando a sua prática Página 1/108 Excel

Selecione todo o texto e dê um Control C para copiar para a área detransferência.

Ao final escreva UNION ALL

Dê um Control V (colar).

Página 64/108

88

Page 89: Excel 2010 – Dashboard - infobitsolucoes.com em Excel - Volum… · Excel 2010 – Dashboard - Incrementando seus conhecimentos e Aperfeiçoando a sua prática Página 1/108 Excel

Excel 2010 – Dashboard - Incrementando seus conhecimentos eAperfeiç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 paracada loja.

Clique em Ok e na próxima tela clique em Ok novamente.

Página 65/108

89

Page 90: Excel 2010 – Dashboard - infobitsolucoes.com em Excel - Volum… · Excel 2010 – Dashboard - Incrementando seus conhecimentos e Aperfeiçoando a sua prática Página 1/108 Excel

Excel 2010 – Dashboard - Incrementando seus conhecimentos eAperfeiçoando a sua prática Vamos obter a seguinte tela

Feche a janela

90

Page 91: Excel 2010 – Dashboard - infobitsolucoes.com em Excel - Volum… · Excel 2010 – Dashboard - Incrementando seus conhecimentos e Aperfeiçoando a sua prática Página 1/108 Excel

Na próxima tela escolha relatório de Tabela Dinâmica

Página 66/108

Excel 2010 – Dashboard - Incrementando seus conhecimentos eAperfeiçoando a sua prática Monte a tabela dinâmica conforme a suanecessidade.

Página 67/108

91

Page 92: Excel 2010 – Dashboard - infobitsolucoes.com em Excel - Volum… · Excel 2010 – Dashboard - Incrementando seus conhecimentos e Aperfeiçoando a sua prática Página 1/108 Excel

Excel 2010 – Dashboard - Incrementando seus conhecimentos eAperfeiçoando a sua prática PREENCHER CÉLULAS EM BRANCO DOCAMPO CHAVE PARA TABELA DINÂMICA

OBJETIVO

Como criar tabela dinamica quando as celulas chaves estiverem em branco.

EXEMPLO

Como criar uma tabela dinâmica quando as células chaves estiverem embranco.

92

Page 93: Excel 2010 – Dashboard - infobitsolucoes.com em Excel - Volum… · Excel 2010 – Dashboard - Incrementando seus conhecimentos e Aperfeiçoando a sua prática Página 1/108 Excel

Digamos que você recebeu um determinado relatório e necessita por algummotivo fazer uma tabela dinâmica.

Se simplesmente as células B1 a D14 fossem selecionadas e aplicadassobre elas uma tabela dinâmica teríamos o seguinte resultado.

Note que a coluna J apresentou a somatória dos valores por filial cujacoluna B estavam em branco.

Como resolver este problema ? Simples, usaremos a função SE.

Página 68/108

93

Page 94: Excel 2010 – Dashboard - infobitsolucoes.com em Excel - Volum… · Excel 2010 – Dashboard - Incrementando seus conhecimentos e Aperfeiçoando a sua prática Página 1/108 Excel

Excel 2010 – Dashboard - Incrementando seus conhecimentos eAperfeiçoando a sua prática Na célula A1 repita o título da célula B1 queserá nosso campo chave.

Na célula A2 digite o comando SE descrito abaixo e arraste-o até o final daplanilha onde possuir valores.

=SE(B2="";A1;B2) (aspas duplas duas vezes “”)

Agora sim, você poderá fazer a sua tabela dinâmica sem prejuízo dosvalores a serem apresentados.

Página 69/108

94

Page 95: Excel 2010 – Dashboard - infobitsolucoes.com em Excel - Volum… · Excel 2010 – Dashboard - Incrementando seus conhecimentos e Aperfeiçoando a sua prática Página 1/108 Excel

Excel 2010 – Dashboard - Incrementando seus conhecimentos eAperfeiç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

Page 96: Excel 2010 – Dashboard - infobitsolucoes.com em Excel - Volum… · Excel 2010 – Dashboard - Incrementando seus conhecimentos e Aperfeiçoando a sua prática Página 1/108 Excel

►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áficoDinâmico.

Página 70/108

Excel 2010 – Dashboard - Incrementando seus conhecimentos eAperfeiç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 deFev";$I$3;"Regional";"RJ") Somou os valores de Fevereiro para aRegional RJ.

96

Page 97: Excel 2010 – Dashboard - infobitsolucoes.com em Excel - Volum… · Excel 2010 – Dashboard - Incrementando seus conhecimentos e Aperfeiçoando a sua prática Página 1/108 Excel

Página 71/108

Excel 2010 – Dashboard - Incrementando seus conhecimentos eAperfeiçoando a sua prática Inserindo um controle de botão

97

Page 98: Excel 2010 – Dashboard - infobitsolucoes.com em Excel - Volum… · Excel 2010 – Dashboard - Incrementando seus conhecimentos e Aperfeiçoando a sua prática Página 1/108 Excel

Clique em qualquer parte da tabela dinâmica para acessar Ferramentas deTabela 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 eAperfeiçoando a sua prática Pressione Alt + F11

Página 73/108

98

Page 99: Excel 2010 – Dashboard - infobitsolucoes.com em Excel - Volum… · Excel 2010 – Dashboard - Incrementando seus conhecimentos e Aperfeiçoando a sua prática Página 1/108 Excel

Excel 2010 – Dashboard - Incrementando seus conhecimentos eAperfeiç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

Page 100: Excel 2010 – Dashboard - infobitsolucoes.com em Excel - Volum… · Excel 2010 – Dashboard - Incrementando seus conhecimentos e Aperfeiçoando a sua prática Página 1/108 Excel

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

Page 101: Excel 2010 – Dashboard - infobitsolucoes.com em Excel - Volum… · Excel 2010 – Dashboard - Incrementando seus conhecimentos e Aperfeiçoando a sua prática Página 1/108 Excel

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 bordamarque a opção bordas arredondadas.

Página 74/108

Excel 2010 – Dashboard - Incrementando seus conhecimentos eAperfeiçoando a sua prática

Página 75/108

101

Page 102: Excel 2010 – Dashboard - infobitsolucoes.com em Excel - Volum… · Excel 2010 – Dashboard - Incrementando seus conhecimentos e Aperfeiçoando a sua prática Página 1/108 Excel

Excel 2010 – Dashboard - Incrementando seus conhecimentos eAperfeiç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 dográfico.

Menu, desenvolvedor, Inserir, botão de opção.

No primeiro escreva Janeiro e no segundo Fevereiro.

102

Page 103: Excel 2010 – Dashboard - infobitsolucoes.com em Excel - Volum… · Excel 2010 – Dashboard - Incrementando seus conhecimentos e Aperfeiçoando a sua prática Página 1/108 Excel

Faça a opção da escolha ser direcionada para a planilha Form_cond nacé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 emForm_cond_dash.

103

Page 104: Excel 2010 – Dashboard - infobitsolucoes.com em Excel - Volum… · Excel 2010 – Dashboard - Incrementando seus conhecimentos e Aperfeiçoando a sua prática Página 1/108 Excel

Clique na primeira caixa e direcione o valor para T11, a segunda para T12e a terceira caixa para T13.

Página 76/108

Excel 2010 – Dashboard - Incrementando seus conhecimentos eAperfeiçoando a sua prática

104

Page 105: Excel 2010 – Dashboard - infobitsolucoes.com em Excel - Volum… · Excel 2010 – Dashboard - Incrementando seus conhecimentos e Aperfeiçoando a sua prática Página 1/108 Excel

Inserindo Check Box

Vamos inserir dois controles de Caixa de Seleção (Check Box) com duasCaixas 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 empropriedades marque para que seu resultado seja colocado na planilhaForm_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á apalavra 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 empropriedades marque a lista sendo as células de Q7, Q8 e Q9 e coloque oresultado em U2 da planilha Form_cond.

Faça o mesmo para a segunda caixa.

105

Page 106: Excel 2010 – Dashboard - infobitsolucoes.com em Excel - Volum… · Excel 2010 – Dashboard - Incrementando seus conhecimentos e Aperfeiçoando a sua prática Página 1/108 Excel

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

Page 107: Excel 2010 – Dashboard - infobitsolucoes.com em Excel - Volum… · Excel 2010 – Dashboard - Incrementando seus conhecimentos e Aperfeiçoando a sua prática Página 1/108 Excel

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 opcao2que 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

Page 108: Excel 2010 – Dashboard - infobitsolucoes.com em Excel - Volum… · Excel 2010 – Dashboard - Incrementando seus conhecimentos e Aperfeiçoando a sua prática Página 1/108 Excel

Excel 2010 – Dashboard - Incrementando seus conhecimentos eAperfeiçoando a sua prática Para a segunda série marque

Para o eixo horizontal marque

108

Page 109: Excel 2010 – Dashboard - infobitsolucoes.com em Excel - Volum… · Excel 2010 – Dashboard - Incrementando seus conhecimentos e Aperfeiçoando a sua prática Página 1/108 Excel

Página 79/108

Excel 2010 – Dashboard - Incrementando seus conhecimentos eAperfeiç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 (segundaopção).

Clique com o botão direito do mouse sobre o gráfico e escolha a opção

109

Page 110: Excel 2010 – Dashboard - infobitsolucoes.com em Excel - Volum… · Excel 2010 – Dashboard - Incrementando seus conhecimentos e Aperfeiçoando a sua prática Página 1/108 Excel

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

Page 111: Excel 2010 – Dashboard - infobitsolucoes.com em Excel - Volum… · Excel 2010 – Dashboard - Incrementando seus conhecimentos e Aperfeiçoando a sua prática Página 1/108 Excel

Aperfeiçoando a sua prática Clique na primeira série (azul) com o botãodireito 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

Page 112: Excel 2010 – Dashboard - infobitsolucoes.com em Excel - Volum… · Excel 2010 – Dashboard - Incrementando seus conhecimentos e Aperfeiçoando a sua prática Página 1/108 Excel

Excel 2010 – Dashboard - Incrementando seus conhecimentos eAperfeiçoando a sua prática Clique na segunda série com o botão direito domouse 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

Page 113: Excel 2010 – Dashboard - infobitsolucoes.com em Excel - Volum… · Excel 2010 – Dashboard - Incrementando seus conhecimentos e Aperfeiçoando a sua prática Página 1/108 Excel

Página 82/108

Excel 2010 – Dashboard - Incrementando seus conhecimentos eAperfeiç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

Page 114: Excel 2010 – Dashboard - infobitsolucoes.com em Excel - Volum… · Excel 2010 – Dashboard - Incrementando seus conhecimentos e Aperfeiçoando a sua prática Página 1/108 Excel

Página 83/108

Excel 2010 – Dashboard - Incrementando seus conhecimentos eAperfeiçoando a sua prática GRÁFICO DE ESTOQUE MÍNIMO

OBJETIVO

Montar um gráfico de informativo de estoque mínimo.

114

Page 115: Excel 2010 – Dashboard - infobitsolucoes.com em Excel - Volum… · Excel 2010 – Dashboard - Incrementando seus conhecimentos e Aperfeiçoando a sua prática Página 1/108 Excel

EXEMPLO I

Monte uma tabela igual à figura abaixo:

Selecione as células de A2 a C13. No menu Inserir, escolha o gráfico decolunas 2D

(primeira opção). Vamos obter o gráfico conforme figura abaixo.

Página 84/108

115

Page 116: Excel 2010 – Dashboard - infobitsolucoes.com em Excel - Volum… · Excel 2010 – Dashboard - Incrementando seus conhecimentos e Aperfeiçoando a sua prática Página 1/108 Excel

Excel 2010 – Dashboard - Incrementando seus conhecimentos eAperfeiçoando a sua prática Clique com o botão direito na legenda, escolhaa 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 itemPreenchimento, na Cor da Borda marque sem linha e em Estilos de bordamarque cantos arredondados Selecione a série 2, com o botão direito domouse selecione a opção: Alterar tipo de gráfico de série. Escolha a opçãoLinha 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çãoFormatar Série de Dados, marque em opções de marcador: Interno tamanho2.

Página 85/108

116

Page 117: Excel 2010 – Dashboard - infobitsolucoes.com em Excel - Volum… · Excel 2010 – Dashboard - Incrementando seus conhecimentos e Aperfeiçoando a sua prática Página 1/108 Excel

Excel 2010 – Dashboard - Incrementando seus conhecimentos eAperfeiçoando a sua prática Em Estilo da Linha marque a opção Linhasuavizada.

Clique com o botão direito no gráfico e na opção Selecionar Dados, altereo nome das séries.

Selecione as linhas de grade e clique em delete.

117

Page 118: Excel 2010 – Dashboard - infobitsolucoes.com em Excel - Volum… · Excel 2010 – Dashboard - Incrementando seus conhecimentos e Aperfeiçoando a sua prática Página 1/108 Excel

Note que os meses de setembro e novembro, são os meses que temos umalerta, ou seja, o estoque mínimo esta sendo sinalizado.

Vamos fazer uma faixa compreendendo todos os meses para mostrar estasituação.

Selecione as células em que o gráfico esta compreendido e utilize a opçãocâmera e cole a figura na planilha desejada.

Página 86/108

Excel 2010 – Dashboard - Incrementando seus conhecimentos eAperfeiçoando a sua prática Alinhe o gráfico (meses) com as células.

118

Page 119: Excel 2010 – Dashboard - infobitsolucoes.com em Excel - Volum… · Excel 2010 – Dashboard - Incrementando seus conhecimentos e Aperfeiçoando a sua prática Página 1/108 Excel

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, emgerenciar regra, nova regra e escolha a opção usar uma fórmula pradeterminar quais...

Página 87/108

119

Page 120: Excel 2010 – Dashboard - infobitsolucoes.com em Excel - Volum… · Excel 2010 – Dashboard - Incrementando seus conhecimentos e Aperfeiçoando a sua prática Página 1/108 Excel

Excel 2010 – Dashboard - Incrementando seus conhecimentos eAperfeiçoando a sua prática =G$2=1 e em formatar, preenchimento, escolhaa 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 lugarda 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

Page 121: Excel 2010 – Dashboard - infobitsolucoes.com em Excel - Volum… · Excel 2010 – Dashboard - Incrementando seus conhecimentos e Aperfeiçoando a sua prática Página 1/108 Excel

Excel 2010 – Dashboard - Incrementando seus conhecimentos eAperfeiçoando a sua prática Teremos o seguinte efeito.

Página 89/108

121

Page 122: Excel 2010 – Dashboard - infobitsolucoes.com em Excel - Volum… · Excel 2010 – Dashboard - Incrementando seus conhecimentos e Aperfeiçoando a sua prática Página 1/108 Excel

Excel 2010 – Dashboard - Incrementando seus conhecimentos eAperfeiçoando a sua prática UTILIZAÇÃO DE DUAS CAIXAS DECOMBINAÇÃ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 estamostrabalhando com 4 áreas.

Vamos usar como suporte a segunda parte, ou seja, das células A15 a C22que não deverão ser alteradas.

Como são 4 áreas vamos selecionar 4 linhas: de A16 a A19.

Página 90/108

122

Page 123: Excel 2010 – Dashboard - infobitsolucoes.com em Excel - Volum… · Excel 2010 – Dashboard - Incrementando seus conhecimentos e Aperfeiçoando a sua prática Página 1/108 Excel

Excel 2010 – Dashboard - Incrementando seus conhecimentos eAperfeiç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 D2conforme figura abaixo.

123

Page 124: Excel 2010 – Dashboard - infobitsolucoes.com em Excel - Volum… · Excel 2010 – Dashboard - Incrementando seus conhecimentos e Aperfeiçoando a sua prática Página 1/108 Excel

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

Page 125: Excel 2010 – Dashboard - infobitsolucoes.com em Excel - Volum… · Excel 2010 – Dashboard - Incrementando seus conhecimentos e Aperfeiçoando a sua prática Página 1/108 Excel

Excel 2010 – Dashboard - Incrementando seus conhecimentos eAperfeiç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

Page 126: Excel 2010 – Dashboard - infobitsolucoes.com em Excel - Volum… · Excel 2010 – Dashboard - Incrementando seus conhecimentos e Aperfeiçoando a sua prática Página 1/108 Excel

Excel 2010 – Dashboard - Incrementando seus conhecimentos eAperfeiç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 dadosconforme figura abaixo.

126

Page 127: Excel 2010 – Dashboard - infobitsolucoes.com em Excel - Volum… · Excel 2010 – Dashboard - Incrementando seus conhecimentos e Aperfeiçoando a sua prática Página 1/108 Excel

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 houvercargo, digite um espaço em branco.

Vamos montar uma caixa de seleção para a lista de B16 a A22 e o seuresultado coloque em D16.

Página 93/108

Excel 2010 – Dashboard - Incrementando seus conhecimentos eAperfeiçoando a sua prática

127

Page 128: Excel 2010 – Dashboard - infobitsolucoes.com em Excel - Volum… · Excel 2010 – Dashboard - Incrementando seus conhecimentos e Aperfeiçoando a sua prática Página 1/108 Excel

Página 94/108

Excel 2010 – Dashboard - Incrementando seus conhecimentos eAperfeiçoando a sua prática GRÁFICO PARA MEDIR VISITAS EM SITEPOR HORA

OBJETIVO

Montar um gráfico de informativo de visitas em site.

EXEMPLO I

Monte uma tabela igual à figura abaixo:

128

Page 129: Excel 2010 – Dashboard - infobitsolucoes.com em Excel - Volum… · Excel 2010 – Dashboard - Incrementando seus conhecimentos e Aperfeiçoando a sua prática Página 1/108 Excel

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çãoSelecionar 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

Page 130: Excel 2010 – Dashboard - infobitsolucoes.com em Excel - Volum… · Excel 2010 – Dashboard - Incrementando seus conhecimentos e Aperfeiçoando a sua prática Página 1/108 Excel

Excel 2010 – Dashboard - Incrementando seus conhecimentos eAperfeiç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 aopçã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 acor 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 parasem linha.

Clique sobre o gráfico. No menu em Ferramentas de Gráfico, Layout,Linhas de grade, Linhas de grade verticais, escolha linha de gradesprincipais.

Vamos obter o gráfico conforme figura abaixo.

Página 96/108

130

Page 131: Excel 2010 – Dashboard - infobitsolucoes.com em Excel - Volum… · Excel 2010 – Dashboard - Incrementando seus conhecimentos e Aperfeiçoando a sua prática Página 1/108 Excel

Excel 2010 – Dashboard - Incrementando seus conhecimentos eAperfeiçoando a sua prática Para incrementar ainda mais o seu gráfico,podemos criar pequenos gráficos de colunas, como na figura abaixo. Cadagráfico representa um dia.

Para alinhar cada gráfico a cada dia, selecione as células em que o gráficoesta compreendido e clique na câmera. Acerte o tamanho das células com ográfico.

131

Page 132: Excel 2010 – Dashboard - infobitsolucoes.com em Excel - Volum… · Excel 2010 – Dashboard - Incrementando seus conhecimentos e Aperfeiçoando a sua prática Página 1/108 Excel

Clique nos valores de cada dia e monte um gráfico de colunas. O gráficodeverá ter o mesmo tamanho da célula. Utilize o botão câmera e cole ográfico embaixo de cada dia.

Página 97/108

Excel 2010 – Dashboard - Incrementando seus conhecimentos eAperfeiç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

Page 133: Excel 2010 – Dashboard - infobitsolucoes.com em Excel - Volum… · Excel 2010 – Dashboard - Incrementando seus conhecimentos e Aperfeiçoando a sua prática Página 1/108 Excel

Clique no gráfico com o botão direito do mouse, opção Formatar serie dedados e na opção preenchimento escolha várias cores por ponto.

Página 98/108

133

Page 134: Excel 2010 – Dashboard - infobitsolucoes.com em Excel - Volum… · Excel 2010 – Dashboard - Incrementando seus conhecimentos e Aperfeiçoando a sua prática Página 1/108 Excel

Excel 2010 – Dashboard - Incrementando seus conhecimentos eAperfeiçoando a sua prática Retire as legendas.

Clique no gráfico com o botão direito do mouse, opção Formatar serie dedados 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 parasem linha.

Página 99/108

134

Page 135: Excel 2010 – Dashboard - infobitsolucoes.com em Excel - Volum… · Excel 2010 – Dashboard - Incrementando seus conhecimentos e Aperfeiçoando a sua prática Página 1/108 Excel

Excel 2010 – Dashboard - Incrementando seus conhecimentos eAperfeiçoando a sua prática GRÁFICO DE VELOCÍMETRO

OBJETIVO

Montar um gráfico de velocímetro.

135

Page 136: Excel 2010 – Dashboard - infobitsolucoes.com em Excel - Volum… · Excel 2010 – Dashboard - Incrementando seus conhecimentos e Aperfeiçoando a sua prática Página 1/108 Excel

EXEMPLO I

Monte uma tabela igual à figura abaixo:

Selecione as células de B2 a B5 e no menu, inserir, gráfico, escolha aopção Rosca (primeira opção).

Página 100/108

136

Page 137: Excel 2010 – Dashboard - infobitsolucoes.com em Excel - Volum… · Excel 2010 – Dashboard - Incrementando seus conhecimentos e Aperfeiçoando a sua prática Página 1/108 Excel

Excel 2010 – Dashboard - Incrementando seus conhecimentos eAperfeiçoando a sua prática Clique sobre o gráfico e depois clique sobre amaior fatia, com o botão direito do mouse, selecione Formatar Pontos deDados, em opções de série, em ângulo mude para 90.

Em preenchimento clique em sem preenchimento e feche a janela e vamosobter o resultado como na figura abaixo.

Clique sobre o gráfico uma vez para que ele seja selecionado, botão direitodo mouse e escolha Selecionar dados.

Página 101/108

137

Page 138: Excel 2010 – Dashboard - infobitsolucoes.com em Excel - Volum… · Excel 2010 – Dashboard - Incrementando seus conhecimentos e Aperfeiçoando a sua prática Página 1/108 Excel

Excel 2010 – Dashboard - Incrementando seus conhecimentos eAperfeiç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 onome Disc para a série1, para a série2 Rotdisc e para a série3 agulha.

138

Page 139: Excel 2010 – Dashboard - infobitsolucoes.com em Excel - Volum… · Excel 2010 – Dashboard - Incrementando seus conhecimentos e Aperfeiçoando a sua prática Página 1/108 Excel

Página 102/108

Excel 2010 – Dashboard - Incrementando seus conhecimentos eAperfeiçoando a sua prática Dê um duplo clique na borda do gráfico paraativar a guia Design e escolha o estilo 42.

139

Page 140: Excel 2010 – Dashboard - infobitsolucoes.com em Excel - Volum… · Excel 2010 – Dashboard - Incrementando seus conhecimentos e Aperfeiçoando a sua prática Página 1/108 Excel

Apague a legenda.

Clique sobre o fundo do gráfico e em formatar área do gráfico e empreenchimento escolha sem preenchimento.

Clique como botão direito do mouse sobre a série rotdisc e adicione rótulode dados.

Clique novamente como botão direito do mouse sobre a série rotdisc eescolha a opção Selecionar Dados.

Página 103/108

140

Page 141: Excel 2010 – Dashboard - infobitsolucoes.com em Excel - Volum… · Excel 2010 – Dashboard - Incrementando seus conhecimentos e Aperfeiçoando a sua prática Página 1/108 Excel

Excel 2010 – Dashboard - Incrementando seus conhecimentos eAperfeiç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 Formatarró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ériede 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 degráfico para pizza (primeira opção).

Página 104/108

141

Page 142: Excel 2010 – Dashboard - infobitsolucoes.com em Excel - Volum… · Excel 2010 – Dashboard - Incrementando seus conhecimentos e Aperfeiçoando a sua prática Página 1/108 Excel

Excel 2010 – Dashboard - Incrementando seus conhecimentos eAperfeiçoando a sua prática Clique na série agulha e com o botão direitodo mouse escolha Formatar série de dados.

Em opções de série, em ângulo coloque 90 e em plotar selecione eixosecundário.

142

Page 143: Excel 2010 – Dashboard - infobitsolucoes.com em Excel - Volum… · Excel 2010 – Dashboard - Incrementando seus conhecimentos e Aperfeiçoando a sua prática Página 1/108 Excel

Clique sobre o gráfico e depois sobre a maior faixa, e com o botão direitodo 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 direitodo mouse selecione Formatar ponto de dados. A seleção deverá estarconforme a figura abaixo, somente e maior faixa.

Em preenchimento clique em sem preenchimento.

Página 105/108

143

Page 144: Excel 2010 – Dashboard - infobitsolucoes.com em Excel - Volum… · Excel 2010 – Dashboard - Incrementando seus conhecimentos e Aperfeiçoando a sua prática Página 1/108 Excel

Excel 2010 – Dashboard - Incrementando seus conhecimentos eAperfeiç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 seralteradas).

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

Page 145: Excel 2010 – Dashboard - infobitsolucoes.com em Excel - Volum… · Excel 2010 – Dashboard - Incrementando seus conhecimentos e Aperfeiçoando a sua prática Página 1/108 Excel

Para alterar a cor da agulha, na célula E4 mude o valor para 10, cliquesomente na agulha até que somente ela esteja selecionada.

Página 106/108

Excel 2010 – Dashboard - Incrementando seus conhecimentos eAperfeiçoando a sua prática Clique com o botão direito do mouse eselecione Formatar ponto de dados, em preenchimento escolhapreenchimento 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 mostradono gráfico.

145

Page 146: Excel 2010 – Dashboard - infobitsolucoes.com em Excel - Volum… · Excel 2010 – Dashboard - Incrementando seus conhecimentos e Aperfeiçoando a sua prática Página 1/108 Excel

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) emudar para vermelho, amarelo e verde conforme figura abaixo. Clique nosegmento 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 corconforme a figura abaixo.

Página 107/108

Excel 2010 – Dashboard - Incrementando seus conhecimentos e

146

Page 147: Excel 2010 – Dashboard - infobitsolucoes.com em Excel - Volum… · Excel 2010 – Dashboard - Incrementando seus conhecimentos e Aperfeiçoando a sua prática Página 1/108 Excel

Aperfeiçoando a sua prática Abaixo temos a tela final do seu gráfico

Página 108/108

147