[schwierige Aufgabe] Zelleninhalt nur teilweise vergleichen

[Serpent Driver]

Hi Serpent Driver,


ich hatte dich heute morgen zwar das erste mal als Schlangen(linien)fahrer angesprochen, ich wollte dich aber keinesfalls beleidigen. Sollte meine eigentlich humorvoll gemeinte Anrede dich verletzt haben, bitte ich das zu entschuldigen. Ich hatte deinen Nickname lediglich ganz grob übersetzt:

[
Bild bitte so als Datei hochladen: Klick mich!
]

Samma mehr guat?   :15:

Ich hab das vom ersten Moment an als spaßigen Nicknamen verstanden. Es gibt also keinen Grund dafür, dass Du Dich entschuldigen musst.  Basst scho! :)


Btw. Der Serpent Driver kommt davon www.serpent.com

[RPP63]

Ihr dürft mich abstrafen!  :s
Ich glaube, ich bin #53
Das Ganze als Schlangenlinien zu bezeichnen, ist eine Beleidigung für jede rechtschaffene Schlange!
Dies ist ein unsäglicher Eiertanz, womit ich nichts gegen Vögel sagen möchte!

Gruß Ralf

[Ego]

Hallo Serpent Driver,

Erst einmal der Hinweis, wo man VBA-Code editiert:

Der VBA-Editor wird in Excel enweder durch "Alt F11" oder über ein Doppelklick auf das Feld "Code anzeigen" im Menu Entwiclertools aufgerufen.
Im linken Teil wird eine Baumstruktur der vorhandenen Module angezeigt. Durch ein  Doppelklick auf den Eintrag "Modul1" in dem sich meine Funktion befindet, wird die Funktion im Editor angezeigt und editierbar.


Jetzt einige Bemerkungen zur Rechenzeit:

Gast 123 hat ja darauf hingewiesen, dass bei sehr grossen Datenmengen die Berechnung der Felder sehr sehr zeitaufwendig ist.
Kannst du einmal die ungefähre Grösse der beiden Spalten in deinen endgültigen Dateien nennen?

In einer von dir hochgeladenen Beispieldatei waren ca 17.000 Suchzellen und > 60.000 Vergleichszellen. Da du irgendwann einmal eine Wert  der Vergleichszellen grösser als 100.000 genannt hast, habe ich im Beispiel die Vergleichszellen verdreifacht.

Der Zeittest auf meinem Rechner erfolgt über 17147 Suchzellen und 199.212 Vergleichszellen.
Die Funktion muss bei jedem Aufruf alle Vergleichszellen durchsuchen und benötigt dafür auf meinem Rechner jeweils ca eine Sekunde.
Bei der Anzahl der Suchzellen benötigt Excel für die gesamte Berechnung auf meinem Rechner ca 4 Stunden.

Die Funktion wird immer aufgerufen, wenn sich eine der Vergleichszellen ändert. Wenn du die Funlktion 17.000 mal kopierst  und danach nur eine Vergleichszelle änderst, kannst du auf meinem Rechner vier Stunden warten bis alle Zellen aktualisiert sind.
Daher sollte die Funktion erst dann auf  einen grösseren Bereich kopiert werden, wenn sich an den Vergleichszellen nichts mehr ändert. Aber auch dann dauert es die vier Stunden bis alle Zellen aktualisert sind.

Wenn du es wünscht kann ich dir auch eine Routine zur Verfügung stellen, die immer erst nach Aufruf über einen Command Button alle Zellen berechnet. Bei Änderungen der Such- und Vergleichszellen werden dann die Ergebnisse nicht direkt aktualisiert.
Da die Vergleichszellen in dieser Routine einmalig in den Arbeitsspeicher gelesen und dort ausgewertet werden, ist diese Routine vier mal so schnell wie die Funktion. Eine vollständige Berechnung des Beispiels würde auf meinem Rechner aber auch mehr als eine Stunde benötigen.

[Serpent Driver]

Ok, jetzt weiß ich das auch, wo man das editiert, danke!

Wegen der Rechenzeit hatte ich schon erwähnt, dass ich das tolerieren könnte. Im Zweifelsfalle läuft die Kiste eben nachts durch. Wenn es keine all zu großen Bemühungen sind, wäre ich aber auc h für eine Lösung dankbar mit der es schneller geht. Die Daten in der Spalte A sind weitestgehend endgültig. Da erwarte ich keine Steigerung. In der Spalte C siehts dann schon anders aus. Gegenwärtig bin ich bei etwas mehr als 160K, jedoch dürften noch mal 40k hinzukommen.

[Serpent Driver]

Hallo Serpent Driver,

hast du die Erweiterung der Funktion mit Berücksichtigung von leeren Zellen gelesen un d angepasst?

Wenn ja, könnte es sein dass ein Inhalt einer Zelle nicht dem Format -zwei Texte mit Leerzeichen getrennt- entspricht?

Hier nochmal eine Erweiterung des codes, der nicht nur leere Zellen, sondern auch Zellen ohne Trennung durch ein  Leerzeichen, ignoriert.
(eingefügt: If Ubound(varVergleich)>0 then....EndIf)

Code:

Option Explicit
Function SerpentSuche(rngSuche As Range, rngSuchFeld As Range) As String

Dim varSuche As Variant
Dim varVergleich As Variant
Dim strAusgabe As String
Dim rngZelle As Range

strAusgabe = ""
varSuche = Split(rngSuche)

For Each rngZelle In rngSuchFeld
    If rngZelle <> "" Then
        varVergleich = Split(rngZelle)
        If UBound(varVergleich) > 0 Then
            If varVergleich(1) = varSuche(1) Then
                strAusgabe = strAusgabe & varVergleich(0) & " "
            End If
        End If
    End If
Next rngZelle

SerpentSuche = strAusgabe
End Function

Wichtig! Die Funktion liefert nur dann das richtige Ergebnis, wenn die Werte in Spalte A und C folgenden Aufbau haben:

ein Text(oder Ziffern ohne Leerzeichen) gefolgt von einem Leerzeichen gefolgt von einem Text(ohne Leerzeichen).

Ich erkläre hiermit feierlich die Aufgabe als gelöst!

Viel Eiertanz und ja auch ein bisschen Schlangenlinienfahren waren notwendig, aber einfach kanns ja jeder, oder? ::) Nachdem ich den geänderten Code eingefügt hatte, wurde alles wie gewünscht ausgegeben. Ein großes Lob und einen Knicks vor so viel Unterstützung und auch an dieses Forum! Hier wird einem wirklich geholfen! Und an alle Kritiker sei gesagt:"Ich hab solche Schlangenlinienaufgaben nicht jeden Tag!" :)

[Ego]

Hallo Serpent Driver,


in der Anlage die Datei mit einer vollständigen Berechnung über einen CommandButton: "SerpentSuche2" (Achtung: lange Laufzeit bei grossen Bereichen).

Wichtig:

Einem Programm muss mitgeteilt werden für welchen Bereich es die Auswertung durchführen soll.
In der Funktion konnte man die Bereiche über die Parameter mitgeben.  Bei einem Aufruf eines Programms über ein Button muss man hierfür einen anderen Weg finden.
Da ich nicht gerne die Bereiche fest in das Programm eintrage (bei einer Erweiterung oder Verschiebung der Bereiche müste das Programm immer geändert werden), übergebe ich die auszuwertenden Bereiche über Namen (= "benannte Bereiche") .

Ich habe in der Datei drei Namen festglegt:
rngSuche
rngVergleich und
rngStartAusgabe.

In dem Beispiel habe ich die Such- und Vergleichsbereiche auf 200 bzw. 20000 Zellen begrenzt (Laufzeit auf meinem Rechner ca . 5 Sekunden).
Über das Menu "Formeln" und das Feld "Namensmanager" kannst du dir die vorhandenen Namen anzeigen lassen. Nach der Auswahl des gewünschten Namens und dem Klicken auf den Button "Bearbeiten", kannst du unter "Bezieht sich auf" den Bereich anpassen.

Die Funktion SerpentSuche ist auch noch vorhanden. Wenn du also für eine Suchzelle einmal schnell die Ergebnisse sehen willst, kannst du sie direkt eintragen (oder aus Zelle D5 in die gewünschte Zelle in Spalte B kopieren). Sie wird dann bei der nächsten Berechnung über den Button überschrieben.