楼主
lrlxxqxa |
Q：如何将彩票中的俩码转化为三码？
A：
一、单码转换：
方法1数组公式- =IF(ISERR(FIND("0",E$26&(ROW(A1)-1))),"","0")&SUM(SMALL(--MID(E$26&(ROW(A1)-1),ROW($1:$3),1),ROW($1:$3))*{100;10;1})
方法2- =TEXT(-SUM(LARGE(-MID(E$26&ROW(A1)-1,{1,2,3},1),{1,2,3})*{100,10,1}),"000")
二、多码转换：
方法1：数组- =IFERROR(TEXT(SUM(SMALL(--MID(MOD(ROW(A1)-1,10)&MID(A$1,INT((ROW(A1)-1)/10)*3+1,2),{1,2,3},1),{1,2,3})*10^{2,1,0}),"000"),"")
方法2：区域数组- =IFERROR(TEXT(SUM(SMALL(--MID(MOD(ROW()-1,10)&MID(A$1,ROUNDUP(ROW()/10,)*3-2,2),{1,2,3},1),{1,2,3})*10^{2,1,0}),"000"),"")
方法3：VBA法
- Sub 转置()
- Const str1$ = "23 04 55" '二码设置
- Dim str2$, dic, i%, k%, arr, arrt(), m%, j&, n%, str3$
- Set dic = CreateObject("scripting.dictionary")
- str2 = Cells(4, 2).Value '三码设置
- arr = Split(str1)
- For i = 0 To 9
- If InStr(1, str2, i) > 0 Then
- k = k + 1
- ReDim Preserve arrt(1 To k)
- arrt(k) = i
- End If
- Next i
- For i = 0 To UBound(arr)
- m = CInt(Mid(arr(i), 1, 1))
- n = CInt(Mid(arr(i), 2, 1))
- For j = 1 To k
- If arrt(j) <= Application.Min(m, n) Then
- str3 = "'" & arrt(j) & IIf(m >= n, n & m, m & n)
- ElseIf arrt(j) >= Application.Max(m, n) Then
- str3 = "'" & IIf(m >= n, n & m, m & n) & arrt(j)
- Else: str3 = "'" & IIf(m >= n, n & arrt(j) & m, m & arrt(j) & n)
- End If
- If Not dic.exists(str3) Then dic.Add str3, ""
- Next j
- Next i
- Range("e4:e" & Rows.Count).ClearContents
- If dic.Count > 0 Then
- Cells(4, "e").Resize(dic.Count, 1) = Application.Transpose(dic.keys)
- End If
- Set dic = Nothing
- End Sub
 两码变三码.rar
|
方法一二三的结果明显不一样嘛。提问者题目绕，咱不能被绕晕啊，几种方法改成指向同一种结果比较好吧？ 