Filtre avançat i una mica de màgia

Per a la gran majoria d'usuaris d'Excel, quan la paraula "filtrat de dades" apareix al seu cap, només el filtre clàssic habitual de la pestanya Dades – Filtre (Dades — Filtre):

Aquest filtre és una cosa familiar, sens dubte, i en la majoria dels casos ho farà. Tanmateix, hi ha situacions en què cal filtrar per un gran nombre de condicions complexes en diverses columnes alhora. El filtre habitual aquí no és molt convenient i vull alguna cosa més potent. Una eina així podria ser filtre avançat, sobretot amb una mica de “acabar amb una llima” (segons la tradició).

Bases

Per començar, inseriu unes quantes línies buides a sobre de la taula de dades i copieu la capçalera de la taula allà; aquest serà un interval amb condicions (ressaltat en groc per a més claredat):

Hi ha d'haver almenys una línia buida entre les cel·les grogues i la taula original.

És a les cel·les grogues on cal introduir els criteris (condicions), segons els quals es realitzarà el filtratge. Per exemple, si heu de seleccionar plàtans al "Auchan" de Moscou al III trimestre, les condicions seran així:

Per filtrar, seleccioneu qualsevol cel·la de l'interval amb les dades d'origen, obriu la pestanya dades I feu clic a Més (Dades — Avançat). A la finestra que s'obre, ja s'hauria d'introduir automàticament un interval amb dades i només haurem d'especificar el rang de condicions, és a dir, A1:I2:

Tingueu en compte que el rang de condicions no es pot assignar "amb un marge", és a dir, no podeu seleccionar línies grogues buides addicionals, perquè una cel·la buida en el rang de condicions és percebuda per Excel com l'absència d'un criteri, i una cel·la buida. línia com una sol·licitud per mostrar totes les dades indistintament.

Interruptor Copieu el resultat a una altra ubicació us permetrà filtrar la llista no allà mateix en aquest full (com amb un filtre normal), sinó descarregar les files seleccionades a un altre rang, que llavors s'haurà d'especificar al camp Posa el resultat en el rang. En aquest cas, no fem servir aquesta funció, deixem Llista de filtres al seu lloc and clic OK. Les files seleccionades es mostraran al full:

Afegir una macro

"Bé, on és la comoditat aquí?" preguntes i tindràs raó. No només cal que introduïu condicions a les cel·les grogues amb les vostres mans, sinó que també heu d'obrir un quadre de diàleg, introduir intervals allà, premeu OK. Trist, estic d'acord! Però "tot canvia quan arriben ©": macros!

El treball amb un filtre avançat es pot accelerar i simplificar molt mitjançant una macro senzilla que executarà automàticament el filtre avançat quan s'introdueixin condicions, és a dir, canviant qualsevol cel·la groga. Feu clic amb el botó dret a la pestanya del full actual i seleccioneu l'ordre Text font (Codi font). A la finestra que s'obre, copieu i enganxeu el codi següent:

Private Sub Worksheet_Change(ByVal Target As Range) Si no Intersect(Target, Range("A2:I5")) No hi ha res, aleshores s'error Reprèn el següent ActiveSheet.ShowAllData Range("A7").CurrentRegion.AdvancedFilter Action:=xlFilterInPlace, CriteriaRange :=Range("A1").CurrentRegion End If End Sub  

Aquest procediment s'executarà automàticament quan es canviï qualsevol cel·la del full de treball actual. Si l'adreça de la cel·la canviada entra en l'interval groc (A2:I5), aquesta macro elimina tots els filtres (si n'hi ha) i torna a aplicar el filtre estès a la taula de dades d'origen començant per A7, és a dir, tot es filtrarà a l'instant, immediatament. després d'introduir la següent condició:

Així que tot és molt millor, oi? 🙂

Implementació de consultes complexes

Ara que tot s'està filtrant sobre la marxa, podem aprofundir una mica més en els matisos i desmuntar els mecanismes de consultes més complexes al filtre avançat. A més d'introduir coincidències exactes, podeu utilitzar diversos caràcters comodins (* i ?) i signes de desigualtat matemàtics en una sèrie de condicions per implementar una cerca aproximada. El cas del caràcter no importa. Per a més claredat, he resumit totes les opcions possibles en una taula:

Punts subtils:

• El signe * significa qualsevol nombre de caràcters i ? - qualsevol personatge.
• La lògica en el processament de les consultes de text i numèriques és lleugerament diferent. Així, per exemple, una cel·la de condició amb el número 5 no vol dir buscar tots els nombres que comencen per cinc, però una cel·la de condició amb la lletra B és igual a B*, és a dir, buscarà qualsevol text que comenci per la lletra B.
• Si la consulta de text no comença amb el signe =, podeu posar mentalment * al final.
• Les dates s'han d'introduir en el format nord-americà mes-dia-any i en una fracció (encara que tinguis una configuració d'Excel i regional).

Connectius lògics AND-OR

Les condicions escrites en cel·les diferents, però en la mateixa línia, es considera que estan interconnectades per un operador lògic И (I):

Aquells. filtre per mi plàtans al tercer trimestre, precisament a Moscou i alhora d'Auchan.

Si necessiteu enllaçar condicions amb un operador lògic OR (O), llavors només cal introduir-los en línies diferents. Per exemple, si hem de trobar totes les comandes del gerent Volina per als préssecs de Moscou i totes les comandes de cebes al tercer trimestre a Samara, això es pot especificar en una sèrie de condicions de la següent manera:

Si necessiteu imposar dues o més condicions en una columna, només podeu duplicar la capçalera de la columna a l'interval de criteris i introduir-hi la segona, la tercera, etc. termes. Així, per exemple, podeu seleccionar totes les transaccions de març a maig:

En general, després d'"acabar amb un fitxer", un filtre avançat resulta ser una eina força decent, en alguns llocs no és pitjor que un filtre automàtic clàssic.

• Superfiltre en macros
• Què són les macros, on i com inserir el codi de macro a Visual Basic
• Taules intel·ligents en Microsoft Excel