在 SUMIF 函数的 sum_range 参数中使用 LEFT（Excel）

Question

我有很多彼此叠在一起的列，就像这样：

我想找到此范围内以 开头的所有单元格1a，然后对后面的内容求和。因此，在此示例中，我们找到1a10和1a20，因此结果将是 10+20，即30。

困难的是a，之后可能会有一个或者两位数，我还想将其扩展为在之前处理两位数a。因此我们可以：

1a10 1a1 11a1 11a10

我曾尝试用我所学到的关于SUMIF、SUMIFS和的知识进行实验SEARCH，但是虽然我能够（在某种程度上）检测到要选择哪些单元格，但切断它的开头，尤其是当结尾和开头的长度可能不同时，只会剩下一堆杂乱的堆叠IF语句和一个不起作用的公式。

不幸的是，我不能使用宏，而且我的学校只使用 Excel 2013，但我希望这仍然可行。

谢谢。

Answer 1

您可以使用数组公式来执行此操作

=SUM((LEFT($A$2:$A$8,2)="1a")*(IF(ISNUMBER(--RIGHT($A$2:$A$8,LEN($A$2:$A$8)-2)),RIGHT($A$2:$A$8,LEN($A$2:$A$8)-2),0)))

因为这是一个数组公式，所以您需要使用Control++而不是仅仅。ShiftEnterEnter

此外，由于它是一个数组公式，您需要避免完整的列引用，例如A:A

当您看到公式周围有 { } 时，您便知道输入正确。请注意，{ } 可能无法手动添加。

更新

如果您希望使公式更加强大，以处理更长的搜索字符串（例如 22a），并允许您使用单元格作为要求和的 ID 字符串，则可以使用以下公式。

=SUM((LEFT($A$1:$A$8,LEN(TRIM(C2)))=C2)*(IF(ISNUMBER(--RIGHT($A$1:$A$8,LEN($A$1:$A$8)-LEN(C2))),RIGHT($A$1:$A$8,LEN($A$1:$A$8)-LEN(C2)),0)))

请注意，这仍然是一个数组公式。此外，范围已扩大，以包括您要搜索的键后面的一位数字的数据值。

小更新，将改为-RIGHT...--RIGHT但显然可能甚至不需要--。

Answer 2

我不想替你做工作，但我可以给你一些建议......

您可以使用一个或两个长公式来实现这一点，但您可以将其拆分成几个单独列中的公式，以便于理解。例如，在 B1 中，您可以使用

=IF(LEFT(A2,2)="1a",1,0)) 这将为以“1a”开头的单元格返回 1，为不是“1a”开头的单元格返回 0。

在 C1 中，你可以使用

=LEN(A2)这将返回 A2 中的字符数。

从那里开始用公式编写 IF 语句（下面的伪代码，而不是实际公式）。

"If A2=1 AND C1=4, return RIGHT(A2,2)"

"If A2=1 AND C1=3, return RIGHT(A2,1)"

我所做的只是设置工作表，将 1a10 中的“10”放入其自己的列中。然后，您可以对结果数字进行任何您想要的代数运算。

Answer 3

以下是另一种方法SUBSTITUTE：

=SUM(IFERROR(SUBSTITUTE(A1:A7,C1,"")*1,0))

怎么运行的：

• =SUM(IFERROR(SUBSTITUTE(A1:A7,C1,"")*1,0))
• SUBSTITUTE(A1:A7,C1,"")-1a从所有行中删除
• SUBSTITUTE(...)*1- 尽可能将条目转换为数字（仅限1a先前删除的行），其他将导致错误
• IFERROR(...,0)- 将错误转换为0

这也是一个数组公式，因此输入后需要按 CTRL+SHIFT+ENTER。