批量读取含公式的Excel指定范围数据时大量返回NA的问题

需要从289个Excel文件中提取「Main tab」工作表的I20:I21单元格范围数据并合并为DataFrame，这些单元格包含引用「input」工作表的公式（示例公式：=IF($C$21<1,$C$21*$I3,$I3)*I13+IF($C$21<1,$C$21*$I$3,$I$3)）。使用readxl::read_excel编写的批量处理代码仅能正确读取约10个文件的数据，其余均返回NA值。使用的代码如下：

files <- list.files('.', pattern = '*.xlsx', recursive = TRUE)
estimation_list <- lapply(files, 
              function(x) {readxl::read_excel(x, sheet = "Main tab", range = "I20:I21", col_names = FALSE)}) %>% 
  bind_cols %>% 
  t %>% 
  data.frame

colnames(estimation_list) <- c('lower', 'upper bound')
estimation_list$project <- files
write.xlsx(estimation_list,"updated_estimates.xlsx")

1. 强制触发Excel公式计算 #

readxl读取的是单元格的已计算结果，如果文件从未打开过、或Excel计算模式设为手动，公式不会自动计算，readxl就会返回NA。可以用openxlsx批量打开并保存文件，触发公式计算：

library(openxlsx)
lapply(files, function(x) {
  wb <- loadWorkbook(x)
  saveWorkbook(wb, x, overwrite = TRUE)
})

处理完所有文件后，再运行原读取代码。

2. 调整readxl读取参数 #

添加guess_max = Inf避免类型猜测错误，同时确保读取的是单元格值而非公式：

estimation_list <- lapply(files, 
              function(x) {
                readxl::read_excel(x, 
                                   sheet = "Main tab", 
                                   range = "I20:I21", 
                                   col_names = FALSE,
                                   guess_max = Inf)
              }) %>% 
  bind_cols %>% 
  t %>% 
  data.frame

3. 改用openxlsx读取数据 #

如果readxl兼容性仍有问题，换用openxlsx直接读取公式计算值，它对公式的支持更稳定：

library(openxlsx)
estimation_list <- lapply(files, function(x) {
  wb <- loadWorkbook(x)
  # 读取I20:I21（第9列，20-21行）的值
  vals <- readWorkbook(wb, sheet = "Main tab", rows = 20:21, cols = 9, colNames = FALSE)
  as.data.frame(t(vals)) # 保持和原代码一致的结构
}) %>% 
  bind_rows()

colnames(estimation_list) <- c('lower', 'upper bound')
estimation_list$project <- files
write.xlsx(estimation_list,"updated_estimates.xlsx")

4. 定位异常文件 #

先筛选出返回NA的文件，手动排查问题（比如工作表不存在、公式引用失效等）：

# 逐个检查文件是否返回NA
check_results <- lapply(files, function(x) {
  df <- readxl::read_excel(x, sheet = "Main tab", range = "I20:I21", col_names = FALSE)
  any(is.na(df))
})
# 获取异常文件列表
na_files <- files[unlist(check_results)]
print(na_files)

内容的提问来源于stack exchange，提问作者Léa