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, kalau 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 arahan Anda. 'Range' akan menjadi data yang ingin Anda urutkan. Misalnya, kalau Anda menyortir data di A1: A10, maka 'Range-nya' ialah Range ("A1: A10").

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

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

Key - di sini anda perlu memilih kolom yang ingin anda urutkan. Misalnya, kalau 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, kalau 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 arahan 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 arahan 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 menilik 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 rujukan sel. Misalnya, kalau range berjulukan ialah DataRange, maka arahan yang harus Anda tulis sbb:

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

Penjelasan parameter yang dipakai dalam teladan di atas:

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

Berikut ialah arahan yang akan mengurutkan data dalam urutan menurut penjualan toko.

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

Perbedaan dari kedua arahan 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 kalau Anda ingin mengurutkan menurut beberapa kolom.

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

Mengurutkan (sorting) Data Dengan Double Click Pada Headear

Berikut arahan 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 arahan sebagai rujukan sel. Begitu Anda mengklik dua kali pada salah satu header, arahan 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 arahan ini akan mengurutkan semua kolom dalam urutan naik saja.

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

Untuk melaksanakan ini:

Klik kanan pada tab sheet.

Klik pada Kode Tampilan.

Tempel arahan di jendela arahan lembar kawasan data Anda berada.

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

Inilah arahan 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 arahan di atas, ia menilik apakah sel yang diklik dua kali ialah header Sales atau tidak. Jika ya, maka ia tetapkan nilai xlDescending ke variabel SortOrder, kalau 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 ibarat 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 arahan VBA ketika Anda mengklik dua kali pada header untuk mengurutkan kolom.

Lembar backend Anda akan terlihat ibarat berikut:

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