如何自定义 Google 表格中答案为“是”时连接的标头值的顺序

我怎样才能修改这个方程谷歌电子表格 https://docs.google.com/spreadsheets/d/1tHL2IdijLaAD5my7m4a1uaiXNleb6Z1bI7x_HPXfAVI/edit?usp=sharing我发现这是另一个问题的答案question https://stackoverflow.com/questions/70962054/array-formula-to-join-header-values-in-a-google-sheet-when-row-values-equal-yes/70962486#70962486这样它就可以允许用户自定义替换值的顺序？

Goal将给定行（即 A2:D4）中某个范围内的“是”答案替换为 E4 中按所需顺序以逗号分隔的标头值。

初步方法初步方法使用电子表格中的“辅助”单元格（更多信息见下图），但我希望能够帮助您组合两个独立的公式。

指定订单标题按照所需的顺序标有数字。原始公式将查找“是”并将其替换为范围内任何问题的标题值，并作为索引输出

然后，第二个公式将按字母顺序对值进行拆分和排序，然后 REGEXREPLACE 数字。

非常感谢任何将这两个公式结合起来的帮助！

SET #1:

=index(substitute(substitute(trim(transpose(query(transpose(if(B2:E<>"yes",,substitute(B1:E1," ","❄️"))),,9^9)))," ",", "),"❄️"," "))

AND

=TRIM(REGEXREPLACE(JOIN(",",SORT(TRANSPOSE(SPLIT(E2,",")))),"\b\d_",""))

另类套装#2

INDEX(REGEXREPLACE(TRIM(FLATTEN(QUERY(TRANSPOSE( IF(B2:E="yes", B1:E1&",", )),,9^9))), ",$", ))

AND

=TRIM(REGEXREPLACE(JOIN(",",SORT(TRANSPOSE(SPLIT(E2,",")))),"\b\d_",""))

预先感谢您的帮助！

共享的谷歌表格是here https://docs.google.com/spreadsheets/d/1tHL2IdijLaAD5my7m4a1uaiXNleb6Z1bI7x_HPXfAVI/edit?usp=sharing

use:

=INDEX(REGEXREPLACE(TRIM(FLATTEN(QUERY(TRANSPOSE(
 IF(TRANSPOSE(SORT(TRANSPOSE(A2:D), FLATTEN(A1:D1), 1))="yes", 
 REGEXREPLACE(TRANSPOSE(SORT(FLATTEN(A1:D1))), "(^\d+_)", )&",", )),,9^9))), ",$", ))