Galerie
Excel: Tipps zu Formeln eines Tabellenblattes
Das eigentliche Know-how eines Tabellenblattes steckt in seinen Formeln, denn diese bereiten die Zahlen perfekt auf. Dieser Workshop verrät Ihnen Tricks, mit denen Sie Ihre Formeln effektiv und flexibel gestalten.
Absoluter Bezug: Da die Kilometerpauschale immer in der gleichen Zelle steht, müssen Sie diese in allen Formeln mit einem absoluten Bezug einfügen.
TIPP 1: Relative und absolute Bezüge
Wenn Sie in einer Formel auf den Inhalt einer anderen Zelle zugreifen wollen, verwenden Sie üblicherweise einen Bezug der Form B6. Der Vorteil solcher Bezüge: Excel passt diese automatisch an, wenn sich die Lage der betreffenden Zellen verändert. Das passiert, wenn Sie zum Beispiel einige Zeilen oder Spalten in das Dokument einfügen. Man spricht bei solchen Verknüpfungen auch von relativen Bezügen.
Gelegentlich ist aber auch der Zugriff auf ein und dieselbe Zelle notwendig. In diesem Fall benötigen Sie einen Bezug, der gewissermaßen immun gegen Verschiebungen ist. Solche Bezüge werden in Excel als absolute Bezüge bezeichnet.
Optisch erkennen Sie absolute Bezüge an einem vorangestellten Dollarzeichen. Soll eine Ihrer Formeln explizit auf die Zelle B6 zugreifen, weil Sie dort einen Vorgabewert für Ihre Kalkulation speichern, geben Sie in Ihrer Formel den Ausdruck $B$6 ein.
In der Abbildung "Absoluter Bezug" ist in der Spalte "Rückerstattung" ein absoluter Bezug auf die Kilometerpauschale in B6 notwendig. Auf diese Weise tippen Sie die Formel nur einmal ein und kopieren Sie durch Aufziehen in die darunterliegenden Spalten. Der Bezug auf die Zelle B6 bleibt dabei in Kopien unverändert.
Sie fixieren die Spalten- oder die Zeilenangabe in einem Zellbezug. Kopieren und verschieben Sie eine Zelle in der Formel =C$12+D$12, bleibt lediglich die Zeilenangabe erhalten und die Spaltenangaben werden durch Excel angepasst. Um beim Schreiben einer Formel einen absoluten Bezug einzugeben, benutzen Sie folgendes Schema: Klicken Sie die gewünschte Zelle an, damit Excel die Koordinaten der Zelle mit dem gewohnten (relativen) Bezug in die Formel einträgt. 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.
Zelle Benennen: Hier können Sie den gewünschten Namen für eine Zelle eintippen.
TIPP 2: Zellen benennen
In den Formeln einer Reisekostenabrechnung taucht die von Ihrem Arbeitgeber gezahlte Kilometerpauschale naturgemäß mehrfach auf. Um hier flexibel zu bleiben, sollten Sie den Geldbetrag nicht jedes Mal manuell eingeben, sondern ihn in einer speziellen dafür vorgesehenen Zelle speichern. Später nehmen Sie in Ihren Formeln darauf Bezug.
Richtig elegant wird dieser Methode aber erst dann, wenn Sie der betreffenden Zelle einen Namen geben. Mit diesem Trick verwandelt sich zum Beispiel der wenig aussagekräftige Ausdruck H1*$B$6 in die "sprechende" Formel H1*kmPauschale. Die $-Zeichen in der Originalformel signalisieren dabei absoluten Bezug.
Das Benennen einer Zelle ist mit Excel denkbar einfach. Markieren Sie einfach die gewünschte Zelle und klicken Sie ganz links in der Bearbeitungsleiste in das Namensfeld. Diese befindet sich dort, wo normalerweise die Koordinaten der aktuellen Zelle stehen. Geben Sie dort den gewünschten Namen der Zelle ein und drücken Sie dann die Eingabetaste. Ein Beispiel sehen Sie im Bild "Zelle Benennen".
Von nun an sprechen Sie die Zelle in Ihren Formeln über deren Namen an und müssen nicht umständlich einen absoluten Zellbezug eintragen. Wenn Sie diese Technik konsequent einsetzen, werden Ihre Formeln vor allem übersichtlicher und Sie sparen sich Tipparbeit. Die vorgestellte Methode sorgt dafür, dass Sie auch noch nach Monaten und Jahren problemlos Ihre Formeln in Ihren Excel-Dokumenten nachvollziehen können.
Übrigens: Sie können nicht nur einzelne Zellen, sondern auch Zellbereiche sowie ganze Zeilen und Spalten mit einem Namen versehen. Aus der Formel
= MITTELWERT(H10:H46)
machen Sie die deutlich anschaulichere Variante
= MITTELWERT(Fahrtstrecken)
Auf der Registerkarte "Formeln" finden Sie in der Gruppe "Definierte Namen" weitere Funktionen, die das Verwenden von benannten Zellen erleichtern.
Interessant in diesem Zusammenhang ist vor allem der Befehl "Namen definieren". Mit ihm öffnen Sie ein Dialogfeld. Darin legen Sie fest, ob der definierte Name nur im aktuellen Tabellenblatt oder in der gesamten Arbeitsmappe gültig sein soll. Außerdem können Sie hier auch einen erläuternden Text eintragen. Der Kommentar hilft Ihnen später die Namensdefinition zu verstehen oder wenn Sie das Dokument an eine andere Person weitergeben.
Der Namens-Manager
Für einen schnellen Überblick über die in einer Arbeitsmappe enthaltenen Zellen, eignet sich der Namens-Manager. Sie öffnen ihn mit der Tastenkombination"Strg+F3" oder über die Registerkarte "Formeln". Im Fenster des Namensmanagers sehen Sie neben dem Namen und der Position der Zelle auch ihren aktuellen Wert und einen eventuell vergebenen Kommentar.
Formel-Detektiv: Die Überwachung für Formeln zeigt genau an, welche Zellen die gewählte Berechnung beeinflussen.
TIPP 3: Der Formeldetektiv
Bei umfangreichen Tabellenblättern mit vielen Formeln ist es oft schwierig, die Zusammenhänge zwischen den einzelnen Zellen nachzuvollziehen. Hier leistet die Formelüberwachung von Excel wertvolle Dienste, denn mit ihrer Hilfe finden Sie sich sogar in fremden Formelbergen mühelos zurecht. Wenn Sie zum Beispiel feststellen wollen, welche Zellen in die Berechnungen einer Formel einfließen, gehen Sie folgendermaßen vor:
Markieren Sie die Zelle, deren Formel Sie untersuchen möchten. Wechseln Sie auf die Registerkarte "Formeln" und klicken Sie in der Gruppe "Formelüberwachung" auf die Schaltfläche "Spur zum Vorgänger". Excel zeigt dann auf dem Tabellenblatt mit blauen Pfeilen an, welche Zellen in der Formel der markierten Zelle verwendet werden. Ein Beispiel finden Sie im Bild auf der nächsten Seite. Falls eine Vorgängerzelle auf einem anderen Tabellenblatt liegt, wird das Blatt durch ein kleines Tabellensymbol angezeigt.
Wollen Sie hingegen wissen, ob auf eine bestimmte Zelle in anderen Formeln der Tabelle zugegriffen wird, markieren Sie die Zelle und wählen den Befehl"Spur zum Nachfolger". Um zu prüfen, ob noch Verknüpfungen mit anderen Zellen bestehen, rufen Sie den Befehl erneut auf. Mit einem Doppelklick auf die Verbindungspfeile springen Sie zwischen den Zellen hin und her. Dieser Trick ist besonders bei großen Tabellenblättern nützlich und funktioniert sogar über die Blattgrenzen. Dann zeigt Excel ein Dialogfeld an, in dem Sie die anzuspringende Zelle wählen können.
Um die Pfeile der Formelüberwachung wieder vom Bildschirm zu verbannen, klicken Sie in der Registerkarte "Formeln" auf "Pfeile entfernen".
Formelberechnung konfigurieren
Das Verhalten von Excel beim Umgang mit Formeln können Sie sehr variabel steuern. Die dazu benötigten Einstellungen finden Sie in den Excel-Optionen (Office-Menüfenster) in der Kategorie "Formeln". Besonderes Augenmerk verdienen die Optionen zur Fehler überprüfung, mit denen Sie zum Beispiel Fehler ausblenden können, die eher den Status einer Warnung haben.
Formelübersicht: Im Formelüberwachungsmodus zeigt Excel alle Formeln des Tabellenblattes an.
TIPP 4: Alle Formeln anzeigen
Normalerweise zeigt Excel die Formel einer Zelle nur dann an, wenn Sie die Zelle markieren bzw. ihren Inhalt bearbeiten. Manchmal ist es aber auch hilfreich, wenn man alle Formeln eines Tabellenblattes gleichzeitig sehen kann.
Beispiel: Sie versuchen den Aufbau eines Tabellenblatts nachzuvollziehen, da Sie es ergänzen oder korrigieren wollen. Dieser spezielle Modus lässt sich mit der Schaltfläche "Formel anzeigen", die Sie auf der Registerkarte "Formeln" in der Gruppe "Formelüberwachung" finden, ein- und ausschalten. Schneller geht es 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.
Formeln schrittweise nachvollziehen
TIPP 4a: Formeln schrittweise nachvollziehen
Wenn Sie beim Prüfen einer Tabelle bemerken, dass die berechneten Ergebnisse nicht schlüssig sind, können Sie die betroffenen Formeln Schritt für Schritt auswerten. Damit endecken Sie schnell den betreffenden Fehler.
Formeln schrittweise nachvollziehen
TIPP 4b: Formeln schrittweise nachvollziehen
Markieren Sie die Zelle, die die Formel enthält und klicken Sie in der Registerkarte "Formeln" auf die Schaltfläche "Formelauswertung". Das ist das Lupensymbol. Excel zeigt dann die Formel im Fenster "Formel auswerten" an. Der Teil der Formel, den Excel im ersten Schritt auswerten wird, ist dabei unterstrichen.
Formeln schrittweise nachvollziehen
TIPP 4c: Formeln schrittweise nachvollziehen
Klicken Sie auf "Auswerten". Excel berechnet den unterstrichenen Teil der Formel und trägt das Ergebnis in das Fenster ein. Anschließend wird die nächste Teilrechnung unterstrichen.
Formeln schrittweise nachvollziehen
TIPP 4d: Formeln schrittweise nachvollziehen
Sollte während dieses Schrittes dabei die Schaltfläche "Einzelschritt"aktiv werden, betrifft der auszuwertende Ausdruck Zellen, die ebenfalls Formeln enthalten. Klicken Sie die Schaltfläche so oft an, 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 jetzt entweder auf den Knopf "Auswerten", damit Excel den unterstrichenen Ausdruck durch sein Ergebnis ersetzt. Alternativ springen Sie mit einem Klick auf die Schaltfläche "Prozedurschritt" eine Ebene nach oben.
Formeln schrittweise nachvollziehen
TIPP 4e: Formeln schrittweise nachvollziehen
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 bei Bedarf direkt einen zweiten Durchlauf vornehmen und damit nach weiteren Problemen und Fehlern in Ihrer Tabelle suchen.
Excel: Tipps zu Formeln eines Tabellenblattes
TIPP 5: Wichtige Zellen im Blick behalten
Beim Entwickeln eines Tabellenblattes werden Sie häufig den Inhalt bestimmter Zellen nachschlagen oder ändern wollen. Das Überwachungsfenster verhindert ständiges Scrollen oder Wechseln zwischen Blättern der Arbeitsmappe. Zum Anzeigen des Fensters wechseln Sie auf die Registerkarte "Formeln" und klicken dort auf die Schaltfläche "Überwachung". Excel blendet dann ein Fenster ein, in dem Herkunft, Name, Wert und Formel der Zelle angezeigt werden.
Um eine Zelle in das Überwachungsfenster einzutragen, markieren Sie die Zelle und klicken dann auf die Schaltfläche "Überwachung hinzufügen", die sich am oberen Rand des Fensters befindet. Excel zeigt ein kleines Fenster, in dem Sie den Vorgang mit "Hinzufügen" bestätigen.
Alternativ klicken Sie auf "Überwachung hinzufügen" und wählen die gewünschte Zelle. Wenn Sie einen der Einträge des Überwachungsfensters doppelt anklicken, springt Excel direkt zur betreffenden Zelle. Das Fenster bietet sich also auch als eine Art Lesezeichenmappe an, mit der Sie zwischen Zellen hin- und herspringen können. Das Überwachungsfenster ist auf dem Bildschirm frei verschiebbar und lässt sich im Excel-Fenster verankern.
