
فیلتر در اکسل
فیلتر کردن یکی از مسائل پرتکرار و مهم و کاربردی هست که با روش های مختلف امکان پذیر هست. تا ورژن ۲۰۱۹ ما میتونستیم با استفاده از ابزار 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 باشن.
{۱,۱,۰}*{۰,۱,۰}={۰,۱,۰}
شکل ۳- اعمال بیش از یک شرط با منطق “و” در تابع Filter
با همین منطق میتونیم داده های یک بازه تاریخی رو فیلتر کنیم. اگر تاریخ میلادی باشه و یا شمسی بعد از ۲۰۱۶ باشه کاملا میشه مثل دو تا عدد باهاش برخورد کرد. اگر راجع به این موضوع آشنایی ندارید، مقاله مفهوم تاریخ در اکسل و تاریخ شمسی در اکسل رو حتما مطالعه کنید.
فیلتر کردن داده ها برای بیش از یک شرط (منطق “یا”)
حالا میخواهیم داده ها رو برای بیش از یک شرط فیلتر کنیم ولی با منطق “یا”. برای این کار بجای ضرب کردن با هم جمع میکنیم. منطق هم مشابه همون توضیحاتی که در بالا برای AND ارائه شد هست. نتیجه دو آرایه صفر و یک در حالت + معادل زیر است. یعنی کافیه که فقط یک یاز درایه ها در هر آرایه ۱ باشه، نتیجه نهایی هم یک خواهد بود و این معنی “یا” هست.
{۱,۱,۰}*{۰,۱,۰}={۱,۱,۰}
پس کافیه بین شرط ها علامت + بذاریم تا منطق “یا” روی شرط ها اعمال بشه.
=FILTER(A2:D11,(A2:A11=F1)+(B2:B11>G1))
شکل ۴- اعمال بیش از یک شرط با منطق “یا” در تابع Filter
شکل ۵- خطای #SPILL!
توجه داشته باشید که این تابع در ورژن ۲۰۲۱ و آفیس ۳۶۵ قابل استفاده است برای جستجوی موارد تکراری در ورژنهای قبلی باید از فرمول نویسی آرایه استفاده کنیم که میتونید این موضوع رو در مقاله جستجوی موارد تکراری مشاهده کنید.
ویدیوی آموزش تابع فیلتر
در این ویدیو میتونید نحوه کار کردن با تابع Filter در اکسل و تعریف کردن شرط های مختلف برای آن را ببینید.
سلام
اکسل بالای ۲۰۲۱ هم تابع فیلتر داره؟
سلام بله
سلام وقت بخیر
من اکسل ۲۰۲۱ رو نصب کردم ولی تابع فیلتر رو نداره
کسی میدونه چرا یا راهنمایی انجام بده!
درود بر شما
بعضی نسخه ها مشکل دارند
از کجا دانلود کردید؟
سلام و عرض ادب خدمت شما
من میخواهم در فرم اکسلم از تابع فیلتر استفاده کنم اما تنها تابع فیلتر که در اکسل به من نشون میده تابع FILTERXML در قسمت قسمت آپشن رفتم و دیدم تیک گزینه فرمولار اتو کامپلیت فعال هست .
ممنون میشم راهنمایی بفرمایید
درود بر شما
نسخه رو چک کردید؟ ۲۰ ۲۱ باید باشه
سلام وقت بخیر
چطوری میتونم فرمول نویسی کنم که توی خروجی فیلتر (به طور مثال توی یک ستون کلمه کشاورز، کشاورزی، کشاورزان و … فیلتر بشه ) نه فقط یک کلمه
میشه از Wildcard مثل * ، ؟ و استفاده کرد ؟
درود
بله * استفاده کنید
سلام
راهی هست این تابع به ورژن های قبلی اضافه بشه ؟
درود
خیر
مگر اینکه یک تابع با VBA بنویسدی و اضافه کنید
سلام وقت بخیر
چگونه میتوان این فیلتر را روی خود جدول اعمال کرد؟
تا همانجا بشه ادیت کرد و در جدول انجام بشه
درود
با ابزار فیلتر میتونید این کار و بکنید
چون فرمول رو نمیتونید در سلول دیتا وارد کنید
سلام
وققتتون بخیر
من یه پیش فاکتور تقریبا حرفه ای درست کردم که با فیلتر که تو اکسل ۲۰۲۱ اکیه
تو اکسلهای پایین تر اررور #name میده
میشه راهنمایی کنید مشکلش چیه
راه حلش چیه ؟
درود بر شما
خودتون جواب خودتون و دادید دیگه!
تابع فیلتر در ورژن های قبلی نیست
راهی نیست که بشه تو همه اکسلا درست نشون بده
نه
مگر اینکه فرمول رو paste formula کنید که تابع نداشته باشه
سلام
من فرمول فیلتر رو مثل اونیکه تو این آموزش هست انجام دادم ولی کار نمیکنه.
میشه کمکم کنین؟
درود بر شما
اگر عین اون انجام بشه که خطا نمیده! یجای کار درست نیست
فرمولتون بذارید تا بررسی بشه
سلام
من از یک سری دیتا فیلتر گرفتم و الان میخوام دیتا هارو به دو دسته ۵۰ تایی تقسیم کنم
یعنی به این صورت که مثلا ۶۲ تا ردیف دیتا یافته و قرار ۵۰ تا ردیف توی ستون اول و از ردیف ۵۱ تا ۶۲ در ستون دوم نشان داده شود
در واقع قراره هرچی دیتا پیدا میشه در یک صفحه A4 چاپ بشه و نیاز است که این تعداد به دو قسمت تقسیم شود
حال با این شرایط راه حل و یا راهکاری برای این موضوع وجود داره ؟
ممنون میشم راهنمایی فرمایید
درود
راه های مختلفی وجود داره برای اینکه دیتای یک رنج رو بشکونید به دیتاهایی کوچک تر
هم کد وی بی هست
هم میتونید با ترکیب تابع index و ساخت مضارب ۵۰ این کار و بکنید
با ترکیب توابع زیادی میشه این کار و انجام داد
سلام
من از این تابع در اکسل استفادع کرده بعداز گذشت چند روز این تابع از اکسل پاک شده و در لیسا توابع نیست و فقط filterxml وجود داره؟
راهکاری هست بتونم درستش کنم؟
درود
نرم افزار آفیس رو به نسخه رسمی آپدیت کنید.
سپاس
باسلام وعرض ادب خدمت شما
چگونه میتوان فرمت سلول جستجو شده را اعمال کنیم
منظورم رنگ فونت یکی یا چند داده ما در شیت داده ها ابی است در خروجی هم این رنگ اعمال شود
باسپاس فراوان
درود
تابع فیلتر فعلا روی محتوای سلول عملیات انجام میده و سرچ میکنه