AVERAGEIFS
Fungsi AVERAGEIFS mengembalikan purata (min aritmetik) bagi sel dalam set yang ditentukan di mana satu atau lebih set memenuhi satu atau lebih syarat berkaitan.
AVERAGEIFS(avg-values, test-values, condition, test-values…, condition…)
avg-values: Koleksi mengandungi nilai yang perlu dipertimbangkan untuk nilai purata. avg-values ialah rujukan kepada koleksi tunggal sel,yang mungkin mengandungi sebarang nilai.
test-values: Koleksi yang mengandungi nilai yang perlu diuji. test-values boleh mengandungi sebarang nilai.
condition: Ungkapan yang membandingkan atau menguji nilai dan hasil dalam nilai boolean TRUE atau FALSE. condition boleh termasuk operator perbandingan, pemalar, operator perangkaian ampersan, rujukan dan kad bebas. Anda boleh menggunakan kad bebas untuk memadankan sebarang aksara tunggal atau berbilang aksara dalam ungkapan. Anda boleh menggunakan ? (tanda soal) untuk mewakili satu aksara, * (asterisk) untuk mewakili pelbagai aksara, dan ~ (tilde) untuk menentukan aksara seterusnya perlu dipadankan berbanding digunakan sebagai kad bebas. condition juga boleh mengandungi fungsi REGEX dan bukannya kad bebas.
test-values…: Secara pilihan masukkan satu atau lebih koleksi tambahan yang mengandungi nilai untuk diuji. Setiap koleksi test-values mesti diikuti serta merta oleh ungkapan condition. Corak test-values, condition ini boleh diulangi sebanyak kali yang diperlukan.
condition…: Jika koleksi pilihan test-values disertakan, condition… ialah ungkapan tambahan yang menghasilkan nilai boolean TRUE atau FALSE. Mesti terdapat satu ungkapan condition berikutan setiap koleksi test-values; oleh itu, fungsi ini sentiasa mempunyai bilangan argumen bernombor ganjil.
Nota
Untuk setiap pasangan test-values dan condition, nilai yang sepadan (kedudukan yang sama dalam tatasusunan) dibandingkan dengan ujian bersyarat. Jika semua ujian bersyarat dipenuhi, nilai yang sepadan dalam avg-values termasuk dalam purata.
avg-values dan semua koleksi test-values mestilah bersaiz sama.
Contoh |
|---|
Jika diberikan jadual berikut: |
A | B | C | D | |
|---|---|---|---|---|
1 | Umur | Jantina | Status | Gaji |
2 | 35 | M | M | 71,000 |
3 | 27 | F | M | 81,000 |
4 | 42 | M | M | 86,000 |
5 | 51 | M | S | 66,000 |
6 | 28 | M | S | 52,000 |
7 | 49 | F | S | 62,000 |
8 | 63 | F | M | 89,000 |
9 | 22 | M | M | 34,000 |
10 | 29 | F | S | 42,000 |
11 | 35 | F | M | 56,000 |
12 | 33 | M | S | 62,000 |
13 | 61 | M | M | 91,000 |
=AVERAGEIFS(D2:D13, A2:A13, "<40", B2:B13, "=M") mengembalikan 54,750, purata gaji bagi lelaki (ditunjukkan dengan "M" dalam lajur B) di bawah umur 40 tahun. =AVERAGEIFS(D2:D13, A2:A13, "<40", B2:B13, "=M", C2:C13, "=S") mengembalikan 57,000, purata gaji bagi lelaki yang bujang (ditunjukkan dengan "S" dalam lajur C) di bawah umur 40 tahun. =AVERAGEIFS(D2:D13, A2:A13, "<40", B2:B13, "=M", C2:C13, "=M") mengembalikan 52,500, purata gaji bagi lelaki yang bujang (ditunjukkan dengan "M" dalam lajur C) di bawah umur 40 tahun. =AVERAGEIFS(D2:D13, A2:A13, ">=40", B2:B13, "=M", C2:C13, "=M") mengembalikan 88,500, purata gaji bagi lelaki yang telah berkahwin (ditunjukkan dengan "M" dalam lajur C) dan sekurang-kurangnya berumur 40 tahun. Anda juga boleh menggunakan operator ≥. =AVERAGEIFS(D2:D13, A2:A13, "<40", B2:B13, "=F") mengembalikan 59,667, purata gaji bagi perempuan (ditunjukkan dengan "F" dalam lajur B) di bawah umur 40 tahun. =AVERAGEIFS(D2:D13, A2:A13, "<>35", C2:C13, "=S") mengembalikan 56,800, purata gaji bagi orang yang bukan berumur 35 tahun dan bujang. Anda juga boleh menggunakan operator ≠. |
Contoh menggunakan REGEX |
|---|
Jika diberikan jadual berikut: |
A | B | |
|---|---|---|
1 | 45 | marina@example.com |
2 | 41 | Aaron |
3 | 29 | michael@example.com |
4 | 64 | katrina@example.com |
5 | 12 | Sarah |
=AVERAGEIFS(A1:A5, B1:B5, REGEX("([A-Z0-9a-z._%+-]+)@([A-Za-z0-9.-]+\.[A-Za-z]{2,4})"), A1:A5, ">10") mengembalikan 46, purata semua sel dalam A1:A5 yang lebih besar daripada 10 dan sel yang sepadan dalam B1:B5 mengandungi alamat e-mel. |