Zastępowanie zagnieżdżonego IF programu Excel przez podgląd tekstu formuły

Pracuję nad arkuszem kalkulacyjnym do planowania zapotrzebowań materiałowych (dla zabawy). Jednym z problemów są reguły dotyczące wielkości partii. Muszę używać funkcji OFFSET i INDIRECT w wielu miejscach, a formuły są niezwykle trudne do odczytania (i są długie!).

Czy jest jakiś sposób na umieszczenie tekstów formuł dla różnych reguł w tablicy i wybranie ich na podstawie WYSZUKAJ.PIONOWO? Mam nadzieję znaleźć funkcję, która wykonuje następujące czynności:

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

gdzie reguła może być jedną z „L4L”, „FOQ” itp., a tabela wyglądałaby tak

L4L     L4L-formula
FOQ     FOQ-formula
...

Dla mnie to wygląda na znacznie czystsze (i krótsze) niż

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

Musisz utworzyć funkcję za pomocą VBA.

Po otwarciu arkusza Excel uruchom Edytor Visual Basic, wykonując następujące czynności:

• na komputerze z systemem Windows
  naciśnij Alt+ F11.
• na komputerze Mac
  naciśnij FN+ ALT+F11

kliknij Insertpotem Module.

Nowe okno modułu pojawi się po prawej stronie edytora Visual Basic.

Skopiuj następujące elementy do nowego okna modułu:

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

Możesz teraz zamknąć Edytor Visual Basic, ponieważ utworzyłeś teraz funkcję EVAL(cell reference)formuły.

Teraz, aby użyć tej nowej funkcji podczas używania VLOOKUP()formuły.

Jeśli formuła ma zostać zbudowana zgodnie z tym, w którym wierszu znajdują się dane, na przykład

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

i komórki C1i C2jestL4L-formula

Następnie należy pamiętać o zmienności formuły. Na przykład nie możesz po prostu położyć A1 + B1 L4L-formula. Musisz wpisać bez znaku równości (=) "B"&ROW()&"+C"&ROW() dla L4L formula.

Następnie musisz utworzyć kolumnę pomocniczą dla wynikowej formuły.

Jeśli lista formuł jest następująca:

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

Jeśli L4L-formulama być F + G, to jak wskazano przed wejściem "F"&ROW()&"+G"&ROW()do komórkiB1

Jeśli FOQ-formulama być F x G, musisz wprowadzić dane "F"&ROW()&"*G"&ROW()do komórkiB2

Teraz, jeśli twój stół roboczy znajduje się na tym samym arkuszu i wygląda następująco:

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

następnie wstaw do komórki I1i przeciągnij w dół:

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

Kolumna Ijest teraz kolumną pomocniczą, która tworzy wymaganą formułę dla tego wiersza. Możesz oczywiście ukryć tę kolumnę podczas drukowania (w razie potrzeby).

Aby uzyskać wynik tej formuły, umieść go =EVAL(I1)w komórce J1i możesz również przeciągnąć w dół.