Anda disini: M Jurnal » Rumus Excel » Rumus SUBTOTAL Excel, Aturan Penulisan + Contoh Lengkap

Rumus SUBTOTAL Excel, Aturan Penulisan + Contoh Lengkap

Rumus SUBTOTAL Excel termasuk kedalam kategori Fungsi Math & Trig yang berguna untuk menghitung subtotal dari sebuah daftar atau tabel data.

Maksud menghitung subtotal seperti ini: Rumus SUBTOTAL hanya akan menghitung nilai pada cell yang terlihat saja dan mengabaikan nilai dari cell yang disembunyikan (hidden cell) menggunakan fitur Filter maupun secara manual.

Sebelum belajar cara kerja dan cara menggunakan Rumus SUBTOTAL Excel, ada baiknya pahami dahulu apa yang akan Anda dapatkan ketika menggunakan Rumus ini.

Mulailah dari bagian pertama: Perbedaan SUBTOTAL dengan SUM, AVERAGE dll…

Apa Perbedaan SUBTOTAL dengan SUM, AVERAGE dll. ?

Dalam penerapannya, Rumus SUBTOTAL dapat menggantikan 11 fungsi Excel lainnya yaitu: AVERAGE, COUNT, COUNTA, MAX, MIN, PRODUCT, STDEV.S, STDEV.P, SUM, VAR.S, VAR.P.

Penting! Sebelumnya Saya sudah membahas tentang penggunaan Rumus COUNT & SUM dkk (lihat Pintasan Panduan).

Saya yakin Anda sudah tahu ini: Rumus SUM, AVERAGE dll pasti akan menyertakan cell yang tersembunyi kedalam perhitungan.

Sementara Rumus SUBTOTAL akan mengabaikannya. Misalnya seperti ini…

Perbedaan SUBTOTAL Excel dan Rumus SUM

Saat ini tidak ada baris yang disembunyikan.

Selanjutnya, coba sembunyikan baris 4 dan 5 menggunakan Fitur AutoFilter atau sembunyikan secara manual. Dan lihat hasilnya:

Penting! Saya juga menulis panduan lengkap tentang penggunaan fitur AutoFilter Excel dan cara menyembunyikan cell secara manual, selengkapnya silahkan kunjungi SUB-BAB Filter & AutoFilter dan SUB-BAB Hide Columns & Rows.

Hasil Rumus SUBTOTAL ketika menggunakan Filter Excel

Hasilnya beda kan ? Ada perbedaan hasil dari rumus SUM dan AVERAGE dengan Rumus SUBTOTAL.

Seperti hasil SUBTOTAL SUM pada Cell C10 yaitu 20. Ini hasil dari 2+8+10.

Begitu juga dengan SUBTOTAL AVERAGE pada cell C12. Ini hasil dari (2+8+10)/3 = 20/3 = 6.6667.

Namun, ada sedikit aturan dalam penggunaan Rumus SUBTOTAL Excel. Jadi pahami dulu aturannya ya…

Aturan Penulisan Rumus SUBTOTAL Excel

Sama seperti Rumus Excel lainnya, Rumus SUBTOTAL juga memiliki aturan penulisan tersendiri yang terdiri dari beberapa argumen seperti berikut:

Tulis Rumus / Fungsi SUBTOTAL:
=
SUBTOTAL(function_num,ref1, )

  1. function_num (wajib) merupakan kode berupa angka dari 1 sampai 11 atau 101 sampai 111 yang akan menentukan Fungsi mana yang digunakan dalam rumus SUBTOTAL.
  2. ref1 (wajib) merupakan satu atau beberapa cell, range, ataupun sebuah rumus yang akan dihitung subtotalnya.
  3. (optional) merupakan argumen optional yang memiliki makna sama dengan argumen ref1. Argumen ini berguna jika Anda memiliki data terpisah yang akan dihitung subtotalnya.

Jika Anda sudah paham argumen dalam rumus SUM, AVERAGE dll, tentu tidak akan asing lagi dengan argumen ref1 dan dalam rumus SUBTOTAL ini.

Jadi, argumen ref1 dan … pada rumus =SUBTOTAL(function_num,ref1,...) memiliki arti yang sama dengan argumen =SUM(number1,[number2],...).

Cara Menghitung SUBTOTAL di Excel

Penting! Sebelum melanjutkan, ketahui bahwa operator pemisah rumus untuk Software Excel format English berbeda dengan format Indonesia ( , atau ; ). Software Excel Saya menggunakan Format English. Jika masih ragu, silahkan kunjungi SUB-BAB Format English vs Indonesia

Untuk menghitung SUBTOTAL, silahkan ikuti tahap-tahap berikut:

Cara menghitung SUBTOTAL di Excel
  1. Pertama, ketik rumus SUBTOTAL seperti berikut: =SUBTOTAL(
  2. Kedua, masukkan argumen function_num. Dalam contoh ini Saya menggunakan 109 untuk SUM. Kemudian ketik operator pemisah rumus ( , atau ; ). Note: Jangan bingung tentang kode 9 dan 109 untuk SUM. Nanti akan Saya jelaskan.
  3. Ketiga, masukkan argumen ref1. Dalam contoh ini adalah Range C3:C7.
  4. Terakhir, jika masih ada data yang akan dihitung, maka masukkan argumen ref2 dan seterusnya dengan cara yang sama. Jika tidak ada, ketik tanda tutup kurung dan tekan Enter. Lihat hasilnya:
Contoh Menghitung SUBTOTAL di Excel

Selanjutnya, coba Kita buktikan…

Silahkan gunakan Fitur AutoFilter, kemudian sembunyikan Row 4 dan 5. Ini hasilnya:

Menggunakan AutoFilter pada Rumus SUBTOTAL Excel

Mana Yang terbaik: Function Num 1-11 atau 101-111 ?

Pada dasarnya, menyembunyikan cell bisa dengan 2 cara yaitu menggunakan fitur AutoFilter dan Manual.

Jika Anda perhatikan, Function Num 1 dan 101 sama-sama untuk Fungsi AVERAGE, function num 9 dan 109 untuk Rumus SUM, begitu juga dengan function num lainnya.

Namun, kemampuan function num 1-11 berbeda dengan 101-111. Perhatikan tabel berikut:

Function NumFungsiAutoFilterManual
1AVERAGE
2COUNT
3COUNTA
4MAX
5MIN
6PRODUCT
7STDEV.S
8STDEV.P
9SUM
10VAR.S
11VAR.P
101AVERAGE
102COUNT
103COUNTA
104MAX
105MIN
106PRODUCT
107STDEV.S
108STDEV.P
109SUM
110VAR.S
111VAR.P
Tabel: Function Num pada rumus SUBTOTAL Excel

Jadi, Function Num 1-11 hanya mengabaikan baris yang difilter menggunakan fitur AutoFilter. Sedangkan fungtion num 101-111 akan mengabaikan baris yang difilter menggunakan fitur AutoFilter serta mengabaikan baris yang disembunyikan secara manual.

Bukti Perbedaan Hasil Function Num 1-11 dan 101-111

Misalnya, Saya menggunakan contoh berikut:

Perbedaan hasil Function Num 1-11 dengan 101-111

Note: Saya menggunakan function num 9 dan 1 pada cell B13 dan B14. Sedangkan cell C13 dan C14 menggunakan 109 dan 101.

Selanjutnya, filter data menggunakan fitur AutoFilter kolom nama (B) dan sembunyikan Nama Dedek dan Ade. Ini hasilnya:

Perbedaan hasil Function Num 1-11 dengan 101-111

Sejauh ini, function num 1-11 dan 101-111 memberikan hasil yang sama.

Penting! Saya menggunakan bantuan Rumus IF ini =IF(C13=B13,"Sama","Beda") pada Cell D13 dan =IF(C14=B14,"Sama","Beda") pada Cell D14 untuk menguji apakah kedua cell tersebut memiliki nilai yang sama. Pelajari selengkapnya tentang Rumus IF pada SUB-BAB IF Function Lengkap.

Selanjutnya, hilangkan AutoFilter dan coba Sembunyikan Baris 4 dan 5 Secara Manual. Lihat hasilnya:

Perbedaan hasil Function Num 1-11 dengan 101-111 dalam RUmus SUBTOTAL Excel

Hasilnya beda kan ?

Hasil Function Num 1-11 tidak mengabaikan cell tersembunyi (manual). Hasilnya sama saja dengan Rumus SUM dan AVERAGE biasa pada Cell B9 dan B10.

Sebaliknya function num 101-111 mengabaikan cell tersembunyi (manual).

Penting! Jika Saya harus memilih, maka Saya akan menggunakan function num 101-111. Karena secara default, Excel juga menggunakan function num ini untuk Format Tabel Bernama.

Saya rasa tidak sulit memahami materi Rumus SUBTOTAL Excel ini.

Jadi jika Anda menemukan kendala atau menemukan trik lain, silahkan coret-coret kolom komentar.

Selanjutnya, silahkan pelajari materi menarik lainnya tentang COUNT & SUM Excel melalui Pintasan Panduan Berikut:

Pintasan Panduan COUNT & SUM Excel

  1. COUNT & SUM: Jenis-jenis dan cara menggunakan Fungsi Count dan SUM di Excel (COUNT, COUNTIF, COUNTIFS, SUM, SUMIF, SUMIFS)
  2. COUNT: Cara menggunakan Fungsi COUNT untuk menghitung banyak cell yang berisi data numeric (angka, tanggal, waktu, persen, pecahan).
  3. COUNTA: Cara menggunakan Fungsi COUNTA untuk menghitung banyak cell yang berisi saja (tidak kosong).
  4. COUNTBLANK: Cara menggunakan Fungsi COUNTBLANK untuk menghitung banyak Cell kosong dari Range Data dengan cepat.
  5. COUNTIF: Contoh Lengkap cara menggunakan rumus COUNTIF (Kriteria berdasarkan angka, teks, menghitung error, dll)
  6. COUNTIFS: Cara menggunakan Fungsi COUNTIFS untuk menghitung banyak data dengan 2 atau lebih kriteria.
  7. SUM: Fungsi SUM Lengkap (seperti menjumlahkan Range tertentu, semua kolom, cell yang berjauhan, angka besar, menjumlahkan otomatis, menjumlahkan setiap baris ke-n, dll)
  8. SUMIF: Cara menggunakan Fungsi SUMIF untuk menjumlahkan dengan syarat (Lengkap)
  9. SUMIFS: Cara Menggunakan Fungsi SUMIFS untuk menjumlahkan data dengan banyak Syarat / kriteria.
  10. SUBTOTAL: (Anda Disini).
  11. Running Total: Menjumlahkan total berjalan (Total berjalan akan berubah setiap nilai baru di tambahkan)
  12. SUMPRODUCT: Cara menjumlahkan yang lebih kompleks (pahami setiap contohnya)
  13. Count Characters: Cara menghitung jumlah karakter dalam Cell atau Range dan cara menghitung berapa kali karakter tertentu muncul dalam Cell atau Range. [Bonus] Kombinasi Fungsi SUM, LEN, SUBSTITUTE dan LOWER.
  14. Count Cells with Text: Alternatif Fungsi COUNTIF. Trik menghitung jumlah Cell yang berisi huruf dengan kombinasi Fungsi SUM, IF, ISTEXT, SUMPRODUCT, ISLOGICAL dan COUNTIFS.

Komentar Paling Lambat di Balas Pukul 23:59.