روش استفاده از تابع filter در اکسل با مثال کاربردی

تابع FILTER در اکسل چیست؟ آموزش کامل + مثال

آیا تا به حال پیش آمده که با یک صفحهٔ گستردهٔ بزرگ از داده‌ها در اکسل مواجه شوید و بخواهید فقط بخش خاصی از آن را ببینید؟ شاید شما یک لیست فروش چندهزارتایی دارید و فقط می‌خواهید محصولاتی را ببینید که بیش از ۱۰۰ میلیون تومان فروش داشته‌اند. یا شاید یک لیست دانش‌آموزی دارید و می‌خواهید فقط دانش‌آموزانی را ببینید که هم نمرهٔ بالایی دارند و هم غیبت نداشته‌اند.

در گذشته، برای انجام چنین کاری باید از فیلتر خودکار (AutoFilter) یا فیلتر پیشرفته (Advanced Filter) استفاده می‌کردید. اما این روش‌ها یک مشکل بزرگ داشتند: پویا نبودند. یعنی اگر داده‌های شما تغییر می‌کرد، باید دوباره فیلتر را اعمال می‌کردید.

اما خبر خوب این است که مایکروسافت در نسخه‌های جدید اکسل (Microsoft ۳۶۵ و Excel ۲۰۲۱) تابعی انقلابی معرفی کرده است که همه‌چیز را تغییر می‌دهد: تابع FILTER.

در این مقالهٔ جامع، قصد داریم شما را از صفر تا صد با تابع FILTER در اکسل آشنا کنیم. با ما همراه باشید تا یاد بگیرید چگونه با استفاده از این تابع قدرتمند، داده‌های خود را به ساده‌ترین و هوشمندانه‌ترین روش ممکن فیلتر کنید.

تابع FILTER در اکسل چیست؟

تابع FILTER یکی از قدرتمندترین توابع آرایهٔ پویا (Dynamic Array Functions) در اکسل است که به شما اجازه می‌دهد داده‌ها را بر اساس یک یا چند شرط مشخص، بدون تغییر دادن داده‌های اصلی، استخراج کنید.

به زبان ساده‌تر، تابع FILTER یک «الک هوشمند» است. شما به آن می‌گویید چه داده‌هایی می‌خواهید و بر اساس چه شرایطی، و آن به‌طور خودکار داده‌های مورد نظر شما را از میان انبوه داده‌ها جدا کرده و در مکانی که مشخص می‌کنید نمایش می‌دهد.

تفاوت تابع FILTER با فیلتر معمولی اکسل

شاید بپرسید: «مگر خود اکسل قابلیت فیلتر کردن ندارد؟ چرا به یک تابع جداگانه نیاز داریم؟»

دلیلش ساده است:

همان‌طور که می‌بینید، تابع FILTER یک ارتقاء اساسی نسبت به روش‌های قدیمی فیلتر کردن است.

ساختار و اجزای تابع FILTER

برای استفاده از تابع FILTER، باید با ساختار آن آشنا شوید. این تابع سه آرگومان (پارامتر) دارد که دو تای آن‌ها اجباری و یکی اختیاری است:

=FILTER(array, include, [if_empty])

بیایید هر کدام را به زبان ساده بررسی کنیم:

۱. Array (محدودهٔ داده‌ها) – اجباری

این همان محدوده‌ای است که می‌خواهید از میان آن داده‌ها را فیلتر کنید. مثلاً اگر داده‌های شما در ستون‌های A تا C و سطرهای ۲ تا ۱۰۰ قرار دارد، آرگومان Array به این صورت نوشته می‌شود:

A2:C100

۲. Include (شرط فیلتر) – اجباری

اینجا جادوی اصلی اتفاق می‌افتد. شما در این بخش مشخص می‌کنید که بر اساس چه شرطی داده‌ها فیلتر شوند. این شرط باید یک عبارت منطقی باشد که نتیجهٔ آن برای هر سطر یا TRUE باشد یا FALSE.

مثلاً اگر می‌خواهید فقط سطرهایی را ببینید که ستون C آن‌ها بزرگ‌تر از ۱۰۰۰ است، می‌نویسید:

C2:C100>1000

۳. if_empty (پیام در صورت خالی بودن نتیجه) – اختیاری

گاهی ممکن است هیچ داده‌ای با شرایط شما مطابقت نداشته باشد. در این حالت، به‌جای اینکه اکسل یک خطای ناخوشایند نشان دهد، می‌توانید یک پیام دلخواه تعیین کنید.

مثلاً:

“نتیجه‌ای یافت نشد”

-50%

افزودن به علاقه مندی

افزودن به سبد خرید

دوره آموزش Excel

۹۹۰,۰۰۰ تومان قیمت اصلی ۹۹۰,۰۰۰ تومان بود.۴۹۵,۰۰۰ تومانقیمت فعلی ۴۹۵,۰۰۰ تومان است.

-50%

افزودن به علاقه مندی

افزودن به سبد خرید

دوره آموزش office

۱,۴۹۰,۰۰۰ تومان قیمت اصلی ۱,۴۹۰,۰۰۰ تومان بود.۷۴۵,۰۰۰ تومانقیمت فعلی ۷۴۵,۰۰۰ تومان است.

-50%

افزودن به علاقه مندی

افزودن به سبد خرید

دوره آموزش word

۹۹۰,۰۰۰ تومان قیمت اصلی ۹۹۰,۰۰۰ تومان بود.۴۹۵,۰۰۰ تومانقیمت فعلی ۴۹۵,۰۰۰ تومان است.

-50%

افزودن به علاقه مندی

افزودن به سبد خرید

دوره آموزش پاورپوینت

۹۹۰,۰۰۰ تومان قیمت اصلی ۹۹۰,۰۰۰ تومان بود.۴۹۵,۰۰۰ تومانقیمت فعلی ۴۹۵,۰۰۰ تومان است.

-50%

افزودن به علاقه مندی

افزودن به سبد خرید

دوره آموزش کار با اینترنت

۳۹۰,۰۰۰ تومان قیمت اصلی ۳۹۰,۰۰۰ تومان بود.۱۹۵,۰۰۰ تومانقیمت فعلی ۱۹۵,۰۰۰ تومان است.

-50%

افزودن به علاقه مندی

افزودن به سبد خرید

دوره آموزش مبانی کار با ویندوز

۵۹۰,۰۰۰ تومان قیمت اصلی ۵۹۰,۰۰۰ تومان بود.۲۹۵,۰۰۰ تومانقیمت فعلی ۲۹۵,۰۰۰ تومان است.

-50%

افزودن به علاقه مندی

افزودن به سبد خرید

دوره جامع کامپیوتر

۱,۹۸۰,۰۰۰ تومان قیمت اصلی ۱,۹۸۰,۰۰۰ تومان بود.۹۹۰,۰۰۰ تومانقیمت فعلی ۹۹۰,۰۰۰ تومان است.

افزودن به علاقه مندی

افزودن به سبد خرید

مینی دوره آموزشی رایگان کامپیوتر

۰ تومان

چه نسخه‌هایی از اکسل از تابع FILTER پشتیبانی می‌کنند؟

قبل از هر چیز، یک نکتهٔ بسیار مهم: تابع FILTER در همهٔ نسخه‌های اکسل وجود ندارد!

این تابع فقط در نسخه‌های زیر در دسترس است:

• ✅ Microsoft ۳۶۵ (همهٔ نسخه‌ها)
• ✅ Excel 2024
• ✅ Excel 2021
• ✅ Excel برای وب
• ✅ Excel برای اندروید و iOS

و در نسخه‌های زیر وجود ندارد:

• ❌ Excel ۲۰۱۹ و پایین‌تر
• ❌ Excel 2016
• ❌ Excel 2013

اگر از نسخه‌های قدیمی‌تر اکسل استفاده می‌کنید، متأسفانه نمی‌توانید از این تابع استفاده کنید. اما نگران نباشید! در بخش پایانی مقاله، راهکارهایی برای نسخه‌های قدیمی‌تر نیز ارائه خواهیم داد.

آموزش گام‌به‌گام تابع FILTER با مثال‌های عملی

حالا که با ساختار تابع آشنا شدیم، وقت آن است که با مثال‌های عملی، نحوهٔ استفاده از آن را یاد بگیریم.

مثال ۱: فیلتر کردن با یک شرط ساده

فرض کنید یک لیست فروش دارید که در آن ستون A نام محصول، ستون B نام فروشنده و ستون C مبلغ فروش است:

حالا می‌خواهید فقط فروش‌هایی را ببینید که مبلغ آن‌ها بیشتر از ۱,۰۰۰,۰۰۰ تومان است.

کافی است در یک سلول خالی (مثلاً E2) فرمول زیر را بنویسید:

=FILTER(A2:C6, C2:C6>۱۰۰۰۰۰۰, “نتیجه‌ای یافت نشد”)

نتیجه به این شکل خواهد بود:

توجه: خروجی تابع FILTER به‌صورت خودکار در سلول‌های مجاور گسترش می‌یابد (Spill). یعنی اگر ۳ ردیف نتیجه داشته باشد، دقیقاً ۳ ردیف را اشغال می‌کند و اگر داده‌ها تغییر کنند، نتیجه نیز به‌روز می‌شود.

مثال ۲: فیلتر کردن با شرط متنی

حالا فرض کنید می‌خواهید فقط فروش‌هایی را ببینید که فروشندهٔ آن‌ها «احمدی» است:

=FILTER(A2:C6, B2:B6=”احمدی”, “نتیجه‌ای یافت نشد”)

نتیجه:

مثال ۳: فیلتر کردن با شرط تاریخ

فرض کنید ستون D شامل تاریخ فروش است و می‌خواهید فقط فروش‌های بعد از تاریخ ۱۴۰۴/۰۱/۰۱ را ببینید:

=FILTER(A2:D100, D2:D100>DATE(۱۴۰۴,۱,۱), “نتیجه‌ای یافت نشد”)

ترکیب چندین شرط با تابع FILTER (AND و OR)

یکی از قوی‌ترین قابلیت‌های تابع FILTER، امکان ترکیب چندین شرط با هم است. برای این کار از دو عملگر مهم استفاده می‌کنیم:

• ستاره (*) به معنای AND (همهٔ شرایط باید برقرار باشند)
• علامت مثبت (+) به معنای OR (حداقل یکی از شرایط باید برقرار باشد)

ترکیب شرط با AND (عملگر *)

فرض کنید می‌خواهید فروش‌هایی را ببینید که هم فروشندهٔ آن‌ها «احمدی» باشد و هم مبلغ فروش بیشتر از ۱,۰۰۰,۰۰۰ تومان باشد:

=FILTER(A2:C6, (B2:B6=”احمدی”) * (C2:C6>۱۰۰۰۰۰۰), “نتیجه‌ای یافت نشد”)

نکته: هر شرط را داخل پرانتز قرار دهید و بین آن‌ها از علامت * استفاده کنید.

نتیجه:

ترکیب شرط با OR (عملگر +)

حالا فرض کنید می‌خواهید فروش‌هایی را ببینید که یا فروشندهٔ آن‌ها «احمدی» است یا مبلغ فروش بیشتر از ۲,۰۰۰,۰۰۰ تومان است:

=FILTER(A2:C6, (B2:B6=”احمدی”) + (C2:C6>۲۰۰۰۰۰۰), “نتیجه‌ای یافت نشد”)

نتیجه:

(همهٔ فروش‌های احمدی + فروش محصول ۳ که مبلغش بالای ۲ میلیون است)

ترکیب پیچیدهٔ AND و OR با هم

گاهی نیاز به ترکیب همزمان AND و OR دارید. مثلاً فرض کنید می‌خواهید فروش‌هایی را ببینید که:

• فروشندهٔ آن‌ها «احمدی» است و (مبلغ فروش بیشتر از ۱ میلیون یا کمتر از ۵۰۰ هزار تومان است)

=FILTER(A2:C6, (B2:B6=”احمدی”) * ((C2:C6>۱۰۰۰۰۰۰) + (C2:C6<۵۰۰۰۰۰)), “نتیجه‌ای یافت نشد”)

نکتهٔ طلایی: وقتی AND و OR را با هم ترکیب می‌کنید، حتماً از پرانتزهای مناسب استفاده کنید تا ترتیب اجرای عملیات‌ها درست باشد.

ویژگی‌های منحصربه‌فرد تابع FILTER

۱. به‌روزرسانی خودکار (Dynamic)

مهم‌ترین ویژگی تابع FILTER این است که به‌طور خودکار با تغییر داده‌ها به‌روز می‌شود. یعنی اگر یک ردیف جدید به داده‌های خود اضافه کنید یا مقداری را تغییر دهید، نتیجهٔ فیلتر شده بلافاصله بدون هیچ اقدام اضافی به‌روز می‌شود.

این ویژگی در مقابل فیلتر معمولی که باید هر بار دوباره اعمال شود، یک تحول بزرگ محسوب می‌شود.

۲. گسترش خودکار (Spill)

خروجی تابع FILTER یک آرایهٔ پویا است که به‌طور خودکار در سلول‌های مجاور گسترش می‌یابد. یعنی اگر نتیجهٔ فیلتر شما ۵ سطر باشد، دقیقاً ۵ سطر را اشغال می‌کند و اگر ۱۰ سطر باشد، ۱۰ سطر را.

۳. عدم تغییر داده‌های اصلی

تابع FILTER هرگز داده‌های اصلی شما را تغییر نمی‌دهد. خروجی در محل جداگانه‌ای نمایش داده می‌شود و داده‌های منبع دست‌نخورده باقی می‌مانند.

اشتباهات رایج و راه‌حل‌ها

۱. خطای #CALC!

اگر هیچ داده‌ای با شرایط شما مطابقت نداشته باشد و از آرگومان if_empty استفاده نکنید، اکسل خطای #CALC! را نشان می‌دهد. برای جلوگیری از این مشکل، همیشه از آرگومان سوم استفاده کنید:

=FILTER(A2:C6, B2:B6=”ناموجود”, “نتیجه‌ای یافت نشد”)

۲. خطای #VALUE!

این خطا معمولاً زمانی رخ می‌دهد که اندازهٔ آرگومان array با include همخوانی نداشته باشد. مثلاً اگر array شما A2:C100 باشد، include شما باید دقیقاً ۹۹ سطر داشته باشد (از سطر ۲ تا ۱۰۰).

۳. خطای #NAME؟

این خطا نشان می‌دهد که نسخهٔ اکسل شما از تابع FILTER پشتیبانی نمی‌کند. برای رفع این مشکل، یا نسخهٔ اکسل خود را به Microsoft ۳۶۵ یا Excel ۲۰۲۱ ارتقا دهید، یا از روش‌های جایگزین (که در ادامه توضیح می‌دهیم) استفاده کنید.

۴. تفاوت جداکنندهٔ آرگومان‌ها در نسخهٔ فارسی اکسل

در نسخه‌های فارسی و برخی نسخه‌های دیگر اکسل، به‌جای ویرگول (,) از نقطه‌ویرگول (;) به‌عنوان جداکنندهٔ آرگومان‌ها استفاده می‌شود. بنابراین فرمول شما ممکن است به این شکل باشد:

=FILTER(A2:C6; B2:B6=”احمدی”; “نتیجه‌ای یافت نشد”)

ترکیب تابع FILTER با سایر توابع قدرتمند اکسل

یکی از جذاب‌ترین قابلیت‌های تابع FILTER، امکان ترکیب آن با سایر توابع است. این ترکیب‌ها می‌توانند کارهای شگفت‌انگیزی انجام دهند.

ترکیب FILTER با UNIQUE

فرض کنید می‌خواهید لیست unique (یکتا) فروشندگانی را که فروش بالای ۱ میلیون داشته‌اند، استخراج کنید:

=UNIQUE(FILTER(B2:B100, C2:C100>۱۰۰۰۰۰۰, “نتیجه‌ای یافت نشد”))

این فرمول ابتدا فروشندگانی که فروش بالای ۱ میلیون داشته‌اند را فیلتر می‌کند و سپس اسامی تکراری را حذف می‌کند.

ترکیب FILTER با SORT

می‌خواهید فروش‌های بالای ۱ میلیون را به ترتیب نزولی (از بزرگ به کوچک) مرتب کنید:

=SORT(FILTER(A2:C100, C2:C100>۱۰۰۰۰۰۰, “نتیجه‌ای یافت نشد”), ۳, -۱)

در این فرمول، پارامتر ۳ به این معناست که بر اساس ستون سوم (مبلغ فروش) مرتب‌سازی انجام شود و -۱ به معنای نزولی است.

ترکیب FILTER با SORT و UNIQUE (ترکیب سه‌گانه)

حالا فرض کنید می‌خواهید لیست یکتای فروشندگانی که فروش بالای ۱ میلیون داشته‌اند را به ترتیب حروف الفبا مرتب کنید:

=SORT(UNIQUE(FILTER(B2:B100, C2:C100>۱۰۰۰۰۰۰, “نتیجه‌ای یافت نشد”)))

این فرمول یک «موتور جستجوی هوشمند» کامل است که در یک سلول تمام کارهای فیلتر، یکتاسازی و مرتب‌سازی را انجام می‌دهد.

ترکیب FILTER با توابع جستجوی متنی (SEARCH)

اگر می‌خواهید بر اساس قسمتی از یک متن فیلتر کنید (مثلاً همهٔ محصولاتی که کلمهٔ «لبنی» در نام آن‌ها وجود دارد):

=FILTER(A2:C100, ISNUMBER(SEARCH(“لبنی”, A2:A100)), “نتیجه‌ای یافت نشد”)

این فرمول از تابع SEARCH برای جستجوی کلمهٔ «لبنی» در ستون A استفاده می‌کند و تابع ISNUMBER بررسی می‌کند که آیا جستجو موفق بوده است یا خیر.

کاربردهای عملی تابع FILTER در زندگی واقعی

۱. مدیریت لیست دانش‌آموزان

فرض کنید معلم هستید و لیستی از دانش‌آموزان با نمرات و غیبت‌ها دارید. می‌خواهید دانش‌آموزانی که هم نمرهٔ بالای ۱۷ دارند و هم کمتر از ۳ جلسه غیبت داشته‌اند را برای تشویق انتخاب کنید:

=FILTER(A2:E100, (D2:D100>۱۷) * (E2:E100<۳), “هیچ دانش‌آموزی یافت نشد”)

۲. تحلیل فروش فروشگاه

مدیر یک فروشگاه آنلاین هستید و می‌خواهید محصولاتی که در ماه گذشته بیش از ۵۰ عدد فروش داشته‌اند و سود هر کدام بالای ۲۰٪ بوده است را استخراج کنید:

=FILTER(A2:G1000, (F2:F1000>۵۰) * (G2:G1000>۰.۲), “محصولی یافت نشد”)

۳. گزارش‌گیری از حقوق کارمندان

واحد منابع انسانی می‌خواهد لیست کارمندانی که حقوق آن‌ها بین ۵ تا ۱۰ میلیون تومان است را استخراج کند:

=FILTER(A2:D500, (C2:C500>=۵۰۰۰۰۰۰) * (C2:C500<=۱۰۰۰۰۰۰۰), “نتیجه‌ای یافت نشد”)

راهکارهای جایگزین برای نسخه‌های قدیمی اکسل

اگر از نسخه‌های قدیمی اکسل (۲۰۱۹ و پایین‌تر) استفاده می‌کنید و تابع FILTER در دسترس شما نیست، نگران نباشید. چند راهکار جایگزین وجود دارد:

۱. استفاده از فیلتر پیشرفته (Advanced Filter)

فیلتر پیشرفتهٔ اکسل می‌تواند داده‌ها را بر اساس معیارهای پیچیده فیلتر کند، اما برخلاف تابع FILTER، به‌روزرسانی خودکار ندارد.

۲. استفاده از ترکیب توابع INDEX و MATCH و IF

با ترکیب این توابع می‌توانید تا حدی رفتار تابع FILTER را شبیه‌سازی کنید، اما فرمول‌ها پیچیده‌تر و کندتر خواهند بود.

۳. استفاده از Power Query

Power Query یک ابزار قدرتمند برای تبدیل و پالایش داده‌هاست که در اکسل ۲۰۱۶ به بالا موجود است و می‌تواند جایگزین مناسبی باشد.

۴. ارتقا به Microsoft ۳۶۵

بهترین و ساده‌ترین راه، ارتقا به Microsoft ۳۶۵ است تا از تمام توابع جدید از جمله FILTER، SORT، UNIQUE و … بهره‌مند شوید.

نکات حرفه‌ای برای استفادهٔ بهتر از تابع FILTER

نکته ۱: از محدوده‌های نامگذاری شده (Named Ranges) استفاده کنید

به‌جای نوشتن A2:C100، می‌توانید به محدودهٔ خود یک نام بدهید (مثلاً Data) و از آن در فرمول استفاده کنید:

=FILTER(Data, Data[Sales]>۱۰۰۰۰۰۰, “نتیجه‌ای یافت نشد”)

این کار باعث می‌شود فرمول‌های شما خواناتر و قابل‌فهم‌تر شوند.

نکته ۲: از جداول (Tables) استفاده کنید

اگر داده‌های خود را به صورت Table (جدول) تعریف کنید، فرمول‌های شما بسیار حرفه‌ای‌تر و مقاوم‌تر در برابر تغییرات خواهند شد:

=FILTER(Table1, Table1[مبلغ]>۱۰۰۰۰۰۰, “نتیجه‌ای یافت نشد”)

نکته ۳: شرط‌های خود را در سلول‌های جداگانه قرار دهید

به‌جای اینکه شرط‌ها را مستقیماً در فرمول بنویسید، می‌توانید آن‌ها را در سلول‌های جداگانه قرار دهید و به آن سلول‌ها ارجاع دهید. این کار باعث می‌شود فرمول شما پویاتر و قابل‌تغییرتر باشد:

=FILTER(A2:C100, C2:C100>G1, “نتیجه‌ای یافت نشد”)

حالا با تغییر مقدار سلول G1، شرط فیلتر تغییر می‌کند.

نکته ۴: از عملگرهای مقایسه‌ای مختلف استفاده کنید

• > بزرگ‌تر از
• < کوچک‌تر از
• >= بزرگ‌تر یا مساوی
• <= کوچک‌تر یا مساوی
• = مساوی
• <> نامساوی

جمع‌بندی

تابع FILTER در اکسل یکی از قدرتمندترین و کاربردی‌ترین توابعی است که در سال‌های اخیر به اکسل اضافه شده است. این تابع با ویژگی‌های منحصربه‌فردی مانند به‌روزرسانی خودکار، خروجی پویا و قابلیت ترکیب با سایر توابع، تحولی عظیم در نحوهٔ فیلتر کردن و تحلیل داده‌ها ایجاد کرده است.

در این مقالهٔ جامع یاد گرفتیم:

• تابع FILTER چیست و چه کاربردی دارد
• ساختار و اجزای این تابع چگونه است
• چگونه با یک شرط ساده داده‌ها را فیلتر کنیم
• چگونه چندین شرط را با AND و OR ترکیب کنیم
• چگونه این تابع را با UNIQUE، SORT و سایر توابع ترکیب کنیم
• اشتباهات رایج و راه‌حل‌های آن‌ها
• راهکارهای جایگزین برای نسخه‌های قدیمی اکسل

حالا نوبت شماست! اکسل خود را باز کنید و با داده‌های واقعی خود، این تابع جادویی را امتحان کنید. هرچه بیشتر تمرین کنید، مسلط‌تر خواهید شد.

سوالات متداول

۱. آیا تابع FILTER در Excel ۲۰۱۹ وجود دارد؟

خیر، این تابع فقط در Microsoft ۳۶۵، Excel ۲۰۲۴ و Excel ۲۰۲۱ در دسترس است.

۲. چرا خروجی تابع FILTER با #CALC! خطا می‌دهد؟

زیرا هیچ داده‌ای با شرایط شما مطابقت ندارد و شما از آرگومان if_empty استفاده نکرده‌اید.

۳. آیا می‌توان از تابع FILTER برای فیلتر کردن ستون‌ها (نه سطرها) استفاده کرد؟

بله، با تنظیم مناسب آرگومان‌ها می‌توانید ستون‌ها را نیز فیلتر کنید، اگرچه کاربرد اصلی آن برای فیلتر کردن سطرهاست.

۴. تفاوت علامت * و + در تابع FILTER چیست؟

علامت * به معنای AND (همهٔ شرایط) و علامت + به معنای OR (حداقل یکی از شرایط) است.

۵. چگونه می‌توانم خطای #NAME? را در تابع FILTER برطرف کنم؟

این خطا نشان می‌دهد که نسخهٔ اکسل شما از این تابع پشتیبانی نمی‌کند. نسخهٔ خود را به Microsoft ۳۶۵ یا Excel ۲۰۲۱ ارتقا دهید.

برای یادگیری بیشتر دربارهٔ توابع پیشرفتهٔ اکسل، مقالات دیگر وب‌سایت ما را دنبال کنید.