Excel表格公式引用在数据刷新时自动变更的原因及解决方法求助

原因分析 #

• 公式使用了固定行号的单元格引用（如'SQL - SellIn Data'!AH9），而非Excel表格的结构化引用。当通过Microsoft Query刷新数据时，Excel会先清除原有查询结果行，再插入新的数据行，这个过程中Excel会自动调整公式中的行号引用，以匹配新的数据位置，导致引用行号偏移。
• 若公式列位于查询表格的外部区域，刷新时查询返回的行数变化会推动外部列的公式引用向下偏移，Excel默认会维护引用的相对位置关系。

解决方法 #

方法1：使用结构化引用 #

将公式中的固定行号引用替换为表格的结构化引用，结构化引用会自动关联当前行的对应列数据，不受刷新时行号变化的影响。
例如，假设AH列对应的表格列名为Sales，将原公式中的'SQL - SellIn Data'!AH9替换为[@[Sales]]，修改后的公式如下：

IF(AF9=Dates!$F$3,[@[Sales]],IF(Dates!$A$18=Dates!$A$21,[@[Sales]],([@[Sales]]/Dates!$A$27)*Dates!$A$24))

注：如果公式是在查询表格的自定义列中，直接使用[@列名]即可；若在表格外部，可使用'SQL - SellIn Data'[@[Sales]]指定表格。

方法2：将公式添加为表格的自定义列 #

1. 选中查询生成的表格，点击顶部菜单栏的「表格设计」（或「表格工具」）。
2. 选择「添加列」→「自定义列」，在弹出的编辑器中输入公式（使用结构化引用）。
3. 确认后，该公式会自动应用到表格的每一行，刷新数据时公式会随新行自动生成，不会出现引用偏移。

方法3：使用关联函数匹配数据 #

如果无法使用结构化引用，可通过唯一标识列（如订单号、产品ID），用XLOOKUP或INDEX+MATCH函数关联数据，避免直接依赖行号：

IF(AF9=Dates!$F$3,XLOOKUP([@唯一标识列],'SQL - SellIn Data'!$A:$A,'SQL - SellIn Data'!$AH:$AH),IF(Dates!$A$18=Dates!$A$21,XLOOKUP([@唯一标识列],'SQL - SellIn Data'!$A:$A,'SQL - SellIn Data'!$AH:$AH),(XLOOKUP([@唯一标识列],'SQL - SellIn Data'!$A:$A,'SQL - SellIn Data'!$AH:$AH)/Dates!$A$27)*Dates!$A$24))

这种方式通过唯一标识定位数据，不受行号变化影响。

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