忽略Excel中的隐藏列 [英] Ignoring a hidden column in an excel sum formula
问题描述
例如
F1 = B1 + C1 + E1(忽略列D)
但第二天列F可能需要= B + D + E。
有没有办法简单地实现这个?我看到一些忽略特定列的公式,但没有动态的可以检测隐藏的数据,然后不包括它。谢谢!!
示例
所以基本上,如果我隐藏Greg的列,我希望所有行的总和反映这个变化。那么当他的列被隐藏时,E2将等于8而不是12。
这里的第二个图像显示公式不能按预期工作
方法1:使用 CELL
和 INDIRECT
单元格功能
单元格功能允许您查找单元格的属性,例如宽度:
= CELL(width)
间接功能
间接功能允许您通过字符串引用单元格,例如获取A1的值,
= INDIRECT(A1)
或
= INDIRECT(R1C1,true )
取所有内容的总和(例如 E2 = B2 + C2 + D2
),然后使用CELL查找每个单元格宽度是否为零,然后将其从总和中取出。
方法2:使用 IF
和 CELL
或者,您可以使用 IF函数和CELL函数,
E1 = IF(CELL(width,B2)= 0,0,B2)
pre>
+ IF CELL(width,C2)= 0,0,C2)
+ IF(CELL(width,D2)= 0,0,D2)
I basically want to ignore random columns in excel. Is there a way to detect if a column is hidden, and then not include that column in the formula?
And example would be F1 = B1 + C1 + E1(ignoring column D) but the next day Column F may need to = B + D + E instead.
Is there a way to simply achieve this? I've seen some formulas that ignore specific columns, but nothing dynamic that can detect the hidden data and then not include it. Thanks!!
Example
So basically, If I hide Greg's column, I want the total for all the rows to reflect that change. So E2 would then equal 8 instead of 12 when his column is hidden.
The second image here shows the formula not working as expected
解决方案Method 1: using
CELL
andINDIRECT
.Cell function
The cell function allows you find properties of a cell, e.g. the width:
=CELL("width")
Indirect function
The Indirect function allows you to refer to cells via a string, e.g. to get the value of A1,
=INDIRECT("A1")
or
=INDIRECT("R1C1", true)
Take the sum of everything (e.g.
E2 = B2 + C2 + D2
), then use CELL to find out if each cell width is zero, then take it away from the sum.
Method 2: using
IF
andCELL
Or, you can use the IF function and the CELL function, e.g.
E1 = IF(CELL("width", B2) = 0, 0, B2) + IF(CELL("width", C2) = 0, 0, C2) + IF(CELL("width", D2) = 0, 0, D2)
这篇关于忽略Excel中的隐藏列的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!