Fungsi AGGREGATE di Excel
Fungsi AGGREGATE di excel mengembalikan agregat dari tabel data tertentu atau daftar data, 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:
- Referensi Sintaks
= AGREGAT (function_num, opsi, ref1, ref2, ref (3),…)
- Array Syntax
= AGREGAT (function_num, options, array, (k))
Function_num adalah angka yang menunjukkan fungsi tertentu yang ingin kita gunakan; itu adalah angka 1-19
Opsi: ini juga merupakan nilai numerik yang berkisar dari 0 hingga 7 dan menentukan nilai mana yang akan diabaikan selama penghitungan
Ref1, ref2, ref (3): adalah argumen saat menggunakan sintaks referensi; itu adalah nilai numerik atau nilai yang ingin kita lakukan penghitungannya, setidaknya diperlukan dua argumen. Argumen istirahat bersifat opsional.
Array: adalah larik nilai yang ingin kita operasikan; itu digunakan dalam sintaks array dari fungsi AGGREGATE di excel.
K: adalah argumen opsional dan nilai numerik; ini digunakan ketika fungsi seperti LARGE, SMALL, PERCENTILE.EXC, QUARTILE.INC, PERCENTILE.INC atau QUARTILE.EXC di Excel digunakan.
Contoh
Contoh 1
Misalkan kita memiliki daftar angka, dan kita akan menghitung Rata-rata, Hitung yaitu jumlah sel yang berisi nilai, Hitung -jumlah 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 1
Di Kolom C, semua nilai diberikan, dan kita tidak perlu mengabaikan nilai apa pun sehingga kita 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 ke hilangkan nilai k.
Jadi, nilai rata-ratanya adalah
Demikian pula untuk range D1: D8, sekali lagi kita akan memilih opsi 4.
Untuk rentang E1: E8, sel E6 berisi nilai kesalahan. Jika kita menggunakan rumus AGREGAT yang sama, kita akan mendapatkan error. Namun, ketika opsi yang sesuai digunakan, AGREGAT di Excel memberikan rata-rata nilai yang tersisa dengan mengabaikan nilai kesalahan 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 memberi nilai 64, dan menyembunyikan baris ketiga dan menggunakan opsi 5, untuk mengabaikan baris tersembunyi, AGGREGATE di Excel, kita akan memberikan nilai rata-rata hanya untuk nilai numerik yang terlihat.
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 pendapatan total 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 memberikan jumlah total
Kami ingin 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 saat filter diterapkan.
Jadi, mengganti rumus SUM dengan fungsi AGGREGATE dengan kode opsi 5 (mengabaikan baris dan nilai tersembunyi), kita punya,
Sekarang, ketika kita menerapkan filter untuk saluran yang berbeda, itu akan menunjukkan pendapatan untuk saluran itu hanya jika baris lainnya disembunyikan.
Pendapatan Total yang Dihasilkan untuk Saluran Langsung:
Pendapatan Total yang Dihasilkan untuk Saluran Organik:
Pendapatan Total yang Dihasilkan untuk Saluran Berbayar:
Kita dapat melihat fungsi AGREGAT menghitung nilai Jumlah yang berbeda untuk pendapatan yang dihasilkan untuk saluran yang berbeda setelah difilter. Jadi, fungsi AGGREGATE dapat digunakan secara dinamis untuk menggantikan fungsi yang berbeda untuk kondisi yang berbeda tanpa menggunakan rumus bersyarat.
Misalkan untuk tabel 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 juga harus mengabaikan nilai baris tersembunyi.
Saat kami menggunakan opsi 5, kami mendapatkan kesalahan untuk SUM dari total pendapatan. Untuk mengabaikan kesalahan, kita harus menggunakan opsi 6
Menggunakan opsi 6, kami mendapatkan jumlah yang mengabaikan nilai kesalahan. Namun, ketika kita menerapkan filter, misalnya, memfilter berdasarkan nilai saluran Langsung, kita mendapatkan jumlah yang sama dengan mengabaikan kesalahan, tetapi pada saat yang sama, kita juga harus mengabaikan nilai tersembunyi.
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 _ num nilai lebih dari 19 atau kurang dari 1. Demikian pula, untuk nomor Opsi, tidak mengidentifikasi nilai lebih besar dari 7 dan kurang dari 1; jika kami memberikan nilai lain, ini memberi #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.
