![在 SUMIF 函数的 sum_range 参数中使用 LEFT(Excel)](https://linux22.com/image/1571967/%E5%9C%A8%20SUMIF%20%E5%87%BD%E6%95%B0%E7%9A%84%20sum_range%20%E5%8F%82%E6%95%B0%E4%B8%AD%E4%BD%BF%E7%94%A8%20LEFT%EF%BC%88Excel%EF%BC%89.png)
我有很多彼此叠在一起的列,就像这样:
我想找到此范围内以 开头的所有单元格1a
,然后对后面的内容求和。因此,在此示例中,我们找到1a10
和1a20
,因此结果将是 10+20,即30
。
困难的是a
,之后可能会有一个或者两位数,我还想将其扩展为在之前处理两位数a
。因此我们可以:
1a10 1a1 11a1 11a10
我曾尝试用我所学到的关于SUMIF
、SUMIFS
和的知识进行实验SEARCH
,但是虽然我能够(在某种程度上)检测到要选择哪些单元格,但切断它的开头,尤其是当结尾和开头的长度可能不同时,只会剩下一堆杂乱的堆叠IF
语句和一个不起作用的公式。
不幸的是,我不能使用宏,而且我的学校只使用 Excel 2013,但我希望这仍然可行。
谢谢。
答案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
但显然可能甚至不需要--。
答案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”放入其自己的列中。然后,您可以对结果数字进行任何您想要的代数运算。