Excel از بهترین و محبوب‌ترین برنامه‌های Microsoft Office است و با آن می‌توان داده‌های مختلفی 🔢 را به شیوه‌های گوناگون دسته‌بندی کرد. یکی از دلایلی که از اکسل استفاده می‌کنیم، این است که داده‌ها را به شکلی مرتب نگه داری کنیم و بتوانیم آنان را راحت‌تر مدیریت کنیم. در این مطلب از کلاسیک وب به راهنما و آموزش کامل نحوه مرتب سازی خودکار در اکسل می‌پردازیم.

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

?How to Automatically Sort Data in Excel

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

مرتب سازی خودکار در اکسل: مرتب‌سازی یک شیت

برای یادگیری مرتب سازی پیشرفته در اکسل، مثال زیر را دنبال کنید.

سلول موردنظر را در ستونی که می‌خواهید مرتب شود، انتخاب کنید. در این مثال، ما سلول C2 را انتخاب کردیم.

روی سربرگ Data در Ribbon کلیک کنید. برای مرتب‌سازی از A تا Z روی Ascending کلیک کنید. برای مرتب‌سازی از Z تا A روی Descending کلیک کنید. در این مثال، ما Ascending را انتخاب کردیم.

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

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

مرتب سازی تاریخ در اکسل: مرتب کردن یک رنج

برای مرتب سازی خودکار اعداد در اکسل در یک رنج، سلول‌های موردنظر خود را انتخاب کنید. در این مثال، ما سلول‌های A13 تا B17 را انتخاب کردیم.

روی سربرگ Data در Ribbon کلیک کنید. سپس Sort را انتخاب کنید.

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

سپس از بخش Orders، نحوه چیدمان موردنظر خود را انتخاب کنید. ما در اینجا Smallest to Largest را انتخاب می‌کنیم. پس از انجام تغییرات، روی OK بزنید.

حال سلول‌های انتخاب شده بر اساس ستونی که انتخاب کردید، مرتب‌سازی می‌شوند. سایر ستون‌ها نیز به همان شکل قبلی خود باقی می‌مانند و تحت تاثیر این چیدمان قرار نمی‌گیرند.

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

اگر اعداد اعشاری در ورک شیت شما رند می‌شوند، می‌توانید این مشکل را با چند راه ساده حل کنید. برای این کار به مطلب آموزش ۳ روش حل مشکل رند شدن اعداد در اکسل مراجعه کنید.

فرمول Sort در اکسل: مرتب‌سازی سفارشی

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

سربرگ Data را بزنید. روی Sort کلیک کنید.

در پنجره Sort و از بخش Column، روی Sort by کلیک کنید و ستون موردنظر خود را انتخاب کنید. سپس روی کادر زیر Order کلیک کنید و Custom List… را انتخاب کنید.

در پنجره Custom List و در بخش Custom Lists روی NEW LIST  کلیک کنید. در بخش List entries ترتیب موردنظر خود را وارد کنید. سپس Enter را بزنید.

حال روی Add بزنید. ترتیب ایجاد شده به Custom lists اضافه می‌شود. در این کادر، آن را انتخاب کنید و سپس OK را بزنید.

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

اکنون ورک شیت بر اساس تنظیمات شما مرتب‌سازی می‌شود.

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

برای مرتب سازی اعداد در اکسل و هر محتوای دیگری بر اساس فرمت سلول، روی سلولی در ستون موردنظر کلیک کنید. در مثال زیر ما سلول E2 را انتخاب کردیم.

روی سربرگ Data کلیک کنید و روی Sort بزنید.

در پنجره Sort، روی ستون موردنظر در بخش Column کلیک کنید. سپس در بخش Sort On مشخص کنید که می‌خواهید مرتب‌سازی بر اساس چه فرمتی انجام شود. در مثال زیر ما مرتب سازی را بر اساس ستون E رنگ سلول‌ها انتخاب کردیم.

حال از بخش Order رنگ موردنظر را انتخاب کنید.

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

مرتب سازی در جدول اکسل بر اساس چند مرحله

برای فیلتر و سورت در اکسل چند مرحله‌ای نیز مثالی می‌آوریم. این شیوه برای زمانی کارایی دارد که شما بخواهید داده‌های ورک شیت را بر اساس دو یا چند معیار مرتب‌سازی کنید. در مثال زیر ما داده‌ها را بر اساس Homeroom Number  و سپس Last Name مرتب‌سازی خواهیم کرد. برای این کار روی یکی از سلول‌های ستون موردنظر خود کلیک کنید. در اینجا سلول A2 را انتخاب کرده‌ایم.

روی سربرگ Data بزنید و Sort را انتخاب کنید.

در پنجره Sort، اولین ستونی را که می‌خواهید مرتب‌سازی بر اساس آن انجام شود، انتخاب کنید. ما در اینجا ستون A را انتخاب می‌کنیم. سپس روی Add Level بزنید تا بتوانید ستون دیگری را انتخاب کنید.

حال ستون دوم را انتخاب کنید و Ok را بزنید. ما در اینجا ستون C را انتخاب کردیم.

در نهایت، ورک شیت بر اساس ستون A سپس C مرتب‌سازی خواهد شد.

برای تغییر ترتیب سلول‌ها در چنین حالتی، کافی است روی تون موردنظر کلیک کنید. سپس علامت جا به جایی به سمت بالا و پایین را بر اساس نیاز خود بزنید.

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

برای یکسان سازی داده ها در اکسل بر اساس حروف الفبا فارسی، کل ستون‌های جدول را انتخاب کنید. حال روی سربرگ Data بزنید و روی Sort کلیک کنید.

در پنجره باز شده، از بخش Column ستونی را که می‌خواهید مرتب‌سازی شود، انتخاب کنید. بخش Sort On را هم روی Values قرار دهید. بخش Order را هم روی یکی از حالت‌های A to Z یا Z to A قرار دهید. بستگی دارد که می‌خواهید حروف الفبا برعکس چیده شود یا در حالت عادی از الف شروع شود.

در آخر روی  OK بزنید.

مرتب سازی تاریخ شمسی در اکسل با VBA

برای مرتب شدن اعداد در اکسل و همین طور موارد دیگر می‌توانید از کد VBA استفاده کنید. برای این کار روی نام شیت در نوار Sheet Tab راست کلیک کنید. سپس View Code را از منوی باز شده انتخاب کنید.

در پنجره باز شده که Microsoft Visual Basic for Application نام دارد، کد VBA زیر را وارد کنید:

Private Sub Worksheet_Change(ByVal Target As Range)

On Error Resume Next

If Not Intersect(Target, Range(“B:B”)) Is Nothing Then

Range(“B1”).Sort Key1:=Range(“B2”), _

Order1:=xlAscending, Header:=xlYes, _

OrderCustom:=1, MatchCase:=False, _

Orientation:=xlTopToBottom

End If

End Sub

در کد بالا B:B به معنای آن است که ستون B به صورت خودکار مرتب خواهد شد. B1 نیز اولین سلول ستون B است. B2 نیز دومین سلول ستون B است. این حروف را با توجه به ستون و سلول موردنظر خود تغییر دهید. به ورک شیت برگردید. در مثال ما اگر عدد جدیدی را در ستون Price وارد کنید یا هر عدد دیگری را در این بخش تغییر دهید، ستون Price به صورت خودکار مرتب خواهد شد.

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

نوشتن اعداد به ترتیب در اکسل می‌توانید از قابلیت Auto filter استفاده کنید. برای این کار، ردیف‌های ابتدایی ستون‌های موردنظر خود را انتخاب کنید. سپس روی سربرگ Data بزنید. سپس Filter را انتخاب کنید و Auto filter را بزنید.

وقتی روی فلش کوچک کنار سربرگ ستون کلیک کنید، می‌توانید فیلترهای مختلف را مشاهده کنید. هر کدام را که می‌خواهید، انتخاب کنید و روی OK بزنید.

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

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

SORT(array, [sort_index], [sort_order], [by_col])

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

Sort_index: یک عدد صحیح است که نشان می‌دهد کدام ستون یا ردیف را می‌توان طبقه‌بندی کرد. وارد کردن آن اختیاری است. در صورتی که می‌خواهید آن را حذف کنید از شاخص  استفاده کنید.

Sort_order: ترتیب مرتب‌سازی را تعریف می‌کند و وارد کردن آن اختیاری است:

۱ یا حالت پیش فرض که ترتیب صعودی یعنی از کوچک‌ترین به بزرگ‌ترین را نشان می‌دهد.

-۱: ترتیب نزولی یعنی از بزرگ‌ترین به کوچک‌ترین.

By_col: مقداری منطقی است که جهت مرتب‌سازی را نشان می‌دهد و وارد کردن آن اختیاری است:

FALSE یا حالت پیش فرض که مرتب‌سازی بر اساس سطر است.

TRUE: مرتب‌سازی بر اساس ستون.

مثال دستور سورت در اکسل با فرمول برای ترتیب صعودی و نزولی

در این بخش مثال دستور سورت در اکسل با فرمول برای ترتیب صعودی را می‌آوریم. قرار است داده‌های ستون B را مرتب کنیم:

=SORT(A2:B8, 2, 1)

A2:B8: Array

۲: شماره ستونی که می‌خواهیم مرتب شود.

۱: مرتب‌سازی صعودی

از آنجایی که داده‌ها بر اساس ردیف هستند، در نتیجه طبق FALSE تنظیم شده‌اند. کافی است فرمول را در کادر جلوی نام سلول (در اینجا D2) وارد کنید و Enter را بزنید. نتیجه به صورت خودکار به D2:E8 تغییر می‌کند.

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

=SORT(A2:B8, 2, -1)

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

در همین مثال، فرمول مرتب‌سازی بر اساس ستون به شکل زیر می‌شود:

=SORT(B1:H2, 2, 1, TRUE)

فرمول Sort در اکسل برای مرتب‌سازی چند مرحله‌ای

اگر بخواهید از مرتب‌سازی چند مرحله‌ای استفاده کنید، فرمول Sort در اکسل خواهید داشت:

=SORT(A2:C13, {1,3}, {1,-1})

{۱,۳} دو ستون ما هستند که می‌خواهیم مرتب‌سازی را ابتدا بر اساس ستون یک و سپس ستون ۳ انجام دهیم.

{۱,-۱} نیز نحوه چیدمان داده‌های این دو ستون است. ۱ برای ستون اول و -۱ برای ستون سوم است.

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

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

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

SORT(FILTER(array, criteria_range=criteria), [sort_index], [sort_order], [by_col])

در قابلیت FILTER معیارها بر اساس مقادیر تعریف شده، مشخص می‌شوند. این مقادیر نیز به اولین سطح Sort منتقل می‌شوند.

باید این فرمول را در کادر بالای صفحه وارد کنید و Enter را بزنید. با استفاده از این فرمول دیگر نیازی نیست تعداد سلول‌ها را برای چیدمان وارد کنید.

به عنوان مثال، ما می‌خواهیم مقادیر برابر یا بیشتر از ۳۰ را در A2:B9 بر اساس ترتیب صعودی مرتب کنیم. برای این کار، ابتدا سلول موردنظر را انتخاب کنید. ما در اینجا E2 را انتخاب کردیم. حال فرمول Excel SORT به صورت زیر می‌شود:

=SORT(FILTER(A2:B9, B2:B9>=E2), 2)

به جای Array عبارت FILTER را وارد کردیم. بخش Sort_Index را روی ۲ (ستون ۲) قرار دادیم. باقی موارد نیز در حالت پیش فرض هستند.

برای به دست آوردن بزرگ‌ترین و کوچک‌ترین داده‌ها نیز می‌توانید از فرمول زیر استفاده کنید:

INDEX(SORT(…), SEQUENCE(n), {column1_to_return, column2_to_return, …})

n در اینجا تعداد داده‌ها را نشان می‌دهد. مثلا، اگر بخواهید سه داده برتر ستون C را بر اساس عدد به دست بیاورید، باید از فرمول زیر استفاده کنید:

SORT(A2:C13, 3, -1)

حال این فرمول را در آرگومان اول تابع INDEX قرار دهید تا داده‌ها بر اساس بالاترین به کمترین طبقه‌بندی شوند. برای آرگومان دوم (row_num) نیز از قابلیت SEQUENCE استفاده کنید. در اینجا از SEQUENCE(3) استفاده می‌کنیم و آن را مستقیما به صورت {۱;۲;۳} وارد فرمول می‌کنیم.

برای سومین آرگومان (col_num) تعداد ستون‌های مدنظرمان را وارد می‌کنیم. در اینجا ستون B و C مدنظر ما است که باید به صورت {۲,۳} وارد فرمول کنیم. در نهایت، فرمول ما به شکل زیر می‌شود:

=INDEX(SORT(A2:C13, 3, -1), SEQUENCE(3), {2,3})

برای ۳ داده آخر (از کوچک‌ترین به بزرگ‌ترین) باید فرمول خود را برای این مثال، به شکل زیر بنویسیم:

=INDEX(SORT(A2:C13, 3, 1), SEQUENCE(3), {2,3})

مرتب سازی خودکار در اکسل: فرمول طبقه‌بندی بر اساس سلولی خاص

در این شیوه مرتب سازی خودکار در اکسل، ممکن است بخواهید اولین، دومین یا سومین و… داده از فهرست طبقه‌بندی شده را به جای دیگری ببرید. در این حالت باید از ساده‌ترین نسخه INDEX SORT استفاده کنید:

INDEX(SORT(…), n, {column1_to_return, column2_to_return, …})

n موقعیت موردنظر شما است.

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

=INDEX(SORT(A2:C13, 3, -1), F1, {2,3})

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

=INDEX(SORT(A2:C13, 3, 1), I1, {2,3})

مرتب سازی خودکار در اکسل: وارد کردن داده جدا نوشته شده در طبقه‌بندی

وقتی داده جدیدی به ورک شیت اضافه کنید، کل آن به صورت خودکار دوباره مرتب‌سازی می‌شوند و داده جدید نیز در این میان قرار می‌گیرد. در صورتی که داده جدید را خارج از داده‌های طبقه‌بندی شده وارد کنید، این چیدمان خودکار شامل حال آنان نمی‌شود. در مثال زیر، داده‌های سطر ۹ در طبقه‌بندی قرار نگرفته‌اند. فرمول به کار رفته شده در این حالت، =SORT(A2:B8, 1, 1) بوده است. قرا هم بر این بود که داده‌های A2:B8 بر اساس الفبا مرتب شوند.

حال برای تغییر محدوده اعمال چیدمان در جدول، دامنه جدید را به همراه عنوان ستون (A1:B8) انتخاب کنید. سپس Ctrl + T را فشار دهید. وقتی فرمول را می‌نویسید، محدوده موردنظر را با موس انتخاب کنید. نام جدول به صورت خودکار وارد فرمول می‌شود.

=SORT(Table1, 1, 1)

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

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

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

Column C: با استفاده از تابع Rank، چیدمانی برای داده‌ها ایجاد کنید. به عنوان مثال:

=RANK(B2, $B$2:$B$16)

Column D: ستونی ترتیبی (Order) با اعداد ۱ الی ۱۵ ایجاد کنید.

Column E: هر یک از مقادیر ستون Order را با استفاده از ترکیبی از MATCH و INDEX مرتب کنید. به عنوان مثال:

=INDEX($B$2:$B$16, MATCH(D2, $C$2:$C$16, 0))

Column F: تاریخ‌ها را مانند دستور Column E مرتب کنید. برای این کار از دستور TEXT استفاده کنید تا از مرتب شدن آن‌ها در نمودار جلوگیری کنید. به عنوان مثال:

=TEXT(INDEX($A$2:$A$16, MATCH(E2, $C$2:$C$16, 0))|”dd mmmm yyyy”)

نمودار را بر اساس ستون E و F آپدیت کنید. اگر داده‌های همه سلول‌ها در این جا مرتب نشدند، می‌توانید در فرمول از  IFERROR استفاده کنید تا داده‌ها مرتب شوند.

برای جلوگیری از بروز مشکل در هنگام ظاهر شدن همان مقادیر، می‌توانید COUNTIF به تابع Rank اضافه کنید. مثال:

RANK(B2, $B$2:$B$16) + COUNTIF($B$2:B2, B2)-1

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

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

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

#NAME

این ارور زمانی رخ می‌دهد که از نسخه‌های قدیمی‌تر اکسل استفاده کنید. قابلیت SORT تنها در Excel 365 وجود دارد.

#SPILL

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

#VALUE

این ارور برای آرگومان‌های نامعتبر است. در چنین حالتی آرگومان‌های sort_index و sort_order را چک کنید. Sort_index نباید از تعداد ستون‌های ورک شیت بیشتر باشد. sort_order هم باید در حالت ۱ یا -۱ باشد.

#REF

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

نظرات و پیشنهادات شما؟

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