Ich habe einige Punkte von der ToDo-Liste auf die WasSolls-Liste gesetzt.

Amüsant. Während Inquire auch ausgeblendete Blätter anzeigt (sowohl hidden als auch very hidden)

zeigt PowerQuery nut die sichtbaren Blätter an:

Nein! Nicht ganz. Wenn man die Daten nun transformiert und zurück zur Source wechselt, so sieht man auch dort alle Blätter – egal ob sichtbar oder ausgeblendet:

Danke an Dominique Dauphin für diesen wertvollen Hinweis!

Die Dummheit ist die sonderbarste aller Krankheiten. Der Kranke leidet nie unter ihr. Die, die leiden, sind die anderen.

Heute in der Excel-Schulung wollte ich den Inquire vorstellen. Ein praktisches, nützliches und gutes Werkzeug. Und dann das:

Excel: nein! Diese Arbeitsmappe ist NICHT beschädigt! Und … die IT ist so weit …

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!

Für [wahre] Freunde geh ich durch die Hölle… den [anderen] zeig ich gern den Weg dorthin.

Gerade mit Andreas Thehos diskutiert (danke für den Hinweis – Andreas!):

Ich trage in A1 eine Zahl ein. Ich nenne diese Zelle MWSt. Ich trage in D1 einen anderen Wert ein und nenne diese Zelle redMWSt.

Ich lasse folgendes Makro über das Dokument laufen:

ActiveWorkbook.Names(„MWSt“).Visible = False

Damit taucht der Name „MWSt“ nicht mehr in der Liste der Namen (im Namensfeld oder im Namensmanager) auf.

Man kann allerdings damit arbeiten:

=500*MWSt

Ganz blöde: der Inquire übergeht auch den ausgeblendeten Namen:

Wegen Rückrufaktion vorübergehend aus dem Sortiment genommen

Schon erstaunt.

Ich schreibe in eine Excelarbeitsmappe einen Text.

Ich speichere die Datei. Benenne die Endung um in „.zip“. Ich entzippe die Datei, suche im Ordner „xl“ nach der Datei „sharedStrings.xml“ und öffne sie in einem guten XML-Editor:

Ich will es wissen! Ich füge weitere Tags hinzu:

Speichere die XML-Datei, zippe das Archiv und sehe nach. Kein Fehler beim Öffnen der Datei (!), kein weiterer Text wird angezeigt. Das Ergebnis sieht so aus:

Ich schaue nach: Datei / Informationen / Auf Probleme überprüfen – Excel findet keine Probleme:

Keine Benutzerdefinierten XML-Daten. Ich schaue im Inquire nach – auch dort wird nichts gefunden:

Das heißt: Hacker aller Länder: versteckt Eure Daten in XML-Elementen! Das findet kein Mensch!

Randbemerkung: okay, okay – man darf nicht jeden beliebigen Knoten an jeder Stelle platzieren. Aber das hat man schnell herausgefunden, was man darf.

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.

Warum ist das Ansetzen des Rotstifts eine so bewährte Methode, wieder schwarze Zahlen zu schreiben?

Inquire ist schon klasse. Er analysiert Tabellen und findet eine Menge „verborgener“ Inhalte: ausgeblendete Zeilen, weiße Schriftfarbe, … Allerdings: zwei Dinge findet dieses Analysewerkzeug nicht:

1.) Wenn eine Form auf einer Zelle liegt (hier etwas versetzt, damit man sie besser sehen kann)

2.) Wenn eine Zelle (hier E4) benutzerdefiniert mit

;;;

formatiert ist. Der leere Wert vor dem ersten Semikolon gibt an: positive Zahlen werden nicht dargestellt; der zweite legt das Gleiche für negative fest. Der dritte für 0.

Toleranz ist der Verdacht, dass der andere Recht hat.

Ich werde regelmäßig gefragt, wie man Exceltabellen „kleiner“ machen kann. Vor allem bis Excel 2003, in denen *.xls noch kein gezipptes XML-Archiv war, wurden Dateien schnell sehr groß.

Ich habe eine Reihe von Vorschlägen.

Einige davon nimmt Inquire mit seinem Werkzeug „Übermäßige Zellformatierung entfernen“ ab. Auf der Seite

https://support.office.com/de-de/article/Verwendungsm%C3%B6glichkeiten-f%C3%BCr-Inquire-ebaf3d62-2af5-4cb1-af7d-e958cc5fad42

wird erklärt: „Haben Sie schon einmal eine Arbeitsmappe geöffnet und festgestellt, dass sie langsam geladen wird oder riesengroß geworden ist? Möglicherweise ist in der Arbeitsmappe auf Zeilen oder Spalten Formatierung angewendet, von der Sie nichts wissen. Verwenden Sie den Befehl Übermäßige Zellformatierung bereinigen, um übermäßige Formatierung zu entfernen und die Dateigröße deutlich zu verringern. Hiermit können Sie ein „Arbeitsblattaufblähen“ vermeiden, wodurch Excel schneller wird.“ Und auf

https://support.office.com/de-de/article/Bereinigen-von-%C3%BCberm%C3%A4%C3%9Figer-Zellformatierung-auf-einem-Arbeitsblatt-e744c248-6925-4e77-9d49-4874f7474738

lesen wir: „Das Bereinigen von übermäßiger Formatierung wird ausgeführt, indem Zellen vom jeweiligen Arbeitsblatt entfernt werden, die sich hinter der letzten nicht leeren Zelle befinden. Wird eine bedingte Formatierung beispielsweise auf eine gesamte Zeile angewendet und sind in der Zeile nur bis zur Spalte V Daten enthalten, wird die bedingte Formatierung aus den Zellen entfernt, die auf die Spalte V folgen.“

20160818Inquire01

Übermäßige Zellformatierung entfernen

Übermäßige Zellformatierung entfernen

 

Ist das alles? Kein Putzen von doppelten Einträgen in der bedingten Formatierung? Kein Zusammenfassen von ähnlichen Farben? Kein Löschen von nicht verwendeten Formatvorlagen? Oder Zahlenformaten? Keine Vorschläge zum Verkleinern von Bildern? Da wäre noch einiges zu tun …

Natürlich könnte man morgens vor dem dritten Kaffee ein konstruktives Gespräch führen. Theoretisch wäre ja auch Weltfrieden möglich.

Die Idee, endlich in Excel eine gute (!) Funktion zu implementieren, mit deren Hilfe man zwei Dateien vergleichen kann, war dringend nötig. Und so findet sich seit Excel 2013 (wenn Sie die richtige Version gekauft haben) hinter der Registerkarte „Inquire“ eine Schaltfläche „Dateien vergleichen“:

Dateien vergleichen

Dateien vergleichen

Das Ergebnis von zwei „größeren“ Dateien begeistert:

Tabellen vergleichen

Tabellen vergleichen

Jedoch weniger das Design, wenn es darum geht, VBA-Code zu vergleichen. Zugegeben – die Option ist praktisch – das Ergebnis schwer zu interpretieren.

VBA1 und VBA2

VBA1 und VBA2

Nett kann ich auch – bringt aber nix!

Microsoft hat in Excel 2013 das Analysewerkzeug „Inquire“ eingeführt, das in Excel 2016 nicht geändert wurde. Damit erspart man sich die umständlich Suche, ob es Verknüpfungen, ausgeblendete Zeilen, Spalten, Blätter gibt, ob Zahlen als Text formatiert wurden, wo Formeln stecken, die einen Fehler liefern, wo Zirkelbezüge zu finden sind, …

Inquire

Inquire

Damit ist Microsoft auf dem richtigen Weg. Für alle, die fremde Dateien analysieren möchten („Was hat der Kollege denn da gemacht?“) oder die zwei Dateien miteinander vergleichen möchten – ein richtiger Schritt in die richtige Richtung. Jedoch mir fehlen:

  • eine Anzeige für „Genauigkeit wie anzeigen“
  • Überhaupt einige Optionen, die Anwender zur Verzweiflung bringen können: „in Zellen mit Nullwerten eine Null anzeigen“, „anstelle der berechneten Werte Formeln anzeigen“, „Dezimalkomma automatisch einfügen“, „1904-Datumswert, „Iterative Berechnung aktivieren“, „manuelle Arbeitsmappenberechnung“ und einige andere hübsche Optionen
  • ausgeblendete Zeilen und Spalten zu finden ist klasse – was aber, wenn die Zeilenhöhe auf 0,1 pt gesetzt wurde?
  • zu schmale Spalten – Zahlen werden mit dem Zahlenzeichen ########## dargestellt.
  • zu viele oder widersprüchliche bedingte Formatierungen
  • Rundungsfehler bei Zahlenformaten
  • Unsinnige Zahlenformate (beispielsweise 0,0 „%“). Allerdings: Wer entscheidet, was unsinnig ist?
  • Zahlenformate wie ;;
  • Zellen mit Leerzeichen
  • Zellen, die Text enthalten mit einem Leerzeichen am Ende: „Rene Martin“ ist etwas anderes als „Rene Martin „
  • Und schließlich: Objekte: Diagramme, die auf ein Pixel verkleinert wurden, Bilder, die auf Zellen liegen und so aussehen, als wären es Elemente der Zelle oder auch weiße Rechtecke, die auf einer Zelle liegen:

Wird leider nicht vom Inquire gefunden

Wird leider nicht vom Inquire gefunden – unter dem Rechteck befindet sich die Zahl 3000

Fazit: Guter Ansatz, muss jedoch erweitert werden. Wenn Microsoft mich fragen würde – ich könnte Ihnen viele Dinge nennen, die Anwendern Probleme verursachen.

Und: ein dankeschön an Stefan, der mir geholfen hat, das Teilchen auseinanderzunehmen.

Oh Herr, lass Hirn regnen und nimm den Bedürftigen die Regenschirme weg.

Inquire ist ein mächtiges Tool, das eine komprimierte Datenanalyse einer Arbeitsmappe liefert, für deren Elemente man lange suchen müsste. Ich gestehe – ich habe es nicht gleich verstanden:

Die einzelnen gefundenen Elemente werden auf der linken Seite aufgelistet und sind dort einsehbar. Warum jedoch liefert der Export in eine andere Datei keine Informationen, sondern schreibt einfach nichts in die leere Datei?

Wohin verschwinden die Informationen?

Wohin verschwinden die Informationen?

Die Antwort: Ich hätte auf der linken Seite die Objekte auswählen müssen, die ich exportieren möchte. An vielen Stellen warnt Excel – hier jedoch nicht und überlässt dem erstaunt auf die leere Arbeitsmappe Schauenden die Suche. Ein Hinweis, dass nichts ausgewählt wurde wäre einfach schön gewesen … Vielleicht in Excel 2019.

Es gibt Augenblicke, da gelingt uns alles. Kein Grund zu erschrecken, das geht vorüber.

In Excel 2010 musste man die PowerPivots hinzuladen. In Excel 2016 sind sie in Excel integriert. Und Excel 2010? Nun – man muss sie aktivieren: Datei / Optionen / Add-Ins / Verwalten – und ein Klick auf PowerPivot? Aber wo sind sie? Auch „Hinzufügen“ hilft nicht weiter – ich finde sie nicht.

Wo ist PowerPivot?

Wo ist PowerPivot?

Die Antwort: Das PowerPivot-Add-In ist kein Excel-Add-In, sondern ein COM-Add-In. Wird diese Option ausgewählt, kann man sie leicht hinzufügen:

20160619PowerPivot02

Und schon erscheinen PowerPivot, Inquire und Power View.

Und schon erscheinen PowerPivot, Inquire und Power View.