Gruppierung / Segmentierung von Daten

[Orlis123]

Hallo,

ich versuche eine Segmentierung/Gruppierung zu erstellen aus zwei Variablen. Variable 1 Artikelnummer; Variable 2 Eigenschaft und möchte nun alle Eigenschaften gruppieren, welche durch eine gleiche Artikelnummer mit einander verbunden sind.
Beispiel 

Artikel               Eigenschaft
1                          A
1                          B
2                          C
2                          B
3                          D
4                          D
4                          E
5                          C

Artikel 1 hat Eigenschaft A und B; Eigenschaft B besitzt aber auch Artikel 2, welcher wiederum noch Eigenschaft C hat. Eigeschaft C hat aber noch Artikel 5, der sonst aber keine weitere Eigenschaft hat.
Zielgruppierung: Gruppe Eigenschaft:  A, B, C
Diese Gruppe enthält die Artikel 1, 2 und 5, da diese Artikel ähnliche Eigenschaften haben.

Meine Frage ist, ob jemand weiß wie man das in Excel lösen kann (Formel, Funktion oder VBA) oder braucht man dafür bereits eine spezielle Statistiksoftware für Zusammenhänge von Variablen?

Vielen Dank vorab für die Unterstützung.

[Rabe]

Hi,

Meine Frage ist, ob jemand weiß wie man das in Excel lösen kann (Formel, Funktion oder VBA) oder braucht man dafür bereits eine spezielle Statistiksoftware für Zusammenhänge von Variablen?

das geht ganz einfach per PivotTable, der Unterschied der Beiden ist nur die Reihenfolge im Zeilenbereich:
   

[Ego]

Hallo Orlis,

ich nehme an, dass die Antwort von Ralf nicht deinen Erwartungen entspricht, bitte melde dich doch noch einmal.

Ich nehme an, dass die Auswertung deiner Beispieldaten zwei Gruppen ausweisen sollen:
A,B,C und D,E.

Da man, wenn ich die Aufgabe richtig verstanden habe, die Eigenschaften einer Gruppe über sehr viele Ebenen aus den Artikeln ermitteln können muss, glaube ich nicht, dass man sie mit einer Formel oder Standardfunktion berechnen kann.
Für VBA ist es aber ohne weiteres machbar.
Wieviel VBA für Excel kannst du oder kennts du jemanden der dir bei der Umsetzung helfen kann.

helmut

[Orlis123]

Hallo Orlis,

ich nehme an, dass die Antwort von Ralf nicht deinen Erwartungen entspricht, bitte melde dich doch noch einmal.

Ich nehme an, dass die Auswertung deiner Beispieldaten zwei Gruppen ausweisen sollen:
A,B,C und D,E.

Da man, wenn ich die Aufgabe richtig verstanden habe, die Eigenschaften einer Gruppe über sehr viele Ebenen aus den Artikeln ermitteln können muss, glaube ich nicht, dass man sie mit einer Formel oder Standardfunktion berechnen kann.
Für VBA ist es aber ohne weiteres machbar.
Wieviel VBA für Excel kannst du oder kennts du jemanden der dir bei der Umsetzung helfen kann.

helmut

Hallo Helmut,
danke für die Antowert. In der Tat hilft mir die Antwort von Ralf leider nicht weiter. Ich hatte gehofft, dass mit einer Formel lösen zu können. Mit einem Pivottable bekomme ich das jedenfalls nicht hin. So bleibt wohl nur der Weg über VBA. Allerdings sind meine Kenntnisse dahingehend eher beschränkt. Sofern du eine einfache Programmierung dafür hättest, wäre ich natürlich sehr dankbar. Ansonsten muss ich mich mal umschauen, ob mir da noch jemand anderes bei der Programmierung helfen könnte.
Gruss
Daniel

[Rabe]

Hi Daniel,

danke für die Antowert. In der Tat hilft mir die Antwort von Ralf leider nicht weiter. Ich hatte gehofft, dass mit einer Formel lösen zu können. Mit einem Pivottable bekomme ich das jedenfalls nicht hin.

was heißt, Du bekommst das nicht hin?
Ist das Ergebnis nicht richtig oder kannst Du das nur nicht selbst erreichen, weil dir die Kenntnisse fehlen?
Im ersten Fall ist dann Deine Fragestellung nicht so klar gewesen. Zeig doch mal anhand Deines Beispiels, wie das Ergebnis aussehen soll.
Im zweiten Fall kann es Dir gezeigt werden.

Nur weil mir grad keine Formel einfällt, mit der das zu lösen ist, heißt es ja nicht, daß es nicht geht.
Und auch für eine VBA-Lösung wird eine klare Fragestellung benötigt.

[schauan]

Hallo Ralf,
Wenn Du noch mal in die Aufgabe schaust, siehst Du das Problem. Der TE möchte für das Beispiel 2 Gruppen als Ergebnis. In der Pivot hast Du mehr Gruppen und demzufolge auch nicht die gewünschte Zusammenstellung. Das Beispiel ist sicher auch nur die"kurze" Variante, real muss man sicher mit deutlich mehr Gruppen und -Mitgliedern rechnen.
Das wird nur mit VBA. Da ist aber eventuell Helmut schon am programmieren...

[Ego]

Hallo Orlis,
eigentlich ist es nicht mein Verständnis eines Excel-Hilfe-Forums fertige Module in solch einem Umfang zu liefern. Ich halte mehr von Hilfe zur Selbsthilfe. Außerdem finde ich es gut, wenn man im eigenen Bekanntenkreis gegenseitig kleine Gefälligkeiten füreinander erstellt. Vielleicht kennst du ja jemanden der sich freut das Programm für dich zu schreiben.

Um dir eine Vorstellung für den Umfang deiner Anfrage zu geben einige Bemerkungen.
A) Umfang:
Ich erwarte, dass der Code weniger als 100 Codezeilen (ohne Kommentare und Leerzeilen) benötigt. Hierbei werden etliche Zeilen für einfache Schleifen zum Einlesen, für die Ausgabe und zur Überprüfung, ob ein Wert schon im Array ist, gebraucht.
Der eigentliche Algorithmus wird weniger als 50 Zeilen umfassen.
Insgesamt wird man weniger als 3 Stunden benötigen
10-30 Minuten für das Verstehen der Aufgabe und dem Aufbau des Algorithmus und
1-2 Stunden für die Programmierung und den Test.
B) Elemente:
Benötigt werden drei Arrays (eindeutige Liste aller Eigenschaften; eindeutige Liste aller Eigenschaften der aktuellen Gruppe und eindeutige Liste aller Artikel der aktuellen Gruppe), einige Variablen zum Nachhalten der Anzahl und der aktuellen Position der Elemente in den Arrays, einige einfache Schleifen wie oben genannt und eine dreifach geschachtelte Schleife (FOR..WHILE..FOR) für den Algorithmus.
C) Algorithmus:
1.       Einlesen aller Eigenschaften ohne Duplikate.
2.       In einer FOR-Schleife (Anzahl ist bekannt) werden alle Eigenschaften bearbeitet.
2.1   Wenn die aktuelle Eigenschaft nicht leer ist. (Weiter unten werden Eigenschaften aus der Liste aller Eigenschaften entfernt) beginnt eine neue Gruppe mit einigen Initialisierungen.
2.2   In einer WHILE-Schleife über alle Eigenschaften der aktuellen Gruppe (das Array wird in der Schleife erweitert) werden für die aktuelle Eigenschaft alle Artikel eingelesen die noch nicht im Array der Artikel für diese Gruppe vorhanden sind.
2.2.1         In einer FOR-Schleife über alle Artikel, die zur aktuellen Eigenschaft eingelesen wurden, werden alle Eigenschaften, die noch nicht in dem Array mit den  Eigenschaften der aktuellen Gruppe sind, hinzugefügt und aus dem Array aller Eigenschaften entfernt.
2.3   Nach der WHILE-Schleife werden die Eigenschaften der aktuellen Gruppe (und eventuell auch der Artikel, die zu dieser Gruppe gehören) ausgegeben.

[schauan]

Hallöchen,

ich habe hier mal schnell eine Lösung mit 33 codezeilen (ohne Kommentarzeilen) und zwei Arrays programmiert und getestet. Mit den Doppelpunkten in der einen Schleife habe ich bisschen getrickst Wenn man das ausschreibt, sind es zwei Zeilen mehr.
Statt B9 könnte man die 9 noch flexibel gestalten, würde aber in der selben Zeile passieren.
Etwas zusätzliche Arbeit würde ggf. die Ausgabe machen, wenn die Dir nicht gefällt. Ich würde vermuten, dass Du eventuell neben jedem Artikel in Spalte C eine Gruppennummer haben willst. In dem Fall würde unten die Ausgabe entfallen und an zwei Stellen im Code könnte eine Ausgabe in die Zellen neben den Artikeln rein, oder man baut noch ein Array mit den Gruppennummern dazu, was man am Ende überträgt.

Code:

Sub Gruppen()
'variablendeklaration
'Integer
Dim iCnt1%, iCnt2%, iCnt3%, icnt4%
'Variant-Array
Dim arrDaten, arrStr
'Boolean
Dim boTreffer As Boolean
'Daten uebernehmen. Bei flexiblem Bereich noch Flexibilisieren :-)
arrDaten = Range("A2:B9").Value
'String-Array Dimensionieren. Hier werden Gruppen zusammengefasst
ReDim arrStr(0)
'ersten Eintrag uebernehmen
arrStr(0) = arrDaten(1, 1) & "," & arrDaten(1, 2)
'Schleife ueber weitere Daten
For iCnt1 = 2 To UBound(arrDaten)
  'Treffer-Variable zuruecksetzen
  boTreffer = False
  'Wenn aktuelle Nummer = der vorigen, dann
  If arrDaten(iCnt1, 1) = arrDaten(iCnt1 - 1, 1) Then
    'Eintrag in aktuelles Stringfeld uebernehmen
    arrStr(iCnt2) = arrStr(iCnt2) & "#" & arrDaten(iCnt1, 1) & "," & arrDaten(iCnt1, 2)
  'oder Wenn aktuelle Nummer ungleich der vorigen, dann
  Else
    'Schleifenzaehler 3 setzen
    iCnt3 = iCnt1
    'Schleife solange Nummer gleich ist
    Do While arrDaten(iCnt3, 1) = arrDaten(iCnt1, 1)
      'Wenn bereits Treffer, dann
      If boTreffer Then
        'Eintrag in aktuelles Stringfeld uebernehmen
        arrStr(icnt4) = arrStr(icnt4) & "#" & arrDaten(iCnt3, 1) & "," & arrDaten(iCnt3, 2)
      'oder Wenn kein Treffer, dann
      Else
        'Schleife uebergesamtes Stringarray
        For icnt4 = 0 To UBound(arrStr)
          'Wenn ein gleicher Eintrag gefunden, dann Treffervariable setzen, Do-Zahler zuruecksetzen und For verlassen
          If InStr(1, arrStr(icnt4), arrDaten(iCnt3, 2)) Then boTreffer = True: iCnt3 = iCnt1 - 1: Exit For
        'Ende Schleife uebergesamtes Stringarray
        Next
      'Ende Wenn bereits Treffer, dann
      End If
      'Do-Schleifenzaehler hochsetzen
      iCnt3 = iCnt3 + 1
      'Schleife verlassen, wenn Arrayende erreicht
      If iCnt3 > UBound(arrDaten) Then Exit Do
    'ende Schleife solange Nummer gleich ist
    Loop
    'Wenn bei Vergleich kein Treffer, dann
    If Not boTreffer Then
      'Arrayzaehler Stringarray hochsetzen
      iCnt2 = iCnt2 + 1
      'Array redimensionieren
      ReDim Preserve arrStr(iCnt2)
      'Daten uebernehmen
      arrStr(iCnt2) = arrStr(iCnt2) & "#" & arrDaten(iCnt1, 1) & "," & arrDaten(iCnt1, 2)
    'Ende Wenn bei Vergleich kein Treffer, dann
    End If
  'Ende Wenn aktuelle Nummer = der vorigen, dann
  End If
'Schleife ueber weitere Daten
Next
'Daten ab C2 eintragen
Range("C2").Resize(UBound(arrStr) + 1).Value = WorksheetFunction.Transpose(arrStr)
End Sub

[schauan]

Hallöchen,

hab mal noch 4 Zeilen dazugepackt, um Doppeleinträge zu vermeiden. Hatte heute früh gar nicht gesehen, dass 4E 2x drin war

Code:

Sub Gruppen()
'variablendeklaration
'Integer
Dim iCnt1%, iCnt2%, iCnt3%, icnt4%
'Variant-Array
Dim arrDaten, arrStr
'Boolean
Dim boTreffer As Boolean
'Daten uebernehmen. Bei flexiblem Bereich noch Flexibilisieren :-)
arrDaten = Range("A2:B9").Value
'String-Array Dimensionieren. Hier werden Gruppen zusammengefasst
ReDim arrStr(0)
'ersten Eintrag uebernehmen
arrStr(0) = arrDaten(1, 1) & "," & arrDaten(1, 2)
'Schleife ueber weitere Daten
For iCnt1 = 2 To UBound(arrDaten)
  'Treffer-Variable zuruecksetzen
  boTreffer = False
  'Wenn aktuelle Nummer = der vorigen, dann
  If arrDaten(iCnt1, 1) = arrDaten(iCnt1 - 1, 1) Then
    'Wenn Eintrag noch nicht vorhanden, dann
    If InStr(1, arrStr(iCnt2), arrDaten(iCnt1, 1) & "," & arrDaten(iCnt1, 2)) = 0 Then
      'Eintrag in aktuelles Stringfeld uebernehmen
      arrStr(iCnt2) = arrStr(iCnt2) & "#" & arrDaten(iCnt1, 1) & "," & arrDaten(iCnt1, 2)
    'Ende Wenn Eintrag noch nicht vorhanden, dann
    End If
  'oder Wenn aktuelle Nummer ungleich der vorigen, dann
  Else
    'Schleifenzaehler 3 setzen
    iCnt3 = iCnt1
    'Schleife solange Nummer gleich ist
    Do While arrDaten(iCnt3, 1) = arrDaten(iCnt1, 1)
      'Wenn bereits Treffer, dann
      If boTreffer Then
        'Wenn Eintrag noch nicht vorhanden, dann
        If InStr(1, arrStr(icnt4), arrDaten(iCnt3, 1) & "," & arrDaten(iCnt3, 2)) = 0 Then
          'Eintrag in aktuelles Stringfeld uebernehmen
          arrStr(icnt4) = arrStr(icnt4) & "#" & arrDaten(iCnt3, 1) & "," & arrDaten(iCnt3, 2)
        'Ende Wenn Eintrag noch nicht vorhanden, dann
        End If
      'oder Wenn kein Treffer, dann
      Else
        'Schleife uebergesamtes Stringarray
        For icnt4 = 0 To UBound(arrStr)
          'Wenn ein gleicher Eintrag gefunden, dann Treffervariable setzen, Do-Zahler zuruecksetzen und For verlassen
          If InStr(1, arrStr(icnt4), arrDaten(iCnt3, 2)) Then boTreffer = True: iCnt3 = iCnt1 - 1: Exit For
        'Ende Schleife uebergesamtes Stringarray
        Next
      'Ende Wenn bereits Treffer, dann
      End If
      'Do-Schleifenzaehler hochsetzen
      iCnt3 = iCnt3 + 1
      'Schleife verlassen, wenn Arrayende erreicht
      If iCnt3 > UBound(arrDaten) Then Exit Do
    'ende Schleife solange Nummer gleich ist
    Loop
    'Wenn bei Vergleich kein Treffer, dann
    If Not boTreffer Then
      'Arrayzaehler Stringarray hochsetzen
      iCnt2 = iCnt2 + 1
      'Array redimensionieren
      ReDim Preserve arrStr(iCnt2)
      'Daten uebernehmen
      arrStr(iCnt2) = arrDaten(iCnt1, 1) & "," & arrDaten(iCnt1, 2)
    'Ende Wenn bei Vergleich kein Treffer, dann
    End If
  'Ende Wenn aktuelle Nummer = der vorigen, dann
  End If
'Schleife ueber weitere Daten
Next
'Daten ab C2 eintragen
Range("C2").Resize(UBound(arrStr) + 1).Value = WorksheetFunction.Transpose(arrStr)
End Sub

[Ego]

Hallo Orlis,
da ich relativ neu in diesem Forum bin war mir nicht bewusst, dass die Fragesteller hier mit vollständig geschriebenen Programmen verwöhnt werden.

Der Vorschlag von Andre ist ja sehr viel kürzer als von mir geschätzt. Im ersten Überblick sehe drei Gründe, warum er die von mir erwarteten Schleifen nicht benötigt:
1 Er erwartet sortierte Daten.
2 Er speichert die Werte einer Gruppe nicht in zwei Arrays sondern in einem String für den er beim Suchen keine Schleife, sondern nur die Funktion Instr benötigt.
3. In der Ausgabe der Gruppen sind zwar die Kombinationen Artikel, Eigenschaft eindeutig, aber eine Eigenschaft kann mehrfach aufgelistet sein.

Teste doch mal das Programm mit deinen Daten und gebe eine Rückmeldung.

Falls du noch Änderungswünsche hast, solltest du zur Vermeidung von mehrfacher Änderung der Programmierung direkt noch einige Zusätzliche Informationen geben:
1 Wie sehen in deiner Anwendung die Artikel- und Eigenschaftskennungen aus? Ich nehme an, dass sie nicht aus einem Zeichen bestehen. (Insbesondere kann die Kennung einer Eigenschaft ein Teilstring einer Kennung einer anderen Eigenschaft sein? ZB: „A“ und „RAB“)
2 Nicht für die Programmierung, aber eventuell für die Art der Ausgabe könnten einige grob geschätzte Maximalwerte hilfreich sein.
Anzahl der Artikel; Anzahl der Eigenschaften; Anzahl der Paare; Anzahl der Eigenschaften pro Gruppe
3. Hast du vielleicht schon eigene Ideen wie die Gruppen ausgegeben werden sollen.