将公式转换为 ArrayFormula [英] Turning formula into ArrayFormula

问题描述

我在 J4 的 Contents!$B$2 中有这个计算匹配项的公式.

I have this formula for counting matches in Contents!$B$2 in J4.

=ARRAYFORMULA(IF(NOT(REGEXMATCH(J4, 
"\w")),0,SUM(COUNTIF(REGEXREPLACE(SPLIT(J4,","),"
[\s]",""),REGEXREPLACE(SPLIT(Contents!$B$2,","),"[\s]","")))))

公式在 K4 中，但理想情况下，我希望它在 J 下工作，将值返回到相关的相邻 K.我试过这个，但没有用.刚刚填写了公式所在的单元格

The formula is in K4 but ideally, I want it to work right down J returning values to the relevant adjacent K. I tried this but it didn't work. Just filled the cell the formula was in

=ARRAYFORMULA(IF(NOT(REGEXMATCH(J4:J, 
"\w")),0,SUM(COUNTIF(REGEXREPLACE(SPLIT(J4:J,","),"
[\s]",""),REGEXREPLACE(SPLIT(Contents!$B$2,","),"[\s]","")))))

我知道我可以填写，但问题是用户插入行.

I know I can just fill down but the issue is users inserting rows.

预先感谢您的任何帮助

推荐答案

=ARRAYFORMULA(IF(LEN(A2:A), IFERROR(VLOOKUP(A2:A, 
 QUERY(TRIM(TRANSPOSE(SPLIT(Contents!B1, ","))), 
 "select Col1,count(Col1) group by Col1", 0), 2, 0), 0), ))

这篇关于将公式转换为 ArrayFormula的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！