Excel вече има няколко начина за бързо сортиране на данни.
Можете лесно да сортирате набор от данни, като използвате иконите за сортиране в лентата или диалоговия прозорец за сортиране.
Тогава защо трябва да знаете как да направите това с помощта на VBA?
Знанието как да сортирате данни с помощта на VBA може да бъде полезно, когато е включено като част от вашия код. Да предположим например, че получавате набор от данни дневно/седмично, който трябва да форматирате и сортирате в определен ред.
Можете да създадете макрос, който да прави всичко това вместо вас с едно щракване. Това ще ви спести много време и усилия всеки път, когато го правите.
Освен това, ако създавате табла за управление на Excel, можете да изведете възможностите за сортиране на Excel на ново ниво, като позволите на потребителя да сортира данните само чрез двукратно щракване върху заглавката (както е показано по-долу).
Ще разгледам как да създам това по -късно в този урок. Нека първо бързо разберем основите.
Разбиране на метода Range.Sort в Excel VBA
Когато сортирате с помощта на VBA, трябва да използвате метода Range.Sort във вашия код.
„Обхватът“ ще бъде данните, които се опитвате да сортирате. Например, ако сортирате данните в A1: A10, тогава „Range“ ще бъде Range („A1: A10“).
Можете също така да създадете именован диапазон и да го използвате вместо препратките към клетки. Например, ако създам именен диапазон „DataRange“ за клетките A1: A10, тогава мога да използвам и Range („DataRange“)
С метода за сортиране трябва да предоставите допълнителна информация чрез параметри. По -долу са основните параметри, които трябва да знаете:
- Ключ - тук трябва да посочите колоната, която искате да сортирате. Например, ако искате да сортирате колона А, трябва да използвате key: = Range (“A1”)
- Поръчка - тук посочвате дали искате сортиране във възходящ или низходящ ред. Например, ако искате сортиране във възходящ ред, ще използвате Order: = xlAscending
- Заглавка - тук посочвате дали вашият набор от данни има заглавки или не. Ако има заглавки, сортирането започва от втория ред на набора от данни, иначе започва от първия ред. За да посочите, че вашите данни имат заглавки, ще използвате Header: = xlДа
Въпреки че тези три са достатъчни в повечето случаи, можете да прочетете повече за параметрите в тази статия.
Сега нека видим как да използваме метода Range.Sort във VBA за сортиране на данни в Excel.
Сортиране на една колона без заглавка
Да предположим, че имате една колона без заглавка (както е показано по -долу).
Можете да използвате кода по -долу, за да го сортирате във възходящ ред.
Sub SortDataWithoutHeader () Диапазон ("A1: A12"). Sort Key1: = Range ("A1"), Order1: = xlAscending, Header: = xlNo End Sub
Имайте предвид, че ръчно съм посочил диапазона от данни като Обхват („A1: A12“).
В случай, че може да има промени в данните и стойностите могат да бъдат добавени/изтрити, можете да използвате кода по -долу, който автоматично се коригира въз основа на запълнените клетки в набора от данни.
Sub SortDataWithoutHeader () Диапазон ("A1", Диапазон ("A1"). Край (xlDown)). Клавиш за сортиране1: = Диапазон ("A1"), Ред1: = xlAscending, Заглавие: = xl Без край Sub
Обърнете внимание, че вместо Range (“A1: A12”), аз съм използвал, Range (“A1”, Range (“A1”). End (xlDown)).
Това ще провери последната поредно попълнена клетка в колоната и ще я включи в сортирането. В случай, че има празни места, той ще разгледа данните само до първата празна клетка.
Можете също така да създадете именован диапазон и да го използвате вместо препратките към клетки. Например, ако именуваният диапазон е DataSet, вашият код сега ще бъде както е показано по -долу.
Sub SortDataWithoutHeader () Range ("DataRange"). Sort Key1: = Range ("A1"), Order1: = xlAscending, Header: = xlNo End Sub
Сега нека бързо да обясня параметрите, използвани в горните примери:
- Key1: = Range (“A1”) - Посочен A1, така че кодът да знае коя колона да сортира.
- Order1: = xlAscending - Поръчката е посочена като xlAscending. Ако искате да е в низходящ ред, използвайте xlDescending.
- Заглавка: = xlNo - Посочено е, че няма заглавки. Това е и стойността по подразбиране. Така че дори да пропуснете това, вашите данни ще бъдат сортирани, като се има предвид, че нямат заглавки.
Чудите се къде да поставите този VBA код и как да стартирате макроса? Прочетете този урок!
Сортиране на една колона с заглавка
В предишния пример наборът от данни няма заглавка.
Когато вашите данни имат заглавки, трябва да посочите това в кода, така че сортирането да може да започне от втория ред на набора от данни.
Да предположим, че имате набор от данни, както е показано по -долу:
По -долу е кодът, който ще сортира данните в низходящ ред въз основа на продажбите на магазините.
Sub SortDataWithHeader () Range ("DataRange"). Sort Key1: = Range ("C1"), Order1: = xlDescending End Sub
Обърнете внимание, че създадох именуван диапазон - „DataRange“ и използвах този именен диапазон в кода.
Сортиране на няколко колони с заглавки
Досега в този урок видяхме как да сортирате една колона (със и без заглавки).
Сега, ако искате да сортирате въз основа на множество колони.
Например, в набора от данни по -долу, какво ще стане, ако първо искам да сортирам по кода на състоянието, а след това по магазина.
Ето кода, който ще сортира няколко колони наведнъж.
Sub SortMultipleColumns () С ActiveSheet.Sort .SortFields.Add Key: = Range ("A1"), Order: = xlAscending .SortFields.Add Key: = Range ("B1"), Order: = xlAscending .SetRange Range ("A1 : C13 "). Глава = xlДа. Приложете End With End Sub
По -долу е резултатът, който ще получите.
В горния пример данните първо се сортират по кода на състоянието (колона А). След това в рамките на данните за кода на състоянието те отново се сортират по Store (колона 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 If Target.Row = 1 And Target.Column <= ColumnCount Cancel = True Set KeyRange = Range (Target.Address) Range ("DataRange"). Sort Key1: = KeyRange, Header: = xlДа End If End 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 If Target.Row = 1 And Target.Column <= ColumnCount Cancel = True Set KeyRange = Range (Target.Address) If Target.Value = "Sales" Then SortOrder = xlDescending Else SortOrder = xlAscending End If Range ("DataRange"). Sort Key1: = KeyRange, Header: = xlYes, Order1: = SortOrder End, ако End Sub
В горния код той проверява дали клетката, която е щракната двукратно, е заглавката Sales или не. Ако да, тогава той присвоява стойността на xlDescending на променливата SortOrder, в противен случай го прави xlAscending.
Сега нека да направим това по -далеч и да покажем визуален маркер (стрелка и цветна клетка) в заглавката, когато е сортиран.
Нещо, както е показано по -долу:
За да получа това, добавих нов работен лист и направих следните промени в него (можете да изтеглите примерния файл и да го следвате):
- Променено е името на новия лист на „BackEnd“.
- В клетка В2 въведете символ на стрелка (за да направите това, отидете на Вмъкване и кликнете върху опцията „Символ“).
- Копирайте и поставете заглавките от набора от данни в клетка A3: C3 в листа „Backend“.
- Използвайте следната функция в клетка A4: AC4:
= АКО (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 If Target.Row = 1 And Target.Column <= ColumnCount Cancel = True Worksheets ("Backend"). Range ("C1") = Target.Value Set KeyRange = Range (Target.Address) Range ("DataRange"). Sort Key1: = KeyRange, Header: = xlДа Работни листове ("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
Имайте предвид, че този код работи добре за начина, по който се изграждат моите данни и работна книга. Ако промените структурата на данните, ще трябва да промените кода съответно.
Изтеглете примерния файл
