Google表thresspose，拆分，重复，保留空白

发布于 2025-01-25 19:20:54 字数 733 浏览 1 评论 0 原文

我陷入困境，在多种功能中迷失了方向。我有凌乱的数据，我已经设法接近我想要的地方，但不能微调它。表格中有一些笔记可以解释我要做的事情。

我已经用它来复制工厂列，但它还复制了以下列（不是理想的，但我可以使用它），

=ArrayFormula(vlookup(transpose(split(query(rept(row(A2:E)&" ",J2:J),,9^9)," ")), {row(A2:E),A2:J},{2,3,4,5,6},0))

我在数字和存储列上尝试了此列：

=ArrayFormula(SUBSTITUTE(QUERY(FLATTEN(TRIM(SPLIT(FILTER(F2:F&";*", F2:F<>""), ";", 0, 1))), "Select Col1 Where Col1 <> ''"), "*",, ))

但是它在错误的位置添加了空间，并扩展到太多行。似乎增加了额外的空白。

这甚至可能吗？

原文

I am in way over my head and am getting lost amongst the multiple functions. I have got messy data that I have managed to get close to where I want it, but can't fine-tune it.
https://docs.google.com/spreadsheets/d/1wXM8DowXjzCt8etP1qYo6SsA-egIlm-UOp8RnfCYv44/edit?usp=sharing
There are notes in the Sheet that explain much of what I'm trying to do.

I have used this to duplicate the Plant column, but it also duplicates the following columns (not ideal but I can live with it)

=ArrayFormula(vlookup(transpose(split(query(rept(row(A2:E)&" ",J2:J),,9^9)," ")), {row(A2:E),A2:J},{2,3,4,5,6},0))

i tried this on the Nums and Storage column:

=ArrayFormula(SUBSTITUTE(QUERY(FLATTEN(TRIM(SPLIT(FILTER(F2:F&";*", F2:F<>""), ";", 0, 1))), "Select Col1 Where Col1 <> ''"), "*",, ))

but it adds spaces in the wrong place and expands to too many rows. Seems to add extra blanks.

Is this even possible?

分享到QQ

分享到微博

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

发布评论

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

南渊 2025-02-01 19:20:54

使用：

=ARRAYFORMULA({A1:I1; QUERY(REGEXREPLACE(""&SPLIT(FLATTEN(A2:A15&"×"&
 IF(IFERROR(SPLIT(G2:G15, ";"), "2♦"&G2:G15)="",, REGEXREPLACE(SEQUENCE(1, 20)&"♦"&IF(B2:B15="", "3♦"&B2:B15, B2:B15), "^1♦", ))&"×"&
 IF(IFERROR(SPLIT(G2:G15, ";"), "2♦"&G2:G15)="",, REGEXREPLACE(SEQUENCE(1, 20)&"♦"&IF(C2:C15="", "3♦"&C2:C15, C2:C15), "^1♦", ))&"×"&
 IF(IFERROR(SPLIT(G2:G15, ";"), "2♦"&G2:G15)="",, REGEXREPLACE(SEQUENCE(1, 20)&"♦"&IF(D2:D15="", "3♦"&D2:D15, D2:D15), "^1♦", ))&"×"&
 IF(IFERROR(SPLIT(G2:G15, ";"), "2♦"&G2:G15)="",, REGEXREPLACE(SEQUENCE(1, 20)&"♦"&IF(E2:E15="", "3♦"&E2:E15, E2:E15), "^1♦", ))&"×"&
 IFERROR(SPLIT(F2:F15, ";"), "4♦"&F2:F15)&"×"&IFERROR(SPLIT(G2:G15, ";"), "5♦"&G2:G15)&"×"&
 IF(IFERROR(SPLIT(G2:G15, ";"), "2♦"&G2:G15)="",, REGEXREPLACE(SEQUENCE(1, 20)&"♦"&IF(H2:H15="", "3♦"&H2:H15, H2:H15), "^1♦", ))&"×"&I2:I15), 
 "×"), ".*♦.*", ), "where Col9 is not null", 0)})

更新：

=ARRAYFORMULA({A1:I1; QUERY(REGEXREPLACE(""&SPLIT(FLATTEN(A2:A9&"×"&
 IF(IFERROR(SPLIT(G2:G9, ";"), "2♦"&G2:G9)="",, REGEXREPLACE(SEQUENCE(1, IFERROR(COLUMNS(SPLIT(G2:G9, ";")), 1))&"♦"&IF(B2:B9="", "3♦"&B2:B9, B2:B9), "^1♦", ))&"×"&
 IF(IFERROR(SPLIT(G2:G9, ";"), "2♦"&G2:G9)="",, REGEXREPLACE(SEQUENCE(1, IFERROR(COLUMNS(SPLIT(G2:G9, ";")), 1))&"♦"&IF(C2:C9="", "3♦"&C2:C9, C2:C9), "^1♦", ))&"×"&
 IF(IFERROR(SPLIT(G2:G9, ";"), "2♦"&G2:G9)="",, REGEXREPLACE(SEQUENCE(1, IFERROR(COLUMNS(SPLIT(G2:G9, ";")), 1))&"♦"&IF(D2:D9="", "3♦"&D2:D9, D2:D9), "^1♦", ))&"×"&
 IF(IFERROR(SPLIT(G2:G9, ";"), "2♦"&G2:G9)="",, REGEXREPLACE(SEQUENCE(1, IFERROR(COLUMNS(SPLIT(G2:G9, ";")), 1))&"♦"&IF(E2:E9="", "3♦"&E2:E9, E2:E9), "^1♦", ))&"×"&
 IFERROR(SPLIT(F2:F9, ";"), "4♦"&F2:F9)&"×"&IFERROR(SPLIT(G2:G9, ";"), "5♦"&G2:G9)&"×"&
 IF(IFERROR(SPLIT(G2:G9, ";"), "2♦"&G2:G9)="",, REGEXREPLACE(SEQUENCE(1, IFERROR(COLUMNS(SPLIT(G2:G9, ";")), 1))&"♦"&IF(H2:H9="", "3♦"&H2:H9, H2:H9), "^1♦", ))&"×"&I2:I9), 
 "×"), ".*♦.*", ), "where Col9 is not null", 0)})

use:

=ARRAYFORMULA({A1:I1; QUERY(REGEXREPLACE(""&SPLIT(FLATTEN(A2:A15&"×"&
 IF(IFERROR(SPLIT(G2:G15, ";"), "2♦"&G2:G15)="",, REGEXREPLACE(SEQUENCE(1, 20)&"♦"&IF(B2:B15="", "3♦"&B2:B15, B2:B15), "^1♦", ))&"×"&
 IF(IFERROR(SPLIT(G2:G15, ";"), "2♦"&G2:G15)="",, REGEXREPLACE(SEQUENCE(1, 20)&"♦"&IF(C2:C15="", "3♦"&C2:C15, C2:C15), "^1♦", ))&"×"&
 IF(IFERROR(SPLIT(G2:G15, ";"), "2♦"&G2:G15)="",, REGEXREPLACE(SEQUENCE(1, 20)&"♦"&IF(D2:D15="", "3♦"&D2:D15, D2:D15), "^1♦", ))&"×"&
 IF(IFERROR(SPLIT(G2:G15, ";"), "2♦"&G2:G15)="",, REGEXREPLACE(SEQUENCE(1, 20)&"♦"&IF(E2:E15="", "3♦"&E2:E15, E2:E15), "^1♦", ))&"×"&
 IFERROR(SPLIT(F2:F15, ";"), "4♦"&F2:F15)&"×"&IFERROR(SPLIT(G2:G15, ";"), "5♦"&G2:G15)&"×"&
 IF(IFERROR(SPLIT(G2:G15, ";"), "2♦"&G2:G15)="",, REGEXREPLACE(SEQUENCE(1, 20)&"♦"&IF(H2:H15="", "3♦"&H2:H15, H2:H15), "^1♦", ))&"×"&I2:I15), 
 "×"), ".*♦.*", ), "where Col9 is not null", 0)})

update:

=ARRAYFORMULA({A1:I1; QUERY(REGEXREPLACE(""&SPLIT(FLATTEN(A2:A9&"×"&
 IF(IFERROR(SPLIT(G2:G9, ";"), "2♦"&G2:G9)="",, REGEXREPLACE(SEQUENCE(1, IFERROR(COLUMNS(SPLIT(G2:G9, ";")), 1))&"♦"&IF(B2:B9="", "3♦"&B2:B9, B2:B9), "^1♦", ))&"×"&
 IF(IFERROR(SPLIT(G2:G9, ";"), "2♦"&G2:G9)="",, REGEXREPLACE(SEQUENCE(1, IFERROR(COLUMNS(SPLIT(G2:G9, ";")), 1))&"♦"&IF(C2:C9="", "3♦"&C2:C9, C2:C9), "^1♦", ))&"×"&
 IF(IFERROR(SPLIT(G2:G9, ";"), "2♦"&G2:G9)="",, REGEXREPLACE(SEQUENCE(1, IFERROR(COLUMNS(SPLIT(G2:G9, ";")), 1))&"♦"&IF(D2:D9="", "3♦"&D2:D9, D2:D9), "^1♦", ))&"×"&
 IF(IFERROR(SPLIT(G2:G9, ";"), "2♦"&G2:G9)="",, REGEXREPLACE(SEQUENCE(1, IFERROR(COLUMNS(SPLIT(G2:G9, ";")), 1))&"♦"&IF(E2:E9="", "3♦"&E2:E9, E2:E9), "^1♦", ))&"×"&
 IFERROR(SPLIT(F2:F9, ";"), "4♦"&F2:F9)&"×"&IFERROR(SPLIT(G2:G9, ";"), "5♦"&G2:G9)&"×"&
 IF(IFERROR(SPLIT(G2:G9, ";"), "2♦"&G2:G9)="",, REGEXREPLACE(SEQUENCE(1, IFERROR(COLUMNS(SPLIT(G2:G9, ";")), 1))&"♦"&IF(H2:H9="", "3♦"&H2:H9, H2:H9), "^1♦", ))&"×"&I2:I9), 
 "×"), ".*♦.*", ), "where Col9 is not null", 0)})