google-apps-script - 拆分并重复

Question

在这张表中，我有以下输入数据：

如所见，courses它们由/

我想以下面的格式显示相同的内容，其中每一行仅显示一门课程，学生的数据重复：

我知道 using=split(C3," / ",true,true)可以将课程分成同一行的 2 列，但我需要它们在同一列中，所以我尝试=TRANSPOSE(split(C3," / ",true,true))这仅适用于第一行，但使用ARRAYFORMULA.

任何想法？我愿意接受任何潜在的解决方案、公式或脚本或任何其他。

更新 我尝试了这个技巧，创建一个新列，显示每个学生的课程数量=ArrayFormula(LEN(REGEXREPLACE(C11:C13, "[^/]", ""))+1)

然后使用Rep根据课程数量重复每一行，=arrayformula({transpose(split(concatenate(rept(B11:B13 & ",",D11:D13)),",",false,true)),transpose(split(concatenate(REPT(C11:C13 & ",",D11:D13)),",",false,true))})然后结束：

但是在这里，我的课程仍然在一起，我怎么能把它们分开！

标签： google-apps-scriptgoogle-sheets

Answer 1

我在您的示例电子表格中添加了两张工作表。“Sheet2”是您的测试表“Sheet1”的清理。另一张表（“Erik 帮助”）引用的是 Sheet2，而不是 Sheet1，并在单元格 A1 中包含以下公式：

=ArrayFormula({"Student ID","Student Name","Course";SUBSTITUTE(SPLIT(QUERY(FLATTEN(SPLIT(FILTER(SUBSTITUTE("/ "&Sheet2!C3:C,"/","/ "&Sheet2!A3:A&"zzz~"&Sheet2!B3:B&"~"),Sheet2!A3:A<>""),"/")),"Select * WHERE Col1 Is Not Null"),"~"),"zzz","")})

这个数组公式生成所有标题和结果。

在大括号 { } 之间形成一个虚拟数组。首先引入标题，然后是分号，这意味着“向下移动一行以继续”。标题标题可以随意更改。

这个怎么运作：

在 Sheet2!C2:C 中每个非空白条目的前面都连接了一个加法“/”。然后SUBSTITUTE将这些正斜杠中的每一个替换为 Col A 数据、“zzz~”、Col B 数据和“~”。波浪线 (~) 稍后将由外部使用SPLIT。添加“zzz”以确保将 ID 号转换为文本，以便它们在整个处理过程中保持格式并且不会变成实数；稍后，外部SUBSTITUTE将用 null 替换那些（即，去掉“zzz”）。

初始连接完成后，它们SPLIT位于正斜杠处，然后FLATTEN编入一列。QUERY到目前为止，删除此虚拟数组中的所有空白行。剩余的结果再次出现SPLIT在波浪号处。最后，该外部SUBSTITUTE删除了“zzz”的临时实例。

我还为交替行上的交替色带添加了一个自定义 CF 公式。