Tag Archives: if

Mencari Nilai dengan Fungsi Index #Bag. 2

Artikel kali ini merupakan lanjutan dari artikel sebelumnya yaitu “Mencari Nilai dengan Fungsi Index #Bag. 1“, pada artikel sebelumnya anda harus melakukan secara manual ketika akan mencari nilai pada suatu cel dengan menggunakan fungsi index. Perhatikan kembali screenshoot dibawah ini :

Sebagai contoh untuk mencari jumlah pendaftar prodi kehutanan – S1 pada tahun 2008 maka anda harus melakukan penentuan index kolom dan index baris secara manual.

Pembuatan formula index apabila penentuan  index kolom dan baris dilakukan secara manual sungguh merepotkan apabila data tersebar dalam spreedsheet yang begitu banyak, nah pada artikel kali ini akan dibuat pengisian cell untuk mencari jumlah pendaftar menggunakan menu, sehingga pengguna tinggal memilih nama jurusan dan tahun pendaftaran maka otomatis akan ditampilkan hasil pencarian data dalam cell. Perhatikan kedua gambar dibawah ini !

Dari kedua gambar diatas untuk mencari jumlah pendaftar anda tinggal memilih program studi – jenjang serta tahun pada drop down yang disediakan. Nah pada aritikel kali ini kita akan memodifikasi artikel sebelumnya menjadi seperti tampak pada kedua gamabr diatas.

Berikut langkah-langkah pengerjaan :

1. Blok cell C7 sampai H11 (C7:H11) dan beri nama data dengan menggunakan fasilitas insert name define.

2. Buat tampilan seperti pada gambar ke-3 (ke-4)

3. Blok cell B7 sampai B11 (B7:B11) dan beri nama prodi dengan menggunakan fasilitas insert name define.

4. Blok cell C6 sampai H6 (C6:H6) dan beri nama tahun dengan menggunakan fasilitas insert name define.

5. Pada tampilan gambar ke-3 (ke-4), simpan kursor di cell K7 untuk membuat drop down jurusan, gunakan fasilitas data validation untuk membuatnya, berikut ketentuan pembuatannya. Pada bagian Allow pilih List selanjutnya pada bagian Source ketik =prodi, sesuaikan namanya sesuai pada langkah 3, jika telah selesai klik OK.

6.  Pada tampilan gambar ke-3 (ke-4), simpan kursor di cell K8 untuk membuat drop down tahun, gunakan fasilitas data validation untuk membuatnya, berikut ketentuan pembuatannya. Pada bagian Allow pilih List selanjutnya pada bagian Source ketik =tahun, sesuaikan namanya sesuai pada langkah 3, jika telah selesai klik OK.

7. Simpan kursor di K9 untuk mencari jumlah pendaftar, dan ketik formula berikut :

=IF(OR(ISBLANK(K7),ISBLANK(K8)),””,INDEX(data,IF(ISERROR(MATCH(K7,prodi,0)),””,MATCH(K7,prodi,0)),IF(ISERROR(MATCH(K8,tahun,0)),””,MATCH(K8,tahun,0))))

Untuk bahan latihan silahkan download filenya :

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.

Mengecek Nilai Ganjil atau Genap

Kadangkala kita menemukan suatu kasus yang mengharuskan kita memisahkan sejumlah angka menjadi dua kelompok yaitu ganjil dan genap, pekerjaan akan sangat mudah jika data yang anda hadapi hanya sedikit misal hanya terdiri dari sepuluh baris, namun pekerjaan tersebut akan menjadi pekerjaan sulit jika data yang anda miliki jumlahnya adalah ribuan. Sebagai ilustrasi perhatikan gambar berikut :

Nah dari data diatas anda memiliki dua tugas yaitu :

  1. Mencari data yang bernilai ganjil atau genap
  2. dan Memberikan tanda dengan huruf tebal (bold) untuk keterangan yang bernilai ganjil

Untuk mengerjakannya tugas pertama :

  1. Tempatkan pointer cell pada B2 dan ketik formula berikut :
  2. =IF(A2=ODD(A2);”Ganjil”;”Genap”)
    Formula diatas menggunakan fungsi ODD untuk menaikan satu angka bagi bilangan positif dan menurunkan satu angka bilangan negatif ke nilai ganjil terdekat, kemudian hasilnya dibandingkan dengan cell A2 jika sama nilainnya ganjil dan jika beda nilanya genap.

Sedangkan untuk mengerjakan tugas kedua membedakan nilai ganjil dan genap dengan memberi format bold pada nilai ganjil ikuti langkah berikut :

Block Kolom B2 sampai B9 (atau sesuai data milik anda), lihat gambar dibawah ini :

Pilih menu Format –> Conditional Formating Sehingga akan tampil berikut :

Pada Condition 1 Pilih Formula Is ketik formula =A2=ODD(A2) kemudian klik tombol format, maka akan keluar kotak dialog formating seperti berikut:

Di kotak formating tersebut pada bagian Font Style pilih Bold, jika telah selesai klik tombol OK  dan klik OK lagi untuk menutuf conditional Formating, sekarang copy paste formual yang telah anda buat.  Sekarang data anda telah diberi tanda menjadi data ganjil dan genap.

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.