Google Sheets数据透视表分组自定义公式求和错误及疑问

问题背景 #

我有一个包含三个工作表的Google Sheets文档：

• Budget：设置各分类支出限额
• Transaction：记录带日期的交易及两个账户（含分类+资金来源，如借记卡）
• Spendings：按月份分组展示各分类支出的数据透视表

我在Spendings数据透视表中新增了两列，使用自定义公式从Budget表获取数据：

1. Budget列：

   =IFNA(INDEX('Budget'!$1:$1000; MATCH('Account'; 'Budget'!$A$1:$A$1000); MATCH((EOMONTH('Date'; -1) + 1); 'Budget'!$A$1:$AA$1)); "")
   

2. Difference列：

   =IFNA(INDEX('Budget'!$1:$1000; MATCH('Account'; 'Budget'!$A$1:$A$1000); MATCH((EOMONTH('Date'; -1) + 1); 'Budget'!$A$1:$AA$1)) - SUM('Amount'); "")
   

公式在分组数据中显示正常，但存在两个疑问：

1. 总计行（已标红）数值错误：预期应为$500和$6000，且总计列显示$1000而非预期的$6500，原因是什么？
2. 为何只有将「汇总方式」设为「Custom」时，这两列才会显示数值？

疑问1：总计行数值错误的原因 #

你的自定义公式依赖数据透视表的行级别字段（Account、Date）进行匹配计算，但总计行没有这些行级别的具体值：

• MATCH('Account', ...)在总计行无法找到对应的账户值，导致INDEX/MATCH无法正确匹配到Budget表的总计限额；
• 公式中的SUM('Amount')在总计行仍沿用行级计算逻辑，没有按预期汇总所有限额与实际支出的差额，最终得到错误合计。

修复方案：给公式添加总计行判断逻辑，识别总计行后单独计算限额总和。示例：

• Budget列修正公式：

  =IF(ISBLANK(GETPIVOTDATA("Amount", $A$3)), SUMIF('Budget'!$A:$A, "<>", 'Budget'!B:B), IFNA(INDEX('Budget'!$1:$1000; MATCH('Account'; 'Budget'!$A$1:$A$1000); MATCH((EOMONTH('Date'; -1) + 1); 'Budget'!$A$1:$AA$1)); ""))
  

• Difference列对应修改为：总计行用限额总和减去实际总支出，行级别保持原有逻辑。

疑问2：必须设为「Custom」汇总方式的原因 #

Google Sheets数据透视表的默认汇总方式（求和、计数等）仅支持对原始数据源的单个字段做运算，无法识别透视表分组后生成的衍生字段（如分组后的Date、Account），也无法引用透视表内部的汇总值（如SUM('Amount')）。

只有选择「Custom」汇总方式，才允许使用引用透视表自身字段的自定义公式，从而实现基于分组结果的跨表匹配和计算，因此其他汇总方式无法显示正确数值。

内容的提问来源于stack exchange，提问作者Kohányi Róbert