Filtrado de columnas horizontales en Excel

Se non es un usuario novato, xa debes notar que o 99% de todo en Excel está deseñado para funcionar con táboas verticais, onde os parámetros ou atributos (campos) pasan polas columnas e se localiza a información sobre obxectos ou eventos. nas liñas. Táboas dinámicas, subtotais, copia de fórmulas cun dobre clic: todo está adaptado especificamente para este formato de datos.

Non obstante, non hai regras sen excepcións e cunha frecuencia bastante regular pregúntanme que facer se na obra se atopa unha táboa con orientación semántica horizontal, ou unha táboa onde as filas e as columnas teñen o mesmo peso no significado:

E se Excel aínda sabe como ordenar horizontalmente (co comando Datos – Ordenar – Opcións – Ordenar columnas), entón a situación co filtrado é peor: simplemente non hai ferramentas integradas para filtrar columnas, non filas en Excel. Entón, se te enfrontas a unha tarefa deste tipo, terás que buscar solucións de distintos graos de complexidade.

Método 1. Nova función FILTER

Se estás na nova versión de Excel 2021 ou nunha subscrición a Excel 365, podes aproveitar a función recentemente introducida FILTER (FILTRO), que pode filtrar os datos de orixe non só por filas, senón tamén por columnas. Para funcionar, esta función require unha matriz-fila unidimensional horizontal, onde cada valor (VERDADEIRO ou FALSO) determina se mostramos ou, pola contra, ocultamos a seguinte columna da táboa.

Engadimos a seguinte liña enriba da nosa táboa e escribamos o estado de cada columna nela:

• Digamos que sempre queremos mostrar a primeira e a última columna (cabeceiras e totais), polo que para elas na primeira e a última cela da matriz establecemos o valor = TRUE.
• Para as columnas restantes, o contido das celas correspondentes será unha fórmula que comproba a condición que necesitamos mediante funcións И (E) or OR (OU). Por exemplo, que o total está no rango de 300 a 500.

Despois diso, só queda usar a función FILTER para seleccionar columnas sobre as que a nosa matriz auxiliar teña un valor VERDADEIRO:

Do mesmo xeito, pode filtrar columnas por unha lista determinada. Neste caso, a función axudará COUNTIF (CONTAR SE), que verifica o número de ocorrencias do seguinte nome da columna da cabeceira da táboa na lista permitida:

Método 2. Táboa dinámica en lugar da habitual

Actualmente, Excel ten incorporado filtrado horizontal por columnas só nas táboas dinámicas, polo que se logramos converter a nosa táboa orixinal nunha táboa dinámica, podemos usar esta funcionalidade integrada. Para iso, a nosa táboa de orixe debe cumprir as seguintes condicións:

• ter unha liña de cabeceira dunha liña "correcta" sen celas baleiras e combinadas; se non, non funcionará para construír unha táboa dinámica;
• non conteñan duplicados nas etiquetas de filas e columnas: "contraeranse" no resumo nunha lista de só valores únicos;
• só conteñen números no intervalo de valores (na intersección de filas e columnas), porque a táboa dinámica definitivamente lles aplicará algún tipo de función de agregación (suma, media, etc.) e isto non funcionará co texto.

Se se cumpren todas estas condicións, para crear unha táboa dinámica que se pareza á nosa táboa orixinal, terá que expandirse (a orixinal) desde a táboa cruzada a outra plana (normalizada). E a forma máis sinxela de facelo é co complemento Power Query, unha poderosa ferramenta de transformación de datos integrada en Excel desde 2016. 

Estes son os seguintes:

1. Imos converter a táboa nun comando dinámico "intelixente". Inicio - Formato como unha táboa (Inicio — Formato como táboa).
2. Cargando en Power Query co comando Datos - Da táboa / Rango (Datos - Da táboa / Rango).
3. Filtramos a liña cos totais (o resumo terá os seus propios totais).
4. Fai clic co botón dereito no título da primeira columna e selecciona Descomprimir outras columnas (Desactivar outras columnas). Todas as columnas non seleccionadas convértense en dúas: o nome do empregado e o valor do seu indicador.
5. Filtrando a columna cos totais que entraron na columna atributo.
6. Construímos unha táboa dinámica segundo a táboa plana (normalizada) resultante co comando Inicio — Pechar e cargar — Pechar e cargar en… (Inicio — Pechar e cargar — Pechar e cargar en…).

Agora podes usar a capacidade de filtrar as columnas dispoñibles nas táboas dinámicas: as marcas de verificación habituais diante dos nomes e elementos Filtros de sinatura (Filtros de etiquetas) or Filtra por valor (Filtros de valores):

E por suposto, ao cambiar os datos, terás que actualizar a nosa consulta e o resumo cun atallo de teclado Ctrl+alt+F5 ou equipo Datos - Actualizar todo (Datos — Actualizar todo).

Método 3. Macro en VBA

Todos os métodos anteriores, como podes ver facilmente, non se filtran exactamente: non ocultamos as columnas da lista orixinal, senón que formamos unha nova táboa cun conxunto determinado de columnas a partir da orixinal. Se é necesario filtrar (ocultar) as columnas dos datos de orixe, entón é necesario un enfoque fundamentalmente diferente, é dicir, unha macro.

Supoñamos que queremos filtrar columnas sobre a marcha onde o nome do xestor na cabeceira da táboa satisfaga a máscara especificada na cela amarela A4, por exemplo, comeza coa letra "A" (é dicir, obtén "Anna" e "Arthur". " como resultado). 

Como no primeiro método, primeiro implementamos unha fila de rango auxiliar, onde en cada cela o noso criterio comprobarase mediante unha fórmula e mostraranse os valores lóxicos TRUE ou FALSE para as columnas visibles e ocultas, respectivamente:

Entón, imos engadir unha macro sinxela. Fai clic co botón dereito na pestana da folla e selecciona o comando fonte (Código fonte). Copia e pega o seguinte código VBA na xanela que se abre:

Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address = "$A$4" Entón, para cada cela no intervalo ("D2:O2") Se cela = True Entón cell.EntireColumn.Hidden = False Else cell.EntireColumn.Hidden = True End If Next cell End If End Sub  

A súa lóxica é a seguinte:

• En xeral, este é un controlador de eventos Folla de traballo_Cambiar, é dicir, esta macro executarase automaticamente en calquera cambio en calquera cela da folla actual.
• A referencia á cela modificada sempre estará na variable Branco.
• En primeiro lugar, comprobamos que o usuario cambiou exactamente a cela co criterio (A4) - isto faise polo operador if.
• Entón comeza o ciclo Para cada un… para iterar sobre celas grises (D2:O2) con valores de indicador VERDADERO/FALSO para cada columna.
• Se o valor da seguinte cela gris é VERDADEIRO (verdadeiro), entón a columna non está oculta, se non, ocultámola (propiedade Escondido).

•  Funcións de matriz dinámica de Office 365: FILTRO, ORDENAR e UNIC
• Táboa dinámica con cabeceira multiliña mediante Power Query
• Que son as macros, como crealas e usalas