如图:
C6公式:
=TEXTJOIN(",",,IF(MMULT(N(TRIM(MID(SUBSTITUTE(TEXTJOIN(",",,IF(COLUMN(B:F)<>4,B2:F4,"")),",",REPT(" ",100)),(ROW(1:12)-1)*100+1,100))=TRANSPOSE(TRIM(MID(SUBSTITUTE(TEXTJOIN(",",,IF(COLUMN(B:F)<>4,B2:F4,"")),",",REPT(" ",100)),(ROW(1:12)-1)*100+1,100)))),ROW(1:12)^0)>1,TRIM(MID(SUBSTITUTE(TEXTJOIN(",",,IF(COLUMN(B:F)<>4,B2:F4,"")),",",REPT(" ",100)),(ROW(1:12)-1)*100+1,100)),""))
数组公式,按组合键CTRL+SHIFT+回车,完成公式。
追问公式结果没看到有2,其实不用回答了,我一但采纳影响大佬采纳率。
我用的是EXCEL,与WPS有点区别,就是TEXTJOIN函数的第二个参数,在WPS中不能用默认空,需要填写。修改公式如下:
=TEXTJOIN(",",1,IF(MMULT(N(TRIM(MID(SUBSTITUTE(TEXTJOIN(",",1,IF(COLUMN(B:F)4,B2:F4,"")),",",REPT(" ",100)),(ROW(1:12)-1)*100+1,100))=TRANSPOSE(TRIM(MID(SUBSTITUTE(TEXTJOIN(",",1,IF(COLUMN(B:F)4,B2:F4,"")),",",REPT(" ",100)),(ROW(1:12)-1)*100+1,100)))),ROW(1:12)^0)>1,TRIM(MID(SUBSTITUTE(TEXTJOIN(",",1,IF(COLUMN(B:F)4,B2:F4,"")),",",REPT(" ",100)),(ROW(1:12)-1)*100+1,100)),))
牛批,厉害,4改成单元格引用,那个工序不计算直接输,直接统计,直接可以打印又不用删除工序数据。目前是问题就是第一次追问中的第二张图,纠结中……
A22,“数据/ 筛选/ 高级筛选/ 将筛选结果复制 到其他位置/ 条件区域/ 空白/ 复制到/D1/ 选择不重复的记 、在E2输入公式“ =COUNTIF($A$
2.
$A$22,D2)” 下复制到E9.结果如黄色区域所示.
这是什么?