将公式转换为ArrayFormula [英] Turning formula into ArrayFormula
本文介绍了将公式转换为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屋!
查看全文