Як сортувати дані в Excel за допомогою VBA (Покроковий посібник)

У Excel вже є кілька способів швидкої сортування даних.

Ви можете легко сортувати набір даних за допомогою значків сортування на стрічці або в діалоговому вікні сортування.

Тоді чому вам потрібно знати, як це зробити за допомогою VBA?

Знання того, як сортувати дані за допомогою VBA, може бути корисним, якщо вони включені як частина вашого коду. Наприклад, припустимо, що ви щодня/щотижня отримуєте набір даних, який вам потрібно відформатувати та відсортувати у певному порядку.

Ви можете створити макрос, щоб зробити все це за вас одним натисканням кнопки. Це заощадить вам багато часу та зусиль щоразу, коли ви це робите.

Крім того, якщо ви створюєте інформаційні панелі Excel, ви можете вивести можливості сортування Excel на новий рівень, дозволивши користувачеві сортувати дані, просто двічі клацнувши на заголовку (як показано нижче).

Про те, як це створити, я розповім далі в цьому підручнику. Давайте спочатку швидко зрозуміємо основи.

Розуміння методу Range.Sort у Excel VBA

При сортуванні за допомогою VBA вам потрібно використовувати метод Range.Sort у своєму коді.

"Діапазон" - це дані, які ви намагаєтесь відсортувати. Наприклад, якщо ви сортуєте дані в A1: A10, тоді "Діапазон" буде діапазоном ("A1: A10").

Ви також можете створити іменований діапазон і використовувати його замість посилань на клітинки. Наприклад, якщо я створю іменований діапазон "DataRange" для клітинок A1: A10, я також можу використовувати діапазон ("DataRange")

За допомогою методу сортування потрібно надати деяку додаткову інформацію через параметри. Нижче наведені основні параметри, які вам потрібно знати:

• Ключ - тут потрібно вказати стовпець, який потрібно відсортувати. Наприклад, якщо ви хочете відсортувати стовпець A, вам потрібно використати ключ: = Діапазон (“A1”)
• Замовлення - тут ви вказуєте, чи потрібно сортувати за зростанням чи спаданням. Наприклад, якщо ви хочете сортування за зростанням, ви будете використовувати Порядок: = xlAscending
• Заголовок - тут ви вказуєте, чи є у наборі даних заголовки чи ні. Якщо у ньому є заголовки, сортування починається з другого рядка набору даних, інакше воно починається з першого рядка. Щоб вказати, що ваші дані мають заголовки, ви будете використовувати заголовок: = xlТак

Хоча в більшості випадків цих трьох достатньо, детальніше про параметри можна прочитати в цій статті.

Тепер давайте подивимося, як використовувати метод Range.Sort у VBA для сортування даних у Excel.

Сортування однієї колонки без заголовка

Припустимо, у вас є один стовпець без заголовка (як показано нижче).

Ви можете скористатися наведеним нижче кодом, щоб відсортувати його за зростанням.

Sub SortDataWithoutHeader () Діапазон ("A1: A12"). Клавіша сортування1: = Діапазон ("A1"), Порядок1: = xlЗростання, Заголовок: = xlНі кінець

Зауважте, що я вказав діапазон даних вручну як Діапазон (“A1: A12”).

Якщо дані можуть бути змінені, а значення можуть бути додані/видалені, ви можете скористатися наведеним нижче кодом, який автоматично коригується на основі заповнених клітинок у наборі даних.

Sub SortDataWithoutHeader () Діапазон ("A1", Діапазон ("A1"). Кінець (xlDown)). Клавіша сортування1: = Діапазон ("A1"), Порядок1: = xlAscending, Заголовок: = xlNo End Sub

Зауважте, що замість діапазону (“A1: A12”) я використав діапазон (“A1”, діапазон (“A1”). Кінець (xlDown)).

Це дозволить перевірити останню послідовно заповнену клітинку у стовпці та включити її до сортування. Якщо є пробіли, вони розглядатимуть дані лише до першої порожньої клітинки.

Ви також можете створити іменований діапазон і використовувати цей іменований діапазон замість посилань на клітинки. Наприклад, якщо іменованим діапазоном є DataSet, ваш код тепер буде таким, як показано нижче.

Додатковий діапазон SortDataWithoutHeader ()

Тепер дозвольте мені швидко пояснити параметри, використані у наведених вище прикладах:

• Key1: = Діапазон (“A1”) - Вказується A1, щоб код знав, який стовпець сортувати.
• Порядок1: = xlAscending - Вказується порядок xlAscending. Якщо ви хочете, щоб воно було в порядку спадання, використовуйте xlDescending.
• Заголовок: = xlNo - вказано, що заголовків немає. Це також значення за замовчуванням. Тому навіть якщо ви пропустіть це, ваші дані будуть відсортовані, враховуючи, що вони не мають заголовків.

Цікаво, де розмістити цей код VBA і як запустити макрос? Прочитайте цей підручник!

Сортування однієї колонки із заголовком

У попередньому прикладі набір даних не мав заголовка.

Коли ваші дані мають заголовки, вам потрібно вказати це в коді, щоб сортування могло розпочатися з другого рядка набору даних.

Припустимо, у вас є набір даних, як показано нижче:

Нижче наведено код, який буде сортувати дані у порядку зменшення залежно від продажів магазинів.

Sub SortDataWithHeader () Діапазон ("DataRange"). Ключ сортування1: = Діапазон ("C1"), Порядок1: = xlЗниження кінцевого підрозділу

Зауважте, що я створив іменований діапазон - "DataRange" і використав цей іменований діапазон у коді.

Сортування кількох стовпців із заголовками

До цього часу в цьому підручнику ми бачили, як сортувати один стовпець (із заголовками та без них).

Що робити, якщо ви хочете сортувати на основі кількох стовпців.

Наприклад, у наведеному нижче наборі даних, що робити, якщо я спочатку сортую за кодом стану, а потім за сховищем.

Ось код, який буде сортувати кілька стовпців за один раз.

Sub SortMultipleColumns () З ActiveSheet.Sort .SortFields.Add Key: = Діапазон ("A1"), Порядок: = xlAscending .SortFields.Add Key: = Діапазон ("B1"), Порядок: = xlAscending .SetRange Діапазон ("A1 : C13 "). Голова = xlТак. Застосуйте End with End Sub

Нижче наведено результат, який ви отримаєте.

У наведеному вище прикладі дані спочатку сортуються за кодом стану (стовпець А). Потім у даних коду стану вони знову сортуються за сховищем (стовпець B). Цей порядок визначається кодом, у якому ви його згадуєте.

Сортування даних за допомогою подвійного клацання на заголовку

Якщо ви створюєте інформаційну панель або хочете, щоб у ваших звітах було зручніше користуватися, ви можете написати код VBA, який буде сортувати дані, коли ви двічі клацнете на заголовках.

Щось, як показано нижче:

Нижче наведено код, який дозволить вам це зробити:

Private Sub Worksheet_BeforeDoubleClick (ByVal Target As Range, Cancel As Boolean) Dim KeyRange As Range Dim ColumnCount As Integer ColumnCount = Range ("DataRange"). Columns.Count Cancel = False Якщо Target.Row = 1 І Target.Column <= ColumnC Тоді Скасувати = Діапазон True True KeyRange = Діапазон (Target.Address) Діапазон ("DataRange"). Клавіша сортування1: = KeyRange, Заголовок: = xlТак, якщо закінчити Sub

Зауважте, що я створив іменований діапазон (“DataRange”) і використав його у коді замість використання посилань на комірки.

Як тільки ви двічі клацнете на будь-якому із заголовків, код відключає звичайну функцію подвійного клацання (тобто для переходу в режим редагування) і використовує цю клітинку як ключ під час сортування даних.

Також зверніть увагу, що наразі цей код буде сортувати всі стовпці лише за зростанням.

Зауважте, що подвійне клацання-це тригер, що дозволяє програмі Excel запускати вказаний код. Ці тригери, такі як подвійне клацання миші, відкриття книги, додавання нового аркуша, зміна комірки тощо, називаються подіями і можуть використовуватися для запуску макросів у Excel. Детальніше про події Excel VBA можна прочитати тут.

Де розмістити цей код?

Вам потрібно вставити цей код у вікно коду аркуша, у якому ви хочете функцію сортування подвійного клацання.

Зробити це:

• Клацніть правою кнопкою миші на вкладці аркуша.
• Натисніть Переглянути код.
• Вставте код у вікно коду аркуша, в якому знаходяться ваші дані.

Що робити, якщо ви хочете відсортувати перші два стовпці ("Стан" та "Зберігати") у порядку зростання, але стовпці "Продажі" за спаданням.

Ось код, який це зробить:

Private Sub Worksheet_BeforeDoubleClick (ByVal Target As Range, Cancel As Boolean) Dim KeyRange As Range Dim ColumnCount As Integer ColumnCount = Range ("DataRange"). Columns.Count Cancel = False Якщо Target.Row = 1 І Target.Column <= ColumnC Тоді Скасувати = Дійсно встановити KeyRange = Діапазон (Target.Address) Якщо Target.Value = "Продажі", то SortOrder = xlЗнижувальний Інший SortOrder = xlAscending End If Range ("DataRange"). Клавіша сортування1: = KeyRange, Заголовок: = xlYes, Order1: = SortOrder End, якщо End Sub

У наведеному вище коді він перевіряє, чи є клітинка, на яку двічі клацається, заголовок Sales чи ні. Якщо так, то він присвоює значення xlDescending змінній SortOrder, інакше зробить це xlAscending.

Тепер візьмемо цю ступінь далі і покажемо візуальний маркер (стрілка та кольорова клітинка) у заголовку, коли він буде відсортований.

Щось, як показано нижче:

Щоб отримати це, я додав новий аркуш і вніс до нього такі зміни (ви можете завантажити файл прикладу і слідувати за ним):

• Змінено назву нового аркуша на "BackEnd".
• У клітинці В2 введіть символ стрілки (для цього перейдіть до пункту Вставити та натисніть опцію «Символ»).
• Скопіюйте та вставте заголовки з набору даних у клітинку A3: C3 на аркуші «Бекенд».
• Використовуйте таку функцію в комірці A4: AC4:

  = IF (A3 = $ C $ 1, A3 & "" & $ B $ 1, A3)

• Решта клітинок автоматично заповниться кодом VBA, коли ви двічі клацнете на заголовках, щоб відсортувати стовпець.

Ваш бекенд -аркуш виглядатиме так, як показано нижче:

Тепер ви можете використовувати наведений нижче код для сортування даних, двічі клацнувши на заголовках. Коли ви двічі клацнете на заголовку, він автоматично отримає стрілку в тексті заголовка. Зауважте, що я також використовував умовне форматування для виділення комірки.

Private Sub Worksheet_BeforeDoubleClick (ByVal Target As Range, Cancel As Boolean) Dim KeyRange As Range Dim ColumnCount As Integer ColumnCount = Range ("DataRange"). Columns.Count Cancel = False Якщо Target.Row = 1 І Target.Column <= ColumnC Тоді Скасувати = Справжні робочі аркуші ("Backend"). Діапазон ("C1") = Target.Value Set KeyRange = Діапазон (Target.Address) Діапазон ("DataRange"). Ключ сортування1: = KeyRange, Заголовок: = xlYes Робочі таблиці ("BackEnd ") .Range (" A1 ") = Target.Column For i = 1 To ColumnCount Range (" DataRange "). Cells (1, i) .Value = Worksheets (" Backend "). Range (" A4 "). Offset (0, i - 1). Значення Next i End If End Sub

Зауважте, що цей код добре працює для способу побудови моїх даних та робочої книги. Якщо ви зміните структуру даних, вам доведеться відповідно змінити код.

Завантажте файл прикладу