MENGURUTKAN DATA EXCEL DENGAN VBA MULTI KOLOM
Excel telah mempunyai beberapa cara buat mengurutkan data menggunakan cepat. Anda bisa menggunakan mudah mengurutkan formasi data dengan memakai ikon sortir pada kotak obrolan sort. Pada artikel ini aku akan membahas cara mengurutkan (sorting) data dengan menggunakan VBA.
Mengetahui bagaimana mengurutkan data memakai VBA bisa membantu jika disertakan bagian berdasarkan data Anda. Misalnya, Anda mendapatkan formasi data setiap hari / mingguan yg perlu Anda format serta urutkan pada urutan tertentu.
Anda sanggup membuat macro buat melakukan semua ini hanya dengan sekali klik. Selain itu, bila Anda membuat dasbor Excel, Anda bisa merogoh kemampuan penyortiran Excel ke taraf yg baru yang memungkinkan anda menyortir data hanya dengan mengklik 2 kali pada header (misalnya 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 menggunakan VBA, Anda perlu menggunakan metode Range.sort pada kode Anda. 'Range' akan sebagai data yang ingin Anda urutkan. Misalnya, jika Anda menyortir data pada A1: A10, maka 'Range-nya' adalah Range ("A1: A10").
Anda pula bisa membuat nama range serta menggunakannya menjadi pengganti acum sel. Sebagai contoh, apabila saya membuat range dengan nama 'DataRange' buat sel A1: A10, maka aku menulisnya dalam kode sebagai "DataRange" bukan lagi A1:A10.
Dalam metode sort, Anda perlu memberikan beberapa kabar tambahan melalui parameter. Berikut merupakan parameter kunci yg 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 - pada sini Anda menentukan apakah Anda ingin menyortir dalam urutan menaik atau urutan menurun. Misalnya, apabila Anda ingin menyortir dalam urutan naik, maka Anda gunakan Order: = xlAscending
Header - di sini Anda menentukan apakah kumpulan data Anda memiliki header atau nir. Jika mempunyai header, pemilahan dimulai dari baris kedua deretan data, selain itu dimulai menurut baris pertama. Untuk menentukan bahwa data Anda memiliki header, maka Anda pakai Header: = xlYes
Mengurutkan (sorting) Kolom Tunggal tanpa Header
Misalkan Anda mempunyai satu kolom tanpa header (seperti gambar pada bawah).
Anda bisa memakai kode di bawah ini buat menyusunnya dalam urutan menaik.
Sub SortDataWithoutHeader ()
Range ("A1: A12") Sort Key1: = Range ("A1"), Order1: = xlAscending, Header: = xlNo
End Sub
Range ("A1: A12") Sort Key1: = Range ("A1"), Order1: = xlAscending, Header: = xlNo
End Sub
Perhatikan bahwa aku telah memilih range data secara manual menjadi Range ("A1: A12").
Jika mungkin terdapat perubahan dalam data dan nilai yg mungkin ditambahkan / dihapus, Anda dapat menggunakan kode pada bawah ini yang secara otomatis menyesuaikan menurut sel yg terisi dalam dataset.
Sub SortDataWithoutHeader()
Range("A1", Range("A1").end(xlDown)).sort Key1:=Range("A1"), Order1:=xlAscending, Header:=xlNo
End Sub
Range("A1", Range("A1").end(xlDown)).sort Key1:=Range("A1"), Order1:=xlAscending, Header:=xlNo
End Sub
Perhatikan bahwa bukan Range ("A1: A12"), saya memakai Range ("A1", Range ("A1"). End (xlDown)).
Ini akan memeriksa sel pengisian terakhir yg berurutan pada kolom dan memasukkannya ke pada sortingan. Apabila ada yg kosong, hanya akan mempertimbangkan data sampai sel kosong pertama.
Anda jua bisa membuat nama range serta menggunakan range bernama itu sebagai surat keterangan sel. Misalnya, apabila range bernama merupakan DataRange, maka kode yg harus Anda tulis sbb:
Sub SortDataWithoutHeader ()
Range ("DataRange") Sort Key1: = Range ("A1"), Order1: = xlAscending, Header: = xlNo
End Sub
Range ("DataRange") Sort Key1: = Range ("A1"), Order1: = xlAscending, Header: = xlNo
End Sub
Penjelasan parameter yg dipakai dalam contoh pada atas:
Key1: = Range ("A1") - A1 yang dipengaruhi sebagai akibatnya kode tersebut akan tahu kolom mana yang akan diurutkan.
Order1: = xlAscending - Menentukan urutan sebagai xlAscending. Apabila Anda menginginkannya dalam urutan menurun, pakai xlDescending.
Header: = xlNo - Ditentukan bahwa tidak ada header. Ini pula adalah nilai default. Jadi, walaupun perintah ini nir dipakai, data Anda permanen diurutkan mengingat nir terdapat header.
Mengurutkan (sorting) Kolom Tunggal Dengan Header
Pada model sebelumnya, deretan data nir mempunyai header. Jika data Anda mempunyai header, Anda perlu menentukannya dalam kode sehingga pemilahan dapat dimulai dari baris kedua dataset. Misalkan Anda memiliki dataset penjualan toko seperti gambar pada bawah ini:
Berikut merupakan kode yg akan mengurutkan data dalam urutan berdasarkan penjualan toko.
Sub SortDataWithHeader ()
Range ("DataRange") Sort Key1: = Range ("C1"), Order1: = xlDescending
End Sub
Range ("DataRange") Sort Key1: = Range ("C1"), Order1: = xlDescending
End Sub
Perbedaan dari kedua kode diatas hanya pada Sub SortDataWithoutHeader ()-apabila tanpa header dan Sub SortDataWithHeader ()-jika menggunakan header.
Rekomendasi artikel buat anda:
Mengurutkan (sorting) Multi Kolom Dengan Header
Sejauh ini dalam tutorial ini, kita sudah melihat bagaimana mengurutkan kolom tunggal (menggunakan serta tanpa header). Nah, bagaimana bila Anda ingin mengurutkan dari beberapa kolom.
Misalnya, dalam kumpulan data pada bawah, bagaimana apabila aku ingin mengurutkan terlebih dulu kode negara, lalu toko. Berikut adalah kode yg 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
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 merupakan output yg akan Anda dapatkan.
Mengurutkan (sorting) Data Dengan Double Click Pada Headear
Berikut kode buat Sorting data dengan double click dalam 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
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 sudah menciptakan nama range ("DataRange") dan aku pakai dalam kode sebagai referensi sel. Begitu Anda mengklik dua kali dalam salah satu header, kode tersebut menonaktifkan fungsionalitas klik 2 kali secara normal (yaitu masuk ke mode edit) serta menggunakan sel itu menjadi kunci saat menyortir data.
Perhatikan pula bahwa kode ini akan mengurutkan semua kolom dalam urutan naik saja.
Dimana buat menempatkan kode ini? Anda perlu menempelkan kode ini di ventilasi kode lembaran loka Anda ingin fungsi sortiri klik ganda ini.
Untuk melakukan ini:
Klik kanan pada tab sheet.
Klik pada Kode Tampilan.
Tempel kode di ventilasi kode lbr loka data Anda berada.
Sekarang bagaimana bila Anda ingin menyortir 2 kolom pertama ('State' serta 'Store') dalam urutan naik, tapi kolom 'sales' pada urutan menurun.
Inilah kode yang wajib 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
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, dia mengusut apakah sel yg diklik dua kali adalah header Sales atau tidak. Apabila ya, maka beliau tetapkan nilai xlDescending ke variabel SortOrder, apabila tidak maka akan menjadi xlAscending.
Sekarang yuk kita ambil trik ini lebih jauh serta membuat Marker visual (panah dan sel berwarna) di header waktu disortir misalnya gambar di bawah ini:
Untuk melakukan ini, aku sudah menambahkan lembar kerja baru serta membuat perubahan berikut di dalamnya:
Mengubah nama sheet baru menjadi 'BackEnd'.
Di sel B2, masuk simbol panah (buat melakukan ini, masuk ke Insert serta klik dalam opsi 'Symbol').
Salin serta tempelkan header menurut dataset ke sel A3: C3 pada lbr 'Backend'.
Gunakan fungsi berikut pada sel A4: AC4:
=IF(A3=$C$1,A3&" "&$B$1,A3)
Sisa sel akan otomatis terisi oleh kode VBA ketika Anda mengklik 2 kali dalam header untuk mengurutkan kolom.
Lembar backend Anda akan terlihat misalnya berikut:
Sekarang Anda sanggup memakai kode di bawah ini buat mengurutkan data dengan mengklik dua kali pada header. Saat Anda mengklik 2 kali dalam header, maka secara otomatis akan ada pertanda panah pada teks header. Perhatikan bahwa aku juga menggunakan conditional formatting buat 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
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 mengganti struktur data, Anda wajib memodifikasi kode buat menyesuaikannya. Apabila anda masih gundah atau ingin mempelajarinya lebih lanjut, silahkan download file model trik ini : sorting data menggunakan vba-semutsujud.
