当前位置：文江博客话题详情

将是/否答案标题替换值的范围加入到谷歌表格中的单个单元格中

发布于 2025-01-20 23:00:43 字数 2230 浏览 4 评论 0原文

在

到目前为止，已经完成了许多工作，但遇到了一些困难，并感谢任何帮助。

所需的输出看起来像这样：

目标1：结合是，否替换公式。

当前公式需要单独/否列，但我想将答案结合起来，显示替换的答案，上面替换的答案否答案并与换行符分开。

目标2：在有条件地插入字符串开始的其他标头文本时，当是/否文本时

我添加了基于标题文本和定界符的文本插入。如果已经提供了“是”替换答案，它将在标题中的定界符的左侧插入文本（提供“否”替换时的反向）。例如，提供了一个标头字符串，例如“ Likes |不喜欢”，然后是Regex
regexextract（j1，“^（。*）|”

将提取管道左侧的文本以获取“是”答案，另一个Regex 当我尝试创建一个“条件”

时，我遇到了一个问题，只有在没有替换时，才能在此中进行此文本，而当时我尝试将此文本重新定位时，才会出现。如下所示，使用本应有效但不起作用的正则是：

我如何在此 google verversheet 以实现我的目标。 -formula-to-join-header-neader-a-a-google-sheet-ne-reow-values-equal-yes/70962486＃70962486“>问题

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

初步方法 使用单独的方程式使用

第三个“助手”单元格在同一范围内替换“否”答案，以添加“否”值，低于YES值

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

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

将这三个公式相结合以实现上述目标的任何助理，都非常感谢！

INDEX(REGEXEXTRACT(G1,"^(.*)\|")&REGEXREPLACE(TRIM(FLATTEN(QUERY(TRANSPOSE(IF(TRANSPOSE(SORT(TRANSPOSE(A2:C3), FLATTEN(A1:C1), 1))="yes", REGEXREPLACE(TRANSPOSE(SORT(FLATTEN(A1:C1))), "(^\d+_)", )&",", )),,9^9))), ",$", ))

和

INDEX( REGEXEXTRACT(G1,"\|(.*)")&REGEXREPLACE(TRIM(FLATTEN(QUERY(TRANSPOSE( IF(TRANSPOSE(SORT(TRANSPOSE(A2:C3), FLATTEN(A1:C1), 1))="no", REGEXREPLACE(TRANSPOSE(SORT(FLATTEN(A1:C1))), "(^\d+_)", )&",", )),,9^9))), ",$", ))

替代套件2

并

替换不跟随其他文本的regextract值，

共享的Google表为在这里

原文

Replace "yes" or "no" values in this sheet a range of row (ie A2:C4) with column header value & when value (yes,no) is present then insert additional text from header text prior to all values with text for "yes" or "no" if not blank by modifying the formulas below that are explained further below.

A lot of work has been done thus far but have run into some difficulty and appreciate any help.

The desired output looks like this:

Goal 1: Combining Yes, No replacement formulas

The current formula requires separate yes/no columns but I would like to combine the answers showing the replaced yes answers above the replaced no answers and separate with a line break.

Goal 2: Inserting additional header text at beginning of string conditionally When yes/no text is present

I added text insertion based on header text and a delimiter. If "yes" replacement answer has been provided it inserts text from the left of a delimiter in header (the reverse when "no" replacement was provided). For example, a header string is provided like "Likes |Doesn't like " and then Regex
REGEXEXTRACT(J1,"^(.*)|"

will extract the text to the left of the pipe for "yes" answers, another regex to the right for "no"

I ran into a problem when I tried to create a "condition" where this would only be present if yes/no replacement was present. I did this by trying to RegExReplace this text when not followed by additional text using regex which should have worked, but did not, as shown here:

How can I modify the equation in this Google Spreadsheet to achieve my objective. This is based on previously answered question

Prior equation
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
Replacing "no" answers in same range using a separate equation

Using a third "helper" cell to add "no" values below the yes values

Specified 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 three formulas to achieve above objective is much appreciated!

INDEX(REGEXEXTRACT(G1,"^(.*)\|")®EXREPLACE(TRIM(FLATTEN(QUERY(TRANSPOSE(IF(TRANSPOSE(SORT(TRANSPOSE(A2:C3), FLATTEN(A1:C1), 1))="yes", REGEXREPLACE(TRANSPOSE(SORT(FLATTEN(A1:C1))), "(^\d+_)", )&",", )),,9^9))), ",quot;, ))

and

INDEX( REGEXEXTRACT(G1,"\|(.*)")®EXREPLACE(TRIM(FLATTEN(QUERY(TRANSPOSE( IF(TRANSPOSE(SORT(TRANSPOSE(A2:C3), FLATTEN(A1:C1), 1))="no", REGEXREPLACE(TRANSPOSE(SORT(FLATTEN(A1:C1))), "(^\d+_)", )&",", )),,9^9))), ",quot;, ))

ALTERNATIVE SET #2

and

REPLACE REGEXTRACTED VALUE WHEN NOT FOLLOWED BY ADDITIONAL TEXT

The shared google sheet is here

分享到QQ

分享到微博

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

发布评论

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

牵强ㄟ 2025-01-27 23:00:43

在G2中使用：

=ARRAYFORMULA(IF(REGEXMATCH(FLATTEN(QUERY(TRANSPOSE(A2:C4),,9^9)), "yes"), 
 REGEXEXTRACT(G1, "(.*)\|"), )®EXREPLACE(TRIM(FLATTEN(QUERY(TRANSPOSE(
 IF(TRANSPOSE(SORT(TRANSPOSE(A2:C4), FLATTEN(A1:C1), 1))="yes", 
 REGEXREPLACE(TRANSPOSE(SORT(FLATTEN(A1:C1&","))), "(^\d+_)", ), )),,9^9))), ",$", ".")&
 IF(REGEXMATCH(FLATTEN(QUERY(TRANSPOSE(A2:C4),,9^9)), "no"), 
 IF(REGEXMATCH(FLATTEN(QUERY(TRANSPOSE(A2:C4),,9^9)), "yes"), CHAR(10), )&
 REGEXEXTRACT(G1, "\|(.*)"), )®EXREPLACE(TRIM(FLATTEN(QUERY(TRANSPOSE(
 IF(TRANSPOSE(SORT(TRANSPOSE(A2:C4), FLATTEN(A1:C1), 1))="no", 
 REGEXREPLACE(TRANSPOSE(SORT(FLATTEN(A1:C1&","))), "(^\d+_)", ), )),,9^9))), ",$", "."))

在H2中使用：

=ARRAYFORMULA(IF(REGEXMATCH(FLATTEN(QUERY(TRANSPOSE(E2:F4),,9^9)), "yes"), 
 REGEXEXTRACT(H1, "(.*)\|"), )®EXREPLACE(TRIM(FLATTEN(QUERY(TRANSPOSE(
 IF(TRANSPOSE(SORT(TRANSPOSE(E2:F4), FLATTEN(E1:F1), 1))="yes", 
 REGEXREPLACE(TRANSPOSE(SORT(FLATTEN(E1:F1&","))), "(^\d+_)", ), )),,9^9))), ",$", ".")&
 IF(REGEXMATCH(FLATTEN(QUERY(TRANSPOSE(E2:F4),,9^9)), "no"), 
 IF(REGEXMATCH(FLATTEN(QUERY(TRANSPOSE(E2:F4),,9^9)), "yes"), CHAR(10), )&
 REGEXEXTRACT(H1, "\|(.*)"), )®EXREPLACE(TRIM(FLATTEN(QUERY(TRANSPOSE(
 IF(TRANSPOSE(SORT(TRANSPOSE(E2:F4), FLATTEN(E1:F1), 1))="no", 
 REGEXREPLACE(TRANSPOSE(SORT(FLATTEN(E1:F1&","))), "(^\d+_)", ), )),,9^9))), ",$", "."))

use in G2:

=ARRAYFORMULA(IF(REGEXMATCH(FLATTEN(QUERY(TRANSPOSE(A2:C4),,9^9)), "yes"), 
 REGEXEXTRACT(G1, "(.*)\|"), )®EXREPLACE(TRIM(FLATTEN(QUERY(TRANSPOSE(
 IF(TRANSPOSE(SORT(TRANSPOSE(A2:C4), FLATTEN(A1:C1), 1))="yes", 
 REGEXREPLACE(TRANSPOSE(SORT(FLATTEN(A1:C1&","))), "(^\d+_)", ), )),,9^9))), ",quot;, ".")&
 IF(REGEXMATCH(FLATTEN(QUERY(TRANSPOSE(A2:C4),,9^9)), "no"), 
 IF(REGEXMATCH(FLATTEN(QUERY(TRANSPOSE(A2:C4),,9^9)), "yes"), CHAR(10), )&
 REGEXEXTRACT(G1, "\|(.*)"), )®EXREPLACE(TRIM(FLATTEN(QUERY(TRANSPOSE(
 IF(TRANSPOSE(SORT(TRANSPOSE(A2:C4), FLATTEN(A1:C1), 1))="no", 
 REGEXREPLACE(TRANSPOSE(SORT(FLATTEN(A1:C1&","))), "(^\d+_)", ), )),,9^9))), ",quot;, "."))

use in H2:

=ARRAYFORMULA(IF(REGEXMATCH(FLATTEN(QUERY(TRANSPOSE(E2:F4),,9^9)), "yes"), 
 REGEXEXTRACT(H1, "(.*)\|"), )®EXREPLACE(TRIM(FLATTEN(QUERY(TRANSPOSE(
 IF(TRANSPOSE(SORT(TRANSPOSE(E2:F4), FLATTEN(E1:F1), 1))="yes", 
 REGEXREPLACE(TRANSPOSE(SORT(FLATTEN(E1:F1&","))), "(^\d+_)", ), )),,9^9))), ",quot;, ".")&
 IF(REGEXMATCH(FLATTEN(QUERY(TRANSPOSE(E2:F4),,9^9)), "no"), 
 IF(REGEXMATCH(FLATTEN(QUERY(TRANSPOSE(E2:F4),,9^9)), "yes"), CHAR(10), )&
 REGEXEXTRACT(H1, "\|(.*)"), )®EXREPLACE(TRIM(FLATTEN(QUERY(TRANSPOSE(
 IF(TRANSPOSE(SORT(TRANSPOSE(E2:F4), FLATTEN(E1:F1), 1))="no", 
 REGEXREPLACE(TRANSPOSE(SORT(FLATTEN(E1:F1&","))), "(^\d+_)", ), )),,9^9))), ",quot;, "."))