A Praktikum ATTDIlmu Pemerintahan FISIP UNISAN
Pertemuan 9 dari 16

Pertemuan 9, Excel 2019 bagian 2

Fungsi statistik dan logika: merekap nilai lomba debat

Durasi 150 menit Excel 2019 Hasil: rekap nilai dan predikat 12 tim

Tujuan dan alur praktikum

Lomba Debat Kebijakan Publik sudah selesai digelar. Sekarang panitia harus merekap nilai 12 tim dari tiga dewan juri, menentukan predikat setiap tim, dan memutuskan siapa yang lolos ke babak semifinal. Mengerjakan ini satu per satu dengan kalkulator memakan waktu dan rawan salah. Hari ini Excel yang akan "mengambil keputusan" untuk Anda, mengikuti aturan yang Anda tuliskan dalam rumus.

Setelah praktikum ini, Anda mampu:

  1. Menghitung rata-rata, nilai tertinggi, dan nilai terendah dengan AVERAGE, MAX, dan MIN.
  2. Membuat keputusan otomatis dengan fungsi IF.
  3. Menyusun IF bertingkat untuk lebih dari dua kemungkinan hasil.
  4. Menggabungkan beberapa syarat dengan AND dan OR.
  5. Menghitung data yang memenuhi syarat tertentu dengan COUNTIF.
  6. Menyorot data secara otomatis dengan Conditional Formatting.

Alur waktu di lab

0–15'Pengantar dan empat fungsi statistik dasar
15–45'Konsep IF, lab menyusun rumus, AND/OR, COUNTIF (dengan simulator)
45–125'Praktik 1–6: rata-rata, predikat, status lolos, ringkasan, format bersyarat
125–150'Latihan mandiri, checklist, dan kuis

Dari rekap ke keputusan

Pertemuan 7 mengajarkan Excel menghitung. Hari ini Excel belajar "mempertimbangkan": jika nilai rata-rata 85 ke atas, maka berikan predikat A. Jika nilainya di bawah 75, tim itu tidak lolos semifinal. Kemampuan ini dipakai luas dalam pekerjaan pemerintahan: menentukan kelayakan penerima bantuan sosial berdasarkan kriteria pendapatan, menetapkan status kelulusan diklat aparatur, atau mengklasifikasikan desa berdasarkan indeks pembangunan.

Rumus adalah kebijakan yang dituliskan

Saat Anda menulis =IF(H5>=75,"Ya","Tidak"), Anda sebenarnya menuliskan sebuah aturan: "siapa pun yang nilainya 75 ke atas dinyatakan lolos". Ketelitian menulis syarat ini sama pentingnya dengan ketelitian merumuskan kriteria kebijakan.

Empat fungsi statistik dasar

FungsiFungsinyaContoh
SUMMenjumlahkan=SUM(E5:G5)
AVERAGEMenghitung rata-rata=AVERAGE(E5:G5)
MAXMencari nilai terbesar=MAX(H5:H16)
MINMencari nilai terkecil=MIN(H5:H16)

Cara penulisannya sama seperti SUM yang sudah Anda kenal: nama fungsi, kurung buka, range sel, kurung tutup. Fungsi-fungsi ini juga tersedia lewat panah kecil di tombol Home › Editing › AutoSum.

Fungsi IF: komputer yang mengambil keputusan

Fungsi IF punya tiga bagian, dipisahkan koma:

=IF( syarat , hasil_jika_benar , hasil_jika_salah )

Contoh: =IF(H5>=75,"Ya","Tidak") dibaca: "Jika nilai di H5 lebih besar atau sama dengan 75, tulis Ya. Jika tidak, tulis Tidak."

Operator pembandingArti
=Sama dengan
<>Tidak sama dengan
> dan <Lebih besar dan lebih kecil
>= dan <=Lebih besar/kecil atau sama dengan
Teks harus diapit tanda kutip

Tulis "Ya" dan "Tidak" dengan tanda kutip dua. Tanpa tanda kutip, Excel mengira Ya dan Tidak adalah nama sel atau fungsi, dan akan menampilkan #NAME?.

Lab: menyusun rumus IF bertingkat

Bagaimana kalau hasilnya bukan cuma dua pilihan (Ya/Tidak), melainkan empat predikat: A, B, C, D? Kita menyarangkan IF di dalam IF. Susun sendiri rumusnya di lab ini, sel demi sel, untuk kolom Predikat.

C2 fx
Tugas di lab ini (sel kuning boleh diisi). Ketentuan predikat: A jika ≥ 85, B jika ≥ 75, C jika ≥ 60, selain itu D.
  1. Klik C2, susun rumus IF bertingkat untuk predikat baris pertama, tekan Enter.
  2. Klik lagi C2, lalu Tarik fill handle ke bawah untuk menyalin ke semua baris.
  3. Periksa: apakah tim dengan nilai 60 mendapat predikat C, dan tim dengan nilai 59 mendapat predikat D?
Cara membaca IF bertingkat

=IF(B2>=85,"A",IF(B2>=75,"B",IF(B2>=60,"C","D"))) dibaca berurutan: "Jika ≥85, A. Kalau tidak, periksa lagi: jika ≥75, B. Kalau tidak, periksa lagi: jika ≥60, C. Kalau tidak semuanya, D." Susun syaratnya dari yang tertinggi ke terendah, karena Excel berhenti pada syarat pertama yang benar.

AND, OR, dan gabungan beberapa syarat

Kadang keputusan bergantung pada lebih dari satu syarat sekaligus. Di sinilah AND (semua syarat harus benar) dan OR (salah satu syarat cukup) dipakai di dalam IF.

KebutuhanRumus
Lolos jika nilai ≥ 75 dan tidak ada nilai juri di bawah 60=IF(AND(H5>=75,MIN(E5:G5)>=60),"Lolos","Tidak")
Dapat catatan khusus jika nilai ≥ 90 atau salah satu juri memberi nilai 100=IF(OR(H5>=90,MAX(E5:G5)=100),"Istimewa","Biasa")

AND dan OR sendiri menghasilkan TRUE atau FALSE. Coba ketik =AND(5>3,2>1) di sel kosong mana pun (boleh di lab di atas), hasilnya TRUE. Ganti angka kedua menjadi 2>5, hasilnya FALSE karena tidak semua syarat terpenuhi.

COUNTIF: menghitung dengan syarat

COUNTIF menghitung berapa banyak sel dalam sebuah range yang memenuhi satu syarat. Sangat berguna untuk membuat ringkasan, misalnya "berapa tim yang lolos semifinal?"

=COUNTIF( range , syarat )

KebutuhanRumus
Berapa tim berpredikat A?=COUNTIF(I5:I16,"A")
Berapa tim lolos semifinal?=COUNTIF(J5:J16,"Ya")
Berapa tim bernilai 75 ke atas?=COUNTIF(H5:H16,">=75")
Syarat berupa teks selalu dalam tanda kutip

Baik syarat berupa kata ("A", "Ya") maupun syarat berupa perbandingan (">=75") sama-sama ditulis di dalam tanda kutip, karena keduanya diperlakukan sebagai satu kesatuan syarat oleh COUNTIF.

Unduh file bahan

X ATTD_P09_Bahan_Rekap_Nilai.xlsxData nilai 12 tim lomba debat, kolom hasil masih kosong Unduh file

Simpan salinannya (F12) sebagai ATTD_P09_Rekap_Nilai_NIM_Nama.xlsx di folder 02_Excel\P09.

Praktik 1: Menyiapkan tabel

  1. Jelajahi data Buka file bahan. Sheet Rekap berisi 12 tim dengan tiga nilai juri: Argumentasi, Sanggahan, dan Kerja Sama. Kolom Rata-rata, Predikat, dan Lolos Semifinal masih kosong.
  2. Kenali batas data Klik sel mana pun di dalam tabel, tekan Ctrl + End untuk melompat ke sel terakhir yang berisi data. Ini membantu Anda memastikan range rumus nanti sudah mencakup semua baris.

Praktik 2: Rata-rata tiap tim

  1. Rumus rata-rata baris pertama Klik H5, ketik =AVERAGE(E5:G5), tekan Enter.
  2. Salin ke seluruh tim Klik H5, klik dua kali fill handle-nya untuk menyalin sampai baris terakhir (H16).
  3. Ringkasan di bawah tabel Di sel yang sudah diberi label "Rata-rata keseluruhan", "Nilai tertinggi", dan "Nilai terendah", buat rumus =AVERAGE(H5:H16), =MAX(H5:H16), dan =MIN(H5:H16).

Praktik 3: Predikat dengan IF bertingkat

  1. Susun rumus di baris pertama Klik I5, ketik: =IF(H5>=85,"A",IF(H5>=75,"B",IF(H5>=60,"C","D"))), tekan Enter.
  2. Salin ke seluruh tim Klik I5, klik dua kali fill handle.
  3. Periksa hasilnya Bandingkan dengan nilai rata-rata di kolom H. Tim dengan rata-rata 83,7 seharusnya mendapat predikat B, bukan A atau C.

Praktik 4: Status lolos semifinal

  1. Syarat lolos Ketentuan panitia: tim lolos semifinal jika rata-rata ≥ 75. Klik J5, ketik =IF(H5>=75,"Ya","Tidak"), Enter.
  2. Salin ke seluruh tim Klik J5, klik dua kali fill handle.
  3. Tantangan tambahan (opsional) Panitia menambah syarat: tim juga harus punya nilai kerja sama minimal 60. Ubah rumus J5 menjadi =IF(AND(H5>=75,G5>=60),"Ya","Tidak"), salin ulang ke seluruh tim, dan amati apakah ada tim yang statusnya berubah.

Praktik 5: Ringkasan dengan COUNTIF

  1. Jumlah tim lolos semifinal Di sel berlabel "Jumlah tim lolos semifinal", ketik =COUNTIF(J5:J16,"Ya").
  2. Jumlah tim predikat A Di sel berlabel "Jumlah tim predikat A", ketik =COUNTIF(I5:I16,"A").
  3. Uji keterkaitan seluruh rumus Ubah salah satu nilai juri, misalnya G6 (Kerja Sama Tim Biru) menjadi 95. Amati: rata-rata, predikat, status lolos, dan kedua ringkasan COUNTIF semuanya ikut berubah tanpa Anda sentuh satu pun secara manual. Kembalikan nilainya ke 60.
Inilah kekuatan sesungguhnya dari rumus

Satu perubahan data merambat ke semua rumus yang bergantung padanya. Bandingkan dengan cara manual: mengganti satu nilai berarti menghitung ulang rata-rata, predikat, status, dan kedua ringkasan itu satu per satu dengan kalkulator.

Praktik 6: Menyorot data dengan Conditional Formatting

Supaya laporan mudah dibaca sekilas, kita beri warna otomatis berdasarkan nilainya, tanpa mewarnai satu per satu secara manual.

  1. Sorot tim yang tidak lolos Blok J5:J16. Klik Home › Conditional Formatting › Highlight Cells Rules › Equal To. Ketik Tidak, pilih format Light Red Fill with Dark Red Text, OK.
  2. Sorot predikat A Blok I5:I16, ulangi dengan Equal To berisi A, format Green Fill with Dark Green Text.
  3. Data bar untuk rata-rata Blok H5:H16, klik Conditional Formatting › Data Bars, pilih salah satu warna. Setiap sel kini menampilkan batang kecil yang panjangnya sebanding dengan nilainya.
  4. Kelola aturan Buka Conditional Formatting › Manage Rules untuk melihat, mengubah, atau menghapus aturan yang sudah dibuat.
Format ikut mengikuti hasil rumus

Karena Conditional Formatting membaca hasil sel (bukan rumusnya), warna akan otomatis berubah jika status "Ya/Tidak" atau predikat berubah akibat data yang diperbarui.

Tombol pintas hari ini

TombolFungsi
Ctrl + EndMelompat ke sel terakhir yang berisi data
Ctrl + Shift + EnterRumus array (di luar cakupan pertemuan ini, sekadar dikenalkan)
Shift + klik sel lainMemperluas blok/range yang dipilih
Ctrl + klik selMenambah sel terpisah ke dalam pilihan
Ctrl + `Menampilkan atau menyembunyikan rumus di seluruh sheet

Kesalahan yang sering terjadi

MasalahPenyebabSolusi
#NAME? pada rumus IFTeks hasil tidak diapit tanda kutip, misalnya =IF(H5>=75,Ya,Tidak).Tambahkan tanda kutip: "Ya", "Tidak".
Semua tim mendapat predikat samaUrutan syarat pada IF bertingkat tidak diurutkan dari tertinggi ke terendah.Susun ulang: syarat ≥85 diperiksa lebih dulu, baru ≥75, lalu ≥60.
Jumlah kurung buka dan tutup tidak seimbangIF bertingkat butuh satu kurung tutup untuk setiap IF yang dibuka.Hitung ulang: tiga IF berarti tiga kurung tutup di akhir rumus.
COUNTIF selalu menghasilkan 0Syarat tidak cocok persis, misalnya mencari "ya" padahal data berisi "Ya" dengan spasi tambahan.Periksa ejaan syarat, dan pastikan tidak ada spasi tersembunyi di data.
Conditional Formatting tidak muncul di baris baruAturan hanya berlaku pada range yang diblok saat aturan dibuat.Manage Rules › Edit Rule, perluas Applies to.

Latihan mandiri

Dasar

Selisih nilai tertinggi dan terendah

Tambahkan baris "Selisih nilai tertinggi dan terendah" di bawah ringkasan, dengan rumus yang merujuk sel MAX dan MIN yang sudah ada (bukan mengetik ulang angkanya).

Menengah

Klasifikasi tiga kategori dengan OR

Tambahkan kolom "Kategori Sekolah": tuliskan "Kota" jika Asal Sekolah adalah Sekolah A atau Sekolah B, selain itu "Luar Kota". Gunakan =IF(OR(C5="Sekolah A",C5="Sekolah B"),"Kota","Luar Kota").

Tantangan

Peringkat dengan RANK

Tambahkan kolom "Peringkat" menggunakan =RANK(H5,$H$5:$H$16). Amati bahwa alamat range-nya dikunci ($) agar tetap merujuk seluruh tabel saat disalin. Urutkan tabel berdasarkan Peringkat dengan Data › Sort, lalu jelaskan apa yang terjadi pada rumus IF dan COUNTIF setelah data diurutkan ulang.

Tugas pertemuan 9

Dikumpulkan

Rekap nilai Seminar Nasional (kuis pemahaman peserta)

Panitia mengadakan kuis singkat untuk 15 peserta seminar (data peserta dari Pertemuan 6 boleh dipakai ulang). Buat sheet baru berisi kolom: No, Nama, Nilai Kuis (0–100), Kehadiran (Hadir/Tidak Hadir; boleh diisi acak), dengan ketentuan:

  • Kolom Predikat: "Baik Sekali" (≥85), "Baik" (≥70), "Cukup" (≥55), "Perlu Perbaikan" (di bawah itu), memakai IF bertingkat.
  • Kolom Berhak Sertifikat Kuis: "Ya" jika nilai ≥70 dan Kehadiran "Hadir", selain itu "Tidak", memakai AND.
  • Ringkasan: rata-rata, nilai tertinggi, nilai terendah, jumlah peserta predikat "Baik Sekali" (COUNTIF), dan jumlah peserta berhak sertifikat (COUNTIF).
  • Conditional Formatting pada kolom Berhak Sertifikat Kuis dan pada kolom Nilai Kuis (Data Bars).

Simpan sebagai ATTD_P09_Rekap_Kuis_Seminar_NIM_Nama.xlsx di 02_Excel\P09, unggah ke OneDrive, dan kumpulkan melalui kanal yang ditentukan dosen.

Checklist hasil praktikum

Kuis cek pemahaman

Pilih jawaban, lalu baca penjelasannya. Belum ada jawaban

1. Rumus =IF(H5>=75,Lolos,Tidak) menghasilkan #NAME?. Penyebabnya...
Tanpa tanda kutip, Excel mencari sel atau nama bernama Lolos dan Tidak, yang tidak ada.
2. Pada IF bertingkat untuk predikat A/B/C/D, mengapa syarat ≥85 harus diperiksa lebih dulu?
Jika syarat ≥60 diperiksa lebih dulu, nilai 90 pun akan langsung dianggap memenuhi syarat itu dan tidak pernah sampai diperiksa sebagai A.
3. Tim dinyatakan lolos jika nilai ≥75 dan nilai kerja sama ≥60. Fungsi yang tepat untuk menggabungkan dua syarat ini adalah...
AND menghasilkan TRUE hanya jika semua syarat di dalamnya terpenuhi.
4. Rumus untuk menghitung berapa banyak tim yang berpredikat "A" adalah...
COUNTIF menghitung jumlah sel dalam range yang memenuhi syarat tertentu.
5. Setelah nilai salah satu tim diubah, predikat dan status lolosnya tidak ikut berubah. Kemungkinan penyebabnya...
Rumus akan otomatis menyesuaikan saat data berubah. Jika hasilnya tidak berubah, kemungkinan besar itu adalah nilai yang diketik langsung, bukan hasil rumus.

Rubrik penilaian tugas

AspekBobotKriteria nilai penuh
Kelengkapan data10%15 peserta dengan data nilai dan kehadiran yang wajar.
IF bertingkat (Predikat)25%Empat kategori benar, syarat terurut dari tertinggi, berfungsi untuk semua baris.
AND (Berhak Sertifikat)20%Menggabungkan syarat nilai dan kehadiran dengan tepat.
Ringkasan statistik dan COUNTIF25%AVERAGE, MAX, MIN, dan dua COUNTIF benar dan merujuk range yang tepat.
Conditional Formatting10%Diterapkan pada minimal dua kolom, berfungsi dan mudah dibaca.
Kerapian dan file10%Format rapi, nama file sesuai aturan, terunggah ke OneDrive.
Pertemuan berikutnya

Pertemuan 10: Fungsi pencarian, teks, dan tanggal

Buka Pertemuan 10