Aus einer Datenbank werden über mehrere Semester Teilnehmerlisten ausgegeben, in denen die Teilnehmernamen mit dem belegten Modulen aufgeführt sind. Dabei erscheinen Teilnehmer, die im Laufe der Zeit mehrere Module belegen, mehrfach untereinander. Es gibt Teilnehmer, die haben vier Module, andere haben zwei oder nur ein Modul besucht. Dabei ist kein Modul vorgeschrieben. Es kann also sein, dass ein Teilnehmer die Module ABCD oder ABD oder BC besucht.
Benötigt wird jetzt eine Auswertung, welche Modulkombinationen vorkommen und wie oft sie gebucht werden. Das Ergebnis zeigt, dass 46 Teilnehmer nur Modul A besuchen, einer hat A und B, einer A und B und C und drei haben alle vier Module besucht.
Wie kann man das ermitteln? Es gibt sicherlich viele Wege, wir haben uns für diesen Weg entschieden:
- Mit der Funktion ZÄHLENWENN wird ermittelt, wie viele Module pro Name gewählt wurden
- Zuerst wird nach Name und dann nach Modul sortiert
- Dann wird ermittelt, wo die Namen wechseln: ein “a” steht für den Beginn eines Namens, ein “e” steht für das Ende.
Die Formel lautet:
=WENN(D2<>D1;“a“;WENN(D2<>D3;“e“;““)) - Die Formel nach unten ausfüllen – jetzt muss jeweils am Anfang eines Namens ein “a” gezeigt werden und am Ende “e”.
Mit diesen beiden Kennzeichnungen haben wir “Stoppstellen”, anhand derer sich die Kombinationen erzeugen lassen. Die Formel für die Kombinationen besteht aus mehreren Funktionen und lautet:
=WENN(F2=1;E2;WENN(G2=“a“;TEXTVERKETTEN(„, „;WAHR;E2:INDEX(E2:$E$1000;VERGLEICH(„e“;G2:$G$1000;0)));““))
Die Funktion TEXTVERKETTEN gibt es erst ab Excel 2016!
Der erste Teil =WENN(F2=1;E2; … wertet die Anzahl der Module aus. Gibt es nur ein Modul, wird einfach das Modul geschrieben. In diesem Fall gibt es ja nur eines.
Der zweite Teil WENN(G2=“a“; … wird angewandt, wenn die Anzahl der Module größer als 1 ist. Dann wird ausgewertet, ob in der Spalte G ein “a” steht. In diesem Fall wird die Funktion TEXTVERKETTEN eingesetzt.
TEXTVERKETTEN gibt als erstes an, welches Trennzeichen zwischen den zu verkettenden Elementen verwendet wird; in meinem Beispiel ein Komma. WAHR gibt an, dass leere Zellen ignoriert werden sollen.
TEXTVERKETTEN(„, „;WAHR; …
Danach folgt der Bereich, der verkettet werden soll – nur ist das bei mir nicht eindeutig. Der Beginn ist in der gleichen Zeile die Spalte E, falls dort ein “a” steht. Dieses Bedingung haben wir mit der WENN-Bedingung schon gestellt. Das Ende kann nur ermittelt werden, indem nach dem Buchstaben “e” gesucht wird. Das passiert mit INDEX, der eine Zeilennummer zurück liefert. Der zu durchsuchende Bereich ist E2 bis E1000, die Zeilennummer ergibt sich aus der Suche nach “e” in G2:B1000 mit VERGLEICH.
TEXTVERKETTEN(„, „;WAHR;E2:INDEX(E2:$E$1000;VERGLEICH(„e“;G2:$G$1000;0)))
Achten Sie darauf, dass in INDEX und VERGLEICH die Bezüge einmal relativ sind (E2 und G2) und einmal absolut ($E$1000 und $G$1000).
Die Funktion wird nach unten ausgefüllt und liefert jetzt pro Name eine Zeile mit den Kombinationen, die restlichen Zeilen für diesen Namen bleiben leer.
Die Kombinationen werden zum Schluss mit einer Pivot-Tabelle ausgewertet. Die Spalte “Kombinationen” wird als Zeilenbeschriftung und als Wertefeld verwendet. Das Feld “Kombinationen” in der Pivottabelle muss noch gefiltert werden, so dass die Leerzellen nicht erscheinen.
Sie können die Beispieltabelle hier herunterladen.
Bork.Blog 