Teste

EXCEL 2016 – CRIANDO PLANILHAS INTERATIVAS E DASHBOARD

EXCEL 2016 – CRIANDO PLANILHAS INTERATIVAS E DASHBOARD

São Paulo agosto/2016

Nome do aluno

ADMINISTRAÇÃO REGIONAL DO SENAC NO ESTADO DE SÃO PAULO Gerência de Desenvolvimento Luciana Bon Duarte Coordenação Técnica Richard Martelli Apoio Técnico Abraão Gomes de Godoy Pedro Henrique Carvalho de Assis Elaboração do Recurso Didático Maria Cecilia Ferreira Editoração Eletrônica Veridiana Freitas Revisão de Texto Luiza Elena Luchini (coord.)

Janaina Lira Rodolfo Santana

© Senac São Paulo, 2016 Proibida a reprodução sem autorização expressa.

Todos os direitos reservados ao Senac São Paulo.

Sumário 1 CAIXAS DE CONTROLE / 9 Introdução / 9 Criar planilhas / 10 Habilitar controles / 10 Controles de formulário / 12 1. Botão (Controle de Formulário) / 12 1.1. Caixa de combinação / 12 1.2. Botão de rotação / 12 1.3. Caixa de listagem / 13 1.4. Barra de rolagem / 13 1.5. Caixa de grupos / 14 1.6. Caixa de seleção / 14 1.7. Botão de opção / 14 Executar controles / 15 Associar comando aos botões de controles / 15 Criar e executar macro / 16 2. Controles / 18 2.1 Criar e executar caixa de combinação / 18 2.2 Criar e executar o botão de rotação / 19 2.3 Criar e executar uma caixa de listagem / 20 2.4 Criar e executar a barra de rolagem / 21 2.5 Criar a caixa de grupos / 23 2.6 Criar a caixa de seleção / 23 2.7 Criar o botão de opção / 24 3. Função REPT / 25 Exercício proposto / 26

SENAC SÃO PAULO | 5

EXCEL 2016 – CRIANDO PLANILHAS INTERATIVAS E DASHBOARD

2 FUNÇÕES ESPECÍFICAS / 29 Introdução / 29 Funções / 29 Nome da lista / 30 Validação de dados / 32 Função REPT / 34 Função SE / 35 Função SOMASE / 38 Função SOMASES / 40 Função SEERRO / 41 Função PROCV / 42 Função PROCH / 44 Função CORRESP / 45 Função ÍNDICE / 45 Função ESCOLHER / 47 Função MÉDIASE / 48 Função MÉDIASES / 49 Tabela Dinâmica / 50 Exercícios propostos / 54 3 CRIAÇÃO DE GRÁFICOS / 59 Introdução / 59 Criação de gráficos / 59 Gráfico tipo colunas / 60 Gráfico tipo barras / 64 Gráfico tipo linhas / 65 Gráfico tipo pizza / 66 Gráfico tipo combinação / 67 Gráfico Dinâmico / 68 Formatação de gráficos / 70 Gráficos específicos – Excel 2016 / 74 Exercício proposto / 75 4 GRÁFICOS ESPECÍFICOS – DASHBOARD / 77 Introdução / 77 Gráfico com as funções REPT e SE / 78 Gráfico Dashboard – Colunas e linhas com o Botão de Rotação / 79 Gráfico Dashboard – Colunas e pizza com barra de rolagem / 83 Gráfico Dashboard – Explosão solar com o Botão de Opção / 85 Gráfico Dashboard – P izza e colunas com barras, pirâmide, cilindro e Botão de Opção / 87 Gráfico Dashboard – Velocímetro com Botão de Opção / 90 Gráfico Dashboard – Termômetro com barra de rolagem / 95

6 | SENAC SÃO PAULO

EXCEL 2016 – CRIANDO PLANILHAS INTERATIVAS E DASHBOARD

Gráfico Dashboard – Semáforo com barra de rolagem / 97 Gráfico Dashboard – Combinação com barra de rolagem / 100 Gráfico Dashboard – Gráficos 2016 com PROCV / 102 Exercícios propostos / 105 5 MACROS / 109 Introdução / 109 Criar macros / 109 Criar botões / 112 Macro de cálculo / 115 Desativar planilhas / 117 Exercício proposto / 118 6 EXERCÍCIOS COMPLEMENTARES / 119 Introdução / 119 Exercício 1 – Caixa de combinação / 119 Exercício 2 – Barra de rolagem / 120 Exercício 3 – Funções SE e MÉDIASE / 122 Exercício 4 – Funções PROCV e SOMASE / 123 Exercício 5 – Função PROCH e barra de rolagem / 125 Exercício 6 – Função SE / 125 Exercício 7 – Vários gráficos Dashboard / 126 Exercício 8 – Dashboard: termômetro / 129 Exercício 9 – Dashboard com Botão de Opção / 130 Exercício 10 – Macros / 130 REFERÊNCIAS / 131

SENAC SÃO PAULO | 7

1. C aixas de controle

INTRODUÇÃO

D

ashboard significa “painel de instrumentos”, então esta apostila tem por objetivo apresentar vários painéis que resultam em gráficos otimizados, construídos no MS Excel 2016. É possível criar vários tipos de gráficos no Excel, no entanto eles são atualizados automaticamente à medida que os dados são alterados nas planilhas. Gráficos Dashboard também serão atualizados, mas por meio de painéis, que compõem uma forma mais ágil e dinâmica na apresentação gráfica.

Muitas vezes um gestor necessita de uma informação estatística de forma rápida e visual, que apresente diversos dados ao mesmo tempo; desse modo, os gráficos sempre foram muito utilizados para essa forma de representação. Por sua vez, o Dashboard também apresenta esses gráficos, mas de forma consolidada, ou seja, gráficos com várias informações ao mesmo tempo. Funções bastante utilizadas são as SOMASE e SOMASES em razão da facilidade em consolidar dados agrupados. Para visualizar a otimização dos gráficos, as planilhas de resumo serão vinculadas aos botões de controle.

O objetivo da abordagem em relação às caixas e aos botões de controle é familiarizar o usuário com esta nova funcionalidade do Excel.

Para a criação dos gráficos Dashboard é necessário trabalhar com funções específicas, principalmente as funções condicionais, pois permitem criar planilhas de resumo que facilitam a criação dos gráficos.

SENAC SÃO PAULO | 9

EXCEL 2016 – CRIANDO PLANILHAS INTERATIVAS E DASHBOARD

CRIAR PLANILHAS Uma planilha é a base para trabalhar com o MS Excel e trata-se de uma composição ou conjunto de informações, como informações financeiras, estatísticas, etc., ou seja, qualquer informação que necessite de algum tipo de cálculo. Para trabalhar com Dashboard é necessário que as planilhas estejam prontas, formatadas e calculadas, pois o objetivo é gerar diversos tipos de gráficos por meio de caixas de controle ou painéis. A versão do Excel aqui apresentada é a 2016, no entanto existem poucas diferenças em relação à versão 2013, sendo o grande diferencial alguns gráficos específicos e muito interessantes além de algumas funções, como, por exemplo, a SES. Assim como nas versões anteriores, a janela do Excel é composta por alguns elementos básicos, tais como guias ou antigos menus. Cada guia é composta por grupos, por exemplo: guia Página Inicial, grupo Fonte. Para a inserção de dados, é preciso observar em que coluna os dados serão digitados, assim como a linha e consequentemente a célula, ou seja, uma célula é o cruzamento entre uma coluna e uma linha. Observe a figura 1.1, a célula apresentada é a D11. Outro ponto importante são as planilhas, guias ou abas de planilhas. Por padrão, quando o Excel 2016 é aberto, é apresentada apenas uma aba, que pode ser renomeada, ter a cor alterada, ser ocultada, protegida, etc.

Neste capítulo, todas as planilhas utilizadas estão digitadas no documento disponível chamado Planilhas Capítulo 1. Para todos os recursos abordados neste capítulo, abra esse documento. Conceitos básicos não serão apresentados.

A figura 1.1 apresenta a janela do Excel 2016. Como mencionado anteriormente, é muito similar às versões anteriores, porém apresenta uma opção de pesquisa (número 5) com a pergunta O que você deseja fazer... Basta digitar o texto desejado e a pesquisa será efetuada dentro das funcionalidades do Excel, por exemplo, se digitar “gráfico” será aberta uma pequena janela com opções de gráficos.

Figura 1.1 – Documento Planilhas Capítulo 1.

HABILITAR CONTROLES Os controles serão bastante usados e representam a base para os gráficos Dashboard. Eles são também muito utilizados em formulários, pois por meio deles a possibilidade de otimizar as planilhas é bem maior. Para trabalhar com os botões de controle, é necessário habilitar a guia Desenvolvedor, caso não esteja habilitada. Para isso: 1. Clique na guia Arquivo, opção Opções.

2. Clique na opção Personalizar Faixa de Opções.

3. Clique para habilitar a opção Desenvolvedor.

4. Clique no botão OK para confirmar.

10 | SENAC SÃO PAULO

EXCEL 2016 – CRIANDO PLANILHAS INTERATIVAS E DASHBOARD

Observe a figura 1.2. Na janela Opções do Excel, clique em Personalizar a Faixa de Opções, opção Desenvolvedor. Ao confirmar, a guia é inserida automaticamente na janela do Excel.

Figura 1.2 – Janela de configurações, habilitar opção Desenvolvedor.

Para usar as diversas opções de controles (Controles de Formulários e Controles ActiveX), ou seja, os diferentes tipos de botões, é necessário que o desenvolvedor fique ativado. Após ativado, ele vai permanecer assim, a não ser que seja desabilitado novamente. Para trabalhar com macros ou Visual Basic, esta guia também é necessária.

Observe na figura 1.3 a guia Desenvolvedor e o painel com as opções de controles.

Figura 1.3 – Documento Planilhas Capítulo 1 (guia Desenvolvedor).

A seguir serão apresentados alguns dos controles mais utilizados e, mais adiante, várias fórmulas serão associadas aos controles para que as planilhas e os gráficos passem a funcionar de forma diferente das planilhas e dos gráficos convencionais. Os controles ActiveX são utilizados em VBA; aqui, usaremos os Controles de Formulário.

Figura 1.4 – Controles.

SENAC SÃO PAULO | 11

EXCEL 2016 – CRIANDO PLANILHAS INTERATIVAS E DASHBOARD

Controles de formulário 1. Botão (Controle de Formulário) O botão Controle de Formulário é uma opção de seleção, mas geralmente é utilizado para associar macros, pois, ao ser ativado, executa a rotina gravada por meio de macros. Para criar o botão: 1. Clique na guia Desenvolvedor, grupo Controles, opção Inserir.

2. Clique no controle Botão (Controle de Formulário).

3. Posicione o mouse na planilha e arraste até que fique com o tamanho desejado. Para digitar um rótulo, basta soltar o mouse e digitar, conforme a figura 1.5.

Figura 1.5 – Planilha exemplo de botão.

1.1 Caixa de combinação Caixa de combinação é uma caixa com uma lista de itens suspensos. Essa lista é combinada a partir de textos ou outros elementos e, ao criar a combinação, a lista com os itens fica mais compacta, sendo preciso clicar na seta para baixo para acioná-la. Similar ao recurso de Validação de dados.

Para inserir a caixa de combinação: 1. Clique na guia Desenvolvedor, grupo Controles, opção Inserir.

2. Clique na opção Caixa de Combinação (Controle de Formulário).

3. Posicione o mouse na planilha e arraste até que fique com o tamanho desejado, conforme a figura 1.6.

Figura 1.6 – Caixa de combinação.

1.2 Botão de rotação A função do botão de rotação é aumentar ou diminuir um valor em uma determinada célula associada a ele. Esse valor funciona como um incremento, ou seja, de um em um, de dois em dois e assim por diante. Para inserir o botão de rotação: 1. Clique na guia Desenvolvedor, grupo Controles, opção Inserir.

12 | SENAC SÃO PAULO

EXCEL 2016 – CRIANDO PLANILHAS INTERATIVAS E DASHBOARD

2. Clique na opção Botão de Rotação (Controle de Formulário).

3. Posicione o mouse na planilha e arraste até que fique com o tamanho desejado, conforme a figura 1.7.

Figura 1.7 – Botão de rotação.

1.3 Caixa de listagem Uma caixa de listagem exibe uma lista com itens, na qual é possível escolher uma das opções, que variam entre número e conteúdo de texto. Existem três opções de seleção: única, múltipla e estendida. Seleção única é a seleção de um item por vez; na múltipla, os itens são selecionados de forma alternada; na estendida, pode-se selecionar todas de uma vez com a tecla Shift pressionada. A diferença com relação à caixa de combinação é que nesta a lista fica oculta, na caixa de listagem os itens são visíveis. Para inserir a caixa de listagem: 1. Clique na guia Desenvolvedor, grupo Controles, opção Inserir.

2. Clique na opção Caixa de Listagem (Controle de Formulário).

3. Posicione o mouse na planilha e arraste até que fique com o tamanho desejado, conforme a figura 1.8.

Figura 1.8 – Caixa de Listagem.

1.4 Barra de rolagem A barra de rolagem percorre um determinado intervalo predefinido pelo usuário. Para rolar, basta clicar nas setas ou arrastar a barra para o lado desejado. Para inserir a barra de rolagem: 1. Clique na guia Desenvolvedor, grupo Controles, opção Inserir.

2. Clique na opção Barra de Rolagem (Controle de Formulário).

3. Posicione o mouse na tela e arraste até que fique com o tamanho desejado, conforme a figura 1.9.

Figura 1.9 – Barra de rolagem.

SENAC SÃO PAULO | 13

EXCEL 2016 – CRIANDO PLANILHAS INTERATIVAS E DASHBOARD

1.5 Caixa de grupos A caixa de grupos é um retângulo, visualmente, mas, para trabalhar com os botões de opções (assinalar), o melhor é usar essa caixa para que os botões fiquem mais organizados.

1. Clique na guia Desenvolvedor, grupo Controles, opção Inserir.

2. Clique na opção Caixa de Grupos (Controle de Formulário).

3. Posicione o mouse na planilha e arraste até que fique com o tamanho desejado, conforme a figura 1.10.

Figura 1.10 – Caixa de grupos.

1.6 Caixa de seleção Pela caixa de seleção é possível ativar ou desativar a opção desejada. Uma caixa de seleção obedece a três estados – ativada, desativada e seleção múltipla – e tem como resultado verdadeiro ou falso.

Para inserir a caixa de seleção, primeiro crie uma caixa de grupo.

1. Clique na guia Desenvolvedor, grupo Controles, opção Inserir.

2. Clique na opção Caixa de Grupo (Controle de Formulário).

3. Posicione o mouse na planilha e arraste até que fique com o tamanho desejado.

4. Clique na Caixa de seleção e dê um clique dentro da Caixa de grupo.

5. Deixe como mostra a figura 1.11.

Figura 1.11 – Caixa de seleção.

1.7 Botão de opção O botão de opção é outra forma de trabalhar com as seleções. Ele é geralmente usado dentro de uma caixa de grupo e permite que os estados ativado e desativado funcionem. Talvez seja um dos botões mais utilizados, em virtude do resultado que produz.

1. Repita o mesmo procedimento utilizado anteriormente, porém usando o Botão de opção, conforme a figura 1.12.

14 | SENAC SÃO PAULO

EXCEL 2016 – CRIANDO PLANILHAS INTERATIVAS E DASHBOARD

Figura 1.12 – Botão de opção.

Para selecionar qualquer botão ou opção de controle é necessário usar Ctrl + clique do mouse. Após selecionar, é possível excluir, mover ou redimensionar.

EXECUTAR CONTROLES A execução de controles é realizada por planilhas. Para cada situação desejada, um controle diferente pode ser utilizado.

Associar comando aos botões de controles Para testar o uso dos controles citados anteriormente, será necessária a criação de uma pequena planilha; não é preciso trabalhar com planilhas grandes, pois o objetivo é aprender a utilizar as funcionalidades de forma adequada.

1. Abra o MS Excel.

2 Abra o documento Planilhas Capítulo 1. É necessário sempre salvar as alterações. Salve em uma pasta específica; se desejar, altere o nome.

Após salvar, deixe a planilha formatada do modo desejado. Clique nas células onde foi efetuada a soma, pressione a tecla F2 e verifique a função usada. Altere o nome da guia Planilha1 para Macro.

Produtos Eletrônicos

Produtos

Quantidade

TV

1.200

Celular

4.300

Tablet

1.400

iPad

2.900

Total

9.800

SENAC SÃO PAULO | 15

EXCEL 2016 – CRIANDO PLANILHAS INTERATIVAS E DASHBOARD

Criar e executar macro Macro é um conjunto de ações ou rotinas gravadas para serem executadas posteriormente. Por meio de macros, o usuário pode definir rotinas executadas com muita frequência. Para que uma macro funcione, primeiro é necessário que o usuário saiba o que deseja executar, para então gravar toda a ação que será executada ao acionar um botão ou objeto. Para que a rotina funcione, é preciso associar a macro criada a algum tipo de objeto ou utilizar atalhos.

A macro que criaremos será de formatação. Neste caso, sempre que o botão for acionado, será alterada a cor da fonte e a cor de preenchimento.

Execute somente a rotina sugerida, pois, com o gravador de macro acionado, tudo que for realizado na planilha será gravado. Então, muito cuidado!

Para criar a macro é necessário que a guia Desenvolvedor esteja habilitada (recurso já apresentado no início desta apostila). Para criar a macro: 1. Clique em uma célula qualquer fora da planilha digitada.

2. Clique na guia Desenvolvedor.

3. No grupo Código, clique na opção Gravar Macro.

4. Em Nome da macro, digite formatar (o Excel não aceita espaço no nome).

5. Pressione o botão OK.

Figura 1.13 – Janela de criação de macro.

6. Altere a cor da fonte e de preenchimento.

7. Clique na opção Parar gravação, conforme figura 1.14.

Figura 1.14 – Opção Parar gravação.

Após gravar a macro, é necessário associar a rotina criada a um objeto. Neste caso, será utilizado o Botão (Controle de Formulário).

8. Clique na guia Desenvolvedor, grupo Controles.

9. Clique na opção Inserir e em Botão (Controle de Formulário).

10. Clique na macro formatar e depois no botão OK.

16 | SENAC SÃO PAULO

EXCEL 2016 – CRIANDO PLANILHAS INTERATIVAS E DASHBOARD

Figura 1.15 – Janela com a macro gravada.

11. Como rótulo do botão, digite Formatar.

12. Selecione o título da tabela e clique no botão da macro.

Observe que o título foi alterado de acordo com o que foi gravado. Teste em outras células.

Figura 1.16 – Botão Formatar é responsável pela execução da macro.

Grave várias macros utilizando recursos diferentes, por exemplo: navegar de uma planilha para outra, macro de cálculo como Soma e Média com execução na mesma célula, limpar formatação, abrir outro documento, etc.

Antes ou após criar macros deve-se salvar o documento e alterar o Tipo. Não é necessário alterar o nome da planilha, mas é preciso alterar o Tipo para Pasta de Trabalho Habilitada para Macro do Excel, conforme a figura 1.17. Sem esse procedimento, ao fechar o documento, as macros são apagadas.

Figura 1.17 – Opção Tipo.

SENAC SÃO PAULO | 17

EXCEL 2016 – CRIANDO PLANILHAS INTERATIVAS E DASHBOARD

2. Controles 2.1 Criar e executar caixa de combinação Uma caixa de combinação cria uma lista com itens suspensos, a qual será feita a partir da planilha Produtos Eletrônicos (crie uma cópia como outra guia). Essa lista será associada à caixa de combinação ao mesmo tempo que terá uma célula vinculada. Ao selecionar o item da lista, surge na célula vinculada a posição do item nessa lista, por exemplo: TV = 1, Celular = 2, e assim por diante.

1. Abra o documento Planilhas Capítulo 1.

2. Crie uma cópia da planilha: segure a tecla Ctrl e, com o mouse, arraste a planilha para o lado direito da aba, solte primeiro o mouse e depois a tecla Ctrl, dessa forma a planilha é duplicada.

3. Renomeie a planilha ou aba como Caixa de Combinação.

4. Deixe a planilha como a figura 1.18.

Figura 1.18 – Janela Posição na Lista.

Para criar a caixa de combinação: 1. Clique na guia Desenvolvedor, grupo Controles, opção Inserir.

2. Em Controles de Formulário, clique em Caixa de Combinação (Controle de Formulário).

3. Com o mouse pressionado, arraste o cursor na planilha.

4. Pressione o botão direito e clique na opção Formatar Controle.

5. Em Formatar Controle, clique na guia Controle.

6. Em Intervalo de Entrada, selecione da célula A4 até a célula A7.

7. Em Vínculo da Célula, clique na célula D2.

8. Para confirmar, clique no botão OK.

Figura 1.19 – Caixa de combinação.

18 | SENAC SÃO PAULO

EXCEL 2016 – CRIANDO PLANILHAS INTERATIVAS E DASHBOARD

Após criar a caixa de combinação, faça alguns testes o observe o resultado. Neste exemplo, ao clicar na seta para baixo da caixa, imediatamente surge na célula D2 um número que representa a posição do texto na planilha, isto é, se é o primeiro, o segundo, o terceiro, assim sucessivamente. Na figura 1.20, o item da lista é Celular e na posição da lista ele é o segundo (2).

Figura 1.20 – Caixa de combinação criada.

2.2 Criar e executar o botão de rotação O botão de rotação tem como objetivo aumentar ou diminuir determinado valor em uma célula. Suponha que um usuário precisa de uma lista numérica com um valor mínimo e um valor máximo. Esse valor será a base para calcular determinada situação.

1. Abra o documento Planilhas Capítulo 1.

2. Clique na célula C4, efetue o cálculo =B4*E2 (ao clicar na célula E2, pressione a tecla de função F4 para fixar ($E$2)) e arraste para as demais.

Deixe a planilha como a figura 1.21.

Figura 1.21 – Planilha Excel.

Para criar o botão de rotação: 1. Clique na guia Desenvolvedor, grupo Controles, opção Inserir.

2. Em Controles de Formulário, clique em Botão de Rotação (Controle de Formulário).

3. Com o mouse pressionado, arraste o cursor na planilha.

4. Pressione o botão direito e clique na opção Formatar Controle.

5. Em Formatar Controle, clique na guia Controle.

6. Em Valor atual, deixe 0, pois a célula está vazia.

7. Em Valor mínimo, deixe 1, pois a contagem será iniciada a partir do número 1.

SENAC SÃO PAULO | 19

EXCEL 2016 – CRIANDO PLANILHAS INTERATIVAS E DASHBOARD

8. Em Valor máximo, deixe 10. Será a contagem máxima.

9. Em Alteração incremental, deixe o número 1, pois a contagem será de um em um (1, 2, 3, 4...).

10. Em Vínculo da célula, clique na célula E2. Para confirmar, dê um clique no botão OK.

Figura 1.22 – Configuração do botão de rotação.

Quando a seta for movimentada, o número na célula E2 será alterado e, consequentemente, os valores da coluna C serão recalculados.

Figura 1.23 – Resultado botão de rotação.

2.3 Criar e executar uma caixa de listagem A caixa de listagem é muito parecida com a caixa de combinação, porém enquanto na caixa de combinação os itens da lista não ficam visíveis, os da caixa de listagem ficam. A lista com itens visíveis depende da quantidade da lista e do tamanho da caixa de listagem. Quem define lista e tamanho da caixa é o usuário.

1. Abra o documento Planilhas Capítulo 1.

2. Deixe a planilha como a figura 1.24.

Figura 1.24 – Planilha Produtos Eletrônicos.

20 | SENAC SÃO PAULO

EXCEL 2016 – CRIANDO PLANILHAS INTERATIVAS E DASHBOARD

Para criar a caixa de listagem: 1. Clique na guia Desenvolvedor, grupo Controles, opção Inserir.

2. Em Controles de Formulário, clique em Caixa de Listagem (Controle de Formulário).

3. Com o mouse pressionado, arraste na planilha.

4. Pressione o botão direito e clique na opção Formatar Controle.

5. Em Formatar Controle, clique na guia Controle.

6. Em Intervalo de Entrada, selecione da célula A4 até a célula A7.

7. Em Vínculo da Célula, clique na célula D2. Para confirmar, dê um clique no botão OK.

Figura 1.25 – Janela Caixa de Listagem.

Tipo de seleção: • Simples: permite a seleção de apenas um item da lista.

• Múltipla: permite que vários itens sejam selecionados sequencialmente ou alternados.

• Estendida: permite que vários itens sejam selecionados com arraste do mouse.

A figura 1.26 apresenta a caixa de listagem com a seleção simples habilitada. Observe que o item Celular está selecionado e na célula D2 a posição é do número dois (2), já que Celular é o segundo item da lista na planilha.

Figura 1.26 – Janela com a caixa de listagem, seleção simples.

2.4 Criar e executar a barra de rolagem A barra de rolagem permite que o usuário crie uma sequência numérica sem a necessidade de digitá-la; ela é muito parecida com o botão de rotação.

SENAC SÃO PAULO | 21

EXCEL 2016 – CRIANDO PLANILHAS INTERATIVAS E DASHBOARD

1. Abra o documento Planilhas Capítulo 1.

2. Clique na célula C4, efetue o cálculo =B4*E2 (ao clicar na célula E2, pressione a tecla de função F4 para fixar ($E$2)) e arraste para as demais.

3. Deixe a planilha como a figura 1.27.

Figura 1.27 – Planilha calculada.

Para criar a barra de rolagem: 1. Clique na guia Desenvolvedor, grupo Controles, opção Inserir.

2. Em Controles de Formulário, clique em Barra de Rolagem (Controle de Formulário).

3. Com o mouse pressionado, arraste na planilha.

4. Pressione o botão direito e clique na opção Formatar Controle.

5. Em Formatar Controle, clique na guia Controle.

6. Em Valor atual, deixe 0, pois a barra de rolagem ainda não foi acionada.

7. Em Valor mínimo, deixe 1, pois a contagem será iniciada a partir do número 1.

8. Em Valor máximo, deixe 20. Será a contagem máxima.

9. Em Alteração incremental, deixe o número 1, pois a contagem será de um em um (1, 2, 3, 4...).

10. Em Mudança de página, mantenha o valor-padrão 10.

11. Em Vínculo da célula, clique na célula E2. Para confirmar, dê um clique no botão OK.

Figura 1.28 – Janela com a barra de rolagem.

À medida que a barra for deslizada, o valor da célula E2 é alterado e os valores da coluna C são recalculados. Faça o teste.

22 | SENAC SÃO PAULO

EXCEL 2016 – CRIANDO PLANILHAS INTERATIVAS E DASHBOARD

2.5 Criar a caixa de grupos A caixa de grupos nada mais é que um retângulo, mas que tem por função agrupar botões e controles; em algumas situações é necessário trabalhar com esta caixa.

Abra o documento Planilhas Capítulo 1.

Para criar a caixa de grupos: 1. Clique na guia Desenvolvedor, grupo Controles, opção Inserir.

2. Em Controles de Formulário, clique em Caixa de Grupos (Controle de Formulário).

3. Com o mouse pressionado, arraste na planilha.

4. Clique no rótulo e digite Grupo; dê um clique fora, o rótulo estará alterado. É interessante usar a caixa de grupos para agrupar botões, como botões de controle para macros ou botões de seleção ou opção.

Figura 1.29 – Caixa de grupo.

2.6 Criar a caixa de seleção Pela caixa de seleção é possível ativar ou desativar a opção desejada. Uma caixa de seleção obedece a três estados: ativada, desativada e seleção múltipla.

Abra o documento Planilhas Capítulo 1.

Para criar a caixa de seleção: 1. Clique na guia Desenvolvedor e crie uma Caixa de Grupos. Altere o rótulo para Caixa de Seleção.

2. Clique na guia Desenvolvedor, grupo Controles, opção Inserir.

3. Em Controles de Formulário, clique em Caixa de Seleção (Controle de Formulário).

4. Com o mouse, dê um clique dentro da Caixa de Grupos. Repita o procedimento três vezes.

Figura 1.30 – Caixa de seleção.

SENAC SÃO PAULO | 23

EXCEL 2016 – CRIANDO PLANILHAS INTERATIVAS E DASHBOARD

Para alinhar as caixas: 1. Pressione a tecla Ctrl e, com o mouse, dê um clique em cada uma das caixas.

2. Clique na guia Layout da Página, grupo Organizar.

3. Em Organizar, clique na opção Alinhar e em Alinhar à Esquerda (como os botões estão na vertical, então as opções de alinhamento são à esquerda ou à direita).

4. Para igualar os espaçamentos entre as caixas, clique na opção Alinhar e Distribuir na vertical.

5. Dê um clique na caixa de texto e digite o nome do rótulo, por exemplo: TV, Celular, Tablet e iPad. 6. Clique em Formatar, grupo Estilos de Forma e opção Contorno da Forma; defina os estilos de contorno e cor se desejar.

Figura 1.31 – Caixa de seleção alinhada.

Por enquanto essa caixa não está vinculada a nenhuma outra opção.

2.7 Criar o botão de opção O botão Opção é parecido com a caixa de seleção e serve para assinalar itens de formulários.

Abra o documento Planilhas Capítulo 1.

Para criar o botão de opção: 1. Clique na guia Desenvolvedor, crie uma caixa de grupos e altere o rótulo para Botão Opção.

2. Clique na guia Desenvolvedor, grupo Controles, opção Inserir.

3. Em Controles de Formulário, clique em Botão de Opção (Controle de Formulário).

4. Com o mouse, dê um clique dentro da caixa de grupos. Repita o procedimento três vezes.

Para alinhar as caixas: 1. Pressione a tecla Ctrl e, com o mouse, dê um clique em cada uma das caixas.

2. Clique na guia Layout da Página, grupo Organizar.

3. Em Organizar, clique na opção Alinhar e depois em Alinhar à Esquerda (como os botões estão na vertical, então as opções de alinhamento são à esquerda ou à direita).

4. Para alinhar os espaçamentos entre as caixas, clique na opção Alinhar e Distribuir na vertical.

5. Dê um clique na caixa de texto e digite o nome do rótulo, por exemplo: TV, Celular, Tablet e iPad.

24 | SENAC SÃO PAULO

EXCEL 2016 – CRIANDO PLANILHAS INTERATIVAS E DASHBOARD

3. Função REPT A função REPT é uma função de texto que age como uma condicional. Dependendo da quantidade de caracteres em uma determinada célula, ela repete símbolos ou texto, ou seja, qualquer tipo de caractere determinado.

Nessa planilha ela será calculada de acordo com as opções do Botão de Opção. Ao configurar o primeiro Botão de Opção automaticamente, todos os outros são vinculados à mesma célula. O primeiro botão corresponde ao número 1 e assim sucessivamente. Neste exemplo, eles foram renomeados em relação aos produtos. Observe a figura 1.32.

Para vincular os botões em relação à célula E2.

1. Clique com o botão direito sobre o botão renomeado como TV. 2. Clique na opção Formatar Controle.

3. Clique na guia Controle e, em Vínculo da Célula, indique a célula E2.

Ao realizar o vínculo com o primeiro botão, o vínculo com os outros é criado automaticamente.

Figura 1.32 – Botão de opção.

Ao clicar no botão de opção TV, aparece na célula E2 o número 1, pois TV é o primeiro item da lista, e assim para os demais.

Para calcular com a função REPT: Clique na célula C4, digite =REPT(“ l ”;$E$2), pressione a tecla Enter e arraste para as demais.

Entre aspas foi digitada a letra l em minúscula e a célula $E$2 é a que mantém o vínculo com os botões de opção. Ao alterar a seleção dos botões, o resultado na coluna C é alterado automaticamente em relação à numeração, o botão relacionado à TV equivale ao número 1, então o resultado será com um l e assim por diante.

Pode usar qualquer letra ou símbolo.

Observe a figura 1.33: na coluna C está a função REPT; na célula E2, o número 3 é referente à seleção.

Neste caso, o produto é Tablet, então o resultado são três símbolos; se fosse iPad seriam quatro, e assim por diante.

SENAC SÃO PAULO | 25

EXCEL 2016 – CRIANDO PLANILHAS INTERATIVAS E DASHBOARD

Figura 1.33 – Planilha calculada.

Mais adiante todas essas funcionalidades serão abordadas, porém com os recursos associados a outras funções e fórmulas. Os exemplos deste capítulo têm por finalidade apresentar a utilidade dos botões de controle.

EXERCÍCIO PROPOSTO Suponha que um usuário trabalha na área financeira de uma loja e precisa simular várias situações de crediário. Para simular com mais rapidez, ele irá utilizar a barra de rolagem para calcular os juros de acordo com o número de parcelas e o valor de cada parcela.

Digite a planilha abaixo e deixe com as formatações desejadas:

Figura 1.34 – Atividade Capítulo 1.

Crie uma barra de rolagem.

1. Em Formatar Controle, defina as seguintes situações:

1.1. Valor atual: 0.

1.2. Valor mínimo: 1, ou seja, este será o número mínimo de parcelas.

1.3. Valor máximo: 12, ou seja, este será o número máximo de parcelas.

1.4. Valor incremental: 1, assim ao deslizar a barra de rolagem a contagem será de um em um.

1.5. Vínculo da célula: célula A11.

26 | SENAC SÃO PAULO

EXCEL 2016 – CRIANDO PLANILHAS INTERATIVAS E DASHBOARD

2. Calcular os juros:

2.1. Clique na célula C11 e digite a fórmula para calcular os juros, que será a quantidade de parcelas em relação aos juros/mês.

3. Calcular a planilha:

3.1. Na célula C4 ficará o valor do primeiro produto em relação ao total de juros (fixar a célula C11); arraste para as demais.

3.2. Na célula D4: a soma do valor do primeiro produto com o valor dos juros.

3.3. Na célula E4: será o valor total com juros em relação ao número de parcelas (fixar a célula A11). À medida que a barra de rolagem for deslizada, os valores serão alterados.

SENAC SÃO PAULO | 27

2. Funções específicas

INTRODUÇÃO

P

ara criar os gráficos Dashboard é importante abordar algumas funções específicas, principalmente as funções condicionais, pois permitem trabalhar com planilhas de resumo.

Vale lembrar que o Excel possui dezenas de funções e no curso não é possível abordar todas, pois muitas são bastante específicas e outras já devem ser do conhecimento do usuário – uma vez que o objetivo do curso é trabalhar com Dashboard, ou seja, não é curso de Excel Avançado ou VBA para Excel.

FUNÇÕES Função é um conceito básico em matemática.

No Excel, é por meio dela que o usuário consegue chegar a algum resultado, envolvendo uma série de valores e operadores como adição, subtração, multiplicação e divisão, que são os itens mais básicos para se trabalhar com o Excel.

Ao trabalhar com funções e fórmulas é importante que o usuário tenha em mente que os valores são digitados em células e não dentro de uma função ou fórmula, pois o objetivo principal é otimizar o máximo possível as planilhas e, consequentemente, qualquer trabalho que envolva cálculos. Na figura 2.1, a função utilizada foi a média dos valores que estão na coluna C. Observe que na célula do resultado C8 a função visualizada é =Média(C4:C7), portanto não existe valor digitado, mas o intervalo de células correspondentes aos valores de cada produto. É dessa forma que tudo deve ser tratado no Excel.

SENAC SÃO PAULO | 29

EXCEL 2016 – CRIANDO PLANILHAS INTERATIVAS E DASHBOARD

Abra o documento Produtos Eletrônicos Capítulo 2; todas a planilhas deste capítulo estão digitadas e formatadas.

Figura 2. 1 – Função Média.

Uma fórmula é quando não existe uma função específica, como Soma, Média, PROCV, então é necessário saber matematicamente que tipo de cálculo utilizar, se multiplicação, divisão, subtração, etc.

Na figura 2.2 é apresentada uma fórmula a fim de calcular o reajuste de preços. Na célula D4, os valores não foram digitados, mas as células que representam o valor contido em cada uma delas. A célula A9 contém o valor do reajuste, então observe que, no cálculo, o reajuste de 10,50% é representado pela célula na qual ele está contido; o símbolo $ significa que é um valor absoluto e para o valor de uma célula tornar-se absoluto é necessário pressionar a tecla de função F4. Arraste para as demais células e o valor do reajuste será calculado na coluna toda. Ao alterar o valor da célula A9, todos os valores da coluna D serão recalculados.

Figura 2.2 – Fórmula para calcular o reajuste de preços.

NOME DA LISTA O que significa nome de lista e qual é sua funcionalidade? Ao selecionar um intervalo e definir um nome, o usuário poderá usar como pesquisa ou em funções para calcular um intervalo inteiro. Na figura 2.3, observe os números indicativos (1, 2 e 3): • 1 – Caixa de nome.

• 2 – Inserir função.

• 3 – Barra de fórmula.

30 | SENAC SÃO PAULO

EXCEL 2016 – CRIANDO PLANILHAS INTERATIVAS E DASHBOARD

Figura 2.3 – Algumas opções.

Para criar uma lista de nomes: 1. Abra o documento Produtos Eletrônicos Capítulo 2.

2. Clique na guia NomeLista.

3. Selecione o intervalo de A3 até C7.

4. Clique na caixa de nome.

5. Digite List1 e pressione a tecla Enter.

Clique em qualquer célula e, em seguida, dê um clique na seta para baixo na caixa de nome. Clique em List1 e observe que o intervalo foi selecionado. Não interessa quantas guias a planilha possui, em qualquer uma delas, ao clicar no nome definido como lista, o Excel procura o intervalo e o abre imediatamente.

Figura 2.4 – Seleção a partir da lista criada.

É possível utilizar a lista para resolver algum tipo de cálculo, ou seja, em vez das células, pode ser utilizada a lista de nome. Crie uma segunda lista com o intervalo de C4 até C7 e, na caixa de nome, digite List2 e use essa lista para calcular. Na figura 2.5, é possível visualizar como fica a função com a lista de nomes. Basta pressionar a tecla Enter e a somatória dos valores será calculada.

SENAC SÃO PAULO | 31

EXCEL 2016 – CRIANDO PLANILHAS INTERATIVAS E DASHBOARD

Figura 2.5 – Soma com intervalo de lista.

Quando uma lista for criada de forma incorreta por meio da caixa de nome, será necessário excluí-la.

O procedimento para exclusão é: 1. Clique na guia Fórmula, grupo Nomes Definidos.

2. Clique em Gerenciador de Nomes.

3. Clique no nome a ser excluído e no botão Excluir.

4. Confirme a exclusão e feche a janela.

VALIDAÇÃO DE DADOS A validação de dados é um recurso bastante interessante e muito utilizado. Pela validação, é possível definir restrições e criar a caixa de listagem, lista drop-down ou lista suspensa. O objetivo principal é impor condições para entrada de dados nas células de uma planilha. Por exemplo: em uma coluna de data, o Excel não permite a digitação de qualquer valor, apenas de data – isso pode ser resolvido pela validação de dados.

1. Abra o documento Produtos Eletrônicos Capítulo 2.

2. Selecione o intervalo de A4 até A7.

3. Clique na caixa de nome, digite prod e pressione a tecla Enter.

4. Repita o procedimento para os itens da coluna B e digite o nome tela.

Figura 2.6 – Lista de nomes.

32 | SENAC SÃO PAULO

EXCEL 2016 – CRIANDO PLANILHAS INTERATIVAS E DASHBOARD

Para definir as validações, insira uma nova guia no documento, digite e formate conforme a figura 2.7:

Figura 2.7 – Planilha formatada.

Para validar os dados na planilha, selecione o intervalo de A4 até A7.

1. Clique na guia Dados, grupo Ferramentas de Dados.

2. Clique na opção Validação de dados.

3. Clique na guia Configurações do painel.

4. Em Critério de validação, clique na opção Permitir.

5. Em Permitir (seta para baixo), clique na opção Lista.

6. Em Fonte, clique na caixa e digite =prod (prod é o nome da lista criada na caixa de nome).

7. Clique no botão OK para confirmar.

Ao clicar em qualquer célula da lista será visualizada a seta para baixo. Basta um clique e a lista irá surgir, conforme a figura 2.8. Repita o mesmo procedimento na coluna B.

Figura 2.8 – Coluna com lista.

Outras situações de validação: 1. Clique na guia Dados, grupo Ferramentas de Dados.

2. Clique na opção Validação de dados.

3. Clique na guia Configurações do painel.

4. Em Critério de validação, clique na opção Permitir.

5. Em Permitir (seta para baixo), clique na opção Decimal.

6. Em Dados deixe a opção Está entre.

SENAC SÃO PAULO | 33

EXCEL 2016 – CRIANDO PLANILHAS INTERATIVAS E DASHBOARD

7. Em Mínimo, digite 1 e, em Máximo, digite 100 (suponha que o preço unitário varia de R$ 1,00 a R$ 100,00).

8. Para confirmar, clique no botão OK. Basta testar os valores.

Se o valor for diferente do permitido, o Excel não deixará que os dados sejam digitados. No painel de validação, guia Alerta de Erro, caixa Mensagem de erro, digite a mensagem que irá aparecer quando uma informação não corresponder à restrição.

Figura 2.9 – Painel de validação.

FUNÇÃO REPT A função REPT é pouco conhecida, mas é uma função bastante fácil e serve para a repetição de caracteres a partir de um endereço determinado, de onde é realizada a contagem de números. Na coluna da função são criados minigráficos. Os caracteres de repetição podem ser símbolos, números, palavras ou qualquer caractere desejado. Para calcular usando a função REPT, abra o documento Produtos Eletrônicos Capítulo 2 e deixe como a figura 2.10:

Figura 2.10 – Planilha de resultado condicional.

Para calcular a função REPT: 1. Clique na célula E4 e na opção Inserir Função.

2. Em Inserir Função, digite o nome da função rept, selecione e confirme.

3. Em Texto, digite I (este é o caractere de repetição, poderia ser outro qualquer).

4. Em Número_vezes, clique na célula C4 (é a quantidade de vezes para a repetição).

34 | SENAC SÃO PAULO

EXCEL 2016 – CRIANDO PLANILHAS INTERATIVAS E DASHBOARD

Figura 2.11 – Janela com a função REPT.

5. Clique no botão OK para confirmar e arraste para as demais.

Observe o resultado da função REPT na coluna E, conforme a figura 2.12. À medida que os valores da coluna C são alterados, a quantidade de caracteres da coluna E também varia, o que acaba funcionando como um gráfico de resultados nas células.

Figura 2.12 – Resultado da função.

Refaça a função REPT usando outros tipos de caracteres em outras planilhas e situações.

FUNÇÃO SE A função condicional SE é uma das mais utilizadas no Excel, por isso a importância de sua abordagem. Ela é a responsável por cálculos mais complexos em que se utilizam vários critérios para chegar a um determinado resultado. Como resultado do critério definido, podem ser utilizados texto, número, fórmula, símbolos, entre outros. Observe a figura 2.13: a partir da linha 15 foram definidos os critérios e nas colunas E, F e G serão calculadas as condições.

Na coluna E, o critério será: se a quantidade da coluna C, célula C4, for maior ou igual a 100, o resultado será Ótimo, senão o resultado será Bom. Na coluna F, o critério será: se o valor da coluna D, célula D4, for maior ou igual a R$ 100.000, o resultado será D4+D4*$C$17, senão D4+D4*$C$18. As células C17 e C18 representam duas condições de reajuste sobre o total faturado.

Na coluna G, o critério será: se a quantidade da coluna C, célula C4, for maior ou igual a 100, o resultado será o símbolo que está na célula D16; se a quantidade da coluna C, célula C4, for maior ou igual a 50, o resultado será o símbolo que está na célula D17, senão o resultado será o símbolo que está na célula D18. Sempre que usar uma célula de valor absoluto é necessário fixá-la, pela tecla de função F4.

SENAC SÃO PAULO | 35

EXCEL 2016 – CRIANDO PLANILHAS INTERATIVAS E DASHBOARD

Figura 2.13 – Planilha utilizando a função SE.

Função SE cujo resultado é um texto: Na figura 2.14, é possível visualizar o resultado, ou seja, a fórmula. Neste caso, foram utilizadas duas condições.

Fórmula: =SE(C4>=$A$16;$B$16;$B$17).

Ao pressionar a tecla Enter, arraste para as demais células posicionadas abaixo, ou seja, a partir da célula com a fórmula, e observe o resultado.

Figura 2.14 – Função SE, coluna E.

Função SE cujo resultado é uma fórmula: Na figura 2.15, é possível visualizar o resultado, ou seja, a fórmula. Neste caso, foram utilizadas duas condições.

Fórmula: =SE(D4>=$C$16;D4+D4*$C$17;D4+D4*$C$18).

Ao pressionar a tecla Enter, arraste para as demais células posicionadas abaixo, ou seja, a partir da célula com a fórmula, e observe o resultado.

Figura 2.15 – Função SE com fórmula.

36 | SENAC SÃO PAULO

EXCEL 2016 – CRIANDO PLANILHAS INTERATIVAS E DASHBOARD

Função SE cujo resultado é um símbolo: Na Figura 2.16, é possível visualizar o resultado, ou seja, a fórmula. Neste caso, foram utilizadas três condições.

Fórmula: =SE(C4>=$A$16;$D$16;SE(C4>=$A$17;$D$17;$D$18)).

Ao pressionar a tecla Enter, arraste para as demais células posicionadas abaixo, ou seja, a partir da célula com a fórmula, e observe o resultado.

Figura 2.16 – Função SE com símbolo.

Quando o resultado for um símbolo, é necessário formatar com a fonte correspondente. No exemplo, a fonte de formatação é Wingdings. O símbolo foi inserido na célula com a referência absoluta. Para inserir o símbolo clique na guia Inserir, opção Símbolo.

Função SE com controles: Digite os dados da planilha conforme a figura 2.17. Use os endereços como apresentados nas respectivas células.

Figura 2.17 – Função SE com controles.

Após a digitação e formatação, insira os seguintes controles: Botão de rotação: quantidade.

1. Clique na guia Desenvolvedor, grupo Controles, opção Botão de Rotação.

2. Posicione no espaço da coluna D. 3. Com o botão direito do mouse, clique na opção Formatar controle e vá para a guia Controle.

SENAC SÃO PAULO | 37

EXCEL 2016 – CRIANDO PLANILHAS INTERATIVAS E DASHBOARD

4. Valor atual: 1.

5. Valor mínimo: 1.

6. Valor máximo: 5.

7. Alteração incremental: 1.

8. Vínculo da célula: E4. Clique no botão OK para confirmar.

Caixa de Listagem: posição do produto na lista.

1. Clique na guia Desenvolvedor, grupo Controles, Inserir, opção Caixa de Listagem.

2. Posicione a caixa abaixo da planilha.

3. Com o botão direito do mouse, clique na opção Formatar controle, na guia Controle.

4. Intervalo de entrada: selecione o intervalo de A2 até A5.

5. Vínculo da célula: E2. Clique no botão OK para confirmar.

Calcular coluna C. 1. A fórmula da coluna C será =B2*$E$4 (fixar a célula com a quantidade). Arraste para as demais.

2. A fórmula da célula B8 será:

=SE(E2=1;B2*$A$11;SE(E2=2;B3*$A$12;SE(E2=3;B4*$A$13;B5*$A$14)))

A figura 2.18 apresenta a planilha finalizada com os devidos resultados. À medida que o botão de rotação é deslizado e o produto da caixa de listagem é alterado, os resultados da coluna C e da célula B8 são alterados automaticamente. Os valores do reajuste estão nas células A11, A12, A13 e A14, respectivamente 1.1, 1.2, 1.5 e 1.8.

Figura 2.18 – Planilha calculada.

FUNÇÃO SOMASE A função SOMASE é uma função condicional, isto é, soma uma lista de itens, porém é uma soma com condição: dos vários produtos de uma lista, ela irá procurar determinado produto e irá somar somente ele. SOMASE nada mais é que uma soma por grupo. A sintaxe é: intervalo (a lista que contém os produtos desejados), critério (o nome do produto desejado) e intervalo da soma (a coluna que contém os valores que serão totalizados). A figura 2.19 apresenta uma pequena planilha ao lado com o nome Resultado, na qual será calculada a SOMASE.

38 | SENAC SÃO PAULO

EXCEL 2016 – CRIANDO PLANILHAS INTERATIVAS E DASHBOARD

Figura 2.19 – Planilha função SOMASE.

Calcular SOMASE: 1. Clique na célula H4.

2. Clique em Inserir Função (fx) e procure a função SOMASE. 3. Em Intervalo, selecione os dados da coluna A, pois correspondem aos critérios estabelecidos.

Fixe o intervalo.

5. Em Critérios, selecione o primeiro critério da lista, célula G4.

6. Em Intervalo_soma, selecione a coluna E, valor total. Fixe o intervalo.

Clique no botão OK para confirmar e arraste para as demais.

Figura 2.20 – Janela função SOMASE.

Os dados da planilha são inseridos de forma aleatória ao usar a SOMASE. Como no exemplo, os critérios são separados, dessa forma é gerado um pequeno relatório com os totais agrupados. Observe a figura 2.21, o total da coluna H precisa ser igual ao da coluna E, pois comprova que o resultado está correto. Altere algum valor da planilha de dados e observe que o valor correspondente ao produto na tabela Resultado é alterado imediatamente.

Figura 2.21 – SOMASE.

SENAC SÃO PAULO | 39

EXCEL 2016 – CRIANDO PLANILHAS INTERATIVAS E DASHBOARD

SOMASE com validação: 1. Clique na célula G11 e crie uma lista de dados utilizando a Validação de dados (lista com os produtos de G4 a G7).

2. Na célula H11, calcule utilizando a função SOMASE. À medida que o produto é alterado pela lista de validação, o cálculo é realizado de forma automática. A sintaxe é:

=SOMASE($A$4:$A$12;G11;$E$4:$E$12).

Figura 2.22 – Função SOMASE por meio de validação de dados.

FUNÇÃO SOMASES Pela função SOMASES, é possível calcular usando várias condições. Assim como a SOMASE a funcionalidade é calcular dados acumulados; mas, enquanto a SOMASE trabalha com apenas um grupo de critérios, a função SOMASES permite o cálculo com vários critérios. Observação: esta função será bastante utilizada para a criação dos gráficos Dashboard.

1. Abra o documento Produtos Eletrônicos Capítulo 2, guia SomaSES. A figura 2.23 apresenta as seguintes situações: Fatura por Produto e Resultado. Em Resultado, será aplicada a função SOMASES na coluna J a partir da célula J4, então a situação será: o total de vendas por vendedor e por produto em relação à coluna F, Valor Total.

2. Clique na célula J4, depois em Inserir Função (fx) e procure por SOMASES.

Figura 2.23 – Planilha SOMASES.

Ao clicar na função, a janela com os campos é aberta com as seguintes opções: • Intervalo_soma: é em relação à coluna com os valores que serão calculados. No exemplo será a coluna F da planilha-base.

40 | SENAC SÃO PAULO

EXCEL 2016 – CRIANDO PLANILHAS INTERATIVAS E DASHBOARD

• Intervalo_critérios1: o primeiro critério da planilha Resultado é em relação aos produtos, então a coluna da planilha-base é a coluna A. • Critérios1: é a coluna de produtos em relação à planilha Resultado, basta clicar na primeira célula, H4.

• Intervalo_critérios2: o segundo critério da planilha Resultado é em relação aos vendedores, então a coluna na planilha-base é a coluna B. • Critérios2: é a coluna dos vendedores em relação à planilha Resultado, basta clicar na primeira célula, I4.

Ao usar a função SOMASES é importante que, ao selecionar os dados na planilha-base – por exemplo, Fatura por Produto –, os intervalos sejam fixados por meio da tecla de função F4.

Figura 2.24 – Janela SOMASES.

Após calcular, arraste para as demais células posicionadas abaixo, ou seja, a partir da célula com a fórmula. A figura 2.25 mostra os dados calculados, isto é, quanto cada vendedor faturou por produto, por exemplo, quanto o Joaquim faturou vendendo TVs. A sintaxe é: =SOMASES($F$4:$F$12;$A$4:$A$12;H4;$B$4:$B$12;I4).

Figura 2.25 – Planilha calculada.

FUNÇÃO SEERRO A função SEERRO pode ser utilizada com qualquer outra função ou fórmula. Com frequência a planilha é calculada sem os valores específicos e as células ficam como se houvesse algum erro – com esta função, esse erro é eliminado. Na figura 2.26, a partir da célula D4 aparece o erro, porque na coluna C a quantidade de parcelas não foi preenchida.

SENAC SÃO PAULO | 41

EXCEL 2016 – CRIANDO PLANILHAS INTERATIVAS E DASHBOARD

Figura 2.26 – Fórmula com erro.

Para que esse problema seja eliminado, a função SEERRO é usada com a fórmula. No exemplo da figura 2.27, foi calculado preço unitário dividido pelo número de parcelas =B4/C4; com a SEERRO ficará da seguinte forma: =SEERRO(B4/C4;0), ou seja, quando a célula estiver sem valor retornará a zero e a aparência de erro não será visualizada.

Observe a figura 2.27. Nas células com a quantidade de parcelas, o cálculo foi efetuado corretamente; nas células sem a quantidade de parcelas, retornou ao valor zero (0).

Figura 2.27 – Planilha com a função SEERRO.

FUNÇÃO PROCV A função PROCV é uma das mais utilizadas no Excel. Ela tem como funcionalidade procurar determinada informação em algum lugar de uma base de dados em outra planilha ou em outro documento. É bem útil quando existe uma base de dados grande, pois evita que os mesmos dados sejam digitados sem necessidade, bastando manter uma base e simplesmente buscar a informação desejada. PROC = procura e V = vertical, ou seja, ela procura determinado tipo de valor em uma planilha, porém é necessário que ele esteja disposto verticalmente.

Existem diversas maneiras de utilizar a função PROCV; aqui será apresentado um exemplo, mas é importante explorar bastante os recursos que ela tem.

42 | SENAC SÃO PAULO

EXCEL 2016 – CRIANDO PLANILHAS INTERATIVAS E DASHBOARD

Para que o funcionamento não dê erro na procura, é importante que a coluna-base ou a coluna com o critério de busca fique classificada (ordem alfabética ou numérica) e que não haja repetição de valores.

No exemplo da figura 2.28, a coluna A contém os critérios de busca. Outra observação importante é que o PROCV procura por número de coluna, então a primeira coluna da base de dados representa a coluna 1. Na figura 2.28, a primeira é a coluna Produtos eletrônicos, que por acaso é a coluna A, mas para o PROCV é a coluna 1.

Figura 2.28 – Dados organizados para a utilização do PROCV.

Crie uma lista de validação de dados na coluna E, conforme a figura 2.29. O PROCV será calculado a partir da coluna F, cujo título é Tela. A sintaxe da função: =PROCV($E4;$A:$C;2). O Excel faz a seguinte leitura: $E4 é o valor que será procurado na base de dados, $A:$C é a base de dados onde os valores serão procurados e 2 é a coluna que apresenta o resultado do valor procurado. Quando na coluna E o valor de entrada for TV, o PROCV vai até a base de dados e traz o valor que está na coluna 2, neste caso, polegadas por tela.

Figura 2.29 – Planilha para calcular PROCV.

A figura 2.30 apresenta a função já calculada. As colunas que representam a base de dados foram fixadas, pois, neste exemplo, a função será arrastada para as demais colunas. Outro ponto importante é o valor de retorno da coluna F. O número 2 representa a segunda coluna da base de dados e a coluna G foi alterada para o número 3, pois o preço do produto está na terceira coluna, e assim por diante.

Figura 2.30 – Função PROCV.

SENAC SÃO PAULO | 43

EXCEL 2016 – CRIANDO PLANILHAS INTERATIVAS E DASHBOARD

A figura 2.31 exibe a planilha calculada. Ao entrar com um produto da lista na coluna E, automaticamente os dados das colunas F e G são preenchidos. Na coluna H, insira a quantidade, e na coluna I, multiplique o preço unitário pela quantidade.

Figura 2.31 – Planilha calculada.

Outra informação importante é que as linhas que ainda não foram preenchidas aparecem na célula #N/D como se fossem erro, então ao PROCV será adicionada a função SEERRO. Exemplo: =SEERRO(PROCV($E4;$A:$C;2);0).

FUNÇÃO PROCH A PROCH também é uma função de pesquisa. A lógica é a mesma da PROCV, só que esta realiza a procura na vertical, enquanto a PROCH realiza na horizontal. A figura 2.32 apresenta a base de dados organizada de forma diferente, disposta na horizontal. Para a PROCH, a linha com os nomes dos produtos é a 1, pois é a primeira da lista, a próxima é a 2, segunda, e assim sucessivamente.

Figura 2.32 – Dados PROCH.

A sintaxe da PROCH é muito parecida com a da PROCV, como mencionado. No exemplo, já está com a SEERRO: =SEERRO(PROCH($A4;$E$1:$H$3;3);0). O zero no final significa que se a coluna não for preenchida, no lugar do erro será adicionado o zero.

Figura 2.33 – Planilha calculada com PROCH.

44 | SENAC SÃO PAULO

EXCEL 2016 – CRIANDO PLANILHAS INTERATIVAS E DASHBOARD

FUNÇÃO CORRESP A função CORRESP tem por finalidade buscar a posição de uma determinada informação a partir de uma lista. Na figura 2.34, a lista de dados está na coluna A. A célula C2 foi validada com os itens da coluna A. Na célula D2 está a função CORRESP, assim, quando o valor da célula D2 for alterado, a posição do produto também será alterada na célula D2.

Figura 2.34 – Função CORRESP.

FUNÇÃO ÍNDICE A função ÍNDICE tem por objetivo buscar uma informação em um intervalo de dados e localizar em que linha e coluna essa informação está posicionada. Na figura 2.35, é possível visualizar as seguintes informações: • Intervalo de A3 até E6 da base de dados.

• Célula G3 corresponde ao número da linha da base de dados.

• Célula H3 corresponde ao número da coluna da base de dados.

• Célula H5 corresponde ao item relacionado à linha e à coluna.

• iPad está localizado na linha 2 e na coluna 1.

Ao alterar os números de linha ou coluna o valor da célula (H5) também será alterado.

Figura 2.35 – Planilha ÍNDICE.

Para utilizar a função ÍNDICE é preciso primeiro saber o resultado desejado e criar a planilha de acordo.

1. Clique na célula H5 e depois na opção Inserir Função (fx).

2. Procure a função ÍNDICE e pressione OK para confirmar.

3. Matriz: intervalo de dados.

4. Núm_linha: é a linha em relação à posição do item dentro do intervalo.

5. Núm_coluna: é a coluna em relação à posição do item dentro do intervalo.

SENAC SÃO PAULO | 45

EXCEL 2016 – CRIANDO PLANILHAS INTERATIVAS E DASHBOARD

Figura 2.36 – Janela com a função ÍNDICE.

A figura 2.37 possui algumas situações de cálculos a partir da função ÍNDICE. 1. Na célula G3, use a validação de dados (lista de A3 até A6).

2. Na célula H3, use a função CORRESP: =CORRESP(G3;A3:A6). Ao entrar com o valor na célula G3, irá surgir a posição no intervalo de dados de A3 até A6.

3. Célula A9: =G3.

4. Célula B9: =ÍNDICE($A$3:$E$6;$H$3;2); arraste para as demais colunas. O número 2 é equivalente à coluna; ao arrastar para as demais colunas, altera-se o número da coluna de 2 para 3 e assim sucessivamente, como acontece nas funções PROCV e PROCH.

Figura 2.37 – Índice com cálculos.

A figura 2.38 apresenta a planilha calculada.

• Exemplo 1: na célula G3 está Celular; na célula H3, a posição da linha. Na linha 9, aparecem os dados correspondentes a Celular.

• Exemplo 2: na célula G3 está Tablet; na célula H3, a posição da linha. Na linha 9, aparecem os dados correspondentes a Tablet. A sintaxe é: =ÍNDICE($A$3:$E$6;$H$3;2).

46 | SENAC SÃO PAULO

EXCEL 2016 – CRIANDO PLANILHAS INTERATIVAS E DASHBOARD

Figura 2.38 – Exemplos de ÍNDICE.

FUNÇÃO ESCOLHER A função ESCOLHER trabalha com índice e critérios. É muito simples, porém será bastante utilizada para a criação de gráficos Dashboard.

A figura 2.39 apresenta nas colunas A e B a base de dados. A célula D2 apresenta o índice, ou seja, cada número da lista está associado a um dos produtos (célula D2). Foi criada a lista com validação de dados. A função ESCOLHER está na célula E2.

Figura 2.39 – Planilha função ESCOLHER.

A figura 2.40 apresenta a função ESCOLHER e sua utilidade.

• A célula E2 contém a função.

• A célula D2 é o índice em números associando cada um dos produtos.

• As células G2, G3, G4 e G5 são a lista dos produtos na ordem que os números são visualizados na coluna G. O número 1 representa Celular, e assim por diante.

Ao alterar o número por meio da lista validada, o produto é alterado na célula E2. Após calcular, oculte a coluna G.

SENAC SÃO PAULO | 47

EXCEL 2016 – CRIANDO PLANILHAS INTERATIVAS E DASHBOARD

Figura 2.40 – Função ESCOLHER.

Clique na célula E5 e digite a seguinte função: =SE(E2=“tablet”;SOMASE($A$2:$A$11;E2;$B$2:$B$11);SE(E2=“ipad”;MÉDIASE($A$2:$A$11;E2;$B $2:$B$11);SE(E2=“celular”;G2;“”))).

Se o número escolhido for 1, o resultado será celular, pois é o primeiro da lista; se for 2, o resultado será a média somente da quantidade de iPads; se for 3, o resultado será a soma referente à quantidade de tablets; se for 4, resultará em nada. No exemplo da figura 2.41, a posição 3 é igual a Tablet; de acordo com a função, quando for Tablet, será uma soma. Este é o resultado.

Figura 2.41 – Funções: SE; SOMASE; MÉDIASE.

FUNÇÃO MÉDIASE A lógica da função MÉDIASE é a mesma da função SOMASE, só que, em vez de efetuar uma soma com condição, será calculada uma média com condição. Observe a figura 2.42: a base de dados está nas colunas A e B, e a planilha de resumo está no intervalo de D2 até E5. Para calcular: 1. Clique na aba MédiaSe. 2. Crie uma caixa de combinação e configure-a para:

2.1 Intervalo de entrada: selecione de J2 até J5.

2.2 Vínculo da célula: clique na célula F1.

3. Clique na célula D2 e digite a sintaxe =SE(F1=1;$J$2;SE(F1=2;$J$3;SE(F1=3;$J$4;$J$5))). Se o valor de F1 for igual a 1, o resultado será o valor da célula J2; se o valor da célula F1 for igual a 2, o resultado será o valor da célula J3; se o valor da célula F1 for igual a 3, o resultado será o valor da célula J4; senão o resultado será o valor da célula J5.

48 | SENAC SÃO PAULO

EXCEL 2016 – CRIANDO PLANILHAS INTERATIVAS E DASHBOARD

Figura 2.42 – Planilha MédiaSe.

4. Clique na célula E2 e digite a sintaxe =MÉDIASE($A$2:$A$11;D2;$B$2:$B$11).

Função MÉDIASE: o intervalo de A2 até A11 corresponde aos critérios pelos quais será calculada a condição da média, na célula D2; a média será calculada a partir do intervalo de B2 até B11. Ao alterar o valor na caixa de combinação, o valor da célula D2 é alterado em relação ao critério, e o valor da média também é calculado de acordo com os dados da planilha. Observe a figura 2.43. Oculte a coluna J.

Figura 2.43 – SOMASE calculada.

FUNÇÃO MÉDIASES A função MÉDIASES também é parecida com a função SOMASES. Para calcular a média será necessária a comparação entre dois critérios, neste caso, produto e vendedor, conforme mostra a figura 2.44.

1. Clique na aba MédiaSES. 2. Selecione o intervalo de I4 até I7 e crie uma lista de validação de dados. Os critérios de validação estão no intervalo de A15 a A18.

3. Após validar, clique na célula J4 e digite a sintaxe: =SEERRO(MÉDIASES($F$4:$F$12;$A$4:$A$12;H4;$B$4:$B$12;I4);0).

Figura 2.44 – Planilha MédiaSes.

SENAC SÃO PAULO | 49

EXCEL 2016 – CRIANDO PLANILHAS INTERATIVAS E DASHBOARD

A função SEERRO será usada porque se determinado vendedor não vendeu um produto é para aparecer zero (0). Na sequência: MÉDIASES, intervalo de F4 até F12 corresponde aos valores de cálculo de média; o intervalo de A4 até A12 corresponde aos critérios em relação aos produtos; a célula H4 é o primeiro critério da lista; o intervalo de B4 até B12 corresponde aos vendedores; e a célula I4 é o critério que corresponde ao vendedor. Ao terminar, pressione a tecla Enter e arraste para as demais células. Altere o valor da lista de validação e observe que a média será recalculada. Na figura 2.45, observe que o vendedor Joaquim não vendeu nenhum celular. O restante é a média por produto em relação ao vendedor. =SEERRO(MÉDIASES($F$4:$F$12;$A$4:$A$12;H4;$B$4:$B$12;I4);0).

Figura 2.45 – Planilha calculada.

TABELA DINÂMICA A tabela dinâmica é um recurso muito utilizado quando a base de dados é muito grande. Ela trabalha com dados agrupados utilizando algumas funções, dentre elas SOMA, MÉDIA, entre outras. Por meio dela, é possível criar filtros e distribuir as informações da forma desejada. A base de dados ou matriz é uma planilha estática, no entanto, ao criar a tabela dinâmica da planilha, os dados ficam totalmente otimizados. O documento que será usado para a criação da tabela dinâmica possui uma única planilha: Dados.

1. Abra o documento Tabela Dinâmica Capítulo_2.

2. Clique em qualquer célula dentro da planilha.

3. Clique na guia Inserir, grupo Tabelas, opção Tabela Dinâmica.

Observe a figura 2.46: ao clicar na opção Tabela Dinâmica, o Excel seleciona toda a base de dados.

As opções do painel são: • Tabela/Intervalo: é o intervalo de toda a base (deixe como está).

• Nova Planilha: deixe como está e a Tabela Dinâmica será criada em outra aba. É melhor, pois a base de dados fica livre e é nela que novos dados serão inseridos e atualizados na tabela dinâmica.

• Clique no botão OK para confirmar.

50 | SENAC SÃO PAULO

EXCEL 2016 – CRIANDO PLANILHAS INTERATIVAS E DASHBOARD

Figura 2.46 – Janela Criar Tabela Dinâmica.

• Altere o nome da nova planilha para Tabela Dinâmica.

Observe que a planilha da figura 2.47 possui duas abas: Dados e Tabela Dinâmica. A planilha Dados é a matriz, onde novos dados serão inseridos, corrigidos e alterados; já a Tabela Dinâmica recebe esses dados e os atualiza. O painel à direita possui todos os campos da matriz, ou seja, os rótulos das colunas. O painel à esquerda é onde os campos serão inseridos para que possa gerar a tabela dinâmica. Antes de iniciar, altere a configuração do painel da tabela, para que os campos possam ser melhor visualizados.

1. Posicione o mouse sobre a tabela dinâmica e pressione o botão direito.

2. Clique em Opções da Tabela Dinâmica.

3. Clique em Exibição e assinale a opção Layout clássico de tabela dinâmica, que habilita as opções de arrastar os campos na grade. Clique no botão OK para confirmar.

Figura 2.47 – Painel Campos da Tabela Dinâmica.

Ao alterar a configuração da tabela dinâmica, os campos ficam com uma visualização melhor. Observe a figura 2.48. Onde está escrito Solte os campos de linha aqui, as informações ficam dispostas na horizontal; onde está escrito Solte os campos de coluna aqui, as informações ficam dispostas na vertical; onde está escrito Soltar campos de valor aqui, os valores são calculados; onde está escrito Soltar campos de filtros de relatório aqui são campos que ajudam na filtragem.

SENAC SÃO PAULO | 51

EXCEL 2016 – CRIANDO PLANILHAS INTERATIVAS E DASHBOARD

Figura 2. 48 – Tabela dinâmica: campos configurados.

Para criar a tabela dinâmica, basta arrastar os campos do painel à direita e soltar no local desejado.

Na figura 2.49, Vendedor ficou posicionado como linha; Produtos Eletrônicos ficou como coluna; Valor Total foi arrastado para o meio da tabela; e Tela ficou como campo de pesquisa.

Pela tabela dinâmica é possível analisar com mais precisão todas as informações da planilha-base ou matriz. Observe na figura 2.49 que o vendedor Francisco não vendeu celular, nem iPad, nem TV; vendeu somente tablet, já com o valor total. Onde as células estão vazias significa que não existe relação entre a informação disposta e a linha e a coluna.

Onde aparecem as setinhas, é possível realizar a filtragem, como analisar determinado vendedor ou produto e ainda o produto e o tamanho da tela.

Figura 2.49 – Tabela Dinâmica.

Desative a tabela dinâmica e construa de outra forma, desmarcando os campos no painel à direita.

Na figura 2.50, os campos foram todos alterados, então as informações podem ser analisadas de forma diferente, ou seja, é por causa dessa flexibilidade que a tabela dinâmica é tão útil e usada em diversas situações e análises de dados.

Figura 2.50 – Nova disposição.

52 | SENAC SÃO PAULO

EXCEL 2016 – CRIANDO PLANILHAS INTERATIVAS E DASHBOARD

Os novos dados devem ser inseridos na planilha Dados e posteriormente atualizados na tabela dinâmica.

1. Clique na planilha de dados e insira uma nova linha no meio, em qualquer posição.

2. Na coluna Produtos eletrônicos, digite Câmera Digital.

3. Em Vendedor, digite Ana. 4. Em Tela, digite 14 Pol e valor R$ 1.600,00.

5. Em Quantidade, digite 10.

6. Retorne para a planilha Tabela Dinâmica e atualize os dados da seguinte forma:

6.1. Clique com o botão direito sobre a tabela dinâmica e depois na opção Atualizar, ou clique na guia Analisar e depois na opção Atualizar.

Observe na figura 2.51 que o novo produto inserido na base de dados já está atualizado com todas as informações referentes a ele.

Figura 2.51 – Dados atualizados.

A figura 2.52 apresenta as vendas de apenas um vendedor, Francisco, com o produto vendido por ele, o faturamento total e a quantidade total de vendas do produto, ou seja, Francisco vendeu apenas um tipo de produto.

Figura 2.52 – Tabela Dinâmica com filtro.

A partir da tabela dinâmica pode ser criado o gráfico dinâmico. Se quiser que ele fique em uma guia, pressione a tecla F11, senão clique dentro da tabela dinâmica e na guia Inserir, grupo Gráficos, opção Gráfico tipo Colunas 2D. Exemplo 1: o gráfico dinâmico na figura 2.53 apresenta as vendas de Francisco. Observe que no gráfico também existem as opções de filtros. Ao filtrar os dados pelo gráfico, a tabela dinâmica assume a filtragem, ou seja, os filtros podem ser executados por meio do gráfico ou da tabela.

SENAC SÃO PAULO | 53

EXCEL 2016 – CRIANDO PLANILHAS INTERATIVAS E DASHBOARD

Figura 2.53 – Gráfico dinâmico.

Exemplo 2: a figura 2.54 apresenta as vendas de todos os vendedores, porém de apenas um produto.

Ao alterar a tabela, o gráfico é atualizado imediatamente e, se a alteração de filtro for no gráfico, a tabela também é atualizada imediatamente.

Figura 2.54 – Gráfico dinâmico.

EXERCÍCIOS PROPOSTOS Exercício 1 Determinado usuário é gerente de uma loja de material de construção e precisa criar um relatório com todas as informações em relação aos dados cadastrados.

Para criar o relatório sugerido, digite a planilha abaixo: Material de Construção Quantidade de Peças

Produtos

Loja 1

Loja 2

Loja 3

Vendedor

Azulejo

500

490

510

Ana

Chuveiro

600

1.100

610

Pedro

Gabinete

280

300

380

Eva

Pia

350

390

1.000

Joana

Revestimento

530

500

520

Diego

1.200

600

680

Henrique

Torneira

54 | SENAC SÃO PAULO

EXCEL 2016 – CRIANDO PLANILHAS INTERATIVAS E DASHBOARD

1. Salve o documento com o nome Material de Construção.

2. Altere o nome da guia para Cadastro.

3. Insira uma nova guia Plan e altere o nome para Consulta.

4. Clique na célula B1 e digite Material de Construção.

5. Selecione o intervalo de B1 a E1 e use a formatação de mesclagem.

6. Clique na célula G1 e digite a função =agora() (data atualizada).

7. Selecione o intervalo de G1 a J1 e use a formatação de mesclagem.

8. Selecione o intervalo de B4 a J16.

9. Adicione borda externa e preenchimento cinza-claro.

10. Clique na célula C6 e digite Produto; na célula C8, digite Quantidade; na célula C10, digite Situação Vendas.

11. Clique na célula C13 e digite Produto – Vendedor.

12. Selecione o intervalo de C13 a D13 e use a formatação de mesclagem.

13. Insira uma caixa de combinação e posicione-a na célula D6.

14. Clique com o botão direito na caixa de combinação e configure para:

14.1. Controle: em Intervalo de entrada, selecione a lista de produtos da planilha Cadastro.

14.2. Vínculo de célula: clique na célula I7 e em Lista suspensa, digite o número 6. Clique em OK.

15. Clique na célula D8 e entre com a função ÍNDICE: =ÍNDICE(Cadastro!B5:D10;I7;I9). Na sintaxe, Cadastro é a planilha com os dados; de B5 a D10 é o intervalo equivalente à quantidade de produtos; I7 equivale ao número da linha (posição do produto no cadastro); I9 é a coluna, posição do produto por coluna.

16. Clique na célula D10 e entre com a função SE: =SE(D8>=1000;”Meta Atingida”;”Não Atingida”).

17. Clique na célula C14 e crie uma lista de Validação de dados, que é a lista dos produtos da planilha Cadastro.

18. Clique na célula D14 e entre com a função PROCV: =PROCV(C14;Cadastro!$A$5:$E$10;5).

19. Clique na célula I6 e digite Posição Linha.

20. Clique na célula I8 e digite Posição Coluna.

21. Insira uma caixa de grupo e, no rótulo, digite Lojas.

22. Insira três botões dentro da caixa de grupo Botão de Opção.

22.1. Altere o nome do primeiro para Loja 1, do segundo para Loja 2 e do terceiro para Loja 3.

22.2. Clique com o botão direito em cada botão; em Controle, na opção Vínculo da célula, clique na célula I9 (posição da coluna referente a cada uma das lojas).

Depois de pronto e calculado, o formulário deverá ficar como a figura 2.55: Gabinete está na linha 3; Loja 1 está na coluna 1; 280 é a quantidade de gabinetes em relação à Loja 1; Meta não Atingida significa que a quantidade de vendas foi inferior a 1.000; Eva é a responsável pelas vendas de gabinetes.

SENAC SÃO PAULO | 55

EXCEL 2016 – CRIANDO PLANILHAS INTERATIVAS E DASHBOARD

Figura 2.55 – Formulário.

Exercício 2 No relatório da figura 2.56, o usuário precisa encontrar a meta em relação às vendas. A condição será: se a soma referente às vendas das lojas 1, 2 e 3 for maior ou igual a 1.000, a meta foi atingida; caso contrário, a meta não foi atingida.

Figura 2.56 – Atividade condição.

1. Clique na célula E2 e digite a sintaxe a seguir: =SE(SOMA(B5:D5)>=$A$13;$B$12;$B$13). Arraste para as demais.

2. Observe a figura 2.57 e veja como a fórmula ficou.

Figura 2.57 – Exercício resolvido.

56 | SENAC SÃO PAULO

EXCEL 2016 – CRIANDO PLANILHAS INTERATIVAS E DASHBOARD

Exercício 3 – Tabela Dinâmica Um gestor precisa criar um relatório resumido e totalizado conforme os dados, como na figura 2.58, e para isso irá criar uma tabela dinâmica.

Figura 2.58 – Exercício proposto.

O resultado deverá ficar como a figura 2.59:

Figura 2.59 – Exercício proposto: tabela dinâmica e gráfico dinâmico.

SENAC SÃO PAULO | 57

EXCEL 2016 – CRIANDO PLANILHAS INTERATIVAS E DASHBOARD

Exercício 4 – Função SOMASE O gestor precisa criar um relatório rápido totalizando a quantidade de produtos por loja. Para isso, irá utilizar a função SOMASE.

Figura 2.60 – Exercício proposto.

Relatório resolvido: • Clique na célula G5, digite a função =SOMASE($B$5:$B$22;F5;$C$5:$C$22), pressione a tecla Enter e arraste para as demais.

Figura 2.61 – Exercício resolvido: função SOMASE.

58 | SENAC SÃO PAULO

3. Criação de gráficos

INTRODUÇÃO

G

ráfico é um recurso visual e dinâmico que demonstra a real importância dos dados em uma planilha. Um gráfico é uma representação por meio de objetos lúdicos, formas e imagens.

Os gráficos apresentam de forma sintética uma leitura rápida dos dados de uma planilha, portanto são muito utilizados para análises de finanças, estatísticas ou planejamento em qualquer setor.

Existem diversos tipos de gráficos que serão abordados neste capítulo, como: coluna, barra, linha, pizza, entre outros.

CRIAÇÃO DE GRÁFICOS O segredo da criação de gráficos é a seleção.

Primeiro, é necessário analisar os dados, depois analisar o resultado desejado e, então, selecionar o intervalo no qual os elementos estão contidos. O gráfico sempre está vinculado à planilha de dados, de modo que qualquer tipo de alteração na base imediatamente atualiza o gráfico, não importando se ele está posicionado com a planilha ou se está em uma guia específica.

Exemplos:

SENAC SÃO PAULO | 59

EXCEL 2016 – CRIANDO PLANILHAS INTERATIVAS E DASHBOARD

Figura 3.1 – Tipos de gráficos.

Componentes do gráfico: 1: Título do gráfico.

2: Eixo de valores.

3: Categorias.

4: Legenda.

Figura 3.2 – Componentes de gráfico

Para selecionar um intervalo contínuo, arraste o mouse ou clique na primeira célula, pressione a tecla Shift e clique na última célula. Se a seleção for alternada, então selecione o primeiro intervalo, mantenha a tecla Ctrl pressionada e vá selecionando com o mouse os intervalos desejados.

GRÁFICO TIPO COLUNAS O gráfico tipo colunas é um dos mais utilizados por ser de fácil compreensão e muito prático. Ele permite a visualização de várias categorias, compara cada uma delas, estabelece a diferença entre um dado e outro e a alteração (inversão) entre a posição dos dados na legenda e na categoria.

Para criar o gráfico é necessário selecionar os rótulos de colunas e os dados desejados para a sua composição.

60 | SENAC SÃO PAULO

EXCEL 2016 – CRIANDO PLANILHAS INTERATIVAS E DASHBOARD

O gráfico a ser criado, conforme a figura 3.3, tem por objetivo mostrar a quantidade de produtos entre os meses de janeiro e março. A seleção é referente às informações que serão analisadas por meio do gráfico. O intervalo de seleção é A2 até D6.

Existem duas formas de criar o gráfico: pela tecla de função F11, em que o gráfico ficará posicionado automaticamente em outra guia, ou pela guia Inserir. Criaremos pela guia Inserir.

Para trabalhar com os gráficos, abra o documento Produtos Eletrônicos_Gráficos_Capítulo 3. Há uma planilha para cada tipo de gráfico sugerido neste capítulo.

Figura 3.3 – Seleção de dados para a criação do gráfico.

1. Clique na guia Inserir, grupo Gráficos, opção Inserir Gráfico de Colunas.

Ao posicionar o mouse nas opções de gráfico, o tipo de gráfico é visualizado. Clicando na opção do gráfico, ele é inserido na planilha.

Figura 3.4 – Opções de gráficos.

2. Clique na opção Colunas agrupadas 2D. 3. Com o mouse pressionado, arraste o gráfico e coloque-o na posição desejada.

4. Clique no título e digite Quantidade meses.

5. Altere o tipo de fonte, o tamanho e a cor, e deixe o gráfico selecionado com um clique.

6. No eixo de valores, os números são definidos de acordo com os valores contidos na planilha.

Ao criar o gráfico, as cores das colunas são definidas pelo Excel, porém é possível alterá-las. Analise as informações da figura 3.5:

SENAC SÃO PAULO | 61

EXCEL 2016 – CRIANDO PLANILHAS INTERATIVAS E DASHBOARD

Figura 3.5 – Gráfico tipo colunas.

• Painel de gráficos: pelo painel de gráficos, é possível alterar vários tipos de informações, tais como tipo de gráfico, estilos de gráfico, layout, cores, adicionar elemento gráfico, alternar entre coluna e linha, etc.

Figura 3.6 – Painel com opções de alterações de gráfico.

• Alterar tipo de gráfico: basta clicar no gráfico, depois na guia Design e na opção Alterar Tipo de Gráfico. A figura 3.7 mostra as opções para alterar o tipo de gráfico; as opções estão na coluna à esquerda da janela.

Figura 3.7 – Janela Alterar Tipo de Gráfico.

• Estilos de gráfico: utilize o grupo Estilos de Gráfico para modificar aspectos visuais das informações do gráfico.

Figura 3.8 – Gráfico com o estilo alterado.

62 | SENAC SÃO PAULO

EXCEL 2016 – CRIANDO PLANILHAS INTERATIVAS E DASHBOARD

• Layout: por meio do layout é possível alterar o gráfico para alguns modelos diferentes. No caso da figura 3.9, a tabela de dados ficou visível logo abaixo do gráfico.

Figura 3.9 – Layout alterado.

• Cores: no painel de cores, é possível definir um padrão de cores diferente para o gráfico.

Figura 3.10 – Cores de gráfico.

• Adicionar elemento gráfico: esta opção permite alterar várias situações ou elementos, como a posição da legenda, valores dos rótulos, linhas de grade, títulos, eixos, entre outros. É importante testar todos os elementos para entender a funcionalidade de cada um.

Figura 3.11 – Elementos de gráfico.

SENAC SÃO PAULO | 63

EXCEL 2016 – CRIANDO PLANILHAS INTERATIVAS E DASHBOARD

• Alternar coluna/linha: a alternação entre coluna e linha modifica o eixo em relação à categoria.

Na figura 3.12, é possível perceber a posição entre a legenda e a categoria. Essa diferenciação depende de como o usuário precisa analisar as informações contidas no gráfico.

Figura 3.12 – Alternação coluna/linha.

O gráfico utilizado como exemplo foi o tipo Coluna 2D, mas é interessante testar outros tipos, como Coluna 3D, Colunas 2D Empilhadas, Colunas 3D Empilhadas, etc.

GRÁFICO TIPO BARRAS O gráfico tipo barras é muito parecido com o tipo colunas: a diferença é que um fica na vertical e o outro na horizontal. Para criar o gráfico, é necessário selecionar o intervalo desejado. Assim como o tipo colunas, o gráfico em barras também permite a seleção de vários elementos, tanto na quantidade de linhas como de colunas.

Para criar o gráfico em barras, selecione o intervalo de A2 a E7; neste caso, os totais estão inclusos.

Figura 3.13 – Seleção de dados para a criação do gráfico.

1. Clique na guia Inserir, grupo Gráficos, opção Inserir Gráfico de Barras.

2. Ao posicionar o mouse nas opções de gráfico, o tipo de gráfico é visualizado. Clicando na opção do gráfico, ele é inserido na planilha.

3. Clique em Barras 2D/Barras Agrupadas.

A figura 3.14 exibe o gráfico tipo barras. É possível efetuar todas as alterações conforme já foi abordado.

64 | SENAC SÃO PAULO

EXCEL 2016 – CRIANDO PLANILHAS INTERATIVAS E DASHBOARD

Figura 3.14 – Gráfico tipo barras.

GRÁFICO TIPO LINHAS O gráfico tipo linhas é bastante utilizado, principalmente quando a situação analisada tem base em dados estatísticos.

Para criar o gráfico em linhas, selecione o intervalo de A3 a D7.

Figura 3.15 – Planilha de dados.

1. Clique na guia Inserir, grupo Gráficos, opção Inserir Gráfico de Linhas.

2. Ao posicionar o mouse nas opções de gráfico, o tipo de gráfico é visualizado. Clicando na opção do gráfico, ele é inserido na planilha.

3. Clique em Linhas 2D/Linhas com Marcadores.

A figura 3.16 apresenta o gráfico em linhas. É possível efetuar todas as alterações conforme já foi abordado.

Figura 3.16 – Gráfico de linhas.

SENAC SÃO PAULO | 65

EXCEL 2016 – CRIANDO PLANILHAS INTERATIVAS E DASHBOARD

GRÁFICO TIPO PIZZA Para criar o gráfico tipo pizza é preciso ter bastante cuidado, pois ele trabalha com um eixo de valores (uma coluna ou linha com texto e apenas uma coluna com valores) e a leitura é realizada por rótulo. É provável que esse seja um dos modelos preferidos de gráfico, no entanto é necessário atentar-se para o tipo de informação desejada, pois se for uma análise em que existe a necessidade de avaliação com vários eixos, então é preciso escolher outro tipo.

Observe que na figura 3.17 existe uma série de colunas com valores, mas, como o gráfico criado será tipo pizza, não será possível selecionar tudo, então é necessário definir o intervalo desejado. A análise será o Total com Reajuste por data.

Selecione de A3 a A13, pressione a tecla Ctrl e selecione as células G3 até G13.

Figura 3.17 – Planilha de dados.

1. Clique na guia Inserir, grupo Gráficos, opção Inserir Gráficos de Pizza ou Rosca.

2. Ao posicionar o mouse nas opções de gráfico, o tipo de gráfico é visualizado. Clicando na opção do gráfico, ele é inserido na planilha.

3. Clique em Pizza 2D/Pizza.

A figura 3.18 apresenta o gráfico tipo pizza. Nele é possível efetuar todas as alterações conforme já foi abordado.

4. Clique na guia Design, grupo Layout de Gráfico.

5. Clique na opção Adicionar Elemento Gráfico; em Rótulos de Dados, deixe Melhor Ajuste; em Legenda, deixe a opção Direita.

66 | SENAC SÃO PAULO

EXCEL 2016 – CRIANDO PLANILHAS INTERATIVAS E DASHBOARD

Figura 3.18 – Planilha e gráfico pizza.

GRÁFICO TIPO COMBINAÇÃO No gráfico tipo combinação é possível combinar mais de um tipo de gráfico. Na figura 3.19, o gráfico será criado selecionando as colunas Produtos eletrônicos, Total e Total %, porém, na visualização, a coluna Total % deve ficar em linhas, para facilitar a análise dos dados.

Selecione as células de A2 a A6, pressione a tecla Ctrl e selecione as células de E2 a F6.

Figura 3.19 – Planilha de dados.

1. Clique na guia Inserir, grupo Gráficos, opção Inserir Gráfico de Combinação.

2. Ao posicionar o mouse nas opções de gráfico, o tipo de gráfico é visualizado. Clicando na opção do gráfico, ele é inserido na planilha.

3. Clique em Linha de Eixo Secundário.

4. Dê um clique na linha do gráfico e depois na guia Formatar.

5. Clique em Grupo Estilos de Forma, opção Contorno da Forma.

6. Em Contorno da Forma, opção Setas, clique no tipo de seta desejada.

Na análise do gráfico de combinação da figura 3.20, a linha com a seta é em relação ao Total %, no eixo à direita; as colunas representam o Total, eixo à esquerda.

SENAC SÃO PAULO | 67

EXCEL 2016 – CRIANDO PLANILHAS INTERATIVAS E DASHBOARD

Figura 3.20 – Gráfico de combinação.

GRÁFICO DINÂMICO Geralmente o gráfico dinâmico é criado a partir de uma tabela dinâmica, porém é possível criar somente o gráfico dinâmico sem a necessidade de criar a tabela dinâmica antes. Por meio do gráfico dinâmico, o usuário pode trabalhar com filtros e visualizar somente o que é necessário naquele momento. A tabela dinâmica é criada automaticamente.

Para criar o gráfico dinâmico:

Figura 3.21 – Painel Gráficos.

1. Clique na planilha com os dados (não é necessário selecionar, pois o gráfico será criado com todo o intervalo).

2. Clique na guia Inserir, grupo Gráficos, opção Gráfico Dinâmico.

3. Em Tabela Intervalo, deixe como está, pois é o intervalo da planilha.

4. Escolha o local para colocar o gráfico dinâmico e clique em Planilha Existente.

5. Local: clique na célula que será o ponto inicial da tabela dinâmica.

6. Clique em OK para confirmar.

Figura 3.22 – Janela Criar Gráfico Dinâmico.

68 | SENAC SÃO PAULO

EXCEL 2016 – CRIANDO PLANILHAS INTERATIVAS E DASHBOARD

Observe a figura 3.23 com as opções Tabela Dinâmica, Gráfico4 e Campos do Gráfico.

• Tabela Dinâmica: é a tabela com dados agrupados.

• Gráfico: é o painel onde será gerado o gráfico.

• Campos do Gráfico: são os campos rótulos de colunas da planilha de dados.

Figura 3.23 – Gráfico Dinâmico.

1. Clique em Campos do Gráfico para criar a tabela dinâmica e automaticamente o gráfico dinâmico também é criado.

2. Itens numerados: 1 – Tabela dinâmica; 2 – Gráfico dinâmico; 3 – Campos de dados. Em relação aos campos, o gráfico dinâmico foi criado com base nos produtos.

Figura 3.24 – Tabela e gráfico dinâmico.

À medida que os campos são filtrados pela tabela dinâmica, o gráfico é alterado imediatamente. Observe a figura 3.25: o gráfico dinâmico foi criado em relação aos produtos eletrônicos.

Figura 3.25 – Gráfico com filtro.

SENAC SÃO PAULO | 69

EXCEL 2016 – CRIANDO PLANILHAS INTERATIVAS E DASHBOARD

Como mostra a figura 3.26, o gráfico dinâmico foi criado com base nos meses. O painel Campos da Tabela já mudou em relação ao da figura 3.25.

Figura 3.26 – Gráfico dinâmico com dados alterados.

Na figura 3.27, um novo produto foi acrescentado à planilha de dados e já está atualizado na tabela dinâmica e no gráfico dinâmico.

Figura 3.27 – Dados atualizados.

FORMATAÇÃO DE GRÁFICOS Após os gráficos serem criados, eles podem e devem ser formatados da forma que o usuário desejar.

A seguir, alguns exemplos: 1. Abra o documento Produtos Eletrônicos_Gráficos_Capítulo3.

2. Clique na guia Formatação e selecione o intervalo de A2 a E6.

3. Pressione a tecla de função F11, que cria um gráfico em outra guia.

4. Renomeie a guia como Gráfico_Formatação.

A figura 3.28 apresenta um gráfico simples tipo colunas agrupadas. Por meio dos painéis, é possível alterar formatações e elementos que compõem o gráfico. Nos painéis à esquerda e à direita são exibidos elementos como eixos, rótulos, títulos, legendas, entre outros.

70 | SENAC SÃO PAULO

EXCEL 2016 – CRIANDO PLANILHAS INTERATIVAS E DASHBOARD

Figura 3.28 – Gráfico tipo colunas sem formatação.

No gráfico da figura 3.29, o painel à direita apresenta diversas opções de formatação: legendas com padrões de cores diferenciados, contornos, preenchimentos, efeitos de formas, etc.

1. Selecione o gráfico e clique na guia Formatar.

2. No grupo Estilos de Formas, clique na setinha ao lado (painel à direita da tela).

3. Em Opções de Série, é possível efetuar as formatações da forma desejada.

Figura 3.29 – Opções de formatação.

Outra opção bastante interessante é deixar cada eixo com um tipo de gráfico.

1. Selecione o gráfico e clique na guia Design.

2. No grupo Tipo, clique em Alterar Tipo de Gráfico.

3. Clique na opção Combinação e, em Nome da Série, altere cada série com um tipo diferente de gráfico.

SENAC SÃO PAULO | 71

EXCEL 2016 – CRIANDO PLANILHAS INTERATIVAS E DASHBOARD

Figura 3.30 – Janela para alteração do gráfico.

4. Após alterar, pressione a tecla OK para confirmar.

As colunas representam janeiro, a área representa fevereiro e a linha representa março.

5. Clique na guia Formatar, grupo Estilos de Forma, e efetue as alterações desejadas, conforme a figura 3.31.

Figura 3.31 – Gráfico com tipos diferentes.

Crie um novo gráfico tipo Colunas 3D Agrupadas.

1. Selecione o intervalo de A2 a D6.

2. Clique na guia Inserir, grupo Gráficos, opção Colunas 3D Agrupadas.

Observe a figura 3.32 e note que o gráfico ficou um pouco inclinado. Para que fique em linha reta, é preciso alterar a posição.

72 | SENAC SÃO PAULO

EXCEL 2016 – CRIANDO PLANILHAS INTERATIVAS E DASHBOARD

Figura 3.32 – Gráfico tipo Colunas 3D Agrupadas.

Para alterar a posição do gráfico: 1. Clique com o botão direito do mouse sobre uma das colunas rótulos.

2. Clique na opção Rotação 3D. Na Figura 3.33, no painel à direita da tela, assinale a opção Eixos em Ângulo Reto. Ao marcar essa opção, o gráfico fica em linha reta.

Figura 3.33 – Opção Eixos em Ângulo Reto.

Conforme a figura 3.33, no painel à direita, altere: 1. Rotação X para 280°.

2. Rotação Y para 90°.

3. Profundidade (% da base): 110.

Observe o resultado na figura 3.34.

Figura 3.34 – Gráfico 3D alterado.

SENAC SÃO PAULO | 73

EXCEL 2016 – CRIANDO PLANILHAS INTERATIVAS E DASHBOARD

A figura 3.35 apresenta o gráfico com as formas das colunas alteradas. Para efetuar as alterações: 1. Clique com o botão direito do mouse sobre uma das colunas.

2. Clique na opção Formatar Série de Dados.

3. Em Forma da Coluna, altere para a forma desejada. Ao clicar em um dos rótulos (coluna), basta escolher a forma: quadrado, cilindro, cone, etc.

Figura 3.35 – Formas de colunas.

GRÁFICOS ESPECÍFICOS – EXCEL 2016 A versão Excel 2016 apresenta poucas diferenças em relação às versões anteriores, mas uma diferença bem significativa são os gráficos. Além dos já conhecidos, a versão 2016 apresenta os tipos:

Treemap Este gráfico apresenta os dados em retângulos, que são maiores quando os valores representados são mais altos, e vice-versa.

Explosão solar Este gráfico é ideal para reproduzir dados de forma hierárquica. A hierarquia é representada por um círculo. Para a criação desse tipo de gráfico, é necessário que os dados da planilha sejam definidos de forma correta. Este gráfico lembra o tipo rosca.

Cascata Este gráfico mostra os dados acumulados e apresenta de forma fácil valores positivos e negativos, além de mostrar como esses valores são afetados quando dados são alterados.

Histograma É um gráfico parecido com o gráfico de Pareto e mostra com mais facilidade dados de frequência.

Caixa estreita O uso deste gráfico é mais comum para representar dados estatísticos.

A figura 3.36 contém três modelos de gráfico da versão 2016: o gráfico tipo explosão solar precisa de dados de associação para criar a explosão; o cascata apresenta os dados em forma de cascata, ou

74 | SENAC SÃO PAULO

EXCEL 2016 – CRIANDO PLANILHAS INTERATIVAS E DASHBOARD

seja, uma coluna após a outra; e o treemap apresenta os dados em formas retangulares. O modo de seleção é o mesmo. Efetue vários testes com esses gráficos.

Figura 3.36 – Gráficos versão 2016.

EXERCÍCIO PROPOSTO Resolução livre: Crie uma planilha relacionada ao seu trabalho ou a alguma área de seu conhecimento e calcule com as funções específicas de acordo com a situação escolhida. Crie os seguintes gráficos: • Pizza.

• Combinação.

• Gráfico dinâmico.

• Explosão solar.

Formate os gráficos da maneira desejada alterando: • Cores.

• Estilos de gráfico.

• Elemento do gráfico.

• Estilos de formas.

Salve o documento.

SENAC SÃO PAULO | 75

4. Gráficos específicos – Dashboard INTRODUÇÃO

D

ashboard significa Painel de Instrumentos ou Painel de Controle, então este capítulo visa apresentar vários painéis que resultam em gráficos otimizados, isto é, gráficos extremamente dinâmicos, construídos no MS Excel.

Para criar os gráficos Dashboard é necessário utilizar os botões de macros, as caixas de controles e as várias funções que têm o objetivo de otimizar as planilhas. Devem ser definidos critérios para facilitar a otimização de todos os dados.

Dashboard possibilita a criação de gráficos que reúnem diversos recursos consolidados e com um grande número de informações, que aparentemente funcionam de maneira simultânea por causa dos diversos controles combinados. Uma das funções bastante utilizadas é a função SOMASE (entre outras), conforme comentado anteriormente.

SENAC SÃO PAULO | 77

EXCEL 2016 – CRIANDO PLANILHAS INTERATIVAS E DASHBOARD

Para a criação de todos os gráficos será usado o documento Produtos Eletrônicos_Gráficos_Dashboard_Capítulo 4. As planilhas com os dados estão renomeadas e na sequência.

GRÁFICO COM AS FUNÇÕES REPT E SE A função SE é uma das mais utilizadas, pois trabalha com critérios e condições, proporcionando uma série de combinações de resultados das mais diversas formas e nas diferentes áreas do mercado de trabalho, sendo, portanto, uma função eclética. A função REPT repete um texto ou símbolo dependendo do valor em quantidade. Por exemplo, para 10 tablets, o símbolo é repetido 10 vezes, se forem 5, repete-se 5 vezes, e assim por diante, símbolo ou texto.

Este primeiro gráfico – se puder ser chamado de gráfico, porque na verdade é o uso de várias funções com critérios diferentes como resultado – acaba criando efeitos interessantes. Ao modificar o critério da célula E1, conforme a figura 4.1, os símbolos são alterados dependendo da alternância do critério presente na lista. Recursos utilizados: caixa de combinação, função SE e função REPT. Referências: • Número 1: caixa de combinação.

• Número 2: funções SE e REPT. • Número 3: resultado e combinação das funções SE e REPT.

Figura 4.1 – Critério para a criação de gráficos.

Para construir este gráfico de resultados, clique na guia ou aba Rept_SE do documento Produtos Eletrônicos_Gráficos_Dashboard_Capítulo 4.

Caixa de combinação Primeiro será construída uma caixa de combinação, pela qual as funções serão calculadas, para que se chegue ao resultado esperado.

1. Clique na guia Desenvolvedor e depois no grupo Controles.

2. No grupo Controles, clique na opção Caixa de Combinação.

3. Posicione o mouse na tela e arraste para definir o tamanho da caixa.

4. Posicione o mouse sobre a caixa e clique com o botão direito.

5. Em Formatar Controle, clique na guia Controle.

6. Em Intervalo de entrada, selecione a lista de produtos eletrônicos nas células L1 a L4.

7. Em Vínculo da célula, clique na célula E1 e depois clique no botão OK para confirmar.

Para verificar se realmente ficou correto, clique na lista e nos itens. Como celular é o primeiro, ao clicar nele, deve aparecer na célula E1 o número 1 e assim sucessivamente. Posicione a caixa de combinação sobre a célula E1, desse modo o número não ficará visível. Oculte a coluna L.

78 | SENAC SÃO PAULO

EXCEL 2016 – CRIANDO PLANILHAS INTERATIVAS E DASHBOARD

Funções SE e REPT O resultado será o seguinte: ao modificar o valor da caixa de combinação, o valor da coluna E será alterado de acordo com a quantidade de produtos vendidos contida na coluna D, mas o resultado será apresentado por meio de símbolos.

1. Clique na célula E4 e digite a sintaxe: =SE($E$1=$J$1;REPT($K$1;D4);SE($E$1=$J$2; REPT($K$2;D4);SE($E$1=$J$3;REPT($K$3;D4);REPT($K$4;D4)))) Se o valor da célula E1 for igual ao da J1, ele será repetido de acordo com a quantidade de vezes de produtos vendidos (coluna D), e essa repetição será apresentada com o símbolo em K1. Se o valor da célula E1 for igual ao da J2, ele será repetido de acordo com a quantidade de vezes de produtos vendidos (coluna D), e essa repetição será apresentada com o símbolo em K2. Se o valor da célula E1 for igual ao da J3, ele será repetido de acordo com a quantidade de vezes de produtos vendidos (coluna D), e essa repetição será apresentada com o símbolo em K3. Se não será representado pelo símbolo em K4.

2. Após a digitação, pressione a tecla Enter e arraste para as demais células.

Na figura 4.2, o item da caixa de combinação é iPad, número 2, e o símbolo apresentado é repetido na quantidade de caracteres correspondente. Se a caixa de combinação for alterada, o símbolo também é alterado automaticamente.

Use a formatação condicional para que, ao alterar o produto pela caixa de combinação, a cor também seja modificada. Crie uma nova planilha com as informações desejadas e calcule com as funções SE e REPT.

Figura 4.2 – Planilha resolvida.

GRÁFICO DASHBOARD – COLUNAS E LINHAS COM O BOTÃO DE ROTAÇÃO O gráfico criado será um Dashboard, ou seja, um gráfico dinâmico, otimizado. Neste exemplo serão criados dois gráficos que funcionarão de maneira simultânea, apresentando a quantidade de produtos vendidos e a fatura total com reajuste.

Para construir esse gráfico de resultados, clique na guia ou aba SOMASE do documento Produtos Eletrônicos_Gráficos_Dashboard_Capítulo 4.

Botão de Rotação Primeiro será criado um botão de rotação, pelo qual as funções serão calculadas, para que se chegue ao resultado esperado.

1. Clique na guia Desenvolvedor, grupo Controles.

2. No grupo Controles, clique na opção Botão de Rotação.

3. Posicione o mouse na tela e arraste para definir o tamanho da caixa.

SENAC SÃO PAULO | 79

EXCEL 2016 – CRIANDO PLANILHAS INTERATIVAS E DASHBOARD

4. Posicione o mouse sobre a caixa e clique com o botão direito.

5. Na opção Formatar Controle, clique na guia Controle.

6. Na caixa Valor atual, insira 0 (zero).

7. Na caixa Valor mínimo, insira 1 (a sequência começa pelo número 1).

8. Na caixa Valor máximo, insira 4 (são quatro itens na lista).

9. Em Alteração incremental, insira 1 (a sequência da lista será de um em um).

10. Em Vinculo da célula, insira célula I1.

11. Clique no botão OK para confirmar.

Função ESCOLHER A função ESCOLHER irá procurar o valor da lista a partir da rolagem, por meio do Botão de Rotação.

1. Clique na célula J1 e depois na opção Inserir Função (fx).

2. Em Núm_Índice, clique na célula I1.

3. Em Valor1, clique na célula I4 para TV. 4. Em Valor2, clique na célula I5 para Celular.

5. Em Valor3, clique na célula I6 para Tablet.

6. Em Valor4, clique na célula I7 para iPad. 7. Clique no botão OK para confirmar, conforme a figura 4.3.

Figura 4.3 – Função ESCOLHER.

Funções SE e SOMASE Lembrando que a função SOMASE trabalha com dados consolidados que facilitam na criação de gráficos Dashboard. É necessário fixar as células que representam a referência absoluta; para isso, pressione a tecla de função F4.

1. Clique na célula J4 e digite a sintaxe =SE(I4=$J$1;SOMASE($B$4:$B$17;I4;$D$4:$D$17);0).

2. Após a digitação, pressione a tecla Enter e arraste para as demais células.

3. Célula I4, posição do produto na planilha-resumo.

4. Célula J4, função ESCOLHER. 5. Função SOMASE, intervalo de B4 a B7, base de dados, coluna de produtos.

6. Célula I4 posição, intervalo de D4 a D7, base de dados, coluna com a quantidade de produtos.

Se a célula I4 for igual à J1, o resultado será a soma da quantidade em relação aos produtos, senão o resultado será 0 (zero).

80 | SENAC SÃO PAULO

EXCEL 2016 – CRIANDO PLANILHAS INTERATIVAS E DASHBOARD

7. Clique na célula K4 e digite a sintaxe =SE(I4=$J$1;SOMASE($B$4:$B$17;I4;$G$4:$G$17);0).

Figura 4.4 – Planilha calculada.

Após finalizar a criação das funções, teste e observe o resultado. Ao deslizar o Botão de Rotação, o resultado na planilha de resumo é alterado de acordo com a célula I1 e a célula J1. Na figura 4.4, célula J1, produto iPad; na planilha de resumo, o total de quantidade e o total com reajuste é referente a este produto.

Criação do gráfico O gráfico será criado a partir da planilha de resumo. O primeiro gráfico será em relação à quantidade, no intervalo de I3 a J7.

1. Insira uma nova planilha (guia) e renomeie como Gráf_SomaSe. 2. Selecione o intervalo de I3 a J7.

3. Clique na guia Inserir, grupo Gráficos, opção Coluna 2D, Colunas Agrupadas.

4. Mova o gráfico para a guia Gráf_SomaSe, clicando na guia Design, grupo Local, opção Mover Gráfico.

5. Em Objeto em, selecione a opção Gráf_SomaSe e confirme em OK. Para o segundo gráfico, siga o procedimento anterior, porém os intervalos serão de I3 a I7 e de K3 a K7, referentes ao total com reajuste.

Na planilha Gráf_SomaSe, crie uma caixa de combinação com a lista de produtos da planilha SomaSe, intervalo de I4 a I7. A figura 4.5 apresenta como o vínculo com a outra planilha ficou, demonstrando as caixas Intervalo de entrada e Vínculo da célula. Quando existe vínculo com outra planilha, no caso a planilha SOMASE, a fixação é automática.

Figura 4.5 – Painel Caixa de Combinação.

SENAC SÃO PAULO | 81

EXCEL 2016 – CRIANDO PLANILHAS INTERATIVAS E DASHBOARD

Deixe o gráfico referente à quantidade como Colunas Agrupadas 2D e formate. Deixe o gráfico Total com Reajuste como Linha Empilhada com Marcadores e formate.

Para que o título do gráfico seja alterado de acordo com o nome do produto, siga o procedimento: 1. Clique no título de um dos gráficos e digite o sinal de igual (=).

2. Clique na planilha SomaSe e depois na célula J1; pressione a tecla Enter para confirmar.

Repita o procedimento para o rótulo Quantidade e para o outro gráfico também. A figura 4.6, mostra que o produto selecionado na caixa de combinação é Celular, que, então, aparece nos títulos dos gráficos. O gráfico da esquerda apresenta a quantidade de celulares vendida e o da direita, o total de fatura já com o reajuste. À medida que os itens são selecionados, o gráfico é alterado. Isso é um Dashboard.

Figura 4.6 – Gráfico Dashboard: celular

A figura 4.7 exibe a seleção do produto Tablet. A dinâmica desse tipo de gráfico é enorme em relação aos gráficos comuns. Teste na caixa de combinação e observe o resultado.

Figura 4.7 – Gráfico Dashboard: tablet.

Crie uma nova planilha com as informações desejadas e calcule. Crie outros tipos de gráficos e teste pela Caixa de Combinação. Observe a interatividade do gráfico.

82 | SENAC SÃO PAULO

EXCEL 2016 – CRIANDO PLANILHAS INTERATIVAS E DASHBOARD

GRÁFICO DASHBOARD – COLUNAS E PIZZA COM BARRA DE ROLAGEM A função SOMASES será usada neste gráfico, pois será realizada a comparação entre produtos e local de vendas, ou seja, onde o produto foi vendido.

Figura 4.8 – Planilha de produtos.

Barra de rolagem A barra de rolagem será criada para facilitar e otimizar a planilha com os dados consolidados e, consequentemente, o gráfico.

1. Clique na guia Desenvolvedor, grupo Controles, opção Barra de Rolagem.

2. Posicione o mouse na tela e arraste até deixar com o tamanho desejado.

3. Pressione o botão direito e clique na opção Formatar Controle.

4. Em Valor atual, insira 0 (zero).

5. Em Valor mínimo, insira 1.

6. Em Valor máximo, insira 4.

7. Em Alteração incremental, insira 1.

8. Em Vínculo com célula, insira G1. Arraste a caixa e posicione na célula G1.

9. Digite Loja na célula F1 e Produto na célula H1.

Função ESCOLHER A função ESCOLHER irá procurar o valor da lista a partir da rolagem, por meio da barra de rolagem.

1. Clique na célula I1 e depois na opção Inserir Função (fx).

2. Clique na função ESCOLHER. 3. Em Núm_Índice, clique na célula G1.

4. Em Valor1, clique na célula Q3 para Celular.

5. Em Valor2, clique na célula Q4 para iPad. 6. Em Valor3, clique na célula Q5 para Tablet.

7. Em Valor4, clique na célula Q6 para TV. 8. Oculte a coluna Q.

SENAC SÃO PAULO | 83

EXCEL 2016 – CRIANDO PLANILHAS INTERATIVAS E DASHBOARD

Função SOMASES A função SOMASES trabalha com dados consolidados, porém usando dois ou mais critérios para a consolidação de dados. Para calcular: 1. Clique na célula G3 referente à quantidade consolidada (Qtd 1) e digite a sintaxe: =SOMASES($C$3:$C$16;$B$3:$B$16;$F3;$A$3:$A$16;$I$1).

2. Após a digitação, pressione a tecla Enter e arraste para as demais células.

3. O intervalo de C3 a C16 é a quantidade referente à coluna C. 4. O intervalo de B3 a B16 é referente às lojas (coluna B).

5. A célula F3 é o primeiro produto dos dados consolidados.

6. O intervalo de A3 a A16 é referente aos produtos (coluna A).

7. Clique na célula I1 e insira a função ESCOLHER. 8. Repita o procedimento para a coluna H. 9. Na coluna I, célula I3, efetue a soma em relação às quantidades 1 e 2. É a partir dos dados consolidados que os gráficos serão criados. Clique nas setinhas da barra de rolagem e observe que os valores da planilha são alterados.

Criação do gráfico A partir dessa planilha consolidada serão criados dois gráficos Dashboard, um pizza, que mostre as lojas e o total de produtos vendidos, outro coluna, que mostre os totais das lojas por Qtd1 e Qtd2.

Para criar o gráfico pizza: 1. Selecione os intervalos de F2 a F6 e de I2 a I6.

2. Clique na guia Inserir, grupo Gráficos, opção Gráfico, tipo Pizza 2D. 3. Clique no título e digite igual (=); clique na célula I1e pressione a tecla Enter. Dessa forma, quando o nome do produto é alterado na célula I1, o título do gráfico também é alterado, pois foi criado um vínculo entre os dois. É bom vincular o título do gráfico a algum título da planilha – com os botões de controle –, pois quando é alterado o título da planilha, o do gráfico é alterado simultaneamente.

4. Clique na guia Design, grupo Layout de Gráfico, opção Adicionar Elemento Gráfico.

5. Em Rótulos de Dados, clique em Extremidade Interna e defina outras formatações desejadas.

O segundo gráfico é para ilustrar as vendas em relação às lojas por meio de duas colunas, Qtd1 e Qtd2. Para criar o gráfico Colunas: 1. Selecione os intervalos de F2 a H6.

2. Clique na guia Inserir, grupo Gráficos, opção Gráfico, tipo Colunas Agrupadas 2D. 3. Clique no título e digite igual (=); clique na célula I1e pressione Enter.

Use a barra de rolagem e verifique a alteração nos dois gráficos. O tipo pizza apresenta somente o total geral, já o colunas altera a quantidade em relação às colunas G e H. A figura 4.9 mostra os dados referentes ao celular.

84 | SENAC SÃO PAULO

EXCEL 2016 – CRIANDO PLANILHAS INTERATIVAS E DASHBOARD

Figura 4.9 – Produto Celular.

Use a barra de rolagem e verifique a alteração nos dois gráficos: o tipo pizza apresenta somente o total geral, já o colunas altera a quantidade em relação às colunas G e H. A figura 4.10 mostra os dados referentes ao iPad. Neste caso, as lojas 3 e 4 não venderam nenhuma unidade deste produto.

Figura 4.10 – Produto iPad.

As novas vendas serão incluídas na planilha de dados. Como os intervalos foram fixados na planilha de resumo, então é preciso que as novas linhas sejam inseridas no meio, ou então selecionar as colunas inteiras, sempre fixando. Crie outra planilha com os dados desejados, use a função SOMASES e crie gráficos associados à planilha de resumo.

GRÁFICO DASHBOARD – EXPLOSÃO SOLAR COM O BOTÃO DE OPÇÃO A planilha usada para este gráfico será a mesma da figura 4.10. Para isso, crie uma cópia, renomeie como Exemplo_Botão_Opção e faça as alterações conforme a figura 4.11. Este gráfico apresenta a quantidade de produto vendido por loja e irá funcionar a partir da seleção do Botão de Opção.

1. Excluir a coluna referente ao total.

2. Na célula F1, insira a função ESCOLHER, conforme a figura 4.11.

3. Na coluna G, insira as lojas.

SENAC SÃO PAULO | 85

EXCEL 2016 – CRIANDO PLANILHAS INTERATIVAS E DASHBOARD

4. Nas colunas H e I, insira a função SOMASES, conforme a sintaxe =SOMASES($C$3:$C$16;$A$3:$A$16; $F$1;$B$3:$B$16;G3).

Figura 4.11 – Planilha Botão de Opção.

Caixa de grupo 1. Clique na guia Desenvolvedor, grupo Controles, opção Caixa de Grupos.

2. Posicione o mouse na tela e arraste, deixando com o tamanho desejado.

3. Altere o rótulo para Produtos Eletrônicos.

Botão de opção 1. Clique na guia Desenvolvedor, grupo Controles, Botão de Opção.

2. Dê um clique dentro da caixa de grupo.

3. Repita o procedimento três vezes, pois são quatro produtos.

4. Altere o rótulo de cada botão com a mesma sequência da planilha.

5. Pressione o botão direito do mouse sobre o primeiro botão e clique em Formatar Controle.

6. Em Controle, opção Vínculo da Célula, clique na célula H1. Repita o procedimento para os demais botões, embora, ao configurar o primeiro, os outros assumam a mesma configuração.

Criação do gráfico O gráfico será criado a partir da planilha consolidada. Ao selecionar os botões, o gráfico é alterado automaticamente.

1. Selecione o intervalo de F2 a I6.

2. Clique na guia Inserir, grupo Gráficos, opção Gráfico Explosão Solar (na versão 2016).

3. Com o gráfico selecionado, clique na guia Design, grupo Layout de Gráfico, opção Rótulos de Dados e em Mais Opções de Rótulos de Dados.

4. No painel Formatar Rótulos de Dados, à direita da tela, clique em Opções de Rótulos (ícone gráfico) e assinale as opções Nome da Categoria e Valor; na opção Separador, deixe como Nova Linha.

Para verificar o resultado, clique nos botões de opção e analise o gráfico, como na figura 4.12.

86 | SENAC SÃO PAULO

EXCEL 2016 – CRIANDO PLANILHAS INTERATIVAS E DASHBOARD

Figura 4.12 – Gráfico com botão de opção.

Altere os dados da planilha-matriz: a planilha consolidada será recalculada e o gráfico será alterado automaticamente. Crie outra planilha com os dados desejados e gráficos da planilha de resumo.

GRÁFICO DASHBOARD – PIZZA E COLUNAS COM BARRAS, PIRÂMIDE, CILINDRO E BOTÃO DE OPÇÃO As funções SOMASES e ESCOLHER serão utilizadas com a caixa Botão de Opção, cujo objetivo é apresentar o total de vendas por mês e o total geral em relação aos meses.

Abra o documento Produtos Eletrônicos_Gráficos_Dashboard_Capítulo 4 e clique na guia Exemplo4. Esta planilha contém toda a base de dados para a criação da planilha-resumo e, posteriormente, dos gráficos.

Figura 4.13 – Planilha de dados.

Para criar a planilha de resumo, será necessário inserir uma nova guia (planilha), altere o nome para Caixa2. Para criar os gráficos siga os procedimentos a seguir:

SENAC SÃO PAULO | 87

EXCEL 2016 – CRIANDO PLANILHAS INTERATIVAS E DASHBOARD

Função ESCOLHER • Clique na célula B1 e calcule usando a função ESCOLHER. A sintaxe é =ESCOLHER(D1;J2;J3;J4), sendo D1 a posição referente ao produto e J2, J3 e J4 referentes à lista de produtos.

Função SOMASES É a soma em relação ao acumulado de produtos e cidades, ou seja, o acumulado de cada produto em relação a uma cidade específica.

1. Clique na célula C3 e calcule com a função SOMASES. A sintaxe é =SOMASES(Exemplo4!C:C; Exemplo4!$A:$A;$B3;Exemplo4!$B:$B;$B$1).

Figura 4.14 – Função SOMASES.

2. Arraste para os demais meses; na coluna F, efetue a soma em relação aos três meses.

Crie uma caixa de grupo 1. Clique na guia Desenvolvedor, grupo Controles, Caixa de Grupo.

2. Arraste a caixa de grupo e deixe-a posicionada conforme a figura 4.15.

Botão de Opção 1. Clique na guia Desenvolvedor, grupo Controles, Botão de Opção.

2. Clique dentro da caixa de grupo e repita duas vezes.

3. Altere o rótulo para os nomes das cidades, conforme a figura 4.15.

Figura 4.15 – Planilha de resumo.

Criação dos gráficos O gráfico será criado a partir da planilha consolidada Caixa2. Ao clicar nos botões, os gráficos são alterados automaticamente. O gráfico pizza é referente aos produtos e ao total dos três meses. O gráfico colunas é referente aos produtos vendidos nos três meses.

88 | SENAC SÃO PAULO

EXCEL 2016 – CRIANDO PLANILHAS INTERATIVAS E DASHBOARD

Figura 4.16 – Tipos de gráficos.

1. Gráfico tipo pizza: selecione os intervalos de B2 a B6 e de F2 a F6.

2. Clique na guia Inserir, grupo Gráficos, opção Pizza 3D. 3. Clique no título, digite o sinal de igual (=) e clique na célula B1. Ao clicar nos botões, a cidade é alterada na célula B1 e no título do gráfico também.

4. Clique na guia Design, grupo Layout de Gráfico, opção Adicionar Elemento Gráfico.

5. Clique em Layout Rápido e selecione a opção com a porcentagem (%).

6. Clique em Rótulos de dados e depois na opção Extremidade Externa.

Formate o gráfico do modo desejado, modificando cores, fonte, linhas, etc.

1. Gráfico tipo colunas: selecione o intervalo de B2 a E6.

2. Clique na guia Inserir, grupo Gráficos, opção Colunas 3D Agrupadas.

3. Clique no título, digite o sinal de igual (=) e clique na célula B1. Ao clicar nos botões, a cidade é alterada na célula B1 e no título do gráfico também.

4. Clique na guia Design, grupo Layout de Gráfico, opção Adicionar Elemento Gráfico.

5. Clique em Tabela de Dados, opção Com Chaves de Legendas.

6. Para deixar as colunas com tipos diferentes, clique em uma das colunas.

7. Pressione o botão direito do mouse e clique na opção Formatar Série de Dados.

No painel à direita, é possível formatar as colunas em formatos diferenciados, todas as colunas com o mesmo formato, ou como apresentado no exemplo.

A figura 4.17 apresenta um gráfico com vários formatos.

Figura 4.17 – Formatação de gráfico.

Crie outra planilha com os dados desejados e gráficos da planilha de resumo. Para a versão 2016, faça gráficos específicos.

SENAC SÃO PAULO | 89

EXCEL 2016 – CRIANDO PLANILHAS INTERATIVAS E DASHBOARD

GRÁFICO DASHBOARD – VELOCÍMETRO COM BOTÃO DE OPÇÃO Dos diferentes tipos de gráficos estilo Dashboard, o tipo velocímetro é muito interessante. Como nos outros gráficos, é necessária ou não uma planilha pronta, porque muitas vezes o usuário trabalha com planilhas padronizadas, então o “esqueleto” do gráfico pode estar pronto e, à medida que os dados são inseridos, o gráfico é completado. A figura 4.18 mostra o recurso que será apresentado a seguir. É necessário seguir com muita atenção, pois para criar esse tipo de gráfico vários recursos de configuração e formatação são utilizados.

Figura 4.18 – Planilha para a criação de gráfico velocímetro.

Primero a planilha será calculada: na coluna D, será calculada a multa com várias condições, a célula A8 será calculada com a função ESCOLHER, e assim por diante.

1. Clique na célula A8 e calcule usando a função ESCOLHER. A B8 é a célula com a posição do produto na planilha, as demais células correspondem a cada produto da lista. A função é: =ESCOLHER(B8;A3;A4;A5;A6).

2. A coluna D é onde a multa será calculada e a multa será de acordo com a posição do mês. Clique na célula D3 e calcule usando a função SE – se o conteúdo da célula A3 for igual ao da célula A8, então será o valor da coluna C, célula C3, multiplicado pela porcentagem que está na célula A19 (sempre que for uma referência absoluta, é necessário fixar a célula com a tecla F4). Para dar sequência à fórmula, siga a sintaxe, quatro condições: =SE(A3=$A$8;C3*$A$19;SE(A3=$A$8; C3*$A$20;SE(A3=$A$8;C3*$A$21;C3*$A$22))).

3. Ao terminar, arraste para as demais células. Oculte o intervalo das linhas 19 até 22.

4. Selecione o intervalo de A9 a B16 e use a mesclagem de células.

5. Para criar os botões de opção, clique na guia Desenvolvedor, grupo Controles, opção Inserir e Botão de Opção.

6. Insira quatro botões e altere o nome conforme cada mês, como na figura 4.18.

7. Pressione o botão direito do mouse sobre o botão de opção, clique em Formatar Controle, guia Controle, opção Vínculo da célula, na célula B8.

Ao terminar, faça um teste e verifique se, ao clicar nos botões, os valores dos juros são alterados na coluna D. Para criar o gráfico de velocímetro, é preciso informar a posição do ponteiro. Essa posição é em relação ao valor total da coluna a partir da qual o gráfico será criado. Como será um gráfico tipo rosca, as informações devem obedecer apenas a um eixo de valores, igual ao gráfico tipo pizza. Em seguida, é preciso definir os eixos X e Y e também a base e a ponta em relação ao ponteiro.

90 | SENAC SÃO PAULO

EXCEL 2016 – CRIANDO PLANILHAS INTERATIVAS E DASHBOARD

Na base do eixo X e Y, digite zero (0). Será necessário criar três funções para calcular os ângulos do ponteiro: cosseno CON e seno SEN, funções Trigonometria e a função Pi. 1. Clique na célula E12 e calcule o cosseno =cos(pi)*E9/D7. E9 é a posição do ponteiro e D7 é o total da coluna sobre a multa, pois nesse exemplo o gráfico de velocímetro será criado com base nessa informação.

2. Clique na célula F12 e calcule o seno =sen(pi)*E9/D7.

Quando a posição for alterada, o seno e o cosseno serão alterados e, consequentemente, a posição do ponteiro em relação ao rótulo também.

Figura 4.19 – Função Seno e Cosseno.

Criação do Gráfico de Velocímetro 1. Selecione os intervalos de A2 a A7 e de D2 a D7. É necessário selecionar o Total também.

2. Clique na guia Inserir, grupo Gráficos, opção Gráfico tipo Rosca (precisa ser o tipo rosca para dar a impressão de velocímetro).

Figura 4.20 – Gráfico tipo rosca.

SENAC SÃO PAULO | 91

EXCEL 2016 – CRIANDO PLANILHAS INTERATIVAS E DASHBOARD

Configuração do gráfico velocímetro: 1. Clique com o botão direito sobre o gráfico.

2. Clique na opção Formatar Série de Dados.

3. No painel à direita, clique na opção Ângulo da primeira fatia, deixe 270°.

No painel à direita, ao deixar com 270°, a fatia da rosca com o total fica posicionada na parte inferior do gráfico (necessário para criar o velocímetro).

Figura 4.21 – Configuração do gráfico.

Observe a figura 4.22, os dois exemplos apresentam a sequência da formatação para a fatia que representa o total.

• Exemplo 1: a fatia que representa o total está selecionada e com preenchimento.

• Exemplo 2: a fatia que representa o total está sem preenchimento e deve permanecer assim.

Figura 4.22 – Configuração do gráfico.

Para criar o ponteiro: É necessário calcular o cosseno CON e o seno SEN, como apresentado anteriormente.

Figura 4.23 – Eixos.

92 | SENAC SÃO PAULO

EXCEL 2016 – CRIANDO PLANILHAS INTERATIVAS E DASHBOARD

Indicação do seno e do cosseno ou posição do ponteiro.

Figura 4.24 – Ponteiro.

1. Com o botão direito do mouse sobre o gráfico, clique na opção Selecionar Fonte de Dados.

2. Clique em Adicionar.

3. Em Nome da Série, digite Ponteiro.

4. Para confirmar clique em OK duas vezes. Veja a figura 4.25:

Figura 4.25 – Criação do ponteiro.

5. Selecione o rótulo externo referente ao ponteiro.

6. Com o botão direito, clique na opção Alterar Tipo de Gráfico de Série.

7. Na opção Ponteiro, altere Rosca para Dispersão (penúltima opção) e o ponteiro será criado a partir dessa alteração, conforme a figura 4.26.

8. Clique no botão OK para confirmar.

Figura 4.26 – Criação do ponteiro.

SENAC SÃO PAULO | 93

EXCEL 2016 – CRIANDO PLANILHAS INTERATIVAS E DASHBOARD

Após confirmar, no lugar da fatia criada aparece um pequeno ponto que será o ponteiro. Para isso é necessário finalizar a configuração.

Figura 4.27 – Ponteiro.

1. Clique com o botão direito do mouse sobre o ponto e depois na opção Selecionar Dados.

2. Clique na opção Ponteiro e depois em Editar.

3. Em Nome da Série, deixe Ponteiro.

4. Em Valores de X da série, selecione o eixo X, referente à base 5. Em Valores de Y da série, selecione o eixo Y, referente à ponta.

6. Clique em OK para confirmar.

Figura 4.28 – Seleção dos eixos.

7. Com o botão direito, selecione o eixo da base e depois clique em Formatar Eixo.

8. No painel Formatar Eixo, altere Limites para Mínimo: -0,3; Máximo: 0,3. Observe a marcação com o número 1 na figura 4.29.

9. Repita o procedimento para o eixo 2.

Figura 4.29 – Configuração dos eixos.

94 | SENAC SÃO PAULO

EXCEL 2016 – CRIANDO PLANILHAS INTERATIVAS E DASHBOARD

Alguns exemplos de posição do ponteiro – gráfico velocímetro finalizado. Para alterar a posição do ponteiro foi criada uma barra de rolagem, com vínculo com a célula B9.

Figura 4.30 – Configuração dos eixos.

Crie outras planilhas com os dados desejados e simule várias situações com o gráfico tipo velocímetro, pois o passo a passo é um pouco longo.

GRÁFICO DASHBOARD – TERMÔMETRO COM BARRA DE ROLAGEM É comum nos gráficos Dashboard o uso do gráfico tipo termômetro, que é basicamente um gráfico Colunas 2D. Digite a planilha a seguir. Neste exemplo, a perspectiva da empresa é de que seu faturamento chegue ao valor máximo de 100, o valor alcançado será o realizado dividido pela meta (REALIZADO / META).

Formate a célula com a porcentagem; o realizado será encontrado por meio da barra de rolagem.

Fatura

Meta

100

Realizado

Alcançado

0%

Para criar a barra de rolagem: 1. Clique na guia Desenvolvedor, grupo Controles, opção Barra de Rolagem.

2. Arraste a barra de rolagem e deixe na posição vertical.

3. Pressione o botão direito do mouse sobre a barra, clique na opção Formatar Controle.

4. Em Valor mínimo, insira 0 (zero).

5. Em Valor máximo, insira 100.

6. Em Alteração incremental, insira 2.

SENAC SÃO PAULO | 95

EXCEL 2016 – CRIANDO PLANILHAS INTERATIVAS E DASHBOARD

7. Em Vínculo da célula, insira B6.

8. Clique em OK para confirmar.

Observe a figura 4.31. Deixe com essa formatação e a barra de rolagem já configurada, teste-a e observe o resultado na célula B6.

Figura 4.31 – Planilha formatada.

O gráfico tipo termômetro será criado a partir do valor realizado. Selecione o intervalo de A6 a B6 e insira o gráfico Colunas 2D. 1. Selecione a área de plotagem e deixe menor, mais estreita.

2. Clique no título e digite o sinal de igual (=); clique na célula B7 e pressione a tecla Enter.

3. Altere a cor da coluna para, por exemplo, amarela.

4. Delete o eixo horizontal.

5. Posicione o mouse sobre o eixo vertical, pressione o botão direito e clique em Formatar Eixo. Em Opções do Eixo, no painel à direita, opção Limites, deixe como: Mínimo 0 e Máximo 100. Feche o painel.

6. Clique na área de plotagem interna e altere a cor de preenchimento.

7. Clique na guia Design, grupo Layout de Gráfico, opção Adicionar Elemento Gráfico. Na opção Linhas de Grade, deixe Horizontal Secundário. Altere a cor da linha de grade.

Observe a figura 4.32. À medida que a barra de rolagem for deslizada, o valor realizado é alterado. O “termômetro” irá deslizar para mais ou para menos. A célula B3 foi calculada com a função SE, cujos resultados são símbolos com base nos critérios utilizados.

Figura 4.32 – Gráfico finalizado.

96 | SENAC SÃO PAULO

EXCEL 2016 – CRIANDO PLANILHAS INTERATIVAS E DASHBOARD

Crie outras planilhas com os dados desejados e outros gráficos tipo termômetro.

GRÁFICO DASHBOARD – SEMÁFORO COM BARRA DE ROLAGEM O gráfico tipo semáforo é uma pequena brincadeira, pois, na verdade, funciona por meio de símbolos e planilha de resumo. Neste exemplo, será usada a função SE, SOMASES, ESCOLHER e E. Digite a planilha conforme a figura 4.33 e insira um símbolo de elipse na célula M14.

1. Clique em uma célula qualquer e insira a barra de rolagem.

2. Clique com o botão direito e configure a barra para:

Valor atual: 1; Valor mínimo: 1; Valor máximo: 4; Vínculo da célula: H1.

3. Clique na célula I1 e digite a função =ESCOLHER(H1;M1;M2;M3;M4). O valor da célula H1 alterna para os produtos no intervalo de M1 até M4. À medida que a barra de rolagem for deslizada, os valores na célula I1 mudam de acordo com a posição da célula H1.

Figura 4.33 – Planilha para gráfico semáforo.

4. A célula D1 será: se o valor de B1 for igual ao valor de H1, o resultado será uma SOMASE, soma de N1 até N12. A primeira lista de critérios está na coluna L e o primeiro critério está na célula B1; a segunda lista de critérios está na coluna M e o primeiro critério da segunda lista está na célula C1. A sintaxe é =SE(B1=$H$1;SOMASES($N$1:$N$12;$L$1:$L$12;B1;$M$1:$M$12;C1);0). Arraste para as demais células.

5. A célula E1 será: se o valor de B1 for igual ao valor de H1 e o valor de C1 for igual ao valor de J1, o resultado será o valor de M14, senão o resultado será vazio. A sintaxe é: =SE(E(B1=$H$1; C1=$I$1);$M$14;“”). Arraste para as demais células. Observação: esse símbolo será utilizado para criar o semáforo. À medida que a barra de rolagem é deslizada, os valores da coluna D são alterados com base na fórmula acima.

Para simular um semáforo, será necessário usar uma caixa de texto e depois a formatação com os símbolos.

1. Clique na guia Inserir, grupo Ilustrações, opção Formas. Em Formas, clique em Caixa de texto.

Arraste com o mouse e deixe posicionada abaixo do texto Tablet, na célula G7.

SENAC SÃO PAULO | 97

EXCEL 2016 – CRIANDO PLANILHAS INTERATIVAS E DASHBOARD

Figura 4.34 – Caixa de texto.

Repita o procedimento para TV, iPad e Celular. Para configurar tipo semáforo: 2. Clique na Caixa de texto, depois na Barra de Fórmula e digite o sinal de igual (=). Clique na célula E1. No intervalo das células E1 a E4 está o símbolo de elipse necessário para a criação do gráfico. Como o símbolo não fica visível na coluna E, foi inserido na célula M14. Depois de finalizar o gráfico, oculte as colunas E, L, M e N. 3. Clique na Caixa de texto, depois na Barra de Fórmula e digite o sinal de igual (=). Clique na célula E2 e repita o procedimento para a caixa de texto de iPad e Celular.

Observe a figura 4.35. Clique na Caixa de texto abaixo de Tablet e formate conforme a figura: fonte Wingdings, tamanho 40 e cor verde. Para TV, repita o procedimento e deixe com a cor de fonte amarela; para iPad, deixe a cor vermelha; para Celular, deixe a cor cinza.

Figura 4.35 – Caixa configurada.

Oculte a coluna E e as colunas de L a N. Vá deslizando a barra de rolagem e observe o resultado.

Clique na célula G5 e digite =D1, repita para as células H5, I5 e J5.

Deslize com a barra de rolagem e observe o resultado.

98 | SENAC SÃO PAULO

EXCEL 2016 – CRIANDO PLANILHAS INTERATIVAS E DASHBOARD

Figura 4.36 – Planilha resolvida.

Crie outro gráfico do mesmo tipo, repita todo o procedimento usado nas figuras 4.33 e 4.34. Para configurar o semáforo: 1. Insira um retângulo com cantos arredondados e deixe com a cor preta.

2. Insira uma elipse e deixe com a cor cinza; em Efeitos de Forma, escolha o estilo Bisel e deixe como baixo relevo. Copie e cole mais três vezes.

3. Defina o alinhamento e o espaçamento entre as formas e posicione-as dentro do retângulo.

4. Insira uma caixa de texto e deixe selecionada; clique na Barra de Fórmula, digite o sinal de igual (=) e clique na célula E1. Repita para a demais caixas de texto com as células E2, E3 e E4.

5. Clique na célula C6 e digite =SE(D1>0;C1;“”). Se o valor de D1 for maior que zero, o resultado será o valor de C1, senão o resultado será considerado vazio (“”).

Para testar, deslize com a barra de rolagem. Para Tablet, a cor será amarela; para TV, será verde; para iPad, será vermelha; para Celular, será cinza.

Figura 4.37 – Segundo gráfico tipo semáforo.

SENAC SÃO PAULO | 99

EXCEL 2016 – CRIANDO PLANILHAS INTERATIVAS E DASHBOARD

GRÁFICO DASHBOARD – COMBINAÇÃO COM BARRA DE ROLAGEM Neste exemplo, será criado um gráfico também a partir de uma planilha de resumo, porém ele será formatado em três tipos diferentes. Para cada situação será apresentado um tipo de gráfico. Para calcular a planilha de resumo conforme a figura 4.38: 1. Função ESCOLHER: clique na célula J1 e digite =ESCOLHER(G1;O3;O4;O5). A célula G1 representa o índice, ou seja, a posição do valor na planilha. As células O3, O4 e O5 são os locais onde os nomes das cidades foram digitados, pois o Dashboard será alterado por cidade.

2. Crie uma barra de rolagem e posicione-a entre as células G1 e J1, ou onde desejar.

3. Pressione o botão direito sobre a barra de rolagem e clique na opção Formatar Controle.

4. Em Formatar Controle, configure: Valor mínimo: 1; Valor máximo: 3, pois são três cidades; Vínculo da célula: G1.

5. Clique em OK para confirmar.

Figura 4.38 – Planilha meses.

Para calcular com a função SOMASES, conforme a figura 4.39.

6. Clique na célula H3 e digite =SOMASES(C:C;$A:$A;$G3;$B:$B;$J$1). A sintaxe é: a coluna C é a soma do mês de janeiro, a coluna A é referente ao primeiro critério, a célula G3 é equivalente ao primeiro critério da planilha de resumo, a coluna B, ao segundo critério e a célula J1 é referente ao critério das cidades. À medida que a barra de rolagem é deslizada, o critério das cidades também é alterado e, consequentemente, os valores da planilha de resumo são alterados. Arraste para as demais células, ou seja, de janeiro até março.

Figura 4.39 – Planilha calculada.

100 | SENAC SÃO PAULO

EXCEL 2016 – CRIANDO PLANILHAS INTERATIVAS E DASHBOARD

Para criar o gráfico, selecione o intervalo de G2 até J6.

1. Clique na guia Inserir, grupo Gráficos, opção Gráfico 2D. 2. Arraste e posicione-o onde desejar na planilha.

3. Clique no título e digite igual (=), depois clique na célula J1.

4. Clique na coluna referente a janeiro, pressione o botão direito e clique na opção Alterar Tipo de Gráfico de Série.

Figura 4.40 – Alteração da série do gráfico.

Observe a figura 4.40. Na opção de Colunas Agrupadas, clique na setinha e no tipo de gráfico Barras Agrupadas, referente a janeiro. Em fevereiro, altere o tipo de gráfico para Linhas com Marcadores; em março, deixe com o tipo que está. Clique em OK para confirmar.

Deixe com a formatação desejada. Observe a planilha com o gráfico finalizado na figura 4.41. Na célula J1, a cidade é Campinas; a célula G1 é a posição encontrada a partir da barra de rolagem, dessa forma, quando a barra é deslizada, os valores da planilha de resumo são alterados e o gráfico também. Se algum valor for alterado na planilha, todo o restante também é.

Figura 4.41 – Planilha com o gráfico Dashboard finalizado.

SENAC SÃO PAULO | 101

EXCEL 2016 – CRIANDO PLANILHAS INTERATIVAS E DASHBOARD

Observe a figura 4.42. O valor da célula C3 foi modificado, consequentemente o valor da célula H3 foi recalculado e o gráfico ficou totalmente alterado em relação à cidade de Campinas.

Figura 4.42 – Gráfico alterado.

Para visualizar vários painéis, basta criar um gráfico para cada mês ou para cada produto ou para cada cidade, assim, quando a barra de rolagem é deslizada, todos os painéis são atualizados.

GRÁFICO DASHBOARD – GRÁFICOS 2016 COM PROCV Os gráficos a seguir são específicos da versão 2016. A planilha da figura 4.43 é a base de dados para a função PROCV e, consequentemente, para a criação de vários gráficos.

Figura 4.43 – Planilha de dados.

1. Crie uma planilha de resumo como mostra a figura 4.44.

2. Na célula I3, crie uma Validação de Lista. Os dados para a lista estão na coluna A.

102 | SENAC SÃO PAULO

EXCEL 2016 – CRIANDO PLANILHAS INTERATIVAS E DASHBOARD

Figura 4.44 – Planilha de dados.

3. Clique na célula J3 e calcule usando a função PROCV: =PROCV($I3;$A$3:$F$18;3). Pressione a tecla Enter e arraste para as demais células, até a N3.

Figura 4.45 – Planilha de dados.

Gráfico Treemap 1. Selecione o intervalo de J2 a N3.

2. Clique na guia Inserir, grupo Gráficos, opção Treemap.

3. As informações do gráfico são referentes à cidade de Hortolândia, com relação aos meses e ao total.

4. Ao alterar a caixa de listagem, o gráfico é recalculado automaticamente.

Figura 4.46 – Gráfico Treemap.

SENAC SÃO PAULO | 103

EXCEL 2016 – CRIANDO PLANILHAS INTERATIVAS E DASHBOARD

Gráfico Cascata 1. Selecione o intervalo de J2 a N3.

2. Clique na guia Inserir, grupo Gráficos, opção Cascata.

3 As informações do gráfico são referentes à cidade de Campinas, com relação aos meses e ao total.

4. Ao alterar a caixa de listagem, o gráfico é recalculado automaticamente.

Figura 4.47 – Gráfico Cascata.

Gráfico Caixa Estreita 1. Selecione o intervalo de J2 a N3.

2. Clique na guia Inserir, grupo Gráficos, opção Caixa Estreita.

3. As informações do gráfico são referentes à cidade de Sumaré, ao produto TV, aos meses e ao total.

4. Ao alterar a caixa de listagem, o gráfico é recalculado automaticamente.

Figura 4.48 – Gráfico Cascata.

104 | SENAC SÃO PAULO

EXCEL 2016 – CRIANDO PLANILHAS INTERATIVAS E DASHBOARD

EXERCÍCIOS PROPOSTOS Um determinado usuário trabalha em uma empresa de câmbio, com venda e compra de outras moedas. Ele, então, criou uma planilha com gráficos interativos para calcular os valores e analisar com mais rapidez as informações. Digite a planilha abaixo, formate e altere o nome para Cotação:

Figura 4.49 – Atividade 1, capítulo 4.

1. Calcule a coluna D, cujo resultado será os valores em reais da coluna B, que serão convertidos para euro e dólar. Para essa conversão será usada a função SE.

1.1. S intaxe. Primeira condição: se o valor da célula C6 for igual ao valor da célula A2, então o resultado será o valor da célula B6 dividido pela cotação da célula A3. Segunda condição: se o valor da célula C6 for igual ao valor da célula B2, então o resultado será o valor da célula B6 dividido pela cotação da célula B3, senão será o valor da célula B6 dividido pela cotação da célula C3. Conforme a figura 4.50 : =SE(C6=$A$2;B6/$A$3;SE(C6=$B$2;B6/$B$3;B6/$C$3)).

Figura 4.50 – Planilha resolvida.

SENAC SÃO PAULO | 105

EXCEL 2016 – CRIANDO PLANILHAS INTERATIVAS E DASHBOARD

2. Para criar uma planilha de resumo:

2.1. Insira uma nova planilha (guia ou aba) e altere o nome para Cotação_Resumo.

2.2. Digite as informações como na figura 4.51.

Figura 4.51 – Exercício planilha de resumo.

3. Calcule conforme segue:

3.1. C lique na célula F1 e digite: =ESCOLHER(H1;A1;A2;A3). A célula H1 será o índice e as demais serão o conteúdo das células A1, A2 e A3.

3.2. C lique na célula B5 e digite: =SE($F$1=$A$2;$F$1;$B$1). Se o conteúdo da célula F1 for igual ao conteúdo da célula A2, o resultado será o conteúdo de F1, senão será o conteúdo da célula B1. Após calcular, arraste para C5 e D5. O título das colunas não será digitado para que o resumo possa ser interativo e os gráficos sejam do tipo Dashboard.

3.3. P ara criar a planilha de resumo serão usadas as funções SE, SOMASE e MÉDIASE, de tal maneira que seja criado um resumo a partir da planilha Cotação.

3.3.1. Primeira sintaxe, célula B6:

=(SE($B$5=$F$1;SOMASE(Cotação!$C$6:$C$16;B5;Cotação!$D$6:$D$16);MÉDIASE(Cotação!$C$6:$C$16; ‘Cotação_Resumo (2)’!$F$1;Cotação!$D$6:$D$16))). Se o valor da célula B5 for igual ao valor da célula F1, então o resultado será uma SOMASE, senão o resultado será uma MÉDIASE. Arraste para as demais colunas à direita.

3.3.2. Segunda sintaxe, célula B7:

= SEERRO(MÉDIASE(Cotação!$C$6:$C$16;B5;Cotação!$D$6:$D$16);0). Arraste para as demais colunas à direita.

3.4. Caixa com botão de opção:

3.4.1. C rie um retângulo a partir das formas, abaixo da planilha de resumo, e formate com uma das formatações de Estilos de Forma. Insira sobre o retângulo uma Caixa de Grupo e altere o rótulo para Cotação.

3.4.2. I nsira três botões de opção: altere o rótulo do primeiro para Turismo, do segundo para Comercial e do terceiro para Euro.

3.4.3. P ara criar os controles para os botões de opção, pressione com o botão direito do mouse sobre o primeiro botão de opção, clique em Formatar Controle e depois na guia Controle.

Em opção Vínculo da Célula, clique na célula H1. Verifique se os demais botões assumiram o mesmo controle, ou seja, célula H1.

Faça um teste clicando nos botões de opção. Ao acionar cada um deles, os dados da planilha de resumo são alterados automaticamente. Faça a leitura das fórmulas.

106 | SENAC SÃO PAULO

EXCEL 2016 – CRIANDO PLANILHAS INTERATIVAS E DASHBOARD

4. Serão criados dois gráficos Dashboard. O primeiro irá apresentar os dados da soma em relação ao dólar comercial, turismo e euro. O segundo, os dados incluindo a soma e a média da planilha de resumo.

4.1. Primeiro gráfico:

4.1.1. S elecione o intervalo de A5 até D6. Insira o gráfico Colunas 2D Agrupadas. Deixe posicionado ao lado. O título será igual à célula F1. Em Rótulos de Dados, deixe como Extremidade Externa. Formate as colunas da maneira desejada.

4.2. Segundo gráfico:

4.2.1. S elecione o intervalo de A5 até D7. Insira o gráfico tipo Combinação Linha no Eixo Secundário. Deixe posicionado ao lado. O título será igual à célula F1. Em Rótulos de Dados, deixe como Extremidade Externa. Formate as colunas e a linha da maneira desejada.

Faça um teste clicando nos botões de opção. Ao acionar cada um deles, os dados da planilha de resumo são alterados automaticamente e, por consequência, também os gráficos. Faça vários testes e confira o resultado.

A figura 4.52 apresenta o exercício resolvido. Como Euro está assinalado no botão de opção, as informações referentes à planilha de resumo e aos gráficos apresentam a soma e a média das compras de euro que estão na planilha Cotação.

Figura 4.52 – Planilha resolvida.

SENAC SÃO PAULO | 107

5. Macros

INTRODUÇÃO

O

intuito não é trabalhar com programação, pois esta apostila não aborda o Visual Basic, mas, sim, apresentar uma forma simples de trabalhar com o Excel, deixando as planilhas e gráficos mais dinâmicos, interativos e lúdicos, como foi abordado nos capítulos anteriores. Então as macros serão gravadas de modo simples.

Macro é um conjunto de ações ou uma sequência de comandos gravados, que ficam armazenados no editor do Visual Basic. O objetivo de macros é otimizar o máximo possível rotinas diárias, fazendo com que o usuário ganhe tempo e rapidez nos trabalhos rotineiros.

Muitos comandos executados manualmente por digitação podem ser resolvidos e dinamizados com a gravação de macros.

CRIAR MACROS Para criar macros é necessário ativar a guia Desenvolvedor. Como as macros obedecem a uma sequência de comandos ou ações, primeiro é necessário planejar o que deseja executar, porque durante o processo de gravação é importante seguir exatamente o que será executado posteriormente. Por exemplo: quando é usado o formato em negrito, ao clicar no comando, ele apenas deixa o texto com esse estilo, não executa mais nada. Se o usuário quiser deixar o texto em negrito e itálico, é preciso clicar nos dois comandos ou atalhos (Ctrl + N para bold e Ctrl + I para itálico). Este é um exemplo de macros que serão criadas.

SENAC SÃO PAULO | 109

EXCEL 2016 – CRIANDO PLANILHAS INTERATIVAS E DASHBOARD

Para criar macros, o documento utilizado será Exercício_Capítulo_5.

Passo a passo para criar macros 1. Abrir o documento Exercício_Capítulo_5.

2. Clique na planilha Botões e depois na guia Desenvolvedor.

Primeiramente, as macros terão por finalidade navegar pelas planilhas do documento. Ao criar macros, é necessário observar alguns critérios: ao digitar o nome, não é permitido que tenha espaço; também não são aceitos caracteres somente numéricos; durante o processo de gravação, grave somente o que será executado. O ponto de partida para a criação de macros será a planilha Botões, conforme a figura 5.1. A macro criada irá abrir a planilha Cadastro.

Figura 5.1 – Janela guia Desenvolvedor.

3. Clique na planilha com o nome de Botões.

4. Clique na guia Desenvolvedor, grupo Código, opção Gravar Macro.

5. Em Nome da macro, digite Cadastro. Para confirmar, clique no botão OK.

Figura 5.2 – Janela Gravar macro.

A partir desse momento, o gravador de macro está acionado, então execute apenas o necessário.

Neste exemplo, a finalidade é criar um caminho entre a planilha Botões e a planilha Cadastro. A figura 5.3 mostra a opção de gravador de macro. Onde antes estava como Gravar macro agora está como Parar gravação, porém antes de parar a gravação é preciso executar todo o caminho que será gravado.

110 | SENAC SÃO PAULO

EXCEL 2016 – CRIANDO PLANILHAS INTERATIVAS E DASHBOARD

Figura 5.3 – Opção de gravação de macro.

6. Clique na planilha Cadastro e em Parar gravação.

7. Clique na planilha Botões.

A próxima macro a ser criada irá abrir a planilha 01. Siga os procedimentos utilizados na criação da macro anterior.

8. Na planilha Botões, clique na guia Desenvolvedor, grupo Código, opção Gravar macro.

9. Em nome, digite 01.

Como foi comentado anteriormente, caractere numérico não é aceito em nome de macro, então clique no botão OK e digite o nome correto, utilizando caracteres alfabéticos e sem espaço. Pode digitar tudo com letras minúsculas e sem acento, pois o nome é de controle, não ficará visível. É importante digitar um nome que tenha alguma relação com o que está sendo gravado.

Figura 5.4 – Nome inválido.

10. Como nome de macro digite um para representar a planilha 01.

11. Clique na planilha 01 e depois em Parar gravação.

12. Clique na planilha Botões.

13. Repita o procedimento para as outras planilhas.

Após criar as macros, ou antes, é necessário salvar a planilha com a alteração do tipo. Em todos os documentos salvos no MS Excel 2016, a extensão padrão é xlsx. No entanto, ao criar as macros, se a extensão não for alterada e o documento for fechado, elas serão excluídas, então, ao salvar o arquivo, altere o tipo para Pasta de Trabalho Habilitada para Macro do Excel (extensão xlsm).

SENAC SÃO PAULO | 111

EXCEL 2016 – CRIANDO PLANILHAS INTERATIVAS E DASHBOARD

CRIAR BOTÕES Os botões são objetos que têm por finalidade gerar uma associação entre a macro criada e o que será executado. Ao clicar, o botão irá executar toda a rotina gravada. Esses botões podem ser o Botão (controle de formulário) – que é padrão –, uma forma ou uma imagem (foto).

Botões de execução de macros 1. Clique na planilha Botões.

2. Clique na guia Desenvolvedor, grupo Controles, opção Inserir, opção Botão (Controle de Formulário), conforme a figura 5.5:

Figura 5.5 – Botão de controle.

Ao clicar no botão, arraste o mouse sobre a tela e solte. Ao soltar o mouse, será criado um retângulo e em seguida a janela que contém todas as macros que foram criadas.

3. Clique na macro Cadastro e depois no botão OK para confirmar.

4. Em seguida, digite o rótulo do botão que irá executar a macro; como nome, digite Cadastro. Dê um clique fora do botão.

Figura 5.6 – Janela para associação de macro.

Ao clicar no botão, a macro será executada, ou seja, irá para a planilha Cadastro. Repita o procedimento para todas as macros criadas.

Figura 5.7 – Botão de execução de macro.

112 | SENAC SÃO PAULO

EXCEL 2016 – CRIANDO PLANILHAS INTERATIVAS E DASHBOARD

Crie os botões sem se preocupar com o tamanho e com a posição na tela. Observe a figura 5.8.

Para selecionar os botões já associados às macros, é necessário usar a tecla Ctrl. Caso contrário, ao clicar no botão, a macro será executada. Pressione a tecla Ctrl e clique com o mouse no botão desejado ou em todos os botões.

Figura 5.8 – Janela com os botões.

Tamanho dos botões Após inserir todos os botões e associar cada um a uma macro específica e com os rótulos digitados, é hora de acertar as posições e o tamanho para que fiquem padronizados. Para selecionar os botões: 1. Pressione a tecla Ctrl e clique em todos.

2. Posicione o mouse sobre um deles e clique com o botão direito.

3. Clique na opção Formatar controle, guia Tamanho.

4. Em Altura, digite 2; em Largura, digite 5, ou o tamanho desejado.

5. Para confirmar, clique no botão OK.

Figura 5.9 – Janela de controle de botões – guia Tamanho.

SENAC SÃO PAULO | 113

EXCEL 2016 – CRIANDO PLANILHAS INTERATIVAS E DASHBOARD

Para alterar a posição do botão ou arrastar para outro ponto da tela, primeiro pressione a tecla Ctrl, dê um clique, solte o mouse e arraste. Se mantiver a tecla Ctrl pressionada, o objeto será duplicado.

Alinhar os botões Para alinhar os botões, selecione por colunas ou por linhas (os que estão posicionados na vertical ou horizontal). Após selecionar: 1. Clique na guia Layout da Página, grupo Organizar, opção Alinhar.

2. Na figura 5.10, a seleção está na horizontal, clique na opção Alinhar Parte Superior.

3. Clique em Distribuir na Horizontal (definição do mesmo espaçamento entre os botões).

Figura 5.10 – Botões alinhados.

Macro de retorno e atalho Ao navegar pelas planilhas por meio dos botões de macros, é necessário retornar à planilha Botões (neste exemplo, é o ponto de partida). Para criar uma macro de retorno, ou seja, de qualquer planilha sempre retornar à planilha Botões, é preciso clicar em qualquer uma, menos na planilha Botões.

1. Clique em qualquer uma das planilhas (menos na Botões).

2. Clique na guia Desenvolvedor, grupo Código, opção Gravar macro.

3. Em Nome da macro, digite Retornar.

4. Em Tecla de Atalho, opção Ctrl +, digite a letra R. Este será o atalho para retornar à planilha Botões.

5. Para confirmar, clique no botão OK. 6. Clique na planilha Botões e depois na opção Parar gravação.

Para testar a macro de atalho, clique em qualquer uma das planilhas e pressione o atalho Ctrl + R. Por exemplo, clique no botão da planilha 03 e em seguida use o atalho para retornar à planilha Botões.

Para não utilizar somente o atalho, crie um botão na planilha cadastro. O botão não será o padrão, mas uma forma.

1. Clique na planilha Cadastro.

2. Clique na guia Inserir, grupo Ilustrações, opção Formas.

3. Clique na forma desejada, posicione o mouse na planilha e arraste.

4. Formate na guia Formatar, opção Estilos de Forma.

5. Digite como rótulo Voltar para Botões e deixe o texto centralizado e alinhado ao meio.

114 | SENAC SÃO PAULO

EXCEL 2016 – CRIANDO PLANILHAS INTERATIVAS E DASHBOARD

Atribuir macro a uma forma 1. Clique com o botão direito sobre a forma.

2. Clique na opção Atribuir Macro.

3. Clique na macro Retornar e depois no botão OK para confirmar.

4. Copie o botão e cole nas planilhas. Para executar a macro de retorno, clique no botão ou use o atalho (Ctrl + R).

Excluir macro É comum criar uma macro errada, que não funcione. Neste caso, é importante que ela seja excluída.

Para excluir macros: 1. Clique na guia Desenvolvedor, grupo Código, opção Macros.

2. Clique no nome da macro que será excluída e depois no botão Excluir. Confirme a exclusão no botão OK.

Figura 5.11 – Janela opção Macros para exclusão.

MACRO DE CÁLCULO Como a função de uma macro é criar rotinas, é possível criar macros para calcular, para atualizar planilhas, para formatar, para limpar formatações e assim por diante. Para criar uma macro com fórmula, clique na planilha 08, que já possui a função SEERRO e ESCOLHER na célula H1. Possui também os botões de opção devidamente configurados, conforme a figura 5.12. Serão criadas as seguintes macros: uma para calcular a SOMA, outra para calcular a MÉDIA e outra para calcular a SOMASE, com o critério Chuveiro.

SENAC SÃO PAULO | 115

EXCEL 2016 – CRIANDO PLANILHAS INTERATIVAS E DASHBOARD

Macro Soma

Figura 5.12 – Planilha para macro de fórmula.

1. Clique em qualquer célula da planilha.

2. Clique na guia Desenvolvedor, grupo Código, opção Gravar macro.

3. Em Nome da macro, digite soma e clique no botão OK para confirmar.

4. Clique na célula E16.

5. Clique na guia Fórmulas, opção AutoSoma e pressione a tecla Enter para efetuar a soma.

6. Clique em Parar gravação.

Macro Média 1. Clique em qualquer célula da planilha.

2. Clique na guia Desenvolvedor, grupo Código, opção Gravar macro.

3. Em Nome da macro, digite media e clique no botão OK para confirmar.

4. Clique na célula E16.

5. Na guia Fórmulas, opção AutoSoma, clique na setinha para baixo e em Média, pressione a tecla Enter para efetuar a Média.

6. Clique em Parar gravação.

Macro SomaSe 1. Clique em qualquer célula da planilha.

2. Clique na guia Desenvolvedor, grupo Código, opção Gravar macro.

3. Em Nome da macro, digite somase e clique no botão OK para confirmar.

4. Clique na célula E16 e digite =SOMASE(B4:B15;H1;E4:E15). Pressione a tecla Enter para confirmar.

5. Clique em Parar gravação.

116 | SENAC SÃO PAULO

EXCEL 2016 – CRIANDO PLANILHAS INTERATIVAS E DASHBOARD

Criar botões de macro de cálculo Insira três formas na planilha e formate, em seguida associe uma macro a cada forma (Soma, Média e SomaSE). Ao clicar no botão referente à soma, será calculada a soma da coluna E na célula E16; ao clicar no botão da média, será calculada a média; e ao clicar no botão SomaSE, será calculada a soma referente ao produto chuveiro.

Clique na macro SomaSE, o valor da célula E16 será a soma em relação a chuveiro. Na sequência, clique no Botão de Opção (Nulo) e observe que o resultado da célula E16 ficou vazio. Ao clicar no Botão de Opção (Chuveiro), o total é calculado.

Figura 5.13 – Planilha finalizada.

DESATIVAR PLANILHAS Para deixar o documento mais profissional e com a aparência mais limpa, as planilhas serão desativadas. Desativar não é ocultar. Ao alterar a configuração, todas as planilhas ficam ocultas. Para desativar as planilhas: 1. Clique na planilha Botões.

2. Clique na guia Arquivo e em Opções do Excel.

3. Clique na opção Avançado e, no painel à direita, desative a caixa Mostrar guias de planilha.

4. Clique no botão OK para confirmar.

Figura 5.14 – Janela de configuração.

SENAC SÃO PAULO | 117

EXCEL 2016 – CRIANDO PLANILHAS INTERATIVAS E DASHBOARD

A figura 5.15 apresenta o painel com os botões de macros. Sempre que o documento for aberto, esta é a tela de abertura. Para acessar as planilhas, o usuário é obrigado a utilizar as macros.

Figura 5.15 – Planilha com as guias desativadas.

Se for necessário habilitar as planilhas novamente, basta refazer o processo e assinalar a opção Mostrar guias de planilha.

EXERCÍCIO PROPOSTO Crie uma planilha de sua área de atuação ou da área desejada, com cálculos e gráficos. Digite os dados, formate e calcule. Em seguida, crie algumas macros e os botões, associe as macros aos botões e salve com o tipo Pasta de Trabalho Habilitada para Macro do Excel.

Exercício livre.

118 | SENAC SÃO PAULO

6. Exercícios

complementares

INTRODUÇÃO

O

objetivo deste capítulo é sugerir uma série de exercícios práticos. Embora a apostila trate cada assunto por meio de planilhas – pois não é um livro teórico –, é interessante que contenha sugestões práticas em relação ao que foi abordado nos capítulos anteriores. Cada exercício apresentará uma situação hipotética. Vale lembrar que os dados ou valores não são reais, servem apenas como sugestão para que o exercício seja resolvido.

EXERCÍCIO 1 – CAIXA DE COMBINAÇÃO Você trabalha em uma agência de turismo e precisa calcular os valores das viagens. Neste caso, os valores estão em euro e dólar e é preciso converter para o real. Para isso: 1. Digite a planilha da figura 6.1 e formate. Salve com o nome Viagens.

2. Clique na célula A2 e calcule com a função ESCOLHER: =ESCOLHER(B2;A13;A14). A célula B2 é referente ao índice e as células A13 e A14 são referentes a dólar e euro, respectivamente.

3. Clique na célula C2 e calcule com a função SE: =SE(B2=1;B13;B14). Se o valor da célula B2 for igual a 1, o resultado será o valor da célula B13, senão o resultado será o valor da célula B14.

4. Crie uma caixa de combinação e configure:

4.1. B otão direito do mouse, clique em Formatar Controle.

4.2. E m Intervalo de entrada, selecione o intervalo de A13 a A14.

4.3. E m Vínculo da célula, insira B2. Posicione a caixa onde desejar.

SENAC SÃO PAULO | 119

EXCEL 2016 – CRIANDO PLANILHAS INTERATIVAS E DASHBOARD

Figura 6.1 – Planilha Viagens.

O resultado da coluna D será: se o valor da célula B4 e das demais for igual ao valor da célula A2, o resultado será a célula C4 multiplicada por C2, senão o resultado será 0 (zero).

1. Oculte as linhas 13 e 14.

2. Salve novamente o documento.

3. Teste o resultado.

Ao alterar a moeda pela caixa de combinação, os valores da coluna D são recalculados automaticamente. A figura 6.2 apresenta o resultado em relação ao dólar; ao alterar para euro, o resultado será referente aos valores em euro.

Figura 6.2 – Planilha Viagens calculada.

EXERCÍCIO 2 – BARRA DE ROLAGEM Você trabalha em uma empresa de produtos agrícolas. Dentre as planilhas que precisa calcular, a figura 6.3 apresenta a seguinte situação: plantação e colheita por hectare, de forma que será necessário calcular os custos com a plantação e com a colheita, o total de custos e o lucro.

1. Digite a planilha da figura 6.3 e formate. Salve com o nome Agrícola.

2. Clique na célula C2 e calcule com a função ESCOLHER: =ESCOLHER(B1;K6;K7;K8;K9). A célula B1 é referente ao índice e as células K6, K7, K8 e K9 são referentes aos produtos em questão.

3. Crie uma barra de rolagem e configure para:

3.1. Valor atual: 1.

120 | SENAC SÃO PAULO

EXCEL 2016 – CRIANDO PLANILHAS INTERATIVAS E DASHBOARD

3.2. Valor mínimo: 1.

3.3. Valor máximo: 4 (relacionado aos quatro produtos).

3.4. Alteração incremental: 10.

3.5. Vínculo da célula: B1.

4. Clique em OK para confirmar.

Figura 6.3 – Planilha digitada.

Para calcular a planilha, as seguintes funções serão usadas: SOMA, SE e PROCV. • Resultado da coluna C (Custo Plantação/Custo): se o valor da célula A4 e das demais for igual ao valor da célula C1, o resultado será a PROCV de K6 a M9. Esse intervalo representa a base de dados e o número 2 significa que, ao entrar com o valor na célula A4, esse valor será procurado na base de dados, retornará ao valor da coluna 2 e será multiplicado pela quantidade de hectares da coluna B. Caso contrário, o valor será somente o que está na coluna 2 da base de dados: =SE($A4=$C$1;PROCV($A4;$K$6:$M$9;2)*$B4;PROCV($A4;$K$6:$M$9;2)).

• Resultado da coluna D (Custo Colheita/Custo): será a mesma função usada na coluna C, só que a coluna referente ao PROCV será a 3.

• Resultado da coluna E (Total de Custos): se o valor da célula A4 e das demais for igual ao valor da célula C1, então o resultado será a soma de C4 a D4, senão será 0 (zero): SE(A4=$C$1; SOMA(C4:D4);0).

• Resultado da coluna F (Preço de Venda): será o total da coluna E em relação ao lucro desejado de 20%, na célula L4: =E4+E4*$L$4.

• Resultado da coluna G (Lucro): será a diferença entre o Preço de Venda e o Custo Total: =F4-E4.

• Formatação condicional: para formatar a célula C1 em relação aos produtos, use cor de preenchimento. Defina uma cor para cada produto, por exemplo: Café, verde, Milho, amarela, Trigo, azul, Soja, laranja. À medida que a barra de rolagem for deslizada, as cores são alteradas.

• Formatação condicional para o intervalo de C4 a G9. Se a célula C1 for igual a Café (verde), para toda a planilha, se for igual a Milho (amarela), se for igual a Trigo (azul) e se for igual a Soja (laranja).

Ao deslizar a barra de rolagem, a cor de preenchimento é alterada tanto na célula C1 quanto no intervalo de C4 a G9.

A figura 6.4 apresenta a planilha calculada. No exemplo, o resultado tem por base o produto Trigo. Ao deslizar a barra de rolagem, observe os valores dos outros produtos.

Figura 6.4 – Planilha calculada.

SENAC SÃO PAULO | 121

EXCEL 2016 – CRIANDO PLANILHAS INTERATIVAS E DASHBOARD

EXERCÍCIO 3 – FUNÇÕES SE E MÉDIASE Você trabalha em uma empresa responsável pelo controle de captação de água, ou seja, o índice de chuva em alguns pontos específicos como em rios, represas e lagos. O controle é feito no Excel.

1. Digite a planilha da figura 6.5 e formate. Salve com o nome Pluviômetro.

Figura 6.5 – Planilha digitada.

2. Crie um botão de rotação e configure para:

2.1. Valor atual: 1.

2.2. Valor mínimo: 1.

2.3. Valor máximo: 200.

2.4. Vínculo da célula: H1.

3. Clique na célula I6 e calcule com a função SE: =SE($H$1>=MÉDIASE($B$4:$B$15;H6; $C$4:$E$15);SOMASE($B$4:$B$15;H6;$C$4:$E$15);MÉDIA($C$4:$E$15)).

Observe a sintaxe acima: se o valor da célula H1 for maior ou igual ao resultado da função MÉDIASE, então o resultado será a função SOMASE; senão, o resultado será apenas a função MÉDIA. Para testar, use a barra do botão de rotação. À medida que o valor da célula H1 for alterado por meio do botão de rotação, os valores da planilha de resumo também serão alterados para SOMASE ou MÉDIASE. Formatação condicional: 1. Selecione o intervalo de C4 a E15.

2. Clique na guia Página Inicial, grupo Estilo, opção Formatação Condicional.

3. Clique em Gerenciar Regras, opção Nova Regra.

4. Clique em Formatar apenas células que contenham.

5. Na opção condicional, deixe a condição Igual a e o valor da condição 0 (zero).

6. Clique no botão Formatar, opção Preenchimento, cor Vermelha.

7. Clique no botão OK duas vezes.

8. Insira uma Nova Regra, condição Maior ou igual a e clique na célula H1.

9. Clique no botão Formatar, opção Preenchimento, cor Azul.

10. Clique no botão OK duas vezes.

11. Insira uma Nova Regra, condição Menor do que e clique na célula H1.

12. Clique no botão Formatar, opção Preenchimento, cor Verde.

13. Clique no botão até retornar à planilha.

122 | SENAC SÃO PAULO

EXCEL 2016 – CRIANDO PLANILHAS INTERATIVAS E DASHBOARD

Ao clicar no botão de rotação, a planilha de resumo é recalculada e as cores na planilha-matriz são alteradas.

Figura 6.6 – Planilha resolvida.

EXERCÍCIO 4 – FUNÇÕES PROCV E SOMASE Determinado usuário trabalha no estoque de uma papelaria e usa o Excel para controlar a entrada e a saída de cada material. Ao entrar com esses valores, o estoque é atualizado. No exemplo da figura 6.7, os dados são inseridos à medida que há entrada de material. Essa planilha é a base de dados.

1. Digite a planilha da figura 6.7, calcule a coluna E conforme o exemplo e arraste para as demais células.

2. Renomeie a planilha como Papelaria.

Figura 6.7 – Planilha digitada.

Na sequência, será inserida uma nova aba para que sejam criadas duas planilhas de resumo com as funções PROCV e SOMASE. Renomeie como Papelaria_Resumo.

SENAC SÃO PAULO | 123

EXCEL 2016 – CRIANDO PLANILHAS INTERATIVAS E DASHBOARD

Figura 6.8 – Aba Papelaria_Resumo.

1. Selecione o intervalo de A4 a A8 e crie uma lista de Validação de Dados.

2. A lista da validação será criada a partir do intervalo de A2 a A19 da planilha Papelaria.

3. Clique na célula B4 e digite =PROCV($A4;Papelaria!$A:$E;2). O valor de entrada a partir da célula A4 será procurado na planilha Papelaria nos intervalos das colunas A até E e o valor de retorno está na coluna 2 da base de dados. Arraste para as demais colunas e altere o número das colunas para 3, 4 e 5, conforme a figura 6.9:

Figura 6.9 – Função PROCV.

4. Clique na célula C12 e digite =SOMASE($B$4:$B$7;$B12;C$4:C$7), ou seja, SOMASE do intervalo de B4 a B7, critério célula B12 e intervalo da soma de C4 a C7. Arraste para as demais colunas.

Quando um dos valores da coluna A, células validadas, for alterado, os valores da planilha com a função SOMASE também serão alterados.

Figura 6.10 – Planilha calculada.

124 | SENAC SÃO PAULO

EXCEL 2016 – CRIANDO PLANILHAS INTERATIVAS E DASHBOARD

EXERCÍCIO 5 – FUNÇÃO PROCH E BARRA DE ROLAGEM Você trabalha em uma livraria e criou um relatório das vendas referentes aos meses de janeiro a dezembro. Na planilha de resumo ao lado será usada a função PROCH, mostrando a quantidade de cada mês por linha.

Figura 6.11 – Planilha Livraria.

1. Crie uma barra de rolagem e configure para:

1.1. Valor atual: 1.

1.2. Valor mínimo: 2 (pois o PROCH começa a contar a partir da linha 2).

1.3. Valor máximo: 8 (é a quantidade de linhas).

1.4. Vínculo da célula: O1.

Clique na célula P3 e digite =PROCH(O3;$B$2:$M$9;$O$1). O valor da célula O3 e das demais será procurado no intervalo de B2 a M9 e o número da linha procurada está na célula O1. Para que o PROCH funcione, os rótulos da linha 2 precisam ser organizados ou classificados em ordem crescente, por isso os meses não estão na sequência.

Conforme a figura 6.12, ao deslizar a barra de rolagem, o valor da célula O1 é alterado e, consequentemente, os valores da coluna P são alterados de acordo com o número da linha. No exemplo, está a linha 6; os valores da coluna P dizem respeito a todos os valores, de todos os meses que estão nessa linha.

Figura 6.12 – Planilha calculada.

EXERCÍCIO 6 – FUNÇÃO SE Determinado usuário é responsável pelo controle do pessoal que utiliza os recursos de informática nos relatórios mensais. Para cada funcionário, é atribuída uma pontuação de 0 a 10 em relação ao uso dos recursos, pois muitos funcionários ainda usam papel para criar seus relatórios.

SENAC SÃO PAULO | 125

EXCEL 2016 – CRIANDO PLANILHAS INTERATIVAS E DASHBOARD

1. Digite a planilha da figura 6.13. Os valores da coluna B não foram digitados. Crie uma lista de validação a partir dos valores que estão na coluna G. Após validar, oculte a coluna G.

Figura 6.13 – Planilha TI.

2. Calcule a coluna C: célula B4 dividida pela célula C11, e arraste para as demais células.

3. Calcule a coluna D: =SE(C4>=$A$14;$A$15;$A$16). Se o valor da célula C4 for maior ou igual ao valor da célula A14, o resultado será o valor da célula A15, senão o resultado será o valor da célula A16.

Ao alterar os valores nas células da coluna B pela validação, os valores das colunas C e D também são alterados.

Figura 6.14 – Planilha resolvida.

EXERCÍCIO 7 – VÁRIOS GRÁFICOS DASHBOARD A partir das planilhas trabalhadas, será criada uma série de gráficos. Esses gráficos serão interativos em razão da interatividade das planilhas calculadas.

Gráfico 1: 1. Abra a planilha Viagens, crie um gráfico tipo rosca e configure para que fique com a aparência de um velocímetro.

2. Selecione o intervalo de A3 a A10 e de D3 a D10 e crie o gráfico.

3. Configure conforme foi abordado em capítulos anteriores.

126 | SENAC SÃO PAULO

EXCEL 2016 – CRIANDO PLANILHAS INTERATIVAS E DASHBOARD

Ao alterar de euro para dólar, os valores do gráfico são alterados. Ao alterar o valor da célula A11, Posição, a posição do ponteiro também é alterada.

Figura 6.15 – Gráfico resolvido.

Gráfico 2: 1. Abra a planilha Agrícola e crie um gráfico tipo Colunas 2D. 2. Selecione o intervalo de A3 a A9 e de C3 a D9 e crie o gráfico.

3. Configure da forma desejada.

Ao deslizar a barra de rolagem, automaticamente a planilha é alterada e, por consequência, o gráfico também, interativo ou Dashboard.

4. Crie outro gráfico tipo combinação.

5. Selecione o intervalo de A3 a A9 e de E3 a G9 e crie o gráfico.

6. Configure da forma desejada.

Figura 6.16 – Gráfico resolvido.

Gráfico 3: 1. Abra a planilha Pluviômetro e crie um gráfico tipo Barra 3D. 2. Selecione o intervalo de H6 até I8 e crie o gráfico.

3. Configure da forma desejada.

Ao deslizar o botão de rotação, automaticamente a planilha é alterada e, por consequência, o gráfico também, interativo ou Dashboard.

SENAC SÃO PAULO | 127

EXCEL 2016 – CRIANDO PLANILHAS INTERATIVAS E DASHBOARD

Para formatar as barras, foram criadas imagens por meio de formas, salvas como JPG. As imagens foram inseridas na opção de preenchimento de formas. Para cada rótulo, há uma imagem diferente.

Figura 6.17 – Planilha com gráfico.

Gráfico 4: 1. Abra a planilha Papelaria_Resumo e crie um gráfico tipo Colunas 3D. 2. Selecione o intervalo de B11 a E16 e crie o gráfico.

3. Configure da forma desejada ou, em Formatar Série de Dados, use uma das opções Forma da Coluna (quadrado, pirâmide, cilindro, etc.).

Ao alterar as validações pela coluna A, os valores da planilha de resumo são alterados e, consequentemente, o gráfico também.

Figura 6.18 – Gráfico resolvido.

Gráfico 5: 1. Abra a planilha Livraria e crie um gráfico tipo linhas.

2. Selecione o intervalo de O2 a P14 e crie o gráfico.

3. Configure do modo desejado ou altere o estilo de linha, cor, marcadores, etc.

Ao deslizar a barra de rolagem, os valores da planilha de resumo são alterados e, consequentemente, o gráfico também é.

128 | SENAC SÃO PAULO

EXCEL 2016 – CRIANDO PLANILHAS INTERATIVAS E DASHBOARD

Figura 6.19 – Gráfico resolvido.

Gráfico 6: 1. Abra a planilha TI e crie um gráfico tipo linhas.

2. Selecione o intervalo de A3 a B11 e crie o gráfico.

3. Configure do modo desejado ou altere o estilo de linha, cor, marcadores, etc.

4. Em Formatar Série de Dados, deixe como Eixo Secundário.

Ao alterar as validações pela coluna B, os valores da coluna C são alterados e, consequentemente, o gráfico também.

Figura 6.20 – Gráfico resolvido.

EXERCÍCIO 8 – DASHBOARD: TERMÔMETRO Neste exercício, serão criados dois gráficos a partir da planilha Floricultura_Resumo, já calculada. O gráfico será do tipo Coluna 2D/Colunas Empilhadas e, depois de pronto, deverá ficar como a figura 6.21: 1. Para o primeiro gráfico (Cores), selecione o intervalo de F3 a F6 e de J3 a J6.

2. Em Colunas 2D Empilhadas, selecione o gráfico e clique na guia Design, grupo Dados, Alternar Linha Coluna. Formate da maneira desejada.

3. Para o segundo gráfico, selecione o intervalo de F8 a F11 e de J8 a J11. Repita o procedimento do primeiro gráfico. Observe a figura 6.21.

SENAC SÃO PAULO | 129

EXCEL 2016 – CRIANDO PLANILHAS INTERATIVAS E DASHBOARD

À medida que os valores da caixa de listagem são alterados, os rótulos dos gráficos serão trocados.

Figura 6.21 – Gráfico resolvido.

EXERCÍCIO 9 – DASHBOARD COM BOTÃO DE OPÇÃO 1. Clique na planilha Transportes. Ela já está calculada, crie apenas os gráficos Dashboard.

2. Selecione o intervalo de I2 a L6 e insira o gráfico tipo Colunas 2D Agrupadas.

3. Selecione o intervalo de A2 a A18 e de G2 a G18 e insira o gráfico tipo Colunas 3D Agrupadas.

4. Formate da maneira desejada. Observe a figura 6.22:

Figura 6.22 – Gráfico resolvido.

EXERCÍCIO 10 – MACROS Crie uma macro de formatação para planilhas. Ao executar a macro pelo botão, a planilha será formatada automaticamente. Crie macros para que cada planilha seja aberta por meio da execução dos botões. Atividade livre.

130 | SENAC SÃO PAULO

Referências • Básica: CHAMON, J. E. Gráficos em Dashboard para Microsoft Excel 2013. São Paulo: Editora Érica/ Saraiva, 2014.

• Complementar: MANZANO, J. A. N. G.; MANZANO, A. L. N. G. Excel 2013 Avançado. São Paulo: Editora Érica, 2013.

MARTELLI, R.; BARROS, M. S. M. Excel 2013 Avançado. São Paulo: Editora Senac São Paulo, 2013.

SENAC SÃO PAULO | 131

Fonte Família Helvetica Neue Papel Offset 115 g/m2 Acabamento em espiral wire­‑o