openxlsx连续使用Excel公式，如何为每列动态创建公式 [英] openxlsx Excel formulae in a row, how to create formula dynamically for each column

问题描述

我的目标

我有一个数据集，其中多个列存储在excel文件中.对于每一列，我需要在数据集的一侧插入统计公式.公式应动态创建.

I have a data set with several columns stored in an excel file. For each column I need to insert statistical formulae on the side of the data set. The formulae should be created dynamically.

为方便起见，让我们创建一个3列×10行的示例，以便任何人都可以跟随.

For the sake of the example, lets create an example 3 columns by 10 rows, so that anyone can follow.

wb <- createWorkbook(title = "simulation")
addWorksheet(wb, "stats") # create a sheet
writeData(wb, "stats", data.frame(A=c(1:10)*pi, B=1/c(6:15), C=sqrt(11:20))) # store the example data frame
saveWorkbook(wb, "formula_example.xlsx", overwrite = TRUE) # save the file

数据集现在看起来像这样:数据集

The data set now looks like this: data set

我想在我的excel数据集的左侧插入一组公式，例如E列.例如，我需要每列的平均值和标准偏差.

I want to insert a set of formulae on the left of my excel data set, say column E. For instance, I need average and standard deviation for each column.

我尝试了方法1:

根据R文档，我可以使用writeFormula方法.但这会将我的公式放在一列中，而我需要连续使用它们！ https://www.rdocumentation.org/packages/openxlsx/版本/4.1.5/topics/writeFormula

According to the R documentation, I can use the writeFormula method. But this will put my formulae in a column, whereas I need them in a row!! https://www.rdocumentation.org/packages/openxlsx/versions/4.1.5/topics/writeFormula

wb <- loadWorkbook(xlsxFile = "formula_example.xlsx") # load the file
v1 <- c("AVERAGE(A2:A11)", "AVERAGE(B2:B11)", "AVERAGE(C2:C11)") # the vector of formulae
writeFormula(wb, sheet = "stats", x = v1, startCol = "E", startRow = 2) # column E and row 2
saveWorkbook(wb, "formula_example.xlsx", overwrite = TRUE) # save the file

我尝试了方法2

文档中有writeData方法，您必须将单元格重新分类为公式.如果我创建一个内部包含动态公式的数据框并将其存储到excel中，也许我可以使其正常工作.

In the documentation there is the method with writeData where you have to re-class the cells as formula. If I create a data frame with dynamic formulae inside and store it to excel, perhaps I can get it working.

df <- data.frame() # initialize as empty data frame
df <- rbind(
  df, # append the formula rows below and create the column names with the method int2col
  sapply(1:3, function(i){paste0("AVERAGE(",int2col(i),"2:",int2col(i),"11)")})
)

df <- rbind(
  df, # do the same for standard deviation
  sapply(1:5, function(i){paste0("STDEV.S(",int2col(i),"2:",int2col(i),"11)")})
)
colnames(df) <- c("A","B","C") # set sensible names

现在数据帧df如下所示:

Now the data frame df look like this:

> df
                A               B               C
1 AVERAGE(A2:A11) AVERAGE(B2:B11) AVERAGE(C2:C11)
2 STDEV.S(A2:A11) STDEV.S(B2:B11) STDEV.S(C2:C11)

现在，我将其存储到excel:

Now, I store it to excel:

class(df[1,]) <- c(class(df[1,]), "formula") # reclass as formula (not sure that this one is correct!!)
class(df[2,]) <- c(class(df[2,]), "formula") # in fact, it doesn't seem to work

writeData(wb, sheet = "stats", x = df, startCol = "E", startRow = 1) # set where to put the stats
saveWorkbook(wb, "formula_example.xlsx", overwrite = TRUE) # save the file

它确实以行的形式存储在excel中，但是以显式文本形式而不是公式形式存储！结果看起来像这样:生成数据集

It does store into excel as rows, but as explicit text and not as formula!! The result looks like this: resulting data set

推荐答案

您可能会使用 lapply 将要写入的公式传递到Excel工作表.请注意，由于某种原因，尽管Excel公式中有.，但似乎无法正确识别该公式，因此在此示例中将其替换为STDEV.

You could potentially use lapply to pass the formulas that you want to write to an Excel sheet. Note for some reason though if there is a . in an Excel formula, it doesn't seem to recognise the formula properly, so have replaced them with STDEV in this example.

wb <- createWorkbook(title = "simulation")
addWorksheet(wb, "stats") 

# Data
writeData(wb, "stats", data.frame(A=c(1:10)*pi, B=1/c(6:15), C=sqrt(11:20)))

# Formula Vector
v1 <- c("AVERAGE(A2:A11)", "AVERAGE(B2:B11)", "AVERAGE(C2:C11)")
v2 <- c("STDEV(A2:A11)", "STDEV(B2:B11)", "STDEV(C2:C11)") 

# Columns where you want to store formulas
column1 <- c("E", "F", "G")

# write formula AVERAGE & STDEV 
lapply(1:length(column1), FUN = function(x) writeFormula(wb, "stats", x = v1[x], startCol = column1[x], startRow = 2))
lapply(1:length(column1), FUN = function(x) writeFormula(wb, "stats", x = v2[x], startCol = column1[x], startRow = 3))

saveWorkbook(wb, "formula_example83.xlsx", overwrite = TRUE)

这篇关于openxlsx连续使用Excel公式，如何为每列动态创建公式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！