在Excel中将UNIQUE与 SUMPRODUCT一起使用

编程语言 2026-07-08

我的合同数据包含以下列：

ContractID（唯一、主键），每个合同都对应以下字段：
FiscalYear（从Contract End Date计算得出的字段）
CostCenter（文本字段）
TotalApprovedCost（分配给每个唯一合同的单一美元金额）

每个ContractID有多条计费行，包括：

Session Date（对某项服务计费的日期）
Billed（已对合同计入的金额）

我想能够显示每份合同已计费的百分比。因此，我想能够对所有已计费金额求和（按FiscalYear和 CostCenter排序），并与每个合同的唯一TotalApprovedCost进行比较。我还想能够将这两笔金额相加，以便查看各财政年度内各成本中心的总完成百分比。

我一直在使用SumProduct结合筛选进行求和，并且我一直在使用（Rows(Unique(Filter())) 来统计某些东西的唯一实例数量（比如合同数量）。但如何将TotalApprovedCost加在一起而不对所有单独的行进行重复计数呢？

enter image description here 1

期望得到的结果如下

合同ID	财年	成本中心	已批准总成本	计费日期	已计费金额
1	2026	Blue	$200	1/1/2026	50
1	2026	Blue	$200	1/18/2026	50
1	2026	Blue	$200	1/31/2026	50
2	2025	Blue	$700	3/2/2025	100
2	2025	Blue	$700	4/12/2025	120
2	2025	Blue	$700	6/12/2025	50
3	2026	Red	$1,200	5/12/2026	100
3	2026	Red	$1,200	5/13/2026	50
3	2026	Red	$1,200	5/14/2026	800
4	2025	Red	$350	3/2/2025	110
4	2025	Red	$350	4/12/2025	75
4	2025	Red	$350	6/12/2025	50
5	2025	Blue	$900	3/2/2025	200
5	2025	Blue	$900	4/12/2025	250
6	2026	Red	$350	5/12/2026	50
6	2026	Red	$350	5/13/2026	50
6	2026	Red	$350	5/14/2026	50

enter image description here 2

已批准总成本	红色	蓝色	合计
2026	1550	200	1750
2025	1600	900	2500
已计费
2026	1100	150	1250
2025	235	720	955
完成百分比
2026	71%	75%	71%
2025	15%	80%	38%

解决方案

正如我在别人给出解决方案之前所说，使用 PIVOTBY() 函数应当简单且优雅，因此方法如下：

• 单元格 H2 使用的公式：

=LET(
     _a, UNIQUE(B2:D18),
     PIVOTBY(CHOOSECOLS(_a, 1),
             CHOOSECOLS(_a, 2),
             CHOOSECOLS(_a, 3),
             SUM, , 0, -1))

上述公式给出了Total Approved Cost的输出。

• 单元格 H6 使用的公式：

=LET(
     _a, UNIQUE(B2:F18),
     PIVOTBY(CHOOSECOLS(_a, 1),
             CHOOSECOLS(_a, 2),
             CHOOSECOLS(_a, -1),
             SUM, , 0, -1))

上述公式给出了Billed的输出。

这里同样适用 ¥

=LET(
     _a, A2:F18,
     PIVOTBY(CHOOSECOLS(_a, 2),
             CHOOSECOLS(_a, 3),
             CHOOSECOLS(_a, -1),
             SUM, , 0, -1))

• 单元格 H10 使用的公式：

=VSTACK(TAKE(H2#, 1), 
 HSTACK(DROP(TAKE(H2#, , 1), 1), 
 DROP(H6#, 1, 1) / DROP(H2#, 1, 1)))

上述公式给出了 % Completed的输出。

如果你也想使用一个单一的动态数组公式来实现：

=LET(
     _C, CHOOSECOLS,
     _U, UNIQUE,
     _L, LAMBDA(x,y,z, PIVOTBY(x, y, z, SUM, , 0, -1)),
     _a, DROP(A:.F, 1),
     _b, _U(TAKE(_a, , 4)),
     _d, _U(DROP(_a, , 1)),
     _e, _L(_C(_b, 2), _C(_b, 3), _C(_b, 4)),
     _f, _L(_C(_d, 1), _C(_d, 2), _C(_d, 5)),
     _g, DROP(_f, 1, 1) / DROP(_e, 1, 1),
     _i, HSTACK("Total Approved Cost", DROP(TAKE(_e, 1), , 1)),
     IFNA(VSTACK(_i, DROP(_e, 1), 
                 "Billed", DROP(_f, 1), 
                 "% Completed", HSTACK(DROP(TAKE(_e, , 1), 1), _g)), ""))

上述公式将仅用一个公式给出完整输出！

¥ 在最后一个单一数组公式中，我为已计费行使用了 UNIQUE() 函数，我们可以排除它，因为如果存在两条完全相同的行，它会丢弃其中一条，这在从OP的描述来看在技术上并不正确，尽管并非冗余。因此，可以按你的需要尝试下面这个版本：

=LET(
     _C, CHOOSECOLS,
     _L, LAMBDA(x,y,z, PIVOTBY(x, y, z, SUM, , 0, -1)),
     _a, DROP(A:.F, 1),
     _b, UNIQUE(TAKE(_a, , 4)),
     _d, DROP(_a, , 1),
     _e, _L(_C(_b, 2), _C(_b, 3), _C(_b, 4)),
     _f, _L(_C(_d, 1), _C(_d, 2), _C(_d, 5)),
     _g, DROP(_f, 1, 1) / DROP(_e, 1, 1),
     _i, HSTACK("Total Approved Cost", DROP(TAKE(_e, 1), , 1)),
     IFNA(VSTACK(_i, DROP(_e, 1),
                 "Billed", DROP(_f, 1),
                 "% Completed", HSTACK(DROP(TAKE(_e, , 1), 1), _g)), ""))

Excel