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

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

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

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

• چگونه یک فیلتر پیشرفته در اکسل ایجاد کنیم؟

فرض کنید جدولی از رشته های مهندسی در یک دانشکده را بصورت تصویر زیر در اختیار دارید که شامل نام رشته ؛ تعداد کل دانشجویان ؛ تعداد دانشجویان زن و مرد و نیز میانگین سن آنها می باشد.

• به این نکات توجه داشته باشید!

قبل از آنکه کار روی جدول بالا را آغاز کنیم بایستی چند نکته را بر روی ساختار جدول خودمان که اطلاعات در درون آنها قرار گرفته است رعایت کنیم ؛ که آن چند نکته بصورت زیر هستند.

1. جدول حاوی اطلاعات باید فاقد هرگونه سطر خالی باشد ؛ اگر اینطور نیست در آموزش نحوه حذف سطرهای خالی در اکسل توضیح دادیم که چگونه می‌توان این مشکل را با چند کلیک ساده برطرف کرد.
2. عناوین یا سر تیترهای جدول شما بایستی منحصربفرد باشند ؛ در Table ها این موضوع بشکل خودکار توسط نرم افزار اکسل کنترل خواهد شد.
3. بهتر است محلی که شرط های خود را در داخل آن می نویسید در داخل محدوده فیلتر نباشد ؛ بطور مثال می توانید با ایجاد چند ردیف خالی در بالای جدول ؛ شرط یا شروط خود را در آن ناحیه بنویسید.
4. دقت داشته باشید ؛ محدوده ای که شرط در داخل آن قرار می گیرد باید با عنوان ستونی که مربوط به جدول شماست ؛ یکسان باشد.

• یک فیلتر ساده ایجاد کنید!

قصد داریم تا با اعمال فیلتر ؛ رشته هایی که تعداد کل دانشجویان آنها بیشتر از 100 است ؛ را به نمایش در آوریم ؛ برای انجام اینکار ابتدا بر روی یکی از عناوین موجود در جدولی که قرار است فیلتر بر روی آن اعمال شود کلیک میکنیم تا انتخاب شود ؛ سپس با مراجعه به تب Data در گروه Sort & Filter روی گزینه Advanced کلیک می کنیم.

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

• Filter the list, in-place : با انتخاب این گزینه نتایج فیلتر اعمال شده بر روی خود جدول اعمال خواهد شد.
• Copy to another location : با انتخاب این گزینه نتیجه فیلتر اعمال شده در محلی که شما مشخص می کنید ایجاد خواهد شد.
• List range : محدوده جدول حاوی اطلاعات که میخواهیم فیلتر بر روی آن اعمال شود.
• Criteria range : محدوده ای که شرط یا شروط فیلتر در داخل آن قرار دارد.

همانطور که در تصویر بالا ملاحظه می کنید ؛ شرط نمایش رشته هایی که تعداد کل دانشجویان آنها بیشتر از صد نفر را ؛ بصورت 100< درج کردیم و نتیجه بشکل آنچه در تصویر زیر ملاحظه می کنید درآمده است. در واقع از عملگر < مقایسه استفاده نمودیم.

• عملگرهای مقایسه ای عددی

علاوه بر عملگری که در بالا از آن استفاده کردیم ؛ عملگرهای دیگری نیز وجود دارند که در اعمال شروط می توانیم آنها را مورد استفاده قرار دهیم ؛ از این عملگرها می‌توانیم در مقایسه مقادیر عددی و تاریخ استفاده کنیم.

• مساوی =
• نا مساوی <>
• کوچکتر >
• بزرگتر <
• کوچکتر مساوی >=
• بزرگتر مساوی <=

فرض کنید در مثال بالا اینبار میخواهیم تعداد شرط ها را از یک شرط به سه شرط افزایش دهیم ؛ در واقع قصد داریم رشته هایی را به نمایش درآوریم که در آنها تعداد کل دانشجویان بیشتر از 100 ؛ تعداد دانشجویان زن بیشتر از 60 و میانگین سن دانشجویان زن بزرگتر مساوی 20 است را به نمایش در آوریم ؛ مطابق تصویر زیر برای انجام اینکار بایستی از عملگرهای مناسب استفاده کنیم.

• عملگرهای مقایسه ای متنی

علاوه بر عملگرهای مقایسه ای ریاضی ؛ نوع دیگری از عملگرها نیز وجود دارند که از آنها در مقایسه مقادیر متنی استفاده می کنیم ؛ این عملگرهای 5 گانه بصورت زیر هستند:

• • =”=Text” : سلولهایی فیلتر خواهند شد که مقدار آنها دقیقأ برابر با Text باشد.
  • Text : سلولهایی فیلتر خواهند شد که مقدار قرار گرفته در آنها با Text شروع شود.
  • <>Text : سلولهایی فیلتر خواهند شد که مقدار قرار گرفته در آنها دقیقأ برابر با Text نباشد.
  • >Text : سلولهایی فیلتر خواهند شد که مقدار آنها بعد از Text قرار بگیرد.
  • <Text : سلولهایی فیلتر خواهند شد که مقدار آنها قبل از Text قرار بگیرد.

فرض کنید ؛ قصد دارید فیلتر را بر روی رشته متالوژی اعمال کنید ؛ برای انجام اینکار کافی است مطابق تصویر زیر از عملگر شماره 1 بصورت =”=متالوژی” استفاده کنیم.

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

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

• *text* : سلول هایی را نشان می‌دهد که شامل text می‌شوند.
• ??text : سلول هایی را نشان می‌دهد که محتوای آنها با دو کاراکتر و بعد از آن text شروع می‌شود.
• =”=text*text” : سلول هایی را نشان می‌دهد که با text شروع میشوند و با text به پایان می‌رسند.
• =”=”text1?text2 : سلول هایی را نشان می‌دهد که با text1 شروع می‌شود، با text2 تمام می‌شود و یک کاراکتر در بین آنها قرار می‌گیرد.
• text~** : سلول هایی را نشان میدهد که با text شروع می‌شود سپس علامت ستاره و سپس یک کاراکتر دیگر قرار خواهد گرفت.
• =”=?????” : سلول هایی را نشان میدهد که دقیقا شامل 5 کاراکتر متنی باشند.

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