Conteúdo
Se você estiver pelo menos familiarizado com a função VPR (PROCV) (se não, então primeiro execute aqui), então você deve entender que esta e outras funções semelhantes a ela (VIEW, INDEX e SEARCH, SELECT, etc.) sempre dão como resultado valor – o número, texto ou data que estamos procurando na tabela fornecida.
Mas e se, em vez de um valor, quisermos obter um hiperlink ativo, clicando no qual poderíamos pular instantaneamente para a correspondência encontrada em outra tabela para vê-la em um contexto geral?
Digamos que temos uma grande tabela de pedidos para nossos clientes como entrada. Por conveniência (embora isso não seja necessário), converti a tabela em um atalho de teclado “inteligente” dinâmico Ctrl+T e deu na aba Construtor (Desenhar) o nome dela tabPedidos:
Em uma folha separada Consolidado Construí uma tabela dinâmica (embora não precise ser exatamente uma tabela dinâmica – qualquer tabela serve em princípio), onde, de acordo com os dados iniciais, é calculada a dinâmica de vendas por meses para cada cliente:
Vamos adicionar uma coluna à tabela de pedidos com uma fórmula que procura o nome do cliente para o pedido atual na planilha Consolidado. Para isso, usamos o conjunto clássico de funções ÍNDICE (ÍNDICE) и MAIS EXPOSTOS (COMBINE):
Agora vamos envolver nossa fórmula em uma função CELL (CÉLULA), que pediremos para exibir o endereço da célula encontrada:
E, finalmente, colocamos tudo o que se tornou uma função HIPERLINK (HIPERLINK), que no Microsoft Excel pode criar um hiperlink ativo para um determinado caminho (endereço). A única coisa que não é óbvia é que você terá que colar o sinal de hash (#) no início do endereço recebido para que o link seja percebido corretamente pelo Excel como interno (de folha em folha):
Agora, quando você clicar em qualquer um dos links, pularemos instantaneamente para a célula com o nome da empresa na planilha com a tabela dinâmica.
Para torná-lo realmente bom, vamos melhorar um pouco nossa fórmula para que a transição ocorra não para o nome do cliente, mas para um valor numérico específico exatamente na coluna do mês em que o pedido correspondente foi concluído. Para isso, devemos lembrar que a função ÍNDICE (ÍNDICE) no Excel é muito versátil e pode ser usado, entre outras coisas, no formato:
= INDEX ( XNUMXD_intervalo; Número da linha; Número_coluna )
Ou seja, como primeiro argumento, podemos especificar não a coluna com os nomes das empresas no pivô, mas toda a área de dados da tabela dinâmica, e como terceiro argumento, adicionar o número da coluna que precisamos. Pode ser facilmente calculado pela função MÊS (MÊS), que retorna o número do mês para a data do negócio:
Melhoria 2. Símbolo de link bonito
Segundo argumento de função HIPERLINK – o texto que é exibido em uma célula com um link – pode ficar mais bonito se você usar caracteres não padronizados de fontes Windings, Webdings e similares em vez dos sinais banais “>>”. Para isso você pode usar a função SÍMBOLO (CARACTERES), que pode exibir caracteres por seu código.
Assim, por exemplo, o código de caractere 56 na fonte Webdings nos dará uma bela seta dupla para um hiperlink:
Melhoria 3. Realce a linha atual e a célula ativa
Bem, para a vitória final da beleza sobre o bom senso, você também pode anexar ao nosso arquivo uma versão simplificada do destaque da linha atual e da célula para a qual seguimos o link. Isso exigirá uma macro simples, que será suspensa para lidar com o evento de alteração de seleção na planilha Consolidado.
Para fazer isso, clique com o botão direito do mouse na guia da planilha Resumo e selecione o comando Ver código (Visão código). Cole o seguinte código na janela do editor Visual Basic que se abre:
Private Sub Worksheet_SelectionChange(ByVal Target As Range) Cells.Interior.ColorIndex = -4142 Cells(ActiveCell.Row, 1).Resize(1, 14).Interior.ColorIndex = 6 ActiveCell.Interior.ColorIndex = 44 End Sub
Como você pode ver facilmente, aqui primeiro removemos o preenchimento de toda a planilha e, em seguida, preenchemos toda a linha do resumo com amarelo (código de cor 6) e depois laranja (código 44) com a célula atual.
Agora, quando qualquer célula dentro da célula de resumo for selecionada (não importa – manualmente ou como resultado de clicar em nosso hiperlink), toda a linha e célula com o mês que precisamos serão destacadas:
Beleza 🙂
PS Apenas lembre-se de salvar o arquivo em um formato habilitado para macro (xlsm ou xlsb).
- Criando links externos e internos com a função HYPERLINK
- Criando e-mails com a função HYPERLINK