Mengurutkan Data Excel Dengan Vba [Multi Kolom]

Excel sudah mempunyai beberapa cara untuk mengurutkan data dengan cepat. Anda sanggup dengan gampang mengurutkan kumpulan data dengan memakai ikon sortir di kotak obrolan sort. Pada artikel ini aku akan membahas cara mengurutkan (sorting) data dengan memakai VBA.

Mengetahui bagaimana mengurutkan data memakai VBA sanggup membantu bila disertakan bab dari data Anda. Misalnya, Anda mendapat kumpulan data setiap hari / mingguan yang perlu Anda format dan urutkan dalam urutan tertentu.

Anda bisa menciptakan macro untuk melaksanakan semua ini hanya dengan sekali klik. Selain itu, bila Anda menciptakan dasbor Excel, Anda sanggup mengambil kemampuan penyortiran Excel ke tingkat yang gres yang memungkinkan anda menyortir data hanya dengan mengklik dua kali pada header (seperti gambar di bawah).

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

Memahami Metode Range.Sort di Excel VBA

Saat menyortir memakai VBA, Anda perlu memakai metode Range.Sort dalam instruksi Anda. 'Range' akan menjadi data yang ingin Anda urutkan. Misalnya, bila Anda menyortir data di A1: A10, maka 'Range-nya' yaitu Range ("A1: A10").

Anda juga sanggup menciptakan nama range dan menggunakannya sebagai pengganti rujukan sel. Sebagai contoh, bila aku menciptakan range dengan nama 'DataRange' untuk sel A1: A10, maka aku menulisnya dalam instruksi menjadi "DataRange" bukan lagi A1:A10.

Dalam metode sort, Anda perlu menawarkan beberapa informasi perhiasan melalui parameter. Berikut yaitu parameter kunci yang perlu diketahui:

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

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

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

Mengurutkan (sorting) Kolom Tunggal tanpa Header

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

Anda bisa memakai instruksi 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 aku telah memilih range data secara manual sebagai Range ("A1: A12").

Jika mungkin ada perubahan pada data dan nilai yang mungkin ditambahkan / dihapus, Anda sanggup memakai instruksi di bawah ini yang secara otomatis menyesuaikan menurut 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"), aku memakai Range ("A1", Range ("A1"). End (xlDown)).

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

Anda juga sanggup menciptakan nama range dan memakai range berjulukan itu sebagai referensi sel. Misalnya, bila range berjulukan yaitu DataRange, maka instruksi yang harus Anda tulis sbb:

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

Penjelasan parameter yang dipakai dalam referensi di atas:

Key1: = Range ("A1") - A1 yang ditentukan sehingga instruksi 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 referensi sebelumnya, kumpulan data tidak mempunyai header. Bila data Anda mempunyai header, Anda perlu menentukannya dalam instruksi sehingga pemilahan sanggup dimulai dari baris kedua dataset. Misalkan Anda mempunyai dataset penjualan toko menyerupai gambar di bawah ini:

Berikut yaitu instruksi yang akan mengurutkan data dalam urutan menurut penjualan toko.

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

Perbedaan dari kedua instruksi diatas hanya pada Sub SortDataWithoutHeader ()-jika tanpa header dan Sub SortDataWithHeader ()-jika memakai 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 menciptakan 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 bila Anda ingin mengurutkan menurut beberapa kolom.

Misalnya, pada kumpulan data di bawah, bagaimana bila aku ingin mengurutkan terlebih dulu instruksi negara, kemudian toko. Berikut yaitu instruksi 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 yaitu hasil yang akan Anda dapatkan.

Mengurutkan (sorting) Data Dengan Double Click Pada Headear

Berikut instruksi 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 aku telah menciptakan nama range ("DataRange") dan aku gunakan dalam instruksi sebagai referensi sel. Begitu Anda mengklik dua kali pada salah satu header, instruksi tersebut menonaktifkan fungsionalitas klik dua kali secara normal (yaitu masuk ke mode edit) dan memakai sel itu sebagai kunci ketika menyortir data.

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

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

Untuk melaksanakan ini:

Klik kanan pada tab sheet.

Klik pada Kode Tampilan.

Tempel instruksi di jendela instruksi lembar kawasan data Anda berada.

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

Inilah instruksi 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 instruksi di atas, ia menyidik apakah sel yang diklik dua kali yaitu header Sales atau tidak. Jika ya, maka ia menetapkan nilai xlDescending ke variabel SortOrder, bila tidak maka akan menjadi xlAscending.

Sekarang mari kita ambil trik ini lebih jauh dan menciptakan Marker visual (panah dan sel berwarna) di header ketika disortir menyerupai gambar di bawah ini:

Untuk melaksanakan ini, aku telah menambahkan lembar kerja gres dan menciptakan perubahan berikut di dalamnya:

Mengubah nama sheet gres menjadi 'BackEnd'.

Di sel B2, masuk simbol panah (untuk melaksanakan 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 instruksi VBA ketika Anda mengklik dua kali pada header untuk mengurutkan kolom.

Lembar backend Anda akan terlihat menyerupai berikut:

Sekarang Anda bisa memakai instruksi 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 aku juga memakai 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 instruksi untuk menyesuaikannya. Jika anda masih galau atau ingin mempelajarinya lebih lanjut, silahkan download file referensi trik ini : sorting data dengan vba-semutsujud.