سبد خرید
0

هیچ محصولی در سبد خرید نیست.

تمام دسته ها
  • تمام دسته ها
  • Power BI
  • Power Pivot
  • Power Query
  • ابزارها
  • افزونه ها
  • اکسل مدیا
  • توابع اکسل
  • دسته‌بندی نشده
  • دیده بان
  • گوگل شیت
  • مسائل کاربردی
  • معرفی کتاب
  • نمودار ها
  • وی بی - ماکرو

تابع 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!

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

133

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

دیدگاه کاربران
  • محسن عادلخانی ۲۱ اسفند ۱۴۰۲ / ۹:۴۷ ق٫ظ

    سلام و عرض ادب خدمت شما
    من میخواهم در فرم اکسلم از تابع فیلتر استفاده کنم اما تنها تابع فیلتر که در اکسل به من نشون میده تابع FILTERXML در قسمت قسمت آپشن رفتم و دیدم تیک گزینه فرمولار اتو کامپلیت فعال هست .
    ممنون میشم راهنمایی بفرمایید

    • آواتار
      حسنا خاکزاد ۲۱ اسفند ۱۴۰۲ / ۶:۳۴ ب٫ظ

      درود بر شما
      نسخه رو چک کردید؟ ۲۰ ۲۱ باید باشه

  • شخص ۲۴ مهر ۱۴۰۲ / ۷:۳۱ ق٫ظ

    سلام وقت بخیر
    چطوری میتونم فرمول نویسی کنم که توی خروجی فیلتر (به طور مثال توی یک ستون کلمه کشاورز، کشاورزی، کشاورزان و … فیلتر بشه ) نه فقط یک کلمه
    میشه از Wildcard مثل * ، ؟ و استفاده کرد ؟

    • آواتار
      حسنا خاکزاد ۲۴ مهر ۱۴۰۲ / ۷:۱۲ ب٫ظ

      درود
      بله * استفاده کنید

  • مهدی ۴ شهریور ۱۴۰۲ / ۲:۵۶ ب٫ظ

    سلام
    راهی هست این تابع به ورژن های قبلی اضافه بشه ؟

    • آواتار
      حسنا خاکزاد ۴ شهریور ۱۴۰۲ / ۵:۴۲ ب٫ظ

      درود
      خیر
      مگر اینکه یک تابع با VBA بنویسدی و اضافه کنید

  • مریم جعفری ۲۵ مرداد ۱۴۰۲ / ۸:۴۳ ب٫ظ

    سلام
    وققتتون بخیر
    من یه پیش فاکتور تقریبا حرفه ای درست کردم که با فیلتر که تو اکسل ۲۰۲۱ اکیه
    تو اکسلهای پایین تر اررور #name میده
    میشه راهنمایی کنید مشکلش چیه
    راه حلش چیه ؟

    • آواتار
      حسنا خاکزاد ۲۵ مرداد ۱۴۰۲ / ۹:۰۷ ب٫ظ

      درود بر شما
      خودتون جواب خودتون و دادید دیگه!
      تابع فیلتر در ورژن های قبلی نیست

      • مریم جعفری ۲۷ مرداد ۱۴۰۲ / ۱:۱۰ ق٫ظ

        راهی نیست که بشه تو همه اکسلا درست نشون بده

        • آواتار
          حسنا خاکزاد ۲۷ مرداد ۱۴۰۲ / ۵:۵۱ ب٫ظ

          نه
          مگر اینکه فرمول رو paste formula کنید که تابع نداشته باشه

  • محمدِ صدرا ۶ مرداد ۱۴۰۲ / ۴:۲۵ ب٫ظ

    سلام
    من فرمول فیلتر رو مثل اونیکه تو این آموزش هست انجام دادم ولی کار نمیکنه.
    میشه کمکم کنین؟

    • آواتار
      حسنا خاکزاد ۷ مرداد ۱۴۰۲ / ۹:۴۴ ق٫ظ

      درود بر شما
      اگر عین اون انجام بشه که خطا نمیده! یجای کار درست نیست
      فرمولتون بذارید تا بررسی بشه

  • کاوه ۸ خرداد ۱۴۰۲ / ۱۱:۱۵ ب٫ظ

    سلام
    من از یک سری دیتا فیلتر گرفتم و الان میخوام دیتا هارو به دو دسته ۵۰ تایی تقسیم کنم
    یعنی به این صورت که مثلا ۶۲ تا ردیف دیتا یافته و قرار ۵۰ تا ردیف توی ستون اول و از ردیف ۵۱ تا ۶۲ در ستون دوم نشان داده شود
    در واقع قراره هرچی دیتا پیدا میشه در یک صفحه A4 چاپ بشه و نیاز است که این تعداد به دو قسمت تقسیم شود
    حال با این شرایط راه حل و یا راهکاری برای این موضوع وجود داره ؟
    ممنون میشم راهنمایی فرمایید

    • آواتار
      حسنا خاکزاد ۹ خرداد ۱۴۰۲ / ۸:۰۶ ب٫ظ

      درود
      راه های مختلفی وجود داره برای اینکه دیتای یک رنج رو بشکونید به دیتاهایی کوچک تر
      هم کد وی بی هست
      هم میتونید با ترکیب تابع index و ساخت مضارب ۵۰ این کار و بکنید

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

  • محمد ۱۲ دی ۱۴۰۱ / ۱۰:۴۰ ق٫ظ

    سلام
    من از این تابع در اکسل استفادع کرده بعداز گذشت چند روز این تابع از اکسل پاک شده و در لیسا توابع نیست و فقط filterxml وجود داره؟
    راهکاری هست بتونم درستش کنم؟

    • سامان چراغی ۷ اردیبهشت ۱۴۰۲ / ۸:۵۴ ب٫ظ

      درود
      نرم افزار آفیس رو به نسخه رسمی آپدیت کنید.

  • محمد شیوانی ۲۸ مرداد ۱۴۰۱ / ۱۱:۰۶ ق٫ظ

    سپاس

  • بابک ظاهری ۱ مرداد ۱۴۰۱ / ۱:۵۳ ب٫ظ

    باسلام وعرض ادب خدمت شما
    چگونه میتوان فرمت سلول جستجو شده را اعمال کنیم
    منظورم رنگ فونت یکی یا چند داده ما در شیت داده ها ابی است در خروجی هم این رنگ اعمال شود
    باسپاس فراوان

    • آواتار
      حسنا خاکزاد ۱ مرداد ۱۴۰۱ / ۲:۴۶ ب٫ظ

      درود
      تابع فیلتر فعلا روی محتوای سلول عملیات انجام میده و سرچ میکنه

ارسال دیدگاه

نشانی ایمیل شما منتشر نخواهد شد. بخش‌های موردنیاز علامت‌گذاری شده‌اند *

توسط
تومان