Einer der größten Vorteile einer Tabellenkalkulation wie Excel ist es, auch komplexe Berechnungen vollautomatisch durchführen lassen zu können. Der Schlüssel zum Erfolg sind dabei Formeln. Excel bietet neben den Formeln noch vielfältige Werkzeuge zur Analyse von Ergebnissen an.
Es gibt zwei Wege, Formeln zu erzeugen. Die eine Möglichkeit ist, die Formel mit all ihren Bezügen und Verweisen direkt in eine Zelle einzutippen. Die zweite Möglichkeit ist, Zellen, die in eine Berechnung einbezogen werden sollen, per Mausklick zu referenzieren.
Um eine Formel sozusagen per Hand einzugeben, gehen Sie wie folgt vor:
Sie können ein Plus-Zeichen (+) oder ein Minus-Zeichen (-) eingeben, um die Formeleingabe einzuleiten. Excel konvertiert die Zelle dann in das entsprechend richtige Format. Wenn Sie also z. B. folgendes eingeben: +B4+B5 konvertiert Excel diese Eingabe zu =+B4+B5.
Excel 97 unterstützt natursprachliche Eingaben in Formeln, so daß Formelnkriterien nicht unbedingt aus den klassischen Zellbezügen bestehen müssen. Anstatt dessen können Sie über Zeilen- und Spaltenbeschriftung auf Zellen verweisen. Ein Beispiel: Sie haben eine Arbeitsmappe, die die Finanzinformation von 20 Geschäften beinhaltet, die über das ganze Land verteilt sind. Sie möchten in Ihrer Formel auf die Verkaufsinformationen Ihres Geschäftes in München zurückgreifen. Dieser Wert befindet sich in der Zelle Q127. Wenn der Spaltentitel der entsprechenden Spalte Verkäufe ist und der Zeilentitel München, dann können Sie einfach eingeben =Verkäufe München. Diese Funktion ist sehr nützlich für große Arbeitsmappen, da es sonst schwierig ist, den Überblick über all die Zellreferenzen zu behalten.
Beim Eintippen einer Formel und der entsprechenden Zellbezüge kann es schnell vorkommen, daß man sich vertippt, beispielsweise einen falschen Zellbezug eingibt. Um diese Fehlerquelle zu minimieren, können Sie alternativ Formeln auch dadurch erzeugen, daß Sie auf jede einzelne zugehörige Zelle mit dem Mauszeiger klicken.
Nehmen wir an, Sie möchten in der Zelle B14 eine Formel konstruieren, die den Wert der Zelle B11 von der Zelle B5 subtrahiert. Um dies nur mit der Maus durchzuführen, gehen Sie wie folgt vor:
Um eine Liste von Zahlen automatisch zu summieren, wie in Bild 13.1, aktivieren Sie die Zelle, in der die Summe erscheinen soll, und klicken dann die Summe-Schaltfläche in der Werkzeugleiste an.
Abbildung 13.1: Das Ergebnis der Formel erscheint in der Zelle B8. Die Formel steht im Formel-Bereich.
Ein weiteres Beispiel soll Ihnen zeigen, wie Sie eine Formel mit Hilfe der Tastatur erzeugen können:
Oftmals ist es nötig, in Formeln auf andere Zellen zu referieren. Excel bietet verschiedene Möglichkeiten, auf andere Zellen zuzugreifen. Sie können hierbei relative oder absolute Adressierung einsetzen sowie auch auf andere Tabellen oder Arbeitsmappen zugreifen. Wie das geht, erfahren Sie in den folgenden Abschnitten.
In den meisten Fällen werden Sie vermutlich einen sogenannten relativen Zellbezug einsetzen, wenn Sie auf andere Zellen referieren. Wenn Sie dann eine Formel kopieren, die Zellreferenzen beinhaltet, werden diese Verweise auf die neue Position des Zellinhaltes adaptiert. Ein Beispiel: Wenn Sie eine Formel erzeugen, die auf den Daten in der Spalte A basiert und diese dann in die Zelle B verschieben, werden die entsprechenden Bezüge auch auf Spalte B umgestellt (siehe Bild 13.2).
Abbildung 13.2: Der Einsatz einer relativen Referenz: Kopieren der Formel in Zelle A3 nach Zelle B3 verändert die Zellbezüge auf Spalte B.
Absolute Zellverweise bzw. -bezüge sind nützlich, wenn Sie auf eine Zelle zugreifen möchten, deren Position sich nicht verändert. Das mag vielleicht auf Anhieb unlogisch klingen, ist aber recht einfach. Die relative Adressierung hat den Vorteil, daß Bezüge aktuell bleiben, auch wenn Sie eine Formel an irgendeine andere Stelle des Blattes verschieben. Nützlich ist es auch, wenn Zellen kopiert werden und Berechnungen nach immer demselben Schema in verschiedenen Reihen oder Spalten durchgeführt werden sollen. Relative Adressierung hat aber auch einen Nachteil. Der tritt immer dann auf, wenn Sie eine Formel in eine andere Zelle verschieben. Wie Sie im Abschnitt über relative Adressierung schon gesehen haben, berechnet Excel in diesem Fall die Bezüge neu und kann dann nicht unterscheiden, ob der Zellbezug relativ oder absolut gemeint war, sich also auf eine feste Zelle im Arbeitsblatt bezieht oder nach einer bestimmten Systematik auf eine im Umfeld liegende Zelle zugreift. In diesem Fall können Sie die absoluten Zellbezüge verwenden. Wenn Sie vor einem Zellbezug das Dollarzeichen setzen, bedeutet das für Excel, daß beim Verschieben der Formel dieser Zellbezug unangetastet bleibt. Ein Beispiel für eine absolute Zellreferenz wäre $A $7. Egal, was Sie dann mit der Formel in der Zelle anstellen oder wohin Sie sie auch verschieben, der Verweis bleibt immer derselbe.
Um einen absoluten Zellbezug in eine Formel einzugeben, gehen Sie also wie folgt vor:
Bild 13.3 illustriert diesen Sachverhalt.
Abbildung 13.3: Die absolute Referenz in Zelle A3 bleibt bestehen, wenn die Formel in Zelle B4 kopiert wird.
Relative und absolute Zelladressierung können Sie auch in einer sogenannten gemischten Referenz verwenden. Als Beispiel: Wenn Sie Ihren Bezug auf die Spalte absolut halten möchten und den Bezug auf die Zeile relativ, geben Sie z. B. $A1 ein. Dies erlaubt Excel beim Verschieben oder Kopieren der Formel den Bezug auf die Spalte zu verändern, nicht aber auf die Zeile.
Um eine bereits existierende Referenz zu bearbeiten, positionieren Sie den Eingabecursor im Formel-Bereich innerhalb der Formel, die Sie ändern möchten. Dann drücken Sie (F4), bis die gewünschten Änderungen vorgenommen sind. Abschließend betätigen Sie die Eingabetaste.
Sie können in eine Formel auch eine Referenz auf Daten einer anderen Tabelle oder sogar einer anderen Arbeitsmappe vornehmen. Um das zu tun, geben Sie den Namen der Tabelle ein, ein Ausrufezeichen und die Zellreferenz. Ein Beispiel: Geben Sie Blatt1!A1 ein, um eine Referenz auf die Zelle A1 in Blatt 1 vorzunehmen.
Wenn der Name der Tabelle Leerzeichen beinhaltet, müssen Sie den Tabellennamen in Anführungszeichen einschließen, z. B. "Budget 1997"!A1.
Manchmal ist es auch wünschenswert, auf Daten zuzugreifen, die sich in einer anderen Arbeitsmappe befinden. Wenn Sie z. B. auf die Zelle C10 über Tabelle »Verkäufe 1997« in der Arbeitsmappe »Westdeutschland« zugreifen möchten, würden Sie folgende Referenz eingeben:
="Westdeutschland.xlsVerkäufe 1997"!$C$10
Die Operatoren in Excel ermöglichen es, mathematische Berechnungen mit Formeln vorzunehmen, Text zu manipulieren, Vergleiche durchzuführen und per Referenz auf verschiedene Daten innerhalb von Excel zuzugreifen.
Zusätzlich zu den eingebauten Berechnungsfunktionen können Sie arithmetische Operationen benutzen, um Berechnungen durchzuführen. Folgende arithmetische Operationen gibt es dabei:
|
Operator |
Zweck |
|
+ |
Addition |
|
- |
Subtraktion |
|
* |
Multiplikation |
|
/ |
Division |
|
% |
Prozent |
|
^ |
Exponentialwert |
Mit Textoperatoren können Sie Texte zerschneiden oder zusammenführen. Ein Beispiel: Wenn Sie eingeben ="Gesamtverkäufe: "&B4, wird der Wert Gesamtverkäufe: 28 zurückgegeben, wenn die Zelle B4 den Wert 28 enthält.
Um Ergebnisse zu vergleichen, können Sie Formeln mit sogenannten Vergleichsoperatoren erzeugen, die dann wahr oder falsch zurückliefern, je nachdem, wie die Bedingung gestellt ist. Ein Beispiel: Die Formel =A4>30 gibt wahr zurück, wenn der Wert in der Zelle A4 größer als 30 ist. Ansonsten wird falsch zurückgeliefert.
Im folgenden ein Überblick über die Vergleichsoperatoren:
|
Operator |
Zweck |
|
= |
gleich |
|
< |
kleiner als |
|
> |
größer als |
|
<= |
kleiner oder gleich |
|
>= |
größer oder gleich |
|
<> |
ungleich |
Referenzoperatoren ermöglichen es, innerhalb einer Formel auf diverse verschiedene Zellen zuzugreifen. Ein Beispiel: Wenn Sie eingeben Summe(A4:A24), werden die Werte der Zellen A 4 bis A 24 aufsummiert.
Die meisten Formelfehler tauchen auf, wenn die arithmetischen Operatoren in einer Formel nicht in der richtigen Reihenfolge eingesetzt werden. Im folgenden eine Übersicht über die richtige Reihenfolge:
|
Operator |
Zweck |
|
^ |
Potenzierung |
|
*, / |
Multiplikation, Division |
|
+, - |
Addition, Subtraktion |
Potenzierung kommt vor Multiplikation oder Division in einer Formel, und Multiplikation und Division kommen vor Addition und Subtraktion.
Ein Beispiel:
Excel berechnet die Formel =4+10*2, indem zuerst 10*2 multipliziert wird und dann das Produkt zu 4 addiert wird, was 24 zurückgibt. Das Ergebnis bleibt daher gleich, egal, ob die Formel als =4+10*2 oder 10*2+4 geschrieben wird.
Wenn eine Formel arithmetische Operatoren desselben Verarbeitungniveaus enthält, wird die Berechnung von links nach rechts segmentiell durchgeführt.
Wie bei normalen mathematischen Berechnungen auch können Sie die Reihenfolge der Berechnung durch das Anschließen von Segmenten in Klammern verändern. Excel führt zuerst alle Operationen innerhalb der Klammern aus und dann erst den Rest. Ein Beispiel: Indem Sie Klammern zu der Formel =4+10*2 hinzufügen, also =(4+10)*2, können Sie Excel dazu bringen, zuerst 4 + 10 zu addieren und dann die Summe mit 2 zu multiplizieren, was dann 28 ergibt.
Ich erhalte eine Fehlermeldung, wenn ich versuche, eine Formel zu erzeugen, die Klammern enthält.
Wenn Sie eine lange Formel erzeugen, muß jede Klammer auf auch durch eine Klammer zu abgeschlossen werden. Ansonsten wird Excel die Eingabe nicht akzeptieren.
Ich habe die Formel =5+10*2 eingegeben und einen falschen Wert, nämlich 25 anstatt 30 zurückerhalten. Denken Sie daran, daß Excel Multiplikation immer vor Addition oder Subtraktion ausführt. Um Excel dazu zu bringen, zuerst zu addieren und dann zu multiplizieren, geben Sie die Formel folgendermaßen ein: =(5+10)*2.
Neben normalen Berechnungen können Sie in Formeln auch mit Datums- und Zeitwerten rechnen. Wenn Sie ein Datum oder eine Uhrzeit in eine Formel einsetzen, muß dabei ein Excel bekanntes Format verwendet werden, und Sie müssen die Werte in Anführungszeichen eingeben. Excel rechnet diese Werte dann intern um, damit sie für mathematische Berechnungen zur Verfügung stehen. Um z. B. die Anzahl der Tage zwischen zwei Datumswerten zu berechnen, müßten Sie folgende Formel eingeben: "2.4.1997"-"27.3.1997".
In diesem Fall würde Excel die Zahl 6 zurückliefern – nämlich die Zahl der vergangenen Tage zwischen den beiden Daten.
Wenn Excel eine Uhrzeit oder einen Datumswerte nicht erkennt und somit auch nicht damit kalkulieren kann, wird die Fehlermeldung #WERT! in der Zelle angezeigt.
Oft benötigen Sie nach einer Berechnung lediglich noch das Ergebnis – nicht aber die Formel. Nachdem Sie z.B. Ihre monatliche Umsatzsteuervoranmeldung berechnet haben, benötigen Sie die Formel nicht mehr. In diesem Fall können Sie die Formel entfernen und lediglich den letzten aktuellen Wert in der Zelle bestehen lassen.
Um Formeln in einen Wert umzuwandeln, gehen Sie wie folgt vor:
Um einen ganzen Bereich von Formeln in Werte zu verwandeln, gehen Sie folgendermaßen vor:
Abbildung 13.4: Verwenden Sie den Inhalte-einfügen-Dialog, um Formeln in Werte umzuwandeln.
Die Arbeit mit Formeln spart Ihnen meistens eine Menge Zeit, da Sie Tabellen mit Berechnungen schnell und einfach aufbauen können. Die andere Seite der Medaille ist, daß dabei natürlich auch Fehler entstehen können, speziell in komplexen oder besonders umfangreichen Formeln oder gegenseitigen Bezügen. Zum Glück bietet Excel für diesen Fall verschiedene Möglichkeiten an, Fehler einzukreisen und sie zu beheben.
Verschiedene Fehler können auftreten, wenn Sie Formeln in Excel eingeben. In den meisten Fällen zeigt Excel in diesem Fall eine Fehlermeldung an, die es Ihnen ermöglicht, den Fehler relativ schnell aufzuspüren. Im folgenden finden Sie eine Übersicht über die gängigsten Fehler und deren mögliche Gründe.
|
Fehler |
Bedeutung |
|
#Div/0! |
In der Formel wird versucht, eine Division durch 0 vorzunehmen. |
|
#N/A |
Die Formel beinhaltet eine Referenz auf eine nicht existierende Zelle bzw. einen nicht existierenden Wert. |
|
#Name? |
Die Formel verwendet einen nicht definierten Namen. |
|
#NUL! |
Die Formel beinhaltet eine Referenz auf eine ungültige Zelle. |
|
#NUM! |
Die Formel verwendet eine Zahl unkorrekt. |
|
# REF! |
Die Formel beinhaltet eine Referenz auf eine nicht gültige Zelle. |
|
#Wert! |
Die Formel verwendet ein unkorrektes Argument oder einen falschen Operator. |
Wenn ein Fehlerwert angezeigt wird, klicken Sie auf die Office-Assistent-Schaltfläche und dann auf Tips, um sich eine Beschreibung des Fehlerwertes anzeigen zu lassen.
Der Befehl Bearbeiten/Gehe zu... bietet einen einfachen Weg, fehlerhafte Zellen aufzufinden. Um diese Funktion einzusetzen, gehen Sie folgendermaßen vor:
Abbildung 13.5: Der Inhalte-auswählen-Dialog hilft Ihnen dabei, fehlerhafte Formeln aufzufinden.
Der Detektiv ist eine weitere nützliche Funktion, die es Ihnen ermöglicht, Fehler aufzufinden. Sie können den Detektiv sowohl dazu einsetzen, Fehler aufzufinden, als auch sicherheitshalber Formeln und Zellreferenzen zu überprüfen. Zusätzlich dazu kann man mit Hilfe des Detektivs auch noch Kommentare in eine Zelle eingeben.
Der Excel-Detektiv ermöglicht Ihnen folgende Aufgaben:
In einer Zelle, die eine Formel enthält, führt die Funktion Vorgänger suchen dazu, daß Zellen aufgefunden werden, die in dieser Formel enthalten sind. Nachfolger suchen findet alle Formeln auf, die zur aktuellen Zelle verweisen. Ein Beispiel: Wenn die Zelle A3 auf den Inhalt der Zelle A1 plus der Zelle A2 verweist, dann verweist die Zelle A3 auf seine Vorgänger, nämlich die Zellen A1 und A2. Die Zelle A1 zeigt auf den Nachfolger A3. Bild 13.6 zeigt Ihnen diese Zusammenhänge.
Abbildung 13.6: Pfeile zeigen sowohl Vorgänger als auch Nachfolger in einer Tabelle an.
Sie können die Detektiv-Funktion sowohl aus der Menüleiste als auch aus der Werkzeugleiste aktivieren. Um sie vom Menü aus zu starten, wählen Sie Extras/Detektiv und dann die in diesem Untermenü enthaltenen Optionen. Um die Detektiv-Werkzeugleiste anzuzeigen, wählen Sie Extras/Detektiv/Detektiv-Symbolleiste aus der Menüleiste aus. Folgende Tabelle gibt einen Überblick über die dort enthaltenen Schaltflächen:
Tabelle 13.1: Die Detektiv-Werkzeugleiste
Stellen Sie sicher, daß die Option aller Anzeigen im Register Ansicht in den Programmoptionen aktiv ist, oder Sie werden nicht in der Lage sein, die Detektiv-Funktionen einzusetzen.
Um einen Fehler aufzuspüren, gehen Sie wie folgt vor:
Um Zellen aufzuspüren und zu identifizieren, die ungültige Daten enthalten, verwenden Sie die Schaltfläche Gültigkeitskette löschen.
Bei komplexen, umfangreichen Formeln und Tabellen kann es sinnvoll sein, zu einzelnen Formeln bzw. Zellen Kommentare hinzuzufügen, um den Sinn und Zweck und die Vorgänge innerhalb einer Formel auch für andere Bearbeiter oder die Zukunft transparent zu halten.
Um einen Kommentar hinzuzufügen, führen Sie bitte folgende Schritte aus:
Abbildung 13.7: Excel ermöglicht es, einen Kommentar zu jeder Zelle hinzuzufügen.
Wenn Sie Kommentare in eine Zelle eingegeben haben, werden Sie feststellen, daß jede Zelle, die mit einem Kommentar versehen ist, einen kleinen rechten Punkt in der oberen rechten Ecke enthält. Sie können die Maus über jede kommentierte Zelle bewegen, um den Zellkommentar anzuzeigen. Um den Kommentar nachträglich zu verändern, drücken Sie (Umschalt)+(F2). Wenn Sie einen Kommentar später wieder löschen möchten, wählen Sie aus der Menüleiste Bearbeiten/Löschen/Kommentar aus oder klicken die rechte Maustaste und wählen aus dem Pop-Up-Menü Kommentar löschen.
Je länger Sie sich damit beschäftigen, Formeln zu schreiben, desto mehr werden Sie bemerken, daß es relativ nichtssagend ist, mit Zellreferenzen wie B9, C9 etc. zu arbeiten. Wenn dann noch die Formeln oder Tabellen komplexer werden, ist es schwer, noch den Überblick zu behalten. Ein guter Ausweg ist in diesem Fall das Zuweisen eines Namens zu einer Zelle oder einem ganzen Zellbereich. Die Formel = Einnahmen – Ausgaben ist wesentlich aussagefähiger als = A1-B2.
Um einen Bereichsnamen festzulegen, gehen Sie folgendermaßen vor:
Eine alternative Methode sieht wie folgt aus:
Bitte denken Sie an folgendes, wenn Sie einen Bereichsnamen festlegen:
Um schnell zu einem Bereich zu springen, tippen Sie den Bereichsnamen in das Namensfeld im Formel-Bereich ein.
Um eine Liste aller definierten Bereichsnamen zu erhalten, klicken Sie den Pfeil neben dem Namensfeld im Formel-Bereich an. Die Ausklappliste zeigt alle in der Arbeitsmappe vorhandenen Namen an.
Abbildung 13.8: Die Ausklappliste zeigt alle Namen in der Arbeitsmappe an.
Sie können das Namensfeld bzw. die Ausklappliste auch dazu einsetzen, einen Namen als Verweis auf eine Zelle oder einen Bereich in eine Formel einzufügen. Klicken Sie dazu einfach diese Auswahlliste an, und wählen Sie einen Namen aus. Er wird dann beim Editieren einer Formel automatisch eingefügt.
Nachdem Sie einen Bereichsnamen zugewiesen haben, können Sie Bezüge auf diesen Namen verwenden, wie Sie auch auf eine unbenannten Zelle zugreifen würden.
Um einen Namen in eine Formel einzufügen, gehen Sie wie folgt vor:
Abbildung 13.9: Fügen Sie einen Namen in eine Formel ein, indem Sie den Namen-einfügen-Dialog verwenden.
Abbildung 13.10: Ein Beispiel für die Übernahme von Zellverweisen.
Wenn Sie den Inhalt eines Bereichs ändern, kann es manchmal nötig oder sinnvoll sein, einen zugewiesenen Namen zu löschen, weil er nicht mehr länger passend ist. Das geht folgendermaßen:
Abbildung 13.11: In diesem Dialog können Sie Bereichsnamen auch löschen.
Nachdem ich einen Bereichsnamen gelöscht habe, hat Excel einige der Formelergebnisse in meiner Tabelle mit dem Hinweis #Name? ersetzt.
Wenn Sie einen Bereichsnamen löschen, wird jedes Formelergebnis, in dem Bezug auf den alten Bereichsnamen genommen wird, als ungültig definiert. Um das zu korrigieren, müssen Sie dann jede einzelne Formel nachbearbeiten und durch die entsprechenden direkten Zellreferenzen ersetzen oder den Namen erneut vergeben und somit wieder einen Bezug herstellen.
Nachdem ich eine Formel erzeugt habe, die einen Namen enthält, interpretiert Excel diese Formel wie eine Texteingabe. Wenn Sie einen Bereichsnamen als ersten Eintrag in einer Formel verwenden, müssen Sie daran denken, daß die Formel mit dem Gleich-Zeichen beginnen muß, wie z. B. =Verkäufe*4. Tun Sie das nicht, geht Excel davon aus, daß Sie Text in die Zelle eintragen.
(c) 1997 Que
