Cara membuat subtotal di excel

Tutorial » Excel 2013 » Formula dan fungsi » ...

Anda pasti sering mengelola data pada Worksheet dan membaginya menjadi beberapa kriteria. Misalnya, Anda memiliki Worksheet yang Anda gunakan untuk menyimpan data penjualan tahunan untuk setiap produk yang Anda jual, dengan produk yang dibagi menjadi beberapa kategori. Jika data Anda terorganisir seperti itu, Excel bisa menghitung subtotal untuk masing-masing kategori produk. Ketika Anda membuat subtotal, Anda akan menentukan cell yang memiliki nilai untuk dihitung, dan cell yang mengidentifikasikan perubahan dari satu kategori ke kategori lainnya. Excel akan mengupdate subtotal dan grand total jika nilai pada cell tersebut berubah.

Cara membuat subtotal:

1. Klik cell dalam rentang yang ingin Anda buat subtotal.
2. Klik tab Data.
3. Di grup Outline, klik Subtotal.
4. Klik tanda-panah-kebawah At Each Change In.
5. Klik nilai yang ingin Anda jadikan dasar dari subtotal.
6. Klik tanda-panah-kebawah Use Function.
7. Klik fungsi subtotal yang ingin Anda gunakan.
8. Pilih kolom mana yang dihitung subtotalnya.
9. Klik OK.
   

Cara menghapus subtotal:

1. Klik cell pada rentang subtotal.
2. Klik tab Data.
3. Klik Subtotal.
4. Klik Remove All.
   

Artikel terkait:

• Membuat Formula Cell Sederhana
• Membuat, Mengedit, Dan Menghapus Named Range
• Membuat Formula Menggunakan Named Range
• Membuat Formula Yang Mereferensikan Nilai Di Dalam Tabel
• Membuat Formula Menggunakan Cell Dari Workbook Lain
• Mengedit Link Ke Workbook Lain
• Menganalisis Data Menggunakan Quick Analysis
• Mengetahui Nilai Ringkasan Cell Tanpa Menggunakan Formula
• Membuat Formula AutoSum
• Membuka Dan Melihat Daftar Fungsi Yang Tersedia Di Excel 2013
• Membuat Fungsi IF
• Mencari Cell Yang Digunakan Pada Formula
• Memonitor Dan Mengevaluasi Perubahan Formula

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. ?
• Aturan Penulisan Rumus SUBTOTAL Excel
• Cara Menghitung SUBTOTAL di Excel
• Mana Yang terbaik: Function Num 1-11 atau 101-111 ?
  • Bukti Perbedaan Hasil Function Num 1-11 dan 101-111
• Pintasan Panduan COUNT & SUM Excel

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…

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.

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…

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:

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:

Selanjutnya, coba Kita buktikan…

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

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:

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:

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:

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:

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

×

"Hidup ini singkat - bermimpilah yang besar dan wujudkan impianmu di tahun 2022 sebaik- baiknya!"

Bagaimana cara membuat subtotal?

Bagaimana cara menghitung subtotal?

Rumus subtotal Excel untuk apa?

Bagaimana cara grouping di Excel?