Saat lembar kerja Excel Anda berisi baris tersembunyi, data yang difilter, atau data yang dikelompokkan, gunakan fungsi SUBTOTAL Excel. Fungsi SUBTOTAL dapat menyertakan atau mengecualikan nilai tersembunyi dalam perhitungan. Selain menemukan total grup data, Excel dapat menghitung rata-rata, maksimum, minimum, standar deviasi, dan varians data Anda. Berikut cara menyisipkan subtotal di Excel.
Petunjuk dalam artikel ini berlaku untuk Excel untuk Microsoft 365, Excel 2019, dan Excel 2016.
Sintaks Fungsi SUBTOTAL
Gunakan fungsi SUBTOTAL di Excel untuk meringkas nilai dalam lembar kerja dengan cara yang berbeda. Ini sangat membantu ketika lembar kerja Anda berisi baris tersembunyi yang ingin Anda sertakan dalam perhitungan.
Sintaks dari fungsi SUBTOTAL adalah: SUBTOTAL(function_num, ref1, ref2, …)
Argumen function_num diperlukan dan menentukan jenis operasi matematika yang akan digunakan untuk subtotal. Fungsi SUBTOTAL dapat menambahkan angka, menghitung nilai rata-rata dari angka yang dipilih, menemukan nilai maksimum dan minimum dalam suatu rentang, menghitung jumlah nilai dalam rentang yang dipilih, dan banyak lagi.
Fungsi SUBTOTAL mengabaikan sel yang tidak berisi data dan sel dengan nilai non-numerik.
Argumen ini adalah angka dan tergantung pada apakah Anda ingin menyertakan baris tersembunyi dalam hasil atau mengecualikan baris tersembunyi dari hasil. Baris ini mungkin disembunyikan atau disembunyikan secara manual oleh filter.
Argumen function_num meliputi:
| Tugas Fungsi | function_num | function_num |
|---|---|---|
| (termasuk nilai tersembunyi) | (tidak termasuk nilai tersembunyi) | |
| RATA-RATA | 1 | 101 |
| COUNT | 2 | 102 |
| COUNTA | 3 | 103 |
| MAX | 4 | 104 |
| MIN | 5 | 105 |
| PRODUK | 6 | 106 |
| STDEV | 7 | 107 |
| STDEVP | 8 | 108 |
| SUM | 9 | 109 |
| VAR | 10 | 110 |
| VARP | 11 | 111 |
Argumen referensi function_num 1 hingga 11 hanya menyertakan nilai dalam baris tersembunyi saat menggunakan perintah Sembunyikan untuk menyembunyikan baris. Saat menggunakan perintah Filter, perhitungan SUBTOTAL tidak menyertakan hasil filter tersembunyi.
Argumen ref1 diperlukan. Ini adalah sel yang digunakan untuk menghitung hasil argumen function_num yang dipilih. Argumen ini dapat berupa nilai, sel tunggal, atau rentang sel.
Argumen ref2, … adalah opsional. Ini adalah sel tambahan yang termasuk dalam perhitungan.
Gunakan Fungsi SUBTOTAL dengan Baris Tersembunyi
Fungsi Excel dapat dimasukkan secara manual atau dengan bantuan kotak dialog Argumen Fungsi. Untuk mengilustrasikan cara memasukkan fungsi secara manual menggunakan bilah rumus, contoh berikut menggunakan argumen COUNT function_num untuk menghitung jumlah nilai di baris yang terlihat dan di baris yang terlihat dan tersembunyi.
Untuk menggunakan fungsi SUBTOTAL untuk menghitung jumlah baris dalam lembar kerja:
- Mulai dengan lembar kerja yang berisi beberapa baris data.
- Pilih sel yang akan berisi jumlah baris yang terlihat.
-
Di bilah fungsi, masukkan =SUBTOTAL. Saat Anda mengetik, Excel menyarankan sebuah fungsi. Klik dua kali fungsi SUBTOTAL.
Untuk menggunakan kotak dialog Argumen Fungsi untuk masuk ke fungsi SUBTOTAL, buka Formulas dan pilih Math & Trig >SUBTOTAL.
Image -
Dalam menu tarik-turun yang muncul, klik dua kali argumen 102 – COUNT function_num.
Image -
Ketik koma (,).
Image -
Di lembar kerja, pilih sel yang akan disertakan dalam rumus.
Image -
Tekan Enter untuk melihat hasil di sel yang Anda pilih pada langkah 2.
Image - Pilih sel yang akan berisi jumlah baris yang terlihat dan tersembunyi.
- Di bilah fungsi, masukkan =SUBTOTAL. Saat Anda mengetik, Excel menyarankan sebuah fungsi. Klik dua kali fungsi SUBTOTAL.
-
Pada menu drop-down yang muncul, klik dua kali argumen 2 – COUNT function_num, lalu ketik koma (,).
-
Di lembar kerja, pilih sel yang akan disertakan dalam rumus, lalu tekan Enter.
Image -
Sembunyikan beberapa baris data. Dalam contoh ini, baris dengan hanya penjualan di bawah $100.000 disembunyikan.
Image
Gunakan Fungsi SUBTOTAL dengan Data yang Difilter
Menggunakan fungsi SUBTOTAL pada data yang difilter akan mengabaikan data dalam baris yang telah dihapus oleh filter. Setiap kali kriteria filter berubah, fungsi menghitung ulang untuk menampilkan subtotal untuk baris yang terlihat.
Untuk menggunakan fungsi SUBTOTAL untuk melihat perbedaan hasil perhitungan saat memfilter data:
-
Buat rumus SUBTOTAL. Misalnya, buat rumus untuk menentukan nilai subtotal dan rata-rata dari data yang difilter.
Tidak masalah jika Anda menggunakan argumen function_num untuk baris yang terlihat atau tersembunyi. Kedua argumen memberikan hasil yang sama dalam data yang difilter.
Image - Pilih sel mana saja dalam kumpulan data.
-
Masuk ke Home, lalu pilih Sort & Filter > Filter.
Image -
Gunakan panah drop-down untuk memfilter data lembar kerja.
Image -
Perhatikan bagaimana nilai berubah setiap kali Anda memilih kriteria filter yang berbeda.
Image
Gunakan Fungsi SUBTOTAL dengan Data yang Dikelompokkan
Saat data dikelompokkan, ada cara untuk menerapkan fungsi SUBTOTAL ke setiap grup individu dan kemudian menghitung total keseluruhan untuk seluruh kumpulan data.
- Pilih sel mana saja dalam kumpulan data.
-
Pilih Data > Subtotal untuk membuka kotak dialog Subtotal.
Image - Pilih Pada setiap perubahan pada panah drop-down dan pilih pengelompokan yang akan dihitung setiap subtotalnya.
- Pilih Gunakan fungsi panah tarik-turun dan pilih jumlah_fungsi.
- Dalam daftar Tambahkan subtotal ke, pilih kolom tempat rumus akan diterapkan.
-
Pilih OK.
Image -
Subtotal disisipkan untuk setiap grup data, dan total keseluruhan disisipkan di bagian bawah kumpulan data.
Image - Untuk mengubah function_num, sorot sel mana pun dalam kumpulan data dan pilih Data > Subtotal. Kemudian, tentukan pilihan Anda di kotak dialog Subtotal.
