تابع FILTER در اکسل همراه با مثال های فرمولی

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

فیتلر به طور معمول در اکسل چگونه انجام میشود؟ در بیشتر مواقع با استفاده از Auto Filter، در موارد پیچیده تر با استفاده از فیلتر پیشرفته (Advanced Filter). این دو روش سرعت و قدرت بسیار خوبی دارند اما دارای نقطه ضعف مهمی نیز هستند-وقتی داده های شما تغییر میکنند آنها به صورت اتوماتیک به روزرسانی نمیشوند، به این معنی که شما باید دوباره پاک کنید و سپس فیلتر کنید- بعد از مدتها انتظار برای آمدن تابع FILTER در اکسل 365، اکنون فیلتر کردن به یک ویژگی معمولی تبدیل شده است. برخلاف دو راه قبلی که برای فیلتر کردن بیان شد، فرمول های اکسل به صورت اتوماتیک با هر تغییری به طور خودکار مجددا محاسبه میشوند، بنابراین شما فقط لازم است که یک بار تنظیمات فیلتر را انجام دهید!!

تابع FILTER در اکسل

تابع FILTER در اکسل برای فیلتر کردن یک محدوده داده با شروطی که شما تعریف کرده اید؛ استفاده میشود.

این تابع به دسته توابع آرایه ای پویا  (Dynamic Arrays) تعلق دارد. دلیلش این است که یک آرایه از مقادیر به صورت اتوماتیک به یک محدوده از سلول ها وارد میشود، نقطه شروع سلولی است که شما فرمول را در آن وارد کرده اید.

ساختار تابع FILTER به صورت زیر است:

FILTER(array,include,[if_empty])

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

• Array: آرگومان اجباری- یک محدوده یا یک آرایه از مقادیر که شما میخواهید فیلتر کنید.
• Include: آرگومان اجباری- شرط های تعیین شده به عنوان آرایه Boolean (مقادیر true و false). به صورت قدی (وقتی داده ها در ستون ها هستند) و به صورت عرضی (وقتی داده ها در ردیف ها قرار دارند.) باید مساوی آرگومان array باشند.
• If_empty: آرگومان اختیاری- مقداری که وقتی شرطها برقرار نباشند؛ برگردانده میشود.

نکته: تابع filter در نسخه آفیس 365 قابل دسترس است.

فرمول های مقدماتی FILTER اکسل

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

با توجه به داده های زیر، فرض کنید شما میخواهید مقادیر خاصی از رکوردها را با یک مقدار خاص در Group و ستون، مثلا GroupB استخراج کنید. برای انجام این کار، در  آرگومان include عبارت “B2:B13=”B را قرار میدهیم، که در صورت مطابقت داشتن با مقدار “B” یک آرایه Boolean تولید میکند.

=FILTER(A2:C13, B2:B13="B", "No results")

در عمل، اگر شرط را در یک سلول جداگانه وارد کنیم راحت تر است؛ به عنوان مثال شرط را در سلول F1 وارد کرده ایم و به جای وارد کردن شرط به صورت مسقیم، کافی است از سلول مرجع (نام سلولی که در آن شرط را نوشته ایم) در فرمول استفاده کنیم:

=FILTER(A2:C13, B2:B13=F1, "No results")

برخلاف ویژگی Filter در اکسل، این تابع تغییری در داده های اصلی ایجاد نمیکند. رکوردهای فیلتر شده در یک محدوده جداگانه (E4:G7 در تصویر زیر) استخراج میشوند، نقطه شروع جایی است که فرمول در آن نوشته شده است:

اگر هیچکدام از رکوردها با شرط تعیین شده مطابقت نداشته باشند، فرمول مقداری که شما در آرایه IF_empty قرار داده اید را برمیگرداند:

اگر نمیخواهید عبارتی برگردانده شود، برای در آخرین آرگومان یک رشته خالی (“”) وارد کنید؛ فرمول به شکل زیر خواهد بود:

=FILTER(A2:C13, B2:B13=F1, "")

در صورتی که داده های شما افقی باشند ماننده تصویر زیر، تابع FILTER نیز به همین صورت عمل میکند! فقط توجه داشته باشید که محدوده آرگومان array و include  را درست تعریف کنید؛ به این صورت که آرایه منبع و آرایه Boolean باید عرض یکسانی داشته باشند:

=FILTER(B2:M4, B3:M3= B7, "No results")

نکات مهم تابع FILTER اکسل

برای اینکه به صورت موثر با استفاده از فرمول ها در اکسل عمل فیلتر را انجام دهید؛ باید به چند نکته مهم توجه کنید:

• تابع filter به صورت اتوماتیک نتایج را به صورت افقی یا عمومی در ورک شیت وارد میکند، بستگی دارد که داده های اصلی شما چگونه سازماندهی شده باشند. بنابراین، لطفا مطمئن شوید که تعداد سلول خالی به اندازه کافی به سمت پایین یا به سمت راست دارید، در غیر اینصورت با خطای #SPILL error مواجه خواهید شد.
• نتایج تابع filter اکسل به صورت پویا است، به این معنا که هر وقت مقادیر اصلی شما تغییر کند آنها به صورت اتوماتیک به روزرسانی میشوند. گرچه، آرگومان array وقتی شما یک منبع داده جدید اضافه میکند، به روز رسانی نمیشود. اگر میخواهید آرگومان array به صورت خودکار تغییر اندازه بدهد، باید آن را در یک جدول (table) اکسل وارد کنید و با Structured references فرمول خود را بسازید یا یک محدوده نام داینامیک (dynamic named range) ایجاد کنید.

چگونگی فیلتر کردن در اکسل – مثال های فرمولی

حالا که متوجه شدید فرمول Filter چگونه کار میکند؛ نوبت آن است که یاد بگیرید فرمول را برای حل مسائل پیچیده چگونه بنویسید.

فیلتر کردن با شرط های چندگانه (شرط AND)

برای فیلتر کردن داده ها با شرطهای چندگانه، دو یا چند شرط را در قسمت آرگومان include وارد کنید:

=FILTER(array, (range1=criteria1) * (range2=criteria2), "No results")

همانطور که میبینید بین دو شرط علامت ضرب قرار گرفته شده است. در مثال بالا عمل ضرب مربوط به شرط and میباشد، و اطمینان میدهد که تنها رکوردهایی برگردانده میشوند که تمام شرطها در مورد آنها درست باشد، به صورت فنی به صورت زیر عمل میکند:

نتیجه هر یک از شرط ها یک آرایه Boolean است، به این صورت که true مساوی 1 و false مساوی 0 است. سپس، تمام عناصر در همان موقعیتی که قرار گرفته اند؛ ضرب میشوند. همیشه ضرب در صفر، صفر را برمیگرداند؛ فقط در صورتی که تمام شرط ها برای آنها true باشد نتیجه غیرصفر (یک) خواهد بود؛ در نتیجه فقط آن مواردی که تمام شرط ها در مورد آنها صحیح باشد وارد آرایه نتیجه خواهند شد و در نتیجه فقط آن موارد (آنهایی که تمام شرطها در موردشان true بود) استخراج میشوند.

اگه متوجه این توضیحات نشدید نگران نباشید،مثال های زیر این فرمول را در عمل نشان میدهد.

مثال 1: فیلتر ستون های مختلف در اکسل

بیایید برای توسعه فرمول Filter دراکسل، دو ستون از اکسل را فیلتر کنید: Group(ستونB) و Wins(ستونC).

برای این کار، این شرطها را تعیین کرده ایم:نام گروه هدف را که شرط اول (criteria1) است در سلول F2 تایپ کن و در سلول F3 حداقل تعداد بردها (criteria2) را وارد کن.

با توجه به اینکه داده های ما در A2:C13 یعنی آرگومان array قرار دارند، groups در B2:B13  در قسمت(range1) فرمول و بردها در C2:C13 در قسمت(range2) فرمول قرار دارند، فرمول شبیه فرمول زیر میشود:

=FILTER(A2:C13, (B2:B13=F2) * (C2:C13>=F3), "No results")

مثال 2: داده بین داده ها را فیلتر کن

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

با توجه به داده هایی که داریم، یک ستون (ستون D) را اضافه میکنیم که در آن تاریخ آخرین برد نشان داده شده است. و حالا، میخواهیم بردهایی  را که در یک دوره زمانی خاص مثلا بین may 17 و may 31 اتفاق افتاده است؛ استخراج کنیم.

دقت کنید که در این مثال، برای هر دو شرط محدوده یکسانی تعیین کرده ایم:

=FILTER(A2:D13, (D2:D13>=G2) * (D2:D13<=G3), "No results")

فیلتر کردن با شرط های چندگانه (شرط OR)

برای استخراج داده بر اساس شرط های چندگانه Or، مانند آنچه در مثال قبلی نشان داده شد عمل کنید با این تفاوت که به جای ضرب کردن آنها، از جمع استفاده میکنید. وقتی ارایه های Boolean با عبارات خلاصه شده برگردانده شدند؛ ورودی هایی که هیچ یک از شرط ها در مورد آنها درست نبوده است در آرایه نتیجه مقدار صفر برای آنها در نظر گرفته میشود (برای مثال تمام شرط ها False هستند.) و چنین ورودی هایی فیلتر خواهند شد. و ورودی هایی که حداقل یک شرط برای آنها True باشد استخراج خواهند شد.

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

FILTER(array, (range1=criteria1) + (range2=criteria2), "No results")

به عنوان مثال، بیا بازیکنانی که این یا آن تعداد برد را داشته اند، استخراج کنیم.

با فرض اینکه منبع داده ها در A2:C13، بردها در C2:C13 و تعداد بردهایی که مد نظر ما است در سلول های F2 و F3 قرار دارند، فرمول به شکل زیر خواهد بود:

=FILTER(A2:C13, (C2:C13=F2) + (C2:C13=F3), "No results")

در نتیجه شما میدانید، کدام بازیکن 4 بازی را برده است و کدام بازیکن هیچکدام از بازی ها را برنده نشده است.

فیلتر بر اساس شرط های AND و OR

در موقعیتی که نیاز به هر دو شرط دارید، این قانون ساده را به یاد آور: شرط and را با علامت ستاره (*) و شرط OR را با علامت جمع (+) پیوند دهید.

به عنوان مثال، برای برگرداندن بازیکنانی که یک عدد برد (F2) و به یک گروه خاصی متعلق هستند (E2 یا E3)، زنجیره شرط های زیر را ایجاد کنید:

=FILTER(A2:C13, (C2:C13=F2) * ((B2:B13=E2) + (B2:B13=E3)), "No results")

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

نحوه فیلتر کردن نسخه های تکراری در اکسل

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

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

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

میخواهیم تعداد تکرار هر مورد را بدست آوریم و سپس مواردی که بیشتر از 1 بار اتفاق افتاده اند را استخراج کنیم. برای شمارش، باید برای هر دو آرگومان Criteria_range و criteria تابع countifs محدوده یکسانی را وارد کنید! به فرمول زیر دقت کنید:

=Filter(array,countifs(column1,column1,column2,column2)>1,”no results)

برای مثال، برای فیلتر کردن نسخ های تکراری در ستون ها در داده های موجود در محدوده A2:C20 که شامل سه ستون است، فرمولی که استفاده میکنیم به شکل خواهد بود:

=filter(A2:C20,countifs(A2:A20,A2:A20,B2:B20,B2:B20,C2:C20,C2:C20)>1,”no results”)

نکته. برای فیلتر کردن نسخه های تکراری در ستون های کلیدی (خاص)، فقط لازم است همان ستون ها را در تابع countifs وارد کنید.

نحوه فیتلر کردن سلول های خالی در اکسل

فرمولی که برای فیلتر کردن سلول های خالی استفاده میشود، در واقع، ترکیب فرمول filter با شرط های چندگانه and است. در این حالت، همه ستون ها (یا ستون های خاص) که در آن داده های وجود دارند چک میکنیم و ردیف هایی را که شامل حداقل یک سلول خالی هستند حذف میکنیم. برای شناسایی سلول های غیرخالی، از عملگر «مساوی نیست با» (<>) همراه با یک رشته خالی (” “) مانند این استفاده کنید:

=filter(array,(column1<>” “)*(column2=<>” “),”no results)

با توجه به منبع داده ها در محدوده A2:C12، برای فیلتر کردن ردیف هایی که شامل یک یا تعداد بیشتری سلول خالی هستند، فرمول زیر را در سلول E3 وارد کرده ایم:

فیلتر کردن سلول هایی که شامل یک متن خاص هستند.

برای استخراج سلول هایی که شامل یک متن معین هستند، میتوانید از تابع filter به همراه فرمول کلاسیک «اگر سلول شامل مقدارباشد» استفاده کنید:

=filter(array, isnumber(search(“text”,range)),”no results”)

در زیر نحوه کار کردن این فرمول گفته شده است:

• تابع Search یک رشته متنی خاص را در محدوده ای که تعیین کرده اید، پیدا میکند و یک عدد را برمیگرداند (مکان اولین کاراکتر) یا خطای #value! (متن پیدا نشد) را برمیگرداند.
• تابع isnumber تمام اعداد را به true و خطاها را به false تبدیل میکند و ارایه Boolean به دست آمده را به آرگومان include در تابع filter منتقل میکند.

برای این مثال، ما نام خانوادگی بازیکنان را که در B2:B13 قرار دارند، قسمتی از نام که ما میخواهیم پیدا کنیم را در سلول G2 تایپ کرده ایم، و سپس از فرمول زیر برای فیلتر کردن داده استفاده کرده ایم:

=FILTER(A2:D13,ISNUMBER(SEARCH(G2,B2:B13)),”no results”)

در نتیجه، این فرمول دو نام خانوادگی شامل “han” را باز میگرداند:

فیلتر و محاسبه (Sum، Average، Min، Max و غیره)

یکی از نکات جالب در مورد تابع Filter در اکسل این است که نه تنها میتواند مقادیر را با شرط هایی که تعریف شده استخراج کند، بلکه میتواند داده های فیلتر شده را نیز خلاصه کند. برای این کار تابع filter با توابع جمع مثل SUM، AVERAGE، COUNT، MAX یا MIN ترکیب شده است.

به عنوان مثال، برای جمع کردن یک گروه خاص در F1، از فرمول زیر استفاده کنید:

کل برنده ها:

=SUM(FILTER(C2:C13,B2:B13=F1,0))

میانگین برنده ها:

=AVERAGE(FILTER(C2:C13,B2:B13=F1,0)

بزرگترین برنده:

=MAX(FILTER(C2:C13,B2:B13=F1,0))

کمترین برنده:

=MIN(FILTER(C2:C13,B2:B13=F1,0))

لطفا به این توجه کنید، در تمام فرمول ها، ما از 0 برای آرگومان If_empty استفاده کرده ایم، بنابراین ممکن است که فرمول اگر هیچ مقداری برای شرط تعیین شده پیدا نکند، صفر را برگرداند. وارد کردن هر متنی مثل “no result” خطای #value! را برمیگرداند.

حساسیت تابع FILTER بر روی حروف بزرگ و کوچک

فرمول استاندارد FILTER نسبت به بزرگی و کوچکی حروف غیرحساس است. اما میتوانید از تابع EXACT در آرگومان include این تابع استفاده کنید. این کار سبب میشود تا FILTER نسبت به یک شرط حساس شود، به مثال زیر دقت کنید:

=FILTER(array,EXACT(range,criteria),”no results”)

فرض کنید شما هر دو گروه A و a را دارید. اما میخواهید موارد را استخراج کنید که شامل “a” هستند. برای انجام این کار، فرمول زیر را استفاده کنید، که A2:C13 داده ها هستند و B2:B13 گروهی است که میخواهید فیلتر کنید:

=FILTER(A2:C13,EXACT(B2:B13,F1),”no result”)

تابع FILTER در اکسل کار نمیکند

در این شرایط وقتی که تابع FILTER یک خطا برمیگرداند، در بیشتر مواقع یکی از موارد زیر اتفاق افتاده است:

خطای #CALC!

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

خطای #VALUE!

زمانی اتفاق می افتد که آرگومان های array و include شامل ابعاد ناسازگار باشند.

خطای #N/A ، #VALUE و غیره

اگر مقداری که در آرگومان include است یک خطا باشد یا نتواند به یک مقدار Boolean تبدیل شود، خطاهای مختلفی ممکن است اتفاق بیفتد.

خطای #NAME

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

در اکسل 365، یک خطای #NAME زمانی اتفاق می افتد که شما نام تابع را اشتباه تایپ کرده باشید.

خطای SPILL

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

خطای #REF!

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

این همه اون چیزی بود که لازم بود در مورد فیلتر اتوماتیک در اکسل بدونی.

ممنون که این مطلب رو مطالعه کردید. امیدواریم که دوباره توی سایتمون ببینیمتون. سوالی نظری داشتید، برامون کامنت کنید😊