Listing Combinations (Microsoft Excel)
Ron weiß, dass er die COMBIN-Funktion verwenden kann, um die Anzahl der Kombinationen zu bestimmen, die aus einer Anzahl von Ziffern hergestellt werden können. Er fragt sich jedoch, ob es eine Möglichkeit gibt, alle Kombinationen selbst aufzulisten.
Es gibt keine integrierte Möglichkeit, Kombinationen in Excel aufzulisten. Sie können jedoch ein Makro erstellen, um die Auflistung für Sie durchzuführen. Wenn Sie die eindeutigen Kombinationen in einer Reihe von fortlaufenden Zahlen finden möchten, die bei 1 beginnen, reichen die folgenden Makros aus. Alles, was Sie tun müssen, ist die Funktion TestCNR auszuführen, und Sie erhalten eine „Matrix“ von Zellen, die die Anzahl der 4-stelligen Kombinationen im sequentiellen Wertesatz von 1 bis 10 darstellen.
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
Das Makro überschreibt alles, was in Ihrem Arbeitsblatt enthalten ist. Stellen Sie daher sicher, dass Sie den Test mit einem leeren Arbeitsblatt ausführen. Wenn Sie die Größe der Menge oder die Anzahl der Elemente in der Teilmenge ändern möchten, ändern Sie einfach die in der TestCNR-Routine übergebenen Werte.
Wenn Sie eindeutige Kombinationen aus einer Zeichenfolge (z. B. den Buchstaben des Alphabets) ziehen möchten, müssen Sie einen anderen Satz von Makros verwenden. Das Folgende wird gut funktionieren; Es wird davon ausgegangen, dass sich die Zeichen, die Sie als „Universum“ verwenden möchten, in Zelle A1 befinden und die Nummer, die Sie in jeder eindeutigen Kombination wünschen, in Zelle 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
Führen Sie das FindSets-Makro aus, und die verschiedenen gewünschten Kombinationen landen in Spalte 2. Seien Sie jedoch vorsichtig, wenn Sie das Makro ausführen. Die Anzahl der Kombinationen kann sehr schnell sehr groß werden. Wenn Sie beispielsweise 26 Buchstaben (A bis Z) in Zelle A1 und den Wert 5 in Zelle A2 eingeben, stürzt das Makro ab. Warum? Denn es gibt 65.780 mögliche Kombinationen aus fünf Zeichen und nur 65.536 Zeilen, in denen sie platziert werden können.
_Hinweis: _
Wenn Sie wissen möchten, wie die auf dieser Seite (oder auf einer anderen Seite der ExcelTips-Websites) beschriebenen Makros verwendet werden, habe ich eine spezielle Seite vorbereitet, die hilfreiche Informationen enthält.
ExcelTips ist Ihre Quelle für kostengünstige Microsoft Excel-Schulungen.
Dieser Tipp (6766) gilt für Microsoft Excel 97, 2000, 2002 und 2003. Eine Version dieses Tipps für die Multifunktionsleistenschnittstelle von Excel (Excel 2007 und höher) finden Sie hier: