SVERWEIS "schneller machen"
Harri Märki
Ich habe zwei Dateien mit mehr oder weniger den gleichen Daten. In Datei 1
sollen zusätzliche Spalten eingetragen werden, die in Datei 2 vorhanden sind.
Als Kriterium dient eine Artikelnummer, die ich mit SVERWEIS auch problemlos
so behandeln kann. Insgesamt müssen in der ersten Datei 4 Spalten ergänzt
werden. Das funktioniert soweit auch ganz gut. Nur handelt es sich um
insgesamt ca 27'000 Datensätze die verglichen werden müssen und bei 4 Spalten
dauert das Ganze doch gut 3 - 5 Minuten, bis alles übertragen wurde (Excel
2007).
Gibt es eine Möglichkeit, um diesen Prozess zu beschleunigen, so dass das
Ganze vielleicht nur noch zw. 10 bis 30 Sekunden dauert?
Gruss Harri
Andreas Killer
wrote:
> Gibt es eine Möglichkeit, um diesen Prozess zu beschleunigen, so dass das
> Ganze vielleicht nur noch zw. 10 bis 30 Sekunden dauert?
Wenn es um reine Daten (ohne Formeln) geht, dann ist es theoretisch
möglich die Daten in ein (zwei) Array zu laden und direkt im Speicher
zu manipulieren.
So wird der Prozess dann wohl nur noch 1 oder 2 Sekunden dauern.
Beschreib mal genauer wie Du die Daten zusammenfügst.
Andreas.
Alexander Wolff
So ganz ist mir Dein Aufbau noch nicht klar: 4 Spalten, die gleich sein
müssen?
Andreas hat recht: Das interne Bearbeiten wird Dich unglaublich
beschleunigen.
Ich habe hier einen Code, der gleiche Inhalte zweier Listen
nebeneinanderstellt:
http://xxcl.de/0052.htm
2 Spalten (statt Deiner 4), 30.000 Sätze, 7 Sekunden
Eine andere noch schnellere Möglichkeit ist folgende:
http://xxcl.de/0067.htm
1 Spalte, 60.000 Sätze, 4 Sekunden
Egal, was Du machst: Entscheidend ist, dass das Ausgangsmaterial sortiert
ist oder sortiert wird.
--
Moin+Gruss Alexander - MVP for MS Excel - www.xxcl.de - mso2000sp3 --7-2
Harri Märki
"Alexander Wolff" wrote:
> So ganz ist mir Dein Aufbau noch nicht klar: 4 Spalten, die gleich sein
> müssen?
>
> Andreas hat recht: Das interne Bearbeiten wird Dich unglaublich
> beschleunigen.
>
> Ich habe hier einen Code, der gleiche Inhalte zweier Listen
> nebeneinanderstellt:
> http://xxcl.de/0052.htm
> 2 Spalten (statt Deiner 4), 30.000 Sätze, 7 Sekunden
>
> Eine andere noch schnellere Möglichkeit ist folgende:
> http://xxcl.de/0067.htm
> 1 Spalte, 60.000 Sätze, 4 Sekunden
>
> Egal, was Du machst: Entscheidend ist, dass das Ausgangsmaterial sortiert
> ist oder sortiert wird.
> --
> Moin+Gruss Alexander - MVP for MS Excel - www.xxcl.de - mso2000sp3 --7-2
Ich verstehe eure Denkanstösse noch nicht so genau. Ich habe ein Makro
geschrieben, das diverse Anpassungen an meinem Hauptfile macht. Das geht
alles vernünftig schnell. Der Teil, der viel Zeit benötigt, ist der hier:
Range("N2").Select
ActiveCell.FormulaR1C1 = _
"=VLOOKUP(RC[-12],AL_Artikeldaten.txt!R1:R1048576,18,FALSE)"
Range("N1").Select
ActiveCell.FormulaR1C1 = "Kategorie 1"
Range("O2").Select
ActiveCell.FormulaR1C1 = _
"=VLOOKUP(RC[-13],AL_Artikeldaten.txt!R1:R1048576,19,FALSE)"
Range("O1").Select
ActiveCell.FormulaR1C1 = "Kategorie 2"
Range("P2").Select
ActiveCell.FormulaR1C1 = _
"=VLOOKUP(RC[-14],AL_Artikeldaten.txt!R1:R1048576,20,FALSE)"
Range("P1").Select
ActiveCell.FormulaR1C1 = "Kategorie 3"
Range("N2").Select
Selection.AutoFill Destination:=Range("N2:N26700")
Range("O2").Select
Selection.AutoFill Destination:=Range("O2:O26700"), Type:=xlFillDefault
Range("P2").Select
Selection.AutoFill Destination:=Range("P2:P26700")
Der erste Teil wo die Formel rein geschrieben wird ist ein Klacks. Aber das
Selection.AutoFill Destination:=Range("N2:N26700") dauert lange pro Spalte.
Hier sind jetzt zwar gerade mal drei Spalten erwähnt, aber es gibt noch eine
vierte, die in etwa gleich behandelt wird.
Range("Q2").Select
ActiveCell.FormulaR1C1 = _
"=IF(VLOOKUP(RC[-15],AL_Artikeldaten.txt!R1C1:R26713C26,22,FALSE)=0,"""",VLOOKUP(RC[-15],AL_Artikeldaten.txt!R1C1:R26713C26,22,FALSE))"
Range("Q2").Select
Selection.AutoFill Destination:=Range("Q2:Q26700")
Hier wird lediglich nichts eingetragen, wenn in der zweiten Datei kein Wert
in der betreffenden Spalte (22) vorhanden ist. Bei allen Spalten dauert die
Auswertung aber etwa gleich lang.
Vielleicht bringt es etwas, wenn ich den Bereich R1C1:R26713C26 anpasse.
Dann wird sicherlich schon mal nicht über das gesamte Datenblatt der zweiten
Datei geprüft. Sollte aber nicht wirklich einen grossen Einfluss haben, da
ich ja im Hauptfile wiederum nur von Zeile 2 bis 26700 füllen lasse.
Hoffe ich habe jetzt verständlicher geschrieben.
Gruss Harri
Thomas Ramel
On 28 Jan., 09:07, Harri Märki <HarriM...@discussions.microsoft.com>
wrote:
>
> Der erste Teil wo die Formel rein geschrieben wird ist ein Klacks. Aber das
> Selection.AutoFill Destination:=Range("N2:N26700") dauert lange pro Spalte.
> Hier sind jetzt zwar gerade mal drei Spalten erwähnt, aber es gibt noch eine
> vierte, die in etwa gleich behandelt wird.
Wenn Du den SVERWEIS() über alle Zeilen kopierst wird das entsprechend
lange dauern, daran kommst Du nicht vorbei.
Wenn deine Daten im Tabellenblatt sortiert sind, könntest Du die
Formel erweitern und bei Übereinstimmung des Such-Kriteriums einfach
den Wert aus der Zeile darüber übernehmen und nur wenn das
Suchkriterium nicht mit dem vorherigen übereinstimmt den neuen Wert
per SVERWEIS() holen.
Das könnte diese Zuordnun noch etwas beschleunigen - musst Du halt mal
ausprobieren.
Ansonsten gehts wirklich nur über Arrays die Du einlies und
durchläufst, fürchte ich.
ScreenUpdating und ev. Calculation hast Du vor dem Eintragen der
Formeln schon mal deaktiviert?
--
Mit freundlichen Grüssen
Thomas Ramel
- MVP für MS-Excel -
Harri Märki
"Thomas Ramel" wrote:
> Grüezi Harri
>
> On 28 Jan., 09:07, Harri Märki <HarriM...@discussions.microsoft.com>
> wrote:
> >
> > Der erste Teil wo die Formel rein geschrieben wird ist ein Klacks. Aber das
> > Selection.AutoFill Destination:=Range("N2:N26700") dauert lange pro Spalte.
> > Hier sind jetzt zwar gerade mal drei Spalten erwähnt, aber es gibt noch eine
> > vierte, die in etwa gleich behandelt wird.
>
> Wenn Du den SVERWEIS() über alle Zeilen kopierst wird das entsprechend
> lange dauern, daran kommst Du nicht vorbei.
>
> Wenn deine Daten im Tabellenblatt sortiert sind, könntest Du die
> Formel erweitern und bei Übereinstimmung des Such-Kriteriums einfach
> den Wert aus der Zeile darüber übernehmen und nur wenn das
> Suchkriterium nicht mit dem vorherigen übereinstimmt den neuen Wert
> per SVERWEIS() holen.
>
> Das könnte diese Zuordnun noch etwas beschleunigen - musst Du halt mal
> ausprobieren.
>
> Ansonsten gehts wirklich nur über Arrays die Du einlies und
> durchläufst, fürchte ich.
Ok, und wie müsste ich dies mit den Arrays machen? Habe da momentan
überhaupt keine Vorstellung geschweige denn Ahnung davon.
> ScreenUpdating und ev. Calculation hast Du vor dem Eintragen der
> Formeln schon mal deaktiviert?
Nein das habe ich noch nicht probiert. Was genau meinst du mit
Screenupdating und Calculation?
Application.ScreenUpdating = False resp. Application.ScreenUpdating = True
im Makro einbauen vor dem füllen der Zellen?
Wie sieht dies dann mit dem Calculation aus?
Gruss Harri
Thomas Ramel
On 28 Jan., 10:19, Harri Märki <HarriM...@discussions.microsoft.com>
wrote:
Ja, genau.
> Wie sieht dies dann mit dem Calculation aus?
Als Gerüst kannst dDu folgendes verwenden:
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
'Dein Code
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
Hast Du das mit dem Anpassen der Formel und dem Sortieren der
Artkelnummern schon getestet?
Harri Märki
>
> Als Gerüst kannst dDu folgendes verwenden:
>
> Application.ScreenUpdating = False
> Application.Calculation = xlCalculationManual
>
> 'Dein Code
>
> Application.Calculation = xlCalculationAutomatic
> Application.ScreenUpdating = True
Habe dies nun vor, resp. nach meinem SVERWEIS eingebaut. Hat aber nix
gebracht. Das Ganze benötigt ca 7.5 Minuten, bis alles durch ist. Das sind
mindestens 6.5 - 7 Minuten zuviel.
>
> Hast Du das mit dem Anpassen der Formel und dem Sortieren der
> Artkelnummern schon getestet?
Nein, das habe ich noch nicht getestet. Beide Listen sind nach Artikelnummer
sortiert. Nur ist es eben so, dass die Liste, aus der die zusätzlichen Infos
in meine Liste eingebaut werden sollen, etwas länger und über mehr Einträge
verfügt.
Wie soll ich denn die Formel genau anpassen? Was schlägst du vor?
Gruss Harri
Andreas Killer
wrote:
> Ich verstehe eure Denkanstösse noch nicht so genau. Ich habe ein Makro
> geschrieben, das diverse Anpassungen an meinem Hauptfile macht. Das geht
> alles vernünftig schnell. Der Teil, der viel Zeit benötigt, ist der hier:
Naja, dann probier ich mal mein Glück und so aus dem Bauch heraus
sollte dies Makro diesen Teil ersetzen können.
Andreas.
Option Explicit
Sub Test()
Dim R As Range, MaxY As Long
Dim Data, Y As Long, C As Range
Dim Import As Worksheet
'Letzte Zeile Spalte B
MaxY = Range("B2").End(xlDown).Row
'Bereich A1:P[letzte Zeile]
Set R = Range(Cells(1, 1), Range("P" & MaxY))
'Daten einlesen
Data = R
'Überschriften setzen:
'Range("N1") = "Kategorie 1"
'Range("O1") = "Kategorie 2"
'Range("P1") = "Kategorie 3"
Y = 1
Data(Y, 14) = "Kategorie 1"
Data(Y, 15) = "Kategorie 2"
Data(Y, 16) = "Kategorie 3"
'Woher importieren?
Set Import = Workbooks("AL_Artikeldaten.txt").Sheets(1)
For Y = 2 To MaxY
'Suche nach dem Wert aus Spalte B in Import Spalte A
Set C = Import.Range("A:A").Find(Range("B" & Y), LookIn:= _
xlValues, LookAt:=xlWhole, MatchCase:=False)
'Gefunden?
If Not C Is Nothing Then
'Ja, Werte holen:
'Range("N" & Y) = Import.Cells(C.Row, 18)
'Range("O" & Y) = Import.Cells(C.Row, 19)
'Range("P" & Y) = Import.Cells(C.Row, 20)
Data(Y, 14) = Import.Cells(C.Row, 18)
Data(Y, 15) = Import.Cells(C.Row, 19)
Data(Y, 16) = Import.Cells(C.Row, 20)
Else
'Fehler #NV
Data(Y, 14) = CVErr(xlErrNA)
Data(Y, 15) = CVErr(xlErrNA)
Data(Y, 16) = CVErr(xlErrNA)
End If
Next
'Daten zurückspeichern
R = Data
End Sub
Harri Märki
Danke für Deinen Tip. Habe es eben getestet. Funktioniert einwandfrei,
benötigt aber immer noch sehr viel Zeit. Hat ebenfalls einige Minuten
gedauert, bis diese drei Spalten drinn waren. Die vierte habe ich noch nicht
ergänzt. Schätze aber, dass es dann doch noch etwas mehr Zeit in Anspruch
nimmt.
Hast Du noch eine weitere Idee?
Gruss Harri
"Andreas Killer" wrote:
> .
>
Michael Schwimmer
Am Thu, 28 Jan 2010 00:07:01 -0800 schrieb Harri M�rki:
> Ich verstehe eure Denkanst�sse noch nicht so genau. Ich habe ein Makro
> geschrieben, das diverse Anpassungen an meinem Hauptfile macht. Das geht
> alles vern�nftig schnell. Der Teil, der viel Zeit ben�tigt, ist der hier:
>
> Range("N2").Select
> ActiveCell.FormulaR1C1 = _
> "=VLOOKUP(RC[-12],AL_Artikeldaten.txt!R1:R1048576,18,FALSE)"
Select ist in den meisten F�llen unn�tig und kostet erheblich Zeit.
Formeln einzutragen, wenn du lediglich die Werte ben�tigst ist beinahe noch
schlimmer.
Probiers mal so, k�nnte man aber sicher noch etwas schneller machen:
Public Sub importArtikel()
Dim avarSource As Variant
Dim colSearch As New Collection
Dim i As Long
Dim k As Long
Dim varDummy As Variant
Dim dteBegin As Date
Dim avarBlock(1 To 1, 1 To 4) As Variant
dteBegin = Now
' Etwa 30000 Datens�tze absteigend sortiert
' 1. Spalte Artikelnummer, Spalte 2-5 Daten
' Daten in Array einlesen
avarSource = Workbooks("Al_Artikeldaten.xlsm" _
).Worksheets(1).UsedRange
On Error Resume Next
' wegen ev. doppelten Artikelnummern oder nicht
' vorhandenen Artikeln im Quellblatt
' Alle "Reihen" im Quellarray durchlaufen
For i = 2 To UBound(avarSource, 1)
If avarSource(i, 1) <> "" Then
' "Reihennummer" in Collection speichern,
' Schl�ssel enth�lt Art. Nr
colSearch.Add i, "ARTIKEL" & avarSource(i, 1)
End If
Next
Application.ScreenUpdating = False
With ThisWorkbook.Worksheets("Tabelle1")
' �berschriften
.Cells(1, 14) = "Kategorie 1"
.Cells(1, 15) = "Kategorie 2"
.Cells(1, 16) = "Kategorie 3"
.Cells(1, 17) = "Kategorie 4"
' Alle Reihen im Zielblatt durchlaufen
' Etwa 30000 Datens�tze aufsteigend sortiert
' Spalte 2 enth�lt Artikelnummern
For i = 2 To .Cells(.Rows.Count, 2).End(xlUp).Row
' Zeile im Quellarray aus Collection auslesen
k = 0: k = colSearch("ARTIKEL" & .Cells(i, 2))
If k > 0 Then ' Artikel existiert im Quellblatt
' Daten eintragen (17 Sekunden)
' .Cells(i, 14) = avarSource(k, 2) '18)
' .Cells(i, 15) = avarSource(k, 3) '19)
' .Cells(i, 16) = avarSource(k, 4) '20)
' .Cells(i, 17) = avarSource(k, 5) '21)
' etwas schneller (7 Sekunden)
avarBlock(1, 1) = avarSource(k, 2) '18)
avarBlock(1, 2) = avarSource(k, 3) '19)
avarBlock(1, 3) = avarSource(k, 4) '20)
avarBlock(1, 4) = avarSource(k, 5) '21)
.Range(.Cells(i, 14), .Cells(i, 17)) = avarBlock
End If
Next i
End With
Application.ScreenUpdating = True
MsgBox Format(Now - dteBegin, "nn:ss"), vbInformation, "Zeitdauer"
End Sub
Das musst du aber noch etwas anpassen, da ich zum Testen Spalte 2-5 statt
18-21 verwendet habe und den Blattnamen in der Zielmappe nicht kenne.
Viele Gr��e
Michael
--
http://michael-schwimmer.de
Masterclass Excel VBA ISBN-10: 3827325250
Das Excel-VBA Codebook ISBN-10: 3827324718
Microsoft Office Excel 2007-Programmierung ISBN-10: 3866454139
Andreas Killer
wrote:
> Hast Du noch eine weitere Idee?
Nun ja, vorsortierte Daten könnten noch etwas die Verarbeitung
beschleunigen.
Der "Zeitkiller" ist das Find, 26.000mal suchen dauert halt.
Wenn man beide Tabellen vorher sortiert, dann könnte man auch beide in
den Speicher lesen, also mit 2 Array's arbeiten.
Wenn Du nun nach dem ersten Artikel "aus Spalte B" des ersten Array in
"Spalte A" des 2ten Array gefunden hast, dann brauchst Du beim
nächsten Artikel nicht mehr von oben ab suchen (so wie FIND es macht),
sondern kannst einfach ab der nächsten Zeile weiter vergleichen.
Wieviel Zeit das bringt... keine Ahnung, das sortieren kommt ja dazu,
musst Du probieren. Ich denke so in etwa sollte es gehen.
Andreas.
Option Explicit
Sub Test()
Dim R As Range, MaxY As Long
Dim Data, Y As Long, C As Range
Dim Import As Worksheet
Dim Data2, Y2 As Long, MaxY2 As Long, YSave As Long
'Letzte Zeile Spalte B
MaxY = Range("B2").End(xlDown).Row
'Bereich A1:P[letzte Zeile]
Set R = Range(Cells(1, 1), Range("P" & MaxY))
'Daten einlesen
Data = R
'Überschriften setzen:
'Range("N1") = "Kategorie 1"
'Range("O1") = "Kategorie 2"
'Range("P1") = "Kategorie 3"
Y = 1
Data(Y, 14) = "Kategorie 1"
Data(Y, 15) = "Kategorie 2"
Data(Y, 16) = "Kategorie 3"
'Woher importieren?
Set Import = Workbooks("AL_Artikeldaten.txt").Sheets(1)
With Import
'Daten einlesen
Data2 = .Range(.Cells(1, 1), .Cells.SpecialCells( _
xlCellTypeLastCell))
'Erste Zeile
Y2 = 1 'LBound(Data2)
'Letzte Zeile
MaxY2 = UBound(Data2)
End With
YSave = 1
For Y = 2 To MaxY
'Suche nach dem Wert aus Spalte B in Import Spalte A
Do While Y2 <= MaxY2 And Data(Y, 2) <> Data2(Y2, 1)
Y2 = Y2 + 1
If Y2 > MaxY2 Then
'Nicht gefunden
Exit Do
End If
Loop
'Gefunden?
If Y2 <= MaxY2 Then
'Zeile sichern
YSave = Y2
'Ja, Werte holen:
'Range("N" & Y) = Import.Cells(C.Row, 18)
'Range("O" & Y) = Import.Cells(C.Row, 19)
'Range("P" & Y) = Import.Cells(C.Row, 20)
Data(Y, 14) = Data2(Y2, 18)
Data(Y, 15) = Data2(Y2, 19)
Data(Y, 16) = Data2(Y2, 20)
Else
'Fehler #NV
Data(Y, 14) = CVErr(xlErrNA)
Data(Y, 15) = CVErr(xlErrNA)
Data(Y, 16) = CVErr(xlErrNA)
'Nächsten Wert wiederab der letzten Zeile suchen
Y2 = YSave
Harri Märki
Besten Dank! So habe ich es hingekriegt :-).
Gruss und schönes WE
Harri
Andreas Killer
wrote:
> Besten Dank! So habe ich es hingekriegt :-).
Sehr gut. :-)
So interessehalber: Wie lange läuft das ganze Makro nun noch?
Andreas.
Harri Märki
Besten Dank für Deine Tips. Hate es versucht, aber es hat nicht so geklappt,
resp, ich konnte die Anpassungen nicht so vornehmen, dass es bei mir
funktioniert hatte. Habe dann die Lösung von Andreas genommen.
Gruss und schönes WE
Harri
"Michael Schwimmer" wrote:
> Halo Harri
>
> Am Thu, 28 Jan 2010 00:07:01 -0800 schrieb Harri Märki:
>
> > Ich verstehe eure Denkanstösse noch nicht so genau. Ich habe ein Makro
> > geschrieben, das diverse Anpassungen an meinem Hauptfile macht. Das geht
> > alles vernünftig schnell. Der Teil, der viel Zeit benötigt, ist der hier:
> >
> > Range("N2").Select
> > ActiveCell.FormulaR1C1 = _
> > "=VLOOKUP(RC[-12],AL_Artikeldaten.txt!R1:R1048576,18,FALSE)"
>
> Select ist in den meisten Fällen unnötig und kostet erheblich Zeit.
> Formeln einzutragen, wenn du lediglich die Werte benötigst ist beinahe noch
> schlimmer.
>
> Probiers mal so, könnte man aber sicher noch etwas schneller machen:
>
> Public Sub importArtikel()
> Dim avarSource As Variant
> Dim colSearch As New Collection
> Dim i As Long
> Dim k As Long
> Dim varDummy As Variant
> Dim dteBegin As Date
> Dim avarBlock(1 To 1, 1 To 4) As Variant
>
> dteBegin = Now
>
> ' Etwa 30000 Datensätze absteigend sortiert
> ' 1. Spalte Artikelnummer, Spalte 2-5 Daten
> ' Daten in Array einlesen
> avarSource = Workbooks("Al_Artikeldaten.xlsm" _
> ).Worksheets(1).UsedRange
>
> On Error Resume Next
> ' wegen ev. doppelten Artikelnummern oder nicht
> ' vorhandenen Artikeln im Quellblatt
>
> ' Alle "Reihen" im Quellarray durchlaufen
> For i = 2 To UBound(avarSource, 1)
> If avarSource(i, 1) <> "" Then
> ' "Reihennummer" in Collection speichern,
> ' Schlüssel enthält Art. Nr
> colSearch.Add i, "ARTIKEL" & avarSource(i, 1)
> End If
> Next
>
> Application.ScreenUpdating = False
>
> With ThisWorkbook.Worksheets("Tabelle1")
>
> ' Überschriften
> .Cells(1, 14) = "Kategorie 1"
> .Cells(1, 15) = "Kategorie 2"
> .Cells(1, 16) = "Kategorie 3"
> .Cells(1, 17) = "Kategorie 4"
>
> ' Alle Reihen im Zielblatt durchlaufen
> ' Etwa 30000 Datensätze aufsteigend sortiert
> ' Spalte 2 enthält Artikelnummern
> For i = 2 To .Cells(.Rows.Count, 2).End(xlUp).Row
>
> ' Zeile im Quellarray aus Collection auslesen
> k = 0: k = colSearch("ARTIKEL" & .Cells(i, 2))
>
> If k > 0 Then ' Artikel existiert im Quellblatt
>
> ' Daten eintragen (17 Sekunden)
> ' .Cells(i, 14) = avarSource(k, 2) '18)
> ' .Cells(i, 15) = avarSource(k, 3) '19)
> ' .Cells(i, 16) = avarSource(k, 4) '20)
> ' .Cells(i, 17) = avarSource(k, 5) '21)
>
> ' etwas schneller (7 Sekunden)
> avarBlock(1, 1) = avarSource(k, 2) '18)
> avarBlock(1, 2) = avarSource(k, 3) '19)
> avarBlock(1, 3) = avarSource(k, 4) '20)
> avarBlock(1, 4) = avarSource(k, 5) '21)
> .Range(.Cells(i, 14), .Cells(i, 17)) = avarBlock
>
> End If
>
> Next i
>
> End With
>
> Application.ScreenUpdating = True
>
> MsgBox Format(Now - dteBegin, "nn:ss"), vbInformation, "Zeitdauer"
>
> End Sub
>
>
> Das musst du aber noch etwas anpassen, da ich zum Testen Spalte 2-5 statt
> 18-21 verwendet habe und den Blattnamen in der Zielmappe nicht kenne.
>
>
> Viele Grüße
> Michael
>
>
>
> --
> http://michael-schwimmer.de
> Masterclass Excel VBA ISBN-10: 3827325250
> Das Excel-VBA Codebook ISBN-10: 3827324718
> Microsoft Office Excel 2007-Programmierung ISBN-10: 3866454139
> .
>
Harri Märki
und zusätzlich gefüllt plus zusätzlich der Makrobereich von Dir, bis zum
Fenster "Speichern" sind es jetzt noch ca 70 Sekunden für alles. Ich werde
versuchen, das Ganze noch mehr zu bschleunigen.
Habe da noch sowas:
Range("J2").Select
ActiveCell.FormulaR1C1 = _
"=IF(RC[-1]>20,""sofort"",IF(RC[-1]>3,""Innert 2 - 4
Arbeitstagen"",IF(RC[-1]<-10,""z.Z. nicht lieferbar"",IF(RC[-1]>-10,""Innert
1 Woche"",""""))))"
Range("J2").Select
Selection.AutoFill Destination:=Range("J2:J26700")
Da finde ich sicherlich auch noch eine Möglichkeit, dies zu beschleunigen.
Ev. mit sowas wie diesem hier... werde da wohl noch etwas tüfteln müssen...
:-)
Gruss Harri
"Andreas Killer" wrote:
> .
>
Harri Märki
statt:
Range("J2").Select
ActiveCell.FormulaR1C1 = _
"=IF(RC[-1]>20,""sofort"",IF(RC[-1]>3,""Innert 2 - 4
Arbeitstagen"",IF(RC[-1]<-10,""z.Z. nicht lieferbar"",IF(RC[-1]>-10,""Innert
1 Woche"",""""))))"
Range("J2").Select
Selection.AutoFill Destination:=Range("J2:J26700")
Habe ich das eingebaut:
Dim L As Range
For Each L In Range("I2:I26700")
Select Case L
Case Value > 20
L = "sofort"
Case Value > 3
L = "Innert 2 - 4 Arbeitstagen"
Case Value < -10
L = "z.Z. nicht lieferbar"
Case Value = -10 - 3
L = "Innert einer Woche"
End Select
Next
funzt noch nicht richtig...
eigentlich soll das Ganze in die Spalte J geschrieben werden und die Prüfung
findet in der Spalte I statt. Egal, ob ich es direkt in I schreibe oder in J.
Hab da wohl noch einen Fehler. Ansonsten geht dies auch mit meiner Formel
relativ schnell.
Was tadelos funktioniert, aber auch etwas Zeit benötig, ist wohl dies:
A = Range("K:K")
For i = 1 To 65536
A(i, 1) = IIf(A(i, 1) <> 0,
"http://shop.psh.ch/tabid/15440/Default.aspx?ID=" & A(i, 1), "")
Next
Range("K:K") = A
Range("K1").Select
Könnte man dies auch noch beschleunigen?
Grundsätzlich kann ich aber mit den ca 75 Sekunden leben. Die das gesamte
Makro benötigt.
Gruss Harri
"Andreas Killer" wrote:
> .
>
Andreas Killer
wrote:
> Dim L As Range
> For Each L In Range("I2:I26700")
Wieso nimmst Du hier den fixen Bereich? Ist der garantiert?
Und wieso arbeitest Du hier wieder mit der Tabelle und nicht mit dem
Array?
> Select Case L
> funzt noch nicht richtig...
Die Select-Zeile gibt an was verglichen wird und da L ein Range und
nix explizit angegeben ist wird die Default-Eigenschaft Value
genommen, sprich eigentlich steht dort "Select Case L.Value", nun ja,
so oder so, ist okay.
> Case Value > 20
Aber der Case-Part ist falsch, das geht so:
Case Is > 20
BTW, bestimmte Grenzen prüft man so: Case 1 To 10
> Was tadelos funktioniert, aber auch etwas Zeit benötig, ist wohl dies:
>
> A = Range("K:K")
> For i = 1 To 65536
...
> Könnte man dies auch noch beschleunigen?
Warum liest Du die Spalte bis zum Ende ein wenn Du nur rund 1/3 davon
brauchst? Hohl dir die letzte Zeile via
Y=Range("K" & Rows.Count)
A = Range("K1:K" &Y)
for i = 1 to Y
> Grundsätzlich kann ich aber mit den ca 75 Sekunden leben. Die das gesamte
> Makro benötigt.
Na, ist doch mal was. :-)
Ich vermute mal wenn Du beide sortierten Blätter EINMAL am Anfang
einliest und dann nur noch mit den Array's arbeitest und erst ganz am
Ende sie EINMAL wieder zurückschreibst wirst Du es noch deutlich
spürbar schneller hinkriegen.
Die Frage ist ob der Aufwand lohnt das alles umzuschreiben.
Andreas.
Michael Schwimmer
Am Fri, 29 Jan 2010 03:00:01 -0800 schrieb Harri M�rki:
> Besten Dank f�r Deine Tips. Hate es versucht, aber es hat nicht so geklappt,
> resp, ich konnte die Anpassungen nicht so vornehmen, dass es bei mir
> funktioniert hatte. Habe dann die L�sung von Andreas genommen.
Wichtig ist zu wissen, ob du tats�chlich Formeln ben�tigst. Wenn ja, kann
man nichts mehr gro�artig beschleunigen, denn das Berechnen nach dem
Einf�gen dauert halt seine Zeit. Selbst, wenn man alle 120000 (30000 Zeilen
mal 4 Zellen) Formeln �ber ein Array einf�gt, dauert das anschlie�ende
Berechnen mehrere Minuten, das Einf�gen selbst dagegen nur ein paar
Sekunden.
Das passiert auch, wenn man die Formeln ohne Gleichheitszeichen (mit einer
Ersatzzeichenfolge) einf�gt und per Suchen/Ersetzen alle
Ersatzzeichenfolgen gegen Gleichheitszeichen ersetzt.
Hab ich bereits so �hnlich mit FormulaLocal probiert, besser ist aber
Formula mit den engl. Funktionsnamen:
Public Sub test()
Dim i As Long
Dim strAddress As String
Dim dteBegin As Date
Dim varDest As Variant
Dim lngSourceEnd As Long
Dim lngDestEnd As Long
dteBegin = Now
With ThisWorkbook.Worksheets("Tabelle1")
lngDestEnd = .Cells(.Rows.Count, 2).End(xlUp).Row
lngSourceEnd = 35000
ReDim varDest(1 To lngDestEnd, 1 To 4)
varDest(1, 1) = "Kategorie 1"
varDest(1, 2) = "Kategorie 2"
varDest(1, 3) = "Kategorie 3"
varDest(1, 4) = "Kategorie 4"
For i = 2 To .Cells(.Rows.Count, 2).End(xlUp).Row
If .Cells(i, 2) <> "" Then
strAddress = .Cells(i, 2).Address
varDest(i, 1) = "=SVERWEIS(" & strAddress _
& ";AL_Artikeldaten.txt!A1:V" & lngSourceEnd & ";18;falsch)"
varDest(i, 2) = "=SVERWEIS(" & strAddress _
& ";AL_Artikeldaten.txt!A1:V" & lngSourceEnd & ";19;falsch)"
varDest(i, 3) = "=SVERWEIS(" & strAddress _
& ";AL_Artikeldaten.txt!A1:V" & lngSourceEnd & ";20;falsch)"
varDest(i, 4) = "=SVERWEIS(" & strAddress _
& ";AL_Artikeldaten.txt!A1:V" & lngSourceEnd & ";21;falsch)"
End If
Next
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
.Range(.Cells(1, 14), .Cells(lngDestEnd, 17)).FormulaLocal = _
varDest
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End With
MsgBox Format(Now - dteBegin, "nn:ss"), vbInformation, "Zeitdauer"
End Sub
Wenn es aber auch nur die Werte selbst sein k�nnen und du mehr Infos gibst,
k�nnte man das zuletzt gepostete anpassen.
Ist das Blatt, aus dem du die vier Infos holst, Bestandteil der gleichen
Mappe, in welche die Infos kopiert werden?
Hei�t das Blatt AL_Artikeldaten.txt, oder hei�t so die Mappe? Wenn so die
Mappe hei�t, wie hei�t dann das Blatt?
Wenn das Blatt so hei�t und es sich in der gleichen Mappe befindet, dann
statt
>> avarSource = Workbooks("Al_Artikeldaten.xlsm" _
>> ).Worksheets(1).UsedRange
avarSource = ThisWorkbook.Worksheets("Al_Artikeldaten.txt").UsedRange
Wenn die Infos im Quellblatt aus Spalte 18-21 geholt werden sollen:
' etwas schneller (7 Sekunden)
avarBlock(1, 1) = avarSource(k, 18)
avarBlock(1, 2) = avarSource(k, 19)
avarBlock(1, 3) = avarSource(k, 20)
avarBlock(1, 4) = avarSource(k, 21)
Viele Gr��e
Harri Märki
Excel mal geschlossen und später nochmals das Makro ausgeführt. Hat jetzt
nur noch 45 Sekunden gedauert für alles.. :-)
das passt :-D
Gruss Harri
Alexander Wolff
Michael Schwimmer wrote:
> Wichtig ist zu wissen, ob du tats�chlich Formeln ben�tigst. Wenn ja,
> kann man nichts mehr gro�artig beschleunigen, denn das Berechnen nach
> dem Einf�gen dauert halt seine Zeit. Selbst, wenn man alle 120000
> (30000 Zeilen mal 4 Zellen) Formeln �ber ein Array einf�gt, dauert
> das anschlie�ende Berechnen mehrere Minuten, das Einf�gen selbst
> dagegen nur ein paar Sekunden.
ich habe mal 325.000 =SVERWEIS(;;) auf eine 256 Eintr�ge lange sortierte
Lookup-Spalte angesetzt. Das ben�tigt ca. 1,2 Sekunden.
Ich wollte nun best�tigt sehen, dass SVERWEIS bin�r (oder �hnlich effizient)
sucht. Das tut es! Denn 256^2 Eintr�ge ben�tigen nur die doppelte Zeit. Bei
mir brauchen also 325.000 =SVERWEIS(;;) auf 65536 Eintr�ge 2,4 Sekunden.
2^8 Eintr�ge = 1,2 Sekunden
2^16 Eintr�ge = 2,4 Sekunden
2^20 Eintr�ge = 3,0 Sekunden (vermutet, da 2007 nicht im Einsatz)
Aber nun kommt es!
Verdopple ich die Anzahl der Formeln auf 650.000, knickt Excel ein. Der
Speicherbedarf ist permanent hoch und an der Grenze.
Daher ist nach Beachtung aller beschleunigenden Kriterien ...
- Sortierung ist ein absolutes Muss! Wenn die Grunddaten nicht sortiert
sind, muss man eine sortierte Kopie zumindest tempor�r erzeugen.
Die Dauer einer Suche ist ungef�hr proportional zum Exponenten zur
Basis 2 der Menge der Daten, w�hrend unsortiert die Dauer direkt linear
proportional ist, also hier 256mal statt nur 2mal so lang.
- niemals SVERWEIS zum Auslesen von ganzen Datens�tzen verwenden. Daf�r ist
VERGLEICH/INDEX zust�ndig. SVERWEIS ist nur f�r eine Einzel-Feldabfrage
eines Datensatzes gedacht. Bei zwei zur�ckzugebenden Feldern desselben
Satzes ben�tigt SVERWEIS die doppelte Zeit gegen�ber VERGLEICH.
... die Verwendung von VBA und einer bin�ren (oder �hnlichen) Suche in
sequentieller Abarbeitung der Einzelformeln schneller - also einfach Deine
Vorgehensweise. Die Aussage, ab wann ein Excelmodell einzubrechen beginnt,
ist systemabh�ngig.
Alexander Wolff
=SVERWEIS(;;;FALSCH) einen
=SVERWEIS(;;) machst.
Dafür müssen die Daten sortiert sein.
Zwei Möglichkeiten dafür:
http://excelformeln.de/formeln.html?welcher=236
http://sulprobil.com/html/sort_vba.html
Sortierte Daten sind bei mehr als nur wenigen SVERWEISen absolute Pflicht.
Es erhöht die Geschwindigkeit enorm.
Michael Schwimmer
Am Sat, 30 Jan 2010 12:35:19 +0100 schrieb Alexander Wolff:
> ich habe mal 325.000 =SVERWEIS(;;) auf eine 256 Eintr�ge lange sortierte
> Lookup-Spalte angesetzt. Das ben�tigt ca. 1,2 Sekunden.
>
> Ich wollte nun best�tigt sehen, dass SVERWEIS bin�r (oder �hnlich effizient)
> sucht. Das tut es! Denn 256^2 Eintr�ge ben�tigen nur die doppelte Zeit. Bei
> mir brauchen also 325.000 =SVERWEIS(;;) auf 65536 Eintr�ge 2,4 Sekunden.
>
> 2^8 Eintr�ge = 1,2 Sekunden
> 2^16 Eintr�ge = 2,4 Sekunden
> 2^20 Eintr�ge = 3,0 Sekunden (vermutet, da 2007 nicht im Einsatz)
>
> Aber nun kommt es!
>
> Verdopple ich die Anzahl der Formeln auf 650.000, knickt Excel ein. Der
> Speicherbedarf ist permanent hoch und an der Grenze.
Das ist sicherlich auch abh�ngig vom System und der Version.
> Daher ist nach Beachtung aller beschleunigenden Kriterien ...
>
> - Sortierung ist ein absolutes Muss! Wenn die Grunddaten nicht sortiert
> sind, muss man eine sortierte Kopie zumindest tempor�r erzeugen.
Full ACK.
> Die Dauer einer Suche ist ungef�hr proportional zum Exponenten zur
> Basis 2 der Menge der Daten, w�hrend unsortiert die Dauer direkt linear
Die bin�re Suche mit Teilen und Vergleichen ist eben unheimlich effizient.
> proportional ist, also hier 256mal statt nur 2mal so lang.
Ich hatte den Fall getestet, dass 120.000 Formeln mit SVERWEIS in einer
umgekehrt sortierten, im Prinzip also ungeordneten Spalte mit 30.000 Zeilen
suchen und das dauert ewig!
>
> - niemals SVERWEIS zum Auslesen von ganzen Datens�tzen verwenden. Daf�r ist
> VERGLEICH/INDEX zust�ndig. SVERWEIS ist nur f�r eine Einzel-Feldabfrage
> eines Datensatzes gedacht. Bei zwei zur�ckzugebenden Feldern desselben
> Satzes ben�tigt SVERWEIS die doppelte Zeit gegen�ber VERGLEICH.
>
> ... die Verwendung von VBA und einer bin�ren (oder �hnlichen) Suche in
> sequentieller Abarbeitung der Einzelformeln schneller - also einfach Deine
> Vorgehensweise. Die Aussage, ab wann ein Excelmodell einzubrechen beginnt,
> ist systemabh�ngig.
Sehe ich genauso.
Harri Märki
Besten Dank nochmals für Eure Tips. Ich werde zu gegebener Zeit sicher noch
das eine oder andere ausprobieren. um es mit diesen Varianten noch mehr zu
beschleunigen. Die Daten sind nach Spalte B2 aufsteigend sortiert... bei
beiden Dateien. Also kann ich dann ruhig noch etwas rumpröbeln :-).
Habe mein aktuelles Makro noch auf einem etwas neueren Rechner ausgeführt.
Hat da lediglich noch ca 25 Sekunden benötigt. Ist gegenüber den Anfänglichen
Minuten zu den 75 Sekunden (später 45) ein erfreulicher Unterschied.
Gruss Harri
"Alexander Wolff" wrote:
> .
>