Der morgendliche Blick in den Spiegel ist ein eindeutiger Hinweis dafür, dass ich nachts von Aliens gefoltert wurde.

Guten Tag Herr Martin

Ich habe mal eine Funktion gesehen, ich meinte, diese haben sie mir sogar gezeigt, wie man so ein Fenster anzeigen kann, wo diese Informationen ohne VBA ersichtlich sind.

Ich prüfe aktuell ein Excel mit gefühlt 10 Mio Formeln 😉 und wollte die Grösse ermitteln, damit ich danach sagen kann wie viel ich kontrollieren konnte.

Freundliche Grüsse

####

Hallo Herr Schönenberger,

Sie haben nach einer „Funktion“ gefragt.

Haben Sie den Inquire? Schauen Sie mal in Com-Add-Ins nach.

Damit ist die Analyse schnell möglich:

Danke an XLarium – oder natürlich über die Arbeitsmappenstatistik

Prokrastination – ein Problem, dass ich gleich morgen angehe.

Es dauert zirka eine Minute, bis die 1,5 MByte große Datei geöffnet ist. Das Filtern dauert ebenso lange.

In einer Excelmappe befindet sich eine Verknüpfung auf eine externe Datei auf die mittels SVERWEIS zugegriffen wird:

Inquire ermittelt 65.000 Formeln in dieser Datei:

Die Aktivierung der Mulithreadingberechnung (In den Optionen / Erweitert) nutzt nichts:

Sämtliche Prozessoren sind ausgelastet:

Lösungsvorschläge bei „zu vielen Formeln“, die Excel verlangsamen:

* Die verknüpften Daten in die Arbeitsmappe hineinkopieren

* Die automatische Berechnung deaktivieren

* Formeln durch Werte ersetzen

* Statt mit einer Verknüpfung auf die Datei mit PowerQuery (Daten abrufen und transformieren) auf die Daten zugreifen.

* Die Datei als Excel-Binärarbeitsmappe (XLSB) speichern.

 

 

Ich hab noch mal nachgemessen: Ich bin großartig.

Excel hat ein grauenvolles Speichermanagement von Dateien in Bezug auf Formatierungen.

Ein manuelles „Wegformatieren“ nützt nichts – dieser Bereich bleibt im benutzten Bereich ([Strg] + [Ende]). Man muss die Zeilen löschen oder „Alles löschen“.
Beispiel: eine Datei hat eine Dateigröße von 9 Mbyte. Sie ist von Zelle A1 bis U1048576 gefüllt!?!

Löscht man von Zelle A3001 bis zum Ende der Datei nicht nur die Zeilen, sondern auch die Formatierungen, so quittiert Excel dies mit einer Dateigrößenänderung auf 48 Mbyte!?!
Der Grund:
Wenn in einer leeren Excelmappe Zeile 2 markiert und gelb formatiert wird, hat die gespeicherte Datei eine Größe von 7,62 KByte. Markiert man dagegen von B2 bis XFD2 verlangt Excel dafür eine Dateigröße von 45,7 KByte.

Der Grund findet sich in XML, genauer in der Datei: xl\worksheets\sheet1.xml. Der XML-Code der Datei mit der durchgehenden Formatierung sieht folgendermaßen aus:
[…]
<sheetView tabSelected=“1″ workbookViewId=“0″>
<selection activeCell=“A2″ sqref=“A2:XFD2″/>
</sheetViews>
<sheetFormatPr baseColWidth=“10″ defaultRowHeight=“12.75″ x14ac:dyDescent=“0.2″/>
<sheetData>
<row r=“2″ s=“1″ customFormat=“1″ x14ac:dyDescent=“0.2″/>
</sheetData>
[…]
Der XML-Code der Datei, in der Zelle B2:XFD2 formatiert wurde:
<sheetView tabSelected=“1″ topLeftCell=“XER1″ workbookViewId=“0″>
<selection activeCell=“XFC2″ sqref=“A2:XFC2″/>
</sheetView>
</sheetViews>
<sheetFormatPr baseColWidth=“10″ defaultRowHeight=“12.75″ x14ac:dyDescent=“0.2″/>
<sheetData>
<row r=“2″ spans=“1:16383″ x14ac:dyDescent=“0.2″>
<c r=“B2″ s=“1″/>
<c r=“C2″ s=“1″/>
<c r=“D2″ s=“1″/>
<c r=“E2″ s=“1″/>
<c r=“F2″ s=“1″/>
<c r=“G2″ s=“1″/>
<c r=“H2″ s=“1″/>
<c r=“I2″ s=“1″/>
<c r=“J2″ s=“1″/>
<c r=“K2″ s=“1″/>
[…]
<c r=“XEW2″ s=“1″/>
<c r=“XEX2″ s=“1″/>
<c r=“XEY2″ s=“1″/>
<c r=“XEZ2″ s=“1″/>
<c r=“XFA2″ s=“1″/>
<c r=“XFB2″ s=“1″/>
<c r=“XFC2″ s=“1″/>
<c r=“XFD2″ s=“1″/>
</row>
</sheetData>
Insbesondere blähen bedingte Formatierungen Excel-Arbeitsmappen auf. Nicht gut: jede Zelle einzeln mit einer bedingten Formatierung versehen, da dieser Formatierung folgender XML-Code
<conditionalFormatting sqref=“H2″>
<cfRule type=“iconSet“ priority=“24766″>
<iconSet iconSet=“5Rating“>
<cfvo type=“percent“ val=“0″/>
<cfvo type=“percent“ val=“20″/>
<cfvo type=“percent“ val=“40″/>
<cfvo type=“percent“ val=“60″/>
<cfvo type=“percent“ val=“80″/>
</iconSet>
</cfRule>
<cfRule type=“iconSet“ priority=“24765″>
<iconSet iconSet=“5Rating“>
<cfvo type=“percent“ val=“0″/>
<cfvo type=“num“ val=“120″/>
<cfvo type=“num“ val=“140″/>
<cfvo type=“num“ val=“160″/>
<cfvo type=“num“ val=“180″/>
</iconSet>
</cfRule>
</conditionalFormatting>
zugewiesen wird.

Lösungen beim Aufblähen durch Formatierungen:
* Zeilen löschen
* Spalten löschen
* Formatierungen löschen:

* Mit Inquire „Übermäßige Zellformatierungen entfernen“
* Die Datei als Excel-Binärarbeitsmappe (XLSB) speichern. Dies verkleinert die Dateigröße um zirka 50%.
Formate vermeiden (so nicht!):

Könnte mal bitte jemand meinen inneren Schweinehund erschießen?

Amüsiert. Eine Teilnehmerin zeigt mir eine Datei, bei der Excel „sehr weit nach unten springt“, wenn man die Bildlaufleiste nach unten schiebt. Der Grund ist schnell gefunden: Die Tastenkombination [Strg] + [Ende] springt auf die Zelle SI37826. Da etwa 1.100 Zeilen gefüllt sind, benötigt Excel weitere 36.000 Zeilen.

In der Schulung markiere ich die überflüssigen Zeilen und lösche sie. Es klappt: der Cursor springt nur noch bis Zeile 1.177. Gewonnen.

Zu Hause schaue ich mir die Datei in Ruhe an. Ich finde wirklich nichts in dieser Datei. Kein Inhalt, keine nennenswerte Formatierung, keine Namen, kein Druckbereich, …

Ich wende den Inquire an: Übermäßige Zellformatierung entfernen – tatsächlich – es funktioniert: DAMIT sind auch die leeren Zellen entfernt. Ich weiß nicht, was in der Tabelle drin war – aber jetzt ist es draußen!

Wenn Sie es schon nichts vom Stand der Wissenschaft sagen können dann glänzen Sie doch wenigstens mit populären Vorurteilen!

Hallo Herr Martin,

Da wir aktuell des Öfteren Probleme mit der Performance von Excel haben und sich unsere IT-Abteilung damit leider weniger im Detail auskennt wende ich mich an Sie. Vielleicht können Sie uns in folgendem Fall weiterhelfen: im Anhang habe ich eine beispielhafte Datei durch die bei uns beispielsweise beim Filtern sofort alle Prozessoren ausgelastet sind. Die Datei hat weniger als 2 MB und enthält einige Formeln. Können Sie einschätzen, ob es nur an den Formeln liegt oder eventuell an technischen Umständen? Lastet es bei Ihnen ebenfalls gleich alles aus? Wir haben dieses Problem häufiger im Controlling, obwohl die Dateien selten besonders groß sind.

Es wäre super, wenn Sie sich das ganze mal ansehen und mir Ihre Einschätzung dazu mitteilen könnten.
Mit freundlichen Grüßen

####

Hallo Frau Kopov,

Jep – beim Öffnen benötigt die Datei zu viele Ressourcen.

Ich habe mir das Teilchen angesehen. Kennen Sie den Inquire? Ich glaube, wir haben das Add-In in der Schulung nicht besprochen. Sie finden es unter Datei / Optionen / Add-Ins COM-Add-Ins und dort Inquire. Damit kann man Dateien analysieren.

Erstaunlicherweise weigert sich Inquire – irgendetwas scheint an der Datei korrupt zu sein.

Kennen Sie die Tools von Charles Williams: http://decisionmodels.com/index.htm

Damit habe ich versucht Ihre Datei zu putzen. Leider verrät er mir nicht, was los war/ist.

Erstes Ergebnis: Er scheint schneller zu sein.

Spannende Sache – ich werde sie mir am Wochenende mal genauer ansehen.