在MAP/MAKEARRAY中调用Excel加载项函数的技术咨询

需求 #

使用Office 365桌面版时，希望在MAP或MAKEARRAY函数中调用加载项函数，实现随现有溢出区域动态溢出的效果（因无法确定溢出行数，动态性是必需的）。但所有尝试均返回#CALC!错误：Unsupported Calculation - This calculation is not supported by this version of Excel.

核心问题 #

1. 是否可以在MAP或MAKEARRAY（推测底层逻辑类似MAP）中调用加载项函数？
2. 错误提示“此Excel版本不支持该计算”，是否存在支持该操作的版本？
3. 若无法在MAP中调用加载项函数，有哪些替代处理策略？

测试示例（原生函数可行，加载项函数报错） #

内联LAMBDA示例 #

A1为基础溢出区域（5行），其余列基于A1#迭代：

• (正常运行) A1: =SEQUENCE(5)
• (正常运行) B1: =MAP($A1#, LAMBDA(item, item))
• (正常运行) C1: =MAP($A1#, LAMBDA(item, MOD(item,2)))
• (报错) D1: =MAP($A1#, LAMBDA(item, INSTALLED_ADD_IN.FUNCTION(item)))
• (正常运行) E1: =MAKEARRAY(ROWS($A1#), 1, LAMBDA(row, col, row))
• (正常运行) F1: =MAKEARRAY(ROWS($A1#), 1, LAMBDA(row, col, MOD(row,2)))
• (报错) G1: =MAKEARRAY(ROWS($A1#), 1, LAMBDA(row, col, INSTALLED_ADD_IN.FUNCTION(row)))

注：D1和G1显示#NAME!是因为INSTALLED_ADD_IN.FUNCTION是加载项函数的占位符，实际使用真实加载项函数时会返回#CALC!错误，无论用内联LAMBDA还是命名LAMBDA都会报错。

命名LAMBDA示例 #

在名称管理器中创建命名LAMBDA：

name: addin_fn
refers to: =LAMBDA(id, INSTALLED_ADD_IN.FUNCTION(id))

• (正常运行) H1: =addin_fn($A1) // 仅单次调用，非数组/迭代器输出
• (报错) I1: =MAP($A1#, addin_fn)
• (报错) J1: =MAKEARRAY(ROWS($A1#), 1, LAMBDA(row, col, addin_fn(row)))

已尝试的其他方法 #

• 将LAMBDA作为参数传递，在MAP/MAKEARRAY内部的LAMBDA中调用
• 使用LET在MAP/MAKEARRAY的LAMBDA内部和外部创建加载项函数的本地引用，直接调用或链式传递给内部LAMBDA
• 结合addin_fn(INDEX(array, row))变体，移除数组内部引用尝试规避错误

解答 #

1. 是否支持在MAP/MAKEARRAY中调用加载项函数？
   目前Excel 365桌面版的MAP、MAKEARRAY等迭代类LAMBDA函数，不支持在其内部调用COM/VSTO加载项函数，也不支持调用依赖外部加载项的自定义函数（UDF），这是当前版本的功能限制，并非语法错误导致。

2. 是否有支持该操作的版本？
   截至2024年，Excel 365订阅版的月度通道、半年度通道均未开放此功能。微软官方未明确说明后续是否会支持，需留意Office官方更新公告。

3. 替代处理策略

• 手动拖拽填充：若能预估最大行数，可先在第一行调用加载项函数，再拖拽填充至最大行，后续新增行需手动补充。
• VBA自动刷新：编写VBA宏监听工作表变更，自动遍历溢出区域调用加载项函数并写入结果，示例代码：

  Sub RefreshAddInResults()
      Dim sourceRange As Range
      Dim targetRange As Range
      Dim cell As Range
      
      Set sourceRange = Range("A1#") ' 源溢出区域
      Set targetRange = sourceRange.Offset(0, 1) ' 结果输出区域
      
      For Each cell In sourceRange
          targetRange.Cells(cell.Row - sourceRange.Row + 1).Value = Application.Run("INSTALLED_ADD_IN.FUNCTION", cell.Value)
      Next cell
  End Sub
  

  可将宏绑定到工作表Change事件，实现源数据变更时自动刷新结果。
• 迁移加载项逻辑：若加载项函数逻辑可剥离，将其重写为Excel原生函数组合或纯自定义LAMBDA函数，彻底摆脱外部加载项依赖。
• Power Query处理：将源数据导入Power Query，在查询编辑器中调用加载项函数（若Power Query兼容该加载项）或重写逻辑后，将结果加载回工作表并设置自动刷新。

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