0

## EXCEL VBA 分組排入各組求解

52學員分7組，多餘學員排至最後一組，原始如A表，001001為第一組第一位，其組員則為001002到001007依此類推，A表組別空格希望能自動填入且不重複能生成至B表，C表只是提供參考每組的分配，主要能產出至B表

### 2 個回答

1

iT邦大神 1 級 ‧ 2020-03-29 14:26:30

``````Dim arrCode(52)
Sub Main()
startRow = 3
startCol = 3
'--------------------------------
nIdx = 0
For nI = 1 To 7
If nI = 7 Then nLoop = 10 Else nLoop = 7
For nJ = 1 To nLoop
nIdx = nIdx + 1
arrCode(nIdx) = Format(nI, "00#") & Format(nJ, "00#")
Next nJ
Next nI
'--------------------------------
For nI = startRow To startRow + 52 - 1
scode = Cells(nI, startCol)
If (scode <> "") Then
Call useCode(scode)
End If
Next nI
'--------------------------------
For nI = startRow To startRow + 52 - 1
scode = Cells(nI, startCol)
If (scode = "") Then
Cells(nI, startCol) = getCode
End If
Next nI
End Sub
Function useCode(ByVal code As String)
For nI = 1 To 52
If (arrCode(nI) = code) Then
arrCode(nI) = ""
Exit For
End If
Next nI
End Function
Function getCode() As String
For nI = 1 To 52
If (arrCode(nI) <> "") Then
getCode = "'" & arrCode(nI)
arrCode(nI) = ""
Exit For
End If
Next nI
End Function
``````
peterzxcv iT邦新手 5 級 ‧ 2020-03-29 21:02:05 檢舉

peterzxcv iT邦新手 5 級 ‧ 2020-03-31 10:57:13 檢舉

0
ccenjor
iT邦研究生 5 級 ‧ 2020-03-28 22:05:50

F2:=A2、F3:=A3，依此類推。
G2:=B2、G3:=B3，依此類推。
H2:
==IF(C2<>"",C2,IF(D2="","",D2))

J2:=A2、J3:=A3，依此類推。
K2:=B2、K3:=B3，依此類推。
L2:
=IF(H2<>"",H2,SUMPRODUCT(SMALL((\$S\$2:\$S\$53=0)*(\$R\$2:\$R\$53),SUMPRODUCT((H\$2:H2="")*1)+COUNT(\$H\$2:\$H\$53))))

N2:=VLOOKUP(R2,IF({1,0},\$L\$2:\$L\$53,\$J\$2:\$J\$53),2,0)

O2:=VLOOKUP(R2,IF({1,0},\$L\$2:\$L\$53,\$K\$2:\$K\$53),2,0)

P2:=R2、P3:=R3，依此類推。
R2:R53輸入所有的項目(1001~7010)
S2:S53統計每個項目的個數。
S2:=COUNTIF(\$H\$2:\$H\$53,R2)

peterzxcv iT邦新手 5 級 ‧ 2020-03-29 00:32:26 檢舉