hb-EXCEL-Logo
Englische Flagge   Deutsche Flagge

Webseite zuletzt aktualisiert: 03.09.2024

Permutationen, Variationen und Kombinationen mit Excel

Einleitung

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

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/

Download

Die Exceldatei enthält den VBA-Code der Funktion 'PermComb' und Anwendungsbeispiele.

Exceldatei mit der Funktion 'PermComb'    (Letzte Änderung: 02.09.2024) Download .xlsm

Die Exceldatei ist kostenlos, ohne Werbung, alles offen einsehbar und frei veränderbar.

Syntax

= 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:
  • einzelner Index
  • Bereich (1 Spalte oder 1 Zeile) mit den ausgewählten Indizes
  • eindimensionales Array mit festen Indizes

Fehlt dieser Parameter, werden alle Variationen/Kombinationen generiert, ansonsten nur die durch die Indizes bestimmte Auswahl.

Beispiele

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.


* * *

Begriffsklärung

Was ist der Unterschied zwischen Permutationen, Variationen und Kombinationen?

Die Bezeichnungsweise ist nicht immer einheitlich. Im deutschsprachigen Raum ist die folgende Bezeichnungsweise üblich:

Permutation

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.

Variation oder k-Permutation

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.

Kombination

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.

Begriffsverwirrung

Eine Permutation ist somit eine 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.

Zusammenfassung

Man kann somit vier grundlegende Fälle unterscheiden (RF = Reihenfolge, WH = Wiederholung):

  1. Auswahl k aus n mit RF ohne WH
    (Variationen  bzw. k-Permutationen ohne WH)

    Anzahl:

    Excel-Formel:  =VARIATIONEN(n;k)

  2. Auswahl k aus n mit RF mit WH
    (Variationen  bzw. k-Permutationen mit WH)

    Anzahl:

    Excel-Formel:  =n^k

  3. Auswahl k aus n ohne RF ohne WH
    (Kombinationen ohne WH)

    Anzahl:

    Excel-Formel:  =KOMBINATIONEN(n;k)

  4. Auswahl k aus n ohne RF mit WH
    (Kombinationen mit WH)

    Anzahl:

    Excel-Formel:  =KOMBINATIONEN2(n;k)

 


* * *

Verwendung des Parameters 'Index'

Das Beispiel 4 auf dem dritten Tabellenblatt der Download-Datei soll aufzeigen, wie der Parameter 'Index' sinnvoll eingesetzt werden kann.

Problemstellung

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.

Lösung

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.


* * *

Permutationen einer Grundmenge mit teilweise nicht unterscheidbaren Elementen

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).

Permutationen einer Grundmenge mit teilweise nicht unterscheidbaren Elementen

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.

Ornament

Wissen teilen ist die Zukunft der Menschheit