Fungsi AGGREGATE di Excel adalah salah satu fitur canggih yang memungkinkan pengguna untuk melakukan operasi agregat seperti SUM, AVERAGE, COUNT, MAX, MIN, dan lainnya pada suatu rentang data. Fungsi ini memiliki keunggulan utama, yaitu kemampuannya untuk mengabaikan baris tersembunyi, nilai kesalahan, atau bahkan hasil dari fungsi SUBTOTAL yang mungkin ada dalam data.
Dalam artikel ini, kita akan membahas berbagai contoh fungsi AGGREGATE Excel serta bagaimana penggunaannya untuk meningkatkan efisiensi pengolahan data di Excel.
Apa Itu Fungsi AGGREGATE di Excel?
Fungsi AGGREGATE adalah fungsi bawaan Excel yang digunakan untuk melakukan operasi agregat pada suatu rentang data vertikal. Keunikan fungsi ini adalah kemampuannya untuk mengabaikan beberapa elemen dalam data seperti nilai kesalahan dan baris tersembunyi, yang tidak dapat dilakukan oleh fungsi agregat biasa seperti SUM atau AVERAGE.
Sintaks dasar dari fungsi AGGREGATE adalah:
=AGGREGATE(function_num, options, array, [k])Dimana:
- function_num adalah angka yang menunjukkan jenis operasi agregat (misalnya, 1 untuk AVERAGE, 9 untuk SUM, 4 untuk MAX, dll.).
- options adalah angka yang menentukan elemen yang akan diabaikan (misalnya, 0 untuk tidak mengabaikan apapun, 3 untuk mengabaikan baris tersembunyi dan nilai kesalahan).
- array adalah rentang data tempat fungsi diterapkan.
- k adalah parameter tambahan untuk beberapa fungsi seperti LARGE, SMALL, PERCENTILE, dan QUARTILE.
Contoh Fungsi AGGREGATE Excel dalam Berbagai Operasi
Berikut beberapa contoh fungsi AGGREGATE Excel untuk penggunaan dengan berbagai skenario. Misalnya kita punya data tabel seperti berikut:
1. Menghitung Jumlah Data dengan SUM
Jika kita ingin menjumlahkan data dalam rentang D4:D19, tetapi ingin mengabaikan nilai kesalahan, kita bisa menggunakan rumus berikut:
=AGGREGATE(9;6;D4:D19)Berikut hasilnya:
Rumus ini menggunakan function_num 9 untuk SUM dan options 6 untuk mengabaikan nilai kesalahan.
2. Menghitung Rata-rata Data dengan AVERAGE
Jika kita ingin menghitung rata-rata dari rentang D4:D19 dengan mengabaikan baris tersembunyi, gunakan rumus berikut:
=AGGREGATE(1;5;D4:D19)Di sini, function_num 1 digunakan untuk AVERAGE, dan options 5 untuk mengabaikan baris tersembunyi.
3. Mencari Nilai Maksimum dengan MAX
Untuk menemukan nilai maksimum dalam rentang D4:D19 dengan mengabaikan nilai kesalahan, gunakan rumus:
=AGGREGATE(4;6;D4:D19)Function_num 4 menunjukkan operasi MAX, sementara options 6 mengabaikan nilai kesalahan.
4. Menghitung Banyaknya Data dengan COUNT
Jika kita ingin menghitung jumlah sel yang berisi angka dalam rentang D4:D19, kita bisa menggunakan rumus:
=AGGREGATE(2;4;D4:D19)Function_num 2 digunakan untuk COUNT, sedangkan options 4 berarti tidak ada yang diabaikan.
5. Mencari Nilai Terbesar ke-k dengan LARGE
Misalnya, kita ingin mencari nilai terbesar ke-2 dalam rentang D4:D19, kita dapat menggunakan rumus berikut:
=AGGREGATE(14;4;D4:D19;2)Function_num 14 digunakan untuk LARGE, dan angka 2 di akhir menunjukkan bahwa kita mencari nilai terbesar ke-2.
6. Mencari Nilai Terkecil ke-k dengan SMALL
Jika kita ingin menemukan nilai terkecil ke-3 dalam rentang data, gunakan rumus berikut:
=AGGREGATE(15;4;D4:D19;3)Di sini, function_num 15 digunakan untuk SMALL, dan angka 3 menunjukkan kita mencari nilai terkecil ke-3.
7. Menghitung Persentil dengan PERCENTILE.INC
contoh fungsi AGGREGATE Excel untuk menghitung persentil ke-75 dari suatu dataset, gunakan rumus:
=AGGREGATE(16;4;D4:D19;0,75)Function_num 16 digunakan untuk PERCENTILE.INC, dan 0.75 menunjukkan persentil ke-75.
8. Menghitung Kuartil dengan QUARTILE.INC
Jika kita ingin menghitung kuartil ke-2 dari rentang data, gunakan rumus contoh fungsi AGGREGATE Excel:
=AGGREGATE(17;4;D4:D19;2)Function_num 17 digunakan untuk QUARTILE.INC, dan angka 2 menunjukkan kuartil kedua (median).
9. Mengabaikan Baris Tersembunyi dan Kesalahan
Misalnya, kita ingin menjumlahkan data tetapi mengabaikan baris tersembunyi dan kesalahan, gunakan:
=AGGREGATE(9;7;D4:D19)Di sini, options 7 mengabaikan baris tersembunyi dan kesalahan.
10. Menggunakan AGGREGATE dengan Referensi Ganda
Fungsi AGGREGATE juga bisa digunakan dengan lebih dari satu referensi. Misalnya, untuk menjumlahkan dua rentang data sekaligus:
=AGGREGATE(9;4;D4:D19;E4:E19)Rumus ini menjumlahkan dua rentang data tanpa mengabaikan elemen apa pun.
Kesimpulan
Fungsi AGGREGATE di Excel adalah alat yang sangat berguna untuk melakukan perhitungan agregat dengan fleksibilitas tinggi. Dengan fungsi ini, kita bisa menghindari berbagai kendala seperti nilai kesalahan dan baris tersembunyi yang sering mengganggu perhitungan. Dari contoh fungsi AGGREGATE Excel yang telah dibahas, kita dapat melihat bahwa fungsi ini dapat diterapkan dalam berbagai skenario, termasuk perhitungan SUM, AVERAGE, MAX, COUNT, LARGE, SMALL, dan lain-lain.
Dengan memahami dan menerapkan contoh fungsi AGGREGATE Excel secara efektif, kita dapat menghemat waktu dalam mengolah data dan meningkatkan akurasi dalam analisis data di Excel.
Download Template Gratis
Bagi Anda yang ingin mencoba teknik ini secara langsung, kami telah menyiapkan template gratis yang dirancang khusus untuk membantu Anda mempaktikkan Contoh Fungsi AGGREGATE Excel dan Penggunaannya Dalam Dunia Kerja ini. File Excel bisa didownload melalui tautan berikut:
Atau juga jika ingin mempelajari langkah demi langkah secara visual, bisa kunjungi Youtube Channel Depot Excel. Di sana teman-teman akan melihat bagaimana rumusnya bekerja. Semoga bermanfaat!
0 Komentar