请教统计问题

yvll

请教统计问题谢谢！

工作簿1.rar (7.93 KB, 下载次数: 20)

2024-5-13 20:31 上传

点击文件名下载附件

良良

G6=SUM(MMULT(COLUMN($A:$E)^0,N($G2:$K2=TRANSPOSE($G4:$K4)))*MMULT(COLUMN($A:$D)^0,N($G2:$K2=TRANSPOSE($A2:$D2))))，三键。
G7=IFERROR(INDEX(2:2,SMALL(IF(MMULT(COLUMN($A:$E)^0,N($G2:$K2=TRANSPOSE($G4:$K4)))*MMULT(COLUMN($A:$D)^0,N($G2:$K2=TRANSPOSE($A2:$D2))),COLUMN($G:$K)),COLUMN(A1))),"")，三键，右拉。

miaojohn45

=COUNT(MATCH(A2:D2,G2:K2,)*MATCH(A2:D2,G4:K4,))

=FILTER(A2:D2,ISNUMBER(MATCH(A2:D2,G2:K2,)*MATCH(A2:D2,G4:K4,)))

mykitami

=SUM(N(COUNTIF(G2:K2,G4:K4)>0))
=TOROW(IF(COUNTIF(G2:K2,G4:K4)>0,G4:K4,x),2)

良良

或
G6=SUM(COUNTIF($G4:$K4,$G2:$K2)*COUNTIF($A2:$D2,$G2:$K2))
G7=IFERROR(INDEX(2:2,SMALL(IF(COUNTIF($G4:$K4,$G2:$K2)*COUNTIF($A2:$D2,$G2:$K2),COLUMN($G:$K)),COLUMN(A1))),"")

世界客都

个数：
=SUM(N(MMULT({1,1},COUNTIF(OFFSET(G2:K2,{0;2},),A2:D2))=2))
数值：
选中G7:J7单元格，输入，按ctrl+shift+enter即可
=INDEX(2:2,SMALL(IF(MMULT({1,1},COUNTIF(OFFSET(G2:K2,{0;2},),A2:D2))=2,{1,2,3,4},99),{1,2,3,4}))&""