如何根据相同单元格值从另一工作表插入对应公式并实现动态引用

问题1：匹配名称返回对应公式而非计算值 #

提供两种可选方案，按需选择即可：

• 无宏手动方案：
  1. 在目标工作表的公式列输入 =XLOOKUP(A2,主表!A:A,主表!B:B,"未匹配",0)（A2为当前行名称列单元格，主表!A:A为主表名称列、主表!B:B为主表公式列），先拿到对应公式的文本内容
  2. 选中所有生成的公式文本，复制后右键粘贴为值，按Ctrl+H打开替换窗口，查找内容输入=，替换内容输入=，点击全部替换，所有文本会自动转为可计算的公式
• VBA自动触发方案（无需手动操作，输入名称后自动生成公式）：
  1. 按Alt+F11打开VBA编辑器，双击需要自动生成公式的工作表名称，粘贴如下代码：

  Private Sub Worksheet_Change(ByVal Target As Range)
      ' 只监测名称列的单单元格变更，示例中名称列为第1列即A列，可按需修改
      If Target.Column <> 1 Or Target.Cells.Count > 1 Then Exit Sub
      Dim wsMain As Worksheet, formulaMap As Range, matchedFormula As String
      ' 替换为你的主表实际工作表名称
      Set wsMain = ThisWorkbook.Worksheets("主表")
      ' 替换为主表的名称+公式对应区域，示例中为A2到B301
      Set formulaMap = wsMain.Range("A2:B301")
      
      On Error Resume Next
      matchedFormula = Application.WorksheetFunction.VLookup(Target.Value, formulaMap, 2, False)
      On Error GoTo 0
      
      If matchedFormula <> "" Then
          ' 示例中公式插入在名称列右侧1列即B列，可按需修改Offset的列数
          Target.Offset(0, 1).Formula = matchedFormula
      End If
  End Sub
  

  2. 保存文件为xlsm格式即可生效，后续在目标工作表名称列输入对应名称，右侧会自动插入匹配的公式。

问题2：设置公式动态引用同一行相对值 #

仅需要调整主表中存储的公式写法即可：

• 不需要固定的行/列不要加绝对引用符号$，比如要引用当前行的B列、C列参与计算，直接写=B1*C1+5即可，不要写成=$B$1*$C$1+5
• 公式插入到对应行时会自动匹配行号，比如=B1*C1插入到第8行的单元格时，会自动变为=B8*C8，符合动态相对引用的要求
• 如果有需要固定引用的参数，再对对应位置加$即可，比如要固定引用主表F1单元格的系数，就写=B1*C1*$主表!$F$1

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