|
本帖最后由 ylhn 于 2013-7-7 23:39 编辑
| A | B | C | D | E | F | G | H | I | J | K | L | M | N | 1 | 4 | 4 | 6 | 4 | 3 | 7 | 8 | 4 | 7 | 3 | | | | | 2 | | | | | | | | | | | | | | | 3 | | | | | | | | | | | | | | |
请教各位高手:
如上图,在K1、L1、M1中分别输入什么公式可以分别统计A1:J1区域中出现次数第一、第二和第三的数字?
本人输入的公式分别为:
K1=IF(ISNA(MODE(A1:J1 )),"",MODE(A1:J1 )) (ISNA 是为了屏蔽错误显示)
L1=IF(ISNA(MODE(IF( A1:J1 =MODE(A1:J1 ),"",A1:J1 ))),"",MODE(IF(A1:J1 =MODE(A1:J1 ),"", A1:J1 ))) (数组公式)
M1=IF(ISNA(MODE(IF(KA1:J1 =MODE(A1:J1 ),"",IF(A1:J1 =MODE(IF(A1:J1 =MODE(A1:J1 ,"",A1:J1 )),"",A1:J1 )))),"",MODE(IF(A1:J1 =MODE(A1:J1 ),"",IF(A1:J1 =MODE(IF(A1:J1 =MODE(A1:J1 ),"",A1:J1 )),"",A1:J1 ))))(数组公式)
以上公式中K1好像没有问题,但是L1、M1总是有些不对劲的地方,有时候的统计不准确。
请各位大虾指点!!
=MOD(LARGE(COUNTIF($A1:$J1,$A1:$J1)*(MATCH($A1:$J1,$A1:$J1,)=COLUMN($A:$J))/1%+$A1:$J1,COLUMN(A1)),100)
|
|