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

本文介绍了将公式转换为ArrayFormula的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

我有这个公式来计算J4Contents!$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屋!

查看全文
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆