Image by mohamed Hassan from Pixabay

Pada kesempatan ini, saya akan menguraikan tips untuk menyelesaikan suatu permasalahan yang sederhana yaitu menghitung nilai rata-rata siswa berdasarkan jenis kelamin dengan menggunakan tiga cara yang berbeda (cara manual dan cara otomatis). Anda dapat memilih cara perhitungan manakah yang paling tepat digunakan untuk menentukan nilai rata-rata yang diperoleh dari nilai siswa laki-laki jika dibandingkan dengan nilai siswa perempuan.

Gambar di bawah ini menunjukkan data nilai siswa yang diurutkan secara alfabet menurut kolom Nama Siswa. Adapun nilai yang akan kita hasilkan adalah nilai rata-rata keseluruhan siswa dan nilai rata-rata menurut jenis kelamin masing-masing siswa tersebut.


Cara Pertama: Menggunakan Rumus/Formula Excel

Untuk menghitung rata-rata berdasarkan jenis kelamin, Anda dapat menggunakan fungsi AVERAGEIF seperti rumus berikut:
=AVERAGEIF(C2:C28,"Perempuan",B2:B28)

=AVERAGEIF(C2:C28,"Laki-laki",B2:B28)
Apabila Anda ingin file Excel dengan rumus diatas tetap sesuai dengan versi sebelum versi Excel 2007 keatas, maka Anda dapat menggunakan rumus berikut:
=SUMIF(C2:C28,"Perempuan",B2:B28)/COUNTIF(C2:C28,"Perempuan")

=SUMIF(C2:C28,"Laki-laki",B2:B28)/COUNTIF(C2:C28,"Laki-laki")
Rumus Excel untuk menghitung rata-rata seluruh siswa adalah:
=AVERAGE(B2:B28)

Cara Kedua: Menggunakan Menu Subtotal

Cara berikut ini merupakan langkah yang otomatis dengan cara menambahkan subtotal. Untuk menggunakan menu subtotal, data siswa harus diurutkan terlebih dahulu berdasarkan kolom Jenis Kelamin agar menghasilkan subtotal.
  1. Pilih sel mana saja pada kolom C.
  2. Klik-kanan dan pilih Sort > Sort A To Z dari menu yang ditampilkan.
  3. Tampilkan kotak dialog Subtotal dengan memilih menu Data > Outline > Subtotal
  4. Pada menu 'At each change in:', pilih 'Jenis Kelamin', lalu pada menu 'Use function:', pilih 'Average' dan terakhir beri tanda centang (klik) hanya pada 'Nilai' saja dibawah menu 'Add subtotal to:' lalu klik 'OK'.

Hasil subtotal ditunjukkan seperti pada gambar di bawah ini. Anda dapat menyembunyikan detail data dengan meng-klik tombol minus (-) yang berada di samping kiri (tepat di baris subtotal), sehingga hanya menampilkan hasil perhitungan saja. Untuk menampilkan kembali detail data tersebut, maka Anda dapat meng-klik tombol plus (+).



Rumus Excel yang ditambahkan menggunakan fungsi Subtotal tersebut dimulai dengan angka 1 yang artinya adalah fungsi AVERAGE seperti rumus pada sel B17:
=SUBTOTAL(1,B2:B16)

Meskipun ketiga rumus subtotal yang dihasilkan tersebut terlihat mirip, namun sel B31 (Grand Average) hanya menghitung dua rumus subtotal yang dihasilkan (pada sel B17 dan B30) dengan mengabaikan sel yang memiliki rumus subtotal lainnya.

Cara Ketiga: Menggunakan Menu PivotTable

Microsoft Excel memiliki salah satu kelebihan yang dapat dimanfaatkan yaitu menu PivotTable, untuk lebih memudahkan Anda mendapatkan nilai rata-rata tanpa harus membuat rumus Excel terlebih dahulu serta tanpa harus mengurutkan data berdasarkan kolom 'Jenis Kelamin'. Berikut ini merupakan langkah-langkah yang dapat Anda ikuti:
  1. Pilih sel mana saja di sekitar data, kemudian pilih menu Insert > Tables > PivotTable untuk menampilkan kotak dialog 'Create PivotTable'.
  2. Pada menu 'Choose where you want the PivotTable report to be placed' pilih 'Existing Worksheet', kemudian klik pada sel yang menurut Anda paling tepat. misalnya pada sel E1. Klik 'OK'
  3. Pada menu 'PivotTable Field List' yang berada di samping kanan, beri tanda centang pada 'Nilai' dan 'Jenis Kelamin', lalu dibawah menu 'Values' klik tanda panah kecil setelah tulisan 'Sum of Nilai' dan pilih 'Value Field Settings'.
  4. Pada menu 'Summarize value field by', pilih 'Average' lalu klik 'OK'.



Catatan:

Fitur PivotTable tidak dapat meng-update secara otomatis apabila data telah diubah, namun Anda tetap dapat mengupdate hasil nilai rata-rata dengan cara meng-klik-kanan sel mana saja di dalam area PivotTable lalu pilih 'Refresh'.