使用openxlsx包writeFormula创建Excel数组公式无法填充问题

问题原因 #

你的代码仅指定了单个单元格的公式，array=TRUE只是将该单元格标记为数组公式，但不会自动扩展到多行；同时未明确指定公式覆盖的行范围，导致公式仅停留在C2单元格。

解决方案 #

方法1：写入覆盖整个目标区域的数组公式 #

如果需要单个数组公式覆盖C2:C6整个区域（Excel中数组公式会自动填充对应结果，用户编辑时需选中整个区域），修改代码如下：

library(tidyverse)
library(openxlsx)

x <- 1:5
data <- tibble(x, y = x * 2)

wb <- createWorkbook()
addWorksheet(wb, sheetName = "test")
# 先写入数据，避免公式被覆盖（养成良好习惯）
writeData(wb, "test", data)
# 写入覆盖C2:C6的数组公式，用rows参数明确行范围
writeFormula(wb, "test", x = "=A2:A6/B2:B6", 
             startCol = "C", startRow = 2, rows = 2:6,
             array = TRUE)
saveWorkbook(wb, file = "test.xlsx", overwrite = TRUE)

方法2：批量写入每行独立的数组公式 #

如果需要每个单元格都是独立的可编辑数组公式，可以生成对应行数的公式向量后批量写入：

library(tidyverse)
library(openxlsx)

x <- 1:5
data <- tibble(x, y = x * 2)

wb <- createWorkbook()
addWorksheet(wb, sheetName = "test")
writeData(wb, "test", data)
# 生成每行对应的数组公式
formulas <- str_glue("=A{2:6}/B{2:6}")
# 批量写入公式到C2:C6
writeFormula(wb, "test", x = formulas, 
             startCol = "C", startRow = 2,
             array = TRUE)
saveWorkbook(wb, file = "test.xlsx", overwrite = TRUE)

关键说明 #

• 优先选择方法1，单个数组公式更符合Excel数组公式的设计逻辑，编辑时只需维护一个公式；
• 确保先写入数据再写入公式，避免数据覆盖公式单元格；
• rows参数用于明确数组公式覆盖的行范围，仅指定startRow只会作用于单个单元格。

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