فیلتر پیشرفته در اکسل (Advance filter)

در این مطلب در زمینه فیلتر پیشرفته در اکسل و نحوه استفاده از آن برای پیدا کردن داده هایی که دارای یک یا چند شرط مشخص شده هستند، صحبت خواهیم کرد.

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

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

تفاوت بین فیلتر پیشرفته در اکسل و Autofilter

1. Autofilter یک خاصیت درونی اکسل است که شما میتوانید صرفا با زدن یک دکمه شروع به استفاده از آن کنید، در حالی که فیلتر پیشرفته در اکسل به صورت اتوماتیک عمل نمیکند و شما می بایست محدوده شرط و محدوده داده های خود را به صورت دستی وارد کنید..
2. AutoFilter فقط امکان اعمال فیلتر بر اساس حداکثر دو شرط را میدهد که شما می بایست این شروط را در باکس Custom AutoFilter وارد کنید. در حالیکه با استفاده از فیلتر پیشرفته در اکسل شما میتوانید چندین شرط ساده و پیچیده را تعیین کنید.

در قسمت های بعد توضیحات مفصلی در زمینه نحوه استفاده از فیلتر پیشرفته در اکسل ارائه کرده ایم و سپس تلاش کرده ایم با استفاده از مثال های کاربردی موضوع را برای شما روشن تر کنیم.

نحوه ایجاد یک فیلتر پیشرفته در اکسل

ایجاد یک فیلتر پیشرفته اکسل به سادگی استفاده از autofilter نیست. برای ایجاد یک فیلتر پیشرفته گام های زیر را طی کنید.

1. داده های منبع خود را سازماندهی کنید.

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

2. محدوده شرط(criteria range) را مشخص کنید.

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

نکته:

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

برای مثال برای فیلتر کردن رکوردهای مربوط به منطقه North که مقدار Sub-total آنها بزرگتر یا مساوی 900 باشد، از شرط زیر استفاده میشود:

• Region: North
• Sub-total: >=900

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

3. اعمال فیلتر پیشرفته در اکسل

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

• یک سلول را در درون مجموعه داده خود انتخاب کنید.
• در اکسل 2007 به بعد، گزینه Advanced را در گروه Sort&filter در تب data انتخاب کنید.
• در اکسل 2003، در منو data، در گروه Filter گزینه advanced filter را انتخاب کنید.

دیالوگ باکس مربوطه باز میشود و حال شما می بایست به شکل زیر آن را تنظیم کنید.

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

• Action: در این قسمت می بایست انتخاب کنید که آیا میخواهید داده ها خود را در همان محل فیلتر کنید یا در نتایج را در محلی دیگر کپی کنید. با انتخاب گزینه “Filter the list in place” داده هایی را که مطابق با شرط های شما نیستند از محدوده داده ها، مخفی خواهد کرد.

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

• List range: این پارامتر محدوده سلول هایی را که می بایست فیلتر شوند را مشخص میکند. عناوین ستون نیز می بایست بعنوان محدوده تعریف شوند. در صورتی که قبل از انتخاب گزینه advance یکی از سلول های محدوده خود را انتخاب کرده باشید، اکسل به صورت اتوماتیک کل آن محدوده را انتخاب میکند، در صورتی که این آدرس اشتباه است میتوانید آن را اصلاح کنید.
• Criteria range: این محدوده، شرط های شما را در برمیگیرد.

در این مثال میخواهیم فیلتر را به صورت مستقیم در محل داده ها انجام دهیم در نتیجه می بایست پارامترهای فیلتر پیشرفته خود را به شکل زیر تنظیم کنید.

در نهایت بر روی OK کلیک کنید تا نتایج نمایش داده شود:

فوق العاده بود. ولی ما میتوانستیم با استفاده از خاصیت ساده Autofilter نیز به این هدف دست پیدا کنیم. پس هنوزه مطلب تمام نشده است و ما تفاوت بین Autofilter و فیلتر پیشرفته در اکسل را به درستی  متوجه نشده ایم. در حقیقت تاکنون فقط پوسته ای از فیلتر پیشرفته در اکسل را توضیح داده ایم. بنابراین با در ادامه مطلب نیز با ما همراه باشید. برای راحتر تر شدن کار ابتدا به بررسی کاملتر محدوده شرط(criteria range) میپردازیم.

محدوده شرط (criteria range) در فیلتر پیشرفته در اکسل

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

عملگرهای مقایسه ای برای اعداد و تاریخ

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

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

 

احتمالا شما از قبل نیز با معانی این عملگرها آشنا بوده اید و نیازی به توضیح بیشتر در مورد آنها نیست. در مثال قبل هم از یکی از این عملگرهای مقایسه ای به شکل criteria >=900 استفاده کردیم. در اینجا یک مثال دیگر را بررسی میکنیم.

فرض کنید میخواهید فیلتری را اعمال کنید که مقادیر sub-total را برای ماه july و بزرگتر از 800 نمایش داده شود. برای انجام این کار، در محدوده شرط، شرط های زیر را وارد کنید.

• Region: North
• Order date: >=7/1/2016
• Order date: <=7/30/2016
• Amount: >800

حال ابزار فیلتر پیشرفته اکسل را فعال کنید و محدوده داده های خود را (A4:D50) و محدوده شرط خود را (A2:D2) را قرار دهید تا نتایج بدست آید.

نکته:

شما همیشه می بایست در محدوده criteria range که برای فیلتر پیشرفته در اکسل استفاده میکنید، از فرمت Full date مثل 7/1/2016 یا 1-Jul-2016 استفاده کنید.

فیلتر پیشرفته برای مقادیر متنی در اکسل

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

شرط	توضیحات
=”=text”	سلول هایی فیلتر میشوند که مقدار آنها دقیقا برابر با “text” باشد.
text	سلول هایی فیلتر میشوند که محتوای آنها با  “text” شروع میشود.
<>text	سلول هایی فیلتر میشوند که محتوای آنها دقیقا برابر با  “text” نباشد.
>text	سلول هایی فیلتر میشون که محتوای آنها به لحاظ الفبایی بعد از “text” قرار بگیرد.
<text	سلول هایی فیلتر میشون که محتوای آنها به لحاظ الفبایی قبل از “text” قرار بگیرد.

 

مثال 1. فیلتر کردن برای نمایش مقدار مطابق

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

برای مثال، برای فیلتر کردن آیتم های banana، از شرط =”=banana” استفاده کنید. اکسل این شرط را در سلول به صورت =banana نمایش میدهد اما شما میتوانید کل عبارت شرط را در قسمت Formula bar مشاهده کنید.

همانطور که در تصویر بالا مشاهده میکنید شرط =”=banana” فقط اطلاعاتی را نمایش میدهد که دقیقا شامل banana و مقدار sub-total بالاتر از 900 باشند.

نکته:

برای فیلتر کردن مقادیر عددی که دقیقا برابر با یک مقدار عددی باشند میتوانید از علامت مساوی استفاده کنید یا نکنید. برای مثال، برای فیلتر کردن اطلاعاتی که مقدار sub-total آنها مساوی 900 باشد، میتوانید از =”=900″، =900 یا 900 استفاده کنید.

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

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

برای مثال، برای فیلتر کردن آیتم های “green” با Subtotal بزرگتر و مساوی 900، از شرط های زیر استفاده کنید:

• Item: Green
• Sub-total: >=900

استفاده از کاراکترهای وایلدکارت در فیلتر پیشرفته در اکسل

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

• (?): برای جستجوی مشابهت بر اساس یک کاراکتر یکسان استفاده میشود.
• (*): برای جستجوی زنجیره ای از کاراکترهای یکسان استفاده میشود.
• (~): این کاراکتر برای فیلتر کردن سلول هایی که حاوی یک علامت سوال یا ستاره واقعی هستند استفاده میشود.

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

شرط	توضیحات	مثال
*text*	سلول هایی را نمایش میدهد که شامل text میشوند	برای مثال *banana* سلول هایی را پیدا میکند که شامل کلمه “banana” میشود مثل green banana
??text	سلول هایی را نمایش میدهد که محتوای آنها با دو کاراکتر و بعد از آن text شروع میشود	??banana سلول هایی را نمایش میدهد که محتوای آنها با banana و دو حرف قبل از آن شروع میشود مثل “1#banana” و “//banana”
=”=text*text”	سلول هایی را نمایش میدهد که با text شروع میشوند و با text به پایان میرسند.	=”=banana*banana” سلول هایی را نمایش میدهد که با banana شروع میشوندو به پایان میرسند. مثل “banana, tasty banana”
=”=text1?text2″	سلول هایی را نمایش میدهد که با text1 شروع میشود، با text2 تمام میشود و یک کاراکتر در بین آنها قرار می گیرد.	=”=banana?orange”  سلول هایی را نمایش میدهد که با عبارت “banana” شروع میشوند، با عبارت “orange” تمام میشوند و یک کاراکتر در بین آنها قرار می گیرد. مثل “banana/orange” ، “banana*orange”
text~**	سلول هایی را نمایش میدهد که با text شروع میشود سپس علامت ستاره و سپس یک کاراکتر دیگر	banana~** سلول هایی را نمایش میدهد که با banana شروع میشوند سپس علامت ستاره قرار می گیرد و سپس یک کاراکتر دیگر . مثل “banana*yellow”
=”=?????”	سلول هایی را نمایش میدهد که دقیقا شامل 5 کاراکتر متنی باشند.	=”=?????” سلول هایی را فیلتر میکند که محتوای متنی داشته باشند و دقیقا شامل 5 کاراکتر باشند. مثل “apple” یا “lemon”

 

برای مثال برای نمایش دادن سلول هایی که شامل مقدار banana هستند از شرط (*banana*) استفاده میشود. در این صورت تمام سلول هایی که banana را در کنار دیگر عبارات یا به تنهایی دارند، نمایش داده میشوند.

استفاده از فرمول ها با برای فیلتر پیشرفته در اکسل

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

• فرمول می بایست یک مقدار true یا false داشته باشد.
• محدوده شرط( criteria range) می بایست حداقل دو سلول داشته باشد: سلول حاوی فرمول و سلول عنوان
• سلول عنوان در شرط مبتنی بر فرمول می بایست خالی باشد یا عنوانی متفاوت با عنوان های list range داشته باشد.
• برای فرمول های مربوط به هرکدام از ردیف های محدوده داده ها، از آدرس های نسبی استفاده کنید و در صورتی که فرمول مورد نظر مربوط به یک ردیف خاص از اطلاعات است آدرس های خود را با استفاده از علامت $ مطلق کنید.
• برای قرار دادن محدوده داده ها(list range) خود در درون فرمول حتما از آدرس های مطلق استفاده کنید.

برای مثال برای نمایش دادن ردیف هایی که مقدار فروش ماه august آن( ستون C) بزرگتر از ماه جولای (ستون D) است، از شرط =D5>C5 استفاده کنید. (5 اولین ردیف داده است)

استفاده از منطق AND و OR در فیلتر پیشرفته اکسل

همانطور که در ابتدای این مطلب گفته شد، شما میتوانید بر اساس نحوه تنظیم محدوده شرط خود از منطق های AND  و OR استفاده کنید.

• شرط های موجود در ردیف یکسان با استفاده از AND متصل میشوند.
• شرط های موجود در ردیف های متفاوت با استفاده از OR متصل میشوند.

برای روشن تر شدن موضوع مثال های زیر را بررسی میکنیم.

فیلتر پیشرفته اکسل با استفاده از منطق AND

برای نمایش دادن اطلاعاتی که مقدار Sub-total آن ها >=900 و میانگین آن Average >=350 باشد( هر دو شرط در یک ردیف قرار می گیرند.)

فیلتر پیشرفته در اکسل با استفاده از منطق OR

برای نمایش رکوردهایی که مقدار sub-total  آن >=900 یا  Average >=350 ( شرط ها در ردیف ها مختلفی قرار می گیرند)

نحوه استخراج ستون های خاص

زمانی که فیلتر پیشرفته را به نحوی تنظیم میکنید که نتایج در محلی دیگر کپی شوند، میتوانید مشخص کنید چه ستونی استخراج شود:

1. قبل از اعمال فیلتر، عنوان ستون هایی را که میخواهید استخراج کنید در اولین ردیف محدوده مقصد کپی کنید.

برای مثال، برای کپی کردن داده های ستون های region، item و sub-total بر اساس شرط های مشخص شده، عنوان این سه ستون را در سلول های H1:J1 وارد کنید(لطفا تصویر زیر را مشاهده کنید)

2. فیلتر پیشرفته اکسل را بر اساس دستورالعمل گفته شده در قبل اعمال کنید و گزینه Copy to another location را انتخاب کنید.
3. در باکس Copy to آدرس برچسب های وارد شده در محدوده مقصد را وارد کنید و ok را بزنید. 

در نتیجه، اکسل ردیف ها را بر اساس شرط های وارد شده در محدوده شرط( آیتم های دارای Sub-total بیش از 900، و مربوط به منطقه north) نمایش میدهد و در سه ستون محدوده مشخص شده کپی میکند.

نحوه کپی کردن ردیف های فیلتر شده به یک ورک شیت دیگر

اگر ابزار فیلتر پبشرفته اکسل (Advance filter) را در ورک شیتی که شامل داده های اصلی شماست ، فعال کنید، دستور “Copy to another location” را انتخاب کنید و محدوده ای در یک شیت دیگر را بعنوان Copy to انتخاب کنید، با خطای “You can only copy filtered data to the active sheet” مواجه خواهید شد.

اما یک راه حل برای کپی کردن اطلاعات فیلتر شده در یک شیت دیگر وجود دارد. شاید اگر کمی دقت کنید خودتان متوجه شیوه کار بشوید. در حقیقت در اینجا می بایست کمی زیرکی به خرج بدهیم. به این صورت که فیلتر پیشرفته را از شیت مقصد شروع میکنیم تا شیت مقصد توسط اکسل بعنوان شیت فعال (active sheet) در نظر گرفته شود.

فرض کنید جدول داده های اصلی شما در شیت 1 قرار دارد، و شما میخواهید اطلاعات فیلتر شده را در شیت 2 کپی کنید. روش کار بسیار ساده خواهد بود:

1. برای شروع محدوده شرط( criteria range) خود را در شیت 1 قرار دهید.
2. به شیت 2 منتقل شوید، و یک سلول خالی در یک قسمت استفاده نشده از شیت را انتخاب کنید.
3. فیلتر پیشرفته اکسل را فعال کنید (Data tab > Advanced)
4. در پنجره advance filter گزینه های زیر را انتخاب کنید:

• در قسمت action، گزینه Copy to another location را انتخاب کنید.
• در باکس list range کلیک کنید و به شیت 1 منتقل شوید و آدرس محدوده داده ها را انتخاب کنید.
• در باکس Criteria range کلیک کنید، به شیت 1 منتقل شوید و محدوده شرط(criteria range) را انتخاب کنید.
• در باکس copy to، کلیک کنید و سلول گوشه بالا سمت چپ محدوده مقصد را در شیت 2 انتخاب کنید.
• Ok را کلیک کنید.

مثال

فرض کنید میخواهیم 4 ستون را فیلتر کرده و در شیت 2 قرار دهیم. بنابراین عنوان چهار ستون را دقیقا به شیت 2 کپی می کنیم و محدوده عنوان های وارد شده (A1:D1)  را در باکس copy to وارد میکنیم.

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