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

از AutoFilter یا عملگرهای مقایسه داخلی مانند "بیشتر از" و "10 برتر" در Top 10 برای نمایش داده های مورد نظر و پنهان کردن بقیه استفاده کنید. پس از فیلتر کردن داده‌ها در محدوده‌ای از سلول‌ها یا جدول، می‌توانید فیلتر را مجدداً برای دریافت آخرین نتایج اعمال کنید یا فیلتر را پاک کنید تا همه داده‌ها دوباره نمایش داده شوند.

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

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

فیلتر کردن داده ها در یک جدول

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

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

توجه داشته باشید:هنگام استفاده از " جستجو کردنبرای جستجوی داده‌های فیلتر شده، فقط داده‌هایی را که نمایش داده می‌شوند جستجو می‌کنید. داده‌هایی که نمایش داده نمی‌شوند جستجو نمی‌شوند. برای جستجوی همه داده‌ها، کادر علامت همه فیلترها را پاک کنید.

اطلاعات بیشتر در مورد فیلترینگ

دو نوع فیلتر

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

اعمال مجدد فیلتر

برای تعیین اینکه آیا فیلتر اعمال شده است، به نماد موجود در سر ستون نگاه کنید.

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

اگر ماوس خود را روی سرصفحه ستونی قرار دهید که فیلتر آن فعال است اما اعمال نمی شود، نوک صفحه نمایش "(همه را نشان می دهد)" ظاهر می شود.

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

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

هنگامی که فیلتر را به طور مکرر اعمال می کنید، نتایج متفاوتی به دلایل زیر ظاهر می شود:

داده ها در محدوده ای از سلول ها یا یک ستون جدول اضافه، تغییر یا حذف شده اند.

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

از انواع داده های مختلط استفاده نکنید

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

فیلتر کردن داده ها در یک جدول

همانطور که داده ها را در جدول وارد می کنید، کنترل های فیلترینگ به طور خودکار به سرفصل های ستون اضافه می شوند.

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

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

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

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

گزینه های فیلتر برای جداول یا محدوده ها

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

گاهی اوقات هنگام کار با یک پایگاه داده بزرگ، انتخاب اطلاعات بر اساس یک معیار خاص ضروری می شود (به عنوان مثال، فقط افرادی را که در سال 1980 متولد شده اند نشان دهید). برای انجام کارهایی از این دست، از یک فیلتر در اکسل استفاده کنید.

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

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

فیلتر کردن، انتخاب داده های لازم از یک لیست برای کار بعدی با آن است.نتیجه این روش خطوط مشخصی خواهد بود که با معیارهای انتخاب مطابقت دارد. ورودی‌های باقی‌مانده به‌طور موقت پنهان می‌شوند و تا زمانی که کاربر فیلتر را غیرفعال نکند، استفاده نمی‌شوند. می توانید اقدامات استاندارد را با داده های انتخاب شده انجام دهید: ویرایش، قالب بندی، چاپ، ایجاد نمودارها، نمودارها و غیره.

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

1. مرتب سازی به ترتیب صعودی یا نزولی.
2. "همه" - اکسل تمام ردیف ها را نشان می دهد (بازیابی).
3. 10 برتر — اکسل 10 رکورد اول را نمایش می دهد. با انتخاب این مورد، پنجره جدیدی باز می شود که در آن می توانید تعداد رکوردها را مشخص کنید، انتخاب کنید که کدام یک (بزرگترین یا کوچکترین) نمایش داده شود و همچنین محدودیتی برای تعداد رکوردهای نمایش داده شده تعیین کنید.
4. "شرط" - در اینجا کاربر می تواند به طور مستقل 2 معیار انتخاب داده را ایجاد کند و آنها را با عملگرهای ساده AND، OR ترکیب کند.
5. هر یک از عناصر در اینجا می توانید هر مقداری را که در ستون است انتخاب کنید. به عنوان مثال، اگر یک ستون از نام محصولات تشکیل شده باشد، کاملاً همه محصولات در لیست عناصر نشان داده می شوند. کاربر می تواند هر یک از آنها را مشخص کند.
6. خالی و غیر خالی - اکسل سلول های خالی (یا غیر خالی) را نمایش می دهد. این گزینه فقط در صورتی ظاهر می شود که ستون دارای فیلدهای خالی باشد.

   برای حذف یک فیلتر از یک ستون، باید روی مورد "همه" در لیست عناصر کلیک کنید. اگر باید آن را برای کل جدول لغو کنید، باید "داده ها - فیلتر - نمایش همه" را در نوار منو انتخاب کنید. حذف یک فیلتر خودکار به همان روشی که راه اندازی آن انجام می شود.

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

   

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

   گزینه دوم فیلترینگ گزینه های بیشتری را برای انتخاب داده های لازم در اختیار کاربر قرار می دهد.برای اجرای گزینه پیشرفته باید یک کپی از هدر جدول ایجاد کنید (یعنی فقط سربرگ را کپی کنید). این محدوده شرایط خواهد بود. سپس باید این محدوده را با معیارهای انتخاب پر کنید. اما در اینجا مهم است که به قوانین پایبند باشید: اگر نیاز دارید مقادیر با توجه به دو پارامتر (به عنوان مثال نام خانوادگی و نمره دانش آموز) انتخاب شوند ، شرایط در یک خط نوشته می شود. اگر معیارها در حالت "OR" انتخاب شوند (ساخت خودرو یا اندازه موتور)، سپس در خطوط مختلف ثبت می شوند.

   فرض کنید یک جدول با 2 ستون وجود دارد - نام محصول و کمیت. در کل 3 محصول وجود دارد - موز، پرتقال، نارنگی و تعداد آنها به ترتیب 10، 20 و 15 عدد است. پس از کپی شدن هدر، می توانید یک شرط ایجاد کنید، به عنوان مثال، محصولاتی را نشان دهید که مقدار آنها کمتر یا مساوی 15 است. یعنی در زیر هدر کپی شده در ستون "تعداد" باید بنویسید.<=15. Затем надо запустить расширенный фильтр, указать исходный диапазон (исходная таблица), диапазон условий (таблица, где указано «кол-во <=15») и нажать «ОК». Исходная таблица изменится: теперь тут будут отображены только бананы (10 штук) и мандарины (15 штук).

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

مایکروسافت اکسل یک ابزار صفحه گسترده همه جا حاضر و راحت است.

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

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

یکی از راحت ترین ویژگی های این برنامه فیلتر کردن اطلاعات است. بیایید نحوه تنظیم و استفاده از فیلترهای MS excel را بررسی کنیم.

یافتن فیلترها در این برنامه آسان است - باید منوی اصلی را باز کنید یا فقط کلیدهای Ctrl+Shift+L را نگه دارید.

توابع اصلی فیلتر کردن در اکسل:

• انتخاب بر اساس رنگ: مرتب کردن داده ها را بر اساس فونت یا رنگ پر می کند.
• فیلترهای متنی در اکسل: به شما امکان می دهد شرایط خاصی را برای ردیف ها تنظیم کنید، به عنوان مثال: کمتر از، بزرگتر از، مساوی، مساوی نیست و سایرین، و همچنین شرایط منطقی را تنظیم کنید - و، یا،
• فیلترهای عددی: مرتب سازی بر اساس معیارهای عددی، به عنوان مثال، زیر متوسط، 10 برتر و موارد دیگر،
• دستی: انتخاب را می توان با توجه به معیارهای خود انتخابی انجام داد.

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

نحوه استفاده از فیلتر پیشرفته در اکسل - نحوه تنظیم آن

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

آموزش تصویری

روش تنظیم:

1. یک جدول با داده ها برای کار بیشتر با آن ایجاد کنید. نباید حاوی خطوط خالی باشد.
2. یک جدول با شرایط انتخاب ایجاد کنید.
3. فیلتر پیشرفته را راه اندازی کنید.

بیایید به یک نمونه راه اندازی نگاه کنیم.
ما یک جدول با ستون های محصول، مقدار و قیمت داریم.

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

جدول با شرایط در سلول های A1:A2 قرار می گیرد. مهم است که نام ستونی را که انتخاب انجام می شود (سلول A1) و کلمه خود انتخاب - Nails (سلول A2) مشخص کنید.

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

سپس شما نیاز دارید:

1. هر یک از سلول ها را انتخاب کنید،
2. "فیلتر پیشرفته" را با دنبال کردن مسیر: داده - مرتب سازی و فیلتر - پیشرفته، باز کنید.
3. آنچه را که در قسمت "محدوده اصلی" مشخص شده است بررسی کنید - کل جدول با اطلاعات باید به آنجا برود.
4. در "محدوده شرایط" باید مقادیر سلول ها را با شرط انتخاب تنظیم کنید، در این مثال این محدوده A1:A2 است.

پس از کلیک بر روی دکمه "OK" اطلاعات لازم انتخاب می شود و فقط ردیف هایی با کلمه مورد نظر در جدول ظاهر می شود که در مورد ما "ناخن" است. اعداد خط باقیمانده آبی می شوند. برای لغو فیلتر مشخص شده، کافی است CTRL+SHIFT+L را فشار دهید.

همچنین تنظیم فیلتر برای خطوط حاوی کلمه دقیق «ناخن» بدون اینکه به حروف کوچک و بزرگ حساس باشد، آسان است. در محدوده B1:B2 ستونی را با معیار انتخاب جدید قرار می دهیم و فراموش نمی کنیم که عنوان ستونی را که غربالگری در آن انجام می شود، نشان دهیم. در سلول B2 باید فرمول زیر را وارد کنید ="=Nails".

• هر سلول جدول را انتخاب کنید،
• "فیلتر پیشرفته" را باز کنید
• بررسی کنید که کل جدول با داده ها در "محدوده منبع" گنجانده شده باشد،
• در "محدوده شرایط" B1:B2 را نشان دهید.

پس از کلیک روی "OK"، داده ها فیلتر می شوند.

اینها ساده ترین نمونه ها هستند کار با فیلترها در اکسل. در نسخه پیشرفته، تنظیم سایر شرایط برای انتخاب راحت است، به عنوان مثال، غربالگری با پارامتر "OR"، غربالگری با پارامتر "Nails" و مقداری در ستون "کمیت" > 40.

اطلاعات موجود در جدول را می توان با ستون ها - یک یا چند - فیلتر کرد. بیایید به مثال جدولی با ستون های "شهر"، "ماه" و "فروش" نگاه کنیم.

مثال 1

اگر می‌خواهید داده‌ها را براساس ستونی با نام شهرها به ترتیب حروف الفبا فیلتر کنید، باید هر یک از سلول‌های این ستون را انتخاب کنید، «مرتب‌سازی» و «فیلتر» را باز کنید و گزینه «AY» را انتخاب کنید. در نتیجه اطلاعات با در نظر گرفتن حرف اول نام شهر نمایش داده می شود.

برای به دست آوردن اطلاعات با استفاده از اصل معکوس، باید از پارامتر "YA" استفاده کنید.

مثال 2

باید اطلاعات را به تفکیک ماه فیلتر کرد و همچنین شهری که حجم فروش بالایی دارد باید در جدول بالای شهر با حجم فروش کمتر باشد. برای حل مشکل، باید گزینه “Sorting” را در “Sorting and Filter” انتخاب کنید. در پنجره تنظیماتی که ظاهر می شود، "مرتب سازی بر اساس" - "ماه" را مشخص کنید.

در مرحله بعد باید سطح مرتب سازی دوم را اضافه کنید. برای انجام این کار، باید در "مرتب سازی" - "افزودن سطح" را انتخاب کنید و ستون "فروش" را مشخص کنید. در ستون تنظیمات "Order"، "Descending" را انتخاب کنید. پس از کلیک بر روی "OK"، داده ها با توجه به پارامترهای مشخص شده انتخاب می شوند.

آموزش تصویری

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

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

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

راه حل مشکل:

1. ستون تاریخ را انتخاب کنید،
2. تب Excel را در منوی اصلی باز کنید،
3. دکمه "Cells" را انتخاب کرده و گزینه "تبدیل متن به تاریخ" را از لیست کشویی انتخاب کنید.

اشتباهات رایج کاربران هنگام کار با این برنامه نیز عبارتند از:

• عدم وجود هدر ستون (بدون آنها، فیلتر کردن، مرتب سازی و تعدادی از پارامترهای مهم دیگر کار نمی کند)،
• وجود سطرها و ستون‌های خالی در جدول با داده‌ها (این سیستم مرتب‌سازی را اشتباه می‌گیرد؛ اکسل اطلاعات را به عنوان دو جدول مختلف مستقل از یکدیگر درک می‌کند)،
• قرار دادن چندین جدول در یک صفحه (قرار دادن هر جدول در یک صفحه جداگانه راحت تر است)
• قرار دادن در چندین ستون داده از همان نوع،
• قرار دادن داده ها در چندین برگه، به عنوان مثال، بر اساس ماه یا سال (میزان کار را می توان بلافاصله در تعداد برگه های حاوی اطلاعات ضرب کرد).

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

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

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

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

به عنوان مثال، یک جدول اضافی در بالای جدول اصلی قرار دادیم و سلول های آن را نارنجی رنگ کردیم. اگرچه، می توانید این میز را در هر فضای آزاد و حتی روی یک صفحه دیگر قرار دهید.

اکنون، داده هایی را که باید از جدول اصلی فیلتر شوند، وارد جدول اضافی می کنیم. در مورد خاص ما، از لیست دستمزدهای صادر شده برای کارمندان، تصمیم گرفتیم داده های مربوط به پرسنل اصلی مرد را برای 2016/07/25 انتخاب کنیم.

اجرای یک فیلتر پیشرفته

فقط پس از ایجاد جدول اضافی می توانید فیلتر پیشرفته را اجرا کنید. برای انجام این کار، به برگه "داده ها" بروید و روی نوار در بلوک ابزار "مرتب سازی و فیلتر" روی دکمه "پیشرفته" کلیک کنید.

پنجره فیلتر پیشرفته باز می شود.

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

در قسمت Source Range، باید محدوده سلول های جدول منبع را مشخص کنید. این کار را می توان به صورت دستی با وارد کردن مختصات از صفحه کلید یا با انتخاب محدوده مورد نظر از سلول ها با استفاده از ماوس انجام داد. در قسمت "محدوده شرایط"، باید به طور مشابه محدوده سرصفحه جدول اضافی و ردیفی که شامل شرایط است را نشان دهید. در عین حال، باید توجه داشته باشید که این محدوده حاوی خطوط خالی نباشد، در غیر این صورت هیچ چیز کار نخواهد کرد. پس از انجام تمام تنظیمات، بر روی دکمه "OK" کلیک کنید.

همانطور که می بینید، فقط مقادیری که تصمیم گرفتیم فیلتر کنیم در جدول اصلی باقی ماندند.

اگر گزینه خروجی نتیجه را در مکان دیگری انتخاب کرده اید، در قسمت "Place result in range" باید محدوده سلول هایی را مشخص کنید که داده های فیلتر شده در آنها خروجی می شود. همچنین می توانید یک سلول را مشخص کنید. در این صورت به سلول سمت چپ بالای جدول جدید تبدیل می شود. پس از انتخاب، بر روی دکمه "OK" کلیک کنید.

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

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

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