Excel 2010 – Dashboard - Incrementando seus conhecimentos e Aperfeiçoando a sua prática Página Página 1/108 1/ 108 Excel 2010 – Dashboard - Incrementando seus conhecimentos e Aperfeiçoando a sua prática Algumas palavras do autor:
Este pequeno pequeno e-book e- book demonst demonstra ra a criação criaçã o de painéis de controle controle (dashboard) no Excel Excel 2010. Nosso objetivo é prover pr over ensinament ensinamentos os para profissionais de diversas dive rsas áreas (financeira, (financeira, contabili contabilidade, dade, economia, economia, administração), administração), micro e pequenas pequenas empresas onde se busca uma melhor análise e gerência de informações. A seguir você poderá pode rá acompanh acompanhar ar todo o conteúdo conteúdo desta apostila.
Acesse também www.ensinandoexcel.com.br 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
Excel 2010 – Dashboard - Incrementando seus conhecimentos e Aperfeiçoando a sua prática Página Página 1/108 1/ 108 Excel 2010 – Dashboard - Incrementando seus conhecimentos e Aperfeiçoando a sua prática Algumas palavras do autor:
Este pequeno pequeno e-book e- book demonst demonstra ra a criação criaçã o de painéis de controle controle (dashboard) no Excel Excel 2010. Nosso objetivo é prover pr over ensinament ensinamentos os para profissionais de diversas dive rsas áreas (financeira, (financeira, contabili contabilidade, dade, economia, economia, administração), administração), micro e pequenas pequenas empresas onde se busca uma melhor análise e gerência de informações. A seguir você poderá pode rá acompanh acompanhar ar todo o conteúdo conteúdo desta apostila.
Acesse também www.ensinandoexcel.com.br 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
Excel 2010 – Dashboard - Incrementando seus conhecimentos e Aperfeiçoando a sua prática Excel 2010 – DAshboard
Incrementando
Seus conhecimentos
Aperfeiçoando
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 . 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
. 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
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.
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.
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.
Versões do Microsoft Excel para Windows
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 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-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 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
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
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.
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
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.
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
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 ;
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
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.
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 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
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 ;
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
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 da pesquisa que irá retornar o conteúdo da linha 8. Vejamos a fórmula que foi utilizada: =PROCH(B6; B1:D4; 3; FALSO) Valor a ser procurado, neste caso Prova 1
B6 –
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
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
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
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 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 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 exem exemplo plo Maria Mari a a célula A10 que apresent aprese ntaa o texto texto Mari Mariaa – significa significa que a partir da célula C2, irá buscar duas células abaixo e deslocar duas células para a esquerda.
Página 23/108
Excel 2010 – Dashboard - Incrementando seus conhecimentos e Aperfeiçoando a sua prática prá tica FUNÇÃO 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 Identifica o texto. núm núm_vez _ve z e s 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
EXEMPLO III 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 uma célula. c élula. SINTAXE DA FUNÇÃO INDIRETO(Texto_ref; A1) Te Te xto_ref É uma referência a uma célula. A1
É um valor lógico que especifica o tipo de referência.
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 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
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.
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
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
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 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)) 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
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. 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.
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) 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
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 coluna A.
No menu clique em Dados, Dados, Texto para Colunas. Colunas.
Página 37/108
Excel 2010 – Dashboard - Incrementando seus conhecimentos e Aperfeiçoando a sua prática Escolha a opção delimitado, delimitado , Ponto e vírgula vírgula e e concluir.
Note Note que Maria da Silva Sil va apareceu apare ceu 2 vezes.
Vamos falar para o Excel que se o nome que estiver na coluna A se repetir, ele deverá excluir excluir a linha linha que estiver em e m duplicidade. Selecione as colunas de A a D. Página 38/108
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.
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.
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
Excel 2010 – Dashboard - Incrementando seus conhecimentos e Aperfeiçoando a sua prática
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 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 Atingir Atingir meta tem como finali finalidade dade obter um valor val or de d e eent ntrada rada de uma uma determin d eterminada ada fórmula para atingir um resultado conhecido. EXEMPLO I Digam Digamos os que seja prestado um serviço no valor de R$ 1.000,00. Se você voc ê tiver que pagar pagar 27,5% de imposto, imposto, qual deveria ser o valor cobrado c obrado para que que ao final você tenha tenha líquido 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 imposto desejado. desej ado. 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
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
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 uros será sempre de 3%.
Vamos ter uma prestação mensal de R$ 1.544,50. Analisando suas contas, chegouse à 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 unto ao banco será de R$ 32.373,02.
Página 44/108
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.
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
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 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
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
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 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
Excel 2010 – Dashboard - Incrementando seus conhecimentos e Aperfeiçoando a sua prática
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)
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
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
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 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
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
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
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.
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
Excel 2010 – Dashboard - Incrementando seus conhecimentos e Aperfeiçoando a sua prática
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.
Página 58/108
Excel 2010 – Dashboard - Incrementando seus conhecimentos e Aperfeiçoando a sua prática
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
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
Excel 2010 – Dashboard - Incrementando seus conhecimentos e Aperfeiçoando a sua prática TABELA DINÂMICA UTILIZANDO VÁRIAS PLANILHAS OBJETIVO 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.
EXEMPLO Lojas.xlsx
Abra uma nova planilha.
Clique no Menu, Dados, Obter Dados Externos, De Outras Fontes, Do Microsoft Query.
Página 61/108
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.
Página 62/108
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
Excel 2010 – Dashboard - Incrementando seus conhecimentos e Aperfeiçoando a sua prática Você irá obter a seguinte tela. Clique na caixa SQL.
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
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
Excel 2010 – Dashboard - Incrementando seus conhecimentos e Aperfeiçoando a sua prática Vamos obter a seguinte tela
Feche a janela
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
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 tabela dinâmica quando as células chaves estiverem em branco.
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
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
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:
►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.
Página 71/108
Excel 2010 – Dashboard - Incrementando seus conhecimentos e Aperfeiçoando a sua prática Inserindo um controle de botão
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
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
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. 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
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.
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.
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
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.
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 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
Excel 2010 – Dashboard - Incrementando seus conhecimentos e Aperfeiçoando a sua prática Para a segunda série marque
Para o eixo horizontal marque
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 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 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
Excel 2010 – Dashboard - Incrementando seus conhecimentos e 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.
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.
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. 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
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
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.
Note que os meses de setembro e novembro, são os meses que temos um alerta, o 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.
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
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
Excel 2010 – Dashboard - Incrementando seus conhecimentos e Aperfeiçoando a sua prática Teremos o seguinte efeito.
Página 89/108
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
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.
Não tecle . 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
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
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.
Não tecle . 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
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:
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
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
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. Acerte o tamanho das células com o gráfico.
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
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
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
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. 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
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
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.
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.
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
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
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.
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
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.
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. 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
