Apabila lembaran kerja Excel anda mengandungi baris tersembunyi, data yang ditapis atau data terkumpul, gunakan fungsi Excel SUBTOTAL. Fungsi SUBTOTAL boleh memasukkan atau mengecualikan nilai tersembunyi dalam pengiraan. Selain mencari jumlah kumpulan data, Excel boleh mengira purata, maksimum, minimum, sisihan piawai dan varians data anda. Begini cara untuk memasukkan subjumlah dalam Excel.
Arahan dalam artikel ini digunakan pada Excel untuk Microsoft 365, Excel 2019 dan Excel 2016.
Sintaks Fungsi SUBTOTAL
Gunakan fungsi SUBTOTAL dalam Excel untuk meringkaskan nilai dalam lembaran kerja dengan cara yang berbeza. Ia amat membantu apabila lembaran kerja anda mengandungi baris tersembunyi yang anda ingin sertakan dalam pengiraan.
Sintaks fungsi SUBTOTAL ialah: SUBTOTAL(function_num, ref1, ref2, …)
Argumen function_num diperlukan dan menentukan jenis operasi matematik untuk digunakan untuk subjumlah. Fungsi SUBTOTAL boleh menambah nombor, mengira nilai purata nombor yang dipilih, mencari nilai maksimum dan minimum dalam julat, mengira bilangan nilai dalam julat yang dipilih dan banyak lagi.
Fungsi SUBTOTAL mengabaikan sel yang tidak mengandungi data dan sel dengan nilai bukan angka.
Argumen ini ialah nombor dan bergantung pada sama ada anda ingin memasukkan baris tersembunyi dalam hasil carian atau mengecualikan baris tersembunyi daripada hasil carian. Baris ini mungkin disembunyikan atau disembunyikan secara manual oleh penapis.
Argumen function_num termasuk:
| Tugas Fungsi | nombor_fungsi | nombor_fungsi |
|---|---|---|
| (termasuk nilai tersembunyi) | (tidak termasuk nilai tersembunyi) | |
| PURATA | 1 | 101 |
| COUNT | 2 | 102 |
| COUNTA | 3 | 103 |
| MAX | 4 | 104 |
| MIN | 5 | 105 |
| PRODUCT | 6 | 106 |
| STDEV | 7 | 107 |
| STDEVP | 8 | 108 |
| SUM | 9 | 109 |
| VAR | 10 | 110 |
| VARP | 11 | 111 |
Argumen rujukan function_num 1 hingga 11 hanya memasukkan nilai dalam baris tersembunyi apabila menggunakan perintah Hide untuk menyembunyikan baris. Apabila menggunakan arahan Penapis, SUBTOTAL pengiraan tidak termasuk hasil penapis tersembunyi.
Argumen ref1 diperlukan. Ini adalah sel yang digunakan untuk mengira hasil argumen function_num yang dipilih. Argumen ini boleh menjadi nilai, sel tunggal atau julat sel.
Argumen ref2, … adalah pilihan. Ini adalah sel tambahan yang disertakan dalam pengiraan.
Gunakan Fungsi SUBTOTAL dengan Baris Tersembunyi
Fungsi Excel boleh dimasukkan secara manual atau dengan bantuan kotak dialog Fungsi Argumen. Untuk menggambarkan cara memasukkan fungsi secara manual menggunakan bar formula, contoh berikut menggunakan argumen COUNT function_num untuk mengira bilangan nilai dalam baris yang boleh dilihat dan dalam kedua-dua baris yang kelihatan dan tersembunyi.
Untuk menggunakan fungsi SUBTOTAL untuk mengira bilangan baris dalam lembaran kerja:
- Mulakan dengan lembaran kerja yang mengandungi berbilang baris data.
- Pilih sel yang akan mengandungi kiraan baris yang boleh dilihat.
-
Dalam bar fungsi, masukkan =SUBJUMLAH. Semasa anda menaip, Excel mencadangkan fungsi. Klik dua kali pada fungsi SUBTOTAL.
Untuk menggunakan kotak dialog Function Arguments untuk memasuki fungsi SUBTOTAL, pergi ke Formulas dan pilih Math & Trig >SUBJUMLAH.
Image -
Dalam menu lungsur yang muncul, klik dua kali pada argumen 102 – COUNT function_num.
Image -
Taip koma (,).
Image -
Dalam lembaran kerja, pilih sel untuk disertakan dalam formula.
Image -
Tekan Enter untuk melihat hasil dalam sel yang anda pilih dalam langkah 2.
Image - Pilih sel yang akan mengandungi kiraan baris yang kelihatan dan tersembunyi.
-
Dalam bar fungsi, masukkan =SUBJUMLAH. Semasa anda menaip, Excel mencadangkan fungsi. Klik dua kali pada fungsi SUBTOTAL.
- Dalam menu lungsur yang muncul, klik dua kali pada argumen 2 – COUNT function_num, kemudian taipkan koma (,).
-
Dalam lembaran kerja, pilih sel untuk dimasukkan ke dalam formula, kemudian tekan Enter.
Image -
Sembunyikan beberapa baris data. Dalam contoh ini, baris dengan hanya jualan di bawah $100, 000 telah disembunyikan.
Image
Gunakan Fungsi SUBTOTAL dengan Data Ditapis
Menggunakan fungsi SUBTOTAL pada data yang ditapis mengabaikan data dalam baris yang telah dialih keluar oleh penapis. Setiap kali kriteria penapis berubah, fungsi mengira semula untuk menunjukkan jumlah kecil bagi baris yang boleh dilihat.
Untuk menggunakan fungsi SUBTOTAL untuk melihat perbezaan dalam hasil pengiraan semasa menapis data:
-
Buat formula SUBTOTAL. Contohnya, buat formula untuk menentukan nilai subjumlah dan purata bagi data yang ditapis.
Tidak kira jika anda menggunakan argumen function_num untuk baris yang kelihatan atau tersembunyi. Kedua-dua hujah memberikan hasil yang sama dalam data yang ditapis.
Image - Pilih mana-mana sel dalam set data.
-
Pergi ke Rumah, kemudian pilih Isih & Tapis > Penapis.
Image -
Gunakan anak panah lungsur untuk menapis data lembaran kerja.
Image -
Perhatikan cara nilai berubah setiap kali anda memilih kriteria penapis yang berbeza.
Image
Gunakan Fungsi SUBTOTAL dengan Data Dikumpulkan
Apabila data dikumpulkan, terdapat cara untuk menggunakan fungsi SUBTOTAL pada setiap kumpulan individu dan kemudian mengira jumlah besar untuk keseluruhan set data.
- Pilih mana-mana sel dalam set data.
-
Pilih Data > Subtotal untuk membuka kotak dialog Subtotal.
Image - Pilih Pada setiap perubahan dalam anak panah lungsur dan pilih kumpulan yang setiap subjumlah akan dikira.
- Pilih Gunakan fungsi anak panah lungsur turun dan pilih nombor_fungsi.
- Dalam senarai Tambah subjumlah pada, pilih lajur yang formula akan digunakan.
-
Pilih OK.
Image -
Subjumlah dimasukkan untuk setiap kumpulan data dan jumlah besar dimasukkan di bahagian bawah set data.
Image - Untuk menukar nombor_fungsi, serlahkan mana-mana sel dalam set data dan pilih Data > Subtotal. Kemudian, buat pilihan anda dalam kotak dialog Subtotal.
