列表组合(Microsoft Excel)
罗恩知道他可以使用COMBIN函数来确定可以由多个数字组成的组合的数量。但是,他想知道是否有办法列出所有组合本身。
没有内置的方法来列出Excel中的组合。但是,您可以创建一个宏来为您列出。如果要查找从1开始的一组连续数字中的唯一组合,则可以使用以下一组宏。您所需要做的就是运行TestCNR函数,最后得到一个“矩阵”单元格,该单元格代表从1到10的一系列值中的4位数字组合的数量。
Sub TestCNR() Cnr 10, 4 End Sub
Sub Cnr(n, r) i = 1 For j = 1 To r Cells(i, j).Value = j Next Do Until Finished(n, r, i) j = FindFirstSmall(n, r, i) For k = 1 To j — 1 Cells(i + 1, k).Value = Cells(i, k).Value Next Cells(i + 1, j).Value = Cells(i, j).Value + 1 For k = j + 1 To r Cells(i + 1, k).Value = Cells(i + 1, k - 1).Value + 1 Next i = i + 1 Loop End Sub
Function Finished(n, r, i) Temp = True For j = r To 1 Step -1 If Cells(i, j).Value <> j + (n - r) Then Temp = False End If Next Finished = Temp End Function Function FindFirstSmall(n, r, i) j = r Do Until Cells(i, j).Value <> j + (n - r) j = j - 1 Loop FindFirstSmall = j End Function
该宏将覆盖工作表中的所有内容,因此请确保在显示空白工作表的情况下运行测试。如果要更改集合的大小或子集中元素的数量,只需更改TestCNR例程中传递的值即可。
如果要从字符串(例如字母)中提取唯一组合,则需要使用另一组宏。以下将正常工作;它假定要用作“ Universe”的字符在单元格A1中,而每个唯一组合中要使用的数字在单元格A2中。
Sub FindSets() Dim iA() As Integer Dim sUniv As String Dim iWanted As Integer Dim j As Integer Dim k As Integer sUniv = Cells(1, 1).Value iWanted = Cells(2, 1).Value ReDim iA(iWanted) For j = 1 To iWanted iA(j) = j Next j iRow = PutRow(iA, sUniv, 1) Do Until DoneYet(iA, Len(sUniv)) j = WorkHere(iA, Len(sUniv)) iA(j) = iA(j) + 1 For k = j + 1 To iWanted iA(k) = iA(k - 1) + 1 Next k iRow = PutRow(iA, sUniv, iRow) Loop End Sub
Function DoneYet(iB, n) As Boolean iMax = UBound(iB) Temp = True For j = iMax To 1 Step -1 If iB(j) <> j + (n - iMax) Then Temp = False End If Next DoneYet = Temp End Function
Function WorkHere(iB, n) As Integer iMax = UBound(iB) j = iMax Do Until iB(j) <> j + (n - iMax) j = j - 1 Loop WorkHere = j End Function
Function PutRow(iB, sUniv, i) iMax = UBound(iB) sTemp = "" For j = 1 To iMax sTemp = sTemp & Mid(sUniv, iB(j), 1) Next j Cells(i, 2).Value = sTemp PutRow = i + 1 End Function
运行FindSets宏,然后所需的不同组合将出现在第2列中。但是,运行宏时要小心。组合的数量很快就会变得非常大。例如,如果在单元格A1中放入26个字母(A到Z),在单元格A2中放入值5,则宏将崩溃。为什么?因为存在65,780个可能的五字符组合,并且只能在其中放置65,536行。
注意:
如果您想知道如何使用此页面(或_ExcelTips_网站上的任何其他页面)中描述的宏,我准备了一个特殊页面,其中包含有用的信息。
_ExcelTips_是您进行经济高效的Microsoft Excel培训的来源。
本技巧(6766)适用于Microsoft Excel 97、2000、2002和2003。可以在以下功能区中为Excel的功能区界面(Excel 2007及更高版本)找到本技巧的版本: