ابزار قدرتمند Advanced Filter

فیلتر پیشرفته در اکسل (Advanced Filter)

در مقالات قبلی راجع به فیلتر دیدیم که ابزار filter در اکسل امکاناتی درون خودش داره برای فیلتر کردن متن، عدد و تاریخ، که کاربردهای زیادی دارن و میتونن خیلی از نیاز های ما رو برای فیلتر کردن داده ها برآورده کنن. اما برخی از نیازهامون، نه همش رو. وقتی که الگوهای آماده فیلتر نیاز ما رو برآورده نکنه، میتونیم از فیلتر پیشرفته (Advanced Filter) استفاده کنیم و شرط های فیلتر رو اعمال کنیم.ابزار Advanced filter کاربرد خیلی زیادی داره چون میتونه شرط های متنوعی رو بصورت تکی یا چندگانه روی داده ها اعمال کنه. فیلتر روی قسمتی از داده ها با شروط مختلف و یا جدا کردن داده های فیلتر شده از لیست و … از توانایی های این ابزار فوق العاده کاربردی است.این ابزار توانمند در همه ورژن های اکسل یعنی از ۲۰۰۳ به بعد موجود هست. در ادامه به معرفی توانایی ها و امکانات این ابزار می پردازیم:

مقایسه Autofilter و Advanced Filter

این دو ابزار به دو جهت با هم تفاوت اساسی دارند:

1. ابزار Autofilter یک امکان آماده در اکسل هست که با فشردن یک کلیک آماده استفاده هست. کافیه روی داده ها کلیک کنیم و دکمه Filter رو بزنیم. ولی ابزار Advanced Filter نمیتونه بصورت خودکار و بدون هیچ تنظیم قبلی کار کنه. این ابزار نیاز به یک محدوده برای تعیین شروط داره که باید از قبل و بصورت دستی آماده باشن تا بتونیم از این ابزار استفاده کنیم.
2. ابزار Autofilter نهایتا میتونه دو تا شرط رو بصورت همزمان و با منطق “و” و “یا” در قسمت Custom Filter اعمال کنه ولی ابزار Advanced Filter میتونه شرط های مختلف و چندگانه رو همزمان اعمال کنه.

در ادامه به جزئیات و نحوه کارکرد این ابزار می پردازیم:استفاده از ابزار Advanced Filter براحتی ابزار Autofilter نیست. اما ارزش اینو داره که روش استفاده از این ابزار خیلی پرکاربرد رو یاد بگیریم. برای استفاده از این ابزار طبق مراحل زیر پیش میریم:

1. داده های مورد نظر رو مرتب میکنیم

برای اینکه داده ها به درستی مرتب بشن، بهتره دو قاعده زیر رو رعایت کنیم:

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

2. محدوده شرط های مورد نظر رو ایجاد میکنیم

شروط مد نظر رو تایپ میکنیم. به اینصورت که عنوان سر ستون رو تایپ کرده و مقادیر (شروط) دلخواه رو در زیر اون وارد میکنیم. شرط ها رو هر جایی از شیت میتونیم وارد کنیم. اما بصورت تجربی، بهتره که چند ردیف خالی بالای داده ها ایجاد کنیم و شروط رو در اونجا بنویسیم. این کار رو برای این انجا ممیدیم که در صورت فیلتر دشن داده ها، شروط مورد استفاده همچنان در بالا نمایان باشن و در داده های فیلتر شده قرار نگیرن و پنهان نشن.دو نکته خیلی مهم در ثبت شروط:محدوده شروط باید عینا عنوان سر ستون های داده ها رو داشته باشن.شروطی که در کنار هم و در یک ردیف قرار میگیرند با منطق “و” و شروطی که در ردیف های مختلف قرار میگیرن با منطق “یا” اعمال میشن.مثلا برای فیلتر کردن داده هایی که در ناحیه شمال قرار گرفتن و مقدار فروش بیش از ۱۰۰۰۰۰ هست، شرط رو بصورت زیر می نویسیم:

شکل ۱- نحوه نوشتن شرط برای ابزار Advanced Filter

3. Advance Filter رو روی داده ها اعمال میکنیم

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

• روی یک یاز سلول های دیتابیس کلیک میکنیم
• از تب Data و قسمت Sort & Filter گزینه Advance رو انتخاب میکنیم

حالا پنجره مربوط به Advance Filter نمای شداده میشه که باید طبق توضیحات زیر تکمیلش کنیم.

4. تنظیم کردن پارامترهای فیلتر

در پنجره Advanced Filter پارامترهای زیر وجود دارن که با توجه به خواستمون باید اونها رو تکمیل کنیم:Action: انتخاب میکنیم که فیلتر در همون محل انجام بشه یا نتیجه فیلتر رو در جای دیگه کپی کنه.در واقع انتخاب اینکه در محل فیلتر کنه، نتیجه ای مشابه Autofilter خواهد داشت. یعنی داده هایی که با شرط همخوانی ندارند پنهان میشن.اگر گزینه دوم رو انتخاب کنیم، گزینه Copy to فعال میشه که باید آدرس اولین سلولی که میخوایم نتیجه فیلتر اونجا نمایش داده بشه، رو تعیین کنیم. فقط باید دقت کنیم که محدوده انتخابی حاوی داده نباشه و خالی باشه که نتیجه فیلتر بتونه در اون محدوده نمایش داده بشه.List Range:  محدوده داده هایی هست که قراره فیلتر بشن در این قسمت مشخص میشه. اگر اکسل بصورت پیشفرض محدوده رو به درستی انتخاب نکرده بود، با کلیک بر روی دکمه انتخاب محدوده، اون رو اصلاح میکنیم.Criteria Range: محدوده شرط مورد نظر برای فیلتر داده ها در این قسمت تعیین میشه.چک باکس موجود در گوشه سمت چپ پایین، از هر داده یک مورد رو انتقال میده و برای تهیه لیست بدون تکرار کاربرد داره.

شکل ۲- تعیین پارامترهای ابزار Advanced Filter

در پایان، با زدن OK نتیجه زیر رو بصورت شکل ۳ خواهید دید:

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

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

شرط های این ابزار

در این قسمت میخوایم به جزئیاتی بپردازیم که اگر به همه زوایای اونها مسلط باشیم، امکانات نامحدودی در تعیین شرط برای فیلتر کردن داده ها خواهیم داشت.عملگرهای مقایسه ای برای رشته های تاریخی (میلادی) و عددیدر قسمت شرط Advanced Filter میتونیم عملگرهای مقایسه ای که در جدول زیر نمایش داده شده هم داشته باشیم.

مثال	معنی	عملگر مقایسه ای
A1=B1	برابر با	=
A1>B1	بزرگتر از	>
A1<B1	کوچکتر از	<
A1>=B1	بزرگتر یا مساوی	>=
A1<=B1	کوچکتر یا مساوی	<=
A1<>B1	مخالف با	<>

نحوه استفاده از این عملگرها رو در مثال بالا دیدیم. در ادامه مثال دیگری از این عملگرها رو خواهیم دید:میخواهیم فروش ناحیه شمال، آنها که بین ۵۰۰۰۰ و ۱۲۰۰۰۰ هستن رو فیلتر کنیم. طبق شکل ۴ شرط ها رو تعیین میکنیم.

شکل ۴- استفاده از عملگرها برای اعمال شرط های چندگانه

همونطور که  میدونیم، تاریخ های میلادی هم چون نوعی عدد هستند، براحتی و با همین منطق میتونن توسط عملگرها مقایسه بشن و در فیلتر اعمال بشن. مثلا فروش بین دو بازه تاریخی در ناحیه شمال. اما نکته ای که باید توجه داشته باشیم فرمت تاریخ مورد استفاده است که باید همیشه فرمت کامل باشه تا اکسل دچار اشتباه نشه و به درستی بتونه تاریخ رو تشخیص بده. فرمت هایی مثل ۷/۱/۲۰۱۶  یا  ۱-Jul-۲۰۱۶.

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

گاهی اوقات لازمه که در رشته های متنی، قسمتی رو مورد جستجو قرار بدیم. مثلا دو حرف اول، سه حرف آخر یا … برای این کار از علامت های *, ?, ~ استفاده میکنیم و شرط های مورد نظر خودمون رو میسازیم.

• علامت سوال ?، نشان دهنده یک کاراکتر هست
• علامت ستاره *، نشان دهنده هر تعداد کاراکتر هست.
• ~ برای پیدا کردن علامت های ? ،*  و  ~ استفاده میشه.

توضیح	شرط
سلول هایی که شامل text هستن رو فیلتر میکنه	*text*
سلول هایی که دو حرف اولشو هر چیزی میتونه باشه و به کلمه text ختم بشن رو فیلتر میکنه	??text
سلولی که با کلمه text شروه میشه و این کلمه یکبار دیگه، هر جای سلول تکرار بشه رو فیلتر میکنه	text*text
سلول هایی که اول و آخرشون کلمه text نوشته شده رو فیلتر میکنه. (هرچیزی میتونه بینشون باشه)	=”=text*text”
سلولی که با کلمه text شروع میشه و به دو تا علامت * ختم میشه رو فیلتر میکنه	text~**
سلول های متنی که دقیقا ۵ کاراکتر هستند رو فیلتر میکنه	=”=?????”

شکل ۵- فیلتر کردن داده ها با استفاده از Wildcards

استفاده از منطق “و” و “یا” در ابزار Advanced Filter

همونطور که در بالا اشاره شد، شرط ها در این ابزار علاوه بر اینکه میتونن بیش از دو تا باشن، میتونن منطق “و” و “یا” هم داشته باشن.

• شرط هایی که در یک ردیف هستن، منطق “و” دارند.
• شرط هایی که در ردیف های متفاوتی قرار میگیرن، منطق “یا” دارند.

برای درک بهتر این موضوع، مثال های زیر رو بررسی میکنیم:ابزار Advanced Filter با منطق “و”میخواهیم داده هایی که هم میانگین بالای ۸۰۰۰۰ دارند و هم مقدار فروش اونها بیش از ۱۰۰۰۰۰ هست رو فیلتر کنیم:

شکل ۶- فیلتر پیشرفته با دو شرط با منطق “و”

ابزار Advanced Filter با منطق “یا”میخواهیم داده هایی که یا میانگین بالای ۴۰۰۰۰ دارند و یا مقدار فروش اونها بیش از ۱۰۰۰۰۰ هست رو فیلتر کنیم:

شکل ۷- فیلتر پیشرفته با دو شرط با منطق “یا” 

ابزار Advanced Filter و ترکیب منطق “و” و “یا”میخواهیم داده هایی که شمال هستن و فروش بیش از ۱۰۰۰۰۰ دارن، یا داده هایی که شمال هستن و میانگین فروش اونها بیش از ۴۰۰۰۰ هست رو فیلتر کنیم. یعنی ترکیب منطق “و” و “یا” با هم. بعبارتی:(شمال و فروش بیش از ۱۰۰۰۰۰) یا (شمال و میانگین بیش از ۴۰۰۰۰)

شکل ۸- فیلتر پیشرفته در اکسل با ترکیب منطق “و” و “یا”

فرمول نویسی در شروط در ابزار Advanced Filter هم امکان پذیر هست که در آینده به این موضوع خواهیم پرداخت.نحوه فیلتر کردن بخشی از داده هامیتونیم در حین فیلتر کردن، بگیم نتیجه روی بخشی از داده ها (ستون های خاص) اعمال بشه. برای این کار طبق مراحل زیر عمل میکنیم:

• قبل از فیلتر کردن، اول سرستون های مورد نظر رو که میخوایم نتیجه فیلتر روی اونها نمایش داده بشه رو انتخاب میکنیم و در جای دیگری کپی میکنیم.

مثلا میخوایم ناحیه، کد محصول و کل فروش رو در نتیجه فیلتر ببینیم.

• Advance Filter رو انتخاب میکنیم و در قسمت Action گزینه Copy to another location رو انتخاب میکنیم.
• در قسمت Copy to محدوده سرستون هایی که تایپ کردیم رو بهش میدیم. طبق شکل ۹، محدوده I1:K1 رو انتخاب میکنیم.

شکل ۹- اعمال فیلتر روی برخی از ستون های انتخابی با استفاده از فیلتر پیشرفته

با زدن OK نتیجه بصورت شکل ۱۰ نمایش داده خواهد شد.

شکل ۱۰- فیلتر کردن داده ها روی برخی از ستون های انتخابی

در این مقاله با ابزار پرکاربرد و قدرتمند فیلتر پیشرفته (Advanced Filter) آشنا شدیم. در مقالات آینده بررسی بیشتری خواهیم داشت و به بحث فرمول نویسی در Criteria خواهیم پرداخت.دانلود فایل اکسل این آموزشبرای دانلود فایل این آموزش روی دکمه زیر کلیک کنید: