Tag Archives: Sum

Membuat Nota Penjualan Dengan Excel

Nota penjualan merupakan dokumen yang mutlak dibuat sebagai bukti fisik penjualan barang apabila toko tidak memiliki sistem yang komprehensif untuk mencatat transaksi penjualan. Untuk toko-toko kecil membuat nota bisa menggunakan bantuan excel, tidak perlu menggunakan aplikasi Point Of Sale (POS) kalau memang tidak memiliki dana untuk membelinya atau volume transaksi nya masih kecil.

Berikut adalah contoh membuat nota dengan excel menggunakan Fungsi Match dan  Fungsi Index yang telah dibahas pada artikel sebelumnya. Untuk jelasnya silahkan perhatikan screenshoot nota penjualan dibawah ini :

Perhatikan pada gambar diatas, tugas dari operator komputer untuk mengisi nota penjualan diatas hanya mengisi kolom kode barang dan jumlah sedangkan kolom yang lainnya (berwarna abu-abu) akan diisi secara otomatis mengambil dari daftar barang yang disimpan dalam tabel referensi seperti tampak pada gambar dibawah ini :

Untuk membuat nota seperti diatas berikut adalah langkah-langkahnya :

1. Buat tampilan nota seperti tampak pada gambar pertama !

2. Buat daftar barang yang akan jadi referensi seperti tampak pada gambar kedua, pada contoh diatas terdapat 4 buah barang kenyataannya toko atau warung pasti memiliki puluhan bahkan ratusan barang, jadi pada prakteknya data seluruh barang di toko atau warung.

3. Pada tabel referensi kolom nama barang (Cell K5:K8) diberi nama barang, gunakan fasilitas insert name define.

4.  Berikutnya masih pada tabel referensi kolom harga barang (Cell L5:L8) beri nama harga, gunakan faslitas insert name define.

5. Terakhir di tabel referensi beri nama kode pada kolom kode barang (Cell M5:M8) dengan fasilitas insert name define.

6. Pada formulir nota penjualan beri nama harga_satuan pada kolom harga (cell F9:F14) dengan fasilitas insert name define.

7. Pada formulir nota penjualan beri nama jml_terjual pada kolom harga (cell G9:G14) dengan fasilitas insert name define.

8. Pada formulir nota penjualan beri nama harga_x_jml pada kolom harga (cell H9:H14) dengan fasilitas insert name define.

10. Pada Cell G11, buat tanggal secara dinamis untuk membuat tanggal kwitansi dengan fungsi date menggunakan formula :

=now()

11. Agar kolom nama barang otomatis terisi ketika mengisi kode pada kolom kode barang, maka pada kolom nama barang (cell E9) ketik formula diawah ini dan copy pada baris dibawah nya :

=IF(NOT(ISBLANK(D9)),INDEX(data_barang,MATCH(D9,kode,0),1),””) 

Maksud dari formula diatas adalah apabila cell D9 / kolom kode barang  tidak kosong (Fungsi ISBLANK), lakukan pencarian data menggunakan Fungsi Index dan Fungsi Match pada tabel referensi, namun jika D9 kosong maka kolom nama barang pun akan kosong.

12. Berikutnya pada kolom harga baris pertama di formulir nota  ketik formula berikut :

=IF(NOT(ISBLANK(D9)),INDEX(data_barang,MATCH(D9,kode,0),2),0)

Maksud dari formula diatas adalah apabila cell D9 / kolom kode tidak kosong (Fungsi ISBLANK), lakukan pencarian data menggunakan Fungsi Index dan Fungsi Match pada tabel referensi, namun jika D9 kosong maka kolom nama barang pun akan kosong.

13. Pada kolom total (cell H9) ketik formula berikut untuk mengkalikan harga satuan dengan banyaknya barang yang dibeli :

=F9*G9

14. Pada baris Total DIbayar (Cell H15) ketik formula berikut :

=SUMPRODUCT(harga_satuan,jml_terjual)

Formula diatas dimaksudkan menjumlahkan hasil perkalian dari tabel harga dengan tabel Jml pada nota penjualan, atau bisa juga menggunakan formula :

=SUM(harga_x_jml)

Simpan dokumen anda, sebagai latihan silahkan download file latihan diatas.

DOWNLOAD

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

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.

Hasil Studi Mahasiswa

Pada studi kasus kali ini akan membahas bagaimana membuat kartu hasil studi (KHS) untuk mahasiswa sekolah tinggi dimana setiap mata kuliah diberi nilai dengan huruf antara A sampai dengan E dengan bobot 4 untuk nilai A sampai 0 untuk nilai E. Setiap mata kuliah memiliki kode sendiri yang sifatnya unik terdiri dari 6 digit, digit terakhir merupakan jumlah SKS yang akan menjadi faktor pengali dengan bobot nilai.

Untuk  jelasnya perhatikan gambar dibawah ini

Mari kita bahas satu persatu kolom diatas, kolom diatas yang akan dibuat formulanya adalah kolom Mutu, SKS, dan kolom Mutu x SKS. Ketiga kolom tersebut akan terisi secara otomatis saat anda mengisikan nilai pada kolom nilai.

Untuk membuat formula pada  kolom nilai baris ke-13 kita gunakan fungsi if dengan formula sebagai berikut :

=IF(F13=”A”;4;IF(F13=”B”;3;IF(F13=”C”;2;IF(F13=”D”;1;O))))

formula diatas artinya jika cell F13 (kolom nilai) berisi  A maka kolom mutu secara otomatis terisi dengan angka 4, jika B terisi angka 3, jika C terisi angka 2 jika nilainya D terisi angka 1
dan jika tidak cocok semuanya adalah 0, kurung tutup akhir formula jumlahnya sebanyak jumlah if di dalam formula, if didalam formula tersebut terdapat empat if jadi kurung tutupnya sejumlah empat buah.

Selanjutnya kita mengisi kolom SKS dengan ketentuan jumlah SKS adalah digit terakhir dari kode mata kuliah, misal jika kode mata kulian ESP203 maka SKS nya adalah 3, untuk itu kita gunakan fungsi text yaitu
fungsi right, namun hasil dari fungsi text adalah character meskipun secara visual berupa angka, karena tipe datanya character maka tidak bisa dikalkukasikan, agar hasilnya berubah menjadi numeric sehingga mudah untuk dikalkulasikan hasil dari fungsi right kita konversi ke numeric dengan fungsi value, sehingga kolom SKS akan kita isi dengan rumus sebagai berikut :

=VALUE(RIGHT(D13;1))

Setelah kolom SKS terisi dengan formula diatas saatnya mengisi kolom
Mutu x SKS dengan dengan menggunakan formula perkalian matematika bisa yaitu :

=G13*H13

Sedangkan untuk mengisi kolom total dari kolom SKS dan Mutu x Bobot menggunakan fungsi database yaitu fungsi sum masing masing dengan rumus :

Total kolom SKS

=SUM(H13:H19)

Total mutu x bobot

=SUM(I13:I19)

Dan index prestasi diisi dengan formula pembagian antara total mutu x bobot dengan total sks dan membulatkan sampai dengan dua angka dibelakang koma menggunakan fungsi round dengan rumus :

=ROUND(I20/H20;2)

Terakhir adalah menetukan beban SKS yang boleh diambil oleh mahasiswa
yang bersangkutan dengan ketentuan jika indeks prestasinya kurang dari sama dengan 3 boleh mengambil maksimal 24 SKS, jika indeks prestasi kirang dari 2,75 boleh mengambil 22 SKS dan jika indeks prestasinya kurang dari 2 maka maksimal boleh mengambil 18 SKS, dengan menggunakan fungsi if maka ketentuan tersebut dibuat rumusan sebagai berikut :

=IF(I22>=3;24;IF(I22>2,75;22;IF(I22>2;20;18)))

Nah sampai disini anda telah mempelajari fungsi if, right, sum, value, dan round. Silahkan download file latihan excel nya untuk anda pelajari lebih lanjut.