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

فیلتر در اکسل

فیلتر کردن یکی از مسائل پرتکرار و مهم و کاربردی هست که با روش های مختلف امکان پذیر هست. تا ورژن ۲۰۱۹ ما میتونستیم با استفاده از ابزار filter و البته Advanced Filter داده ها رو به حالت های مختلف فیلتر کنیم و یا حتی با استفاده از توابع جستجو و فرمول نویسی های ترکیبی داده مورد نظر رو جستجو و فیلتر کنیم. در ورژن ۲۰۲۱ اکسل تابعی به نام تابع Filter به اکسل اضافه شده است. این تابع از توابع Dynamic array formula هست و خروجی رو بصورت یک آرایه نمایش میده. خبر خوب اینه که با این تابع مسئله جستجوی موارد تکراری براحتی قابل حله و در واقع اگر خروجی مقدار جستجو، چند مورد باشه، براحتی همه رو لیست میکنه. این تابع بسیار قدرتمند و کاربردی هست. در این مقاله سعی میکنیم که این تابع رو معرفی کنیم و مثال هایی رو با هم ببینیم. در مرحله اول با آرگومان های این تابع آشنا میشیم: Array: (اجباری) محدوده داده ای که میخواهیم فیلتر کنیم. Include: (اجباری) شرط مورد نظر رو طوری مینویسیم که خروجی بصورت آرایه ای از True/False باشه. If_Empty: (اختیاری) در صورتی که داده ای مطابق با شرط مورد نظر پیدا نشد، این عبارت رو نمایش خواهد داد.

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

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

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

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

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

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

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

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

شکل ۲ – استفاده از تابع 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 باشن.

{1,1,0}*{0,1,0}={0,1,0}

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

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

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

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

{1,1,0}*{0,1,0}={1,1,0}

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

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

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

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

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

ویدیوی آموزش تابع فیلتر

در این ویدیو میتونید نحوه کار کردن با تابع Filter در اکسل و تعریف کردن شرط های مختلف برای آن را ببینید.