google-sheets - 在查询结果中添加可变文本（连续小写字母）

Question

我在本表第 3 页的 Col B 处收到如下字符串：

) 原因 1，姓名：布鲁诺 (2º OfSC, 3844923)；
) 原因 1，姓名：Wladimir (3º OfSC, 3693237)；
) 原因 2，姓名：Jorge (9º OfSC, 4715814)；
) 原因 3，姓名：Holden (1º OfSC, 4541195)；
) Reason4，姓名：John (5º OfSC, 4729589)；
) 原因 5，姓名：安东尼 (6º OfSC)；
) Reason5，姓名：Edson (7º OfSC)；

有没有办法使用CHAR(ROW(97))或lower(regexreplace(address(1,row(A1)),"[^A-Z]",""))进入公式=IFERROR( TEXTJOIN( CHAR(10); TRUE; TRANSPOSE( QUERY(Page2!$A$3:$C; "select C where A <= date '" & TEXT( DATEVALUE(A4); "yyyy-mm-dd") & "' and (B >= date '" & TEXT( DATEVALUE(A4); "yyyy-mm-dd") & "' or B is null)"))))来获得以下结果？

a) 原因 1，姓名：布鲁诺 (2º OfSC, 3844923)；
b) 原因 1，姓名：Wladimir (3º OfSC, 3693237)；
c) 原因 2，姓名：Jorge (9º OfSC, 4715814)；
d) 原因 3，姓名：Holden (1º OfSC, 4541195)；
e) 原因 4，姓名：John (5º OfSC, 4729589)；
f) 原因 5，姓名：Antony (6º OfSC)；
g) 原因 5，姓名：Edson (7º OfSC)；

我= TEXTJOIN( CHAR(10); TRUE; TRANSPOSE( ARRAYFORMULA QUERY({CHAR(ROW(97)&Page2!$A$3:$C}; "select C where A <= date '" & TEXT( DATEVALUE(A4); "yyyy-mm-dd") & "' and (B >= date '" & TEXT( DATEVALUE(A4); "yyyy-mm-dd") & "' or B is null)")))在 Page3 的 Col C 上尝试过不成功

此外，如果公式可以是一个数组，那就太好了！

标签： google-sheetsgoogle-sheets-formulaarray-formulas

Answer 1

如果您看一下您的样本表，就会有一个名为 MK.Idea 的新选项卡。

在其中，您将在单元格 I1 中找到此公式：

=ARRAYFORMULA(QUERY(IFERROR(SPLIT(FLATTEN(Page1!E3:E70&", Name:"&Page1!C3:C70&" ("&Page1!D3:D70&IF(LEN(Page1!F3:F70);", "&Page1!F3:F70;)&")|"&Page1!A3:A70+SEQUENCE(1;L2;0)&"|"&TEXT(Page1!A3:A70+SEQUENCE(1;L2;0);"mmmyyyy")&"|"&(1*SUBSTITUTE(Page1!B3:B70;"undefined";TODAY())));"|";0;0));"select Col2,Col1 where Col2<=Col4 and Col3 = "&DATE(A1;A2;1)&" order by Col2 label Col2'Date',Col1'Reason'"))

直接从 Page1 选项卡完成所有工作。除了原始数据，它不使用任何东西。所以第2页和第3页不是必需的。

此公式提供动态日期：

={"Date";SEQUENCE(DAY(EOMONTH(DATE(A1;A2;1);0));1;DATE(A1;A2;1))}

然后构建为数组公式的“过滤器”类型公式得到结果。

={"Reasons";ARRAYFORMULA(VLOOKUP(A4:A34;SPLIT(TRANSPOSE(SUBSTITUTE(TRIM(QUERY(QUERY({F:F&"|"\CHAR(10)&H:H&G:G};"select MAX(Col2) group by Col2 pivot Col1");;9^9));" "&CHAR(10);";"&CHAR(10)));"|;"&CHAR(10);0);2;0))}

在这里，带有我的解决方案的 OP 表的副本将永久存在。