Substituindo um IF aninhado no Excel por um vlookup de texto de fórmula

Question

Estou trabalhando em uma planilha para Planejamento de Requisitos de Materiais (apenas por diversão). Um problema é com as regras de dimensionamento de lotes. Eu tenho que usar funções OFFSET e INDIRECT em muitos lugares, e as fórmulas são extremamente difíceis de ler (e são longas!).

Existe alguma maneira de colocar os textos da fórmula para diferentes regras em uma matriz e selecioná-los com base em um VLOOKUP? O que eu espero é encontrar uma função que faça o seguinte:

=FORMULAFROMTEXT(VLOOKUP(rule,$A1:$B5,formula, FALSE))

onde a regra pode ser uma das "L4L", "FOQ", etc, e a tabela seria semelhante a

L4L     L4L-formula
FOQ     FOQ-formula
...

Isso parece muito mais limpo (e mais curto) para mim do que

IF(rule="L4L",L4L-formula,IF(rule="FOQ",FOQ-formula,IF(...))).

    

Answer 1

Você precisará criar uma função usando o VBA.

Com sua planilha do Excel aberta, inicie o Editor do Visual Basic fazendo o seguinte:

• num PC com Windows
  prima Alt + F11 .
• em um Mac
  pressione FN + ALT + F11

depois clique em Insert then Module .

Uma nova janela de módulo aparece no lado direito do Editor do Visual Basic.

Copie o seguinte na nova janela do módulo:

Function Eval(Ref As String)
Application.Volatile
Eval = Evaluate(Ref)
End Function

Agora você pode fechar o Editor do Visual Basic já que criou uma função de fórmula EVAL(cell reference) .

Agora, use esta nova função enquanto usa VLOOKUP() para a fórmula.

Se a fórmula deve ser construída de acordo com a linha em que os dados estão, por exemplo,

      ¦  A  ¦  B  ¦    C    ¦
-----------------------------
   1  ¦  2  ¦  2  ¦  =A1+B1 ¦
-----------------------------
   2  ¦  4  ¦  2  ¦  =A2+B2 ¦
-----------------------------

e as células C1 e C2 são L4L-formula

Então você precisa ter em mente a volatilidade da fórmula. Você não pode simplesmente colocar A1 + B1 para L4L-formula por exemplo. Você teria que inserir sem o sinal de igual (=) "B"&ROW()&"+C"&ROW() para L4L formula .

Você precisará criar uma coluna auxiliar para a fórmula resultante.

Se a sua lista de fórmulas é a seguinte:

      ¦  A  ¦       B       ¦
-----------------------------
   1  ¦ L4L ¦  L4L-formula  ¦  Let's say this is F + G
-----------------------------
   2  ¦ FOQ ¦  FOQ-formula  ¦  Let's say this is F x G
-----------------------------

Se L4L-formula for F + G , conforme indicado antes, você precisará inserir "F"&ROW()&"+G"&ROW() na célula B1

Se FOQ-formula for F x G , será necessário inserir "F"&ROW()&"*G"&ROW() na célula B2

Agora, se sua mesa de trabalho estiver na mesma planilha e for a seguinte:

      ¦  F  ¦  G  ¦  H  ¦
-------------------------
   1  ¦  2  ¦  2  ¦ L4L ¦
-------------------------
   2  ¦  4  ¦  2  ¦ FOQ ¦
-------------------------

em seguida, insira o seguinte na célula I1 e arraste-o para baixo:

=EVAL(VLOOKUP(H1,A:B,2,FALSE))

Coluna I agora é uma coluna auxiliar que cria a fórmula necessária para essa linha. Você pode, claro, ocultar essa coluna ao imprimir (se necessário).

Para o resultado dessa fórmula, você colocaria =EVAL(I1) na célula J1 e também poderia arrastar isso para baixo.