忽略excel总和公式中的隐藏列 [英] Ignoring a hidden column in an excel sum formula

问题描述

我基本上想忽略excel中的随机列.有没有办法检测一列是否隐藏，然后不在公式中包含该列?

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?

例如F1 = B1 + C1 + E1(忽略D列)但第二天F列可能需要= B + D + E.

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!!

示例

所以基本上，如果我隐藏 Greg 的列，我希望所有行的总数反映该更改.因此，当他的列被隐藏时，E2 将等于 8 而不是 12.

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

推荐答案

方法一:使用CELL和INDIRECT.

Method 1: using CELL and INDIRECT.

细胞功能

单元格函数 允许您查找单元格的属性，例如宽度:

The cell function allows you find properties of a cell, e.g. the width:

=CELL("width")

间接作用

间接函数允许您通过字符串引用单元格，例如得到A1的值，

The Indirect function allows you to refer to cells via a string, e.g. to get the value of A1,

=INDIRECT("A1")

或

=INDIRECT("R1C1", true)

取所有的总和(例如E2 = B2 + C2 + D2)，然后使用CELL找出每个单元格的宽度是否为零，然后将其从总和中取出.

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.

方法二:使用IF和CELL

或者，您可以使用 IF函数和CELL函数，例如

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屋！