如何在R中实现Excel的MATCH INDEX等效二维查找并填充转置矩阵

以下是针对需求的具体实现方案，基于tidyverse工具链完成，无需依赖Excel函数：

核心思路 #

因为输入和输出矩阵是转置关系且行列顺序不一致，我们先把输入数据转为长格式（每行对应一个<行名, 列名, 值>的组合），再和输出矩阵的所有行列组合做匹配，最后转回宽格式填充到输出数据框中。

具体步骤（附代码示例） #

假设你已经将两个Excel文件读取为以下结构的dataframe：

• input_df：输入矩阵，第一列为行名，其余列为数据列
• output_df：待填充的输出矩阵，第一列为行名（对应输入矩阵的列名），其余列为待填充的列（对应输入矩阵的行名）

1. 加载依赖包 #

library(tidyverse)

2. 示例数据（可替换为你的实际数据） #

# 输入矩阵：行名A/B/C，列名X/Y/Z
input_df <- data.frame(
  Row = c("A", "B", "C"),
  X = c(10, 20, 30),
  Y = c(15, 25, 35),
  Z = c(18, 28, 38)
)

# 输出矩阵：行名Y/X/Z（顺序打乱），列名B/A/C（顺序打乱），值为NA待填充
output_df <- data.frame(
  Row = c("Y", "X", "Z"),
  B = NA,
  A = NA,
  C = NA
)

3. 将输入矩阵转为长格式 #

把输入的宽格式矩阵拆成每行对应一个二维坐标的记录：

input_long <- input_df %>%
  pivot_longer(
    cols = -Row,  # 排除行名列
    names_to = "Input_Col",  # 原输入的列名
    values_to = "Value"  # 对应的值
  )

4. 生成输出矩阵的所有行列组合 #

提取输出矩阵的行名和列名，生成需要匹配的全部二维坐标：

output_coords <- expand.grid(
  Output_Row = output_df$Row,  # 输出的行名（对应输入的列名）
  Output_Col = colnames(output_df)[-1]  # 输出的列名（对应输入的行名），排除第一列的行名
)

5. 匹配对应的值 #

因为输出是输入的转置，所以输出的行名对应输入的列名，输出的列名对应输入的行名，通过left_join完成匹配：

matched_data <- output_coords %>%
  left_join(
    input_long,
    by = c("Output_Row" = "Input_Col", "Output_Col" = "Row")
  )

6. 转回宽格式并填充到输出矩阵 #

把匹配后的长格式数据转回宽格式，保持输出矩阵的行列顺序，最后替换原输出矩阵的NA值：

# 转回宽格式，按输出的列名排列
filled_wide <- matched_data %>%
  pivot_wider(
    names_from = Output_Col,
    values_from = Value
  ) %>%
  # 按原输出矩阵的行顺序排序
  arrange(factor(Output_Row, levels = output_df$Row))

# 将填充好的数据替换原输出矩阵的待填充列
output_df[, -1] <- filled_wide[, -1]

最终效果 #

执行完上述代码后，output_df会被填充为：

注意事项 #

• 如果输出矩阵中存在输入矩阵没有的行列名，对应位置会保留NA，你可以根据需求用replace_na()函数处理
• 若你的输入矩阵行名不是单独一列，而是作为行名（rownames），可以先用rownames_to_column(input_df, "Row")转换为列

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