Geavanceerd filter en wat magie

Voor de overgrote meerderheid van Excel-gebruikers, wanneer het woord "gegevensfiltering" in hun hoofd opkomt, alleen het gebruikelijke klassieke filter van het tabblad Gegevens – Filter (Gegevens — Filter):

Zo'n filter is ongetwijfeld een bekend iets, en in de meeste gevallen zal het dat ook doen. Er zijn echter situaties waarin u moet filteren op een groot aantal complexe voorwaarden in meerdere kolommen tegelijk. Het gebruikelijke filter hier is niet erg handig en ik wil iets krachtigers. Zo'n hulpmiddel zou kunnen zijn: geavanceerd filter, vooral met een beetje “afwerken met een vijl” (volgens de traditie).

Basis

Voeg om te beginnen een paar lege regels boven uw gegevenstabel in en kopieer de tabelkop daar - dit is een bereik met voorwaarden (geel gemarkeerd voor de duidelijkheid):

Er moet ten minste één lege regel tussen de gele cellen en de originele tabel staan.

Het is in de gele cellen dat u de criteria (voorwaarden) moet invoeren op basis waarvan de filtering vervolgens zal worden uitgevoerd. Als u bijvoorbeeld bananen moet selecteren in de "Auchan" in Moskou in het III-kwartier, zien de voorwaarden er als volgt uit:

Om te filteren, selecteer een cel in het bereik met de brongegevens, open het tabblad Data En klik op de Daarnaast (Gegevens — Geavanceerd). In het geopende venster zou al automatisch een bereik met gegevens moeten worden ingevoerd en hoeven we alleen het bereik van voorwaarden te specificeren, namelijk A1:I2:

Houd er rekening mee dat het bereik van voorwaarden niet "met een marge" kan worden toegewezen, dwz u kunt geen extra lege gele lijnen selecteren, omdat een lege cel in het bereik van voorwaarden door Excel wordt waargenomen als de afwezigheid van een criterium, en een hele lege cel regel als een verzoek om alle gegevens willekeurig weer te geven.

Stap over voor slechts Kopieer het resultaat naar een andere locatie stelt u in staat om de lijst te filteren die niet direct op dit blad staat (zoals bij een gewoon filter), maar om de geselecteerde rijen in een ander bereik te verwijderen, dat dan in het veld moet worden opgegeven Zet resultaat in bereik. In dit geval gebruiken we deze functie niet, we laten Filterlijst aanwezig en klik op OK. De geselecteerde rijen worden op het blad weergegeven:

Een macro toevoegen

"Nou, waar is het gemak hier?" je vraagt ​​en je hebt gelijk. U moet niet alleen met uw handen voorwaarden in de gele cellen invoeren, maar ook een dialoogvenster openen, daar bereiken invoeren, op drukken OK. Jammer, ik ben het ermee eens! Maar "alles verandert wanneer ze komen ©" - macro's!

Werken met een geavanceerd filter kan aanzienlijk worden versneld en vereenvoudigd met behulp van een eenvoudige macro die automatisch het geavanceerde filter uitvoert wanneer er voorwaarden worden ingevoerd, dwz het wijzigen van een gele cel. Klik met de rechtermuisknop op het tabblad van het huidige blad en selecteer de opdracht Brontekst (Broncode). Kopieer en plak de volgende code in het geopende venster:

Private Sub Worksheet_Change(ByVal Target As Range) If Not Intersect(Target, Range("A2:I5")) Is niets dan op Fout Hervat volgende ActiveSheet.ShowAllData Range("A7").CurrentRegion.AdvancedFilter Action:=xlFilterInPlace, CriteriaRange :=Bereik ("A1"). Huidig ​​​​Region End If End Sub  

Deze procedure wordt automatisch uitgevoerd wanneer een cel op het huidige werkblad wordt gewijzigd. Als het adres van de gewijzigde cel in het gele bereik valt (A2:I5), verwijdert deze macro alle filters (indien aanwezig) en past het uitgebreide filter opnieuw toe op de brongegevenstabel beginnend met A7, dwz alles wordt onmiddellijk en onmiddellijk gefilterd na het invoeren van de volgende voorwaarde:

Dus alles is veel beter, toch?

Complexe zoekopdrachten implementeren

Nu alles on-the-fly wordt gefilterd, kunnen we wat dieper ingaan op de nuances en de mechanismen van complexere zoekopdrachten in het geavanceerde filter demonteren. Naast het invoeren van exacte overeenkomsten, kunt u verschillende jokertekens (* en ?) en wiskundige ongelijkheidstekens gebruiken in een reeks voorwaarden om een ​​zoekopdracht bij benadering uit te voeren. Het karaktergeval doet er niet toe. Voor de duidelijkheid heb ik alle mogelijke opties in een tabel samengevat:

Subtiele punten:

• Het *-teken betekent een willekeurig aantal tekens, en ? - een willekeurig karakter.
• De logica bij het verwerken van tekst- en numerieke query's is iets anders. Een conditiecel met het getal 5 betekent bijvoorbeeld niet dat er naar alle getallen moet worden gezocht die met vijf beginnen, maar een conditiecel met de letter B is gelijk aan B*, dwz zoekt naar elke tekst die begint met de letter B.
• Als de tekstquery niet begint met het =-teken, dan kun je mentaal * aan het einde zetten.
• Datums moeten worden ingevoerd in het Amerikaanse formaat maand-dag-jaar en in breuken (zelfs als u Excel en regionale instellingen heeft).

Logische connectieven EN-OF

Voorwaarden die in verschillende cellen zijn geschreven, maar in dezelfde regel, worden beschouwd als onderling verbonden door een logische operator И (EN):

Die. filter bananen voor mij in het derde kwartaal, precies in Moskou en tegelijkertijd vanuit Auchan.

Als u voorwaarden moet koppelen aan een logische operator OR (OR), dan hoeven ze alleen in verschillende regels te worden ingevoerd. Als we bijvoorbeeld alle bestellingen van manager Volina voor perziken uit Moskou en alle bestellingen voor uien in het derde kwartaal in Samara moeten vinden, dan kan dit als volgt worden gespecificeerd in een reeks voorwaarden:

Als u twee of meer voorwaarden aan één kolom moet opleggen, kunt u eenvoudig de kolomkop in het criteriumbereik dupliceren en de tweede, derde, enz. eronder invoeren. voorwaarden. Zo kunt u bijvoorbeeld alle transacties van maart tot mei selecteren:

Over het algemeen blijkt een geavanceerd filter na "afwerken met een bestand" een behoorlijk fatsoenlijk hulpmiddel te zijn, op sommige plaatsen niet slechter dan een klassiek autofilter.

• Superfilter op macro's
• Wat zijn macro's, waar en hoe macrocode in te voegen in Visual Basic
• Slimme tabellen in Microsoft Excel