Filter公式无法返回全部值及宏更新后出现@前缀的问题求助

1. 修正公式基础语法错误 #

你的原公式存在两处语法问题：EEID7J:J缺少工作表引用的感叹号，公式末尾缺少右括号。完整的基础公式应为：

=FILTER(EEID7!N:N, EEID7!J:J=Statements!A1)

2. 实现动态工作表引用（通过A1切换目标表） #

由于A1存储的是目标工作表名称（如EEID6、EEID7），硬编码工作表名称无法动态切换，需用INDIRECT函数拼接完整区域引用：

动态获取Terr值的公式： #

=FILTER(INDIRECT(A1&"!N:N"), INDIRECT(A1&"!J:J")=Statements!A1)

动态获取commission值的公式（假设commission在M列，可自行替换列标）： #

=FILTER(INDIRECT(A1&"!M:M"), INDIRECT(A1&"!J:J")=Statements!A1)

3. 消除@前缀并恢复溢出行为 #

公式前的@是Excel自动触发了隐式交集，抑制了FILTER的数组溢出效果，仅返回第一个匹配值。解决方法：

• 手动删除@符号，新版Excel直接按Enter即可启用动态数组溢出；旧版Excel需按Ctrl+Shift+Enter以数组公式形式运行。
• 检查宏代码：如果宏通过Range.Formula设置公式，改为使用Range.Formula2（新版Excel支持动态数组的属性），避免宏自动添加@。示例代码片段：

  ' 错误写法（易触发隐式交集）
  ' Range("B1").Formula = "=FILTER(INDIRECT(A1&""!N:N""), INDIRECT(A1&""!J:J"")=Statements!A1)"
  ' 正确写法
  Range("B1").Formula2 = "=FILTER(INDIRECT(A1&""!N:N""), INDIRECT(A1&""!J:J"")=Statements!A1)"
  

4. Access链接数据的注意事项 #

• 及时刷新链接数据：点击Excel的数据选项卡 → 全部刷新，确保加载最新的Access数据，避免FILTER匹配旧数据。
• 统一单元格格式：确保目标表J列（Sales name）与Statements!A1的格式一致（均设为文本格式），避免因格式不匹配导致无结果或错误匹配。
• 优化引用范围：若Access链接表数据量较大，避免使用整列引用（如N:N），改为引用实际数据范围（如INDIRECT(A1&"!N1:N1000")），提升公式运行效率。

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