آموزش تابع SORT در اکسل ۲۰۲۱

در این آموزش نحوه استفاده از تابع SORT در اکسل 2021 برای مرتب‌سازی پویا آرایه‌های داده را نشان می‌دهیم. در آموزش تابع SORT در اکسل ۲۰۲۱ فرمولی برای مرتب‌سازی بر اساس حروف الفبا در اکسل، مرتب کردن اعداد به ترتیب صعودی یا نزولی، مرتب‌سازی بر اساس ستون‌های متعدد و موارد دیگر را یاد خواهید گرفت.

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

ساختار تابع SORT اکسل

تابع SORT در اکسل محتویات یک آرایه یا محدوده را بر اساس ستون ها یا ردیف ها به ترتیب صعودی یا نزولی مرتب می کند.

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

سینتکس یا ساختار تابع SORT به صورت زیر است:

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

• آرایه (الزامی) : آرایه ای از مقادیر یا محدوده ای از سلول ها برای مرتب سازی مورد نظر است. اینها می توانند هر مقداری از جمله متن، اعداد، تاریخ، زمان و غیره باشند.
• Sort_index (اختیاری) : یک عدد صحیح میباشد که نشان می دهد کدام ستون یا ردیف باید مرتب شود. در صورت حذف، از شاخص پیش فرض 1 استفاده می شود.
• Sort_order (اختیاری) : ترتیب مرتب سازی را مشخص می کند:

1. 1 یا حذف شده (پیش‌فرض) : ترتیب صعودی، یعنی از کوچکترین به بزرگترین
2. 1- : ترتیب نزولی، یعنی از بزرگترین به کوچکترین

• By_col (اختیاری) : یک مقدار منطقی که جهت مرتب سازی را نشان می دهد:

1. FALSE یا حذف شده (پیش فرض) : مرتب سازی بر اساس ردیف. شما بیشتر اوقات از این گزینه استفاده خواهید کرد.
2. TRUE : مرتب سازی بر اساس ستون. اگر داده های شما به صورت افقی در ستون هایی مانند این مثال سازماندهی شده اند از این گزینه استفاده کنید.

اگر میخواهید بیشتر با مباحث حسابداری آشنا شوید و درخواست آموزش خاصی در حوزه حسابداری به صورت رایگان را دارید میتوانید در صفحه آموزش حسابداری رایگان فیلم های آموزشی رایگان ما را مشاهده کنید. در صورت تمایل میتوانید مبحث مورد نظرتان را برای قرار گرفتن در آموزش های رایگان در بخش نظرات ذکر کنید.

عملکرد SORT اکسل

SORT یک تابع آرایه پویا جدید است و به همین ترتیب دارای چند ویژگی است که باید از آنها آگاه باشید:

در حال حاضر تابع SORT فقط در مایکروسافت 365 و اکسل 2021 موجود است. اکسل 2019، اکسل 2016 از فرمول های آرایه پویا پشتیبانی نمی کنند، بنابراین تابع SORT در این نسخه ها در دسترس نیست.

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

بنابراین، مطمئن شوید که همیشه به اندازه کافی سلول خالی در پایین و یا سمت راست سلولی که فرمول را وارد می کنید دارید، در غیر این صورت یک خطای #SPILL رخ می دهد.

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

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

فرمول تابع SORT اکسل

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

فرض کنید داده های شما بر اساس حروف الفبا مطابق تصویر زیر مرتب شده اند. شما به دنبال مرتب کردن اعداد در ستون B بدون شکستن یا مخلوط کردن داده ها هستید.

• فرمول مرتب سازی به ترتیب صعودی

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

SORT(A2:B8, 2, 1)=

• A2:B8 : آرایه منبع است
• 2 : شماره ستونی است که باید بر اساس آن مرتب شود
• 1 : ترتیب مرتب سازی صعودی است
• از آنجایی که داده‌های ما در ردیف‌هایی سازمان‌دهی شده‌اند، آخرین آرگومان را می‌توان به صورت پیش‌فرض FALSE حذف کرد – مرتب‌سازی بر اساس ردیف‌ها.

فقط فرمول را در هر سلول خالی (در مورد ما D2) وارد کنید، Enter را فشار دهید، و نتایج به طور خودکار به D2:E8 ریخته می شود.

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

برای مرتب‌سازی داده‌ها به صورت نزولی، یعنی از بزرگ‌ترین به کوچک‌ترین، آرگومان sort_order را به صورت 1- تنظیم کنید:

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

فرمول را در سلول سمت چپ بالای محدوده مقصد وارد کنید و این نتیجه را دریافت خواهید کرد:

به روشی مشابه، می توانید مقادیر متن را به ترتیب حروف الفبا از A به Z یا از Z به A مرتب کنید و از این روش برای مرتب كردن بر اساس حروف الفبا در اکسل استفاده کنید.

مثال مرتب سازی داده ها در اکسل با تابع sort

مثال های زیر چند کاربرد معمولی از تابع SORT در اکسل 2021 را نشان می دهد.

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

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

برای مرتب سازی بر اساس ستون در اکسل، آرگومان by_col را روی TRUE قرار دهید. در این حالت sort_index یک سطر را نشان می دهد نه یک ستون.

به عنوان مثال، برای مرتب کردن داده های زیر بر اساس Qty. از بالاترین به پایین ترین، از این فرمول استفاده کنید:

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

• B1:H2 : منبع داده برای مرتب سازی است.
• 2 : شاخص مرتب سازی است، زیرا ما اعداد را در ردیف دوم مرتب می کنیم.
• 1- : ترتیب مرتب سازی نزولی را نشان می دهد.
• TRUE : به معنای مرتب کردن ستون ها است نه ردیف ها.

مرتب سازی چندین ستون با sort اکسل 2021

در این قسمت میخواهیم مرتب سازی چندین ستون با sort اکسل 2021 به ترتیب مختلف (مرتب سازی چند سطحی) را به شما آموزش دهیم.

هنگام کار با مدل های داده پیچیده، ممکن است اغلب به مرتب سازی چند سطحی نیاز داشته باشید.

سوالی که مطرح میشود این است که آیا با فرمول می توان این کار را انجام داد؟ بله، به راحتی!

کاری که شما انجام می دهید این است که ثابت های آرایه ای برای آرگومان های sort_index و sort_order فراهم کنید.

به عنوان مثال، برای مرتب کردن داده های زیر ابتدا بر اساس منطقه (ستون A) از A به Z و سپس بر اساس Qty. (ستون C) از کوچکترین به بزرگترین، آرگومان های زیر را تنظیم کنید:

• آرایه داده های A2:C13 است.
• Sort_index : ثابت آرایه {1,3} است، زیرا ابتدا بر اساس منطقه (ستون اول) و سپس بر اساس Qty مرتب می‌کنیم (ستون سوم).
• Sort_order : ثابت آرایه {1-,1} است، زیرا ستون 1 باید به ترتیب صعودی و ستون 3 به ترتیب نزولی مرتب شوند.
• By_col : حذف شده است زیرا ما ردیف ها را مرتب می کنیم که پیش فرض است.

با کنار هم قرار دادن آرگومان ها به این فرمول می رسیم:

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

در این مثال مقادیر متن در ستون اول بر اساس حروف الفبا و اعداد در ستون سوم از بزرگترین به کوچکترین مرتب شده اند:

ترکیب sort و filter در اکسل

در صورتی که به دنبال فیلتر کردن داده ها با برخی معیارها و مرتب کردن خروجی ها هستید، از توابع SORT و FILTER با هم استفاده کنید:

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

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

بهترین چیز در مورد این فرمول این است که نتایج را نیز به عنوان یک محدوده پویا خروجی میدهد، بدون اینکه لازم باشد Ctrl + Shift + Enter را فشار دهید باید آن را کپی کنید.

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

به عنوان مثال، می‌خواهیم مواردی با مقدار مساوی یا بیشتر از 30 (>=30) را از داده‌های منبع در A2:B9 استخراج کنیم و نتایج را به ترتیب صعودی مرتب کنیم.

برای این کار ابتدا شرط را مثلاً در سلول E2 همانطور که در تصویر زیر نشان داده شده است تنظیم می کنیم و سپس، فرمول Excel SORT را به این صورت بسازید:

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

به غیر از آرایه تولید شده توسط تابع FILTER، ما فقط آرگومان sort_index (ستون 2) را مشخص می کنیم.

دو آرگومان باقیمانده حذف شده‌اند، زیرا پیش‌فرض‌ها دقیقاً همانطور که نیاز داریم کار می‌کنند (مرتب‌سازی صعودی، بر اساس ردیف).

استخراج ماکزیمم و مینیمم و اعمال تابع sort در اکسل 2021

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

برای این کار در حالت ایده آل، انتخاب کنید که کدام ستون ها در نتایج گنجانده شوند. مشکل به نظر می رسد؟ نه با توابع جدید آرایه پویا!

در اینجا یک فرمول عمومی وجود دارد:

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

در اینجا n تعداد مقادیری است که می خواهید برگردانید.

از مجموعه داده های زیر، فرض کنید می خواهید از یک لیست 3 عضو برتر را بر اساس اعداد ستون C دریافت کنید.

برای انجام این کار، ابتدا آرایه A2:C13 را بر اساس ستون 3 به ترتیب نزولی مرتب کنید:

SORT(A2:C13, 3, -1)

و سپس، فرمول بالا را در آرگومان اول (آرایه) تابع INDEX قرار دهید تا آرایه از بالاترین به کوچکترین مرتب شده باشد.

برای آرگومان دوم (row_num)، که نشان می دهد چند ردیف باید برگردانده شود، با استفاده از تابع SEQUENCE اعداد ترتیبی مورد نیاز را ایجاد کنید.

از آنجایی که ما به 3 مقدار بالا نیاز داریم، از SEQUENCE(3) استفاده می کنیم، که همان ارائه ثابت آرایه عمودی {1;2;3} به طور مستقیم در فرمول است.

برای آرگومان سوم (col_num)، که تعیین می‌کند چند ستون باید برگردانده شود، اعداد ستون‌ها را در قالب یک آرایه ثابت افقی ارائه کنید. ما می خواهیم ستون های B و C را برگردانیم، بنابراین از آرایه {2،3} استفاده می کنیم.

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

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

نتیجه به صورت زیر خواهد بود :

برای برگرداندن 3 مقدار پایین، به سادگی داده های اصلی را از کوچکترین به بزرگترین مرتب کنید. برای این منظور، آرگومان sort_order را از -1 به 1 تغییر دهید:

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

برگشت مرتب شده یک مقدار در position خاص

از زاویه دیگری نگاه کنید، اگر بخواهید فقط یک موقعیت مرتب سازی خاص را برگردانید، چه؟ بگویید، فقط رکورد 1، فقط 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})=

جایی که A2:C13 داده منبع است، F1 موقعیت از بالا، I1 موقعیت از پایین، و {2،3} ستون هایی هستند که باید برگردانده شوند.

مرتب سازی آرایه برای گسترش خودکار

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

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

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

برای مشاهده نحوه عملکرد آن در عمل، لطفاً به مثال زیر توجه کنید.

فرض کنید از فرمول Excel SORT زیر برای مرتب کردن مقادیر در محدوده A2:B8 به ترتیب حروف الفبا استفاده می کنید:

SORT(A2:B8, 1, 1)=

سپس، یک ورودی جدید را در ردیف 9 وارد می‌کنید و از اینکه می‌بینید ورودی جدید اضافه‌شده خارج از محدوده ریخته شده است، ناامید می‌شوید:

حالاباید محدوده منبع را به جدول تبدیل کنید. برای این کار، به سادگی محدوده خود شامل سرصفحه های ستون (A1:B8) را انتخاب کنید و Ctrl + T را فشار دهید. هنگام ساخت فرمول خود، محدوده منبع را با استفاده از ماوس انتخاب کنید، و نام جدول به طور خودکار در فرمول درج می شود (به این نام می گویند. یک مرجع ساختاریافته):

SORT(Table1, 1, 1)=

هنگامی که یک ورودی جدید را درست زیر آخرین ردیف تایپ می کنید، جدول به طور خودکار گسترش می یابد و داده های جدید در محدوده ریخته شدن فرمول SORT گنجانده می شود:

خطا در عملکرد Excel SORT

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

• علت خطای #NAME : نسخه قدیمی اکسل

SORT یک تابع جدید است و فقط در Excel 365 و Excel 2021 کار می کند. در نسخه های قدیمی که این تابع پشتیبانی نمی شود، یک #NAME خطا رخ می دهد.

• خطای #VALUE : آرگومان‌های نامعتبر

هر زمان که با یک #VALUE مواجه شدید! خطا، آرگومان های sort_index و sort_order را بررسی کنید. Sort_index نباید از تعداد ستون های آرایه تجاوز کند و sort_order باید 1 (صعودی) یا -1 (نزولی) باشد.

• خطای #REF : محدودیت منبع

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

Auto sort در اکسل

برای Auto sort در اکسل یا به عبارتی برای مرتب سازی داده ها به صورت اتوماتیک باید به زبان برنامه نویسی vba این کار را انجام دهید و با ماکرو نویسی در اکسل آشنایی داشته باشید.(مطالعه بیشتر: نحوه ذخیره ماکرو در اکسل)

شماباید رد محیط وی بی ای (کلیدمیانبر Alt + F۱۱) رافشار دهید سپس بر روی Worksheet کلیک راست کرده و View Code را انتخاب کنید.

برای Auto sort در اکسل باید کد زیر را در پنجره باز شده کپی پیست کنید.

 On Error Resume Next

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

  _,Range(“F۱”).Sort Key۱:=Range(“F۲”)

 _,Order۱:=xlAscending, Header:=xlYes

_,OrderCustom:=۱, MatchCase:=False

 Orientation:=xlTopToBottom

 End If

End Sub

درنهایت Auto sort در اکسل برای ستون F اتفاق میفتد و داده ها به صورت صعودی مرتب سازی میشوند.

حذف sort در اکسل

یکی از سوالات پرتکرار کاربران ما این است که برای حذف sort در اکسل باید چیکار کنیم؟

در واقع راهی برای حذف sort در اکسل وجود ندارد و نمیتوان داده ها را به صورت نامرتب اولیه سازماندهی کرد.

جمعبندی

در این مقاله ما در شرکت حسابداری آریا تهران تلاش کردیم آموزش تابع SORT در اکسل ۲۰۲۱ را به صورت کامل با ذکر مثال هایی کاربردی توضیح دهیم تا شما بتوانید به شما چگونه ردیف را در اکسل مرتب کنیم پاسخ جامعی بدهید.

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

مقالات مرتبط:

تابع CHOOSE در اکسل

تابع ERROR.TYPE در اکسل

ایجاد آلارم چشمک زن در اکسل

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

سوالات متداول