فیلتر پیشرفته در Excel

مقاله زیر توسط جناب آقای «فرشــاد منصـوریان» از سایت Contextures  ترجمه شده است و توسط ایشان در اختیار کاربران فرساران قرار گرفته است. با تشکر و قدردانی از ایشان، این مقاله ارائه می‌گردد.

---

 

در همین آغاز باید بگویم از مجموعه داده هائی با 10000 رکورد در چشم بر هم زدنی با وجود پنج شرط مثلا میتوان 800 رکورد جدا و در جای دیگری کپی کرد این وقتی زیباتر می شود که بدانید میتوان در یک ماکرو  Advanced Filter را بکار برد یعنی با نوشتن فقط یک خط کد اتفاقی که شرح آن رفت را می توان رقم زد

ذیلا مراحل تنظیم مجموعه داده ها و ایجاد  فیلتر پیشرفته شرح داده می شود :

1- تنظیم مجموعه داده ها

1- اولین ردیف (A1:D1)  شامل سر ستونهاست

2- سپس داده را ذیل سر ستونها می نویسیم

3- هیچ ردیف خالی در بین داده ها وجود ندارد

4- ردیف خالی در انتهای مجموعه داده ها و ستون خالی نیز در انتهای ستون داده هاست

 

 

2- تنظیم محدودهء شرط ها (اختیاری )

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

1- در این مثال سلولهای F1:F2  محدوده  شرط است که می تواند بیش از این تعداد نیز باشد

2- سر ستون F1  دقیقا با سر ستون D1  در مجموعه  داده ها یکی است

3- سلول F2  شامل شرط است . عملگر < (بزرگتر از ) مورد استفاده قرار گرفته است با عدد 500 (بدون بکارگیری علامت $)

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

دیگر عملگرها شامل : <  کمتر از > بیشتر از <=   بزرگتر یا مساوی     >= کوچکتر یا مساوی <> نابرابر با

3- تنظیم محدوده استخراج شده بعد از اعمال فیلتر (اختیاری)

اگر هدف شما کپی کردن داده ها فیلتر شده در محل دیگریست ، می توانید ستونهائی که قصد استخراجشان را دارید مشخص کنید

( اگر قصد استخراج همه ستونها را دارید این مرحله را به خود excel  واگذارید.)

1- در شکل زیر سلول بالا و چپ را برای داده های استخراج شده انتخاب کنید

2- سرستونهای (فیلدهائی) که قصد استخراجشان را دارید تایپ کنید آنها دقیقا همان فیلدهائی باید باشند که در مجموعه داده ها وجود دارند .

4- اجرای فیلتراسیون پیشرفته اکسل

1- در مجموعه داده ها یک سلول را انتخاب کنید

2- در ریبون data  مجموعهء sort &filter  ، tab ِ "advance filter" را به منظور باز شدن پنجره advance filter کلیک کنید

3- انتخاب با شماست که خروجی فیلتراسیون در همین شیت یا در جای دیگری کپی شود.

4- پیشفرض اکسل بطور اتوماتیک نشان دادن لیست داده ها در همان sheet  یست که مجموعه داده ها وجود دارند وگرنه copy to anather lacation  را تیک کنید

5- محدوده شرط(یا شروط) را انتخاب کنید

6- اگر قصد دارید داده را در جای دیگری کپی کنید برای copy to  یک سلول در مکان دیگری انتخاب کنید  نوجه : اگر خروجی فیلتراسیون شما در مکان دیگری باشد همه سلول های زیر محدوده استخراج شده هنگام اعمال فیلتر پاک خواهند شد .

7- OK  را کلیک کنید

فیلم : نحوه استخراج داده‌های غیر تکراری (unique records)

 

 

فیلتر کردن داده ها بصورت منحصر بفرد ( unique )

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

توجه : لیست باید شامل سرستون باشد وگرنه ممکن است اولین آیتم بصورت دوبل ظاهر شود

1- یک سلول را در مجموعه داده ها انتخاب کنید

2- در ریبون data  مجموعهء sort &filter  ، tab ِ "advance filter" را به منظور باز شدن پنجره advance filter کلیک کنید

3- Copy to anather location  را انتخاب کنید

4- ستون (ستونها)ی را که می خواهید از آنها استخراج uniqe صورت گیرد انتخاب کنید

5- Criteria range  را خالی بگذارید

6- سلولی را برای شروع کپی در محلی دیگر انتخاب کنید

7- قسمت unique record only  را تیک کنید

8- OK  را کلیک کنید

 

استخراج داده ها در شیت‌ دیگر

اگر داده ها در برگه ای باشند شما می توانید در برگه ای دیگر داده های مورد نظرتان را زاآن اسنخراج کنید در این مثال داده ها در sheet1 و نتیجه در sheet2 کپی می شود.

1-    به sheet2  بروید

2-    سلولی را در بخش استفاده نشده برگه انتخاب کنید (در اینجا C4)

3-    Advance filter  را کلیک کنید

4-    Copy to anather location  را انتخاب کنید

5-    در جعبهء the list range کلیک کنید

6-     در  sheet1 محدوده داده ها را انتخاب کنید

7-    در بخش  criteria range  کلیک کنید (اختیاری)

8-    محدوده شرط را انتخاب کنید

9-    در باکس copy to  کلیک کنید

10-      درsheet2  یک سلول که می خواهید کپی داده ها از آنجا اغاز شود یا سرستونهائی که تایپ کرده اید را انتخاب کنید

11-      در صورت تمایل unique record only  نیز تیک کنید(اختیاری)

12-      OK  را کلیک کنید

چیدمان محدوده شرطها

مقایسه گزینه AND و OR

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

در این مثال مشتری magamart  ، محصول cookies   و total  500< است اگر محدوده را بشکل زیر تعریف کنیم عملگر OR  در فیلتراسیون اعمال می شود

 

و اگر قصد استفادهء همزمان از عملگرهای AND  و OR  را دارید محدوده شروط را بشکل زیر تنظیم کنید

این شکل بیان گر این است که مشتری باید megamart  و محصول باید cookies  باشد یا 

محصول باید cookies  و total  باید 500< باشد

استفاده از wildcards

علامت (*)

جایگزین چند کاراکتر می شود در این مثال هر مشتری که نامش شامل "mart" باشد از فیلتر اکسل عبور می کند

علامت ؟

که بجای یک کاراکتر می نشیند در این مثال همه محصولاتی که شامل c?oke  هستند از فیلتر عبور می کنند coke  یا  cake

علامت ~

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

ملاحظه می کنید که در اولی تمام آنچه که با good  شروع  و به  eats  ختم میشود از فیلتر عبور کرده اما در دومی eats  از فیلتر عبور کرده و نه  treats

مثالی برای فیلتر کردن فیلد تاریخ .

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

قرار دادن دو یا چند مجموعه از شروط :

 

استخراج آیتم ها با متنی خاص

وقتی شما یک متن را در شرط قرار می دهید اکسل همه آیتم هایئ که شامل این متن است را از فیلتر عبور می دهد مثلا اگر شرط "Ice"  باشد  "ice milk" "Ice" "Ice cream"استخراج می شوند . برای پرهیز از چنین اتفاقی در صورت نیاز مانند شکل عمل کنید