TL;DR
Rumus AGGREGATE Excel menjalankan 19 fungsi agregat berbeda (SUM, AVERAGE, LARGE, MEDIAN, dan lainnya) dengan opsi tambahan buat mengabaikan sel error, baris tersembunyi, dan rumus SUBTOTAL lain di dalam range. Sintaksnya =AGGREGATE(nomor_fungsi; opsi; range; [k]), di mana nomor_fungsi milih operasi dan opsi nentuin apa yang dilewati. Kelebihan utamanya dibanding SUBTOTAL: dia tetap ngasih hasil yang benar walaupun ada sel #DIV/0! atau #N/A di tengah data.
Satu sel #DIV/0! di tengah kolom bikin =SUM(...) ikut jadi #DIV/0!. SUBTOTAL juga sama.
AGGREGATE bisa lompatin sel error itu dan tetap ngasih total yang bener. Datanya gak perlu kamu sentuh.
Fungsi ini udah ada sejak Excel 2010, tapi jarang banget dipakai. Padahal dia bisa ngerjain semua kerjaan SUBTOTAL, plus 8 fungsi tambahan.
AGGREGATE adalah fungsi Excel yang menjalankan salah satu dari 19 fungsi agregat. Dia juga punya opsi buat mengabaikan sel error, baris tersembunyi, dan rumus SUBTOTAL lain di dalam range-nya.
Sintaksnya:
=AGGREGATE(nomor_fungsi; opsi; range; [k])Empat bagian:
Contoh paling sering dipakai:
=AGGREGATE(9; 6; D2:D500)Artinya: jumlahkan (9), lewati sel error (6), dari range D2:D500.
| No | Fungsi | Ngitung apa |
|---|---|---|
| 1 | AVERAGE | Rata-rata |
| 2 | COUNT | Jumlah sel berisi angka |
| 3 | COUNTA | Jumlah sel yang gak kosong |
| 4 | MAX | Nilai tertinggi |
| 5 | MIN | Nilai terendah |
| 6 | PRODUCT | Hasil perkalian |
| 7 | STDEV.S | Standar deviasi sampel |
| 8 | STDEV.P | Standar deviasi populasi |
| 9 | SUM | Total penjumlahan |
| 10 | VAR.S | Varians sampel |
| 11 | VAR.P | Varians populasi |
| 12 | MEDIAN | Nilai tengah |
| 13 | MODE.SNGL | Nilai paling sering muncul |
| 14 | LARGE | Nilai ke-k tertinggi (butuh k) |
| 15 | SMALL | Nilai ke-k terendah (butuh k) |
| 16 | PERCENTILE.INC | Persentil (butuh k) |
| 17 | QUARTILE.INC | Kuartil (butuh k) |
| 18 | PERCENTILE.EXC | Persentil eksklusif (butuh k) |
| 19 | QUARTILE.EXC | Kuartil eksklusif (butuh k) |
Nomor 1 sampai 13 sintaksnya cuma 3 argumen. Nomor 14 sampai 19 wajib pakai argumen k di akhir.
Delapan fungsi terakhir (12-19) itu yang gak ada di SUBTOTAL sama sekali.
| Opsi | Yang diabaikan |
|---|---|
| 0 atau kosong | Rumus SUBTOTAL dan AGGREGATE lain |
| 1 | Baris tersembunyi + SUBTOTAL/AGGREGATE lain |
| 2 | Sel error + SUBTOTAL/AGGREGATE lain |
| 3 | Baris tersembunyi + sel error + SUBTOTAL/AGGREGATE lain |
| 4 | Gak ngabaikan apa-apa |
| 5 | Baris tersembunyi doang |
| 6 | Sel error doang |
| 7 | Baris tersembunyi + sel error |
Yang paling sering kepakai: opsi 6. Lewatin error, hitung semua baris.
Kalau laporanmu pakai filter dan ada sel error, opsi 6 tetap cukup. Baris yang kena AutoFilter emang selalu diabaikan tanpa perlu opsi khusus.
Referensi resminya ada di dokumentasi AGGREGATE di Microsoft Support.
Ini kejadian nyata di sheet toko_berkah.
Kolom F isinya persentase margin, rumusnya =(D2-E2)/E2 (selisih harga jual dan modal, dibagi modal).
Masalahnya, ada 14 baris di mana harga_modal-nya nol (barang bonus dari supplier). Pembagian nol jadi #DIV/0!.
Coba hitung rata-rata margin:
=AVERAGE(F2:F1843)Hasilnya: #DIV/0!. Satu sel busuk ngerusak seluruh laporan.
Sekarang pakai AGGREGATE:
=AGGREGATE(1; 6; F2:F1843)Hasilnya: 23,4%. Empat belas baris error dilewatin, 1.828 baris sisanya dihitung normal.
=AGGREGATE(14; 6; F2:F1843; 1)Angka 14 = LARGE, opsi 6 = lewatin error, angka 1 di akhir = ranking pertama.
Hasilnya 68,2% (produk Gula Aren 500g).
Mau top 3? Ganti angka terakhirnya jadi 1, 2, 3 di tiga sel berbeda:
=AGGREGATE(14; 6; $F$2:$F$1843; 1) → 68,2%
=AGGREGATE(14; 6; $F$2:$F$1843; 2) → 61,5%
=AGGREGATE(14; 6; $F$2:$F$1843; 3) → 57,9%MAX biasa gak bisa ngerjain ini. Dia langsung nyerah begitu ketemu satu sel #DIV/0!.
=AGGREGATE(12; 6; F2:F1843)Hasilnya 19,8%. Jauh di bawah rata-rata 23,4%.
Selisih 3,6 poin persen ini artinya ada segelintir produk margin tinggi yang narik rata-ratanya ke atas. Kebanyakan produk sebenernya margin-nya di bawah 20%.
Angka median itu gak akan pernah muncul kalau kamu cuma pakai SUBTOTAL. Dia emang gak punya fungsi MEDIAN.
| Kebutuhan | SUBTOTAL | AGGREGATE |
|---|---|---|
| Total data terfilter | Bisa | Bisa |
| Abaikan baris di-hide manual | Bisa (kode 100-an) | Bisa (opsi 5 atau 7) |
| Lewatin sel error | Gak bisa | Bisa (opsi 2, 3, 6, 7) |
| MEDIAN, MODE | Gak ada | Ada (12, 13) |
| Nilai ke-N tertinggi | Gak ada | Ada (14, 15) |
| Persentil, kuartil | Gak ada | Ada (16-19) |
| Ada di Google Sheets | Ada | Gak ada |
Aturan praktisnya: data bersih dan cuma butuh total ikut filter? Pakai SUBTOTAL, lebih pendek dan lebih gampang dibaca orang lain.
Ada sel error, atau butuh median dan top-N? Pakai AGGREGATE.
=AGGREGATE(14; 6; F2:F100) bakal error. LARGE gak tau kamu mau nilai ke berapa. Tambahin angkanya di akhir.
=AGGREGATE(9; 6; F2:F100; 1) juga error. SUM gak nerima argumen keempat.
Opsi 6 cuma buat error. Kalau kamu juga mau ngabaikan baris yang di-hide manual, pakai opsi 7.
Ini yang paling bahaya. Kalau di kolom kamu ada 200 sel error, jangan buru-buru dilewatin. Cari tau dulu kenapa error. Mungkin ada masalah di data sumbernya yang lebih penting dari angka totalnya.
AGGREGATE buat error yang emang gak bisa dihindari, bukan buat nyembunyiin masalah.
AGGREGATE gak ada di Sheets. File kamu bakal error waktu dibuka di sana. Kalau kolaborasinya di Sheets, pakai IFERROR plus SUBTOTAL sebagai gantinya.
SUBTOTAL punya 11 fungsi dan gak bisa lewatin sel error. Satu sel #DIV/0! di dalam range bikin seluruh hasilnya jadi error. AGGREGATE punya 19 fungsi, termasuk LARGE, SMALL, MEDIAN, dan PERCENTILE. Dia juga punya argumen opsi buat ngabaikan error dan baris tersembunyi. Kalau data kamu bersih, SUBTOTAL cukup.
Argumen keempat bernama k dan cuma wajib buat 5 fungsi: LARGE (14), SMALL (15), PERCENTILE.INC (16), QUARTILE.INC (17), dan pasangan versi EXC-nya. Nilai k nentuin urutan ke berapa yang mau diambil, misalnya k=3 buat nilai tertinggi ketiga. Buat SUM atau AVERAGE, argumen ini gak usah ditulis.
Opsi 6 kalau kamu cuma mau ngabaikan sel error tapi tetap ngitung semua baris. Opsi 7 kalau mau ngabaikan error dan baris yang di-hide manual. Kalau laporanmu pakai AutoFilter dan ada sel error, pakai opsi 6, soalnya baris kena filter emang selalu diabaikan tanpa perlu opsi khusus.
AGGREGATE muncul mulai Excel 2010 dan tersedia di semua versi setelahnya, termasuk Excel 365 dan Excel untuk Mac. Google Sheets gak punya fungsi ini, jadi kalau file kamu bakal dibuka di Sheets, pakai kombinasi IFERROR dan SUBTOTAL sebagai gantinya.
Bisa, dan ini kasus paling kuat buat AGGREGATE. Rumusnya =AGGREGATE(14; 6; range; 1) buat ambil nilai tertinggi sambil melewati semua sel error. MAX biasa bakal langsung error kalau ada satu sel #N/A di range-nya. Ganti angka 1 di akhir jadi 2 buat ambil nilai tertinggi kedua.
Yang perlu nempel:
=AGGREGATE(9; 6; range): total yang lewatin sel error. Hafalin pola ini.Buka laporan yang paling sering error-nya. Ganti satu AVERAGE jadi AGGREGATE(1; 6; ...), terus liat angkanya akhirnya keluar.
Latihan lengkapnya ada di halaman fungsi AGGREGATE. Kalau kamu belum ngerti bedanya median dan rata-rata, ada penjelasannya di glosarium median.
Lanjut baca: Rumus LARGE dan SMALL Excel (versi berdiri sendiri dari fungsi 14 dan 15 di atas).
Mau praktek langsung? Mulai latihan SQL gratis
Latihan interaktif, langsung di browser.
Cara pakai rumus FV dan PV Excel buat hitung nilai uang di masa depan dan sekarang. Lengkap dengan contoh tabungan dan target modal UMKM.
Investasi kelihatan untung di atas kertas, tapi nilai uang berubah tiap tahun. NPV dan IRR Excel bantu kamu nilai apakah proyek beneran layak, bukan cuma kelihatan untung.
Sebelum ambil pinjaman, kamu bisa hitung sendiri cicilan bulanannya pakai satu rumus. Ini cara PMT Excel bekerja, lengkap dengan contoh modal usaha dan jebakan bunga tahunan.