 这些公式转自彩缘论坛的同意高手收藏的彩票公式，希望对大家有用！笑脸对复制说是没影响！嘿嘿！ excel彩票公式技巧,慢慢积A B 不连续单元格统计 =SUM(COUNTIF(INDIRECT({"a1","c1","f1","g1"}),3)) 复制时用=SUM(COUNTIF(OFFSET($A1,,{0,2,5,6}),12))｝ 不为0的最小数LARGE(D12:F12,COUNTIF(D12:F12,">0")) C 查找数字并引用 =INDEX($A$4A$1477,SMALL(IF(--$E$3E$1477=$N$2,ROW($A$4A$1477),""),ROW()-4)-3) 引用区域 条件 D 多条件统计 =sum(if(c2:c10="男"，if(g2:g10="是",b2:b10)))数组公式 多条件求和 =SUM((条件1)*(条件2)*IF(ISNUMBER(D110),D110,)) E F G H I J 基本参数 百=INT(B3/100) 十=INT(MOD(B3,100)/10) 个=MOD(B3,10) 大小=if(c3>4,"大","小") 单双=IF(MOD(c3,2),"单","双") 合质=if(or(c3=1,c3=2,c3=3,c3=5,c3=7),"质","合") 复隔中=if(or(c4=c3,c4=d3,c4=e3),"复",if(or(c4=c2,c4=d2,c4=e2),"隔","中")) 邻孤传=if(or(c4=c3,c4=d3,c4=e3),"邻",if(or(abs(c4-c3)=1,abs(c4-d3)=1,abs(c4-e3)=1),"传","孤")) 跨度=max(c3:e3)-min(c3:e3) 类型=if(and(c3=d3,c3=e3,d3=e3),"豹子",if(and(c3<>d3,c3<>e3,d3<>e3),"组六","组三")) K L 连续出现次数最多的字符 {=INDEX(A:A,MATCH(MAX(FREQUENCY(ROW(1:19),IF(A1:A18<>A2:A19,ROW(1:18),))),FREQUENCY(ROW(1:19),IF(A1:A18<>A2:A19,ROW(1:18),)),))} =INDEX(A:A,MATCH(MAX(FREQUENCY(ROW(1:19),(A1:A18<>A2:A19)*ROW(1:18))),FREQUENCY(ROW(1:19),(A1:A18<>A$2:A$19)*ROW(1:18)),)) M N O P Q 清除重复数字 一=IF(ISERROR(FIND(0,A1)),"",0)&IF(ISERROR(FIND(1,A1)),"",1)&IF(ISERROR(FIND(2,A1)),"",2)&IF(ISERROR(FIND(3,A1)),"",3)&IF(ISERROR(FIND(4,A1)),"",4)&IF(ISERROR(FIND(5,A1)),"",5)&IF(ISERROR(FIND(6,A1)),"",6)&IF(ISERROR(FIND(7,A1)),"",7)&IF(ISERROR(FIND(8,A1)),"",8)&IF(ISERROR(FIND(9,A1)),"",9) 二=(IF(COUNT(FIND(0,A1)),0,)&SUBSTITUTE(SUM(IF(ISNUMBER(FIND(ROW($19),A1)),ROW($19))*10^(9-ROW($19))),"0",)) 三=(IF(COUNT(FIND(0,A1)),0,))&SUBSTITUTE(SUM((MID(A1,FIND(ROW($19),A1&5^19),1)&0)*10^(9-ROW($19))),0,) 四=(IF(COUNT(FIND(0,A1)),0,)&SUBSTITUTE(SUM((1-ISERR(FIND(ROW($19),A1)))*ROW($19)*10^(9-ROW($19))),0,)) 五(不变顺序)=RIGHT(TEXT(SUM(MID(A1,SMALL(--TEXT(FIND(ROW($110)-1,A1&"0123456789"),"[<="&LEN(A1)&"]#;1"),ROW($110)),1)*10^(10-ROW($110))),REPT("0",10)),COUNT(FIND(ROW($110)-1,A1))) 说明:5^19=19073486328125 其中包含了0～9这10个数字。find(row(1:9),A1&5^19),如果A1中不包含某个数字，find的结果也不会返回错误值，而是返回一个大于len(A1)的数字。 去重复内容按列输出 =IF(ROW(1:1)>SUM(1/COUNTIF($A$1A$18,$A$1A$18)),"",INDEX($A$1A$18,SMALL(IF(MATCH($A$1A$18,$A$1A$18,0)=ROW(INDIRECT("1:"&ROWS($A$1A$18))),MATCH($A$1A$18,$A$1A$18,)),ROW(1:1)))) =IF(LEN(SMALL(IF(IF(MATCH(C5:K5&"",C5:K5&"",)<=COUNT(C5:K5),MATCH(C5:K5&"",C5:K5&"",))=COLUMN(A5:I5),C5:K5,4^8),COLUMN(A5:I5)))=5,"",SMALL(IF(IF(MATCH(C5:K5&"",C5:K5&"",)<=COUNT(C5:K5),MATCH(C5:K5&"",C5:K5&"",))=COLUMN(A5:I5),C5:K5),COLUMN(A5:I5))) =IF(COLUMN(A1)>COUNT(1/FREQUENCY($C5K5,$C5K5)),"",SMALL(IF(IF(MATCH($C5K5&"",$C5K5&"",)<=COUNT($C5K5),MATCH($C5K5&"",$C5K5&"",))=COLUMN($A5I5),$C5K5),COLUMN(A5))) R S 删除 columns("a:z").delete 或Range("e3:e65536").ClearContents T 统计不连结单元的含某数的个数 SUM(COUNTIF(OFFSET($D11,{0,1,2,3,4},{0,1,2,3,4}),2)) 统计两组数的相同数个数 =SUM(IF(COUNTIF(A2:F2,H2:T2)>0,1/COUNTIF(H2:T2,H2:T2))) 或=SUM(--(COUNTIF(H2:T2,A2:F2)>0)) U V W X Y 有效数据行数[c65536].End(xlUp).Row 遗漏显示 =IF(a1="","",IF(COUNTIF(b1:d1,目标值)=0,SUM(e1,1),"●")) 余码 =SUBSTITUTE(SUBSTITUTE(SUBSTITUTE("0123456789",MID(A1,1,1),),MID(A1,2,1),),MID(A1,3,1),) Z 最大连出=MAX(FREQUENCY(IF(个数,ROW(个数)),IF(个数=0,ROW(个数)))) 最大连空=MAX(FREQUENCY(IF(个数=0,ROW(个数)),IF(个数,ROW(个数)))) =MAX(FREQUENCY(IF($F$4F$103="●",ROW($F$4F$103)),IF($F$4F$103<>"●",ROW($F$4F$103)))) 最大连出=MAX(FREQUENCY(IF((N$3:N$20=N$2)*($H$3H$20=N$2),ROW(N$3:N$20)),IF(N$3:N$20<>N$2,ROW(N$3:N$20)))) 数组公式 |
|
|
