LKPD Excel Lanjutan: Kuasai Fungsi VLOOKUP & IF!

LKPD Microsoft Excel Tingkat Lanjutan: Mengolah Data untuk Kebutuhan Pekerjaan Harian

Mata Pelajaran: Teknologi Informasi dan Komunikasi (TIK)

Kelas: SMK (Semua Tingkat)

Tujuan Pembelajaran: Setelah menyelesaikan LKPD ini, siswa diharapkan mampu:

1. Memahami dan menggunakan fungsi-fungsi lanjutan di Microsoft Excel.
2. Menerapkan fungsi-fungsi tersebut untuk memecahkan masalah dalam konteks pekerjaan harian.
3. Mengolah dan menganalisis data dengan efisien menggunakan Microsoft Excel.

Petunjuk: Kerjakan LKPD ini secara bertahap dan berkelompok (jika memungkinkan). Bacalah petunjuk dengan teliti dan praktikkan setiap langkah. Diskusikan jika mengalami kesulitan.

Bagian 1: Fungsi LOOKUP dan VLOOKUP (Pencarian Data)

Penjelasan:

• Fungsi LOOKUP: Mencari nilai dalam satu kolom atau baris dan mengembalikan nilai yang sesuai dari kolom atau baris lain. Sederhana, tetapi kurang fleksibel dibandingkan VLOOKUP.
• Fungsi VLOOKUP: Mencari nilai dalam kolom pertama dari sebuah tabel dan mengembalikan nilai dari kolom yang ditentukan. Lebih fleksibel dan sering digunakan untuk pencarian data yang lebih kompleks. Sintaks: VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
  • lookup_value: Nilai yang dicari.
  • table_array: Range sel yang berisi tabel data.
  • col_index_num: Nomor kolom yang berisi nilai yang ingin dikembalikan (kolom pertama dalam table_array adalah 1).
  • range_lookup: TRUE (atau dikosongkan) untuk pencarian perkiraan, FALSE untuk pencarian tepat. Untuk data yang tepat, gunakan FALSE.

Contoh Kasus:

Sebuah toko buku memiliki data buku berikut:

Buatlah rumus VLOOKUP untuk mencari harga buku dengan kode "BK002".

Jawaban: =VLOOKUP("BK002";A1:C4;3;FALSE) (Asumsi data berada di range A1:C4)

Praktik:

Buatlah tabel data penjualan buku dengan kolom: Kode Buku, Judul Buku, Jumlah Terjual. Gunakan VLOOKUP untuk menampilkan harga buku berdasarkan kode buku pada kolom baru "Total Harga". (Data buku dari contoh kasus di atas).

Bagian 2: Fungsi IF (Kondisi Logika)

Penjelasan:

Fungsi IF digunakan untuk melakukan pengujian kondisi dan mengembalikan nilai yang berbeda berdasarkan hasil pengujian. Sintaks: IF(logical_test, value_if_true, value_if_false)

• logical_test: Ekspresi logika yang menghasilkan TRUE atau FALSE.
• value_if_true: Nilai yang dikembalikan jika logical_test TRUE.
• value_if_false: Nilai yang dikembalikan jika logical_test FALSE.

Contoh Kasus:

Hitung bonus karyawan berdasarkan target penjualan. Jika penjualan >= 10 juta, bonus 10%, jika < 10 juta, bonus 5%.

Jawaban: =IF(A1>=10000000;A1*0.1;A1*0.5) (Asumsi penjualan di sel A1)

Praktik:

Buatlah tabel data penjualan karyawan dengan kolom: Nama Karyawan, Penjualan. Gunakan fungsi IF untuk menghitung bonus masing-masing karyawan berdasarkan aturan di contoh kasus di atas.

Bagian 3: Fungsi SUMIF dan COUNTIF (Agregasi Bersyarat)

Penjelasan:

• Fungsi SUMIF: Menjumlahkan nilai-nilai dalam range yang memenuhi kriteria tertentu. Sintaks: SUMIF(range, criteria, [sum_range])
• Fungsi COUNTIF: Menghitung jumlah sel dalam range yang memenuhi kriteria tertentu. Sintaks: COUNTIF(range, criteria)

Contoh Kasus:

Hitung total penjualan buku "Pemrograman Java".

Jawaban: =SUMIF(B:B;"Pemrograman Java";C:C) (Asumsi kolom B berisi Judul Buku, kolom C berisi Jumlah Terjual)

Praktik:

Gunakan fungsi SUMIF dan COUNTIF untuk menghitung total penjualan dan jumlah buku yang terjual untuk setiap judul buku.

Contoh Kasus untuk Siswa:

1. Membuat Lembar Kerja dan Memasukkan Data

• Buka lembar kerja Excel baru.
• Masukkan data penjualan ke dalam kolom-kolom yang sesuai (No., Produk, Harga Satuan, Jumlah Terjual, Diskon (%)).
• Pastikan data terstruktur dengan rapi seperti dalam tabel yang Anda berikan.

2. Menggunakan VLOOKUP untuk Mencari Harga Satuan

• Misalnya, Anda ingin mencari harga satuan "Laptop A". Ketik "Laptop A" di sel yang kosong (misalnya, di kolom terpisah).
• Di sel sebelahnya, gunakan rumus VLOOKUP: =VLOOKUP("Laptop A",B2:E6,2,FALSE)
  • "Laptop A" adalah nilai yang dicari.
  • B2:E6 adalah rentang tabel data penjualan.
  • 2 adalah nomor kolom di tabel yang berisi harga satuan (kolom ke-2).
  • FALSE menunjukkan kita ingin mencari nilai yang persis sama.
• Excel akan otomatis menampilkan harga satuan Laptop A (12000000).

3. Menghitung Total Harga Sebelum Diskon

• Buat kolom baru di sebelah kolom "Diskon (%)" dan beri nama "Total Harga Sebelum Diskon".
• Di baris pertama kolom ini, masukkan rumus: =C2*D2 (Harga Satuan * Jumlah Terjual).
• Klik dan seret sudut kanan bawah sel rumus ke bawah untuk menerapkan rumus yang sama ke baris-baris lainnya.

4. Menghitung Diskon

• Buat kolom baru lagi di sebelah kolom "Total Harga Sebelum Diskon" dan beri nama "Diskon".
• Di baris pertama kolom ini, masukkan rumus: =F2*(E2/100) (Total Harga Sebelum Diskon * (Diskon (%)/100)).
• Klik dan seret sudut kanan bawah sel rumus ke bawah untuk menerapkan rumus yang sama ke baris-baris lainnya.

5. Menghitung Total Harga Setelah Diskon

• Buat kolom baru lagi di sebelah kolom "Diskon" dan beri nama "Total Harga Setelah Diskon".
• Di baris pertama kolom ini, masukkan rumus: =F2-G2 (Total Harga Sebelum Diskon - Diskon).
• Klik dan seret sudut kanan bawah sel rumus ke bawah untuk menerapkan rumus yang sama ke baris-baris lainnya.

6. Menghitung Total Penjualan Keseluruhan

• Di sel kosong di bawah kolom "Total Harga Setelah Diskon", gunakan fungsi SUM: =SUM(H2:H6) (asumsi total harga setelah diskon ada di kolom H).
• Excel akan menjumlahkan semua nilai di kolom tersebut dan menampilkan total penjualan keseluruhan.

7. Menghitung Jumlah Penjualan per Jenis Produk

• Untuk memudahkan, urutkan data berdasarkan nama produk (Laptop A, HP B, TV C).
• Di sel kosong, gunakan fungsi COUNTIF untuk menghitung jumlah penjualan masing-masing produk. Contoh:
  • Laptop A: =COUNTIF(B2:B6,"Laptop A")
  • HP B: =COUNTIF(B2:B6,"HP B")
  • TV C: =COUNTIF(B2:B6,"TV C")

8. Menggunakan Fungsi IF untuk Memberikan Label Penjualan

• Buat kolom baru di sebelah kolom "Jumlah Terjual" dan beri nama "Status Penjualan".
• Di baris pertama kolom ini, masukkan rumus IF: =IF(D2>5,"Penjualan Tinggi","Penjualan Rendah")
  • D2>5 adalah kondisi yang diperiksa.
  • "Penjualan Tinggi" adalah nilai jika kondisi benar.
  • "Penjualan Rendah" adalah nilai jika kondisi salah.
• Klik dan seret sudut kanan bawah sel rumus ke bawah untuk menerapkan rumus yang sama ke baris-baris lainnya.

Penjelasan Singkat Fungsi-fungsi yang Digunakan

• VLOOKUP: Mencari nilai dalam kolom pertama suatu rentang, dan kemudian mengembalikan nilai dalam baris yang sama dari kolom yang ditentukan.
• SUM: Menjumlahkan angka-angka dalam suatu rentang.
• COUNTIF: Menghitung jumlah sel dalam suatu rentang yang memenuhi kriteria yang diberikan.
• IF: Melakukan uji logika dan mengembalikan satu nilai jika kondisi benar, dan nilai lain jika kondisi salah.

Dengan mengikuti langkah-langkah ini, Anda akan dapat membuat lembar kerja Excel yang berisi data penjualan, melakukan berbagai analisis, dan mendapatkan informasi yang berguna untuk pengambilan keputusan.

Penilaian: LKPD ini akan dinilai berdasarkan kelengkapan jawaban, keakuratan rumus, dan presentasi hasil kerja.

Semoga LKPD ini membantu siswa memahami dan mengaplikasikan fungsi-fungsi lanjutan Microsoft Excel. Jangan ragu untuk bertanya kepada guru jika mengalami kesulitan.