Fórmula de pesquisa do Excel em outra planilha

Question

Eu tenho 2 planilhas. Eu quero ser capaz de calcular o comprimento nominal com base no código de forma que tem diferentes fórmulas para diferentes códigos de forma. Os parâmetros estão no comprimento nominal. Por favor, veja fotos para obter uma imagem mais clara.

Comprimento nominal:

Fórmula:

    

Answer 1

Com base no meu entendimento da sua pergunta, estou sugerindo uma solução baseada em VBA a seguir.

Suposição - Sheet1 tem a tabela de dados em colunas do Excel A a T. Nomes de coluna da tabela A a G mapear para as colunas reais do Excel E a K. Sheet2 tem a tabela Lookup ShapeCode no intervalo de células D4 a E12. As opções de cálculo são definidas como Automático, que é a configuração padrão no Excel.

NaFolha1pressioneALT+F11.EditordecódigoVBAdeveabrir.NomenuInserir,insiraummódulo.MódulochamadoModule1(Assumindoquenãoháoutrosmódulospresentes)deveserinseridoeonomedeveaparecernopainelesquerdo.Principalmente,eletambémdeveabrirajaneladoeditordecódigodoMódulo1;casocontrário,cliqueduasvezesnoMódulo1paraabriroeditordeCódigodoVBA.Aquipassadoocódigoabaixo.

FunctionEvaluateExp(parm1AsString,parm2AsLong,parm3AsLong)'parm1istheVLOOKUPString'parm2istherownumber'parm3isthecolumnnumberofthefirstcolumninthetableAthruGApplication.VolatileDimfinalstringAsStringfinalstring=""

    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

Este código cria uma função personalizada chamada EvaluateExp que aceita 3 parâmetros. String de entrada, número da linha & Número da coluna. Essa função mapeia as colunas da tabela A a G para as colunas E e K do Excel, respectivamente, e constrói a expressão esperada do Excel, agrupada em Evaluate, que retorna o resultado real.

Na Planilha1 da célula O3, insira a seguinte fórmula criada em torno desta função personalizada e, em seguida, arraste-a para baixo nas linhas necessárias da tabela.

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

Conforme você insere o Código de forma na célula T3 e abaixo, a fórmula retorna a soma esperada de colunas aplicáveis conforme a expressão retornada pela pesquisa.

A fórmula deve retornar #VALUE! nas seguintes situações

• O valor de pesquisa não existe na tabela de pesquisa

• Referindo-se a colunas diferentes de A a G na expressão

• Expressão Incompleta / Inválida na tabela de consulta, por ex. A + B + ou ABCD ou A + BC

• Expressão contendo operador de matemática diferente de +, e. A-C

Limitações

• A função mapeia as colunas de tabela A a G para as colunas reais do Excel de E a K.  No futuro, se você inserir mais colunas após G, será necessário atualizar o código para acomodar o mesmo.

Atualizar

OcódigoaseguirparaFunçãoDefinidapeloUsuárioégenéricoosuficienteparasuportarqualquerexpressãomatemáticaválida.Seaexpressãonãopuderseravaliada,eladeveretornar#VALUE!erro.Secolchetesforemomitidos,eledeveseguiraprecedênciadooperadorconformedocumentado aqui . Mencione todos os operadores aplicáveis na expressão, e. (2 * A) + (3 * B) e não 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