Cara menggunakan fungsi Excel AVERAGEIFS -

Ringkasan

Fungsi Excel AVERAGEIFS menghitung rata-rata angka dalam rentang yang memenuhi satu atau beberapa kriteria. Kriteria yang digunakan untuk AVERAGEIFS dapat mencakup operator logika (>, <,, =) dan karakter pengganti (* ,?) untuk pencocokan parsial.

Tujuan

Rata-rata sel yang cocok dengan beberapa kriteria

Nilai kembali

Rata-rata sel yang memenuhi semua kriteria

Sintaksis

= AVERAGEIFS (avg_rng, range1, criteria1, (range2), (criteria2),…)

Argumen

  • avg_rng - Rentang untuk dirata - ratakan .
  • range1 - Rentang pertama yang dievaluasi.
  • kriteria1 - Kriteria untuk digunakan di range1.
  • range2 - (opsional) Rentang kedua untuk dievaluasi.
  • kriteria2 - (opsional) Kriteria yang akan digunakan di range2.

Versi: kapan

Excel 2007

Catatan penggunaan

Fungsi AVERAGEIFS menghitung rata-rata angka dalam rentang yang memenuhi kriteria yang disediakan. Rentang pertama yang diberikan untuk AVERAGEIFS adalah rentang yang berisi angka untuk dirata-ratakan. Kriteria disediakan berpasangan (rentang, kriteria) dan hanya pasangan pertama yang diperlukan. Untuk setiap kriteria tambahan, berikan pasangan rentang / kriteria lain. Kriteria yang diberikan untuk AVERAGEIFS diterapkan dengan logika AND - semua kriteria harus benar agar angka dapat disertakan dalam rata-rata.

Kriteria dapat diberikan sebagai angka, string, atau referensi. Misalnya, kriteria yang valid bisa jadi 10, "> 10", A1, atau "<" & A1. Kriteria juga dapat menyertakan karakter pengganti untuk pencocokan sebagian.

AVERAGEIFS berada dalam grup delapan fungsi di Excel yang memisahkan kriteria logis menjadi dua bagian (rentang + kriteria). Akibatnya, sintaksis yang digunakan untuk menyusun kriteria berbeda, dan AVERAGEIFS memerlukan rentang sel untuk argumen rentang - Anda tidak dapat menggunakan larik.

AVERAGEIFS secara otomatis akan mengabaikan sel kosong, bahkan ketika kriteria cocok. Dengan kata lain, AVERAGEIFS tidak akan menyertakan rata-rata sel kosong. AVERAGEIFS mengembalikan # DIV / 0! jika tidak ada sel yang memenuhi kriteria.

Contoh

Pada contoh yang ditampilkan, rumus di H5: H7 adalah:

=AVERAGEIFS(C5:C15,C5:C15,">0") =AVERAGEIFS(C5:C15,C5:C15,">0",C5:C15,"=2",E5:E15,">1")

Rumus ini mengembalikan harga rata-rata properti di mana:

  1. harga lebih besar dari nol
  2. harga lebih besar dari nol dan kurang dari $ 500.000
  3. properti memiliki minimal 2 kamar tidur dan lebih dari 1 kamar mandi

Tanda kutip ganda ("") dalam kriteria

Secara umum, nilai teks dalam kriteria diapit tanda kutip ganda (""), dan angka tidak. Namun, bila operator logika disertakan dengan angka, angka dan operator harus diapit tanda kutip. Perhatikan perbedaan dua contoh di bawah ini. Karena rumus kedua menggunakan operator lebih besar dari atau sama dengan (> =), operator dan angka diapit oleh tanda kutip ganda.

=AVERAGEIFS(C5:C15,D5:D15,2) // 2 bedrooms =AVERAGEIFS(C5:C15,D5:D15,">=2") // 2+ bedrooms

Tanda kutip ganda juga digunakan untuk nilai teks. Misalnya, untuk nilai rata-rata di B1: B10 ketika nilai di A1: A10 sama dengan "merah", Anda dapat menggunakan rumus seperti ini:

=AVERAGEIFS(B1:B10,A1:A10,"red")

Berbagai kriteria

Masukkan kriteria berpasangan (rentang, kriteria). Misalnya, untuk nilai rata-rata di A1: A10, di mana B1: B10 = "A", dan C1: C10> 5, gunakan:

=AVERAGEIFS(A1:A10,B1:B10,"A",C1:C10,">5")

Nilai dari sel lain

Nilai dari sel lain bisa dimasukkan dalam kriteria menggunakan penggabungan. Dalam contoh di bawah ini, AVERAGEIFS akan mengembalikan rata-rata angka di A1: A10 yang kurang dari nilai di sel B1. Perhatikan operator less than (yang merupakan teks) diapit tanda kutip.

=AVERAGEIFS(A1:A10,A1:A10,"<"&B1) // average values less than B1

Karakter pengganti

Karakter wildcard tanda tanya (?), Asterisk (*), atau tilde (~) dapat digunakan dalam kriteria. Tanda tanya (?) Cocok dengan salah satu karakter dan tanda bintang (*) cocok dengan nol atau lebih karakter apapun. Misalnya, untuk nilai rata-rata di B1: B10 ketika nilai di A1: A10 berisi teks "merah", Anda dapat menggunakan rumus seperti ini:

=AVERAGEIFS(B1:B10,A1:A10,"*red*")

Tilde (~) adalah karakter pelarian untuk memungkinkan Anda menemukan karakter pengganti literal. Misalnya, untuk mencocokkan tanda tanya literal (?), Asterisk (*), atau tilde (~), tambahkan tilde di depan wildcard (yaitu ~ ?, ~ *, ~~).

Catatan: urutan argumen berbeda antara AVERAGEIFS dan AVERAGEIF. Rentang untuk dirata-rata selalu menjadi argumen pertama dalam AVERAGEIFS.

Catatan

  • Jika tidak ada data yang cocok dengan kriteria, AVERAGEIFS mengembalikan # DIV0! kesalahan
  • Setiap rentang tambahan harus memiliki jumlah baris dan kolom yang sama dengan average_range .
  • Kriteria non-numerik perlu diapit tanda kutip ganda tetapi kriteria numerik tidak. Misalnya: 100, "100", "> 32", "jim", atau A1 (di mana A1 berisi angka).
  • Karakter wildcard? dan * dapat digunakan dalam kriteria. Tanda tanya cocok dengan salah satu karakter dan tanda bintang cocok dengan nol atau lebih karakter apa pun.
  • Untuk menemukan tanda tanya atau tanda bintang literal, gunakan tanda gelombang (~) di depan tanda tanya atau tanda bintang (yaitu ~ ?, ~ *).

Artikel yang menarik...