Rumus dalam Microsoft excel pada tulisan ini, menggunakan beberapa rumus kombinasi yang cukup lengkap dengan penggabungan beberapa fungsi diantaranya: Vlookup, Hlookup,Left, Mid dan If.

Contoh Soal yang harus anda diselesaikan tampak seperti gambar di bawah ini:

Soal di atas, terdiri dari 4 tabel, dimana ada 1 tabel utama yang harus anda selesaikan pada kolom-kolom yang kosong. Lalu ada 3 tabel referensi/tabel bantu yaitu: tabel menu makanan, tabel tujuan dan tabel kelas.
Bagi anda yang ingin langsung praktek, silahkan ketikkan soal di atas….

Sekedar saran…

Alangkah lebih baik, bila anda menyesuaikan letak kolom dan baris sesuai dengan soal gambar yang ada di atas…

Tujuannya Apa?
Tentunya untuk memudahkan anda dalam proses belajar Microsoft excel…

Versi Microsoft office yang digunakan?
Silahkan menggunakan versi ms. Office 2007, 2010, 2013 atau yang paling terbaru tidak masalah, semua bisa anda gunakan..

Oke, untuk mengerjakan soal excel dari gambar di atas, ada keterangan petunjuk pengerjaannya. Petunjuk keterangan soal biasanya juga diberikan bila anda mengerjakan soal excel di sekolah, kampus ataupun lembaga-lembaga pendidikan lainnya.

Berikut ini keterangan soalnya:

Keterangan:

1. Buat tabel di atas dengan menggunakan Microsoft Excel
   Data Masukan : Tgl Keberangkatan, Kode Tiket, Nama Tiket, Tgl Pembelian
   Keterangan Kode Tiket, Contoh: BBBY-P1-035
   BBB -> Menentukan Tujuan
   Y-> Menentukan Kelas
   P1-> Menentukan Menu
2. Isikan rumus untuk kolom-kolom :
   a. Tujuan
   – Diambil dari Tabel Tujuan sesuai dengan Kode Tiket
   b. Harga
   – Diambil dari Tabel Tujuan sesuai dengan Kode Tiket
   c. Kelas
   – Diambil dari Tabel Kelas sesuai dengan Kode Tiket
   d. Menu
   – Diambil dari Tabel Menu Makanan sesuai dengan Kode Tiket
   e. Diskon
   – Jika tiket dibeli lebih dari 60 hari sebelumnya, maka mendapat diskon. Jumlah Diskon diambil dari Tabel Kelas sesuai Kode Tiket dikali Harga
   f. Harga Jual
   – Diambil dari Harga dikurangi Diskon ditambah Harga Menu. Harga Menu diambil dari Tabel Menu Makanan sesuai Kode Tiket
   g. Total Pembelian
   – Jumlah keseluruhan Harga Jual
   h. Jumlah Pembeli Kelas VIP
   – Jumlah Pembelian Tiket Kelas VIP

Sesuai dengan petunjuk soal yang diberikan, berikut langkah pengerjaannya:

1. Tujuan

Untuk mengisi kolom tujuan, gunakanlah rumus kombinasi fungsi vlookup dan fungsi left. Mengapa menggunakan fungsi vlookup?

Karena kita akan mengambil data dalam bentuk vertikal dari tabel referensi (tabel bantu) yaitu tabel tujuan.

Sedangkan fungsi Left untuk mengambil tiga karakter dari sebelah kiri kolom kode tiket (tabel utama).

Jawaban untuk kolom tujuan akan terlihat seperti gambar berikut ini:
Keterangan dari gambar jawaban di atas:
Angka 1 merupakan jawaban dari kolom tujuan yang diisikan di CELL F5 yaitu:

=VLOOKUP (LEFT(C5,3),$B$23:$D26,2)

Penulisan rumus kombinasi diatas, tentunya Sesuai dengan penulisan fungsi vlookup

=VLOOKUP (nilai kunci, letak tabel, nomor kolom)

Hasil pengerjaan soal dari angka 1 didapatkan dari angka 2 dan 3, bila dijabarkan:

• Angka 2 dijadikan sebagai nilai kunci (C5) pada fungsi vlookup, karena kita hanya memerlukan tiga karakter untuk menentukan tujuan dari sebelah kiri maka penulisannya nilai kuncinya adalah Left (C5,3).

• Angka 3 digunakan sebagai letak tabel dan nomor kolom pada fungsi VLOOKUP, letak tabel yang diambil dari tabel bantu/referensi yaitu tabel tujuan. Untuk mengisinya, blok dari cell B23 sampai D26. Jangan lupa untuk menambahkan tanda dollar ($) untuk mengabsolutkan, menjadi seperti ini $B$23:$D$26.  Tujuan pemberian dollar ini sudah sering saya tulis dari berbagai artikel yang mengenai excel di blog ini, yaitu agar pada saat anda mengisi jawaban di cell F5 kemudian mendrag /copy ke bawah untuk mengisi jawaban pada kolom dibawahnya, hasilnya tidak menjadi #N/A.

Nomor kolom isikan dengan 2, karena letak kolom tujuan pada tabel tujuan, berada pada kolom kedua.
Jawaban Lengkapnya untuk kolom tujuan, terlihat seperti gambar di bawah ini:

Harga

Untuk mengisi harga, sesuai dengan instruksi soal yang diberikan dimana harga diambil dari Tabel Tujuan sesuai dengan Kode Tiket.  Instruksi yang diberikan untuk menjawab kolom harga, sama persis dengan kolom tujuan sebelumnya.
Proses pengerjaannya bisa anda lihat seperti dibawah ini:
Sama bukan?
Yup, untuk mengisi harga masih menggunakan rumus kombinasi dengan menggunakan dua fungsi, yaitu fungsi vlookup dan left.
Tapi ada satu yang membedakan jawaban yang telah digambarkan di atas, yaitu pada nomor kolom. Nomor kolom menjadi 3 bukan 2, karena pada tabel tujuan (tabel bantu yang terletak di bawah, diberi lingkaran dengan angka 3), harga terletak pada kolom ketiga, sesuai dengan panah yang saya berikan pada gambar.
Jadi untuk menjawab harga, rumus kombinasinya adalah:

=VLOOKUP (LEFT(C5,3),$B$23:$D26,3)

3. Kelas

 Untuk mengisi kelas, anda bisa menggunakan rumus kombinasi dari fungsi Vlookup dan mid. Fungsi mid digunakan untuk mengambil sejumlah karakter dari posisi tertentu sebanyak yang diinginkan. Bentuk penulisannya:

=Mid(text, start_num, num_chars)

Atau Sama juga seperti ini:

=MID(data_yang_ diambil, posisi_awal, jumlah_pengambilan)

jadi jawaban untuk kolom harga:

=VLOOKUP(MID(C5, 4,1),$G$23:$I$25,2)

Penulisan rumus kombinasi untuk kelas, tentunya Sesuai dengan penulisan fungsi vlookup, dibawah ini:

=VLOOKUP (nilai kunci, letak tabel, nomor kolom)

Penjelasannya?
Angka 1 pada gambar di atas merupakan jawaban yang didapatkan dari Angka 2 dan Angka 3.
Angka 2 merupakan nilai kunci yang terletak pada Cell C5, yaitu menggunakan fungsi Mid (C5,4,1).
Mengapa mesti menggunakan mid?
Karena kita ingin mengambil karakter Y yang ada pada Kode Tiket BBBY, yang terletak pada urutan keempat sebanyak 1 karakter.
Angka 3 merupakan letak tabel dan nomor kolom, saya rasa tidak perlu dijelaskan lagi ya? Karena proses penjelasannya sama dengan letak tabel dan nomor kolom pada jawaban sebelumnya.
Selanjutnya tinggal anda drag/copy ke bawah dari cell H5 untuk mengisi kolom kelas yang masih kosong.
Hasil lengkap dari jawaban kolom kelas, seperti gambar di bawah ini:

Menu

Untuk mengisi kolom menu; diambil dari tabel menu makanan (tabel bantu) sesuai dengan kode tiket. Bila dilihat dari tabel menu makanan, posisi data yang akan diambil berbentuk horizontal, untuk itu kita bisa menggunakan fungsi Hlookup. Pengerjaannya bisa Anda lihat seperti di bawah ini:

Pada gambar di atas, untuk mengisi kolom menu di Cell I5  (kotak dengan angka1), rumus kombinasi yang digunakan adalah Fungsi Hlookup dan Mid.
Penulisan Fungsi HLOOKUP

=HLOOKUP(lookup_value,tabel_array,row_index_num)

=HLOOKUP(nilai kunci, letak tabel, nomor baris)

Hasil yang didapatkan dari rumus HLOOKUP dan MID, adalah :

=HLOOKUP(MID(C5,6,2),$C$17:$E$19,2)

Pada fungsi HLOOKUP jawaban di atas, fungsi mid (C5,6,2) sebagai nilai kunci. C17:E19 merupakan letak tabel dan angka 2 merupakan nomor baris.
Hasil lengkap jawaban dari kolom menu, seperti dibawah ini:

Diskon

Petunjuk soal untuk mengisi kolom diskon adalah jika tiket dibeli lebih dari 60 hari sebelumnya, maka mendapat diskon. Jumlah Diskon diambil dari Tabel Kelas sesuai Kode Tiket dikali Harga.
Proses pengerjaan untuk Diskon, bisa anda lihat seperti di bawah ini:
Untuk mengisi kolom Diskon Pada Cell J5, anda bisa menggunakan rumus kombinasi Fungsi IF dan VLOOKUP, Selain itu rumus pengurangan,dan perkalian juga digunakan untuk menjawab diskon, seperti gambar di atas.
Hasil Pengerjaan rumus menjadi seperti ini:

=IF ($D$3-E5>60,G5*VLOOKUP(MID(C5,4,1),$G$23:$I$25,3),0)

Setelah cell J5 sudah anda isi, silahkan copy/drag ke bawah sampai kolom diskon ke isi semuanya. Hasilnya akan tampak seperti dibawah ini:

Harga Jual

Untuk mengisi kolom harga jual, anda bisa mengambil harga dikurangi diskon ditambah harga Menu. Harga menu diambil dari tabel menu makanan sesuai kode tiket.

Proses pengerjaan untuk harga jual, seperti di bawah ini:

=G5-J5+(HLOOKUP(MID(C5,6,2),$B$17:$E$19,3))

Setelah cell K5 sudah anda isi, silahkan copy/drag ke bawah sampai kolom harga jual ke isi semuanya. Hasil harga jual selengkapnya:

Total Pembelian

Total pembelian, didapatkan dari jumlah keseluruhan harga jual, untuk itu gunakan fungsi sum, dengan cara meletakkan kursor di K15, lalu blok area dari K5 sampai K14, menjadi seperti ini:

=SUM(K5:K14)

Selain itu anda bisa mengunakan AutoSum, untuk menghitung penjumlahan/total secara langsung.

umlah Pembeli Kelas VIP

Untuk mengisi jumlah pembeli kelas VIP anda bisa menggunakan fungsi COUNTIF. COUNTIF adalah salah satu fungsi statistik yang digunakan untuk menghitung jumlah sel yang memenuhi kriteria; misalnya, untuk menghitung berapa kali kelas “VIP” muncul dalam daftar Kelas
penulisan rumusnya:

=COUNTIF(range, critea)

Hasil Pengerjaan:

=COUNTIF(H5:H14,”VIP”)

Fungsi adalah suatu rumus dalam excel yang harus diawali dengan tanda = dalam suatu cell, kemudian baru masukkan fungsi yang akan digunakan. Contohnya adalah: fungsi statistic penggunaan/pemakaiannya =sum(B6:B21), fungsi formula contoh pemakainnya =B7*C5. Fungsi-fungsi lainnya yang perlu anda ketahui, diantaranya:

Pengenalan fungsi yang umum pada ms excel

– Fungsi Mid : digunakan untuk mengambil sejumlah karakter dari posisi tertentu sebanyak yang diinginkan. Format yang digunakan:
=MID(data_yang_ diambil, posisi_awal, jumlah_pengambilan)
Contoh: = MID(“SEMANGAT”,4,3)
Hasilnya: ANG
– Fungsi Left : digunakan untuk mengambil sejumlah karakter dari sebelah kiri sebanyak yang diinginkan. Format yang digunakan:
=LEFT(data_yang_diambil, jumlah_yang_diambil)
Contoh: =LEFT(“SEMANGAT”, 4)
Hasilnya=SEMA
– Fungsi Right : digunakan untuk mengambil sejumlah karakter dari sebelah kanan
karakter dari posisi tertentu sebanyak yang diinginkan. Formatnya:=RIGHT(data_yang_diambil, jumlah_yang diambil)
Contoh: =RIGHT(“SEMANGAT”, 4)
Hasilnya : NGAT
– Fungsi Len : digunakan untuk mengetahui berapa jumlah karakter termasuk spasi. Formatnya: =LEN(letak_data)
atau bisa dituliskan juga: =LEN(text)
Contoh : = LEN(“SEMANGAT”)
Hasilnya: 8
– Fungsi Lower : Digunakan untuk mengubah teks dari huruf besar ke huruf kecil. Formatnya :
=LOWER(Letak_data)  atau =LOWER(text)
Contoh: =LOWER(“SEMANGAT”)
Hasilnya: semangat
-. Fungsi Upper : digunakan untuk mengubah teks dari huruf besar ke huruf kecil (kebalikan dari fungsi LOWER). Formatnya :
=UPPER(letak_data)  atau =UPPER(text)
Contoh : =UPPER (“semangat”)
Hasilnya: SEMANGAT
– Fungsi Time : digunakan untuk memasukkan atau menampilkan waktu. Formatnya :
=TIME(Jam,Menit,Detik)
Contoh : TIME(22,38,00)
Hasilnya: 10:38 PM
– Fungsi Date : digunakan untuk menampilkan atau membuat tanggal. Formatnya:
=DATE(Tahun,Bulan,Tanggal)
Contoh : =Date(2013,11,26)
Hasil : 11/26/2013
– Fungsi Mod : digunakan untuk mengambil nilai dari hasil pembagian. Formatnya:
=MOD(nilai, pembagi)
=MOD(21,5)
Hasilnya:1
– Concatenate : digunakan untuk menggabungkan dua atau lebih string teks ke dalam satu string teks. Formatnya :
=Concatenate(teks1, teks2…)
Contoh : =CONCATENATE(11,”Bln”,2013,”Thn”)
Hasilnya : 11 Bln 2013 Thn

Fungsi-Fungsi Statistik

=SUM(Range) : digunakan untuk menjumlahkan atau mentotalkan data dalam range
=AVERAGE(Range) : digunakan untuk mencari nilai rata-rata dalam range.
=COUNT(Range) : digunakan untuk mengetahui ada berapa dalam range.
=MAX(Range): digunakan untuk mengetahui mengetahui berapa nilai yang terbesar dalam range.
=MIN(Range) : digunakan untuk mengetahui berapa nilai yang terkecil dalam range.
=Count(Range) : digunakan untuk mengetahui jumlah data dalam range.

Mengisi Pajak Beda Sheet dengan HLOOKUP

Oke, lanjut ke pengerjaan soalnya, dimana data yang harus diisikan adalah pajak dan Gaji Bersih pada tabel bagian operator (sheet1). Tabel yang akan dijadikan referensi, yaitu tabel potongan pajak berada pada sheet3, seperti gambar di bawah ini:
Untuk mengisi pajak (sheet1), anda harus menggunakan tabel potongan pajak (sheet3) dengan menggunakan fungsi HLOOKUP. Mengapa? Karena data-data yang ada pada tabel potongan pajak berbentuk horizontal, lihat gambar di bawah ini:

Penulisan Rumus/Formula HLOOKUP:

Silahkan letakkan kursor di cell H5 pada tabel bagian operator (sheet1) untuk mengisi nilai_kunci dan tuliskan datanya seperti ini:
=HLOOKUP(B5,
Lalu arahkan kursor ke sheet3, untuk mengisi tabel_data, sehingga penulisannya menjadi seperti ini:
=HLOOKUP(B5,Sheet3!B2:D3
Lalu beri tanda $ (dollar) untuk mengabsolutkan sell. Cara mudahnya tekan tombol F4 pada keyboard setelah anda memblok area B2:D3. Penulisan formulanya:
=HLOOKUP(B5,Sheet3!$B$2:$D$3,
Selanjutnya mengisikan nomor_baris, lihat gambar dibawah ini:
Potongan pajak terletak pada baris kedua. Untuk itu formula/rumus yang harus anda isikan adalah:
=HLOOKUP(B5,Sheet3!$B$2:$D$3,2,
Setelah mengisi nomor baris, selanjutnya isikan tipe_data dengan menggunakan FALSE sebagai tipe data. Hasilnya:
=HLOOKUP(B5,Sheet3!$B$2:$D$3,2,FALSE)
Setelah semua penulisan formula HLOOKUP di atas selesai, jangan lupa untuk mengalikannya dengan total gaji yang beada pada sel D5 (sheet1). Sehingga penulisan formula seluruhnya, seperti berikut ini:
=HLOOKUP(B5,Sheet3!$B$2:$D$3,2,FALSE) *D5
Setelah anda mengisi pajak pada cel H5, silahkan copy/drag ke bawah sampai sel H9, seperti ini hasilnya:

Mengisi Gaji Bersih

Untuk mengisi gaji bersih, anda cukup menggunakan operator pengurangan dengan cara Total Gaji dikurangi pajak atau =G5-H5.

Hasil pengurangan total gaji dengan pajak bisa anda lihat seperti dibawah ini:

Untuk menampilkan/mengisi tahun masuk  gunakan fungsi vlookup dan fungsi string LEFT. Fungsi left menghasilkan karakter pada data bertipe teks sebanyak x karakter yang dimulai dari posisi sebelah kiri. Bentuk penulisannya =LEFT(text, num_chars). Dalam soal ini, dikombinasikan/digabung dengan vlookup . Hasil pengerjaan dari penggabungan vlookup dengan left bisa anda lihat seperti gambar di bawah ini:



Pada gambar di atas fungsi yang digunakan =VLOOKUP(LEFT(C5,2),$E$18:$F$23,2). Setelah mengisi formula di sel F5, copy formula atau drag hingga sel F14.

Hasil lengkap untuk pengerjaan dari soal di atas, akan terlihat seperti gambar di bawah ini.

Perlu Anda ketahui, bahwa Fungsi mid digunakan untuk mengambil sejumlah karakter dari posisi tertentu sebanyak yang diinginkan. Untuk menggabungkan fungsi mid dan vlookup, pastinya kita memerlukan soal  untuk dibahas atau dikerjakan. Jadi, silahkan Anda lihat gambar soalnya di bawah ini :

Pada gambar di atas, terlihat kolom “jurusan” dan “tahun masuk” masih kosong. Lalu bagaimana cara mengisinya. Untuk menampilkan/mengisinya gunakan fungsi vlookup dan fungsi string MID. Oh iya, yang perlu Anda ketahui juga untuk penulisan di kolom “NIM”, “kode jurusan (tabel jurusan)”, dan “kode tahun masuk(tabel tahun masuk)”, gunakan type data text. Atau pada saat mengetik tambahkan ‘ (kutip 1). Misal ’11. Hasil  pengerjaannya bisa anda lihat seperti gambar di bawah ini:

Pada gambar di atas fungsi yang digunakan =VLOOKUP(MID(C5,3,2),$B$18:$C$21,2).  Setelah mengisi formula di sel E5, copy formula atau drag hingga sel E14. Setelah kolom “jurusan” berhasil Anda isikan, selanjutnya adalah mengisi mengisi kolom “tahun masuk”, berikut langkah-langkahnya.

Berikut beberapa contoh untuk rumus Hlookup excel.

Contoh 1.
Laporan Penjualan Barang.
Ketentuan penulisan rumus sebagai berikut:
1. Nama barang dan harga diambil dari tabel sesuai dengan kode barang.
2. Discount diambil dari tabel sesuai dengan nama barang dikalikan dengan harga.
3. Total Harga = Harga dikurangi Discount.
Lihat data di bawah ini:


Penulisan rumus data di atas sebagai berikut:
Mencari Nama Barang.
C5=Hlookup(B5;$C$14:$F$17;2)
Mencari Harga.
D5=Hlookup(B5;$C$14:$F$17;3)
Mencari discount.
E5 = Hlookup(B5;$C$14:$F$17;4)*D5
Mencari Total Harga.
F5=D5-E5

Jika dilihat hasilnya akan seperti ini

Pengertian rumus microsoft excel Hlookup 

Fungsi atau rumus Hlookup excel ini digunakan untuk mencari nilai di baris paling bawah tabel referensi, dan kemudian mengembalikan sebuah nilai dalam kolom yang sama dari sebuah baris yang di tetapkan dalam tabel referensi. Menggunakan fungsi Hlookup hanya jika  nilai-nilai perbandingan berada di baris sebuah data yang ingin ditemukan.

Fungsi ini digunakan untuk melakukan penghitungan yang range datanya disusun secara Horizontal (mendatar), range data bisa di beri nama tertentu untuk memudahkan


Fungsi HLOOKUP digunakan untuk menampilkan data dari sebuah tabel yang disusun dalam format horizontal.

Bentuk penulisan fungsinya =HLOOKUP(lookup_value, tabel_array, row_index_num) atau bisa juga dibaca =HLOOKUP(nilai kunci, letak table, nomor baris).

Rumus dasar atau sintak rumus Hlookup excel 2007

Setelah mengetahui secara singkat pengertian rumus excel Hlookup, selanjutnya hal yang sangat penting untuk dipelajari adalah rumus dasar atau sintaks dari rumus tersebut, sehingga dalam penulisan rumus Hlookup bisa sesuai dan hasilnya pun sesuai.
Sintaks rumus Hlookup excel

=HLOOKUP(lookup_value;tabel_array;row_index_num)

Penjelasan sintaks di atas sebagai berikut;
Lookup_value adalah nilai yang akan dicari. lookup_value dapat berupa sebuah nilai, referensi, atau string teks.
Tabel_array adalah area data pada tabel pencarian. tabel informasi/referensi, tabel ini dapat berupa range, atau nama range.
Row_index_num adalah nomor indek baris pencarian dari tabel pencarian.

Beberapa keterangan tentang col_index_num;
Jika row_index_num bernilai 1, maka nilai-nilai yang dihasilkan/dikembalikan berada pada baris kedua pada tabel referensi; demikian seterusnya.
Jika row_index_num bernilai kurang dari 1, maka fungsi HLOOKUP akan mengembalikan nilai kesalahan berupa # VALUE!,
jika row_index_num lebih besar daripada jumlah baris tabel referensi, maka fungsi HLOOKUP akan  mengembalikan nilai kesalahan tersebut berupa # REF! .
tabel_array pada rumus Hlookup excel bisa menggunakan nama range.

Cara memberi nama range.
a. Blok range datanya.
b. Klik menu Insert kemudian Define.
c. Setelah dibuka dialoq Define Name, ketik namarange pada Names In Workbook.
d. Kemudian klik tombol Add.
e. Klik Ok untuk mengakhiri.

Catatan: Untuk membuat nama range harus mengikuti ketentuan sebagai berikut:
1. Tidak boleh ada spasi.
2. Tidak boleh menggunakan operator matematika maupun logika.
3. Maksimum 8 karakter.
4. Tidak boleh menggunakan nama yang sama dengan nama sel.

Cara menuliskan rumus microsoft excel Hlookup 
Penulisan rumus terhadap data yang akan dikelola bisa dilakukan dengan beberapa cara;
• Penulisan rumus dengan menggunakan alamat cell.
Contoh =Hlookup(B2;$B$10:$D$15;2).

Penting: Penulisan table_array (;$B$10:$D$15) harus diberi tanda $ artinya menjadi alamat cell absolut, agar ketika disalin ke cell lain tidak berubah.

• Penulisan rumus dengan menggunakan nama range pada tabel_array.
Contoh : Hlookup(B2;TABEL;2).

• Penulisan rumus dengan menggunakan Function Arguments Hlookup.
Berikut gambarnya.

Latihan 1
Petunjuk mengerjakan soal :
1.  Ketiklah judul dan tabel dibawah, dimana data pada kolom
NIM, Nama, dan Lama Studi di ketik manual, dan kolom yang lain diisi dengan memakai rumus sesuai dengan petunjuk mengerjakan soal nomor 2 sampai dengan 7.

2.  Kolom Kode  Jurusan  diisi  dengan  fungsi  teks  dengan  mengambil  1  karakter  mulai 
karakter ke-3 kolom NIM

3.  Kolom Jurusan  diisi dengan fungsi IF dengan ketentuan :
-Jika Kolom Kode Jurusan = A maka Keperawatan -Selainnya Kebidanan

4.  Kolom Uang Gedung diisi dengan fungsi Vlookup  berdasarkan Tabel A.

5.  Kolom SPP diisi dengan fungsi Hlookup berdasarkan Tabel B

6.  Kolom  Total SPP = Lama Studi * SPP

7.  Kolom Total Bayar = Uang Gedung + Total SPP



Latihan 2
Petunjuk mengerjakan soal :
1.    Ketiklah  judul  dan  tabel  dengan  kolom  No,  NIK,  dan  Nama  di  ketik  manual  dan  kolom 
yang  lain  dengan  memakai  rumus  dengan  ketentuan  seperti  pada  petunjuk  mengerjakan 
soal no. 2 sampai dengan 9

2.  Kolom
Kode  Bagian  diisi  dengan  fungsi  teks  dengan  mengambil  1  karakter  dari  sebelah  kiri kolom NIK

3.  Kolom Kode  Pendidikan  diisi  dengan  fungsi  teks  dengan  mengambil  1  karakter  mulai  karakter ke-2 kolom NIK

4.  Kolom Pendidikan diisi dengan fungsi IF dengan ketentuan :
-Jika Kolom Kode Pendidikan = A maka SMA
-Jika Kolom Kode Pendidikan = B maka D3
-Selainnya S1

5.  Kolom Bagian  di  isi  dengan  Fungsi  If  dengan  ketetntuan 
Jika  Kolom  Kode  Bagian  = A
maka Kolom Bagian = Produksi
 selainya Gudang
.
6.  Kolom  Gaji Pokok diisi dengan Fungsi Lookup berdasarkan Tabel A

7. Kolom Tunjangan diisi dengan fungsi lookup berdasarkan Tabel B

8. Kolom  Gaji Total = Gaji Pokok + Tunjangan

9. Sel Total Gaji dan Gaji Tertinggi diisi dengan fungsi statistik 


Latihan 3
1.Ketiklah data pada Tabel Soal dibawah ini: 

 Tabel Soal : 

 2.Isilah data pada kolom-kolom yang masih kosong dengan memakai rumus sehingga hasilnya seperti pada Table Hasil dibawah, dengan ketentuan dibawah ini:

1. Kolom Lama Inap (Hari) = Tanggal Sekarang (26 Juli 2010) – Tanggal Masuk
2. Kolom Presentase potongan  di gunakan rumus Lookup dengan menggunakan Tabel Pembantu
3. Kolom Biaya kamar/hari di isi dengan fungsi IF dengan ketentuan jika kelas kamar sama dengan VIP maka biaya kamar/harinya 100.000, selainnya itu 60.000
4. Kolom Biaya Fasilitas diisi dengan fungsi if dengan ketentuan jika Kelas kamarnya VIP dan Lama inapnya lebih dari 5 hari maka biaya fasilitasnya 50.000, selainya 10.000.  
5. Kolom Total biaya =Lama Inap (Hari) x Biaya kamar/Hari + Biaya Fasilitas 
6. Kolom Potongan Status = Presentasi Potongan x Total Biaya 
7. Kolom Biaya Akhir = Total Biaya – Potongan Status 
8. Buatlah Grafik Nama pasien (Sumbu X) terhadap Lama Inap (Sumbu Y) dengan jenis grafik Stacked Colum

. Tabel Hasil :

1. Ketiklah tabel dibawah ini

2. Isilah rumus pada kolom Gaji Pokok dan THR seperti dibawah ini:

3. Kopikan rumus diatas pada sel dibawahnya yang masih kosong, sehingga hasilnya seperti pada gambar dibawah ini.

Fungsi Lookup

Fungsi Lookup digunakan untuk memilih beberapa pilihan berdasarkan kondisi tertentu, dimana pilihan-pilihannya di gunakan tabel pembantu. Fungsi lookup yang sering dipakai yaitu Vlookup. Pada Vlookup ini tabel pembantunya berbentuk vertical (kepala table berada diatas atau dibawah). Rumus umumnya yaitu sebagai baerikut:

=VLOOKUP(Sel_Penghubung,Tabel_Pilihan, Nomor_Kolom)