我正在使用一个从电子邮件解析器获取数据的谷歌工作表。每次收到电子邮件时,都会创建一行,并填写以下列:
B, C, D, E, F, G, H, I, J, K, L, M, N, O, P, Q, R, S, T, U, V, W, X, Y, Z, AA, AB, AC, AD, AE, AF, AG, AH, AI, AJ, AK, AL, AM, AN, AO, AP, AQ。
我想把它输出为:
B1 | C1 | D1
E1 | F1 | G1
....
AO1 | AP1 | AQ1
B2 | C2 | D2
....这个是可能的吗?我试过用这个:
=FILTER({Sheet1!B:B,Sheet1!C:C,Sheet1!D:D;Sheet1!E:E,Sheet1!F:F,Sheet1!G:G;Sheet1!H:H,Sheet1!I:I,Sheet1!J:J;Sheet1!K:K,Sheet1!L:L,Sheet1!M:M;Sheet1!N:N,Sheet1!O:O,Sheet1!P:P;Sheet1!Q:Q,Sheet1!R:R,Sheet1!S:S;Sheet1!T:T,Sheet1!U:U,Sheet1!V:V;Sheet1!W:W,Sheet1!X:X,Sheet1!Y:Y;Sheet1!Z:Z,Sheet1!AA:AA,Sheet1!AB:AB;Sheet1!AC:AC,Sheet1!AD:AD,Sheet1!AE:AE;Sheet1!AF:AF,Sheet1!AG:AG,Sheet1!AH:AH;Sheet1!AI:AI,Sheet1!AJ:AJ,Sheet1!AK:AK;Sheet1!AL:AL,Sheet1!AM:AM,Sheet1!AN:AN;Sheet1!AO:AO,Sheet1!AP:AP,Sheet1!AQ:AQ},LEN(Sheet1!A:A,Sheet1!A:A,Sheet1!A:A))
但这只是基于我在这里看到的另一个答案,我确信我没有正确地应用filter(range,len())部分。
发布于 2020-07-04 07:56:00
您有一个包含45个逗号分隔值的单元格。您的目标是拆分这些值,为行号添加一个后缀,然后在三个行中列出值--也就是说,在45个单元格中(3列宽,15行深)。
使用这个公式:
=ArrayFormula(TRIM(split(transpose(SPLIT(Query(transpose(split(REGEXREPLACE($C$2&",",",",""&$B$2&","),", ",1,1))&","&if(MOD(row(indirect((address($B$7,$B$6)&":"&address($B$9,$B$6))))-row(indirect(address($B$7-1,$B$6))),$B$3)=0,"|",""),,9^9),"|")),",")))
Example输出

道具:
infoinspired.com:在Google中将一列拆分为多个N列
其他类似的公式:
更新
值已经在45列中;不需要初始拆分。
<#>Revised公式
=arrayformula(trim(split(transpose(split(query(transpose(indirect(address($A$6,$A$8)&":"&address($A$6,$A$10-1)))&A6&","&if(MOD(row(indirect((address($A$22,$A$20)&":"&address($A$24,$A$20))))-row(indirect(address($A$22-1,$A$20))),$A$2)=0,"|",""),,9^9),"|")),",")))

https://webapps.stackexchange.com/questions/143616
复制相似问题