问题有点复杂,请各位高手帮忙解决!!!
举例:
A B C D
1 760215 215 3489 0
2 027572 572 0257 3
3 401956 956 2378 0
4 453908 908 1267 0
5 717262 262 1267 2
在A、B两例已输入数字,希望得出C、D列的结果:
问题1:C列是根据A列来推,如C列中的数字不重复(如A1、A3、A4),那么0-9十个数字在A列中已经出现6个,C列得出未出现的4个数字,如C列中的数字出现重复(如A2、A5),那C列就得出重复的这4个数字。
问题2:D列与C列比较,D列得出其中重复的个数
问题挺烦的,请高手帮忙!!!
我想在C列中得出两个结果,第一种是A列上的数字是没有重复的,如A1:175620,第位位数上的数字没有重复,那根据A列推算C列,C列得出结果是3489,A列+C例正好等于0-9十个数字。第二种是A例上的数字是有重复的,如A3:207572,十万位和个位,万位和十位是重复数字,那根据A列推算C列,C例得出结果是0257。
D列我想得出的结果是,C列与B列比较后,D例得出重复数字的个数。如A1:D例=B列的620与C列的3489比较,没有重复的数字,结果为0。如A3:D例=B列的572与C列的0257比较,有重复的数字3个,结果为3。
已在补充问题中重新提问,请高手再看看,至于666444的问题,可以直接输出成64吗,如可以结果是可以接受的,如不能,提示出错信息,手工输入也可以接受,请高手指教
追答可以,我已做了修改。图片已经上传但还没显示出来,是写了一个自定义的函数
太复杂了,这下我是理解了,但公式好像没法做到哦,vba可以实现,有点复杂,
通过测试,实现了,有点复杂,如下:
1、分列,在c列插入6列,通过分列功能将A的6位数单个显示在插入的6列中,输入公式即可,
J1公式:
=IF(LEN(IF(COUNTIF(C1:H1,0)=1,"0","")&IF(COUNTIF(C1:H1,1)=1,"1","")&IF(COUNTIF(C1:H1,2)=1,"2","")&IF(COUNTIF(C1:H1,3)=1,"3","")&IF(COUNTIF(C1:H1,4)=1,"4","")&IF(COUNTIF(C1:H1,5)=1,"5","")&IF(COUNTIF(C1:H1,6)=1,"6","")&IF(COUNTIF(C1:H1,"7")=1,7,"")&IF(COUNTIF(C1:H1,8)=1,"8","")&IF(COUNTIF(C1:H1,9)=1,"9",""))>4,IF(COUNTIF(C1:H1,0)=1,"0","")&IF(COUNTIF(C1:H1,1)=1,"1","")&IF(COUNTIF(C1:H1,2)=1,"2","")&IF(COUNTIF(C1:H1,3)=1,"3","")&IF(COUNTIF(C1:H1,4)=1,"4","")&IF(COUNTIF(C1:H1,5)=1,"5","")&IF(COUNTIF(C1:H1,6)=1,"6","")&IF(COUNTIF(C1:H1,"7")=1,7,"")&IF(COUNTIF(C1:H1,8)=1,"8","")&IF(COUNTIF(C1:H1,9)=1,"9",""),IF(COUNTIF(C1:H1,0)>=1,"0","")&IF(COUNTIF(C1:H1,1)>=1,"1","")&IF(COUNTIF(C1:H1,2)>=1,"2","")&IF(COUNTIF(C1:H1,3)>=1,"3","")&IF(COUNTIF(C1:H1,4)>=1,"4","")&IF(COUNTIF(C1:H1,5)>=1,"5","")&IF(COUNTIF(C1:H1,6)>=1,"6","")&IF(COUNTIF(C1:H1,"7")>=1,7,"")&IF(COUNTIF(C1:H1,8)>=1,"8","")&IF(COUNTIF(C1:H1,9)>=1,"9",""))
K1公式:=IF(LEN(J1)=4,J1,IF(ISERROR(FIND("0",J1))=TRUE,"0","")&IF(ISERROR(FIND("1",J1))=TRUE,"1","")&IF(ISERROR(FIND("2",J1))=TRUE,"2","")&IF(ISERROR(FIND("3",J1))=TRUE,"3","")&IF(ISERROR(FIND("4",J1))=TRUE,"4","")&IF(ISERROR(FIND("5",J1))=TRUE,"5","")&IF(ISERROR(FIND("6",J1))=TRUE,"6","")&IF(ISERROR(FIND("7",J1))=TRUE,7,"")&IF(ISERROR(FIND("8",J1))=TRUE,"8","")&IF(ISERROR(FIND("9",J1))=TRUE,"9",""))
L1公式:
=IF(ISERROR(FIND(LEFT(B1,1),K1))=TRUE,0,1)+IF(ISERROR(FIND(MID(B1,2,1),K1))=TRUE,0,1)+IF(ISERROR(RIGHT(FIND(B1,1),K1))=TRUE,0,1)
如图:讲辅助列隐藏即可。