☰

在 Excel 中，根据子数据的存在对分层数据进行求和

tag-icon • tag-icon microsoft-excel worksheet-function visual-basic
在 Excel 中，根据子数据的存在对分层数据进行求和

我有一组像这样的行，显示一些分层数据。

      A  |    B    | C | D | E | F 

1   Task |         | 1 |   | 2 |  
2   Task |         | 1 |   | 2 |  
3   Task |         |   |   | 8 |  
4        | Subtask |   | 2 |   | 4  
5        | Subtask |   | 2 |   | 4  
6   Task |         | 1 |   | 2 |   
7   Task |         | 1 |   | 2 |

A 列和 B 列是任务和子任务，C 列和 D 列是与每个任务/子任务对应的输入。E 列和 F 列是转换列 C 和 D（这里只是将它们翻倍）。

请注意，C3 为空，因为任务没有直接赋值；而是子任务的总和。相应地，E3 为 =SUM(F4:F5)。

目前我手动完成此操作。我想要一个可以执行类似以下伪代码的函数：

IF(
  NOT(C3=""),
  C3,
  SUM_UNTIL(E:E,NOT(""),F:F)
)

因此单元格 E3 将：

  • 如果 C3 不为空，则使用 C3 中的值。
  • 如果 C3 为空白，则对 F4:F 中任意数量的连续空白单元格使用 SUM() 进行求和。

我对 Excel 的掌握相当不错。另外，我是一名程序员，但我对 VB 一窍不通（Linux 程序员），所以我对 VB 解决方案很满意。我精通 JavaScript，所以如果有某种适用于 Excel 的 JavaScript 插件，那也行）。

我正在使用最新版本的 Excel（可能是 2016？Office 最新商业版中的版本）。

答案1

这可能不是最有效的公式，但它有效

=IF(AND(ISBLANK(B3); ISBLANK(C3));
    SUM(INDIRECT("F"&ROW()+1&":
                  F"&MATCH(TRUE; INDEX($B4:$B$16=""; 0); 0)+ROW()-1));
    C3*2)

解释

AND(ISBLANK(B3); ISBLANK(C3));检测何时开始求和公式。它需要两个都单元格B3和C3为空（如果仅使用C3，则C4和C5也将触发总和公式，而不是将其值加倍）

INDIRECT( ... )形成要使用的范围SUM

"F"&ROW()+1&":范围从当前行开始F并低于当前行（在此示例中F4）

F"&MATCH(TRUE; INDEX($B4:$B$16=""; 0); 0)+ROW())F并在列的下一个空白单元格处的行结束B

笔记：请记住，范围从当前行开始和结束一行。还请注意绝对符号 ($）

进一步解释MATCH(TRUE; INDEX($B4:$B$16=""; 0); 0)+ROW()-1 我找到了公式这里

首先，INDEX($B4:$B$16=""; 0)创建一个数组，无论是B4 = ""和B5 = ""等等。您可以通过将公式放在任意列的第 1 行，然后更改0为ROW()-1并将公式向下拖动来查看此数组。

FALSE
FALSE
FALSE
TRUE
and so on...

然后，MATCH(TRUE; ... ; 0)会找到第一个TRUE，也就是第一个下一个空行此行之后（返回索引3）

然而，结果是数组的索引 3， 代替行号所以我们添加当前行的索引，3 + 3，然后瞧！我们有下一个空白行的行号。只需添加-1即可确保我们求和的范围正确F4:F5，而不是F4:F6。

相关内容