روش انجام فیلتر در اکسل و همه نکات کاربردی

فیلتر (Filter) در اکسل ساده ترین راه برای نمایش و استخراج اطلاعات مرتبط به هم از یک مجموعه داده بزرگ است، به گونه ای که بقیه داده ها پنهان می شوند. اکسل می تواند ردیف ها را بر اساس قالب داده ها، مقدار یا شرایط تعیین شده فیلتر کند. علاوه بر این با استفاده از فیلتر می توانیم داده های فیلتر شده را بدون تغییر در کل لیست، کپی، ویرایش و یا چاپ کنیم.

بسیاری از افراد تصور می کنند، عملیات های فیلتر و مرتب سازی یکسان هستند اما تفاوت های کلیدی بین آنها وجود دارد:

• وقتی داده ها را فیلتر کنید، تنها داده های مورد نظرتان نمایش داده می شود و بقیه داده ها به طور موقت پنهان می شوند. به عنوان مثال، نتیجه فیلتر لیست (۴,۲,۸, a, b,1,9) برای نمایش “حروف الفبا” برابر است با لیست (a,b).
• اما وقتی داده ها را مرتب کنید، داده ها فقط مرتب می شوند. به عنوان مثال، نتیجه مرتب سازی صعودی لیست (۸, ۲, ۳) برابر است با لیست (۲, ۳, ۸).

در این مقاله، فیلتر داده ها در اکسل را به طور کامل بررسی خواهیم کرد:

• شیوه فیلتر داده ها در اکسل
• فیلتر کردن چند ستون
• کلیدهای میانبر فیلتر
• انواع فیلترها
• حذف فیلتر
• فیلترهای پیچیده
• دلایل کار نکردن فیلتر

فیلتر کردن داده ها اکسل

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

در مثال زیر، هدرهای Month، Days و Season انتخاب شده اند که به ترتیب ماه، روزها و فصل را نشان می دهند.

۱- روی یک سلول دلخواه در محدوده مورد نظر کلیک کنید.

۲- از تب Data در گروه Sort & Filter روی گزینه Filter کلیک کنید.

با فعال کردن این ویژگی، یک پیکان (رو به پایین) در سمت راست ستون ها نمایش داده می شود. با کلیک روی این پیکان، تمام مقادیر خاص ستون را مشاهده خواهید کرد. برای غیرفعال کردن این ویژگی و حذف پیکان ها، کافیست یکبار دیگر روی گزینه Filter در تب Data کلیک کنید.

حالا باید یک مرحله رو به جلو بروید و فیلتر مورد نظر خود را اعمال کنید.

نحوه فیلتر کردن

فرض کنید می خواهید ردیف های حاوی مقدار Vienna در ستون City را در مثال زیر فیلتر کنید.

روی پیکان ستون “City” کلیک کنید. لیستی از مقادیر خاص در ستون نمایش داده می شود. به طور پیشفرض همه مقادیر انتخاب شده اند. با کلیک روی گزینه Select All، تیک آن را بردارید تا تیک همه مقادیر حذف شود.

حالا کادر گزینه مورد نظر برای فیلتر یعنی “Vienna” را علامت بزنید، سپس رویOK کلیک کنید.

در نهایت فقط ردیف های حاوی مقدار Vienna نمایش داده می شود.

همانطور که از شماره های ردیف مشخص است، ردیف بقیه داده ها حذف نشده بلکه فقط پنهان شده اند. ردیف ۱ قرار دارد و بعد از آن ردیف های ۹ و ۱۰ آمده است و شماره ردیف بعدی ۱۶ است که نشان دهنده مخفی شدن سایر داده هایی است که در فیلتر قرار نگرفته اند.

همه منو/تب های اکسل در نوار ریبون

فیلتر کردن چند ستون

در اکسل می توانید به طور همزمان فیلتر را برای بیش از یک ستون اجرا کنید. برای انجام این کار باید برای ستون بعدی نیز مراحل مثال قبل را تکرار کنید.

در مثال زیر، دو ستون به طور همزمان فیلتر شده اند. London در ستون City و July در ستون Month:

نتیجه این دو فیلتر ردیف های حاوی London در ستون City و July در ستون Month را نمایش می دهند.

بله، فیلتر کردن در اکسل خیلی آسونه! و برای فیلتر کردن داده ها در هر جدولی می توانید این مراحل را دنبال کنید.

کلیدهای میانبر فیلتر

با یک میانبر ساده روی صفحه کلید می توانید فیلترها را با سرعت بیشتری روی داده ها اعمال کنید.

• SHIFT + CTRL + L کلید های میانبر برای فعال کردن فیلتر است.
• کلیدهای میانبر دیگر ALT + D + F + F می باشند.

انواع فیلترها

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

• فیلترهای متنی
• فیلترهای عددی
• فیلترهای تاریخ
• فیلتر براساس رنگ

فیلترهای متنی

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

فرض کنید می خواهید همه داده ها به جز Berlin را فیلتر کنید. باید از گزینه Don’t Contain استفاده کنید و رشته “Berlin” را وارد کنید: (البته در این مثال می توانید از گزینه Don’t Equal نیز استفاده کنید.)

در نتیجه، تمام ردیف های مرتبط با Berlin پنهان می شوند:

نکته: فیلترهای متنی زمانی در دسترس هستند که داده های ستون به صورت متن قالب بندی شده باشند. در سایر موارد به جای Text Filter، گزینه های دیگری مانند فیلترهای عددی یا تاریخ (Number / Date Filters) را نمایش می دهد.

  نحوه تایپ شدن اتوماتیک اعداد

فیلترهای عددی

برای ستون های حاوی داده های عددی، اکسل گزینه ویژه تری ارائه می دهد؛ فیلترهای عددی یا Number filters. این گزینه بسیار شبیه به فیلترهای متنی است اما گزینه های بیشتری دارد:

در مثال زیر مقادیر بین ۵۰۰۰ و ۱۰۰۰۰ فیلتر می شوند:

فیلترهای تاریخ

فیلترهای تاریخ یا Date Filters ابزاری مشابه با فیلترهای متنی و عددی است اما در ستون های شامل اطلاعات تاریخ در دسترس می باشد. فیلترهای تاریخ تنوع بیشتری در گزینه های فیلتر دارد. تعدادی از این فیلتر ها در جدول زیر آمده است:

فیلتر براساس رنگ

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

با استفاده از گزینه Filter by Color می توانید این کار را انجام دهید:

حذف فیلتر ستون در اکسل

می توانید فیلتر داده ها را در ستون های مختلف حذف کنید.

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

نحوه حذف فیلتر ستون داده ها به صورت زیر است:

۱- روی پیکان هدر ستونی کلیک کنید که می خواهید فیلتر آن را حذف کنید.

۲- گزینه “[نام ستون]Clear filter from ” را انتخاب کنید.

۳- این گزینه فیلتر را پاک می کند و داده های پنهان شده را نشان می دهد.

اگر روی بیش از یک ستون فیلتر اعمال کرده اید و می خواهید همه آنها را حذف کنید، باید از دستور Clear در تب Data استفاده کنید:

سریعترین روش جمع در اکسل (جمع سطر، ستون یا یک ناحیه)

فیلترهای پیچیده تر

در اکسل می توانید فیلترهای پیچیده تری با با استفاده از عملگرهای AND و OR بسازید. به عنوان مثال، ممکنه دو شرط داشته باشید.

در مثال زیر می خواهیم ردیف های حاوی عبارت “pinot noir” را در ستونی به نام Description فیلتر کنیم. مراحل زیر را دنبال کنید:

۱- فیلتر را براساس مراحل گفته شده فعال کنید.

۲- روی پیکان ستون مورد نظر کلیک کنید.

۳- از لیست کشویی، Text Filters را انتخاب کنید. (نوع داده های ستون از نوع متن است.)

۴- از منوی بعدی، Custom Filter… را انتخاب کنید. البته در اینجا می توانید به جای Custom Filter… به طور مستقیم فیلتر اول خود را انتخاب کنید.

۵- پنجره Custom AutoFilter باز می شود.

۶- روی منوی کشویی اول کلیک کرده و فیلتر اول خود را انتخاب کنید.

۷- در کادر سمت راست، یک عبارت تایپ کنید یا یکی از موارد لیست را انتخاب کنید. در این مثال، گزینه “does not contain” را انتخاب کنید.

۸- در کادر پایین باید شرط دوم را اضافه کنید.

• اگر هر دو شرط باید با هم برقرار باشند، از دکمه رادیویی “And” استفاده کنید.
• اگر برقراری یکی از شرایط کافی باشد، از دکمه رادیویی “Or” استفاده کنید.

۹- در نهایت روی دکمه OK کلیک کنید.

آموزش ضرب در اکسل (با مثال و به زبان ساده)

دلایل کار نکردن فیلتر در اکسل

آیا فیلتر به درستی یا طبق انتظار شما کار نمی کند؟ اکسل انتظار دارد که شما قبل از استفاده از فیلتر، داده های خود را برای مطابقت با برخی از استانداردهای اساسی آماده کرده باشید. در اینجا چند دلیل رایج کار نکردن فیلتر در اکسل را آورده ایم.

• عدم انتخاب صحیح داده ها

بررسی کنید که آیا همه داده ها را انتخاب کرده باشید.

اگر محدوده مورد نظر خود را انتخاب نکنید، اکسل خودش محدوده فیلتر را تعیین می کند و احتمالا فقط تا اولین سطر یا ستون خالی را انتخاب خواهد کرد. در نتبجه بهتر است به صورت دستی محدوده را انتخاب کنید تا مطمئن شوید که همه داده ها در فیلتر قرار می گیرند.

• مشکل در هدر ستون ها

بررسی کنید که فقط یک ردیف برای هدر یا عنوان ستون ها در داده ها وجود داشته باشد.

اگر برای نوشتن متن هدر به چند خط نیاز دارید، کافیست اولین خط در سلول را تایپ کرده و سپس ALT + ENTER را فشار دهید و ادامه آن را در خط جدید در همان سلول تایپ شود.

قالب بندی سلول با استفاده از Wrap Text نیز موثر است.

• سلول های ادغام شده

سلول های ادغام شده دلیل دیگری است که ممکنه فیلتر کار نکند.

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

اگر سلول های عنوان ستون ها ادغام شده باشد، ممکنه هنگام فیلتر نتوانید موارد یکی از ستون های ادغام شده را انتخاب کنید.

اگر ردیف ها ادغام شده باشند، فیلتر همه ردیف های ادغام شده را انتخاب نخواهد کرد.

• خطاها

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

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

خطاها همیشه در پایین لیست مقادیر فیلتر نمایش داده می شوند.

خطا را انتخاب کرده و روی OK کلیک کنید. پس از یافتن خطا، آن را برطرف یا حذف کنید و سپس فیلتر را پاک کنید و دوباره از اول شروع کنید.

• ردیف های پنهان شده

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

برای نمایش ردیف های پنهان شده، ابتدا ناحیه حاوی ردیف های را انتخاب کنید. این یعنی که باید ردیف بالا و پایین داده های مخفی را انتخاب کنید.

سپس در قسمت هدر ردیف راست کلیک کنید (بالای شماره های ردیف) و گزینه Unhide را انتخاب کنید یا اینکه از تب Home روی گزینه Format کلیک کرده سپس از لیست آن گزینه Hide & Unhide و در نهایت Unhide Rows را انتخاب کنید.

دانلود فیلم های آموزش صفر تا صد اکسل +جزوه

ویدیوی آموزش فیلتر در اکسل (اگر توضیحات بالا کافی نبود)