Praktik Excel: Analisis Data
VLOOKUP, HLOOKUP, COUNTIF, SUMIF, dan Grafik
📋 Studi Kasus
Situasi: Kamu adalah petugas data kelas VIII. Tugasmu adalah mengolah data nilai siswa di kelasmu menggunakan Microsoft Excel. Gunakan berbagai fungsi untuk menganalisis data dengan tepat dan akurat.
📊 Tabel Utama - Data Nilai Siswa
| No | NIS | Nama Siswa | Kode Mapel | Nilai | Nama Mapel | KKM | Keterangan |
|---|---|---|---|---|---|---|---|
| 1 | 1408 | AGRIFA JECONIA BR TAMPUBOLON | MTK | 85 | Matematika | 80 | Lulus |
| 2 | 1409 | AMEL CINTYA BR HUTAURUK | IPA | 78 | Ilmu Pengetahuan Alam | 75 | Lulus |
| 3 | 1410 | ANDIKA EPRATEN TARIGAN | IPS | 72 | Ilmu Pengetahuan Sosial | 78 | Tidak Lulus |
| 4 | 1412 | ANSELMUS ALESSANDRO HASIHOLAN SIANTURI | MTK | 90 | Matematika | 80 | Lulus |
| 5 | 1423 | CHERYL CLAUDIA SEMBIRING | IPA | 88 | Ilmu Pengetahuan Alam | 75 | Lulus |
| 6 | 1425 | CHRISTINE MARTHA DAMANIK | IPS | 81 | Ilmu Pengetahuan Sosial | 78 | Lulus |
| 7 | 1426 | CLARA ELISABETH NAULI PANDIANGAN | MTK | 76 | Matematika | 80 | Tidak Lulus |
| 8 | 1428 | DANIEL GALANG SINAGA | IPA | 92 | Ilmu Pengetahuan Alam | 75 | Lulus |
| 9 | 1429 | DARRIEL EICHELIONEL SINULINGGA | IPS | 79 | Ilmu Pengetahuan Sosial | 78 | Lulus |
| 10 | 3254 | GAVIN YANPETRA ARSENIO S. MILALA | MTK | 94 | Matematika | 80 | Lulus |
Kolom berwarna abu-abu harus diisi menggunakan rumus Excel
💡 Panduan Rumus Tabel Utama
1️⃣ Rumus VLOOKUP (Nama Mapel)
=VLOOKUP(D2,$D$19:$E$21,2,0)
Penjelasan: Cari nilai dari D2 (Kode Mapel) di tabel referensi (D19:E21), ambil kolom ke-2 (nama mapel), dengan pencarian tepat (0)
📍 Parameter: (lookup_value, table_array, col_index_num, [range_lookup])
2️⃣ Rumus HLOOKUP (KKM)
=HLOOKUP(D2,$A$19:$D$20,2,0)
Penjelasan: Cari nilai dari D2 (Kode Mapel) di tabel referensi yang disusun horizontal (A19:D20), ambil baris ke-2 (nilai KKM)
📍 Catatan: Gunakan referensi absolut ($) pada tabel referensi
3️⃣ Rumus IF (Keterangan Lulus/Tidak Lulus)
=IF(E2>=G2,"Lulus","Tidak Lulus")
Penjelasan: Jika nilai (E2) lebih besar atau sama dengan KKM (G2), maka tampilkan "Lulus", jika tidak tampilkan "Tidak Lulus"
📍 Logika: IF(kondisi, nilai_jika_benar, nilai_jika_salah)
📋 Contoh Pengisian untuk Baris Pertama (AGRIFA):
Kolom F (Nama Mapel): =VLOOKUP(D2,$D$19:$E$21,2,0) → Hasil: Matematika
Kolom G (KKM): =HLOOKUP(D2,$A$19:$D$20,2,0) → Hasil: 80
Kolom H (Keterangan): =IF(E2>=G2,"Lulus","Tidak Lulus") → Hasil: Lulus (karena 85 ≥ 80)
📚 Tabel Referensi Kode Mapel
Untuk fungsi VLOOKUP
| Kode | Nama Mata Pelajaran |
|---|---|
| MTK | Matematika |
| IPA | Ilmu Pengetahuan Alam |
| IPS | Ilmu Pengetahuan Sosial |
🎯 Tabel Referensi KKM
Untuk fungsi HLOOKUP
| MTK | IPA | IPS |
|---|---|---|
| 80 | 75 | 78 |
📊 Tabel Analisis - COUNTIF & SUMIF
Buat tabel analisis berikut untuk menghitung jumlah siswa dan total nilai per mata pelajaran:
| Mata Pelajaran | Kode | Jumlah Siswa (COUNTIF) | Total Nilai (SUMIF) | Rata-rata Nilai |
|---|---|---|---|---|
| Matematika | MTK | ← COUNTIF | ← SUMIF | ← Total/Jumlah |
| Ilmu Pengetahuan Alam | IPA | ← COUNTIF | ← SUMIF | ← Total/Jumlah |
| Ilmu Pengetahuan Sosial | IPS | ← COUNTIF | ← SUMIF | ← Total/Jumlah |
📌 Fungsi COUNTIF
Menghitung jumlah data yang memenuhi kriteria tertentu
📌 Fungsi SUMIF
Menjumlahkan data berdasarkan kriteria yang ditentukan
📌 Rata-rata
Total Nilai dibagi Jumlah Siswa
💡 Panduan Rumus Excel
1️⃣ Rumus COUNTIF (Jumlah Siswa)
=COUNTIF($D$2:$D$11,D2)
Penjelasan: Hitung berapa banyak sel di kolom "Kode Mapel" (D2:D11) yang cocok dengan kode mapel pada baris ini (D2 untuk Matematika, D3 untuk IPA, dst)
📍 Referensi Absolut: $D$2:$D$11 (agar range tetap saat di-copy)
2️⃣ Rumus SUMIF (Total Nilai)
=SUMIF($D$2:$D$11,D2,$E$2:$E$11)
Penjelasan: Jumlahkan semua nilai pada kolom "Nilai" (E2:E11) dimana kolom "Kode Mapel" (D2:D11) cocok dengan kode mapel pada baris ini
📍 Urutan parameter: Range Kriteria, Kriteria, Range Penjumlahan
3️⃣ Rumus Rata-rata (Pembagian)
=C2/B2
Penjelasan: Bagi Total Nilai (C2) dengan Jumlah Siswa (B2) untuk mendapatkan rata-rata
📍 Catatan: Gunakan referensi relatif (B2, C2) agar otomatis berubah per baris
📋 Contoh Pengisian untuk Baris Pertama (Matematika):
Kolom B (Jumlah Siswa): =COUNTIF($D$2:$D$11,"MTK") → Hasil: 4
Kolom C (Total Nilai): =SUMIF($D$2:$D$11,"MTK",$E$2:$E$11) → Hasil: 345
Kolom D (Rata-rata): =345/4 → Hasil: 86.25
📈 Visualisasi Grafik Data
Matematika
📊Jumlah Siswa: 4
Total Nilai: 345
Rata-rata: 86.25
IPA
🔬Jumlah Siswa: 3
Total Nilai: 258
Rata-rata: 86.00
IPS
🌍Jumlah Siswa: 3
Total Nilai: 232
Rata-rata: 77.33
📊 Grafik Kolom - Total Nilai per Mata Pelajaran
📈 Grafik Garis - Trend Nilai
🥧 Grafik Lingkaran - Distribusi Siswa
📊 Grafik Batang - Jumlah Siswa Lulus/Tidak Lulus
✏️ Daftar Tugas Praktik
VLOOKUP - Nama Mata Pelajaran
Pada Tabel Utama, isi kolom "Nama Mapel" menggunakan fungsi VLOOKUP yang mencari kode dari kolom "Kode Mapel" pada tabel referensi kode mapel
HLOOKUP - Nilai KKM
Pada Tabel Utama, isi kolom "KKM" menggunakan fungsi HLOOKUP yang mencari kode dari kolom "Kode Mapel" pada tabel referensi KKM
IF - Status Lulus/Tidak Lulus
Pada Tabel Utama, isi kolom "Keterangan" menggunakan fungsi IF untuk membandingkan nilai siswa dengan KKM (jika nilai ≥ KKM, maka "Lulus", jika tidak "Tidak Lulus")
COUNTIF - Jumlah Siswa Per Mapel
Pada Tabel Analisis, isi kolom "Jumlah Siswa (COUNTIF)" untuk menghitung berapa banyak siswa yang mengambil setiap mata pelajaran
SUMIF - Total Nilai Per Mapel
Pada Tabel Analisis, isi kolom "Total Nilai (SUMIF)" untuk menjumlahkan semua nilai siswa untuk setiap mata pelajaran
Rata-rata Nilai
Pada Tabel Analisis, isi kolom "Rata-rata Nilai" dengan rumus pembagian antara Total Nilai dibagi Jumlah Siswa
Buat Grafik Data
Buat minimal 3 jenis grafik dari hasil analisis: Grafik Kolom, Grafik Garis, dan Grafik Lingkaran untuk memvisualisasikan data
💡 Tips Penting:
- • Gunakan referensi absolut ($) pada tabel referensi agar tidak berubah saat di-copy
- • Pastikan tabel referensi berada di area terpisah dari tabel utama
- • Cek kembali setiap rumus sebelum di-copy ke baris lainnya