Fungsi subtotal Excel dengan contoh formula

Tutorial menerangkan spesifikasi fungsi subtotal dalam Excel dan menunjukkan cara menggunakan formula subtotal untuk meringkaskan data dalam sel yang kelihatan.

Dalam artikel sebelumnya, kami membincangkan cara automatik untuk memasukkan subtotal dalam Excel dengan menggunakan ciri subtotal. Hari ini, anda akan belajar bagaimana menulis formula subtotal sendiri dan kelebihan yang diberikan kepada anda.

• Fungsi Subtotal dalam Excel - Sintaks dan Kegunaan
• 3 Sebab Teratas Menggunakan Fungsi Subtotal di Excel
• Contoh formula subtotal
• Subtotal excel tidak berfungsi

Fungsi Subtotal Excel - Sintaks dan Kegunaan

Microsoft mentakrifkan subtotal Excel sebagai fungsi yang mengembalikan subtotal dalam senarai atau pangkalan data. Dalam konteks ini, "subtotal" bukan hanya jumlah nombor dalam pelbagai sel. Tidak seperti fungsi Excel lain yang direka untuk melakukan hanya satu perkara tertentu, subtotal sangat serba boleh - ia boleh melakukan operasi aritmetik dan logik yang berbeza seperti sel mengira, mengira purata, mencari nilai minimum atau maksimum, dan banyak lagi.

Fungsi subtotal tersedia dalam semua versi Excel 2016, Excel 2013, Excel 2010, Excel 2007, dan lebih rendah.

Sintaks fungsi subtotal Excel adalah seperti berikut:

Subtotal (function_num, ref1, [ref2], ...)

Di mana:

• Function_num - Nombor yang menentukan yang berfungsi untuk digunakan untuk subtotal.
• REF1, REF2, ... - Satu atau lebih sel atau julat ke subtotal. Hujah REF pertama diperlukan, yang lain (sehingga 254) adalah pilihan.

Hujah fungsi_num boleh dimiliki oleh salah satu set berikut:

• 1 - 11 Abaikan sel -sel yang ditapis, tetapi termasuk baris tersembunyi secara manual.
• 101 - 111 Abaikan semua sel tersembunyi - ditapis dan tersembunyi secara manual.

Fungsi_num		Fungsi	Penerangan
1	101	Purata	Mengembalikan purata nombor.
2	102	Hitung	Mengira sel yang mengandungi nilai angka.
3	103	Counta	Mengira sel-sel yang tidak kosong.
4	104	Maks	Mengembalikan nilai terbesar.
5	105	Min	Mengembalikan nilai terkecil.
6	106	Produk	Mengira produk sel.
7	107	Stdev	Mengembalikan sisihan piawai populasi berdasarkan sampel nombor.
8	108	Stdevp	Mengembalikan sisihan piawai berdasarkan seluruh penduduk nombor.
9	109	Jumlah	Menambah nombor.
10	110	Var	Anggarkan varians populasi berdasarkan sampel nombor.
11	111	Varp	Anggarkan varians populasi berdasarkan seluruh populasi bilangan.

Malah, tidak perlu menghafal semua nombor fungsi. Sebaik sahaja anda mula menaip formula subtotal dalam sel atau di bar formula, Microsoft Excel akan memaparkan senarai nombor fungsi yang tersedia untuk anda.

Sebagai contoh, ini adalah bagaimana anda boleh membuat formula Subtotal 9 untuk meringkaskan nilai -nilai dalam sel C2 hingga C8:

Untuk menambah nombor fungsi ke formula, klik dua kali di atasnya, kemudian taipkan koma, tentukan julat, taipkan kurungan penutup, dan tekan Enter. Formula yang lengkap akan kelihatan seperti ini:

=SUBTOTAL(9,C2:C8)

Dengan cara yang sama, anda boleh menulis formula Subtotal 1 untuk mendapatkan purata, subtotal 2 untuk mengira sel dengan nombor, subtotal 3 untuk mengira bukan kosong, dan sebagainya. Tangkapan skrin berikut menunjukkan beberapa formula lain dalam tindakan:

Nota. Apabila anda menggunakan formula subtotal dengan fungsi ringkasan seperti jumlah atau purata, ia mengira hanya sel dengan nombor yang mengabaikan kosong dan sel yang mengandungi nilai bukan numerik.

Sekarang anda tahu bagaimana untuk membuat formula subtotal di Excel, soalan utama adalah - mengapa seseorang mahu mengambil masalah untuk belajar? Kenapa tidak hanya menggunakan fungsi biasa seperti jumlah, kiraan, max, dan lain -lain? Anda akan mendapat jawapan yang betul di bawah.

3 Sebab Teratas Menggunakan Subtotal di Excel

Berbanding dengan fungsi Excel tradisional, subtotal memberi anda kelebihan penting berikut.

1. Kirakan nilai dalam baris yang ditapis

Kerana fungsi subtotal Excel mengabaikan nilai-nilai dalam baris yang ditapis, anda boleh menggunakannya untuk membuat ringkasan data dinamik di mana nilai subtotal dikira semula secara automatik mengikut penapis.

Sebagai contoh, jika kita menapis jadual untuk menunjukkan jualan hanya untuk rantau Timur, formula subtotal secara automatik akan menyesuaikan diri supaya semua kawasan lain dikeluarkan dari jumlah:

Nota. Kerana kedua-dua set nombor fungsi (1-11 dan 101-111) mengabaikan sel yang ditapis, anda boleh menggunakan formula subtotal 9 atau subtotal 109 dalam kes ini.

2. Kirakan hanya sel yang kelihatan

Seperti yang anda ingat, formula subtotal dengan function_num 101 hingga 111 mengabaikan semua sel tersembunyi - ditapis dan tersembunyi secara manual. Oleh itu, apabila anda menggunakan ciri Sembunyikan Excel untuk mengeluarkan data yang tidak relevan dari pandangan, gunakan nombor fungsi 101-111 untuk mengecualikan nilai dalam baris tersembunyi dari subtotal.

Contoh berikut akan membantu anda mendapatkan lebih banyak pemahaman tentang bagaimana ia berfungsi: Subtotal 9 vs Subtotal 109.

3. Abaikan nilai dalam formula subtotal bersarang

Jika julat yang dibekalkan ke formula subtotal Excel anda mengandungi sebarang formula subtotal yang lain, subtotal bersarang itu akan diabaikan, jadi nombor yang sama tidak akan dikira dua kali. Hebat, bukan?

Dalam tangkapan skrin di bawah, SUBTOTAL(1, C2:C10) mengabaikan hasil formula subtotal dalam sel C3 dan C10, seolah -olah anda menggunakan formula purata dengan 2 rentang berasingan AVERAGE(C2:C5, C7:C9) .

Menggunakan Subtotal dalam Excel - Contoh Formula

Apabila anda pertama kali menemui subtotal, ia mungkin kelihatan rumit, rumit, dan bahkan sia -sia. Tetapi apabila anda turun ke tembaga, anda akan menyedari bahawa ia tidak sukar untuk menguasai. Contoh -contoh berikut akan menunjukkan kepada anda beberapa tip berguna dan idea inspirasi.

Contoh 1. Subtotal 9 vs Subtotal 109

Seperti yang sudah anda ketahui, subtotal Excel menerima 2 set nombor fungsi: 1-11 dan 101-111. Kedua-dua set mengabaikan baris yang ditapis, tetapi nombor 1-11 termasuk baris tersembunyi manakala 101-111 tidak termasuk mereka. Untuk lebih memahami perbezaannya, mari kita pertimbangkan contoh berikut.

Untuk jumlah baris yang ditapis , anda boleh menggunakan formula subtotal 9 atau subtotal 109 seperti yang ditunjukkan dalam tangkapan skrin di bawah:

Tetapi jika mempunyai barang -barang yang tidak relevan secara manual dengan menggunakan perintah Hide Rows pada tab Rumah > Kumpulan Sel > Format > Sembunyikan & Unhide , atau dengan mengklik kanan baris, dan kemudian mengklik Sembunyikan , dan sekarang anda ingin jumlah nilai hanya dalam baris yang kelihatan, subtotal 109 adalah satu -satunya pilihan:

Nombor fungsi lain berfungsi dengan cara yang sama. Sebagai contoh, untuk mengira sel-sel yang ditapis bukan kosong , sama ada subtotal 3 atau subtotal 103 formula akan dilakukan. Tetapi hanya subtotal 103 yang boleh mengira dengan betul bukan kosong jika terdapat baris tersembunyi dalam julat:

Nota. Fungsi subtotal Excel dengan fungsi_num 101-111 mengabaikan nilai dalam baris tersembunyi, tetapi tidak dalam lajur tersembunyi . Sebagai contoh, jika anda menggunakan formula seperti SUBTOTAL(109, A1:E1) kepada jumlah nombor dalam julat mendatar, menyembunyikan lajur tidak akan menjejaskan subtotal.

Contoh 2. Sekiranya subtotal secara dinamik meringkaskan data

Jika anda membuat laporan ringkasan atau papan pemuka di mana anda perlu memaparkan pelbagai ringkasan data tetapi anda tidak mempunyai ruang untuk segala -galanya, pendekatan berikut mungkin menjadi penyelesaian:

• Dalam satu sel, buat senarai drop-down yang mengandungi nama-nama fungsi seperti total, max, min, dan sebagainya.
• Di dalam sel di sebelah lungut, masukkan bersarang jika formula dengan fungsi subtotal tertanam yang sepadan dengan nama fungsi dalam senarai drop-down.

Sebagai contoh, dengan mengandaikan nilai-nilai ke subtotal berada dalam sel C2: C16, dan senarai juntai bawah dalam A17 mengandungi jumlah keseluruhan , purata , maksimum , dan min , formula subtotal "dinamik" adalah seperti berikut:

=IF(A17="total", SUBTOTAL(9,C2:C16), IF(A17="average", SUBTOTAL(1,C2:C16), IF(A17="min", SUBTOTAL(5,C2:C16), IF(A17="max", SUBTOTAL(4,C2:C16),""))))

Dan sekarang, bergantung kepada fungsi apa yang pengguna pilih dari senarai drop-down, fungsi subtotal yang sepadan akan mengira nilai dalam baris yang ditapis:

Petua. Jika tiba-tiba senarai drop-down dan sel formula hilang dari lembaran kerja anda, pastikan anda memilihnya dalam senarai penapis.

Subtotal Excel Tidak Berfungsi - Kesalahan Biasa

Jika formula subtotal anda mengembalikan kesilapan, ia mungkin disebabkan oleh salah satu sebab berikut:

#VALUE! - Argumen fungsi_num adalah selain integer antara 1 - 11 atau 101 - 111; atau mana-mana hujah REF mengandungi rujukan 3-D.

#DIV/0! - Berlaku jika fungsi ringkasan yang ditentukan harus melakukan pembahagian dengan sifar (misalnya mengira sisihan purata atau piawai untuk pelbagai sel yang tidak mengandungi satu nilai angka tunggal).

#NAME? - Nama fungsi subtotal salah eja - kesilapan yang lebih mudah untuk diperbaiki :)

Petua. Jika anda tidak merasa selesa dengan fungsi subtotal lagi, anda boleh menggunakan ciri subtotal terbina dalam dan mempunyai formula yang dimasukkan untuk anda secara automatik.

Itulah cara menggunakan formula subtotal dalam Excel untuk mengira data dalam sel yang kelihatan. Untuk membuat contoh lebih mudah untuk diikuti, anda dialu -alukan untuk memuat turun buku kerja sampel kami di bawah. Terima kasih kerana membaca!

Amalan Buku Kerja

Contoh Formula Subtotal Excel (fail .xlsx)

Atas ialah kandungan terperinci Fungsi subtotal Excel dengan contoh formula. Untuk maklumat lanjut, sila ikut artikel berkaitan lain di laman web China PHP!