Microsoft Excel

Herbers Excel/VBA-Archiv

Workbook aufgebläht - wie verschlanken?

Betrifft: Workbook aufgebläht - wie verschlanken? von: Sioo
Geschrieben am: 10.10.2014 10:25:05

Hallo zusammen, habe eine ziemlich überladene Excel Datei mit einer externen Verknüpfung

Mein Problem:
Wenn ich die Daten aktualisieren möchte, brauch die Datei ca. 1 Minute mittlerweile, obwohl sie ca. nur 3 MB hat und die Verknüpfung nur 1MB. Gibt es eine Möglichkeit die Datei zu verschlanken. Habe schon alle veralteten Verknüpfungen gelöscht.

Bringt es evtl was, durch ein Makro beim Start die Datei automatisch zu aktualisieren?

Kann es an überblähten Formeln liegen?
Da sind einige verknüpfte formeln (sverweis, wenn etc)

Leider kann ich die Datei nicht hochladen aus Datenschutzgründen, daher hoffe ich auf allgemeine Tipps

  

Betrifft: AW: Workbook aufgebläht - wie verschlanken? von: Sioo
Geschrieben am: 10.10.2014 10:40:29

Formeln die ich in der Datei verwende könnte man evtl optimieren:

1.) =WENN(X2="rot";WENN(O2="FY14";SVERWEIS(D2;'IPC Database'!A:R;18;0)*T2;Y2*T2);SVERWEIS(D2;'IPC Database'!A:R;18;0)*T2)

2.)=(SVERWEIS(D2;'IPC Database'!A:D;4;0)/1000)*H2

=SVERWEIS(VERKETTEN(N2;L2);Help!C:D;2;0)


  

Betrifft: AW: Workbook aufgebläht - wie verschlanken? von: Daniel
Geschrieben am: 10.10.2014 11:12:00

Hi

wie gross ist denn die Datentabelle in den Blättern 'IPC Database' und 'Help'?
der SVeweis mit 4. Parameter = 0 ist ziemlich langsam, wenn die Suchmatrix gross ist.

ist diese Tabelle sehr gross, würde folgende Massname die Sache extrem beschleunigen:
1. die Tabellen 'IPC Database' und 'Help' müssen nach der ersten Spalte der Suchmatrix des Sverweises aufsteigend sortiert sein (Spalte A bzw C)

2. setze den 4. Parameter des SVerweises = Wahr oder 1

3. diese Variante des Sverweises erzeugt keinen Fehler, wenn der Suchbegriff nicht gefunden wird, sondern verwendet den nächstkleineren Wert als Ergebnis!
wenn nicht sichergestellt ist, dass der Suchbegriff in der Suchspalte vorhanden ist, und in diesem Fall eine Meldung erforderlich ist (also der #NV-Fehler wie beim SVerweis mit 4. Parameter = 0/Falsch, dann muss man etwas aufwendiger formulieren und anstelle des einfachen SVerweises folgende Formel erstellen:

=Wenn(SVerweis(SuchWert;SuchSpalte;1;Wahr)=SuchWert;SVerweis(SuchWert;SuchMatrix;SpaltenNummer;Wahr); #NV) 

(anstelle des Fehlerwertes kann man auch einen anderen Wert ausgeben lassen)
man hat dann zwar 2 SVerweise, diese sind aber immer noch wesentlich schneller als der einfache SVerweis mit 4. Parameter = 0/falsch.

Gruß Daniel


  

Betrifft: AW: Workbook aufgebläht - wie verschlanken? von: Sioo
Geschrieben am: 10.10.2014 11:14:20

Hallo Daniel, danke für deine Antwort. Das Problem hat sich glücklicherweise gelöst. Da war noch eine fehlerhafte Verknüpfung die das alles verlangsamt hat.


  

Betrifft: SVERWEIS ;0 langsam? von: {Boris}
Geschrieben am: 10.10.2014 17:04:20

Hi Daniel,

der SVeweis mit 4. Parameter = 0 ist ziemlich langsam, wenn die Suchmatrix gross ist.

Hör ich so das erste mal. Hab`s grad mal mit ner 20000-Zeilen-Matrix sowie Bereichsangaben wie A:D probiert - Null Verzögerung.
Gibt`s da irgendwo Erhebungen drüber?

VG, Boris


  

Betrifft: Es wäre logisch, dass der SVERWEIS bei ... von: Luc:-?
Geschrieben am: 10.10.2014 17:36:23

…vollem Vgl minimal langsamer, als bei reduziertem ist, Boris,
allerdings sollte sich das erst bei wirklich sehr großen Datenmengen, für die Xl ja eigentlich und ursprünglich gar nicht gedacht ist, bemerkbar machen. Wer so etwas mit Xl macht, statt eine qualifizierte DB-Abfrage zu verwenden, hat selber schuld und muss halt damit leben.
Gruß, Luc :-?


  

Betrifft: AW: SVERWEIS ;0 langsam? von: Daniel
Geschrieben am: 10.10.2014 18:03:13

HI

der SVerweis mit 4. Parameter durchsucht eine Liste von oben nach unten solange bis er auf den Suchwert trifft.
Suchst du nach dem ersten Wert, geht das sehr schnell, suchst du hingeben nach dem Letzten Wert, dann dauert das viel länger.
Der zusammenhang ist Linear, dh je weiter untern der Suchwert in der Liste vorkommt, um so länger dauert die Suche.
Das kann man relativ leicht selber ausprobieren, man muss wahrscheinlich die Formeln ein paar mal kopieren, damit messbare Zeiten zusammen kommen.

Aber in der Realität ist es ja auch, dass wenn ich besipielsweise über den SVerweis in eine Artikelliste mit 20.000 Einträgen neue Preise einspielen will (aus einer anderen, etwa gleich grossen Liste) ich dann auch 20.000 SVerweise habe und jeden Eintrag 1x suche.
Dann habe ich pro SVerweis im Statistischen Mittel eine Suchzeit, die Proportional zur Hälfte der Anzahl der Werte in der Suchmatrix ist.


Die Suche in sortiertn Daten geht ja auch von Hand viel schneller, als die Suche in einer unsortierten Liste, weil ich nicht jeden Wert einzeln prüfen muss, sondern z.B. einfach einen Wert aus der Mitte der Daten mit dem Suchwert vergleichen kann und ich dann sofort weiss, in welcher Hälfte der Daten sich der Suchwert befinden muss und ich dann die andere Hälfte gar nicht mehr überprüfen muss.
Die Zeitbestimmende Aktion ist für Excel der einzelne Wertevergleich.
in der unsortierten Suche reduziert jeder Wertevergleich die Restmenge um 1,
in der sortierten Suche halbiert jeder Wertevergleich die Restmenge.

dh in der unsortierten Suche brauche ich bei 20.000 Datenstätzen im statsischen Mittel 10.000 Zellvergleiche bis ich am Ziel bin, in der Sortierten Suche brauche ich immer 15.

Gruß Daniel


 

Beiträge aus den Excel-Beispielen zum Thema "Workbook aufgebläht - wie verschlanken?"