Google Sheets多列工具ID合并至单列：现有公式优化咨询

问题背景 #

我有包含产品名称、工具名称及工具ID的表格数据，原始表格如下：

我希望将其整理为包含产品名称、工具名称、工具ID、工具类型的结构化输出，但当前使用的公式既无法显示工具类型，添加产品和工具名称时还会报错，公式如下：

=LET(
    data; C3:E11;
    row; ROW(C3:E11)-ROW(C3:C11)+1;
    column; COLUMN(C3:E11)-COLUMN(C3:C11)+1;
    sorted_data; FLATTEN(data);
    label; FLATTEN(IF(COLUMN(C3:E11)-COLUMN(C3:C11)=0; "Standard"; "Alternative"));
    result; IFERROR(FILTER(HSTACK(sorted_data; label); LEN(sorted_data)>0); HSTACK(""; ""));
    result
) 

公式优化方案 #

针对需求，以下是优化后的公式，解决了工具类型显示、关联产品/工具名称的问题：

=LET(
    source, A3:E11,
    prod_col, INDEX(source,,1),
    tool_col, INDEX(source,,2),
    id_cols, INDEX(source,,3):INDEX(source,,5),
    prod_repeat, FLATTEN(IF(id_cols<>"", prod_col, "")),
    tool_repeat, FLATTEN(IF(id_cols<>"", tool_col, "")),
    type_label, FLATTEN(IF(COLUMN(id_cols)=COLUMN(INDEX(source,,3)), "标准", "替代")),
    ids, FLATTEN(FILTER(id_cols, id_cols<>"")),
    final, FILTER(HSTACK(prod_repeat, tool_repeat, ids, type_label), ids<>""),
    IFERROR(final, {"无匹配数据", "", "", ""})
)

优化点说明 #

• 扩展数据源范围：从仅取ID列改为包含产品、工具名称列的完整数据源，确保能关联对应名称。
• 重复匹配名称：通过FLATTEN(IF(id_cols<>"", prod_col, ""))实现每个有效ID对应原行的产品/工具名称，避免错位。
• 修正工具类型生成逻辑：直接通过列位置判断（第三列为标准，其余为替代），确保每个ID对应正确标签。
• 过滤空值逻辑优化：统一过滤无效空ID，同时保留关联的名称和类型，避免结果混乱。
• 错误处理增强：无有效数据时返回明确提示，提升可读性。

额外注意事项 #

• 确保公式分隔符（如;）与你的Google Sheets区域设置匹配，英文环境需改为逗号,。
• 若后续新增替代工具列，只需调整id_cols的范围（如改为INDEX(source,,3):INDEX(source,,6)）即可适配。

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