如何用R实现Excel表格间的二维数据匹配与索引填充

问题分析 #

输入矩阵是日期-国家-值的宽格式，输出矩阵是国家-Excel日期序列号-值的宽格式，需要基于「国家+日期」的二维匹配，把输入的值填充到输出对应位置，无匹配则保留NA。

完整解决方案代码 #

# 清理工作区
rm(list=ls())

# 加载依赖包
library(tidyverse)
library(openxlsx)

# 文件路径设置
username <- Sys.getenv("USER")
input_file <- paste0("/Users/", username, "/Downloads/input_file.xlsx")
output_file <- paste0("/Users/", username, "/Desktop/output_file.xlsx")

# 读取数据
input_matrix <- read_xlsx(input_file, sheet = "KLICKHERE")
output_matrix <- read_xlsx(output_file, sheet = "ENTERHERE")

# 1. 转换输入矩阵为匹配用的长格式，同时把日期转为Excel序列号
input_long <- input_matrix |>
  mutate(quarter = as.Date(quarter),
         quarter_serial = as.numeric(convertToDate(quarter))) |>
  pivot_longer(-c(quarter, quarter_serial), names_to = "country", values_to = "value") |>
  select(country, quarter_serial, value)

# 2. 转换输出矩阵为长格式，保留原始结构信息
output_long <- output_matrix |>
  rename(country = ...1) |>
  pivot_longer(-country, names_to = "quarter_serial", values_to = "placeholder") |>
  mutate(quarter_serial = as.numeric(quarter_serial))

# 3. 执行二维匹配
matched_data <- output_long |>
  left_join(input_long, by = c("country", "quarter_serial")) |>
  mutate(final_value = coalesce(value, placeholder)) |>
  select(country, quarter_serial, final_value)

# 4. 转回宽格式，完全恢复输出矩阵的原始结构
result_matrix <- matched_data |>
  pivot_wider(names_from = quarter_serial, values_from = final_value) |>
  slice(match(output_matrix$...1, country)) |>
  select(country, as.character(colnames(output_matrix)[-1])) |>
  rename(...1 = country)

# 保存结果覆盖原输出文件（如需备份可修改路径）
write_xlsx(result_matrix, output_file, sheetName = "ENTERHERE")

关键步骤说明 #

1. 日期格式统一：用convertToDate把输入的标准日期转为Excel日期序列号，和输出列名的数字对应
2. 长格式转换：把两个矩阵都转成长格式，让「国家+日期」成为匹配键，这是tidyverse处理二维匹配的标准方式
3. 保留原始结构：用slice(match(...))和指定列顺序，确保输出的国家顺序、列顺序和原文件完全一致，不会破坏原有格式
4. 匹配逻辑：left_join保证输出的所有行/列都被保留，coalesce自动用输入值替换占位的NA，无匹配则保持NA

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