☰

在 Excel 中编写公式时引用所选内容的可见部分

tag-icon • tag-icon microsoft-excel
在 Excel 中编写公式时引用所选内容的可见部分

我知道有很多方法可以创建子表那个改变基于改变单元格的可见性 - 尽管我的问题可能听起来类似 - 但这不是我的问题。

假设我有一个有 2 列 5 行的工作簿，例如

.    A      B
1    Name   Val
2    A      1
3    A      3
4    B      5
5    B      2
6    A      6
7    A      
8    B      5
9    A      2

我正在尝试用上面一些以“A”命名的行的平均值来填充单元格 B7...因此，我使用“A”根据名称列过滤表格，我想要得到的是公式 sum(B2,B3,B6) 或（甚至更好）sum(B2:B3,B6)，即单元格的总和在创建此公式时可见。

我不能使用小计，因为当我更改过滤条件时它会改变其值...理论上我可以使用小计并复制粘贴作为值，但随后我失去了值的语义。

请注意，无论行可见性将来如何变化，该值都应保持不变（如果 A2、A3、A6 中的值从 A 变为 B，我甚至不需要它改变 - 但我确实希望如果 B2、B3 或 B6 发生变化，它会改变......）

我还知道，如果我选择范围 B2:B6（仍经过筛选以仅显示“A”命名的行）并按 ALT+;（窗口），它会将选择更改为恰好是 B2:B3,B6，但这在创建公式期间似乎不起作用 :(

只是为了澄清 - 真实数据要大得多，因此逐个选择单元格是不可能的。

有什么想法可以获取对当前选定范围的当前可见部分的引用吗？

答案1

过滤并输入此类信息似乎需要做很多工作。为什么不选择简单的方法呢？

=IF(B2<>"","",AVERAGEIF($A$2:A2,"A",$B$2:B2))

在 C 列中，将其复制下来。在空白单元格旁边，它会将所有“A”的平均值放在其上方。您可以使用 AVERAGEIFS 将其扩展到更多条件，并且如果您希望它对该值的所有观察值取平均值，则可以将条件从“A”更改为 a2（这样它就会选择 B、C 等等）。

我知道这不能回答你的确切问题，但你的实际问题可能很耗时，而这是一个非常快速的解决方法

之后，您可以创建另一列来合并值 - 如果 B 中有值，则取该值，否则取 C 中的值。您也可以将公式更改为：

=IF(B2<>"",B2,AVERAGEIF($A$2:A2,"A",$B$2:B2))

只需在一列中完成所有操作即可。

答案2

我希望得到一个更好的解决方案，所以我等待了，但由于似乎没有更好的解决方案，所以我最终这样做了：

我过滤并选择了想要求平均值的单元格。

例如，在提供的示例中，根据 AI 列的名称“A”进行过滤后得到：

.    A      B
1    Name   Val
2    A      1
3    A      3
6    A      6
7    A      
9    A      2

说实话，我不确定我最初是如何解决这个问题的，它涉及选择我想要的单元格，然后复制它们（强制在每个可见单元格周围出现一个选框），然后在 VBA Watches 窗格中使用某些表达式来获取所有选定单元格的地址列表。

但是，如果我现在尝试在示例中使用鼠标从 B2 到 B6 进行选择并复制。实际上选择了 B2:B3,B6（因为 B4 和 B5 由于过滤器而不可见）。我无法重新创建我的一行代码 :(

话虽如此，我现在发现我可以使用debug.print selection.SpecialCells(xlCellTypeVisible).address它甚至不需要复制操作，所以它实际上可能比我原来的解决方案更好。

如果您打算使用此方法，您应该知道，SpecialCells当基础范围有超过 8192 个区域时，该方法会有一些限制 - 在此处查看有关此问题的更多详细信息和克服此问题的方法：https://stackoverflow.com/a/1375508/25412

相关内容