Dasar Kingsoft Spreadsheet 2013 (2)
April 07, 2017
Hai teman-teman, pada kesempatan kali ini, kita akan membahas mengenai lanjutan beberapa contoh penggunaan formula Spreadsheet.
Contoh 06 (Penggunaan Fungsi Vlookup & Hlookup)
Fungsi VLOOKUP
Vlookup digunakan untuk menentukan suatu nilai tertentu berdasarkan kondisi di mana data diambil dari tabel sumber secara vertikal. Sintaksnya adalah :
=VLOOKUP(Lookup_value, Table_array, Col_index_num, [Range_lookup])
Lookup_value : nilai yang dijadikan acuan
Table_array : tabel sumber data
Col_index_num : nomor kolom tempat data yang ingin dimunculkan dari tabel
Fungsi HLOOKUP
Hlookup menentukan suatu nilai tertentu berdasarkan kondisi di mana data diambil dari tabel sumber secara horizontal. Sintaksnya adalah :
=HLOOKUP(Lookup_value, Table_array, Row_index_num, [Range_lookup])
Lookup_value : nilai yang dijadikan acuan
Table_array : tabel sumber data
Row_index_num : nomor baris tempat data yang ingin dimunculkan dari tabel
Penggunaan Vlookup dan Hlookup
Pada kesempatan kali ini kita akan mengisi data-data yang kosong pada tabel dengan mengambil data dari tabel lain. Pada kasus ini, kita akan membuat daftar penghasilan.
Pada kesempatan kali ini kita akan mengisi data-data yang kosong pada tabel dengan mengambil data dari tabel lain. Pada kasus ini, kita akan membuat daftar penghasilan.
- Buatlah tabel seperti Gambar 1 yang berisikan data berupa nama pegawai dan golongan serta buat tabel untuk daftar gaji untuk setiap golongan pegawai.
- Untuk mengisi kolom gaji pokok pegawai (kolom D), kita dapat menggunakan fungsi Vlookup dan menggunakan dua tabel yang telah disediakan (kolom M dan N). Formulanya adalah =VLOOKUP(B6,$M$6:$P$22,2). Salin (drag and drop) dari D7 sampai dengan D31.
- Ulangi langkah kedua untuk mengisi kolom tunjangan dan transportasi tetapi kita perlu mengganti sintaks col_index_num pada rumusnya menjadi 3 untuk tunjangan dan 4 untuk transportasi. Salin (drag and drop) dari E7 sampai dengan E31 serta F7 sampai dengan F31.
- Untuk tunjangan istri/suami kita perlu menggunakan fungsi logika If yang telah dipelajari sebelumnya. Logical_test nya jika pegawai sudah kawin (K) maka ia mendapatkan tunjangan istri/suami, jika tidak kawin (TK) maka tidak mendapatkan tunjangan istri/suami (0). Jadi, jika diaplikasikan dalam formula menjadi =IF(G6=”K”,10%*D6,”0”). Salin (drag and drop) dari H7 sampai dengan H31.
- Untuk mendapatkan total gaji, kita hanya perlu menjumlahkan seluruh gaji dan tunjangan istri/suami dengan menggunakan =SUM(D6:F6,H6). Salin (drag and drop) dari I7 sampai dengan I31.
- Buatlah daftar tarif pajak untuk setiap golongan pegawai secara horizontal, sebagai contoh penerapan fungsi Hlookup.
- Untuk mengisi kolom potongan pajak kita menggunakan fungsi Hlookup. Formulanya adalah =HLOOKUP(B6,$D$33:$T$34,2)*I6. Salin (drag and drop) dari J7 sampai dengan J31.
- Terakhir, untuk mendapatkan gaji bersih kita perlu mengurangkan total gaji dengan potongan pajak. Formulanya langsung saja =I5-J5. Salin (drag and drop) dari K7 sampai dengan K31.
Contoh 07 (Penggunaan Fungsi Teks)
Contoh 08 (Penggunaan Fungsi If)
Fungsi If digunakan untuk menentukan dua kondisi di mana jika kondisinya terpenuhi maka hasil pertama yang keluar dan jika kondisinya tidak terpenuhi maka yang keluar adalah hasil yang kedua. Cara menggunakan formula If yaitu :
Fungsi If digunakan untuk menentukan dua kondisi di mana jika kondisinya terpenuhi maka hasil pertama yang keluar dan jika kondisinya tidak terpenuhi maka yang keluar adalah hasil yang kedua. Cara menggunakan formula If yaitu :
- Buat format seperti gambar di bawah (untuk kolom Nama Barang dikosongkan terlebih dahulu);
- Pada sel E3 tuliskan rumus =IF(C3="G","Guitar","Piano") (Enter); dan
- Salin (drag and drop) dari E4 sampai dengan E10, maka hasilnya seperti Gambar 11.
Terus bagaimana jika kode barangnya ditambahkan dengan A yaitu untuk Nama barang atau nama alat musik Angklung?
Tahapannya sama, tinggal ubah atau tambahkan yang tadinya =IF(C3="G","Guitar","Piano) menjadi =IF(C3="G","Guitar",IF(C3="P","Piano","Angklung")), selanjutnya salin sampai sel E10.
Tahapannya sama, tinggal ubah atau tambahkan yang tadinya =IF(C3="G","Guitar","Piano) menjadi =IF(C3="G","Guitar",IF(C3="P","Piano","Angklung")), selanjutnya salin sampai sel E10.
Studi Kasus (Mengidentifikasi NIP)
Studi kasus berikut ini berhubungan dengan kombinasi fungsi teks dan If. Teman-teman dapat melihat berbagai formula yang digunakan.
Studi kasus berikut ini berhubungan dengan kombinasi fungsi teks dan If. Teman-teman dapat melihat berbagai formula yang digunakan.
Contoh 09 (Penggunaan Fungsi Gabungan)
Gabungan Fungsi IF dengan Fungsi AND
Cara mengisi kolom keterangannya adalah :
- Ketikkan formula seperti di atas yaitu =IF(AND(D39>=75,C39<>"D"),"Lulus","Ngulang")
- Salin sampai dengan sel E42.
Gabungan Fungsi IF dengan Fungsi OR
Masih menggunakan contoh seperti di atas tetapi menggunakan formula If dan OR.
Cara mengisi kolom keterangannya adalah :
Masih menggunakan contoh seperti di atas tetapi menggunakan formula If dan OR.
- Ketikkan formula seperti di atas yaitu =IF(OR(D39<75,C39="D"),"Ngulang","Lulus")
- Salin sampai dengan sel E42.
Gabungan Fungsi IF , AND , dan OR
Ada soal
Pada Jurusan Pendidikan Matematika FKIP UNSRI membuka kesempatan kepada alumni untuk menjadi tenaga pengajar. Pelamar yang layak diterima adalah pelamar yang memenuhi salah satu persyaratan berikut:
Ada soal
Pada Jurusan Pendidikan Matematika FKIP UNSRI membuka kesempatan kepada alumni untuk menjadi tenaga pengajar. Pelamar yang layak diterima adalah pelamar yang memenuhi salah satu persyaratan berikut:
- Memiliki IPK minimal 3,75 tanpa memperhatikan keaktifannya dalam organisasi kemahasiswaan; atau
- Memiliki IPK minimal 3,50 tetapi kurang dari 3,75 dan keaktifannya dalam organisasi kemahasiswaan tidak termasuk ke dalam katagori kurang; atau
- Memiliki IPK minimal 3,25 tetapi kurang dari 3,50 dan keaktifannya dalam organisasi kemahasiswaan termasuk kategori baik.
Siapakah di antara nama berikut yang layak menjadi tenaga pengajar?
Untuk mengisi kolom kelayakan di atas caranya yaitu :
- Masukkan/ketik formula berikut pada sel E2 =IF(C2>=3,75,"LAYAK",IF(AND(C2>=3,5,C2<3,75,D2<>"KURANG"),"LAYA K",IF(AND(C2>=3,25,C2<3,5,D2="BAIK"),"LAYAK","TIDAK LAYAK")))
- Lalu salin dari E2 s/d E9.
Gabungan Fungsi IF , AND , dan COUNTIF
- Pada sel I3 masukkan formulanya yaitu =IF(AND(C3>=3,25,COUNTIF(D3:H3,"C")=0),"LAYAK",IF(AND(C3>=3,25, COUNTIF(D3:H3,"C")=1,COUNTIF(D3:H3,"A")>=2),"LAYAK","TIDAK LAYAK"))
- Salin formula tersebut dari I3 s/d I10.
Gabungan Fungsi LOOKUP, LEFT, dan RIGHT
- Pada Kolom D4 isikan formula =VLOOKUP(LEFT(C4,3),$B$12:$C$14,2);
- Lalu salin D4 s/d D9;
- Pada kolom E4 isikan formula =HLOOKUP(RIGHT(C4,2),$E$13:$F$14,2);
- Lalu salin E4 s/d E9.
Gabungan Fungsi Sumif dengan *
Fungsi Sumif dan * digunakan jika pada suatu barang tersebut memiliki banyak jenis, seperti contoh di bawah, saya contohkan dengan jenis FD yang mana dibedakan berdasarkan sizenya dan type FD. Bagaimana untuk mengisi sel D11 ?
Fungsi Sumif dan * digunakan jika pada suatu barang tersebut memiliki banyak jenis, seperti contoh di bawah, saya contohkan dengan jenis FD yang mana dibedakan berdasarkan sizenya dan type FD. Bagaimana untuk mengisi sel D11 ?
- Untuk kolom total harga, teman-teman tinggal masukkan formula =C2*D2 (Enter) lalu salin ke bawah s/d sel E9;
- Untuk mengisi sel D11 masukkan formula =SUMIF(B2:B9,"kinstoon*",C2:C9);
- Untuk mengisi sel D12 masukkan formula =SUMIF(B2:B9,"toshiba*",C2:C9).
Lalu bagaimana untuk sel D13 dan D14, silahkan teman-teman coba sebagai latihan.
Gabungan Fungsi IF dengan Fungsi NOT
Ada soal
Seorang mahasiswa dinyatakan lulus mata kuliah tertentu jika dia memperoleh skor akhir minimal 55.
Ada soal
Seorang mahasiswa dinyatakan lulus mata kuliah tertentu jika dia memperoleh skor akhir minimal 55.
- Tuliskan formulanya =IF(NOT(B17<55),"Lulus","Tidak Lulus") pada kolom C17
- Salin s/d C18.
Gabungan Fungsi IF dengan Fungsi VLOOKUP
- Buat formatnya seperti di atas dan untuk kolom “Keikutsertaan dalam UTS (Boleh/Tidak Boleh)” kita akan gunakan gabungan formula ini;
- Pada Sel F4 isikan rumusnya =IF(E4>=75%*(VLOOKUP(C4,$H$4:$J$8,2)+D4*VLOOKUP(C4,$H$4:$J$8,3)),"Boleh","Tidak Boleh");
- Salin sampai F17.
Sebenarnya masih banyak fungsi-fungsi yang bisa digabungkan untuk menyelesaikan sebuah permasalahan yang diberikan. Tetapi mungkin hanya sebegitu yang saya bisa sajikan. Apabila ada kesalahan, mohon dimaafkan ya (masih belajar juga). Terima kasih.
Jika teman-teman ingin mengunduh artikel ini bisa klik Download Dasar Kingsoft Spreadsheet (2).
Teman-teman bisa unduh juga Spreadsheet saya dengan mengklik Formula Dasar.
Jika teman-teman ingin mengunduh artikel ini bisa klik Download Dasar Kingsoft Spreadsheet (2).
Teman-teman bisa unduh juga Spreadsheet saya dengan mengklik Formula Dasar.
