Filtrat de columnes horitzontals a Excel

Si no ets un usuari novell, ja has d'haver notat que el 99% de tot a Excel està dissenyat per funcionar amb taules verticals, on els paràmetres o atributs (camps) passen per les columnes i es troba la informació sobre objectes o esdeveniments. a les línies. Taules dinàmiques, subtotals, còpia de fórmules amb un doble clic: tot està dissenyat específicament per a aquest format de dades.

No obstant això, no hi ha regles sense excepcions i amb una freqüència bastant regular em demanen què fer si en el treball es troba una taula amb una orientació semàntica horitzontal, o una taula on les files i les columnes tenen el mateix pes de significat:

I si Excel encara sap com ordenar horitzontalment (amb l'ordre Dades – Ordena – Opcions – Ordena columnes), llavors la situació amb el filtratge és pitjor: simplement no hi ha eines integrades per filtrar columnes, no files a Excel. Per tant, si us trobeu davant d'aquesta tasca, haureu de trobar solucions alternatives de diferents graus de complexitat.

Mètode 1. Nova funció FILTER

Si teniu la nova versió d'Excel 2021 o una subscripció d'Excel 365, podeu aprofitar la nova funció introduïda FILTRE (FILTRE), que pot filtrar les dades d'origen no només per files, sinó també per columnes. Per funcionar, aquesta funció requereix una matriu-fila unidimensional horitzontal auxiliar, on cada valor (VERTADER o FALS) determina si mostrem o, al contrari, amaguem la següent columna de la taula.

Afegim la línia següent a sobre de la nostra taula i escrivim l'estat de cada columna:

• Suposem que sempre volem mostrar la primera i l'última columnes (capçaleres i totals), de manera que per a elles a la primera i l'última cel·la de la matriu establim el valor = TRUE.
• Per a les columnes restants, el contingut de les cel·les corresponents serà una fórmula que comprova la condició que necessitem mitjançant funcions И (I) or OR (O). Per exemple, que el total està entre 300 i 500.

Després d'això, només queda utilitzar la funció FILTRE per seleccionar columnes per sobre de les quals la nostra matriu auxiliar té un valor TRUE:

De la mateixa manera, podeu filtrar columnes per una llista determinada. En aquest cas, la funció ajudarà COUNTIF (COUNTIF), que comprova el nombre d'ocurrències del nom de columna següent de la capçalera de la taula a la llista permesa:

Mètode 2. Taula dinàmica en lloc de l'habitual

Actualment, Excel té integrat el filtratge horitzontal per columnes només a les taules dinàmiques, de manera que si aconseguim convertir la nostra taula original en una taula dinàmica, podem utilitzar aquesta funcionalitat integrada. Per fer-ho, la nostra taula d'origen ha de complir les condicions següents:

• tenir una línia de capçalera d'una línia "correcta" sense cel·les buides i combinades; en cas contrari, no funcionarà per crear una taula dinàmica;
• no continguin duplicats a les etiquetes de files i columnes: es "replegaran" al resum en una llista de valors únics;
• conté només números en el rang de valors (a la intersecció de files i columnes), perquè la taula dinàmica definitivament els aplicarà algun tipus de funció d'agregació (suma, mitjana, etc.) i això no funcionarà amb el text

Si es compleixen totes aquestes condicions, per crear una taula dinàmica que s'assembla a la nostra taula original, s'haurà d'ampliar (l'original) de la taula encreuada a una altra plana (normalitzada). I la manera més senzilla de fer-ho és amb el complement Power Query, una potent eina de transformació de dades integrada a Excel des del 2016. 

Aquests són:

1. Convertim la taula en una ordre dinàmica "intel·ligent". Inici – Format com a taula (Inici — Format com a taula).
2. Carregant a Power Query amb l'ordre Dades - De la taula / Interval (Dades - De la taula / Interval).
3. Filtrem la línia amb els totals (el resum tindrà els seus propis totals).
4. Feu clic amb el botó dret a l'encapçalament de la primera columna i seleccioneu Desplegueu altres columnes (Desactivar altres columnes). Totes les columnes no seleccionades es converteixen en dues: el nom de l'empleat i el valor del seu indicador.
5. Filtrant la columna amb els totals que van entrar a la columna Atribut.
6. Construïm una taula dinàmica segons la taula plana (normalitzada) resultant amb l'ordre Inici — Tancar i carregar — Tancar i carregar a... (Inici — Tancar i carregar — Tancar i carregar a...).

Ara podeu utilitzar la possibilitat de filtrar les columnes disponibles a les taules dinàmiques: les marques de verificació habituals davant dels noms i elements Filtres de signatura (Filtres d'etiquetes) or Filtra per valor (Filtres de valors):

I per descomptat, en canviar les dades, caldrà actualitzar la nostra consulta i el resum amb una drecera de teclat Ctrl+Alt+F5 o equip Dades: actualitza-ho tot (Dades — Actualitza-ho tot).

Mètode 3. Macro en VBA

Tots els mètodes anteriors, com podeu veure fàcilment, no es filtren exactament: no amaguem les columnes a la llista original, sinó que formem una taula nova amb un conjunt determinat de columnes de l'original. Si cal filtrar (amagar) les columnes de les dades font, cal un enfocament fonamentalment diferent, és a dir, una macro.

Suposem que volem filtrar columnes sobre la marxa on el nom del gestor a la capçalera de la taula compleix la màscara especificada a la cel·la groga A4, per exemple, comença amb la lletra "A" (és a dir, obteniu "Anna" i "Arthur". " com a resultat). 

Com en el primer mètode, primer implementem una fila d'interval auxiliar, on a cada cel·la el nostre criteri es comprovarà mitjançant una fórmula i es mostraran els valors lògics TRUE o FALSE per a les columnes visibles i ocultes, respectivament:

A continuació, afegim una macro senzilla. Feu clic amb el botó dret a la pestanya del full i seleccioneu l'ordre font (Codi font). Copieu i enganxeu el següent codi VBA a la finestra que s'obre:

Private Sub Worksheet_Change(ByVal Target As Range) Si Target.Address = "$A$4" Aleshores per a cada cel·la de l'interval ("D2:O2") Si cel·la = True Aleshores cell.EntireColumn.Hidden = False Else Cell.EntireColumn.Hidden = True End If Següent cel·la End If End Sub  

La seva lògica és la següent:

• En general, aquest és un gestor d'esdeveniments Full de treball_Canvi, és a dir, aquesta macro s'executarà automàticament en qualsevol canvi a qualsevol cel·la del full actual.
• La referència a la cel·la canviada sempre estarà a la variable Objectiu.
• En primer lloc, comprovem que l'usuari ha canviat exactament la cel·la amb el criteri (A4) - ho fa l'operador if.
• Llavors comença el cicle Per cadascú… per iterar sobre cel·les grises (D2:O2) amb valors d'indicadors TRUE / FALSE per a cada columna.
• Si el valor de la següent cel·la grisa és TRUE (true), la columna no s'amaga, en cas contrari l'amaguem (propietat Hidden).

•  Funcions de matriu dinàmiques d'Office 365: FILTER, SORT i UNIC
• Taula dinàmica amb capçalera multilínia mitjançant Power Query
• Què són les macros, com crear-les i utilitzar-les