决定细胞是否可见的公式 [英] Formula to decide if cell is visible
问题描述
我可以使用公式来确定单元格是否不在隐藏的行/列中?
Can I use a formula to determine if a cell is not in a hidden row/column?
我知道有相关的公式,如小计和聚合,但是我不太明白那些。而小计只是关心过滤行,而不是关于一般的隐藏行。
I know that there are related formulas like subtotal and aggregate, but I don't quite understand those. And subtotal only seems to care about filtered row, not about hidden rows in general.
我正在寻找类似的东西:
I'm looking for something like:
=ISVISIBLE(A1)
是吗可能的组合公式达到该功能?
Is it possible combine formulas that arrive at that functionality?
推荐答案
没有内置的公式。但是,您可以使用VBA /编写自己的函数来实现这一点。
There is no built-in formula for this. However, you can use VBA / write your own function to achieve this.
Function isvisible(cellname As String)
isvisible = Not (Worksheets("Sheet1").Range(cellname).EntireColumn.Hidden Or Worksheets("Sheet1").Range(cellname).EntireRow.Hidden)
End Function
然后,您可以在单元格中键入 = isvisible(A1)
以获取结果。
Then you can type =isvisible("A1")
in a cell to get the result.
如果你不喜欢双引号,这里是另一种方式:
If you don't like the double quotes, here is another way:
Function isvisible(rng As Range)
isvisible = Not (rng.EntireColumn.Hidden Or rng.EntireRow.Hidden)
End Function
然后,您可以在单元格中键入 = isvisible(A1)
以获取结果。
Then you can type =isvisible(A1)
in a cell to get the result.
这篇关于决定细胞是否可见的公式的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!