用如下数组公式(用Shift+Ctrl+Enter输入):
=MOD(MATCH(MIN(COUNTIF(A1:E20,MOD(ROW(1:10),10))),COUNTIF(A1:E20,MOD(ROW(1:10),10)),0),10)&MOD(MATCH(SMALL(COUNTIF(A1:E20,MOD(ROW(1:10),10)),2),COUNTIF(A1:E20,MOD(ROW(1:10),10)),0),10)
再问: 为什么,我一下拉公式就算不对了,
再答: 这个公式只需计算一次,没有必要下拉呀!
再问:
像这个一样,每加一行,自动算去固定范围的最少次数二个数
再答: F21=INDEX({0,1,2,3,4,5,6,7,8,9},MATCH(SMALL(COUNTIF($A1:$E20,{0,1,2,3,4,5,6,7,8,9}),1),COUNTIF($A1:$E20,{0,1,2,3,4,5,6,7,8,9}),0))&INDEX({0,1,2,3,4,5,6,7,8,9},MATCH(SMALL(COUNTIF($A1:$E20,{0,1,2,3,4,5,6,7,8,9}),2),COUNTIF($A1:$E20,{0,1,2,3,4,5,6,7,8,9}),0))下拉
你迟迟不作采纳,看来有不满意之处。我也觉得F23=22,不太合理,应该=23,2和3在A3:E22都出现了8次,没有理由不把3放进。看一下我做的附件吧: