☰

Excel 公式用于计算 104 个可能单元格中 52 个空白单元格的总和

tag-icon • tag-icon microsoft-excel
Excel 公式用于计算 104 个可能单元格中 52 个空白单元格的总和

我正在尝试找到一个 Microsoft Excel 公式，可以让我计算 52 周的总和。

Excel 工作表设置了 104 周的数据，其中有些周的数据为空白。我想计算最近 52 周的数据总和。Excel 工作表有多行数据，因此没有两行是相同的（即不同位置的空白）。

我知道这很复杂，而且我以为自己擅长 Excel，但这却难倒了我认识的每个人。

答案1

您可以使用一些辅助列来实现您的要求。由于问题不包含任何示例数据，因此需要做出一些假设。在这里，我假设您的数据如下面 A 列和 B 列所示，其中 A 是您的周数（按降序排序），B 列是您要求和的值。

此外，为了将所有数据放入一张屏幕截图中，我只对最近 10 周的填充数据进行汇总。根据需要调整第二个公式。

第一辅助列（C 列）：将 0（零）作为列标题，并在单元格 C2 中插入以下 COUNTA 公式。此公式计算从单元格 B2 开始到当前行的非空白单元格的数量。将其向下拖动。

    =COUNTA($B$2:B2)

第二个辅助列（D 列）：在单元格 D2 中插入以下 IF(AND) 公式。如果前一列的值等于上一行加 1，则该行将编码为“是”，如果该值小于 11，则将该行编码为 AND。根据您的情况调整为 53。将其向下拖动。

    =IF(AND(C1+1=C2,C2<11),"yes","no")

最后，使用以下 SUMIFS 公式（位于单元格 G2 中），对辅助 D =“是”的值求和。

    =SUMIFS(B:B,D:D,"yes")

在此处输入图片描述

答案2

您可以使用以下公式：

=LET(  List,  FILTERXML("<group><element>"&TEXTJOIN("</element><element>",TRUE,A1:A104)&"</element></group>","/*/*"),

        IF(COUNT(List)<52,"Not enough weeks",SUM(INDEX(List,SEQUENCE(52,1,COUNT(List),-1),1)))
      )

它用于TEXTJOIN()生成 104 个值的字符串（此处为 A1:A104，根据需要进行调整），其重要功能是删除所有空白单元格，因此只保留值单元格。它使用FILTERXML()值组元素之间所需的 HTML 标记序列作为分隔符。这省去了一个SUBSTITUTE()步骤。

然后调整字符串的前后顺序，使之可以被接受，FILTERXML()并且该函数将值从单个字符串返回为离散值数组，按照它们最初出现的顺序。但所有空单元格都已从数组中删除。

您想要最新的 52 个值，因此首先检查是否有那么多值，如果计数少于 52，则返回“值不够”。

如果 >=52，则下一步反转它们的原始顺序并选择其中的前 52 个。然后将它们相加。

因为该FILTERXML()步骤是一大段公式，所以使用它LET()可以让内容更直观。这不是必需的，但似乎值得。

重要的因素是找到要使用的“非空白”单元格，然后选择其中的最后 52 个。

下面的方法也可以正常工作，而且会更短，因为它永远不会遇到“公式内部”字符串的长度限制FILTERXML()。FILTERXML()但它有两个优点，一个是它可以为您提取编号元素，另一个可以构建更复杂的 HTML 字符串，而没有太多麻烦，从而为您提供两列或更多列数据，而不仅仅是一组数据。

=LET(  List,  FILTER(A1:A104,A1:A104<>0),

        IF(COUNT(List)<52,"Not enough weeks",SUM(INDEX(List,SEQUENCE(52,1,COUNT(List),-1),1)))
      )

还有其他方法可以反转列表。一种方法是使用该FILTERXML()方法，对记录执行“from:to”操作，记录编号基于值，COUNT(List)因此它取最后 52 条记录。在函数中名为“XPath”的字符串中执行此操作FILTERXML()，“//“在上面第一个公式的末尾。它需要看起来像：

"/*/*[last]" or "/*/*[43]"

但是可以使用公式构建字符串，因此可以使用其中第一个，但可以使用第二个，而不是第二个：

"/*/*["  &  (COUNT(List) - 52 + 1) &  "]"

得到第一个要使用的。看起来是一场噩梦，但做了一两次之后就很清楚了。对于您的问题来说不是必需的，但可以扩展您未来可能使用的方法。例如，可以像这样提取一条记录以进行比较IF()。可以用于条件格式中，以解决您的问题，也许可以更改背景颜色以指示总和涵盖两年的部分，而不仅仅是一年。尽管考虑到您的数据并不复杂，有更简单的方法来测试它！

相关内容