В Excel уже есть несколько способов быстрой сортировки данных.
Вы можете легко отсортировать набор данных, используя значки сортировки на ленте или в диалоговом окне сортировки.
Тогда зачем вам знать, как это сделать с помощью VBA?
Знание того, как сортировать данные с помощью VBA, может быть полезным, если оно включено в ваш код. Например, предположим, что вы получаете набор данных ежедневно / еженедельно, который необходимо отформатировать и отсортировать в определенном порядке.
Вы можете создать макрос, который сделает все это за вас одним щелчком мыши. Это сэкономит вам много времени и усилий каждый раз, когда вы будете это делать.
Кроме того, если вы создаете информационные панели Excel, вы можете вывести возможности сортировки Excel на новый уровень, позволив пользователю сортировать данные, просто дважды щелкнув заголовок (как показано ниже).
Я расскажу, как создать это позже в этом уроке. Давайте сначала быстро разберемся с основами.
Понимание метода Range.Sort в Excel VBA
При сортировке с использованием VBA вам необходимо использовать в своем коде метод Range.Sort.
«Диапазон» - это данные, которые вы пытаетесь отсортировать. Например, если вы сортируете данные в формате A1: A10, тогда «Диапазон» будет иметь значение «Диапазон» («A1: A10»).
Вы также можете создать именованный диапазон и использовать его вместо ссылок на ячейки. Например, если я создаю именованный диапазон «DataRange» для ячеек A1: A10, я также могу использовать Range («DataRange»).
С помощью метода сортировки вам необходимо предоставить дополнительную информацию через параметры. Ниже приведены ключевые параметры, которые вам необходимо знать:
- Ключ - здесь нужно указать столбец, который нужно отсортировать. Например, если вы хотите отсортировать столбец A, вам нужно использовать ключ: = Range («A1»)
- Заказ - здесь вы указываете, хотите ли вы сортировку по возрастанию или по убыванию. Например, если вам нужна сортировка по возрастанию, вы должны использовать Order: = xlAscending
- Заголовок - здесь вы указываете, есть ли у вашего набора данных заголовки или нет. Если у него есть заголовки, сортировка начинается со второй строки набора данных, иначе она начинается с первой строки. Чтобы указать, что у ваших данных есть заголовки, вы будете использовать Header: = xlYes
Хотя этих трех достаточно в большинстве случаев, вы можете узнать больше о параметрах в этой статье.
Теперь давайте посмотрим, как использовать метод Range.Sort в VBA для сортировки данных в Excel.
Сортировка одного столбца без заголовка
Предположим, у вас есть один столбец без заголовка (как показано ниже).
Вы можете использовать приведенный ниже код, чтобы отсортировать его в порядке возрастания.
Sub SortDataWithoutHeader () Range ("A1: A12"). Sort Key1: = Range ("A1"), Order1: = xlAscending, Header: = xlNo End Sub
Обратите внимание, что я указал диапазон данных вручную как Range («A1: A12»).
В случае, если в данных могут быть изменения и значения могут быть добавлены / удалены, вы можете использовать приведенный ниже код, который автоматически настраивается на основе заполненных ячеек в наборе данных.
Sub SortDataWithoutHeader () Range ("A1", Range ("A1"). End (xlDown)). Sort Key1: = Range ("A1"), Order1: = xlAscending, Header: = xlNo End 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: = Range ("A1"), Order: = xlAscending .SortFields.Add Key: = Range ("B1"), Order: = xlAscending .SetRange Range ("A1 : C13 ") .Header = xlYes. Применить End With End Sub
Ниже представлен результат, который вы получите.
В приведенном выше примере данные сначала сортируются по коду состояния (столбец A). Затем в данных кода состояния он снова сортируется по магазину (столбец 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 Then Cancel = True Установить KeyRange = Range (Target.Address) Range ("DataRange"). Sort Key1: = KeyRange, Header: = xlYes 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 Then Cancel = True Установить KeyRange = Range (Target.Address) Если Target.Value = "Sales" Тогда SortOrder = xlDescending Else SortOrder = xlAscending End If Range ("DataRange"). Sort Key1: = KeyRange, Header: = xlYes, Order1: = SortOrder End If End Sub
В приведенном выше коде он проверяет, является ли ячейка, по которой дважды щелкнули, заголовком Продажи или нет. Если да, то он присваивает значение xlDescending переменной SortOrder, иначе делает его xlAscending.
Теперь давайте сделаем еще один шаг вперед и покажем визуальный маркер (стрелка и цветная ячейка) в заголовке при сортировке.
Что-то вроде того, что показано ниже:
Чтобы получить это, я добавил новый рабочий лист и внес в него следующие изменения (вы можете загрузить файл примера и следить за ним):
- Изменил имя нового листа на «BackEnd».
- В ячейке B2 введите символ стрелки (для этого перейдите во вкладку «Вставить» и выберите параметр «Символ»).
- Скопируйте и вставьте заголовки из набора данных в ячейку 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 Then Отмена = True Worksheets ("Backend"). Range ("C1") = Target.Value Set KeyRange = Range (Target.Address) Range ("DataRange"). Sort Key1: = KeyRange, Header: = xlYes Worksheets ("BackEnd ") .Range (" A1 ") = Target.Column For i = 1 To ColumnCount Range (" DataRange "). Cells (1, i) .Value = Worksheets (" Backend "). Range (" A4 "). Offset (0, i - 1). Value Next i End If End Sub
Обратите внимание, что этот код хорошо подходит для построения моих данных и книги. Если вы измените структуру данных, вам придется соответствующим образом изменить код.
Загрузите файл примера
