تابع FILTER ؛ ابزاری قدرتمند در دست کاربران برای فیلتر کردن داده‌ها
اغلب کاربران معمولاً عملیات فیلتر را در اکسل چگونه انجام می‌دهند؟ در بیشتر موارد، با ابزار Auto Filter و در حالت‌های یکم پیچیده‌تر با ابزار Advanced Filter عملیات فیلتر در اکسل انجام می‌شود. با وجودی که این روش‌ها سریع و قدرتمند هستند، یک اشکال قابل توجه دارند.
پس از انجام عملیات فیلتر، اگر داده‌ها تغییر کنند، نتیجه فیلتر بطور خودکار بروزرسانی نمی‌شوند. به عبارت دیگر شما باید دوباره عملیات فیلتر را انجام دهید. همین امر باعث شد تا شرکت مایکروسافت، تابع FILTER را در نسخه‌های جدید خود یعنی Office 2021 و Office 365 ارائه نماید.
تابع FILTER در اکسل ابزاری قدرتمند برای فیلتر کردن داده‌ها به صورت پویا و انعطاف پذیر است. تابع FILTER این امگان را به کاربر می‌دهد تا داده‌ها را بر اساس معیارهای مختلف فیلتر و نتایج را به صورت یک آرایه نمایش دهد.
مزایای استفاده از تابع FILTER :
- پویایی: تابع FILTER به طور خودکار با ایجاد هر تغییری در داده‌ها، نتایج را بروزرسانی می‌کند ؛
- انعطاف پذیری: کاربر می‌تواند از معیارهای مختلف از جمله مقادیر سلول، متن، توابع و حتی مقادیر خالی برای فیلتر کردن داده‌ها استفاده کند ؛
- کارایی: تابع FILTER به کاربر این امکان را می‌دهد تا داده‌ها را به سرعت و به طور موثر فیلتر کند.
نکته:
توجه داشته باشید که تابع FILTER جزو دسته توابع آرایه‌ای اکسل می‌باشد. به این معنی که نتیجه آن یک آرایه از مقادیر است و به طور خودکار از محل سلولی که در آن فرمول را وارد کرده‌اید، شروع می‌شود.
دستور کلی تابع FILTER :
=FILTER(array; include; [if_empty])
همان طور که مشخص است، تابع FILTER دارای سه آرگومان است که دو آرگومان اجباری و یک آرگومان آن اختیاری است.
- آرگومان array : محدوده‌ای از داده‌ها که قصد دارید عملیات فیلتر بر روی آن انجام شود ؛
- آرگومان include : با استفاده از این آرگومان می‌توانید معیارهای مربوط به فیلتر را وارد کنید ؛
- آرگومان if_empty : مقداری است که به عنوان جایگزین سلول‌های خالی ارائه خواهد شد. این آرگومان اختیاری است.
به یک مثال توجه کنید.
فرض کنید می‌خواهید لیست دانش آموزان و نمرات آنها که در محدوده A2:D10 قرار دارد را بر اساس نمره آنها فیلتر کنید. برای این کار می‌توانید از تابع FILTER به صورت زیر استفاده کنید:
=FILTER(A2;D10 ; A2:A10 > 80)
نتیجه فرمول فوق، نمایش لیست تمام دانش آموزانی است که نمره آنها بیشتر از 80 است. همان طور که گفته شد، می‌توانید از آرگومان سوم این تابع به صورت اختیاری استفاده کنید. در مثال فوق، می‌توانید از عباراتی مانند “بدون نتیجه” یا “موجود نیست” یا حتی “” استفاده کنید تا در صورت موجود نبودن نتیجه برای عملیات فیلتر، با عبارت خطا (که معمولا !CALC# است) مواجه نشوید.
نکته:
دقت داشته باشید که می‌توانید برای فیلتر کردن داده‌ها از چند معیار استفاده کنید. همچنین می‌توانید از توابعی مانند AND و OR نیز در معیارها استفاده کنید. شما می‌توانید از تابع FILTER برای انجام عملیات فیلتر در چندین جدول نیز استفاده کنید.
برای فیلتر کردن مؤثر در اکسل با استفاده از فرمول، چند نکته مهم وجود دارد که باید به آنها توجه کنید:
تابع FILTER بسته به اینکه ساختار داده‌های اصلی چگونه باشد، نتایج را به طور خودکار به صورت عمودی یا افقی ارائه می‌کند. بنابراین، حتما مطمئن شوید که همیشه تعداد کافی سلول خالی در پایین و سمت چپ وجود دارد. در غیر این صورت با خطای SPILL# مواجه خواهید شد.
نتایج تابع FILTER پویا هستند، به این معنی که با تغییر مقادیر در مجموعه داده‌های اصلی، به طور خودکار بروز می‌شوند. با این حال، محدوده معرفی شده برای آرگومان array زمانی که ورودی جدیدی به داده‌های اصلی اضافه شود، بروز نمی‌شوند. اگر می‌خواهید اندازه array به طور خودکار تغییر کند، آن را به یک Table تبدیل کنید و فرمول‌هایی را با ارجاعات ساختار یافته بسازید یا یک محدوده با نام پویا ایجاد کنید.
اطلاعات بیشتر در زمینه Table را می‌توانید از اینجا مطالعه کنید.
به چند مثال در خصوص تابع FILTER توجه کنید
مثال 1) عملیات فیلتر بر اساس یک معیار
فرض کنید می‌خواهید لیست کارکنان شرکت را بر اساس جنسیت آنها فیلتر کنید. با استفاده از تابع FILTER به صورت زیر عمل کنید :
=FILTER(A2:B10 ; B2:B10=”زن”)
فرمول فوق، نحوه استفاده از تابع FILTER با یک معیار است و نتیجه آن، نمایش لیست تمام کارکنان خانم خواهد بود.
مثال 2) عملیات فیلتر بر اساس چند معیار (AND)
فرض کنید می‌خواهید لیست محصولات را بر اساس قیمت و موجودی آن فیلتر کنید. می‌توانید از تابع FILTER به صورت زیر استفاده کنید :
=FILTER(A2:C10 ; (B2:B10<50000000) * (C2:C10<5))
فرمول فوق، نحوه استفاده از تابع FILTER با دو معیار است و نتیجه آن، نمایش لیست محصولاتی است که قیمت آنها کمتر از 5 میلیون تومان و تعداد آنها نیز کمتر از 5 عدد است.
همان طور که مشخص است، در فرمول فوق بین دو شرط علامت ضرب قرار دارد و به این معنی است که تنها رکوردهایی بازگردانده می‌شود که حائز هر دو شرط باشد. به عبارت دیگر در چنین مثالی، اگر نتیجه یک شرط TRUE باشد، مقدار یک و در غیر اینصورت مقدار صفر را خواهد گرفت. بنابراین، تنها مواردی در آرایه خروجی مشاهده می‌شوند که تمام شرط‌ها در مورد آنها مقدار TRUE را داشته باشد.
مثال 3) عملیات فیلتر بر اساس چند معیار (OR)
مورد مطرح شده در مثال 2 را در نظر بگیرید. می‌خواهیم لیست محصولات را بر اساس قیمت و موجودی به گونه‌ای فیلتر کنیم که قیمت آنها کمتر از 5 میلیون تومان یا تعداد آنها کمتر از 5 عدد باشد. برای این کار می‌توانید از فرمول زیر استفاده کنید :
=FILTER(A2:C10 ; (B2:B10<50000000) + (C2:C10<5))
کافی است به جای علامت ضرب، علامت جمع را قرار دهید. نتیجه متفاوت خواهد بود.
مثال 4) عملیات فیلتر بر اساس چند معیار (AND و OR)
می‌خواهم کمی استفاده از این تابع را پیچیده تر کنم. فرض کنید لیست محصولاتی را نیاز داریم که تعداد آنها کمتر از 5 عدد بوده و (قیمت آنها بیشتر از 10 میلیون تومان یا کمتر از 5 میلیون تومان) است.
برای این کار از دو سلول کمکی استفاده خواهیم کرد. تعداد مورد نظر را در سلول E2 و مبالغ مورد نظر را در سلول‌های F2 و F3 قرار می‌دهیم. نتیجه مورد نظر با استفاده از فرمول زیر به دست می‌آید :
=FILTER(A2:C10 ; (C2:C10<E2) * ((B2:B10>F2) + (B2:B10<F3)))
مثال 5) عملیات فیلتر برای سلول‌هایی که حاوی یک متن خاص هستند
برای استخراج سلول‌هایی که حاوی یک متن مشخص هستند، می‌توانید از تابع FILTER به همراه توابع دیگری مانند SEARCH استفاده کنید. به طور مثال فرض کنید می‌خواهیم مشخصات کلیه مانیتورها یا اسپیکرهای موجود در لیست محصولات را استخراج کنیم. برای این کار از ترکیب توابع مطابق با فرمول زیر استفاده می‌شود :
=FILTER(A2:C15 ; ISNUMBER(SEARCH(E2 ; A2:A15)) ; “No Result”)
شرح فرمول فوق به این صورت است که تابع SEARCH، متن وارد شده در سلول E2 را در محدوده A2:A15 جستجو کرده و در صورت پیدا کردن آن، عددی را باز می‌گرداند (این عدد، جایگاه اولین کاراکتر پیدا شده در متن است). اگر متن مورد نظر را پیدا نکرد، خطای !VALUE# را باز می‌گرداند.
سپس تابع ISNUMBER، خروجی تابع SEARCH را از لحاتظ اینکه عدد هست یا خیر مورد بررسی قرار می‌دهد. همگی اعداد را به TRUE و مابقی را به FALSE تبدیل کرده و آرایه به دست آمده را به تابع FILTER ارائه می‌کند.
در مثال فوق، کلیه سلول‌هایی که حاوی عبارت “مان” (یا هر عبارت متنی دیگری که در سلول E2 وارد شود) هستند مقدار TRUE را گرفته و به تابع FILTER منتقل می‌شوند و نتیجه مطابق با درخواست کاربر، ارائه خواهد شد.
مثال 6) استفاده از تابع FILTER برای استخراج ردیف‌هایی که حاوی سلول خالی هستند
فرض کنید می‌خواهید ردیف‌هایی از یک محدوده که حاوی سلول خالی هستند را استخراج گنید. برای این کار از تابع FILTER به صورت زیر استفاده می‌شود :
=FILTER(A2:C15 ; (A2:A15=””) + (B2:B15=””) + (C2:C15=””))
مثال 7) چگونه مقادیر تکراری یک لیست را فیلتر کنیم
هنگام کار با شیت‌های بزرگ یا استفاده از منابع مختلف داده، این احتمال هست که داده تکراری وجود داشته باشد.
اگر به دنبال استخراج موارد منحصر به فرد هستید، می‌توانید از تابع UNIQUE استفاده کنید. این تابع در نوشته دیگری به طور مفصل مورد بررسی قرار خواهد گرفت.
اما اگر هدف شما فیلتر کردن موارد تکراری است، یعنی استخراج داده‌هایی که بیش از یک بار در لیست آمده‌اند، باید از تابع FILTER به همراه تابع COUNTIFS استفاده کنید.
ایده این است که تعداد رخدادها را برای همه رکوردها به دست آوریم و آنهایی که بزرگتر از 1 هستند را استخراج کنیم. برای بدست آوردن تعداد، محدوده یکسانی را برای هر جفت criteria_range و criteria در تابع COUNTIFS مشخص خواهیم کرد. در واقع اگر در یک لیست، تمام فیلدهای یک رکورد عینا تکرار شده باشد، می‌توان از طریق دستور زیر آنها را فیلتر نمود.
عملیات فیلتر در تصویر فوق مطابق با آنچه که توضیح داده شد، انجام شده است. نتیجه کاملا مشخص می‌باشد و فرمولی که برای این کار استفاده شده، به شرح زیر است :
در بیشتر موارد، فیلتر کردن تمام ستون‌های یک محدوده با یک فرمول واحد، چیزی است که کاربران اکسل انجام می‌دهند. اما اگر محدوده حاوی ده‌ها یا حتی صدها ستون باشد، مطمئناً ممکن است بخواهید نتایج را به چند مورد از مهمترین آنها محدود کنید.
مثال 8) انجام عملیات فیلتر در ستون‌های مجاور
در شرایطی که می‌خواهید تنها برخی از ستون‌های همجوار در نتیجه FILTER ظاهر شوند، فقط آن ستون‌ها را در array معرفی کنید، زیرا این آرگومان تعیین می‌کند کدام ستون‌ها در نتیجه فیلتر ظاهر شوند.
در این مثال، قصد داریم تنها برای ستون‌های اول و دوم محدوده همراه با یک معیار، عملیات فیلتر را انجام دهیم. بنابراین محدوده A2:B18 را به عنوان آرگومان array معرفی می‌کنیم و پس از آن معیار را مشخص می‌نمائیم. نتیجه به صورت زیر خواهد بود.
مثال 9) انجام عملیات فیلتر در ستون‌های غیر مجاور
برای اینکه تابع FILTER ستون‌های غیر مجاور را استخراج کند، از یک ترفند جذاب استفاده می‌کنیم :
- یک فرمول FILTER با استفاده از کل جدول و شرط(های) مورد نظر بسازید ؛
- فرمول بالا را در یک تابع FILTER دیگر قرار دهید. برای پیکربندی تابع، از یک آرایه ثابت با مقادیر TRUE و FALSE یا 1 و 0 به عنوان آرگومان include استفاده می‌گردد، که در آن TRUE (1) مشخص کننده ستون‌هایی است که باید نگه داشته شوند و FALSE (0) نیز مشخص کننده ستون‌هایی است که باید حذف شوند.
به طور مثال، برای استخراج ستون اول یعنی محصول و ستون سوم یعنی تعداد، از {1,0,1} یا {TRUE,FALSE,TRUE} به عنوان آرگومان include در تابع FILTER استفاده می‌گردد :
چه مواقعی تابع FILTER خطا می‌دهد
اگر فرمولی دارید که حاوی تابع FILTER است و منجر به خطا شده، به احتمال زیاد، این خطا یکی از موارد زیر خواهد بود.
خطای !CALC#
اگر آرگومان اختیاری if_empty حذف شود و هیچ نتیجه‌ای مطابق با معیارهای تابع FILETR یافت نشود، این خطا نمایش خواهد یافت. دلیل آن این است که در حال حاضر اکسل از آرایه‌های خالی پشتیبانی نمی‌کند. به عبارت دیگر اکسل نمی‌تواند یک آرایه خالی را باز گرداند. برای جلوگیری از چنین خطایی، حتماً همیشه مقدار if_empty را در فرمول‌های خود تعریف کنید.
خطای VALUE#
اگر آرگومان‌های array و include ابعاد برابری نداشته باشند، این خطا نمایش خواهد یافت.
خطاهای N/A# یا VALUE# یا…
اگر مقداری وارد شده در آرگومان include خطا باشد یا نتوان آن را به مقادیر منطقی تبدیل کرد، ممکن است خطاهای مختلفی رخ دهد.
خطای NAME#
اگر بخواهید از تابع FILTER در نسخه‌های قدیمی اکسل استفاده کنید با این خطا مواجه خواهید شد. به یاد داشته باشید که این تابع فقط در Office 365 و Excle 2021 موجود است. بنابراین در این نسخه‌ها اگر نام تابع را اشتباه بنویسید، خطای #NAME رخ می‌دهد.
خطای SPILL#
این خطا در صورتی رخ می‌دهد که یک یا چند سلول در محدوده خروجی تابع، کاملا خالی نباشد. برای رفع آن، باید به اندازه کافی، سلول خالی برای درج نتایج تابع در مقصد وجود داشته باشد.به طور مثال فرض کنید نتیجه تابع FILTER نیاز به 10 سلول در یک ستون داشته باشد، اما یکی از ده سلول، خالی نباشد. در اینجا کاربر با خطای SPILL# مواجه خواهد شد.
در آینده سعی خواهم کرد در خصوص این خطا (که شامل توابع دیگری نیز می‌شود) توضیحات کاملی ارائه نمایم.
