随心所欲玩转数字问题

楼主 Zaezhong	提取单元格中的数字肯定大家都遇到过，同时各式各样的变式也很多，接下去我将汇总在提取单元格中数字问题中的常见解法，并适当地配合简要的说明，感谢各位的捧场~~用到的附件如下，如果是下载2003版本附件，第6题的第二个公式会出现嵌套过多的情况，其他正常玩转数字.rar
2楼 Zaezhong	=IF(ISERR(FIND(0,A2)),0,"")&SUBSTITUTE(SUM(IF(ISERR(FIND(ROW($1:$9),A2)=ROW($1:$9)),ROW($1:$9),)(10^(9-ROW($1:$9)))),0,) =IF(ISNUMBER(FIND(0,A2)),"",0)&SUBSTITUTE(SUM(IF(ISERR(FIND(ROW($1:$9),A2)),ROW($1:$9))10^(9-ROW($1:$9))),0,) =RIGHT(SUM(--MID(A2&1/17,SMALL(TEXT(FIND(ROW($1:$10)-1,A2&1/17),"[>="&LEN(A2)&"]0;1")/1,ROW($1:$10)),1)10^(10-ROW($1:$10))),10-COUNT(FIND(ROW($1:$10)-1,A2))) =IF(ISERR(FIND(0,A2)),0,"")&SUBSTITUTE(SUM(IF(ISERR(FIND(ROW($1:$9),A2)),ROW($1:$9))10^(10-ROW($1:$9))),0,) =RIGHT(0&SUBSTITUTE(SUM(IF(ISERR(FIND(ROW($1:$10)-1,A2)),ROW($1:$10)-1)*10^(10-ROW($1:$10))),0,),10-COUNT(FIND(ROW($1:$10)-1,A2))) =IF(ISERR(FIND(0,A2)),0,"")&SUBSTITUTE(MID(SUM(IF(ISERR(FIND(ROW($1:$9),A2)),ROW($1:$9))/10^ROW($1:$9)),3,10),0,) 公式1的核心部分是FIND(ROW($1:$9),A2)=ROW($1:$9)，其实这部分多了一点累赘的东西，等号后面部分是多于的，find的结果如果是错误值，那么使用等号判断后结果也是错误值，另外的结果true和False并不改变效果，总体来说公式1、公式2、公式4大同小异，前面的if函数用于判断是否需要显示0值，这样可以方便后面的公式书写。将没有出现过的数字列出，通过后面的10^(9-row($1:$9))依次乘以一个权数，后面就简单了，公式的结构是substitute+sum+if+信息函数+find+row 公式3和公式5相对复杂一点，公式3中FIND(ROW($1:$10)-1,A2&1/17)部分用于查找0~9这10个数字出现的顺序，其中的1/17包含0~9这10个数，主要用途是容错，配合Text将find结果大于a2字符长度的全部转换为1，其余保持不变，再用small函数依次按照数字在单元格中出现的顺序，后面的sum部分与上面相似，最后配合right和count由后往前取字符，公式3的效果与其他结果不同，按照字符出现的顺序排列公式6与前面的类似，if部分与前面相同，不同之处是用了除法，这样得出的结果是小时，使用mid函数从第三个字符开始取10个数字，将可能存在的0替换掉即可
3楼 Zaezhong	=TEXT(SMALL(IF(COUNTIF($A11:$E11,ROW($1:$10)-1)>1,ROW($1:$10)-1,10),COLUMN(A1)),"[>9]\ ") =TEXT(SMALL(IF(COUNTIF($A11:$E11,ROW($1:$10)-1)>1,ROW($1:$10)-1,10),COLUMN(A1)),"[<10]0;;") 这个比较简单，不同之处在Text函数部分，前者的效果中其实有一个空格，前面用countif函数统计0~9这10个数是否出现过
4楼 Zaezhong	=MID(SUM((0&MID(A22,SMALL(FIND(ROW($1:$10)-1,A22&5^19),ROW($1:$10)),1))/10^ROW($1:$10))&"00",3,COUNT(FIND(ROW($1:$10)-1,A22))) =RIGHT(TEXT(SUM(MID(A22,SMALL(--TEXT(FIND(ROW($1:$10)-1,A22&1/17),"[<="&LEN(A22)&"]0;1"),ROW($1:$10)),1)10^(10-ROW($1:$10))),REPT(0,10)),COUNT(FIND(ROW($1:$10)-1,A22))) =RIGHT(0&SUM(MID(A22,SMALL(--TEXT(FIND(ROW($1:$10)-1,A22&1/17),"[<="&LEN(A22)&"]0;1"),ROW($1:$10)),1)10^(10-ROW($1:$10))),(LEFT(A22)="0")+COUNT(FIND(ROW($1:$10)-1,A22))) =MID(SUM(MID(A22&1/7,SMALL(FIND(ROW($1:$10)-1,A22&1/17),ROW($1:$10)),1)/10^ROW($1:$10))&0,3,COUNT(FIND(ROW($1:$10)-1,A22))) =MID(SUM(MID(A22&56^7,SMALL(FIND(ROW($1:$10)-1,A22&56^7),ROW($1:$10)),1)/10^ROW($1:$10))&0,3,COUNT(FIND(ROW($1:$10)-1,A22))) 公式1中的FIND(ROW($1:$10)-1,A22&5^19)与前面的意义类似，其中的5^19跟1/17作用一样，后面部分跟楼上一样公式2与公式1不同之处是前面0的处理方法，用Text后，不管前面是否添加0，都将产生一些0，后面的count是计算需要从右往左取字符数的个数公式3与公式1的不同是right的第二参数不同，(LEFT(A22)="0")+COUNT(FIND(ROW($1:$10)-1,A22)))，红色部分用于判断第一个是否是0，是0的情况下就多取一个余下公式请参考楼上各部分结合
5楼 Zaezhong	=IF(COUNT(FIND(0,A32)),0,)&SUM(LARGE((FIND(ROW($1:$9),A32&5^19)<=LEN(A32))ROW($1:$9),ROW($1:$9))10^ROW($2:$10))% =IF(COUNT(FIND(0,A32)),0,)&SUM(LARGE(ISNUMBER(FIND(ROW($1:$9),A32))ROW($1:$9),ROW($1:$9))10^ROW($2:$10))% =IF(ISERR(FIND(0,A32)),,0)&SUBSTITUTE(SUM(ISNUMBER(FIND(ROW($1:$9),A32))ROW($1:$9)10^(9-ROW($1:$9))),0,)
6楼 Zaezhong	=RIGHT(0&SUBSTITUTE(SUM((MMULT(1-ISERR(FIND(ROW($1:$10)-1,A42:B42)),ROW($1:$2)^0)=2)(ROW($1:$10)-1)10^(10-ROW($1:$10))),0,),COUNT(FIND(ROW($1:$10)-1,A42)FIND(ROW($1:$10)-1,B42))) =IF(COUNT(FIND(0,A42:B42))>1,0,)&SUBSTITUTE(SUM(ISNUMBER(FIND(ROW($1:$9),A42)FIND(ROW($1:$9),B42))ROW($1:$9)10^(9-ROW($1:$9))),0,) =IF(COUNT(FIND(0,A42:B42))>1,0,"")&SUBSTITUTE(SUM(IF(MMULT(--ISNUMBER(-FIND(ROW($1:$9),A42:B42)),{1;1})=2,ROW($1:$9),0)*10^(9-ROW($1:$9))),0,)
7楼 Zaezhong	排序且去重 =RIGHT(0&SUBSTITUTE(SUM(((1-ISERR(FIND(ROW($1:$10)-1,A52)))(ISERR(FIND(ROW($1:$10)-1,B52)))=1)(ROW($1:$10)-1)10^(10-ROW($1:$10))),0,),SUM(N(((1-ISERR(FIND(ROW($1:$10)-1,A52)))(ISERR(FIND(ROW($1:$10)-1,B52)))=1)))) 非排序不去重 =RIGHT(0&SUBSTITUTE(SUM(MID(0&A52,LARGE(1+ISERR(FIND(MID(A52,ROW($1:$10),1),B52))ROW($1:$10),ROW($1:$10)),1)10^ROW($2:$11))%,0,),SUM(ISERR(FIND(MID(A52,ROW($1:$10),1),B52))/1))
8楼 Zaezhong	=TEXT(SMALL(IF(FREQUENCY(MOD($A62:$C62+{1;-1},10),ROW($1:$10)-2),ROW($1:$11)-1,99),COLUMN(A1))-1,"[<10]0;") 原数据加减1得到相邻的数据，用mod是为了将10转换为0，Frequency的第二参数从-1开始是因为0存在的时减1为-1，而最大为8统计的时候会自动产生大于8（其实就是9）的个数。后面用row($1:$11)-1一个11个数是因为要与前面的个数对应（前面if的第一参数结果有11个数），如果最后是10，那么这个是因为容错过程产生的，需要用Text将这部分屏蔽
9楼 Zaezhong	=RIGHT(0&SUM(SMALL(IF(FREQUENCY(MID(A72,ROW(INDIRECT("1:"&LEN(A72))),1)/1,MID(A72,ROW(INDIRECT("1:"&LEN(A72))),1)/1)>1,MID(A72,ROW(INDIRECT("1:"&LEN(A72))),1)/1,),ROW(INDIRECT("1:"&LEN(A72)))+1)10^(LEN(A72)-ROW(INDIRECT("1:"&LEN(A72))))),COUNT(FIND(ROW($1:$10)-1,A72)FIND(ROW($1:$10)-1,A72,FIND(ROW($1:$10)-1,A72)+1))) mid部分将数字一个个取出，除1的目的是转换文本数字为数值，用Frequency函数判断大于1（即重复的数字）那么返回对应的数字，否则为0，除了最小的一个（以a72为例，虽然有多个0，也将其中一个0去掉），后面是配权，权数跟a列的字符长度有关系，基本原理跟前面中的类似，count部分有两个find组成，第二个find的第三参数不省略，在前面一个的基础上再次查找，开始的位置是前一次找到的后面一个位置，如果是错误（表示前面一次没找到）那么加上1以后还是错误值，最后通过相乘统计数字的个数就是最后结果的字符长度 =RIGHT(0&SUBSTITUTE(SUM(IF((LEN(A72)-LEN(SUBSTITUTE(A72,ROW($1:$10)-1,)))>1,ROW($1:$10)-1)*10^(10-ROW($1:$10))),0,),SUM(N((LEN(A72)-LEN(SUBSTITUTE(A72,ROW($1:$10)-1,)))>1))) 这个公式是用得比较多的，也相对更容易理解
10楼 Zaezhong	=RIGHT(0&SUM(RIGHT(10-(0&MID(A82,ROW($1:$15),1)))10^(LEN(A82)-ROW($1:$15))),LEN(A82)) =RIGHT(0&SUM(MOD(-(0&MID(A82,ROW($1:$15),1)),10)10^(LEN(A82)-ROW($1:$15))),LEN(A82)) =RIGHT(0&(REPT(1,LEN(A82))&0)-A82-SUM((MID(A82,ROW($1:$15),1)="0")*10^(LEN(A82)+1-ROW($1:$15))),LEN(A82))
11楼 stevehai
12楼传递	很好的学习资料
13楼 stevehai
14楼冰心8549	谢谢分享，学习学习
15楼天南地北	总结的是真不错，学习！喜欢这样的帖子！一次就能全方位的掌握！
16楼大地回春	真棒先收藏起来再说，绝对用得着。
17楼大地回春
18楼 Zaezhong	=IF(COUNT(FIND(0,A42:I42))>COLUMNS(A:I)-1,0,)&SUBSTITUTE(SUM((MMULT(1-ISERR(FIND(ROW($1:$9),A42:I42)),ROW(INDIRECT("1:"&COLUMNS(A:I)))0+1)=COLUMNS(A:I))ROW($1:$9)10^(9-ROW($1:$9))),0,) 问题针对6楼，如果有多列的情况，修改第二个公式最简单，需要修改部分已经用颜色标出 =IF(COUNT(FIND(0,A42:I42))>COLUMNS(A:I)-1,0,)&SUBSTITUTE(SUM((MMULT(1-ISERR(FIND(ROW($1:$9),A42:I42)),ROW(INDIRECT("1:"&COLUMNS(A:I)))0+1)=COLUMNS(A:I))ROW($1:$9)10^(9-ROW($1:$9))),0,)
19楼大地回春	Zaezhong 老师，谢谢您的回复！我完全复制公式包括行列都不变的情况下，结果没有显示出来不知错在什么地方了，请审查一下。谢谢了！行重复值提取求助表.rar
20楼 Zaezhong	有两点需要注意： 1、公式计算的是每一个单元格为单位，计算在每一个单元格都有的数字，这一点你的意思可能理解错了 2、公式是数组公式，需要同时按下ctrl+shift+enter结束，注意公式的最外面有一个大括号，这时候的结果是3，而不是1，也不是你给的0346，你再比较下前面6楼的结果跟数据部分的区别，有问题再跟帖
21楼 fengku	神贴，正需要呢。
22楼じ☆潴の︵ゞ
23楼 roman0	牛叉叉啊
24楼滴水穿石	居然今天才看到，果断收藏了！感谢版主分享~
25楼 Zaezhong
26楼猴子	精彩啊、、、、赞
27楼 heavenhouse	看的眼花缭乱**！
28楼 id_lck	先收着
29楼 kyser	先收着有时间慢慢研究
30楼好大一棵树王玉	刚才输错了地方了,抱歉!