ARRAYFORMULA没有扩展

debugcn 发表于 Dev

Justbriman

我在单元格D2中输入了以下ARRAYFORMULA，似乎应该在整个列中向下扩展（一旦该部分正常工作，我将在公式中添加“检查空白A2”），但是没有任何扩展。我哪里迷路了？

公式如下：

=ArrayFormula(IFERROR(QUERY('Form Responses 1'!$A:$J,"select count(E) where (C contains '"& $A2:A &"' or C contains '"& $B2:B &"') and (E) Contains 'Option' label count(E) ''"),0)+
IFERROR(QUERY('Form Responses 1'!$A:$J,"select count(F) where (C contains '"& $A2:A &"' or C contains '"& $B2:B &"') and (F) Contains 'Option' label count(F) ''"),0)+
IFERROR(QUERY('Form Responses 1'!$A:$J,"select count(G) where (C contains '"& $A2:A &"' or C contains '"& $B2:B &"') and (G) Contains 'Option' label count(G) ''"),0)+
IFERROR(QUERY('Form Responses 1'!$A:$J,"select count(H) where (C contains '"& $A2:A &"' or C contains '"& $B2:B &"') and (H) Contains 'Option' label count(H) ''"),0)+
IFERROR(QUERY('Form Responses 1'!$A:$J,"select count(I) where (C contains '"& $A2:A &"' or C contains '"& $B2:B &"') and (I) Contains 'Option' label count(I) ''"),0))

指向价差副本的链接如下：https : //docs.google.com/spreadsheets/d/14E1QEfcTYiwOG_gORkc8YoRuWEPM7FweLljj0hQCQTc/edit?usp=sharing

玩家0

尝试：

=ARRAYFORMULA(IF(TRIM(B2:B)="",,IFNA(IFNA(VLOOKUP((B2:B), 
 QUERY({IFNA(IFNA(REGEXEXTRACT('Form Responses 1'!C2:C, "\b"&TEXTJOIN("\b|\b", 1, (B2:B))&"\b"), 
 REGEXEXTRACT('Form Responses 1'!C2:C, "\b"&TEXTJOIN("\b|\b", 1, TRIM(A2:A))&"\b"))), 
 MMULT(N(REGEXMATCH('Form Responses 1'!E2:I, "(?i)Option")), {1;1;1;1;1})}, 
 "select Col1,sum(Col2) where Col1 is not null group by Col1 label sum(Col2)''"), 2, 0), VLOOKUP(TRIM(A2:A), 
 QUERY({IFNA(IFNA(REGEXEXTRACT('Form Responses 1'!C2:C, "\b"&TEXTJOIN("\b|\b", 1, (B2:B))&"\b"), 
 REGEXEXTRACT('Form Responses 1'!C2:C, "\b"&TEXTJOIN("\b|\b", 1, TRIM(A2:A))&"\b"))), 
 MMULT(N(REGEXMATCH('Form Responses 1'!E2:I, "(?i)Option")), {1;1;1;1;1})}, 
 "select Col1,sum(Col2) where Col1 is not null group by Col1 label sum(Col2)''"), 2, 0)), 0)))

更新：

=ARRAYFORMULA(IF(C2:C="",,IFNA(IFNA(VLOOKUP(B2:B, QUERY({TRIM(FLATTEN(QUERY(TRANSPOSE(
 REGEXEXTRACT('Form Responses 1'!C2:C, TEXTJOIN("|", 1, "("&SUBSTITUTE(TRIM(UNIQUE(
 FILTER({Sheet3!B2:B; Sheet3!A2:A}, {Sheet3!B2:B; Sheet3!A2:A}<>""))), " ", ").+(")&")"))),,9^9))) , 
 MMULT(N(REGEXMATCH('Form Responses 1'!E2:I, "(?i)Option")), {1;1;1;1;1})}, 
 "select Col1,sum(Col2) where Col1 is not null group by Col1 label sum(Col2)''"), 2, 0), 
 VLOOKUP(A2:A, QUERY({TRIM(FLATTEN(QUERY(TRANSPOSE(
 REGEXEXTRACT('Form Responses 1'!C2:C, TEXTJOIN("|", 1, "("&SUBSTITUTE(TRIM(UNIQUE(
 FILTER({Sheet3!B2:B; Sheet3!A2:A}, {Sheet3!B2:B; Sheet3!A2:A}<>""))), " ", ").+(")&")"))),,9^9))) , 
 MMULT(N(REGEXMATCH('Form Responses 1'!E2:I, "(?i)Option")), {1;1;1;1;1})}, 
 "select Col1,sum(Col2) where Col1 is not null group by Col1 label sum(Col2)''"), 2, 0)), 0)))

本文收集自互联网，转载请注明来源。

如有侵权，请联系[email protected] 删除。