如何在R或Excel中基于染色体范围匹配合并表格并填充特征名

方法1：INDEX+MATCH数组公式（兼容多数Excel版本） #

假设Table 1的A列是染色体号、B列是物理位置、C列是特征名称；Table 2的A列是染色体号、B列是Start_Mb、C列是End_Mb，需要填充E列。

在Table 2的E2单元格输入公式，旧版Excel按Ctrl+Shift+Enter确认，新版直接回车即可：

=INDEX(Table1[特征名称], MATCH(1, (Table1[染色体号]=Table2[@染色体号])*(Table1[物理位置]>=Table2[@Start_Mb])*(Table1[物理位置]<=Table2[@End_Mb]), 0))

• 无匹配结果时会显示#N/A，可以套IFERROR改成空值：=IFERROR(INDEX(...), "")
• 这个公式只会返回满足条件的第一条匹配项。

方法2：XLOOKUP函数（Excel 365/2021及以上） #

如果用的是新版Excel，XLOOKUP写法更简洁：

=XLOOKUP(1, (Table1[染色体号]=Table2[@染色体号])*(Table1[物理位置]>=Table2[@Start_Mb])*(Table1[物理位置]<=Table2[@End_Mb]), Table1[特征名称], "")

• 最后一个参数""指定无匹配时返回空值，无需额外处理错误。

方法3：Power Query批量处理（大数据量首选） #

数据量大时公式会卡顿，用Power Query更高效：

1. 把两个表格导入Power Query（点击「数据」→「自表格/区域」）
2. 保留两表中需要的列：染色体号、物理位置/Start_Mb/End_Mb、特征名称
3. 选中Table 2，点击「合并查询」→「合并为新查询」，选择Table 1作为合并对象
4. 设置合并条件：染色体号相等，且Table1[物理位置]≥Table2[Start_Mb]，且Table1[物理位置]≤Table2[End_Mb]
5. 展开合并后的列，只提取特征名称
6. 关闭并上载到Excel，得到匹配后的结果

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