Rumus AGGREGATE Excel: Versi Upgrade dari SUBTOTAL

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.

Apa itu rumus AGGREGATE di Excel?

AGGREGATE adalah fungsi Excel yang menjalankan salah satu dari 19 fungsi agregat, dengan opsi buat mengabaikan sel error, baris tersembunyi, dan rumus SUBTOTAL lain di dalam range-nya.

Sintaksnya:

=AGGREGATE(nomor_fungsi; opsi; range; [k])

Empat bagian:

• nomor_fungsi — mau ngitung apa (1 sampai 19)
• opsi — apa yang mau dilewati (0 sampai 7)
• range — data yang diproses
• k — cuma buat 5 fungsi tertentu, misalnya nilai ke berapa

Contoh paling sering dipakai:

=AGGREGATE(9; 6; D2:D500)

Artinya: jumlahkan (9), lewati sel error (6), dari range D2:D500.

Apa aja 19 nomor fungsi AGGREGATE?

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.

Apa aja opsi AGGREGATE?

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.

Contoh kasus: laporan margin toko_berkah yang penuh error

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.

Nilai margin tertinggi, tanpa kena error

=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!.

Median, yang gak dipunyai SUBTOTAL

=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.

AGGREGATE vs SUBTOTAL: pilih yang mana?

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.

Kesalahan umum

1. Lupa argumen k di fungsi 14–19

=AGGREGATE(14; 6; F2:F100) bakal error. LARGE gak tau kamu mau nilai ke berapa. Tambahin angkanya di akhir.

2. Ngasih argumen k padahal gak perlu

=AGGREGATE(9; 6; F2:F100; 1) juga error. SUM gak nerima argumen keempat.

3. Ngira opsi 6 juga ngabaikan baris di-hide

Opsi 6 cuma buat error. Kalau kamu juga mau ngabaikan baris yang di-hide manual, pakai opsi 7.

4. Pakai AGGREGATE buat nutupin bug

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.

5. Bikin file buat orang yang pakai Google Sheets

AGGREGATE gak ada di Sheets. File kamu bakal error waktu dibuka di sana. Kalau kolaborasinya di Sheets, pakai IFERROR plus SUBTOTAL sebagai gantinya.

FAQ

Apa bedanya AGGREGATE dan SUBTOTAL?

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, plus argumen opsi buat ngabaikan error dan baris tersembunyi. Kalau data kamu bersih, SUBTOTAL cukup.

Kenapa AGGREGATE minta argumen keempat?

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 mana yang paling aman dipakai di AGGREGATE?

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 ada di Excel versi berapa?

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 nggak AGGREGATE dipakai buat ambil nilai tertinggi tanpa kena error?

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.

Penutup

Yang perlu nempel:

1. =AGGREGATE(9; 6; range) — total yang lewatin sel error. Hafalin pola ini.
2. Nomor 12–19 (median, top-N, kuartil) gak ada di SUBTOTAL. Itu alasan utama pindah.
3. Fungsi 14–19 wajib pakai argumen k di akhir. Sisanya gak boleh.

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.