ich bin auf der Suche nach einer Lösung für folgende Herausforderung:
Ich habe eine Tabelle mit allen Bestellungen, den enthaltenen Produkten und der Anzahl von bestellten Produkten. Dabei wird pro Produkt und pro Bestellung eine neue Zeile generiert. Das heißt wenn ein Kunde zwei verschiedene Produkte in einer Bestellung bestellt, dann wird das in zwei Zeilen angezeigt. Nun möchte ich herausfinden, welche Produkte häufig zusammen bestellt werden, also welche Kombinationen häufig vorkommen. Anbei einmal eine Beispieldatei. Meine eigentliche Tabelle enthält 694 Zeilen, aufgrund von kundenbezogener Daten kann ich diese hier nicht teilen.
Ich habe überlegt, ob sich das über eine Pivot Tabelle lösen lässt und habe als Zeile die Bestellnummer, Spalte die Produktcodes und im Datenbereich die Summe der Produktcodes. Hier kann ich jedoch nicht sehen, welche Kombination häufig vorkommt sondern nur wie häufig das Produkt vorkommt.
Hat hier jemand eine Idee? Ich bin leider überfordert. Danke schonmal!
in der Anlage ein Lösungsbeispiel mit Makro und hier noch zwei Bemerkungen:
1. Sortierung
Die Liste muss zur Zeit nach Bestellnummer und Produktcode sortiert sein.
2. Namen
Da ich in makros ungern mit festen Adressen arbeite, benötigt das Makro zur Zeit zwei benannte Zellen ("Liste" und "Ausgabe").
Ich greife auf die Bereiche zu den benannten Zellen mit "CurrentRegion" zu, daher dürfen in den Zellen direkt vor, unter oder neben den Listen keine Einträge vorhanden sein.
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.
vielen Dank für deine Hilfe. Leider kann ich auf die Datei nicht vollständig zugreifen, da ich eine andere Excel Version habe. Könntest du das Makro hier einmal rein kopieren? Danke.
Private Sub cbTuwat_Click()
Dim Z1 As Long
Dim Z2 As Long
Dim Key As Variant
Dim Liste As Variant
Dim Text() As String
Dim Ausgabe() As Variant
Dim dict As Object
Set dict = CreateObject("Scripting.Dictionary")
Liste = ThisWorkbook.Names("Liste").RefersToRange.CurrentRegion.Value
'-----------------------------------------
' Einlesen der Paarungen
'-----------------------------------------
For Z1 = 2 To UBound(Liste, 1) - 1
For Z2 = Z1 + 1 To UBound(Liste, 1)
If Liste(Z1, 1) = Liste(Z2, 1) Then
Key = Liste(Z1, 2) & ";" & Liste(Z2, 2)
dict(Key) = dict(Key) + 1
Else
Exit For
End If
Next Z2
Next Z1
'-----------------------------------------
' Ausgeben der Paarungen
'-----------------------------------------
ReDim Ausgabe(1 To dict.Count, 1 To 3)
Z1 = 0
For Each Key In dict.keys
Z1 = Z1 + 1
Text = Split(Key, ";")
Ausgabe(Z1, 1) = dict(Key)
Ausgabe(Z1, 2) = Text(0)
Ausgabe(Z1, 3) = Text(1)
Next Key
ThisWorkbook.Names("Ausgabe").RefersToRange.CurrentRegion.ClearContents
ThisWorkbook.Names("Ausgabe").RefersToRange.Resize(dict.Count, 3).Value = Ausgabe
End Sub
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.
Ich habe jedoch festgestellt, dass wenn ein Kunde mehr als 2 verschiedene Produkte in seiner Bestellung hat, dass das Makro das nicht erkennt, sondern diese dann überspringt. Gibt es da noch eine Lösung für?
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.
Ich habe festgestellt, dass wenn ich eine Bestellung habe die 3 Produkte beinhaltet, die Kombination nicht richtig gezählt werden. Es werden dann nur die ersten beiden Artikel gezählt. Ich habe auch Bestellung die mehr als 3 Produkte beinhalten. Anbei nochmal eine Beispieldatei zur Verdeutlichung.
Was wird denn falsch berechnet, wenn du deine Beispieldaten in meiner Datei mit dem Makro auswertest?
In meiner Datei waren mehr Beispiele, auch Bestellungen mit mehreren Produkten. Wo wird die Anzahl der auftretenden Kombinationen falsch ermittelt?
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.
