在R中创建一个动态函数

我也许希望使用一个看起来像这样的excel / csv：

然后，我需要做的是，在公式中进行更改在excel文件中输入合适的单元格，并通过函数传递csv / excel来获得输出。

我的数据框可重复使用的代码：

函数的可重复代码：目前，公式都是硬编码的。该函数将数据帧作为输入/参数，其中包含两列1.）代码列DN1，DN2等。2）与代码相关的值列22（DN1的值），48（DN2的值）等等。

更新：如何修改代码以将硬编码引用替换为上一季度值例如 Value [11]< - as.numeric（Value [10]）+ DK3

假设值将以后缀提供，但数据正在按季度接收，意味着相同的单元代码将重复4次（每个季度一次），即

     
解决方案
目前还不完全清楚你在这里要做什么，但这是我的理解。你有一个表格，每个单元格的内容根据特定的列进行计算。您希望能够更新公式并重新计算表格中的值，而不用硬编码所有内容。
   
我的解决方案是编写一个函数，被更新并且每个单元格都是你想要使用的公式。公式必须写成字符。然后您可以使用 eval（parse（））来完成这项工作。 试试：
 ＃创建数据和公式数据框x<  -  10 y<  -  5  形式< -c（x + y，x-y，x / y，x * y）  form_df<  -  as.data.frame（matrix（forms，nrow = 2，ncol = 2），stringsAsFactors = F）＃V1 V2 ＃1 x + yx / y ＃2 x  -  yx * y   df1 < -  data.frame（col1 = c（15,6），col2 = c（50,50））＃col1 col2 ＃1 15 50 ＃2 6 50  ＃根据这个 dyndf < - 函数（x，y，df_old， form_df）{ df_new<  -  sapply（form_df，function（z1）sapply（z1，function（z2）eval（parse（text = z2）））） df_old [df_new！= df_old]< ;  -  df_new [df_new！= df_old]  df_old }   dyndf（x，y，df1，form_df） ＃col1 col2 ＃1 15 2 ＃2 5 50   
一些解释...函数并不复杂，但基本上我们取公式数据框架，我们评估每个单元格中的每个函数并创建一个新的数据框架。这个新的数据框看起来很可怕，因为它缺少列名和其他类型的信息。因此，我们只需检查哪些值已更新，然后将其覆盖。
   
通过重写函数，您可以使其适合您自己的问题。 b  $ b 
编辑：一个向量的简单例子，其中任何元素都可以依赖于前面的元素...   
 <$ cc（1 + 1，vec [1] + 2，vec [1] * vec [2]，vec <-1:5 形式< （vec）vec [i] < -  eval（parse（text = form（））[vec [1]vec [5]）  [i]））  vec ＃[1] 2 4 8 4 5   
 
I have a dataframe with 7 columns. The 3 columns Product, Original Price and New Price are self explanatory. Then, Q1-Q4 are average projected prices over 4 quarters.  

What you will see in these columns are codes e.g. DN1, DG1, DN2 etc. These codes have values associated with them but the values are irrelevant for this exercise i.e. they could be anything.

For Q1 - Q4, each cell value is calculated using a formula e.g. projected price for Product A in Q1 = DN1+1. So if DN1= 3, then projected price is 3+1 = 4. Similar logic is followed and each value in Q1-Q4 for all products is calculated based on formulas.

How do I edit my function to make it more dynamic/efficient in the sense that should the formulas change, I am able to easily make that change e.g. if formula for projected price for Product D in Q2 changes, I should be able to make that change easily without having to go and amend the code.

I was hoping to perhaps use an excel/csv that will look something like this:
Cell         Formula    Period
Value[1]     =DN1+1      Q1
Value[2]     =DK1        Q2
Value[3]     =DK1        Q3
Value[4]     =DK1        Q4
Value[5]     =DN2+3 (this value e.g. changed from DN2)
All i would then have to do is, make the change in the formula in the appropriate cell in the excel file and pass the csv/excel through the function to get the output.

Reproducible code for my dataframe:
    Q1<-c("=DN1+1","=DN2+3","=DN3*4","=DN4-1","=DN1 + DN2 + DN3 + DN4") 
    Q2<-c("=DK1", "=DK2","=DN3+DK3","=DN4+DL3- DM1 * ( DM2 + ((-DJ7-DK3) / S20+ S22) + S223 + Y446 / ZQ282 - W223)","=DG1 + DG2 + DG3 + DG4")
    Q3<-c("=DK1","=DK2","=DG3 (-1 qtr) +DK3","=DG4(-1 qtr)+DL3- DM1 * ( DM2 + ((-DJ7-DK3) / S20+ S22) + S223 + Y446 / ZQ282 - W223)","=DG1 + DG2 + DG3 + DG4")
    Q4<-c("=DK1","=DK2","DG3 (-1 qtr) +DK3","=DG4(-1 qtr)+DL3- DM1 * ( DM2 + ((-DJ7-DK3) / S20+ S22) + S223 + Y446 / ZQ282 - W223)","DG1 + DG2 + DG3 + DG4")
    D1<-data.frame(Q1,Q2,Q3,Q4)
    D1$Product<-c("A","B","C","D","Total")
    D1$Original_Price<-c("DN1","DN2","DN3","DN4","DN7")
    D1$New_Price<-c("DG1","DG2","DG3","DG4","DG7")
    D1<-D1[,c(5,6,7,1,2,3,4)]
Reproducible code for function: Currently, the formulas are all hardcoded. This function takes a dataframe as an input/argument which contains two columns 1.) Column for codes e.g. DN1, DN2 etc. 2.) Column for values associated with codes e.g. 22 (value for DN1), 48 (value for DN2)..etc.
function_1<-function(dta)
{
Product <- c("A","A","A","A","B","B","B","B","C","C","C","C","D","D","D","D","Total","Total","Total","Total")
Original_Price <- c("DN1","DN1","DN1","DN1","DN2","DN2","DN2","DN2","DN3","DN3","DN3","DN3","DN4","DN4","DN4","DN4","DN7","DN7","DN7","DN7")
New_Price <- c("DG1","DG1","DG1","DG1","DG2","DG2","DG2","DG2","DG3","DG3","DG3","DG3","DG4","DG4","DG4","DG4","DG7","DG7","DG7","DG7")
Period <- c("Q1","Q2","Q3","Q4","Q1","Q2","Q3","Q4","Q1","Q2","Q3","Q4","Q1","Q2","Q3","Q4","Q1","Q2","Q3","Q4")
Value <- c("n/a","n/a","n/a","n/a","n/a","n/a","n/a","n/a","n/a","n/a","n/a","n/a","n/a","n/a","n/a","n/a","n/a","n/a","n/a","n/a")
for (i in c (1:nrow(dta))) {
    assign(as.character(dta[i,1]), dta[i,2])
    print(dta[i,1])
    print ("_")
    print(dta[i,2])
}

Value[1] <- DN1+1
Value[2] <- DK1
Value[3] <- DK1
Value[4] <- DK1
Value[5] <- DN2   
Value[6] <- DK2
Value[7] <- DK2
Value[8] <- DK2
Value[9] <- DN3
Value[10] <- DN3 + DK3
Value[11] <- as.numeric(Value[10]) + DK3
Value[12] <- as.numeric(Value[11]) + DK3
Value[13] <- DN4
Value[14] <- DN4+DL3- DM1 * ( DM2 + ((-DJ7-DK3) / S20+ S22) + S223 + Y446 / ZQ282 - W223)
Value[15] <- as.numeric(Value[14]) + DL3 - DM1 * ( DM2 + ((0 -DJ7-DK3) / S20 + S22) + S223 + Y446 / ZQ282 - W223)
Value[16] <- as.numeric(Value[15]) + DL3 - DM1 * ( DM2 + ((0 -DJ7-DK3) / S20 + S22) + S223 + Y446 / ZQ282 - W223)
Value[17] <- DN1 + DN2 + DN3 + DN4 
Value[18] <- as.numeric(Value[2]) + as.numeric(Value[6]) + as.numeric(Value[10]) + as.numeric(Value[14]) 
Value[19] <- as.numeric(Value[3]) + as.numeric(Value[7]) + as.numeric(Value[11]) + as.numeric(Value[15]) 
Value[20] <- as.numeric(Value[4]) + as.numeric(Value[8]) + as.numeric(Value[12]) + as.numeric(Value[16])
output <- data.frame(Product,Original_Price,New_Price,Period,Value)
 }
UPDATE:
How do I amend the code to replace the hard-coded reference to previous quarter values e.g. Value[11] <- as.numeric(Value[10]) + DK3

I coded the function on the assumption the values will be provided with a suffix, but the data is being received by quarter meaning the same cell code will repeat 4 times (once for each quarter) i.e.
Code Value Period
DN1  200    Q1
DN1  300    Q2
DN1  400    Q3
DN1  500    Q4
So my function will fail since it won't pick up correct values.
 解决方案 
It's not entirely clear what you are trying to do here, but this is how I understood it.. You have a table where each cell's content is calculated according to a specific column. You want to be able to update the formulas and recalculate the values within your table, without hardcoding everything.

My solution would be to write a function where you supply a table to be updated AND a table where each cell is the formula that you want to use. The formulas have to be written down as characters. You then can use eval(parse()) to make this work.

Let's try:
# Create data and a formula data frame
x <- 10
y <- 5

forms <- c("x + y", 
           "x - y", 
           "x / y", 
           "x * y")

form_df <- as.data.frame(matrix(forms, nrow = 2, ncol = 2), stringsAsFactors = F)
#      V1    V2
# 1 x + y x / y
# 2 x - y x * y

df1 <- data.frame(col1 = c(15,6), col2 = c(50,50))
#   col1 col2
# 1   15   50
# 2    6   50

# Create a function that creates a data frame from this
dyndf <- function(x, y, df_old, form_df) {
             df_new <- sapply(form_df,function(z1) sapply(z1, function(z2) eval(parse(text=z2))))
             df_old[df_new != df_old] <- df_new[df_new != df_old]
             df_old
}

dyndf(x,y,df1,form_df)

#   col1 col2
# 1   15    2
# 2    5   50
Some explanations... The function is not that complicated, but basically we take the formula data frame, we evaluate every function in each cell and create a new data frame. This new data frame looks horrible because it lacks column names and other kinds of information. So, we simply check which values are updated, and then overwrite those.

By rewriting the function, you can probably make it fit your own problem.

Edit: A quick example of a vector where any element may rely on the previous elements...
vec <- 1:5
forms <- c("1 + 1", "vec[1] + 2", "vec[1] * vec[2]", "vec[3] / vec[1]", "vec[5]")

for (i in 1:length(vec)) vec[i] <- eval(parse(text=forms[i]))

vec
# [1] 2 4 8 4 5