基于双列唯一值创建带筛选的Excel数据透视表及求和方案问询

核心思路 #

先把原始数据的「第一语言+FLScore」「第二语言+SLScore」拆分成统一的明细行结构，再基于这个重构后的数据源，用PIVOTBY结合FILTER实现可筛选的聚合透视表。

步骤1：重构数据源（动态数组） #

在空白单元格输入以下公式，生成每条原始记录拆分为两行的明细数据（保留Year、Quarter、语言值、对应分数）：

=LET(
    原始表, dtScore,
    总记录数, ROWS(原始表),
    扩展行号, SEQUENCE(总记录数*2),
    关联原始行, CEILING(扩展行号/2, 1),
    HSTACK(
        INDEX(原始表[Year], 关联原始行),
        INDEX(原始表[Quarter], 关联原始行),
        IF(MOD(扩展行号,2)=1, INDEX(原始表[First Language],关联原始行), INDEX(原始表[Second Language],关联原始行)),
        IF(MOD(扩展行号,2)=1, INDEX(原始表[FLScore],关联原始行), INDEX(原始表[SLScore],关联原始行))
    )
)

这个公式会自动生成包含4列的动态数组：Year、Quarter、Language、Score，把每条原始记录的两个语言-分数对拆成独立行，为后续聚合做准备。

步骤2：实现带筛选的透视表 #

1. 先设置筛选器单元格：比如在A1输入要筛选的年份（如2023），B1输入要筛选的季度（如Q1）。
2. 在空白单元格输入以下公式，生成按筛选条件聚合的透视表：

=LET(
    重构数据, 步骤1生成的动态数组,
    筛选年, A1,
    筛选季, B1,
    过滤后数据, FILTER(重构数据, (INDEX(重构数据,,1)=筛选年)*(INDEX(重构数据,,2)=筛选季)),
    PIVOTBY(
        CHOOSECOLS(过滤后数据, 1, 2),  '行维度：Year + Quarter
        CHOOSECOLS(过滤后数据, 3),      '列维度：所有唯一语言值
        CHOOSECOLS(过滤后数据, 4),      '聚合值：分数总和
        SUM,
        0,                             '空值填充为0
        TRUE                           '可选：显示行/列总计
    )
)

修改A1和B1的筛选值，透视表会自动更新。

替代方案（用SUMIFS+动态列） #

如果不想用PIVOTBY，可以结合你之前的UNIQUE和SUMIFS实现：

1. 用=UNIQUE(VSTACK(dtScore[First Language], dtScore[Second Language]))生成所有唯一语言的列标题（比如从M10开始）。
2. 在列标题对应的下方单元格，输入带筛选的SUMIFS公式：

=SUMIFS(dtScore[FLScore], dtScore[First Language], M$10, dtScore[Year], $A$1, dtScore[Quarter], $B$1) + SUMIFS(dtScore[SLScore], dtScore[Second Language], M$10, dtScore[Year], $A$1, dtScore[Quarter], $B$1)

横向填充公式到所有语言列，修改A1/B1的筛选值即可更新结果。

注意事项 #

• 以上函数需要Excel 365/2021及以上版本支持动态数组。
• 若原始表是结构化表（dtScore），公式会自动适配表的新增/删除行。

内容的提问来源于stack exchange，提问作者Stephan