Live-Forum - Die aktuellen Beiträge
Anzeige
Archiv - Navigation
824to828
Aktuelles Verzeichnis
Verzeichnis Index
Übersicht Verzeichnisse
Vorheriger Thread
Rückwärts Blättern
Nächster Thread
Vorwärts blättern
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
824to828
824to828
Aktuelles Verzeichnis
Verzeichnis Index
Verzeichnis Index
Übersicht Verzeichnisse
Inhaltsverzeichnis

Sverweis dauert zu lange

Sverweis dauert zu lange
07.12.2006 09:42:42
Melanie
Hi alle zusammen,
ich bräuchte noch mal Eure Schützenhilfe....
ich muss monatlich eine aktuelle Materialstammliste erstellen, die aus 52000 Zeilen und 70 Spalten besteht, wovon ca. 30 Spalten mit diversen S-Verweisen aus wiederum 10 anderen Dateien gefüllt werden. UND DAS DAUERT STUNDEN!!! Ich hatte mir damals ein netten kleinen Anfänger-Code geschrieben, das klappt auch alles, aber es dauert halt EWIG.
Zum Jahreswechsel stehen eh Veränderungen an dieser Datei an und da dachte ich mir, vielleicht kann man das ja auch schneller hinkriegen. Diese Dinge mit Screenupdating und Displayalerts ausschalten und so habe ich natürlich schon gemacht.
Sub TabelleAusfüllen2006neu()
Dim intRow As Long
Dim wkbQuelle1 As Workbook
Dim wkbQuelle2 As Workbook
Dim wkbQuelle3 As Workbook
Dim wkbQuelle4 As Workbook
Dim wkbQuelle5 As Workbook
Dim wkbQuelle6 As Workbook
Dim wkbQuelle7 As Workbook
Dim wbkQuelle8 As Workbook
Dim wkbQuelle9 As Workbook
Dim wkbQuelle10 As Workbook
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Set wkbQuelle1 = Workbooks.Open("M:\et-p\Materialstamm ET\Disponenten.xls")
Set wkbQuelle2 = Workbooks.Open("M:\et-p\Materialstamm ET\VStati-aktuell.xls")
Set wkbQuelle3 = Workbooks.Open("M:\et-p\Materialstamm ET\Verbräuche\(Monats-)Verbräuche 2004_neu.xls")
Set wkbQuelle4 = Workbooks.Open("M:\Werk7WorkingCapital\Projektcontrolling\Gesamtbestand-aktuell.xls")
Set wkbQuelle5 = Workbooks.Open("M:\et-p\Materialstamm ET\Verbräuche\(Monats-)Verbräuche 2005.xls")
Set wkbQuelle6 = Workbooks.Open("M:\et-p\Materialstamm ET\Verbräuche\(Monats-)Verbräuche 2006.xls")
Set wkbQuelle7 = Workbooks.Open("M:\et-p\Materialstamm ET\ET-Auslaufdaten.xls")
Set wkbQuelle8 = Workbooks.Open("M:\et-p\Schrott\Schrott_2006\Schrottliste 2006-die aktuelle.xls")
Set wkbQuelle9 = Workbooks.Open("M:\et-p\Materialstamm ET\Verwendung.xls")
Set wkbQuelle10 = Workbooks.Open("M:\erstanlagedatum.xls")
Workbooks("matstwerk7.xls").Sheets("matstwerk7").Activate
intRow = Cells(Rows.Count, 1).End(xlUp).Row
Range("F2:F" & intRow).FormulaR1C1 = "=IF(ISERROR(VLOOKUP(C[1],[Disponenten.xls]Tabelle1!C1:C2,2,0)), ""nicht Werk 7"", VLOOKUP(C[1],[Disponenten.xls]Tabelle1!C1:C2,2,0))"
Columns("F:F").Copy
Columns("F:F").PasteSpecial Paste:=xlPasteValues
' wenn Disponent = leer kein Disponent
Range("J2:J" & intRow).FormulaR1C1 = "=IF(ISERROR(VLOOKUP(C[-9],'[VStati-aktuell.xls]Export'!C1:C6,6,0)), ""-"", VLOOKUP(C[-9],'[VStati-aktuell.xls]Export'!C1:C6,6,0))"
Columns("J:J").Copy
Columns("J:J").PasteSpecial Paste:=xlPasteValues
Range("K2:K" & intRow).FormulaR1C1 = "=IF(ISERROR(VLOOKUP(C[-10],'[VStati-aktuell.xls]TG'!C1:C6,6,0)), ""-"",VLOOKUP(C[-10],'[VStati-aktuell.xls]TG'!C1:C6,6,0)) "
Columns("K:K").Copy
Columns("K:K").PasteSpecial Paste:=xlPasteValues
Range("N2:N" & intRow).FormulaR1C1 = "=IF(ISERROR(VLOOKUP(C[-13],'[(Monats-)Verbräuche 2004_neu.xls]Verbrauch2004'!C1:C3,3,0)), ""-"", VLOOKUP(C[-13],'[(Monats-)Verbräuche 2004_neu.xls]Verbrauch2004'!C1:C3,3,0))"
Columns("N:N").Copy
Columns("N:N").PasteSpecial Paste:=xlPasteValues
Range("O2:O" & intRow).FormulaR1C1 = "=IF(ISERROR(VLOOKUP(C[-14],'[(Monats-)Verbräuche 2005.xls]verbrauch2005'!C1:C4,4,0)), ""-"", VLOOKUP(C[-14],'[(Monats-)Verbräuche 2005.xls]verbrauch2005'!C1:C4,4,0))"
Columns("O:O").Copy
Columns("O:O").PasteSpecial Paste:=xlPasteValues
Range("P2:P" & intRow).FormulaR1C1 = "=IF(ISERROR(VLOOKUP(C[-15],'[(Monats-)Verbräuche 2006.xls]verbrauch2006'!C1:C4,4,0)), ""-"", VLOOKUP(C[-15],'[(Monats-)Verbräuche 2006.xls]verbrauch2006'!C1:C4,4,0))"
Columns("P:P").Copy
Columns("P:P").PasteSpecial Paste:=xlPasteValues
Range("Q2:Q59000").FormulaR1C1 = "=IF(ISERROR(VLOOKUP(C[-16],'[Gesamtbestand-aktuell.xls]Gesamtbestand ohne Sonderläger'!C1:C16,16,0)), 0, VLOOKUP(C[-16],'[Gesamtbestand-aktuell.xls]Gesamtbestand ohne Sonderläger'!C1:C16,16,0))"
Columns("Q:Q").Copy
Columns("Q:Q").PasteSpecial Paste:=xlPasteValues
Range("BM2:BM59000").FormulaR1C1 = "=IF(ISERROR(VLOOKUP(C[-64],'[Gesamtbestand-aktuell.xls]5000'!C1:C16,12,0)), 0, VLOOKUP(C[-64],'[Gesamtbestand-aktuell.xls]5000'!C1:C16,12,0))"
Columns("Q:Q").Copy
Columns("Q:Q").PasteSpecial Paste:=xlPasteValues
usw. usw.
dann folgen noch haufenweise Berechnungen, ich wollte das nur mal beispielhaft zeigen, wie ich an die Daten komme
FRAGE:
mir hat der Josef Ehrensberger (nochmals meinen allerherzlichsten DANK!!!) mal mit einem genialen Code geholfen, in dem Arrays eingelesen wurden - aber das habe ich mit meinen Anfänger-Kenntnissen nicht mal ansatzweise durchblickt.
Könnte man diese S-Verweise durch Arrays "schneller" machen???
Mir würde auf meinen konkreten Fall bezogen ein Beispiel vermutlich ausreichen, um das auf die 10 anderen Dateien zu beziehen.
Wäre super freundlich....
Gruß,
Melanie

12
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: Sverweis dauert zu lange
07.12.2006 10:08:41
ingUR
Hallo, Melanie,
das Einschreiben von Zellenformeln und das mögliche Berechnen mit Zelleninhalte, deren Ergebnise in Zellen geschrieben werden, kann durchaus eine erheblichen Teil der Zeit benötigen, die das Programm zur Aktuallisierung benötigt.
Als erste Maßnahme könntest Du das automatische Neuberechnen der Tabellen unterdrüccken, welches bei jedem eintrag einer Zellenformel erfolgt. Zusätzlich kannst Du auch gleich das Aktuallisieren des Bilschirmes ausschalten:
Dim prevScreenUpdating As Boolean, _
prevCalculation As Integer
prevCalculation = Application.Calculation
Application.Calculation = xlCalculationManual
prevScreenUpdating = Application.ScreenUpdating
Application.ScreenUpdating = False
... Operationen
Application.ScreenUpdating = prevScreenUpdating
Application.Calculation = prevCalculation
Um jedoch nun die Vorteile von Arrayvariablen im Programm zu nutzen, müßte eine Analsys der Programmfunktion erfolgen. Wenn Du vorwiegend SVerweis-Funktionen damit ersetzen möchtest, so ist es mit dem Einlesen von Daten in eine Array-Variable
Sub ReadArray()
Dim myArray
myArray = Range("A1:B12")
End Sub
nicht getan, da nun das Suchen und die Indexberechnung, im Programm selbst gestaltet werden muß.
Gru0,
Uwe
Anzeige
AW: Sverweis dauert zu lange
07.12.2006 12:14:28
Melanie
Hi Uwe,
danke schon mal für Deine Mühe. Ich will das gerne testweise (über Nacht ;-) ) mal ausprobieren, wenn ich die Zellberechnung noch zusätzlich ausschalte - aber: bringt das wirklich VIEL Zeitersparnis???
Es ist wirklich so, dass ich einzig und allein den S-Verweis mit dem Array umgehen will. Wie sähe das denn im Code aus, wenn ich z.B. den Datenbereich einer Datei von Spalte A-D einlese und er immer den passenden Wert der Spalte D in meine Ursprungsdatei (Spalte O) schreiben soll (s. SVerweis-Formel)?
Range("O2:O" & intRow).FormulaR1C1 = "=IF(ISERROR(VLOOKUP(C[-14],'[(Monats-)Verbräuche 2005.xls]verbrauch2005'!C1:C4,4,0)), ""-"", VLOOKUP(C[-14],'[(Monats-)Verbräuche 2005.xls]verbrauch2005'!C1:C4,4,0))"
Könntest Du mir dabei helfen???
Gruß,
Melanie
Anzeige
AW: Sverweis dauert zu lange
07.12.2006 13:00:34
ingUR
Hallo, Melanie,
der geschickte Einsatz der SVerweis-Formel ist wohl unter dem Gesichtspunkt erfolgt, nicht einen besonderen Suchalgorihmus in einem Datenarray zu entwickeln. In der Regel sollte dies auch, so wie es umgesetzt wurde, die bessere Möglichkeit sein, denn die Zeitdauer des Suchen in einem Feld hängt von den Suchkriterien und dem Datentyp des Suchschlüssels ab; daher bietet sich als erste Möglichkeit die Nutzung der eingebauten Funktionalitäten an, da sie in der Regel schnellere Ausführungszeiten aufweisen, als nutzerdefinierte VBA-Routinen.
Jedoch bei Zeilenzahlen bis zu 59000 wäre es wohl Wert zu untersuchen, ob es einen geeigneteren Ablauf geben kann (Vorsortieren, Indexfeld).
Aus dem Programmteil kann ich nicht recht erkennen, was eigentlich wie dort miteinander verknüpft werden soll bzw. in Beziehung steht.
Ist es möglich, eine abgespeckte Version der Mappen (Daten und Mappenanzahl)vorzustellen, so dass man alle notwendigen Schritte erkennen, bescheiben und überprüfen kann?
Zudem ist es auch notwendig zu wissen, ob Zwischenrechnungen erforderlich sind, deren Ergebnisse Einfluß auf nachfolgende Rechnungen des weiteren Programmablaufs haben.
Sofern keine Lösung gefunden wird (ich markiere die Frage daher als noch offen), versuche ich mich nach meinen Tagesarbeiten an einem Konzept.
Gruß,
Uwe
Anzeige
AW: Sverweis dauert zu lange
07.12.2006 18:09:20
Melanie
Hui,
da muss "man" (das bin ICH) "sich" (das bist DU, seid IHR) ja mehr Arbeit machen als ich dachte. Meine Vorstellung war, da ich VBA-mäßig viel abgucken muss und mit meinen Umschreibversuchen nicht immer erfolgreich bin, dass ich eine Problembeschreibung abgebe (z.B. "aus anderer Arbeitsmappe die 4. Spalte in die aktuelle Arbeitsmappe schreiben") und dann kann ich anhand dieses Beispiels mir die anderen Spalten zurechtstricken. Dass das jetzt auch noch Einfluss auf weitere Berechnungen nimmt wusste ich nicht.
Wie gesagt: der Trümmer hat ungefähr 70 Spalten und hat 25-30 S-Verweise aus 10 anderen Dateien. Mit abgespeckter Version ist das daher ziemlich schwierig. Aber ich kann morgen gerne zumindest diese beiden Beispielarbeitsmappen mal hochladen...
DANKE jedenfalls für Deine Mühe!!!
Gruß, Melanie
Anzeige
AW: Sverweis dauert zu lange
08.12.2006 04:34:15
ingUR
Hallo, Melanie,
nicht lag es in meiner Absicht, die Probleme zu vergrößern, als ich vom Gesamtüberblick schrieb, der notwendig ist, um abschätzen zu können, an welchen Stellen sich dass Abschalten der automatischen Neuberechnung unzulässig auswirken könnte. Es werden also nicht die Rechenansätze selber beeinflußt oder infolge des anvisierten Ersatzes der SVERWEIS-Zellenfunktion zu verändern sein, da ist Deine Vorstellung im Wesentlichen schon korrekt.
Es geht mir, da ich bisher nicht übersehen kann, wie Diene Dateienn aufgebaut sind und welche Operationen sonst noch durchgeführt werden (Du hast im ersten Beitrag von weiteren Berechnungen geschreiben) im wesentlichen um die Datentypen der involvierten Zellenbereiche (A?X1?:D?X2?), insbesondere des Suchschlüssels, denn hier entscheidet sich, ob man durch gezieltes Einlesen unter Beachtung der Datenrypen, bereits Ausführungszeiten bei der Zuordnung verringern kann, denn das Anlegen von Daten des Typs Variant, bedingt bereits eine Verlängerung an Rechenzeit.
Möglicherweise stecken jedoch auch noch andere "Zeitfresser" in anderen Zellenoperationen, daher mein Hinweis auf die Beachtung des Gesamtwerks.
Um also Gedankenspiele über die Art der Funktion zu unternehmen, die die Zellenformel SVERWEIS durch die Suche innerhalb eines Datenfeldes zu ersetzen soll, ist es notwendig, die Bedingungen bei der Suche zu kennen. Hieraus kann dann durchaus versucht werden eine optimale Ersatzfunktion zu erstellen, die die Spalten füllt.
Notwendig wäre, nach meinem jetzigen Überblick, eine Datenauszug oder eine Beispieldatenreihe aus einer "Quelldatei" und der Aufbau der Zieldatei mit denen die wesentlichen Operatioen nachvollzogen werden können.
Gruß,
Uwe
P.S.
Werde erst gegen Abend dazu kommen, Deine Antworten zu lesen.
Anzeige
AW: Sverweis dauert zu lange
08.12.2006 14:12:45
Melanie
Hi Uwe,
ich habe hier nun die abgespeckte Version meiner Liste hochgeladen und die Felder je nach unterschiedlicher "Behandlung" farblich markiert: hellgelb für alle Felder, die ich mit dem Download der Datei bekomme, knallgelb für alles was ich mit S-Verweisen reinziehe und orange für alles was ich ausrechne. Meistenteils erfolgen die Berechnungen per Makro, nur ganz selten stehen Formeln in den Feldern. Die ganzen letzten Spalten, die orange markiert sind, sollen mir ab dem kommenden Jahr auswerten, ob alle Materialstammdaten in sich logisch sind. Diese Berechnungen muss ich noch mit einer
Schleife in den bestehenden Code einbauen oder ich schreibe einen Extra-Code, der nur die Berechnungen vornimmt (s. Modul 2). Ich weiss, dass es für die Berechnung sicherlich elegantere Methoden gibt - bitte vor Lachen nicht vom Stuhl kippen!!!
https://www.herber.de/bbs/user/38830.xls Materialstamm

Die Datei https://www.herber.de/bbs/user/38831.xls wurde aus Datenschutzgründen gelöscht

zugehörige Disponentennamen (wird ich Spalte 6 eingefügt, Kriterium ist Spalte 7)
Das mit den Disponentennamen ist der simpelste S-Verweis, weil ich hier nur ca. 50 Disponentennummern habe. Überlicherweise haben die anderen Dateien (z.B. die Verbräuche der Artikel oder der aktuelle Bestand) ca. 30-40000 Zeilen. Alle Zellformate sollen so übernommen werden, wie sie in den Ursprungsdateien sind: Text = Text, Zahl = Zahl und Datum = Datum (weil ich sonst Probleme mit der Berechnung kriege, wenn auf einmal eine 0 eine "0" ist).
Dann danke ich Dir noch mal für Deine Hilfe und wünsche ein schönes Wochenende!!!
Gruß,
Melanie
Anzeige
AW: Sverweis dauert zu lange
09.12.2006 09:04:28
ingUR
Hallo, Melanie,
Deine Dateine habe ich heruntergeladen, und ich werde sie mir vermutlich erst am Sonntag anschauen können.
Mit Deinen Erläuterungen werde ich wohl einen Überblick über die zu erledigenden Arbeiten erhalten.
Ob jedoch Deine Aussage «Das mit den Disponentennamen ist der simpelste S-Verweis [...]. Überlicherweise haben die anderen Dateien (z.B. die Verbräuche der Artikel oder der aktuelle Bestand)», Zusatzfragen aufwerfen wird, wenn es darum gehen soll, die SVERWEIS-Funktion durch eine Suchfunktion in einer Arrayvariablen zu ersetzen, wird sich zeigen, denn einen wesentlichen Zeitgewinn wird mann dann erzielen, wenn eine sortierte Liste vorliegt, da in dieser, ähnlich wie in einem Lexikon, die Eintragstelle durch fortgesetztes Teilen der Bereich, in denen gesucht wird, ermittelt wird:
START:
Wähle Mittelstelle aus Bereich ANFANG bis ENDE,
- WENN Suchwert steht an der Mittelstelle
--- DANN Indexwert=Zeile=Suchstellennummer gefunden : EXIT IF
--- SONST
----- WENN Suchwert ist kleiner als Wert an der Mittelstelle,
-------- DANN suche erneut in Teil ANFANG bis ENDE=Mittelstelle
-------- SONST suche erneut in Teil Anfang=Mittelstelle bis ENDE
GEHE ZUM START
Konntest Du nachvollziehen, ob meine Hinweise in meinem Beitrag vom Donnerstag, dem 07.12.2006 um 13:25:23, eine meßbare Kürzung der Ausführungszeit mit sich brachte?
Gruß,
Uwe
Anzeige
AW: Sverweis dauert zu lange
10.12.2006 18:47:42
ingUR
Hallo, Melanie,
bevor ich Deine bestehendes, funktionierendes System analysiere und Umbauvorschläge unterbreite - so mir sie den einfallen -, möchte ich Dich Doch bitten, folgende Untersuchungen durchzuführen und auf folgende Fragen antworten zu geben:
  1. In der Arbeitsmappe Materialstamm Tabelle Matstwerk7 ist ein AutoFilter in der Zeile 6 eingebaut. Ist dieser Autofilter auch bei der Datenaktuallisierung eingestellt? Wenn "ja", wie verändert sich das Ausführungszeit verhalten bei ausgeschaltetem Autofilter (ggf. Programmzeilen einbauen Prozedur AutoFilterOnOff); s. u.?
  2. Welchen Effekt haben die Anweisungen zum Setzen vor einen jeden Copy-Befehl und zum Ausschalten nach einen jeden Paste-Befehl der automatischen Neuberechnung?

Dein Test zur Verwendung eines Datenfeldes, kann so wie Du dieses dan verweendest nicht funktionoeren, da Matche als zweiten Parameter ein Range-Objekt erfordert. Das ist aber dann gerade wieder die gleiche Anweisung, als wenn Du den Range-Bezug direkt als Parameter übergibst.
Ohne es durchgreifend geprüft und optimiert zu haben, habe ich unten eine Funktion vorgestellt, an der Du mit der ebenfalls veröffentlichten Sub-Prozedur Du das Wesen der Such in einem sortierten Feld nachvollziehen kannst. Die Sub-Prozedur bezieht sich auf die Tabelle Matstwer7 der Arbeitsmappe Materialstammdaten, die Funktion gilt allgemein für sortierte Variablenlisten mit Daten von Datentyp Long (für andere Datentypen, die ebenfalls sortiert vorliegen müssen, wären entsprechende Abänderungen auf den Datentyp erforderliche).
Mit der Funktion wird z.B. bereits ein Element, das an der 20. Zelle in der Liste steht, nach 7 Vergleichen gefunden und nicht erst nach 20, wenn eine lineare Suchrichtung programmiert ist! Bei zunehmender Listenlänge wird der Effekt noch gravierender, so dass sich das interne sortieren der eingelesenen Liste (des Datenenfelds) lohnt, wenn mehrfaches Suchen in der Liste erfolgt.
Soweit der Eindruck nach dem Ersten Einblick, ohne dass ich bisher den eigentlichen Programm-Code analysiert habe.
Bisheriges Fazit:
Bevor hier in ein bestehendes System Veränderung vorgenommen werden, sollten m.E. alle Konsequenzen überschaubar sein und bedacht werden, um am Ende es nicht mit einem "Flickenwerk" von Programm zu tum zu haben, das nicht mehr gut zu bearbeiten ist, denn ich vermute einmal, dass es sich hier um eine kommerziell genutzte Lösung handeln soll.
Ich empfehle daher nach einer Möglichkeit Ausschau zu halten, bei der eine Agenda für das Gesamtprojekt erarbeitet werden kann (Direktkontakt zum rrojektbezogenen Gedankenaustausch wäre wohl von Vorteil und Nutzen) und die einzelen Arbeitsschritte im Gesamtzusammenhang verfolgt werden können.
Gruß,
Uwe


'Prozedur AutoFilterOnOff
Dim IsAutoFilter As Boolean
If ActiveSheet.AutoFilterMode Then
Rows("6:6").Autofilter
IsAutoFilter = True
End If
If IsAutoFilter And ActiveSheet.AutoFilterMode Then
Rows("6:6").Autofilter
IsAutoFilter = False
End If



Option Explicit
Const HEADERROWS = 6
'Sub Durchsuchen()
'   Dim arr As Variant
'   Dim iRow As Integer
'   arr = Range("A7:A16")
'   iRow = Application.Match(20033, arr, 0)  <<<< arr muß ein Range-Objekt sein(!), und
'                                                 dann ist es aber "SVERWEIS"
'   If Err > 0 Then
'      MsgBox "Nicht gefunden"
'   Else
'      MsgBox "An " & iRow & " .Stelle gefunden"
'   End If
'End Sub
Function lngMyMatch(Wert As Long, Liste() As Long, Optional Typ = 0) As Long
Dim m0 As Long, m1 As Long, mm As Long, mWert As Long
Dim n As Long
m0 = 0
m1 = UBound(Liste)
mm = (m1 + m0) / 2
While m1 > m0
n = n + 1
mWert = Liste(mm)
If Wert = mWert Then
lngMyMatch = mm
MsgBox "Gefunden nach " & n & " Vergleichen an Platz " & lngMyMatch
Exit Function
End If
If Wert < mWert Then
m1 = mm
Else
m0 = mm
End If
mm = (m1 + m0) / 2
Wend
If Wert = Liste(mm) Then
lngMyMatch = mm
Else
lngMyMatch = -1
End If
MsgBox "Gefunden nach " & n & " Vergleichen an Platz " & lngMyMatch
End Function
Sub SuchenTest()
Dim maxItems As Long, lngArr() As Long
Dim iRow As Long
maxItems = Cells(Rows.Count, 1).End(xlUp).Row - HEADERROWS
ReDim lngArr(maxItems)
For iRow = HEADERROWS + 1 To maxItems - 1
lngArr(iRow - (HEADERROWS + 1)) = CLng(Cells(iRow, 1).Value)
Next iRow
iRow = lngMyMatch(247145, lngArr, 0) + HEADERROWS + 1
If iRow < 0 Then
MsgBox "Nicht gefunden"
Else
MsgBox "An " & iRow & " .Stelle gefunden"
End If
End Sub





Anzeige
AW: P.S. zum 'schnellen" Suchen ...
10.12.2006 19:54:19
ingUR
Hallo, Melanie,
in meiner Beschreibung zum vermeintlichen Zeitgewinn eines Suchalgorithmus, der nur jeweils das mittlere Elemnt einer verbleibenden Datenmenge untersucht, habe ich ein wenig nach "Schönredereimanier" den besonderen Aspekt hervorgehoben, ohne jedoch gleich daneben auf den Nachteil hinzuweisen.
Doch zuerst muß korrikert werden, dass das 20.Elemnt mit 7 Vergleichen gefunden wird, denn ich vergaß das Offset für die Kopfzeilenm das in Deiner Tabelle sechs Zeilen beträgt. Somit also: das 13. Element wird mit sieben Vergleichen gefunden.
Nun ist es aber so, dass die lineare Suche mit folgendem Programmcode gestalltet werden kann:
while Liste(i) < Wert: i=i+1 :wend
iRow=i-1
Die Anzahl der "Organisationsbefehle" um den Vergleich sind natürlich erheblich geringer, so dass sich die vorgestellte Methode erst ab einer bestimmten Anzahl der Listenelemente lohnt.
Gruß,
Uwe
Anzeige
AW: Sverweis dauert zu lange
11.12.2006 08:58:21
ingUR
Hallo, Melanie,
es scheint, als wenn ich irgendetwas nicht richtig aufgenommen hätte, denn nachdem ich mir den Programmcode angeschaut habe, konnte ich nicht entdecken, wo die Prozedur TabelleAusfüllen2006neu(), die Du bei der Beschreibung Deines Ausgangsproblems erwähnt hast, ist nicht zu entdecken, noch ist erkennbar, wie sie aktiviert wird.
Da ich an dieser Stelle nicht weitergekommen bin, bleiben die Anmerkungen des Beitrags von Sonntag (10.12.06 18:47) unverändert, insbesondere die Empfehlungen, die ich im "Fazit" zusammengefasst habe (Wie hattest Du den Einsatz der SVERWEIS-Funktion als die Funktion in Deinem Programm ausgemacht, die zu der langen Ausführungszeit führt?).
Die Anmerkungen zum Suchalgorithmus in einem Array, sind vorerst unbedeutend, denn zum einen ist m.E. die verbrauchte Zeit, um in einem sortierten Feld zu suchen, gering und zum anderen können diese kaum die Ursache für einen großartigen Anstieg des Zeitverbrauchs sein.
Gruß,
Uwe
AW: Sverweis dauert zu lange
07.12.2006 12:59:56
nach jedem S-Verweis ein Break?
Hi nochmal an Uwe,
ich habe jetzt mit Deinem Code die Berechnung ausgeschaltet und nun kommt nach jedem Mal, wenn eine Spalte erfolgreich mit SVerweisen berechnet wurde - vor dem Copybefehl -, eine Fehlermeldung("Ausführung des Codes wurde unterbrochen"), die ich dann zwar einfach überspringen kann, aber .... muss ja nicht sein! Weißt Du woran das liegt???
Gruß,
Melanie
AW: Sverweis dauert zu lange
07.12.2006 13:25:23
ingUR
Ja, Melanie,
mir ist leider erst zuspät aufgefallen, das der Einsatz der SVerweis-Formel recht geschickt erfolgt ist (siehe meinen vorherigen Beitrag).
1. Es werden die Zellenformeln in die entsprechende Spalte eingetragen.
2. Es wird die Spalte kopiert
3. Es werden die Werte der Spaltenkopie an die Stelle der Spalte kopiert.
Mit dem Schritt 3 wird erreicht, dass die Formel nicht mehr existiert, sondern nur noch ein Wert in der Zelle ist, so dass im wieteren Verlauf keine erneute Berechnung des Zelleninhalts erfolgt. Der Schritt 2 in Verbindung mit dem Schritt 3 erfordert natürlich, dass die Formeln zu diesem Zeitpunkt(!) ausgewertet sind, und da das automatische Berechnen abgeschaltet wurde, teilt das System diesen Umstand mit.
Da jedoch dieses erst am Ende der Spalte geschiet, wird mit jedem Formeleintrag die vorherigen Formeln neu berechnet, wenn das Neuberechnen nicht auf Manuell geschalteet wird.
Es ist möglich, vor jedem Copybefehl die Berechnung einzuschalten und nach dem Paste-Befehl wieder aus:
Range("O2:O" & intRow).FormulaR1C1 = "=IF(ISERROR(VLOOKUP(C[-14],'[(Monats-)Verbräuche 2005.xls]verbrauch2005'!C1:C4,4,0)), ""-"", VLOOKUP(C[-14],'[(Monats-)Verbräuche 2005.xls]verbrauch2005'!C1:C4,4,0))"
Application.Calculation = xlCalculationAutomatic
Columns("O:O").Copy
Columns("O:O").PasteSpecial Paste:=xlPasteValues
Application.Calculation = xlCalculationManual

Ob jedoch damit zeitlich etwas zu gewinnen ist, hängt von der Anzahl der Zeilen (intRow bzw. 59000; Erläuterung oben) ab.
Gruß,
Uwe

Beliebteste Forumthreads (12 Monate)

Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige