Google Sheets中TRANSPOSE+QUERY查询结果按指定MaterialID排序优化

核心思路 #

放弃依赖原数据顺序的QUERY直接返回结果，转而以目标表的MaterialID序列为基准，逐个匹配对应条件的Quantity值，同时用错误捕获处理空缺场景。

公式示例（适配单/多BlueprintID场景） #

假设：

• 目标表的MaterialID序列位于E1:E100（即你需要按顺序展示的MaterialID-1、MaterialID-2...）
• 查询的ItemID位于单元格$G$1

1. 数组公式（一次性返回所有结果）

=ARRAYFORMULA(IFERROR(VLOOKUP(E1:E100, QUERY(Materials!A:D, "Select C,D where A matches '"&JOIN("|", QUERY(Items!A:C, "Select A where C = '"&$G$1&"'", 0))&"' and B = 1", 0), 2, FALSE), ""))

2. 单个单元格公式（可横向/纵向拖拽）

如果不需要数组公式，在目标表第一个结果单元格输入以下公式，再拖拽覆盖所有MaterialID对应的位置：

=IFERROR(INDEX(Materials!D:D, MATCH(1, (Materials!A:A=QUERY(Items!A:C, "Select A where C = '"&$G$1&"'", 0))*(Materials!B:B=1)*(Materials!C:C=E1), 0)), "")

公式说明 #

1. 内层QUERY：从Items表中筛选出当前ItemID对应的所有BlueprintID，用JOIN("|", ...)把多个BlueprintID拼接成正则匹配格式（适配多BlueprintID场景）。
2. 中层QUERY：从Materials表中筛选出符合BlueprintID、ActivityID=1的MaterialID和Quantity，得到结构化的<MaterialID, Quantity>数据集。
3. VLOOKUP/INDEX+MATCH：以目标表的MaterialID为查询键，按目标表顺序逐个匹配对应Quantity，确保结果顺序完全符合要求。
4. IFERROR：捕获匹配失败的情况，返回空值（若需要显示0，可将""改为0）。

注意事项 #

• 替换公式中的单元格范围（E1:E100、$G$1）为你实际的目标表MaterialID序列位置和ItemID位置。
• 若ItemID仅对应单个BlueprintID，可简化公式，把JOIN("|", ...)部分直接替换为单个BlueprintID的QUERY结果，性能会略有提升。

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