Validere en formel
Nogen gange har man i JetReports*) brug for kun at validere en formel én gang
ved kørslen af en rapport. Et godt eksempel på dette er funktionen ’Now()’. Som
regel foretrækkes det, at man kun kører ´Now()´ funktionen en gang ved opdatering
af rapporten. Hvis funktion ’Now()’ er sat til at køre normalt, vil det bevirke
at Jet formlen konstant genberegnes, hvilket resulterer i stor driftnedsættelse.
Læs meget mere om dette nedenfor.
*) JetReports er et rapporteringsværktøj til Navision, som bruger Excel som frontend.
Se mere om Jet Reports her: www.jetreports.com
Solution
There are some cases where you want to evaluate a formula only once each time
you run your report. A good example of this is the Now() function. You generally
want the Now() function to run once when you update the report, and otherwise
remain inert. If Now() is allowed to behave normally, it will cause the sheet
it is on, including Jet formulas, to recalculate constantly, resulting in a major
performance problem.
You can use the NP function to control when a formula updates by putting "Eval"
in the What parameter. The formula you want to evaluate, including the "=" sign
goes in the Arg1 parameter and must be in quotes. If you are evaluating a formula
that already has quotes inside it, then Excel requires you to use two sets of
quotes instead of one. Some examples are listed below.
This following function will calculate Now() once each time the report updates.
=NP("Eval","=Now()")
The function below calculates a date filter for the current month without using
NP("Eval").
=NP("Datefilter",Date(Year(Now()),Month(Now()),1),Date(Year(Now()),Month(Now())+1,1)-1)
However, the formula above uses the Now() function liberally, so you should put
it inside an NP("Eval") formula and change "Datefilter" to ""Datefilter"" with
two sets of quotes as in the following formula.
=NP("Eval","=NP(""Datefilter"",Date(Year(Now()),Month(Now()),1),Date(Year(Now()),Month(Now())+1,1)-1)")
Note that you should place any volatile Excel function (i.e. NOW() and TODAY())
directly inside the NL("Eval") to avoid constant recalculation, even if the volatile
function has no other dependent cells. A cell reference is not sufficient. For
instance, =NP("Eval","=NOW()") will only be calculated once with each run of the
report, whereas =NP("Eval","=B4"), where B4 contains a volatile function, will
still be constantly recalculated.
Evaluating Off-sheet References
The NP ("Eval") function can evaluate any Excel function including cell references.
The formula below displays the value in cell C5.
=NP("Eval","=C5")
This application of the Eval function can improve your report execution time
if you have an Options sheet. When your report runs, any cell which has a dependency
on a cell that has changed will be recalculated. Unfortunately, when cells have
dependencies that extend outside their sheet, Excel does not know when that external
reference may have changed so it will recalculate the formula every time the current
worksheet changes. Jet Reports changes worksheets many times over the course of
evaluating a report causing Excel to recalculate any formula that has off-sheet
dependencies. You can eliminate this unnecessary recalculation by using the NP("Eval")
function to bring the values from off-sheet references onto the local worksheet,
then change your cell references to only use the local cell where you have the
NP("Eval") function.
When you need to reference cells on other worksheets inside your workbook, you
can use the same formula only with the worksheet name followed by an "!" as part
of your cell reference. The following Eval function will display the value from
C5 of the worksheet named "Options".
=NP("Eval","=Options!C5")
If the cell you are trying to reference is on a worksheet that has a space in
its name you need to wrap the worksheet name in single quotes as in the following
formula.
=NP("Eval","=Options Page!C5")
Named ranges with NP("Eval")
NP("Eval") can be used with named ranges as well as cell references (see the
Useful Excel Features section for information on Named Ranges). So assuming you
named cell C5 on your Options worksheet "DateFilter", you can use the cells name
as if it were a normal cell reference without the worksheet or workbook name as
in the following formula.
NP("Eval") can be used with named ranges as well as cell references (see the
Useful Excel Features section for information on Named Ranges). So assuming you
named cell C5 on your Options worksheet "DateFilter", you can use the cells name
as if it were a normal cell reference without the worksheet or workbook name as
in the following formula.
=NP("Eval","=DateFilter")
If you had a Profit and Loss report, you would probably have a changeable Start
Date and End Date on the Options page with cell references on a Detail page and
a Summary page. Instead of having many cell references to the Options page, you
could name the cells StartDate and EndDate respectively, and use the following
two formulas to bring the data in the two cells onto the Detail and Summary pages.
=NP("Eval","=StartDate")
=NP("Eval","=EndDate")
If you put these two formulas in cells B2 and B3 respectively, you can then use
local cell references to B2 and B3 for your filters, eliminating a large amount
of Excel calculation overhead when reporting.
Using NL("Sheets") with NP("Eval")
The NP("Eval") function will work to reference an Options page from a worksheet
that is replicated with NL ("Sheets"). However, it may behave unexpectedly when
it is used to reference a cell on the same sheet as the NL("Sheets") formula.
Therefore using an NP("Eval") formula on a sheet with an NL("Sheets") to reference
cells that are on that same worksheet is not recommended.
Til toppen af siden
Du skal være logget ind før du kan se eller skrive kommentarer til de forskellige indlæg. Klik her for at logge ind, eller oprette en bruger.
