Merata-ratakan Berdasarkan Kriteria dengan AVERAGEIF

Anda telah menjumlahkan dengan SUMIF dan menghitung dengan COUNTIF. Anggota ketiga dalam kelompok ini adalah AVERAGEIF, yang mencari rata-rata hanya dari nilai yang memenuhi suatu kondisi.

Alih-alih menghitung rata-rata semua penjualan, Anda dapat menghitung rata-rata hanya untuk penjualan wilayah Timur atau hanya untuk skor di atas 50. Fungsi ini menggabungkan penyaringan dan perhitungan rata-rata dalam satu langkah yang rapi.

AVERAGEIF menggunakan susunan tiga bagian yang sama seperti SUMIF:

• rentang sel yang diuji
• kriteria kondisi
• rentang rata-rata sel yang dirata-ratakan

Fungsi ini menelusuri rentang, menemukan kecocokan, lalu menghitung rata-rata sel yang bersesuaian dalam rentang rata-rata.

Dengan wilayah di A2:A10 dan jumlah di B2:B10, rata-rata penjualan untuk wilayah Timur menguji kolom A untuk mencari Timur, lalu menghitung rata-rata nilai yang cocok di kolom B.

Di balik layar, prosesnya adalah SUMIF dibagi COUNTIF, tetapi AVERAGEIF merangkum proses tersebut dalam satu rumus yang mudah dibaca.

Jika kolom yang diuji juga merupakan kolom yang ingin Anda rata-ratakan, Anda dapat menghilangkan argumen ketiga. Untuk menghitung rata-rata hanya dari skor di B2:B10 yang lebih besar dari 70:

Di sini kolom B diuji sekaligus dirata-ratakan. Jika rentang rata-rata dihilangkan, lembar bentang menggunakan rentang yang sama untuk keduanya.

Seperti pada fungsi IF lainnya, simpan kondisi di dalam sel agar lebih fleksibel. Jika D1 berisi nama wilayah, arahkan kriteria ke D1.

Sekarang satu sel mengendalikan rata-rata. Mengubah D1 dari Timur menjadi Barat akan langsung menghitung ulang hasilnya, sehingga cocok untuk tabel ringkasan atau pemilih dasbor.

AVERAGEIF menerima operator di dalam tanda kutip, sama seperti fungsi-fungsi terkaitnya. Untuk menghitung rata-rata hanya dari penjualan yang setidaknya bernilai 100:

• ">=100" 100 atau lebih
• "<50" di bawah 50
• "<>0" tidak termasuk nol

Pilihan terakhir sering digunakan karena menghitung rata-rata sambil mengabaikan entri nol yang jika tidak diabaikan akan menurunkan nilai rata-rata.

Untuk menggunakan ambang dari sebuah sel, gabungkan operator dengan sel tersebut menggunakan tanda ampersand. Jika D1 berisi nilai ambang, hitung rata-rata semua nilai yang lebih besar darinya.

& membentuk teks kondisi dari ">" dan nilai di D1. Ini adalah trik penggabungan yang sama seperti yang Anda gunakan pada SUMIF dan COUNTIF.

AVERAGEIF memiliki satu jebakan yang tidak dimiliki fungsi lainnya. Jika tidak ada baris yang cocok, tidak ada nilai untuk dibagi sehingga muncul kesalahan #DIV/0!.

Misalnya, menghitung rata-rata wilayah yang tidak ada dalam data akan menghasilkan kesalahan ini. Lembar bentang sedang memberi tahu Anda bahwa penyaringan tidak menemukan sel apa pun, bukan bahwa rumus Anda rusak.

Masukkan rumus ke dalam IFERROR untuk menampilkan pesan yang lebih ramah, bukan #DIV/0!, saat tidak ada kecocokan.

Sekarang wilayah yang tidak memiliki data menampilkan teks Tidak ada data, bukan kesalahan yang mengkhawatirkan. Dengan begitu, laporan tetap terlihat rapi meskipun beberapa kategori tidak tersedia.

AVERAGEIF hanya menghitung rata-rata sel yang berisi angka di antara hasil yang cocok. Sel yang benar-benar kosong dalam rentang rata-rata diabaikan, sehingga tidak dihitung sebagai nol.

Hal ini penting: nilai yang hilang tidak akan menarik rata-rata menuju nol seperti halnya nilai 0. Jika Anda juga ingin mengecualikan nol, tambahkan kriteria "<>0" pada kolom nilai.

Buat ringkasan yang rapi: masukkan setiap kategori ke sel D2, D3, dan D4, lalu tulis satu AVERAGEIF yang merujuk ke sel label dan salin ke bawah.

Sekarang setiap baris menunjukkan nilai rata-rata untuk kategorinya sendiri, berdampingan dengan total SUMIF dan jumlah COUNTIF dari pelajaran sebelumnya. Bersama-sama, semuanya membentuk laporan ringkas yang sepenuhnya digerakkan oleh rumus.

Sekarang Anda dapat menghitung rata-rata secara kondisional dengan AVERAGEIF. Poin-poin penting:

• Urutannya adalah rentang, kriteria, rentang rata-rata; hilangkan argumen terakhir untuk menghitung rata-rata dari rentang yang diuji itu sendiri.
• Fungsi ini mendukung teks, angka, dan operator seperti ">=100".
• Tidak adanya kecocokan menghasilkan kesalahan #DIV/0!, jadi tangani dengan IFERROR.
• Sel kosong dilewati, bukan dianggap sebagai nol.

Selanjutnya Anda akan mencocokkan sebagian teks menggunakan karakter pengganti.