如何在R语言中实现Excel的XLOOKUP函数功能？

我正尝试在R语言中实现Excel的XLOOKUP函数功能，目前尚未成功。以下是我的示例数据框、尝试的代码以及预期输出：

示例数据框 #

dataF <- structure(list(
       A_1 = c(3.548393, 4.211518, 4.712385,3.895335,3.016505, 2.728305), 
       A_2 = c(3.879862, 4.061333, 4.950454, 4.242747,3.216443, 2.530415), 
       A_3 = c(6.543898, 8.928949, 9.418975, 8.372533,5.68308, 5.278409), 
       A_4 = c(3.493369, 4.108747, 4.477213, 3.256992,2.991532, 3.015297), 
       A_5 = c(3.548393, 4.211518, 4.712385, 3.895335,3.016505, 2.728305), 
       A_6 = c(7.428255, 8.272851, 9.662839, 8.138082,6.232948, 5.258721), 
       A_7 = c(10.09229, 13.14047, 14.13136, 12.26787,8.699586, 8.006714), 
       A_8 = c(13.64068, 17.35199, 18.84375, 16.1632,11.71609, 10.73502), 
       A_9 = c(17.46552, 21.31055, 23.55903, 19.76761,14.90756, 13.55243), 
       A_10 = c(21.01392, 25.52207, 28.27141, 23.66294,17.92407, 16.28073), 
       A_19 = c(2L, 2L, 2L, 2L, 2L, 3L), 
       A_20 = c(2L,2L, 1L, 1L, 2L, 2L), 
       A_21 = c(2L, 0L, 0L, 0L, 2L, 0L), 
       SearchVal = c(2.5,2, 2, 2, 2.5, 3)),row.names = c(NA, -6L),class = "data.frame")

df_lookupVal <- tribble(~threshold,0,1,2,2.5,3,3.5)

尝试的代码 #

dataF %>% mutate(New_Val = with(df_lookupVal, approx(df_lookupVal$threshold, 
                  dataF[,A_5:A_10], dataF[,SearchVal]))$y)

预期输出 #

> outputDF_with_newVariable$New_Val
[1] 13.64068 13.14047 14.13136 12.26787 11.71609 13.55243

我希望实现类似Excel中XLOOKUP(SearchVal, df_lookupVal$threshold, dataF$A_5:A_10)的功能，尝试使用approx函数时出现错误，请问是否有简便的实现方法？

解决方案 #

你的需求本质是根据每行的SearchVal匹配df_lookupVal$threshold的对应位置，再提取dataF中A_5:A_10的对应列值。注意df_lookupVal$threshold的顺序0,1,2,2.5,3,3.5正好对应A_5到A_10的6列（索引1到6分别对应阈值0到3.5），以下是两种简便实现方法：

方法1：用match结合行索引提取

library(dplyr)

# 建立阈值与列位置的映射
threshold_map <- df_lookupVal$threshold

dataF <- dataF %>%
  mutate(
    # 匹配每行SearchVal对应的阈值位置
    threshold_pos = match(SearchVal, threshold_map),
    # 根据位置提取对应列的值
    New_Val = rowSums(select(., A_5:A_10) * (col(select(., A_5:A_10)) == threshold_pos))
  )

方法2：用purrr逐行处理

library(dplyr)
library(purrr)

dataF <- dataF %>%
  mutate(New_Val = pmap_dbl(., function(...) {
    row <- tibble(...)
    # 匹配阈值位置
    pos <- match(row$SearchVal, df_lookupVal$threshold)
    # 提取对应A列的值（A_5对应pos=1，所以4+pos得到列名后缀）
    row[[paste0("A_", 4 + pos)]]
  }))

验证结果

运行上述代码后，dataF$New_Val会得到预期输出：

> dataF$New_Val
[1] 13.64068 13.14047 14.13136 12.26787 11.71609 13.55243

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