Menggunakan Rule dengan Formula pada Conditional Formatting

Pada artikel sebelumnya, ditunjukkan bagaimana memberi pewarnaan dengan conditional formatting. Spektrum warna pada artikel tersebut ditentukan oleh Excel berdasarkan perbandingan otomatis. Rule ini tentunya tidak cocok untuk banyak kondisi.

Melalui artikel ini, penulis akan menunjukkan contoh bagaimana membuat dan mengelola rule yang banyak digunakan, yaitu menggunakan formula untuk mewarnai cell kita. Untuk contoh kasusnya adalah membandingkan antara nilai aktual penjualan dibandingkan dengan target yang ingin dicapai.

Berikut adalah langkah-langkahnya:

1. Download file contoh sales_vs_target.xlsx dari link http://goo.gl/tjs7B2.
2. Jalankan aplikasi Microsoft Excel 2010, buka file yang telah di-download tersebut.
3. Pada Sheet 1 terdapat data dengan tampilan sebagai berikut.
   
   
   
   Data tersebut adalah data penjualan dari tiga kategori produk (Buah, Makanan & Minuman, Sayur-Sayuran) selama dua semester. Sementara itu terdapat juga target penjualan untuk tiap semester.
4. Dari data tersebut, kita akan memberi dua warna, yaitu hijau dan merah pada kolom penjualan dari kedua semester. Hijau apabila target tercapai, tetapi merah apabila target tidak tercapai.
5. Dengan mouse dan tombol CTRL, pilih dua range yang B3:B5 serta D3:D5 seperti tampak pada gambar berikut.
   
   
6. Pada ribbon tab Home, klik Conditional Formatting | Manage Rules.
   
   
7. Pada dialog Conditional Formatting Rules Manager, klik tombol New.
   
   
8. Pada dialog New Formatting Rule dan pada daftar Select a Rule Type, pilih Use a formula to determine which cells to format.
   
   
9. Pada bagian Edit the Rule Description | Format values where this formula is true: ketik manual formula berikut:
   
   B3>=C3
   
   
10. Setelah itu klik tombol Format. Pada dialog Format Cells yang muncul, klik tab Fill. Pilih warna hijau untuk Background Color. Klik tombol OK.
    
    
11. Dialog New Forma Rule sekarang akan terlihat seperti gambar berikut.
    
    
12. Klik tombol OK untuk menutup dialog.
13. Sekarang terdapat tambahan satu rule pada Conditional Formatting Rules Manager. Namun, perhatikan pada rule tersebut apakah yang terisi sesuai dengan yang kita isikan sebelumnya, yaitu B3 >= C3. Pada kasus penulis, rule tidak terisi seperti itu (terlihat pada gambar berikut).
    
    
14. Untuk memperbaiki ini, klik tombol Edit Rule dan isikan kembali formula =B3 >= C3 dan klik tombol OK. Kali ini rulenya telah benar.
    
    
15. Ulangi langkah 7 s/d 14 untuk rule berikut dengan format warna merah.
    
    =B3 < C3
    
    Hasil akhir tambahan rule tersebut akan tampak sebagai berikut.
    
    
16. Klik tombol Apply, cell-cell yang kita pilih pada langkah no. 5 akan di-format sesuai kondisi rule-rule yang kita masukkan.
    
    
17. Selesai.
    

Pewarnaan dengan Conditional Formatting pada Excel 2010

Seperti namanya, conditional formatting adalah satu fitur pada Excel 2007 ke atas yang digunakan untuk melakukan format pada range dari cell berdasarkan kondisi tertentu, sebagai contoh:

• Mewarnai cell dengan hijau, kuning dan merah berdasarkan nilai tertentu.
• Memberikan intensitas spektrum warna sesuai dengan rentang nilai.

Melalui artikel ini, mari kita coba fitur ini dengan contoh pewarnaan sederhana sebagai berikut:

1. Download file contoh summary_penjualan.xlsx dari link http://goo.gl/awgyIB.
2. Jalankan aplikasi Microsoft Excel 2010, buka file yang telah di-download tersebut.
3. Pada Sheet 1 terdapat data dengan tampilan sebagai berikut. Klik pada gambar untuk memperbesar tampilannya.
   
   
   
   Contoh data ini menunjukkan penjualan dari buah-buahan, makanan dan sayur-sayuran dari bulan ke bulan.
4. Kita akan coba melihat perbandingan penjualan dari seluruh bulan dan dari seluruh kategori. Pilih range B3:M5.
   
   
5. Pada ribbon tab Home, klik Conditional Formatting pada group Styles.
   
   
6. Lanjutkan dengan mengarahkan pilihan ke Color Scales | Red - Yellow - Green Color Scale
   
   
7. Hasilnya akan terlihat pada gambar berikut, dimana nilai penjualan sangat jelas terbagi berdasarkan nilai penjualannya. Buah-buahan selalu menempati nilai tertinggi (merah), diikuti Sayur-sayuran (kuning) dan terakhir Makanan dan Minuman (hijau).
   
   
8. Dari hasil pewarnaan tersebut, untuk dilakukan analisa terasa kurang "menggigit". Cobalah ulangi langkah pewarnaan di atas tapi pemilihan range hanya untuk tiap baris kategor, yaitu berturut range B3:M3, B4:M4 dan B5:M5.
   
   Jika semua berjalan lancar, maka hasilnya akan terlihat seperti tampilan berikut.
   
   
   
   Terlihat penjualan yang mendominasi ada pada bulan 10, dimana semua warna hampir mendekati merah.
9. Selain warna, sebenarnya conditional formatting ini juga bisa menggunakan indikator lain seperti panjang bar berwarna dengan Data Bars dan gambar menggunakan Icon Sets.
   
   Cobalah blok bari Grand Total, yaitu range B6:M6. Kemudian dari menu Conditional Formatting |  Icon Sets, dan pilih 3 Signs.
   
   
10. Sebagian hasilnya akan terlihat seperti pada gambar berikut. Perhatikan gambar bulat hijau menandakan penjualan terbaik, yaitu di bulan 3, 7, 8 dan 10.
    
    
11. Sekarang hilangkan format warna yang telah kita buat dengan cara mengklik menu Conditional Formatting | Clear Rules, dan pilih Clear Rules from Entire Sheet.
12. Selesai.

Demikian artikel pewarnaan sederhana dengan conditional formatting ini penulis buat, pada artikel lainnya akan ditunjukkan bagaimana menggunakan berbagai rule untuk melakukan formatting yang lebih terarah. Semoga bermanfaat.

Contoh Cara Penggunaan "Format as Table"

Berikut adalah contoh dengan animasi penggunaan Format as Table. 

Klik pada gambar untuk memperbesar ukurannya.

Silahkan download contoh file Excelnya di http://goo.gl/8G53dk.

Semoga bermanfaat !

Contoh Kasus: Mendapatkan Nilai Tertinggi dari Kumpulan Tanggal Yang Sama

Sering sekali dari suatu kumpulan data tanggal dan nilai, kita ingin mengambil nilai maksimum dari tanggal tersebut.

Sebagai contoh, dari data pada gambar berikut untuk tanggal 3 Oktober 2013 (10/3/2013) kita ingin mendapatkan nilai 212 sebagai nilai terbesarnya.

Nilai maksimum untuk tanggal 10/3/2013

Sedangkan untuk tanggal Y kita ingin mendapatkan YMax.

Nilai Maksimum untuk tanggal 8/16/2013

Berikut adalah contoh rumus array formula untuk mendapatkan max dari tanggal 10/3/2013 - diketik dulu rumusnya dan diakhiri penekanan tombol CTRL+SHIFT+ENTER. Rumus ini berlaku untuk kondisi data seperti pada gambar di atas.

=MAX(IF(B2:B10=DATEVALUE("10/3/2013"),D2:D10,0))

Dan jika kita menggunakan filter tanggal yang kita masukkan pada kolom F, maka kita masukkan formulanya kira-kira sebagai berikut (ingat diakhiri dengan penekanan tombol CTRL+SHIFT+ENTER).

=MAX(IF($B$2:$B$10=F2,$D$2:$D$10,0))

Penggunaan Formula dengan Filter pada kolom B (klik untuk memperbesar gambar)

Untuk Anda yang ingin mendownload filenya silahkan klik link di bawah ini, file tersebut terdapat di user group Facebook kami.

Array Formula - Mendapatkan Nilai Tertinggi dari Group Tanggal Yang Sama.xlsx

Semoga Bermanfaat !

Cara Menghitung Umur dengan Fungsi DATEDIF

DATEDIF adalah suatu fungsi pada Excel untuk menghitung perbedaan jarak antara kedua tanggal. Perhitungan yang sangat rumit ini - karena jumlah hari pada tiap bulan berbeda -  menjadi sangat sederhana dengan fungsi rumus ini.

Nah, pada artikel sebelumnya ditunjukkan cara yang cukup panjang untuk menghitung umur atau usia seseorang. Kali ini kita akan coba menggunakan rumus DATEDIF menghitung ulang usia seseorang berdasarkan tanggal lahirnya.

Syntax dari fungsi DATEDIF adalah sebagai berikut:

=DATEDIF(tanggal_1, tanggal_2, jenis_interval)

dimana:

• tanggal_1 adalah tanggal awal.
• tanggal_2 adalah tanggal akhir, dan tanggal akhir ini harus lebih besar nilainya dari tanggal_1.
• jenis_interval adalah teks yang menyatakan jenis interval atau jarak tanggal yang diinginkan, terdiri dari:
• d  :    menghitung total jarak hari secara keseluruhan.
• m :    menghitung total jarak bulan secara keseluruhan.
• y  : menghitung total jarak tahun secara keseluruhan.
• md: menghitung total jarak hari secara pada bulan dan tahun yang sama.
• yd: menghitung total jarak hari pada tahun yang sama.
• ym: menghitung total jarak bulan pada tahun yang sama.

Sebagai contoh, kita akan menghitung umur untuk bayi yang lahir pada tanggal 21-Maret-2007 dengan tanggal hari ini (tanggal penulisan adalah 3 Oktober 2013). Dengan berbagai jenis interval maka penggunaan rumus dan hasilnya adalah sebagai berikut:

• =DATEDIF("3/21/2007", today(),"d") akan menghasilkan nilai jarak total 2388 (hari)
• =DATEDIF("3/21/2007", today(),"m") akan menghasilkan nilai jarak total 78 (bulan)
• =DATEDIF("3/21/2007", today(),"y") akan menghasilkan nilai jarak total 6 (tahun)
• =DATEDIF("3/21/2007", today(),"md") akan menghasilkan nilai jarak 12 (hari) dengan anggapan ada pada  bulan dan tahun yang sama.
• =DATEDIF("3/21/2007", today(),"yd") akan menghasilkan nilai jarak 196 (hari) dengan anggapan ada pada tahun yang sama.
• =DATEDIF("3/21/2007", today(),"ym") akan menghasilkan nilai jarak 6 (bulan) dengan anggapan ada pada tahun yang sama.

Dan berikut adalah contoh screenshot lengkap untuk perhitungan usia untuk berbagai jarak tanggal. File tersebut dapat Anda download di user group Facebook. Semoga bermanfaat.

Hasil Perhitungan Umur dengan Fungsi DATEDIF

Cara Menghitung Umur dengan Excel berdasarkan Tanggal Lahir

Menghitung UMUR dan USIA dari informasi tanggal lahir adalah salah satu yang sering ditanyakan kepada penulis baik pada sesi training maupun ditemukan di forum-forum online.

Perhitungan usia yang kelihatannya gampang ini ternyata cukup kompleks karena tidak seperti perhitungan numerik, tanggal memiliki jumlah hari tiap bulan yang berbeda-beda (28, 29, 30 dan 31).

Berikut adalah screenshot solusi dari BelajarExcel.info dengan berbagai kombinasi tanggal lahir dan tanggal pengamatan.  Hasil N/A adalah karena tanggal pengamatan lebih kecil dari tanggal lahir.

Solusi ini menggunakan rumus IF dan fungsi tanggal YEAR, MONTH dan DATE. Selain itu Anda dapat menggunakan DATEDIF, namun kami akan tunjukkan di artikel lain. File ini dapat Anda download di koleksi file Excel user group kami.

Hasil Final Perhitungan Usia (klik untuk memperbesar tampilan gambar)

Dan berikut adalah rumus lengkapnya untuk perhitungan umur berdasarkan tahun, bulan dan hari.

C3 = TANGGAL PENGAMATAN
B3 = TANGGAL LAHIR

RUMUS UNTUK TAHUN
=================
=IF(C3>=B3,
IF(
OR(AND(DAY(C3)<DAY(B3),OR(MONTH(C3)-1=MONTH(B3),MONTH(C3)=MONTH(B3))), MONTH(C3)<MONTH(B3)),
IF(YEAR(C3)>YEAR(B3),YEAR(C3)-YEAR(B3)-1,YEAR(C3)-YEAR(B3)),
YEAR(C3)-YEAR(B3)
)
,"N/A ") & " tahun"

RUMUS UNTUK BULAN
=================
=IF(C3>=B3,
 IF(
  DAY(C3)<DAY(B3),
 IF(MONTH(C3)>MONTH(B3),MONTH(C3)-MONTH(B3)-1, MONTH(C3)+11-MONTH(B3)),
 IF(MONTH(C3)>=MONTH(B3),MONTH(C3)-MONTH(B3), MONTH(C3)+12-MONTH(B3))
  ), "N/A") & " bulan"

RUMUS UNTUK MENCARI HARI
========================
=IF(C3>=B3,
IF(
DAY(C3)>=DAY(B3), 
DAY(C3)-DAY(B3), 
C3-DATE(YEAR(C3),MONTH(C3)-1,DAY(B3))
), 
"N/A ") & " hari"

Info Tambahan: Kombinasi tanggal yang digunakan untuk testing tersebut dibuat berdasarkan matrix berikut.

Contoh Invoice Sederhana dengan Excel 2010

Invoice adalah surat tagihan yang kita alamatkan ke pelanggan kita, yang isinya antara lain mengandung:

1. Tujuan Penagihan - Nama Orang, Jabatan, Nama Perusahaan, dll.
2. Identitas Penagih.
3. Nomor invoice, nomor surat pemesanan atau purchase order atau nomor-nomor dokumen lainnya yang terkait dengan invoice tersebut.
4. Daftar item dengan deskripsi, jumlah, harga dan total harga
5. Total pajak dan total penagihan.
6. Tujuan pembayaran - biasanya berupa nomor akun dan nama bank lengkap.

Contoh invoice seperti ini dapat Anda lihat pada screenshot berikut. Yang tidak ada pada dokumen ini adalah rekening bank jika pembayaran di-transfer, Anda bisa menambahkannya setelah bagian terbilang.

File contoh invoice ini dapat Anda download disini. Untuk contoh dokumen lainnya, Anda dapat bergabung dengan user group Facebook kami, dan mendapatkan berbagai file contoh Excel sumbangan komunitas.

Semoga bermanfaat

Mengelola Named Range Pada Excel 2007 / 2010

Named Range adalah suatu fasilitas penamaan data range di Excel sehingga menjadi lebih mudah digunakan berulang kali pada rumus. Keuntungan lainnya adalah ketika terjadi perubahan cakupan data maka rumus yang menggunakan named range tidak perlu dirubah. Jika ada 1000 rumus yang mengacu ke suatu data range dan suatu saat terjadi penambahan data, Anda tentunya juga harus merubah semua rumus tersebut bukan? Bayangkan betapa repotnya melakukan hal seperti ini.

Artikel tutorial berikut akan menunjukkan bagaimana Anda dapat menambahkan dan menghapus named range dengan beberapa langkah sederhana.

Pada Excel 2007 / 2010, Anda dapat melihat ribbon group yang khusus untuk menangani named range ini pada bagian tab Formula | Defined Names.

Group Defined Names pada tab Formula

Pada ribbon group ini, Anda dapat menambahkan, merubah, dan menghapus nama-nama range yang diinginkan.

Selain itu untuk mendefinisikan nama Anda juga dapat menggunakan Name Box yang terletak di sebelah kiri kotak formula seperti terlihat pada gambar berikut ini.

Area Name Box yang terletak di sebelah Formula Bar

Mendefinisikan Named Range dengan Name Box

1. Download dan buka file contoh dari http://goo.gl/LTQ1NN.
2. Klik tombol Enable Editing jika muncul peringatan pada toolbar Excel 2010.
   
   
3. Pilih range data A3:C14.
   
   
4. Pada kotak Name Box, masukkan nama DaftarArtikel dan tekan tombol Enter.
   
   
5. Cobalah arahkan cell ke tempat lain, misalkan ke alamat D4.
6. Klik panah bawah pada Name Box, klik pilihan DaftarArtikel. Terlihat data range telah dipilih kembali.
7. Nama named range Anda telah siap untuk digunakan pada berbagai formula. Sebagai contoh, coba masukkan formula berikut pada cell E3:
   
   =VLOOKUP(6,DaftarArtikel,2)
   
   Hasil yang didapatkan harusnya : Penggunaan Rumus.
   
   

Menghapus Named Range

1. Klik tab Formulas, dan pada group Defined Names klik Name Manager.
   
   
2. Pada dialog yang muncul, pilih Delete dan klik tombol OK untuk menghapus.
   
   
   
   Catatan: Di dalam dialog ini, Anda juga bisa melakukan penambahan atau perubahan named range. Cobalah luangkan waktu untuk bereksperimen dengan fitur ini sehingga dapat digunakan secara maksimal ketika dibutuhkan.
3. Selesai.

Memberi Warna pada Angka dengan Custom Format

Warna adalah ekspresi yang sangat baik untuk memberi indikator bahwa ada sesuatu yang perlu diperhatikan pada data Excel kita.

Sebagai contoh, warna merah sering digunakan untuk mengindikasikan terjadi penurunan pertumbuhan. Warna hitam atau hijau sering digunakan untuk hal sebaliknya, memberi indikasi bahwa semua berjalan sesuai target.

Berikut adalah langkah-langkah untuk melakukan pewarnaan di Excel.

1. Jalankan program Excel Anda, dan buatlah worksheet baru.
2. Isikan worksheet Anda dengan data berikut atau download dari sini.
   
   
3. Letakkan cursor pada cell E3, dan buka dialog Format Cells (klik launcher pada tab Home | Number).
   
   
4. Pada tab Number, pilih Custom pada panel Category dan masukkan kode berikut :
   
   [Color 43]#,##0_);[Red](#,##0)
   
   Kode ini artinya jika angka bernilai positif maka diberi warna hijau (Color 43), dan jika bernilai negatif diberi warna merah.
   
   Catatan : Format angka berdasarkan nilai ini lengkapnya dibagi atas 4 bagian dengan pemisahan tanda titik koma. Bagian pertama adalah angka positif, kedua adalah angka negatif, ketiga adalah angka 0, dan keempat adalah teks.
5. Klik tombol OK untuk menutup dialog. Hasilnya terlihat sebagai berikut.
   
   
6. Gunakan Format Painter untuk menduplikasikan format dari cell E3 tersebut ke cell-cell di bawahnya sehingga hasil akhir terlihat sebagai berikut.
   
   
7. Selesai.

Jika ada pertanyaan terkait artikel ini, silahkan isi melalui komentar berikut atau bergabung ke forum diskusi kami. Terima kasih dan semoga bermanfaat.

Contoh Kasus: Grouping dan Subtotal pada Excel 2007

Untuk analisa data yang cukup besar dan kompleks, tentu kita perlu mengorganisasikan data dalam berbagai pengelompokan (grouping).

Dengan penggunaan grouping, kita akan mudah bekerja data yang memiliki subtotal pada berbagai level pengelompokan data.

Penggunaan Grouping Subtotal

1. Download dan buka dokumen penjualan.xlsx dengan MS Excel 2007.
2. Buka sheet Data Penjualan.
3. Lakukan sorting terhadap kolom year, month, nama produk dan nama_kota.
4. Pilih cell A1 atau selengkapnya dengan range A1:K11351.
5. Klik tab Data pada Ribbon.
6. Pada group Outline klik button Subtotal.
   
   
7. Kita akan menghitung subtotal untuk nama produk. Masukkan konfigurasi seperti pada gambar. Klik tombol OK.
   
   
8. Tunggu beberapa saat sampai proses grouping selesai. Anda akan mendapatkan tampilan baris subtotal pada akhir dari peralihan setiap nama produk.
   
   
9. Perhatikan juga di samping kiri dari worksheet muncul area margin baru dengan tanda plus (+) dan minus (-) yang digunakan untuk membuka (expand) rincian level dan sebaliknya melakukan grouping. Terdapat juga tombol urutan level yang dinomori dengan 1, 2, 3. Urutan level 1 adalah urutan teratas.
   
   
10. Cobalah menyusun layout dari worksheet sehingga mendapatkan hasil seperti pada gambar di bawah.
    
    
11. Selesai.

Menambahkan Subtotal Baru

Sekarang kita akan coba menambahkan subtotal berdasarkan “nama_kota” :

1. Klik cell A1.
2. Klik tab Data pada Ribbon.
3. Pada group Outline klik button Subtotal.
4. Masukkan konfigurasi seperti pada gambar. Pastikan opsi Replace current subtotals tidak aktif. Klik tombol OK.
   
   
5. Tunggu beberapa saat dan kita akan mendapatkan subtotal untuk nama produk dan nama kota. Pada margin area terlihat rincian level sudah pada sampai tingkat 4.
   
   
6. Selesai.

Menghilangkan Subtotal

1. Klik cell A1.
2. Klik tab Data pada Ribbon.
3. Klik tombol Remove All. 
4. Tunggu beberapa saat sampai dengan selesai.
5. Selesai.