Menyelesaikan masalah dengan VBA Macro Excel

2 Tanggapan

Ups artikel ini ane buat mungkin bisa berguna bagi yang lainnya. Nah pada kesempatan kali ini ane ingin memperkenalkan VBA (Visual Basic Application) yang dibuat oleh Microsoft pada produk office mereka. Nah berhubung kemaren ada temen yang nanya dan mungkin bisa memberikan pencerahan bagi  temen - temen kalo Excel bisa melakukan dengan cepat dengan bantuan VBA ini.

Oke langsung saja Tanpa basa basi ane ingin menunjukkan dulu GOAL yang akan kita capai dengan memberikan permasalahan terlebih dahulu.. Oke langsung saja permasalahannya ane cetak tebal

Pada gambar di bawah ini ada beberapa nilai dari data. Tujuan kita kali ini adalah mengelompokkan data di bawah ini dengan menggunakan idkecamatan yaitu lima digit dari kolom 27775 ( hehe maap lupa namainnya).
Nah untuk tiap kecamatan dengan id yang sama dihitung rata - rata pada kolom warna kuning dengan syarat data yang bernilai 0 tidak diikut sertakan.

Nah dari gambar di atas output yang diharapkan adalah sebagai berikut :





Nah untuk kolom A menunjukkan identitas kecamatan 5 digit sedangkan pada kolom B sampai E adalah hasil rata - ratanya. Karena ada syarat tidak boleh ada nilai 0 pada perhitungan maka kita bisa manfaatkan fungsi AVERAGEIF pada Excel. Tetapi coba kita lihat berapa banyak barisnya. Ternyata 8737, alangkah banyaknya hal yang harus kita kerjakan dengan mensortir satu per satu kecamatan tersebut. Belum lagi kalau ditemukan kesalahan kita dalam melihat cell yang kita inginkan. Pasti hasilnya akan banyak Errornya


Nah dengan pendekatan macro inilah permasalahan kita yang seperti ini bisa kita selesaikan dengan mudah. Pasti dengan sedikit keahlian programming tetapi pakai VBA. Yang belum tahu apa itu VBA ataupun macro bisa di googling dulu di google
Nah yang di tab developer toolnya belum nampak bisa ikutin langkah - langkah sebagai berikut :

1. Buka excel tekan tombol office button> masuk ke excel option> general > show developer tab (office2007)
2. Untuk (office 2010) masuk ke File > option > costumize ribbon >sebelah kanan > developer tool dicentang

Setelah ada tab developer toolnya klik Macro terus tulis nama macronya misalnya running_data. Penulisan ini tidak boleh menggunakan spasi ya. Setelah itu klik ok akan muncul wilayah kita menuliskan macro. Nah di sinilah kita bermain kode



Untuk memperbesar silahkan klik pada gambar. Nah setelah itu kita paste kode di bawah ini :

Sub running_data()
Dim dataAwal As Worksheet
Dim dataAkhir As Worksheet
'ganti nama worksheet yang akan dihitung (Case sensitive)
namasheetawal = "12_SumUt"
'ganti nama worksheet hasilnya
namasheetakhir = "Hasil_Sumut"
Set dataAwal = Sheets(namasheetawal)
Set dataAkhir = Sheets(namasheetakhir)
barisawal = 4
barisakhir = 5
cekkecamatan = ""
i = 1
indeksawal = 0
indeksakhir = 0
Do While dataAwal.Cells(barisawal, 1) <> ""
    'dibagi berdasarkan kecamatan jika ingin ganti kabupaten tinggal ganti angka 5 menjadi 3
    kecamatan = Left(dataAwal.Cells(barisawal, 8), 5)
    If dataAwal.Cells(barisawal + 1, 1) = "" Then
        cekkecamatan = ""
    End If
    If cekkecamatan <> kecamatan Then
        cekkecamatan = kecamatan
        dataAkhir.Cells(barisakhir, 1) = kecamatan
        If indeksawal <> 0 Then
            indeksakhir = barisawal - 1
        End If
        If indeksakhir <> 0 Then
            'mendefinisikan kolom yang akan digunakan daerah untuk dihitung dengan menggunakan averageif
            kolom = Array("I", "J", "K", "M", "N", "O", "P", "R", "S", "T", "U", "W", "X", "Y", "Z", "AB", "AC", "AD")
            For k = 0 To 17
                'hitung dengan menggunakan average if
                dataAkhir.Cells(barisakhir - 1, k + 2).Formula = "=AVERAGEIF('" & namasheetawal & "'!" & kolom(k) & indeksawal & ":" & kolom(k) & indeksakhir & "," & Chr(34) & "<>0" & Chr(34) & ")"
            Next k
            'Str (indeksawal) + "-" + Str(indeksakhir - 1)
            
        End If
        barisakhir = barisakhir + 1
        indeksawal = barisawal
                
    End If
    barisawal = barisawal + 1
Loop
End Sub

Sebelum menjalankan ini buat sheet dulu klik kanan insert sheet namain hasil dari datanya apa  disini misalnya "Hasil_Sumut".Setelah dinamakan ubah kode pada baris namasheetakhir dengan nama dari hasilnya untuk sumbernya ubah pada namasheetawal dengan nama sheet datanya harus sama Case sensitive. Setelah diubah selesai klik macro lagi baru di klik macro terus run.Tunggu hasilnya lumayan lama. Nanti hasilnya ada di dalam sheet yang kamu buat. Setelah selesai jangan lupa di save as pake extensi .xlsm yang macro enable biar gak ilang macronya.