Formula generik
=N(CELL("width",A1)>0)
Ringkasan
Untuk mengira lajur yang kelihatan dalam julat, anda boleh menggunakan formula pembantu berdasarkan fungsi CELL dengan IF, kemudian hasilnya dihitung dengan fungsi SUM. Dalam contoh yang ditunjukkan, formula dalam I4 adalah:
=SUM(key)
di mana "kunci" adalah julat bernama B4: F4, dan semua sel mengandungi formula ini, disalin di:
=N(CELL("width",B4)>0)
Untuk melihat perubahan kiraan, anda mesti memaksa pengiraan dengan F9, atau melakukan perubahan lembaran kerja lain yang mencetuskan pengiraan semula. Berikut adalah lembaran kerja yang sama dengan semua lajur yang dapat dilihat:
Nota: Saya mendapat idea utama untuk formula ini di laman web wmfexcel.com yang sangat baik.
Penjelasan
Tidak ada cara langsung untuk mengesan lajur tersembunyi dengan formula di Excel. Anda mungkin berfikir untuk menggunakan fungsi SUBTOTAL, tetapi SUBTOTAL hanya berfungsi dengan julat menegak. Akibatnya, pendekatan yang dijelaskan dalam contoh ini adalah penyelesaian berdasarkan formula penolong yang harus dimasukkan dalam julat yang merangkumi semua lajur dalam ruang lingkup minat. Dalam contoh ini, julat ini adalah julat "kunci" yang dinamakan.
Dalam contoh yang ditunjukkan, lajur C dan E disembunyikan. Formula pembantu, dimasukkan dalam B4 dan disalin di B4: F4, berdasarkan fungsi CELL:
=CELL("width",B4)>0
Fungsi CELL hanya akan mengembalikan lebar sel di lajur yang kelihatan. Apabila lajur disembunyikan, formula yang sama akan mengembalikan sifar. Dengan memeriksa apakah hasilnya lebih besar daripada sifar, kita mendapat hasil yang BENAR atau SALAH. Fungsi N digunakan untuk memaksa TRUE ke 1 dan FALSE menjadi sifar, jadi hasil akhir adalah 1 ketika lajur dapat dilihat, dan 0 ketika lajur disembunyikan. Bagus.
Untuk mengira lajur yang kelihatan, kami menggunakan formula fungsi SUM di I4:
=SUM(key)
di mana "kunci" adalah julat bernama B4: F4.
Hitung lajur tersembunyi
Untuk mengira lajur tersembunyi, formula dalam I5 adalah:
=COLUMNS(key)-SUM(key)
Fungsi COLUMNS mengembalikan jumlah lajur dalam julat (5) dan fungsi SUM mengembalikan jumlah lajur yang dapat dilihat (3), jadi hasil akhir adalah 2:
=COLUMNS(key)-SUM(key) =5-3 =2
Dengan operasi lain
Sebaik sahaja anda mempunyai "kekunci lajur", anda boleh menggunakannya dengan operasi lain. Contohnya, anda dapat nilai SUM pada lajur yang kelihatan dengan menggunakan SUM seperti ini:
=SUM(key*B6:F6)
Walaupun setiap sel di B6: F6 berisi angka 25, SUM akan mengembalikan 75 apabila lajur C dan E disembunyikan, seperti yang ditunjukkan dalam contoh.
Catatan: Fungsi CELL adalah fungsi yang tidak menentu. Fungsi meruap biasanya dikira semula dengan setiap perubahan lembaran kerja, sehingga dapat menyebabkan masalah prestasi. Malangnya, CELL tidak menyala apabila lajur disembunyikan atau kelihatan semula. Ini bermakna anda tidak akan melihat hasil yang betul sehingga lembaran kerja mengira semula, sama ada dengan perubahan biasa, atau dengan menekan F9.
