Excel Datei zu groß

Matze_789_e445fe · 29. November 2010 um 10:53

Guten morgen allerseits,

Ich habe eine recht große Excel Datei erstellt, die aus ca 2700 einzelnen Blättern, einer Vielzahl von Formeln, sowie mit Hyperlink Funktionen ausgestattet ist, um einen schnellen Zugriff auf einzelne Tabellenblätter zu erhalten.

Aufgrund der hier erwähnten Inhalte ist die Datei auf eine beachtliche Größe von ca 23MB angewachsen, was mittlerweile zu erheblichen Ladezeiten beim Öffnen der Datei führt. (Sobald die Datei geladen ist, gibt es keine Einschränkungen in der Funktionalität usw.)

Meine Frage ist nun, ob es möglich ist, die Datei ohne zu großen Aufwand zu komprimieren (kein Zip), um so die Ladezeiten zu Minimieren. Hat jemand Erfahrungen mit dem Handling solcher großen Excel Dateien?

Bin über jeden Hinweis dankbar.

Mit freundlichen Grüßen

Matze_789

Reinhard_fb8cb7 · 29. November 2010 um 11:31

Aufgrund der hier erwähnten Inhalte ist die Datei auf eine
beachtliche Größe von ca 23MB angewachsen, was mittlerweile zu
erheblichen Ladezeiten beim Öffnen der Datei führt. (Sobald
die Datei geladen ist, gibt es keine Einschränkungen in der
Funktionalität usw.)

Meine Frage ist nun, ob es möglich ist, die Datei ohne zu
großen Aufwand zu komprimieren (kein Zip), um so die
Ladezeiten zu Minimieren.

Hallo Matze_789,

Komprimierung kenne ich keine für Excel. Wie wäre es die Formeln erst nach Start der Excelmappe eintragen zu lassen?

Nimm mal eine leere neue Mappe, Alt+F11, Einfügen–Modul, Code reinkopieren.

Dann stellst du den Curser mitten in den Code und drückst F5.
Ist der Code fertig kommt ein Fensterchen mit der Zeit in Sekunden die er brauchte (bei mir 13-15 s, liegt am Rechner und was der Rechner grad noch anderes tut)

Immerhin hat Vba mittels Excel in dieser Zeit eine Formel, die 25 Byte groß/lang ist in 65.536 Zellen von 10 Spalten reingeschrieben und berechnet. Das sind 16,4 MB!

Du kannst ja, vevor du den Code startest, in eine Zelle was reinschreiben dami die Mappe nicht leer ist und abspeichern. Dir dann im Eplorer die Dateigröße anschauen.
Dann nochmals nach dem Codedurchlauf wieder unter neuen namen abspeichern und dann die Dateigrößen vergleichen.

Die Umstzung des Ganzen ist dann so angedacht, beim Schöießen der mappe werden alle/möglichst viele Formeln gelöscht.
Beim Starten der Mappe werden dann die Formeln wieder reingeschrieben.

Je größer man die Zellbereiche machen kann, hier 655.360 Zellen, desto effiktiver und relativ schneller ist das Ganze.
Müßte Vba alle 655.536 Zellen in einer Schleife einzeln abklappern und überall eine Formel reinschreiben weil die Formel nicht kopirbar ist, so kann man die Vba-Idee vergessen, dann biste im Stundenbereich.

Wichtig, der Code ist jetzt noch roh, übe derzeit also nur mit EINER geöffneten Mappe, nicht daß grad ein falsches Fenster bzw. Tabelle aktiv ist. Dort wird dann natürlich auch gnadenlos A1:J65536 überschrieben.

Gruß
Reinhard

Matze_789_e445fe · 29. November 2010 um 19:16

Hmmm das klingt jetzt aber doch speziell bei großen Dateien nach viel Arbeit!

Habe mich in der Zwischnzeit durch mehrere Foren gekämpft und dort war oft die Rede vom *.XLSB Dateiformat.

Die dort erwähnten Vorteile:

Kürzere Ladezeiten (Rechenzeiten)speziell bei großen Dateien!
Weniger Speicherplatz weil Binär!

Aber wie verhalten sich Formeln bei der Umbenennung? Wer hat bereits Erfahrung bei der Verwenung von *.XLXB? Ist dieses Format empfehlenwert für meine oben beschriebene Anwendung?

Vielen Dank für eure Hilfe!

Mit freundlichen Grüßen

Matze_789

Reinhard_fb8cb7 · 29. November 2010 um 20:16

Hallo Matze,

Hmmm das klingt jetzt aber doch speziell bei großen Dateien
nach viel Arbeit!

Arbeit ist relativ, mal einen Arbeitstag opfern um die Datei umzugestalten und danach hat man dann jahrelang bei jedem öffnen viel kürzere Ladezeiten, kann sich gut rechnen.

Habe mich in der Zwischnzeit durch mehrere Foren gekämpft und
dort war oft die Rede vom *.XLSB Dateiformat.

xlxb ist in XL2007 die „alte“ xlb, zumindest bei der excel*.xlb, die wird, wenn sie gelöscht wurde bei jedem Start von Excel erzeugt.
Darin stehen z.B. alle Informationen über deine Menu- und Symbolleisten.

Ich sehe da erstens nicht ob und wie man die als normale datei benutzen kann, gaber garantiert ist da kein kleinere dateigröße.

Die dort erwähnten Vorteile:

Kürzere Ladezeiten (Rechenzeiten)speziell bei großen Dateien!
Weniger Speicherplatz weil Binär!

Glaube ich nicht, also daß du da deine Datei in eine xlsb abspeichern kannst und die lädt dann schneller. Ist mir sehr fremd, wo steht das?
Ein Argument noch warum ich glaube daß du falsch liegst, wenn die Abspeicherung als xlsb DIE Lösung wäre um Mappen zu komprimieren hätte das MS garantiert als Feature eingebaut.

Gruß
Reinhard

Thomas_Ramel · 29. November 2010 um 20:27

Grüezi Matze

Habe mich in der Zwischnzeit durch mehrere Foren gekämpft und
dort war oft die Rede vom *.XLSB Dateiformat.

Die dort erwähnten Vorteile:

Kürzere Ladezeiten (Rechenzeiten)speziell bei großen Dateien!
Weniger Speicherplatz weil Binär!

Aber wie verhalten sich Formeln bei der Umbenennung? Wer hat
bereits Erfahrung bei der Verwenung von *.XLXB? Ist dieses
Format empfehlenwert für meine oben beschriebene Anwendung?

Ich kann dieses Format uneingeschränkt empfehlen.
Ich habe dieses als Standard-Speicher-Format in den Optionen eingestellt, so muss ich nicht mehr unterscheiden ob meine Datei Makros enthält oder nicht *von Natur aus faul bin*

Also einfach mal die Datei öffnen und mit ‚Speichern unter‘ im .XLSB-Format speichern (das ‚Original‘ bleibt dabei ja erhalten) und dann mal die Performance ein wenig testen.

Aber vielleicht solltest Du dennoch überlegen ob es nicht möglich wäre die Datei auf mehrere Mappen aufzuteilen. Mit einem Hyperlink kann ein entsprechender Teil dann ja sehr einfach nachgeladen werden.
Dabei kommt es stark darauf an, wie die einzelnen Tabellenblätter untereinander verknüpft sind. Aber auf diese Weise sind nur die Teile im Speicher, die dann momentan auch verwendet werden.

Mit freundlichen Grüssen

Thomas Ramel

MVP für MS-Excel -

Thomas_Ramel · 29. November 2010 um 20:35

Grüezi Reinhard

Hmmm das klingt jetzt aber doch speziell bei großen Dateien
nach viel Arbeit!

Arbeit ist relativ, mal einen Arbeitstag opfern um die Datei
umzugestalten und danach hat man dann jahrelang bei jedem
öffnen viel kürzere Ladezeiten, kann sich gut rechnen.

Ja, dieser Ansicht bin ich auch - das Ganze muss halt strategisch gut geplant werden, sollte sich dann aber lohnen.

Habe mich in der Zwischnzeit durch mehrere Foren gekämpft und
dort war oft die Rede vom *.XLSB Dateiformat.

xlxb ist in XL2007 die „alte“ xlb, zumindest bei der
excel*.xlb, die wird, wenn sie gelöscht wurde bei jedem Start
von Excel erzeugt.
Darin stehen z.B. alle Informationen über deine Menu- und
Symbolleisten.

Autsch - wo hast Du denn diesen Käse aufgeschnappt…

Seit der Umstellung der Menüleisten auf die Ribbons in xl2007 gibt es diese .xlb-Datei nicht mehr.

Die dort erwähnten Vorteile:

Kürzere Ladezeiten (Rechenzeiten)speziell bei großen Dateien!
Weniger Speicherplatz weil Binär!

Glaube ich nicht, also daß du da deine Datei in eine xlsb
abspeichern kannst und die lädt dann schneller. Ist mir sehr
fremd, wo steht das?

Hier z.B.:

http://blogs.msdn.com/b/excel/archive/2006/07/20/671…

Ein Argument noch warum ich glaube daß du falsch liegst, wenn
die Abspeicherung als xlsb DIE Lösung wäre um Mappen zu
komprimieren hätte das MS garantiert als Feature eingebaut.

Dieses Feature ist ja da, man kann die Dateien als .XLSB abspeichern (bei installiertem Compatibility-Paket auch in den älteren Excel-Versionen).

…aber wenn .XLSB als Standard-Format gewählt worden wäre - wer hätte sich denn dann noch mit .XLSX und .XLSM ‚herumgeschlagen‘…?

Wie ich an Matze bereits geschrieben habe, ist bei mir .XLSB als Standard-Speicherformat eingestellt und ich kann keine Beeinträchtigung irgendwelcher Art feststellen.

Mit freundlichen Grüssen

Thomas Ramel

MVP für MS-Excel -

Reinhard_fb8cb7 · 29. November 2010 um 21:08

Grüezi Thomas,

Arbeit ist relativ, mal einen Arbeitstag opfern um die Datei
umzugestalten und danach hat man dann jahrelang bei jedem
öffnen viel kürzere Ladezeiten, kann sich gut rechnen.

Ja, dieser Ansicht bin ich auch - das Ganze muss halt
strategisch gut geplant werden, sollte sich dann aber lohnen.

aha, lassen wir mal diese komischen xlsb weg, Komprimierungstools kennst du auch nicht? Also wie von mir angedacht, Formeln per Va setzen lassen ist der einzige Weg?

xlxb ist in XL2007 die „alte“ xlb, zumindest bei der
excel*.xlb, die wird, wenn sie gelöscht wurde bei jedem Start
von Excel erzeugt.
Darin stehen z.B. alle Informationen über deine Menu- und
Symbolleisten.

Autsch - wo hast Du denn diesen Käse aufgeschnappt…

Aua Irgendwann, irgendwo. In XL2000 kann man nix als xlb abspeichern, zumindest wird es nicht als Auswahl angeboten.

Seit der Umstellung der Menüleisten auf die Ribbons in xl2007
gibt es diese .xlb-Datei nicht mehr.

? Ach, die Xlsb ist nicht der Nachfolger? Okay.

Glaube ich nicht, also daß du da deine Datei in eine xlsb
abspeichern kannst und die lädt dann schneller. Ist mir sehr
fremd, wo steht das?

Hier z.B.:

http://blogs.msdn.com/b/excel/archive/2006/07/20/671…

Gut, schau ich ich mir an, äh, morgen *glaub*

Ein Argument noch warum ich glaube daß du falsch liegst, wenn
die Abspeicherung als xlsb DIE Lösung wäre um Mappen zu
komprimieren hätte das MS garantiert als Feature eingebaut.

Dieses Feature ist ja da, man kann die Dateien als .XLSB
abspeichern (bei installiertem Compatibility-Paket auch in den
älteren Excel-Versionen).

Wie heißt das dann in der Endung? xlb glaub ich irgendwie nicht.

…aber wenn .XLSB als Standard-Format gewählt worden wäre -
wer hätte sich denn dann noch mit .XLSX und .XLSM
‚herumgeschlagen‘…?

Keiner. Ist auch normal, das m bzw. x gibt ja nur an mit makros oder ohne, EIn Format, von mir aus xls7 für beide Varianten hätte auch gereicht.

Wie ich an Matze bereits geschrieben habe, ist bei mir .XLSB
als Standard-Speicherformat eingestellt und ich kann keine
Beeinträchtigung irgendwelcher Art feststellen.

*gg* hab ich noch gar nicht gelesen, mach ich aber gleich.
Interessiert mich schon ob man mit xlsb Speicherplatz sparen kann bzw. Ladezeiten verringern kann.

Gut’s Nächtle
Reinhard

DuDo · 29. November 2010 um 21:27

Meine Frage ist nun, ob es möglich ist, die Datei ohne zu
großen Aufwand zu komprimieren (kein Zip), um so die
Ladezeiten zu Minimieren. Hat jemand Erfahrungen mit dem
Handling solcher großen Excel Dateien?

Bin über jeden Hinweis dankbar.

Hallo Matze,

ein Tipp aus einem anderen - wirklich sehr hilfreichen - Forum hat mir schon mal sehr geholfen.

Es geht insbesondere um „von Excel angeblich genutzte Bereiche“ in Excel-Tabellen, die aber nicht wirklich genutzt sind.

Schau mal hier:

Bei ca. 2.700 Tabellen könnte man versuchen, das Han…

Stephan_8a4919 · 29. November 2010 um 21:42

Hallo Matze,

Ich habe eine recht große Excel Datei erstellt, die aus ca
2700 einzelnen Blättern, einer Vielzahl von Formeln, sowie mit
Hyperlink Funktionen ausgestattet ist, um einen schnellen
Zugriff auf einzelne Tabellenblätter zu erhalten.

verrate uns doch bitte mal wie man zu einem solchen „Monster“ gelangt? Welche Anwendung steckt dahinter? Wozu braucht man soviele einzelne Blätter?

Aufgrund der hier erwähnten Inhalte ist die Datei auf eine
beachtliche Größe von ca 23MB angewachsen, was mittlerweile zu
erheblichen Ladezeiten beim Öffnen der Datei führt. (Sobald
die Datei geladen ist, gibt es keine Einschränkungen in der
Funktionalität usw.)

Was sind denn für Dich erhebliche Ladezeiten. 1 Minute, 5 Minuten, 10 Minuten??? Lädst du die Dateien lokal, oder vom Netzwerk?

Meine Frage ist nun, ob es möglich ist, die Datei ohne zu
großen Aufwand zu komprimieren (kein Zip), um so die
Ladezeiten zu Minimieren. Hat jemand Erfahrungen mit dem
Handling solcher großen Excel Dateien?

Leider habe ich auch Erfahrungen mit Excel-Dateien, die zwar nicht soviele Worksheets haben, aber noch um einiges größer sind. Wenn irgendwie möglich versuche ich jeden unnötigen Ballast in den Excel-Dateien zu vermeiden. Excel ist zwar ganz nett, aber nicht wirklich performant. Was viele aber immer noch nicht kapiert haben: Excel ist kein Datenbankprogramm. Ab einer gewissen Datenmenge macht es mit Excel einfach keinen Sinn mehr. Ein teilweiser oder vollständiger Umstieg auf z.B. Access kann wahre Wunder bewirken.

Wenn man, aus welchen Gründen auch immer, weder umprogrammieren noch auslagern kann, dann muss man mit der Geschwindigkeit von Excel leben. Ein schneller Rechner ist natürlich von Vorteil.

MfG
Stephan

Thomas_Ramel · 29. November 2010 um 21:42

Grüezi Reinhard

Arbeit ist relativ, mal einen Arbeitstag opfern um die Datei
umzugestalten und danach hat man dann jahrelang bei jedem
öffnen viel kürzere Ladezeiten, kann sich gut rechnen.

Ja, dieser Ansicht bin ich auch - das Ganze muss halt
strategisch gut geplant werden, sollte sich dann aber lohnen.

aha, lassen wir mal diese komischen xlsb weg,
Komprimierungstools kennst du auch nicht? Also wie von mir
angedacht, Formeln per Va setzen lassen ist der einzige Weg?

Ja, das ist so - komprimieren lässt sich da sonst nichts, daran ändern auch die neuen Dateiformate nichts.

xlxb ist in XL2007 die „alte“ xlb, zumindest bei der
excel*.xlb, die wird, wenn sie gelöscht wurde bei jedem Start
von Excel erzeugt.
Darin stehen z.B. alle Informationen über deine Menu- und
Symbolleisten.

Autsch - wo hast Du denn diesen Käse aufgeschnappt…

Aua Irgendwann, irgendwo. In XL2000 kann man nix als xlb
abspeichern, zumindest wird es nicht als Auswahl angeboten.

Ja, wohl weil Du das Compatibility-Paket für die neuen Dateitypen nicht installiert hast…

Seit der Umstellung der Menüleisten auf die Ribbons in xl2007
gibt es diese .xlb-Datei nicht mehr.

? Ach, die Xlsb ist nicht der Nachfolger? Okay.

Nein, einen solchen gibt es nicht - die Darstellung in den Ribbons lässt sich nur noch über XML-Programmierung beeinflussen (und neu in xl2010 auch direkt wieder, zum Glück)

Glaube ich nicht, also daß du da deine Datei in eine xlsb
abspeichern kannst und die lädt dann schneller. Ist mir sehr
fremd, wo steht das?

Hier z.B.:

http://blogs.msdn.com/b/excel/archive/2006/07/20/671…

Gut, schau ich ich mir an, äh, morgen *glaub*

Ein Argument noch warum ich glaube daß du falsch liegst, wenn
die Abspeicherung als xlsb DIE Lösung wäre um Mappen zu
komprimieren hätte das MS garantiert als Feature eingebaut.

Dieses Feature ist ja da, man kann die Dateien als .XLSB
abspeichern (bei installiertem Compatibility-Paket auch in den
älteren Excel-Versionen).

Wie heißt das dann in der Endung? xlb glaub ich irgendwie
nicht.

eben .XLSB

Wie ich an Matze bereits geschrieben habe, ist bei mir .XLSB
als Standard-Speicherformat eingestellt und ich kann keine
Beeinträchtigung irgendwelcher Art feststellen.

*gg* hab ich noch gar nicht gelesen, mach ich aber gleich.
Interessiert mich schon ob man mit xlsb Speicherplatz sparen
kann bzw. Ladezeiten verringern kann.

Ja, das tut es - Speicherplatz nicht unbedingt, aber sicher Performance - steht alles in dem Link oben (in Englisch).

Mit freundlichen Grüssen

Thomas Ramel

MVP für MS-Excel -

Matze_789_e445fe · 29. November 2010 um 22:40

Guten Abend,

eine diese menge an Reaktionen hätte ich für ein auf den ersten Blick triviales Thema nicht erwartet… versuche nun als erstes mal mit den Formaten zu varrieren und falls das nicht den gewünschten Erfolg bringt die „Monster“ datei etwas aufzuspalten…

Werde mich diesbezüglich die tage mal wieder melden:smile:

bis denne

Tschüss

Matze_789_e445fe · 29. November 2010 um 23:17

So eine kurze Wasserstandsmeldung:

Habe die 23MB Datei jetzt mal als XLSB Abgespeicht und bin positiv überrascht. Die Datei ist zwar „nur“ um etwa 3MB geschrumpft, aber die Ladezeit sticht echt positiv heraus: Sie beträgt „nur“ noch etwa 1- 2 min: ist für meine Anwenung vollkommen OK, die Ladezeit im XLSX Format betrug etwa 40 min!!!

und die Funktionen scheinen auch alle noch vorhanden zu sein… mal sehen wie sich das jetzt weiterentwickelt bzw was bei mehrmaligen Speichern geschieht (hört ja die skurilsten Dinge mit sprughaften Dateigrößenanstieg…)

Mal schauen, ich werde euch in jedem Fall mal auf den laufenden halten…

Bis dahin

Tschüss und schönen Abend wünscht

Matze

Thomas_Ramel · 30. November 2010 um 06:31

Grüezi Matze

Habe die 23MB Datei jetzt mal als XLSB Abgespeicht und bin
positiv überrascht. Die Datei ist zwar „nur“ um etwa 3MB
geschrumpft, aber die Ladezeit sticht echt positiv heraus: Sie
beträgt „nur“ noch etwa 1- 2 min: ist für meine Anwenung
vollkommen OK, die Ladezeit im XLSX Format betrug etwa 40
min!!!

Ja, diese Aussagen werden auch in dem Artikel von David Gainer gemacht, den ich in einer Antwort an Reinhard bereits verklinkt habe. Hier werden auch Gründe dafür angegeben, weshalb dem so ist, daher nenne ich diesen Link hier nochmals, es lohnt sich wirklich die Seite zu lesen:

http://blogs.msdn.com/b/excel/archive/2006/07/20/671…

und die Funktionen scheinen auch alle noch vorhanden zu
sein… mal sehen wie sich das jetzt weiterentwickelt bzw was
bei mehrmaligen Speichern geschieht (hört ja die skurilsten
Dinge mit sprughaften Dateigrößenanstieg…)

Ich erwarte hier keine dramatischen Dinge - letztlich wirst Du von dem binären Dateiformat ausser dem schnelleren öffnen/berechnen kaum etwas bemerken.

Mal schauen, ich werde euch in jedem Fall mal auf den
laufenden halten…

Ja, darüber freuen wir uns, das erweitert immer wieder den Erfahrungsschatz.

Mit freundlichen Grüssen

Thomas Ramel

MVP für MS-Excel -

Reinhard_fb8cb7 · 30. November 2010 um 11:25

Hallo Matze,

Habe die 23MB Datei jetzt mal als XLSB Abgespeicht und bin
positiv überrascht. Die Datei ist zwar „nur“ um etwa 3MB
geschrumpft, aber die Ladezeit sticht echt positiv heraus: Sie
beträgt „nur“ noch etwa 1- 2 min: ist für meine Anwenung
vollkommen OK, die Ladezeit im XLSX Format betrug etwa 40
min!!!

aha, interessant, gut zu wissen.

und die Funktionen scheinen auch alle noch vorhanden zu
sein… mal sehen wie sich das jetzt weiterentwickelt bzw was
bei mehrmaligen Speichern geschieht (hört ja die skurilsten
Dinge mit sprughaften Dateigrößenanstieg…)

Ich erwarte da jetzt von der anderen Speicherungsart als xlsb in der Hinsicht keine bösen Überraschungen.
Gelegentlich neigt Excel grundsätzlich dazu unerwartet und ohne sichtbare Gründe Mappen aufzublähen.

Dann gibt es m.E. nur eine saubere Lösung, alle Blätter einzeln in eine neue Mapppe zu übertragen.
Bei so vielen Blättern natürlich nur mit Vba.

Was du noch tun kannst um Bytes zu sparen. Benutze für oft benutzte Formeln Namen.
Hier ist ein Beispiel von mir.
Auch wenn dir die Formeln unbekannt vorkommen, das macht nix.
Du siehst, in jeder Zelle steht nur noch „=Nam“, also 4 bytes.

Bei den vergebenen Namen erkennst du wieviele Bytes aber PRO Zelle stehen würden wenn keine Namen benutzt worden wären.
Rechne das mal auf 100.000 Zellen hoch…

Formelnamen sind anfangs gewöhnungsbedürftig, aber später sogar leichter zu pflegen/ändern als Monsterformeln.

Gruß
Reinhard