Category Archives: Studi Kasus

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

Data Validation # Drop Down Button

Pada contoh kasus kali ini penulis akan memberikan contoh kasus penggunaan data validating dengan drop down button, perhatikan gambar dibawah ini :

Penggunaan drop down pada kasus dimana anda akan memberikan pilihan yang harus diisi oleh user, dimana user tidak diperkenankan memilih/menginput yang lain, sehingga kesalahan user melakukan input data bisa dihindari agar konsistensi data bisa terjaga, sebagai contoh jika anda mendata pekerjaan pada kepala keluarga di sebuah kota, maka user yang menginput bisa saja mengetik banyak variasi hanya untuk menyebutkan pekerjaan Pegawai Negeri Sipil, kemungkinan ada yang diisi PNS atau Pegawai Negeri Sipil atau bahkan PN Sipil padahal ketiganya memiliki arti dan maksud yang sama. Untuk memaksa user memilih salah satu yang telah kita tentukan maka kita bisa menggunakan fasilitas Validation Rule dengan memilih tipe Drop Down Button (List), seperti tampak pada gambar diatas !

Sekarang mari kita buat form seperti tampilan diatas !

  1. Sebagai langkah awal siapkan tabel seperti pada gambar diatas pada sheet 1 dan ganti nama sheet menjadi form
  2. berikutnya buat tabel referensi pada sheet kedua dan beri nama referensi pada sheet 2
  3. Setelah membuat referensi seperti tampak pada gambar diatas, blok cell yang berisi laki-laki sampai perempuan, kemudian beri nama cell dengan nama jenis_kelamin menggunakan fasilitas name define, dan blok seluruh tipe pekerjaan dan beri nama cell dengan pekerjaan menggunakan fasilitas name define
  4. Kembali ke sheef form yang berisi tabel seperti pada gambar pertama, blok kolom jenis kelamin (judul kolom jangan diblok), kemudian pilih menu Data –> Validation Rule (lihat gambar)

  5. Kemudian akan tampak kotak dialog validation rule seperti pada gambar dibawah ini


    Dari gambar diatas pada tab setting, pada bagian Allow pilih List, kemudian cek list pilihan In_cell dropdown, dan pada bagian source ketik formula =jenis_kelamin (nama disesuaikan ketika anda membuatnya di langkah ke-3).
  6. Kemudian klik tab Pada contoh kasus kali ini penulis akan memberikan contoh kasus penggunaan data validating dengan drop down button, perhatikan gambar dibawah ini :

    Penggunaan drop down pada kasus dimana anda akan memberikan pilihan yang harus diisi oleh user, dimana user tidak diperkenankan memilih/menginput yang lain, sehingga kesalahan user melakukan input data bisa dihindari agar konsistensi data bisa terjaga, sebagai contoh jika anda mendata pekerjaan pada kepala keluarga di sebuah kota, maka user yang menginput bisa saja mengetik banyak variasi hanya untuk menyebutkan pekerjaan Pegawai Negeri Sipil, kemungkinan ada yang diisi PNS atau Pegawai Negeri Sipil atau bahkan PN Sipil padahal ketiganya memiliki arti dan maksud yang sama. Untuk memaksa user memilih salah satu yang telah kita tentukan maka kita bisa menggunakan fasilitas Validation Rule dengan memilih tipe Drop Down Button (List), seperti tampak pada gambar diatas !

    Sekarang mari kita buat form seperti tampilan diatas !

    1. Sebagai langkah awal siapkan tabel seperti pada gambar diatas pada sheet 1 dan ganti nama sheet menjadi form
    2. berikutnya buat tabel referensi pada sheet kedua dan beri nama referensi pada sheet 2
    3. Setelah membuat referensi seperti tampak pada gambar diatas, blok cell yang berisi laki-laki sampai perempuan, kemudian beri nama cell dengan nama jenis_kelamin menggunakan fasilitas name define, dan blok seluruh tipe pekerjaan dan beri nama cell dengan pekerjaan menggunakan fasilitas name define
    4. Kembali ke sheef form yang berisi tabel seperti pada gambar pertama, blok kolom jenis kelamin (judul kolom jangan diblok), kemudian pilih menu Data –> Validation Rule (lihat gambar)

    5. Kemudian akan tampak kotak dialog validation rule seperti pada gambar dibawah ini


      Dari gambar diatas pada tab setting, pada bagian Allow pilih List, kemudian cek list pilihan In_cell dropdown, dan pada bagian source ketik formula =jenis_kelamin (nama disesuaikan ketika anda membuatnya di langkah ke-3).
    6. Kemudian klik tab input message, pada bagian Tittle pilih judul untuk validation rule dan pada bagian input message, masukan pesan yang akan ditampilkan, sehingga akan tampak seperti pada gamabr dibawah ini

    7. Dan pada tab Error Alert, ceklist pilihan Show error alert…, sedangkan pada bagian style anda pilih Error, serta masukan pesan error ketika user salah memilih / mengisi jenis kelamin (lihat gambar!)

    8. Klik Ok dan tutup semua jendela, saatnya mengecek apakah berhasil atau tidak, coba anda pindahkan pointer ke kolom jenis kelamin di baris pertama, maka akan timbul kotak dropd down yang berisi pilihan jenis kelain laki-laki dan perempuan, coba anda mengetik selain dua pilihan diatas misal anda ketik huruf L kemudian tekan enter atau tab, maka akan terdapat pesan error bahwa anda memasukan data tidak sesuai dengan yang telah ditentukan seperti tampak pada gambar dibawah ini

    9. Sebagai latihan coba anda buat data validation rule untuk kolom pekerjaan, anda cukup mengulangi langkah ke-4 sampai ke-7
    10. Untuk bahan percoban silahkan download file yang sudah

      disini

      input message, pada bagian Tittle pilih judul untuk validation rule dan pada bagian input message, masukan pesan yang akan ditampilkan, sehingga akan tampak seperti pada gamabr dibawah ini

    11. Dan pada tab Error Alert, ceklist pilihan Show error alert…, sedangkan pada bagian style anda pilih Error, serta masukan pesan error ketika user salah memilih / mengisi jenis kelamin (lihat gambar!)

    12. Klik Ok dan tutup semua jendela, saatnya mengecek apakah berhasil atau tidak, coba anda pindahkan pointer ke kolom jenis kelamin di baris pertama, maka akan timbul kotak dropd down yang berisi pilihan jenis kelain laki-laki dan perempuan, coba anda mengetik selain dua pilihan diatas misal anda ketik huruf L kemudian tekan enter atau tab, maka akan terdapat pesan error bahwa anda memasukan data tidak sesuai dengan yang telah ditentukan seperti tampak pada gambar dibawah ini

    13. Sebagai latihan coba anda buat data validation rule untuk kolom pekerjaan, anda cukup mengulangi langkah ke-4 sampai ke-7
    14. Untuk bahan percoban silahkan download file yang sudah

      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

Data Validation # Kasus Error Warning

Bagi anda yang terbiasa membuat format data isian di Excel artikel ini mungkin akan sangat berguna untuk menjaga agar user tidak salah memasukan nilai kedalam cell yang telah ditentukan, untuk mudahnya penulis memberikan contoh sederhana sebagai berikut :

Dari gambar diatas adalah contoh pengisian nilai hasi tes masuk pada suatu sekolah, dimana area berwarna hijau merupakan data dari bagian pendaftaran, sedangkan area kuning merukapakan area yang harus diisi oleh bagian tes masuk untuk mengisi nilai tes masuk.

Dari gambar diatas, area warna kuning sangat mungkin sekali diisi data apa saja baik huruf (a-z), tanda baca, angka, gabungan angka dan huruf bahkan gabungan karakter apapun, padahal panitia bagian tes masuk hanya diperbolehkan mengisi nilai angka yang diberi kisaran antara 1 sampai 100, jadi jika panitia bagian tes masuk mengisi selain angka 1 sampai 100 maka akan dikeluarkan error message dan dipaksa untuk mengisi angka 1 sampai 100, dengan cara ini maka dapat kita peroleh secara konsisten, inilah yang dimaksud dengan Data Validation dengan contoh kasus Error Warning

Untuk memulai membuat data validation rule, siapkan terlebih dahulu data yang akan dibuat, sebagai contoh silahkan anda download file latihan disini.

1. Blok area cell yang akan anda berikan rule validasi nya, dalam gambar contoh diatas adalah area warna kuning (perhatikan gambar dibawah )

2. Berikutnya pilih Menu Data –> Data Valdation –> Data Validation …, maka akan tampak kotak dialog data validation

3. Karena kasus ini akan mem-validasi input nilai antara 1 sampai dengan 100 maka pada Drop Down Allow Pilih Whole Number dan ceklis pilihan Ignore Blank jika anda mengizinkan cell warna kuning untuk dikosongkan. Pada bagian minimum isi dengan =1 (nilai minimal) dan pada bagian maximum isi dengan =100 (nilai maksimum).

4. Berikutnya klik tab Input Message, maka akan akan tampak kotak dialog seperti tampak pada gambar dibawah

5. Dari gambar diatas, cek list Show input message when cell is selected, untuk mengaktifkan pesan aturan input saat cell isian data dipilih, kemudian bagian Title Isi dengan judul pesan, dan pada bagian Input Message isi dengan pesan input.

6. Langkah terakhir anda klik tab Error Alert maka akan tampak pengisian error message seperti pada tampak gambar dibawah

7. Dari gambar diatas pada kotak Style pilih Stop artinya jika cell yang telah diberi aturan dipaksa diisi diluar aturan yang ditetapkan maka akan dihentikan dan dipaksa untuk dibatalkan, Kemudian pada bagian Title Isi dengan judul pesan kesalahan, dan pada Error Message isi pesan kesalahan yang akan disampaikan apa bila user salah mengisi data.

8. Klik Ok dan siap digunakan, dan hasilnya tampak seperti berikut :

TESTING
Sebagai percobaan, coba anda input pada area kuning dengan angka 1 sampai 100, bagaimana hasilnya ?? bisa masuk ?? ya pasti bisa , sekarang anda coba masukan karakter selain angka misal huruf atau tanda baca , bagai mana hasilnya ?? jika tampil seperti berikut :

berarti rule anda berhasil dibuat, bahkan saat anda mengetikan angka 0 atau -1 bahkan 101 pun anda akan dihadapkan pada error message dan dipaksa memasukan angka 1 sampai dengan 100 … dengan rule validation ini dijamin data anda akan konsisten.

Menghitung Jadwal Angsuran

Simulasi artikel kali ini adalah simulasi perhitungan angsuran pinjaman, kasusnya diambil dari kasus penulis sendiri saat mengajukan pinjaman dari bank, dengan asumsi sebagai berikut :

Besar Pinjaman : Rp. 50.000.000
Bunga / tahun : 8%
Jangka waktu pinjaman : 36 bulan (3 tahun)

Dari informasi diatas akan dicari informasi berapa angsuran yang harus dibayar baik pokok maupun bunga, beserta jadwal angsuran sampai dengan pinjaman tersebut selesai.

berikut screenshoot hasil perhitungan jadwal angsuran pinjaman :
Continue reading