Di excel kita bisa buat Chart atau grafik untuk menunjang laporan biar enak diliat, tapi kalau chart yang kita sajikan terlalu “rame” malah bikin bingung lho, tapi mau gimana lagi ? kalau datanya emang perlu ditampilin semua ??? nah kali ini kita akan coba membuat grafik laporan triwulanan pengunjung situs ini sesuae permintaan boss Sarmili si Super Admin ^^.

Jadi skenarionya dari data harian yang bajibun kita akan bikin min, max dan rata-rata pengunjung, tapi yang ditampilin digrafik hanya rekap per-3 bulanan saja… lalu agar si boss bisa membandingkan secara bulanan sesue keinginan kita tambahkan sedikit form kontrol dari toolbar form berupa dropdown untuk memilih bulan…mmm kayaknya cukup menarik, yuk kita mulai!

Langkah pertama kita buka excel beserta datanya (ssstt… contoh datanya sudah dimark up biar kesannya situs ini banyak penggemarnya ^^), okey kita sudah punya data tanggal dengan jumlah pengunjungnya dengan tampilan kira-kira seperti disamping. Dilanjutkan dengan menambah sebuah combo box agar boss Super Admin bisa memilih bulan mana yang mau dilihat di grafik nantinya. Boss super admin cukup memilih satu bulan (H)  untuk satu bulan sebelumnya (H-1) dan satu bulan berikutnya (H+1) akan muncul otomatis beserta jumlah min-max dan rata-rata pengunjung pada bulan yang bersangkutan.
(1)Input Range: isi pilihan di isi di sini, dengan merujuk nilai suatu range.  (2)Cell Link: posisi cell yang akan menampung nomor index pilihan yang terpilih. (3) Drop down lines: Jumlah pilihan yang ditampilkan saat combo box diklik. untuk memunculkan form disamping klik kanan combo box kemudian pilih format kontrol. Sebagai sumber input range terlebih dahulu kita buat list bulan sebagai rujukan combo box, buat list bulan di cell E10 sampai E21, untuk mempercepat gunakan auto series untuk mengisi nama bulannya. kemudian untuk seting Cell link, isikan E9 sebagai cell target yang akan menyimpan index pilihan combo box. tampilan control combo box seperti tampak di samping. Setelah selesai combo box sudah siap digunakan dengan pilihan sesui dengan range yang ditentukan ^^

Langkah berikutnya adalah menampilkan nilai pilihan di table month sesuai dengan nomor index yang terpilih lewat combo box, setidaknya ada 2 formula yang bisa digunakan yaitu dengan index atau choose, tapi kita pake yang paling mudah yaitu formula index. Untuk menampilkan nama bulan pilihan di cell G4 ketikakan
=INDEX($E$10:$E$21,$E$9,1)
dan untuk satu bulan sebelumnya di cell F4 ketikkan
=INDEX($E$10:$E$21,$E$9-1,1)
untuk satu bulan berikutnya di cell H4 ketikkan
=INDEX($E$10:$E$21,$E$9+1,1)

tapi ada satu masalah saat combo box kita pilih ke bulan januari atau Desember hasilnya untuk satu bulan sebelum dan sesudahnya akan error bukan? nah untuk itu kita butuh sedikit formula pengandaian untuk menangkap error yang terjadi kita gunakan kombinasi if dan iserror
jadi formula lengkapnya begini:
Cell F4:
=IF(ISERROR(INDEX($E$10:$E$21,$E$9-1,1)),”",INDEX($E$10:$E$21,$E$9-1,1))
Cell H4:
=IF(ISERROR(INDEX($E$10:$E$21,$E$9+1,1)),”",INDEX($E$10:$E$21,$E$9+1,1))

Langkah berikutnya adalah merekap setiap kunjungan per bulan, jadi kita butuh formula yang bisa merekap berdasarkan bulan pilihan yang ada, kali ini kita akan memakai array formula jadi jangan lupa untuk menekan tombol ctrl+shift+enter saat selesai mengetik formula-nya ^^  agar excel membacanya sebagai array formula ditandai dengan penambahan bracket

seperti kasus sebelumnya untuk satu bulan H-1 dan H+1 musti ditambahin percabangan agar tidak menghasilkan kesalahan khusus rekap rata-rata. kalau benar mustinya tampilannya seperti ini:

Lankah selanjutnya tinggal memasukkan chart berdasarkan list rekap yang telah kita buat sebelumnya. Biar kelihatan lebih cantik tempatkan chart disatu sheet yang sama dengan datanya, posisinya tepat di atas list bulan, sekaligus buat nutupin dummy listnya ^^.

Ok, selesai!, baiknya kita rekap nieh apa yang kita pelajari hari ini biar lebih yakin ^^. Hari ini kita menggunakan fungsi-fungsi sbb:

Index	Untuk mengambil nilai dari sebuah range berdasarkan nomor urutnya (index)
If	Percabangan, yang kita gunakan untuk memisahkan dua kondisi yang membutuhkan penanganan yang berbeda
IsError	Menghasilkan nilai 0/1 (true/false) jika hasil suatu formula bernilai error (mencakup N/A, #Value dll) maka akan bernilai true, kebalikannya akan bernilai false
Min, Max, Avarege	Formula umum yang tidak usah dijelaskan lebih lanjut ^^
Month	Menghasilkan nilai numerik bulan dari suatu tanggal
Array formula	formula ini banyak fungsinya, yang utama bisa digunakan untuk mengenerate nilai dalam range tertentu kemudian kita test dengan kondisi tertentu untuk diproses lebih lanjut untuk menghasilkan nilai tertentu…. bingung kan ? emang susah dijelasin, ini waktunya anda menggunakan toolbar Formula Auditing untuk melihat step-step kalkulasi sebuah formula khususnya Array formula biar lebih ngerti….

Nah semoga trik kali ini bermanfaat, untuk download file contoh trik kali ini, silahkan klik tombol dibawah ini, selamat mencoba!