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

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

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

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

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

=FILTER (range, condition1, [condition2])

همان طور که از آرگومان های تابع 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))

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

• نحوه فیلتر کردن داده ها بر اساس جنس آنها
• Paste کردن در لیست فیلتر شده
• نکات پیشرفته از فیلترها در اکسل