Painéis do Excel - controles interativos

Se você tiver mais dados para exibir no painel que não cabem em uma única tela, pode optar por usar os controles do Excel que vêm como parte do Excel Visual Basic. Os controles mais comumente usados ​​são barras de rolagem, botões de opção e caixas de seleção. Ao incorporá-los ao painel, você pode torná-lo interativo e permitir que o usuário visualize as diferentes facetas dos dados por meio de seleções possíveis.

Você pode fornecer controles interativos, como barras de rolagem, caixas de seleção e botões de rádio em seus painéis para facilitar que os destinatários visualizem dinamicamente as diferentes facetas dos dados exibidos como resultados. Você pode decidir sobre um layout específico do painel junto com os destinatários e usar o mesmo layout a partir de então. Os controles interativos do Excel são simples de usar e não requerem nenhum conhecimento do Excel.

Os controles interativos do Excel estarão disponíveis na guia DESENVOLVEDOR na Faixa de Opções.

Se você não encontrar a guia DEVELOPER na Faixa de Opções, faça o seguinte -

  • Clique em Personalizar Faixa de Opções na caixa Opções do Excel.
  • Selecione Guias principais na caixa Personalizar a faixa de opções.
  • Marque a caixa Desenvolvedor na lista Guias principais.
  • Clique em OK. Você encontrará a guia DEVELOPER na Faixa de Opções.

Barras de rolagem em painéis

Um dos recursos de qualquer painel é que cada componente do painel é o mais compacto possível. Suponha que seus resultados sejam os seguintes -

Se você puder apresentar esta tabela com uma barra de rolagem conforme mostrado abaixo, seria mais fácil navegar pelos dados.

Você também pode ter uma linha de destino dinâmica em um gráfico de barras com barra de rolagem. Conforme você move a barra de rolagem para cima e para baixo, a linha de destino se move para cima e para baixo e as barras que estão cruzando a linha de destino serão destacadas.

Nas seções a seguir, você aprenderá como criar uma barra de rolagem e uma linha de destino dinâmica vinculada a uma barra de rolagem. Você também aprenderá a exibir rótulos dinâmicos em barras de rolagem.

Criação de uma barra de rolagem

Para criar uma barra de rolagem para uma tabela, primeiro copie os cabeçalhos das colunas para uma área vazia da planilha, conforme mostrado abaixo.

  • Insira uma barra de rolagem.

    • Clique na guia DEVELOPER na Ribbon.

    • Clique em Inserir no grupo Controles.

    • Clique no ícone Barra de rolagem em Controles de formulário na lista suspensa de ícones.

  • Leve o cursor para a coluna I e puxe para baixo para inserir uma barra de rolagem vertical.

  • Ajuste a altura e a largura da barra de rolagem e alinhe-a com a tabela.

  • Clique com o botão direito na barra de rolagem.

  • Clique em Format Control na lista suspensa.

A caixa de diálogo Format Control é exibida.

  • Clique na guia Controle.

  • Digite o seguinte nas caixas que aparecem.

  • Clique no botão OK. A barra de rolagem está pronta para uso. Você escolheu a célula O2 como o link de célula para a barra de rolagem, que assume valores de 0 a 36, ​​quando você move a barra de rolagem para cima e para baixo. Em seguida, você deve criar uma cópia dos dados da tabela com uma referência baseada no valor da célula O2.

  • Na célula K3, digite o seguinte -

    = OFFSET (Resumo [@ [S. No.]], $ O $ 2,0).

  • Aperte o botão Enter. Preencha as células da coluna copiando a fórmula.

  • Preencha as células nas outras colunas copiando a fórmula.

Sua tabela dinâmica e rolável está pronta para ser copiada para o seu painel.

  • Mova a barra de rolagem para baixo.

Como você pode observar, o valor na célula - link da célula da barra de rolagem muda e os dados na tabela são copiados com base neste valor. Por vez, 12 linhas de dados são exibidas.

  • Arraste a barra de rolagem para a parte inferior.

As últimas 12 linhas dos dados são exibidas, pois o valor atual é 36 (conforme mostrado na célula O2) e 36 é o valor máximo que você definiu na caixa de diálogo Controle de formulário.

Você pode alterar a posição relativa da tabela dinâmica, alterar o número de linhas a serem exibidas por vez, link de célula para barra de rolagem, etc. com base em suas necessidades. Como você viu acima, eles precisam ser configurados na caixa de diálogo Format Control.

Criação de uma linha de destino dinâmica e interativa

Suponha que você queira exibir as vendas por região nos últimos 6 meses. Você também definiu metas para cada mês.

Você pode fazer o seguinte -

  • Crie um gráfico de colunas mostrando todas essas informações.
  • Crie uma linha de destino nas colunas.
  • Torne a Linha de destino interativa com uma barra de rolagem.
  • Torne a linha de destino dinâmica configurando os valores de destino em seus dados.
  • Destaque os valores que estão atingindo a meta.

Crie um gráfico de colunas mostrando todas essas informações

Selecione os dados. Insira um gráfico de colunas agrupadas.

Crie uma linha de destino nas colunas

Altere o tipo de gráfico para combo. Selecione o tipo de gráfico como Linha para a série de destino e Coluna em cluster para o resto da série.

Crie uma tabela base para a linha de destino. Você tornará isso dinâmico mais tarde.

Altere os valores da série de dados da Linha de destino para a coluna de destino na tabela acima.

Clique no botão OK.

Altere o esquema de cores da coluna agrupada. Mude a linha de destino em uma linha pontilhada verde.

Torne a linha de destino interativa com uma barra de rolagem

  • Insira uma barra de rolagem e coloque-a abaixo do gráfico e dimensione-a para abranger de janeiro a junho.

  • Insira os parâmetros da barra de rolagem na caixa de diálogo Controle de formato.

  • Crie uma tabela com duas colunas - Mês e Meta.

  • Insira os valores com base na tabela de dados e no link da célula da barra de rolagem.

Esta tabela exibe o mês e a meta correspondente com base na posição da barra de rolagem.

Torne a linha de destino dinâmica configurando os valores de destino em seus dados

Agora, você está pronto para tornar sua Linha-alvo dinâmica.

  • Altere os valores da coluna de destino na tabela base que você criou para a linha de destino digitando = $ G $ 12 em todas as linhas.

Como você sabe, a célula G12 exibe o valor Alvo dinamicamente.

Como você pode observar, a linha de destino se move com base na barra de rolagem.

Destaque os valores que estão atingindo a meta

Esta é a etapa final. Você deseja destacar os valores que atendem à meta em qualquer ponto do tempo.

  • Adicione colunas ao lado direito de sua tabela de dados - East-Results, North-Results, SouthResults e West-Results.

  • Na célula H3, insira a seguinte fórmula -

    = SE (D3> = $ G $ 12, D3, NA ())

  • Copie a fórmula para as outras células da tabela. Redimensione a tabela.

Como você pode observar, os valores nas colunas - East-Results, North-Results, SouthResults e West-Results mudam dinamicamente com base na barra de rolagem (ou seja, valor de destino). Valores maiores ou iguais ao alvo são exibidos e os outros valores são apenas # N / A.

  • Altere o intervalo de dados do gráfico para incluir as colunas adicionadas recentemente na tabela de dados.

  • Clique em Alterar tipo de gráfico.

  • Faça a série Alvo ser Linha e o resto Coluna Agrupada.

  • Para a série de dados recém-adicionada, selecione Eixo secundário.

  • Formate as séries de dados de forma que as séries Leste, Norte, Sul e Oeste tenham uma cor de preenchimento laranja e as séries East-Results, North-Results, South-Results e WestResults uma cor de preenchimento verde.

  • Insira um rótulo de dados para a linha de destino e torne-o dinâmico com a referência da célula ao valor do mês na tabela de dados dinâmicos.

Seu gráfico com Target Line dinâmico está pronto para inclusão no painel.

Você pode limpar o eixo secundário, pois não é necessário. Conforme você move a barra de rolagem, a Linha de Destino se move e as Barras são realçadas de acordo. A linha de destino também terá um rótulo mostrando o mês.

Botões de opção do Excel (rádio)

Os botões de opção são normalmente usados ​​para selecionar uma opção de um determinado conjunto de opções. É sempre representado por um pequeno círculo, que terá um ponto quando selecionado. Quando você tem um conjunto de botões de opção, pode selecionar apenas um deles.

No Excel, os botões de opção são chamados de botões de opção.

Você pode usar os botões de opção do Excel em gráficos para escolher os dados específicos que o leitor deseja consultar. Por exemplo, no exemplo da seção anterior, você criou uma barra de rolagem para obter uma Linha de destino dinâmica com valores de destino baseados no mês. Você pode usar os botões de opção para selecionar um mês e, portanto, o valor alvo, e basear a linha alvo no valor alvo. A seguir serão as etapas -

  • Crie um gráfico de colunas mostrando todas essas informações.
  • Crie uma linha de destino nas colunas.
  • Torne a Linha de destino interativa com os botões de opção.
  • Torne a linha de destino dinâmica configurando os valores de destino em seus dados.
  • Destaque os valores que estão atingindo a meta.

As etapas 1 e 2 são iguais às do caso anterior. Ao final da segunda etapa, você terá o gráfico a seguir.

Torne a linha de destino interativa com os botões de opção

  • Insira um botão de opção.

    • Clique na guia DEVELOPER na Ribbon.

    • Clique em Inserir no grupo Controles.

    • Clique no ícone Botão de opção em Controles de formulário na lista suspensa de ícones.

Coloque-o no canto superior direito do gráfico.

Clique com o botão direito no botão Opção. Clique na opção Format Control na lista suspensa.

Insira os parâmetros do Botão de opção na caixa de diálogo Formatar objeto, na guia Controle.

A célula F10 está vinculada ao Botão de Opção. Faça 5 cópias do botão de opção verticalmente.

Como você pode observar, todos os botões de opção têm o mesmo nome, conhecidos como nomes de legenda. Mas, internamente, o Excel terá nomes diferentes para esses botões de opção, que você pode ver na caixa Nome. Além disso, como o Botão de Opção 1 foi definido para vincular à célula F10, todas as cópias também se referem à mesma célula.

Clique em qualquer um dos botões de opção.

Como você pode observar, o número na célula vinculada muda para o número de série do Botão de Opção. Renomeie os botões de opção para janeiro, fevereiro, março, abril, maio e junho.

Crie uma tabela com duas colunas - Mês e Meta. Insira os valores com base na tabela de dados e no link da célula da barra de rolagem.

Esta tabela exibe o mês e a meta correspondente com base no botão de opção selecionado.

Torne a linha de destino dinâmica configurando os valores de destino em seus dados

Agora, você está pronto para tornar sua Linha-alvo dinâmica.

  • Altere os valores da coluna de destino na tabela base que você criou para a linha de destino digitando = $ G $ 12 em todas as linhas.

Como você sabe, a célula G12 exibe o valor Alvo dinamicamente.

Como você pode observar, a Linha de destino é exibida com base no botão de opção selecionado.

Destaque os valores que estão atingindo a meta

Esta é a etapa final. Você deseja destacar os valores que atendem à meta em qualquer ponto do tempo.

  • Adicione colunas ao lado direito de sua tabela de dados - East-Results, North-Results, SouthResults e West-Results.

  • Na célula H3, insira a seguinte fórmula -

    = SE (D3> = $ G $ 12, D3, NA ())

  • Copie a fórmula para as outras células da tabela. Redimensione a tabela.

Como você pode observar, os valores nas colunas - East-Results, North-Results, SouthResults e West-Results mudam dinamicamente com base na barra de rolagem (ou seja, valor de destino). Valores maiores ou iguais ao alvo são exibidos e os outros valores são apenas # N / A.

  • Altere o intervalo de dados do gráfico para incluir as colunas adicionadas recentemente na tabela de dados.

  • Clique em Alterar tipo de gráfico.

  • Faça a série Alvo ser Linha e o resto Coluna Agrupada.

  • Para a série de dados recém-adicionada, selecione Eixo secundário.

  • Formate as séries de dados de forma que as séries Leste, Norte, Sul e Oeste tenham uma cor de preenchimento laranja e as séries East-Results, North-Results, South-Results e WestResults uma cor de preenchimento verde.

  • Adicione um rótulo de dados dinâmico à linha de destino com o valor da célula $ G $ 12.

  • Limpe o eixo secundário, pois não é necessário.

  • Na guia VISUALIZAR na faixa de opções, desmarque a caixa Linhas de grade.

  • Altere a opção Label para High nas opções Format Axis. Isso desloca os rótulos do eixo vertical para a direita, tornando visível o rótulo dos dados da linha de destino.

Seu gráfico com Linha de destino dinâmica e botões de opção está pronto para inclusão no painel.

Conforme você seleciona um botão de opção, a linha alvo é exibida de acordo com o valor alvo do mês selecionado e as barras serão destacadas de acordo. A linha de destino também terá um rótulo de dados mostrando o valor de destino.

Excel Checkboxes

Normalmente, as caixas de seleção são usadas para selecionar uma ou mais opções de um determinado conjunto de opções. As caixas de seleção são sempre representadas por pequenos quadrados, que terão uma marca de seleção quando selecionados. Quando você tem um conjunto de caixas de seleção, é possível selecionar qualquer número delas. Por exemplo,

Você pode usar as caixas de seleção do Excel em gráficos para escolher os dados específicos que o leitor deseja examinar. Por exemplo, no exemplo da seção anterior, você criou um gráfico de colunas que exibe os dados de 4 regiões - Leste, Norte, Sul e Oeste. Você pode usar as caixas de seleção para selecionar as regiões para as quais os dados são exibidos. Você pode selecionar qualquer número de regiões de uma vez.

Você pode começar com a última etapa da seção anterior -

  • Insira uma caixa de seleção.

    • Clique na guia DEVELOPER na Ribbon.

    • Clique em Inserir no grupo Controles.

    • Clique no ícone da caixa de seleção em Controles de formulário na lista suspensa de ícones.

  • Coloque-o no canto superior esquerdo do gráfico.

  • Altere o nome da caixa de seleção para leste.

  • Clique com o botão direito na caixa de seleção. Clique em Format Control na lista suspensa.

  • Insira os parâmetros da caixa de seleção na caixa de diálogo Controle de formato, na guia Controle.

  • Clique no botão OK. Você pode observar que na célula C19 vinculada, TRUE será exibido se você selecionar a caixa de seleção e FALSE será exibido se você desmarcar a caixa de seleção.

  • Copie a caixa de seleção e cole 3 vezes horizontalmente.

  • Altere os nomes para Norte, Sul e Oeste.

Como você pode observar, quando você copia uma caixa de seleção, a célula vinculada também permanece a mesma para a caixa de seleção copiada. No entanto, como as caixas de seleção podem ter várias seleções, você precisa tornar as células vinculadas diferentes.

  • Altere as células vinculadas para Norte, Sul e Oeste para $ C $ 20, $ C $ 21 e $ C $ 22, respectivamente.

A próxima etapa é ter apenas os dados das regiões selecionadas no gráfico.

  • Crie uma estrutura de tabela da seguinte maneira -

  • Digite = IF ($ C $ 19, H3, NA ()) na célula C21.
  • Digite = SE ($ D $ 19, I3, NA ()) na célula D21.
  • Digite = IF ($ E $ 19, J3, NA ()) na célula E21.
  • Digite = IF ($ F $ 19, K3, NA ()) na célula F21.
  • Preencha as outras linhas da tabela.

  • Adicione a coluna Destino.

  • Altere os dados do gráfico para esta tabela.

O gráfico exibe os dados para as regiões selecionadas que são mais do que o valor definido para o mês selecionado.