Conteúdo
Suponha que você e eu precisemos visualizar dados da tabela a seguir com valores de vendas de carrospor diferentes países em 2021 (dados reais retirados daqui, aliás):
Como o número de séries de dados (países) é grande, tentar colocar todas elas em um gráfico de uma só vez levará a um terrível “gráfico de espaguete” ou à construção de gráficos separados para cada série, o que é muito complicado.
Uma solução elegante para esse problema pode ser traçar um gráfico apenas com os dados da linha atual, ou seja, a linha onde a célula ativa está localizada:
Implementar isso é muito fácil – você só precisa de duas fórmulas e uma pequena macro em 3 linhas.
Etapa 1. Número da linha atual
A primeira coisa que precisamos é de um intervalo nomeado que calcule o número da linha na planilha onde nossa célula ativa está localizada agora. Abrindo em uma guia Fórmulas - Gerenciador de Nomes (Fórmulas — Gerenciador de nomes), clique no botão Crie (Criar) e insira a seguinte estrutura lá:
- Nome – qualquer nome adequado para nossa variável (no nosso caso, este é TekString)
- Área – a seguir, você precisa selecionar a planilha atual para que os nomes criados sejam locais
- Variação – aqui usamos a função CELL (CÉLULA), que pode emitir vários parâmetros diferentes para uma determinada célula, incluindo o número da linha que precisamos – o argumento “line” é responsável por isso.
Etapa 2. Link para o título
Para exibir o país selecionado no título e na legenda do gráfico, precisamos obter uma referência à célula com seu nome (país) da primeira coluna. Para fazer isso, criamos outro local (ou seja, Área = planilha atual, não Book!) um intervalo nomeado com a seguinte fórmula:
Aqui, a função INDEX seleciona de um determinado intervalo (coluna A, onde estão nossos países signatários) uma célula com o número da linha que determinamos anteriormente.
Etapa 3. Vincular aos dados
Agora, de forma semelhante, vamos obter um link para um intervalo com todos os dados de vendas da linha atual, onde a célula ativa agora está localizada. Crie outro intervalo nomeado com a seguinte fórmula:
Aqui, o terceiro argumento, que é zero, faz com que INDEX retorne não um único valor, mas a linha inteira como resultado.
Etapa 4. Substituindo links no gráfico
Agora selecione o cabeçalho da tabela e a primeira linha com dados (intervalo) e construa um gráfico com base neles usando Inserir - Gráficos (Inserir - Gráficos). Se você selecionar uma linha com dados no gráfico, a função será exibida na barra de fórmulas LINHA (SERIES) é uma função especial que o Excel usa automaticamente ao criar qualquer gráfico para se referir aos dados e rótulos originais:
Vamos substituir cuidadosamente o primeiro (assinatura) e o terceiro (dados) argumentos nesta função pelos nomes de nossos intervalos das etapas 2 e 3:
O gráfico começará a exibir dados de vendas da linha atual.
Etapa 5. Macro de recálculo
O toque final permanece. O Microsoft Excel recalcula as fórmulas somente quando os dados da planilha são alterados ou quando uma tecla é pressionada F9, e queremos que o recálculo ocorra quando a seleção for alterada, ou seja, quando a célula ativa for movida pela planilha. Para fazer isso, precisamos adicionar uma macro simples à nossa pasta de trabalho.
Clique com o botão direito do mouse na guia da folha de dados e selecione o comando fonte (Código fonte). Na janela que se abre, insira o código do macro-manipulador para o evento de mudança de seleção:
Como você pode imaginar facilmente, tudo o que ele faz é acionar um recálculo de planilha sempre que a posição da célula ativa mudar.
Etapa 6. Destacando a Linha Atual
Para maior clareza, você também pode adicionar uma regra de formatação condicional para destacar o país atualmente exibido no gráfico. Para fazer isso, selecione a tabela e selecione Home — Formatação Condicional — Criar Regra — Usar Fórmula para Determinar as Células a Formatar (Início — Formatação condicional — Nova regra — Use uma fórmula para determinar quais células devem ser formatadas):
Aqui, a fórmula verifica para cada célula na tabela se o número da linha corresponde ao número armazenado na variável TekRow e, se houver uma correspondência, o preenchimento com a cor selecionada é acionado.
É isso – simples e bonito, certo?
Notas
- Em tabelas grandes, toda essa beleza pode desacelerar – a formatação condicional é uma coisa que consome muitos recursos, e o recálculo para cada seleção também pode ser pesado.
- Para evitar que os dados desapareçam no gráfico quando uma célula é acidentalmente selecionada acima ou abaixo da tabela, você pode adicionar uma verificação adicional ao nome do TekRow usando funções IF aninhadas do formulário:
=SE(CÉLULA(“linha”)<4,SE(CÉLULA("linha")>4,CÉLULA(“linha”)))
- Destacando colunas especificadas em um gráfico
- Como criar um gráfico interativo no Excel
- Seleção de Coordenadas