مرتب سازی و فیلتر کردن باSORT و FILTER در اکسل | (آموزش کامل)

مرتب سازی SORT(سورت) و FILTER(فیلتر) در نرم افزار اکسل

مرتب سازی (Sort) در اکسل در Excel

آموزش ابزار فیلتر(filter) و سورت(sort) در نرم افزار اکسل : یکی از ابزارهای بسیار مهم در نرم افزار اکسل Sort میباشد. با استفاده از امکانات Sort میتوانید اطلاعات و داده‌هایتان را به صورت سطری، ستونی، صعودی یا نزولی و یا یک ترتیب سفارشی خاص مرتب نمایید.

سورس ستونی (تک ستون) در Excel

فرض کنید در ناحیه A1تا A7 داده های عدد وارد شده است و میخواهید سورت کنید، برای این کار کافیست ستون مربوطه را انتخاب کرده و گزینه‌های A-Zیا Z-A را کلیک کنید تا عملیات مرتب سازی بصورت صعودی یا نزولی انجام شود.

سورس ستونی (چند ستون) در Excel

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

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

گزینه اول – Expand the selection: با انتخاب این گزینه اکسل علاوه بر مرتب کردن ستون انتخاب شده، ستون‌های مجاور را هم مرتب میکند. همانطور که مشاهده میکنید تمامی ستون‌ها نظیر به نظیر براساس ستون B مرتب شدند.

گزینه دوم – Continue with the current selection: با انتخاب این گزینه اکسل فقط همان ستون انتخاب شده را سورت می‌کند و با بقیه ستون‌ها کاری ندارد.
نکته: شما می‌توانید از دکمه های تعبیه شده برای فیلتر هم عملیات مرتب سازی را انجام دهید. بدین صورت که اگر دکمه کشویی مربوط به کد گروه را باز کنید و گزینه‌های سورت را مشاهده می‌کنید.

آموزش مرتبط برای شما : آموزش اکسل مقدماتی تا پیشرفته کاملا رایگان

سورت ۱۸۱ درجه در اکسل:

فرض کنید جدول زیر را می‌خواهید ۱۱۰درجه معکوس کنید برای این کار کافیست یاک ستون کمکی که دارای شماره های صعودی هستند ایجاد کنید و عملیات انتخاب ناحیه را از ستون F تا ستون A انجام دهید نه از ستون A تا F. (ترتیب انتخاب در اینجا مهم است) سپس به راحتی عملیات سورت را انجام دهید.

سورت براساس لیست سفارشی در اکسل:

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

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

برای این کار یکی از سلول‌های جدول داده را انتخاب کنید و گزینه Sort را انتخاب کنید تا پنجره زیر باز شود.

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

• Column: در این قسمت باید فیلدی انتخاب شود که میخواهیم سورت سفارشی بر اساس آن انجام شود. مانند ماه
• Sort On: در این قسمت گزینه Cell Values را انتخاب میکنیم تا براساس ارزشی که هر سلول دارد سورت شود نه رنگ سلول یارنگ فونت.
• Order: کشوی مربوط به این قسمت را باز کرده و گزینه … Custom List را انتخاب میکنیم. این قسمت اصلی ترین قسمت سورت سفارشی می‌باشد. با انتخاب این گزینه پنجره زیر باز میشود

از پنجره باز شده ابتدا از جدول سمت چپ ماه‌ ها را انتخاب کنید سپس در گام بعد OK را کلیک کنید.

توجه مهم: درصورتی که در سیستم شما ماه ها وجود نداشت باید از مسیر زیر ماه‌ها را تعریف کنید:

• گزینه File
• گزینه Options
• گزینه Advanced
• گزینه Edit Custom Lists

• در کادر مربوط به List entries ماه‌ها را به ترتیب نوشته و بین آنها اینتر را بزنید تا ماه بعدی در خط بعدی نوشته شود.
• بعد از نوشتن تمام ماه‌ها گزینه Add را بزنید تا ماه های نوشته شده وارد کادر سمت چپ ( … Custom List) شود.

• در گام آخر گزینه OK را کلیک کنید.

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

در انتها گزینه OK را انتخاب کنید تا سورت سفارشی انجام شود.

سورت چند سطحی در Excel:

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

 

برای این کار یکی از سلولهای جدول داده را انتخاب کنید و گزینه Sort را انتخاب کنید تا پنجره زیر باز شود.

ابتدا سورس اولیه را براساس شرط اول یعنی “سال” انجام میدهیم و سه گام ۱تا ۳ را مانند مراحل قبال انجام می دهیم.

در گام بعد (گام ۴) گزینه Add Level را میزنیم تا بتوانیم شرط بعدی (ماه) را اعمال کنیم. شرط بعدی چون یک شرط سفارشی است باید مانند مراحل سورت سفارشی انجام شود.

در نهایت OK را انتخاب میکنیم تا سورت براساس چند شرط اعمال شود.

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

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



درنهایت نمایش جدول سورت شده بصورت زیر میباشد:

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

ابزار سورت برای مرتب سازی داده ها در اکسل بکار می رود. فرض کنید در ستون A نام افراد را بصورت زیر داشته باشید.

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

سپس کافیست روی ابزار SORT & FILTER کلیک کرده و از لیست باز شده گزینه را انتخاب کنیم.

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

ابزار SORT Z TO A عکس عمل ابزار فوق را انجام میدهد و دادهها را از بزرگ به کوچک و یا از انتها به ابتدا مرتب میکند.

آموزش مفید : لیست کشویی در اکسل | ۱ روش عالی برای وارد کردن محتویات یک سلول

مثال: در محیط اکسل همانند تصویر زیر لیستی از مبلغ حقوق ماهانه کارکنان یک شرکت را در اختیار داریم.

می خواهیم این دستمزدها را از بزرگ به کوچک مرتب کنیم. برای این کار ابتدا محدوده مورد نظر را با ماوس انتخاب کرده و سپس با کلیک روی ابزار SORT & FILTER ابزار SORT Z TO A را انتخاب میکنیم.

مشاهده میکنیم که حقوق افراد از بیشترین مبلغ تا کمترین مبلغ مرتب شده است.

مثال: فرض کنیم لیست حقوق کارکنان یک شرکت بصورت زیر باشد.

می‌خواهیم لیستی تهیه کنیم که نام افراد به ترتیب حروف الفبا باشد و حقوق هر فرد به درستی در مقابل آن قرار گیرد. برای این کار قسمت نام ها را با ماوس انتخاب میکنیم و سپس از قسمت ابزارهای SORT گزینه SORT A TO Z را انتخاب میکنیم.

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

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

مثال: فرض کنید لیست حقوق زیر در محیط اکسل در دسترس است. میخواهیم این لیست بر اساس مبالغ حقوق از بزرگ به کوچک مرتب شود.

برای انجام این کار میتوان همانند مثال قبل عمل کرد. اما روش دیگر برای انجام این کار استفاده از ابزار CUSTOM SORT میباشد. در ابتدا کل جدول را با ماوس انتخاب کنید )حتی نام ستونها را هم با ماوس انتخاب کنید.)

سپس از ابزار SORT(سورت) & (فیلتر)FILTER گزینه CUSTOM SORT را انتخاب کنید تا کادر آن باز شود.

مطلب مرتبط  : آموزش سربرگ review در اکسل به صورت تصویری

در ادامه آموزش ابزار فیلتر و سورت در نرم افزار اکسل در گوشه کادر باز شده حتما باید گزینه MY DATA HAS HEADERS تیک دار باشد، این گزینه به اکسل فرمان میدهد که جدول دادهها دارای نام ستون یا سربرگ است. با این کار نام ستونها در جابجایی ابزار SORT شرکت داده نمیشوند.

حالا در لیست باز شونده مقابل گزینه SORT BY ستونی که مرتب سازی باید بر اساس آن انجام شود را مشخص کنید.

سایر تنظیمات این کادر در تصویر زیر نمایش داده شدهاند. پس از اعمال این تنظیمات در پایان روی گزینه OK کلیک کنید.

جدول حاصل بصورت زیر خواهد بود.

آموزش ابزار فیلتر(Filter) در اکسل

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

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

حالا مشاهده می‌کنید که در کنار نام ستون ها مثلث کوچکی وجود دارد که با کلیک بر آن لیست انواع دادههای موجود در هر ستون نمایش داده میشود.

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

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

پس از کلیک بر گزینه OK تصویر حاصل بصورت زیر خواهد بود.

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

مثال: در تصویر زیر که لیست دستمزد کارکنان یک شرکت است، میخواهیم نام آقای احمدی را جستوجو کنیم.

برای این کار کافیست روی ابزار FIND & SELECT کلیک کنیم و گزینه FIND را انتخاب کنیم.

در کادر باز شده همانند تصویر زیر نام شخص مورد نظر را مینویسیم و روی گزینه FIND ALL کلیک میکنیم.

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

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

در پایان با کلیک بر گزینه CLOSE لیست حقوق بصورت زیر اصلاح شده است.

سایر گزینه های این ابزار شامل جستوجوی سلول های دارایی فرمول، یادداشت، فرمت شرطی و … می باشد.

فیلتر کردن (Filter) در Excel

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

 

 

 

نکته: برای اعمال فیلتر میتوان از کلید میانبر Ctrl + Shift +L نیز استفاده نمود.

بعد از اعمال فیلتر دکمه Drop Down در بالای ستون‌ها فعال میشود که با استفاده از آنها میتوان فیلترهای مورد نظر خودمان را اعمال کنیم. مانند شکلهای زیر:

قبل از فعال کردن ابزار فیلتر:

 

بعد از فعال کردن ابزار فیلتر:

 

فرض کنید در شکل زیر بخواهیم دادهها را براساس ماه فروردین فیلتر نمائیم. مانند شکل زیر بر روی دکمه Drop Down در سلول B1 کلیک کرده و از لیست ماه‌ ها تیک مربوط به ماه مورد نظر را فعال می کنیم. (تیک سایر ماه‌ها را بردارید، با زدن تیک Select All همه ماه‌ها برداشته خواهد شد.)

 

 

تشریح برخی از گزینه‌های موجود در پنجره فیلتر:

اگر در ستونی که داده‌های آن متنی باشد، دکمه Drop Down را بزنید در پنجره Filter گزینه Text Filters ظاهر می‌شود. مانند شکل زیر:

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

از Wildcard ها در بحث فیلترها نیز میتوان استفاده کرد. در ادامه چند نمونه از این Wildcardها و نحوه استفاده از آنها توضیح داده‌میشود:

الف) علامت *

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

فرض کنید در دیتابیس شکل زیر بخواهیم استان‌هایی که با حرف الف شروع میشوند را فیلتر کنیم برای این کار مانند شکل زیر کافی است در کادر مربوط به جستجو عبارت ا* تایپ نمائیم

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

ب) علامت ؟
این علامت به نسبت کاراکتر * کاربرد کمتری دارد و معمولا به جای یک کاراکتر می‌نشیند.

فیلتر چند شرطی در اکسل

اگر بخواهیم داده ها را همزمان براساس چند شرط فیلتر کنیم، می‌توانیم از فیلترهای شرطی استفاده نمائیم.
اعمال شرط AND:
فرض کنید داده‌های شکل زیر را بخواهیم براساس سال و نام محصول فیلتر کنیم. در این مثال شرط‌های ما عبارتند از سال ۱۳۹۳ و محصول ۱بودن. در این مثال عبارت (و) به این معنی است که هر دو شرط باید برقرار باشد.
مانند شکل زیر ابتدا محدوده شرط را ایجاد کرده و شرط‌های مورد نظر را بنویسید.

اکنون از تب DATA گزینه Advanced را انتخاب کنید مانند شکل روبرو:

در پنجره باز شده مانند شکل زیر محدوده دیتابیس و محدوده شرط را انتخاب کرده و Ok نمائید. 

بعد از Ok کردن مانند شکل زیر داده‌های فیلتر شده در محل مورد نظر ما کپی خواهند شد.

اعمال شرط OR

اگر بخواهیم داده‌ها را براساس شرط یا بودن فیلتر کنیم، مثلا سال ۱۳۹۳ یا محصول ۱بودن. در اینگونه مسائل باید محدوده شروط را مانند شکل زیر وارد کنیم:

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

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

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

فقط اعمال فیلتر براساس ماه

اعمال فیلتر براساس ماه و سورت براساس سال

امیدواریم از این مطلب در سایت صنایع سافت که درباره ابزار Sort(سورت) و Filter(فیلتر) در اکسل بود، لذت برده باشید. هر سوال و نظری دارید برای ما کامنت بذارید، کمتر از یک روز پاسخ داده می‌شه 😉