Cara menggunakan fungsi Excel AGGREGATE -

Isi kandungan

Ringkasan

Fungsi Excel AGGREGATE mengembalikan pengiraan agregat seperti AVERAGE, COUNT, MAX, dll., Secara pilihan mengabaikan baris dan ralat tersembunyi. Sebanyak 19 operasi tersedia, ditentukan oleh nombor fungsi dalam argumen pertama (lihat jadual untuk pilihan).

Tujuan

Kembalikan pengiraan agregat

Nilai pulangan

Bergantung pada fungsi yang ditentukan

Sintaks

= AGGREGATE (fungsi_num, pilihan, ref1, ref2)

Hujah

  • function_num - Operasi yang akan dilakukan (1-19).
  • pilihan - Nilai untuk diabaikan (0-7).
  • ref1 - Hujah pertama.
  • ref2 - Hujah kedua (k).

Versi

Excel 2010

Catatan penggunaan

Fungsi AGGREGATE mengembalikan hasil pengiraan agregat seperti RATA-RATA, COUNT, MAX, MIN, dll. Sebanyak 19 operasi tersedia, dan operasi yang akan dilakukan ditentukan sebagai angka, yang muncul sebagai argumen pertama dalam fungsi tersebut. Argumen kedua, pilihan, mengawal bagaimana AGGREGATE menangani kesilapan dan nilai dalam baris tersembunyi. Lihat jadual di bawah untuk semua pilihan yang ada.

Contoh # 1

Untuk mengembalikan nilai MAX dalam julat A1: A10, mengabaikan ralat dan baris tersembunyi, sediakan 4 untuk nombor fungsi dan 7 untuk pilihan:

=AGGREGATE(4,7,A1:A10) // max value

Untuk mengembalikan nilai MIN dengan pilihan yang sama, ubah nombor fungsi menjadi 5:

=AGGREGATE(5,7,A1:A10) // min value

Contoh # 2

Dalam contoh yang ditunjukkan di atas, formula dalam D5 adalah:

=AGGREGATE(4,6,values)

di mana "nilai" adalah julat bernama B5: B14. Nombor fungsi adalah 4, yang menentukan MAX. Pilihan disediakan sebagai 6, untuk mengabaikan kesalahan sahaja.

Contoh # 3 - terbesar ke-9

Rumus dalam D8: D10 menunjukkan cara mengembalikan nilai "nth terbesar":

=AGGREGATE(14,6,values,1) // 1st largest =AGGREGATE(14,6,values,2) // 2nd largest =AGGREGATE(14,6,values,3) // 3rd largest

Nombor fungsi di sini adalah 14, yang menjalankan fungsi BESAR. Oleh kerana fungsi LARGE memerlukan argumen ak, ia muncul sebagai argumen terakhir dalam ketiga formula di atas.

Contoh # 4 - operasi array

Apa yang menjadikan AGGREGATE sangat berguna untuk formula yang lebih kompleks adalah ia dapat menangani tatasusunan secara semula jadi apabila nombor fungsi adalah 14-19. Sebagai contoh, untuk mencari nilai MAX pada hari Isnin, dengan data yang merangkumi tarikh dan nilai, anda boleh menggunakan AGGREGATE seperti ini:

=AGGREGATE(14,6,values/(TEXT(dates,"ddd")="Mon"),1)

Di sini kita tentukan 14 untuk fungsi (BESAR) dan 6 untuk pilihan (abaikan kesalahan). Kemudian kami membina ungkapan logik menggunakan fungsi TEXT untuk memeriksa semua tarikh untuk hari Isnin. Hasil operasi ini adalah susunan nilai BENAR / SALAH, yang menjadi penyebut nilai asal. FALSE menilai sebagai sifar, dan melemparkan # DIV / 0! kesilapan. BENAR menilai sebagai 1 dan mengembalikan nilai asal. Susunan nilai dan ralat terakhir bertindak seperti penapis. AGGREGATE mengabaikan semua kesalahan dan mengembalikan (maksimum) nilai yang masih ada. Contoh yang lebih lengkap di sini.

Nombor fungsi

Jadual di bawah menyenaraikan nombor fungsi yang tersedia untuk fungsi AGGREGATE, bersama dengan nama fungsi yang berkaitan. Lajur ketiga, Ref2, menunjukkan nilai argumen kedua yang diharapkan apabila diperlukan.

Fungsi Fungsi Ruj2
1 Purata
2 BANYAK
3 NEGARA
4 MAX
5 MIN
6 PRODUK
7 STDEV.S
8 STDEV.P
9 RUMUSAN
10 VAR.S
11 VAR.P
12 PERUBATAN
13 MODE.SNGL
14 BESAR k
15 KECIL k
16 PERCENTILE.INC k
17 KUANTITI.INC kuart
18 PERATUS.EXC k
19 KUANTITI.EXC kuart

Pilihan tingkah laku

Fungsi AGGREGATE mempunyai banyak pilihan untuk mengabaikan kesalahan, baris tersembunyi, dan fungsi lain. Nilai yang mungkin adalah 0-7, seperti yang ditunjukkan dalam jadual di bawah.

Pilihan Kelakuan
0 Abaikan fungsi SUBTOTAL dan AGGREGATE
1 Abaikan fungsi baris tersembunyi, SUBTOTAL dan AGGREGATE
2 Abaikan nilai ralat, fungsi SUBTOTAL dan AGGREGATE
3 Abaikan baris tersembunyi, nilai ralat, fungsi SUBTOTAL dan AGGREGATE
4 Abaikan apa-apa
5 Abaikan baris tersembunyi
6 Abaikan nilai ralat
7 Abaikan baris tersembunyi dan nilai ralat

Catatan

  • AGGREGATE mengembalikan # NILAI! ralat jika argumen fungsi kedua diperlukan, tetapi tidak diberikan.
  • Rujukan 3D tidak berfungsi dengan AGGREGATE.
  • Fungsi AGGREGATE dirancang julat menegak, bukan julat mendatar.

Artikel menarik...