Formula umum
=N(CELL("width",A1)>0)
Ringkasan
Untuk menghitung kolom yang terlihat dalam suatu rentang, Anda dapat menggunakan rumus pembantu berdasarkan fungsi CELL dengan IF, lalu menghitung hasilnya dengan fungsi SUM. Dalam contoh yang ditunjukkan, rumus di I4 adalah:
=SUM(key)
di mana "kunci" adalah rentang bernama B4: F4, dan semua sel berisi rumus ini, disalin ke:
=N(CELL("width",B4)>0)
Untuk melihat perubahan hitungan, Anda harus memaksa penghitungan dengan F9, atau melakukan perubahan lembar kerja lain yang memicu penghitungan ulang. Di bawah ini adalah lembar kerja yang sama dengan semua kolom terlihat:
Catatan: Saya menemukan ide inti untuk formula ini di situs wmfexcel.com yang luar biasa.
Penjelasan
Tidak ada cara langsung untuk mendeteksi kolom tersembunyi dengan rumus di Excel. Anda mungkin berpikir untuk menggunakan fungsi SUBTOTAL, tetapi SUBTOTAL hanya berfungsi dengan rentang vertikal. Hasilnya, pendekatan yang dijelaskan dalam contoh ini adalah solusi berdasarkan rumus pembantu yang harus dimasukkan dalam rentang yang mencakup semua kolom dalam cakupan minat. Dalam contoh ini, rentang ini adalah rentang bernama "kunci".
Dalam contoh yang diperlihatkan, kolom C dan E disembunyikan. Rumus pembantu, yang dimasukkan dalam B4 dan disalin di B4: F4, didasarkan pada fungsi CELL:
=CELL("width",B4)>0
Fungsi CELL hanya akan mengembalikan lebar sel di kolom yang terlihat. Saat kolom disembunyikan, rumus yang sama akan mengembalikan nol. Dengan memeriksa apakah hasilnya lebih besar dari nol, kita mendapatkan hasil yang BENAR atau SALAH. Fungsi N digunakan untuk memaksa TRUE menjadi 1 dan FALSE menjadi nol, sehingga hasil akhirnya adalah 1 saat kolom terlihat, dan 0 saat kolom tersembunyi. Bagus.
Untuk menghitung kolom yang terlihat, kami menggunakan rumus fungsi SUM di I4:
=SUM(key)
di mana "kunci" adalah rentang bernama B4: F4.
Hitung kolom tersembunyi
Untuk menghitung kolom tersembunyi, rumus di I5 adalah:
=COLUMNS(key)-SUM(key)
Fungsi COLUMNS mengembalikan kolom total dalam rentang (5) dan fungsi SUM mengembalikan jumlah kolom yang terlihat (3), sehingga hasil akhirnya adalah 2:
=COLUMNS(key)-SUM(key) =5-3 =2
Dengan operasi lain
Setelah Anda memiliki "kunci kolom", Anda dapat menggunakannya dengan operasi lain. Misalnya, Anda bisa menjumlahkan nilai di kolom yang terlihat dengan menggunakan SUM seperti ini:
=SUM(key*B6:F6)
Meskipun setiap sel di B6: F6 berisi angka 25, SUM akan mengembalikan 75 saat kolom C dan E disembunyikan, seperti yang diperlihatkan dalam contoh.
Catatan: Fungsi CELL adalah fungsi yang mudah menguap. Fungsi volatil biasanya dihitung ulang dengan setiap perubahan lembar kerja, sehingga dapat menyebabkan masalah kinerja. Sayangnya, CELL tidak menyala saat kolom disembunyikan atau dibuat terlihat lagi. Ini berarti Anda tidak akan melihat hasil yang benar sampai lembar kerja dihitung ulang, baik dengan perubahan normal, atau dengan menekan F9.
