با جدول ها در اکسل و ویژگی های آنها در مقالات قبلی آشنا شدیم، مجدداً یادآوری میگردد که برای تحلیل بر روی داده ها در اکسل بهتر است ابتدا داده ها را به صورت جدول در بیاوریم و سپس بر روی آنها عملیات انجام دهیم. در مقاله معرفی جدول در اکسل بیان شد که یکی از مزایای تبدیل داده ها به جدول این است که بر روی هر فیلد (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 به کار برد.
برای تمرین بیشتر مبحث فیلتر در اکسل، با کلیک بر روی این خط، فایل نمونه را دانلود کنید.
چه جوری میشه بعد از فیلت کردن، مقادیر یک ستون و هم جمع کرد؟؟
سلام، با استفاده از تابع SUBTOTAL، لطفاً مقاله مربوطه را مطالعه کنید. موفق باشید.
سلام خسته نباشید ببخشید ابزار فیلتر توی اکسل من به صورته کم رنگه و استفاده نمی شه می شه راهنمایی کنین ممنونم اگه واسم ایمیل کنیین
سلام، داده های خود را در اکسل دیگر در کامپیوتر دیگر امتحان کنید، در صورت غیر فعال بودن ابزار فیلتر داده های شما مشکل دارند و قابل فیلتر شدن نیستند. در این صورت فایل را ارسال کنید تا بررسی کنم. موفق باشید.
سلام
منم همچین مشکلی دارم چطوری فایل رو براتون ارسال کنم ؟
سلام، میتوانید از طریق ایمیل یا تلگرام ارسال کنید، در قسمت “تماس با ما” ازلاعات مورد نیاز وجود دارد.
سلام من یه فاکتور فروش درسات کردم ولی وقتی نام هر مشتری رو برای چاپ فاکتورش فیلتر میکنم چندبار تو فاکتور زیر ردیف نام مشتری تکرار میشه که جالب نیست میشه یکاری کرد که هر اسمی رو که فیلتر میکنم یجا بنویسه تا یبار چاپ بشه؟
سلام، در جدول معمولی خیر، اما در Pivot Table این کار راحت و بسیار حرفه ای تر قابل انجام است. لطفاً از Pivot Table استفاده کنید.
جناب آقای قدیمی. سلام و تشکر بابت زحماتی که در کمک به دیگران تحمل می کنید. یک سؤال:
یک شیت را فیلتر کرده ام. می خواهم فیلتر را بردارم اما اشیاء فیلتر شده بازنگردند. به دلیلی امکان کپی و پیست هم نیست. چه راهی پیشنهاد می فرمایید؟ با تشکر
سلام، متشکرم، شما میبایست از ابزار Advanced Filter استفاده کنید. لطفاً مقاله مربوطه را بخوانید.
سلام
ممنون از مطالب مفیدتون
میشه لطفا به من بگید چگونه سطرها و ستون های فیلتر شده را بدون پاک شدن سایر سطرها پاک کنیم؟
سلام، شما باید از ابزار Advanced Filter استفاده کنید. مقاله مربوطه را بخوانید.
با سلام خدمت استاد گرامی
من الکسلی دارم که تعداد سطر از ۱۰۰۰ به بالا هستش . و فیلتر نمیتونه سرچ کنه مگر با ctrl+f
لطفا راهنماییم کنید با تشکر
سلام، از ابزار Filter درست استفاده کنید. مشکلی وجود ندارد. مقاله مربوطه را بخوانید.
با سلام
وقتی که در یک ستون فقط تیک یک مربع را می زنم همه داده ها نشان داده می شود درصورتیکه فقط باید مربع انتخاب شده نشان داده شود. مشکل په چیزی است؟
متشکرم
سلام، لطفاً در فیلتر کردن دقت نمایید و مطالب آموزشی را مطالعه کنید. موفق باشید.
سلام من می خوام یک جدول رو براساس ساعت فیلتر کنم مثلا میخوام براساس یک تاریخ و ساعت مشخص فیلتر کنم باید چیکار کنم؟
لطفا راهنماییم کنید
سلام، ساعت و تاریخ در اکسل در واقع داده های عددی هستند، لیکن میتوان مانند اعداد بر روی آنها عملیات انجام داد،
با عرض سلام و خسته نباشید. در یک فایل اکسل که بیش از ۳۰ شیت دارد چطور میتوانم یک داده را که شامل چهار ردیف است فیلتر کرده در صفحه اکسل جداگانه و یا شیت جداگانه کپی کنم؟
در یک فایل اکسل که بیش از ۳۰ شیت دارد چطور میتوانم یک داده را که شامل چهار ردیف است فیلتر کرده در صفحه اکسل جداگانه و یا شیت جداگانه کپی کنم؟
سلام، از ابزار Advanced Filter میتوانید برای کپی و فیلتر کردن داده ها به صورت همزمان استفاده نمایید. اما نمیتوانید داده ها را در شضیت دیگر کپی کنید، به عبارتی با این ابزار میتوانید داده ها را در همان شیت فیلتر کرده و در محل دیگری در همان شیت کپی نمایید. موفق باشید.
سلام و خسته نباشید
در یک ستون که فیلتر شده وقتی میخوام یه گزینه رو پیدا کنم دقیقا باید همون مقادیر رو تایپ کنم و این کارمو سخت میکنه
مثلا گزینه انتخابیم ۱۲۳-۳۲۵۶-۶۷ هست و من اگر حتی یه خط فاصله رو نذارم پیدا نمیکنه
راهی هست برای سرچ این نوع گزینه ها؟
سلام، بله، در قسمت Filter از کارکترهای Wildcard استفاده کنید، مثلاً اگر عبارت *۱۲۳* را جستجو کنید به این معنی است که دنبال هر نوشته ای که در آن ۱۲۳ هست میگردید. لطفاً مقاله مربوط به کارکترهای Wildcard و جستجو را بخوانید. موفق باشید.
سلام
لطف میکنید برام نمونه سوال بفرستید ؟ برای دانش آموزان مجتمع فنی می خوام
ممنون
سلام، من نمونه سوال آماده ندارم و به تناسب درخواست ارائه شده نمونه تهیه میکنم، میتوانید از فایل های نمونه قرار داده شده در مقالات استفاده کنید و با توجه به سطح دانش آموزان خود سوالات را طرح کنید. موفق باشید.
من فیلترو استفاده میکنم مثلا بین دوتا گزینه گزینه دومو فیلتر میکنم اما فیلتر نمیشه همه ی شیت و هنوز بین سطر ها گزینه ی اول موجوده ولی علامت فیلتر بالای شیت میاد
سلام، قطعاً مشکلی در داده شما وجود دارد لطفاً دقت کنید که کاراکترها باید کاملاً یکسان باشند تا فیلتر انجام شود. موفق باشید.
سلام،ببخشید اگر یک ستون را بخواهید فیلتر کنید و فیلتر در یک بازه باشد و این بازه توسط دو سل با آدرس دهی مشخص شود چکار بایست کرد؟مرسی
سلام، در مقاله این موضوع توضیح داده شده است. Number filter را انتخاب کنید و Between را انتخاب کنید و آدرس سلول های مورد نظر را در این قسمت وارد کنید. موفق باشید.
سلام خسته نباشید فرض کنید ۱۰۰ ردیف در یک شیت داریم و میخواهیم یک ستون رو فیلتر کنیم ولی ردیف صدم رو نمیخوایم جز فیلتر باشه آیا راهی هست به جز درگ کردن ۹۹ ردیف بالا؟
سلام، اگر داده های شما به صورت Table باشند، خیر راهی نیست و تمامی سطر ها طبق قوانین فیلتر شما فیلتر میشوند. اما در صورتی که داده های Table نباشند راه حل پیشنهادی شما به کار میرود، اما پیشنهاد من استفاده از فیلتر هوشمند تر و اضافه کردن ستون ها برای ایجاد شروط بیشتر میباشد، به این ترتیب نیازی به حذف دستی هیپچ داده ای نیست، تعداد شرط ها برای فیلتر کردن میتواند بینهایت باشد.
موفق باشید.
سلام. ببخشید آقای قدیمی یه سوالی داشتم. من یه ستون دارم که تعداد خیلی زیادی ردیف داره. میخوام داده ی ردیف ۸۲ + ۲۰۷ + ۳۳۲ + ۴۵۷ + … تا آخر رو برام جمع کنه و بعد میانگین بگیره. یعنی اول داده ی ۸۲ و سپس با گام ۱۲۵ تایی بره پایین و جمع کنه و سپس میانگین بگیره. خیلی ممنون میشم اگر راهنمایی بفرمایید.
ارادتمند
سلام، به نظرم بهترین راه حل برای انجام این کار، استفاده از تابع averageifs هست، شما ابتدا باید یک ستون در کنار ستون اعداد خود ایجاد نمایید و سپس با استفاده از آن ستون و شروط قابل درج در تابع averageifs فرمول مورد نظر را بنویسید، موفق باشید
سلام. متشکرم. از فرمول ذکر شده ممکنه یه مثال بزنید؟ من بخش averageifs رو از سایت مطالعه کردم ولی criteria_range رو نمیدونم به چه شکلی باید بنویسم.
سلام .
میخوام یه فیلتری بین یک سری اعداد ایجاد کنم که هر عدد نسبت به ۳ تا عدد بالایی و پایین خود تقریبا تو یک بازه باشد میشه راهنمایی کنید
باسپاس.
سلام، به نظرم در اکسل این قابلیت وجود ندارد و برای انجام این کار باید کد نویسی صورت پذرد. موفق باشید.
ممنون از تلاش و آموزشتون .
چجوری میتون از بین مثلا ۱۰۰۰ عدد و ارقام متفاوت مثلا بیست عدد رو که صورت هم نیست فیلتر کنم ؟
سلام، خواهش میکنم، برای انجام این کار باید از Advanced Filter استفاده کنید، Criteria را Or تعریف کنید و اعداد مورد نظر را در یک ستون زیر هم بنویسید. مقاله Advanced Filter را بخوانید. موفق باشید.
سلام و ادب خدمت شما.ببخشید در اکسل راهی هست که برای داده هایی که دارای مختصات یکسان اما شوری متفاوتی هستند،به دلیل اینکه تعداد داده ها زیاد هستند،دستوری نوشته شود که برای داده هایی که دارای مختصات یکسان و شوری متفاوتی هستند، میانگین گیری شود؟؟ با فیلتر کردن چطوری میشه اینکار انجام داد؟؟ ممنون میشم راهنماییم کنید.
سلام، متشکرم، برای انجام این کار میتوانید از تابع AVERAGEIFS استفاده نمایید. میتوانید با درج چند شرط برای داده ها میانگین محاسبه کنید، در صورت استفاده از Filter حتما از تابع Subtotal یا Aggregate استفاده کنید، با هر دو تابع میتوان بر روی داده های فیلتر نشده محاسبه انجام داد. موفق باشید.
سلام یه جدول کالا دارم با ۵ ستون ۱-شماره ردیف ۲-نام کالا ۳- موجودی ۴- قیمت خرید ۵- کد کالا. میخوام ستون کد کالا را فیلتر کنم ولی ستون اول که شماره ردیف هست ثابت باشه و در نتیجه فرمول تغییر نکنه میشه راهنماییم کنید
سلام، در استفاده از ابزار Sort و Filter در جدول، این ابزار بر روی تمام ستون های جدول سورت و فیلتر میکند و نمیتوان یک ستون را استثنا کرد، و کلا انجام این کار به هیچ وجه منطقی نیست و نیازی به انجام این کار نیست، فکر میکنم بهتر است بررسی داده های خود را به روش دیگری انجام دهید. موفق باشید.
سلام و عرض ادب
من میخوام توی اکسل روی هر سلول یا فیلد که کلیک میکنم یه لیست رو برام باز کنه منظورم اینه که محدوده ای از سلول ها رو به من نشون بده(منظورم زیر منو و لیست کشویی نیست)یعنی میخوام وقتی روی یه سول کلیک میکنم یه لیستی از مخاطبامو که توی قسمت دیگه از سیتم هستند رو نشون بده
ممنون میشم راهنماییم کنید
سلام، برای انجام این کار میتوانید از Hyperlink استفاده کنید و با کلیک روی سلول مورد نظر، محدوده ی مورد نظر شما انتخاب شود. موفق باشید.
سلام من اکسل ۲۰۰۷ دارم وقتی فیلتر میکنم کادر سرچ داخل بخش فیلتر نیست که من بتونم کلمه مورد نظرم رو سرچ کنم راهنمایی میفرمائید
دورد، من این نسخه از اکسل رو ندارم، اگر کادر Search وجود نداره، بالتبع این قابلیت هم وجود نداره، میتوانید از Cltr+F و Search معمول در اکسل استفاده نمایید. یا اینکه ابتدا آن ستون را انتخاب کنید و سپس Ctrl+F را فشار دهید تا جستجو محدود به همان ستون شود. موفق باشید.
با سلام
ابتدا باید بابت مطالب خوبتون تشکر کنم
شاید سوالم یکم اینجا بی ربط باشه
من یک فایل اکسل دارم که سی تا حدودا شبت جداگانه داره
این فایل برای تقریبا ۱۲ تا محصول هست
حالا مسئله ای که وجود داره
بیشتر شیت ها مشترک هستن ولی برخی شیت ها مخصوص یه سری محصول خاص هستن
من هر وقت ویرایشی میکنم و میخوام فایل هر محصولی را ذخیره کنم باید دونه دونه شیت هایی که مال اون محصول نیست و هاید کنم
راهی هست بشه تعریف کرد زمان ذخیره کردن یا حتی زمان پرینت کردن به صورت pdf بگیم برای هر محصول چه شیت هایی دخیره یا پرینت شوند؟
امیدوارم تونسته باشم خوب توضیح بدم
ممنون میشم اگر راهنمایی کنید.
سلام، بله، بهترین راه کد نویسی ویژوال بیسیک هست که میتونید قسمت های دلخواه رو فیلتر و پرینت بگیرید، اما به صورت دستی، خیر، فرمولی یا دستوری در اکسل نیست که بتوان با آن شیت هایی را Hide کرد.
پیشنهاد من البته استفاده از یک شیت است به جای ۳۰ شیت (البته در صورتی که محدودیت سطر مانعی ایجاد نکند)، در این صورت تمام داده ها در یک Table مرتب در یک شیت ذخیره شود، سپس در شیت دیگری یک Pivot Table ایجاد گردد، به این صورت به راحتی میتوانید داده دلخواه را پیدا و گزارش گیری نمایید.
با سلام
می خواهم ابزار فیلتر را از ستونها بردارم اما دستور clear filter from غیر فعال است.چه باید کرد؟
با تشکر
سلام، احتمالا داده های شما به صورت Table ذخیره شده است، در این صورت باید از قسمت Design مربوط به Table، تنظیمات مورد نظر را انجام دهید.