时间戳

首页 > 解决方案 > 使用逗号分隔的字符串作为参数的 SUMIFS

excel - 使用逗号分隔的字符串作为参数的 SUMIFS

问题描述

我有一个看起来像这样的 SUMIFS

=SUM(SUMIFS('EMPLOYEES'!J:J,'Collections Test'!B:B,AE15,'EMPLOYEES'!F:F,{"00865","00866","00870","16411","16419","00978","10017","90185","90024","00069"},'EMPLOYEES'!C:C,{"STAFF"}))

我想运行一个查询，将单元格“A2”的值设置为：

 "00865","00866","00870","16411","16419","00978","10017","90185","90024","00069"

然后在我的公式中引用 A2，例如：

=SUM(SUMIFS('EMPLOYEES'!J:J,'Collections Test'!B:B,AE15,'EMPLOYEES'!F:F,{A2},'EMPLOYEES'!C:C,{"STAFF"}))

这可能吗？

标签： excel

解决方案

如果员工编号始终为 5 位数字，这将起作用：

=SUMPRODUCT(SUMIFS('EMPLOYEES'!J:J,'Collections Test'!B:B,AE15,'EMPLOYEES'!F:F,MID(A2,ROW(INDIRECT("1:"&(LEN(A2)+1)/8))*8-6,5),'EMPLOYEES'!C:C,"STAFF"))

如果员工编号不总是 5 位数字，那就有点复杂了：

=SUMPRODUCT(SUMIFS('EMPLOYEES'!J:J,'Collections Test'!B:B,AE15,'EMPLOYEES'!F:F,MID(A2,SMALL(IFERROR(IF(MID(A2,ROW(INDIRECT("1:"&LEN(A2)+1))-1,1)=",",ROW(INDIRECT("1:"&LEN(A2)))-1),0),ROW(INDIRECT("1:"&LEN(A2)-LEN(SUBSTITUTE(A2,",",""))+1)))+2,SMALL(IFERROR(IF(MID(A2,ROW(INDIRECT("1:"&LEN(A2)+1))-1,1)=",",ROW(INDIRECT("1:"&LEN(A2)))-1),LEN(A2)),ROW(INDIRECT("1:"&LEN(A2)-LEN(SUBSTITUTE(A2,",",""))+1)))-SMALL(IFERROR(IF(MID(A2,ROW(INDIRECT("1:"&LEN(A2)+1))-1,1)=",",ROW(INDIRECT("1:"&LEN(A2)))-1),0),ROW(INDIRECT("1:"&LEN(A2)-LEN(SUBSTITUTE(A2,",",""))+1)))-3),'EMPLOYEES'!C:C,"STAFF"))

推荐阅读