öffentlich
Redaktion Druckversion

Excel: Professionelles Arbeiten dank Bereichsnamen

Bereichsnamen

Angenommen, Sie haben in Excel eine ganz typische Kalkulation erstellt, in unserem Beispiel für eine Pizza, deren Preis Sie anhand der Zutaten ermitteln wollen. Das sähe beispielsweise so aus:

Bild vergrößernDie Grunddaten für die Pizza-Kalkulation

Dabei ist die Netto-Formel lediglich die Multiplikation aus Menge und kg-Preis durch Tausend (weil die Einheiten sich unterscheiden!), also noch nichts Ungewöhnliches. Die Formel etwa in $E$3 lautet:

=C3*D3/1000

Unter den Menge- und Netto-Spalten können Sie eine übliche Summe bilden (rechnerisch mag das auch unter dem kg-Preis funktionieren, aber inhaltlich wäre es nicht erklärbar).

Vor allem für die Übersichtlichkeit können Sie nun mitten in den Block hineinklicken und mit dem Menü Format/AutoFormat das Format Liste1 auswählen. Damit ergibt sich die folgende Tabelle:

Bild vergrößernDie Daten mit AutoFormat

Um den Brutto-Wert zu errechnen, würden Sie nun wahrscheinlich eine Formel wie

=E3*1,07

in $F$3 eingeben, da alle Lebensmittel ja den reduzierten Mehrwertsteuer-Satz haben. Das ist rechnerisch durchaus richtig, birgt aber bei größeren Tabellen ein gewisses Risiko:

  • Wenn Sie später mal alle Mehrwertsteuer-Berechnungen anpassen wollen, müssen Sie suchen, in welcher Formel diese vorkommt.

  • Wenn Sie hingegen mit Bearbeiten/Ersetzen diverse Faktoren korrigieren, können Sie nie sicher sein, dass ein zufälliger Faktor 1,07 bei dieser Gelegenheit nicht auch die Mehrwertsteuer unfreiwillig anpasst.

Konstanten auslagern

Daher sollten Sie solche Konstanten (die MWSt werde ich als Konstante bezeichnen, obwohl sie gelegentlich per Gesetz geändert wird) immer kennzeichnen. Das könnten Sie machen, indem Sie deren Wert in einer Zelle speichern und sich auf diese Zelle beziehen. Alle Formeln, welche diese Zelle benutzen, rechnen dann mit der Mehrwertsteuer:

Bild vergrößernDie Mehrwertsteuer als Wert in der Zelle $D$1

Die Formel für den Brutto-Wert lautet dann beispielsweise für die Zelle $F$3:

=E3*$D$1

Dadurch wird der (relativ adressierte) jeweilige Nachbarwert wie gewünscht mit dem (absolut adressierten) Mehrwertsteuer-Faktor aus $D$1 multipliziert.

Hilfswerte verstecken

Damit müssen eventuelle Änderungen an der Mehrwertsteuer zwar nur noch in Zelle $D$1 vorgenommen werden, aber solche Hilfswerte machen eine Tabelle nicht wirklich schöner.

Jeder Versuch, diese zu verstecken, führt im übrigen früher oder später zu Katastrophen. Es gibt nämlich verschiedene Tricks des Versteckens:

  • Sie ändern die Schrift-(und Hintergrund-)Farbe auf weiß,

  • Sie blenden die Zeile 1 aus oder

  • Sie ändern das Zahlenformat auf ";;;;".

Was auch immer Ihnen einfällt, irgendwann werden Sie diese Zeile versehentlich löschen, und zwar nicht nur inhaltlich, sondern komplett mit Bearbeiten/Zeilen löschen. Danach sieht Ihre Tabelle so aus:

Bild vergrößernDie Hilfswerte-Zeile wurde versehentlich gelöscht

Schade, denn jetzt ist Ihre Tabelle ziemlich beschädigt. Wenn Sie das nicht schnell genug bemerken, nämlich vor dem nächsten Speichern, lässt sich das nicht einmal mehr rückgängig machen!

Hilfswerte mit Bereichsnamen versehen

Viel einfacher ist es, solche Hilfswerte außerhalb der Tabelle, aber natürlich immer noch innerhalb der Datei zu lagern. Im ersten Schritt zu dieser Lösung hin wird die Formel in $F$3 vorerst lesefreundlicher, aber die Mehrwertsteuer noch nicht unsichtbar:

Bild vergrößernZelle D1 wurde als MWSt benannt

Dazu markieren Sie bitte die Zelle $D$1, weil sie die Mehrwertsteuer enthält. Dann klicken Sie oben links in das Namensfeld, welches derzeit noch D1 anzeigt. Geben Sie dort einen weiteren Namen für die Zelle an, hier MWSt und bestätigen Sie dies mit der Return-Taste

Bereichsnamen immer bestätigen!

Sie müssen die Eingabe eines Bereichsnamens immer mit der Return-Taste bestätigen, andernfalls "vergisst" Excel Ihre Eingabe ohne Hinweis. Ein einfacher Mausklick irgendwohin reicht keineswegs.

Jetzt ändert sich die Formel in $F$3 so, dass Sie statt der bisherigen Zelladresse $D$1 direkt den neuen (zusätzlichen) Namen MWSt der Zelle benutzen:

=E3*MWSt

Inhaltlich kommt die Formel natürlich zum gleichen Ergebnis.

Bereichsnamen aus Dialog auswählen

Da ein solcher Bereichsname bedeutend länger sein kann, sollten Sie Schreibfehlern vorbeugen, indem Sie sich alle Bereichsnamen mittels F3-Taste als Dialog zur Auswahl präsentieren lassen:

Auswahl der Bereichsnamen

Das geht auch bei der Eingabe einer Formel, allerdings muss der Bereichsname im Dialog immer ausdrücklich markiert werden, auch wenn es sich wie hier um den einzigen handelt. Erst dann wird die OK-Schaltfläche aktiv.

Die so geänderte Formel kann wie bisher in die Zellen darunter kopiert werden, weil Bereichsnamen grundsätzlich absolut adressiert sind (es sei denn, Sie änderten deren Definition, wozu es aber keinen Anlaß gibt).

Namen oder Bereichsnamen?

Während die hier benutzten Zell-Benennungen in Excel allgemein nur als "Namen" bezeichnet werden, heißen Sie bei mir ausdrücklich Bereichsnamen. Dadurch sind Verwechslungen mit Tabellen-/Blatt-Namen oder Datei-Namen ausgeschlossen.

Das gilt sogar dann, wenn die Bereichs-Namen in späteren Beispielen gar keinen Zell-Bereich beschreiben werden.

Die Lesefreundlichkeit der Formel gewinnt eigentlich erst dann, wenn es sehr viele Konstanten innerhalb der Datei gibt, so dass in jeder Formel zu sehen ist, welche der Konstanten benutzt wurde.

Mitglied werden, Vorteile nutzen!

  • Sie können alles lesen und herunterladen: Beiträge, PDF-Dateien und Zusatzdateien (Checklisten, Vorlagen, Musterbriefe, Excel-Rechner u.v.a.m.)
  • Unsere Autoren beantworten Ihre Fragen

Inhalt

Downloads zu diesem Beitrag

Newsletter abonnieren