Eine Arbeitsappenanalyse offenbart wirklich viel, aber nicht alles, was falsch aussieht, ist tatsächlich falsch, und darum soll es hier gehen.

FAQ - Welche Fragen werden in diesem Artikel beantwortet?

Was genau überprüft ein Tool zur Arbeitsmappenanalyse?

A: Es untersucht drei Dinge:

• die in Formeln verwendeten Namen und benannten Bereiche,
• die auf Zellen angewendeten Formatierungen und Zahlenformate sowie
• den strukturellen Zustand jedes Arbeitsblatts einschließlich der Zellenanzahl, der Leerstandsquote, der Fehlerergebnisse und des Umfangs der bedingten Formatierung.

Zusammen bieten diese drei Analysen einen umfassenden Überblick über den Inhalt, die Struktur und potenzielle Problembereiche einer Arbeitsmappe.

Q: Warum sehe ich Hunderte von benannten Bereichen, die ich nie erstellt habe?

A: Wenn Ihre Arbeitsmappe moderne Excel-365-Funktionen wie LET oder LAMBDA verwendet, registriert Excel die von Ihnen innerhalb dieser Funktionen definierten Parameternamen automatisch als Einträge für benannte Bereiche im Namensmanager der Arbeitsmappe. Da es sich technisch gesehen nicht um benannte Bereiche, sondern um Definitionen von Formelvariablen handelt, wird eine Verwendungsanzahl von Null angezeigt. Wenn Sie diese Einträge löschen, werden alle davon abhängigen Formeln beschädigt.

Q: Was bedeutet ein Verschwendungsanteil von 99 %?

A: Der Verschwendungsanteil gibt an, welcher Anteil des „genutzten Bereichs“ eines Arbeitsblatts keine Daten enthält. Excel definiert den „genutzten Bereich“ als den Bereich von Zelle A1 bis zur zuletzt ausgewählten Zelle ganz rechts und ganz unten, auch wenn diese anschließend gelöscht wurde. Ein Verschwendungsanteil von 99 % bedeutet, dass der tatsächliche Inhalt des Arbeitsblatts nur 1 % des von Excel für die Verwaltung als notwendig erachteten Bereichs einnimmt. Dies kann das Öffnen, Speichern und Scrollen verlangsamen. In der Regel lässt sich dieses Problem durch Zurücksetzen des genutzten Bereichs beheben.

Q: Sind Millionen von Datensätzen mit bedingter Formatierung ein ernstes Problem?

A: Ja, aber nur in Bezug auf die Leistung, nicht auf die Korrektheit. Wenn Sie bedingte Formatierungen auf ganze Spalten statt auf bestimmte Datenbereiche anwenden, entstehen zahlreiche interne Datensätze, was sich jedoch nicht auf das visuelle Ergebnis auswirkt. Zwar berechnet und zeigt die Arbeitsmappe alles korrekt an, sie lässt sich jedoch nur langsam öffnen und speichern und ist schwer zu teilen. Beim Entfernen bedingter Formatierungen ist Vorsicht geboten, da das Löschen aus den falschen Zellen das Layout des Arbeitsblatts zerstören kann.

Q: Was ist der Unterschied zwischen etwas, das bereinigt werden kann, und etwas, das nicht bereinigt werden kann?

A: Der entscheidende Unterschied liegt zwischen strukturellen und visuellen bzw. leistungsbezogenen Daten. Benannte Bereiche, die LAMBDA- oder LET-Parameterdefinitionen sind, sind strukturell. Werden sie entfernt, werden Formeln unwirksam. Bedingte Formatierungen, die auf leere Spalten angewendet werden, sind dagegen visuelle bzw. leistungsbezogene Daten. Werden sie aus dem leeren Bereich entfernt, hat das keine optischen Auswirkungen, die Leistung wird jedoch verbessert. Ungenutzte integrierte Formatvorlagen sind reiner Overhead und ihre Entfernung hat keine Auswirkungen. Ein gutes Analyse-Tool sollte alle drei Kategorien identifizieren können. Ein guter Berater weiß, was was ist, bevor er Änderungen vornimmt.

Wenn Sie ein Arbeitsmappenanalyse-Tool zum ersten Mal ausführen, kann das zunächst erschreckend wirken

Möglicherweise sehen Sie Hunderte benannter Bereiche, die Sie nie erstellt haben. Millionen von Datensätzen mit bedingter Formatierung. Oder Verschwendungsanteile von über 99 %. Die Datei ist größer als nötig. Ganz zu schweigen von einem Namensmanager voller Einträge mit kryptischen Bezeichnungen wie _xlpm.startDates und _xlpm.monthlyDays, die Ihnen unbekannt sind und die Sie nicht verstehen.

Ihr erster Impuls ist, alles zu bereinigen. Die ungenutzten Namen löschen. Die überflüssige Formatierung entfernen. Die Bereiche zurücksetzen..

Wenn dieser Instinkt ohne Verständnis angewendet wird, kann er eine professionell erstellte Arbeitsmappe zerstören.

In diesem Artikel wird eine Arbeitsmappe zur Projektkostenbasis detailliert analysiert. Es wird erklärt, was die einzelnen Ergebnisse bedeuten, welche Teile bedenkenlos bereinigt werden können und welche man niemals anfassen darf – selbst wenn sie falsch erscheinen.

Die hier untersuchte Arbeitsmappe

Die Arbeitsmappe dient als Projektkosten-Baseline und Ausgabenplan. Als Instrument der Finanzplanung verteilt sie die geschätzten Projektkosten auf einzelne Monate, vergleicht die Ist-Kosten mit der Baseline und erstellt kumulierte Ausgabenkurven sowie Abweichungsberichte.

Das Projekt mit 13 Budgetkategorien läuft von Januar bis Dezember 2024. Bei der Berechnung der Arbeitstage werden italienische sowie sardische Regionalfeiertage berücksichtigt. Aus einem einzigen Dateneingabeblatt werden vier grafische Auswertungen erstellt.

Es wurde vollständig in Microsoft Excel 365 unter Verwendung der benannten Bereiche, dynamischen Array-Formeln, LET-, LAMBDA- und MAKEARRAY-Funktionen – der modernsten verfügbaren Excel-Formelarchitektur – erstellt.

Die drei Analyse-Tools

Auf diese Arbeitsmappe wurden drei separate Analysemodule angewendet. Jedes Modul deckt dabei eine andere Informationsebene auf.

Das obige Diagramm veranschaulicht die Zusammenhänge zwischen den drei Tools und der Arbeitsmappe sowie die Informationen, die jedes einzelne Tool aufzeigen soll. Im Folgenden finden Sie eine detaillierte Anleitung zu jedem Tool.

Analyse 1: Namensübersicht (FastXLMap1)

Was wird angezeigt?

Die Namensübersicht ist eine Liste aller benannten Bereiche und benannten Formeln in der Arbeitsmappe. Sie zeigt an, wie oft jeder einzelne Eintrag verwendet wird und wie häufig er auf den einzelnen Arbeitsblättern vorkommt.

Die Ergebnisse

Das Arbeitsbuch enthält insgesamt 82 benannte Einträge. Davon weisen nur wenige eine Verwendungshäufigkeit von mehr als null auf.

Die verbleibenden 77 Einträge weisen alle eine Verwendungshäufigkeit von Null auf. Dazu gehören Einträge mit Namen wie: _xlpm.startDates, _xlpm.endDates, _xlpm.taskAmounts, _xlpm.totalDays, _xlpm.headerDates, _xlpm.dailyRate, _xlpm.monthlyAmount und einige weitere.

Die entscheidende Erkenntnis ist, dass das, was wie ein Problem aussieht, keines ist.

Ein automatisiertes Bereinigungstool erkennt 77 ungenutzte benannte Bereiche als 77 zu löschende Elemente. Zu demselben Schluss würde natürlich auch jemand kommen, der nicht weiß, womit er es zu tun hat.

Es handelt sich jedoch nicht um ungenutzte benannte Bereiche. Es handelt sich um die internen Parameterdefinitionen von LAMBDA- und LET-Formeln.

Wenn eine Formel die Funktionen LET oder LAMBDA mit benannten Parametern verwendet, beispielsweise LET(startDates, C9:C21; endDates, D9:D21; ...), registriert Excel 365 jeden Parameternamen im Namensmanager der Arbeitsmappe. Dabei wird das Präfix „_xlpm.” verwendet, gefolgt vom Parameternamen. Dadurch kann die Berechnungsengine während der Formelauswertung den Überblick darüber behalten, was die einzelnen Variablen bedeuten.

Die Verwendungsanzahl wird als Null angezeigt, da FastExcel sie als benannte Bereiche und nicht als Formelparameterdefinitionen zählt. Sie gehören zu einer anderen Funktionskategorie. Es handelt sich jedoch nicht um optionale Einträge, die entfernt werden können. Sie sind Teil der internen Struktur der Formel.

In manchen Fällen führt das Löschen dieser Elemente nicht zu einem sofort erkennbaren Fehler. Es kann jedoch die Formelstruktur destabilisieren, in bestimmten Berechnungsszenarien zu falschen Ergebnissen führen und die Arbeitsmappe für alle, die die Formeln später bearbeiten möchten, unübersichtlich machen.

Eines der wichtigsten Dinge, die Ihnen ein Diagnosetool aufzeigen kann, ist, zu welcher Kategorie ein Element gehört. Dies ist auch eine der wichtigsten Informationen, die ein Berater wissen muss: Wann sollte er keine Bereinigung vornehmen?

Was in dieser Analyse bedenkenlos bereinigt werden kann

Die benannten Bereiche ACTUALDURATION, DDL_SORTER und DURATA_GGL sind zwar im Namensmanager vorhanden, jedoch ungenutzt und mit keiner aktiven Formel verknüpft. Sie können entfernt werden.

Die beiden inkonsistenten italienischen Ländercodes [$-410] und [$-10410], die in der Stilzuordnung (siehe unten) identifiziert wurden, stellen eine geringfügige Inkonsistenz dar. Diese kann vereinheitlicht werden, ohne dass dies Auswirkungen auf Berechnungen hat.

Analyse 2: Arbeitsmappenprüfung (FastXLCheck1)

Was wird angezeigt

Bei der Arbeitsmappenprüfung wird für jedes Blatt eine Zeile erstellt, in der die Anzahl der Zellen, Formeln, eindeutigen Formeln, Fehlerergebnisse, CSE-Formeln (Array-Formeln im alten Stil), Datenüberprüfungen, Einträge zur bedingten Formatierung sowie die Anteile ungenutzter und spärlich besetzter Zellen angezeigt werden.

die wichtigsten Zahlen

Ergebnis 1: Keine Fehler und keine CSE-Formeln

Dies ist der wichtigste Qualitätsindikator der gesamten Analyse. Die Tatsache, dass eine Arbeitsmappe mit 533 Formeln keinerlei Fehler aufweist, bedeutet, dass alle Berechnungen korrekt durchgeführt werden. Nichts ist fehlerhaft. Keine Formel liefert ein falsches Ergebnis. Die Arbeitsmappe erfüllt somit genau die Aufgaben, für die sie vorgesehen ist.

Die Anzahl der Nullwerte in der CSE-Statistik bestätigt, dass alle Array-Formeln den modernen, dynamischen Array-Ansatz verwenden. Die veralteten Formeln mit Strg + Umschalt + Eingabetaste sind dagegen anfällig, schwer zu bearbeiten und mit neueren Excel-Funktionen nicht kompatibel.

Ergebnis 2: Insgesamt ergeben 45 verschiedene Formeln 533 Einträge.

Dieses Verhältnis gibt Aufschluss über die Qualität der Architektur. In den 533 Formeleinträgen kommen lediglich 45 verschiedene Formelmuster vor. Das bedeutet, dass die meisten Formeln Wiederholungen derselben Logik sind, die auf verschiedene Zeilen oder Spalten angewendet wird, genau so, wie eine gut konzipierte Arbeitsmappe funktionieren sollte.

Würde jede Zelle ihre eigene benutzerdefinierte Formel enthalten, wäre die Pflege der Arbeitsmappe äußerst schwierig. Da sich lediglich 45 Muster konsistent wiederholen, wird eine einzige Änderung an der Logik korrekt auf die gesamte Struktur übertragen.

Ergebnis 3: 16,8 Millionen Datensätze mit bedingter Formatierung

Dies ist das eigentliche Problem. Es betrifft fast ausschließlich das Arbeitsblatt REGISTROENTRATE&_USCITE, das Einnahmen- und Ausgabenbuch.

Dieses Hauptbuch enthält 172 275 verwendete Zellen, aber erstaunliche 16 787 942 Datensätze mit bedingter Formatierung. Ein solches Verhältnis wäre nicht möglich, wenn die bedingte Formatierung nur auf den Datenbereich angewendet worden wäre. Dies deutet darauf hin, dass die Regeln für die bedingte Formatierung auf ganze Spalten – also alle 1.048.576 Zeilen und nicht nur auf die Zeilen mit Daten angewendet wurden.

Für den Benutzer, der auf den Bildschirm schaut, ist dies nicht sichtbar. Die Farben werden korrekt angezeigt. Die Tabelle sieht einwandfrei aus. Excel wertet jedoch intern bei jeder Neuberechnung, jedem Speichern und jedem Öffnen die Regeln für die bedingte Formatierung anhand von Millionen leerer Zellen aus. Aus diesem Grund dauert das Öffnen der Datei länger als nötig und das Speichern erscheint langsam.

Ergebnis 4: Verschwendungsanteil pro Blatt

Der Verschwendungsanteil gibt an, um wie viel der genutzte Bereich jedes Blattes über dessen tatsächlichen Inhalt hinausgeht. Mithilfe dieser Angabe lässt sich ermitteln, wie viel des Blattes tatsächlich genutzt wird.

Der relativ geringe Verschwendungsanteil von „SETUP_BASELINE” bestätigt, dass diese Tabelle sorgfältig erstellt wurde – der genutzte Bereich entspricht nahezu dem tatsächlichen Inhaltsbereich. Die übersichtlichsten Blätter in der Arbeitsmappe sind die Ausgabediagrammblätter mit 0 %, da sie nur das für ihre Diagramme erforderliche Minimum enthalten.

Der hohe Verschwendungsanteil in den Hauptberechnungsblättern deutet eher auf ein Leistungsproblem als auf ein Genauigkeitsproblem hin. Die Formeln berechnen korrekt. Excel verwaltet jedoch für jede Operation einen viel größeren Bereich als nötig.

Analyse 3: Stilübersicht (FastXLMap2 und FastXLMap3)

Was wird dargestellt

Die Stilübersicht setzt sich aus zwei Komponenten zusammen: der Anzahl der Zellformate und der Anzahl der Zahlenformate, jeweils pro Arbeitsblatt.

Zellenformate: Tatsächlich wird nur eines verwendet

Die Arbeitsmappe enthält 47 verschiedene Zellenformate, die die gesamte Palette der in Excel integrierten Formate abdecken. Dazu gehören beispielsweise die Kategorien „Gut“, „Schlecht“, „Überschrift 1“ bis „Überschrift 4“, „Eingabe“, „Ausgabe“, „Berechnung“ und „Warntext“ sowie alle Varianten des Formats „Prozentangabe“.

Keines dieser Formate wird in allen elf Arbeitsblättern verwendet. In der gesamten Arbeitsmappe wird nur der Stil „Normal“ verwendet.

Dies ist eine bewusste fachliche Entscheidung. Die visuelle Unterscheidung erfolgt ausschließlich über benutzerdefinierte Zahlenformate und nicht über benannte Formatvorlagen. Letztere können nämlich unerwartete visuelle Veränderungen verursachen, wenn Arbeitsmappen zwischen verschiedenen Umgebungen kopiert oder mit anderen Dateien zusammengeführt werden. Durch deren vollständige Entfernung wird die Arbeitsmappe portabler und berechenbarer.

Die 46 ungenutzten integrierten Formatvorlagen erhöhen lediglich die Dateigröße, ohne einen Nutzen zu bieten. Im Gegensatz zu den Einträgen für benannte Bereiche stehen sie in keinem Zusammenhang mit Formeln oder Berechnungen, sodass sie bedenkenlos entfernt werden können.

Zahlenformate: ein einheitlicher und bewusst gewählter Satz

Die Zahlenformate geben genaue Aufschlüsse über die Gestaltung der Arbeitsmappe:

"€"#,##0.00 wird 978 Mal in fünf Arbeitsblättern verwendet und ist das primäre Euro-Währungsformat für alle Finanzzahlen.

Die Variante #,##0.00 "€" wird 10.004 Mal verwendet, hauptsächlich in DATI_COSTI_EFFETTIVI. Dies entspricht der italienischen Konvention, das Euro-Symbol hinter der Zahl zu setzen. In der Arbeitsmappe sind beide Konventionen bewusst vorhanden und spiegeln deren Verwendung sowohl im internationalen als auch im italienischen Kontext wider.

Das italienische Länderkodierungsformat [$-410] mmm-yyyy;@ wird 1.252-mal in den Spaltenüberschriften der Kostentabelle verwendet. Es zeigt Datumsangaben in italienischen Monatsabkürzungen an, beispielsweise „Gen-2024" oder „Feb-2024". [$-410] ist der italienische Länderkodierungscode.

In SETUP_BASELINE verwenden zwei Zellen den Variantencode [$-10410]. Die Beseitigung dieser geringfügigen Inkonsistenz ist das einzige Formatierungsproblem, das behoben werden sollte.

dd/mm/yyyy umfasst 11.522 Zellen im Hauptbuch, wobei die Datumsformatierung im gesamten Transaktionsregister einheitlich ist.

@* "..." erscheint 19 Mal im Hauptbuch und bietet ein Textformat mit Ausfüllzeichen, das die Spaltenbreite optisch ausfüllt. Eine subtile, aber professionelle Note.

Was die Analyse uns insgesamt verrät:

Drei Analysen, drei Informationsebenen und ein schlüssiges Gesamtbild.

Die Arbeitsmappe ist strukturell einwandfrei. Jede Formel wird korrekt berechnet. Die Struktur ist konsistent: 45 unterschiedliche Muster steuern 533 Formeln. Die benannten Bereiche, die auf den ersten Blick redundant erschienen, sind in Wirklichkeit die Parameterdefinitionen einer ausgeklügelten, modernen Formelarchitektur und dürfen nicht gelöscht werden.

Die eigentlichen Probleme betreffen die Leistung, nicht die Genauigkeit. Auf ganze Spalten wurde eine bedingte Formatierung angewendet. Die verwendeten Bereiche reichen dabei weit über ihren Inhalt hinaus. Dies beeinträchtigt die Geschwindigkeit, nicht die Genauigkeit. Diese Probleme lassen sich beheben, jedoch nur von jemandem, der versteht, welche Zellen bereinigt und welche unverändert bleiben müssen.

Genau darin liegt der Unterschied zwischen einer Arbeitsmappenanalyse und einer Arbeitsmappenbereinigung. Eine Analyse zeigt Ihnen, welche Daten vorhanden sind. Das Verständnis vermittelt Ihnen, was dies bedeutet. Fachwissen sagt Ihnen, was Sie unternehmen sollten und was Sie besser unberührt lassen sollten.

Zusammenfassung: Was soll weg und was soll bleiben?

Was kommt als Nächstes

In einem der nächsten Artikel auf helpme.safeoffice.de werden ich mich ausführlich mit der Formel selbst beschäftigen. Mithilfe des Formel-Explorers zeige ich, welche Auswirkungen die Funktionen MAKEARRAY, LET und LAMBDA in einer einzelnen Zelle haben. Ich demonstriere unter anderem, wie der italienische Feiertagskalender direkt in die Berechnung der Arbeitstage eingebunden ist und wie benannte Variablen die Formel übersichtlicher machen. Zudem wird erläutert, warum die Formel für Monate, in denen eine Kategorie keine Ausgaben aufweist, den Wert „NA” statt Null zurückgibt.

Dieser Artikel richtet sich an alle, die nicht nur die Ergebnisse der Arbeitsmappe verstehen möchten, sondern auch deren Denkweise.

Über diese Analyse

Diese Analyse wurde mit einem speziellen Tool in Excel 365 auf einem Windows-Computer mit einem 16-Kern-Prozessor durchgeführt. Bei der analysierten Arbeitsmappe handelt es sich um eine Projektkosten-Baseline-Arbeitsmappe für Kunden aus der italienischen Baubranche, die zu Demonstrationszwecken mit Dummy-Daten gefüllt ist.

Das Analyse-Tool, der Arbeitsmappen-Check, der Formel-Explorer und der Arbeitsmappen-Reiniger sind Teil eines professionellen Toolkits für Datenberatungsprojekte. Wenn Sie Fragen zur Analyse oder Optimierung Ihrer eigenen Excel-Dateien haben, besuchen Sie bitte safeoffice.de oder helpme.safeoffice.de.

Dieser Artikel ist Teil der Reihe helpme.safeoffice.de, die praktische Anleitungen zu Datenlösungen, der Modellierung von Arbeitsmappen und der Prozessdokumentation bietet. Die Reihe richtet sich an Unternehmen, die effektive, nachhaltige Tools suchen, die für alle verständlich sind.