Hallo zusammen,
Ich komme bei einem Problem nicht weiter, evtl. weiß hier jemand wie das gehen könnte...
Ich habe eine Excel Liste mit 51 Spalten, in denen untereinander jeweils ca. 400 natürliche Zahlen (Bereich 1-1200) stehen.
Die Zahlen sind teils innerhalb einer Spalte teils auch innerhalb mehrerer Spalten doppelt/mehrfach vorhanden.
Von den 51 Spalten muss ich nun die 4 Spalten auswählen, deren Zahlen kombiniert die größtmögliche Anzahl unterschiedlicher Werte ergeben
Die Datei hab ich mal angehängt.
Vielen Dank vorab, vllt. weiß jemand wie sowas gehen könnte...
Theawry
Hallo Thea,
kann es sein, dass du Permutationen meinst?
Bei den Formeln für Kombinationen
a) wird davon ausgegangen, dass alle (n) Elemente der Grundmenge verschieden sind,
b) eine Anzahl für die Teilmengen benötigt (k),
c) die Vorgabe benötigt ob Wiederholungen erlaubt sind.
Oder sollen sogar Rechenoperartionen auf verschiedene Kombinationen angewand werden?
Zitat:deren Zahlen kombiniert die größtmögliche Anzahl unterschiedlicher Werte ergeben
Hallo,
nach einer ersten Prüfung stehen in allen Spalten unterschiedliche Werte, in den Zeilen systemtisch variiert zwischen 1 und 38 unterschiedlichen Werten.
Wenn ich die Frage richtig verstaanden habe, müßten (n über k: 4 über 51) Möglichkeiten getestet werden! Ziemlich viele!!!
Die unterschiedlichen Zahlen zweier Spalten kann man recht leicht berechnen, aber das ist wohl zu kurz gegriffen.
Wie wäre es mit weiteren Erklärungen, z.B. über den Sinn der Fragestellung?
mfg
Hallo,
hier ein Ansatz um die Unterschiede für 2 Spalten zu erfassen. Die Ergebnisse für den Vergleich der 1. Spalte mit allen anderen werden in das Sheet "iDic" geschrieben. Die Originaldaten sind im Sheet "Ori". Für die Spalte A und B ist es getestet:
Code:
Sub iDic()
Sheets("Ori").Activate
With CreateObject("scripting.dictionary")
For i = 1 To 1
For j = i + 1 To 51
For k = 1 To 375
Tx = Cells(k, i).Value
.Add Tx, 1
Next k
For k = 1 To 375
Tx = Cells(k, j).Value
If Not .exists(Tx) Then
.Add Tx, 0
Else
.Item(Tx) = 0
End If
Next k
Z = 0
For Each k In .keys
Z = Z + .Item(k)
Next k
Sheets("iDic").Cells(i, j) = Z + (.Count - 375)
.RemoveAll
Next j
Next i
End With
Beep
End Sub
So ganz trivial war die Erfassung der Unterschiede dann doch nicht. Aber einen "=sverweis()" in beide Richtungen zu rechnen, erschien noch komplexer bei so vielen Spalten.
Nur falls es möglich sein sollte die Fragestellung iterativ zu lösen (zuerst die 2 Spalten mit den größten Unterschieden, dann die Spalte mit den gr. Unterschieden zu (1 und 2), kann es ein Teil einer Lösung sein.
mfg
Hallo,
Zitat:So ganz trivial war die Erfassung der Unterschiede dann doch nicht.
da genügt doche eine einfache Formel:
Code:
{=SUMMENPRODUKT((A$1:A$375=MTRANS(B$1:B$375))*1)}
Das ist wie das Auswerten von Lottotipps!
Hier mal eine Auswertung mit Formeln:
[
attachment=9052]
@Bosko
bei Formeln bin ich noch stark verbesserungsfähig.
Ich hatte schon befürchtet, dass es so eine Formel geben könnte und vba wieder einmal das Nachsehen hat. Aber die Formel ergibt "325", das gesuchte Ergebnis ist aber "100". Wenn das "=" gegen "<>" ersetzt wird, ergibt die Formel "140300".
Da man die einzelnen Schritte bei vba besser testen kann, bleibe ich dabei.
mfg
@Fennek,
dass Du meine Formel nicht verstanden hast und nicht nachvollziehen kannst (da gäbe es die Formelauswertung!!!!!), kann ich Dir noch nachsehen. Dass Du das Ergebnis der Formel infrage stellst ist unverzeihlich. Es wäre ein leichtes gewesen, das Ergebnis zu überprüfen, was Du offensichtlich nicht gemacht hast:
| Arbeitsblatt mit dem Namen 'Tabelle2' |
| | A | B | C | D |
| 1 | 19 | 19 | 1 | 325 |
| 2 | 33 | 34 | 0 | |
| 3 | 48 | 48 | 1 | |
| 4 | 62 | 63 | 0 | |
| 5 | 77 | 77 | 1 | |
| 6 | 91 | 92 | 0 | |
| 7 | 106 | 106 | 1 | |
| 8 | 120 | 121 | 0 | |
| 9 | 135 | 135 | 1 | |
| 10 | 149 | 150 | 0 | |
| 11 | 164 | 164 | 1 | |
| Zelle | Formel |
| C1 | =ZÄHLENWENN(A:A;B1) |
| D1 | =SUMME(C:C) |
| Diese Tabelle wurde mit Tab2Html (v2.4.1) erstellt. ©Gerd alias Bamberg |
Es gibt also in Spalte A/B 325 Überinstimmungen. Ich zähle die Übereinstimmungen jeder einzelnen Spalte mit allen rechts davon liegenden Spalten (in der senkrechten!), und das für jede Spalte (horizontal). Dann suche ich mir die 3 Spalten mit der kleinsten Anzahl von Übereinstimmungen (in der Summe).
Hier könnte noch ein Fehler liegen, nicht rechnerisch, sondern gedanklich.
Gerade geprüft, passt!
Hallo,
es war keine Kritik, sondern die Wertschätzung für viel bessere Formelkenntnisse.
Nach meinem Verständnis müßte aber auch umgekehrt geprüft werden, also nicht nur
C1
=ZÄHLENWENN(A:A;B1)
D1
=SUMME(C:C)
sondern auch
e1: =zählenwenn(B:B, A1)
Dann ergibt es 2x 325 von möglichen 375 Übereinstimmungen, d.h. 2x 50 keine Übereinstimmung.
mfg
Hallo,
die Spalten mit den geringsten Übereinstimmungen haben doch die grösste Anzahl von unterschiedlichen Zahlen. Bei der dritten und vierten Spalte könnte es anders aussehen, aber das wird mit Formeln wohl eher nicht relisierbar sein, zumindest nicht ohne den Rechner in die Knie zu zwingen.
Zitat:Nach meinem Verständnis müßte aber auch umgekehrt geprüft werden
warum?
| Arbeitsblatt mit dem Namen 'Tabelle2' |
| | A | B | C | D | E | F |
| 1 | 19 | 19 | 1 | 1 | 325 | 325 |
| 2 | 33 | 34 | 0 | 1 | | |
| 3 | 48 | 48 | 1 | 1 | | |
| 4 | 62 | 63 | 0 | 1 | | |
| 5 | 77 | 77 | 1 | 1 | | |
| 6 | 91 | 92 | 0 | 1 | | |
| Zelle | Formel |
| C1 | =ZÄHLENWENN(A:A;B1) |
| D1 | =ZÄHLENWENN(B:B;A1) |
| E1 | =SUMME(C:C) |
| F1 | =SUMME(D:D) |
| Diese Tabelle wurde mit Tab2Html (v2.4.1) erstellt. ©Gerd alias Bamberg |
Wenn ich 325 Übereinstimmungen habe, habe ich 425 unterschiedliche Zahlen statt 650. Habe ich 90 Übereinstimmungen, dann sind das 660 verschiedene Zahlen.
Hallo Fennek,
so gross ist 51 über 4 nun auch wieder nicht (249.900).
Ich habe dein Programm einmal mit allen 2erKombinationen (1275) laufen lassen. Auf meinem Rechner hat das ca 14 Sekunden gedauert.
Wenn man alle Kombinationen damit rechnet, sollte die Rechenzeit ca 3 Stunden und 5 Minuten nicht überschreiten.
= Sekunden (14) * FaktorKombinationen (196) * Faktor der zu prüfenden Zahlen (2) * Faktor der mittleren Vergleiche pro Zahl (2).
Wenn ich dein Makro in zwei kleinen Punkten ändere ist es ca 10Mal so schnell. Die Rechenzeit für alle Kombinationen sollte dann kleiner als 20 Minuten sein.
1) Einmaliges Einlesen der Daten in ein Array und danach Speicherintern lesen. (Faktor 6)
2) Nutzen der Dictionary Funktionalität "intDummy = .Item(intWert)" das einen leeren Eintrag erzeugt wenn intWert noch nicht vorhanden ist. (Faktor ca 1,5)
Die zweite Änderung hat auch den Vorteil, dass in den Spalten Wiederholungen vorkommen dürfen.
Ich hab das Programm einmal mit allen dreierKombinationen laufen lassen. Erwartet hatte ich nach meiner Berechnung < 50 Sekunden und benötigt hat das Programm ca 27 Sekunden.
Wenn man dies wieder hochrechnet sollte die Berechnung aller viererKombinationen weniger als 10 Minuten dauern. Und das ist doch machbar.
Ich finde deine Idee über das Dictionary zu gehen sehr gut.
Bei noch grösseren Datenmengen könnte man über sortierte Spalten die vier Spalten auswählen. Der einmalige Mehraufwand die Spalten zu sortieren sollte dann schnell ausgeglichen werden, da ich dann nicht für jeden Wert im Mittel 2*AnzahlZeilen(hier750) sondern nur 5 Vergleiche durchführen muss.
Ergänzung: Die Berechnung aller viererKombinationen hat auf meinem Rechner 6 Minuten und 47 Sekunden gedauert.