آموزش sort اکسل

آموزش Sort در اکسل

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

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

دو مسیر برای دسترسی به گزینه مرتب سازی داده ها در اکسل وجود دارد. یکی در تب Home و دیگری تب Data.

1. در در تب Home گزینه Sort & Filter در بخش Editing قابل دسترس است.

۲- در تب Data می توانید در بخش Sort & Filter به گزینه های مرتبط به مرتب سازی داده ها دسترسی پیدا کنید. A to Z ↓ نشان دهنده مرتب سازی صعودی و Z to A ↓ مرتب سازی نزولی را نشان می هد.

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

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

مرتب سازی بر اساس معیارها

مرتب سازی بر اساس یک معیار

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

به مثال زیر نگاه کنید. می خواهیم داده ها را بر اساس ستون Customer Name به ترتیب حروف الفبا مرتب کنیم.

مرحله ۱- روی یکی از سلول ها در ستون Customer Name کلیک می کنیم.

مرحله ۲– در تب Data روی گزینه AZ ↓ کلیک می کنیم.

در تصویر زیر داده های مرتب شده نشان داده شده است. برای شناسایی بهتر، ستون Customer Name برجسته شده است.

حالا اگر بخواهیم داده ها را براساس فیلد یا معیار دیگری مرتب کنیم، چه کاری باید انجام دهیم؟ برای مثال براساس ستون Date نه ستون Name. ساده است، یک سلول دلخواه در ستون Date را انتخاب کرده سپس روی AZ ↓ یا ZA ↓ کلیک می کنیم. حالا داده ها بر اساس ستون Date مرتب می شوند.

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

مرتب سازی ( sort ) بر اساس چند معیار

در این نوع مرتب سازی، داده ها بر اساس چند معیار یا شرط مرتب می شوند.

همان مجموعه داده بالا را در نظر بگیرید. می خواهیم داده ها را براساس ستون Date سپس ستون Amount مرتب کنیم. مراحل زیر را دنبال می کنیم:

مرحله ۱– داده ها را انتخاب کرده و سپس در تب Data روی گزینه Sort کلیک می کنیم.

پنجره Sort باز می شود.

مرحله ۲- از لیست Sort by ستون “Date” و از Sort On گزینه Cell Values را انتخاب می کنیم. در بخش Order نیز گزینه Oldest to Newest را انتخاب می کنیم.

• Sort by: در این قسمت می توانید تعیین کنید که بر اساس کدام فیلد یا ستون مرتب سازی انجام شود. در این مثال می خواهیم در ابتدا داده ها بر اساس ستون Date مرتب شوند.
• Sort On: در این قسمت می توانید تعیین کنید بر اساس چه چیزی مرتب سازی انجام شود. گزینه های مختلفی مانند Cell Color (رنگ سلول)، Font Color (رنگ قلم) و غیره وجود دارد اما در این مثال Cell Values ​​(مقادیر سلول) را انتخاب می کنیم.
• Order: گزینه های مختلفی مانند A to Z یا Z to A برای داده های متنی، Smallest to Largest یا Largest to Smallest برای داده های عددی، Oldest to Newest یا Newest to Oldest برای داده های تاریخ وجود دارد. در این مثال، گزینه Oldest to Newest (قدیمی ترین به جدیدترین) را انتخاب می کنیم.

مرحله ۳- روی Add Level کلیک می کنیم تا معیار و شرط بعدی را اضافه کنیم. به ترتیب مقادیر مقدار Amount ، Cell Values و Smallest to Largest (کوچکترین به بزرگترین) را برای قسمت های Sort by، Sort On و Order وارد می کنیم:

مرحله ۴–OK می کنیم.

نتیجه مرتب سازی به شکل زیر است. ابتدا داده ها بر اساس “Date” به ترتیب صعودی و سپس بر اساس “Amount” از کوچک به بزرگ مرتب می شوند.

به اولین معیار وارد شده، معیار اصلی یا اولیه (primary criteria) گفته می شود. در مثال بالا، Date معیار اصلی است و بعد از آن Amount می آید. داده ها ابتدا بر اساس معیار اصلی و سپس سایر معیارها به ترتیب مرتب می شوند.

مرتب سازی بر اساس ستون (بالا به پایین) آموزش sort اکسل

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

مرحله ۱– داده ها را انتخاب کرده و سپس روی گزینه Sort در تب Data کلیک می کنیم.

مرحله ۲– مانند تصویر زیر روی Options کلیک می کنیم.

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

توجه داشته باشید که به طور پی فرض مرتب سازی براساس ستون (از بالا به پایین) انجام می شود.

مرحله ۳- در قسمت Sort by گزینه “Amount”، در بخش Sort On گزینه Cell Values و سپس Smallest to Largest را برای Order انتخاب می کنیم. به طور خلاصه، داده ها بر اساس مقدار به ترتیب صعودی مرتب سازی خواهند شد.

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

مرتب سازی بر اساس ردیف (چپ به راست)

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

مرحله ۱–یک سلول از داده ها را انتخاب کرده و روی گزینه Sort در تب Data کلیک می کنیم.

مرحله ۲- روی Options کلیک کرده و سپس گزینه Sort left to right (مرتب سازی از چپ به راست) را انتخاب می کنیم.

مرحله ۳- از لیست Sort by، ردیف مورد نظر برای مرتب سازی را انتخاب می شود. در این مثال، ردیف ۱۱ را انتخاب می کنیم.

مرحله ۴– در بخش Sort On گزینه Cell Values را انتخاب می کنیم.

مرحله ۵– در قسمت Order برای ترتیب مرتب سازی، گزینه Smallest to Largest (کوچکترین به بزرگترین) را انتخاب می کنیم.

مرحله ۶- روی OK کلیک می کنیم.

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

گزینه Case sensitive

وقتی روی دکمه Options کلیک کنید، گزینه Case sensitive (حساس به حروف) در بالای پنجره نمایش داده می شود.

اگر داده ها ترکیبی از حروف کوچک و بزرگ باشند، هنگام مرتب سازی باید مراقب باشید. اگر می خواهید مرتب سازی داده ها به کوچک یا بزرگ بودن حروف حساسیت داشته باشد، کادر Case sensitive را فعال کنید. به عنوان مثال ممکنه موردی داشته باشید که با هر دو حروف کوچک و بزرگ نوشته می شود، مانند “Cash” و “cash”.

گزینه My data has headers

در پنجره Sort گزینه “My data has headers” در گوشه سمت راست-بالا نمایش داده می شود. اگر جدول دارای عنوان باشد، می توانیم این کادر را انتخاب کنیم، در غیر اینصورت می توانیم کادر را غیرفعال کنیم. خب حالا اگر جدول عنوان داشته باشد و کادر انتخاب نشده باشد، چه اتفاقی می افتد؟ در این صورت عنوان نیز همراه با داده ها مرتب می شود.

خب حالا بیایید گزینه های لیست بخش Sort On را با جزئیات بررسی کنیم. در Sort On گزینه های زیر وجود دارد:

Cell Values- داده ها براساس مقادیر موجود در سلول ها مرتب می شوند.

Cell Color– داده ها بر اساس تنظیمات رنگ مرتب می شوند.

Font Color– داده ها براساس رنگ قلم مرتب می شوند.

Conditional Formation Icon- داده ها بر اساس شرط های معین مرتب سازی می شوند.

مرتب سازی بر اساس Cell Values

جدول زیر را در نظر بگیرید، می خواهیم داده ها را بر اساس Cell Values (مقادیر سلول) مرتب کنیم. یعنی مقادیر موجود در سلول ها برای مرتب سازی در نظر گرفته شوند.

مرتب سازی بر اساس مقدار سلول باعث می شود که مرتب کردن لیست های متنی، عددی و یا تاریخ/زمان آسان تر شود. می توانید مقادیر سلول را به ترتیب صعودی یا نزولی و یا براساس لیست های سفارشی مرتب کنید. ( آموزش sort اکسل )

مرتب سازی بر اساس Cell Color (رنگ سلول)

در مجموعه داده زیر یک ستون وجود دارد که دارای سلول های رنگی است، ستون Payment Method.

برای مرتب سازی بر اساس رنگ، مراحل زیر را دنبال می کنیم:

مرحله ۱– به گزینه Sort در تب Data می رویم.

مرحله ۲- در قسمت های Sort by و Sort On به ترتیب گزینه های Payment Method و Cell Color را انتخاب می کنیم.

مرحله ۳- یک تب جدید در سمت راست ظاهر می شود. این امکان می دهد تا ابتدا رنگ مورد نظر خود را برای مرتب سازی انتخاب کنیم سپس تعیین کنیم که رنگ در بالا (on top) باشد یا پایین (at the bottom).

مرحله ۴– می توانیم سطوح بیشتری برای ترتیب رنگی برای مرتب سازی داده ها اضافه کنیم.

مرحله ۵– روی OK کلیک می کنیم. داده ها بر اساس Cell Color مرتب می شوند.

مرتب سازی براساس Font Color

در مجموعه داده زیر، رنگ های قلم متفاوتی در ستون Payment Method وجود دارد.

برای مرتب سازی داده ها بر اساس Font Color مراحل زیر را دنبال می کنیم.

مرحله ۱–روی گزینه Sort در تب Data کلیک می کنیم.

مرحله ۲- در Sort by ستون مورد نظر را انتخاب کنید. در این مثال، Payment Method را انتخاب می کنیم زیرا متن سلول های آن فونت رنگی دارد.

مرحله ۳– در پنجره باز شده رنگ قلم مورد نظر خود را اضافه می کنیم.

مرحله ۴– OK می کنیم. داده ها براساس رنگ قلم مرتب می شوند.

مرتب سازی بر اساس Conditional Formatting Icon

بسیاری از مجموعه های داده های زمانی شامل آیکون های تولید شده توسط قالب بندی مشروط (Conditional Formatting) هستند. می توانید داده ها را بر اساس این آیکون ها براساس ترتیب خاصی مرتب کنید.

داده های زیر شامل آیکون ها است و می خواهیم آنها را به ترتیب دایره قرمز، دایره نارنجی و در نهایت دایره سبز مرتب کنیم. ( آموزش sort اکسل )

مرحله ۱– به تب Dataرفته و گزینه Sort را انتخاب می کنیم.

مرحله ۲- پنجره Sort با چندین گزینه ظاهر می شود. در بخش Sort by گزینه Icons، در Sort On گزینه Conditional Formatting Icon و در بخش Order، آیکون را بر اساس اولویت خود انتخاب می کنیم. در این مثال می خواهیم ابتدا دایره قرمز نشان داده شود، بنابراین آن را انتخاب کرده و از لیست کنار آن گزینه On top را انتخاب می کنیم.

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

OK می کنیم. نتیجه به صورت زیر نمایش داده می شود:

توابع مرتب سازی در اکسل

در این قسمت دو تابع Sort و SortBy برای مرتب سازی داده ها را بررسی می کنیم.

تابع Sort

تابع Sort داده ها را در یک محدوده یا آرایه مرتب می کند. نتیجه یک آرایه پویا از مقادیر است.

بیایید به مثال زیر نگاه کنیم تا عملکرد این تابع را بهتر درک کنیم.

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

۱- فرمول )SORT= را تایپ می کنیم.

۲- محدوده مورد نظر برای مرتب سازی را تعیین می کنیم. در این مثال، محدوده B8:B17 شامل ستون Name را انتخاب می کنیم.

۳- Enter را می زنیم. داده ها به ترتیب صعودی مرتب می شوند.

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

تابع SortBy

تابع SortBy امکان مرتب سازی داده ها بر اساس مقادیر موجود در آرایه یا محدوده متناظر را فراهم می کند.

شیوه کار تابع را با یک مثال بررسی می کنیم. داده های زیر را در نظر بگیرید، می خواهیم داده ها را ابتدا بر اساس ستون “Date” سپس “Amount” مرتب کنیم.

۱- )SORTBY= را تایپ می کنیم.

۲- اولین آرگومان تابع، کل آرایه یا داده ها است. در این مثال، همه داده های محدوده B8:E16 را انتخاب می کنبم.

۳- آرگومان بعدی، آرایه یا محدوده برای مرتب سازی است. در این مثال، می خواهیم ابتدا Date را مرتب کنیم بنابراین محدوده B8:B16 را انتخاب می کنیم.

۴- آرگومان بعدی، ترتیب مرتب سازی است که با مقادیر ۱ یا ۱- برای مرتب سازی صعودی یا نزولی نشان داده می شود.

۵- به طور مشابه، محدوده داده E8:E16 را اضافه می کنیم و با نوشتن ۱ آن را به ترتیب صعودی تنظیم می کنیم.

۶- به همین ترتیب می توانیم آرگومان های بیشتری اضافه کنیم.

خروجی به شکل زیر است.

مرتب سازی در Power Query (آموزش sort اکسل)

برای مرتب سازی داده ها در Power Query، ابتدا داده ها را در Power Query Editor بارگذاری کنید.

به مثال زیر توجه کنید. تصویر زیر داده ها را در Power Query Editor نشان می دهد، می خواهیم ستون Customer Name را به ترتیب نزولی مرتب می کنیم.

در سمت راست ستون Customer Name، یک کادر کشویی وجود دارد. روی آن کلیک می کنیم، گزینه های مختلف نمایش داده می شود. از بین آنها می توانیم مرتب سازی صعودی (Sort Ascending) یا مرتب سازی نزولی (Sort Descending) را انتخاب کنیم.

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

برای پاک کردن مرتب سازی می توانید از گزینه های لیست کشویی “Clear Sort” را انتخاب کنید.

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