Pertemuan 4 – Fungsi String dan Fungsi COUNTIF/SUMIF

Capaian Pembelajaran

Setelah pertemuan ini mahasiswa mampu memanipulasi dan membersihkan teks menggunakan fungsi string, menstandardisasi format teks agar konsisten, serta menghitung dan menjumlahkan data berdasarkan satu kriteria memakai COUNTIF dan SUMIF.

Fungsi String Dasar

Fungsi teks (string) sangat berguna untuk membersihkan data hasil impor yang sering tidak konsisten besar hurufnya atau mengandung spasi berlebih.

• LEFT: =LEFT(teks;n) mengambil n karakter dari kiri. =LEFT(“ADI0192”;3) menghasilkan ADI.
• RIGHT: =RIGHT(teks;n) mengambil n karakter dari kanan. =RIGHT(“ADI0192”;4) menghasilkan 0192.
• MID: =MID(teks;mulai;jumlah) mengambil bagian karakter dari posisi tertentu. =MID(“8901234567”;3;4) menghasilkan 0123.
• LEN: =LEN(teks) menghitung panjang teks termasuk spasi. =LEN(“Excel”) menghasilkan 5.
• UPPER: =UPPER(“joko”) menjadi JOKO.
• LOWER: =LOWER(“JOKO”) menjadi joko.
• PROPER: =PROPER(“joko susilo”) menjadi Joko Susilo, kapital di awal setiap kata.
• TRIM: =TRIM(” Adi Budi “) menghapus spasi berlebih menjadi “Adi Budi”.

Fungsi-fungsi ini kerap digabung dengan operator &. Contoh menyusun nama utuh =UPPER(A2)&” “&B2. Fungsi MID umum dipakai mengekstrak kode dari data gabungan seperti nomor akun atau awalan pada NIK. Kombinasi TRIM dan UPPER sangat disarankan sebagai langkah pembersihan data sebelum dilakukan pencocokan.

Fungsi COUNTIF

=COUNTIF(range;kriteria) menghitung jumlah sel dalam range yang memenuhi satu kriteria. Cocok untuk ringkasan frekuensi.

• =COUNTIF(A2:A21;”Jakarta”) menghitung banyaknya sel bertuliskan Jakarta.
• =COUNTIF(A2:A21;”>=60″) menghitung nilai yang 60 atau lebih.
• Wildcard mendukung pola: =”J*” untuk diawali J, =”*i” untuk diakhiri i, dan “?np” untuk satu karakter apa pun di posisi tertentu.
• Kriteria dapat merujuk ke sel: =COUNTIF(A2:A21;B1) membandingkan dengan isi B1 sehingga mudah diubah.
• Contoh penggunaan: menghitung jumlah siswa lulus, jumlah stok di bawah ambang, atau jumlah transaksi per kota.

Fungsi SUMIF

=SUMIF(range;kriteria;sum_range) menjumlahkan sel pada sum_range bila sel sepadan di range memenuhi kriteria.

• =SUMIF(A2:A21;”Bandung”;C2:C21) menjumlahkan kolom C hanya untuk baris yang kotanya Bandung.
• Bila range dan sum_range sama, parameter sum_range dapat dihilangkan: =SUMIF(C2:C21;”>0″).
• Kriteria dapat berupa teks, angka, ekspresi perbandingan, atau wildcard, dan dapat merujuk ke sel seperti =SUMIF(B2:B21;D1;C2:C21).
• Contoh: total penjualan per produk, total gaji per divisi, atau total stok per gudang.

Contoh Penggabungan

Data berisi kolom ID (misal BDG001), Nama, dan Total. Untuk mengetahui total nilai yang diawali kode kota BDG gunakan =SUMIF(ID;”BDG*”;Total). Untuk menghitung berapa transaksi yang nilainya di atas rata-rata gabungkan =COUNTIF(Total;”>”&AVERAGE(Total)). Teknik menyusun kriteria dinamis seperti ini membuat formula tetap fleksibel terhadap perubahan data.

Metode dan Aktivitas (120 Menit)

1. Penjelasan fungsi string serta contoh ekstraksi dan pembersihan (35 menit).
2. Demonstrasi COUNTIF dan SUMIF dengan berbagai jenis kriteria (35 menit).
3. Praktik pembersihan data dan ringkasan kondisional (40 menit).
4. Diskusi perbandingan hasil dan kendala (10 menit).

Latihan Praktik

1. Dari kolom ID seperti BDG001, ekstrak 3 huruf pertama dengan LEFT dan 3 angka terakhir dengan RIGHT.
2. Standarkan kolom Nama dengan PROPER dan buang spasi berlebih dengan TRIM.
3. Ubah seluruh kolom email menjadi huruf kecil menggunakan LOWER.
4. Hitung jumlah transaksi yang nilainya di atas 500.000 dengan COUNTIF.
5. Jumlahkan total penjualan per kota dengan SUMIF untuk tiga kota berbeda.

Ringkasan

Fungsi string membersihkan dan menstandarkan teks, sementara COUNTIF dan SUMIF meringkas data berdasarkan satu kriteria. Keterampilan menggabungkan fungsi teks dan kondisional menjadi modal penting sebelum masuk ke logika IF pada pertemuan berikutnya.

Contoh Kasus Langkah Demi Langkah

Sebuah perusahaan menerima data pelanggan dari sistem lama yang kacau: nama ditulis campuran huruf besar dan kecil serta disertai spasi berlebih, dan kode pelanggan berbentuk gabungan seperti BDG0452JR. Untuk membersihkan, buat kolom baru Nama Rapi dengan =TRIM(PROPER(B2)), email dijadikan huruf kecil dengan =LOWER(C2), lalu ekstrak tiga huruf kota dari kode dengan =LEFT(A2;3) dan tiga digit urutan dengan =MID(A2;4;3).

Setelah data bersih, ringkas berdasarkan kriteria. Untuk menghitung berapa pelanggan yang berasal dari kota BDG, gunakan =COUNTIF(A2:A101;”BDG*”). Untuk menjumlahkan total transaksi kota Bandung pada kolom Total, pakai =SUMIF(A2:A101;”BDG*”;D2:D101). Untuk menghitung berapa transaksi yang nilainya melebihi 500.000, gunakan =COUNTIF(D2:D101;”>500000″). Susun ringkasan per kota dengan menyalin SUMIF dan mengganti kriteria kota satu per satu, lalu bandingkan total seluruh kota dengan SUM untuk memastikan jumlahnya konsisten.

Soal Diskusi

• Mengapa pembersihan data menggunakan TRIM dan UPPER penting sebelum dilakukan pencocokan antar tabel?
• Bagaimana cara menghitung jumlah sel yang mengandung kata “BPJS” di tengah teks?
• Apa perbedaan praktis antara kriteria “=”&B1 dan kriteria B1 pada COUNTIF?
• Kapan sebuah ringkasan lebih tepat memakai SUMIF dibanding memfilter manual lalu menjumlahkan?

Tugas Mandiri

Siapkan satu kumpulan data mentah minimal 20 baris yang mengandung kolom kode gabungan, nama dengan spasi berlebih, dan total nilai. Bersihkan data menggunakan TRIM, PROPER, dan LOWER, ekstrak kode kota dan nomor urut dengan LEFT dan MID, lalu susun ringkasan per kota memakai SUMIF dan jumlah entri dengan COUNTIF. Simpan hasil analisis dan siap dipresentasikan pada pertemuan berikutnya untuk dibahas bersama.