AGGREGATE Fungsi Excel

Fungsi AGGREGATE di Excel

Fungsi AGGREGATE di excel mengembalikan agregat tabel data atau daftar data tertentu, fungsi ini juga memiliki argumen pertama sebagai nomor fungsi dan argumen lebih lanjut untuk rentang kumpulan data, nomor fungsi harus diingat untuk mengetahui fungsi mana yang akan digunakan .

Sintaksis

Ada dua sintaks untuk Rumus AGREGAT:

  1. Referensi Sintaks

= AGREGAT (function_num, opsi, ref1, ref2, ref [3],…)

  1. Array Syntax

= AGREGAT (function_num, options, array, [k])

Function_num adalah angka yang menunjukkan fungsi tertentu yang ingin kita gunakan, yaitu angka 1-19

Opsi: ini juga merupakan nilai numerik mulai dari 0 hingga 7 dan menentukan nilai mana yang akan diabaikan saat penghitungan

Ref1, ref2, ref [3]:  adalah argumen saat menggunakan sintaks referensi, itu adalah nilai numerik atau nilai yang ingin kita lakukan penghitungannya, setidaknya dua argumen diperlukan, argumen selebihnya bersifat opsional.

Array: adalah larik nilai yang ingin kita operasikan, digunakan dalam sintaks larik dari fungsi AGGREGATE di excel

K: adalah argumen opsional dan merupakan nilai numerik, digunakan ketika fungsi seperti LARGE, SMALL, PERCENTILE.EXC, QUARTILE.INC, PERCENTILE.INC atau QUARTILE.EXC di Excel digunakan.

Contoh

Anda dapat mendownload Template Excel Fungsi AGREGAT ini di sini - Template Excel Fungsi AGREGAT

Contoh 1

Misalkan kita memiliki daftar angka dan kita akan menghitung Average, Count yaitu jumlah sel yang berisi nilai, Counta -count sel yang tidak kosong, Maksimum, Minimum, produk, dan jumlah nilai numerik yang diberikan. Nilai diberikan di bawah ini dalam tabel:

Mari kita hitung dahulu Rata-rata di Baris 9, untuk semua nilai yang diberikan. Untuk rata-rata function_ num adalah

Di Kolom C, semua nilai diberikan dan kami tidak perlu mengabaikan nilai apa pun, jadi kami akan memilih Opsi 4 (tidak mengabaikan apa pun)

Dan memilih rentang nilai C1: C8 sebagai array nilai numerik

Karena ' k' adalah argumen opsional dan digunakan ketika fungsi seperti LARGE, SMALL di Excel, PERCENTILE.EXC, QUARTILE.INC, PERCENTILE.INC atau QUARTILE.EXC digunakan tetapi dalam kasus ini, kami menghitung Average jadi kami akan menghilangkan nilai k.

Jadi, nilai rata-ratanya adalah

Begitu pula untuk range D1: D8, sekali lagi kita akan memilih opsi 4.

Untuk range E1: E8, cell E6 berisi nilai error, jika kita akan menggunakan rumus AGGREGATE yang sama kita akan mendapatkan error, tetapi ketika opsi yang sesuai digunakan, AGGREGATE di Excel memberikan rata-rata nilai yang tersisa mengabaikan error nilai di E6.

Untuk mengabaikan nilai kesalahan, kami memiliki opsi 6.

Demikian pula untuk range G1: G8 kita akan menggunakan opsi 6 (abaikan nilai error)

Sekarang, untuk range H3 jika kita beri nilai 64, dan sembunyikan baris ketiga dan gunakan opsi 5, untuk mengabaikan baris tersembunyi, AGREGAT di Excel kita akan memberikan nilai rata-rata untuk nilai numerik yang terlihat saja.

Keluaran tanpa Menyembunyikan Baris 3

Output setelah Menyembunyikan Baris 3

Menerapkan rumus AGREGAT untuk operasi lain, kami punya

Contoh - # 2

Misalkan kita memiliki tabel untuk pendapatan yang dihasilkan pada tanggal yang berbeda dari saluran yang berbeda seperti yang diberikan di bawah ini

Sekarang, kami ingin memeriksa pendapatan yang dihasilkan untuk berbagai saluran. Jadi, ketika kita menerapkan fungsi penjumlahan kita mendapatkan total pendapatan yang dihasilkan tetapi jika kita ingin memeriksa pendapatan yang dihasilkan untuk saluran Organik atau saluran langsung atau lainnya, ketika kita menerapkan filter di excel untuk hal yang sama, fungsi penjumlahan akan selalu berikan jumlah total

Kami ingin bahwa ketika kami memfilter saluran, kami mendapatkan jumlah nilai yang terlihat, jadi daripada menggunakan fungsi SUM, kami akan menggunakan fungsi AGGREGATE untuk mendapatkan jumlah nilai yang terlihat ketika filter terapan.

Jadi, mengganti rumus SUM dengan fungsi AGGREGATE dengan kode opsi 5 (mengabaikan baris dan nilai tersembunyi) yang kita miliki,

Sekarang, ketika kita akan menerapkan filter untuk saluran yang berbeda, itu akan menunjukkan pendapatan untuk saluran itu hanya saat sisa baris disembunyikan.

Pendapatan Total yang Dihasilkan untuk Saluran Langsung:

Pendapatan Total yang Dihasilkan untuk Saluran Organik:

Pendapatan Total yang Dihasilkan untuk Saluran Berbayar:

Jadi, kita dapat melihat fungsi AGREGAT menghitung nilai Jumlah berbeda untuk pendapatan yang dihasilkan untuk saluran yang berbeda setelah difilter. Jadi, fungsi AGGREGATE dapat digunakan secara dinamis untuk penggantian fungsi yang berbeda untuk kondisi yang berbeda tanpa menggunakan rumus kondisional.

Misalkan untuk tabel channel dan pendapatan yang sama, beberapa nilai pendapatan kita mengandung kesalahan, sekarang kita perlu mengabaikan kesalahan dan pada saat yang sama, jika kita ingin menerapkan filter, fungsi AGGREGATE harus mengabaikan nilai baris tersembunyi juga.

Ketika kami menggunakan opsi 5, kami mendapatkan kesalahan untuk SUM dari total pendapatan, sekarang untuk mengabaikan kesalahan kami harus menggunakan opsi 6

Menggunakan opsi 6 kita mendapatkan jumlah yang mengabaikan nilai kesalahan, tetapi ketika kita menerapkan filter, misalnya, memfilter berdasarkan nilai saluran Langsung kita mendapatkan jumlah yang sama dengan mengabaikan kesalahan tetapi pada saat yang sama kita harus mengabaikan nilai yang tersembunyi juga.

Jadi, dalam kasus ini, kami akan menggunakan opsi 7 yang mengabaikan nilai kesalahan dan pada saat yang sama baris tersembunyi

Hal-hal untuk diingat

  • Fungsi AGGREGATE tidak mengenali fungsi _ nilai num lebih besar dari 19 atau kurang dari 1 dan demikian pula untuk nomor Opsi tidak mengenali nilai yang lebih besar dari 7 dan kurang dari 1, jika kita memberikan nilai lain itu memberikan #VALUE ! Kesalahan
  • Itu selalu menerima nilai numerik dan selalu mengembalikan nilai numerik sebagai output
  • AGREGAT di Excel memiliki batasan; itu hanya mengabaikan baris tersembunyi tetapi tidak mengabaikan kolom tersembunyi.