Excel istifadəçilərinin böyük əksəriyyəti üçün "məlumatların filtrasiyası" sözü onların ağlına gələndə, nişandan yalnız adi klassik filtr Məlumat - Filtr (Məlumat - Filtr):
Belə bir filtr, şübhəsiz ki, tanış bir şeydir və əksər hallarda bunu edəcək. Bununla belə, bir anda bir neçə sütunda çoxlu sayda mürəkkəb şərtlərə görə süzgəcdən keçirməli olduğunuz vəziyyətlər var. Buradakı adi filtr çox rahat deyil və mən daha güclü bir şey istəyirəm. Belə bir vasitə ola bilər qabaqcıl filtr, xüsusən də bir az "faylla bitirmə" ilə (ənənəyə görə).
Baza
Başlamaq üçün məlumat cədvəlinizin üstünə bir neçə boş sətir daxil edin və cədvəl başlığını ora köçürün – bu, şərtləri olan bir sıra olacaq (aydınlıq üçün sarı rənglə vurğulanmışdır):
Sarı xanalarla orijinal cədvəl arasında ən azı bir boş xətt olmalıdır.
Məhz sarı hüceyrələrdə meyarları (şərtləri) daxil etməlisiniz, buna görə filtrləmə aparılacaqdır. Məsələn, III rübdə Moskva "Auchan" da banan seçmək lazımdırsa, şərtlər belə görünəcək:
Filtr etmək üçün, mənbə məlumatı ilə diapazonda istənilən xananı seçin, tabı açın Tarix Və düyməsini vurun Bundan əlavə, (Data - Qabaqcıl). Açılan pəncərədə verilənləri olan diapazon artıq avtomatik daxil edilməlidir və biz yalnız şərtlər diapazonunu, yəni A1:I2-ni təyin etməli olacağıq:
Nəzərə alın ki, şərtlər diapazonu "marja ilə" bölünə bilməz, yəni əlavə boş sarı xətlər seçə bilməzsiniz, çünki şərtlər diapazonunda boş xana Excel tərəfindən meyarın olmaması və bütövlükdə boş kimi qəbul edilir. sətir bütün məlumatları ayrı-seçkilik etmədən göstərmək tələbi kimi.
Switch Nəticəni başqa yerə kopyalayın siyahını bu vərəqdə yox (adi filtrdə olduğu kimi) süzgəcdən keçirməyə imkan verəcək, lakin seçilmiş sətirləri başqa diapazona boşaltmağa imkan verəcək ki, bu da daha sonra sahədə göstərilməlidir. Nəticəni aralığa qoyun. Bu halda biz bu funksiyadan istifadə etmirik, ayrılırıq Filtr siyahısı yerində basın OK. Seçilmiş sətirlər vərəqdə göstəriləcək:
Makro əlavə etmək
"Yaxşı, burada rahatlıq haradadır?" soruşsan və haqlı olacaqsan. Yalnız əllərinizlə sarı hüceyrələrə şərtlər daxil etməlisiniz, həm də bir dialoq qutusu açın, orada diapazonları daxil edin, basın OK. Təəssüf ki, razıyam! Amma “onlar gələndə hər şey dəyişir ©” – makrolar!
Qabaqcıl filtrlə işləmək, şərtlər daxil edildikdə, yəni hər hansı bir sarı xananın dəyişdirilməsi zamanı qabaqcıl filtri avtomatik işə salacaq sadə makrodan istifadə etməklə xeyli sürətləndirilə və sadələşdirilə bilər. Cari vərəqin nişanına sağ vurun və əmri seçin Mənbə mətni (Mənbə kodu). Açılan pəncərədə aşağıdakı kodu kopyalayın və yapışdırın:
Private Sub Worksheet_Change(ByVal Target As Range) Əgər Kesişməzsə(Hədəf, Diapazon("A2:I5")) Xəta Onda Heç Nə Deyil Növbəti ActiveSheet.ShowAllData Range("A7").CurrentRegion.AdvancedFilter Fəaliyyəti:=xlFilter, :=Range("A1").Cari Region End If End Sub Cari iş vərəqindəki hər hansı bir xana dəyişdirildikdə bu prosedur avtomatik olaraq işləyəcək. Əgər dəyişdirilmiş xananın ünvanı sarı diapazona düşürsə (A2:I5), onda bu makro bütün filtrləri (əgər varsa) silir və genişləndirilmiş filtri A7 ilə başlayan mənbə məlumat cədvəlinə yenidən tətbiq edir, yəni hər şey dərhal, dərhal süzülür. növbəti şərti daxil etdikdən sonra:
Beləliklə, hər şey daha yaxşıdır, elə deyilmi? 🙂
Mürəkkəb sorğuların həyata keçirilməsi
İndi hər şey sürətlə süzülür, biz nüanslara bir az daha dərinləşə və inkişaf etmiş filtrdə daha mürəkkəb sorğuların mexanizmlərini sökə bilərik. Dəqiq uyğunluqları daxil etməklə yanaşı, təxmini axtarışı həyata keçirmək üçün müxtəlif şərti işarələrdən (* və ?) və riyazi bərabərsizlik işarələrindən istifadə edə bilərsiniz. Xarakter hadisəsinin əhəmiyyəti yoxdur. Aydınlıq üçün bütün mümkün variantları cədvəldə ümumiləşdirdim:
| Meyar | Nəticə |
| gr* və ya gr | ilə başlayan bütün hüceyrələr GrIe Grqulaq, Grafrut, Granat s. |
| = soğan | bütün hüceyrələr dəqiq və yalnız sözlə Kaman, yəni dəqiq uyğunluq |
| *liv* və ya *liv | ehtiva edən hüceyrələr Liv necə altından xətt çəkmək, yəni ОLivO, Livep, GörəLiv s. |
| =p*v | ilə başlayan sözlər П ilə bitir В ie Пilkв, Пefirв s. |
| a*s | ilə başlayan sözlər А və daha çox ehtiva edir СIe Аçanaqсin, Аnanaс, Asai s. |
| =*s | ilə bitən sözlər С |
| =???? | 4 simvoldan ibarət bütün xanalar (boşluqlar daxil olmaqla hərflər və ya rəqəmlər) |
| =m??????n | ilə başlayan 8 simvoldan ibarət mətni olan bütün xanalar М ilə bitir НIe Мandariн, Мnarahatlıqн s. |
| =*n??a | ilə bitən bütün sözlər А, sonundan 4-cü hərf haradadır НIe Şüaнikа, Görəнozа s. |
| >=e | ilə başlayan bütün sözlər Э, Ю or Я |
| <>*o* | hərf olmayan bütün sözlər О |
| <>*viç | ilə bitənlərdən başqa bütün sözlər HIV (məsələn, qadınları ata adına görə süzün) |
| = | bütün boş hüceyrələr |
| <> | bütün boş olmayan hüceyrələr |
| > = 5000 | dəyəri 5000-dən çox və ya ona bərabər olan bütün xanalar |
| 5 və ya =5 | 5 dəyəri olan bütün xanalar |
| >=3/18/2013 | 18 mart 2013-cü il tarixindən sonrakı bütün xanalar (daxil olmaqla) |
İncə məqamlar:
- * işarəsi hər hansı bir simvolun istənilən sayını bildirir və ? - hər hansı bir xarakter.
- Mətn və rəqəmli sorğuların işlənməsi məntiqi bir qədər fərqlidir. Beləliklə, məsələn, 5 rəqəmi olan şərt xanası beş ilə başlayan bütün rəqəmləri axtarmaq demək deyil, B hərfi olan şərt xanası B*-a bərabərdir, yəni B hərfi ilə başlayan istənilən mətni axtaracaq.
- Əgər mətn sorğusu = işarəsi ilə başlamırsa, onda siz zehni olaraq sonuna * qoya bilərsiniz.
- Tarixlər ABŞ formatında ay-gün-il və kəsrlə daxil edilməlidir (Excel və regional parametrləriniz olsa belə).
Məntiqi bağlayıcılar AND-OR
Fərqli xanalarda yazılmış, lakin eyni sətirdə olan şərtlər məntiqi operator tərəfindən bir-birinə bağlanmış hesab olunur. И (VƏ):
Bunlar. üçüncü rübdə mənim üçün bananları süzün, dəqiq Moskvada və eyni zamanda Auchan-dan.
Şərtləri məntiqi operatorla əlaqələndirmək lazımdırsa OR (OR), sonra onları müxtəlif sətirlərə daxil etmək kifayətdir. Məsələn, Samarada üçüncü rübdə Moskva şaftalıları üçün menecer Volinanın bütün sifarişlərini və soğan üçün bütün sifarişləri tapmaq lazımdırsa, bu, aşağıdakı şərtlərdə göstərilə bilər:
Bir sütuna iki və ya daha çox şərt qoymaq lazımdırsa, o zaman sadəcə meyarlar diapazonunda sütun başlığını dublikat edə və onun altına ikinci, üçüncü və s. daxil edə bilərsiniz. şərtlər. Beləliklə, məsələn, martdan may ayına qədər bütün əməliyyatları seçə bilərsiniz:
Ümumiyyətlə, "faylla bitirdikdən" sonra inkişaf etmiş bir filtr olduqca layiqli bir vasitə kimi ortaya çıxır, bəzi yerlərdə klassik avtofiltrdən daha pis deyil.
- Makrolarda superfiltr
- Makrolar nədir, Visual Basic-də makro kodu hara və necə daxil etmək olar
- Microsoft Excel-də ağıllı masalar