![使用 VLOOKUP 进行 SUMPRODUCT](https://linux22.com/image/1446206/%E4%BD%BF%E7%94%A8%20VLOOKUP%20%E8%BF%9B%E8%A1%8C%20SUMPRODUCT.png)
我正在尝试获取 TC Ref 列和 Earn Type 中匹配的乘积和总和。
例如,7926 对于 REG 将是 (45.32 x -3) + (45.32 x -3) + (45.32 x -4),对于 OT 将是 (45.32 x -4)。
我尝试使用 VLOOKUP 进行 SUMPRODUCT,但只得到匹配的第一行。我尝试将值返回到另一张表。
=SUMPRODUCT(VLOOKUP([@[TC Ref]],Table_timecard,2,FALSE),VLOOKUP([@[TC Ref]],Table_timecard,3,FALSE))
当前公式:(有没有更好的方法来做到这一点?)
=SUMPRODUCT(--(Timecard!$A$2:$A$5574=[@[TC Ref]])*(Timecard!$J$2:$J$5574="REG"),Timecard!$H$2:$H$5574,Timecard!$I$2:$I$5574)
组合公式:(有没有办法组合 J2:J5574 来查找所有类型 REG、ADDTL、FMHOL、SHIFT)
=SUMPRODUCT(--(Timecard!$A$2:$A$5574=[@[TC Ref]])*(Timecard!$J$2:$J$5574="REG"),Timecard!$H$2:$H$5574,Timecard!$I$2:$I$5574)+SUMPRODUCT(--(Timecard!$A$2:$A$5574=[@[TC Ref]])*(Timecard!$J$2:$J$5574="ADDTL"),Timecard!$H$2:$H$5574,Timecard!$I$2:$I$5574)+SUMPRODUCT(--(Timecard!$A$2:$A$5574=[@[TC Ref]])*(Timecard!$J$2:$J$5574="FMHOL"),Timecard!$H$2:$H$5574,Timecard!$I$2:$I$5574)+SUMPRODUCT(--(Timecard!$A$2:$A$5574=[@[TC Ref]])*(Timecard!$J$2:$J$5574="SHIFT"),Timecard!$H$2:$H$5574,Timecard!$I$2:$I$5574)
示例数据:
TC Ref Pay Rate Hours Earn Type
7926 $45.32 -3 REG
7926 $45.32 -3 REG
7926 $45.32 -4 OT
7927 $45.32 3 REG
7927 $45.32 7 REG
7927 $45.32 3 DT
7927 $45.32 3 OT
答案1
SUMPRODUCT
您可以使用像这样的单个公式
=SUMPRODUCT((Timecard!$A$2:$A$5574=[@[TC Ref]])*ISNUMBER(MATCH(Timecard!$J$2:$J$5574,{"REG","ADDTL","FMHOL","SHIFT"},0)),Timecard!$H$2:$H$5574,Timecard!$I$2:$I$5574)
MATCH
函数J2:J5574
与包含 4 个值的“数组常量”匹配 - 如果匹配则MATCH
返回一个数字,否则会得到 #N/A 错误 -ISNUMBER
然后函数将其转换为TRUE
或,FALSE
我们可以在SUMPRODUCT
将其乘以第一个条件时使用它
答案2
我可以使用辅助列使其工作,如下所示
TC 参考工资率工时价值收入类型 7926 45.32 -3 -135.96 登记 7926 45.32 -3 -135.96 登记 7926 45.32 -4 -181.28 加时赛 7927 45.32 3 135.96 登记 7927 45.32 7 317.24 登记 7927 45.32 3 135.96 DT 7927 45.32 3 135.96 加时
最后,如果你在 TC Ref 列中列出唯一值,例如
7926 -453.2 7927 725.12
上图显示了针对每个 TC Ref 使用的公式。