Office: häufigste Zahlenkombination

Hallo, ich suche nach einer Lösung für folgendes Problem. Ich habe eine Tabelle mit drei Spalten. Jede Spalte hat elf Zeilen (Überschrift + 10 ganze Zahlen).

[table="width: 500, class: grid"]
[tr]
[td]A[/td]
[td]B[/td]
[td]C[/td]
[/tr]
[tr]
[td]01.01.2014[/td]
[td]01.02.2014[/td]
[td]01.03.2014[/td]
[/tr]
[tr]
[td]1[/td]
[td]15[/td]
[td]27[/td]
[/tr]
[tr]
[td]5[/td]
[td]3[/td]
[td]99[/td]
[/tr]
[tr]
[td]7[/td]
[td]5[/td]
[td]2[/td]
[/tr]
[tr]
[td]2[/td]
[td]33[/td]
[td]28[/td]
[/tr]
[tr]
[td]17[/td]
[td]55[/td]
[td]7[/td]
[/tr]
[tr]
[td]22[/td]
[td]28[/td]
[td]1[/td]
[/tr]
[tr]
[td]13[/td]
[td]24[/td]
[td]9[/td]
[/tr]
[tr]
[td]49[/td]
[td]2[/td]
[td]54[/td]
[/tr]
[tr]
[td]19[/td]
[td]18[/td]
[td]5[/td]
[/tr]
[tr]
[td]37[/td]
[td]16[/td]
[td]14[/td]
[/tr]

[/table]

Jede Zahl kommt pro Spalte nur einmal vor. Excel soll nun anhand einer Formel prüfen:

- gibt es eine vierer Kombination (pro Spalte) welche mehrfach vorkommt?
- wie oft kommt diese vor?
- wann kommt diese vor?

In diesem Beispiel müsste also ausgegeben werden, dass die Kombination 1, 2, 5, 7 zwei mal vorkommt. Am 01.01.2014 (Spalte A) und am 01.03.2014 (Spalte C).

Wie kann ich diese Problematik am einfachsten mit Excel lösen? Vielen Dank vorab.

 

Hallo!

Bei diesen Aufgabenstellungen ist VBA meist sinnvoller als Formeln.

Wenn sich deine Daten im Bereich A1:C11 befinden, dann sollte dieser Code dein Problem lösen:

Code:

Sub Test()
Dim a%, b%, c%, d%, s%, z%, Bereich As Range
'Spalten aufsteigend sortieren:
For s = 1 To 3
    For z = 2 To 11
        Cells(z, s + 4) = WorksheetFunction.Small(Range(Cells(2, s), Cells(11, s)), z - 1)
    Next z
    z = 2
    'Kombinationen ohne Wiederholung auflisten:
    Range("A1:C1").Copy Range("I1:K1")
    For a = 2 To 8
        For b = a + 1 To 9
            For c = b + 1 To 10
                For d = c + 1 To 11
                    Cells(z, s + 8) = Cells(a, s + 4) & "," & Cells(b, s + 4) & "," & Cells(c, s + 4) & "," & Cells(d, s + 4)
                    z = z + 1
                Next d
            Next c
        Next b
    Next a
Next s
'Einmalige Kombinationen löschen:
Set Bereich = Cells(2, 9).CurrentRegion
For s = 9 To 11
    For z = 211 To 2 Step -1
        If WorksheetFunction.CountIf(Bereich, Cells(z, s)) = 1 Then Cells(z, s).Delete Shift:=xlUp
    Next z
Next s
End Sub

In den Spalten E bis G werden die Spalten A bis C jeweils aufsteigend sortiert, da die Reihenfolge bei dir ja keine Rolle spielt.
In den Spalten I bis K werden zunächst sämtliche (pro Spalte 210) Kombinationen ohne Wiederholung aufgelistet.
Dann werden alle einmaligen Kombinationen gelöscht, so dass nur die mehrfachen Kombinationen übrig bleiben.

 

Moinsens!

schatzi hat ja schon eine Lösung in die Runde geworfen, aber da ich auch schon dran gebastelt hatte, schieb ich meine Lösung mal hinterher:

Ich geb schatzi in jedem Fall mal recht, daß dieses Problem mit reinem Formelwerk kaum noch zu lösen sein dürfte. Die Listen müssen zunächst sortiert werden, was mit kKleinste noch gerade eben so geht. Aber das Zusammenstellen der Kombinationen ist dann schon ziemlich unmöglich. Das Zählen der Kombinationen dann wiederum geht per Formel.

Ich komme zum gleichen Ergebnis wie schatzi, aber auf einem etwas anderem Weg, der allerdings auch etwas variabler ist. In meiner Version kann die Anzahl der Zeilen (Zahlen pro Kolonne), die Anzahl der Spalten und die Anzahl der Zahlen pro Kombination frei definiert werden, ohne daß die verschachtelten For..Next Schleifen angefaßt werden müssen. Zu dem Zweck encodiere ich quasi die Zahlen binär, laufe dann 0 bis 2^(n-1) durch und zähle die gesetzten Bits - bei 4 Bits wird die Kombination ausgegeben. Die zu setzenden Parameter (Konstanten im Source-Header) sind also:
Anzahl Spalten (3)
Anzahl Zahlen (10)
Kombinations-Zahlen (4)
(Da spätestens bei 64 Bit Ende ist, darf "Anzahl Zahlen (10)" entsprechend nicht größer werden. Um ungeplante Seiteneffekte zu vermeiden, würde ich nicht über 63 hinaus gehen. Aber 6 aus 49 gehen damit allemal... ;-) )

Gruß, Michael

[ .xls im .zip, da 143k zu groß für Upload ]