Merata-ratakan Berdasarkan Banyak Kondisi dengan AVERAGEIFS

AVERAGEIFS menghitung rata-rata nilai yang memenuhi beberapa kondisi sekaligus. Daripada menghitung rata-rata setiap penjualan, Anda dapat bertanya Berapa rata-rata penjualan wilayah Timur pada bulan Januari?

Fungsi ini melengkapi tiga serangkai: SUMIFS menjumlahkan, COUNTIFS menghitung, dan AVERAGEIFS menghitung rata-rata — semuanya menggunakan gaya beberapa kriteria yang sama. Jika Anda mengetahui salah satunya, Anda hampir mengetahui ketiganya.

AVERAGEIFS mencerminkan SUMIFS secara persis. Nilai yang dirata-ratakan ditulis terlebih dahulu, kemudian pasangan kondisinya.

• average_range — angka yang dirata-ratakan
• criteria_range1, criteria1
• criteria_range2, criteria2

Di balik layar, fungsi ini menjumlahkan nilai yang cocok lalu membaginya dengan jumlah nilai yang cocok — pada dasarnya SUMIFS dibagi COUNTIFS, dalam satu fungsi yang rapi.

Dengan kolom A sebagai Wilayah, kolom B sebagai Bulan, dan kolom C sebagai Jumlah, berikut rata-rata penjualan wilayah Timur pada bulan Januari.

Nilai di C:C adalah nilai yang dirata-ratakan. Kedua pasangan kondisi menyaring baris hingga tersisa wilayah Timur dan bulan Januari sebelum rata-rata dihitung.

Anda dapat memfilter nilai yang dirata-ratakan berdasarkan ukurannya sendiri. Misalkan Anda hanya ingin menghitung rata-rata penjualan besar di wilayah Timur — yaitu penjualan di atas 100.

Di sini kolom C digunakan baik sebagai rentang rata-rata maupun sebagai rentang kriteria. AVERAGEIFS dapat menggunakan kembali kolom yang sama untuk kedua peran tersebut.

Untuk membuat laporan yang dapat dikendalikan oleh pengguna, referensikan sel alih-alih mengetikkan nilai. Masukkan wilayah di F1 dan jumlah minimum di F2.

Untuk perbandingan, gabungkan operator dengan sel: ">&F2 berarti lebih besar dari apa pun yang ada di F2. Pencocokan teks biasa seperti wilayah tidak memerlukan operator; cukup gunakan referensi sel.

Hal yang paling sering menjebak dalam AVERAGEIFS: jika tidak ada baris yang cocok dengan semua kondisi Anda, tidak ada nilai yang dapat dirata-ratakan, sehingga fungsi ini mengembalikan kesalahan #DIV/0!.

Ini berbeda dari SUMIFS (yang mengembalikan 0) dan COUNTIFS (yang juga mengembalikan 0). Rata-rata dari nol item tidak terdefinisi, jadi Excel menampilkan kesalahan alih-alih menebak.

Bungkus rumus dengan IFERROR untuk menampilkan nilai yang mudah dipahami saat tidak ada yang cocok. Alih-alih #DIV/0! yang mengganggu, pengguna akan melihat tanda pisah atau pesan.

Dengan demikian, dasbor tetap terlihat rapi meskipun kombinasi filter tidak memiliki data. Saat menghitung rata-rata data yang difilter, selalu pertimbangkan kondisi kosong.

Hal penting yang perlu diperhatikan: AVERAGEIFS mengabaikan sel kosong dalam rentang rata-rata — sel tersebut tidak dihitung dalam total maupun pembagi. Namun, sel yang berisi 0 adalah angka nyata dan tetap disertakan.

Jika nol digunakan sebagai pengganti data yang hilang, nilai tersebut akan menurunkan rata-rata Anda. Pertimbangkan untuk menambahkan kriteria seperti C:C, "<>0" guna mengecualikannya jika sesuai.

Tambahkan kondisi sebanyak yang Anda perlukan. Dengan kolom D sebagai Penjual, carilah rata-rata penjualan yang berasal dari wilayah Timur, terjadi pada bulan Januari, dan dilakukan oleh Maria.

Setiap pasangan tambahan memperketat filter. Karena AVERAGEIFS menggunakan logika AND, hanya baris yang memenuhi ketiga kondisi yang dimasukkan ke dalam perhitungan rata-rata.

Pastikan Anda tidak keliru dengan urutan kedua jenis argumen ini:

• AVERAGEIF: range, criteria, [average_range] — rentang pengujian ditulis terlebih dahulu, sedangkan average_range bersifat opsional dan ditulis terakhir.
• AVERAGEIFS: average_range, range1, crit1, ... — average_range selalu ditulis terlebih dahulu.

Seperti fungsi lainnya, memilih versi jamak ...IFS sebagai pola utama memberi Anda pola yang konsisten untuk SUM, COUNT, dan AVERAGE.

AVERAGEIFS mendukung banyak ringkasan yang membandingkan beberapa hal secara berdampingan. Cantumkan nama wilayah di kolom F, lalu hitung rata-rata penjualan setiap wilayah dengan satu rumus yang dapat disalin ke bawah, sambil mengunci kolom kriteria.

Dengan $C:$C sebagai rentang rata-rata, $A:$A sebagai rentang kriteria, dan $F2 sebagai sel wilayah relatif, menyalin rumus ke bawah akan langsung memberi Anda satu rata-rata untuk setiap wilayah.

Ingat kembali hal yang membuat AVERAGEIFS berperilaku berbeda dari fungsi sejenisnya.

Sekarang Anda dapat menghitung rata-rata nilai berdasarkan beberapa kondisi:

• AVERAGEIFS(average_range, range1, crit1, ...) — average_range ditulis terlebih dahulu.
• Tidak ada kecocokan menghasilkan #DIV/0! — tangani dengan IFERROR.
• Sel kosong dilewati, tetapi nol tetap disertakan; kecualikan dengan "<>0" jika diperlukan.
• Gunakan ">"&F1 untuk batas angka yang dinamis.

Berikutnya: menangani rentang tanggal dalam fungsi-fungsi kriteria ini.