Замена вложенного IF в Excel с помощью vlookup текста формулы

Question

Я работаю над таблицей для планирования потребности в материалах (просто для удовольствия). Одна проблема связана с правилами определения размера лота. Я должен использовать функции OFFSET и INDIRECT во многих местах, и формулы чрезвычайно трудно читать (и они длинные!).

Есть ли способ поместить текст формулы для различных правил в массив и выбрать их на основе VLOOKUP? Я надеюсь найти функцию, которая выполняет следующее:

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

где правило может быть одним из "L4L", "FOQ" и т. д., и таблица будет выглядеть так

L4L     L4L-formula
FOQ     FOQ-formula
...

Это выглядит намного чище (и короче) для меня, чем

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

Answer 1

Вам нужно будет создать функцию с использованием VBA.

Открыв свой лист Excel, запустите редактор Visual Basic, выполнив следующие действия:

• на ПК с Windows
  нажмите Alt+F11.
• на Mac
  нажмите FN+ALT+F11

затем нажмите Insert затем Module .

Новое окно модуля появится в правой части редактора Visual Basic.

Скопируйте следующее в новое окно модуля:

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

Теперь вы можете закрыть редактор Visual Basic, как только что создали функцию EVAL(cell reference) .

Теперь, чтобы использовать эту новую функцию, используя VLOOKUP() для формулы.

Если формула должна быть построена в соответствии с тем, в какой строке находятся данные, например,

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

и клетки C1 и C2 представляют собой L4L-formula

Тогда вам необходимо учитывать изменчивость формулы. Вы не можете просто положить A1+B1 для L4L-formula например. Вы должны ввести без знака равенства (=) "B"&ROW()&"+C"&ROW() для L4L formula .

Затем вам нужно будет создать вспомогательный столбец для полученной формулы.

Если ваш список формул выглядит следующим образом:

      ¦  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
-----------------------------

Если L4L-formula должна быть F + G , то, как указано выше, вам нужно ввести "F"&ROW()&"+G"&ROW() в ячейку B1

Если FOQ-formula должна быть F x G то вам нужно ввести "F"&ROW()&"*G"&ROW() в ячейку B2

Теперь, если ваш рабочий стол находится на том же листе и выглядит следующим образом:

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

затем поместите следующее в ячейку I1 и перетащите его вниз:

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

Столбец I теперь является вспомогательным столбцом, который строит необходимую формулу для этой строки. Вы, конечно, можете скрыть этот столбец при печати (если требуется).

Для результата этой формулы вы должны поместить =EVAL(I1) в ячейку J1 и также можете перетащить ее вниз.