فیلتر در اکسل (Filter)

با جدول ها در اکسل و ویژگی های آنها در مقالات قبلی آشنا شدیم، مجدداً یادآوری میگردد که برای تحلیل بر روی داده ها در اکسل بهتر است ابتدا داده ها را به صورت جدول در بیاوریم و سپس بر روی آنها عملیات انجام دهیم. در مقاله معرفی جدول در اکسل بیان شد که یکی از مزایای تبدیل داده ها به جدول این است که بر روی هر فیلد (Field یا ستون از داده ها) ابزار فیلتر (Filter) به صورت خودکار قرار داده میشود، برای داده هایی که جدول نیستند کاربران میتوانند ابزار فیلتر را به صورت دستی وارد نمایند. در این مقاله میخواهیم به تفصیل در مورد ابزار فیلتر در اکسل صحبت کنیم. به طور خلاصه، ابزار Filter در اکسل برای فیلتر کردن داده های یک جدول یا مجموعه ای از داده ها کاربرد دارد.

برای استفاده از ابزار فیلتر در اکسل لزومی ندارد که حتماً داده های ما به صورت جدول (Table) باشند، اما توصیه میگردد برای داشتن فایل اکسلی مرتب تر و حرفه تر حتماً از جداول برای تحلیل داده ها استفاده نمایید. پیش از مطالعه این مطلب مقاله “جدول در اکسل” را مطالعه نمایید.

فیلتر در اکسل

معرفی ابزار فیلتر در اکسل (Filter)

در صورت تبدیل داده ها به جدول، به صورت خودکار ابزار فیلتر در بالای هر ستون قرار میگیرد، برای غیر فعال کردن ابزار فیلتر قرار گرفته در بالای جداول و همچنین برای قرار دادن ابزار فیلتر در بالای ستون داده های غیر جدول میتوان از تب Data و در گروه Sort & Filter بر روی آیکون فیلتر کلیک کرد و یا در تب Home در گروه Editing و در منوی Sort & Filter ابزار Filter را انتخاب نمود (همچنین، فشردن کلید Ctrl + Shift + L ابزار فیلتر در اکسل را فعال/ غیر فعال میکند)، شکل های زیر مسیر استفاده از ابزار فیلتر در اکسل را نشان میدهند:

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

پس از فعال کردن ابزار فیلتر در اکسل دکمه Drop Down فیلتر در بالای ستون ها قرار داده میشود. نمودار شاخه ای زیر مبحث فیلتر در اکسل به صورت خلاصه بیان میکند:

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

فیلتر در اکسل

استفاده از ابزار فیلتر در اکسل

برای درک بهتر کاربرد ابزار فیلتر در اکسل، ادامه آموزش را با یک مثال دنبال میکنیم. داده های جدول زیر را در نظر بگیرید، ابزار فیلتر برای ستون های این جدول فعال شده است. جدول زیر لیست اقلام فروخته شده در یک فروشگاه میباشد که برای هر کالا (Item Name)، تعداد فروخته شده (Count)، قیمت واحد (Unit Price)، قیمت کل (Total Price)، در صد تخفیف (Off %) و قیمت نهایی بعد از اعمال تخفیف (Final) در جدول درج شده است. برخی سطر ها نیز با رنگی متفاوت مشخص شده اند.

برای تمرین بیشتر مبحث فیلتر در اکسل، با کلیک بر روی این خط،  فایل نمونه را دانلود کنید.

جدول و فیلتر در اکسل

برای هر ستون یک Drop Down فیلتر قرار داده شده است، ستون هایی (Field) که دارای داده های عددی میباشند (یا بیشتر داده های آن عددی میباشد) منوی Number Filters در آنها قرار دارد و برای ستون هایی که داده های متنی دارند منوی Text Filters قرار گرفته است، از آنجایی که سطرهای جدول در مثال بالا دارای رنگ های متفاوت میباشند برای فیلتر کردن سطرها بر اساس رنگ آنها منوی Filter Color در Drop Down فیلتر قرار گرفته است.

ساختار Drop Down فیلتر در اکسل برای ستونی که بیشتر داده های آن متنی هستند (ستون Item Name) به صورت زیر است:

مثال فیلتر در اکسل

برای تمرین بیشتر مبحث فیلتر در اکسل، با کلیک بر روی این خط،  فایل نمونه را دانلود کنید.

در منوی Drop Down باز شده، برای فیلتر کردن جدول بر اساس رنگ سطر ها کافیست بر روی گزینه Filter by Color کلیک نمایید و رنگ مورد نظر برای نشان داده شدن را انتخاب کنید. علاوه بر این، همانطور که در شکل نشان داده شده است، تمام آیتم های موجود در فیلد مورد بررسی (فیلد Item Name) در قسمت پایین منوی Drop Down لیست شده اند، با استفاده از باکس Search در بالای لیست مزبور میتوانید یک آیتم خاص را جستجو نمایید، در کنار هر کدام از آیتم ها یک چک باکس (Check Box) قرار گرفته است، برای فیلتر کردن داده ای خاص میتوان به صورت دستی تیک چک باکس را حذف کرد، در اینصورت آیتم مورد نظر فیلتر شده و دیگر نشان داده نمیشود.

برای فیلتر کردن داده های متنی فیلد انتخاب شده، گزینه Text Filter را انتخاب میکنیم، منوی Text Filter در فیلتر در اکسل دارای گزینه های زیر است:

  • گزینه Equals: تمام آیتم ها بجز آیتم هایی که برابر با مقدار (رشته متنی) مورد نظر ما میباشد را فیلتر میکند.
  • گزینه Does Not Equal: تمام آیتم هایی که با مقدار (رشته متنی) مورد نظر ما برابر میباشند را فیلتر میکند.
  • گزینه Begins With: تمام آیتم ها بجز آیتم هایی که با رشته متنی مورد نظر ما شروع میشوند را فیلتر میکند.
  • گزینه Ends With: تمام آیتم ها بجز آیتم هایی که با رشته متنی مورد نظر ما تمام میشوند را فیلتر میکند.
  • گزینه Contains: تمام آیتم ها بجز آیتم هایی که رشته متنی مورد نظر ما را شامل میشوند فیلتر میکند.
  • گزینه Does Not Contain: تمام آیتم ها بجز آیتم هایی که رشته متنی مورد نظر ما را شامل نمیشوند فیلتر میکند.
  • گزینه Custom Filter: در این حالت تمام شرط های بالا را میتوان به صورت ترکیبی AND و OR به کار برد.

در یادگیری مباحثی مانند فیلتر در اکسل، جستجو و جایگزینی، Advanced Filter، استفاده از توابع متنی و آماری و بسیاری مباحث مهم دیگر، کاراکتر های ? و * و ~ نقش خیلی مهمی دارند، به کاراکترهای * و ? در اصطلاح  Wildcards Characters گفته میشود و توابعی که در آنها میتوان این کاراکتر ها را مورد استفاده قرار داد Wildcards Functions نامیده میشوند. در مقاله ای جداگانه این مبحث به تفصیل بحث خواهد شد.

فیلتر در اکسل

برای فیلد هایی که بیشتر داده های آنها عددی میباشد گزینه ی Number Filter فعال است، منوی Number Filter در فیلتر در اکسل دارای گزینه های زیر است:

  • گزینه Equals: تمام آیتم ها بجز آیتم هایی که برابر با مقدار مورد نظر ما میباشد را فیلتر میکند.
  • گزینه Does Not Equal: تمام آیتم هایی که با مقدار مورد نظر ما برابر میباشند را فیلتر میکند.
  • گزینه Greater Than: تمام آیتم ها بجز آیتم هایی که بزرگتر از مقدار مورد نظر ما باشند را فیلتر میکند.
  • گزینه Greater Than or Equal to: تمام آیتم ها بجز آیتم هایی که بزرگتر مساوی مقدار مورد نظر ما باشند را فیلتر میکند.
  • گزینه Less Than: تمام آیتم ها بجز آیتم هایی که کوچکتر از مقدار مورد نظر ما باشند را فیلتر میکند.
  • گزینه Less Than or Equal to: تمام آیتم ها بجز آیتم هایی که کوچکتر مساوی مقدار مورد نظر ما باشند را فیلتر میکند.
  • گزینه Between: تمام آیتم ها بجز آیتم هایی که بین دو مقدار مورد نظر ما باشند را فیلتر میکند. گزینه Not Between را میتوان در قسمت Custom ایجاد کرد.
  • گزینه Top 10: تمام آیتم ها بجز آیتم هایی که از نظر مقدار جز ۱۰ مورد بیشترین باشند را فیلتر میکند. (لازم به ذکر است، عدد ۱۰ قابل تغییر است و گزینه Bottom– برعکس Top – هم زیر مجموعه این گزینه قرار دارد، یعنی میتوان تمام آیتم ها بجز آیتم هایی که از نظر مقدار جز ۱۰ مورد کمترین باشند را نیز فیلتر کرد).
  • گزینه Above Average: تمام آیتم ها بجز آیتم هایی که مقدار آنها بیشتر از میانگین آیتم های فیلد باشند را فیلتر میکند.
  • گزینه Below Average: تمام آیتم ها بجز آیتم هایی که مقدار آنها کمتر از میانگین آیتم های فیلد باشند را فیلتر میکند.
  • گزینه Custom Filter: در این حالت تمام شرط های بالا را میتوان به صورت ترکیبی AND و OR به کار برد.

برای تمرین بیشتر مبحث فیلتر در اکسل، با کلیک بر روی این خط،  فایل نمونه را دانلود کنید.

 

اشتراک گذاری در شبکه های اجتماعی: share on facebook share on google plus share on linkedin

۳۱ نظر

  1. مرتضی

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

    1. حامد قدیمی

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

        1. حامد قدیمی

          سلام، میتوانید از طریق ایمیل یا تلگرام ارسال کنید، در قسمت “تماس با ما” ازلاعات مورد نیاز وجود دارد.

  2. امیر

    سلام من یه فاکتور فروش درسات کردم ولی وقتی نام هر مشتری رو برای چاپ فاکتورش فیلتر میکنم چندبار تو فاکتور زیر ردیف نام مشتری تکرار میشه که جالب نیست میشه یکاری کرد که هر اسمی رو که فیلتر میکنم یجا بنویسه تا یبار چاپ بشه؟

    1. حامد قدیمی

      سلام، در جدول معمولی خیر، اما در Pivot Table این کار راحت و بسیار حرفه ای تر قابل انجام است. لطفاً از Pivot Table استفاده کنید.

  3. عماد جوادی

    جناب آقای قدیمی. سلام و تشکر بابت زحماتی که در کمک به دیگران تحمل می کنید. یک سؤال:
    یک شیت را فیلتر کرده ام. می خواهم فیلتر را بردارم اما اشیاء فیلتر شده بازنگردند. به دلیلی امکان کپی و پیست هم نیست. چه راهی پیشنهاد می فرمایید؟ با تشکر

    1. حامد قدیمی

      سلام، متشکرم، شما میبایست از ابزار Advanced Filter استفاده کنید. لطفاً مقاله مربوطه را بخوانید.

  4. مهدی

    سلام
    ممنون از مطالب مفیدتون
    میشه لطفا به من بگید چگونه سطرها و ستون های فیلتر شده را بدون پاک شدن سایر سطرها پاک کنیم؟

  5. حمید

    با سلام خدمت استاد گرامی
    من الکسلی دارم که تعداد سطر از ۱۰۰۰ به بالا هستش . و فیلتر نمیتونه سرچ کنه مگر با ctrl+f
    لطفا راهنماییم کنید با تشکر

  6. عطا

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

  7. hashem

    سلام من می خوام یک جدول رو براساس ساعت فیلتر کنم مثلا میخوام براساس یک تاریخ و ساعت مشخص فیلتر کنم باید چیکار کنم؟
    لطفا راهنماییم کنید

    1. حامد قدیمی

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

  8. س. نیک

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

  9. س. نیک

    در یک فایل اکسل که بیش از ۳۰ شیت دارد چطور میتوانم یک داده را که شامل چهار ردیف است فیلتر کرده در صفحه اکسل جداگانه و یا شیت جداگانه کپی کنم؟

    1. حامد قدیمی

      سلام، از ابزار Advanced Filter میتوانید برای کپی و فیلتر کردن داده ها به صورت همزمان استفاده نمایید. اما نمیتوانید داده ها را در شضیت دیگر کپی کنید، به عبارتی با این ابزار میتوانید داده ها را در همان شیت فیلتر کرده و در محل دیگری در همان شیت کپی نمایید. موفق باشید.

  10. شهداد

    سلام و خسته نباشید
    در یک ستون که فیلتر شده وقتی میخوام یه گزینه رو پیدا کنم دقیقا باید همون مقادیر رو تایپ کنم و این کارمو سخت میکنه
    مثلا گزینه انتخابیم ۱۲۳-۳۲۵۶-۶۷ هست و من اگر حتی یه خط فاصله رو نذارم پیدا نمیکنه
    راهی هست برای سرچ این نوع گزینه ها؟

    1. حامد قدیمی

      سلام، بله، در قسمت Filter از کارکترهای Wildcard استفاده کنید، مثلاً اگر عبارت *۱۲۳* را جستجو کنید به این معنی است که دنبال هر نوشته ای که در آن ۱۲۳ هست میگردید. لطفاً مقاله مربوط به کارکترهای Wildcard و جستجو را بخوانید. موفق باشید.

    1. حامد قدیمی

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

  11. پیمان

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

    1. حامد قدیمی

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

  12. لطیف افق عطا

    سلام،ببخشید اگر یک ستون را بخواهید فیلتر کنید و فیلتر در یک بازه باشد و این بازه توسط دو سل با آدرس دهی مشخص شود چکار بایست کرد؟مرسی

    1. حامد قدیمی

      سلام، در مقاله این موضوع توضیح داده شده است. Number filter را انتخاب کنید و Between را انتخاب کنید و آدرس سلول های مورد نظر را در این قسمت وارد کنید. موفق باشید.

  13. میلاد

    سلام خسته نباشید فرض کنید ۱۰۰ ردیف در یک شیت داریم و میخواهیم یک ستون رو فیلتر کنیم ولی ردیف صدم رو نمیخوایم جز فیلتر باشه آیا راهی هست به جز درگ کردن ۹۹ ردیف بالا؟

    1. حامد قدیمی

      سلام، اگر داده های شما به صورت Table باشند، خیر راهی نیست و تمامی سطر ها طبق قوانین فیلتر شما فیلتر میشوند. اما در صورتی که داده های Table نباشند راه حل پیشنهادی شما به کار میرود، اما پیشنهاد من استفاده از فیلتر هوشمند تر و اضافه کردن ستون ها برای ایجاد شروط بیشتر میباشد، به این ترتیب نیازی به حذف دستی هیپچ داده ای نیست، تعداد شرط ها برای فیلتر کردن میتواند بینهایت باشد.
      موفق باشید.

دیدگاهتان را بنویسید؟