Fungsi SUBTOTAL: Formula dan cara menggunakan fungsi SUBTOTAL dalam Excel

Apakah fungsi SUBTOTAL dalam Excel ? Apakah formula SUBTOTAL dalam Excel ? Jom ketahui dengan LuckyTemplates.com!

Microsoft Excel mempunyai banyak fungsi yang digunakan untuk mengira jumlah, dan SUBTOTAL adalah salah satu daripadanya. Walau bagaimanapun, Subtotal berbeza daripada fungsi SUM. Ia bukan jumlah biasa. Sebaliknya, ia digunakan untuk mengira subjumlah dalam senarai atau pangkalan data, mengira purata dan banyak lagi.

Kandungan utama tentang fungsi SUBTOTAL

• Apakah fungsi SUBTOTAL dan untuk apa ia digunakan?
• Formula SUBJUMLAH dalam Excel
• Cara menggunakan fungsi SUBTOTAL dalam Excel
  • 1. Kira jumlah baris yang ditapis 
  • 2. Kira sel yang ditapis yang tidak kosong
  • 3. Abaikan nilai dalam formula Subtotal bersarang
• Perkara yang perlu dipertimbangkan sebelum menggunakan fungsi SUBTOTAL dalam Excel
• Bagaimana untuk ralat semasa melaksanakan fungsi SUBTOTAL dalam Excel

Apakah fungsi SUBTOTAL dan untuk apa ia digunakan?

Pada pandangan pertama, anda mungkin tertanya-tanya: Apakah tujuan fungsi SUBTOTAL? Lagipun, adalah mudah untuk mencari subjumlah daripada julat sel menggunakan fungsi sedia ada seperti SUMIF atau AVERAGE .

Walaupun benar bahawa anda tidak semestinya perlu menggunakan SUBTOTAL, ia menjadikan menganalisis data yang disasarkan lebih mudah. Anda boleh memasukkan formula menggunakan SUBTOTAL ke dalam julat sel tanpa menjejaskan jumlah keseluruhan, kerana SUBTOTAL mengabaikan sel lain yang mengandungi formula SUBTOTAL.

SUBTOTAL berfungsi dengan ciri Excel lain, seperti menapis sel. Jika anda menapis jadual mengikut nilai, formula SUBTOTAL mengemas kini, mengecam penapis dan mengecualikan sel yang sepadan. SUBTOTAL juga berguna untuk mengabaikan nilai tersembunyi, yang fungsi lain (seperti SUM) tidak boleh lakukan.

Nasib baik, anda boleh menggunakan fungsi lain sebagai sebahagian daripada formula SUBTOTAL. Terdapat 11 kaedah subjumlah berbeza yang tersedia, setiap satu sesuai untuk fungsi Excel sedia ada. Jika anda mahukan formula SUM yang berfungsi dengan sel yang ditapis dan mengabaikan formula SUBTOTAL yang lain, sebaiknya gunakan SUBTOTAL, walaupun jadual pangsi boleh berfungsi dengan cara yang sama.

Formula SUBJUMLAH dalam Excel

Fungsi SUBTOTAL berfungsi dengan mengira jumlah nilai julat sel, berdasarkan fungsi matematik yang lain. SUBTOTAL menggunakan sehingga 11 fungsi matematik yang berbeza (daripada AVERAGE hingga VAR.P) untuk mencari jumlah nilai yang sesuai, sambil bekerja dalam parameter fungsi (iaitu, mengabaikan formula SUBTOTAL yang lain).

Fungsi SUBTOTAL mempunyai sintaks =SUBTOTAL(function_num, ref1, [ref2],…) . Di sana:

• Function_num: Nombor dari 1 hingga 11 dan 101 hingga 111 menentukan fungsi yang akan digunakan untuk mengira dalam SUBTOTAL.
• Ref1, Ref2, ...: 1 atau lebih sel, atau julat sel untuk mengira subjumlah, maksimum 254.

Catatan:

• Fungsi SUBTOTAL direka untuk mengira lajur menegak data.
• Jika argumen ref1, ref2,... mengandungi fungsi SUBTOTAL, ia akan diabaikan untuk mengelakkan pengiraan dua kali.
• Jika function_num adalah dari 1 hingga 11, fungsi SUBTOTAL mengira termasuk nilai tersembunyi dalam set data kerana baris yang mengandungi julat itu disembunyikan. Jika function_num adalah dari 101 hingga 111, fungsi SUBTOTAL hanya mengira nilai bukan tersembunyi dalam set data (tidak mengira nilai dalam baris tersembunyi).
• Untuk kawasan data yang disembunyikan oleh Penapis, SUBTOTAL akan diabaikan.

Cara menggunakan fungsi SUBTOTAL dalam Excel

1. Kira jumlah baris yang ditapis 

Untuk lebih memahami jenis jumlah ini, sila rujuk artikel Kira jumlah nilai senarai yang ditapis dalam Excel . Pada asasnya, fungsi SUBTOTAL dalam kes ini ialah:

=SUBJUMLAH(9,_julat)

pham_range di sini ialah kawasan yang anda ingin jumlahkan selepas menapis data.

2. Kira sel yang ditapis yang tidak kosong

Kami akan menggunakan SUBTOTAL 3 atau SUBTOTAL 103. Walau bagaimanapun, sekiranya terdapat baris tersembunyi, anda mesti menggunakan SUBTOTAL 103 untuk mengira dengan tepat sel bukan kosong yang boleh dilihat.

Sebagai contoh, jadual data di atas akan menyembunyikan 2 baris 4 dan 5. Apabila menggunakan SUBTOTAL 3 atau SUBTOTAL 103, ia akan menghasilkan 2 hasil yang berbeza.

Kami memasukkan formula dan kemudian Excel secara automatik akan memaparkan fungsi yang ditetapkan untuk anda pilih tanpa perlu ingat.

Hasilnya apabila menggunakan SUBTOTAL 3 ialah 3, termasuk sel tersembunyi dalam baris.

Bagi SUBTOTAL 103, ia hanya akan memaparkan sel bukan kosong yang kita lihat, mengabaikan baris tersembunyi.

3. Abaikan nilai dalam formula Subtotal bersarang

Sebagai contoh, kami akan mengira purata jumlah kilogram kain di gudang A1 dan A2.

Formula purata untuk gudang A2= SUBTOTAL(1,C2:C4) dan memberikan keputusan 19.

Formula purata untuk gudang A1 = SUBTOTAL(1,C5:C7) mempunyai keputusan 38.

Walau bagaimanapun, apabila mengira purata jumlah fabrik dalam 2 gudang, purata keputusan dalam 2 gudang akan diabaikan. Kami mempunyai formula =SUBTOTAL(1,C2:C9) dan hasilnya akan mengalih keluar purata hasil yang dikira sebelumnya secara automatik.

Perkara yang perlu dipertimbangkan sebelum menggunakan fungsi SUBTOTAL dalam Excel

Walaupun SUBTOTAL mempunyai kelebihannya, terdapat beberapa perkara yang perlu anda pertimbangkan sebelum anda mula menggunakan fungsi ini, termasuk yang berikut:

• Seperti yang dinyatakan, menggunakan 1-11 untuk argumen function_num akan memastikan bahawa SUBTOTAL termasuk nilai tersembunyi, manakala 101-111 mengabaikannya.
• Menggunakan nilai selain daripada 1-11 atau 101-111 akan menyebabkan Excel mengembalikan ralat #VALUE!. Ini juga akan berlaku untuk rujukan sel 3D (di mana sel yang sama, merentas berbilang helaian, dirujuk dalam julat).
• Apabila julat sel mendatar seperti A1:D1 digunakan, nilai tersembunyi disertakan secara automatik (tanpa mengira nilai argumen function_num). Ini adalah had bagi fungsi SUBTOTAL dan tidak boleh diatasi.
• Menggunakan SUBTOTAL pada data yang ditapis akan memastikan bahawa nilai tersembunyi sentiasa diabaikan, tanpa mengira hujah function_num yang digunakan.
• SUBTOTAL boleh digunakan sebagai fungsi bersarang sebagai sebahagian daripada fungsi lain, termasuk formula yang mengandungi fungsi IF.
• Jika formula SUBTOTAL lain berada dalam julat data ref1, SUBTOTAL mengabaikannya dan mengecualikannya daripada pengiraan keseluruhan.
• Excel membenarkan sehingga 254 julat sel untuk digunakan dalam formula SUBJUMLAH, walaupun hanya 1 julat sel diperlukan untuk formula berfungsi.

Bagaimana untuk ralat semasa melaksanakan fungsi SUBTOTAL dalam Excel

Apabila anda mengira fungsi SUBTOTAL dalam Excel, anda akan sering menghadapi beberapa ralat asas:

• #VALUE!: Pengecam fungsi tidak berada dalam julat 1-11 atau 101-111 atau mempunyai rujukan 3D.
• #DIV/0!: Berlaku apabila jumlah tertentu mesti dibahagikan dengan 0 (contohnya, mengira purata atau sisihan piawai julat sel yang tidak mengandungi nilai berangka).
• #NAMA? Nama fungsi SUBTOTAL salah eja.

Beberapa petua tentang cara menggunakan fungsi SUBTOTAL dalam Microsoft Excel yang mungkin anda tidak tahu

Petua 1: Katakan anda ingin memastikan semua warna t-shirt tersedia dalam setiap saiz di mana-mana gudang. Anda boleh mengikuti langkah berikut:

Langkah 1: Klik Subjumlah . Ingat bahawa langkah ini adalah untuk menambah lebih banyak kriteria pada data subjumlah sedia ada.

Langkah 2: Pilih COUNT daripada menu lungsur turun dan Saiz daripada medan “ Tambah subjumlah medan pada ”. Kemudian, nyahtanda Gantikan subjumlah semasa . Apabila anda mengklik OK , anda akan menerima jadual data berikut:

Ini membantu memastikan anda mempunyai bilangan dimensi berbeza yang mencukupi dan boleh mengklasifikasikan data dengan cara yang tidak memerlukan operasi berulang.

Petua 2 : Ia sentiasa berguna untuk mengisih data mengikut lajur.

Petua 3: Membantu anda memasukkan label dalam baris pertama.

Petua 4: Jika anda ingin meringkaskan data, nyahtanda kotak " Ringkasan di bawah data semasa memasukkan Subtotal ." - Ringkasan data di bawah apabila memasukkan fungsi SUBTOTAL.

Ringkasnya, apakah fungsi Subtotal dalam Excel?

Seperti namanya, fungsi SUBTOTAL dalam Excel digunakan untuk mengira subjumlah. Fungsi Excel SUBTOTAL mengembalikan hasil agregat untuk nilai yang diberikan. SUBTOTAL boleh mengira jumlah, purata, kiraan, maksimum… Excel ini juga boleh memasukkan atau mengecualikan nilai dalam baris tersembunyi.

• Tujuan: Kira subjumlah dalam senarai atau pangkalan data.
• Nilai pulangan: Nombor yang mewakili jenis subjumlah.
• Hujah:
  • function_num- Nombor yang menentukan fungsi yang hendak digunakan untuk mengira subjumlah dalam senarai.
  • ref1- Julat bernama atau rujukan kepada jumlah kecil.
  • ref2- [pilihan] Julat bernama atau rujukan kepada subjumlah.

Menggunakan fungsi SUBTOTAL boleh membantu anda menganalisis set data dengan cepat tanpa bergantung pada jadual pangsi yang lebih kompleks. Jika anda tersekat, anda juga boleh menggunakan ciri Subtotal, yang terdapat dalam tab Data pada reben, untuk membuat formula SUBJUMLAH secara automatik yang kemudian anda boleh edit atau salin di tempat lain.

Lihat lagi:

• Fungsi SUMPRODUCT dalam Excel: Kira jumlah produk nilai yang sepadan
• Bagaimana untuk menggabungkan 2 lajur Nama Penuh dan Nama Pertama dalam Excel tanpa kehilangan kandungan