如何用R/Python/Excel对区间数据执行VLOOKUP并核查费用差异

需求说明 #

现有两个Excel文件：

• source_data.xlsx：存储距离区间-标准成本对应关系（例：1-100公里对应4800）
• Analysis.xlsx：记录各站点的实际行程距离、已付总成本及状态

需要完成：

• 匹配实际距离对应的标准成本，新增source_cost列
• 计算费用差异Difference列（已付总成本 - 标准成本）
• 识别多付（Difference > 0）、少付（Difference < 0）情况

完整实现代码 #

# 加载所需包
library(readxl)
library(dplyr)
library(stringr)
library(fuzzyjoin)

# 读取数据
analysis <- read_excel("~/analysis.xlsx")
source_data <- read_excel("~/source_data.xlsx")

# 预处理标准成本数据：拆分距离区间为最小/最大距离值
# 假设source_data包含列：distance_range（如"1-100"）、standard_cost
source_data_clean <- source_data %>%
  mutate(
    min_dist = as.numeric(str_extract(distance_range, "^\\d+")),
    max_dist = as.numeric(str_extract(distance_range, "\\d+$"))
  )

# 匹配实际距离对应的标准成本
# 用模糊匹配找到实际距离落在的区间
analysis_matched <- fuzzy_left_join(
  analysis,
  source_data_clean,
  by = c("实际行程距离" = "min_dist", "实际行程距离" = "max_dist"),
  match_fun = list(`>=`, `<=`)
) %>%
  # 保留需要的列，并重命名标准成本列
  select(
    -distance_range, -min_dist, -max_dist,
    source_cost = standard_cost
  )

# 计算差异并识别多付/少付
analysis_result <- analysis_matched %>%
  mutate(
    Difference = 已付总成本 - source_cost,
    费用状态 = case_when(
      Difference > 0 ~ "多付",
      Difference < 0 ~ "少付",
      TRUE ~ "无差异"
    )
  )

# 查看结果
View(analysis_result)

# 可选：将结果导出为Excel
# library(writexl)
# write_xlsx(analysis_result, "费用差异核查结果.xlsx")

代码说明 #

1. 区间预处理：通过stringr拆分距离区间字符串，得到最小/最大距离数值，为后续匹配做准备
2. 模糊匹配：使用fuzzyjoin包的fuzzy_left_join，根据实际距离是否落在区间内完成匹配，替代Excel的近似匹配VLOOKUP，逻辑更直观
3. 差异计算与状态识别：通过mutate和case_when计算差值并标记多付/少付状态
4. 结果导出：可选将最终结果导出为Excel文件，方便后续分析

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