045这3个数字我希望能用excel输入A1以后自动在B1算出这049号码出3个号的组合。045,054,405,450,504,540,要有逗号分割开来。就是彩票里面的排列三，我有软件，但是数据量太大，很费时间，希望能用excel节约大概1－2个小时算法。.另外要是一个单元格有10个这种3个数字，又怎么计算？比如在A1单元格中有：045,269,078,046,058,359,236,469,039,789这10个自动在B1中算出他们出3个号的组合045,054,405,450,504,540,269,296,629,692,926,962,078,087,708,780,807,870, 046,064,406,460,604,640,058,085,508,580,805,850,359,395,539,593,935,953,236,263,326,362,623,632,469,496,649,694,946,964,039,093,309,390,903,930,789,798,879,897,978,987,如何计算?请高手指点，谢谢。
用自定义函数get_array试试,最终结果如图:
step1.工具->宏->安全性->低->保存退出(不然用不了自定义函数)
step2.打开该文件,工具->宏->Visual Basic 编辑器
---------------------------------------------------------------------------------
我用的是03版的Excel,如果你用07版,也按照上面说的这样设置一下吧,你应该也可以找到打开07版Excel的Visual Basic 编辑器的方法
---------------------------------------------------------------------------------
step3.在Visual Basic 编辑器里->插入->模块
step4.把以下代码拷进编辑器右边空白处
Function get_array(x)
comma_count = Len(x) - Len(Replace(x, ",", ""))
Dim A(65536)
get_array = ""
temp = x
For i = 1 To comma_count + 1
A(i) = Left(temp, 3)
first_letter = Left(A(i), 1)
mid_letter = Mid(A(i), 2, 1)
last_letter = Right(A(i), 1)
get_array = get_array & IIf(i = 1, "", ",") & first_letter & mid_letter & last_letter & "," & first_letter & last_letter & mid_letter & "," _
& mid_letter & first_letter & last_letter & "," & mid_letter & last_letter & first_letter & "," & last_letter & first_letter & mid_letter & "," _
& last_letter & mid_letter & first_letter
If i <> comma_count + 1 Then temp = Mid(temp, 5, Len(temp))
Next
End Function
如图:
这样就可以定义函数get_array了,调用这个函数时跟其他Excel带的函数一样,如在B2输入get_array(A2)就可以得到所需结果了:)
