Meteo #6 - Menghitung Statistik pada Excel berdasarkan Warna Cell

Salah satu bagian yang paling merepotkan ketika mengolah data dengan Excel adalah melakukan perhitungan statistik. Perhitungan statistik yang dimaksud di sini tentu saja bukan statistik dasar (karena Excel pada dasarnya dirancang untuk melakukan perhitungan statistik). Yang saya maksud adalah statistik lanjut, misalnya anda ingin menghitung rata-rata berdasarkan kondisi yang acak dan tidak tetap.

Contoh kasus :

Anda mempunyai data hujan bulanan (Januari-Desember) selama 13 tahun (2000-2012). Misalnya anda ingin mengetahui rata-rata seluruh bulan, anda tinggal menggunakan fungsi AVERAGE dan memilih seluruh sel data, dan ... hola, muncullah nilai rata-rata yang anda cari.

Pada contoh di atas, saya menghitung rata-rata pada sel B155 sampai M167, dikali 100, hasilnya berada pada sel A168. Piece of cake.

Sekarang, bagaimana misalnya anda ingin menghitung rata-rata hujan pada saat MJO fase 1 ? Artinya tidak semua nilai ikut dimasukkan dalam rata-rata, karena MJO fase 1 terjadi hanya pada bulan-bulan tertentu saja. Untuk lebih jelasnya perhatikan gambar berikut.

Nilai pada cell dengan background kuning pada gambar di atas tidak ikut dirata-ratakan. 

Salah satu cara yg paling mudah adalah dengan menghapus nilai pada cell dengan background kuning, karena Excel tidak akan meratakan-ratakan data pada cell kosong. Masalahnya, bagaimana bila jumlah data yg ingin dirata-ratakan banyak ? Misal 100 tahun atau 100 stasiun ? Tentu tidak akan efisien bila kita menghapus nilai pada cell dengan background kuning secara manual.

Sayangnya, Excel tidak memiliki fungsi statistik untuk melakukan perhitungan berdasarkan properti cell, seperti warna background, warna teks dll. Saya kurang tahu kalau Excel 2010 dan setelahnya bisa melakukannya. karena selama ini saya menggunakan Excel 2007 dan sebelumnya.

Salah satu trik yang bisa kita lakukan adalah dengan menggunakan Macro. Macro pada dasarnya merupakan kode program yang bisa disisipkan dalam spreadsheet untuk melakukan suatu fungsi tertentu. 

Untuk melakukan perhitungan statistik berdasarkan warna background cell, lakukan langkah berikut :

• Tekan Alt-F11 untuk membuka jendela Microsoft Visual Basic. Di sinilah anda akan memasukan kode macro ke spreadsheet.
• Pada menu 'Insert' pilih 'Module'. Kode yang dimasukkan ke Module akan bisa diakses oleh setiap Sheet pada Spreadsheet Excel.
• Copy dan paste kode berikut pada jendela Module :

Function ColorFunction(rColor As Range, rRange As Range, Optional SUM As Boolean)

Dim rCell As Range

Dim lCol As Long

Dim vResult

lCol = rColor.Interior.ColorIndex

If SUM = True Then

  For Each rCell In rRange

    If rCell.Interior.ColorIndex = lCol Then

      vResult = WorksheetFunction.SUM(rCell, vResult)

    End If

  Next rCell

Else

  For Each rCell In rRange

    If rCell.Interior.ColorIndex = lCol Then

      vResult = 1 + vResult

    End If

  Next rCell

End If

ColorFunction = vResult

End Function

• Simpan kode di atas. Agar spreadsheet bisa menggunakan Macro, anda harus menyimpan file Excel ke dalam format Macro Spreadsheet (.xlsm). 
• Pada spreadsheet, buatlah fungsi berikut untuk melakukan perhitungan pada cell. 

=COLORFUNCTION(CELLWARNA,RANGEDATA,OPSI)

COLORFUNCTION adalah nama fungsi yang kita buat dari modul Macro.

CELLWARNA adalah cell dari warna background yang ingin kita proses.

RANGEDATA adalah rentang cell dari data yang ingin kita proses.

OPSI adalah proses yang ingin kita lakukan. Pada kode di atas, bila kita ingin melakukan penjumlahan (SUM), gunakan TRUE. Sedangkan bila ingin menghitung jumlah cell (COUNT), gunakan FALSE. Anda bisa mengganti SUM atau COUNT tadi ke fungsi lain seperti AVERAGE, STDEV dll tergantung selera dan kebutuhan anda.  

Ini contohnya :

Pada gambar di atas, saya meletakkan fungsi COLORFUNCTION(FW2,FI2:FT2,TRUE) pada cell FU2. Artinya, saya akan menghitung akumulasi data (TRUE) pada cell dengan warna background yg sama dengan cell FW12 (putih), dengan rentang data dari cell FI2 sampai FT2.

Untuk cell FV2, saya meletakkan fungsi COLORFUNCTION(FW2,FI2:FT2,FALSE). Proses kerjanya sama dengan sebelumnya, hanya fungsinya adalah menghitung jumlah cell dengan background putih. Hasilnya, FV2 bernilai 7, karena jumlah cell dengan background putih dari FI2 sampai FT2 ada 7.

Tentu saja, fungsi Macro ini bisa disalin seperti fungsi Excel lainnya, sehingga dapat membantu pengolahan data secara lebih efektif dan efisien.