Analyse, welche Artikel häufig in Kombination gekauft werden

[Strailer]

Hallo

Ich habe zwar einige Grundkenntnisse in Excel, aber bei der folgenden Frage bin ich komplett überfordert und hoffe auf eure Hilfe.

Ich möchte analysieren, welche Artikel von den Kunden oft in Kombination gekauft werden.
Aus der folgenden Tabelle sollte also rauskommen, dass 3 Kunden die Kombination aus Artikel 11 + 12 bestellt haben und 2 Kunden die Kombi aus Artikel 29 + 30 + 31.
Wenn die Anzahl Artikel immer 2 wäre, könnte ich mir etwa folgendes vorstellen:
Sortieren nach KundenbestellNr.
Funktion in C3: Wert = 1 wenn
Bedingung 1: An=An+1
Bedingung 2: Wert in Spalte Bn = B3, Wert in Spalte Bn+1 = B4
Diese Funktion könnte ich dann komplett runterziehen und hätte die Anzahl, wie oft die Kombi vorkommt.
Problem 1: Ich müsste diesen Schritt für jede denkbare Kombi wiederholen.
Problem 2:  DIe Kunden bestellen hmanchmal zwei oder mehrere Artikel, die in dieser Kombination häufig vorkommen, und außerdem noch zufällige weitere Artikel. Dann sollte natürlich nur nach der häufigen Kombination gesucht werden.
Anmerkung: Es handelt sich um rund 30.000 Zeilen pro Tabelle.

Ich weiß das es kompliziert ist, aber ich wäre euch echt dankbar für einige Hinweise.

[Fennek]

Hallo,

ein interessantes Thema, im Prinzip würde ich mir das ansehen, aber...

Beim Öffnen kam die Meldung "soll die Datei FF_einfacher snb.xlsm wiederhergestellt werden?"

Da snb anscheinend daran gearbeitet hat, gibt es nichts mehr zu verbessern! Du solltest einen link auf den alten post setzen

mfg

(nachträglich ergänzt: auch noch Lidl, eine Firma, die für schlecht Löhne und Arbeitbedingungen bekannt ist (aus den Doc-Properties)

[Rabe]

Hi,

Da snb anscheinend daran gearbeitet hat, gibt es nichts mehr zu verbessern! Du solltest einen link auf den alten post setzen

:19:  :21:
bei mir kam die Frage nicht!

Hier ein Bild seiner Datei, da das Auslesetool rumspackt:
   

[Jockel]

Hi,
...da das Auslesetool rumspackt...

Hallo Ralf, welches nutzt du denn..? (Spaß)

[Fennek]

Hallo Timo,

hier ist eine Lösung, dürfte zwar wesentlich langsamer als der Vorschlag von snb sein, ist dafür aber einfach zu programmieren.

mfg

(ich hatte die bereitgestellte Datei zuerst mit OO geöffnet, dabei erscheint die Verknüpfung. In xl2016 war dies nicht zu sehen)
(Meine Empfehlung wäre einen Statistikprof, Spezialgebiet "DataMining", zu beauftragen, da ist das Preis-/Leistungsverhältnis am schlechtestens)

[Ego]

Hallo ,

@strailer

a) Gibt es schon eine Lösung von snb und wenn ja was fehlt dabei?

@Fennek

a) Warum ohne Makro?
b) Warum ohnde dreier und vierer ... Kombinationen?

[Fennek]

Hallo,

der erste Versuch mit "Areas" benöigte in einem Test-Scenario mit ca 125.000 Zeilen ca 26 Sekunden. Dieser Code lag unter 0,5 Sekunden:

Code:

Private Declare Function GetTickCount Lib "kernel32" () As Long
Dim iDic As Object
Sub Fen2()
Anf = Timer
iZeit = GetTickCount
Dim i As Long, K As Long, R As Long
Dim Art As String
Set iDic = CreateObject("Scripting.Dictionary")
K = Cells(3, "A")
Art = Cells(3, "B")
R1 = Range("A3:B" & Cells(Rows.Count, "B").End(xlUp).Row + 1)
For i = 2 To UBound(R1)
   If R1(i, 1) = K Then
       Art = Art & " " & R1(i, 2)
   Else
       Paare (Art)
       Art = R1(i, 2)
       K = R1(i, 1)
   End If
Next i
Cells(11, "J").Resize(iDic.Count, 2) = Application.Transpose(Array(iDic.keys, iDic.Items))
Debug.Print Timer - Anf, GetTickCount - iZeit
End Sub

Sub Paare(Tx As String)
Dim P As String
Dim i As Long, j As Long
F1 = Split(Tx)
   Select Case UBound(F1)
   Case Is = 1
       Pa = F1(0) & ", " & F1(1)
       LL (Pa)
   Case Is > 1
   For i = 0 To UBound(F1) - 1
       For j = i + 1 To UBound(F1)
           Pa = F1(i) & ", " & F1(j)
           LL (Pa)
       Next j
   Next i
End Select
End Sub

Sub LL(Tx As String)
If Not iDic.exists(Tx) Then
   iDic.Add (Tx), 1
Else
   iDic.Item(Tx) = iDic.Item(Tx) + 1
End If
End Sub

Falls der Fragesteller trotz aller Kommentare den Code nutzen möchte, wäre eine Spende für den Verein angemessen.

mfg

PS: Folgenden Code hätte ich gerne eingbaut, um anschließend mit einem "Speicher-Kopie" das Programm zu "testen", aber der Code lief nicht unter xl2016 64bit

Code:

Public Declare PtrSafe Function VarPtrArray Lib "VBE7" Alias _
   "VarPtr" (Var() As Any) As LongPtr

Sub PtrExample()

   Dim lLong As Long
   Dim sString As String
   Dim oCollection As Collection
   Dim aDoubles(10) As Double
   Dim vDate As Variant
   
   lLong = 42
   sString = "Hello"
   Set oCollection = New Collection
   aDoubles(0) = 3.14159
   vDate = Now
       
   Dim ptrToLong As LongPtr
   Dim ptrToStringVar As LongPtr
   Dim ptrToBSTR As LongPtr
   Dim ptrToObjVar As LongPtr
   Dim ptrToObject As LongPtr
   Dim ptrToArray As LongPtr
   Dim ptrToArrayData As LongPtr
   Dim ptrToVarVar As LongPtr
   Dim ptrToDate As LongPtr
   
   ' Pointer to Long variable, which is also the actual Long value
   ptrToLong = VarPtr(lLong)
   
   ' Pointer to String variable
   ptrToStringVar = VarPtr(sString)
   
   ' Pointer to actual string content
   ptrToBSTR = StrPtr(sString)
   
   ' Pointer to Object variable
   ptrToObjVar = VarPtr(oCollection)
   
   ' Pointer to actual object in memory
   ptrToObject = ObjPtr(oCollection)
   
   ' Pointer to array variable
   ptrToArray = VarPtrArray(aDoubles)
   
   ' Pointer to the start of the actual array content
   ptrToArrayData = VarPtr(aDoubles(0))
   
   ' Pointer to Variant variable
   ptrToVarVar = VarPtr(vDate)
   
   ' Pointer to actual Date value, at offset 8 of VARIANT struct
   ptrToDate = ptrToVarVar + 8
   
End Sub

[Ego]

Hallo strailer,

in der Anlage einmal ein Programm, dass auch Kombinationen mit mehr als zwei Elementen untersucht.

Achtung! Für grössere Datenbestände ist Excel nicht das richtige Werkzeug. Hier wäre ein kompelierbarer Code, der auch mehrere Prozessorkerne benutzen kann, um ein Vielfaches schneller.

Dabei ist der Aufwand (Zeit) nur geringfügig von der Anzahl der Datensätze abhängig. Viel eher erhöht sich der Aufwand, wenn ich Kunden mit mehr Artikeln habe.

a) Prüfungen von 2erKombinationen bei 100 Datensätzen

0 wenn ich 100 Kunden mit je einem Artikel habe.
50  wenn ich 50 Kunden mit je 2 Artikeln habe.
200 wenn ich 20 Kunden mit je 5 Artikeln habe.
2.450 wenn ich 2 Kunden mit je 50 Artikeln habe.

Noch extremer ist es, wenn ich auch Kombinationen mit mehr als zwei Elementen untersuchen möchte.

b) Prüfungen von Kombinationenmit bis zu 5 Elementen bei 100 Datensätzen

0 wenn ich 100 Kunden mit je einem Artikel habe.
50  wenn ich 50 Kunden mit je 2 Artikeln habe.
520 wenn ich 20 Kunden mit je 5 Artikeln habe.
4.739.770 wenn ich 2 Kunden mit je 50 Artikeln habe.

[Strailer]

Hallo Ego

Wow, vielen Dank, es funktioniert perfekt!
Sorry dass ich jetzt erst antworte, ich hatte eingestellt dass ich bei einer neuen Antwort eine Mail erhalte, aber irgendwie hat das nicht funktioniert.

Vielen Dank natürlich auch an alle anderen für die schnelle und kompetente Antwort.

Grüße, Timo