Cara Menggunakan Fungsi AVERAGEIF di Excel

Di Excel, ada cara mudah untuk mengidentifikasi dan menemukan rata-rata angka yang memenuhi kriteria tertentu. Dengan cara menggunakan fungsi AVERAGEIF di excel, Excel mencari dalam rentang yang ditentukan untuk satu kondisi dan kemudian menemukan rata-rata aritmatika sel yang memenuhi kondisi tersebut.

Untuk lebih banyak cara menghitung rata-rata di Excel, lihat sumber daya kami tentang menghitung rata-rata dan rata-rata tertimbang di Excel.

Sintaksis

Fungsi AVERAGEIF memiliki dua argumen wajib dan satu argumen opsional.

Sintaksnya adalah:

=AVERAGEIF(range,criteria,[average_range])

- Jangkauan: lokasi di mana kita dapat mengharapkan untuk menemukan sel yang memenuhi kriteria.

- Kriteria: nilai atau ekspresi yang harus dicari Excel dalam rentang.

- Rentang_rata-rata: argumen opsional. Ini adalah rentang sel yang sebenarnya di mana nilai-nilai yang akan dirata-ratakan berada. Jika rata-rata_range dihilangkan, jangkauan digunakan.

Kriteria teks (semua argumen)

Misalnya, dari daftar harga buah di bawah ini, kita dapat meminta Excel untuk mengekstrak hanya sel yang bertuliskan “apel” di kolom A, dan menemukan harga rata-ratanya dari kolom B. Dalam kasus ini, ketiga argumen digunakan, karena jangkauan tidak berisi angka yang digunakan untuk menghitung harga rata-rata.

Perhatikan bahwa nilai teks “apel” ditempatkan dalam tanda kutip ganda.

Dalam kasus di mana jangkauan dan rata_rentang ukuran atau bentuknya berbeda, Excel menggunakan kombinasi sel pertama di rata_rentangditambah ukuran dan bentuk jangkauan, untuk menentukan rentang sebenarnya dari sel yang akan dirata-ratakan.

Meskipun average_range dinyatakan sebagai B2 hingga B14, hanya sel di B2 hingga B8 yang benar-benar dirata-ratakan, karena itu adalah sel yang sesuai dengan rentang A2 hingga A8.

Kriteria logis

Kriteria dalam fungsi AVERAGEIF juga dapat berupa pernyataan logika, menggunakan operator matematika logika standar (=, >, <). Namun, seperti yang ditunjukkan dengan contoh teks di atas, kriteria argumen default ke “sama dengan” sehingga tidak perlu memasukkan tanda sama dengan (=) saat menyatakan kriteria “sama dengan”.

Kombinasi berikut dari operator ini juga digunakan untuk perbandingan logis spesifik lainnya.

Operator	Arti
>=	Lebih dari atau sama dengan
<=	Kurang dari atau sama dengan
<>	Tidak sama dengan

Contoh perbandingan kurang dari ditunjukkan di bawah ini. Perhatikan bahwa argumen ketiga (opsional) dihilangkan. Oleh karena itu sel-sel di jangkauan digunakan untuk menghitung rata-rata.

=AVERAGEIF(B2:H11, “<60”)

Rumus di atas hanya memperhitungkan dan menemukan rata-rata dari empat nilai yang disorot — 54, 49, 56, dan 0. Rumus ini mengabaikan nilai “Sick” di sel B6 karena fungsi AVERAGEIF tidak menetapkan nilai numerik ke teks.

Kriteria referensi sel

Kita juga dapat menggunakan operator logika dalam kombinasi dengan referensi sel untuk menetapkan kriteria fungsi AVERAGEIF. Contoh berikut menemukan rata-rata semua sel dalam rentang B2 hingga H11, yang lebih kecil dari nilai di sel J1.

=AVERAGEIF(B2:H11, “<”&J1)

Saat menggunakan operator logika dengan kriteria referensi sel, operator logika itu sendiri diapit oleh tanda kutip ganda. Ini diikuti oleh ampersand (&) untuk menggabungkan, atau menggabungkan, operator ke referensi sel yang relevan.

Seperti yang ditunjukkan di atas, teks “Sakit” di sel B6 dilewati ketika tiba di rata-rata, jadi satu-satunya sel yang digunakan dalam perhitungan adalah C3, E3, F7, dan D11.

Sel tidak sama dengan

Ke mengecualikan sel yang berisi nilai tertentu, <> (tidak sama dengan) operator harus digunakan. Sebagai contoh:

=AVERAGEIF(B2:H2,"<>0")

Rumus di atas akan menemukan rata-rata nilai dalam rentang B2 hingga H2 yang tidak sama dengan nol, yaitu, semua sel dalam rentang kecuali D2 di bawah.

Sebagai perbandingan, perhatikan hasil pada gambar di bawah ini, di mana sel J2 menggunakan fungsi AVERAGE sebagai gantinya.

Dalam contoh di atas, fungsi AVERAGE digunakan di sel J2, yang berarti semua nilai dalam rentang B2 hingga H2 digunakan untuk menghitung rentang. Oleh karena itu, nilai nol di sel D2 mempengaruhi hasil rata-rata.

Jika tidak kosong

Fungsi AVERAGE mengabaikan sel kosong, sehingga fungsi AVERAGEIF tidak diperlukan jika kita ingin mendapatkan rata-rata sel yang tidak kosong. Untuk membuktikan hal ini, kami membuat rumus AVERAGEIF yang kompleks dengan menggunakan operator logika <> bersama dengan tanda kutip ganda kosong, untuk mewakili kriteria tidak sama dengan sel kosong dalam contoh di bawah ini.

Rata-rata yang dihitung oleh fungsi AVERAGEIF di sel H2 identik dengan yang dihitung oleh fungsi AVERAGEIF di sel I2. Masuk akal untuk menggunakan fungsi AVERAGE untuk sel yang tidak kosong — ini adalah solusi paling sederhana dan paling tidak rumit.

Beberapa kriteria

RATA-RATA dengan logika ATAU

Mungkin ada saatnya kita ingin menemukan rata-rata sel dalam rentang yang memenuhi siapa pun dari beberapa kriteria (seperti “apel” atau “jeruk”). Skenario ini cukup mudah diselesaikan dengan memasukkan logika OR.

Kita dapat membuat rumus AVERAGEIF yang menemukan harga rata-rata “apel” dan rumus yang menemukan harga rata-rata “jeruk” — dan cukup meminta Excel untuk menghitung rata-rata hasilnya dengan menggabungkan dua fungsi AVERAGEIF ke dalam fungsi AVERAGE, seperti yang ditunjukkan di bawah ini.

=AVERAGE(AVERAGEIF(A2:A14,"apples",B2:B14),AVERAGEIF(A2:A14,"oranges",B2:B14))

RATA-RATA dengan logika AND

Untuk menemukan rata-rata sel yang memenuhi beberapa kriteria (seperti “lebih besar dari x tetapi kurang dari y”), kita akan menggunakan fungsi AVERAGEIFS, yang menggabungkan AVERAGEIF dengan logika AND. Ini mengidentifikasi dan menemukan rata-rata sel yang memenuhi semua dari kriteria yang telah ditentukan.

AVERAGEIF dengan kecocokan sebagian

Terkadang sel yang ingin kita ekstrak dari suatu rentang serupa dalam beberapa hal, tetapi tidak identik. Dengan kata lain, mereka hanya pertandingan parsial. Wildcard memberikan solusi hebat untuk mengidentifikasi sel-sel ini, dan AVERAGEIF bekerja dengan wildcard.

Wildcard	Arti
*	Setiap nomor atau string karakter yang tidak diketahui, atau tidak ada karakter
?	Satu karakter yang tidak diketahui
~	Mendahului tanda bintang atau tanda tanya untuk digunakan sebagai karakter literal

Kami dapat menemukan rata-rata semua teks dalam rentang A2 hingga A14 yang diakhiri dengan “es” dengan menempatkan tanda bintang sebelum “es” sebagai kriteria kami.

=AVERAGEIF(A2:A14,"*es",B2:B14)

Hasil dari kriteria di atas adalah harga rata-rata jeruk nipis, apel, mangga, dan jeruk dihitung dan dikembalikan di sel D2.

Poin yang harus diperhatikan

- Jika Anda mendapatkan #DIV/0! error, artinya Excel tidak dapat menemukan nilai dalam rentang yang memenuhi kriteria yang ditentukan.

- AVERAGEIF mengabaikan sel dalam rentang yang berisi nilai Boolean TRUE atau FALSE.