چرا تابع FILTER در اکسل از Advanced Filter است؟

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

هر بار که داده ها یا شروط فیلتر تغییر کنند، باید دوباره به صورت دستی پنجره فیلتر را باز کرده و عملیات را تکرار کنید.

در مقابل، تابع FILTER این مشکل را حل می کند. با FILTER می توان منطق فیلتر را در قالب یک فرمول واحد نوشت؛ فرمولی که به صورت خودکار با تغییر داده ها یا معیارها به روزرسانی می شود. همین ویژگی باعث می شود در بسیاری از کاربردهای مدرن، FILTER انتخاب بهتری نسبت به Advanced Filter باشد.

Advanced Filter در تب Data و بخش Sort & Filter قرار دارد. این ابزار دو حالت اصلی دارد:

• Filter the list in place : فقط سطرهای نامنطبق را مخفی می کند.
• Copy to another location : خروجی فیلتر را در ناحیه ای دیگر کپی می کند.

برای استفاده از آن باید ابتدا یک Criteria Range بسازید؛ یعنی یک محدوده جداگانه شامل سرستون هایی که دقیقاً با سرستون های جدول داده یکسان باشند. سپس نحوه قرارگیری شرط ها معنا پیدا می کند:

• منطق AND : شرط ها در یک سطر قرار می گیرند.
• منطق OR : شرط ها در سطرهای مختلف نوشته می شوند.

این مدل هرچند کاربردی است، اما چند مشکل دارد:

1. وابسته به چیدمان فیزیکی سلول هاست
2. مدیریت شرط های پیچیده سخت تر می شود
3. با تغییر داده یا شرط، باید دوباره فیلتر را دستی اجرا کرد
4. برای کارهای تکرارشونده و داشبوردهای پویا مناسب نیست

با وجود محدودیت ها، این ابزار هنوز هم چند نقطه قوت دارد:

• پشتیبانی خوب از wildcard ها مثل * و ?
• امکان استفاده از Named Range
• مناسب برای استخراج های یک باره
• مناسب برای کاربرانی که محیط منویی را به فرمول نویسی ترجیح می دهند

تابع FILTER ساختاری ساده دارد:

=FILTER(array, include, [if_empty])

اجزای تابع

• array : محدوده داده ای که باید فیلتر شود
• include : شرط یا مجموعه شرط ها
• if_empty : مقدار جایگزین در صورت نبود نتیجه

• FILTER از توابع Dynamic Array اکسل است. یعنی نتیجه را از یک سلول شروع می کند و به طور خودکار در سلول های مجاور گسترش می دهد.
• اگر داده منبع تغییر کند، خروجی هم بلادرنگ به روزرسانی می شود.
• این دقیقاً همان چیزی است که Advanced Filter ندارد.

یکی از نکات جالب و مهم برای کاربران فنی این است که FILTER منطق شرطی را با عملگرهای ریاضی پیاده سازی می کند:

• AND با *
• OR با +

مثال:

=FILTER(A1:N100,(A1:A100="West Asia")*(C1:C100="IRAN"),"No Records Found")

در این مثال:

• شرط اول بررسی می کند که مقدار ستون Region برابر "West Asia" باشد.
• شرط دوم بررسی می کند که مقدار ستون Item Type برابر "IRAN" باشد.
• ضرب این دو آرایه منطقی فقط سطرهایی را نگه می دارد که هر دو شرط را همزمان داشته باشند.

از منظر محاسباتی، اکسل مقادیر TRUE/FALSE را به 1/0 تبدیل می کند و سپس روی آن ها عمل انجام می دهد. این رویکرد برای کسانی که ذهن الگوریتمی یا تجربه برنامه نویسی دارند، بسیار خواناتر و منطقی تر از ساخت Criteria Range است.

وقتی منطق فیلتر، معیارها و خروجی همگی در یک فرمول باشند، فهم و ویرایش آن بسیار ساده تر است.

اگر داده ها تغییر کنند، ردیفی اضافه یا حذف شود، یا معیار فیلتر از طریق یک سلول یا Dropdown عوض شود، خروجی بلافاصله refresh می شود.

می توان شرط ها را به ورودی کاربر، لیست کشویی، یا کنترل های داشبورد متصل کرد.

برای کسی که با منطق بولی، آرایه ها و فرمول های ترکیبی آشناست، FILTER بسیار شفاف تر و ساخت یافته تر است.

FILTER را می توان با توابعی مثل SORT ، UNIQUE و دیگر توابع پویا ترکیب کرد تا خروجی های تحلیلی قوی تری ساخت.

مثلاً:

=SORT(FILTER(A1:N100,(A1:A100="West Asia")*(C1:C100="IRAN"),"No Records"))