Excel : Memberi Simbol Mata Uang

Seringkali dalam membuat laporan keuangan, kita harus mencantumkan simbol mata uang rupiah sehingga yang membaca tahu bahwa nilai itu adalah nilai nominal uang. Hal ini sebetulnya sepele sih, hanya tidak ada salah-nya saya menulis tentang cara pemberian simbol mata uang di dalam laporan kita ini (keliatan baru ngga dapat ide menulis artikel hehehehe…). Untuk itu saya ingin menulis 2 cara pemberian simbol mata uang negara kita (Rupiah).

Sebelumnya, cek dulu apakah setting regional kita sudah mengarah ke negara kita, Indonesia. Caranya:

1. Klik Start -> Control Panel.
2. Di Control Panel, klik ganda Regional and Language Options.
   
3. Pastikan di combo Customize to choose your own format: adalah Indonesia, begitu juga dengan Location.
   
4. Klik tombol OK, nah sekarang kita sudah siap memberikan simbol mata uang-nya di Excel.

Cara yang pertama:

1. Blok cell yang ingin di beri simbol.
2. Klik kanan range cell yang telah terblok itu, pilih menu Format Cells…
3. Di tab Number, pilih Currency, pilih isian combo Symbol dengan Rp.
   
4. Klik OK untuk mengeksekusi format-nya.

Cara yang kedua:

1. Memakai formula DOLLAR. Formula ini akan menambahkan secara otomatis suatu nilai dengan mata uang negara pada setting Regional and Language Options.
2. Cara penggunaannya sangat mudah,=DOLLAR(cell_dari_nilai_yang_ingin_diberi_simbol_dollar). Misalnya nilai ada di cell A1, maka penggunaannya cukup dengan mengetik=DOLLAR(A1).
   
3. Untuk contoh penggunaan formula DOLLAR, bisa di unduh di sini.

Semoga bermanfaat…

Menghitung Besar Pokok Hutang dengan Excel

Artikel ini akan membahas lagi tentang dunia hutang-menghutang (ngga papa ya…), setelah membahas bagaimana cara menghitung cicilan dari satu pokok hutang, lalu caramenghitung bunga hutang, kali ini kita akan belajar tentang bagaimana menghitung besar pokok hutang.

Untuk menghitung pokok hutang, Excel sudah menyediakan formula bernama PV. Penggunaan PV ini cukup mudah, yang perlu anda ketahui adalah nilai bunga yang berlaku, lama angsuran dan besar cicilan tiap bulan-nya.

PV sendiri memiliki format parameter seperti ini:=PV(bunga_tahunan/12;lama_angsuran;besar_cicilan). Jadi misalnya (saya ambil dari contoh yang saya sertakan di artikel ini, besar bunga saya tulis di cell B3, lama angsuran di B4 dan besar cicilan di B5, maka saya dapat menulis formula seperti ini: =PV(B3/12;B4;B5) lalu tekan Enter untuk mengeksekusi formula tersebut.

Secara langsung, Microsoft Excel akan menyajikan besaran pokok hutang yang anda maksud. Semoga artikel ini dapat sedikit membantu rekan-rekan.

Excel : Gunakan Replace Aja…

Jika rekan-rekan mempunyai masalah seperti sirajabrewok ini, yaitu menghilangkan karakter-karakter tertentu yang tidak diinginkan dalam sheet atau range cell di dokumen Excel anda, mungkin trik sederhana berikut ini bisa membantu rekan-rekan.

Sebetulnya masalahnya apa sih? Begini kalau apa yang saya tangkap dari kesulitan mas rajabrewok adalah menghilangkan karakter Rp dan titik separatornya.

Saya yakin ada cara dengan formula seperti REPLACE yang dapat menggantikan karakter tertentu menjadi karakter lain. Tapi ada trik yang sangat sederhana yang mungkin bisa menggantikan penggunaan formula REPLACE tersebut. Yaitu menggunakan menu Find & Replace.

Berikut langkahnya:

1. Blok dulu data yang ingin anda ubah format datanya atau ingin menghilangkan karakter tertentu-nya. Atau jika ingin mengubah semua dalam satu sheet anda tidak perlu blok.
2. Tekan kombinasi tombol Ctrl + H, untuk memanggil menu Replace.
3. Karena dalam kasus ini kita ingin menghilangkan karakter Rp , ketikan Rp diFind what dan biarkan kosong untuk input Replace what. Lalu tekan tombolReplace All.
   
4. Semua karakter Rp akan hilang, untuk menghilangkan karakter titik, ulangi langkah 3 tapi isikan karakter titik (.) di masukan Find what, maka semua titik akan hilang sekarang anda bisa mengolah data tersebut seperti angka biasa.
   

Semoga bisa membantu…..

Melihat Data Dengan Peringkat Tertentu dengan Excel

Jika kita mempunyai satu range data penjualan misalnya, pasti kita ingin juga menganalisanya. Salah satu analisa yang sering di lakukan terhadap data penjualan adalah dengan melihatperingkat dari data-data tersebut atau ingin melihat di angka berapakah penjualan dengan peringkat 3. Kalau ingin melihat peringkat pertama sih, kita bisa menggunakan formula MAX yang akan menampilkan nilai tertinggi dari satu kumpulan data.

Nah, untuk melihat data dengan peringkat alias urutan tertentu, kita bisa menggunakan formula LARGE untuk melihat peringkat dari peringkat terbesar dan formula SMALL untuk melihat sebaliknya. Penggunaan kedua formula tersebut tidaklah susah. Untuk menggunakan LARGE kita dapat menuliskan formula tersebut dengan aturan sebagai berikut: =LARGE(range_data; urutan_yang_diinginkan). Penggunaan SMALL juga sama, yaitu=SMALL(range_data; urutan_yang_diinginkan).

Misalnya kita mempunyai data di cell B4 sampai B24, dan kita ingin mengetahui data dengan peringkat 2 tertinggi, kita bisa menuliskan formula-nya seperti berikut:=LARGE($B$4:$B$24;2). Sebaliknya jika kita ingin mengetahui rangking 2 dari data terkecil dari range data yang sama dengan contoh di atas, anda bisa menuliskannya sebagai berikut =SMALL($B$4:$B$24;2).

Cukup mudah kan? Jika ingin melihat contoh secara lengkapnya, Anda dapat mengambilnya di sini.

Semoga artikel ini bisa sedikit berguna.

Menghitung Jumlah Cell Yang Terisi

Langsung aja, di artikel ini kita akan belajar kebalikan dari cara menghitung jumlah cell yang kosong di Microsoft Excel. Rumus yang kita gunakan ada COUNTA.

Untuk pemakaian COUNTA sama persis dengan COUNTBLANK, yaitu=COUNTA(range_cell). Misalnya data kita ada di cell A1 sampai A20, maka kita dapat menuliskannya sebagai berikut =COUNTA(A1:A20).

Sebagai hasilnya adalah jumlah cell dalam range yang terisi dengan data. Anda dapat mengambil contoh yang saya sediakan jika kurang jelas.

Mengenal Formula SUMIFS di Excell

Penjumlahan dengan kriteria tertentu bisa dilakukan dengan formula SUMIF dengan sangat sederhana. Hanya saja ada kelemahannya ketika menggunakan SUMIF untuk menghitung data dengan range nilai untuk kriteria. Misalnya ingin menghitung data dari tanggal 1 sampai 7. Jika memakai SUMIF, kita harus menambahkan hasil SUMIF dengan kriteria 1, lalu dengan kriteria 2 dan ini menjadi tidak efisien. Untuk hal itu di MS Office 2007, Excel menyediakan formula dengan kegunaan SUMIF tetapi dengan multi kriteria, formula baru tersebut bernama SUMIFS.

Penggunaan SUMIFS menjadi lebih sederhana, karena kita diijinkan memasukan kriteria terhadap sum range lebih dari 1. Misalnya kita ingin menjumlahkan data dari nomor 1 sampai dengan 7 (pada file contoh yang saya sertakan). Kolom kriteria ada di range A4 sampai A16 dan sum range dari C4 sampai C16. Maka untuk menjumlakannya jika kita memakai formula SUMIF maka kita harus harus menuliskan rumus sebagai berikut:

=SUMIF(A4:A16;1;C4:C16)+SUMIF(A4:A16;2;C4:C16)+SUMIF(A4:A16;3;C4:C16)+SUMIF(A4:A16;4;C4:C16)+SUMIF(A4:A16;5;C4:C16)+SUMIF(A4:A16;6;C4:C16)+SUMIF(A4:A16;7;C4:C16)

Tapi dengan SUMIFS kita cukup menuliskan seperti ini:

=SUMIFS($C$4:$C$16;$A$4:$A$16;">=1";$A$4:$A$16;"<=7")

Jauh lebih efisien kan? Jika kita melihat contoh SUMIFS diatas maka sintaks formula nya adalah sebagai berikut: SUMIFS(sum_range; range_kriteria_1; kriteria_1; range_kriteria_2; kriteria_2... dst)

Menulis Jam Dengan Lebih Cepat di Excel

Jika anda adalah seorang staff HRD, tentu menuliskan atau mengurus absensi karyawan adalah pekerjaan seharia anda. Saya pun pernah merasakan ngga enaknya menuliskan data jam dalam jumlah banyak. Bagi saya yang membuat ketidak nyamanan penulisan jam adalah pada tanda titik dua (:). Untungnya, hal ini bisa di buat lebih mudah.

Caranya cukup mudah:

1. Klik kanan di cell dimana kita akan menuliskan data jam, anda bisa membloknya dulu jika kita ingin memformat pada banyak cell sekaligus, lalu pilih menu Format Cells…
2. Di Category, pilih Custom, lalu ketikan 00″:”00. Kemudian klik tombol OK.
   
3. Nah, sekarang coba ketikan 4 angka jam tanpa tanda titik dua, Excel akan otomatis menambahkan tanda titik dua pada data anda.

Mengetahui Tanggal Akhir Bulan dengan Excel

Tanggal terakhir dari satu bulan biasanya adalah tanggal ‘penting’ dalam pelaporan keuangan kita. Biasanya saya ketika membuat satu laporan keuangan, saya melihat kalender di atas meja kerja saya dan memasukannya ke sheet Excel satu satu persatu secara manual. Tentu hal ini adalah suatu pekerjaan yang merepotkan dan menjemukan. Sebetulnya Excel sudah menyediakan satu formula untuk menangani hal ini.

Formula ato rumus tersebut adalah EOMONTH. Penggunaannya sangatlah mudah yaitu =EOMONTH(tanggal, offset). Untuk tanggal kita tinggal menuliskan sebuah tanggal di satu bulan yang ingin kita lihat tanggal akhir bulannya, untukoffset adalah apakah kita ingin memajukan atau memundurkan bulan dari data yang kita masukan. Misalnya jika tanggal saya isi dengan 06/06/2009, jika offset saya isi dengan 0 maka hasil yang ditampilkan adalah bulan yang bersangkutan dan jika saya masukan nilai offset adalah 2 maka yang ditampilkan di hasil adalah akhir bulan dari 2 bulan setelah Juni yaitu Agustus.

Nah, langsung saja pada contoh dari rumus ini. Jika saya ingin melihat tanggal terakhir dari bulan Juni, maka rumus yang harus saya tulis adalah=EOMONTH(6/6/2009, 0). Untuk tanggal bisa anda tuliskan sembarang asal bulannya adalah bulan yang anda ingin cari tanggal terakhirnya.

Oya, anda perlu mengaktifkan Add-ins Analysis ToolPak (melalui menu Tools ->Select Add-ins…) untuk menggunakan rumus ini. Hanya sebagai catatan, ketika saya mencobanya di Excel 2007 saya tidak mendapatkan kesalahan dalam penggunaan EOMONTH ini.

Untuk melihat contoh rumus ini, anda dapat mengunduhnya di sini.

Menghitung Cell Yang Kosong Pada Excel

Dalam pengisian dari data-data yang kita lakukan di Microsoft Excel, terkadang ada beberapa cell yang kita biarkan kosong, karena memang tidak ada entry value untuk cell itu. Hal itu bisa terjadi karena memang tidak ada penjualan misalnya di hari itu sehingga kita membiarkannya kosong. Jika memang demikian keadaannya, tentu kita suatu kali pernah ingin menghitung, berapa kali sih tidak ada penjualan? Atau berapa banyak cell yang kosong?

Daripada kita menghitung secara manual, ada satu formula milik Microsoft Excel untuk melakukan tugas tersebut. Formula tersebut ada COUNTBLANK.

Penggunaan dari COUNTBLANK sangat mudah, tinggal ketik=COUNTBLANK(range_awal:range_akhir). Misalnya range isian omzet ada di cell B2 sampai B10, maka penulisan formula yang benar adalah=COUNTBLANK(B2:B10).

Maka dengan cepat kita bisa mengetahui berapa cell-kah yang dibiarkan dalam keadaan kosong. Anda bisa mengunduh contoh file artikel ini.

Semoga bermanfaat….

PivotTable Dan PivotChart Pada Excel

I. Membuat PivotTable

PivotTable adalah salah satu fasilitas didalam Excel, dimana ia berupa table interaktif yang sangat cepat dalam menganalisa, mengkombinasikan dan membandingkan sejumlah data baik sedikit maupun banyak. Dalam PivotTable, kita dapat memilih suatu baris atau kolom yang hendak dijadikan referensi dalam melihat jumlah total dari suatu data. PivotTable hanya bersifat report / laporan, artinya Anda tidak dapat mengedit data pada PivotTable. Jika ingin mengedit data, maka Anda harus melakukannya pada table aslinya (sumber data). Adapun PivotChart adalah sama saja dengan PivotTable, hanya saja ia ditampilkan berupa Grafik. Untuk lebih jelasnya, dapat melihat contoh berikut ini:







Gambar 1. Contoh penggunaan PivotTable

Penjelasan dari nomor-nomor yang berwarna biru pada gambar 1 diatas adalah sebagai berikut:

1) Sumber data (Source data / table aslinya)
2) Sumber nilai sales bulan Jan untuk pensil 2B
3) Laporan PivotTable
4) Jumlah sales bulan Jan untuk pensil 2B
5) PivotChart yang menyatakan grafik dari laporan PivotTable (no.3)

Terlihat gambar 1 diatas, untuk menampilkan total sales per-bulan untuk masing-masing pensil, kita dapat menggunakan PivotTable. Baiklah, kita mulai saja latihan dalam membuat PivotTable ini. Silahkan ikuti langkah-langkah dibawah ini:

1. Buatlah table “Daftar Penjualan Barang” seperti dibawah ini :



Gambar 2. Daftar penjualan barang 


2. Sorotlah range B4:E18.

Catatan: dalam menyorot range, harap mencangkup seluruh nama field (judul kolom) berikut dengan seluruh data yang ada dibawahnya.

3. Klik menu Data è PivotTabel and PivotChart Report. Maka kotak dialog PivotTabel and PivotChart Wizard – Step 1 of 3 akan ditampilkan.



Gambar 3. Kotak dialog PivotTabel and PivotChart Wizard – Step 1 of 3

4. Pada kotak dialog PivotTabel and PivotChart Wizard – Step 1 of 3 ini terdapat 4 pilihan pada “Where is the data that you want to analyze ?”, yaitu:

· Microsoft Excel lists or databases

Pilihan ini jika sumber data yang hendak dibuat PivotTablenya berasal dari Excel worksheet.

· External data sources

Pilihan ini jika sumber data yang hendak dibuat PivotTablenya berasal dari database external, seperti Microsoft SQL Server, Microsoft Access database, text file database (misalnya dBase), dll.

· Multiple consolidation ranges

Pilihan ini jika sumber data yang hendak dibuat PivotTablenya berasal dari beberapa (lebih dari satu) Excel worksheet.

· Another PivotTable report

Pilihan ini jika sumber data yang hendak dibuat PivotTablenya berasal dari PivotTable lainnya (bukan dari PivotChart).

Pada latihan ini, pilihlah : Microsoft Excel lists or databases



5. Juga pada kotak dialog PivotTabel and PivotChart Wizard – Step 1 of 3 ini terdapat 2 pilihan pada “What kind of report do you want to create ?”, yaitu:

· PivotTable

Pilihan ini jika kita hendak membuat suatu PivotTable

· PivotChart report

Pilihan ini jika kita hendak membuat PivotChart berikut dengan PivotTable-nya.

Pada latihan ini, pilihlah : PivotTable

6. Klik tombol Next. Maka akan muncul kotak dialog PivotTabel and PivotChart Wizard – Step 2 of 3 sbb:



Gambar 4. Kotak dialog PivotTabel and PivotChart Wizard – Step 2 of 3

Karena pada awalnya kita sudah menyorot Range B4:E18, maka secara otomatis pada field Range sudah terisi range yang kita sorot tersebut.

7. Klik tombol Next. Maka akan muncul kotak dialog PivotTabel and PivotChart Wizard – Step 3 of 3 sbb:



Gambar 5. Kotak dialog PivotTabel and PivotChart Wizard – Step 3 of 3

8. Klik tombol Layout. Maka akan muncul kotak dialog sbb:


Gambar 6. Kotak dialog Layout pada PivotTabel and PivotChart Wizard – Step 3 of 3

Pada kotak dialog ini, klik & drag (digeser dengan mouse kiri tidak dilepas sampai objek telah sampai ke tempat tujuan) tombol-tombol nama field yang ada disebelah kanan ke diagram PivotTable yang disediakan. Silahkan ikuti langkah-langkah ini:

· Tombol “Barang” ditempatkan (klik & drag) ke ROW

· Tombol “Kota” ditempatkan (klik & drag) ke COLUMN

· Tombol “Unit” ditempatkan (klik & drag) ke DATA

· Tombol “Bulan” ditempatkan (klik & drag) ke PAGE

Sehingga akan didapatkan hasil sbb :


Gambar 7. Hasil yang diperoleh setelah tombol-tombolnya di geser ke diagram PivotTable

Kemudian klik tombol OK. Kemudian kotak dialog PivotTabel and PivotChart Wizard – Step 3 of 3 akan aktif kembali. Pada kotak dialog PivotTabel and PivotChart Wizard – Step 3 of 3, pilihlah New Worksheet.


9. Klik tombol Finish. Dengan langkah ini, maka akan muncul sheet baru lengkap dengan PivotTable-nya. Lihat gambar dibawah ini:


Gambar 8. PivotTable untuk Daftar Penjualan Barang PT.Mastermedia

Terlihat pada gambar 8 diatas, pada field Bulan, Barang, dan Kota berupa ComboBox yaitu suatu field berjenis pilihan, dimana kita bisa memilih checklist ketika kita klik field tersebut. Silahkan dicoba untuk klik field Barang (Sell A4). Akan ditampilkan gambar sbb :



Gambar 9. Pilihan yang tersedia dalam menampilkan Barang apa saja yang hendak diperlihatkan di PivotTable

II. Mengedit / Mengubah Layout (tampilan) PivotTable

Apabila diperlukan, kita dapat mengedit tampilan PivotTable ini, seperti mengubah posisi tombol-tombol nama field yang ada pada layout PivotTable ke posisi yang lain sesuai yang kita inginkan. Sebagai contoh, silahkan Anda coba menempatkan field “Bulan” pada posisi Row berdampingan dengan field “Barang”. Caranya adalah, field “Bulan” tersebut di klik & drag ke posisi ROW. Hasilnya dapat dilihat pada gambar 10 dibawah ini.



Gambar 10. Hasil yang diperoleh setelah field “Bulan” di klik & drag ke posisi ROW

III. Mengedit Data Sumber PivotTable

Sebagaimana sudah saya jelaskan sebelumnya, kita tidak dapat mengedit data di dalam PivotTable. Kita hanya dapat mengedit pada sumber data. Setelah mengedit data, jangan lupa PivotTable tersebut harus di refresh. Caranya, klik kanan PivotTable tersebut, lalu pilih menu Refresh Data. Atau bisa juga dengan mengklik icon dengan gambar tanda seru () yang terdapat pada toolbar PivotTable.


IV. Membuat PivotChart

Untuk membuat PivotChart, pastikan toolbar PivotTable belum Anda close. Gambar toolbar-nya adalah sbb :



Gambar 11. Toolbar PivotTable.

Klik tombol pada toolbar PivotTable. Maka akan muncul sheet baru yang menampilkan grafik dari PivotTable yang sedang Anda buka ini.


Gambar 12. Tampilan Grafik (PivotChart)

Apabila dibutuhkan, Anda dapat mengedit tampilan grafik ini sesuai yang Anda inginkan. Untuk mengeditnya, lakukan langkah-langkah berikut ini :

· Klik gambar grafik tersebut.
· Klik kanan mouse Anda, pilih : Chart Type. Maka akan muncul kotak dialog sbb :


Gambar 13. Kotak dialog Chart Type

· Pilihlah Chart Type : Column dan Chart sub-type seperti yang terdapat pada gambar 13 diatas (background warna hitam).
· Klik tombol OK.
· Maka akan ditampilkan grafik baru sbb :


Gambar 14. Hasil yang diperoleh setelah mengedit Chart Type.