Excel MS365：如何用公式基于二维数组生成指定格式动态输出？

问题说明 #

给定输入区域A1:B3（每行存储一个起始和结束列位置），需要一次性生成输出区域D1:K3，规则是：每行根据A列的起始值和B列的结束值，填充输出区域中对应位置的列（例如输入{1,4}意味着该行输出的第1至4列（对应D到G）需要填充内容，其余列留空）。输出宽度由输入数组中B列的最大值决定。

示例参考：

输入（A1:B3）：

• A1=1, B1=4
• A2=3, B2=6
• A3=5, B3=8
  期望输出（D1:K3）：
• D1-G1填充指定内容，H1-K1为空
• F2-I2填充指定内容，D2-E2、J2-K2为空
• H3-K3填充指定内容，D3-G3为空

简洁数组公式方案 #

无需逐行处理，用以下动态数组公式可以直接生成整个输出区域：

=LET(
    input, A1:B3,
    total_rows, ROWS(input),
    output_cols, MAX(INDEX(input,,2)),
    col_numbers, SEQUENCE(1, output_cols),
    row_numbers, SEQUENCE(total_rows),
    fill_flag, (col_numbers >= INDEX(input, row_numbers, 1)) * (col_numbers <= INDEX(input, row_numbers, 2)),
    IF(fill_flag, "X", "")
)

公式解释 #

• input：定义输入区域，简化后续引用
• total_rows：获取输入的总行数
• output_cols：提取输入中第二列的最大值，确定输出的总列数
• col_numbers：生成1到output_cols的水平序列，对应输出区域的每一列位置
• row_numbers：生成1到total_rows的垂直序列，对应输入的每一行
• fill_flag：通过数组逻辑运算，逐行逐列判断当前列是否在该行的起始-结束范围内（结果为1或0的数组）
• 最后用IF将符合条件的位置填充指定内容（示例中为"X"），不符合的留空

注意事项 #

• 该公式仅支持Excel 365、Excel 2021及以上版本的动态数组功能，输入后会自动溢出填充整个目标区域
• 如需填充其他内容，将公式中的"X"替换为你需要的文本或数值即可
• 对比你之前用SEQUENCE/SWITCH的逐行方案，此公式通过数组广播实现批量处理，更简洁高效

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