Công thức chung
=N(CELL("width",A1)>0)
Tóm lược
Để đếm các cột hiển thị trong một phạm vi, bạn có thể sử dụng công thức trợ giúp dựa trên hàm CELL với IF, sau đó kiểm đếm kết quả bằng hàm SUM. Trong ví dụ được hiển thị, công thức trong I4 là:
=SUM(key)
trong đó "key" là phạm vi được đặt tên B4: F4 và tất cả các ô chứa công thức này, được sao chép qua:
=N(CELL("width",B4)>0)
Để xem sự thay đổi về số lượng, bạn phải buộc tính toán với F9 hoặc thực hiện một thay đổi trang tính khác để kích hoạt tính toán lại. Dưới đây là cùng một bảng tính với tất cả các cột có thể nhìn thấy:
Lưu ý: Tôi đã tìm ra ý tưởng cốt lõi cho công thức này trên trang web wmfexcel.com tuyệt vời.
Giải trình
Không có cách nào trực tiếp để phát hiện cột ẩn có công thức trong Excel. Bạn có thể nghĩ đến việc sử dụng hàm SUBTOTAL, nhưng hàm SUBTOTAL chỉ hoạt động với phạm vi dọc. Do đó, cách tiếp cận được mô tả trong ví dụ này là một cách giải quyết dựa trên công thức trợ giúp phải được nhập vào một phạm vi bao gồm tất cả các cột trong phạm vi quan tâm. Trong ví dụ này, phạm vi này là phạm vi được đặt tên là "khóa".
Trong ví dụ được hiển thị, cột C và E bị ẩn. Công thức trợ giúp, được nhập trong B4 và được sao chép qua B4: F4, dựa trên hàm CELL:
=CELL("width",B4)>0
Hàm CELL sẽ chỉ trả về chiều rộng cho một ô trong cột hiển thị. Khi một cột bị ẩn, cùng một công thức sẽ trả về 0. Bằng cách kiểm tra nếu kết quả lớn hơn 0, chúng ta nhận được kết quả TRUE hoặc FALSE. Hàm N được sử dụng để buộc TRUE thành 1 và FALSE thành 0, do đó, kết quả cuối cùng là 1 khi một cột hiển thị và 0 khi một cột bị ẩn. Đẹp.
Để đếm các cột hiển thị, chúng tôi sử dụng công thức hàm SUM trong I4:
=SUM(key)
trong đó "key" là phạm vi được đặt tên B4: F4.
Đếm số cột ẩn
Để đếm các cột bị ẩn, công thức trong I5 là:
=COLUMNS(key)-SUM(key)
Hàm COLUMNS trả về tổng số cột trong phạm vi (5) và hàm SUM trả về tổng của các cột hiển thị (3), do đó, kết quả cuối cùng là 2:
=COLUMNS(key)-SUM(key) =5-3 =2
Với các hoạt động khác
Khi bạn đã có "phím cột" tại chỗ, bạn có thể sử dụng nó với các thao tác khác. Ví dụ: bạn có thể có giá trị SUM trong các cột hiển thị bằng cách sử dụng SUM như sau:
=SUM(key*B6:F6)
Mặc dù mỗi ô trong B6: F6 chứa số 25, SUM sẽ trả về 75 khi cột C và E bị ẩn, như được hiển thị trong ví dụ.
Lưu ý: Hàm CELL là một hàm dễ bay hơi. Các hàm thay đổi thường được tính toán lại với mọi thay đổi trang tính, vì vậy chúng có thể gây ra các vấn đề về hiệu suất. Thật không may, CELL không kích hoạt khi một cột bị ẩn hoặc hiển thị lại. Điều này có nghĩa là bạn sẽ không thấy kết quả chính xác cho đến khi trang tính tính toán lại, với một thay đổi bình thường hoặc bằng cách nhấn F9.
