Google Sheets拆分逗号文本为行并自动填充对应抽屉号的公式方法

直接在你要生成结果的独立工作表的空白单元格输入以下数组公式即可自动溢出全部结果，不需要手动下拉填充：

=LET(
  源数据,FILTER(Sheet1!A2:C,Sheet1!A2:A<>""),
  抽屉号列,CHOOSECOLS(源数据,1),
  物品清单列,CHOOSECOLS(源数据,3),
  拆分后物品,BYROW(物品清单列,LAMBDA(行内容,IF(行内容="",,SPLIT(TRIM(行内容),",")))),
  抽屉号重复规则,BYROW(SEQUENCE(ROWS(抽屉号列)),LAMBDA(行号,IFERROR(REPT(INDEX(抽屉号列,行号)&"|",COLUMNS(INDEX(拆分后物品,行号))),""))),
  最终抽屉号列,TOCOL(SPLIT(TEXTJOIN("",TRUE,抽屉号重复规则),"|"),1),
  最终物品列,TOCOL(拆分后物品,1),
  {"抽屉号","物品";最终抽屉号列,最终物品列}
)

适配调整说明 #

• 如果你的抽屉号和物品清单在Sheet1的其他列，修改CHOOSECOLS的第二个参数即可：比如抽屉号在B列就把对应行改成CHOOSECOLS(源数据,2)，物品清单在D列就改成CHOOSECOLS(源数据,4)
• 如果源数据是从第3行开始，把Sheet1!A2:C调整为Sheet1!A3:C
• 如果物品清单用中文逗号分隔，把SPLIT(TRIM(行内容),",")里的英文逗号替换为中文逗号，

效果说明 #

公式会自动识别每个抽屉的物品数量，自动填充对应次数的抽屉号，后续在Sheet1新增/修改抽屉和物品内容，结果页会自动同步更新，无需手动调整。

内容的提问来源于stack exchange，提问作者Lil Mami Chula