Excel技术问询：如何根据Length和Weight查询矩阵上界值，无匹配返回1.0

前提说明 #

因无法查看你提供的矩阵附图，以下基于两种常见的矩阵结构给出实现方案，你可根据实际结构调整：

情况1：结构化数据表（列存储上界与对应值） #

假设你的矩阵是三列结构：

• A列：Length的上界值（必须升序排列）
• B列：Weight的上界值（必须升序排列）
• C列：对应Length/Weight上界组合的Value值

输入值：Length存于E2，Weight存于F2

实现公式（Excel 365及以上版本） #

=LET(
    len_input, E2,
    weight_input, F2,
    len_upper_list, A$2:A$10,
    weight_upper_list, B$2:B$10,
    value_list, C$2:C$10,
    -- 找到Length的下一个上界位置
    len_match_pos, MATCH(len_input, len_upper_list, 1),
    len_upper_pos, IF(INDEX(len_upper_list, len_match_pos)=len_input, len_match_pos, len_match_pos+1),
    len_upper_val, IFERROR(INDEX(len_upper_list, len_upper_pos), NA()),
    -- 找到Weight的下一个上界位置
    weight_match_pos, MATCH(weight_input, weight_upper_list, 1),
    weight_upper_pos, IF(INDEX(weight_upper_list, weight_match_pos)=weight_input, weight_match_pos, weight_match_pos+1),
    weight_upper_val, IFERROR(INDEX(weight_upper_list, weight_upper_pos), NA()),
    -- 匹配对应Value，无匹配则返回1.0
    final_result, IFERROR(INDEX(value_list, MATCH(1, (len_upper_list=len_upper_val)*(weight_upper_list=weight_upper_val), 0)), 1.0),
    final_result
)

低版本Excel兼容方案（无LET函数） #

=IFERROR(INDEX(C:C,MATCH(1,(A:A=IFERROR(INDEX(A:A,IF(INDEX(A:A,MATCH(E2,A:A,1))=E2,MATCH(E2,A:A,1),MATCH(E2,A:A,1)+1)),NA()))*(B:B=IFERROR(INDEX(B:B,IF(INDEX(B:B,MATCH(F2,B:B,1))=F2,MATCH(F2,B:B,1),MATCH(F2,B:B,1)+1)),NA())),0)),1.0)

情况2：二维交叉矩阵（行/列分别存储上界） #

假设矩阵是交叉结构：

• 第一行（B1:J1）：Weight的上界值（必须升序排列）
• 第一列（A2:A10）：Length的上界值（必须升序排列）
• 单元格区域（B2:J10）：对应Length/Weight交叉的Value值

输入值：Length存于E2，Weight存于F2

实现公式（Excel 365及以上版本） #

=LET(
    len_input, E2,
    weight_input, F2,
    len_upper_col, A$2:A$10,
    weight_upper_row, B$1:J$1,
    value_matrix, B$2:J$10,
    -- 定位Length的下一个上界
    len_match_pos, MATCH(len_input, len_upper_col, 1),
    len_upper_pos, IF(INDEX(len_upper_col, len_match_pos)=len_input, len_match_pos, len_match_pos+1),
    len_upper_val, IFERROR(INDEX(len_upper_col, len_upper_pos), NA()),
    -- 定位Weight的下一个上界
    weight_match_pos, MATCH(weight_input, weight_upper_row, 1),
    weight_upper_pos, IF(INDEX(weight_upper_row, weight_match_pos)=weight_input, weight_match_pos, weight_match_pos+1),
    weight_upper_val, IFERROR(INDEX(weight_upper_row, weight_upper_pos), NA()),
    -- 匹配交叉单元格的Value，无匹配则返回1.0
    final_result, IFERROR(INDEX(value_matrix, MATCH(len_upper_val, len_upper_col, 0), MATCH(weight_upper_val, weight_upper_row, 0)), 1.0),
    final_result
)

关键注意事项 #

• 所有上界值区域必须升序排列，否则MATCH近似匹配会失效。
• 若输入值大于所有上界值，IFERROR会自动触发fallback，返回1.0。
• 公式中引用的区域（如A$2:A$10）请替换为你实际的矩阵范围。

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