Формула поиска в Excel на другом листе

Question

У меня есть 2 рабочих листа. Я хочу иметь возможность рассчитать номинальную длину на основе кода формы, которые имеют разные формулы для разных кодов формы. Параметры указаны в номинальной длине. Пожалуйста, смотрите фотографии, чтобы получить более четкую картину.

Номинальная длина:

Формула:

Answer 1

Исходя из моего понимания вашего вопроса, я предлагаю следующее решение на основе VBA.

Предположение - Sheet1 имеет таблицу данных в столбцах Excel от A до T. Имена столбцов таблицы От G до сопоставления с фактическими столбцами Excel E до K. Sheet2 имеет таблицу поиска ShapeCode в диапазоне ячеек от D4 до E12. Для параметров расчета установлено значение «Автоматически», которое по умолчанию используется в Excel.

На листе 1 нажмите 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, возвращает фактический результат.

В поле «Лист1» в ячейке O3 введите следующую формулу, построенную вокруг этой пользовательской функции, а затем перетащите ее вниз по необходимым строкам таблицы.

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

Когда вы вводите код формы в ячейку T3 и ниже, формула возвращает ожидаемую сумму применимых столбцов в соответствии с выражением, возвращаемым поиском.

Формула должна вернуть # ЗНАЧЕНИЕ! в следующих ситуациях

• Значение поиска не существует в таблице поиска

• Ссылка на столбцы, отличные от A до G в выражении

• Неполное / неверное выражение в справочной таблице, например, A+B+ или ABCD или A+BC

• Выражение, содержащее математический оператор, отличный от +, например, AC

Ограничения

• Функция отображает столбцы таблицы от G до фактических столбцов Excel от E до K. В будущем, если вы добавите больше столбцов после G, вам необходимо обновить код, чтобы приспособить его.

---

Обновить

Следующий код для пользовательской функции достаточно универсален, чтобы поддерживать любое допустимое математическое выражение. Если выражение не может быть оценено, оно должно вернуть # ЗНАЧЕНИЕ! ошибка. Если скобки опущены, он должен следовать за приоритетом оператора, как описано здесь. Упомяните все применимые операторы в выражении, например (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