将层次代码分配到父项目中（Google表

发布于 2025-02-03 20:06:42 字数 947 浏览 7 评论 0 原文

源单元： CCA，BF 结果单元格： c，cc，cca，b，bf

cca 是与层次结构有关的复杂代码。它的意思是， cca 是 cc 的孩子，它是 c 的孩子。因此，公式应分为所有父母（ c 和 cc ），并保留孩子（ cc a） - 串起的串联列表。当前的最大级别为四个（ abcd ），但将来可能更多。

我不知道如何为此创建一个公式，但是我得到了这个公式： = arrayformula（join（“”，“，”，uniquar（tranpose（transpose（left（split（a2，'a2，''，“，”，true，true）），序列（max（len（a2，'，“，”，true，true））））））））））））

请参见post there> there 。

但是，我需要一个阵列公式，因为它可以应用于整个列中，

它连接到研究数据是 https://raramagnetica.vercel.app/vikus/vismag/

原文

分享到QQ

分享到微博

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

发布评论

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

与风相奔跑 2025-02-10 20:06:42

尝试：

=ARRAYFORMULA(REGEXREPLACE(FLATTEN(TRIM(QUERY(QUERY(QUERY({IFERROR(SPLIT(FLATTEN(
 IF(""=IFERROR(SPLIT(A1:A3, ", ")),,TEXT(ROW(A1:A3), "♥00000"))), "×")), 
 SEQUENCE(COLUMNS(SPLIT(A1:A3, ", "))*MAX(LEN(TRIM(SPLIT(QUERY(A1:A3&",",,9^9), ", "))))), 
 TRIM(FLATTEN(QUERY(IFERROR(REGEXREPLACE(SORT(TRANSPOSE(REGEXREPLACE(SPLIT(FLATTEN(
 IF(""=IFERROR(SPLIT(A1:A3, ", ")),,REPT(SPLIT(A1:A3, ", ")&"×", LEN(SPLIT(A1:A3, ", "))))), "×"), 
 {"(×)", "("&REPT(".", SEQUENCE(1, MAX(LEN(TRIM(SPLIT(QUERY(A1:A3&",",,9^9), ", "))))-1))&")$"}, )&","), 
 SEQUENCE(MAX(LEN(TRIM(SPLIT(QUERY(A1:A3&",",,9^9), ", "))))), 0), "^,$", )),,9^9)))}, 
 "select max(Col3) where Col1 is not null group by Col2 pivot Col1"), "offset 1", 0),,9^9))), ",$", ))

对于非英语床单：

=ARRAYFORMULA(REGEXREPLACE(FLATTEN(TRIM(QUERY(QUERY(QUERY({IFERROR(SPLIT(FLATTEN(
 IF(""=IFERROR(SPLIT(A1:A3; ", "));;TEXT(ROW(A1:A3); "♥00000"))); "×"))\ 
 SEQUENCE(COLUMNS(SPLIT(A1:A3; ", "))*MAX(LEN(TRIM(SPLIT(QUERY(A1:A3&",";;9^9); ", ")))))\ 
 TRIM(FLATTEN(QUERY(IFERROR(REGEXREPLACE(SORT(TRANSPOSE(REGEXREPLACE(SPLIT(FLATTEN(
 IF(""=IFERROR(SPLIT(A1:A3; ", "));;REPT(SPLIT(A1:A3; ", ")&"×"; LEN(SPLIT(A1:A3; ", "))))); "×"); 
 {"(×)"\ "("&REPT("."; SEQUENCE(1; MAX(LEN(TRIM(SPLIT(QUERY(A1:A3&",";;9^9); ", "))))-1))&")$"}; )&","); 
 SEQUENCE(MAX(LEN(TRIM(SPLIT(QUERY(A1:A3&",";;9^9); ", "))))); 0); "^,$"; ));;9^9)))}; 
 "select max(Col3) where Col1 is not null group by Col2 pivot Col1"); "offset 1"; 0);;9^9))); ",$"; ))

update：

=ARRAYFORMULA(REGEXREPLACE(FLATTEN(TRIM(QUERY(QUERY(QUERY({IFERROR(SPLIT(FLATTEN(
 IF(""=IFERROR(SPLIT(A2:A; ", "));;TEXT(ROW(A2:A); "♥00000"))); "×"))\ 
 SEQUENCE(ROWS(IFERROR(SPLIT(FLATTEN(
 IF(""=IFERROR(SPLIT(A2:A; ", "));;TEXT(ROW(A2:A); "♥00000"))); "×"))))\ 
 TRIM(FLATTEN(QUERY(IFERROR(REGEXREPLACE(SORT(TRANSPOSE(REGEXREPLACE(SPLIT(FLATTEN(
 IF(""=IFERROR(SPLIT(A2:A; ", "));;REPT(SPLIT(A2:A; ", ")&"×"; LEN(SPLIT(A2:A; ", "))))); "×"); 
 {"(×)"\ "("&REPT("."; SEQUENCE(1; MAX(LEN(TRIM(SPLIT(QUERY(A2:A&",";;9^9); ", "))))-1))&")$"}; )&","); 
 SEQUENCE(MAX(LEN(TRIM(SPLIT(QUERY(A2:A&",";;9^9); ", "))))); 0); "^,$"; ));;9^9)))}; 
 "select max(Col3) where Col1 is not null group by Col2 pivot Col1"); "offset 1"; 0);;9^9))); ",$"; ))

但是，如果您获得的大数据集更好（更快），将其分成块，例如：

=ARRAYFORMULA({REGEXREPLACE(FLATTEN(TRIM(QUERY(QUERY(QUERY({IFERROR(SPLIT(FLATTEN(
 IF(""=IFERROR(SPLIT(A2:A600; ", "));;TEXT(ROW(A2:A600); "♥00000"))); "×"))\ 
 SEQUENCE(ROWS(IFERROR(SPLIT(FLATTEN(
 IF(""=IFERROR(SPLIT(A2:A600; ", "));;TEXT(ROW(A2:A600); "♥00000"))); "×"))))\ 
 TRIM(FLATTEN(QUERY(IFERROR(REGEXREPLACE(SORT(TRANSPOSE(REGEXREPLACE(SPLIT(FLATTEN(
 IF(""=IFERROR(SPLIT(A2:A600; ", "));;REPT(SPLIT(A2:A600; ", ")&"×"; LEN(SPLIT(A2:A600; ", "))))); "×"); 
 {"(×)"\ "("&REPT("."; SEQUENCE(1; MAX(LEN(TRIM(SPLIT(QUERY(A2:A600&",";;9^9); ", "))))-1))&")$"}; )&","); 
 SEQUENCE(MAX(LEN(TRIM(SPLIT(QUERY(A2:A600&",";;9^9); ", "))))); 0); "^,$"; ));;9^9)))}; 
 "select max(Col3) where Col1 is not null group by Col2 pivot Col1"); "offset 1"; 0);;9^9))); ",$"; ); 
 REGEXREPLACE(FLATTEN(TRIM(QUERY(QUERY(QUERY({IFERROR(SPLIT(FLATTEN(
 IF(""=IFERROR(SPLIT(A601:A; ", "));;TEXT(ROW(A601:A); "♥00000"))); "×"))\ 
 SEQUENCE(ROWS(IFERROR(SPLIT(FLATTEN(
 IF(""=IFERROR(SPLIT(A601:A; ", "));;TEXT(ROW(A601:A); "♥00000"))); "×"))))\ 
 TRIM(FLATTEN(QUERY(IFERROR(REGEXREPLACE(SORT(TRANSPOSE(REGEXREPLACE(SPLIT(FLATTEN(
 IF(""=IFERROR(SPLIT(A601:A; ", "));;REPT(SPLIT(A601:A; ", ")&"×"; LEN(SPLIT(A601:A; ", "))))); "×"); 
 {"(×)"\ "("&REPT("."; SEQUENCE(1; MAX(LEN(TRIM(SPLIT(QUERY(A601:A&",";;9^9); ", "))))-1))&")$"}; )&","); 
 SEQUENCE(MAX(LEN(TRIM(SPLIT(QUERY(A601:A&",";;9^9); ", "))))); 0); "^,$"; ));;9^9)))}; 
 "select max(Col3) where Col1 is not null group by Col2 pivot Col1"); "offset 1"; 0);;9^9))); ",$"; )})

try:

=ARRAYFORMULA(REGEXREPLACE(FLATTEN(TRIM(QUERY(QUERY(QUERY({IFERROR(SPLIT(FLATTEN(
 IF(""=IFERROR(SPLIT(A1:A3, ", ")),,TEXT(ROW(A1:A3), "♥00000"))), "×")), 
 SEQUENCE(COLUMNS(SPLIT(A1:A3, ", "))*MAX(LEN(TRIM(SPLIT(QUERY(A1:A3&",",,9^9), ", "))))), 
 TRIM(FLATTEN(QUERY(IFERROR(REGEXREPLACE(SORT(TRANSPOSE(REGEXREPLACE(SPLIT(FLATTEN(
 IF(""=IFERROR(SPLIT(A1:A3, ", ")),,REPT(SPLIT(A1:A3, ", ")&"×", LEN(SPLIT(A1:A3, ", "))))), "×"), 
 {"(×)", "("&REPT(".", SEQUENCE(1, MAX(LEN(TRIM(SPLIT(QUERY(A1:A3&",",,9^9), ", "))))-1))&")quot;}, )&","), 
 SEQUENCE(MAX(LEN(TRIM(SPLIT(QUERY(A1:A3&",",,9^9), ", "))))), 0), "^,quot;, )),,9^9)))}, 
 "select max(Col3) where Col1 is not null group by Col2 pivot Col1"), "offset 1", 0),,9^9))), ",quot;, ))

for non-english sheets:

=ARRAYFORMULA(REGEXREPLACE(FLATTEN(TRIM(QUERY(QUERY(QUERY({IFERROR(SPLIT(FLATTEN(
 IF(""=IFERROR(SPLIT(A1:A3; ", "));;TEXT(ROW(A1:A3); "♥00000"))); "×"))\ 
 SEQUENCE(COLUMNS(SPLIT(A1:A3; ", "))*MAX(LEN(TRIM(SPLIT(QUERY(A1:A3&",";;9^9); ", ")))))\ 
 TRIM(FLATTEN(QUERY(IFERROR(REGEXREPLACE(SORT(TRANSPOSE(REGEXREPLACE(SPLIT(FLATTEN(
 IF(""=IFERROR(SPLIT(A1:A3; ", "));;REPT(SPLIT(A1:A3; ", ")&"×"; LEN(SPLIT(A1:A3; ", "))))); "×"); 
 {"(×)"\ "("&REPT("."; SEQUENCE(1; MAX(LEN(TRIM(SPLIT(QUERY(A1:A3&",";;9^9); ", "))))-1))&")quot;}; )&","); 
 SEQUENCE(MAX(LEN(TRIM(SPLIT(QUERY(A1:A3&",";;9^9); ", "))))); 0); "^,quot;; ));;9^9)))}; 
 "select max(Col3) where Col1 is not null group by Col2 pivot Col1"); "offset 1"; 0);;9^9))); ",quot;; ))

update:

=ARRAYFORMULA(REGEXREPLACE(FLATTEN(TRIM(QUERY(QUERY(QUERY({IFERROR(SPLIT(FLATTEN(
 IF(""=IFERROR(SPLIT(A2:A; ", "));;TEXT(ROW(A2:A); "♥00000"))); "×"))\ 
 SEQUENCE(ROWS(IFERROR(SPLIT(FLATTEN(
 IF(""=IFERROR(SPLIT(A2:A; ", "));;TEXT(ROW(A2:A); "♥00000"))); "×"))))\ 
 TRIM(FLATTEN(QUERY(IFERROR(REGEXREPLACE(SORT(TRANSPOSE(REGEXREPLACE(SPLIT(FLATTEN(
 IF(""=IFERROR(SPLIT(A2:A; ", "));;REPT(SPLIT(A2:A; ", ")&"×"; LEN(SPLIT(A2:A; ", "))))); "×"); 
 {"(×)"\ "("&REPT("."; SEQUENCE(1; MAX(LEN(TRIM(SPLIT(QUERY(A2:A&",";;9^9); ", "))))-1))&")quot;}; )&","); 
 SEQUENCE(MAX(LEN(TRIM(SPLIT(QUERY(A2:A&",";;9^9); ", "))))); 0); "^,quot;; ));;9^9)))}; 
 "select max(Col3) where Col1 is not null group by Col2 pivot Col1"); "offset 1"; 0);;9^9))); ",quot;; ))

but if you got large dataset its better (faster) to split it into chunks like:

=ARRAYFORMULA({REGEXREPLACE(FLATTEN(TRIM(QUERY(QUERY(QUERY({IFERROR(SPLIT(FLATTEN(
 IF(""=IFERROR(SPLIT(A2:A600; ", "));;TEXT(ROW(A2:A600); "♥00000"))); "×"))\ 
 SEQUENCE(ROWS(IFERROR(SPLIT(FLATTEN(
 IF(""=IFERROR(SPLIT(A2:A600; ", "));;TEXT(ROW(A2:A600); "♥00000"))); "×"))))\ 
 TRIM(FLATTEN(QUERY(IFERROR(REGEXREPLACE(SORT(TRANSPOSE(REGEXREPLACE(SPLIT(FLATTEN(
 IF(""=IFERROR(SPLIT(A2:A600; ", "));;REPT(SPLIT(A2:A600; ", ")&"×"; LEN(SPLIT(A2:A600; ", "))))); "×"); 
 {"(×)"\ "("&REPT("."; SEQUENCE(1; MAX(LEN(TRIM(SPLIT(QUERY(A2:A600&",";;9^9); ", "))))-1))&")quot;}; )&","); 
 SEQUENCE(MAX(LEN(TRIM(SPLIT(QUERY(A2:A600&",";;9^9); ", "))))); 0); "^,quot;; ));;9^9)))}; 
 "select max(Col3) where Col1 is not null group by Col2 pivot Col1"); "offset 1"; 0);;9^9))); ",quot;; ); 
 REGEXREPLACE(FLATTEN(TRIM(QUERY(QUERY(QUERY({IFERROR(SPLIT(FLATTEN(
 IF(""=IFERROR(SPLIT(A601:A; ", "));;TEXT(ROW(A601:A); "♥00000"))); "×"))\ 
 SEQUENCE(ROWS(IFERROR(SPLIT(FLATTEN(
 IF(""=IFERROR(SPLIT(A601:A; ", "));;TEXT(ROW(A601:A); "♥00000"))); "×"))))\ 
 TRIM(FLATTEN(QUERY(IFERROR(REGEXREPLACE(SORT(TRANSPOSE(REGEXREPLACE(SPLIT(FLATTEN(
 IF(""=IFERROR(SPLIT(A601:A; ", "));;REPT(SPLIT(A601:A; ", ")&"×"; LEN(SPLIT(A601:A; ", "))))); "×"); 
 {"(×)"\ "("&REPT("."; SEQUENCE(1; MAX(LEN(TRIM(SPLIT(QUERY(A601:A&",";;9^9); ", "))))-1))&")quot;}; )&","); 
 SEQUENCE(MAX(LEN(TRIM(SPLIT(QUERY(A601:A&",";;9^9); ", "))))); 0); "^,quot;; ));;9^9)))}; 
 "select max(Col3) where Col1 is not null group by Col2 pivot Col1"); "offset 1"; 0);;9^9))); ",quot;; )})