فیلتر پیشرفته و مقداری جادو

برای اکثریت قریب به اتفاق کاربران اکسل، وقتی کلمه "فیلتر کردن داده" در ذهن آنها ظاهر می شود، فقط فیلتر کلاسیک معمولی از تب داده - فیلتر (داده ها - فیلتر):

بدون شک چنین فیلتری یک چیز آشناست و در بیشتر موارد انجام خواهد شد. با این حال، شرایطی وجود دارد که شما نیاز دارید که با تعداد زیادی از شرایط پیچیده در چندین ستون به طور همزمان فیلتر کنید. فیلتر معمولی در اینجا خیلی راحت نیست و من چیزی قدرتمندتر می خواهم. چنین ابزاری می تواند باشد فیلتر پیشرفته، مخصوصاً با کمی "تمام با پرونده" (طبق سنت).

پایه

برای شروع، چند خط خالی در بالای جدول داده خود وارد کنید و سرصفحه جدول را در آنجا کپی کنید - این یک محدوده با شرایط خواهد بود (برای وضوح با رنگ زرد برجسته شده است):

بین سلول های زرد و جدول اصلی باید حداقل یک خط خالی وجود داشته باشد.

در سلول های زرد است که باید معیارها (شرایط) را وارد کنید که براساس آن فیلترینگ انجام می شود. به عنوان مثال، اگر شما نیاز به انتخاب موز در مسکو "Auchan" در سه ماهه سوم دارید، شرایط به این صورت خواهد بود:

برای فیلتر کردن، هر سلولی را در محدوده دارای داده منبع انتخاب کنید، برگه را باز کنید داده ها و روی آن کلیک کنید علاوه بر این (داده ها - پیشرفته). در پنجره ای که باز می شود، یک محدوده با داده باید به طور خودکار وارد شود و ما فقط باید محدوده شرایط را مشخص کنیم، یعنی A1:I2:

لطفاً توجه داشته باشید که محدوده شرایط را نمی توان "با حاشیه" تخصیص داد، یعنی نمی توانید خطوط زرد خالی اضافی را انتخاب کنید، زیرا یک سلول خالی در محدوده شرایط توسط اکسل به عنوان عدم وجود یک معیار و یک کل خالی درک می شود. خط به عنوان درخواستی برای نمایش همه داده ها به صورت بی رویه.

گزینه نتیجه را در مکان دیگری کپی کنید به شما این امکان را می دهد که لیست را نه در همان صفحه (مانند یک فیلتر معمولی) فیلتر کنید، بلکه ردیف های انتخاب شده را در محدوده دیگری بارگیری کنید، که سپس باید در فیلد مشخص شود. نتیجه را در محدوده قرار دهید. در این صورت از این تابع استفاده نمی کنیم، ترک می کنیم لیست فیلتر در جای خود کلیک کنید و OK. ردیف های انتخاب شده در صفحه نمایش داده می شوند:

اضافه کردن ماکرو

"خب، راحتی اینجا کجاست؟" شما بپرسید و حق خواهید داشت. نه تنها باید با دست خود شرایط را در سلول های زرد وارد کنید، بلکه یک کادر محاوره ای را نیز باز کنید، محدوده ها را در آنجا وارد کنید، فشار دهید OK. متاسفم، موافقم! اما "همه چیز با آمدن آنها تغییر می کند ©" - ماکروها!

کار با یک فیلتر پیشرفته را می توان با استفاده از یک ماکرو ساده که به طور خودکار فیلتر پیشرفته را با وارد کردن شرایط، یعنی تغییر هر سلول زرد، اجرا می کند، تا حد زیادی تسریع و ساده کرد. بر روی زبانه برگه فعلی کلیک راست کرده و دستور را انتخاب کنید متن منبع (کد منبع). در پنجره باز شده کد زیر را کپی و پیست کنید:

Worksheet_Change فرعی خصوصی (ByVal Target As Range) If Not Intersect(Target, Range("A2:I5")) Nothing ("A7:I1")) هیچ چیز نیست و سپس روی خطا Resume Next ActiveSheet.ShowAllData Range("AXNUMX").CurrentRegion.AdvancedFilter Action:=PageanFilter:=Pagean :=Range("AXNUMX").CurrentRegion End If End Sub  

هنگامی که هر سلولی در کاربرگ فعلی تغییر کند، این روش به طور خودکار اجرا می شود. اگر آدرس سلول تغییر یافته در محدوده زرد (A2:I5) قرار گیرد، این ماکرو تمام فیلترها (در صورت وجود) را حذف می کند و فیلتر توسعه یافته را مجدداً در جدول داده منبع که با A7 شروع می شود اعمال می کند، یعنی همه چیز فوراً، بلافاصله فیلتر می شود. پس از وارد کردن شرط بعدی:

پس همه چیز خیلی بهتر است، درست است؟ 🙂

پیاده سازی پرس و جوهای پیچیده

اکنون که همه چیز در حال فیلتر شدن است، می‌توانیم کمی عمیق‌تر به جزئیات برویم و مکانیسم‌های جستجوهای پیچیده‌تر را در فیلتر پیشرفته از هم جدا کنیم. علاوه بر وارد کردن مطابقت‌های دقیق، می‌توانید از نویسه‌های مختلف (* و ?) و علائم نابرابری ریاضی در شرایط مختلف برای اجرای یک جستجوی تقریبی استفاده کنید. مورد شخصیت مهم نیست. برای وضوح، تمام گزینه های ممکن را در یک جدول خلاصه کرده ام:

نکات ظریف:

• علامت * به معنای هر تعداد از هر کاراکتر است و ? - هر شخصیت
• منطق در پردازش متن و پرس و جوهای عددی کمی متفاوت است. برای مثال سلول شرطی با عدد 5 به این معنی نیست که همه اعدادی که با پنج شروع می شوند را جستجو کنید، بلکه سلول شرطی با حرف B برابر است با B*، یعنی هر متنی را که با حرف B شروع می شود جستجو می کند.
• اگر پرس و جو متن با علامت = شروع نمی شود، می توانید به صورت ذهنی * را در پایان قرار دهید.
• تاریخ ها باید در قالب ایالات متحده ماه-روز-سال و از طریق کسری وارد شوند (حتی اگر تنظیمات اکسل و منطقه ای دارید).

اتصالات منطقی AND-OR

شرایط نوشته شده در سلول های مختلف، اما در یک خط، توسط یک عملگر منطقی به هم مرتبط هستند И (و):

آن ها در سه ماهه سوم، دقیقاً در مسکو و در همان زمان از Auchan، موز را برای من فیلتر کنید.

اگر نیاز دارید که شرایط را با یک عملگر منطقی پیوند دهید OR (یا)، سپس آنها فقط باید در خطوط مختلف وارد شوند. به عنوان مثال، اگر ما نیاز به یافتن تمام سفارشات مدیر Volina برای هلو مسکو و تمام سفارشات برای پیاز در سه ماهه سوم در سامارا داشته باشیم، آنگاه می توان این را در طیف وسیعی از شرایط به شرح زیر مشخص کرد:

اگر نیاز به اعمال دو یا چند شرط برای یک ستون دارید، می توانید به سادگی سرفصل ستون را در محدوده معیار کپی کرده و دوم، سوم و غیره را در زیر آن وارد کنید. مقررات. بنابراین، به عنوان مثال، می توانید تمام معاملات را از مارس تا مه انتخاب کنید:

به طور کلی، پس از "پایان با یک فایل"، یک فیلتر پیشرفته به عنوان یک ابزار کاملا مناسب ظاهر می شود، در برخی مکان ها بدتر از یک فیلتر خودکار کلاسیک نیست.

• سوپرفیلتر در ماکروها
• ماکروها چیست، کجا و چگونه کد ماکرو را در ویژوال بیسیک وارد کنیم
• جداول هوشمند در مایکروسافت اکسل