تابع FILTER در اکسل

تابع FILTER یکی از توابع قدرتمند اکسل (در مایکروسافت 365 و نسخه‌های جدید مانند Excel 2021) است که خروجی‌های چندردیفی و پویا تولید می‌کند. این تابع به شما امکان می‌دهد بر اساس یک یا چند شرط، از یک منطقه داده‌ای (array) فقط سطرهای مدنظر را جدا کنید و نتیجه بلافاصله در سلول‌های مجاور «spill» شود. کاربردهای رایج شامل ساخت داشبوردهای تعاملی، گزارش‌های دینامیک و جستجوهای پیشرفته است.

نحو (Syntax) تابع FILTER

مثال پایه — فیلتر بر اساس مقدار عددی

=FILTER(A2:B100, B2:B100>1000, "هیچ نتیجه‌ای یافت نشد")

در این مثال، محدوده A2:B100 شامل نام مشتری و فروش است. شرط B2:B100>1000 باعث می‌شود فقط سطرهایی که فروش آن‌ها بیشتر از 1000 است برگردانده شوند. اگر هیچ سطری مطابقت نداشته باشد متن “هیچ نتیجه‌ای یافت نشد” نمایش داده می‌شود. خروجی به صورت خودکار در سلول جاری و ردیف‌های زیر آن انتشار می‌یابد (spill).

استفاده از چند شرط (AND و OR)

برای ترکیب شروط از عملگر * برای AND و + برای OR استفاده کنید.

=FILTER(A2:C100, (B2:B100="تهران")*(C2:C100>=5000), "پیدا نشد")

فرمول بالا سطرهایی را برمی‌گرداند که هم شهر آنها “تهران” باشد و هم مقدار ستون C بزرگ‌تر یا مساوی 5000 باشد. ضرب (*) نشان‌دهنده عمل منطقی AND است. برای OR می‌توانستیم از علامت + استفاده کنیم، مثلاً (B2:B100=”تهران”)+(B2:B100=”اصفهان”).

فیلتر با جستجوی متنی (مطابقت جزئی)

=FILTER(A2:C100, ISNUMBER(SEARCH("محصولX", A2:A100)), "پیدا نشد")

چون FILTER به تنهایی از wildcard برای آرایه شرط پشتیبانی نمی‌کند، از SEARCH/ISNUMBER برای پیدا کردن متن درون سلول‌ها استفاده می‌کنیم. SEARCH بزرگ‌حروف/کوچک‌حروف را نادیده می‌گیرد و اگر متن پیدا شود ISNUMBER عددی برمی‌گرداند که به صورت TRUE تفسیر می‌شود.

ترکیب FILTER با توابع دیگر؛ مرتب‌سازی و یکتاکردن

اغلب FILTER با SORT و UNIQUE ترکیب می‌شود تا نتایج مرتب یا یکتا شوند.

=SORT(FILTER(A2:C100, B2:B100>1000), 3, -1)

این فرمول ابتدا سطرهایی که فروش آنها بیش از 1000 است فیلتر می‌کند و سپس خروجی را بر اساس ستون سوم به ترتیب نزولی مرتب می‌کند (پارامتر -1 برای نزولی).

=UNIQUE(FILTER(A2:A100, C2:C100="فعال"))

در این مثال، ابتدا سطرهایی که ستون C مقدار “فعال” دارد فیلتر می‌شود و سپس با UNIQUE مقادیر تکراری در ستون A حذف می‌شوند.

استفاده از ارجاع به سلول برای ورودی دینامیک

=FILTER(A2:C100, B2:B100>$F$1, "هیچ")

با استفاده از یک سلول ورودی (مثلاً F1) می‌توانید آستانه (threshold) فیلتر را بصورت تعاملی تغییر دهید؛ مثلاً کاربر مقدار F1 را تغییر می‌دهد و خروجی فیلتر بلافاصله بروزرسانی می‌شود.

نمونه با جدول ساختاری (Structured Table)

=FILTER(Table1, Table1[Sales]>1000, "بدون نتیجه")

اگر داده‌ها را به شکل Table درآورده باشید، می‌توانید از نام جدول و ستون‌ها استفاده کنید که خوانایی و پایداری فرمول را افزایش می‌دهد.

دریافت اولین نتیجه یا انتخاب تفصیلی

=INDEX(FILTER(A2:B100, B2:B100>1000), 1, 1)

اگر فقط بخواهید اولین سطرِ مطابقت‌دهنده را بگیرید، از INDEX روی نتیجه‌ی FILTER استفاده کنید. در مثال بالا، اولین مقدار از ستون اولِ نتایج برگردانده می‌شود.

نکات و محدودیت‌ها

• تابع FILTER تنها در Excel برای مایکروسافت 365 و نسخه‌های جدیدتر (Excel 2021 و بالاتر) در دسترس است. در نسخه‌های قدیمی باید از Advanced Filter، AutoFilter یا فرمول‌های آرایه‌ای پیچیده (INDEX/SMALL/IF) استفاده کنید.
• نتایج FILTER به صورت dynamic array «spill» می‌شوند؛ بنابراین نباید سلول‌های پایین‌تر قفل شده یا حاوی داده باشند، در غیر این صورت #SPILL! نمایش داده می‌شود.
• برای متن‌های جزئی از SEARCH یا FIND همراه با ISNUMBER استفاده کنید؛ FILTER خودش wildcard را به صورت مستقیم در include پشتیبانی نمی‌کند.
• برای فیلترهای پیچیده و بزرگ، توجه به عملکرد و زمان محاسبه مهم است؛ ترکیبات متعدد و مرجع به کل ستون می‌تواند کندی ایجاد کند.

چند حالت کاربردی (Use Cases)

• داشبورد فروش: نمایش خودکار فروشندگان با فروش بالاتر از هدف.
• صفحات گزارش: فیلتر کردن تراکنش‌ها براساس بازه تاریخ یا وضعیت پرداخت.
• جستجوی تعاملی: ساخت باکس جستجو که متن واردشده را با LIST فیلتر می‌کند.
• پاکسازی داده: استخراج سطرهای دارای خطا یا مقادیر خالی برای بررسی جداگانه.

تابع FILTER ابزاری بسیار مفید و ساده برای عملیات فیلترینگ در محیط‌های تعاملی و داشبوردی است. با ترکیب آن با توابعی مانند SORT، UNIQUE، INDEX و توابع متنی می‌توانید سناریوهای گزارش‌دهی و تحلیل را به سادگی و با دقت بالاتر پیاده‌سازی کنید.

لطفاً از کمبود ها و مشکلات این محتوا برای ما بنویسید

ارسال