Cara menggunakan fungsi Excel AGGREGATE -

Daftar Isi

Ringkasan

Fungsi Excel AGGREGATE mengembalikan penghitungan agregat seperti AVERAGE, COUNT, MAX, dll., Secara opsional mengabaikan baris dan kesalahan tersembunyi. Total 19 operasi tersedia, ditentukan oleh nomor fungsi di argumen pertama (lihat tabel untuk opsi).

Tujuan

Kembalikan penghitungan agregat

Nilai kembali

Tergantung pada fungsi yang ditentukan

Sintaksis

= AGREGAT (function_num, options, ref1, ref2)

Argumen

  • function_num - Operasi untuk melakukan (1-19).
  • options - Nilai untuk diabaikan (0-7).
  • ref1 - Argumen pertama.
  • ref2 - Argumen kedua (k).

Versi: kapan

Excel 2010

Catatan penggunaan

Fungsi AGGREGATE mengembalikan hasil penghitungan agregat seperti AVERAGE, COUNT, MAX, MIN, dll. Total 19 operasi tersedia, dan operasi yang akan dilakukan ditentukan sebagai angka, yang muncul sebagai argumen pertama dalam fungsi tersebut. Argumen kedua, opsi, mengontrol bagaimana AGREGAT menangani kesalahan dan nilai dalam baris tersembunyi. Lihat tabel di bawah untuk semua opsi yang tersedia.

Contoh 1

Untuk mengembalikan nilai MAX dalam rentang A1: A10, mengabaikan kesalahan dan baris tersembunyi, berikan 4 untuk nomor fungsi dan 7 untuk opsi:

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

Untuk mengembalikan nilai MIN dengan opsi yang sama, ubah nomor fungsi menjadi 5:

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

Contoh # 2

Pada contoh yang ditunjukkan di atas, rumus di D5 adalah:

=AGGREGATE(4,6,values)

di mana "nilai" adalah rentang bernama B5: B14. Nomor fungsinya adalah 4, yang menentukan MAX. Opsi disediakan sebagai 6, untuk mengabaikan kesalahan saja.

Contoh # 3 - terbesar ke-n

Rumus di D8: D10 menunjukkan cara mengembalikan nilai "terbesar ke-n":

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

Nomor fungsi di sini adalah 14, yang menjalankan fungsi LARGE. Karena fungsi LARGE memerlukan argumen ak, ini muncul sebagai argumen terakhir dalam tiga rumus di atas.

Contoh # 4 - operasi array

Apa yang membuat AGGREGATE sangat berguna untuk rumus yang lebih kompleks adalah ia dapat menangani array secara native ketika nomor fungsinya adalah 14-19. Misalnya, untuk menemukan nilai MAX pada hari Senin, dengan data yang menyertakan tanggal dan nilai, Anda dapat menggunakan AGGREGATE seperti ini:

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

Di sini kami menetapkan 14 untuk fungsi (LARGE) dan 6 untuk opsi (abaikan kesalahan). Kemudian kami membangun ekspresi logis menggunakan fungsi TEXT untuk memeriksa semua tanggal untuk hari Senin. Hasil dari operasi ini adalah larik nilai TRUE / FALSE, yang menjadi penyebut dari nilai aslinya. FALSE mengevaluasi sebagai nol, dan melempar # DIV / 0! kesalahan. TRUE mengevaluasi sebagai 1 dan mengembalikan nilai aslinya. Array terakhir dari nilai dan kesalahan berfungsi seperti filter. AGGREGATE mengabaikan semua kesalahan dan mengembalikan nilai terbesar (maksimum) yang bertahan. Contoh lebih lengkapnya disini.

Nomor fungsi

Tabel di bawah ini mencantumkan nomor fungsi yang tersedia untuk fungsi AGGREGATE, bersama dengan nama fungsi terkait. Kolom ketiga, Ref2, menunjukkan nilai argumen kedua yang diharapkan saat diperlukan.

Fungsi Fungsi Ref2
1 RATA-RATA
2 MENGHITUNG
3 COUNTA
4 MAKS
5 MIN
6 PRODUK
7 STDEV.S
8 STDEV.P
9 JUMLAH
10 VAR.S
11 VAR.P
12 MEDIAN
13 MODE.SNGL
14 BESAR k
15 KECIL k
16 PERCENTILE.INC k
17 QUARTILE.INC kuart
18 PERCENTILE.EXC k
19 QUARTILE.EXC kuart

Pilihan perilaku

Fungsi AGGREGATE memiliki banyak opsi untuk mengabaikan kesalahan, baris tersembunyi, dan fungsi lainnya. Nilai yang memungkinkan adalah 0-7, seperti yang ditunjukkan pada tabel di bawah.

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

Catatan

  • AGGREGATE mengembalikan #VALUE! kesalahan jika argumen fungsi kedua diperlukan, tetapi tidak disediakan.
  • Referensi 3D tidak berfungsi dengan AGGREGATE.
  • Fungsi AGGREGATE dirancang rentang vertikal, bukan rentang horizontal.

Artikel yang menarik...