Pertemuan 3 – Formula Dasar, Fungsi Statistik, dan Referensi Relatif vs Absolut

Capaian Pembelajaran

Setelah pertemuan ini mahasiswa mampu menyusun formula aritmatika dasar, memahami urutan kalkulasi operator, meringkas data dengan fungsi statistik, memakai SUBTOTAL untuk menghitung data terfilter, serta menggunakan referensi sel relatif, absolut, dan campuran dengan tepat agar formula mudah disalin tanpa salah.

Anatomi Formula

Formula di Excel selalu diawali dengan tanda sama dengan (=). Contoh =A1+B1 menjumlahkan isi cell A1 dan B1. Keunggulan formula dibanding nilai ketik manual adalah hasilnya diperbarui secara otomatis apabila data sumber berubah.

Operator Aritmatika dan Urutan Kalkulasi

• ( ) kurung: bagian di dalam dihitung lebih dahulu.
• ^ pangkat.
• * dan / perkalian dan pembagian.
• + dan – penjumlahan dan pengurangan.
• & penggabungan teks; dan =, <, > untuk perbandingan logika.

Urutan kalkulasi: kurung terlebih dahulu, lalu pangkat, kemudian perkalian dan pembagian, dan terakhir penjumlahan dan pengurangan. Contoh =2+3*4 menghasilkan 14, bukan 20, karena perkalian dihitung lebih dahulu. Gunakan kurung untuk mengubah urutan, misal =(2+3)*4 yang menghasilkan 20. Kesalahan urutan operator merupakan sumber umum keliru hasil, oleh karena itu biasakan menulis kurung secara eksplisit agar formula terbaca jelas.

Fungsi Statistik Dasar

• =SUM(range): menjumlahkan seluruh nilai numerik pada range, misal =SUM(B2:B11).
• =AVERAGE(range): rata-rata aritmatika; mengabaikan sel kosong dan teks.
• =MAX(range): nilai terbesar pada range.
• =MIN(range): nilai terkecil pada range.
• =COUNT(range): menghitung banyaknya sel yang berisi angka saja.
• =COUNTA(range): menghitung banyaknya sel yang tidak kosong, baik angka maupun teks.
• =COUNTBLANK(range): menghitung banyaknya sel kosong.

Perhatikan beda COUNT dan COUNTA. Bila ingin menghitung nomor pendaftaran yang campuran teks dan angka, COUNT akan mengabaikan yang berisi teks sementara COUNTA menghitung semuanya. Sel kosong, sel teks, serta nilai kesalahan diabaikan oleh SUM, AVERAGE, MAX, dan MIN. Penggunaan fungsi yang tepat pada data nyata menyelamatkan dari angka yang menyesatkan.

Fungsi SUBTOTAL

=SUBTOTAL(no_fungsi; range) melakukan perhitungan sesuai nomor fungsi dan peka terhadap penyaringan. Sering dipakai pada baris total yang berdampingan dengan filter.

• Nomor 1-11 menghitung semua baris, termasuk yang tersembunyi oleh filter.
• Nomor 101-111 menghitung hanya baris yang terlihat: 109 = SUM, 101 = AVERAGE, 103 = COUNTA, 104 = MAX, 105 = MIN.
• Contoh =SUBTOTAL(109;B2:B11) menjumlahkan hanya baris yang tidak tersaring sehingga total laporan selalu sesuai tampilan.
• SUBTOTAL mengabaikan baris tersembunyi dari filter dan tidak menghitung SUBTOTAL lain di dalamnya, mencegah perhitungan ganda.

Referensi Relatif, Absolut, dan Campuran

Referensi sel menentukan perilaku formula ketika disalin ke sel lain.

• Relatif (A1): alamat ikut bergeser sesuai arah penyalinan. Rumus =B2+C2 pada baris 3 otomatis menjadi =B3+C3.
• Absolut ($A$1): kolom dan baris terkunci sehingga tidak berubah saat disalin. Cocok untuk konstanta seperti tarif pajak atau kurs yang diletakkan pada satu cell tetap.
• Campuran ($A1 atau A$1): hanya sebagian yang terkunci. $A1 mengunci kolom A sedangkan A$1 mengunci baris 1, berguna untuk tabel perhitungan dua dimensi.

Tekan F4 saat kursor berada pada referensi untuk bersiklus cepat antara relatif, absolut, dan campuran. Referensi absolut sangat penting saat formula yang sama disalin ke banyak baris namun harus tetap merujuk ke satu cell konstanta. Tanpa tanda dolar, penyalinan akan menggeser acuan dan menghasilkan nilai yang salah.

Contoh Praktik

Pada tabel penjualan berisi kolom Harga Satuan dan Qty, hitung Total dengan =Harga*Qty lalu salin ke semua baris. Ringkas dengan =SUM untuk jumlah keseluruhan, =AVERAGE untuk rata-rata harga, serta MAX dan MIN untuk nilai ekstrem. Bila pajak 10% pada satu cell, misal $E$1, gunakan =Total*$E$1 agar formula tetap merujuk ke cell pajak saat disalin ke seluruh baris.

Metode dan Aktivitas (120 Menit)

1. Penjelasan operator dan urutan kalkulasi dengan contoh nyata (25 menit).
2. Demonstrasi fungsi statistik dan SUBTOTAL (35 menit).
3. Praktik referensi relatif, absolut, dan campuran (40 menit).
4. Latihan mandiri dan pembahasan bersama (20 menit).

Latihan Praktik

1. Buat 12 baris data nilai mahasiswa berisi Nama, UTS, UAS, dan Tugas.
2. Hitung Nilai Akhir = UTS*0,3 + UAS*0,5 + Tugas*0,2 dengan referensi yang tepat.
3. Hitung total, rata-rata, nilai tertinggi, dan terendah pada tiap kolom.
4. Gunakan COUNT untuk jumlah data UTS dan COUNTA untuk jumlah nama.
5. Buat baris total dengan SUBTOTAL 109 lalu amati perubahannya saat data difilter.

Ringkasan

Memahami urutan operator, fungsi statistik, dan jenis referensi adalah kunci menyusun spreadsheet yang benar dan dapat disalin ke mana saja. Referensi absolut memungkinkan pemakaian konstanta bersama. Latihan berulang membuat penyusunan formula menjadi kebiasaan sebelum mempelajari fungsi teks dan logika.

Contoh Kasus Langkah Demi Langkah

Sebuah toko memiliki 10 data transaksi harian pada kolom A berisi Nomor, kolom B berisi Harga, dan kolom C berisi Qty. Anda diminta membuat total penjualan harian dan rata-rata harga. Pertama, tentukan sel untuk total dengan rumus =SUM(B2:C11) bila ingin menjumlah langsung seluruh area harga, atau buat kolom subtotal per baris dengan =B2*C2 lalu jumlahkan hasilnya dengan =SUM(D2:D11). Kedua, rata-rata harga satuan dihitung dengan =AVERAGE(B2:B11). Ketiga, cari transaksi terbesar dan terkecil memakai =MAX(C2:C11) dan =MIN(C2:C11).

Selanjutnya, tarif pajak 5% pada sel $E$1. Total yang harus dibayarkan = subtotal ditambah pajak: =D2*(1+$E$1). Karena $E$1 absolut, rumus dapat disalin ke semua baris tanpa bergeser. Untuk memastikan pemahaman, ubah nilai Qty pada satu baris lalu amati perubahan otomatis pada subtotal dan grand total. Prinsip ini menunjukkan keunggulan formula dibanding mengetik hasil secara manual.

Soal Diskusi

• Mengapa =2+3*4 menghasilkan 14? Apa yang diubah bila menulis =(2+3)*4?
• Kapan sebuah konstanta sebaiknya ditandai referensi absolut daripada ditulis langsung di dalam rumus?
• Apa beda hasil COUNT dengan COUNTA pada kolom yang bercampur angka, teks, dan kosong?
• Mengapa baris total lebih aman menggunakan SUBTOTAL 109 daripada SUM ketika data difilter?