Filtragem de colunas horizontais no Excel

Se você não é um usuário iniciante, já deve ter notado que 99% de tudo no Excel é projetado para trabalhar com tabelas verticais, onde parâmetros ou atributos (campos) passam pelas colunas e informações sobre objetos ou eventos estão localizadas nas linhas. Tabelas dinâmicas, subtotais, copiar fórmulas com um duplo clique – tudo é adaptado especificamente para este formato de dados.

No entanto, não há regras sem exceções e com uma frequência bastante regular me perguntam o que fazer se uma tabela com orientação semântica horizontal, ou uma tabela onde linhas e colunas têm o mesmo peso de significado, surgisse no trabalho:

E se o Excel ainda souber ordenar horizontalmente (com o comando Dados – Classificar – Opções – Classificar colunas), então a situação com a filtragem é pior – simplesmente não há ferramentas internas para filtrar colunas, nem linhas no Excel. Portanto, se você se deparar com essa tarefa, terá que criar soluções alternativas de vários graus de complexidade.

Método 1. Nova função FILTRO

Se você estiver na nova versão do Excel 2021 ou em uma assinatura do Excel 365, poderá aproveitar o recurso recém-introduzido FILTRO (FILTRO), que pode filtrar os dados de origem não apenas por linhas, mas também por colunas. Para funcionar, esta função requer uma linha de matriz horizontal unidimensional auxiliar, onde cada valor (VERDADEIRO ou FALSO) determina se mostramos ou, inversamente, ocultamos a próxima coluna na tabela.

Vamos adicionar a seguinte linha acima da nossa tabela e escrever o status de cada coluna nela:

• Digamos que sempre queremos exibir a primeira e a última colunas (cabeçalhos e totais), então para elas na primeira e na última células do array definimos o valor = TRUE.
• Para as colunas restantes, o conteúdo das células correspondentes será uma fórmula que verifica a condição que precisamos usando funções И (E) or OR (OR). Por exemplo, que o total está no intervalo de 300 a 500.

Depois disso, resta apenas usar a função FILTRO para selecionar colunas acima das quais nosso array auxiliar tem um valor TRUE:

Da mesma forma, você pode filtrar colunas por uma determinada lista. Neste caso, a função ajudará COUNTIF (CONT.SE), que verifica o número de ocorrências do nome da próxima coluna do cabeçalho da tabela na lista permitida:

Método 2. Tabela dinâmica em vez da usual

Atualmente, o Excel possui filtragem horizontal integrada por colunas apenas em tabelas dinâmicas, portanto, se conseguirmos converter nossa tabela original em uma tabela dinâmica, podemos usar essa funcionalidade integrada. Para fazer isso, nossa tabela de origem deve satisfazer as seguintes condições:

• ter uma linha de cabeçalho de uma linha “correta” sem células vazias e mescladas – caso contrário, não funcionará para construir uma tabela dinâmica;
• não contenha duplicatas nos rótulos de linhas e colunas – elas “colarão” no resumo em uma lista de apenas valores únicos;
• conter apenas números no intervalo de valores (na interseção de linhas e colunas), pois a tabela dinâmica definitivamente aplicará algum tipo de função agregadora a eles (soma, média, etc.) e isso não funcionará com o texto

Se todas essas condições forem atendidas, para construir uma tabela dinâmica que se pareça com a nossa tabela original, ela (a original) precisará ser expandida da crosstab para uma tabela plana (normalizada). E a maneira mais fácil de fazer isso é com o suplemento Power Query, uma poderosa ferramenta de transformação de dados incorporada ao Excel desde 2016. 

Estes são:

1. Vamos converter a tabela em um comando dinâmico “inteligente” Home – Formatar como uma tabela (Início — Formatar como Tabela).
2. Carregando no Power Query com o comando Dados - Da Tabela / Faixa (Dados - Da Tabela / Faixa).
3. Filtramos a linha com os totais (o resumo terá seus próprios totais).
4. Clique com o botão direito do mouse no título da primeira coluna e selecione Recolher outras colunas (Desarticular outras colunas). Todas as colunas não selecionadas são convertidas em duas – o nome do funcionário e o valor de seu indicador.
5. Filtrando a coluna com os totais que entraram na coluna Atributo.
6. Construímos uma tabela dinâmica de acordo com a tabela plana (normalizada) resultante com o comando Home — Fechar e carregar — Fechar e carregar em… (Início — Fechar e carregar — Fechar e carregar para…).

Agora você pode usar a capacidade de filtrar colunas disponíveis em tabelas dinâmicas – as marcas de verificação usuais na frente dos nomes e itens Filtros de assinatura (Filtros de etiqueta) or Filtros por valor (Filtros de valor):

E, claro, ao alterar os dados, você precisará atualizar nossa consulta e o resumo com um atalho de teclado Ctrl+outro+F5 ou equipe Dados - Atualizar tudo (Dados — Atualizar tudo).

Método 3. Macro em VBA

Todos os métodos anteriores, como você pode ver facilmente, não são exatamente filtrantes – não ocultamos as colunas na lista original, mas formamos uma nova tabela com um determinado conjunto de colunas da original. Se for necessário filtrar (ocultar) as colunas nos dados de origem, será necessária uma abordagem fundamentalmente diferente, ou seja, uma macro.

Suponha que queiramos filtrar colunas dinamicamente onde o nome do gerente no cabeçalho da tabela satisfaça a máscara especificada na célula amarela A4, por exemplo, comece com a letra “A” (ou seja, obtenha “Anna” e “Arthur " como resultado). 

Como no primeiro método, primeiro implementamos uma linha auxiliar de intervalo, onde em cada célula nosso critério será verificado por uma fórmula e os valores lógicos VERDADEIRO ou FALSO serão exibidos para colunas visíveis e ocultas, respectivamente:

Então vamos adicionar uma macro simples. Clique com o botão direito do mouse na guia da planilha e selecione o comando fonte (Código fonte). Copie e cole o seguinte código VBA na janela que se abre:

Private Sub Worksheet_Change(ByVal Target As Range) Se Target.Address = "$A$4" Then For Each Cell In Range("D2:O2") Se cell = True Então cell.EntireColumn.Hidden = False Else cell.EntireColumn.Hidden = True End If Next cell End If End Sub  

Sua lógica é a seguinte:

• Em geral, este é um manipulador de eventos Planilha_Alterar, ou seja, essa macro será executada automaticamente em qualquer alteração em qualquer célula da planilha atual.
• A referência à célula alterada estará sempre na variável Target.
• Primeiro, verificamos se o usuário alterou exatamente a célula com o critério (A4) – isso é feito pelo operador if.
• Então o ciclo começa Para cada… para iterar sobre células cinzas (D2:O2) com valores do indicador VERDADEIRO/FALSO para cada coluna.
• Se o valor da próxima célula cinza for TRUE (true), então a coluna não está oculta, caso contrário, a ocultamos (propriedade oculto).

•  Funções de matriz dinâmica do Office 365: FILTER, SORT e UNIC
• Tabela dinâmica com cabeçalho de várias linhas usando o Power Query
• O que são macros, como criá-las e usá-las