另一张表上的 Excel 查找公式

Question

根据我对您的问题的理解，我建议采用以下基于 VBA 的解决方案。

假设 – Sheet1 在 Excel 列 A 到 T 中有数据表。表格列名 A 到 G 映射到实际 Excel 列 E 到 K。Sheet2 在单元格区域 D4 到 E12 中有 ShapeCode 查找表。计算选项设置为自动，这是 Excel 中的默认设置。

在 Sheet1 中按 ALT + F11。VBA 代码编辑器将打开。从“插入”菜单中插入一个模块。名为 Module1 的模块（假设没有其他模块）将被插入，并且名称将出现在左侧窗格中。通常，它还将打开 Module1 的代码编辑器窗口，如果没有，请双击 Module1 以打开 VBA 代码编辑器。以下是以下代码。

Function EvaluateExp(parm1 As String, parm2 As Long, parm3 As Long)

    ' parm1 is the VLOOKUP String
    ' parm2 is the row number
    ' parm3 is the column number of the first column in the table A thru G

    Application.Volatile
    Dim finalstring As String
    finalstring = ""

    For I = 1 To Len(parm1)
          If I Mod 2 = 0 Then          ' it's even position

                Select Case Mid(parm1, I, 1)

                ' Ensure only + opertor is allowed. In future add more Case statements
                ' below in case more math operators are applicable

                    Case "+"
                          finalstring = finalstring & Mid(parm1, I, 1)
                    Case Else
                          EvaluateExp = CVErr(xlErrValue)
                          Exit Function
                End Select

          Else                         ' it's odd position
              J = UCase(Mid(parm1, I, 1))

              ' Construct the expression from the actual columns mapping to A thru G
              ' In future if you insert more columns after G, add new Case statements here below

              Select Case J
                 Case "A"
                     finalstring = finalstring & Cells(parm2, parm3).Address
                 Case "B"
                     finalstring = finalstring & Cells(parm2, parm3 + 1).Address
                 Case "C"
                     finalstring = finalstring & Cells(parm2, parm3 + 2).Address
                 Case "D"
                     finalstring = finalstring & Cells(parm2, parm3 + 3).Address
                 Case "E"
                     finalstring = finalstring & Cells(parm2, parm3 + 4).Address
                 Case "F"
                     finalstring = finalstring & Cells(parm2, parm3 + 5).Address
                 Case "G"
                     finalstring = finalstring & Cells(parm2, parm3 + 6).Address
                 Case Else
                     EvaluateExp = CVErr(xlErrValue)
                     Exit Function
              End Select

           End If

     Next I

   EvaluateExp = Application.Caller.Worksheet.Evaluate(finalstring)

End Function

此代码创建了一个名为 EvaluateExp 的自定义函数，该函数接受 3 个参数。输入字符串、行号和列号。此函数分别将表格列 A 至 G 映射到 Excel 列 E 至 K，并构建预期的 Excel 表达式，包装在 Evaluate 中返回实际结果。

在 Sheet1 的单元格 O3 中输入围绕此自定义函数构建的以下公式，然后将其沿着表格所需的行向下拖动。

=IF(ISBLANK(T3),"",EvaluateExp(VLOOKUP(T3,Sheet2!$D$4:$E$12,2,FALSE),ROW(),COLUMN(E3)))

当您在单元格 T3 及下方输入形状代码时，公式将根据查找返回的表达式返回适用列的预期总和。

下列情况下，公式将返回 #VALUE!

• 查找表中不存在查找值

• 引用表达式中除 A 至 G 之外的其他列

• 查找表中的表达式不完整/无效，例如 A+B+ 或 ABCD 或 A+BC

• 包含除 + 之外的数学运算符的表达式，例如 AC

限制

• 该函数将表格列 A 到 G 映射到实际的 Excel 列 E 到 K。将来如果您在 G 后插入更多列，则需要更新代码以适应相同的情况。

更新

以下用户定义函数代码足够通用，可以支持任何有效的数学表达式。如果无法评估表达式，它将返回 #VALUE! 错误。如果省略括号，则应遵循文档中的运算符优先级这里. 提及表达式中所有适用的运算符，例如 (2*A)+(3*B) 而不是 2A+3B。

Function EvaluateExp(parm1 As String, parm2 As Long, parm3 As Long)

    ' parm1 is the VLOOKUP String
    ' parm2 is the row number
    ' parm3 is the column number of the first column in the table A thru G

    Application.Volatile
    Dim finalstring As String
    finalstring = ""

    For I = 1 To Len(parm1)
        J = UCase(Mid(parm1, I, 1))
        Select Case J
             Case "A"
                  finalstring = finalstring & Cells(parm2, parm3).Address
             Case "B"
                  finalstring = finalstring & Cells(parm2, parm3 + 1).Address
             Case "C"
                  finalstring = finalstring & Cells(parm2, parm3 + 2).Address
             Case "D"
                  finalstring = finalstring & Cells(parm2, parm3 + 3).Address
             Case "E"
                  finalstring = finalstring & Cells(parm2, parm3 + 4).Address
             Case "F"
                  finalstring = finalstring & Cells(parm2, parm3 + 5).Address
             Case "G"
                  finalstring = finalstring & Cells(parm2, parm3 + 6).Address
             Case Else
                  finalstring = finalstring & J

        End Select

    Next I


   EvaluateExp = Application.Caller.Worksheet.Evaluate(finalstring)

End Function

Answer 1