Tag Archives: Name Define

Membuat Rekapitulasi Data Dengan DSUM

Pernahkan anda memiliki kasus untuk merekap data atau dalam pekerjaan anda diminta membuat rekapitulasi atas serangkaian data yang anda miliki. Misal merekap jumlah gaji per divisi pada sebuah institusi, nah untuk jelasnya perhatikan tampilan gambar dibawah ini.

Pada gambar ditampilkan misal anda memiliki serangkaian daftar gaji karyawan

Dari data diatas anda dimina laporan untuk membuat laporan jumlah komponen penghaslan dan potongan serta jumlah per divisi sehingga dimisalkan anda diminta ringkasan laporan daftar gaji seperti tampak pada gambar dibawah ini :

Formula apa yang akan anda gunakan ?? SUM ? ya bisa anda gunaka fungsi sum, namun akan sangat merepotkan apabila data pegawaian sangat banyak. Untuk masalah ini Excel menyediakan fungsi penjumlahan untuk database yaitu fungsi DSUM, fungsi DSUM itu sendiri adalah untuk menjumlahkan angka-angka dalam kolomdi database sesuai dengan kondisi yang anda tetapkan.

Untuk membuatnya rekap atas data yang dimiliki langkah-langkahnya adalah sebagai berikut:

Siapkan data dan simpan di worksheet pertama dan ganti namanya menjadi data.

Siapkan Format Rekapitulasi simpan di worksheet kedua dan ganti namanya menjadi rekap per divisi.

Pada saat anda membuat kolom di bagian rekapitulasi pastikan menggunakan nama kolom sesuai dengan nama kolom yang digunakan pada worksheet data, perhatikan gambar berikut :

Jadi ketika pada sheet data anda menggunakan kata divisi maka pada sheet rekap gunakan juga kata divisi, dan ketika pada kolom divisi di sheet data anda mengetik Gudang dan Logistik maka pada sheet rekap pun gunakan Gudang dan Logostik jangan menggunakan Gudang & Logistik Prinsipnya fungsi ini mencari kecocokan antara data dan kriteria yang anda tentukan !.

Buat nama range area dengan menggunakan fasilitas insert name define, adapun nama nama yang dibuat adalah :

Rumus dasar untuk menggunakan fungsi DSUM adalah :
DSUM(Database,Kolom,Kriteria)

Dari formula dasar diatas untuk mengisi Gaji Pokok Per Divisi, maka formulanya :
=DSUM(data,”Gaji Pokok”,div_keuangan)
=DSUM(data,”Gaji Pokok”,div_kepegawaian)
=DSUM(data,”Gaji Pokok”,div_gudang)
=DSUM(data,”Gaji Pokok”,div_umum)
=DSUM(data,”Gaji Pokok”,div_pemasaran)

Sedangkan untuk mengisi Tunjangan Per Divisi
=DSUM(data,”Tunjangan”,div_keuangan)
=DSUM(data,”Tunjangan”,div_kepegawaian)
=DSUM(data,”Tunjangan”,div_gudang)
=DSUM(data,”Tunjangan”,div_umum)
=DSUM(data,”Tunjangan”,div_pemasaran)

Dan mengisi Potongan Per Divisi
=DSUM(data,”potongan”,div_keuangan)
=DSUM(data,”potongan”,div_kepegawaian)
=DSUM(data,”potongan”,div_gudang)
=DSUM(data,”potongan”,div_umum)
=DSUM(data,”potongan”,div_pemasaran)

Sedangkan untuk mencari mencari Total Penghasilan Per Divisi, gunakan formula berikut :
GajiPokok + Tunjangan – Potongan

Sebagai tahap akhir mencari Total per jenis penghasilan, gunakan formula :
Gaji Pokok : sum(rekap_gapok)
Tunjangan : sum(rekap_gapok)
Potongan : sum(rekap_gapok)

Seperti halnya fungsi agregasi, Selain DSUM juga berlaku DMIN, DMAX, DCOUNT, dan DAVERAGE, untuk penggunaanya anda tinggal mengganti DSUM dengan DMIN, DMAX, DCOUNT dan DAVERAGE.

Sebagai bahan latihan silahkan anda

DOWNLOAD

file latihan session ini !

Menjumlahkan Untuk Kondisi Tertentu

Didalam Excel terdapat fungsi agregasi yang digunakan untuk menjumlahkan serangkaian angka yaitu fungsi sum. Fungsi sum akan sangat bermanfaat jika jumlah data yang akan kita jumlahkan sangat banyak, namun akan sangat merepotkan ketika akan menjumlahkan dengan kondisi tertentu. Nah untuk jelasnya silahkan perhatikan screenshot dibawah ini :

Dari laporan penjualan diatas untuk mencari berapa jumlah penjualan total maka kita gunakan fungsi SUM, namun bagaimana jika kita ingin mencari jumlah penjualan untuk jenis barang Monitor LCD 15′ atau barang yang lainnya ?? Nah jika kasus yang dihadapi adalah kasus seperti ini maka fungsi SUM sudah tidak efisien lagi untuk digunakan sebagai penggantinya kita gunakan fungsi SUMIF.

Adapun langkah-langkah untuk menghitung jumlah dengan fungsi sumif adalah sebagai berikut :

  • Dari gambar diatas buat tabel referensi jenis barang (atau sesuai kebutuhan untuk kasus anda) dengan cara mendata semua jenis barang (lihat tabel kecil sebelah kanan !).
  • Beri nama tabel referensi (Cell H6 s.d. H10) tersebut dengan nama jenis_barang, gunakan fasilitas insert name define.
  • Siapkan tabel sesuai kasus anda, dalam hal ini gunakan gambar tabel diatas.
  • Beri nama kolom jenis barang (Cell D6 sampai D16) yang terjual dengan nama barang_terjual, gunakan fasilitas insert name define.
  • Beri nama kolom harga Satuan (Cell F6 sampai F16) yang terjual dengan nama harga_barang, gunakan fasilitas insert name define.
  • Beri nama Cell F17 dengan nama kriteria, gunakan fasilitas insert name define.
  • Buat List Barang pada cell F17 dengan Data Validation type List, sehingga ketika di klik cell tersebut menampilkan kotak drop down jenis barang.
  • Pada Sel F17 ketik Formula berikut :

    =SUMIF(barang_terjual;kriteria;harga_barang)

Demikian contoh kasus sumif, silahkan anda berexperimen dengan menggunakan kriteria yang lain untuk menjumlahkan berdasarkan kriteria. Untuk mempermudah latihan silahkan anda download file latihannya.

DISINI

Membuat Form Biling Rental

Biling merupakan aplikasi tagihan untuk mengkalkulasi berapa biaya yang harus dibaya oleh pelanggan, pada kasus ini akan dibahas bagaimana membuat biling rental sederhana dengan excel. Prinsip pokoknya adalah melakukan pengurangan jam mulai dengan jam selesai , dimana hasilnya adalah selisih dalam satuan menit, kemudian selisih tersebut dibagi 60 untuk dikonversi menjadi selisih jam baru dikalikan dengan tarif perjam.

Untuk jelasnya perhatikan contoh form biling warnet dibawah ini :

Perhatikan gambar diatas, pengguna cukup memasukan PC yang dirental, Jam Mulai dan Jam Selesai serta menentukan tarif standar per jamnya, sedangkan kolom WAktu, Durasi dan Total akan diisi secara otomatis.

Untuk membuat form biling seperti tampak diatas , tahap pertama adalah membuat cell yang akan menampung besarnya tarif perjam, kemudian beri nama cell tersebut dengan nama tarif melalui menu insert -> name define

Pada kolom waktu diisi dengan mengurangi waktu selesai dengan waktu mulai, maka formulanya :

=E3-D3

Pada kolom  Durasi (Menit) adalah merubah kolom waktu menjadi menit dengan carai mengambil nilai jam dengan fungsi HOUR pada kolom waktu dan dikali 60 kemudian ditambah dengan nilai menit pada kolom waktu menggunakan fungsi MINUTE, sehingga formulanya adalah :

=(HOUR(F3)*60)+MINUTE(F3)

Pada kolom Total, membagi kolom WAktu (Durasi) dengan 60 dan mengkalikannya dengan cell tarif

=(G3/60)*tarif

Untuk jelasnya silahkan download file latihannya : DOWNLOAD

Menghitung Gaji Karyawan

Studi kasus kali ini akan menghitung gaji setiap karyawan pada suatu perusahaan, dari daftar gaji tersebut akan menghitung jumlah jam kerja masing-masing karyawan dan menghitung lembur dari masing karyawan serta menentukan besarnya penghasilan yang diterima baik dari gaji pokok, insentif dan pengurangan atas potongan, untuk jelasnya perhatikan gambar dibawah ini, pada gambar tersebut tugas anda hanyalah mengisi bagian yang berwarna hijau.

Sedangkan untuk pengisian kolom diberikan beberapa ketentuan seperti terlihat pada gambar dibawah ini :

Tahap-tahap pengisian

Sebagai langkah awal anda beri nama pada area cell berikut :

Nama Area Cell
lembur_per_jam I24
upah_per_jam I25
makan_per_hari I26
insentif_mingguan I27
jam_normal I28
pajak I29

Untuk memberikan penamaan pada cell silahkan baca http://latihanexcel.co.cc/2010/04/memberi-nama-pada-cell-area/

1. Pengisian Kolom Jumlah Jam Kerja
Pada cell I8 buat formula berikut :
=SUM(C8:H8)
formula diatas menjumlahkan jam kerja dari kolom hari senin sampai hari sabtu, selanjutnya copy paste pada baris berikutnya

2. Pengisian Kolom Jumlah Jam Lembur
Pada cell J8 buat formula berikut :
=IF(I8>(jam_normal*6);I8-(jam_normal*6);0)
formula diatas melakukan pengecekan dengan fungsi if, jika jumlah jam kerja (I8) lebih dari 48 (jam kerja normal x 6 hari) berarti terdapat lembur untuk mencarinya maka jumlah jam kerja dikurangi hasil perkalian antara jam kerja normal dengan 6 hari kerja, jika jumlah jam kerja lebih kecil dari 48 maka tidak ada lembur atau nol.
Setelah terisi lakukan format cell agar dibelakang angka hasil perhitungan terdapat satuan jam, caranya sebagai berikut :
Klik menu Format –> cell, pilih tab General dan pada bagian Caption pilih custom dan pada bagian tipe ketik ## “jam”, lihat gambar dibawah ini :

3. Pengisian Upah Pokok
Untuk mengisi upah pokok yaitu perkalian antara kolom jumlah jam kerja dengan upah per jam, maka formulanya adalah sebagai berikut :
=I8*upah_per_jam
Untuk menambahkan simbol rupiah di depan angka, klik menu format cell, kemudian pada tab general pilih accounting pada bagian Catgeory, jika tidak ada lambang rupiah lakukan perubahan pada seting regional komputer anda, silahkan baca http://latihanexcel.co.cc/prasyarat/ untuk mengubah regional seting komputer anda.

4. Pengisian Upah Lembur
Untuk mengisi lembur terlebih dahulu mengecek apakah karyawan yang bersangkutan memiliki jam lembur atau tidak ? jika ya lakukan perkalian jumlah jam lembur dengan upah lembur per jam nya jika tidak maka isi dengan nol, dari ketentuan ditersebut maka formulanya adalah :
=IF(J8>0;J8*lembur_per_jam;0)
Unuk menimbulkan lambang rupiah, silahkan lakukan format cell seperti pada langkah ke-2.

5. Pengisian Uang Makan
ketentuan untuk mengisi uang makan adalah uang makan akan dibayar hanya jika karyawan yang bersangkutan hadir atau jam kerja lebih dari nol, jika jam kerja pada hari yang bersangkutan adalah nol artinya tidak hadir berkerja dan tidak memperoleh uang makan harian, untuk itu dalam satu minggu dihitung berapa kali karyawan yang bersangkutan hadir dengan menggunakan fungsi COUNTIF dengan kriteria jam kerja lebih dari nol, maka formulanya adalah :
=(COUNTIF(C8:H8;”>0″)*makan_per_hari)

5. Pengisian Uang Insentif
Uang insentif hanya diberikan kepada karyawan yang tidak pernah absen dalam satu minggu, jika dalam satu minggu (6 hari) full terus bekerja (jam kerja harian tidak nol) maka dia berhak memperoleh uang insentif, untuk itu terlebih dahulu harus dicek dengan menghitung banyaknya hari dengan kriteria jam kerja lebih dari 0 dengan fungsi COUTNIF, maka formulanya:
=IF(COUNTIF(C8:H8;”>0″)=6;insentif_mingguan;0)

Untuk menampilkan format rupiah caranya seperti pada langkah ke-2.

7. Pengisian Gaji Kotor
Gaji kotor merupakan penjumlahan dari upah pokok, upah lembur, uang makan, dan insentif, maka formulaynya adalah :
=SUM(K8:N8)
Untuk menampilkan format rupiah caranya seperti pada langkah ke-2.

8. Pengisian Pajak
Pajak ditentukan dengan tarif 10% dari gaji kotor maka formulanya adalah :
=pajak*O8
Untuk menampilkan format rupiah caranya seperti pada langkah ke-2.

9. Pengisian Gaji Bersih
Gaji kotor diperoleh dari pengurangan antara gaji kotor dengan pajak, maka formulanya
=O8-P8
Untuk menampilkan format rupiah caranya seperti pada langkah ke-2.

10. Menghilangkan “0 jam” Pada Kolom Jumlah jam Lembur
Pada saat anda mengisi kolom jumlah jam lembur, terdapat karyawan yang memiliki 0 jam, sedangkan pada gambar diatas tidak tampak 0 jam, untuk melakukannya kita akan memberikan CONDITIONAL FORMATING, untuk itu blok kolom jumlah jam lembur, pilih menu Format -> Conditional Formating, maka akan tampak gambar dibawah ini :

pada gambar diatas pada kotak drop down pertama pilih Cell Value Is, kemudian pada drop down kedua pilih less then, dan pada kotak isian ketiga isi dengan angka 1, kemudian klik tombol format maka akan tampak gambar dibawah ini :

Pada gambar diatas klik color pilih wana yang sesuai dengan warna latar belakang kolom Jumlah jam lembur dalam hal ini warna hijau. kemudian klik tombol ok dua kali sampai kedua kotak dialog hilang, dan perhatikan hasilnya nilai 0 jam dihilangkan !
Maksud langkah-langkah diatas adalah jika nilai kolom jumlah jam lembur kurang dari 1 maka isi cell nya diberi warna yang sama dengan warna latar belakang cell, sehingga seolah-olah nilai 0 jam hilang.

11. Menghilangkan “Rp 0” Pada Kolom Upah Lembur
Untuk menghilangkan Rp 0 pada kolom upah lembur, blok kolom upah lembur dan lakukan conditional formating seperti pada langkah 10.

12. Mengisi Baris Total, Rata-rata, Terbesar dan Terkecil.
Untuk mengisi baris total, rata rata, terkecil dan terbesar gunakan fungsi SUM, AVERAGE, MIN dan MAX.
Untuk memudahkan studi kasus ini silahkan download file latihan studi kasus ini.

Menghitung Selisih Hari dan Jam

Pada contoh kasus ini akan dibahas bagaimana mehiug selisih hari dan jam pada sebuah usaha rental, Perhatikan gmbar dibawah ini, tugas anda hanya mengisi bagian yang berwarna kuning, yaitu lama dalam jumlah hari dan jam, total tagihan, discount, dan jumlah neto yang harus dibayar oleh penyewa, seta pada bagian akhir mentotalkan sera mencari waktu pengembalian terlama dan tercepat. Langkah-langkah pengisian

  • Sebelumnya tentukan terlebih dahulu area_cell pada kolom pembayaran bruto, Discount, Pembayaran neto, untuk membuat penamaan area cell silahkan baca artikel Memberi Nama Pada Cell Area
  • Pengisian lama peminjaman dalam hari (Kolom H8)
    =INT((F8-D8)+(G8-E8))
    Rumus diatas mengurangi kolom tanggal pengembalian dengan tanggal peminjaman dan hasilnya ditambah dengan hasil pengurangan kolom jam pengembalian dengan jam peminjaman, hasilnya akan berupa desimal dan dibulatkan ke bawah dengan fungsi INT().
  • Pengisian lama peminjaman dalam jam (Kolom I8)
    =INT(((F8-D8)+(G8-E8)-H8)*24)
    Rumus diatas mengurangi tanggal pengembalian dan peminjaman untuk mengetahui apakah peminjaman dilakukan pada tanggal yang sama atau tidak, kemudian hasilnya ditambah dengan pengurangan jam pengembalian dan jam peminjaman. Jika terjadi tanggal peminjaman dan pengembalian berbeda maka hasilnya dikurangi kolom H8, sehingga yang ditampilkan adalah selisih jamnya saja, hasil rumus tersebut dibulatkan ke bawah menggunakan fungsi INT().
  • Pengisian pembayaran bruto (Kolom J8)
    Kolom J8 adalah jumlah uang yang h arus dibayar oleh penyewa dengan ketentuan sewa per hari adalah Rp. 5.000 dan Rp 100 per jam, maka formulanya adalah :
    =(H8*5000)+(I8*100)
  • Pengisian discount  (Kolom K8)
    Kolom K8 adalah perhitungan diskon untuk penyewa yang mengembalikan kurang dari dua hari dengan besar discount sebesar 10% dari harga sewa yang harus dibayar, dan jika lewat dari dua hari maka tidak mendapat discount, maka formulanya adalah :
    =IF(H8<2,10%*J8,0)
  • Pengisian pembayaran neto (Kolom L8)
    Pembayaran neto merupakan hasil pengurangan dari pembayaran bruto-discount, maka formulanya :
    =J8-K8
  • Pengisian Peminjaman tercepat(Kolom H15)
    Peminjaman tercepat adalah lama hari pinjaman dengan nilai terkecil, untuk mencarinya gunakan fungsi MIN(area_cell) dan untuk menambahkan kata ‘hari’ dibelakangnya gunakan fungsi CONCATENATE(), maka formulanya :
    =CONCATENATE(MIN(hari_pinjam),” “,”hari”)
    dari formula diatas pertama mencari nilai terkecil dari jumlah hari kemudian nilainya dibagungkan dengan spasi (” “) dan kata “hari” dengan fungsi CONCATENATE
  • Pengisian Peminjaman terlama(Kolom H16), pada dasarnya sama dengan formula peminjaman tercepat, namun menggunakan fungsi MAX(area_cell)
  • Menghitung Kolom Total (Kolom J14,K14,L14)
    Untuk menghitungnya menggunakan formula =SUM(area_cell), dari contoh diatas maka rumusnya :
    Kolom Pembayaran Bruto : =SUM(bayar)
    Kolom Discount : =SUM(discount)
    Kolom Pembayaran Neto : =SUM(neto)

Untuk mempelajari kasus diatas, silahkan download file latihannya.

Memberi Nama Pada Cell Area

Penggunaan formula di excel sering melibatkan aktifitas mem-blok cell atau kita sebut cell area, agar pengaturan atau formula dapat berfungsi dengan baik.

Ada dua cara untuk memberi nama pada cell area yang telah di blok, yaitu :

Menggunakan Menu

1. Untuk memberi nama menggunakan menu, anda blok cell area yang akan diberi nama lihat gambar :

Kemudian pilih menu

2. Beri nama pada area yang di blok, jika telah selelsai Klik Add

3. Maka seluruh nama area yang telah disimpan akan ditampilkan.

4. Klik Ok untuk menutupnya

Menggunakan Cara Cepat
1. Blok Area yang akan diberi nama.
2. Klik kotak drop down diatas cell a1

3. Ketik nama area (tidak boleh pakai spasi), jika telah selesai tekan tombol enter
4. Untuk melihat semua nama area yang telah dibuat klik kotak dropdown tersebut, maka akan ditampilkan semua nama area, jika anda klik salah satu nama area maka, akan terblok area tertentu sesuai namanya

Penggunaan nama area ini untuk memudahkan dalam proses kalkulasi dan penggunaan fungsi dalam skala besar.