Webseite zuletzt aktualisiert: 23.10.2024
Für Permutationen, Variationen und Kombinationen stellt Excel Funktionen mit den Namen "VARIATIONEN", "KOMBINATIONEN" und "KOMBINATIONEN2" zur Verfügung, mit deren Hilfe man berechnen kann, wie viele Möglichkeiten es für die verschiedenen Fälle gibt.
Aber leider stellt Excel keine Funktion zur Verfügung, die diese Variationen bzw. Kombinationen automatisch generiert und auf dem Tabellenblatt anzeigt.
Hier soll nun eine in VBA programmierte Funktion (UDF = User Defined Function) namens "PermComb" vorgestellt werden, die Permutationen, Variationen und Kombinationen automatisch generiert.
* * *
Die Funktion 'PermComb' erzeugt wahlweise Permutationen, Variationen oder Kombinationen der Länge k aus einer Grundmenge mit n Elementen.
Da die Funktion mehrere Werte zurückgibt, handelt es sich um eine Matrixfunktion (engl. array formula). Wer Excel 2019 oder eine ältere Version verwendet, muss - wie bei CSE-Formeln üblich - vor Eingabe der Formel einen passenden Bereich selektieren und die Eingabe der Formel mit STRG-UMSCHALT-RETURN (Ctrl-Shift-Enter) abschließen.
Eine grundlegende Einführung in Matrixformeln gibt es hier:
https://hermann-baum.de/excel/tutorial/matrixformeln/
Die Exceldatei enthält den VBA-Code der Funktion 'PermComb' und Anwendungsbeispiele.
Die Exceldatei ist kostenlos, ohne Werbung, alles offen einsehbar und frei veränderbar.
= PermComb ( Grundmenge; [k]; [Reihenfolge]; [Wiederholung]; [Index] )
| Parameter | Erläuterung |
|---|---|
| Grundmenge | Kann ein Bereich (1 Spalte oder 1 Zeile) oder ein eindimensionales Array sein; n = Anzahl der Elemente in der Grundmenge |
| k (optional) |
Länge der Variation/Kombination (Anzahl der auszuwählenden Elemente) (Standard: k = n) |
| Reihenfolge (optional) |
FALSCH (0): keine Berücksichtigung der Reihenfolge WAHR (1): mit Berücksichtigung der Reihenfolge (Standard) |
| Wiederholung (optional) |
FALSCH (0): ohne Wiederholungen (Standard) WAHR (1): mit Wiederholungen |
| Index (optional) |
Mögliche Optionen:
Fehlt dieser Parameter, werden alle Variationen/Kombinationen generiert, ansonsten nur die durch die Indizes bestimmte Auswahl. |
Für alle Beispiele besteht die Grundmenge aus den 6 Buchstaben ABCDEF.
Diese 6 Buchstaben stehen in den Zellen B1:B6.
1. Alle Permutationen der 6 Buchstaben ABCDEF:
= permcomb (B1:B6)
Es ist nur der erste Paramter angegeben. Von allen anderen werden die Standardwerte verwendet.
A B C D E F
A B C D F E
A B C E D F
. . .
F E D C B A
Es gibt 720 Permutationen.
2. Alle Variationen ohne Wiederholung mit 4 Elementen aus den 6 Buchstaben ABCDEF:
= permcomb (B1:B6; 4)
A B C D
A B C E
A B C F
. . .
F E D C
Es gibt 360 Variationen.
3. Alle Kombinationen mit Wiederholung von 4 Elementen aus den 6 Buchstaben ABCDEF:
= permcomb (B1:B6; 4; FALSCH; WAHR)
A A A A
A A A B
A A A C
. . .
F F F F
Es gibt 126 Kombinationen.
4. Wie letztes Beispiel, jedoch nur die beiden ersten und die beiden letzten Kombinationen:
= permcomb (B1:B6; 4; FALSCH; WAHR; {1;2;125;126})
A A A A
A A A B
E F F F
F F F F
Es werden nur diese 4 Kombinationen erzeugt und angezeigt.
* * *
Was ist der Unterschied zwischen Permutationen, Variationen und Kombinationen?
Die Bezeichnungsweise ist nicht immer einheitlich. Im deutschsprachigen Raum ist die folgende Bezeichnungsweise üblich:
Permutationen sind verschiedene Anordnungen (Reihenfolgen) von n Elementen (lat. permutare = vertauschen).
Zum Beispiel gibt es 120 Möglichkeiten, fünf Bücher in unterschiedlicher Reihenfolge nebeneinander anzuordnen. Es gibt also 120 Permutationen dieser 5 Elemente.
Eine Variation oder eine k-Permutation ist in der Kombinatorik eine Auswahl von k Elementen aus einer Grundmenge von n Elementen unter Berücksichtigung der Reihenfolge.
Man wählt z.B. aus 6 Büchern 4 Bücher aus und stellt sie in einer bestimmten Reihenfolge nebeneinander.
Werden bei der Auswahl der k Elemente Wiederholungen zugelassen, so spricht man von Variationen mit Wiederholung oder k-Permutationen mit Wiederholung, ansonsten von Variationen ohne Wiederholung oder k-Permutationen ohne Wiederholung.
Bei der Auswahl der 4 Bücher wäre es ja denkbar, dass ein bestimmtes Buch mehrmals (z.B. zum Lesen) ausgewählt wurde.
Eine Kombination ist eine Auswahl von k Elementen aus einer Grundmenge von n Elementen ohne Berücksichtigung der Reihenfolge.
Man wählt z.B. aus 6 Büchern 4 Bücher aus, wobei es nur von Interesse ist, welche 4 Bücher das sind.
Auch Kombinationen gibt es mit Wiederholungen und ohne Wiederholungen. Eine Kombination mit Wiederholung läge vor, wenn man bei der Auswahl von 4 Büchern dreimal Buch A wählt und einmal Buch C, die Reihenfolge dabei aber keine Rolle spielt.
Eine Permutation ist somit eine Variation ohne Wiederholung (k-Permutation ohne Wiederholung) im Spezialfall k = n.
Im englischsprachigen Raum werden nur die Begriffe "permutation" (mit Reihenfolge) und "combination" (ohne Reihenfolge) verwendet. Eine Variation wird als 'partial permutation' oder als "k-permutation" bezeichnet.
Man kann somit vier grundlegende Fälle unterscheiden (RF = Reihenfolge, WH = Wiederholung):
Auswahl k aus n mit RF ohne WH
(Variationen bzw. k-Permutationen ohne WH)
Anzahl:
Excel-Formel: =VARIATIONEN(n;k)
Auswahl k aus n mit RF mit WH
(Variationen bzw. k-Permutationen mit WH)
Anzahl:
Excel-Formel: =n^k
Auswahl k aus n ohne RF ohne WH
(Kombinationen ohne WH)
Anzahl:
Excel-Formel: =KOMBINATIONEN(n;k)
Auswahl k aus n ohne RF mit WH
(Kombinationen mit WH)
Anzahl:
Excel-Formel: =KOMBINATIONEN2(n;k)
* * *
Das Beispiel 4 auf dem dritten Tabellenblatt der Download-Datei soll aufzeigen, wie der Parameter 'Index' sinnvoll eingesetzt werden kann.
In diesem Beispiel 4 geht es darum, zwei verschiedene Grundmengen miteinander zu kombinieren, sozusagen Kombinationen von Kombinationen zu bilden.
Die erste Grundmenge besteht aus den zwei Buchstaben A und B.
Es gibt 4 Variationen mit Wiederholung dieser beiden Buchstaben:
AA
AB
BA
BB
Formel zur Erzeugung dieser Variationen:
=permcomb(S5:S6;2;;WAHR) (1)
Die zweite Grundmenge besteht aus den beiden Ziffern 1 und 2.
Es gibt 8 Variationen mit Wiederholung von drei Ziffern aus dieser Grundmenge:
1 1 1
1 1 2
1 2 1
1 2 2
2 1 1
2 1 2
2 2 1
2 2 2
Formel zur Erzeugung dieser Variationen:
=permcomb(U5:U6;3;;WAHR) (2)
Nun sollen alle möglichen Kombinationen aus diesen zwei "Variationsmengen" gebildet werden, d.h. es sind alle Kombinationen aus zwei Buchstaben (nur A und B) und drei Ziffern (nur 1 und 2) gefragt:
A A 1 1 1
A A 1 1 2
...
B B 2 2 2
Es gibt 4 x 8 = 32 dieser Kombinationen.
Die ersten acht Kombinationen beginnen alle mit "AA", die zweiten acht mit "AB" usw.
In der Formel zur Erzeugung der Buchstabengruppe - siehe Formel (1) - müssten wir dafür sorgen, dass achtmal der Index 1 verwendet wird, dann achtmal der Index 2 usw.
Eine solche Folge von Indizes erzeugt die Formel
=QUOTIENT(ZEILE(1:32)-1;8)+1 (3)
Bei der Zifferngruppe, die mit Formel (2) generiert wird, bräuchten wir Indizes, die zuerst von 1 bis 8 laufen, danach wieder bei 1 beginnen und bis 8 laufen usw.
Eine solche Folge von Indizes erzeugt die Formel
=REST(ZEILE(1:32)-1;8)+1 (4)
In den Spalten AH und AI des dritten Tabellenblattes wird nun die Buchstabengruppe erzeugt, indem die Formel (3) als Parameter 'Index' in die Formel (1) eingesetzt wird:
=permcomb(S5:S6;2;;WAHR; QUOTIENT(ZEILE(1:32)-1;8)+1)
In den Spalten AJ, AK und AL wird die Zifferngruppe erzeugt, indem die Formel (4) als Parameter 'Index' in die Formel (2) eingesetzt wird:
=permcomb(U5:U6;3;;WAHR; REST(ZEILE(1:32)-1;8)+1)
Wer die 32 Kombinationen in jeweils einer Zelle haben möchte, kann dies anschließend mit einer Formel der Art
=AH5 & AI5 & AJ5 & AK5 & AL5 realisieren.
* * *
Manchmal sucht man alle möglichen Anordnungen einer Grundmenge, in der nicht alle Elemente voneinander unterschieden werden können. Als Beispiel soll die Grundmenge {A, B, B, C, C, C} dienen (siehe nachfolgende Abbildung).
Die Lösungsidee besteht darin, zunächst mit der Funktion PermComb alle 720 Permutationen zu erzeugen - so, als ob es 6 unterscheidbare Elemente wären - und dann mit der Funktion EINDEUTIG alle Duplikate zu entfernen.
Die verwendete Formel lautet somit:
=EINDEUTIG(permcomb(B2:G2))
Wer eine ältere Excel-Version verwendet, welche die Funktion EINDEUTIG nicht kennt, kann ▸hier eine entsprechende UDF (Benutzerdefinierte Funktion) 'Eindeutig' herunterladen.
Es gibt 60 Permutationen dieser Grundmenge. Die allgemeine Formel für die Anzahl lautet:
Konkret für dieses Beispiel:
Dieses Beispiel ist nicht in der Downloaddatei enthalten.
