تابع SORTBY در اکسل — معرفی و کاربردها

تابع SORTBY در اکسل ابزاری قدرتمند برای مرتب‌سازی داینامیک (dynamic) داده‌ها بر اساس یک یا چند ستون/آرایه است. این تابع مخصوص نسخه‌های جدید اکسل (Microsoft 365، Excel 2021 و Excel Online) است و خروجی آن به صورت یک آرایه سرریز (spill) در سلول‌های مجاور قرار می‌گیرد.

سینتکس و پارامترها

سینتکس کلی:

=SORTBY(array, by_array1, [sort_order1], [by_array2, sort_order2], ...)

توضیحات پارامترها:

• array: محدوده‌ای که می‌خواهید مرتب شود (مثلاً A2:C10).
• by_array1: ستون یا آرایه‌ای که بر اساس آن مرتب‌سازی انجام می‌شود.
• sort_order: عدد 1 برای مرتب‌سازی صعودی (Ascending) و -1 برای نزولی (Descending). اگر وارد نشود، پیش‌فرض 1 است.
• می‌توانید چندین جفت by_array و sort_order برای مرتب‌سازی چندمعیاره وارد کنید.

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

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

=SORTBY(A2:C4, B2:B4, -1)

این فرمول داده‌های موجود در A2:C4 را بر اساس B2:B4 به ترتیب نزولی مرتب می‌کند و نتیجه به صورت سرریز (spill) در پایین/سمت راست ظاهر می‌شود.

مرتب‌سازی چندمعیاره (چند ستون)

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

=SORTBY(A2:C10, B2:B10, -1, A2:A10, 1)

در اینجا ابتدا B2:B10 (نمره) به صورت نزولی مرتب می‌شود و در صورت تساوی، بر اساس A2:A10 (نام) به صورت صعودی مرتب خواهد شد.

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

می‌توانید از عبارت‌های محاسباتی به عنوان by_array استفاده کنید؛ مثلاً مرتب‌سازی بر اساس ماه یک تاریخ، یا بر اساس حاصل جمع شرطی.

=SORTBY(A2:C50, MONTH(C2:C50), 1)

این فرمول جدول A2:C50 را بر اساس ماه ستون تاریخ (فرضاً C2:C50) به صورت صعودی مرتب می‌کند. by_array می‌تواند هر آرایه‌ای باشد که طولش با array اصلی برابر است.

مثال پیشرفته: مرتب‌سازی آیتم‌های یکتا بر اساس مجموع فروش

فرض کنید ستون A لیست محصولات و ستون B مقادیر فروش است؛ می‌خواهیم فهرست محصولات یکتا را بر اساس مجموع فروش نزولی مرتب کنیم:

=SORTBY(UNIQUE(A2:A100), SUMIF(A2:A100, UNIQUE(A2:A100), B2:B100), -1)

توضیح: UNIQUE(A2:A100) فهرست محصولات یکتا را می‌سازد. SUMIF برای هر محصول یکتا مجموع فروش را محاسبه می‌کند و SORTBY آن لیست یکتا را بر اساس آن مقادیر به صورت نزولی مرتب می‌کند. این روش تنها در اکسل با پشتیبانی از توابع آرایه داینامیک کاربرد دارد.

نکات فنی، خطاها و محدودیت‌ها

• طول آرایه‌های by_array باید با طول آرایه اصلی (array) مطابقت داشته باشد؛ در غیر این صورت #VALUE! ایجاد می‌شود.
• sort_order فقط مقادیر 1 یا -1 را می‌پذیرد؛ مقادیر دیگر منجر به خطا می‌شوند.
• SORTBY تابعی غیروابسته به حالت volatile است — یعنی به‌طور پیش‌فرض موجب بازخوانی مکرر نمی‌شود — اما استفاده ترکیبی با توابع volatile ممکن است بر کارایی تأثیر بگذارد.
• پایداری مرتب‌سازی (stable sort) در مستندات صراحت کامل ندارد؛ در موارد حساس به ترتیب، بهتر است کلید ثانویه را مشخص کنید تا رفتار قطعی داشته باشید.
• این تابع فقط در نسخه‌های جدید اکسل موجود است؛ کاربران اکسل قدیمی باید از ستون‌های کمکی، توابع INDEX/MATCH یا Power Query استفاده کنند.

نکات بهینه‌سازی و بهترین روش‌ها

• برای مجموعه‌های خیلی بزرگ، بهتر است محاسبات سنگین (مثل SUMIF/ARRAY) را در ستون‌های کمکی انجام داده و سپس از SORTBY روی آن ستون‌ها استفاده کنید تا عملکرد بهبود یابد.
• هنگام استفاده با FILTER، ترکیب آن‌ها بسیار مفید است: ابتدا فیلتر کنید سپس مرتب؛ یا برعکس، بسته به نیاز.
• برای حفظ فرمت‌ها یا جدول‌سازی، بهتر است از Table (Ctrl+T) استفاده کنید و محدوده‌ها را با نام ستون ارجاع دهید.

ترکیب با توابع دیگر — مثال FILTER + SORTBY

=SORTBY(FILTER(A2:C100, D2:D100="فعال"), B2:B100, -1)

توضیح: ابتدا با FILTER فقط ردیف‌هایی که ستون D مقدار “فعال” دارند انتخاب می‌شوند؛ سپس آن نتایج بر اساس ستون B به صورت نزولی مرتب می‌شود. این ترکیب برای داشبوردها و گزارش‌های پویا بسیار مفید است.

راهکار برای کاربران اکسل قدیمی

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

• استفاده از ستون کمکی: یک ستون جدید بسازید و معیار مرتب‌سازی را در آن قرار دهید، سپس ستون‌ها را با ابزار Sort از تب Data مرتب کنید.
• Power Query: داده‌ها را به Power Query منتقل و در آنجا مرتب‌سازی چندمعیاره انجام دهید و نتیجه را در شیت بارگزاری کنید.
• فرمول‌های پیچیده با INDEX/MATCH و RANK می‌توانند جایگزین باشند اما پیاده‌سازی طولانی‌تر و پرخطاتر است.

جمع‌بندی

SORTBY یک تابع انعطاف‌پذیر و قدرتمند برای مرتب‌سازی داینامیک در اکسل است و وقتی با توابع دیگری مثل UNIQUE، FILTER یا SUMIF ترکیب شود، می‌تواند گزارش‌ها و داشبوردهای بسیار تعاملی و خودبه‌خودی تولید کند. دقت کنید که سازگاری نسخه‌ها، تطابق اندازه آرایه‌ها و انتخاب کلیدهای ثانویه از نکات کلیدی موفقیت در استفاده از این تابع هستند.

لطفاً از کمبود ها و مشکلات این محتوا برای ما بنویسید

ارسال