سبد خرید
0

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

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

تابع فیلتر در گوگل شیت و کاربردهای آن

۵/۵ - (۱ امتیاز)

مثال هایی کاربردی از تابع FILTER در گوگل شیت

در اکسل ابزار فیلتر بسیار مورد استفاده قرار میگره، اما تابعی با این موضوع در اکسل وجود نداره. در مقالات قبلی به معرفی تابع FILTER و همچنین چند مثال از این تابع پرداختیم در این مقاله قصد داریم به طور اختصاصی تر کاربردهای این تابع رو بررسی کنیم. اگر دوست داری با توابع بیشتری از گوگل شیت آشنا بشی حتما مقاله توابع پیشرفته و قدرتمند گوگل شیت رو بخون.

آرگومان های تابع FILTER

آرگومان های تابع FILTER به شکل زیر هست:

=FILTER (range, condition1, [condition2])

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

نکته:

  • هر شرطی که در تابع FILTER نوشته میشه باید اندازه محدوده اون با محدوده داده ها که در قسمت range انتخاب شده یکی باشه. مثالا اگر محدوده داده ها از ردیف یک شروع میشه تا ردیف ۱۰، شرط هم باید از ردیف یک شروع بشه و به ردیف ۱۰ ختم بشه.
  • اگر از چندین شرط استفاده می کنیم، همه آنها باید برای ستون ها یا ردیف ها تعریف شده باشن. تابع FILTER اجازه استفاده از شرط های مختلط رو نمیده.
  • ویژگی اصلی که این تابع داره این هست که داده های اصلی رو حفظ می کنه و در درمحدوده ای دیگری نزدیک به محل داده های اصلی می تونیم اطلاعات فیلتر شده رو مشاهده کنیم.

 

با در نظر گرفتن نکات گفته شده، در ادامه مقاله مثال های کاربردی از تابع FILTER رو بررسی می کنیم.

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

دیتابیس سفارشات

شکل ۱- دیتابیس سفارشات

فیلتر کردن متن با استفاده از تابع Filter

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

=FILTER (A1:E15, E1:E15=”با تاخیر”)

همان طور که از فرمول مشخص هست محدوده اعمال شرط ستون E هست و می خواهیم براساس اطلاعات ستون E اطلاعات محدوده A1:E15 رو فیلتر کنیم. خروجی فرمول در شکل زیر نمایش داده شده:

فیلتر کردن سفارشات تاخیر دار

شکل ۲- فیلتر کردن سفارشات تاخیر دار

مثال ۲:در این مثال می خواهیم سفارشاتی رو فیلتر کنیم که با تاخیر تحویل نشدن یعنی یکی از از دو حالت “در راه” یا “به موقع” رو دارن. برای اینکار فرمول به صورت زیر تغییر خواهد کرد:

=FILTER (A1:E15, E1:E15 <> “با تاخیر”)

شرط رو به این صورت تعریف کردیم که تمام مواردی که مخالف عبارت “با تاخیر” در ستون E هستن رو نمایش بده. علامت <> به معنی نامساوی هست. نتیجه فیلتر در شکل زیر نمایش داده شده:

فیلتر کردن سفارشات بدون تاخیر

شکل ۳- فیلتر کردن سفارشات بدون تاخیر

مثال ۳: در ستون “شناسه سفارش” قسمت انتهایی شناسه سفارش به اختصار نام شهر نوشته شده (MSH مشهد، THR تهران، ESF اصفهان، YZD یزد، SHR شیراز(. در این مثال می خواهیم سفارشاتی رو فیلتر کنیم که شناسه سفارش آن ها حاوی عبارت MSH باشه. در این حالت فرمول نویسی نسبت به دو حالت قبل مقداری پیچیدگی داره. برای اینکه شناسه سفارش هایی رو جستجو کنیم که حاوی عبارت مورد نظر هستن از تابع SEARCH استفاده می کنیم. آرگومان های تابع SEARCH به صورت زیر هست:

=SEARCH (search_for, text_to_search, [starting_at])

search_for: عبارتی متنی هست که به دنبال آن هستیم؛

text_to_search: محدوده حاوی داده های متنی هست که می خواهیم جستجو در آن انجام بشه؛

starting_at: شماره کاراکتری رو مشخص می کنه که می خواهیم جستجو از اون شماره کاراکتر انجام بشه. این آرگومان اختیاری هست و بیشتر برای مواقعی که تعداد کاراکتر های داده های متنی یکسان هستند کاربرد داره.

با توجه به توضیحاتی که در مورد تابع SEARCH ارائه کردیم، فرمول مورد نظر برای فیلتر کردن سفارشاتی که برای مشهد هستن به صورت زیر هست:

=FILTER (A1:E15, SEARCH (“msh”, A1:A15))

خروجی فرمول بالا در شکل زیر نمایش داده شده:

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

شکل ۴- فیلتر کردن سفارشات مربوطه به شهر مشهد

نکته:
در صورتی که اندازه حروف برامون مهم باشه به جای تابع SEARCH باید از تابع FIND استفاده کنیم.

 

فیلتر کردن تاریخ و زمان

برای فیلتر کردن تاریخ و زمان علاوه بر تابع FILTER باید از توابع دیگری هم کمک بگیریم که این توابع براساس نوع شرطی که تعریف می کنیم می تونه یکی از توابع DAY، MONTH، YEAR، DATE و TIME باشه. در ادامه مثال هایی برای فیلتر کردن تاریخ و زمان ارائه می کنیم:

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

=FILTER (A1:E15, C1:C15=DATE (2021, 6 ,26))

خروجی فرمول بالا در شکل زیر نمایش داده شده:

فیلتر کردن سفارشات با تاریخ ارسال 26/06/2021

شکل ۵- فیلتر کردن سفارشات با تاریخ ارسال ۲۶/۰۶/۲۰۲۱

همان طور که در شکل ۵ مشخص شده، تعداد ۴ سفارش با تاریخ تحویل ۲۶/۰۶/۲۰۲۱ در جدول سفارشات موجود هست.

نکته:
این فرمول زمانی جواب میده که تاریخ های نوشته شده همراه با زمان نباشن. در صورتی که در ادامه تاریخ زمان هم وجود داشته باشه این فرمول نتیجه ای نخواهد داشت. برای اطمینان از فرمت تاریخ وارد شده روی سلول مورد نظر کلیک می کنیم و در نوار فرمول می تونیم ببینیم که آیا زمان هم به تاریخ اضافه شده یا خیر.

 

در صورتی که بخواهیم زمان ها رو از ادامه تاریخ ها حذف کنیم، باید در مثال قبل فرمول رو به صورت زیر تغییر بدیم:

=FILTER (A1:E15, C1:C15>=DATE (2021, 6,26), C1:C15<DATE (2021 ,6,27))

فرمول بالا یک فرمول چند شرطی هست که در خروجی این فرمول هر دو شرط صادق هستند.

مثال ۲: در صورتی که بخواهیم سفارشاتی رو فیلتر کنیم که مثلا مهلت ارسال آن ها ماه ۵ ام باشه می تونیم از فرمول زیر استفاده کنیم:

=FILTER (A1:E15, MONTH (C1:C15) =5)

در صورتی که سال خاصی هم مد نظر باشه می تونیم با استفاده از تابع YEAR اطلاعات سفارش رو فیلتر کنیم.

با توجه به فرمول بالا، سفارشات مربوط به ماه ۵ ام در شکل زیر نمایش داده شده:

سفارشات با تاریخ ارسال در ماه 5 ام

شکل ۶- فیلتر کردن سفارشات با تاریخ ارسال در ماه ۵ ام

مثال ۳: در صورتی که بخواهیم تاریخ های تحویل سفارش که بعد از تاریخ ۰۱/۰۳/۲۰۲۱ هستند رو فیلتر کنیم فرمول به صورت زیر تغییر می کنه:

=FILTER (A1:E15, D1:D15>=DATE (2021, 3, 1))

خروجی فرمول در شکل زیر نمایش داده شده:

سفارشات با تاریخ تحویل بعد از 01/03/2021

شکل ۷- فیلتر کردن سفارشات با تاریخ تحویل بعد از ۰۱/۰۳/۲۰۲۱

مثال ۴: فرض کنید محدوده داده ای به صورت زیر داریم و می خواهیم زمان های بزرگ تر از ساعت ۲:۳۰ بعد از ظهر رو فیلتر کنیم. برای این منظور فرمول رو به صورت زیر می نویسیم:

=FILTER (A1:B8, A1:A8>TIME (14, 30, 0))

زمان های بزرگتر از ساعت 2:30 بعد از ظهر

شکل ۸- فیلتر کردن زمان های بزرگتر از ساعت ۲:۳۰ بعد از ظهر

در صورتی که در مثال بالا بخواهیم به جای تابع TIME از تابع HOUR استفاده کنیم، فرمول مقداری تغییر می کنه و به شکل زیر در میاد:

=FILTER (A1:B8, HOUR (A1:A8)>=HOUR (“۱۴:۳۰:۰۰”))

فیلتر کردن زمان های بزرگتر از ساعت 2:30 بعد از ظهر با استفاده از تابع HOUR

شکل ۹- فیلتر کردن زمان های بزرگتر از ساعت ۲:۳۰ بعد از ظهر با استفاده از تابع HOUR

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

استفاده از سلول مرجع می تونه فرمول نویسی رو پویاتر و انعطاف پذیر تر بکنه. در این حالت به جای اینکه شرط رو در متن فرمول بنویسیم، آدرس سلول مرجع که مقدار مورد نظرمون در آن هست رو وارد می کنیم. با این کار دیگه لازم نیست از توابع DATE و یا TIME استفاده کنیم. برای مثال اگر بخواهیم سفارشات که تاریخ تحویل آن ها بعد از تاریخ ۰۱/۰۳/۲۰۲۱ رو فیلتر کنیم فرمول مطابق شکل زیر هست:

تاریخ های تحول بعد از 01/03/2021 با استفاده از سلول مرجع

شکل ۱۰- فیلتر کردن تاریخ های تحول بعد از ۰۱/۰۳/۲۰۲۱ با استفاده از سلول مرجع

همان طور که در شکل ۱۰ نمایش داده شده به جای استفاده از تابع DATE برای تعریف تاریخ مورد نظر، آدرس سلول D3 رو وارد کردیم و همان طور که مشخص هست نتیجه دقیقا مثل حالتیه که از تابع DATE استفاده کردیم.

در صورتی که بخواهیم سفارشات با تاخیر رو فیلتر کنیم کافیه به جای نوشتن عبارت “با تاخیر” در متن فرمول، آدرس یکی از سلول هایی که این عبارت در آن وجود داره (مثلا سلول E2)رو در فرمول وارد کنیم.

نکته:
با استفاده از سلول مرجع می تونیم از اطلاعات سایر شیت ها هم برای فیلتر کردن استفاده کنیم فقط کافیه در متن فرمول اسم شیت رو هم وارد کنیم. یک نمونه استفاده از اطلاعات سایر شیت ها برای فیلتر کردن در زیر ارائه شده:

=FILTER (Orders! A1:E20, Orders! C1:C20=Orders! C15)

فیلتر کردن چند شرطی

استفاده از چندین شرط در تابع FILTER خیلی کاربرد داره. در ادامه چند مثال برای این حالت ارائه می کنیم.

مثال ۱: در صورتی که در بانک اطلاعاتی سفارشات بخواهیم سفارشاتی که مبلغ آن ها بین ۳ میلیون تا ۱۰ میلیون هست رو فیلتر کنیم باید در تابع FILTER دو شرط تعریف کنیم که یکی مربوط به مبالغ بالای ۳ میلیون و شرط دوم مربوط به مبالغ پایین تر از ۱۰ میلیون هست در نتیجه فرمول به شکل نوشته میشه:

=FILTER (A1:E15, B1:B15>3000000, B1:B15<10000000)

خروجی فرمول در شکل زیر نمایش داده شده:

فیلتر کردن مبالغ بین 3و 10 میلیون با اعمال دو شرط

شکل ۱۱- فیلتر کردن مبالغ بین ۳و ۱۰ میلیون با اعمال دو شرط

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

خطای نمایش داده شده در صورت عدم موجود داده برای فیلتر کردن

شکل ۱۲- خطای نمایش داده شده در صورت عدم موجود داده برای فیلتر کردن

همان طور که در شکل ۱۲ نمایش داده شده، در متن خطا عنوان شده که هیچ رکوردی در محدوده پیدا نمی شه که بتونه هر دو شرط رو هم تامین بکنه و این منطقی هست. برای اینکه شرط هایی که با منطق “یا” تعریف می شن رو در تابع FILTER تعریف کنیم از الگوی زیر استفاده می کنیم:

=FILTER (A1:E15, (E1:E15 =”با تاخیر”) + (E1:E15=”در راه”))

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

که نتیجه فرمول به شکل زیر هست:

الگوی تعریف شرط های با منطق "یا" در تابع FILTER

شکل ۱۳- الگوی تعریف شرط های با منطق “یا” در تابع FILTER

فیلتر کردن چند ستون با استفاده از تابع Filter

تا اینجای کار هر مسئله ای که تعریف کردیم به این صورت بود که شرط ها در محدوده یک ستون قرار داشت. حالا فرض کنید بخواهیم سفارشاتی رو فیلتر کنیم که در راه هستن و آخرین مهلت ارسال آن ها هم ماه ۶ ام هست. همان طور که از تعریف مسئله مشخص هست دو شرط داریم که باید آن ها رو در دو ستون مجزا (ستون های E و C) بررسی کنیم. الگوی تعریف فرمول به شکل زیر هست:

=FILTER (A1:E15, E1:E15=”در راه”, MONTH (C1:C15) =6)

خروجی فرمول در شکل زیر نمایش داده شده:

فیلتر کردن براساس اطلاعات چند ستون

شکل ۱۴- فیلتر کردن براساس اطلاعات چند ستون

در این مقاله به طور اختصاصی به ابعاد مختلف تابع FILTER پرداختیم و فیلتر کردن داده های متنی، زمان و تاریخ رو با استفاده از تابع FILTER با ارائه مثال های مختلف بررسی کردیم. همچنین نحوه تعریف شرط های چندگانه با منطق And و OR رو توضیح دادیم.

چند مقاله مرتبط با فیلتر در اکسل:

آواتار
128

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

دیدگاه کاربران
  • کریمی ۲۰ اردیبهشت ۱۴۰۱ / ۲:۳۹ ب٫ظ

    سلام وقتتون بخیر. برای ایجاد نمای فیلتر موقتی چیکار باید کرد؟

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

      درود
      منظور از نمای موقتی چی هست؟

  • حسن رضا ۴ آذر ۱۴۰۰ / ۴:۱۷ ب٫ظ

    سلام بنده از فرمول آرایه ای گوگل شیت می خوام استفاده کنم ولی اجرا نمی شود همان صفحه را دانلود می کنم نشون میده به نظر شما مشکل کجاست

  • mohammad333 ۲۲ آبان ۱۴۰۰ / ۱:۰۱ ب٫ظ

    سلام .من تابع فیلتر رو در اکسل نمیتونم پیدا کنم ؟
    راهنمایی بفرمائید اکسل ۲۰۱۳

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

      درد بر شما
      این تابع فقط در گوگل شیت هست
      در نسخه ۲۰۲۱ اکسل هم اضافه شده

ارسال دیدگاه

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

توسط
تومان