☰

使用 VLOOKUP 进行 SUMPRODUCT

tag-icon • tag-icon microsoft-excel worksheet-function
使用 VLOOKUP 进行 SUMPRODUCT

我正在尝试获取 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 使用的公式。

相关内容