在 SUMIF 函数的第一个参数中使用不连续的单元格引用

Question 1

从编辑的示例中，您可以使用 Excel 的间接函数来设置引用的单元格。

INDIRECT({"A2","C2","E2","G2","I2"})

然后使用 SUMIF 条件"<0"获取需要求和的值。

把它们放在一起你就会得到所需的公式。

=SUM(SUMIF(INDIRECT({"A2","C2","E2","G2","I2"}),"<0"))

Answer

从编辑的示例中，您可以使用 Excel 的间接函数来设置引用的单元格。

INDIRECT({"A2","C2","E2","G2","I2"})

然后使用 SUMIF 条件"<0"获取需要求和的值。

把它们放在一起你就会得到所需的公式。

=SUM(SUMIF(INDIRECT({"A2","C2","E2","G2","I2"}),"<0"))

Question 2

你的答案有效，但是是解决方法。如果你想处理 A1、C1、E1、G1、 I1、 K1、M1、O1、和Q1，它显然会变得笨重。一个通用的解决方案是使用辅助行。选择你不使用的行；它可能是第 100 行，或另一张表上的第 1 行。（假设你使用第 100 行。）进入单元格并拖动/填充到右侧（或，或任何地方）。然后使用S1U1W1Y1=MOD(COLUMN(),2)A100H100Z100

=SUMIFS(A1:H1, A100:H100, 1, A1:H1, "<0")

计算所需的总和。该A100:H100, 1部分选择（即限制总和）列号为奇数（即为MOD(COLUMN(),2)1）的单元格。A1:H1, "<0"显然，该部分仅从第 1 行的奇数单元格中选择负数。

当然，您可以根据需要将其扩展到任意多的列。如果您不想专门用一行作为辅助行（例如，因为您的数据可以不受限制地垂直增长），您可以使用类似这样的内容AA1:AZ1作为辅助行。

以下是屏幕截图：

您可以看到A2= -105= (-100) + (-4) + (-1)= A1 + C1 +G1。当然E1被跳过，因为它的值是正数。

Answer

你的答案有效，但是是解决方法。如果你想处理 A1、C1、E1、G1、 I1、 K1、M1、O1、和Q1，它显然会变得笨重。一个通用的解决方案是使用辅助行。选择你不使用的行；它可能是第 100 行，或另一张表上的第 1 行。（假设你使用第 100 行。）进入单元格并拖动/填充到右侧（或，或任何地方）。然后使用S1U1W1Y1=MOD(COLUMN(),2)A100H100Z100

=SUMIFS(A1:H1, A100:H100, 1, A1:H1, "<0")

计算所需的总和。该A100:H100, 1部分选择（即限制总和）列号为奇数（即为MOD(COLUMN(),2)1）的单元格。A1:H1, "<0"显然，该部分仅从第 1 行的奇数单元格中选择负数。

当然，您可以根据需要将其扩展到任意多的列。如果您不想专门用一行作为辅助行（例如，因为您的数据可以不受限制地垂直增长），您可以使用类似这样的内容AA1:AZ1作为辅助行。

以下是屏幕截图：

您可以看到A2= -105= (-100) + (-4) + (-1)= A1 + C1 +G1。当然E1被跳过，因为它的值是正数。

Question 3

如果将以下内容放入命名范围：

=ADDRESS(ROW(INDIRECT(SORT(FILTERXML("<Outer><Inner>"&SUBSTITUTE(A16,",","</Inner><Inner>")&"</Inner></Outer>","/Outer/Inner")))),COLUMN(INDIRECT(SORT(FILTERXML("<Outer><Inner>"&SUBSTITUTE(A16,",","</Inner><Inner>")&"</Inner></Outer>","/Outer/Inner")))))

那么该命名范围就可以用作您的参考SUM()。

尝试直接在单元格/SPILL 范围内使用它总是会导致 #VALUE! 错误。我确信可以克服这个问题，但我还没有做到。无论如何，将它放入命名范围可以解决这个问题...

也许命名范围是cow。如果是这样，则以下公式适用于对所选单元格中的负值求和：

=SUM((cow<0)*cow)

（上面使用了 TRUE/FALSE 测试，其结果通过乘法转换为 1 和 0。由于 1*单元格值 = 单元格值，因此该乘法为范围内的失败项提供 0，并为成功单元格提供值。SUM()然后将其全部加起来即可得到所需的结果。）

第一个公式假设您输入了要在 A16 中处理的单元格列表（调整以适合您的实际输入单元格）。它使用该FILTERXML()方法将其转换为SPILL单元格地址列表（作为字符串，尚未引用）并对其进行排序（对此没有必要，只是我之前为一个想法做了一些事情，并没有删除……但这也没有任何害处，如果想从这种情况中提取更多信息，可能会很有用）。然后ADDRESS()使用这些字符串INDIRECT()（稍后会详细介绍）来创建 Excel 可以识别的引用。这一切都在命名区域中，然后命名区域会输出它们的值列表。

奇怪的是，INDIRECT()这似乎是必要的。似乎可以删除它，但随后会得到 #VALUE! 错误。或者只使用它，不需要ADDRESS()，但也会失败，同样的错误。但只要将其包装在另一个函数中，它似乎就可以工作。我注意到一些类似的事情，即使是表面上返回单个单元格的函数，几十年来 MS 反复叫喊的臭名昭著的范围“左上”单元格，实际上也会使用整个范围生成一个数组，如果包装在（几乎）任何其他函数中，会将整个数组传递给外部函数，而不仅仅是它应该生成的单个值。INDIRECT()似乎不愿意以某种方式与这里的 SPILL 功能正常工作，但会将整个数组在工作状态下传递给函数ADDRESS()。

就像需要使用命名范围而不是像人们希望的那样将其全部放入最终公式中一样，我确信我还没有更深层次的理解，其他人可能会在这里补充问题来解释和解决这两个问题。希望如此。

当然，第一个公式通常使用起来效果会很好，LET()但由于它位于命名范围内，所以我没有费心。应该对其进行编辑以使其整洁，以便将来维护电子表格。它隐藏在命名范围内，但出于维护目的，可以将其复制出来，这样可读性和理解性就会很重要。

一个人的非连续单元格列表可以通过多种方式构建。我假设一个简单的所需单元格列表，就像人们可以在公式中将其作为参考输入：A1、M5、C2、E1、K42

当然，它可以按照老式的方式使用数组来完成SUMIF()朋友所做的事情。

但除了眼前的问题之外，这种方法似乎是一种可行的方法，可以将这种不连续的单元格选择转变为工作参考。

我刚刚看了这里旁边的一个“相关问题”（Excel 中非连续单元格的 if 语句中的多个条件) 讨论了将其变成可用命名范围的几种方法，但我无法让它以任何一种方式工作。其中的答案表明，Máté Juhász一旦它是命名范围，COUNTIF()它就应该可以工作。SUMPRODUCT()

有点相关，但就像把所有内容都放入单元格公式中会出现 #VALUE! 错误一样，在函数计算中定义名称LET()并使用它也会得到错误。所以这不会是轻松的结局。

Answer