Code-Refactoring-Techniken für saubere Tabellenblätter

Code-Refactoring-Techniken für saubere Tabellenblätter

Code-Refactoring-Techniken räumen auf, was schon funktioniert, ohne es kaputtzumachen. Bei Tabellenblättern heißt das: Sie nehmen eine Formel, die zwar das Richtige berechnet, aber aussieht, als wäre sie um 23 Uhr kurz vor der Deadline getippt worden, und machen daraus etwas, das Sie auch in einem Quartal noch gerne öffnen.

Biscuit hat die passende Formel längst apportiert. Hier kommt, was wirklich etwas bringt.

Ihre Formel funktioniert, aber warum fühlt sie sich falsch an?

Das klassische Zeichen, dass Refactoring fällig ist: Sie öffnen eine Datei von vor sechs Monaten und brauchen drei Minuten, um eine einzige Zelle zu verstehen. Oder Sie kopieren eine Formel in eine neue Spalte, und sie bricht lautlos zusammen, weil irgendwo ein hartcodierter Zeilenbezug versteckt war.

Refactoring ändert nicht das Ergebnis. Es ändert die Struktur, damit das Ergebnis auch dann noch stimmt, wenn sich das Drumherum ändert: neue Zeilen, neue Spalten, ein umbenanntes Blatt.

Überspringen Sie das, wenn: Ihre Datei eine einmalige Berechnung ist, die sonst niemand anfasst. Refactoring kostet etwas (Zeit, Unterbrechung), und wenn der Nutzen null ist, ist der Return on Investment es auch.

Es lohnt sich, wenn: mehrere Personen die Datei nutzen, Sie sie regelmäßig aktualisieren, oder Formeln über mehrere Blätter hinweg verkettet sind.

Ein paar Warnsignale, auf die Sie reagieren sollten:

• Eine Formel, die bei 100 % Zoom breiter ist als die Spalte

• Mehr als zwei verschachtelte WENN-Ebenen

• Hartcodierte Zahlen, die an mehr als drei Stellen auftauchen

• Kolleginnen und Kollegen, die Sie fragen, was eine Zelle macht, statt selbst nachzulesen

Jedes einzelne dieser Signale reicht, um eine Refactoring-Runde einzuplanen.

Extract Method: eine Formel in benannte Teilschritte zerlegen

Das ist die nützlichste Refactoring-Technik überhaupt, in Tabellenblättern genauso wie im Programmcode. Die Idee: Wenn eine Formel drei Dinge auf einmal macht, teilen Sie sie in drei Zellen auf, je einen Schritt, und kombinieren die Ergebnisse danach.

Vorher (eine Zelle macht alles):

=WENN(UND(A2<>"";DATEDIF(A2;HEUTE();"Y")>30);"Senior";"Junior")

Nachher:

• Zelle C2: =DATEDIF(A2;HEUTE();"Y"), benannt "Alter in Jahren"

• Zelle D2: =UND(A2<>"";C2>30), benannt "Ist Senior?"

• Zelle E2: =WENN(D2;"Senior";"Junior"), das eigentliche Ergebnis

So sieht das dann in einer echten Zelle aus. Jeder Schritt ist für sich lesbar. Ändert sich die Schwelle von 30 auf 25, ändern Sie eine einzige Zahl in einer einzigen Zelle. Bricht DATEDIF, weil ein Datum fehlt, sehen Sie sofort, wo.

Der Nachteil: Sie verbrauchen mehr Spalten. Auf einem breiten Tabellenblatt kann sich das verschwenderisch anfühlen. Legen Sie bei knappem Platz einen Hilfsspalten-Bereich auf einem separaten Blatt an. Nennen Sie es "Hilfszellen" oder "Berechnungen", damit die Struktur für jede Person, die die Datei öffnet, sofort klar ist.

Funktioniert in Excel und Google Sheets identisch, es gibt keine Versionsbeschränkung für Hilfszellen.

Replace Temp with Query: Zwischenwerte nicht unnötig aufheben

Eine Zwischenvariable ist im Tabellenblatt-Kontext eine Zelle, die Sie mit einem Zwischenergebnis füllen, das sonst nirgendwo direkt referenziert wird, Sie brauchten sie nur, um zum nächsten Schritt zu kommen.

Manchmal sind solche Zellen sinnvoll (siehe oben). Aber wenn Sie eine Kette aus fünf Hilfszellen haben, bei der nur die letzte wirklich zählt und die Zwischenschritte sich nie ändern, können Sie sie zu einer einzigen Formel zusammenfassen.

Drei Hilfszellen, die zu einem Ergebnis führen:

• =B2*C2, Zwischensumme

• =D2*0,2, Steuerbetrag

• =D2+E2, Gesamtsumme

Zusammengefasst:

=(B2*C2)+(B2*C2*0,2)

Oder mit einem benannten Bereich für den Steuersatz:

=B2*C2*(1+Steuersatz)

Die Faustregel: Wenn eine Hilfszelle nur von genau einer anderen Zelle verwendet wird, fragen Sie sich, ob diese Formel kurz genug für eine Zusammenfassung ist. Wenn ja, fassen Sie sie zusammen. Wenn nein, behalten Sie die Hilfszelle und benennen Sie sie ordentlich.

Wo die Grenze liegt: Eine Formel, die über 80 Zeichen hinausgeht, ist meist zu lang, um sie einzubetten. An dem Punkt hilft Ihnen eher der Extract-Method-Ansatz von oben. Behalten Sie Hilfszellen, wenn sie die Lesbarkeit wirklich erhöhen, entfernen Sie sie, wenn sie nur Rauschen sind.

Encapsulate Field: feste Werte an einem Ort schützen

Bei Tabellenblättern bedeutet Kapselung, feste Eingabewerte, Steuersätze, Schwellenwerte, Referenzwerte, an einem Ort zu halten, statt sie verstreut als hartcodierte Zahlen in Formeln zu verstecken.

Das ist die Formel, die alle vergessen und die alle suchen.

Ein Mehrwertsteuersatz, hartcodiert als 0,19 in 47 Formeln über eine Arbeitsmappe verteilt, ist eine Wartungsfalle. Ändert sich der Satz, müssen Sie alle 47 finden. Übersehen Sie drei davon, haben Sie einen Rechenfehler.

Lösung:

1. Legen Sie ein Blatt Einstellungen an mit einer Zelle namens MwSt = 0,19

2. Referenzieren Sie sie in jeder Formel: =B2*MwSt

3. Ändert sich der Satz, aktualisieren Sie eine Zelle. Der Rest folgt automatisch.

Das gilt genauso für Datumsbezüge (das heutige Datum als fester Anker), Währungsmultiplikatoren und Kategorie-Schwellenwerte. Taucht ein Wert mehr als zweimal auf und könnte sich jemals ändern, gehört er in einen benannten Bereich.

So legen Sie benannte Bereiche an:

• Excel: Formeln > Namens-Manager > Neu

• Google Sheets: Daten > Benannte Bereiche

Die Namenskonvention macht den Unterschied. Steuersatz ist klar. SS ist es nicht. tmp_3 auch nicht. Wählen Sie Namen, die jemand, der die Formel nicht geschrieben hat, in drei Sekunden versteht.

Duplikate eliminieren: das DRY-Prinzip für Formeln

DRY, Don't Repeat Yourself ist eines der meistzitierten Prinzipien der Softwareentwicklung, und es gilt eins zu eins für Tabellenblätter. Wenn Sie dasselbe SVERWEIS über 15 Spalten mit kleinen Abweichungen kopiert haben, haben Sie ein Wartungsproblem, das nur noch auf seinen Auftritt wartet.

Zwei Ansätze, je nach Ihrer Ausgangslage:

Excel 365 / Google Sheets: Nutzen Sie XVERWEIS oder INDEX-VERGLEICH mit Array-Erweiterung, um mehrere Spalten in einer einzigen Formel zu holen.

=XVERWEIS(A2;Tabelle[ID];Tabelle[[Name]:[Status]])

Das liefert Name und Status in einem Rutsch, kein Copy-Paste über Spalten hinweg mehr.

Älteres Excel (2016 oder früher): Legen Sie einen einzigen VERGLEICH-Aufruf in einer Hilfszelle an und referenzieren Sie ihn dann per INDEX:

• G2: =VERGLEICH(A2;Tabelle[ID];0), ein VERGLEICH für alle Spalten

• H2: =INDEX(Tabelle[Name];G2)

• I2: =INDEX(Tabelle[Status];G2)

Sie referenzieren überall dasselbe VERGLEICH-Ergebnis. Ändert sich die Nachschlagespalte, aktualisieren Sie eine einzige Formel.

Ein Hinweis zur Version: XVERWEIS kam 2019 für Excel-365-Abos, Google Sheets unterstützt es seit 2022 auf allen Plänen. Nutzt Ihr Team Excel 2016, ist INDEX-VERGLEICH der richtige Weg.

Verschachtelte WENNs ersetzen und veraltete Formeln neu schreiben

Verschachtelte WENNs sind der Ort, an dem Tabellenblätter unlesbar werden. Drei Ebenen tief, und Sie haben die meisten Leserinnen und Leser verloren, sich selbst eingeschlossen, in sechs Monaten.

Vorher (verschachtelte WENN-Kette):

=WENN(A2>10000;"A";WENN(A2>5000;"B";WENN(A2>1000;"C";"D")))

Option 1: WENNS (Excel 2016+ / Sheets)

=WENNS(A2>10000;"A";A2>5000;"B";A2>1000;"C";WAHR;"D")

Flacher, aber immer noch vier Bedingungen in einer Formel. In Ordnung.

Option 2: SVERWEIS mit Nachschlagetabelle (der unterschätzte Ansatz)

Legen Sie eine kleine Referenztabelle an:

• Ab 10.001: Kategorie A

• 5.001 bis 10.000: Kategorie B

• 1.001 bis 5.000: Kategorie C

• 0 bis 1.000: Kategorie D

Dann: =SVERWEIS(A2;Schwellentabelle;2;WAHR)

Das WAHR (ungefähre Übereinstimmung) läuft die sortierte Tabelle entlang und liefert die letzte passende Kategorie. Die Logik steckt jetzt in der Tabelle, nicht versteckt in der Formel. Ändert sich ein Schwellenwert? Eine Zeile in der Tabelle anpassen, fertig.

Lohnt sich der Mehraufwand: der Nachschlagetabellen-Ansatz, er macht die Logik nachvollziehbar und sichtbar für alle, auch für Menschen, die keine Formeln lesen. Bei Zeitdruck überspringen: WENNS reicht für drei Bedingungen oder weniger.

Die Funktion WAHLS ist eine weitere Option ab Excel 2019+ und in Sheets, nützlich bei exakten Wertvergleichen statt Bereichen. Sie liest sich sauberer als WENNS für exakte Treffer.

Manchmal ist die Formel nicht falsch, nur der komplett falsche Ansatz. Sie erben eine 200 Zeichen lange Formel, die Text mit VERKETTEN und & zusammenfügt, und sollen ein weiteres Feld ergänzen. Der richtige Schritt ist nicht, sie zu verlängern. Sondern sie mit TEXTVERKETTEN neu zu schreiben.

Vorher:

=A2&" | "&B2&" | "&C2&" | "&D2

Nachher:

=TEXTVERKETTEN(" | ";WAHR;A2:D2)

Wenn Ihr Tabellenblatt morgen anders aussieht, zieht diese Formel mit. TEXTVERKETTEN kommt mit leeren Zellen klar, mit Arrays, und bricht nicht, sobald Sie Spalte E hinzufügen.

Dieselbe Logik gilt für:

• SVERWEIS durch XVERWEIS ersetzen, wenn Sie exakte Treffer plus Behandlung fehlender Werte brauchen

• ISTFEHLER-Umhüllungen durch native Fehlerbehandlung neuerer Funktionen ersetzen

• Mit Strg+Umschalt+Eingabe eingegebene Array-Formeln durch native dynamische Arrays in Excel 365 ersetzen

Das gleiche Prinzip skaliert nach oben: Pflegen Sie eine große Datenpipeline in einem Tabellenblatt, das zu einem Gewirr aus INDIREKT-Bezügen, externen Verknüpfungen und Bereichen, die auf Bereiche zeigen, angewachsen ist, ist manchmal der richtige Refactoring-Schritt, die Logik in ein echtes Tool auszulagern (SQL, Python, Power Query) und das Tabellenblatt nur noch zur Anzeige zu nutzen. Das ist eine größere Entscheidung, gehört aber in dieselbe Kategorie.

Drei Dinge, die Sie vor dem Refactoring brauchen, und wann Sie aufhören sollten

Code-Refactoring-Techniken funktionieren nur, wenn Sie nichts kaputt machen, das Sie nicht wiederherstellen können. Bei Tabellenblättern heißt das konkret:

1. Eine Sicherungskopie. Speichern Sie vor jeder Refactoring-Sitzung eine datierte Kopie der Arbeitsmappe. Nicht als "Speichern unter" im selben Ordner, sondern an einem separaten Ort (Cloud-Backup, an sich selbst mailen, was auch immer). Sie werden es sich danken, wenn genau einmal etwas schiefgeht.

2. Wissen, was die aktuellen Ergebnisse sein sollten. Notieren Sie sich vor jeder Änderung die wichtigsten Ausgaben: Summen, Kennzahlen, Schlüsselwerte. Vergleichen Sie sie nach jeder Änderung mit denselben Zellen. Ein Zehn-Sekunden-Check, der Regressionen sofort auffängt.

3. Eine Änderung nach der anderen. Machen Sie nicht Extract Method, drei benannte Bereiche und eine geflachte WENN-Kette in derselben Sitzung. Geht etwas kaputt, müssen Sie wissen, welche Änderung schuld war. Eine Sache machen, prüfen, dann zur nächsten.

Diese drei Schritte kosten etwa fünf Minuten Vorbereitung. Sie haben in jeder Sitzung, in der etwas Unerwartetes passiert ist, deutlich mehr als fünf Minuten gespart.

Das Risiko beim Refactoring ist Perfektionismus. Sie können einen ganzen Nachmittag damit verbringen, eine bereits funktionierende Arbeitsmappe umzustrukturieren, und am Ende etwas minimal Saubereres haben, das einen halben Tag gekostet hat.

Setzen Sie sich vorher ein Zeitlimit: 45 Minuten, dann ist Schluss. Priorisieren Sie die Formeln, die aktiv Probleme verursachen, die falsch gelesen werden, die im normalen Betrieb brechen, die eine neue Funktion blockieren, die Sie brauchen.

Alles andere: lassen Sie es. Eine Formel, die funktioniert, die niemand anfasst, die seit einem Jahr keinen Fehler verursacht hat, ist gut so, wie sie ist. Gut genug ist ein legitimer Haltepunkt.

Ein nützlicher Gedanke: Beim Refactoring geht es nicht darum, die Arbeitsmappe perfekt zu machen. Es geht darum, die nächste Änderung leichter zu machen. Fragen Sie sich vor jeder Änderung: Macht das den nächsten Schritt schneller oder sicherer? Wenn ja, tun Sie es. Wenn nicht, hören Sie auf.

Gut gemachtes Refactoring fällt niemandem auf. Genau das ist das Ziel.