آغاز ثبت نام دوره آنلاین مقدماتی تا میانی اکسل (ظرفیت محدود)
سبد خرید
1
جستجو
Generic filters

تابع Filter در اکسل ۲۰۲۱ و آفیس ۳۶۵

تابع Filter
نظر شما در مورد این آموزش

فیلتر کردن یکی از مسائل پرتکرار و مهم و کاربردی هست که با روش های مختلف امکان پذیر هست. تا ورژن ۲۰۱۹ ما میتونستیم با استفاده از ابزار filter و البته Advanced Filter داده ها رو به حالت های مختلف فیلتر کنیم و یا حتی با استفاده از توابع جستجو و فرمول نویسی های ترکیبی داده مورد نظر رو جستجو و فیلتر کنیم. در ورژن ۲۰۲۱ اکسل تابعی به نام تابع Filter به اکسل اضافه شده است. این تابع از توابع Dynamic array formula هست و خروجی رو بصورت یک آرایه نمایش میده. خبر خوب اینه که با این تابع مسئله جستجوی موارد تکراری براحتی قابل حله و در واقع اگر خروجی مقدار جستجو، چند مورد باشه، براحتی همه رو لیست میکنه.

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

در مرحله اول با آرگومان های این تابع آشنا میشیم:

Array: (اجباری) محدوده داده ای که میخواهیم فیلتر کنیم.

Include: (اجباری) شرط مورد نظر رو طوری مینویسیم که خروجی بصورت آرایه ای از True/False باشه.

If_Empty: (اختیاری) در صورتی که داده ای مطابق با شرط مورد نظر پیدا نشد، این عبارت رو نمایش خواهد داد.

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

فرض کنید داده ای مشابه به شکل ۱ داریم، میخواهیم داده هایی که در بخش صنعت هستن رو نمایش بدیم.

برای این کار کافیه فرمول رو بصورت زیر بنویسیم:

=FILTER( A2:D100 , A2:A100=F1 , “داده ای یافت نشد” )

آرگومان اول: محدوده ای که به عنوان نتیجه میخوایم نمایش داده بشه

آرگومان دوم: شرط مورد نظر در اینجا از سلول F1 استفاده شده و داخل این سلول شرط مورد نظر نوشته شده.

آرگومان سوم: در صورت عدم تطبیق داده ها با شرط، این آرگومان نمایش داده میشه.

تابع Filter ورژن 2021

شکل ۱- تابع Filter ورژن ۲۰۲۱

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

تابع Filter جهت فیلتر کردن داده های افقی

با این تابع امکان فیلتر کردن داده ها بصورت افقی هم فراهم میشه. چطوری؟

مثلا فرض کنید مطابق شکل ۲ میخواهیم بانک هایی که دولتی هستن رو به همراه نام و مبلغ پرداختی لیست کنیم.

=FILTER ( B1:I3 , B2:I2=A5)

استفاده از تابع Filter در داده های افقی

شکل ۲ – استفاده از تابع Filter در داده های افقی

فیلتر کردن داده ها برای بیش از یک شرط (منطق “و”)

وقتی بیش از یک شرط برای فیلتر کردن داریم باید شرط ها رو در هم ضرب کنیم.

مثلا میخواهیم بخش کشاورزی که مبلغ درخواستی بیش از ۷۰۰ داره رو فیلتر کنیم:

=FILTER(A2:D11,(A2:A11=F1)*(B2:B11>G1))

برای اینکه متوجه بشیم چرا شرط ها رو ضرب میکنیم، باید با مفهوم آرایه آشنا باشیم. نتیجه این عبارت رو   (A2:A11=F1)*(B2:B11>G1) در زیر میبینیم:

{FALSE;TRUE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;TRUE}*
{FALSE;TRUE;FALSE;FALSE;TRUE;FALSE;FALSE;TRUE;TRUE;TRUE}

تک تک داده های موجود در ستون A با شرط مورد نظر یعنی کشاورزی مقایسه میشن و اگر برابر باشن، True و اگر برابر نباشن، False خواهد بود. همین منطق هم برای شرط بعدی. در نتیجه دو تا آرایه داریم که شامل عبارات True, false هستند. حالا این دو آرایه تک تک در هم ضرب میشن. (برای اینکه نتیجه رو بهتر درک کنیم، مقدار True رو ۱ و مقدار False رو صفر در نظر میگیریم). حالا در یک مثال کوچکتر بخوایم نشون بدیم، نتیجه ضرب دو آرایه صفر و یک مشابه زیر خواهد بود. و این معنی AND رو داره چون فقط زمانی ۱ میشه که هر دو درایه در آرایه True باشن.

{۱,۱,۰}*{۰,۱,۰}={۰,۱,۰}

اعمال بیش از یک شرط با منطق "و"

شکل ۳- اعمال بیش از یک شرط با منطق “و” در تابع Filter

با همین منطق میتونیم داده های یک بازه تاریخی رو فیلتر کنیم. اگر تاریخ میلادی باشه و یا شمسی بعد از ۲۰۱۶ باشه کاملا میشه مثل دو تا عدد باهاش برخورد کرد. اگر راجع به این موضوع آشنایی ندارید، مقاله مفهوم تاریخ در اکسل و تاریخ شمسی در اکسل رو حتما مطالعه کنید.

فیلتر کردن داده ها برای بیش از یک شرط (منطق “یا”)

حالا میخواهیم داده ها رو برای بیش از یک شرط فیلتر کنیم ولی با منطق “یا”. برای این کار بجای ضرب کردن با هم جمع میکنیم. منطق هم مشابه همون توضیحاتی که در بالا برای AND ارائه شد هست. نتیجه دو آرایه صفر و یک در حالت + معادل زیر است. یعنی کافیه که فقط یک یاز درایه ها در هر آرایه ۱ باشه، نتیجه نهایی هم یک خواهد بود و این معنی “یا” هست.

{۱,۱,۰}*{۰,۱,۰}={۱,۱,۰}

پس کافیه بین شرط ها علامت + بذاریم تا منطق “یا” روی شرط ها اعمال بشه.

=FILTER(A2:D11,(A2:A11=F1)+(B2:B11>G1))

  اعمال بیش از یک شرط با منطق "یا"

شکل ۴- اعمال بیش از یک شرط با منطق “یا” در تابع Filter

نکته:
این تابع جزو توابع Dynamic array است و نتیجه تابع در سلول ها SPILL میشه. در واقع خودش میتونه تشخیص بده که چه محدوده ای رو برای جواب نیاز داره. برای همین اگر در مسیر جواب، سلولی پر باشه که تابع نتونه نتیجه رو بیاره، با خطای #SPILL! مواجه میشه. به شکل ۵ دقت کنید.چون در سلول H4 کلمه اکسل نوشته شده  و در واقع سلول پر هست، تابع نوشته شده در سلول F2 نمیتونه نتیجه رو Spill کنه و برای همین با خطا مواجه میشه. برای رفع خطا باید سلول H4 رو خالی کنیم.

خطای #SPILL!

شکل ۵- خطای #SPILL!

توجه داشته باشید که این تابع در ورژن ۲۰۲۱ و آفیس ۳۶۵ قابل استفاده است برای جستجوی موارد تکراری در ورژنهای قبلی باید از فرمول نویسی آرایه استفاده کنیم که میتونید این موضوع رو در مقاله جستجوی موارد تکراری مشاهده کنید.

126

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

ارسال دیدگاه

نشانی ایمیل شما منتشر نخواهد شد.

19 − هشت =

توسط
تومان