Mengurutkan Data Excel Dengan VBA [Multi Kolom]

Excel sudah memiliki beberapa cara untuk mengurutkan data dengan cepat. Anda dapat dengan mudah mengurutkan kumpulan data dengan menggunakan ikon sortir di kotak dialog sort. Pada artikel ini saya akan membahas cara mengurutkan (sorting) data dengan menggunakan VBA.

Mengetahui bagaimana mengurutkan data menggunakan VBA dapat membantu bila disertakan bagian dari data Anda. Misalnya, Anda mendapatkan kumpulan data setiap hari / mingguan yang perlu Anda format dan urutkan dalam urutan tertentu.

Anda bisa membuat macro untuk melakukan semua ini hanya dengan sekali klik. Selain itu, jika Anda membuat dasbor Excel, Anda dapat mengambil kemampuan penyortiran Excel ke tingkat yang baru yang memungkinkan anda menyortir data hanya dengan mengklik dua kali pada header (seperti gambar di bawah).

Oke langsung saja kita mulai tutorial ini, pertama mari kita bahas dasar-dasarnya dulu.

Memahami Metode Range.Sort di Excel VBA

Saat menyortir menggunakan VBA, Anda perlu menggunakan metode Range.Sort dalam kode Anda. 'Range' akan menjadi data yang ingin Anda urutkan. Misalnya, jika Anda menyortir data di A1: A10, maka 'Range-nya' adalah Range ("A1: A10").

Anda juga dapat membuat nama range dan menggunakannya sebagai pengganti rujukan sel. Sebagai contoh, jika saya membuat range dengan nama 'DataRange' untuk sel A1: A10, maka saya menulisnya dalam kode menjadi "DataRange" bukan lagi A1:A10.

Dalam metode sort, Anda perlu memberikan beberapa informasi tambahan melalui parameter. Berikut adalah parameter kunci yang perlu diketahui:

Key - di sini anda perlu menentukan kolom yang ingin anda urutkan. Misalnya, jika Anda ingin mengurutkan kolom A, Anda perlu menggunakan kunci: = Range ("A1")

Order - di sini Anda menentukan apakah Anda ingin menyortir dalam urutan menaik atau urutan menurun. Misalnya, jika Anda ingin menyortir dalam urutan naik, maka Anda gunakan Order: = xlAscending

Header - di sini Anda menentukan apakah kumpulan data Anda memiliki header atau tidak. Jika memiliki header, pemilahan dimulai dari baris kedua kumpulan data, selain itu dimulai dari baris pertama. Untuk menentukan bahwa data Anda memiliki header, maka Anda gunakan Header: = xlYes

Mengurutkan (sorting) Kolom Tunggal tanpa Header

Misalkan Anda memiliki satu kolom tanpa header (seperti gambar di bawah).

Anda bisa menggunakan kode di bawah ini untuk menyusunnya dalam urutan menaik.

Sub SortDataWithoutHeader ()
Range ("A1: A12") Sort Key1: = Range ("A1"), Order1: = xlAscending, Header: = xlNo
End Sub

Perhatikan bahwa saya telah menentukan range data secara manual sebagai Range ("A1: A12").

Jika mungkin ada perubahan pada data dan nilai yang mungkin ditambahkan / dihapus, Anda dapat menggunakan kode di bawah ini yang secara otomatis menyesuaikan berdasarkan sel yang terisi dalam dataset.

Sub SortDataWithoutHeader()
Range("A1", Range("A1").End(xlDown)).Sort Key1:=Range("A1"), Order1:=xlAscending, Header:=xlNo
End Sub

Perhatikan bahwa bukan Range ("A1: A12"), saya menggunakan Range ("A1", Range ("A1"). End (xlDown)).

Ini akan memeriksa sel pengisian terakhir yang berurutan di kolom dan memasukkannya ke dalam sortingan. Jika ada yang kosong, hanya akan mempertimbangkan data sampai sel kosong pertama.

Anda juga dapat membuat nama range dan menggunakan range bernama itu sebagai referensi sel. Misalnya, jika range bernama adalah DataRange, maka kode yang harus Anda tulis sbb:

Sub SortDataWithoutHeader ()
Range ("DataRange") Sort Key1: = Range ("A1"), Order1: = xlAscending, Header: = xlNo
End Sub

Penjelasan parameter yang digunakan dalam contoh di atas:

Key1: = Range ("A1") - A1 yang ditentukan sehingga kode tersebut akan tahu kolom mana yang akan diurutkan.

Order1: = xlAscending - Menentukan urutan sebagai xlAscending. Jika Anda menginginkannya dalam urutan menurun, gunakan xlDescending.

Header: = xlNo - Ditentukan bahwa tidak ada header. Ini juga merupakan nilai default. Jadi, walaupun perintah ini tidak digunakan, data Anda tetap diurutkan mengingat tidak ada header.

Mengurutkan (sorting) Kolom Tunggal Dengan Header

Pada contoh sebelumnya, kumpulan data tidak memiliki header. Bila data Anda memiliki header, Anda perlu menentukannya dalam kode sehingga pemilahan dapat dimulai dari baris kedua dataset. Misalkan Anda memiliki dataset penjualan toko seperti gambar di bawah ini:

Berikut adalah kode yang akan mengurutkan data dalam urutan berdasarkan penjualan toko.

Sub SortDataWithHeader ()
Range ("DataRange") Sort Key1: = Range ("C1"), Order1: = xlDescending
End Sub

Perbedaan dari kedua kode diatas hanya pada Sub SortDataWithoutHeader ()-jika tanpa header dan Sub SortDataWithHeader ()-jika menggunakan header.

Rekomendasi artikel untuk anda:

Membuat Faktur Penjualan Menggunakan VLOOKUP di Excel 2007 ke atas

Cara Membuat Daftar Dropdown di Excel

Cara memberi password file excel 2007

Cara membuat sel excel jadi berwarna (sel yang mengandung rumus)

Mengurutkan (sorting) Multi Kolom Dengan Header

Sejauh ini dalam tutorial ini, kita telah melihat bagaimana mengurutkan kolom tunggal (dengan dan tanpa header). Nah, bagaimana jika Anda ingin mengurutkan berdasarkan beberapa kolom.

Misalnya, pada kumpulan data di bawah, bagaimana jika saya ingin mengurutkan terlebih dulu kode negara, lalu toko. Berikut adalah kode yang akan mengurutkan beberapa kolom sekaligus.

Sub SortMultipleColumns()
With ActiveSheet.Sort
     .SortFields.Add Key:=Range("A1"), Order:=xlAscending
     .SortFields.Add Key:=Range("B1"), Order:=xlAscending
     .SetRange Range("A1:C13")
     .Header = xlYes
     .Apply
End With
End Sub

Berikut adalah hasil yang akan Anda dapatkan.

Mengurutkan (sorting) Data Dengan Double Click Pada Headear

Berikut kode untuk Sorting data dengan double click pada header

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
Set KeyRange = Range(Target.Address)
Range("DataRange").Sort Key1:=KeyRange, Header:=xlYes
End If
End Sub

Perhatikan bahwa saya telah membuat nama range ("DataRange") dan saya gunakan dalam kode sebagai referensi sel. Begitu Anda mengklik dua kali pada salah satu header, kode tersebut menonaktifkan fungsionalitas klik dua kali secara normal (yaitu masuk ke mode edit) dan menggunakan sel itu sebagai kunci saat menyortir data.

Perhatikan juga bahwa kode ini akan mengurutkan semua kolom dalam urutan naik saja.

Dimana untuk menempatkan kode ini? Anda perlu menempelkan kode ini di jendela kode lembaran tempat Anda ingin fungsi sortiri klik ganda ini.

Untuk melakukan ini:

Klik kanan pada tab sheet.

Klik pada Kode Tampilan.

Tempel kode di jendela kode lembar tempat data Anda berada.

Sekarang bagaimana jika Anda ingin menyortir dua kolom pertama ('State' dan 'Store') dalam urutan naik, tapi kolom 'sales' dalam urutan menurun.

Inilah kode yang harus anda tulis:

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
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 If
End Sub

Pada kode di atas, ia memeriksa apakah sel yang diklik dua kali adalah header Sales atau tidak. Jika ya, maka ia menetapkan nilai xlDescending ke variabel SortOrder, jika tidak maka akan menjadi xlAscending.

Sekarang mari kita ambil trik ini lebih jauh dan membuat Marker visual (panah dan sel berwarna) di header saat disortir seperti gambar di bawah ini:

Untuk melakukan ini, saya telah menambahkan lembar kerja baru dan membuat perubahan berikut di dalamnya:

Mengubah nama sheet baru menjadi 'BackEnd'.

Di sel B2, masuk simbol panah (untuk melakukan ini, masuk ke Insert dan klik pada opsi 'Symbol').

Salin dan tempelkan header dari dataset ke sel A3: C3 di lembar 'Backend'.

Gunakan fungsi berikut di sel A4: AC4:

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

Sisa sel akan otomatis terisi oleh kode VBA saat Anda mengklik dua kali pada header untuk mengurutkan kolom.

Lembar backend Anda akan terlihat seperti berikut:

Sekarang Anda bisa menggunakan kode di bawah ini untuk mengurutkan data dengan mengklik dua kali pada header. Saat Anda mengklik dua kali pada header, maka secara otomatis akan muncul tanda panah di teks header. Perhatikan bahwa saya juga menggunakan conditional formatting untuk menyorot sel.

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
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

Dan selesai, harap diperhatikan Jika Anda mengubah struktur data, Anda harus memodifikasi kode untuk menyesuaikannya. Jika anda masih bingung atau ingin mempelajarinya lebih lanjut, silahkan download file contoh trik ini : sorting data dengan vba-semutsujud.