فیلتر در اکسل — راهنمای کاربردی + فیلم آموزشی رایگان
اکسل به عنوان یک از ابزارهای بسیار کاربردی در زمینه پردازش دادهها، محبوبیت خاصی دارد و انبوه کثیری از کاربران رایانه، بطور روزانه از آن استفاده کرده و دادهها را پردازش میکنند. اگر قرار باشد که فقط بعضی از سطرهای کاربرگ مورد بررسی قرار گرفته و محاسبات روی آنها صورت گیرد، لازم است از «فیلتر گذاری» (Filtering) که ابزار مناسب برای این کار در اکسل است، بهره ببریم. به همین علت این نوشتار از مجله فرادرس را به نحوه به کارگیری ابزار فیلتر در اکسل و همینطور تابع فیلتر اختصاص دادهایم.
فیلم آموزشی فیلتر اطلاعات در اکسل
برای آگاهی نسبت به نحوه کار در محیط اکسل بهتر است نوشتارهای آموزش اکسل — به زبان ساده و فرمول نویسی در اکسل — به زبان ساده را مطالعه کنید. همچنین خواندن مطالب فرمول چند شرطی در اکسل — به زبان ساده و تابع IF در اکسل — به زبان ساده نیز خالی از لطف نیست.
فیلتر در اکسل
فیلتر در دنیای واقعی به ابزاری برای جداسازی گفته میشود. در اکسل نیز فیلتر همین وظیفه را دارد. به این معنی که به کمک فیلتر در اکسل میتوانیم بعضی از سطرهای کاربرگ را به کمک معرفی کردن یک یا چند شرط، جدا کرده و براساس آنها محاسباتی را صورت دهیم.
برای استفاده از فیلتر در اکسل دو راهکار وجود دارد. میتوان با استفاده از ابزار فیلترگذاری، شرط یا شرطهایی را برای جدا کردن سطرهای کاربرگ مشخص کرد. از طرف دیگر با قید کردن ناحیهای برای مشخص کردن شرط، امکان جداسازی چنین سطرهایی، به کمک فیلتر پیشرفته در اکسل وجود دارد. البته میتوانید با کپی کردن نتیجه فیلتر در کاربرگ یا ناحیه دیگر، تغییری در کاربرگ اصلی ایجاد نکرده ولی مطابق شرط داده شده، سطرهای دلخواه را در اختیار کاربر قرار دهید.
قبل از هر چیز بهتر است هدف از اجرای فیلتر در اکسل را بدانیم. در بحث مربوط به مرتبسازی در اکسل به این موضوع پرداختیم که به کمک ایجاد یک ترتیب در کاربرگ، میتوانیم سطرها را به دلخواه مرتب کرده و جستجو را سریعتر و راحتتر انجام دهیم. بعلاوه مرتب سازی براساس اقلام و مقادیر تکراری، باعث گروهبندی سطرهای کاربرگ میشود. با توجه به این موضوعات، مشخص است که گروهبندی یکی از مزایای استفاده از مرتبسازی است.
همین دلایل را میتوان برای فیلتر کردن نیز در نظر گرفت. با این تفاوت که بعد از اجرای فیلتر، با به کارگیری بعضی از توابع در اکسل، میتوانیم محاسباتی را برای سطرهای انتخاب شده، انجام دهیم. به این ترتیب اهداف زیر را برای فیلتر در اکسل مشخص میکنیم.
- جستجو یک یا چند مقدار در یک ستون مشخص.
- دستهبندی و گروهبندی سطرهای اکسل براساس یک یا چند ستون.
- اجرای محاسبات روی گروه ایجاد شده براساس فیلتر.
نکته: محاسبات روی سطرهای فیلتر شده در اکسل توسط تابع SUBTOTAL صورت میگیرد که در نوشتار دیگری از مجله فرادرس به آن نیز خواهیم پرداخت.
فیلتر در اکسل به دو شیوه اعمال میشود. یکی از روشها که معمولا از آن به عنوان فیلتر ساده یاد میکنیم، استفاده از ابزار فیلتر گذاری روی ستونها است. به این ترتیب با انتخاب اقلام مورد نظر برای ستونها، قادر هستیم سطرهای متناظر با آن مقادیر را از کاربرگ نمایش داده و بقیه سطرها را مخفی کنیم.
اما در روش دوم، که به فیلتر پیشرفته (Advance Filter) معروف است، میتوانیم شرطها برای انتخاب مقادیر مرتبط با ستونها را به صورت سفارشی درون کاربرگ و سلولهای آن تعیین کرده و شرطهای پیچیدهتری نسبت به فیلتر عادی، اعمال کنیم. همچنین در این حالت، اختیار کپی کردن نتیجه فیلتر به ناحیه یا کاربرگ جدید نیز میسر است.
در ادامه متن به هر دو شیوه فیلتر کردن خواهیم پرداخت. البته در انتها نیز به تابعی در اکسل ۳۶۵ خواهیم پرداخت که وظیفه فیلتر کردن دادهها را در نسخه آنلاین اکسل، به عهده دارد.
برای شروع کار بهتر است ابتدا یک فایل اطلاعاتی را مبنای کار قرار داده و براساس آن مثالها را دنبال کنیم. در زیر نمایی از یک کاربرگ اطلاعاتی برای اجرای فیلتر را مشاهده میکنید. این کاربرگ شامل چهار ستون و ۱۲ سطر اطلاعاتی است. سطر اول نیز مربوط به اسامی ستونها است که شامل مقادیر متنی، عددی و تاریخی است.
تصویر ۱ را در نظر بگیرید که یک کاربرگ اکسل را با مقادیر مختلف شامل مقادیر عددی (ستون مبلغ گردش حساب)، متنی (ستونهای نوع حساب و نام بانک) و تاریخی (تاریخ گردش حساب) نشان میدهد. توجه داشته باشید که زمانی فیلتر کردن موثر است که بعضی از این مقادیر تکراری باشند تا بتوانند با یکدیگر تشکیل یک گروه بدهند. به این ترتیب علاوه بر جستجو، میتوان دستهبندی و محاسبات دلخواه را روی نتایج فیلتر انجام داد. همانطور که مشخص است ستونهای مربوط به «نام بانک» شامل مقادیر «صادرات، ملی و تجارت» بوده و ستون «نوع حساب» نیز مقادیر «سپرده و جاری» را شامل میشود. همچنین ستون «تاریخ گردش حساب» نیز مقادیر مختلف تاریخی در سال ۲۰۰۲ را نشان داده و در انتها نیز «ستون مبلغ گردش حساب» مقادیر عددی در بازه ۱۲۰ تا ۹۰۰ را مشخص کرده است. این فایل اطلاعاتی را مبنای محاسبات و ایجاد فیلتر در اکسل قرار خواهیم داد.
نکته: توجه داشته باشید که ناحیه اطلاعاتی برای فیلتر باید شامل سطر عنوان باشد. به این معنی که سطر اول ناحیه اطلاعات، اسامی ستونها را مشخص کرده و سطرهای بعدی را برای فیلتر به کار میگیرد. در ضمن اگر سلولهایی در ناحیه اطلاعاتی وجود داشته باشند که با یکدیگر ادغام شدهاند، فیلتر دچار خطا شده و اکسل قادر به اعمال فیلتر روی چنین ناحیهای نبوده یا استفاده از فیلتر را محدود خواهد کرد. همچنین پیوستگی در ناحیه اطلاعاتی نیز از ضروریات دیگر استفاده از فیلتر است. به این معنی که باید بین سطرها یا ستونهای کاربرگ اطلاعاتی فاصلهای به صورت سطر یا ستون خالی وجود نداشته باشد.
فیلتر کردن در اکسل با ابزار Filter
برای اجرای فیلتر در اکسل کافی است مراحل زیر را روی کاربرگ جاری، طی کنید.
- سلول فعال را در ناحیه اطلاعاتی قرار دهید. به این ترتیب اکسل متوجه میشود که جدول اطلاعاتی در کجای کاربرگ قرار گرفته و باید چه ناحیهای را فیلتر کند.
- از برگه Data گزینه Filter را در بخش Sort & Filter انتخاب کنید.
- علامت فیلتر روی اسامی ستونهای جدول اطلاعاتی (مطابق با تصویر ۲) ایجاد خواهد شد.
- ستونی که باید شرط مربوط به فیلتر روی آن اعمال شود را مشخص کرده و روی فلش (علامت فیلتر) ظاهر شده روی سلول، کلیک کنید.
- شرط مربوط به فیلتر را مطابق با گزینههای لیست ظاهر شده، انتخاب کرده و دکمه OK را بزنید.
نکته: نیازی نیست که ناحیه اطلاعاتی از ابتدای کاربرگ آغاز شود. اگر ناحیه اطلاعاتی قسمتی از کاربرگ باشد که از سطر دهم شروع شده، اکسل سطر اول این ناحیه را به عنوان اسامی ستونها در نظر گرفته و بقیه مقادیر را در فیلتر به کار میگیرد.
در صورتی که هنگام اجرای فیلتر از تغییر تنظیمات فیلتر گذاری منصرف شدید، از دکمه Cancel استفاده کنید.
فرض کنید میخواهیم همه سطرهای مربوط به گردش حساب بانک ملی را مشاهده کنیم. به این ترتیب فیلتر مربوط به نام بانک را فعال کرده و از لیست ظاهر شده مطابق با تصویر ۳، گزینه ملی را انتخاب و بقیه اسامی بانکها (تجارت و صادرات) را غیر فعال میکنیم.
با شرط اعمال شده، فقط سطرهایی از کاربرگ ظاهر میشوند که در قید مربوطه صدق کنند. با این کار، شماره سطرها، به رنگ آبی در آمده و شکل فیلتر روی ستون مربوطه نیز تغییر میکند. در انتهای کاربرگ و در بخش «نوار وضعیت» (Status Bar) در کنار عبارت Ready، نیز تعداد سطرهایی که با شرط مطابقت داشتهاند به شکل $$ 5\; of\; 12\; records\; found$$ مشخص شده است.
مطابق با تصویر ۴، فیلتر را روی کاربرگ برای نام بانک به کار برده و فقط سطرهای مربوط به بانک ملی را نمایش دادهایم. توجه داشته باشید که سطرهای دیگر از کاربرگ، حذف نشدهاند، بلکه فقط از دید شما مخفی هستند. احتمالا شمارههای سطرهای کاربرگ نظم خود را از دست دادهاند. البته مثلا ممکن است بعد از سطر شماره 2، سطر 4 را مشاهده کنید. این امر نشان میدهد که سطر 3 مطابق با شرط شما نبوده و در نتیجه مخفی (Hidden) شده است. مشخص است که این وضعیت برای سطرهای دیگر کاربرگ اطلاعاتی مانند سطر ۵ تا ۸، همچنین سطر ۱۱ و ۱۳ نیز رخ داده است.
برای خارج کردن فیلتر از حالت فعال، کافی است روی دکمه فیلتر در ستون مورد نظر، کلیک کرده و از فهرست ظاهر شده، گزینه Select all را فعال کرده و دکمه OK را بزنید. به این ترتیب همه سطرها، ظاهر شده و اکسل آماده است که شرط دیگری را برای فیلتر روی ستون جاری یا ستونهای دیگر اجرا کند. البته میدانید که برای لغو تغییراتی که روی فیلتر ایجاد کردهاید، باید روی دکمه Cancel کلیک نمایید. همچنین اگر میخواهید ناحیه اطلاعاتی شما کلا بدون فیلتر شده و همه مقادیر سطرهای کاربرگ نمایش داده شوند، کافی است از برگه Data دکمه مربوط به Filter را خاموش کنید. با این کار علامت فیلتر از ستونها برداشته میشود و تمامی سطرهای مخفی شده، مجدد ظاهر خواهند شد.
نکته: در لیست فیلتر، گزینههای مرتب سازی (Sort) نیز وجود دارد. در نتیجه امکان ایجاد ترتیب صعودی یا نزولی در لیستهای انتخابی نیز وجود دارد.
بحثی که در این قسمت در مورد نحوه ایجاد فیلتر صورت گرفت، شیوه عمومی برای ایجاد فیلتر است. ولی با توجه به نوع مقادیری که در کاربرگ و ستون فیلتر شده، قرار داد، میتوان از امکانات فیلتر در اکسل به بهترین نحوه استفاده کرد. این موضوعات در ادامه متن مورد بررسی قرار خواهند گرفت.
فیلتر در اکسل برای مقادیر عددی
این بار فرض کنید ستونی که قرار است فیلتر روی آن اعمال شود، شامل مقادیر عددی است یا در اصل میخواهیم اقدام به فیلتر کردن مقادیر عددی در اکسل بکنیم. بنابراین امکان استفاده از گزینه Number Filters در لیست فیلتر، نیز برای کاربران فراهم میشود. در این قسمت، از پیش، امکاناتی برای فیلتر کردن مقادیر عددی در نظر گرفته شده که سرعت کاربران را هنگام به کارگیری فیلتر در اکسل افزایش میدهد. برای مثال اگر بخواهید سطرهایی را در کاربرگ جدا کنید که برای ستون عددی گردش حساب، شامل مقادیر بیشتر از 500 هستند، کافی است از لیست Number Filter گزینه Greater than را به کار ببرید.
حتی میتوان شرطها را پیچیدهتر هم کرد. برای مثال فرض کنید بخواهیم مقادیری که در بازه ۱۰ تا ۲۰ قرار گرفتهاند را فیلتر کرده و سطرهای مرتبط را نمایش دهیم. تنظیمات مربوط به این کار را مطابق با تصویر 5، انجام میدهیم. با ثبت مقدار ۵۰۰ در کادر ظاهر شده و فشردن دکمه OK، این شرط برای نمایش سطرهای کاربرگ اعمال میشود.
با ثبت مقدار ۵۰۰ در کادر ظاهر شده و فشردن دکمه OK، این شرط برای نمایش سطرهای کاربرگ اعمال میشود. در تصویر ۶، نحوه تنظیم پارامترهای این شرط را مشاهده میکنید.
حتی میتوان شرط را باز هم پیچیدهتر کرد و برای مثال به دنبال سطرهایی بگردیم که مقدار گردش حساب در آنها، بین ۱۰۰ تا ۲۰۰ باشد. به این ترتیب با انتخاب گزینه Between از لیست فیلتر، در کادر اول مقدار ۱۰۰ و در کادر دوم نیز ۲۰۰ را وارد میکنیم. این تنظیمات را در تصویر ۷، میتوان مشاهده کرد.
به منظور درک بهتر عملیاتی که توسط گزینههای فیلتر عددی، اجرا میشود، جدولی را تشکیل داده و هر یک از گزینههای این لیست را معرفی کردهایم.
جدول ۱: گزینههای فیلتر عددی
گزینه | عملکرد |
Equals... | شرط برابری با یک مقدار عددی |
Doe Not Equal... | شرط نابرابری یا نامساوی با یک مقدار عددی |
Greater Than... | شرط بزرگتر از مقدار عددی معرفی شده |
Greater Thank Or Equal To... | شرط بزرگتر یا مساوی با مقدار عددی معرفی شده |
Less Than | شرط کوچکتر از مقدار عددی معرفی شده |
Less Than Or Equal To... | شرط کوچکتر یا مساوی با مقدار عددی تعریف شده |
Between... | شرط قرارگیری بین دو مقدار یا دو کران |
Top 10... | شرط برای نمایش چند مقدار اول (بزرگترین از لحاظ مقدار یا درصد) |
Above Average | شرط برای مقادیر بیش از میانگین |
Below Average | شرط برای مقادیر کمتر از میانگین |
Custom Filter... | شرطهای مربوط به فیلتر سفارشی |
استفاده از «فیلتر سفارشی» (Custom Filter) این امکان را به شما میدهد که دو نوع شرط برای یک ستون را به صورت ترکیب عطفی (AND) یا ترکیب فصلی (OR) در آورید.
ولی متاسفانه امکان ترکیب بیش از دو شرط در این قسمت وجود ندارد. برای ترکیب چندین شرط در فیلتر باید از «فیلتر پیشرفته» (Advanced Filter) کمک بگیرد که در ادامه این متن به آن اشاره خواهیم کرد. مثلا فرض کنید میخواهید سطرهایی از کاربرگ را مشاهده کنید که مقادیر گردش حساب در آنها بیش از ۵۰۰ یا کمتر از ۲۰۰ یا در بازه ۳۰۰ تا ۳۵۰ باشد. با استفاده از فیلتر عادی این کار امکانپذیر نبوده و نمیتوانیم خروجی مناسبی برای این کار با فیلتر ایجاد کنیم.
پس از اجرای فیلتر در برگه Data و بخش Sort & Filter، گزینههای دیگری نیز فعال میشوند. این گزینهها به صورت زیر هستند.
- گزینه Clear: برای آنکه فیلتر را از یک ستون برداشته و بدون آنکه گزینه فیلتر غیر فعال شود، همه سطرها را مشاهده کنیم، باید روی دکمه Clear از برگه Data کلیک کنیم.
- گزینه Reapply: اعمال مجدد فیلتر براساس شرطهای قبلی توسط این گزینه صورت میگیرد. فرض کنید مقداری از ناحیه فیلتر شده، تغییر کند و مطابق با شرط نباشد. در نتیجه گزینه Reapply، باعث میشود این شرطها دوباره اعمال شود. به این ترتیب ممکن است مقدار جدید از نتیجه فیلتر خارج شود.
- گزینه Advanced: به کارگیری ابزار فیلتر پیشرفته روی مجموعه دادهها.
نکته: توجه داشته باشید که فیلتر جدید فقط روی مقادیری که توسط فیلتر قبلی ظاهر شده، اثر میکند. در نتیجه فیلترها دارای اولویت بوده ولی با توجه به ترکیب عطفی («و» منطقی) شرطهای فیلترها در ستونهای مختلف، نتیجه در انتها یکسان است. زیرا میدانیم که عملگر «و» منطقی، دارای خاصیت جابجایی است.
فیلتر در اکسل برای مقادیر متنی
این بار براساس یک ستون متنی، فیلتر را اجرا میکنیم یا در اصل به فیلتر کردن مقادیر متنی در اکسل میپردازیم. البته توجه داشته باشید که فیلتر روی ستون جدید میتواند همزمان با فیلتر روی ستونهای قبلی نیز اجرا شود. به این معنی که اگر در قسمت قبل فیلتر را براساس ستون گردش حساب (با مقادیر عددی) اجرا کردهایم، این بار میتوانیم فیلتر متنی را به فیلتر قبلی اضافه کنیم و نتیجه یا حاصل اجرای فیلترها، سطرهایی خواهد بود که هم در شرط فیلتر عددی و هم در شرط فیلتر متنی صدق میکنند. ولی برای اینکه نتایج حاصل قابل درک باشند، اینجا فرض کردهایم که فیلتر قبلی را از روی مجموعه داده، برداشتهایم.
ستون متنی به صورت «نام بانک» را در نظر بگیرید. این ستون حاوی مقادیر متنی است که نام بانکهای ملی، تجارت و صادرات در آن قرار گرفته است. به این جهت، علاوه بر امکان انتخاب گزینههای متنی از لیست فیلتر این ستون، امکاناتی نیز که فقط برای متن قابل اجرا هستند، در گزینه Text Filters قرار گرفته است. به تصویر ۸ توجه کنید.
جدول زیر گزینههای مربوط به انتخاب کاربر در فیلتر متنی را مشخص و معرفی کرده است. توجه داشته باشید که این گزینهها فقط در صورت متنی بودن مقادیر ستون فیلتر شده، ظاهر خواهند شد.
جدول 2: گزینههای فیلتر متنی
گزینه | عملکرد |
Equals... | شرط برابری با یک مقدار متنی |
Does Not Equal... | شرط نابرابری با یک مقدار متنی |
Begin With... | شرط برای مطابقت یک متن با ابتدای عبارت درون سلولها |
Ends With... | شرط برای مطابقت یک متن با انتهای عبارت درون سلولها |
Contains... | شرط برای مطابقت یک متن با بخشی از عبارت درون سلولها |
Does Not Contain... | شرط برای عدم مطابقت یک متن با بخشی از عبارت درون سلولها |
Custom Filter... | شرطهای مربوط به فیلتر سفارشی |
برای مثال، فرض کنید در اسامی بانکها، بانک ملی و بانک ملت وجود داشته باشد و بخواهیم هر دو بانک را مشخص کنیم. از آنجایی که هر دو نام با عبارت «مل» شروع میشوند، استفاده از گزینه Begin With میتواند نتیجه دلخواه را برایمان فراهم کند.
فیلتر در اکسل برای مقادیر تاریخی
اگر در یک ستون از کاربرگ، مقادیر مرتبط با تاریخ قرار گرفته باشد، قابلیتهای متفاوتی برای انتخاب سطرها و اعمال شرطها بوجود میآید. به تصویر ۹، دقت کنید. انتخاب هر یک از این گزینهها، باعث میشود که در ستونهای تاریخی، فیلتر را براساس روز، ماه، فصل و سال تعیین کرد.
باز هم از جدولی برای نمایش گزینههای لیست Date Filters استفاده کرده و اقلام آن را معرفی خواهیم کرد.
جدول 3: گزینههای فیلتر تاریخ در اکسل
گزینه | عملکرد |
Equals | شرط مطابقت با یک تاریخ مشخص |
Before | شرط برای مطابقت تاریخهای یک ستون قبل از تاریخ مشخص شده |
After | شرط برای مطابقت تاریخهای یک ستون بعد از تاریخ مشخص شده |
Between | شرط برای مطابقت تاریخهای یک ستون قبل و بعد از تاریخ مشخص شده |
Tomorrow | شرط برای مطابقت تاریخ با تاریخ فردا (براساس تاریخ و ساعت سیستم) |
Today | شرط برای مطابقت تاریخ با تاریخ امروز (براساس تاریخ و ساعت سیستم) |
Yesterday | شرط برای مطابقت تاریخ با تاریخ دیروز (براساس تاریخ و ساعت سیستم) |
Next Week | شرط مقایسه تاریخ با هفته آینده |
This Week | شرط مقایسه تاریخ با هفته جاری |
Last Week | شرط مقایسه تاریخ با هفته گذشته |
Next Month | شرط مقایسه با تاریخ ماه بعد |
This Month | شرط مقایسه با تاریخ ماه جاری |
Last Month | شرط مقایسه با تاریخ ماه قبل |
Next Quarter | شرط مقایسه با تاریخ فصل بعدی |
This Quarter | شرط مقایسه با تاریخ فصل جاری |
Last Quarter | شرط مقایسه با تاریخ فصل گذشته |
Next Year | شرط مقایسه با تاریخ سال آینده |
This Year | شرط مقایسه با تاریخ سال جاری |
Last Year | شرط مقایسه با تاریخ سال گذشته |
Years to Date | شرط مربوط به سالهای گذشته از تاریخ مشخص شده |
All Dates in the Period | شرط مربوط به همه دورههای زمانی (فصلی و ماهانه) |
Custom Filter | شرطهای مربوط به فیلتر سفارشی |
نکته: زمانی که پنجره انتخاب تاریخ به مانند تصویر 10، ظاهر میشود، دکمهای برای ظاهر کردن تقویم و انتخاب زمان مورد نظر برای هر یک از محدودههای ذکر شده، دیده میشود. برای انتخاب روز جاری، کافی است روی دکمه Today کلیک کرده تا تاریخ جاری به عنوان یک پارامتر انتخاب شود. فلسهای راست (>) و چپ (<) نیز امکان تغییر در بازه ماه یا سال را فراهم میآورند.
فیلتر در اکسل براساس رنگ سلول
این بار فیلتر را براساس رنگ انجام خواهیم داد یا در اصل اقدام به فیلتر کردن سلول ها در اکسل بر اساس رنگشان میکنیم. به تصویر 11 توجه کنید. همانطور که میبینید، انتخاب سلولهایی رنگی توسط فیلتر در اکسل امکانپذیر است. هر چند هنگام انتخاب رنگها، امکان انتخاب چند گزینهای وجود ندارد و کاربر فقط میتواند یک رنگ را برای فیلتر انتخاب کند. در نتیجه انتخاب همزمان وجود نداشته و نمیتوان چند گزینه را انتخاب کرد. به تصویر ۱۱ توجه فرمایید.
اگر رنگ آبی پر رنگ را انتخاب کنید، فقط سطرهایی از کاربرگ ظاهر خواهند شد که دارای این رنگ هستند.
نکته: استفاده از فیلتر رنگی بخصوص زمانی که از «قالببندی شرطی» (Conditional Formatting) استفاده کردهاید، قابلیت ویژهای به کاربرگتان میبخشد. به این ترتیب شرطهای مختلف را براساس قالببندی شرطی اعمال کرده و سطرهایی که با آن قید و شرطها مطابقت دارند را به رنگ مشخصی در میآورید. به این ترتیب شرطهایی که حتی ممکن است با فیلتر نیز قابل اعمال نباشند، اجرا شده و در انتها نیز با فیلتر کردن سلولهای رنگی، سطرهای مورد نظرتان را نمایش میدهید.
فیلتر پیشرفته در اکسل
کاربرگ اولیه که در تصویر ۱ ارائه شد، را در نظر بگیرید. میخواهیم بعضی از سطرهای آن را از بقیه سطرها، جدا یا فیلتر کنیم. این بار این کار را به کمک «فیلتر پیشرفته» (Advance Filter) انجام خواهیم داد. یکی از مواردی که فیلتر پیشرفته را نسبت به فیلتر عادی، متمایز میکند، ایجاد شرطهای پیچیده و مختلف برای نمایش سطرهای دلخواه است. همچنین در فیلتر پیشرفته این امکان وجود دارد که فقط بعضی از ستونهای کاربرگ اطلاعاتی را به عنوان نتیجه فیلتر نمایش داد. به این معنی که میتوان نتیجه فیلتر را در قسمت دیگری از کاربرگ قرار داده و فقط بعضی از مقادیر ستونها را به عنوان حاصل فیلتر در نظر گرفت.
از طرفی میتوانیم شرط یا ترکیب شرطها را برای فیلتر پیشرفته، درون سلولها مشخص کنیم. این مزیت کاربران را در تنظیم و تغییر سریع شرطها کمک میکند. همچنین فیلتر پیشرفته محدودیت ایجاد شرطهای پیچیده برای فیلتر ساده را نداشته و کاربر قادر است شرایط مختلفی را برای نمایش سطرها، اعمال کند.
به منظور دسترسی به فیلتر پیشرفته، کافی است از برگه Data گزینه Advance Filter را انتخاب کنید. البته شرط انتخاب یک سلول در ناحیه اطلاعاتی نیز نباید فراموش شود. به این ترتیب پنجرهای به مانند تصویر 12، ظاهر شده که پارامترهای لازم برای فیلتر پیشرفته را از کاربر دریافت میکند.
این قسمتها را در جدول زیر معرفی کردهایم.
گزینه | عملکرد |
Filter the list, in-place | نمایش نتیجه فیلتر روی جدول اطلاعاتی |
Copy to another location | نمایش نتیجه فیلتر در محل دیگر از کاربرگ یا کاربرگها |
List range | ناحیه مربوط به فیلتر |
Criteria range | ناحیه مربوط به شرط یا شرطهای فیلتر |
Copy to | تعیین ناحیه مربوط به نمایش نتیجه فیلتر در قسمت دیگری از کاربرگ (در صورت انتخاب کرد گزینه Copy to another location) |
Unique records only | نمایش سطرهای یکتا برای نتیجه فیلتر پیشرفته |
قبل از هر چیز باید نحوه به کارگیری سلولهای کاربرگ برای تعیین شرط در فیلتر پیشرفته را یادآوری کنیم. ناحیه مربوط به شرط فیلتر (Criteria range)، باید شامل اسامی ستونها و مقادیری باشد که برای فیلتر کردن آن ستونها لازم است. اگر میخواهید هر یک از شرطها را با شرطهای بعدی به صورت عطفی (ترکیب گزارههای منطقی) یا به صورت «و» ترکیب کنید، باید مقادیر آنها را در سلولهای مجاور هم و در یک سطر بنویسد. ولی اگر لازم است ترکیب به صورت فصلی و به صورت «یا» باشد، مقدار شرطها باید در زیر هم نوشته شوند. برای روشن شدن موضوع به بیان چند مثال میپردازیم.
مثال ۱:
فرض کنید طبق کاربرگ معرفی شده در تصویر ۱، بخواهیم فقط سطرهای مربوط به بانک ملی را مشاهده کنیم. آنگاه باید شرطها را (مثلا در سلولهای F16 و F17) مطابق با تصویر ۱۳، مشخص کنیم. واضح است در سلول F16، نام ستون مربوط به فیلتر و در سلول F17، مقدار شرط برای آن ستون را مشخص کردهایم.
مثال ۲:
این بار میخواهیم شرط را به صورتی با شرط مربوط به «مبلغ گردش حساب» ترکیب کنیم. فرض کنید هدف سطرهایی باشد که مربوط به بانک ملی و گردش بیش از ۲۰۰ است. از آنجایی که دو شرط «نام بانک = ملی» و «مبلغ گردش حساب > ۲۰۰» است به صورت عطفی یا ترکیب «و» به کار رفتهاند، شکل ناحیه شرط در فیلتر پیشرفته به شکل تصویر ۱۴ در خواهد آمد.
مثال ۳:
اگر بخواهیم به ترکیب حاصل از شرطهای قبلی، گزینه مربوط به مبلغ گردش حساب کمتر یا مساوی با ۵۰۰ را هم اضافه کنیم، به مانند تصویر ۱۵ عمل میکنیم. واضح است که قرارگیری مجدد مبلغ گردش حساب در شرایط فیلتر اجباری است.
مثال ۴:
در نظر بگیرید که پرسشی که از کاربرگ اطلاعاتی داریم براساس حسابهای جاری بانک ملی و صادرات است. در نتیجه شرطها به صورت «نوع حساب = جاری و نام بانک ملی» یا «نوع حساب = جاری و نام بانک صادارت» است. برای این که اکسل این موضوع را هنگام ترکیب گزارههای شرطی متوجه شود مطابق با تصویر ۱۶ عمل خواهیم کرد.
نکته: البته میدانیم که با توجه به ثابت بودن بخش اول شرط در هر دو وضعیت میتوان این قید را به صورت «نوع حساب = جاری» و «نام بانک = ملی یا صادرات» در نظر گرفت.
مثال ۵:
این بار به پرسشی میپردازیم که به قیدهای مطرح شده در مثال ۴، قید یا شرط «مبلغ گردش بیش از ۵۰۰» را هم اضافه کند. به این ترتیب منظور از اجرای فیلتر، نمایش سطرهایی است که یا مربوط به حساب جاری بانک ملی یا صادرات هستند یا اینکه وضعیت گردش حساب آنها بیش از ۵۰۰ است. به این ترتیب شکل ترکیب گزارهها باید به صورت («نام بانک = ملی و نوع حساب = جاری» یا «نام بانک = صادرات» و «نوع حساب» = جاری») یا («مبلغ گردش حساب» کمتر یا مساوی با ۵۰۰) باشد. این ترکیب از گزارههای منطقی را مطابق با تصویر ۱۷، در کاربرگ برای فیلتر پیشرفته ثبت خواهیم کرد.
از آنجایی که میخواهیم نتیجه ترکیب نوع حساب و نام بانک، به صورت فصلی (یا منطقی) با مبلغ گردش حساب، داشته باشیم، سطر مربوط به مبلغ گردش حساب را در زیر شرطهای دیگر قرار دادهایم. به یاد دارید که اگر شرطها در یک سطر قرار گیرند، ترکیب عطفی است ولی در زیر یکدیگر، ترکیب فصلی ساخته خواهد شد.
حال به بررسی فیلتر پیشرفته در اکسل خواهیم پرداخت و نتیجه اجرای مثال ۵ را در کاربرگ مربوط به تصویر ۱، بدست خواهیم آورد. ابتدا فیلتر را از روی مجموعه داده (ناحیه A1 تا D13) بر میداریم. کافی است دکمه فیلتر را در برگه Data در بخش Sort & Filter خاموش کنید. حال پس از اجرای دستور Advance، پنجره تنظیمات فیلتر پیشرفته ظاهر خواهد شد. به منظور اجرای فیلتر مطابق با مثال ۵، گزینههای این پنجره مطابق با شکل سمت راست تصویر ۱۸ تنظیم شده است. همچنین ناحیه شرط نیز در پایین تصویر قرار گرفته است. نتیجه اجرای فیلتر را هم در سمت چپ تصویر 18، مشاهده میکنید.
از طرفی اگر بخواهیم نتیجه اجرای فیلتر پیشرفته را در قسمت دیگری از کاربرگ کپی کنیم، پس از فعال کردن گزینه Copy to another location، ناحیهای از کاربرگ را در قسمت Copy to انتخاب میکنیم که قرار است، نتیجه در آنجا ظاهر شود. اگر ناحیه انتخاب شده ستونهایی برابر با تعداد ستونهای مجموعه اطلاعاتی اصلی داشته باشد، به راحتی نتیجه با همان اسامی ستونهای ناحیه اطلاعاتی ساخته خواهد شد. این کار را در تصویر ۱۹ انجام دادهایم. توجه کنید که ناحیه اطلاعاتی با ناحیه فیلتر، مقادیر متفاوتی دارند.
همانطور که میبینید با انتخاب گزینه Copy، میتوان نتایج فیلتر و سطرهای مطابق با شرط را به ناحیهای دیگر از کاربرگ منتقل کرد. این امکان، مزیت ویژهای است که در فیلتر عادی وجود ندارد. البته برای ایجاد چنین لیستی، باید شرایطی را اعمال کنیم. همانطور که مشخص است در ناحیه شماره ۱، ناحیه اطلاعاتی قرار دارد که در بخش List range قرار گرفته، ناحیه کپی کردن نتیجه نیز که مربوط به ناحیه شماره ۲ است، در پارامتر Copy to ظاهر شده و در نهایت نیز ناحیه شرط با شماره ۳ در پارامتر Criteria range دیده میشود.
نکته: توجه داشته باشید که نتیجه اجرای فیلتر مربوط به مثال ۵، سطرهایی از جدول اطلاعاتی را نشان میدهد که یا مربوط به بانک صادرات یا ملی هستند و یا اینکه مبلغ گردش حساب آنها کمتر از ۵۰۰ است. به همین جهت فقط یکی از سطرهای مربوط به بانک تجارت در بین پاسخها قرار گرفته است.
برای خارج کردن جدول اطلاعاتی از حالت فیلتر، کافی است دکمه Clear را از برگه Data در قسمت Sort & Filter کلیک کنید.
البته به این موضوع نیز توجه کنید که هنگام اجرای دستور کپی در فیلتر پیشرفته، محلی را برای نتیجه فیلتر انتخاب کنید که با سطرهای ناحیه اطلاعاتی، اشتراک نداشته باشد. زیرا در صورت مخفی شدن سطرهای ناحیه در کاربرگ توسط فیلترهای دیگر، سطرهای مربوط به نتیجه فیلتر نیز مخفی خواهند شد.
تابع فیلتر در اکسل ۳۶۵
علاوه بر ابزار فیلتر در اکسل تابعی نیز وجود دارد که فقط در نسخه اکسل ۳۶۵ وجود دارد. این نسخه از اکسل فقط از طریق اینترنت و با استفاده از مرورگرهای آن قابل دسترسی بوده و برای دستیابی به آن احتیاج به یک حساب کاربری (به همراه پرداخت هزینه) در گروه office دارید.
قالب دستوری برای به کارگیری تابع FILTER به صورت زیر است.
=FILTER(array,include,[if_empty])
همانطور که مشخص است این تابع دارای دو پارامتر اصلی و یک پارامتر اختیاری است. در ادامه به معرفی هر یک از این پارامترها خواهیم پرداخت.
- پارامتر اول (array): این پارامتر به ناحیه یا برداری از کاربرگ اشاره دارد که باید فیلتر شود.
- پارامتر دوم (include): در این پارامتر، شرط یا همان قید برای ایجاد فیلتر قرار میگیرد. واضح است که مقادیر این ناحیه باید به صورت مقادیر منطقی با دو نوع مقدار TRUE یا FALSE برای سطرهای پارامتر اول باشند. در صورتی که نتیجه این شرطها برای سطری به صورت TRUE باشد، در نتیجه فیلتر ظاهر شده و در غیر اینصورت این سطر از کاربرگ، در نتیجه فرمول دیده نمیشود.
- پارامتر سوم (if_empty): اگر نتیجه اجرای شرطهای بیان شده در پارامتر دوم، هیچ حاصلی روی پارامتر اول نداشته باشد، پارامتر if_empty میتواند به کار رود و در خروجی تابع فیلتر، مقدار تعیین شده را نشان دهد.
به منظور روشن شدن موضوع براساس کاربرگ اطلاعاتی مربوط به تصویر20، تابع FILTER را در مثالهایی به کار خواهیم برد. البته توجه داشته باشید که این کاربرگ و تابع FILTER باید در محیط office 365 نوشته شده باشند.
در تصویر ۲۰، نواحی مختلف تابع FILTER با شماره مشخص شدهاند. ناحیه شماره ۱، همان ناحیه اطلاعاتی است که باید فیلتر شود. ناحیه شماره ۲، ستونی است که مبنای فیلتر قرار گرفته. از طرفی شرط برای این ستون نیز در بخش شماره ۳ مشخص شده. این تابع نیز در ناحیه شماره ۴ نوشته شده است که نتیجه فیلتر را نشان میدهد. واضح است حاصل از اجرای تابع فیلتر در این مجموعه داده، نمایش سطرهایی است که در متغیر محصول (Product) دارای مقدار «سیب» (Apple) هستند.
نکته: نتیجه اجرای تابع فیلتر، به صورت یک بردار ظاهر میشود. در نتیجه باید به اندازه ناحیه اطلاعاتی در محل نوشتن تابع، فضای خالی (شامل سطر و ستون) داشته باشید.
اگر لازم است در تابع فیلتر، شرطهای بیشتری را با یکدیگر ترکیب کنید، میتوانید آنها را با علامت «*» برای ترکیب عطفی (و منطقی) و «+» برای ترکیب فصلی (یا منطقی) به کار ببرید. به مثال زیر توجه کنید که در آن قرار است «محصول» (Product) «سیب» (Apple) مربوط به «ناحیه» (Region) «شرقی» (East) باشد.
در ضمن پارامتر سوم نیز به صورت "" ظاهر شده که نشان میدهد، در صورتی که تابع فیلتر نتیجهای نداشته باشد، مقدار خالی در خروجی تابع ظاهر شود.
همچنین اگر میخواهید نتیجه اجرای فیلتر سطرهایی باشد که یا مربوط به محصول سیب «یا» مربوط به نواحی شرقی باشد، تابع را به صورت زیر مینویسیم. واضح است که تعداد سطرهایی که مطابق این شرط هستند بیشتر از حالتی است که شرط به صورت عطفی نوشته شده بود.
نکته: عملگر یا ترکیب عطفی در انگلیسی به صورت (AND) و عملگر یا ترکیب فصلی نیز به شکل (OR) استفاده میشود. در نتیجه * نشانگر عمل AND و + نیز بیانگر OR است.
خلاصه و جمعبندی
همانطور که در این نوشتار خواندید، فیلتر در اکسل به دو شیوه قابل استفاده است. روش اول تکیه به ابزارهای اکسل داشته و با استفاده از فهرست دستورات، میتوان سطرهای دلخواه را از کاربرگ جداسازی کرد. در روش دوم، با استفاده از «فیلتر پیشرفته» (Advanced Filter) جداسازی به صورت شرطهایی خواهد بود که درون سلولها نوشته شده است. به این ترتیب با تغییر مقادیر سلولها و اعمال مجدد فیلتر، نتایج به سرعت تغییر خواهد کرد. از طرفی به کارگیری تابع Filter نیز در نسخه برخط (Online) اکسل ۳۶۵ نیز وجود دارد. پارامترهای این تابع بسیار شبیه به تنظیمات فیلتر پیشرفته هستند و تقریبا نحوه عملکرد مشابهی نیز دارند.
با سلام و تشکر بابت ارایه مطلب خوبتان
سوالی داشتم
آیا این امکان وجود دارد که در ردیف های اکسل هم از فیلتر است نماییم
اگر نه ، چه راهکاری را میتوان به کار برد
سپاسگذارم
یک فایل دارم که برای هر مدل دستگاه، تعدادی قیمت که متفاوت هستند ثبت شده
چطوری می تونم خروجی برای هر مدل دستگاه بگیرم که فقط قیمت های آخر و ماقبل آخر نشون بده ؟؟
سلام
فکر میکنم باید از پاور کوئری استفاده کنی
سلام و خداقوت برای جمع بستن اعداد فیلتر شده مثلا حساب بانک تجارت چکار باید کرد ؟مبالغ بدهکار و بستانکار حساب بانکی که در نهایت نیازمند مانده حساب هم خواهد بود
در واقع در آخر فیلتر شدن یک ردیف جمع و مانده را بطور اتوماتیک بدهد . ببخشید بزرگوار