Avancerat filter och lite magi

För de allra flesta Excel-användare, när ordet "datafiltrering" kommer upp i deras huvud, är det bara det vanliga klassiska filtret från fliken Data – Filter (Data — Filter):

Ett sådant filter är en bekant sak, utan tvekan, och i de flesta fall kommer det att göra det. Det finns dock situationer när du behöver filtrera efter ett stort antal komplexa villkor i flera kolumner samtidigt. Det vanliga filtret här är inte särskilt bekvämt och jag vill ha något kraftfullare. Ett sådant verktyg kan vara avancerat filter, speciellt med lite ”avsluta med fil” (enligt tradition).

Bas

För att komma igång, infoga några tomma rader ovanför din datatabell och kopiera tabellhuvudet dit – detta kommer att vara ett intervall med villkor (markerat i gult för tydlighetens skull):

Det måste finnas minst en tom rad mellan de gula cellerna och den ursprungliga tabellen.

Det är i de gula cellerna du behöver ange kriterierna (villkoren), enligt vilka filtreringen sedan kommer att utföras. Till exempel, om du behöver välja bananer i Moskva "Auchan" i tredje kvartalet, kommer förhållandena att se ut så här:

För att filtrera, välj valfri cell i intervallet med källdata, öppna fliken Data Och klicka på Dessutom (Data – Avancerat). I fönstret som öppnas bör ett intervall med data redan matas in automatiskt och vi behöver bara ange villkorsintervallet, dvs A1:I2:

Observera att villkorsintervallet inte kan tilldelas "med en marginal", dvs. du kan inte välja extra tomma gula linjer, eftersom en tom cell i villkorsintervallet uppfattas av Excel som frånvaro av ett kriterium, och en hel tom rad som en begäran om att visa alla data urskillningslöst.

Växla Kopiera resultatet till en annan plats kommer att tillåta dig att filtrera listan inte precis där på det här bladet (som med ett vanligt filter), utan att ladda ner de valda raderna till ett annat intervall, som sedan måste anges i fältet Sätt resultatet i intervallet. I det här fallet använder vi inte den här funktionen, vi lämnar Filterlista på plats och klicka OK. De valda raderna kommer att visas på arket:

Lägga till ett makro

"Tja, var är bekvämligheten här?" du frågar och du kommer att ha rätt. Du behöver inte bara ange villkor i de gula cellerna med händerna, utan också öppna en dialogruta, ange intervall där, tryck på OK. Tråkigt, jag håller med! Men "allt förändras när de kommer ©" – makron!

Arbetet med ett avancerat filter kan påskyndas och förenklas avsevärt med hjälp av ett enkelt makro som automatiskt kör det avancerade filtret när villkor anges, dvs. byter valfri gul cell. Högerklicka på fliken i det aktuella arket och välj kommandot Källtext (Källkod). Kopiera och klistra in följande kod i fönstret som öppnas:

Private Sub Worksheet_Change(ByVal Target As Range) If Not Intersect(Target, Range("A2:I5")) Is Nothing Then On Error Resume Next ActiveSheet.ShowAllData Range("A7").CurrentRegion.AdvancedFilter Action:=xlFilterInPlace, CriteriaR :=Range("A1").CurrentRegion End If End Sub  

Denna procedur körs automatiskt när någon cell i det aktuella kalkylbladet ändras. Om adressen för den ändrade cellen faller inom det gula intervallet (A2:I5), tar detta makro bort alla filter (om några) och applicerar det utökade filtret igen på källdatatabellen som börjar med A7, dvs allt kommer att filtreras omedelbart, omedelbart efter att ha angett nästa villkor:

Så allt är mycket bättre, eller hur? 🙂

Implementera komplexa frågor

Nu när allt filtreras i farten kan vi gå lite djupare in i nyanserna och plocka isär mekanismerna för mer komplexa frågor i det avancerade filtret. Förutom att ange exakta matchningar kan du använda olika jokertecken (* och ?) och matematiska olikhetstecken under en rad förhållanden för att implementera en ungefärlig sökning. Teckenskiften spelar ingen roll. För tydlighetens skull har jag sammanfattat alla möjliga alternativ i en tabell:

Subtila punkter:

• Tecknet * betyder valfritt antal tecken, och ? – vilken karaktär som helst.
• Logiken vid bearbetning av text och numeriska frågor är något annorlunda. Så, till exempel, en villkorscell med siffran 5 betyder inte att söka efter alla siffror som börjar med fem, utan en villkorscell med bokstaven B är lika med B*, dvs kommer att leta efter vilken text som helst som börjar med bokstaven B.
• Om textfrågan inte börjar med tecknet =, kan du mentalt sätta * i slutet.
• Datum måste anges i USA-format månad-dag-år och genom en bråkdel (även om du har Excel och regionala inställningar).

Logiska kopplingar OCH-ELLER

Villkor skrivna i olika celler, men på samma rad, anses vara sammankopplade av en logisk operator И (OCH):

De där. filtrera bananer för mig under tredje kvartalet, just i Moskva och samtidigt från Auchan.

Om du behöver koppla villkor med en logisk operator OR (ELLER), då behöver de bara anges på olika rader. Till exempel, om vi behöver hitta alla beställningar av chef Volina för Moskva-persikor och alla beställningar på lök under tredje kvartalet i Samara, kan detta specificeras i en rad olika förhållanden enligt följande:

Om du behöver ställa två eller flera villkor på en kolumn, kan du helt enkelt duplicera kolumnrubriken i kriterieintervallet och ange den andra, tredje, etc. under den. villkor. Så, till exempel, kan du välja alla transaktioner från mars till maj:

I allmänhet, efter att ha "avslutat med en fil", kommer ett avancerat filter att vara ett ganska anständigt verktyg, på vissa ställen inte sämre än ett klassiskt autofilter.

• Superfilter på makron
• Vad är makron, var och hur man infogar makrokod i Visual Basic
• Smarta tabeller i Microsoft Excel