Workshop: Rechnen mit Excel
Formeln sind das Lebenselixier von Excel. Dabei geht es nicht nur um deren mathematische Korrektheit. Mindestens genauso wichtig ist das intelligente Zusammenspiel aller Formeln einer Tabelle. Entscheidende Tipps zur geschickten Formelgestaltung, erhalten Sie hier.
Formeln perfekt im Griff Formeln sind das Lebenselixier von Excel. Dabei geht es nicht nur um deren mathematische Korrektheit. Mindestens genauso wichtig ist das intelligente Zusammenspiel aller Formeln einer Tabelle. Entscheidende Tipps zur geschickten Formelgestaltung, erhalten Sie hier. Tipp: r...
Formeln perfekt im Griff Formeln sind das Lebenselixier von Excel. Dabei geht es nicht nur um deren mathematische Korrektheit. Mindestens genauso wichtig ist das intelligente Zusammenspiel aller Formeln einer Tabelle. Entscheidende Tipps zur geschickten Formelgestaltung, erhalten Sie hier.
Tipp: relative und absolute Bezüge
Wenn Sie in einer Formel den Inhalt einer anderen Zelle verarbeiten wollen, verwenden Sie üblicherweise einen Bezug der Form C2, wenn Sie Daten der Zelle C2 in der Formel verwenden wollen. Praktischerweise passt Excel Bezüge automatisch an, wenn sich die Lage der betreffenden Zellen verändert.
Einen absoluten Bezug benötigen Sie, wenn Informationen immer aus einer bestimmten Zelle bezogen werden sollen. Um einen absoluten Bezug zu definieren, fügen Sie zusätzlich Dollarzeichen ein. Wenn Sie also in einer Formel explizit auf die Zelle A2 zugreifen wollen, geben Sie in Ihrer Formel den Ausdruck $A$2 ein. Wollen Sie wie in der Beispieltabelle den Anteil der Autoversicherung an den Gesamtkosten berechnen, erhält der Verweis auf die Gesamtkosten einen absoluten Bezug. Geben Sie also die Formel =C6/$C14$ ein. Um auch die Anteile der folgenden Kosten an den Gesamtkosten zu berechnen, kopieren Sie die Formel in die darunter liegenden Zellen. Der Bezug auf die Zelle C14 bleibt dadurch in allen Kopien unverändert.
Mit Excel sind auch Mischformen der beiden Zellangaben möglich. Wenn Sie ein Zelle mit der Formel =C$12+D$12 kopieren oder verschieben, bleiben die Zeilenangaben immer erhalten und es werden lediglich die Spaltenangaben angepasst. Klicken Sie in eine Zelle, trägt Excel den relativen Bezug ein. Drücken Sie direkt danach die Taste [F4], damit Excel den relativen Bezug in einen absoluten Bezug umwandelt. Durch mehrfaches Drücken von [F4] erhalten Sie auch die beiden anderen Bezugsvarianten.
Tipp: Zellen benennen
In einer Tabelle, in der Sie das Kilometergeld von Ihrem Arbeitgeber abrechnen wollen, taucht die vom Arbeitgeber gezahlte Kilometerpauschale naturgemäß mehrfach auf. Um hier flexibel zu bleiben, sollten Sie den Geldbetrag nicht jedes Mal manuell eingeben, sondern ihn in einer separaten Zelle speichern. Diese Zelle erhält eine Bezeichnung, etwa Kilometerpauschale und in den verschiedenen Formeln nehmen Sie einfach Bezug darauf.
Markieren Sie eine Zelle in Ihrer Tabelle, in der die Kilometerpauschale eingetragen werden soll. Klicken Sie ganz links in der Bearbeitungsleiste in das Namensfeld (links oberhalb des Tabellenblatts). Markieren Sie die dort angezeigte Zellkoordinate und überschreiben Sie sie mit einem aussagekräftigen Namen, also etwa km-Pauschale, wenn diese berechnet werden sollen. Drücken Sie anschließend die Eingabetaste.
Soll der Inhalt dieser Zelle in einer Formel verwendet werden, müssen Sie nicht erst umständlich einen absoluten Zellbezug eintragen, sondern einfach den Namen der Zelle. Setzen Sie diese Technik konsequent ein, werden Ihre Formeln nicht nur übersichtlicher, sondern Sie können sie auch noch nach Monaten und Jahren problemlos nachvollziehen, wie die Formel in Ihrer Tabelle aufgebaut ist. Anwenden können Sie diese Technik auch über mehrere Tabellen hinweg.
Tipp: Sie können nicht nur einzelne Zellen, sondern auch Zellbereiche sowie ganze Zeilen und Spalten mit einem Namen versehen. Die nüchterne, wenig aussagekräftige Formel =MITTELWERT(C4:C20) wird so zur anschaulicheren Variante: =MITTELWERT(Fahrtstrecken).
Tipp: Formeldetektiv
Je größer Tabellenblätter werden, desto unübersichtlicher werden sie. Damit Sie später noch die Möglichkeit haben, Zusammenhänge zwischen einzelnen Zellen nachzuvollziehen, gibt es in Excel die Formelüberwachung. Sie verfolgen damit, welche Zellen in die Berechnungen einer Formel einfließen.
Markieren Sie die Zelle, deren Formel Sie untersuchen möchten. Wählen Sie den Befehl Extras/Formelüberwachung/Spur zum Vorgänger. Eine blaue Markierung zeigt an, welche Zellen in der Formel verwendet werden. Falls eine Vorgängerzelle auf einem anderen Tabellenblatt liegt, wird das Blatt durch ein Tabellensymbol angedeutet. Wählen Sie den Befehl Extras/Formelüberwachung und Spur zum Nachfolger, um zu überprüfen, ob auf eine Zelle in anderen Formeln der Tabelle zugegriffen wird. Um zu prüfen, ob noch Verknüpfungen mit anderen Zellen bestehen, rufen Sie den Befehl erneut auf.
Durch einen Doppelklick auf die Verbindungspfeile können Sie zwischen den Zellen hin und her springen. Dieser Trick ist besonders bei umfangreichen Tabellenblättern nützlich und funktioniert sogar über die Blattgrenzen hinweg. In diesem Fall zeigt Excel ein kleines Dialogfeld an, in dem Sie die anzuspringende Zelle auswählen müssen. Um die Pfeile der Formelüberwachung wieder vom Bildschirm zu verbannen, rufen Sie den Befehl Extras/Formelüberwachung/Alle Spuren entfernen auf.
Wollen Sie nur die Pfeile einer bestimmten Zelle ausblenden, benutzen Sie die Befehle Spur zum Vorgänger entfernen oder Spur zum Nachfolger entfernen. Wenn Sie die Befehle der Formelüberwachung regelmäßig einsetzen, sollten Sie die Symbolleiste Formelüberwachung via Extras/Formelüberwachung/Detektivsymbolleiste anzeigen einschalten. Auf ihr finden Sie für jeden der hier vorgestellten Befehle eine eigene Schaltfläche.
Tipp: Alle Formeln anzeigen
Normalerweise zeigt Excel die Formel einer Zelle nur dann an, wenn Sie die Zelle markieren oder ihren Inhalt bearbeiten. Manchmal ist es aber hilfreich, wenn alle Formeln eines Tabellenblattes gleichzeitig sichtbar sind.
Wichtig ist dies zum Beispiel, wenn Sie versuchen, den Aufbau eines Tabellenblatts nachzuvollziehen, um es zu ergänzen. Dieser spezielle Modus lässt sich mit dem Befehl Extras/Formelüberwachung/ Formelüberwachungsmodus ein- und ausschalten (oder mit der Tastenkombination [Strg]+[#]). Excel zeigt dann alle Formeln auf dem Tabellenblatt an und verbreitert dazu die Spalten der Tabelle, damit Sie die Formeln vollständig sehen können.
Tipp: wichtige Zellen im Blick behalten
Eine andere Möglichkeit, um den Inhalt einer Zelle im Blick zu behalten, ist das Überwachungsfenster.
Klicken Sie die Zelle, die Sie beobachten möchten, mit der rechten Maustaste an. Wählen Sie im Kontextmenü der Zelle den Befehl Überwachung hinzufügen. Excel blendet ein Fenster ein, in dem Name, Herkunft, Wert und Formel der Zelle angezeigt werden. Wollen Sie noch weitere Formeln in Ihrer Tabelle überprüfen, klicken Sie im Überwachungsfenster auf die Schaltfläche Überwachung hinzufügen. Klicken Sie auf Überwachung löschen, um die Überwachungsfunktion zu entfernen.
Wenn Sie im Überwachungsfenster doppelt auf einen der dort aufgelisteten Einträge klicken, springt Excel direkt zu der betreffenden Zelle. Damit bietet sich das Überwachungsfenster auch als Lesezeichen an, das Ihnen auf umfangreichen Tabellenblättern eine komfortable Navigation ermöglicht. Übrigens handelt es sich bei dem Überwachungsfenster um eine Symbolleiste. Sie können das Fenster auch über Ansicht/Symbolleiste/ Überwachungsfenster ein- und ausblenden. Und wie bei Symbolleisten üblich, lässt sich das Überwachungsfenster im Excel-Fenster verankern, indem Sie es einfach zum gewünschten Fensterrand ziehen.
Tipp: Fehlerüberprüfung
Auch wenn Excel jede Formel bereits bei der Eingabe überprüft und nur formal richtige Eingaben zulässt, heißt das noch lange nicht, dass die Formel auch korrekt ausgewertet werden kann und das richtige Ergebnis liefert.
Legen Sie also die Formel =B4*Mehrwertsteuer an, wird sie von Excel als formal korrekt akzeptiert. Existiert aber die Zelle mit dem Namen Mehrwertsteuer nicht, kann Excel die Formel nicht berechnen und gibt in der Zelle den Fehlerwert #NAME? aus. Zusätzlich zeigt Excel neben der betreffenden Zelle ein Smarttag an, dessen Menü verschiedene Hilfsangebote enthält. Mit dem Befehl Hilfe für diesen Fehler anzeigen können Sie die Online-Hilfe aufrufen, in der zu jedem Fehlertyp Tipps für die Fehlersuche enthalten sind. Diese Informationen beziehen sich natürlich nicht konkret auf die von Ihnen eingegebene Formel, sondern nur allgemein auf den aufgetretenen Fehlertyp.
Mit dem Befehl Berechnungs-Schritte anzeigen können Sie ein Fenster aufrufen, mit dem Sie die Formel schrittweise durchlaufen und überprüfen können. Klicken Sie in diesem Auswahlmenü auf Fehler ignorieren, teilen Sie Excel mit, dass es den Fehler in Zukunft ignorieren soll. Dadurch verschwindet auch der Smarttag, also der Fehlerhinweis neben der Zelle. Haben Sie den Fehler vorschnell übergangen, rufen Sie den Befehl Extras/Optionen auf. Im folgenden Fenster wählen Sie auf der Registerkarte Fehlerüberprüfung die Schaltfläche Zu ignorierende Fehler zurücksetzen. Excel zeigt den fraglichen Fehler-Smarttag wieder an.
Der Befehl Optionen zur Fehlerüberprüfung zeigt ein Dialogfeld an, mit dem Sie die Regeln einstellen können, nach denen Excel die Formeln Ihres Tabellenblatts untersucht. Mit dem letzten Eintrag des Smarttag-Menüs können Sie das im letzten Tipp vorgestellte Fenster Formelüberwachung anzeigen.
Tipp: Formeln schrittweise prüfen
Irren ist bekanntlich menschlich und so kommt es in der Praxis regelmäßig vor, dass Ihnen beim Eingeben von Formeln Fehler unterlaufen. Solange Excel darauf mit einer Fehlermeldung reagiert, ist die Gefahr schon halb gebannt. Deutlich kritischer sind Konstruktionsfehler einer Formel, die von Excel nicht erkannt werden.
In einfachen Fällen reicht es aus, wenn Sie sich die Spuren zu den Vorgängerzellen anzeigen lassen. Auf diese Weise lassen sich zum Beispiel falsche Bezüge schnell aufdecken. In hartnäckigen Fällen kann es aber auch notwendig werden, die Formel Schritt für Schritt auszuwerten, um die fehlerhafte Stelle in der Formel zu entdecken. In unserem Beispiel soll eine Formel den Gesamtbetrag einer Rechnung ermitteln, indem sie zum Nettobetrag die in einer separaten Zelle ausgewiesene Mehrwertsteuer addiert. Klicken Sie die Zelle an, die die Formel enthält und wählen Sie den Befehl Extras/Formelüberwachung/Formelauswertung. Excel zeigt die Formel, die der markierten Zelle zugrunde liegt, im Dialog Formel auswerten an. Der Teil der Formel, den Excel im ersten Schritt auswerten wird, ist unterstrichen.
Klicken Sie auf die Schaltfläche Auswerten. Excel berechnet den unterstrichenen Teil der Formel und trägt das Ergebnis in das Fenster ein. Anschließend wird die nächste Teilformel unterstrichen. Daran, dass unten im Fenster die Schaltfläche Einzelschritt aktiv ist, können Sie erkennen, dass der auszuwertende Ausdruck Zellen betrifft, die verzweigt sind und ebenfalls Formeln enthalten. Klicken Sie dann so oft auf die Schaltfläche, bis sie von Excel deaktiviert wird. Mit jedem Klick erscheint im Fenster ein neues Feld, in dem die nächste Ebene der Formel angezeigt wird. Klicken Sie die einzelnen Felder des Fensters der Reihe nach an. Excel stellt das jeweils angeklickte Feld etwas größer dar und markiert die zugrunde liegende Zelle im Tabellenblatt. Klicken Sie jetzt entweder auf Auswerten, damit Excel den unterstrichenen Ausdruck durch sein Ergebnis ersetzt oder springen Sie mit der Schaltfläche Prozedurschritt eine Ebene nach oben. Durchlaufen Sie die Formel, bis Excel das Gesamtergebnis anzeigt. Wenn Sie das Ende der Formel erreicht haben, verwandelt sich die Beschriftung der Schaltfläche Auswerten in Neu starten. Sie können dann einen zweiten Anlauf nehmen.
Tipp: Diese Art der Fehlersuche entspricht dem so genannten Debuggen eines Programms, bei dem ebenfalls versucht wird, einen Fehler durch schrittweises Ausführen des Programms zu finden. Dabei wird zwar ein hohes Maß an Konzentration gefordert, doch dafür führt die Methode auch zuverlässig zum Ziel.
