如何创建实现数对差值匹配与名称关联的Excel公式？

前提假设 #

假设数据结构为：名称列在A2:A10，数值列在B2:B10，目标结果输出在右侧空白区域（如D列开始）。

1. 生成所有有效数对（含名称、数值、差值） #

先通过动态数组生成所有不重复的数对（仅计算i<j的组合，避免重复计算A-B和B-A），可放在辅助区域（如E2开始）：

=LET(
    names, A2:A10,
    values, B2:B10,
    pair_mask, TOCOL(ROW(names) < TRANSPOSE(ROW(names)) * 1, 2),
    name_pairs, TOCOL(names & "|" & TRANSPOSE(names), 2),
    value_pairs, TOCOL(values & "|" & TRANSPOSE(values), 2),
    diffs, TOCOL(ABS(values - TRANSPOSE(values)), 2),
    valid_pairs, FILTER(HSTACK(TEXTSPLIT(name_pairs, "|"), TEXTSPLIT(value_pairs, "|"), diffs), pair_mask),
    FILTER(valid_pairs, INDEX(valid_pairs,,5) > 0)
)

公式说明：

• 最后一层FILTER用于排除差值为0的完全重复数值对，若需保留重复，删除此条件即可
• 输出结果为5列：名称1、名称2、数值1、数值2、差值

2. 提取最小差值数对及结果 #

单结果输出（取第一个最小差值数对） #

在目标单元格（如D2）输入：

=XLOOKUP(MIN(INDEX(E2:I100,,5)), INDEX(E2:I100,,5), E2:H100, "无匹配")

在差值单元格（如F2）输入：

=MIN(INDEX(E2:I100,,5))

多结果输出（若存在多个最小差值数对） #

=FILTER(E2:H100, INDEX(E2:I100,,5) = MIN(INDEX(E2:I100,,5)))

3. 提取次小差值数对及结果 #

单结果输出 #

在目标单元格（如H2）输入：

=LET(
    min_diff, MIN(INDEX(E2:I100,,5)),
    filtered_data, FILTER(E2:I100, INDEX(E2:I100,,5) > min_diff),
    XLOOKUP(MIN(INDEX(filtered_data,,5)), INDEX(filtered_data,,5), INDEX(filtered_data,,1):INDEX(filtered_data,,4), "无匹配")
)

在差值单元格（如J2）输入：

=LET(
    min_diff, MIN(INDEX(E2:I100,,5)),
    filtered_data, FILTER(E2:I100, INDEX(E2:I100,,5) > min_diff),
    MIN(INDEX(filtered_data,,5))
)

多结果输出 #

=LET(
    min_diff, MIN(INDEX(E2:I100,,5)),
    filtered_data, FILTER(E2:I100, INDEX(E2:I100,,5) > min_diff),
    second_min, MIN(INDEX(filtered_data,,5)),
    FILTER(INDEX(filtered_data,,1):INDEX(filtered_data,,4), INDEX(filtered_data,,5) = second_min)
)

4. 无辅助列直接输出（整合版） #

不想用辅助列的话，可直接在目标区域输入整合公式，一次性输出最小差值数对及结果：

=LET(
    names, A2:A10, values, B2:B10,
    pair_mask, TOCOL(ROW(names) < TRANSPOSE(ROW(names)) * 1, 2),
    diffs, TOCOL(ABS(values - TRANSPOSE(values)), 2),
    min_diff, MIN(FILTER(diffs, pair_mask * (diffs>0))),
    match_pos, XMATCH(min_diff, FILTER(diffs, pair_mask * (diffs>0))),
    pos_list, TOCOL(IF(pair_mask, ROW(names) & ":" & TRANSPOSE(ROW(names))), 2),
    target_pos, TEXTSPLIT(INDEX(pos_list, match_pos), ":"),
    HSTACK(INDEX(names, target_pos[1]), INDEX(names, target_pos[2]), INDEX(values, target_pos[1]), INDEX(values, target_pos[2]), min_diff)
)

次小差值整合公式同理，只需在过滤条件中加入diffs>min_diff即可。

重复数值处理说明 #

• 若需保留差值为0的重复数值对：删除公式中diffs>0的过滤条件，这类数对会被优先识别为最小差值结果
• 若存在多个同差值数对：使用FILTER替代XLOOKUP，可一次性输出所有匹配的数对，再根据需求调整显示布局

内容的提问来源于stack exchange，提问作者Fish In a Tree 0