Memberi Warna pada Angka dengan Custom Format

Warna adalah ekspresi yang sangat baik untuk memberi indikator bahwa ada sesuatu yang perlu diperhatikan, terutama jika digunakan untuk menjelaskan data kita di Excel.

Sebagai contoh, warna merah sering digunakan untuk mengindikasikan terjadi penurunan angka penjualan, yang tentunya sangat tidak diinginkan oleh para bos. Warna hitam / hijau sering digunakan untuk hal sebaliknya, memberi indikasi bahwa semua berjalan sesuai yang ditargetkan.

Ok, jadi karena sedemikian pentingnya hal ini, bagaimana kita melakukan pewarnaan sesuai kondisi yang kita inginkan pada Excel ?

Sederhana, artikel berikut akan menunjukkan caranya, dengan menggunakan custom format.

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 : Menghitung Diskon atau Potongan Harga di Excel

Kasus

Jika Ani membeli baju kaos seharga Rp 60,000.- sepotong. Diskon per baju adalah 10%, dan ada tambahan diskon 5% per baju jika terjadi pembelian di atas 10 potong baju.

Susunlah formula untuk total pembayaran yang harus dilakukan oleh Ani, dan hitung pembelian untuk :

1. 8 potong baju.
2. 15 potong baju.

Jawab

Cobalah susun tampilan worksheet seperti pada gambar berikut ini.

Kemudian pada cell E2, kita akan masukkan rumus perhitungan diskon dan juga fungsi IF karena ada kondisi potongan tambahan jika pembelian lebih dari 10.

Rumus perhitungan harga setelah diskon adalah sebagai berikut :

     Harga Akhir = Harga Awal * Jumlah * (1 - Persentase Diskon)

atau 

     Harga Akhir = Harga Awal * Jumlah * (100% - Persentase Diskon)

Dengan berdasarkan rumus di atas maka kita masukkan rumus selengkapnya pada cell E2 sebagai berikut :

     =IF(D2<=10,A2*D2*(1-B2), A2*D2*(1-(B2+C2)))

atau

     =IF(D2<=10,A2*D2*(1-B2), A2*D2*(1-B2-C2))

Terlihat bahwa kita menggunakan fungsi IF untuk melakukan pengecekan apakah pembelian lebih dari 10 (pada cell D2). Jika iya, maka perhitungan diskon cukup dari diskon per potong (B2). Dan jika tidak, maka terdapat tambahan diskon dari C2, dengan total diskon nilai dari cell B2+C2.

Jawaban total harga yang harus dibayar dari pertanyaan di atas adalah :

1. Rp 432,000.-
   
   
   
2. Rp 765,000.-
   
   

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.

Contoh Kasus : Pencarian Multi Variable dengan Match dan Index

Pertanyaan

Saya lagi cari rumus utk bisa mengembalikan nilai dari pencarian yang menggunakan 3 variabel.. misalnya saya punya sheet Daftar Harga Barang dgn kolom sbb:

• Kolom 1 : Berupa Kode/ID
• Kolom 2 : Berupa tipe konsumen (reseller/langsung)
• Kolom 3 : Harga barang.

di sheet yang lain saya punya rincian transaksi penjualan yang disusun perhari. Pada sheet ini sy punya kolom:

• Kolom 1: Tanggal
• Kolom 2: Kode/ID
• Kolom 3: Tipe konsumen

Nah saya menginginkan pada Kolom 4 dapat muncul harga barang tersebut utk Kode dan tipe konsumen yg sudah saya catat sebelumnya.

Bagaimana caranya? function apa yg perlu sy gunakan dan gimana rumusnya.

Video Contoh Penggunaan VLookup pada Excel 2007

Berikut adalah video contoh penggunaan VLookup dari kami, dengan susunan yang sederhana dan mudah dimengerti. Semoga bermanfaat.