本帖最后由 edwin11891 于 2025-1-23 15:23 编辑
要全部一次性计算,公式会比较长,四行公式其实基本一样,只修改了一下范围值,然后复制4行,向下粘贴即可,懒得编制循环公式了。
第一行:
- =LET(src,等级!B$2:I$24,GongL,CHOOSECOLS(src,5),GangL,CHOOSECOLS(src,8),GangW,TAKE(src,,1),Gang,XLOOKUP(1=1,A$33:A33<>"",A$33:A33,,,-1),s,TOROW(FREQUENCY(FILTER(GongL,(SUBSTITUTE(GangW,"专业技术","")=Gang)*(GangL<=5)),{5,10,15,20,25,30,35,40})),IFERROR(IF(s=0,"",s),""))
复制代码 第二行:
- =LET(src,等级!B$2:I$24,GongL,CHOOSECOLS(src,5),GangL,CHOOSECOLS(src,8),GangW,TAKE(src,,1),Gang,XLOOKUP(1=1,A$33:A34<>"",A$33:A34,,,-1),s,TOROW(FREQUENCY(FILTER(GongL,(SUBSTITUTE(GangW,"专业技术","")=Gang)*(GangL<=10)*(GangL>5)),{5,10,15,20,25,30,35,40})),IFERROR(IF(s=0,"",s),""))
复制代码 第三行:
- =LET(src,等级!B$2:I$24,GongL,CHOOSECOLS(src,5),GangL,CHOOSECOLS(src,8),GangW,TAKE(src,,1),Gang,XLOOKUP(1=1,A$33:A35<>"",A$33:A35,,,-1),s,TOROW(FREQUENCY(FILTER(GongL,(SUBSTITUTE(GangW,"专业技术","")=Gang)*(GangL<=15)*(GangL>10)),{5,10,15,20,25,30,35,40})),IFERROR(IF(s=0,"",s),""))
复制代码 第四行:
- =LET(src,等级!B$2:I$24,GongL,CHOOSECOLS(src,5),GangL,CHOOSECOLS(src,8),GangW,TAKE(src,,1),Gang,XLOOKUP(1=1,A$33:A36<>"",A$33:A36,,,-1),s,TOROW(FREQUENCY(FILTER(GongL,(SUBSTITUTE(GangW,"专业技术","")=Gang)*(GangL>15)),{5,10,15,20,25,30,35,40})),IFERROR(IF(s=0,"",s),""))
复制代码
|