在r中使用openxlsx时避免Excel隐式交叉运算符

Question

我正在使用R中的openxlsx包制作Excel工作表，该工作表以后每天将有数据添加到其中一行。

我在使用ISERROR和STDEV的公式中遇到问题。P，但不只是AVERAGE, MIN或MAX。Excel是添加一个隐式的交集运算符(@)到我的公式。我删除了我的ISERROR代码，但我仍然有STDEV的问题。P

创建工作簿wb并将标题放入其中后，我的代码简化为:

formula1 <- "AVERAGE(E2:E32)"
formula2 <- "AVERAGE(IF(ISERROR(E2:E32),"",E2:E32))"
formula3 <- "STDEV.P(IF(ISERROR(E2:E32),"",E2:E32))"
formula4 <- "STDEV.P(E2:E34)"
writeFormula(wb, 1, x=formula1, startCol = 5, startRow = 34)
writeFormula(wb, 1, x=formula2, startCol = 5, startRow = 35)
writeFormula(wb, 1, x=formula3, startCol = 5, startRow = 36)
writeFormula(wb, 1, x=formula4, startCol = 5, startRow = 37)
writeFormula(wb, 1, x=formula5, startCol = 5, startRow = 38)
saveWorkbook(wb, file = "Log.xlsx", overwrite = TRUE)

这是Excel对我的公式所做的:

formula1
What in R is the string:  AVERAGE(E2:E32)
What Excel shows:        =AVERAGE(E2:E32)             ok; works
formula2
What in R is the string:  AVERAGE(IF(ISERROR(E2:E32),"",E2:E32))
What Excel shows:        =AVERAGE(IF(ISERROR(@E2:E32),"",E2:E32))    value error in Excel
formula3
What in R is the string:  STDEV.P(IF(ISERROR(E2:E32),"",E2:E32))
What Excel shows:        =@STDEV.P(IF(ISERROR(@E2:E32),"",E2:E32))   name error in Excel
formula4
What in R is the string:  STDEV.P(E2:E34)
What Excel shows:        =@STDEV.P(E2:E34)               Name error in Excel

添加@的每个公式在Excel中不起作用，无论我是否在E2:E34中有数据。我得到一个?NAME错误的公式使用STDEV。P和其他值错误

有人知道如何解决这个问题吗?有什么我可以添加到我的R代码强制Excel停止添加这些@?有没有办法改变我的公式字符串在R，使Excel不会给出一个错误?

谢谢你提供的任何帮助。

Answer 1

我无法复制Excel错误以在Excel公式中显示@，但您使用公式AVERAGE(IF(ISERROR(E2:E32),"",E2:E32))获得#VALUE的原因是因为ISERROR需要单元格输入而不是范围。所以如果你把这个改成AVERAGE(IF(ISERROR(E2),"",E2:E32))，那么这个应该可以工作了。

还要确保将开头和结尾的双引号"替换为单个'。这是必需的，因为在您的Excel公式中，您正在使用"和R会认为这是字符串"的结束。

如果你正在使用包含.的Excel函数，那么你的公式应该从_xlfn.开始，所以要使用STDEV.P，应该是_xlfn.STDEV.P(E2:E34)。

library(openxlsx)
wb <- createWorkbook()
addWorksheet(wb, "1")
# This works
formula1 <- "AVERAGE(E2:E32)"
# This won't work (note it begins with a single ')
formula2 <- 'AVERAGE(IF(ISERROR(E2:E32),"",E2:E32))'
# This should work
formula3 <- 'AVERAGE(IF(ISERROR(E2),"",E2:E32))'
# This won't work
formula4 <- 'STDEV.P(IF(ISERROR(E2:E32),"",E2:E32))'
formula5 <- "STDEV.P(E2:E34)"
# This should work. For a Excel functions with . start with _xlfn
formula6 <- "_xlfn.STDEV.P(E2:E34)"

writeData(wb, 1, 1:30, startRow = 2, startCol = 5)
writeFormula(wb, 1, x=formula1, startCol = 5, startRow = 34)
writeFormula(wb, 1, x=formula2, startCol = 5, startRow = 35)
writeFormula(wb, 1, x=formula3, startCol = 5, startRow = 36)
writeFormula(wb, 1, x=formula4, startCol = 5, startRow = 37)
writeFormula(wb, 1, x=formula5, startCol = 5, startRow = 38)
writeFormula(wb, 1, x=formula6, startCol = 5, startRow = 39)
saveWorkbook(wb, file = "Log.xlsx", overwrite = TRUE)