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

Membuat dan mengupdate dashboard offline merupakan salah satu (indirect) job saya. Seperti biasa, software yang paling mudah, paling cocok dan paling familiar untuk membuat report dengan scheme seperti itu (i.e dashboard) adalah Microsoft Excel. Anyway busway, bila anda kerap berselancar ke situs-situs online shop seperti Amazon.com, CNET.com dll, pasti anda akan menemukan symbol bintang (umumnya berderet lima, red) atau symbol lain yang sejenis (seperti yang ditunjukkan gambar di bawah, red) di bagian kanan, kiri, atas atau bawah sebuah product yang dipromosikan. Symbol seperti itu biasa disebut dengan istilah “Rate” atau Rating. Rating pada umumnya digunakan oleh Online shop sebagai indikator kualitas sebuah product yang dijual atau bisa juga menunjukkan seberapa tinggi respon pasar terhadap produk yang ditawarkan. Mereka menamakannya dengan “User Review” atau “Customer Review”. Terinspirasi dengan apa yang sudah dibuat oleh Online shop tersebut, saya berencana memasukkan display rating yang serupa pada report dashboard perseroan agar terlihat lebih atraktif.

Anyway, bagaimana cara membuatnya?

Ternyata sederhana. Berikut akan saya terjemahkan (dengan tambahan seperlunya dari penerjemah, red) langkah-langkah membuat “Product Review” di Microsoft Excel dari tulisan original seorang Master Excel dan VBA sekaligus MVP (Microsoft most Valuable Professional), Mr. Purnachandra R Duggirala. Siapkan segelas teh atau kopi hangat, plus cemilan (sehat) untuk menemani aktivitas anda kali ini, since it’s going to spend much time anyway,.. J. Let’s kick off!...

Saya sarankan anda masuk terlebih dahulu ke Chandoo dot Com, kemudian geser scroll bar anda ke bawah, cari judul “Download Example Workbook – On-demand Details in Excel” dan klik link download di bawahnya untuk mendapatkan sample chartnya. Belajar dengan menggunakan contoh dan mempraktekannya secara langsung biasanya lebih mudah ditangkap dan dicerna. Sudah?, Let’s go on…

Perhatikan unlive demo di bawah ini (sekedar info bahwa contoh rating di bawah merupakan replica dari review sebuah product di Amazon.com, red). Ketika anda mengklik di Row (baris) manapun yang anda suka di dalam cell Item + Rating Table di bawah, detail review dari Item yang berkesesuaian (dengan pilihan anda tersebut) akan tampil pada chart di sampingnya.

Nah, apa saja yang perlu dilakukan?

1). Memahami Database

Database ini merupakan source (sumber) dari Chart yang akan kita buat. “It has 3 columns” antara lain Item, Reviewer ID dan Rating (lihat sheet “Item ratings”). Masing-masing Item memiliki rating (antara 1 sampai 5) yang diberikan oleh beberapa reviewer yang berbeda-beda. Nah yang akan kita lakukan adalah mensummarykan seluruh rating-rating tersebut (ke dalam sebuah table dan grafik, red). Seluruh record data berada pada Range Table1 (i.e B2:B102). Kita akan menggunakan structured reference [What are they?] dalam formula agar data dapat tetap terbaca.

(2). Menset-up table Item dan Rating

Langkah pertama yang perlu kita lakukan adalah menampilkan table dengan seluruh Item productnya dan rata-rata rating setiap Item product yang berkesesuaian (note: Pada “Table1” hanya terdapat 9 Products dari 102 record nama yang sebagiannya berulang). Kemudian akan kita tambahkan “circle indicators” atau indicator rating yang berbentuk bulatan untuk memvisualisasikan average rating. Perhatikan gambar di bawah dan pahami penjelasannya (i.e formulasi yang digunakannya).

Ada 3 formulasi yang digunakan dalam hal ini.

(a). AVERAGEIF() formula

Formulasi ini sangat sederhana. Perhatikan, product Item berada pada range C5:C13 (i.e di sheet “Rating Summary”). Klik Cell D5 dan masukkan formulasi berikut =AVERAGEIF(Table1[Item],C5,Table1[Rating]) untuk mengetahui berapa nilai rata-rata rating Item C5 (i.e Instant Road). Kemudian “Fill the rest by dragging the formula down” i.e silahkan anda drag kursor dari D5 ke bawah untuk mengisi Row (baris) yang tersisa.

(b). Conditional Formatting

Setiap kali anda mengklik data pada column Items atau Rating di baris manapun yang anda suka di dalam range Item + Rating Table, maka warna dari Row tersebut akan ikut berubah secara otomatis. Di sinilah fungsi dari Conditional Formatting. Caranya; klik menu Home > Conditional Formatting > Manage Rule. Disana sudah ada sample formulanya untuk masing-masing cell. Silahkan anda pelajari.

(c). REPT formula

Formulasi ini digunakan untuk membuat repetisi (pengulangan) sebuah value (dalam case ini adalah symbol). Jika average rating dari suatu item product nilainya 3 misalnya, maka pada baris yang sama dalam column Rating akan muncul 3 bulatan penuh. Artinya formulasi akan melakukan repetisi symbol full circles sebanyak 3 kali secara otomatis (note: sebenarnya penulis i.e Duggirala “Chandoo” bermaksud menggunakan symbol bintang, namun ternyata tidak tersedia di defaut fonts Excel sehingga dia memilih circle symbol sebagai penggantinya. Simak penuturannya berikut, “There are no star symbols in the default fonts. But we have circles – a full circle, an empty circle and a donut to indicate half-circle. These symbols are available in Wingdings 2 font.”

Jika average ratingnya adalah angka bulat (1, 2, 3, 4 atau 5 misalnya), maka rating yang terprint menjadi 3 full circle dan 2 empty circle. Bagaimana jika average ratingnya decimal e.g 2.83?. Maka rating yang terprint adalah 2 full circles, one donut and 2 empty circles. Silahkan ikuti petunjuk berikut [inserting symbols in to Excel workbooks] untuk menampilkan circle symbol pada worksheet/ workbook anda.


Formulasi Repetisinya sendiri juga tidak kalah sederhana. Karena average ratingnya berada pada range D5:D13, maka formulasinya menjadi sebagai berikut;

=REPT(fullCircleSymbol,INT(D5)) & REPT(donutSymbol,(INT(D5)<>D5)+0) & REPT(emptyCircleSymbol,INT(5-D5))

Anda bisa memulai dari cell E5 dengan mengetikkan formulasi berikut ini;

=REPT($D$37,INT(D5))&REPT($E$37,(INT(D5)<>D5)+0)&REPT($F$37,INT(5-D5))

Block range C5:E15, kemudian kasih nama - rngReviews. Penamaan range ini (i.e di menu Formulas > Name Manager, red) berguna bagi user pada saat melakukan pengcodingan procedure menggunakan VBA. Selain melalui menu Name Manager, kita juga bisa membuat penamaan range di window VBA dengan mengetikkan syntax berikut;

Set rngReviews = Sheets(“Rating Summary”).range(“C5:E15”)

Tafadhal, terserah anda mau menggunakan metode yang mana.. J. O ya, jangan lupa beri nama range C5:C13 dengan lstItems dan cell E28 dengan valSelItem. Apa fungsinya?, akan terjawab pada penjelasan selanjutnya.

(Bersambung ke pembahasan 2)