وبلاگ

You are currently viewing چگونه داده‌ها را در اکسل با VBA مرتب‌سازی چندسطحی کنیم؟
تصویر شاخص پست آموزشی درباره مرتب‌سازی چندسطحی داده‌ها در اکسل با VBA.

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

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

در این مقاله یاد می‌گیریم چگونه با استفاده از VBA این نوع مرتب‌سازی چندسطحی (Multi-level Sorting) را به‌صورت خودکار انجام دهیم.

۱. داده‌های نمونه و نیاز به مرتب‌سازی چندسطحی

فرض کنید جدولی از پرداخت‌ها در اختیار داریم:

شعبهتاریخ پرداختمبلغ
تهران1404/01/152,500,000
مشهد1404/01/101,200,000
تهران1404/01/103,000,000
شیراز1404/01/20950,000
مشهد1404/01/122,800,000
تهران1404/01/101,500,000
شیراز1404/01/181,200,000
مشهد1404/01/103,200,000
تهران1404/01/154,000,000
شیراز1404/01/202,200,000

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

۲. روش دستی مرتب‌سازی چندسطحی

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

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

۲.۱. تبدیل محدوده داده‌ها به جدول

  1. از تب Home روی Format as Table کلیک و از منو استایل دلخواه‌تان را انتخاب و کلیک نمایید.
  2. در پنجره باز شده محدوده داده‌ها (شامل ردیف عناوین) را انتخاب و تیک My table has headers را فعال نمایید.
  3. روی کلید OK کلیک نمایید.
منوی کشویی Format as Table در تب Home اکسل
تبدیل یک محدوده سلول به جدول در اکسل با استفاده از گزینه “Format as Table”.
پنجره "Create Table" برای انتخاب محدوده داده‌ها
انتخاب محدوده داده‌ها و تأیید وجود هدر در پنجره “Create Table”.

۲.۲. افزودن سطوح Sort از طریق تب Home منوی Sort & Filter اکسل

بعد از تبدیل به جدول، می‌توانید مرتب‌سازی چندسطحی را به‌صورت زیر انجام دهید:

  1. در تب Home، در بخش Editing روی گزینه Sort & Filter کلیک کنید و سپس Custom Sort… را انتخاب کنید.
  2. در پنجره باز شده:
    • در بخش Column، ستون اول مرتب‌سازی (مثلاً “شعبه”) را انتخاب کنید.
    • در بخش Sort On، مقدار پیش‌فرض Cell Values را تغییر ندهید.
    • در بخش Order ترتیب مرتب‌سازی (A → Z یا Z → A) را مشخص کنید.
  3. روی Add Level کلیک کنید تا یک سطح دیگر اضافه شود.
    • در بخش Column، ستون دوم مرتب‌سازی (مثلاً “تاریخ پرداخت”) را انتخاب کنید.
    • مشابه ستون اول در بخش Sort On مقدار پیش‌فرض Cell Values را تغییر ندهید.
    • در بخش Order، مقدار Oldest to Newest (قدیمی‌ترین تا جدیدترین) یا Newest to Oldest (جدیدترین تا قدیمی‌ترین) را انتخاب کنید.
  4. مشابه دو ستون اول و دوم، ستون سوم (مثلاً “مبلغ”) را نیز به Sort اضافه کنید.
  5. در صورت نیاز، با Add Level می‌توانید ستون‌های بیشتری اضافه کنید.
  6. پس از تکمیل تنظیمات، روی OK کلیک کنید تا داده‌ها مرتب شوند.
تصویر منوی کشویی Sort & Filter در تب Home اکسل
منوی “Sort & Filter” در بخش Editing تب Home اکسل، که گزینه‌هایی برای مرتب‌سازی و فیلتر کردن داده‌ها ارائه می‌دهد.
تصویر پنجره Sort با سه سطح مرتب‌سازی در اکسل
تنظیم سه سطح مرتب‌سازی برای داده‌ها در پنجره “Sort” اکسل.

۳. روش اتوماتیک با VBA

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

۳.۱. تعریف متغیرهای موردنیاز

Dim ws As Worksheet
Set ws = ThisWorkbook.Sheets("Payments")

در خط اول، یک متغیر با نام ws از نوع Worksheet (معادل صفحه اکسل) تعریف شده است.
در خط دوم، مرجع متغیر ws با استفاده از شیء ThisWorkbook و مجموعه Sheets به صفحه‌ای به نام "Payments" اختصاص داده شده است. توجه داشته باشید که اگر صفحه‌ای به نام Payments در اکسل شما نباشد این خط باعث بروز خطای Subscript out of range شود.

تصویر خطای Subscript out of range در VBA اکسل
پنجره پیام خطای “Subscript out of range” در Microsoft Visual Basic for Applications.

۳.۲. پاک‌سازی مرتب‌سازی‌های قبلی

ws.Sort.SortFields.Clear

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

۳.۳. مرتب‌سازی سطح اول

ws.Sort.SortFields.Add Key:=ws.Range("A2:A1000"), Order:=xlAscending

در این خط ستون اول (شعبه) به اولین سطح مرتب‌سازی انتخاب شده و به صورت صعودی (الفبایی) مرتب می‌شود. توجه داشته باشید که مرتب‌سازی روی محدوده A2:A1000 اعمال خواهد شد.

۳.۴. مرتب‌سازی سطح دوم

ws.Sort.SortFields.Add Key:=ws.Range("B2:B1000"), Order:=xlAscending

اینجا ستون تاریخ پرداخت به عنوان معیار دوم اضافه می‌شود و ترتیب آن نیز صعودی است (از قدیمی به جدید).

۳.۵. مرتب‌سازی سطح سوم

ws.Sort.SortFields.Add Key:=ws.Range("C2:C1000"), Order:=xlAscending

ستون مبلغ به عنوان معیار سوم مرتب‌سازی افزوده می‌شود.

۳.۶. تعیین محدوده داده‌ها

ws.Sort.SetRange Rng:=ws.Range("A1:C1000")

در این مرحله محدوده‌ای که مرتب‌سازی باید روی آن اعمال شود را مشخص می‌کنیم. در این کد با استفاده از متد SetRange شیء Sort محدوده A1:C1000 را بعنوان محدوده مرتب‌سازی انتخاب کردیم.

۳.۷. مشخص کردن ردیف اول بعنوان ردیف عنوان

ws.Sort.Header = xlYes

در این مرحله با استفاده از ویژگی Header شیء Sort مشخص کردیم که ردیف اول داده‌ها حاوی اطلاعات سربرگ است.

۳.۸. اعمال مرتب‌سازی

ws.Sort.Apply

مرتب‌سازی با استفاده از متد Apply اعمال می‌شود.

۳.۹. ماکروی کامل

Sub MultiLevelSort() Dim ws As Worksheet Set ws = ThisWorkbook.Sheets("Payments") ws.Sort.SortFields.Clear ws.Sort.SortFields.Add Key:=ws.Range("A2:A1000"), Order:=xlAscending ws.Sort.SortFields.Add Key:=ws.Range("B2:B1000"), Order:=xlAscending ws.Sort.SortFields.Add Key:=ws.Range("C2:C1000"), Order:=xlAscending ws.Sort.SetRange ws.Range("A1:C1000") ws.Sort.Header = xlYes ws.Sort.Apply
End Sub

برای کامل کردن کد و ایجاد ماکرو کل کد را داخل یک روال Sub با عنوان MultiLevelSort قرار می‌دهیم و در یک ماژول استاندارد ذخیره می‌نماییم.

۳.۹.۱. مراحل ایجاد ماکرو در VBA

  1. وارد منوی Developer شوید و گزینه Visual Basic را انتخاب کنید. اطلاعات بیشتر درباره نحوه فعال کردن منوی Developer را می‌توانید در صفحه چگونه سربرگ توسعه دهنده را در اکسل فعال نمایم؟ مشاهده نمایید.
  2. از منوی Insert، گزینه Module را انتخاب کنید تا یک ماژول جدید ایجاد شود. آموزش تصویری نحوه افزودن ماژول استاندارد در VBA را می‌توانید در صفحه ماژول در VBA مشاهده نمایید.
منوی Insert برای درج یک ماژول جدید در VBA اکسل
درج یک ماژول استاندارد جدید در محیط ویرایشگر VBA با استفاده از منوی Insert.

۴. استفاده از xlAscending و xlDescending

در ماکروی بالا از xlAscending (مرتب‌سازی صعودی) استفاده کردیم.
اگر بخواهیم مرتب‌سازی نزولی داشته باشیم، می‌توانیم همان ماکرو را با نام دیگری ذخیره کرده و xlDescending را جایگزین کنیم.

اما راه‌حل بهتر تعریف یک تابع خصوصی (Private Subroutine) است که ترتیب مرتب‌سازی را به‌صورت آرگومان دریافت کند.

۴.۱. تابع کمکی مرتب‌سازی

Private Sub MultiLevelSortCustom(ByVal sortOrder As XlSortOrder) Dim ws As Worksheet Set ws = ThisWorkbook.Sheets("Payments") ws.Sort.SortFields.Clear ws.Sort.SortFields.Add Key:=ws.Range("A2:A1000"), Order:=sortOrder ws.Sort.SortFields.Add Key:=ws.Range("B2:B1000"), Order:=sortOrder ws.Sort.SortFields.Add Key:=ws.Range("C2:C1000"), Order:=sortOrder ws.Sort.SetRange ws.Range("A1:C1000") ws.Sort.Header = xlYes ws.Sort.Apply
End Sub

سابروتین MultiLevelSortCustom یک تابع کمکی است که به لطف آرگومان sortOrder آن می‌توانیم براحتی صعودی/نزولی بودن مرتب‌سازی چندسطحی را تغییر دهیم. علت استفاده از Private در روال MultiLevelSortCustom این است که نمی‌خواهیم که این روال در لیست ماکروهای اکسل ظاهر شود.

۴.۲. ماکروی مرتب‌سازی چندسطحی صعودی

Sub MultiLevelSortAsc() MultiLevelSortCustom xlAscending
End Sub

به لطف تابع کمکی MultiLevelSortCustom ماکروی صعودی را در یک خط ایجاد کردیم.

۴.۳. ماکروی مرتب‌سازی چندسطحی نزولی

Sub MultiLevelSortDesc() MultiLevelSortCustom xlDescending
End Sub

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

۵. اجرای ماکرو با دکمه در شیت

برای راحتی کار، می‌توانید دو دکمه در صفحه اکسل قرار دهید:

  1. به تب Developer بروید و از بخش Insert → Form Controls یک کنترل Button به شیت اضافه کنید.
  2. ماکروی MultiLevelSortAsc را به دکمه اول و ماکروی MultiLevelSortDesc را به دکمه دوم اختصاص دهید.
  3. با این کار کاربر تنها با یک کلیک می‌تواند داده‌ها را مرتب کند.
نمای افزودن دکمه از تب Developer در اکسل
اضافه کردن یک دکمه کنترل جدید برای اجرای ماکروی VBA از طریق تب Developer اکسل.

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

در ویدیوی آموزشی زیر می‌توانید مراحل این آموزش را از ابتدا تا انتها مشاهده نمایید.

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

۷. دانلود فایل اکسل آموزش

فایل اکسل با قابلیت مرتب‌سازی چندسطحی VBAدریافت

بیشتر بخوانید

برچسب ها: ,,,,,,,,,,,,,,,,,

دیدگاهتان را بنویسید