Create Rating Product Using Excel Formula And VBA (Vol. 2)

Let’s continue. Sekedar mereview saja; poin-poin yang sudah kita kupas pada pembahasan 1 sebelumnya adalah: (1). Memahami Database, (2). Menset-up table Item dan Rating. Dan berikut adalah poin-poin selanjutnya:

3). Mencari tahu cell mana yang diseleksi

OK (but not FOKE.. J), saatnya kita ber-VBA ria. Sebelum masuk ke pengcodingan, penulis asli artikel ini mengingatkan, “Take a sip of that coffee. It is getting cold” katanya,.. hehehe,..

Mungkin kita bingung dengan judul poin ke 3, apa sih maksudnya?.  Penulis menuturkan, “When a user selects any cell inside rngReviews, we need to find out which product it is so that we can load corresponding details.” Ya,.. ketika user menselect cell manapun dalam range “rngReviews” (i.e pada range “C5:E5”), kita perlu mengetahui product yang manakah itu sehingga kita bisa meloading data yang berkesesuaian dengan product yang dimaksud.

Berikut adalah penjelasan logic untuk syntax macro yang kita buat (gambar codingnya bisa dilihat pada link berikut)

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

    If Not (Application.Intersect(ActiveCell, Range("rngReviews").Cells) _

    Is Nothing) Then _

    Call UpdateAfterAction

End Sub

Syntax ini dibuat di Sheet1 (Rating Summary) menggunakan Worksheet Event.

Adapun procedure UpdateAfterAction() dibuat pada Module1 dengan detail syntax sebagai berikut;

Sub UpdateAfterAction()

    Dim topRow As Integer

   

    topRow = Range("rngReviews").Cells(1, 1).Row

    [valSelItem] = ActiveCell.Row() - topRow + 1

End Sub

(a). On Worksheet_SelectionChange, check if the ActiveCell overlaps with rngReviews

Maksudnya; Pada Worksheet_SelectionChange, check apakah terjadi overlaps atau intersect (perpotongan) antara ActiveCell (i.e Cell yang kita pilih, red) dengan rngReviews?.

(b). Jika iya, maka system akan otomatis memanggil prosedur UpdateAfterAction (pemahaman terbaliknya adalah; jika ActiveCell berada di luar rngReviews, maka procedure UpdateAfterAction tidak akan dipanggil oleh system, red). Syntax ini berfungsi mencari relative row number pada ActiveCell (i.e posisi dari selected cell pada range “rngReviews”) subject to row teratas pada rngReviews, kemudian system akan memasukkan value tersebut ke cell E28 secara otomatis. Bingung?. Ok, akan saya jelaskan lebih detail –cmiiw-. Coba anda klik cell E28. Apakah ada formulasi disana?, dari manakah asal angka (1, 2, 3.. 9) tersebut muncul?. Semuanya terjawab oleh syntax;

topRow = Range("rngReviews").Cells(2, 1).Row

[valSelItem] = ActiveCell.Row() - topRow + 1

Atau syntax hasil modifikasi saya sbb;

Set rngReviews = Sheets("Rating Summary").Range("C5:E5")

   

        topRow = rngReviews.Cells(1, 1).Row

        [valSelItem] = ActiveCell.Row() - topRow + 1

topRow akan mendefinisikan posisi baris teratas dari Range("rngReviews"). Posisi baris yang dimaksud adalah 5. Sedangkan nilai dari [valSelItem] bersifat dependen, tergantung di Row berapa ActiveCell tersebut berada. Sebagai contoh; ActiveCell kita set di posisi D11, maka ActiveCell.Row() menunjukkan row 11. Dengan demikian [valSelItem] = ActiveCell.Row() - topRow + 1 atau [valSelItem] = 11-5+1 (i.e 7). Angka 7 inilah yang akan otomatis muncul di cell E28.

4). Menggunakan output Macro VBA untuk menjalankan program

Kita memerlukan value pada cell E28 untuk 2 hal;


(a). Menandai (highlight) row yang berkesesuaian di dalam rngReviews dengan warna menggunakan [Conditional Formatting]

(b). Mencari product yang berkesesuaian dengan menggunakan INDEX formula

Sebagaimana fungsi VLOOKUP, INDEX formula berfungsi mencari record pada suatu database. Syntaxnya adalah sbb; =INDEX(array, row_num, [column_num])

Untuk case di atas, Array diisi dengan lstItems yang sudah kita definisikan sebelumnya. Adapun row_num diisi dengan valSelItem yang juga sudah kita definisikan sebelumnya. Silahkan klik link [INDEX] untuk memahami lebih dalam mengenai INDEX formula.

5). Menghitung Rating Product

Agar info detail dari sebuah product muncul, kita harus menghitung jumlah rating dari item product yang berkesesuaian (i.e berapa banyak 1 star, 2 star… 5 star reviews yang didapat oleh product tersebut). Formula yang digunakan adalah COUNTIFS. Apa bedanya dengan COUNTIF?. COUNTIFS (dengan “S”) diset lebih powerfull oleh Excel karena mampu menseleksi range data dengan kriteria lebih banyak, tidak seperti COUNTIF yang hanya bisa menampung satu kriteria saja. Ilustrasinya sbb:

COUNTIF formula >> misalkan: Inputan range-nya adalah “6 calon pemimpin yang maju ke Pilkada tahun ini”, criteria pilihannya adalah “jujur”, dan ini akan menjadi satu-satunya kriteria yang bisa dimasukkan dalam formulasi. Jika anda ingin memilih kriteria diluar “jujur” (amanah misalnya, red), maka kriteria jujur tidak bisa dipilih (karena di ganti dengan kriteria amanah, red). Selanjutnya ketika COUNTIF ini dirunning, maka system akan menghitung pemimpin yang memenuhi satu-satunya kriteria tersebut i.e jujur. Jika ada 3 pemimpin yang ternyata berkesesuaian dengan kriteria diatas, maka ketiga-tiganya akan dihitung sebagai pemimpin (i.e hasilnya 3, red).

COUNTIFS formula >> misalkan: Inputan range-nya adalah “6 calon pemimpin yang maju ke Pilkada tahun ini”, criteria pilihannya adalah “jujur”, “shalih”, “amanah”. Ketika COUNTIFS ini dirunning, maka system akan menghitung jumlah pemimpin yang memenuhi tiga kriteria yang ditetapkan tersebut sekaligus (note: jika merujuk kepada teori dasar teknik digital atau bahasa pemrograman, logika yang digunakan adalah AND, bukan OR, red) i.e jujur, shalih dan amanah. Jika tidak ada satu pun calon pemimpin yang memenuhi seluruh kriteria, maka hasilnya akan 0.

Untuk case ini, penulis mengatakan, “I am leaving the formulas for this to your imagination.” Atau dengan kata lain, sok lah mangga diulik sendiri formulasinya [use COUNTIFS formula] sampe anda bener-bener paham.. J. Jika sudah, pastikan bahwa hasil akhirnya akan menjadi seperti ini [note: Khusus untuk angka-angka pada column Distribution; agar tampak seperti pada gambar di bawah i.e ada tanda kurungnya, red, maka silahkan blok D30:34, klik kanan Format Cells > Number > Custom > (0)]

6). Membuat Chart untuk menampilkan Rating

Ini adalah step terakhir yang harus kita lakukan sebelum kita menata seluruh tampilan tabel/ chart dengan rapi. Ikuti lima langkah berikut;

(1). Blok column Distribution of Ratings, Distribution dan Total kemudian klik Insert > Charts pilih charts dengan tipe Column > All Chart Types…> Bar > Clustered Bar. Di Workbook yang anda download, data tersebut bisa anda dapatkan pada range C29:E34 di dalam sheet “Rating Summary”

(2). Balik urutan kategorinya, yang berada di posisi atas menjadi di bawah dan yang di bawah menjadi di atas. Caranya; klik vertical axis-nya dan tekan CTRL+1 kemudian centang “Categories in reverse order”. Jangan lupa untuk mendelete Legend-nya.

(3). Kik kanan pada area chart Format Data Series…> Series Options. Pada Series Overlap, [klik image] geser kursor ke kanan hingga Overlapped 100%. Sedangkan pada Gap Width, set angka di kisaran 50%. Jangan lupa, sesuaikan urutan series melalui source chart data option (i.e klik kanan pada chart series, klik Select Data, pilih distribution kemudian klik tombol movedown) -> [klik image] 

(4). Hapus Grid Lines, Axis Line, dan Horizontal Axis. Silahkan format warna chart sesuai dengan tampilan gambar di bawah (i.e pada langkah ke 4)

(5). Kecilkan chart, tambahkan judul, tambahkan label dan hilangkan bordernya. Agar judul chart bisa berubah secara dinamis, maka klik title chart, pada formula bar, ketik formula berikut ='Rating Summary'!$E$35. Buatlah hal yang sama untuk labelnya.

7). Mengatur tampilan akhir “Product Review”

Inilah saatnya untuk mengatur tampilan akhir dan melakukan testing. Geser chart yang sudah kita buat sebelumnya ke samping Rating Table. Lakukan test dengan mengklik cell manapun di dalam Rating Table (rngReviews). Selesai!

Sekarang anda bisa menghabiskan teh atau kopi anda yang sudah mulai dingin itu, jangan lupa simpan cemilan anda (untuk mempelajari trik-trik yang lainnya, red), dan tunjukkan pada partner kerja anda hasil akhir dari “ulikan” anda tersebut. Well done!.. J