Fungsi AVERAGEIF menghitung rata-rata dengan kriteria.
Bila kriteria yang dibutuhkan lebih dari satu, maka rumus AVERAGEIFS Excel adalah alternatif yang lebih cocok.
File berikut memuat data dan rumus dari mana screenshot-screenshot di artikel ini berasal.
File: fungsi-averageifs-excel.xlsx.
Apakah Fungsi AVERAGEIFS pada Excel Itu?
Fungsi AVERAGEIFS pada Excel adalah fungsi untuk menghitung rata-rata di Excel dengan menggunakan satu atau lebih kriteria.
Perbedaan antara fungsi AVERAGEIF dan AVERAGEIFS adalah kemungkinan jumlah kriteria yang bisa digunakan.
Fungsi AVERAGEIF hanya menampung satu kriteria, sementara fungsi AVERAGEIFS memungkinkan hingga 127 kriteria dengan kriteria pertama adalah wajib.
Sintaks
AVERAGEIFS(average_range; criteria_range1; criteria1; [criteria_range2; criteria2]; …)
- average_range (wajib) adalah range yang berisi satu atau lebih angka untuk dihitung rata-ratanya. average_range dapat berupa nama, array, atau referensi cell.
- criteria_range1 (wajib) adalah range yang berisi nilai-nilai yang akan dievaluasi.
- criteria1 (wajib) adalah kriteria yang digunakan untuk mengevaluasi nilai-nilai di criteria_range1.
- criteria_range2 dan criteria2 sampai criteria_range127 dan criteria127 (opsional) adalah range dan kriteria tambahan.
Poin Penting Saat Menggunakan Rumus AVERAGEIFS Excel
- Jika average_range berisi nilai teks atau tidak berisi nilai sama sekali, fungsi AVERAGEIFS akan mengembalikan pesan error #DIV/0!.
- Cell kosong di criteria_range akan dianggap sebagai cell bernilai nol.
- Cell dengan nilai logika TRUE dianggap bernilai (satu), sementara cell dengan nilai logika FALSE dianggap bernilai 0 (nol).
- Cell-cell dari average_range yang digunakan untuk menghitung rata-rata adalah cell-cell yang berkesesuaian dengan cell-cell hasil kriteria di criteria_range1, criteria_range2, dan seterusnya.
- Besaran average_range dan criteria_range1 sampai criteria_range127 harus sama.
- Cell-cell di average_range harus bertipe data angka atau dapat diterjemahkan sebagai angka, termasuk angka berformat teks. Bila tidak, fungsi AVERAGEIFS akan menampilkan #DIV/0!.
- Bila tidak ada satu pun cell yang sesuai kriteria, maka AVERAGEIFS akan menampilkan #DIV/0!.
Cara Menggunakan Rumus AVERAGEIFS Excel
Tidak ada perbedaan mendasar dari cara menggunakan fungsi AVERAGEIFS pada Excel dengan fungsi AVERAGEIF.
Kriteria yang kita gunakan di AVERAGEIF bisa berfungsi sama baiknya bila kita pakai di AVERAGEIFS.
Pada contoh-contoh di bawah, kami menampilkan beberapa kasus yang bisa dipecahkan dengan fungsi AVERAGEIFS.
Untuk penjelasan detail mengenai penggunaan kriteria itu sendiri, kami sarankan untuk membaca penggunaan fungsi AVERAGEIF.
Rumus AVERAGEIFS Excel: Dasar
Rumus AVERAGEIFS Excel bisa menerima sampai 127 kriteria. Namun, bentuk dasarnya hanya mewajibkan satu.
=AVERAGEIFS(C2:C9; A2:A9; "Dinah")
Pada rumus di atas, C2:C9 adalah range dari mana rata-rata dihitung.
A2:A9 adalah range tempat nilai-nilai yang akan dievaluasi berada.
"Dinah" adalah kriteria yang digunakan untuk mengevaluasi nilai-nilai di A2:A9. Bila ketemu, AVERAGEIFS akan mencari cell-cell di C2:C9 yang sesuai.
Secara keseluruhan, rumus di atas mencari rata-rata penjualan di mana agen adalah Dinah.
Rumus AVERAGEIFS dengan Operator Perbandingan Excel
Fungsi AVERAGEIFS pada Excel mengenal enam operator perbandingan, yaitu:
- = (sama dengan).
- <> (tidak sama dengan).
- > (lebih besar dari).
- >= (lebih besar atau sama dengan).
- < (lebih kecil dari).
- <= (lebih kecil dari atau sama dengan).
Rumus yang kita jumpai sebelumnya mencari nilai-nilai di A2:A9 yang sama dengan Dinah.
Perbandingan sama dengan adalah perbandingan default untuk fungsi AVERAGEIFS.
Untuk menggunakan operator perbandingan selain sama dengan, tambahkan operator sebagai kriteria yang diapit dengan tanda kutip ganda.
| Rumus | Keterangan |
|---|---|
=AVERAGEIFS(C2:C9; B2:B9; 1) |
Menghitung rata-rata penjualan di mana jumlah unit adalah 1. |
=AVERAGEIFS(C2:C9; B2:B9; "<>1") |
Menghitung rata-rata penjualan dengan jumlah unit selain 1. |
=AVERAGEIFS(C2:C9; C2:C9; ">"&AVERAGE(C2:C9)) |
Menghitung rata-rata bagi penjualan yang berada di atas rata-rata secara keseluruhan. |
=AVERAGEIFS(C2:C9; B2:B9; ">=3") |
Menghitung rata-rata penjualan untuk jumlah unit penjualan lebih besar atau sama dengan 3. |
=AVERAGEIFS(C2:C9; C2:C9; "<"&AVERAGE(C2:C9)) |
Menghitung rata-rata penjualan yang berada di bawah rata-rata penjualan secara keseluruhan. |
=AVERAGEIFS(C2:C9; B2:B9; "<=3") |
Menghitung rata-rata penjualan yang memiliki total unit penjualan lebih kecil atau sama dengan 3. |
Rumus AVERAGEIFS Excel dengan Dua Kriteria
Rumus berikut memperlihatkan bagaimana menggunakan fungsi AVERAGEIFS pada Excel dengan dua kriteria.
=AVERAGEIFS(C2:C9; B2:B9; "Januari"; A2:A9; "Dinah")
B2:B29 dan "Januari" adalah range_criteria1 dan criteria1.
Kriteria berikutnya kita tambahkan di belakangnya sebagai range_criteria2 dan criteria, yaitu A2:A9 dan "Dinah".
Bila menginginkan rumus AVERAGEIFS dengan lebih banyak kriteria, tambahkan kriteria-kriteria tersebut kembali di belakangnya.
=AVERAGEIFS(C2:C9, B2:B9, "Januari", A2:A9, "Dinah"; C2:C9; "<"&AVERAGE(C2:C9))
Perlu diketahui bahwa AVERAGEIFS menggunakan ekspresi logika And untuk menggabungkan satu kriteria dengan lainnya.
Rumus AVERAGEIFS Excel dengan Kriteria Range
Range adalah satu atau lebih nilai yang berada di antara dua nilai lainnya.
Secara logika, kriteria range adalah kriteria dengan dua ekspresi logika yang digabungkan dengan ekspresi And.
Lowongan kerja untuk sales berusia 18-25 tahun berarti:
- Kriteria 1: usia sama dengan atau lebih besar dari 18 tahun.
- Kriteria 2: usia sama dengan atau lebih kecil dari 25 tahun.
Kedua kriteria tersebut digabungkan dengan ekspresi logika And. Dengan begitu, usia 25 ke atas dan usia 18 ke bawah tidak akan masuk.
Untuk menggunakan rumus AVERAGEIFS dengan kriteria range, cukup pecah range bersangkutan ke kriteria-kriteria pembentuknya.
Rumus di bawah menghitung rata-rata penjualan di atas 3,000,000,000 dan di bawah 6,000,000,000.
=AVERAGEIFS(C2:C9; C2:C9; ">3000000000"; C2:C9; "<6000000000")
Dengan konsep yang sama, rumus berikut menghitung rata-rata penjualan di antara dua tanggal.
=AVERAGEIFS(C2:C9; B2:B9; ">2024-01-14"; B2:B9; "<2024-02-15")
Perhatikan format saat menggunakan kriteria tanggal. Format yang salah bisa berakibat hasil yang tidak diharapkan.
Sangat disarankan menggunakan fungsi DATE agar terhindar dari salah format.
Rumus AVERAGEIFS Mencari Sebagian Teks
=AVERAGEIFS(C2:C9, A2:A9, "Dinah")
Rumus di atas mencari rata-rata penjualan Dinah. Pencarian menggunakan nama penuh.
Tapi, bagaimana bila kita tidak ingat nama penuhnya. Misalkan cuma ingat nah saja. Kita tidak yakin apakah Dinah, Aminah, atau Sutinah.
Di sinilah peran karakter-karakter wildcard dalam mencari sebagian teks sebagai kriteria AVERAGEIFS.
Dengan wildcard, kita gak perlu ingat keseluruhan isi teks. Cukup sebagian saja.
Fungsi AVERAGEIFS mengenal tiga macam wildcard dengan fungsinya masing-masing, yaitu:
- Tanda asterisk (*) untuk mewakili satu atau lebih karakter dalam suatu teks.
- Tanda tanya (?) untuk mewakili satu karakter dalam suatu teks.
- Tanda tilde (~) untuk mengabaikan fungsi satu tanda tanya atau tanda asterisk yang ditulis segera setelahnya.
| Rumus | Fungsi | Kandidat Cell |
|---|---|---|
=AVERAGEIFS(B2:B11; A2:A11; "*al*") |
Menghitung rata-rata penjualan dari klien-klien yang namanya memiliki al. | Semua cell. |
=AVERAGEIFS(B2:B11; A2:A11; "al*") |
Klien-klien yang namanya diawali oleh al. | B2. |
=AVERAGEIFS(B2:B11; A2:A11; "*al") |
Klien-klien yang namanya diakhiri oleh al. | B3. |
=AVERAGEIFS(B2:B11; A2:A11; "*") |
Klien-klien dengan nama apa pun juga. | Semua cell. |
=AVERAGEIFS(B2:B11; A2:A11; "~*") |
Klien-klien dengan namanya terdiri dari satu karakter asterisk. | Tidak ada. |
=AVERAGEIFS(B2:B11; A2:A11; "*~**") |
Nama yang memiliki karakter asterisk di dalamnya. | B6, B9, B11. |
| Rumus | Keterangan | Kandidat Cell |
|---|---|---|
=AVERAGEIFS(C2:C11; B2:B11; "?al?") |
Klien-klien yang namanya memiliki al dengan hanya satu huruf sebelum dan setelahnya. | Tidak ada. |
=AVERAGEIFS(C2:C11; B2:B11; "al?") |
Klien-klien dengan namanya hanya terdiri dari tiga karakter yang diawali dengan al. | Tidak ada. |
=AVERAGEIFS(C2:C11; B2:B11; "*al?") |
Klien-klien dengan nama yang diakhiri dengan al dan satu karakter lain yang mengikutinya. | Tidak ada. |
=AVERAGEIFS(C2:C11; B2:B11; "?al*") |
Klien-klien dengan nama yang diawali dengan satu karakter dan diikuti dengan al. | B6, B9, B11. |
=AVERAGEIFS(C2:C11; B2:B11; "??al*") |
Nama-nama yang diawali dengan empat karakter dengan karakter ketiga dan keempat adalah al. | B4, B5, B7. |
=AVERAGEIFS(C2:C11; B2:B11; "*al??") |
Nama-nama yang empat karakter terakhir adalah al dan diikuti oleh dua karakter lainnya. | B7. |
=AVERAGEIFS(C2:C11; B2:B11; "~?") |
Nama yang hanya terdiri dari satu karakter tanda tanya. | Tidak ada. |
=AVERAGEIFS(C2:C11; B2:B11; "*~?*") |
Nama yang memiliki tanda tanya. | B3, B8, B11. |
