Mengurutkan Data Excel Dengan Vba [Multi Kolom]

Friday, June 22, 2018

Bagikan :

Tweet

loading...

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

Mengetahui bagaimana mengurutkan data memakai VBA sanggup memmenolong 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 membuat macro untuk melaksanakan tiruana ini spesialuntuk dengan sekali klik. Selain itu, jikalau Anda membuat dasbor Excel, Anda sanggup mengambil kemampuan penyortiran Excel ke tingkat yang gres yang memungkinkan anda menyortir data spesialuntuk dengan mengklik dua kali pada header (seperti gambar di bawah).

Oke pribadi saja kita mulai tutorial ini, pertama mari kita bahas dasar-dasarnya doloe.

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, jikalau Anda menyortir data di A1: A10, maka 'Range-nya' yaitu Range ("A1: A10").

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

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

Key - di sini anda perlu memilih kolom yang ingin anda urutkan. Misalnya, jikalau 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, jikalau 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 saya sudah memilih range data secara manual sebagai Range ("A1: A12").

Jika mungkin ada perubahan pada data dan nilai yang mungkin dimenambahkan / 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"), saya memakai Range ("A1", Range ("A1"). End (xlDown)).

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

Anda juga sanggup membuat nama range dan memakai range berjulukan itu sebagai acuan sel. Misalnya, jikalau 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 teladan 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 ialah nilai default. Jadi, walaupun perintah ini tidak digunakan, data Anda tetap diurutkan mengingat tidak ada header.

Mengurutkan (sorting) Kolom Tunggal melaluiataubersamaini Header

Pada teladan 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 spesialuntuk 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

Teknik Membuat Daftar Dropdown di Excel

Teknik memdiberi password file excel 2007

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

Mengurutkan (sorting) Multi Kolom melaluiataubersamaini Header

Sejauh ini dalam tutorial ini, kita sudah melihat bagaimana mengurutkan kolom tunggal (dengan dan tanpa header). Nah, bagaimana jikalau Anda ingin mengurutkan menurut beberapa kolom.

Misalnya, pada kumpulan data di bawah, bagaimana jikalau saya ingin mengurutkan terlebih doloe 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 melaluiataubersamaini 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 saya sudah membuat nama range ("DataRange") dan saya gunakan dalam instruksi sebagai acuan 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 tiruana kolom dalam urutan naik saja.

Dimana untuk menempatkan instruksi ini? Anda perlu menempelkan instruksi ini di jendela instruksi lembaran daerah 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 daerah data Anda berada.

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

INI 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 mengusut apakah sel yang diklik dua kali yaitu header Sales atau tidak. Jika ya, maka ia menetapkan nilai xlDescending ke variabel SortOrder, jikalau tidak maka akan menjadi xlAscending.

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

Untuk melaksanakan ini, saya sudah menambahkan lembar kerja gres dan membuat perubahan diberikut 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 diberikut 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 diberikut:

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 saya 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 gundah atau ingin mempelajarinya lebih lanjut, silahkan download file teladan trik ini : sorting data dengan vba-tiruantsujud.

Tag : excel, ms office