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

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


 

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

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

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

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

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

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

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

 

Description: Database

 

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

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

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

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

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

Description: Criteria Range

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

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

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

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

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

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

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

Description: Criteria Range

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

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

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

Description: Criteria Range

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

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

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

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

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

Description: Apply Filter

فیلم : نحوه استخراج داده‌های غیر تکراری (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  را کلیک کنید

Description: Extract Data to Another Sheet

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

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

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

Description: C:\Documents and Settings\name_less\My Documents\Downloads\Documents\Excel Advanced Filter Introduction_files\advancedfilter07.png

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

 Description: C:\Documents and Settings\name_less\My Documents\Downloads\Documents\Excel Advanced Filter Introduction_files\advancedfilter08.png

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

Description: C:\Documents and Settings\name_less\My Documents\Downloads\Documents\Excel Advanced Filter Introduction_files\advancedfilter09.png

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

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

استفاده از wildcards

علامت (*)

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

Description: C:\Documents and Settings\name_less\My Documents\Downloads\Documents\Excel Advanced Filter Introduction_files\advancedfilter10.png

علامت ؟

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

Description: C:\Documents and Settings\name_less\My Documents\Downloads\Documents\Excel Advanced Filter Introduction_files\advancedfilter11.png

علامت ~

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

Description: C:\Documents and Settings\name_less\My Documents\Downloads\Documents\Excel Advanced Filter Introduction_files\advancedfilter12.png

Description: C:\Documents and Settings\name_less\My Documents\Downloads\Documents\Excel Advanced Filter Introduction_files\advancedfilter13.png

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

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

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

Description: Extract Items

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

Description: Multiple Conditions

 

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

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

Description: Multiple Conditions

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

دیدگاه‌ها

عکس‌های sanmodirus
sanmodirus

دمتون گرم

عکس‌های جواد محمودآبادی
جواد محمودآبادی

عالی بود

تشکر

عکس‌های admin
admin

دوستان گرامی، ممنون از لطف های شما. اگر مقاله ای برای شما جالب بود لطفا بر روی آیکون g+1 در سمت راست پایین صفحه کلیک نمایید.

عکس‌های اکبر صبوری
اکبر صبوری

با سلام و خسته نباشید.

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

عکس‌های admin
admin

لطفا سوالات خود را در انجمن مطرح نمایید. اما با توجه به سوال شما با توابع Vlookup و ... می توانید اینکار را انجام دهید. شما می توانید در دوره اینترنتی "توابع Lookup پیشرفته" شرکت نمایید، مشاهده سرفصل ها و ثبت نام در آدرس زیر:

vira.farsaran.com
 

عکس‌های hrn14459
hrn14459

سلام . برای من بسیار کاربردی بود . سپاس فراوان

عکس‌های الهه
الهه

عالی بود سپاسگذارم

عکس‌های علی جبارزارع
علی جبارزارع

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

عکس‌های آنیا
آنیا

سلام.میشه شیت ها رو هم فیلتر کرد؟مثلا 80 تا شیت دارم دنبال شیت نمودار میگردم.میخوام فیلتر کنم که فقط شیت نمودارو نشون بده.همچین کاری امکان پذیره تو اکسل؟

عکس‌های فرشید میدانی
فرشید میدانی

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

 

Sub FilterSheets()

Dim sh As String
Dim sh1 As Worksheet
'-- By www.farsaran.com

On Error Resume Next
sh = InputBox("Enter Sheet Name (* and %) :", "Filter Sheets ")


For Each sh1 In Sheets

    If sh1.Name Like ("*" & sh & "*") Then
        sh1.Visible = True
    Else
        sh1.Visible = False
    End If

Next sh1


End Sub

عکس‌های kaster
kaster

سلام

من یک شیت دارم شامل یک سری اطلاعات پکینگ لیست کالا. شامل نام کالا تعداد و قیمت شماره کارتنی که آن کالاها در آن قرار دارد و ... در آن است و اسم بعضی کالا ها تکراری. میخواهم بدانم

در مجموع از هر کالا چندتا موجود است؟

جمع ارزش هر کالا چقدر است؟

هر کالا در چه کارتن هایی موجود است؟

سوال دیگر اینکه آیا میتوانم اطلاعات Row در یک شیت یا یک فایل با شیت یا فایل دیگر را مقایسه کرد؟

مثلا در یک فایل کالا شماره فنی یک کالا حاوی اطلاعاتی مثل تعداد و قیمت است که برای 3 ماه قبل است حال ما بخواهیم با یک فایل دیگر که همین اطلاعات را دارد و برای امروز است را مقایسه کنیم تا ببینیم قیمت کالا یا تعداد آن تغییر کرده یا خیر؟

 

با سپاس

افزودن دیدگاه جدید