excel如何做“排列组合”函数-excel设置组合,excel如何设置组合图

Function ABC(arr As Range, num As Integer)
R = arr.Rows.Count
For C1 = 1 To R
For C2 = C1 + 1 To R
For C3 = C2 + 1 To R
For C4 = 1 To R
For C5 = C4 + 1 To R
For C6 = C5 + 1 To R
rr = rr + 1
ABC = arr(C1, 1) & "," & arr(C2, 1) & "," & arr(C3, 1) & "," & arr(C4, 2) & "," & arr(C5, 2) & "," & arr(C6, 2)
If rr = num Then Exit Function
Next
Next
Next
Next
Next
Next
End Function

用VBA解决，ALT+F11打开VBA编辑器，插入模块，将以下代码粘贴，按F5运行，C列为“一：”的7a64e78988e69d83334结果，D列为“二：”的结果。

Sub sort()

Dim i, j, k1, k2, k3, k4, m As Integer

Sheet1.Activate

Range("C:C").Clear

i = Range("a65535").End(xlUp).Row

j = Range("b65535").End(xlUp).Row

m = 1

For k1 = 1 To i

  For k2 = 1 To j

    For k3 = k2 + 1 To j

       Cells(m, 3) = Cells(k1, 1) & Cells(k2, 2) & Cells(k3, 2)

       m = m + 1

    Next

  Next

Next

Range("D:D").Clear

m = 1

For k1 = 1 To i

  For k2 = 1 To j

    For k3 = k2 + 1 To j

        For k4 = k3 + 1 To j

          Cells(m, 4) = Cells(k1, 1) & Cells(k2, 2) & Cells(k3, 2) & Cells(k4, 2)

          m = m + 1

        Next

    Next

  Next

Next

End Sub

将这几个字段分zd别编在A1-A5，B1=OFFSET($A$1,INT((ROW(1:1)-1)/5),) C1=OFFSET($A$1,MOD(ROW(1:1)-1,5),) 然后回将答B1，C1连起来即可

本文标签：excel设置组合(1)excel如何设置组合图(1)