18.04.2017, 16:15 (Dieser Beitrag wurde zuletzt bearbeitet: 18.04.2017, 16:15 von 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.
(18.04.2017, 16:40)Fennek schrieb: 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:
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)
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?
helmut
Für mich ist die Möglichkeit in Excel an Zellen und Bereichen Namen zu vergeben die wichtigste Funktionalität. Sie macht Formeln und den VBA-code verständlicher. Für Makros gilt die Regel: "Nur über benannte Bereiche auf den Inhalt der Zellen zugreifen."
Und wofür sind Regeln da? Um nachzudenken bevor man sie bricht.
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
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.
helmut
Für mich ist die Möglichkeit in Excel an Zellen und Bereichen Namen zu vergeben die wichtigste Funktionalität. Sie macht Formeln und den VBA-code verständlicher. Für Makros gilt die Regel: "Nur über benannte Bereiche auf den Inhalt der Zellen zugreifen."
Und wofür sind Regeln da? Um nachzudenken bevor man sie bricht.
Folgende(r) 1 Nutzer sagt Danke an Ego für diesen Beitrag:1 Nutzer sagt Danke an Ego für diesen Beitrag 28 • Strailer
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.