فیلتر در اکسل – راهنمای کاربردی

مطالب مجله فرادرس را در جستجوی گوگل سریعتر ببینید
فیلتر در اکسل
فیلتر در دنیای واقعی به ابزاری برای جداسازی گفته میشود. در اکسل نیز فیلتر همین وظیفه را دارد. به این معنی که به کمک فیلتر در اکسل میتوانیم بعضی از سطرهای کاربرگ را به کمک معرفی کردن یک یا چند شرط، جدا کرده و براساس آنها محاسباتی را صورت دهیم.
برای استفاده از فیلتر در اکسل دو راهکار وجود دارد. میتوان با استفاده از ابزار فیلترگذاری، شرط یا شرطهایی را برای جدا کردن سطرهای کاربرگ مشخص کرد. از طرف دیگر با قید کردن ناحیهای برای مشخص کردن شرط، امکان جداسازی چنین سطرهایی، به کمک فیلتر پیشرفته در اکسل وجود دارد. البته میتوانید با کپی کردن نتیجه فیلتر در کاربرگ یا ناحیه دیگر، تغییری در کاربرگ اصلی ایجاد نکرده ولی مطابق شرط داده شده، سطرهای دلخواه را در اختیار کاربر قرار دهید.
همین دلایل را میتوان برای فیلتر کردن نیز در نظر گرفت. با این تفاوت که بعد از اجرای فیلتر، با به کارگیری بعضی از توابع در اکسل، میتوانیم محاسباتی را برای سطرهای انتخاب شده، انجام دهیم. به این ترتیب اهداف زیر را برای فیلتر در اکسل مشخص میکنیم.
- جستجو یک یا چند مقدار در یک ستون مشخص.
- دستهبندی و گروهبندی سطرهای اکسل براساس یک یا چند ستون.
- اجرای محاسبات روی گروه ایجاد شده براساس فیلتر.
نکته: محاسبات روی سطرهای فیلتر شده در اکسل توسط تابع SUBTOTAL صورت میگیرد که در نوشتار دیگری از مجله فرادرس به آن نیز خواهیم پرداخت.
فیلتر در اکسل به دو شیوه اعمال میشود. یکی از روشها که معمولا از آن به عنوان فیلتر ساده یاد میکنیم، استفاده از ابزار فیلتر گذاری روی ستونها است. به این ترتیب با انتخاب اقلام مورد نظر برای ستونها، قادر هستیم سطرهای متناظر با آن مقادیر را از کاربرگ نمایش داده و بقیه سطرها را مخفی کنیم.
اما در روش دوم، که به فیلتر پیشرفته (Advance Filter) معروف است، میتوانیم شرطها برای انتخاب مقادیر مرتبط با ستونها را به صورت سفارشی درون کاربرگ و سلولهای آن تعیین کرده و شرطهای پیچیدهتری نسبت به فیلتر عادی، اعمال کنیم. همچنین در این حالت، اختیار کپی کردن نتیجه فیلتر به ناحیه یا کاربرگ جدید نیز میسر است.
در ادامه متن به هر دو شیوه فیلتر کردن خواهیم پرداخت. البته در انتها نیز به تابعی در اکسل ۳۶۵ خواهیم پرداخت که وظیفه فیلتر کردن دادهها را در نسخه آنلاین اکسل، به عهده دارد.
برای شروع کار بهتر است ابتدا یک فایل اطلاعاتی را مبنای کار قرار داده و براساس آن مثالها را دنبال کنیم. در زیر نمایی از یک کاربرگ اطلاعاتی برای اجرای فیلتر را مشاهده میکنید. این کاربرگ شامل چهار ستون و ۱۲ سطر اطلاعاتی است. سطر اول نیز مربوط به اسامی ستونها است که شامل مقادیر متنی، عددی و تاریخی است.

تصویر ۱ را در نظر بگیرید که یک کاربرگ اکسل را با مقادیر مختلف شامل مقادیر عددی (ستون مبلغ گردش حساب)، متنی (ستونهای نوع حساب و نام بانک) و تاریخی (تاریخ گردش حساب) نشان میدهد. توجه داشته باشید که زمانی فیلتر کردن موثر است که بعضی از این مقادیر تکراری باشند تا بتوانند با یکدیگر تشکیل یک گروه بدهند. به این ترتیب علاوه بر جستجو، میتوان دستهبندی و محاسبات دلخواه را روی نتایج فیلتر انجام داد. همانطور که مشخص است ستونهای مربوط به «نام بانک» شامل مقادیر «صادرات، ملی و تجارت» بوده و ستون «نوع حساب» نیز مقادیر «سپرده و جاری» را شامل میشود. همچنین ستون «تاریخ گردش حساب» نیز مقادیر مختلف تاریخی در سال ۲۰۰۲ را نشان داده و در انتها نیز «ستون مبلغ گردش حساب» مقادیر عددی در بازه ۱۲۰ تا ۹۰۰ را مشخص کرده است. این فایل اطلاعاتی را مبنای محاسبات و ایجاد فیلتر در اکسل قرار خواهیم داد.
نکته: توجه داشته باشید که ناحیه اطلاعاتی برای فیلتر باید شامل سطر عنوان باشد. به این معنی که سطر اول ناحیه اطلاعات، اسامی ستونها را مشخص کرده و سطرهای بعدی را برای فیلتر به کار میگیرد. در ضمن اگر سلولهایی در ناحیه اطلاعاتی وجود داشته باشند که با یکدیگر ادغام شدهاند، فیلتر دچار خطا شده و اکسل قادر به اعمال فیلتر روی چنین ناحیهای نبوده یا استفاده از فیلتر را محدود خواهد کرد. همچنین پیوستگی در ناحیه اطلاعاتی نیز از ضروریات دیگر استفاده از فیلتر است. به این معنی که باید بین سطرها یا ستونهای کاربرگ اطلاعاتی فاصلهای به صورت سطر یا ستون خالی وجود نداشته باشد.
فیلتر کردن در اکسل با ابزار Filter
برای اجرای فیلتر در اکسل کافی است مراحل زیر را روی کاربرگ جاری، طی کنید.
- سلول فعال را در ناحیه اطلاعاتی قرار دهید. به این ترتیب اکسل متوجه میشود که جدول اطلاعاتی در کجای کاربرگ قرار گرفته و باید چه ناحیهای را فیلتر کند.
- از برگه Data گزینه Filter را در بخش Sort & Filter انتخاب کنید.
- علامت فیلتر روی اسامی ستونهای جدول اطلاعاتی (مطابق با تصویر ۲) ایجاد خواهد شد.
- ستونی که باید شرط مربوط به فیلتر روی آن اعمال شود را مشخص کرده و روی فلش (علامت فیلتر) ظاهر شده روی سلول، کلیک کنید.
- شرط مربوط به فیلتر را مطابق با گزینههای لیست ظاهر شده، انتخاب کرده و دکمه OK را بزنید.

در صورتی که هنگام اجرای فیلتر از تغییر تنظیمات فیلتر گذاری منصرف شدید، از دکمه Cancel استفاده کنید.
فرض کنید میخواهیم همه سطرهای مربوط به گردش حساب بانک ملی را مشاهده کنیم. به این ترتیب فیلتر مربوط به نام بانک را فعال کرده و از لیست ظاهر شده مطابق با تصویر ۳، گزینه ملی را انتخاب و بقیه اسامی بانکها (تجارت و صادرات) را غیر فعال میکنیم.

با شرط اعمال شده، فقط سطرهایی از کاربرگ ظاهر میشوند که در قید مربوطه صدق کنند. با این کار، شماره سطرها، به رنگ آبی در آمده و شکل فیلتر روی ستون مربوطه نیز تغییر میکند. در انتهای کاربرگ و در بخش «نوار وضعیت» (Status Bar) در کنار عبارت Ready، نیز تعداد سطرهایی که با شرط مطابقت داشتهاند به شکل مشخص شده است.
مطابق با تصویر ۴، فیلتر را روی کاربرگ برای نام بانک به کار برده و فقط سطرهای مربوط به بانک ملی را نمایش دادهایم. توجه داشته باشید که سطرهای دیگر از کاربرگ، حذف نشدهاند، بلکه فقط از دید شما مخفی هستند. احتمالا شمارههای سطرهای کاربرگ نظم خود را از دست دادهاند. البته مثلا ممکن است بعد از سطر شماره 2، سطر 4 را مشاهده کنید. این امر نشان میدهد که سطر 3 مطابق با شرط شما نبوده و در نتیجه مخفی (Hidden) شده است. مشخص است که این وضعیت برای سطرهای دیگر کاربرگ اطلاعاتی مانند سطر ۵ تا ۸، همچنین سطر ۱۱ و ۱۳ نیز رخ داده است.

نکته: در لیست فیلتر، گزینههای مرتب سازی (Sort) نیز وجود دارد. در نتیجه امکان ایجاد ترتیب صعودی یا نزولی در لیستهای انتخابی نیز وجود دارد.
بحثی که در این قسمت در مورد نحوه ایجاد فیلتر صورت گرفت، شیوه عمومی برای ایجاد فیلتر است. ولی با توجه به نوع مقادیری که در کاربرگ و ستون فیلتر شده، قرار داد، میتوان از امکانات فیلتر در اکسل به بهترین نحوه استفاده کرد. این موضوعات در ادامه متن مورد بررسی قرار خواهند گرفت.
فیلتر در اکسل برای مقادیر عددی
این بار فرض کنید ستونی که قرار است فیلتر روی آن اعمال شود، شامل مقادیر عددی است یا در اصل میخواهیم اقدام به فیلتر کردن مقادیر عددی در اکسل بکنیم. بنابراین امکان استفاده از گزینه Number Filters در لیست فیلتر، نیز برای کاربران فراهم میشود. در این قسمت، از پیش، امکاناتی برای فیلتر کردن مقادیر عددی در نظر گرفته شده که سرعت کاربران را هنگام به کارگیری فیلتر در اکسل افزایش میدهد. برای مثال اگر بخواهید سطرهایی را در کاربرگ جدا کنید که برای ستون عددی گردش حساب، شامل مقادیر بیشتر از 500 هستند، کافی است از لیست Number Filter گزینه Greater than را به کار ببرید.
حتی میتوان شرطها را پیچیدهتر هم کرد. برای مثال فرض کنید بخواهیم مقادیری که در بازه ۱۰ تا ۲۰ قرار گرفتهاند را فیلتر کرده و سطرهای مرتبط را نمایش دهیم. تنظیمات مربوط به این کار را مطابق با تصویر 5، انجام میدهیم. با ثبت مقدار ۵۰۰ در کادر ظاهر شده و فشردن دکمه OK، این شرط برای نمایش سطرهای کاربرگ اعمال میشود.

با ثبت مقدار ۵۰۰ در کادر ظاهر شده و فشردن دکمه OK، این شرط برای نمایش سطرهای کاربرگ اعمال میشود. در تصویر ۶، نحوه تنظیم پارامترهای این شرط را مشاهده میکنید.

حتی میتوان شرط را باز هم پیچیدهتر کرد و برای مثال به دنبال سطرهایی بگردیم که مقدار گردش حساب در آنها، بین ۱۰۰ تا ۲۰۰ باشد. به این ترتیب با انتخاب گزینه Between از لیست فیلتر، در کادر اول مقدار ۱۰۰ و در کادر دوم نیز ۲۰۰ را وارد میکنیم. این تنظیمات را در تصویر ۷، میتوان مشاهده کرد.
به منظور درک بهتر عملیاتی که توسط گزینههای فیلتر عددی، اجرا میشود، جدولی را تشکیل داده و هر یک از گزینههای این لیست را معرفی کردهایم.
جدول ۱: گزینههای فیلتر عددی
| گزینه | عملکرد |
| Equals... | شرط برابری با یک مقدار عددی |
| Doe Not Equal... | شرط نابرابری یا نامساوی با یک مقدار عددی |
| Greater Than... | شرط بزرگتر از مقدار عددی معرفی شده |
| Greater Thank Or Equal To... | شرط بزرگتر یا مساوی با مقدار عددی معرفی شده |
| Less Than | شرط کوچکتر از مقدار عددی معرفی شده |
| Less Than Or Equal To... | شرط کوچکتر یا مساوی با مقدار عددی تعریف شده |
| Between... | شرط قرارگیری بین دو مقدار یا دو کران |
| Top 10... | شرط برای نمایش چند مقدار اول (بزرگترین از لحاظ مقدار یا درصد) |
| Above Average | شرط برای مقادیر بیش از میانگین |
| Below Average | شرط برای مقادیر کمتر از میانگین |
| Custom Filter... | شرطهای مربوط به فیلتر سفارشی |
استفاده از «فیلتر سفارشی» (Custom Filter) این امکان را به شما میدهد که دو نوع شرط برای یک ستون را به صورت ترکیب عطفی (AND) یا ترکیب فصلی (OR) در آورید.

ولی متاسفانه امکان ترکیب بیش از دو شرط در این قسمت وجود ندارد. برای ترکیب چندین شرط در فیلتر باید از «فیلتر پیشرفته» (Advanced Filter) کمک بگیرد که در ادامه این متن به آن اشاره خواهیم کرد. مثلا فرض کنید میخواهید سطرهایی از کاربرگ را مشاهده کنید که مقادیر گردش حساب در آنها بیش از ۵۰۰ یا کمتر از ۲۰۰ یا در بازه ۳۰۰ تا ۳۵۰ باشد. با استفاده از فیلتر عادی این کار امکانپذیر نبوده و نمیتوانیم خروجی مناسبی برای این کار با فیلتر ایجاد کنیم.
پس از اجرای فیلتر در برگه Data و بخش Sort & Filter، گزینههای دیگری نیز فعال میشوند. این گزینهها به صورت زیر هستند.
- گزینه Clear: برای آنکه فیلتر را از یک ستون برداشته و بدون آنکه گزینه فیلتر غیر فعال شود، همه سطرها را مشاهده کنیم، باید روی دکمه Clear از برگه Data کلیک کنیم.
- گزینه Reapply: اعمال مجدد فیلتر براساس شرطهای قبلی توسط این گزینه صورت میگیرد. فرض کنید مقداری از ناحیه فیلتر شده، تغییر کند و مطابق با شرط نباشد. در نتیجه گزینه Reapply، باعث میشود این شرطها دوباره اعمال شود. به این ترتیب ممکن است مقدار جدید از نتیجه فیلتر خارج شود.
- گزینه Advanced: به کارگیری ابزار فیلتر پیشرفته روی مجموعه دادهها.
نکته: توجه داشته باشید که فیلتر جدید فقط روی مقادیری که توسط فیلتر قبلی ظاهر شده، اثر میکند. در نتیجه فیلترها دارای اولویت بوده ولی با توجه به ترکیب عطفی («و» منطقی) شرطهای فیلترها در ستونهای مختلف، نتیجه در انتها یکسان است. زیرا میدانیم که عملگر «و» منطقی، دارای خاصیت جابجایی است.
فیلتر در اکسل برای مقادیر متنی
این بار براساس یک ستون متنی، فیلتر را اجرا میکنیم یا در اصل به فیلتر کردن مقادیر متنی در اکسل میپردازیم. البته توجه داشته باشید که فیلتر روی ستون جدید میتواند همزمان با فیلتر روی ستونهای قبلی نیز اجرا شود. به این معنی که اگر در قسمت قبل فیلتر را براساس ستون گردش حساب (با مقادیر عددی) اجرا کردهایم، این بار میتوانیم فیلتر متنی را به فیلتر قبلی اضافه کنیم و نتیجه یا حاصل اجرای فیلترها، سطرهایی خواهد بود که هم در شرط فیلتر عددی و هم در شرط فیلتر متنی صدق میکنند. ولی برای اینکه نتایج حاصل قابل درک باشند، اینجا فرض کردهایم که فیلتر قبلی را از روی مجموعه داده، برداشتهایم.
ستون متنی به صورت «نام بانک» را در نظر بگیرید. این ستون حاوی مقادیر متنی است که نام بانکهای ملی، تجارت و صادرات در آن قرار گرفته است. به این جهت، علاوه بر امکان انتخاب گزینههای متنی از لیست فیلتر این ستون، امکاناتی نیز که فقط برای متن قابل اجرا هستند، در گزینه Text Filters قرار گرفته است. به تصویر ۸ توجه کنید.

جدول زیر گزینههای مربوط به انتخاب کاربر در فیلتر متنی را مشخص و معرفی کرده است. توجه داشته باشید که این گزینهها فقط در صورت متنی بودن مقادیر ستون فیلتر شده، ظاهر خواهند شد.
جدول 2: گزینههای فیلتر متنی
| گزینه | عملکرد |
| Equals... | شرط برابری با یک مقدار متنی |
| Does Not Equal... | شرط نابرابری با یک مقدار متنی |
| Begin With... | شرط برای مطابقت یک متن با ابتدای عبارت درون سلولها |
| Ends With... | شرط برای مطابقت یک متن با انتهای عبارت درون سلولها |
| Contains... | شرط برای مطابقت یک متن با بخشی از عبارت درون سلولها |
| Does Not Contain... | شرط برای عدم مطابقت یک متن با بخشی از عبارت درون سلولها |
| Custom Filter... | شرطهای مربوط به فیلتر سفارشی |
برای مثال، فرض کنید در اسامی بانکها، بانک ملی و بانک ملت وجود داشته باشد و بخواهیم هر دو بانک را مشخص کنیم. از آنجایی که هر دو نام با عبارت «مل» شروع میشوند، استفاده از گزینه Begin With میتواند نتیجه دلخواه را برایمان فراهم کند.
فیلتر در اکسل برای مقادیر تاریخی
اگر در یک ستون از کاربرگ، مقادیر مرتبط با تاریخ قرار گرفته باشد، قابلیتهای متفاوتی برای انتخاب سطرها و اعمال شرطها بوجود میآید. به تصویر ۹، دقت کنید. انتخاب هر یک از این گزینهها، باعث میشود که در ستونهای تاریخی، فیلتر را براساس روز، ماه، فصل و سال تعیین کرد.

باز هم از جدولی برای نمایش گزینههای لیست Date Filters استفاده کرده و اقلام آن را معرفی خواهیم کرد.
جدول 3: گزینههای فیلتر تاریخ در اکسل
| گزینه | عملکرد |
| Equals | شرط مطابقت با یک تاریخ مشخص |
| Before | شرط برای مطابقت تاریخهای یک ستون قبل از تاریخ مشخص شده |
| After | شرط برای مطابقت تاریخهای یک ستون بعد از تاریخ مشخص شده |
| Between | شرط برای مطابقت تاریخهای یک ستون قبل و بعد از تاریخ مشخص شده |
| Tomorrow | شرط برای مطابقت تاریخ با تاریخ فردا (براساس تاریخ و ساعت سیستم) |
| Today | شرط برای مطابقت تاریخ با تاریخ امروز (براساس تاریخ و ساعت سیستم) |
| Yesterday | شرط برای مطابقت تاریخ با تاریخ دیروز (براساس تاریخ و ساعت سیستم) |
| Next Week | شرط مقایسه تاریخ با هفته آینده |
| This Week | شرط مقایسه تاریخ با هفته جاری |
| Last Week | شرط مقایسه تاریخ با هفته گذشته |
| Next Month | شرط مقایسه با تاریخ ماه بعد |
| This Month | شرط مقایسه با تاریخ ماه جاری |
| Last Month | شرط مقایسه با تاریخ ماه قبل |
| Next Quarter | شرط مقایسه با تاریخ فصل بعدی |
| This Quarter | شرط مقایسه با تاریخ فصل جاری |
| Last Quarter | شرط مقایسه با تاریخ فصل گذشته |
| Next Year | شرط مقایسه با تاریخ سال آینده |
| This Year | شرط مقایسه با تاریخ سال جاری |
| Last Year | شرط مقایسه با تاریخ سال گذشته |
| Years to Date | شرط مربوط به سالهای گذشته از تاریخ مشخص شده |
| All Dates in the Period | شرط مربوط به همه دورههای زمانی (فصلی و ماهانه) |
| Custom Filter | شرطهای مربوط به فیلتر سفارشی |
نکته: زمانی که پنجره انتخاب تاریخ به مانند تصویر 10، ظاهر میشود، دکمهای برای ظاهر کردن تقویم و انتخاب زمان مورد نظر برای هر یک از محدودههای ذکر شده، دیده میشود. برای انتخاب روز جاری، کافی است روی دکمه Today کلیک کرده تا تاریخ جاری به عنوان یک پارامتر انتخاب شود. فلسهای راست (>) و چپ (<) نیز امکان تغییر در بازه ماه یا سال را فراهم میآورند.

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

اگر رنگ آبی پر رنگ را انتخاب کنید، فقط سطرهایی از کاربرگ ظاهر خواهند شد که دارای این رنگ هستند.
فیلتر پیشرفته در اکسل
کاربرگ اولیه که در تصویر ۱ ارائه شد، را در نظر بگیرید. میخواهیم بعضی از سطرهای آن را از بقیه سطرها، جدا یا فیلتر کنیم. این بار این کار را به کمک «فیلتر پیشرفته» (Advance Filter) انجام خواهیم داد. یکی از مواردی که فیلتر پیشرفته را نسبت به فیلتر عادی، متمایز میکند، ایجاد شرطهای پیچیده و مختلف برای نمایش سطرهای دلخواه است. همچنین در فیلتر پیشرفته این امکان وجود دارد که فقط بعضی از ستونهای کاربرگ اطلاعاتی را به عنوان نتیجه فیلتر نمایش داد. به این معنی که میتوان نتیجه فیلتر را در قسمت دیگری از کاربرگ قرار داده و فقط بعضی از مقادیر ستونها را به عنوان حاصل فیلتر در نظر گرفت.
از طرفی میتوانیم شرط یا ترکیب شرطها را برای فیلتر پیشرفته، درون سلولها مشخص کنیم. این مزیت کاربران را در تنظیم و تغییر سریع شرطها کمک میکند. همچنین فیلتر پیشرفته محدودیت ایجاد شرطهای پیچیده برای فیلتر ساده را نداشته و کاربر قادر است شرایط مختلفی را برای نمایش سطرها، اعمال کند.
به منظور دسترسی به فیلتر پیشرفته، کافی است از برگه Data گزینه Advance Filter را انتخاب کنید. البته شرط انتخاب یک سلول در ناحیه اطلاعاتی نیز نباید فراموش شود. به این ترتیب پنجرهای به مانند تصویر 12، ظاهر شده که پارامترهای لازم برای فیلتر پیشرفته را از کاربر دریافت میکند.

این قسمتها را در جدول زیر معرفی کردهایم.
| گزینه | عملکرد |
| Filter the list, in-place | نمایش نتیجه فیلتر روی جدول اطلاعاتی |
| Copy to another location | نمایش نتیجه فیلتر در محل دیگر از کاربرگ یا کاربرگها |
| List range | ناحیه مربوط به فیلتر |
| Criteria range | ناحیه مربوط به شرط یا شرطهای فیلتر |
| Copy to | تعیین ناحیه مربوط به نمایش نتیجه فیلتر در قسمت دیگری از کاربرگ (در صورت انتخاب کرد گزینه Copy to another location) |
| Unique records only | نمایش سطرهای یکتا برای نتیجه فیلتر پیشرفته |
قبل از هر چیز باید نحوه به کارگیری سلولهای کاربرگ برای تعیین شرط در فیلتر پیشرفته را یادآوری کنیم. ناحیه مربوط به شرط فیلتر (Criteria range)، باید شامل اسامی ستونها و مقادیری باشد که برای فیلتر کردن آن ستونها لازم است. اگر میخواهید هر یک از شرطها را با شرطهای بعدی به صورت عطفی (ترکیب گزارههای منطقی) یا به صورت «و» ترکیب کنید، باید مقادیر آنها را در سلولهای مجاور هم و در یک سطر بنویسد. ولی اگر لازم است ترکیب به صورت فصلی و به صورت «یا» باشد، مقدار شرطها باید در زیر هم نوشته شوند. برای روشن شدن موضوع به بیان چند مثال میپردازیم.
مثال ۱:
فرض کنید طبق کاربرگ معرفی شده در تصویر ۱، بخواهیم فقط سطرهای مربوط به بانک ملی را مشاهده کنیم. آنگاه باید شرطها را (مثلا در سلولهای F16 و F17) مطابق با تصویر ۱۳، مشخص کنیم. واضح است در سلول F16، نام ستون مربوط به فیلتر و در سلول F17، مقدار شرط برای آن ستون را مشخص کردهایم.

مثال ۲:

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

مثال ۴:
در نظر بگیرید که پرسشی که از کاربرگ اطلاعاتی داریم براساس حسابهای جاری بانک ملی و صادرات است. در نتیجه شرطها به صورت «نوع حساب = جاری و نام بانک ملی» یا «نوع حساب = جاری و نام بانک صادارت» است. برای این که اکسل این موضوع را هنگام ترکیب گزارههای شرطی متوجه شود مطابق با تصویر ۱۶ عمل خواهیم کرد.

نکته: البته میدانیم که با توجه به ثابت بودن بخش اول شرط در هر دو وضعیت میتوان این قید را به صورت «نوع حساب = جاری» و «نام بانک = ملی یا صادرات» در نظر گرفت.
مثال ۵:

از آنجایی که میخواهیم نتیجه ترکیب نوع حساب و نام بانک، به صورت فصلی (یا منطقی) با مبلغ گردش حساب، داشته باشیم، سطر مربوط به مبلغ گردش حساب را در زیر شرطهای دیگر قرار دادهایم. به یاد دارید که اگر شرطها در یک سطر قرار گیرند، ترکیب عطفی است ولی در زیر یکدیگر، ترکیب فصلی ساخته خواهد شد.
حال به بررسی فیلتر پیشرفته در اکسل خواهیم پرداخت و نتیجه اجرای مثال ۵ را در کاربرگ مربوط به تصویر ۱، بدست خواهیم آورد. ابتدا فیلتر را از روی مجموعه داده (ناحیه A1 تا D13) بر میداریم. کافی است دکمه فیلتر را در برگه Data در بخش Sort & Filter خاموش کنید. حال پس از اجرای دستور Advance، پنجره تنظیمات فیلتر پیشرفته ظاهر خواهد شد. به منظور اجرای فیلتر مطابق با مثال ۵، گزینههای این پنجره مطابق با شکل سمت راست تصویر ۱۸ تنظیم شده است. همچنین ناحیه شرط نیز در پایین تصویر قرار گرفته است. نتیجه اجرای فیلتر را هم در سمت چپ تصویر 18، مشاهده میکنید.

از طرفی اگر بخواهیم نتیجه اجرای فیلتر پیشرفته را در قسمت دیگری از کاربرگ کپی کنیم، پس از فعال کردن گزینه Copy to another location، ناحیهای از کاربرگ را در قسمت Copy to انتخاب میکنیم که قرار است، نتیجه در آنجا ظاهر شود. اگر ناحیه انتخاب شده ستونهایی برابر با تعداد ستونهای مجموعه اطلاعاتی اصلی داشته باشد، به راحتی نتیجه با همان اسامی ستونهای ناحیه اطلاعاتی ساخته خواهد شد. این کار را در تصویر ۱۹ انجام دادهایم. توجه کنید که ناحیه اطلاعاتی با ناحیه فیلتر، مقادیر متفاوتی دارند.

همانطور که میبینید با انتخاب گزینه Copy، میتوان نتایج فیلتر و سطرهای مطابق با شرط را به ناحیهای دیگر از کاربرگ منتقل کرد. این امکان، مزیت ویژهای است که در فیلتر عادی وجود ندارد. البته برای ایجاد چنین لیستی، باید شرایطی را اعمال کنیم. همانطور که مشخص است در ناحیه شماره ۱، ناحیه اطلاعاتی قرار دارد که در بخش List range قرار گرفته، ناحیه کپی کردن نتیجه نیز که مربوط به ناحیه شماره ۲ است، در پارامتر Copy to ظاهر شده و در نهایت نیز ناحیه شرط با شماره ۳ در پارامتر Criteria range دیده میشود.
نکته: توجه داشته باشید که نتیجه اجرای فیلتر مربوط به مثال ۵، سطرهایی از جدول اطلاعاتی را نشان میدهد که یا مربوط به بانک صادرات یا ملی هستند و یا اینکه مبلغ گردش حساب آنها کمتر از ۵۰۰ است. به همین جهت فقط یکی از سطرهای مربوط به بانک تجارت در بین پاسخها قرار گرفته است.
برای خارج کردن جدول اطلاعاتی از حالت فیلتر، کافی است دکمه Clear را از برگه Data در قسمت Sort & Filter کلیک کنید.
البته به این موضوع نیز توجه کنید که هنگام اجرای دستور کپی در فیلتر پیشرفته، محلی را برای نتیجه فیلتر انتخاب کنید که با سطرهای ناحیه اطلاعاتی، اشتراک نداشته باشد. زیرا در صورت مخفی شدن سطرهای ناحیه در کاربرگ توسط فیلترهای دیگر، سطرهای مربوط به نتیجه فیلتر نیز مخفی خواهند شد.
تابع فیلتر در اکسل ۳۶۵
قالب دستوری برای به کارگیری تابع FILTER به صورت زیر است.
=FILTER(array,include,[if_empty])
همانطور که مشخص است این تابع دارای دو پارامتر اصلی و یک پارامتر اختیاری است. در ادامه به معرفی هر یک از این پارامترها خواهیم پرداخت.
- پارامتر اول (array): این پارامتر به ناحیه یا برداری از کاربرگ اشاره دارد که باید فیلتر شود.
- پارامتر دوم (include): در این پارامتر، شرط یا همان قید برای ایجاد فیلتر قرار میگیرد. واضح است که مقادیر این ناحیه باید به صورت مقادیر منطقی با دو نوع مقدار TRUE یا FALSE برای سطرهای پارامتر اول باشند. در صورتی که نتیجه این شرطها برای سطری به صورت TRUE باشد، در نتیجه فیلتر ظاهر شده و در غیر اینصورت این سطر از کاربرگ، در نتیجه فرمول دیده نمیشود.
- پارامتر سوم (if_empty): اگر نتیجه اجرای شرطهای بیان شده در پارامتر دوم، هیچ حاصلی روی پارامتر اول نداشته باشد، پارامتر if_empty میتواند به کار رود و در خروجی تابع فیلتر، مقدار تعیین شده را نشان دهد.
به منظور روشن شدن موضوع براساس کاربرگ اطلاعاتی مربوط به تصویر20، تابع FILTER را در مثالهایی به کار خواهیم برد. البته توجه داشته باشید که این کاربرگ و تابع FILTER باید در محیط office 365 نوشته شده باشند.

در تصویر ۲۰، نواحی مختلف تابع FILTER با شماره مشخص شدهاند. ناحیه شماره ۱، همان ناحیه اطلاعاتی است که باید فیلتر شود. ناحیه شماره ۲، ستونی است که مبنای فیلتر قرار گرفته. از طرفی شرط برای این ستون نیز در بخش شماره ۳ مشخص شده. این تابع نیز در ناحیه شماره ۴ نوشته شده است که نتیجه فیلتر را نشان میدهد. واضح است حاصل از اجرای تابع فیلتر در این مجموعه داده، نمایش سطرهایی است که در متغیر محصول (Product) دارای مقدار «سیب» (Apple) هستند.
نکته: نتیجه اجرای تابع فیلتر، به صورت یک بردار ظاهر میشود. در نتیجه باید به اندازه ناحیه اطلاعاتی در محل نوشتن تابع، فضای خالی (شامل سطر و ستون) داشته باشید.
اگر لازم است در تابع فیلتر، شرطهای بیشتری را با یکدیگر ترکیب کنید، میتوانید آنها را با علامت «*» برای ترکیب عطفی (و منطقی) و «+» برای ترکیب فصلی (یا منطقی) به کار ببرید. به مثال زیر توجه کنید که در آن قرار است «محصول» (Product) «سیب» (Apple) مربوط به «ناحیه» (Region) «شرقی» (East) باشد.

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

نکته: عملگر یا ترکیب عطفی در انگلیسی به صورت (AND) و عملگر یا ترکیب فصلی نیز به شکل (OR) استفاده میشود. در نتیجه * نشانگر عمل AND و + نیز بیانگر OR است.
خلاصه و جمعبندی
مطالب مجله فرادرس را در جستجوی گوگل سریعتر ببینید















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