Excel komplexe Kombinationenmöglichkeiten zählen!

[Alexander92]

Hallo zusammen,

ich als neues Mitglied dieses Forums, werde zukünftig aufgrund meines neuen Jobs öfter von mir hören lassen.

Ich halte es kurz und habe folgendes Problem:

In meiner Excel Datei befinden sich unzählige Kunden die alle Produkte besitzen. Es gibt die Produkte A bis O und ich muss wissen, wie viele Kunden z.B. die Produkt-Kombi A & B (analog auch B&A was ja dasselbe wäre), A & C, A&C&C&O&F usw. besitzen.

Das heißt Ergebnis müsste sein, dass ich am Ende eine Liste habe in der ablesbar ist wie viele Kunden jegliche gleiche Kombinationen (egal ob es nur eine zweier Kombi oder 6er Kombi) ist besitzen.

PS: Ich weiß es gibt hier zahlreiche Topics dazu, ich verstehe sie allerdings nicht, und finde auch keine die exakt mein Problem beschreibt!

Ich hoffe auf rasche Hilfe & bedanke mich schon mal vielmals bei allen die mir helfen können!

Liebe Grüße
Alexander

[Jonas0806]

Hallo Alexander,

ich als neues Mitglied dieses Forums, werde zukünftig aufgrund meines neuen Jobs öfter von mir hören lassen.

Von mir bekommst Du jetzt schonmal die goldene Regel zu hören. Ein Problem lässt sich meist mit einer Beispieldatei, die falls nötig, anonymisiert wurde, viel leichter beschreiben und verstehen. Ausserdem muss ein potentieller Helfer deine Tabelle nicht nachbauen. Zum einen ist dies sehr aufwendig, zum Anderen sieht das Original dann doch meist etwas anders aus, was dann zu Anapssungen im Nachhinein führt.

Essenz: Bitte eine .xlsx-Datei mit gewünschter Lösung, händisch eingetragen, hochladen.

Ansonsten natürlich, willkommen im Forum ;)

[lupo1]

Bitte verhalte Dich tatsächlich so, wie Jonas es sagt.

Eine Frage wäre da schon mal:

Wenn gefragt wird, wieviele Kunden A und B haben, haben wir dann hier 2 (weil "enthalten") oder 1 (weil "identisch")?

Kunde1: A B C
Kunde2: A B

[Alexander92]

Ok, da hätte ich selber drauf kommen können! Also ab sofort mit Beispiel...

Bei Kunde 1: A,B,C
     Kunde 2: A,B

Hätten wir in dem Fall 2 Kunden mit der Produkt-Kombi A & B haben. Ein Kunde hat die Kombi A&B&C.

Ich könnte somit eine Tendenz ableiten, welche Produkte am Ende zusammen sinnig verkauft werden könnten.

Liebe Grüße
Alex

[Jonas0806]

Hallo Alex,

ich hätte da die ein oder andere Frage.

Ist das exakt der Aufbau deiner Originaldatei? Wo kommen die Daten her, Import? Sind die Trennzeichen immer die selben? Wären Hilfsspalten denkbar? Wäre Power Query denkbar? Wäre VBA denkbar?

[Alexander92]

Genau, die Daten/Wörter sind alle durch ein Komma getrennt und ein Import. Ich hatte auch schon mal rum getüfftelt und alles auf separate Zellen aufgeteilt.
Power Query und VBA sagt mir leider nichts :(

[Jonas0806]

Hallo Alex,

dass Du von VBA noch nie was gehört hast, macht die Sache jetzt nicht einfacher. Aber Du kriegst das hin ;)

Anhand deiner Aussagen zum Thema VBA, gehe ich nicht davon aus, dass Du die Registerkarte "Entwicklertools" aktiv hast. Also:
1. Mache unten wo deine Tabellenblätter aufgelistet sind einen Rechtsklick auf einen Tab.
2. Klicke auf Code anzeigen.
3. Es hat sich ein neues Fenster geöffnet. Hier klickst Du oben im Menüband auf Einfügen und auf Modul.
4. Es hat sich wieder ein kleineres Fenster (Weißer Hintergrund) innerhalb des neuen Fensters geöffnet. Hier fügst Du den folgenden Code an:

Code:

Option Explicit

Public Function AnzahlKombination(rngBereich As Range, varSearchstring As Variant) As Long
   Dim rngZelle As Range
   Dim i As Long
   Dim lngCounter As Long: lngCounter = 0
   Dim booContainsAll As Boolean
   Dim arrSplitted
   
   arrSplitted = Split(varSearchstring, ",")
   
   For Each rngZelle In rngBereich
       booContainsAll = True
       
       For i = LBound(arrSplitted) To UBound(arrSplitted)
           If Not (InStr(1, rngZelle.Value, arrSplitted(i)) > 0) Then booContainsAll = False
       Next i
       
       If booContainsAll Then lngCounter = lngCounter + 1
   Next rngZelle
   
   AnzahlKombination = lngCounter
End Function

Jetzt kannst Du das Fenster wieder schließen. Du kannst die Funktion jetzt ganz normal im Tabellenblatt verwenden. Also so:

Tabelle1

	A	B	C	D	E
1	KRN	Produkte		Kombinationen	Anzahl
2	a	1,2,3,6		1,2	4
3	b	2,4,6		1,2,3	3
4	c	2,1		1,2,3,6	3
5	d	6,3		2,4	3
6	e	3,2		2,3	4
7	f	1,2,3,4,5,6
8	g	3,4,6,5,1,2

Formeln der Tabelle

Zelle Formel E2 =AnzahlKombination($B$2:$B$8;D2) E3 =AnzahlKombination($B$2:$B$8;D3) E4 =AnzahlKombination($B$2:$B$8;D4) E5 =AnzahlKombination($B$2:$B$8;D5) E6 =AnzahlKombination($B$2:$B$8;D6)

Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4.8

Du darfst jetzt allerdings deine Datei nicht mehr als .xlsx sonder als .xlsm speichern.

[Ego]

Hallo Alex,

hier noch einige Fragen:

1) Für Jonas Lösung müssen die gesuchten Kombinationen vorgegeben werden, ist das so richtig, oder sollen alle vorhandenen Kombinationen ermittelt werden?

2) Wieviel ist denn bei dir unzählig?

3) Gibt es eine maximale Anzahl von Produkten je Kunde oder soll das auch vom Programm ermittelt werden?

4) Denkst du es sind weniger als 1.048.576 Kombinationen vorhanden? (Falsch wenn ein Kunde mehr als 20 Produkte hat)

[Alexander92]

Wow! Zunächst vielen VIELEN Dank für die Hilfe!!!!

1. Genau, das wäre noch wichtig. Es müsste noch herausgefunden werden, welche Kombinationsmöglichkeiten es gibt, diese sind also nicht vorgegeben. Die vorhandenen Kombis müssten also ermittelt werden.
   
2. Es handelt sich um ca. 22 Produkte die je von 2600 Kunden unterschiedlich bezogen werden können. Manche haben nur 2 davon, andere 6 usw.
   
3. Ich denke, es gibt eine Hand voll Kunden die alle 22 Produkte haben, auch hier wäre eine Ermittlung sinvoll
   
4. Es sind definitiv weniger, da wirklich nur ein geringer Teil mehr als 8 Produkte gleichzeitig bezieht
   

Eine Frage noch zur Formel: Ich habe diese zwar bei bei Code und dann Modul eingefügt, zurück in der Tabelle wird mir diese aber sobald ich das = in die Zelle mache nicht angezeigt als "KombinationenAnzahl".

Liebe Grüße und nochmals vielen Dank! Ich hoffe wir finden eine Lösung :)

[Ego]

Hallo Allex,

in der Anlage eine erste Beispieldatei und hier noch Anmerkungen:

a) Deine Aussagen zu den Fragen 3 und 4 passen nicht zusammen.
Wenn ich einen Kunden mit 22 Produkten habe, kann ich allein für diesen mehr als 4 Millionen Kombinationen von Produkten erstellen.

b)Begrenzung
Ich habe in der Anlage:
1. Die Anzahl der Ausgabezeilen auf 1.000.000 begrenzt (da Excel noch nicht sehr viel mehr kann; das kann man im Kopf des Programmes anpassen)
2. Die "maximale Anzahl von Produkten in Kombinationen" als Parameter vorgebbar realisiert.
Achtung! Dieser Parameter hat eine grosse Auswirkung auf die  Laufzeit des Programmes und auf die Anzahl der Ausgabezeilen.

c) zur Benutzung
Da ich im Programm ungern mit festen Adressen arbeite benötigt das Programm zur Zeit drei benannte Bereiche.
1) Ein benannter Bereich "Liste" mit den Eingangsgrössen. (In der ersten Zeile Überschriften und in der zweiten Spalte die Produkte dur Kommata getrennt)
2) Eine benannte Zelle "AnzProd" für die Vorgabe der "maximale Anzahl von Produkten in Kombinationen".
3) Eine benannte Zelle "Ausgabe" ab der die Ergebnisse geschrieben werden.
Vor einem Lauf müssen die Ergebnisse der letzten Berechnung zur Zeit noch manuell gelöscht werden.
Ich habe (zur Zeit in Zelle E1) eine Funktion eingebaut. Wenn man sie nach einem Lauf bis ans Ende der Ausgabe kopiert, kann man die Ausgabe leicht nach der Anzahl der Produkte in einer Kombination sortieren.