Cell Format Analyzer

  Cell Format Analyzer 2.0

Wichtiger Hinweis

Die Software Cell Format Analyzer 2.0 wurde von Grund auf neu konzipiert und wird nun in Form von mehreren einzelnen Tools angeboten, zusammengefasst unter dem Produktnamen 'CEFA-Toolbox'. Jedes Tool aus der CEFA-Toolbox ist ein eigenständiges Add-In für Microsoft Excel (ab Version 97) und besitzt ein Spezialgebiet. Beispielsweise befasst sich das Werkzeug mit der Bezeichnung 'Style Expert' ausschliesslich mit Formatvorlagen in Microsoft Excel. Das Utility 'Cell Format Counter' dient zum Ermitteln verschiedener Kennzahlen über verwendete, verfügbare und maximale Anzahl Zellformate in einer Arbeitsmappe und mehr. Die CEFA-Toolbox umfasst momentan 24 verschiedene Add-Ins.

Auf der CEFA-Toolbox Produkt-Website (http://www.xlam.ch/products/cefa-toolbox/index.htm) finden Sie umfassende Informationen über die CEFA-Toolbox, die momentan verfügbaren Add-Ins, viele Tipps und Tricks, Benutzer-Anleitungen, sämtliche Dateien zum kostenlosen Download und vieles mehr.



 

Kurzbeschreibung

Der Cell Format Analyzer ist ein einzigartiges Tool zum Analysieren und Optimieren der in einer Microsoft Excel-Arbeitsmappe verwendeten Formatierungen.

Ursprünglich war der Cell Format Analyzer (Version 1.0) lediglich ein kleines Hilfsmittel, mit dem man herausfinden konnte, wieviele verschiedene Zellformate von den Zellen einer Arbeitsmappe verwendet wurden. Inzwischen wurde der Funktionsumfang stark erweitert. Das Tool stellt in der neuesten Version leistungsfähige Funktionen zur Verfügung, mit denen man die verwendeten Zellformate einer gesamten Arbeitsmappe oder wahlweise auch nur von einzelnen Arbeitsblättern auflisten, analysieren und optimieren kann. Zudem kann der benutzte Zellbereich von jedem einzelnen Tabellenblatt auf Knopfdruck optimiert werden, wobei Spezialfälle wie ausgeblendete Zeilen/Spalten, verbundene Zellen und eingebettete Objekte ausnahmslos berücksichtigt werden.

Eine Beschreibung der Stärken, Funktionen und Features des Cell Format Analyzers finden Sie unter Die Stärken des Cell Format Analyzer.

 

Hintergrund-Info "Zellformate"
In einer Excel-Arbeitsmappe können maximal ca. 4'000 verschiedene Zellformate verwendet werden. Wird diese Limitation überschritten, erscheint die Fehlermeldung "Zu viele verschiedene Zellformate". Bei grösseren Mappen mit vielen Tabellenblättern oder bei Blättern mit einer aufwändigen Gestaltung ist diese Grenze ziemlich schnell erreicht. Stellen Sie sich vor, die unterschiedlich formatierten Zellen des einzigen Tabellenblattes einer Mappe ergeben insgesamt 200 verschiedene Zellformate, wobei keine Zelle den Schriftgrad 11 Punkte benutzt. Wenn Sie dieses Tabellenblatt kopieren und einfügen (in der gleichen Mappe), sämtliche Zellen selektieren und dann die 11 Punkt-Schriftgrösse einstellen, so besitzt die Arbeitsmappe bereits 400 verschiedene Zellformate.

Vielleicht denkt man nun, dass sich anhand den existierenden Formatierungseigenschaften einer Zelle (Zahlenformat, Ausrichtung, Schrift, Rahmen, Muster, Schutz) gar nicht 4'000 verschiedene Kombinationen bilden lassen. Und ob man das kann! Mathematisch gesehen lassen sich bei 100 installierten Schriftarten über 7 Milliarden verschiedene Zellformate bilden!

In meinen Tests unter Microsoft Excel 97 und Excel 2000 konnte ich immer maximal 197 benutzerdefinierte Zahlenformate anlegen. Beim Anlegen des 198. Zahlenformates trat der Fehler "Es können keine weiteren benutzerdefinierten Formate mehr hinzugefügt werden." auf. Die von Microsoft angegebene Spezifikation "Durch den verfügbaren Speicher begrenzt" ist somit meiner Meinung nach falsch.

Detaillierte Informationen über die Limitation "Maximale Anzahl verschiedene Zellformate" erhalten Sie auf der Seite Limitationen in Microsoft Excel von XLimits.

To Top


Zweck und Nutzen

Mit dem Cell Format Analyzer finden Sie heraus, wieviele verschiedene und welche Zellformate in einer Arbeitsmappe existieren.

Es wird geprüft, ob der benutzte Zellbereich optimal eingestellt ist.

Es wird geprüft, ob sich die letzte Zelle innerhalb dieses Bereiches befindet (verbundene Zellen!).

Der Cell Format Analyzer reduziert die Anzahl benutzter Zellen auf ein absolutes Minimum, sodass Arbeitsspeicher eingespart, die Arbeitsmappendatei verkleinert und die Anzahl verschiedener Zellformate reduziert wird.

Während der Bearbeitung einer Arbeitsmappe kann es schnell mal passieren, dass der benutzte Zellbereich unnötig gross eingestellt wird. Oft kann man nichts dagegen tun, da Excel den Bereich automatisch und selbständig ermittelt.

Das Verbinden von mehreren Zellen zu einer einzigen Zelle kann unbewusst zu neuen Zellformaten führen. Es ist problemlos möglich, jede einzelne Zelle eines zu einer Zelle verbundenen Bereiches unterschiedlich zu formatieren. Da aber nur das Zellformat der Hauptzelle des verbundenen Zellbereich für die Anzeige massgebend ist, sind die anderen Zellformate überflüssig und können vorbehaltslos gelöscht werden, ohne dabei die Darstellung der verbundenen Zelle zu verändern.

Beispiele
Beispiel "AutoFormen":
1. Neues Tabellenblatt einfügen
2. Text in Zelle A1, B2 und D4 eintragen
3. AutoForm einfügen und vergrössern (Zellbereich A1:E5)
4. Spalten C:IV markieren und löschen
Die AutoForm wird gestaucht.

Beispiel "Ausgeblendete Spalten":
1. Neues Tabellenblatt einfügen
2. Text in Zelle A1, B2 und D4 eintragen
3. Spalte D markieren und ausblenden
4. Zelle A1 selektieren
5. Gehe zu letzte Zelle
Zelle B2 wird selektiert.

To Top


Stärken

Der Cell Format Analyzer besitzt eine Vielzahl Stärken, die ihn einzigartig machen. Hier eine kleine Auswahl:

- Automatisierte Vorgänge:


Die Funktionen können einzeln (z.B. "Formatvorlagen auflisten" und "Unbenutzte benutzerdefinierte Zahlenformate löschen") oder im Rahmen einer Gesamt-Verarbeitung automatisch nacheinander ausgeführt werden. Grössere Verarbeitungsabläufe werden durch Assistenten unterstützt, die Sie durch die einzelnen Verarbeitungsschritte führen. Alle Assistenten besitzen eine benutzerfreundliche und intuitiv bedienbare grafische Oberfläche.

Klicken für grösseres Bild Klicken für grösseres Bild

Der Cell Format Analyzer verwendet hochoptimierte Programmroutinen zur Ermittlung, welche Zellformate die Zellen eines Tabellenblattes besitzen. Auf einem Computer mit einer 1 GHz Prozessor (CPU) werden ca. 125 Zellen pro Sekunde eingelesen und aufgelistet (0,008 Sekunden/Zelle). Erfahrungsgemäss enthält ein Tabellenblatt im Durchschnitt ca. 1'500 benutzte Zellen (dieser Erfahrungswert stimmt auch für grosse Arbeitsmappen und professionelle bzw. kommerzielle Excel-Anwendungen). Die Analyse eines Tabellenblattes dauert somit nur etwa 12 Sekunden. Eine durchschnittliche grosse Arbeitsmappe enthält zwischen 30 und 40 Tabellenblätter, sodass die Analyse einer gesamten Arbeitsmappe lediglich 6 bis 8 Minuten dauert.


Sind die Formatierungseigenschaften der Zellen einmal eingelesen, können jederzeit beliebige Analysen und Optimierungen durchgeführt werden. Dies ist auch zu einem späteren Zeitpunkt möglich, d.h. nach Beenden und erneutem Starten von Microsoft Excel. Die Dateien mit den Reports und Zelllisten müssen Sie nicht einzeln selektieren und öffnen, sondern können dazu den Cell Format Analyzer-Menübefehl "Arbeitsdateien öffnen" verwenden. Sie müssen lediglich angeben, in welchem Ordner sich der Analysereport der gewünschten Arbeitsmappe befindet, und schon sucht sich das Tool die benötigten Dateien selbständig zusammen und öffnet diese.


Bei der Zellformatanalyse werden sämtliche Zelleigenschaften, die einen Einfluss auf das Format besitzen, abgefragt und analysiert. Ein Zellformat setzt sich aus insgesamt 59 verschiedenen Merkmalen zusammen (unter anderem Zahlenformat, horizontale Textausrichtung, Texteinzug, Schriftart, Schriftgrad, Farbe der oberen Rahmenlinie, Zellenhintergrundmuster und Zellsperrung). Dieser höchstmögliche Detaillierungsgrad erlaubt die Erstellung von beliebigen Auswertungen aller Art. Da nach dem Einlesen der Zellformatierungen das Tool weiss, wie jede einzelne Zelle formatiert ist, können Sie unter anderem gezielt nach einer ungewöhnlichen Schriftart, zum Beispiel "Book Antiqua" suchen. Sämtliche Formatierungseigenschaften lassen sich in frei wählbaren Kombinationen filtern.

Beispiel "Auswertungen":
Man kann zum Beispiel abfragen, welche Zellen in der Arbeitsmappe die Schriftart "Arial" besitzen, einen Rahmen mit einer durchgezogenen, dicken, blauen Rahmenlinie verwenden, das Hintergrundmuster "Horizontal gestreift" aufweisen und gesperrt sind. Eine zweite Abfrage könnte alle Zellen ermitteln, die die gleichen Kriterien besitzen, jedoch nicht gesperrt sind. Die Zellen dieser beiden Filterergebnisse können anschliessend markiert und die Zellformate vereinheitlicht werden, indem man beispielsweise den Zellschutz bei allen Zellen auf gesperrt stellt. Dadurch reduziert sich die Anzahl verschiedener Zellformate.


Das Tool stellt erweiterte Funktionen zur Verfügung, mit denen man spezielle Formatierungsmerkmale untersuchen und bearbeiten kann. Zu diesen Merkmalen gehören Formatvorlagen, Zahlenformate, Schriften, verbundene Zellen und sogenannte Mixed-Formate.

Beispiel "Formatvorlagen":
Es ist unter anderem möglich, eine Liste der in der Arbeitsmappe gespeicherten Formatvorlagen zu erstellen. Da nicht alle gespeicherten Vorlagen tatsächlich von Zellen benutzt sein müssen, kann man unbenutzte Formatvorlagen auf Knopfdruck löschen. Microsoft Excel stellt zwar eine Löschen-Funktion zur Verfügung; man sieht jedoch nicht, ob die zu löschende Vorlage allenfalls von Zellen aktiv verwendet wird. Mit dem Cell Format Analyzer lassen sich auch sämtliche Zellen auflisten, die eine bestimmte Formatvorlage verwenden. Diese Möglichkeit bietet Microsoft Excel nicht. Wenn man in Microsoft Excel eine Vorlage löscht, erhalten alle mit dieser Formatvorlage formatierten Zellen automatisch das Format "Standard" zugewiesen. Im Cell Format Analyzer kann man dagegen auswählen, welche Formatvorlage zugewiesen werden soll.

Beispiel "Zahlformate":
Der Cell Format Analyzer generiert auf Knopfdruck eine Liste der benutzerdefinierten Zahlenformate. In Microsoft Excel sieht man zwar diese Zahlenformate im Zellformatierungsdialog, wobei der Cell Format Analyzer noch einen Schritt weiter geht und zu jedem Zahlenformat angibt, von welchen Zellen es benutzt wird. Eine weitere interessante Funktion ist das Löschen von benutzerdefinierten Zahlenformaten. Auch in Microsoft Excel kann man benutzerdefinierte Zahlenformate löschen. Dabei wird jedoch weder geprüft, ob das Zahlenformat von Zellen verwendet wird, noch ob es Bestandteil einer Formatvorlage ist. Der Cell Format Analyzer prüft beides.

Beispiel "Schriften":
Die von einer Zelle verwendete Schrift ist Bestandteil des Zellformates und wird somit automatisch vom Cell Format Analyzer für alle benutzten Zellen aufgelistet. Diese Informationen dienen als Grundlage für spezielle Schrift-Analysen. Da in Microsoft Excel 97 pro Excel-Sitzung maximal 256 verschiedene Schriften verwendet werden können (d.h. alle geöffneten Arbeitsmappen zusammen), ist es äusserst wichtig, dass eine Mappe, die in Excel 97 bearbeitet werden soll, so wenig Schriften wie möglich enthält. In Microsoft Excel 2000 und neuer können bis zu 512 verschiedene Schriften benutzt werden (gewöhnlich sogar pro geöffnete Mappe, und nicht pro Excel-Sitzung). Der Cell Format Analyzer macht Vorschläge, welche Schriften aufgrund ihrer Ähnlichkeit zusammengefasst werden können und somit die Anzahl verschiedener Schriften reduziert werden kann.


Auf Wunsch lässt sich ein sogenannter Vorher/Nachher-Bericht generieren. Dabei wird ein Analysereport einem anderen, älteren Analysereport gegenübergestellt. Auf diese Weise kann man sehr einfach herausfinden, was sich in dem Zeitraum zwischen der Erstellung der beiden Reports alles verändert hat. Der Vorher/Nachher-Bericht zeigt unter anderem, wie stark die Grösse des benutzten Zellbereiches jeder einzelnen Tabelle reduziert werden konnte und wie gross die Arbeitsmappendatei nach der Optimierung noch ist.


Das Tool verwendet komplexe Routinen zum Optimierung des benutzten Zellbereiches eines Tabellenblattes und zum Zurücksetzen der sogenannten letzten Zelle. Die Programmroutinen berücksichtigen Spezialitäten wie ausgeblendete Zeilen/Spalten, verbundene Zellen und eingefügte Objekte (Bilder, AutoFormen, Steuerelemente etc.), die sich innerhalb des benutzten Bereiches befinden. Vor allem aber werden - und das macht den Cell Format Analyzer so einzigartig - Spezialfälle und Ausnahmen korrekt verarbeitet. Es gibt nämlich Zellen, die sich ausserhalb des benutzten Zellbereiches befinden, aber trotzdem einen bestimmten Zweck erfüllen und daher nicht gelöscht werden dürfen: Zum Beispiel erkennt das Tool leere Zellen, die eine bedingte Formatierung besitzen, sich aber ausserhalb des benutzten Zellbereiches befinden. Das Gleiche gilt für Zellen mit einer Gültigkeitsprüfung sowie für benannte Zellen. All diese Spezialfälle werden von gängigen, frei erhältlichen VBA-Makros nicht berücksichtigt (auch das von Microsoft publizierte Makro zum Zurücksetzen der letzten Zelle berücksichtigt diese Fälle nicht).


Die kommende Version 2.0 des Cell Format Analyzers wird die enormen Datenmengen, die zur Durchführung einer Analyse benötigt werden, ohne nennenswerte Probleme bewältigen. Man sollte berücksichtigen, dass eine umfangreiche Arbeitsmappe sehr viele Zellen enthält. Angenommen, eine Mappe besitzt 40 Tabellenblätter mit durchschnittlich 2'000 benutzten Zellen pro Blatt, so gibt das bei rund 60 Zellformateigenschaften eine Anzahl von 4'800'000 Daten. Ausgewachsene Excel-Anwendungen enthalten ohne weiteres 200'000 Zellen, was bei 60 Formateigenschaften pro Zelle die Anzahl von insgesamt 12 Millionen Daten gibt.


Durch die Optimierung der Zelldarstellung wird die Arbeitsmappendatei kleiner, was folglich zu einem schnelleren Öffnen und Speichern der Mappe führt. Zudem beansprucht die Arbeitsmappe weniger Arbeitsspeicher und Systemressourcen, da Zellformate vergleichsweise viel Speicher benötigen. Die Optimierung bewirkt ausserdem, dass mehrere Excel-Limitationen weniger schnell erreicht werden, insbesondere die Limitationen "Anzahl verschiedene Schriften", "Anzahl verschiedene Zellformate" und "Anzahl verschiedene Zahlenformate".


Versuchen Sie einmal in Microsoft Excel ...
... herauszufinden, welche Formatvorlagen tatsächlich von Zellen benutzt werden.
... herauszufinden, wieviele Zellen eine bestimmte Formatvorlage benutzen.
... herauszufinden, welche Zellen eine bestimmte Formatvorlage benutzen.
... alle Zellen zu markieren, die eine bestimmte Formatvorlage benutzen.
... herauszufinden, welche benutzerdefinierten Zahlenformate tatsächlich von Zellen verwendet werden.
... herauszufinden, wieviele Zellen ein bestimmtes Zahlenformate verwenden.
... herauszufinden, welche Zellen ein bestimmtes Zahlenformate verwenden.
... alle Zellen zu markieren, die ein bestimmtes Zahlenformat verwenden.
... alle Zellen zu markieren, die einen Rich-Text-formatierten Zellinhalt besitzen.

To Top


Benutzer-Zielgruppe

Grundsätzlich kann der Cell Format Analyzer von jedermann eingesetzt werden. Besonders interessant ist das Tool für Anwender, die mit mittelgrosse und grossen Arbeitsmappen arbeiten. Aber auch bei kleinen Arbeitsmappen lohnt sich der Tooleinsatz allemal, da jede beliebige Mappe analysiert und optimiert werden kann.

- Excel-Anwender (Einsteiger, Fortgeschrittene und Experten)
- Excel-Programmierer (Einsteiger, Fortgeschrittene und Experten)

To Top


Screen Shots

Die nachfolgenden Abbildungen zeigen verschiedene Fenster der momentanen Entwickler-Version (Stand 17.06.2004). Das endgültige Aussehen der Dialogfenster kann sich bis zum fertigen Release noch ändern.


Abbildung: Der Analyse-Assistent (Schritt 2)


Abbildung: Der Analyse-Assistent (Schritt 3)


Abbildung: Das Schriften-Dialogfenster


Abbildung: Das Verbundene Zellen-Dialogfenster


Abbildung: Der Spaltenformatierung-Optimierer


Abbildung: Das Zahlenformate-Dialogfenster


Abbildung: Das Objekte-Dialogfenster


Abbildung: Das Formatvorlagen-Dialogfenster


Abbildung: Der Zellbereich-Experte


Abbildung: Der Zellbereich-Optimierer

 

To Top


Autor

Entwickelt von: Philipp von Wartburg
www.xlam.ch

To Top


Produkt-Info

- Cell Format Analyzer Version 2.0
- Microsoft Excel Add-In (xla)
- Für Microsoft Excel 97, 2000, 2002/XP und 2003
- Mit Online-Hilfe

- Freeware

To Top


Download

Der Cell Format Analyzer Version 2.0 befindet sich noch in der Entwicklungsphase und steht daher momentan nicht zum Download bereit. Es ist geplant, dass die Version 2.0 gegen Mitte April 2004 offiziell freigegeben wird.

CellFormatAnalyzer.zip

Bitte beachten Sie den Hinweis am Anfang dieser Webseite.

To Top

Zuletzt aktualisiert am 9.07.2004 / 23:00 Uhr
© 2003-2010 by Philipp von Wartburg, Schweiz
Alle Rechte vorbehalten