如何通过R脚本在Excel中写入MMult函数实现矩阵运算？

方法一：直接用openxlsx写入Excel数组公式（MMULT） #

openxlsx 支持写入Excel公式，包括数组公式，可直接实现Y=M*X的动态计算。核心是用writeFormula()函数并开启数组公式参数，让Excel自动执行矩阵乘法，用户修改X向量时Y会实时更新。

示例代码：

library(openxlsx)

# 生成测试数据
M <- matrix(1:9, nrow = 3)  # 3x3矩阵
X <- c(1, 2, 3)             # 3维列向量

# 创建工作簿和工作表
wb <- createWorkbook()
addWorksheet(wb, sheetName = "MatrixCalc")

# 写入矩阵M到A1:C3区域
writeData(wb, sheet = "MatrixCalc", x = M, startCol = 1, startRow = 1)
# 写入向量X到E1:E3区域（无列名）
writeData(wb, sheet = "MatrixCalc", x = X, startCol = 5, startRow = 1, colNames = FALSE)

# 写入MMULT数组公式到G1:G3区域，计算Y=M*X
# array=TRUE表示这是数组公式，Excel会自动处理矩阵乘法
writeFormula(
  wb,
  sheet = "MatrixCalc",
  x = "=MMULT(A1:C3, E1:E3)",
  startCol = 7,
  startRow = 1,
  array = TRUE
)

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

打开生成的Excel文件后，修改E1:E3的X值，G1:G3的Y会自动重新计算。

方法二：用SUMPRODUCT实现逐单元格独立计算 #

如果担心用户对Excel数组公式不熟悉，可给Y的每个单元格单独写入SUMPRODUCT公式（本质是矩阵行与X向量的点积），每个单元格逻辑独立，更直观：

示例代码：

library(openxlsx)

M <- matrix(1:9, nrow = 3)
X <- c(1, 2, 3)

wb <- createWorkbook()
addWorksheet(wb, "MatrixCalc")

writeData(wb, "MatrixCalc", M, startCol = 1, startRow = 1)
writeData(wb, "MatrixCalc", X, startCol = 5, startRow = 1, colNames = FALSE)

# 循环给每个Y单元格写入SUMPRODUCT公式
for (i in 1:nrow(M)) {
  # 第i行Y的公式：SUMPRODUCT(M的第i行, X向量)
  formula_str <- sprintf("=SUMPRODUCT(A%d:C%d, $E$1:$E$3)", i, i)
  writeFormula(wb, "MatrixCalc", x = formula_str, startCol = 7, startRow = i)
}

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

这种方式下，用户修改X后Y值同样会实时更新。

注意事项 #

• 确保Excel中M的列数等于X的行数，否则MMULT/SUMPRODUCT会报错；
• 写入公式时要注意单元格引用的正确性，固定X向量的引用（比如$E$1:$E$3）可避免用户拖动公式时引用错位；
• 若使用数组公式，Excel会自动将公式应用到选中的整个区域，无需手动按Ctrl+Shift+Enter。

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