Dieses Forum nutzt Cookies
Dieses Forum verwendet Cookies, um deine Login-Informationen zu speichern, wenn du registriert bist, und deinen letzten Besuch, wenn du es nicht bist. Cookies sind kleine Textdokumente, die auf deinem Computer gespeichert werden. Die von diesem Forum gesetzten Cookies werden nur auf dieser Website verwendet und stellen kein Sicherheitsrisiko dar. Cookies aus diesem Forum speichern auch die spezifischen Themen, die du gelesen hast und wann du zum letzten Mal gelesen hast. Bitte bestätige, ob du diese Cookies akzeptierst oder ablehnst.

Ein Cookie wird in deinem Browser unabhängig von der Wahl gespeichert, um zu verhindern, dass dir diese Frage erneut gestellt wird. Du kannst deine Cookie-Einstellungen jederzeit über den Link in der Fußzeile ändern.

4 Spalten mit geringster Schnittmenge an Werten auswählen
#1
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


Angehängte Dateien
.xlsx   Beispiel.xlsx (Größe: 120,93 KB / Downloads: 9)
Antworten Top
#2
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
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.





Antworten Top
#3
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
Antworten Top
#4
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
Antworten Top
#5
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:


.xlsx   Uebereinstimmungen.xlsx (Größe: 193,21 KB / Downloads: 4)
Gruß

Edgar

Meine Antworten sind freiwillig und ohne Gewähr!
Über Rückmeldungen würde ich mich freuen.
Antworten Top
#6
@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
Antworten Top
#7
@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'
 ABCD
119191325
233340 
348481 
462630 
577771 
691920 
71061061 
81201210 
91351351 
101491500 
111641641 

ZelleFormel
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!
Gruß

Edgar

Meine Antworten sind freiwillig und ohne Gewähr!
Über Rückmeldungen würde ich mich freuen.
Antworten Top
#8
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
Antworten Top
#9
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'
 ABCDEF
1191911325325
2333401  
3484811  
4626301  
5777711  
6919201  

ZelleFormel
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.
Gruß

Edgar

Meine Antworten sind freiwillig und ohne Gewähr!
Über Rückmeldungen würde ich mich freuen.
Antworten Top
#10
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.
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.





Antworten Top


Gehe zu:


Benutzer, die gerade dieses Thema anschauen: 1 Gast/Gäste