将一行转换为多行和多列的固定数目

Question

我有一次争吵看起来是这样的：

|Data A-1|Data A-2|Data A-3|Data A-4|Data A-5|Data A-6|Data B-1|Data B-2|Data B-3|Data B-4|Data B-5|Data B-6|Data C-1|Data C-2|Data C-3|Data C-4|Data C-5|Data C-6|

有5列与每个数据相关。我需要将一行中的所有数据转换为如下所示的修复行和列：

|Data A-1|Data A-2|Data A-3|Data A-4|Data A-5|Data A-6|
|Data B-1|Data B-2|Data B-3|Data B-4|Data B-5|Data B-6|
|Data C-1|Data C-2|Data C-3|Data C-4|Data C-5|Data C-6|

我如何在谷歌单张中实现这一点？

示例表

Answer 1

在您的例子中，我认为可以使用这条线。

样本公式1：

对于您问题中的目标，下面的示例公式如何？

=ARRAYFORMULA(TRIM(SPLIT(TRANSPOSE(SPLIT(REGEXREPLACE(TEXTJOIN(",",TRUE,A1:1),"(([\w\s\S]+?,){6})","$1@"),"@")),",")))

• 在这种情况下，使用一行。因此，A1:1被用作范围。但是当您有几行时，请修改范围。行被6列分开。因此，(([\w\s\S]+?,){6})被用作正则表达式。
• 这个公式的流程如下。
  1. 通过使用TEXTJOIN忽略空单元格来连接所有单元格值。
  2. 使用@将REGEXREPLACE放到6列的联合文本值中。
  3. 使用@使用SPLIT拆分文本值。
  4. 使用TRANSPOSE转置分裂值。
  5. 使用,使用SPLIT拆分每一行。

结果：

​

​

样本公式2：

对于共享电子表格，下面的示例公式如何？

=ARRAYFORMULA(TRIM(SPLIT(TRANSPOSE(SPLIT(REGEXREPLACE(TEXTJOIN(",",TRUE,A8:8),"(([\w\s\S]+?,){8})","$1@"),"@")),",")))

• 在这种情况下，使用一行。因此，A8:8被用作范围。但是当您有几行时，请修改范围。并且，行被8列分开。因此，(([\w\s\S]+?,){8})被用作正则表达式。

结果：

​

​

注意：

• 在这种情况下，由于REGEXREPLACE(TEXTJOIN(",",TRUE,A8:8),"(([\w\s\S]+?,){8})","$1@")，当字符超过50,000时，会发生错误。在这种情况下，我建议使用Google脚本作为自定义函数。示例脚本如下所示。请将以下脚本复制并粘贴到电子表格的脚本编辑器中，并在使用共享电子表格时将=SAMPLE(A8:8, 8)放到单元格中。在这种情况下，A8:8和8的参数分别是范围和分裂数。这样，你的目标就能实现。 const SAMPLE =(值，拆分) => values.flatMap(r => { const temp = []；r.length> 0) temp.push(r.splice(0，split)；返回temp })；

参考文献：

• 泰克
• 雷格里斯
• 裂开
• 转座子
• Google中的自定义函数

添加：

关于你的评论提出的补充问题如下，

是否有任何方法使第一列被排序？

下面的示例公式如何？

样本公式：

=SORT(ARRAYFORMULA(TRIM(SPLIT(TRANSPOSE(SPLIT(REGEXREPLACE(TEXTJOIN(",",TRUE,A8:8),"(([\w\s\S]+?,){8})","$1@"),"@")),","))),1,TRUE)

• 在本例中，行按照升序按第一列排序。
• 当您使用Google脚本创建的自定义公式时，您还可以使用SORT，如下所示。 =排序(样本(A8:8，8),1，真)

- Or, you can also use the following script. When you use this, please put `=SAMPLE2(A8:8, 8)` to a cell.

const SAMPLE2 =(值，拆分) => values.flatMap(r => { const temp = []；values.flatMap (r.length > 0) temp.push(r.splice(0，split))；返回temp.sort((a，b) => a)；}；

Answer 2

或尝试：

=QUERY(
 {FLATTEN(FILTER(A8:15, MOD(COLUMN(A1:1)-1, 8)=0)), 
  FLATTEN(FILTER(A8:15, MOD(COLUMN(A1:1)-2, 8)=0)), 
  FLATTEN(FILTER(A8:15, MOD(COLUMN(A1:1)-3, 8)=0)), 
  FLATTEN(FILTER(A8:15, MOD(COLUMN(A1:1)-4, 8)=0)), 
  FLATTEN(FILTER(A8:15, MOD(COLUMN(A1:1)-5, 8)=0)), 
  FLATTEN(FILTER(A8:15, MOD(COLUMN(A1:1)-6, 8)=0)), 
  FLATTEN(FILTER(A8:15, MOD(COLUMN(A1:1)-7, 8)=0)), 
  FLATTEN(FILTER(A8:15, MOD(COLUMN(A1:1)-8, 8)=0))}, 
 "where Col1 is not null", 0)

​

​