Rumus SUBTOTAL() di Excel: Panduan Perhitungan Lengkap

Pengguna Excel sering memerlukan cara fleksibel untuk meringkas data yang responsif terhadap filter atau pengelompokan. Fungsi tradisional seperti SUM(), AVERAGE(), atau COUNT() menghitung hasil berdasarkan semua sel dalam suatu rentang, terlepas dari apakah beberapa baris disembunyikan atau difilter. Fungsi SUBTOTAL() menawarkan alternatif dinamis yang secara otomatis menyesuaikan perhitungannya berdasarkan apa yang saat ini terlihat di lembar kerja Anda.

Jika Anda baru mengenal Excel dan ingin membangun dasar yang kuat sebelum mengeksplorasi fungsi lanjutan seperti SUBTOTAL(), kursus Introduction to Excel kami mencakup keterampilan esensial termasuk navigasi lembar kerja, rumus dasar, dan teknik pemformatan data yang akan mempersiapkan Anda untuk pekerjaan Excel yang lebih canggih.

Apa Itu Fungsi SUBTOTAL() di Excel?

Fungsi SUBTOTAL() menghitung nilai agregat seperti jumlah, rata-rata, hitung, dan operasi statistik lainnya pada sebuah dataset. Yang membedakannya dari fungsi standar Excel adalah cara kerjanya yang dinamis—mengubah hasilnya berdasarkan apa yang terlihat atau terfilter. Fungsi ini dapat mengabaikan baris yang disembunyikan secara manual maupun baris yang disembunyikan melalui filter, bergantung pada nomor fungsi yang Anda tentukan.

Saat Anda menerapkan filter pada data atau menyembunyikan baris tertentu secara manual, SUBTOTAL() secara otomatis menyesuaikan perhitungannya untuk hanya menyertakan sel yang terlihat. Perilaku ini menjadikannya pilihan tepat untuk membuat baris ringkasan dalam dataset terfilter, membangun dasbor interaktif, atau membuat laporan yang perlu diperbarui berdasarkan pilihan pengguna.

Fungsi ini mendukung operasi statistik yang sama seperti fungsi standar Excel—termasuk jumlah, rata-rata, hitung, maksimum, minimum, dan beberapa lainnya—tetapi mengemasnya ke dalam satu fungsi yang peka konteks dan beradaptasi dengan kondisi data Anda saat ini.

Sintaks dan Operasi yang Didukung

Fungsi SUBTOTAL() mengikuti struktur sintaks yang mudah:

=SUBTOTAL(function_num, ref1, [ref2], ...)

Parameter pertama, function_num menentukan jenis perhitungan sekaligus bagaimana fungsi menangani baris tersembunyi. Parameter berikutnya (ref1, ref2, dst.) adalah rentang sel yang ingin Anda sertakan dalam perhitungan. Anda dapat menentukan hingga 254 rentang berbeda.

Nomor fungsi terbagi dalam dua kategori berbeda berdasarkan cara memperlakukan baris yang disembunyikan secara manual:

Nomor fungsi 1-11 menyertakan sel dari baris yang disembunyikan secara manual dalam perhitungannya, sedangkan nomor fungsi 101-111 mengecualikannya. Namun, kedua rentang tersebut selalu mengabaikan baris yang disembunyikan oleh filter.

Sebagai contoh, SUBTOTAL(9, A2:A10) menghitung jumlah A2:A10 termasuk baris yang disembunyikan manual, sedangkan SUBTOTAL(109, A2:A10) mengecualikan baris yang disembunyikan manual dari perhitungan jumlah. Pada kedua kasus, baris yang terfilter otomatis dikecualikan dari hasil.

Contoh Fungsi SUBTOTAL() dalam Aksi

Mari kita lihat cara kerja SUBTOTAL() dengan contoh praktis menggunakan data penjualan dari toko elektronik dan furnitur.

Dataset tanpa filter. Gambar oleh Penulis. 

Data contoh kami berisi tujuh produk dalam dua kategori - empat item Elektronik (Laptop, Ponsel, Tablet, Monitor) dan tiga item Furnitur (Meja, Kursi, Lampu). Tanpa filter diterapkan, baik rumus SUBTOTAL(109,C2:C8) maupun rumus SUM(C2:C8) biasa menampilkan hasil yang sama: 3525 (total seluruh penjualan).

Dataset terfilter. Gambar oleh Penulis. 

Sekarang kami menerapkan filter untuk menampilkan hanya produk Elektronik, menyembunyikan tiga baris Furnitur. Perhatikan perbedaan utama pada hasil perhitungan:

• Rumus SUBTOTAL menampilkan 3000 - secara otomatis menyesuaikan untuk menjumlahkan hanya baris Elektronik yang terlihat
• Rumus SUM biasa masih menampilkan 3525 - tetap menyertakan item Furnitur yang tersembunyi

Perbandingan berdampingan ini menunjukkan keunggulan utama SUBTOTAL(): ia merespons secara dinamis terhadap filter dan perubahan visibilitas data, sementara fungsi tradisional seperti SUM() tetap statis. Saat Anda memfilter data atau menyembunyikan baris, SUBTOTAL() langsung menghitung ulang untuk mencerminkan hanya apa yang saat ini terlihat, menjadikannya esensial untuk dasbor yang responsif dan laporan interaktif.

Cara Kerja SUBTOTAL() dengan Data Tersembunyi dan Terfilter

Fungsi SUBTOTAL() menangani berbagai jenis data tersembunyi dengan cara tertentu, sehingga penting memahami perilaku ini saat membangun rumus Anda.

SUBTOTAL() dengan baris terfilter

Saat Anda menerapkan filter pada dataset, SUBTOTAL() selalu mengecualikan baris yang terfilter dari perhitungannya, terlepas dari nomor fungsi yang Anda gunakan. Perilaku ini otomatis dan konsisten di semua nomor fungsi (baik rentang 1-11 maupun 101-111).

Misalnya, jika Anda memiliki data penjualan dari Januari hingga Desember dan menerapkan filter untuk menampilkan hanya bulan Q1 (Januari, Februari, Maret), SUBTOTAL(9, B2:B13) akan menghitung jumlah hanya untuk tiga bulan yang terlihat tersebut, mengabaikan data Q2-Q4 yang terfilter.

SUBTOTAL() dengan baris yang disembunyikan manual

Baris yang disembunyikan manual ditangani berbeda tergantung pilihan nomor fungsi Anda. Saat Anda klik kanan nomor baris dan memilih "Hide", Anda menyembunyikan baris tersebut secara manual.

Menggunakan nomor fungsi 1-11 akan menyertakan baris yang disembunyikan manual dalam perhitungan. Menggunakan nomor fungsi 101-111 akan mengecualikannya. Ini memberi Anda kendali apakah data tersembunyi harus memengaruhi hasil atau tidak.

SUBTOTAL() dengan rentang horizontal dan vertikal

Fungsi SUBTOTAL() paling baik digunakan dengan rentang data vertikal (kolom data). Meskipun dapat menangani rentang horizontal, perilaku penyembunyiannya dioptimalkan untuk operasi berbasis baris karena fitur filter dan sembunyikan baris di Excel bekerja secara vertikal.

Saat Anda menumpuk rumus SUBTOTAL() dalam rentang yang sama, fungsi ini secara otomatis melewati hasil SUBTOTAL() lain untuk menghindari penghitungan ganda. Ini sangat berguna saat membuat ringkasan hierarkis atau subtotal dalam grup, memastikan total Anda secara akurat mencerminkan setiap pengelompokan tanpa duplikasi.

Penggunaan Lanjutan Fungsi SUBTOTAL()

Membuat ringkasan dropdown dinamis dengan SUBTOTAL()

Anda dapat menggabungkan SUBTOTAL() dengan fungsi IF() untuk membuat ringkasan interaktif yang berubah berdasarkan pilihan pengguna. Pendekatan ini menggunakan daftar dropdown untuk memungkinkan pengguna beralih antar jenis perhitungan.

Untuk membuat ringkasan dropdown dinamis, pertama buat dropdown di sel D1 dengan opsi seperti "Sum", "Average", "Count", dan "Max".

Lalu, gunakan rumus berikut di sel lain (misalnya, D2) untuk menyesuaikan perhitungan secara dinamis berdasarkan pilihan pengguna:

=IF(D1="Sum",SUBTOTAL(109,A2:A10),
  IF(D1="Average",SUBTOTAL(101,A2:A10),
  IF(D1="Count",SUBTOTAL(103,A2:A10),
  IF(D1="Max",SUBTOTAL(104,A2:A10),"Select Option"))))

Rumus ini mengevaluasi pilihan dropdown dan menerapkan fungsi SUBTOTAL() yang sesuai. Pengguna dapat beralih antar jenis perhitungan tanpa mengedit rumus, membuat laporan lebih interaktif dan mudah digunakan.

SUBTOTAL() di tabel dan outline Excel

Tabel Excel secara otomatis menggunakan SUBTOTAL() pada fitur Total Row. Saat Anda mengonversi rentang data menjadi Tabel (Ctrl+T) dan mengaktifkan Total Row, Excel menyisipkan SUBTOTAL(109, …) secara default untuk perhitungan jumlah. Total Row ini akan diperbarui secara dinamis setiap kali baris difilter, langsung menyesuaikan hasil agar hanya mencerminkan data yang terlihat.

Anda dapat mengubah jenis perhitungan dengan mengklik panah dropdown di sel Total Row mana pun dan memilih opsi berbeda seperti Count, Average, Max, atau Min. Excel secara otomatis memperbarui nomor fungsi sambil mempertahankan struktur SUBTOTAL().

Fitur Outline (Data > Subtotal) juga bergantung pada rumus SUBTOTAL(). Saat Anda mengelompokkan data dan menyisipkan subtotal, Excel secara otomatis menempatkan fungsi SUBTOTAL() di setiap jeda grup. Ini menciptakan ringkasan hierarkis yang merespons dengan benar saat Anda memperluas atau menciutkan level outline, karena fungsi tersebut mengabaikan hasil subtotal yang bertingkat.

Jebakan Umum dan Batasan

Memahami kesalahan umum dan batasan SUBTOTAL() membantu Anda menghindari kekeliruan perhitungan dan memilih pendekatan yang tepat untuk kebutuhan analisis data Anda.

• Kesalahan #VALUE! dengan nomor fungsi tidak valid: Kesalahan paling sering terjadi saat menggunakan nomor fungsi yang tidak valid. SUBTOTAL() hanya menerima nomor fungsi 1-11 dan 101-111. Menggunakan angka seperti 12, 50, atau 200 akan menghasilkan kesalahan #VALUE!. Selalu pastikan nomor fungsi Anda sesuai dengan operasi yang valid dari rentang yang didukung.

• Perilaku kolom tersembunyi vs. baris tersembunyi: Meskipun SUBTOTAL() merespons baris tersembunyi berdasarkan pilihan nomor fungsi Anda, fungsinya tidak berperilaku sama pada kolom tersembunyi. Fungsi ini menyertakan data dari kolom tersembunyi dalam semua perhitungan, terlepas dari apakah Anda menggunakan nomor fungsi 1-11 atau 101-111. Batasan ini berarti Anda memerlukan pendekatan alternatif saat bekerja dengan dataset di mana visibilitas kolom memengaruhi analisis.

• Referensi 3D tidak didukung: Tidak seperti banyak fungsi Excel, SUBTOTAL() tidak dapat mereferensikan rentang di beberapa lembar kerja. Rumus seperti SUBTOTAL(109, Sheet1:Sheet3!A1:A10) akan menghasilkan kesalahan. Sebagai solusi, Anda dapat terlebih dahulu menghitung jumlah atau agregat lainnya secara terpisah di setiap lembar menggunakan fungsi standar seperti SUM(), lalu mengonsolidasikan hasil antara ini dengan satu SUBTOTAL di lembar ringkasan.

• Nilai kesalahan tetap dalam perhitungan: SUBTOTAL() tidak mengabaikan sel yang berisi nilai kesalahan seperti #N/A, #DIV/0!, atau #VALUE!. Kesalahan ini akan menyebar dalam perhitungan subtotal Anda, berpotensi membuat hasil keseluruhan tidak valid. Bersihkan data Anda dari kesalahan sebelum menerapkan SUBTOTAL(), atau pertimbangkan menggunakan fungsi AGGREGATE() yang dapat melewati nilai kesalahan.

• Orientasi data vertikal bekerja paling baik: Meskipun SUBTOTAL() dapat menangani rentang horizontal, desainnya dioptimalkan untuk struktur data vertikal. Fitur pemfilteran, pengurutan, dan penyembunyian Excel bekerja baris demi baris, membuat susunan vertikal lebih selaras dengan perilaku fungsi yang diharapkan.

Subtotal() vs. AGGREGATE()

Fungsi AGGREGATE() berperan sebagai alternatif yang ditingkatkan dari SUBTOTAL() dengan kemampuan tambahan untuk menangani kesalahan dan lebih banyak operasi statistik. Sementara SUBTOTAL() menawarkan 11 operasi dasar, AGGREGATE() menyediakan 19 fungsi berbeda termasuk persentil, kuartil, dan perhitungan median.

Keunggulan utama AGGREGATE() terletak pada kemampuan penanganan kesalahannya. Tidak seperti SUBTOTAL(), yang menyertakan nilai kesalahan dalam perhitungan, AGGREGATE() dapat secara otomatis melewati sel yang berisi kesalahan seperti #N/A, #DIV/0!, atau #VALUE!. Ini sangat berguna untuk dataset dengan data yang tidak lengkap atau bermasalah.

AGGREGATE() juga menawarkan kontrol lebih rinci atas apa yang harus diabaikan. Anda dapat mengonfigurasinya untuk melewati baris tersembunyi, subtotal bertingkat, nilai kesalahan, atau kombinasi mana pun dari elemen-elemen ini menggunakan parameter opsinya.

KiAT Pro Saat Menggunakan SUBTOTAL()

Selalu urutkan terlebih dahulu

Saat menggunakan fitur bawaan Data > Subtotal di Excel, urutkan data Anda terlebih dahulu berdasarkan kolom pengelompokan. Ini memastikan semua catatan terkait muncul bersama, menciptakan jeda grup yang rapi untuk perhitungan subtotal Anda. Data yang tidak diurutkan menghasilkan subtotal yang tersebar dan tidak memberikan ringkasan yang bermakna.

Gunakan label di baris pertama kolom

Tempatkan header deskriptif di baris pertama rentang data Anda sebelum menerapkan subtotal. Excel menggunakan label ini untuk membuat deskripsi subtotal yang bermakna dan membuat laporan Anda lebih mudah dipahami. Header kolom yang jelas juga membantu saat memilih rentang untuk rumus SUBTOTAL() manual.

Perhatikan penempatan ringkasan (di atas atau di bawah)

Alat Subtotal Excel menempatkan baris ringkasan di bawah setiap grup secara default, tetapi Anda dapat memilih untuk menempatkannya di atas. Pertimbangkan tujuan laporan Anda saat membuat pilihan ini. Baris ringkasan di atas grup cocok untuk dasbor eksekutif, sedangkan baris ringkasan di bawah grup selaras dengan format akuntansi tradisional.

Sembunyikan vs. filter baris untuk kontrol presisi

Pahami perbedaan antara memfilter dan menyembunyikan baris secara manual untuk mendapatkan hasil yang sesuai harapan. Gunakan filter saat Anda ingin SUBTOTAL() mengabaikan data tertentu sementara waktu. Gunakan penyembunyian manual (klik kanan > Hide) saat Anda menginginkan pengecualian yang lebih permanen, lalu pilih nomor fungsi 101-111 agar menghormati status tersembunyi.

Pendekatan ini memberi Anda kontrol berlapis: filter untuk tampilan data sementara, sembunyikan manual untuk pengecualian semi-permanen, dan pilih nomor fungsi yang sesuai untuk menghormati niat penyembunyian Anda.

Kesimpulan

Mempelajari penggunaan SUBTOTAL() secara efektif akan mengubah cara Anda melakukan analisis data di Excel. Alih-alih membuat perhitungan statis yang mudah rusak saat data berubah, Anda dapat membangun rumus tangguh yang beradaptasi otomatis terhadap tindakan pengguna dan modifikasi data. Keterampilan ini menjadi sangat berharga saat bekerja dengan dataset besar atau membuat laporan yang akan difilter dan dimanipulasi banyak pengguna dari waktu ke waktu.

Untuk membangun keahlian Excel yang komprehensif di luar fungsi individual seperti SUBTOTAL(), kami Excel Fundamentals menawarkan jalur pembelajaran terstruktur selama 16 jam yang membawa Anda dari penyiapan data hingga analisis dan teknik visualisasi lanjutan. Bagi pembaca yang siap langsung mengeksplorasi aplikasi analitik, kursus Data Analysis in Excel kami mengajarkan penguasaan PivotTable dan fungsi logika lanjutan untuk wawasan yang lebih mendalam.