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

发布于 2025-01-19 01:49:53 字数 1199 浏览 3 评论 0 原文

如何在此问题这样，它可以允许用户自定义替换值的顺序？

目标在给定行（即A2：d4）中替换“是”的答案，其标题值按E4中的逗号分隔为E4。

初步方法 初步方法在电子表格中使用“助手”单元格（图像下图），但我很感谢将两个独立公式组合起来的帮助。

指定顺序 标题标记为所需顺序的数字。原始公式将寻找一个“是”，并将其替换为范围内的任何问题的标题值，并作为索引输出

第二个公式将按字母顺序排列并对值进行分组，然后将数字置换。

任何结合这两个公式结合的助手都将受到赞赏！

设置＃1：

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

以及

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

替代套件＃2

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

，并

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

提前感谢您的帮助！

共享的Google表为

原文

How can I modify the equation in this Google Spreadsheet which I found as an answer to another question so that it can allow a user to customize the order of replaced values?

Goal
Replace "yes" answers in a range in a given row (ie A2:D4), with a header values separated by commas in E4 in an order that is desired.

Preliminary approach
The preliminary approach uses "helper" cells in the spreadsheet (more below image), but I would appreciate help in combining two independent formulas.

Specify order
The headers are labeled with a number in the desired order. The original formula will look for a "yes" and replace that with the header value for any questions in a range and outputs as an index

The second formula will then split and sort the values in alphabetical order and then REGEXREPLACE the numbers.

Any assitance to combine these two formulas is much appreciated!

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_",""))

ALTERNATIVE SET #2

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

AND

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

Thank you in advance for your help!

The shared google sheet is here

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

浅忆流年 2025-01-26 01:49:53

使用：

=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))), ",$", ))

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))), ",quot;, ))