A Praktikum ATTDIlmu Pemerintahan FISIP UNISAN
Pertemuan 11 dari 16

Pertemuan 11, Excel 2019 bagian 4

Sort, Filter, Conditional Formatting, dan grafik pendaftar

Durasi 150 menit Excel 2019 Hasil: analisis dan grafik pendaftar seminar

Tujuan dan alur praktikum

Pendaftaran Seminar Nasional sudah ditutup. Panitia mempunyai daftar 24 pendaftar yang tercatat apa adanya, sesuai urutan masuk. Sekretaris membutuhkan daftar itu terurut per kategori untuk mencetak name tag, bendahara ingin menyaring siapa saja yang belum melunasi pembayaran, dan ketua panitia ingin grafik ringkas untuk laporan ke pihak fakultas. Satu tabel data yang sama, tiga kebutuhan berbeda. Semuanya bisa dipenuhi tanpa mengubah data aslinya.

Setelah praktikum ini, Anda mampu:

  1. Mengurutkan data dengan satu atau beberapa kunci pengurutan (Sort).
  2. Menyaring data dengan AutoFilter berdasarkan satu atau beberapa syarat.
  3. Membatasi apa yang boleh diketik di sebuah sel dengan Data Validation.
  4. Menyorot data secara bertingkat dengan Color Scale dan Icon Set.
  5. Memilih dan membuat jenis grafik yang tepat untuk data yang berbeda-beda.

Alur waktu di lab

0–20'Pengantar, konsep Sort dan Filter, simulator
20–35'Data Validation dan Conditional Formatting lanjutan (materi)
35–50'Memilih jenis grafik yang tepat (materi dan uji cepat)
50–125'Praktik 1–5: sort, filter, validation, color scale/icon set, grafik
125–150'Latihan mandiri, checklist, dan kuis

Dari data mentah ke informasi

Data yang tersusun apa adanya (sesuai urutan masuk) disebut data mentah. Data itu baru menjadi informasi yang berguna setelah diurutkan, disaring, atau divisualisasikan sesuai pertanyaan yang ingin dijawab. Kemampuan ini dipakai setiap hari dalam pekerjaan pemerintahan: mengurutkan pemohon bantuan berdasarkan prioritas, menyaring warga yang belum melapor pajak, atau membuat grafik tren jumlah pengunjung layanan publik per bulan.

Sort dan Filter tidak mengubah data

Mengurutkan atau menyaring data hanya mengubah tampilannya. Data aslinya tetap ada; Anda selalu bisa mengurutkan ulang atau menampilkan semua baris kembali. Ini berbeda dengan menghapus baris, yang benar-benar menghilangkan data.

Sort dan Filter: konsep dasar

Sort (mengurutkan)

Menyusun ulang seluruh baris berdasarkan nilai satu kolom atau lebih, dari kecil ke besar (A-Z, 1-9, tanggal lama-baru) atau sebaliknya. Semua baris tetap ditampilkan, hanya urutannya berubah.

Filter (menyaring)

Menyembunyikan baris yang tidak memenuhi syarat, dan menampilkan hanya yang cocok. Urutan baris yang tampil tidak berubah, hanya sebagian yang disembunyikan sementara.

Keduanya diaktifkan melalui panah kecil di kepala kolom yang muncul setelah Anda mengklik Data › Sort & Filter › Filter (atau Ctrl + Shift + L).

Simulator Sort dan Filter

Klik panah di kepala kolom untuk membuka menunya. Dari situ Anda bisa mengurutkan (Sort A-Z / Z-A) atau menyaring dengan mencentang/menghapus centang nilai tertentu, lalu klik Terapkan.

Data Klik panah di kepala kolom untuk Sort/Filter
No Nama Kategori Status Pembayaran Metode

Coba: saring Status Pembayaran agar hanya menampilkan "Belum Lunas", lalu urutkan Nama dari A ke Z. Baris yang belum lunas juga diberi warna merah muda, contoh sederhana Conditional Formatting berbasis rumus yang akan kita pelajari sebentar lagi.

Data Validation: mencegah data ngawur

Kolom Status Pembayaran seharusnya hanya berisi "Lunas" atau "Belum Lunas". Tanpa pembatasan, seseorang bisa saja mengetik "lunas", "Sudah Bayar", atau "-", yang membuat rumus COUNTIF dan grafik jadi tidak akurat karena setiap variasi ejaan dihitung terpisah.

Data Validation membatasi apa yang boleh diketik di sebuah sel. Jenis yang paling sering dipakai adalah List: sel hanya bisa diisi dari daftar pilihan, muncul sebagai dropdown seperti di Word/Excel pada umumnya.

Manfaatnya melampaui mencegah salah ketik

Dropdown juga mempercepat pengisian data. Petugas loket, misalnya, tinggal memilih dari daftar jenis layanan yang tersedia, tanpa mengetik dan tanpa risiko variasi penulisan.

Conditional Formatting lanjutan

Pertemuan 9 telah memperkenalkan Highlight Cells Rules dan Data Bars. Ada dua jenis lain yang berguna untuk data numerik:

JenisCocok untukContoh
Color ScaleMenunjukkan gradasi nilai dengan gradasi warna (merah → kuning → hijau)Skor kelengkapan pembayaran dari 0 sampai 100
Icon SetMenandai kategori nilai dengan simbol (lampu lalu lintas, panah, bintang)Status capaian target per triwulan

Selain itu, aturan berbasis rumus (New Rule › Use a formula to determine which cells to format) memungkinkan Conditional Formatting mengikuti logika apa pun, misalnya mewarnai seluruh baris ketika satu kolom tertentu bernilai "Belum Lunas" — seperti yang Anda lihat pada simulator di atas.

Memilih jenis grafik yang tepat

Grafik yang salah jenis justru bisa menyesatkan pembaca. Kenali empat jenis dasar berikut:

Bar/ColumnMembandingkan jumlah antarkategori. Contoh: jumlah pendaftar per kategori.
LineMenunjukkan tren dari waktu ke waktu. Contoh: jumlah pendaftar per hari.
PieMenunjukkan proporsi dari keseluruhan (harus 100%). Contoh: persentase Mahasiswa vs Umum.
ScatterMenunjukkan hubungan antara dua data numerik. Contoh: usia peserta dibanding nilai kuis.

Uji cepat: grafik mana yang cocok?

Persentase pendaftar Mahasiswa dibanding Umum dari total keseluruhan
Tren jumlah pendaftar setiap hari sejak pendaftaran dibuka
Perbandingan jumlah pendaftar dari lima asal instansi berbeda
Hubungan antara usia peserta dan skor kuis yang mereka peroleh

Unduh file bahan

X ATTD_P11_Bahan_Data_Pendaftar.xlsx24 data pendaftar seminar dan sheet Rekap kosong Unduh file

Simpan salinannya (F12) sebagai ATTD_P11_Data_Pendaftar_NIM_Nama.xlsx di folder 02_Excel\P11.

Praktik 1: Sort data pendaftar

  1. Urutkan sederhana Klik salah satu sel di kolom Nama, lalu klik Data › Sort A to Z. Seluruh baris ikut berpindah bersama nama pemiliknya, tidak hanya kolom Nama saja.
  2. Urutkan dengan beberapa kunci Klik sel mana pun di dalam tabel, klik Data › Sort (bukan A-Z). Pada jendela Sort: Sort by Kategori (A to Z), klik Add Level, Then by Nama (A to Z), OK.
  3. Periksa hasilnya Data kini terkelompok per Kategori (Mahasiswa dahulu, baru Umum), dan di dalam tiap kelompok terurut nama. Cocok untuk mencetak name tag per kelompok.
Pastikan tabel punya judul kolom

Saat jendela Sort terbuka, pastikan My data has headers tercentang. Jika tidak, baris judul (No, Nama, Kategori, ...) akan ikut terurut sebagai data.

Praktik 2: Menyaring data dengan Filter

  1. Aktifkan Filter Klik sel mana pun di dalam tabel, tekan Ctrl + Shift + L. Panah kecil muncul di setiap judul kolom.
  2. Saring satu kolom Klik panah di kolom Status Pembayaran. Hapus centang Lunas, sisakan Belum Lunas, klik OK. Hanya pendaftar yang belum melunasi yang tampil, dengan nomor baris melompat menandakan ada baris tersembunyi.
  3. Tambahkan syarat kedua Klik panah kolom Kategori, sisakan hanya Mahasiswa. Sekarang tabel menampilkan gabungan dua syarat: belum lunas dan berkategori Mahasiswa.
  4. Salin hasil filter Blok hasil yang tampil (termasuk judul kolom), Ctrl + C, tempel di sheet baru bernama Belum Lunas. Hanya baris yang terlihat yang ikut tersalin.
  5. Kembalikan tampilan Kembali ke sheet Pendaftar, klik Data › Clear untuk menghapus semua syarat filter sekaligus, tanpa mematikan Filter itu sendiri.

Praktik 3: Data Validation pada Status Pembayaran

  1. Blok kolom yang dibatasi Blok seluruh sel data di kolom Status Pembayaran (F5:F28).
  2. Buka Data Validation Klik Data › Data Validation.
  3. Atur jenis List Di tab Settings, pilih Allow: List. Di kotak Source, ketik Lunas,Belum Lunas (dipisah koma, tanpa spasi).
  4. Tambahkan pesan kesalahan (opsional tapi dianjurkan) Buka tab Error Alert, isi Title Data tidak valid dan Error message Pilih salah satu: Lunas atau Belum Lunas. OK.
  5. Uji coba Klik salah satu sel di kolom itu: muncul tanda panah dropdown. Coba ketik manual lunas (huruf kecil semua) atau Sudah: Excel menolak dan menampilkan pesan kesalahan Anda.

Praktik 4: Color Scale dan Icon Set

  1. Tambahkan kolom bantu Di kolom H, beri judul "Skor Kelengkapan". Isi 100 untuk baris berstatus Lunas dan 0 untuk Belum Lunas (boleh diketik manual, atau dengan =IF(F5="Lunas",100,0) mengulang Pertemuan 9).
  2. Terapkan Color Scale Blok H5:H28, klik Conditional Formatting › Color Scales, pilih skema merah-kuning-hijau. Nilai 100 otomatis hijau, 0 otomatis merah.
  3. Terapkan Icon Set Dengan H5:H28 masih terblok, klik Conditional Formatting › Icon Sets, pilih 3 Traffic Lights. Setiap sel kini mendapat simbol lampu sesuai nilainya.
  4. Lihat bersamaan Color Scale dan Icon Set boleh diterapkan pada range yang sama sekaligus, seperti pada file contoh yang akan diberikan dosen sebagai pembanding.
Mengedit ambang batas Icon Set

Buka Manage Rules › Edit Rule untuk mengubah nilai ambang setiap ikon, misalnya supaya lampu hijau hanya untuk nilai 100 persis, bukan 67 ke atas.

Praktik 5: Membuat grafik

  1. Siapkan tabel ringkas di sheet Rekap Isi B4 dan B5 dengan =COUNTIF(Pendaftar!C5:C28,"Mahasiswa") dan =COUNTIF(Pendaftar!C5:C28,"Umum") (mengulang COUNTIF dari Pertemuan 9). Grafik selalu dibuat dari tabel ringkas seperti ini, bukan langsung dari data mentah 24 baris.
  2. Pilih data untuk grafik Blok A3:B5 (judul kolom Kategori dan Jumlah Pendaftar beserta isinya).
  3. Sisipkan grafik batang Klik Insert › Charts › Column Chart › Clustered Column.
  4. Beri judul dan label Klik judul grafik bawaan ("Jumlah Pendaftar"), ganti menjadi Jumlah Pendaftar per Kategori. Klik grafik, lalu Chart Design › Add Chart Element › Axis Titles untuk memberi judul sumbu X (Kategori) dan sumbu Y (Jumlah).
  5. Pindahkan ke lokasi yang rapi Seret grafik ke area kosong di sebelah kanan tabel Rekap agar tidak menutupi data.
  6. Coba jenis lain (opsional) Blok A4:B5 saja (tanpa judul kolom "Jumlah Pendaftar" di B3), sisipkan Insert › Charts › Pie Chart untuk melihat proporsi Mahasiswa dan Umum sebagai lingkaran.

Tombol pintas hari ini

TombolFungsi
Ctrl + Shift + LMenyalakan atau mematikan AutoFilter
Alt + ↓ (pada sel berfilter)Membuka menu filter kolom itu
Alt + F1Membuat grafik cepat dari data terpilih
F11Membuat grafik di sheet baru dari data terpilih

Kesalahan yang sering terjadi

MasalahPenyebabSolusi
Hanya sebagian kolom yang ikut terurut, data jadi kacauHanya satu kolom yang diblok sebelum Sort, bukan seluruh tabel.Batalkan (Ctrl + Z), klik sel di dalam tabel (bukan blok satu kolom), lalu Sort ulang. Excel otomatis mendeteksi batas tabel.
Baris judul ikut tersaring atau terurut sebagai dataMy data has headers tidak dicentang.Ulangi Sort/Filter dengan opsi itu dicentang.
Tidak yakin apakah filter masih aktifIkon corong kecil di kepala kolom menandakan filter sedang aktif di kolom itu, sering terlewat.Gunakan Data › Clear untuk memastikan semua syarat filter dihapus.
Data Validation tidak mencegah data lama yang sudah salahValidation hanya mencegah data baru yang menyalahi aturan, data lama tidak otomatis diperiksa.Gunakan Data Validation › Circle Invalid Data untuk menandai data lama yang tidak sesuai.
Grafik menampilkan angka yang aneh atau kosongData yang diblok termasuk baris data mentah (24 baris), bukan tabel ringkasnya.Buat dulu tabel ringkas (misalnya dengan COUNTIF), baru buat grafik dari situ.
Pie chart terlihat aneh untuk banyak kategori kecilPie chart cocok untuk sedikit kategori yang jumlahnya proporsional terhadap keseluruhan.Gunakan Bar/Column chart untuk kategori yang banyak atau nilainya timpang jauh.

Latihan mandiri

Dasar

Urutkan berdasarkan tanggal

Urutkan data pendaftar berdasarkan Tanggal Daftar, dari yang paling awal mendaftar. Siapa lima pendaftar pertama?

Menengah

Filter kustom

Gunakan Text Filters › Contains pada kolom Asal untuk menyaring pendaftar yang asalnya mengandung kata "Universitas". Catat berapa banyak yang tersaring, lalu bandingkan dengan hasil =COUNTIF(Pendaftar!D5:D28,"*Universitas*") di sel kosong mana pun.

Tantangan

Grafik tren pendaftaran harian

Buat tabel ringkas jumlah pendaftar per tanggal (petunjuk: gunakan COUNTIF dengan kriteria tanggal, atau Sort dulu berdasarkan tanggal untuk melihat pola), lalu buat Line Chart yang menunjukkan tren jumlah pendaftar dari hari ke hari.

Tugas pertemuan 11

Dikumpulkan

Analisis pendaftar Lomba Debat Kebijakan Publik

Buat data pendaftar Lomba Debat (boleh memakai ulang data peserta dari Pertemuan 9–10, ditambah kolom Status Pembayaran dan Tanggal Daftar) minimal 15 baris, lalu:

  • Urutkan data dengan minimal dua kunci pengurutan (misalnya Asal Sekolah, lalu Nama).
  • Terapkan Filter dan tunjukkan (dalam sheet terpisah, hasil salinan) minimal satu kondisi gabungan (dua kolom disaring sekaligus).
  • Terapkan Data Validation berupa dropdown pada kolom Status Pembayaran.
  • Terapkan Color Scale atau Icon Set pada satu kolom numerik yang relevan (skor, nilai, atau jumlah pembayaran).
  • Buat sebuah tabel ringkas dan minimal dua grafik dengan jenis yang berbeda (misalnya Bar untuk perbandingan sekolah, dan Pie untuk proporsi status pembayaran), masing-masing berjudul dan berlabel sumbu yang jelas.

Simpan sebagai ATTD_P11_Analisis_Pendaftar_NIM_Nama.xlsx di 02_Excel\P11, 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. Perbedaan mendasar antara Sort dan Filter adalah...
Sort menata ulang posisi semua baris. Filter menampilkan sebagian dan menyembunyikan sisanya, tanpa mengubah urutan aslinya.
2. Sebelum menjalankan Sort pada tabel berjudul kolom, pengaturan yang wajib diperiksa adalah...
Tanpa centang ini, Excel memperlakukan baris judul sebagai data biasa dan bisa ikut terurut.
3. Agar kolom Status Pembayaran hanya bisa diisi "Lunas" atau "Belum Lunas", gunakan...
Data Validation List membatasi input sel hanya pada pilihan yang telah ditentukan.
4. Untuk menunjukkan proporsi pendaftar Mahasiswa dan Umum terhadap keseluruhan pendaftar, jenis grafik paling tepat adalah...
Pie chart dirancang khusus untuk menunjukkan proporsi bagian terhadap keseluruhan (100%).
5. Sebelum membuat grafik jumlah pendaftar per kategori, langkah yang seharusnya dilakukan lebih dulu adalah...
Grafik dibuat dari data yang sudah diringkas per kategori, bukan langsung dari baris data mentah satu per satu.

Rubrik penilaian tugas

AspekBobotKriteria nilai penuh
Kelengkapan data10%Minimal 15 baris dengan kolom yang lengkap dan wajar.
Sort15%Minimal dua kunci pengurutan, hasilnya benar dan konsisten per baris.
Filter20%Minimal dua syarat sekaligus, hasil sudah disalin ke sheet terpisah dengan benar.
Data Validation15%Dropdown berfungsi dan menolak data di luar daftar, disertai pesan kesalahan.
Color Scale/Icon Set10%Diterapkan pada kolom numerik yang relevan dan mudah dibaca.
Grafik20%Minimal dua jenis grafik yang sesuai jenis data, berjudul dan berlabel sumbu.
Kerapian dan file10%Format rapi, nama file sesuai aturan, terunggah ke OneDrive.
Pertemuan berikutnya

Pertemuan 12: PivotTable, PivotChart, dan dashboard sederhana

Buka Pertemuan 12