آموزش صفر تا صد تابع FILTER در اکسل

فرمول ها و توابع اکسل

آموزش کامل استفاده از تابع Filter در اکسل ۲۰۲۱

آموزش تابع Filter در Excel

یکی از کاربردی‌ترین توابعی که در Excel از نسخه 2021 اضافه شده، تابع Filter است. در این آموزش قصد داریم صفر تا صد این تابع را یاد بگیریم. این تابع بسیار قدرتمند و دارای کاربردهای متنوعی است که در طی مثال‌های مختلف کاملاً برای شما روشن خواهد شد.

معرفی اجزای تابع Filter

تابع Filter شامل سه آرگومان اصلی است که در ادامه به توضیح هر یک می‌پردازیم:

  1. آرگومان اول (Array): این آرگومان تعیین می‌کند که کدام ستون‌ها یا محدوده داده‌ها در نتیجه نهایی نمایش داده شوند. می‌تواند شامل یک یا چند ستون باشد.

  2. آرگومان دوم (Include): این آرگومان شرطی است که ردیف‌ها بر اساس آن فیلتر می‌شوند. تعداد ردیف‌های این شرط باید با تعداد ردیف‌های آرگومان Array برابر باشد. خروجی این آرگومان باید به صورت True یا False باشد.

  3. آرگومان سوم (If_Empty): این آرگومان مشخص می‌کند که اگر نتیجه‌ای برای شرط تعیین‌شده وجود نداشت، چه پیامی یا مقداری نمایش داده شود. به طور پیش‌فرض، اگر این مقدار تعریف نشود، خطای #CALC! نشان داده می‌شود.

ساختار تابع Filter

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

				
					=FILTER(Array, Include, [If_Empty])
  • Array: محدوده داده‌هایی که قصد نمایش آن‌ها را دارید.
  • Include: شرط مورد نظر برای فیلتر.
  • If_Empty: مقداری که در صورت خالی بودن نتیجه نمایش داده می‌شود.

مثال اولیه

فرض کنید ستونی از داده‌ها داریم که می‌خواهیم فقط ردیف‌هایی را نمایش دهیم که مقدار خاصی (مثلاً “B”) در آن‌ها وجود دارد.

  1. مقدار آرگومان Array را محدوده داده‌های خود، مثلاً A1:A10، قرار می‌دهیم.
  2. شرط آرگومان Include را به صورت زیر تعیین می‌کنیم:
				
					A1:A10="B"
  1. این شرط بررسی می‌کند که کدام سلول‌ها مقدار “B” را دارند و خروجی آن به صورت True/False خواهد بود.
  2. مقدار آرگومان If_Empty را می‌توانیم متن دلخواهی مثل “No Results Found” تعیین کنیم.

تابع نهایی به صورت زیر خواهد بود:

				
					=FILTER(A1:A10, A1:A10="B", "No Results Found")

نحوه کارکرد

  1. اگر شرط در آرگومان Include برقرار باشد (مثلاً سلولی برابر “B” باشد)، آن ردیف نمایش داده می‌شود.
  2. اگر هیچ مقداری مطابق شرط وجود نداشته باشد، مقدار تعیین‌شده در آرگومان If_Empty، مثلاً “No Results Found”، نشان داده می‌شود.

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

در این مثال، قصد داریم داده‌ها را بر اساس یک شرط ساده فیلتر کنیم. فرض کنید داده‌های زیر را داریم:

CategoryProduct NameYear 1Year 2Year 3
BikeProduct A500600700
ClothingProduct B200300400
BikeProduct C8009001000
ClothingProduct D100150200

هدف ما این است که دسته‌بندی (Category) مربوط به “Bike” را فیلتر کرده و فقط نام محصول (Product Name) و مقادیر فروش در سال‌های مختلف را نمایش دهیم.

نحوه نوشتن تابع

برای این کار تابع Filter را به شکل زیر می‌نویسیم:

  1. Array (محدوده داده‌ها): محدوده‌ای که می‌خواهیم نمایش داده شود، شامل ستون‌های Product Name و مقادیر فروش (Year 1 تا Year 3) است.
  2. Include (شرط): شرط ما این است که ستون Category برابر با مقدار مورد نظر (مثلاً “Bike”) باشد.

تابع کامل به صورت زیر خواهد بود:

				
					=FILTER(B2:E5, A2:A5="Bike", "No Results Found")
  • B2:E5: محدوده‌ای که شامل Product Name و سال‌های فروش است.
  • A2:A5=”Bike”: شرطی که بررسی می‌کند ستون Category مقدار “Bike” داشته باشد.
  • “No Results Found”: متنی که در صورت نبود نتیجه نمایش داده می‌شود.

خروجی

اگر شرط برقرار باشد، خروجی به شکل زیر خواهد بود:

Product NameYear 1Year 2Year 3
Product A500600700
Product C8009001000

تغییر شرط

اگر مقدار شرط (مثلاً در یک سلول دیگر، مانند G1) قرار داشته باشد، می‌توانید به جای “Bike” به سلول اشاره کنید:

				
					=FILTER(B2:E5, A2:A5=G1, "No Results Found")

اگر مقدار سلول G1 به “Clothing” تغییر کند، خروجی فقط محصولات مرتبط با Clothing را نمایش می‌دهد.

مثال دوم: فیلتر کردن با چند شرط (OR)

در این مثال، قصد داریم داده‌هایی را فیلتر کنیم که متعلق به دسته‌بندی‌های “Bike” یا “Accessories” باشند. اینجا از دو شرط استفاده می‌کنیم که هر کدام به طور مستقل می‌توانند برقرار باشند. این حالت مشابه عملگر OR در منطق است.

داده‌های اولیه

فرض کنید داده‌ها به این صورت باشند:

CategoryProduct NameYear 1Year 2Year 3
BikeProduct A500600700
AccessoriesProduct B300400500
ClothingProduct C200250300
BikeProduct D8009001000
AccessoriesProduct E100150200

هدف ما این است که محصولات دسته‌بندی Bike و Accessories را فیلتر کرده و نام محصول و فروش سال‌های مختلف را نمایش دهیم.

نحوه نوشتن تابع

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

  1. Array (محدوده داده‌ها): محدوده‌ای که باید نمایش داده شود، شامل ستون‌های Product Name و مقادیر فروش است.
  2. Include (شرط): شرطی که بررسی می‌کند ستون Category برابر با “Bike” یا “Accessories” باشد.

تابع کامل:

				
					=FILTER(B2:E6, (A2:A6="Bike") + (A2:A6="Accessories"), "No Results Found")
  • (A2:A6=”Bike”): بررسی می‌کند که آیا مقدار ستون Category برابر با “Bike” است.
  • (A2:A6=”Accessories”): بررسی می‌کند که آیا مقدار ستون Category برابر با “Accessories” است.
  • عملگر + بین دو شرط به معنی “یا” (OR) است.
  • “No Results Found”: متنی که در صورت نبود نتیجه نمایش داده می‌شود.

خروجی

اگر شرط برقرار باشد، خروجی به شکل زیر خواهد بود:

Product NameYear 1Year 2Year 3
Product A500600700
Product B300400500
Product D8009001000
Product E100150200

اضافه کردن شرط‌های بیشتر

برای اضافه کردن شرط‌های بیشتر، می‌توانید به همین روش عمل کنید. مثلاً اگر بخواهید دسته‌بندی “Clothing” را نیز اضافه کنید:

				
					=FILTER(B2:E6, (A2:A6="Bike") + (A2:A6="Accessories") + (A2:A6="Clothing"), "No Results Found")

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

وقتی تعداد شرط‌های شما زیاد باشد، استفاده از عملگرهای OR یا جمع (+) ممکن است فرمول را بسیار طولانی کند و مدیریت آن دشوار شود. در این حالت، می‌توانید از ترکیب توابع MATCH و ISNUMBER استفاده کنید تا فرآیند ساده‌تر شود.

توضیح مسئله

فرض کنید یک ستون Category داریم که شامل دسته‌بندی‌های مختلف است، و می‌خواهیم چندین دسته‌بندی را با هم فیلتر کنیم، مثلاً ۵ یا ۱۰ دسته‌بندی مختلف. برای این کار:

  1. لیست دسته‌بندی‌هایی که باید فیلتر شوند را مشخص می‌کنیم.
  2. از MATCH برای پیدا کردن این دسته‌ها در ستون Category استفاده می‌کنیم.
  3. ISNUMBER بررسی می‌کند که آیا مقدار پیدا شده یک عدد معتبر است یا خیر.
  4. نتیجه این بررسی (True یا False) را به تابع Filter می‌دهیم.

ساختار فرمول

  1. لیست دسته‌بندی‌ها: دسته‌بندی‌هایی که می‌خواهیم فیلتر شوند را در یک محدوده قرار می‌دهیم. مثلاً محدوده H2:H6 شامل دسته‌های مورد نظر است.
  2. MATCH: بررسی می‌کند که هر مقدار ستون Category (مثلاً A2:A20) در لیست دسته‌بندی‌ها وجود دارد یا خیر.
  3. ISNUMBER: چک می‌کند که خروجی MATCH عدد معتبر باشد (یعنی مقدار پیدا شده باشد).

فرمول کامل:

				
					=FILTER(B2:E20, ISNUMBER(MATCH(A2:A20, H2:H6, 0)), "No Results Found")

توضیح فرمول

  • MATCH(A2:A20, H2:H6, 0): بررسی می‌کند که هر مقدار در ستون A2:A20 (Category) در محدوده H2:H6 (لیست دسته‌ها) موجود است یا خیر. اگر پیدا شود، شماره ردیف لیست دسته‌بندی را برمی‌گرداند.
  • ISNUMBER(…): خروجی MATCH را به True یا False تبدیل می‌کند. اگر مقدار عدد باشد (پیدا شده باشد)، True و در غیر این صورت False برمی‌گرداند.
  • FILTER(B2:E20, …): مقادیر ستون‌های مورد نظر (نام محصول و مقادیر فروش) را بر اساس شرط True/False فیلتر می‌کند.

داده‌های نمونه

CategoryProduct NameYear 1Year 2Year 3
BikeProduct A500600700
AccessoriesProduct B300400500
ClothingProduct C200250300
BikeProduct D8009001000
AccessoriesProduct E100150200

لیست دسته‌بندی‌ها

Categories
Bike
Accessories
Clothing

نتیجه

اگر فرمول بالا را اجرا کنیم، خروجی فیلتر شامل همه محصولات دسته‌بندی‌های Bike، Accessories و Clothing خواهد بود:

Product NameYear 1Year 2Year 3
Product A500600700
Product B300400500
Product C200250300
Product D8009001000
Product E100150200

مثال چهارم: فیلتر کردن مقادیر بین دو مقدار مشخص

در این مثال، هدف این است که داده‌ها را بر اساس شرطی که “بین دو عدد” یا “بین دو تاریخ” باشد، فیلتر کنیم. در چنین مواردی از AND استفاده می‌کنیم، که به جای علامت + (برای OR) از علامت × (برای AND) استفاده می‌شود.

توضیح مسئله

فرض کنید یک ستون اعداد (یا تاریخ‌ها) داریم و می‌خواهیم فقط مقادیری را نمایش دهیم که:

  1. بزرگ‌تر یا مساوی یک مقدار حداقلی باشند.
  2. کوچک‌تر یا مساوی یک مقدار حداکثری باشند.

این دو شرط باید همزمان برقرار باشند، بنابراین از عملگر ضرب استفاده می‌کنیم.

ساختار فرمول

  1. شرط اول: مقادیر باید بزرگ‌تر یا مساوی مقدار حداقلی باشند.
  2. شرط دوم: مقادیر باید کوچک‌تر یا مساوی مقدار حداکثری باشند.
  3. ضرب شرایط: خروجی دو شرط را در هم ضرب می‌کنیم. چون True معادل عدد 1 و False معادل عدد 0 است، فقط زمانی خروجی 1 خواهد بود که هر دو شرط برقرار باشد.

فرمول کلی:

				
					=FILTER(A2:D20, (B2:B20 >= E2) * (B2:B20 <= F2), "No Results Found")

توضیح فرمول

  • B2:B20 >= E2: بررسی می‌کند که آیا مقادیر ستون B بزرگ‌تر یا مساوی مقدار حداقلی در سلول E2 هستند.
  • B2:B20 <= F2: بررسی می‌کند که آیا مقادیر ستون B کوچک‌تر یا مساوی مقدار حداکثری در سلول F2 هستند.
  • (شرط اول) * (شرط دوم): خروجی دو شرط را در هم ضرب می‌کند؛ فقط زمانی نتیجه 1 (True) خواهد بود که هر دو شرط برقرار باشند.
  • FILTER(A2:D20, …): فیلتر کردن مقادیر ستون‌های A تا D بر اساس نتیجه شرایط.

داده‌های نمونه

ProductAmountYear 1Year 2
Product A5020202021
Product B7520202021
Product C10020202021
Product D15020202021
Product E20020202021

مقدارهای حداقلی و حداکثری

MinimumMaximum
75150

نتیجه

اگر فرمول بالا را اجرا کنیم، خروجی فیلتر شامل محصولات با مقادیر بین 75 و 150 خواهد بود:

ProductAmountYear 1Year 2
Product B7520202021
Product C10020202021
Product D15020202021

مثال پنجم: فیلتر کردن با استفاده از جستجوی متن (Search)

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

توضیح مسئله

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

ساختار فرمول

  1. تابع SEARCH:
    • این تابع بررسی می‌کند که آیا یک عبارت مشخص در یک متن وجود دارد یا خیر.
    • اگر عبارت پیدا شود، مکان شروع آن کاراکتر در متن را برمی‌گرداند.
    • اگر عبارت پیدا نشود، خطای #VALUE! می‌دهد.
  2. تبدیل خروجی به True/False:
    • با استفاده از تابع ISNUMBER، مقادیر بازگشتی از SEARCH به مقادیر منطقی True و False تبدیل می‌شوند.
  3. ترکیب با FILTER:
    • خروجی ISNUMBER به عنوان شرط فیلتر استفاده می‌شود.

فرمول کلی

				
					=FILTER(A2:D20, ISNUMBER(SEARCH(E2, B2:B20)), "No Results Found")

توضیح فرمول

  • SEARCH(E2, B2:B20): بررسی می‌کند که آیا متن داخل سلول E2 در هر کدام از مقادیر ستون B وجود دارد یا نه.
  • ISNUMBER(…): مقادیر بازگشتی را به True یا False تبدیل می‌کند.
  • FILTER(A2:D20, …): ستون‌های A تا D را بر اساس شرط بازگشتی فیلتر می‌کند.

داده‌های نمونه

ProductDescription
Bike AFast bike
Bike BMountain bike
Accessory 1Helmet
Accessory 2Gloves
Accessory 3Water bottle

ورودی کاربر

Search
bike

نتیجه

با اجرای فرمول، خروجی شامل تمامی مواردی خواهد بود که عبارت bike در آن‌ها وجود دارد:

ProductDescription
Bike AFast bike
Bike BMountain bike

نکات مهم

  • نتایج پویا: به محض تغییر عبارت جستجو در سلول E2، نتایج به‌روز می‌شوند.

مثال ششم: استفاده از FILTER برای پیدا کردن تمامی نتایج مشابه در یک جستجو

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

توضیح مسئله

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

مراحل اجرای فرمول

  1. نوشتن شرط فیلتر:
    • ابتدا شرط مورد نظر خود را می‌نویسید. مثلاً اگر می‌خواهید تمامی محصولات با یک گروه خاص را پیدا کنید، شرط را به صورت =CategoryColumn=specifiedCategory می‌نویسید.
  2. استفاده از تابع FILTER:
    • بعد از نوشتن شرط، از FILTER استفاده می‌کنید تا تمام محصولاتی که مطابق با آن شرط هستند، به شما نشان داده شوند.
    • در این حالت، می‌توانید فقط نام محصول را به عنوان خروجی انتخاب کنید.
  3. افقی کردن داده‌ها (Transpose):
    • چون معمولاً نتایج به صورت عمودی برگشت داده می‌شوند، اگر بخواهید این نتایج را به صورت افقی در کنار هم داشته باشید (برای استفاده در ردیف‌های مختلف)، از TRANSPOSE استفاده می‌کنید.

فرمول کلی

				
					=TRANSPOSE(FILTER(ProductNames, CategoryColumn = "Category", ""))

توضیح فرمول

  • FILTER(ProductNames, CategoryColumn = “Category”, “”): این فرمول تمامی محصولاتی که در ستون CategoryColumn برابر با “Category” هستند را فیلتر کرده و به شما نمایش می‌دهد.
  • TRANSPOSE: این تابع داده‌های عمودی را به داده‌های افقی تبدیل می‌کند تا در ردیف‌های مختلف نمایش داده شوند.

نکات مهم

  • درگ کردن داده‌ها: بعد از استفاده از TRANSPOSE، می‌توانید فرمول را در سلول‌های بعدی به راحتی با کشیدن (drag) تکثیر کنید.
  • حذف تکراری‌ها: اگر نتایج شما تکراری هستند، می‌توانید از تابع UNIQUE برای حذف تکراری‌ها استفاده کنید.
  • ادغام داده‌ها در یک سلول: اگر بخواهید تمامی نتایج را در یک سلول نمایش دهید، از تابع ARRAYTOTEXT استفاده کنید.

فرمول برای ادغام نتایج در یک سلول

				
					=ARRAYTOTEXT(FILTER(ProductNames, CategoryColumn = "Category"))

این فرمول تمام محصولات مطابق با گروه مشخص‌شده را در یک سلول و با جداکننده کاما (,) ادغام می‌کند.

مثال هفتم: استفاده از چک باکس در اکسل برای فیلتر کردن لیست کارها

در این مثال، از چک باکس در اکسل برای فیلتر کردن لیست کارهایی که باید انجام دهید، استفاده می‌کنیم. این قابلیت جدید در آفیس ۳۶۵ وجود دارد که به شما امکان می‌دهد به راحتی وضعیت انجام کارها را پیگیری کنید.

توضیح مسئله

فرض کنید شما یک لیست از کارهایی دارید که باید انجام دهید و برای هر کار یک چک باکس اضافه کرده‌اید تا وضعیت آن را (انجام شده یا نه) مشخص کنید. با استفاده از تابع فیلتر می‌توانیم کارهایی که انجام شده‌اند و کارهایی که هنوز باقی مانده‌اند را فیلتر کنیم.

مراحل اجرای فرمول

  1. استفاده از چک باکس:

    • در نسخه‌های جدید اکسل، با استفاده از چک باکس می‌توانیم وضعیت انجام کارها را به‌صورت TRUE (اگر انجام شده) یا FALSE (اگر انجام نشده) مشخص کنیم.
    • شما می‌توانید این چک باکس را از تب Insert و گزینه Checkbox پیدا کنید.

  2. نوشتن شرط فیلتر برای کارهای انجام شده:

    • اگر بخواهید کارهایی که انجام شده‌اند را فیلتر کنید، کافی است از یک شرط ساده استفاده کنید. چون چک باکس در اکسل به صورت پیش‌فرض وضعیت آن را به TRUE یا FALSE تبدیل می‌کند، بنابراین شرط برای انجام شده‌ها به‌سادگی به صورت =TRUE خواهد بود.
    • از تابع FILTER برای فیلتر کردن مواردی که وضعیت آن‌ها TRUE است استفاده می‌کنیم.

  3. نوشتن شرط فیلتر برای کارهای باقی‌مانده:

    • برای فیلتر کردن کارهایی که انجام نشده‌اند، می‌توانیم شرایط فیلتر را به گونه‌ای تغییر دهیم که از FALSE برای فیلتر کردن استفاده کند.
    • برای این کار از تابع NOT استفاده می‌کنیم تا تمام کارهایی که وضعیت آن‌ها FALSE است را فیلتر کنیم.

فرمول‌ها

  • فیلتر کردن کارهای انجام شده

				
					=FILTER(TaskList, CheckboxColumn , "No tasks completed")
 

  • فیلتر کردن کارهای باقی‌مانده:

				
					=FILTER(TaskList, NOT(CheckboxColumn), "No tasks remaining")

نکات اضافی

  • استفاده از متن دلخواه برای خطا: در صورتی که هیچ کار انجام شده یا باقی‌مانده‌ای نباشد، می‌توانید یک پیام دلخواه مانند “No tasks completed” یا “No tasks remaining” نمایش دهید.

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

فایلهای تمرین

فایل خام اولیهدانلود
فایل نهاییدانلود
Rate this post
درباره محمود بنی اسدی (مدیر سایت)

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

دیدگاهتان را بنویسید