楼主
herelazy |
Q：如何提取出出现3次以上的数字？
如图所示：
A：方法1、- =TEXT(LARGE((FREQUENCY(IF(ROW($1:$22)>11,N(OFFSET($A$1,ROW($1:$22)-12,1)),N(OFFSET($A$1,ROW($1:$22)-1,))),IF(ROW($1:$22)>11,N(OFFSET($A$1,ROW($1:$22)-12,1)),N(OFFSET($A$1,ROW($1:$22)-1,))))>2)*IF(ROW($1:$23)>11,N(OFFSET($A$1,ROW($1:$23)-12,1)),N(OFFSET($A$1,ROW($1:$22)-1,))),ROW(A1)),"[=0] ")
方法2、- =INDIRECT(TEXT(LEFT(TEXT(SMALL((FREQUENCY(A$1:B$11,A$1:B$11)<3)*99+ROW($2:$24),ROW())/2,"00!00/2"),4)+1,"r0c00"),)&""
方法3、- =SMALL(IF(FREQUENCY(A$1:B$11,SMALL(A$1:B$11,ROW($1:$22)))>2,SMALL(A$1:B$11,ROW($1:$22))),ROW(A1))
方法4、- =SMALL(IF(INDEX(FREQUENCY($A$1:$B$11,$A$1:$B$11),N(IF(1,(ROW($1:$11)-1)*2+{1,2})))>2,$A$1:$B$11),ROW(A1))
方法5、- =INDIRECT(TEXT(SMALL(IF(FREQUENCY($A$1:$B$11,$A$1:$B$11)>2,SMALL(ROW($1:$11)*10+{1,2},ROW($1:$22)),121),ROW(A1)),"R0C0"),)&""
方法6、- =INDIRECT(TEXT(SMALL((FREQUENCY(A$1:B$11,A$1:B$11)<3)*97+10*INT(ROW($2:$24)/2)+(-1^ROW($1:$23)+3)/2,ROW()),"!R0C0"),)&""
方法7、- =INDIRECT(TEXT(SMALL((FREQUENCY(A$1:B$11,A$1:B$11)<3)*4^8+ROW($2:$24)*5+1-MOD(ROW($2:$24),2)*4,ROW()),"R0C0"),)&""
方法8、- =INDIRECT(LEFT(TEXT(SMALL((FREQUENCY(A$1:B$11,A$1:B$11)<3)*96+INT(ROW($2:$24)*1.5)+1,ROW())/3,"r00c0/3"),5),)&""
 查找2列中个数大于等于3的数.rar
|