Question

EXCEL问题 用什么函数公式找出每组六个数相差为2的所有组合（例如：三连号以上18-20-22-24）？

=SUBSTITUTE(REPT(","&C3,SUMPRODUCT(--(ABS(C3-$C3:$H3)=2))>0)&REPT(","&D3,SUMPRODUCT(--(ABS(D3-$C3:$H3)=2))>0)&REPT(","&E3,SUMPRODUCT(--(ABS(E3-$C3:$H3)=2))>0)&REPT(","&F3,SUMPRODUCT(--(ABS(F3-$C3:$H3)=2))>0)&REPT(","&G3,SUMPRODUCT(--(ABS(G3-$C3:$H3)=2))>0)&REPT(","&H3,SUMPRODUCT(--(ABS(H3-$C3:$H3)=2))>0),",","",1)改成三连号以上三连号以上18-20-22-24，不含二连号例如：18-20。的函数公式。

Answer 1

=SUBSTITUTE(IF((COUNTIF(D3:H3,C3+2)+COUNTIF(D3:H3,C3+4))=2," -"&C3&"-"&C3+2&"-"&C3+4&IF(COUNTIF(F3:H3,C3+6),"-"&C3+6&IF(COUNTIF(F3:H3,C3+8),"-"&C3+8&IF(H3=C3+10,"-"&C3+10,""),""),""),"")&IF(D3=C3+2,"",IF((COUNTIF(E3:H3,D3+2)+COUNTIF(E3:H3,D3+4))=2," -"&D3&"-"&D3+2&"-"&D3+4&IF(COUNTIF(G3:H3,D3+6),"-"&D3+6&IF(H3=D3+8,"-"&D3+8,""),""),""))&IF(COUNTIF(C3:D3,E3-2),"",IF((COUNTIF(F3:H3,E3+2)+COUNTIF(F3:H3,E3+4))=2," -"&E3&"-"&E3+2&"-"&E3+4&IF(COUNTIF(H3:H3,E3+6),"-"&E3+6&IF(H3=E3+8,"-"&E3+8,""),""),""))&IF(COUNTIF(C3:E3,F3-2),"",IF((COUNTIF(G3:H3,F3+2)+COUNTIF(G3:H3,F3+4))=2," -"&F3&"-"&F3+2&"-"&F3+4&IF(COUNTIF(H3:I3,F3+6),"-"&F3+6&IF(H3=F3+8,"-"&F3+8,""),""),""))," -"," ")

Answer 2

为了研究彩票也是拼了，写这么长的公式，截个图看看啊，我研究研究，

追问

EXCEL问题 用什么函数公式找出每组六个数相差为（1-12）的所有组合（例如：三连号以上，二连号不统计）