使用openxlsx生成Excel公式出现@符号致计算错误，求解决方法

问题描述 #

我想用openxlsx给每行添加Excel公式：=IF(COUNTIF(E3:G3,"*none*")=3,"N/A",AVERAGE(IF(ISNUMBER(--E3:G3),--E3:G3,"")))，这个公式在Excel中能正常计算，但通过openxlsx生成后，公式自动变成了：
=IF(COUNTIF(E3:G3, "*none*") = 3, "N/A", AVERAGE(IF(ISNUMBER(--@E3:G3), --@E3:G3, "")))
新增的@符号破坏了原本的数组运算逻辑，导致无法得到正确结果，需要修复并去除@符号。

原R代码如下：

library(openxlsx)

# Create a new workbook and add a worksheet
wb <- createWorkbook()
addWorksheet(wb, "Sheet1")

# Example data: write some sample data to columns E, F, and G starting from row 1
example_data <- data.frame(
  E = c(1, NA, 3, "none", 5, 6, 7, 8, 9, 10), 
  F = c(11, 12, "none", 14, 15, 16, 17, 18, 19, 20),
  G = c(21, 22, 23, 24, "none", 26, 27, 28, 29, 30)
)
writeData(wb, "Sheet1", example_data, startCol = 5, startRow = 1)

# Determine the number of rows in your data
num_rows <- nrow(example_data)

# Function to create the formula with conditions
create_custom_formula <- function(row) {
  sprintf(
    "=IF(COUNTIF(E%d:G%d, \"*none*\") = 3, \"N/A\", AVERAGE(IF(ISNUMBER(--E%d:G%d), --E%d:G%d, \"\")))",
    row, row, row, row, row, row
  )
}

# Apply the function to each row and write the formulas to column H
lapply(1:num_rows, function(row) {
  writeFormula(wb, "Sheet1", x = create_custom_formula(row + 1), startCol = 8, startRow = row + 1)
})

# Set the column width for better visibility
setColWidths(wb, "Sheet1", cols = 8, widths = 15)

# Set the header for the new column (H1) to "Average"
writeData(wb, "Sheet1", "Average", startCol = 8, startRow = 1)

# Save the workbook
saveWorkbook(wb, "example_with_custom_averages.xlsx", overwrite = TRUE)

问题原因 #

openxlsx默认会将包含数组运算的公式识别为动态数组公式，自动添加@符号（Excel的隐式交集运算符），这会破坏原公式中依赖数组运算的逻辑，导致计算错误。

修复方法 #

在调用writeFormula函数时，添加array = TRUE参数，明确告知openxlsx这是一个数组公式，避免自动添加@符号。

修改后的完整代码 #

library(openxlsx)

# 创建工作簿并添加工作表
wb <- createWorkbook()
addWorksheet(wb, "Sheet1")

# 示例数据：写入E、F、G列（从第1行开始）
example_data <- data.frame(
  E = c(1, NA, 3, "none", 5, 6, 7, 8, 9, 10), 
  F = c(11, 12, "none", 14, 15, 16, 17, 18, 19, 20),
  G = c(21, 22, 23, 24, "none", 26, 27, 28, 29, 30)
)
writeData(wb, "Sheet1", example_data, startCol = 5, startRow = 1)

# 获取数据行数
num_rows <- nrow(example_data)

# 生成自定义公式的函数
create_custom_formula <- function(row) {
  sprintf(
    "=IF(COUNTIF(E%d:G%d, \"*none*\") = 3, \"N/A\", AVERAGE(IF(ISNUMBER(--E%d:G%d), --E%d:G%d, \"\")))",
    row, row, row, row, row, row
  )
}

# 为每行写入公式，添加array = TRUE参数指定为数组公式
lapply(1:num_rows, function(row) {
  writeFormula(wb, "Sheet1", x = create_custom_formula(row + 1), 
               startCol = 8, startRow = row + 1, 
               array = TRUE)  # 关键修改
})

# 设置列宽以优化显示
setColWidths(wb, "Sheet1", cols = 8, widths = 15)

# 写入H列标题
writeData(wb, "Sheet1", "Average", startCol = 8, startRow = 1)

# 保存工作簿
saveWorkbook(wb, "example_with_custom_averages.xlsx", overwrite = TRUE)

说明 #

• 添加array = TRUE后，openxlsx会将公式以数组公式的形式写入Excel，不会自动添加@符号，完整保留原公式的数组运算逻辑。
• 通过该参数写入的数组公式，Excel会自动识别并执行计算，无需手动按Ctrl+Shift+Enter触发。

内容的提问来源于stack exchange，提问作者Mel