Perkongsian kemahiran Excel praktikal: Bagaimana untuk mengabaikan lajur tersembunyi untuk jumlah?

Dalam artikel sebelumnya " Perkongsian Petua Praktikal Excel: Menggunakan "Pemformatan Bersyarat" dan "Formula Fungsi" Bersama", kami mempelajari tentang "Pemformatan Bersyarat" dan "Formula Fungsi" melalui 2 contoh yang digunakan bersama-sama. Hari ini kita akan bercakap tentang penjumlahan data dan memperkenalkan kaedah penjumlahan dengan mengabaikan lajur tersembunyi.

Dalam kerja harian, kita sering menggunakan penjumlahan Masalah seperti ini mudah untuk semua orang dapat diselesaikan dengan menggunakan fungsi SUM:

Kadangkala beberapa baris data mungkin disembunyikan dalam kes ini, fungsi SUBTOTAL digunakan untuk penjumlahan. Apabila tiada penyembunyian, hasil fungsi SUBTOTAL adalah konsisten dengan hasil pengiraan fungsi SUM, seperti berikut:

Setelah kami menambah baris tertentu (seperti 3, 6, 9 , 12 baris) data disembunyikan, dan hasilnya berubah, seperti berikut:

Ambil perhatian bahawa parameter pertama fungsi SUBTOTAL menggunakan 109 untuk menunjukkan bahawa jumlah tersembunyi baris diabaikan.

Mengenai fungsi SUBTOTAL, tutorial telah diterbitkan sebelum ini Kawan-kawan yang berminat boleh membaca artikel sejarah.

Masalah yang akan kita bincangkan hari ini bukanlah cara menjumlahkan jika anda mengabaikan baris tersembunyi, tetapi cara menjumlahkan jika anda mengabaikan lajur tersembunyi. Pertama sekali, mari kita jelaskan bahawa fungsi SUBTOTAL tidak boleh melakukan ini dengan jelas dalam bantuan fungsi:

Bukan sahaja fungsi SUBTOTAL tidak boleh melakukan ini, tetapi pada masa ini Sebagai contoh, Excel belum mempunyai fungsi yang boleh mengabaikan lajur tersembunyi untuk penjumlahan. Jadi bagaimana untuk menangani permintaan ini? Ini memerlukan penggunaan fungsi yang agak baharu - fungsi CELL untuk membantu.

Saya percaya bahawa tidak ramai rakan yang melihat fungsi ini, malah lebih sedikit yang tahu cara menggunakannya. Mari kita fahami secara ringkas untuk apa fungsi CELL digunakan. Masukkan =c dalam sel dan anda akan melihat fungsi ini Selepas memilih fungsi, penjelasan mudah akan muncul:

Dalam ayat ini, anda boleh memahami secara kasar Pergi ke. Fungsi CELL untuk mendapatkan format, lokasi dan maklumat lain sel. Klik dua kali fungsi ini, dan beberapa pilihan akan muncul:

Seperti yang anda lihat, fungsi ini mempunyai dua parameter, info_type dan rujukan. Parameter info_type pertama, jenis maklumat, terdapat 12 jenis secara keseluruhannya Makna khusus setiap jenis boleh difahami melalui fungsi:

Rakan yang berminat dengan maklumat ini. jenis boleh melakukannya sendiri Sila lihat, semuanya sangat mudah difahami. Perkara yang paling penting untuk digunakan hari ini ialah jenis maklumat terakhir "lebar", iaitu hanya lebar lajur.

Sesetengah rakan mungkin berfikir bahawa jika lajur disembunyikan, lebar lajurnya ialah 0. Adakah ini benar? Pilih "lebar" sebagai parameter pertama formula, dan tetapkan parameter kedua kepada B1, yang bermaksud anda ingin mendapatkan lebar lajur sel B1 (sebenarnya lebar lajur lajur B). Masukkan formula dalam sel B16: =CELL("width", B1), dan kemudian tarik formula ke kanan:

Hasilnya ialah kesemuanya 8. Kita boleh cuba melaraskan lebar lajur individu dan lihat jika terdapat sebarang perubahan:

Apabila kita melaraskan lebar, hasilnya masih 8. Adakah terdapat sesuatu yang salah dengan formula?

Sebenarnya tidak, sebabnya fungsi CELL agak temperamental Apabila format sel berubah (lebar lajur ialah format), ia mesti dikira semula untuk mengemas kini keputusan. Terdapat dua cara untuk mengira semula, satu adalah dengan menekan kekunci fungsi F9, satu lagi adalah dengan mengklik dua kali mana-mana sel dan tekan Enter. Lihat sekali lagi dan dapati keputusan telah dikemas kini:

Saiz nombor sepadan dengan lebar sel. Bercakap tentang cara mengabaikan jumlah lajur tersembunyi, jawapannya sudah jelas: gunakan fungsi CELL untuk mendapatkan lebar lajur, dan kemudian gunakan fungsi SUMIF untuk melaksanakan jumlah tersebut. Masukkan formula dalam sel I2 sebagai: =SUMIF($B$16:$G$16,">0",B2:G2), dan kemudian tarik formula ke bawah.

Apabila tiada penyembunyian, semuanya dijumlahkan Sekarang mari kita sembunyikan beberapa lajur untuk melihat kesannya:

Ingat untuk menekan F9 atau klik dua kali selepas bersembunyi.

Masalah nampaknya berakhir di sini, tetapi selalu ada beberapa rakan kongsi yang tidak bersedia menggunakan lajur tambahan (baris tambahan), dan ingin menggunakan formula tatasusunan untuk mencapainya, contohnya: =SUMPRODUCT((CELL ("lebar",B1 :G1>0)*B2:G2)

Idea ini kelihatan munasabah, tetapi ini tidak mungkin kerana jika rujukan parameter ialah julat sel tertentu, fungsi CELL hanya akan mengembalikan maklumat kepada julat sel Sel di sudut kiri atas kawasan Dalam erti kata lain, walaupun saya menulis julat seperti B1:G1, saya hanya mendapat lebar lajur B1 agak pelik~~~

Sebenarnya, terdapat banyak kegunaan menarik fungsi ini Jika anda ingin tahu, tinggalkan mesej di bawah!

Cadangan pembelajaran berkaitan:

tutorial excel<.>

Atas ialah kandungan terperinci Perkongsian kemahiran Excel praktikal: Bagaimana untuk mengabaikan lajur tersembunyi untuk jumlah?. Untuk maklumat lanjut, sila ikut artikel berkaitan lain di laman web China PHP!