4 Entwicklung von Berechnungen mit Formeln

Der Einsatz von Excel lohnt sich zwar schon, wenn es nur darum geht, Informationen in ordentlicher Form zusammenzustellen, denn gerade die tabellarische Anordnung ist für die übersichtliche Darstellung von Daten gut geeignet. Ob Sie nun eine Checkliste für eine Dienstreise, einen Terminkalender für ein Projekt oder einen Stellenplan für eine Abteilung brauchen, all diese Aufgaben lassen sich in einem Arbeitsblatt effektiv erledigen. So richtig zum Zuge kommen die Fähigkeiten von Excel aber erst, wenn Sie Excel für sich rechnen lassen. Das geschieht in erster Linie durch die Eingabe von Formeln.

4.1 Der Aufbau von Formeln 

Bevor auf die Konstruktion von Formeln im Detail eingegangen wird, soll an dieser Stelle zunächst noch auf eine Funktion von Excel 2007 hingewiesen werden, die Ihnen Rechenergebnisse ohne Formeln anbietet.

Schnelle Summen mit AutoBerechnung 

Wenn Sie sofort wissen wollen, welchen Gesamtbetrag eine Gruppe von Werten ergibt, können Sie auch ganz ohne Formeln ans Ziel kommen. Sobald Sie mehr als eine Zelle markieren, gibt Ihnen Excel unaufgefordert den Mittelwert, die Anzahl und die Summe dieser Werte in der Statusleiste aus. Das funktioniert nicht nur bei geschlossenen Zellblöcken, sondern auch, wenn Sie nicht zusammenhängende Zellen markieren. Das erspart in vielen Fällen den Zugriff auf den Taschenrechner.

Statt der drei vorgegebenen Auswertungen kann über das Menü Statusleiste anpassen auch ein anderes Ergebnis errechnet werden. Dazu müssen Sie den Ergebnisbereich in der Statusleiste nur mit der rechten Maustaste anklicken und die gewünschte Berechnungsart auswählen.

Mittelwert, Anzahl und Summe markierter Zellen und Wahl der Berechnungsart

Die Rolle der Formeln 

Eine Formel ist in Excel so etwas wie ein Dauerauftrag an das Programm. Mit einer Formel bestimmen Sie, dass Excel immer wieder bestimmte Dinge erledigt. Das kann beispielsweise eine Rechenvorschrift sein wie: »Bilde die Summe einer Zahlenkolonne« oder »Multipliziere den Betrag mit dem Faktor 1,19.« Oder aber eine Anweisung wie »Setze eine Bezeichnung aus den aktuellen Werten von zwei Zellen zusammen.«.

Ist die entsprechende Formel einmal gebildet, vielleicht am Anfang durchaus mit gewissen Mühen, steht sie anschließend gleichsam auf Abruf zur Verfügung. Ändern sich die Zahlen in der Kolonne oder der Betrag oder eine der Zeichenketten, liefern die Formeln sofort wieder das aktuelle Ergebnis.

Formelketten 

Material für Formeln können dabei nicht nur Zahlen und Zeichenfolgen, sondern auch selbst wiederum andere Formeln sein. Das heißt beispielsweise, mit dem Ergebnis einer Formel in der Zelle B12 wird in der Zelle F18 weitergerechnet. Die Zelle F18 ihrerseits gibt ihr Ergebnis an eine Formel in der Zelle H15 weiter. Auf diese Weise entstehen regelrechte Formelketten.

Je länger eine solche Kette ist, umso größer ist allerdings auch die Möglichkeit, dass die Übersicht darüber, wie im Arbeitsblatt gerechnet wird, verloren geht. Über Maßnahmen, damit umzugehen, soll später noch gesprochen werden.

4.1.1 Formeltypen 

Normalerweise liefert eine Formel nur einen Wert für die Zelle, in die sie eingetragen ist. Eine spezielle Variante sind Matrixformeln, die Werte für mehrere Zellen berechnen bzw. Wertegruppen gleichzeitig auswerten können.

Excel 2007 kennt verschiedene Typen von Formeln:

• Arithmetische Formelnenthalten Konstanten, Zellbezüge und arithmetische Operatoren (+ – * / ^). Diese Formeln errechnen das Ergebnis und zeigen es in der Zelle an, die die Formel enthält. Beispiele für arithmetische Formeln:

=B7+C7 
=8000 * 1,19 
=C5/5

• Zeichenfolgen-Formeln erlauben die Verknüpfung von zwei oder mehreren Zeichenfolgen mit dem &-Operator. Beispiele für solche Textverkettungen:

="Audio"&2006 
="Farbe "&H4 
="Margo "&"Lewin"

• Logische Formeln enthalten Vergleiche zwischen Konstanten oder Zellbezügen mit Hilfe von Vergleichsoperatoren. Formeln wie »=Z8 > H5« ergeben den Wert WAHR, wenn die darin formulierte Beziehung der beiden Zellwerte tatsächlich wahr ist; wenn nicht, liefert eine solche Formel den Wert FALSCH. Der angezeigte Wert WAHR hat gleichzeitig den numerischen Wert 1, der Wert FALSCH den numerischen Wert 0. (=WAHR + 1 ergibt also 2!)

• Formeln, die Funktionen enthalten, z. B. die Summenfunktion:

=Summe(F1:F30)

Datentypen 

Die verschiedenen Typen können in einer Formel auch gemischt auftreten. Dabei muss aber beachtet werden, dass die Ergebnisse der verschiedenen Teile einer Formel Daten liefern, die vom Typ her verträglich sind.

Eine Funktion, die eine Zeichenfolge zum Ergebnis hat, kann nicht multipliziert werden. Das macht auch wenig Sinn. Dagegen lässt der &-Operator auch Zahlen als Operanden zu, wandelt sie aber automatisch in Zeichenfolgen um.

=1111&2222

ergibt die Zeichenfolge 11112222.

4.1.2 Operatoren und ihre Priorität 

Die einfachsten Formeln, die in einer Zelle abgelegt werden können, sind Formeln, in denen Zahlen mit den Operatoren der Grundrechenarten verknüpft werden. Die Schreibweise entspricht abgesehen von dem vorangestellten Gleichheitszeichen dem gewohnten Bild:

=13+25–5 
=25*4/3 
=3^2

Anstelle von Zahlen, also von Konstanten, können Variablen in die Formeln eingeführt werden. In der Algebra werden für Variable bekanntlich Buchstaben benutzt. Wenn Sie mit Excel 2007 arbeiten, dienen die Zell- oder Bereichsadressen dazu, Variablen in eine Formel einzubeziehen.

Die Operatoren in einer Formel werden entsprechend ihrer Priorität behandelt. Die folgende Liste zeigt die Rangfolge der Priorität. Die höchste Priorität ist 1.

Tabelle der Operatoren 

Die Priorität bestimmt die Reihenfolge, in der Excel die Operatoren auswertet. Nehmen Sie das folgende Beispiel: Ein numerischer Ausdruck lautet:

=5+6*3–7

Würde das Programm schlicht von links nach rechts vorgehen, ergäbe sich

5+6    = 11 
11*3   = 33 
33–7   = 26

Tatsächlich aber führt Excel 2007 korrekterweise zunächst die Multiplikation durch, die Priorität vor der Addition und Subtraktion hat:

6*3    = 18 
5+18–7 = 16

Die Operation mit der höheren Priorität wird also zuerst ausgeführt. Bei Operatoren derselben Priorität wird von links nach rechts gearbeitet. Soll die Rangfolge der Prioritäten unterlaufen werden, kann wie üblich mit Klammern gearbeitet werden. Bei

=(5*6)*(3–7)

wird zunächst das Innere der beiden Klammern berechnet. Das ergibt:

=30*-4 
=-120

4.1.3 Addition und Subtraktion 

Die folgende Liste zeigt einige Beispiele für korrekt eingegebene Formeln, um Ihnen die Schreibweise zu demonstrieren. Wie Sie sehen, können auch Datumswerte als Operanden in den Berechnungen verwendet werden:

=12+27 
=-15+33 
=C17+F18-J21 
=B14 
=$B16-$C$16 
=-(B13+B14+C17) 
=SUMME(B12:F12)+1000 
=SUMME(B12:B17)+SUMME(C12:C19) 
=DATUM(12;12;01)+90 
=SUMME00+SUMME01

Das letzte Beispiel verwendet Bereichsnamen. Es ist allerdings nur dann zulässig, wenn mit den beiden Namen jeweils nur eine Zelle benannt wird. Ist ein Bereich aus mehreren Zellen benannt, liefert die Formel die Fehlermeldung #WERT!.

4.1.4 Multiplikation und Division 

Bei der Multiplikation und Division muss beachtet werden, wie sich das Ergebnis in Bezug auf die Dezimalstellen verhält. Bei der Multiplikation addieren sich die Nachkommastellen:

=33,33*33,33 ergibt 1110,8889 
=33,33*33,33*33,33 ergibt 37025,927037

Wird die Darstellung des Ergebnisses durch die Zellformatierung auf zwei Dezimalstellen reduziert, erscheint zwar in der Zelle ein gerundeter Wert, im Beispiel also 1  110,89 bzw. 37  025,93, intern bleibt aber das genauere Ergebnis gespeichert. Wird auf diese Zelle in einer anderen Formel Bezug genommen, rechnet diese Formel mit den vier bzw. sechs Dezimalstellen weiter. Werden solche Werte dann erneut multipliziert, kann es zu deutlichen Differenzen kommen, je nachdem, ob mit den intern gespeicherten Werten weitergerechnet wird oder mit gerundeten Werten. Die nächste Abbildung zeigt ein kleines Beispiel.

Beispiel für Rundungsdifferenzen

In der Spalte D sind die Ergebnisse der benachbarten Spalte C mit der Funktion RUNDEN bearbeitet worden. In F6 ist das nicht gerundete Gesamtergebnis mit 17 multipliziert, in F7 das gerundete Gesamtergebnis. Die Differenz ist deutlich.

Division durch Null abfangen 

Bei der Division ist die Anzahl der Dezimalstellen, die der Quotient erhält, ganz unterschiedlich. Hier kommt zu dem bei der Multiplikation angesprochenen Rundungsproblem ein weiteres hinzu, das unbedingt beachtet werden muss: Die Division durch Null ist nicht erlaubt.

Solange der Divisor direkt eingegeben wird, lässt sich dieser Fehler relativ leicht vermeiden. Was aber, wenn der Divisor eine Zelladresse oder eine Funktion ist, deren Ergebnis unvorhersehbar ist, wenn also der Divisor eine Variable, eine Unbekannte ist. Für diesen Fall muss Vorsorge getroffen werden.

Der Fall, dass ein Divisor mit dem Wert 0 verwendet wird, sollte unterbunden werden. Das ist möglich mit der WENN-Funktion. Dem Programm kann damit erklärt werden, was anstelle einer Division zu geschehen hat, wenn der Divisor tatsächlich den Wert 0 annehmen sollte. Das kann etwa so aussehen:

=WENN(B3<>0;RUNDEN(A3/B3;2);"")

Wenn der Wert der Zelle B3 ungleich 0 ist, wird die Division A3/B3 durchgeführt; ist B3 aber tatsächlich gleich 0, findet die Division erst gar nicht statt, die Zelle bleibt leer.

4.1.5 Texte verketten 

Gelegentlich ist es sinnvoll, eine Zeichenfolge in einer Zelle durch eine Formel zu erzeugen, die verschiedene Zeichen oder Zeichenfolgen verknüpft. Angenommen, Sie wollen Artikelnummern um zwei Zeichen erweitern, die die Warengruppe beinhalten. Wenn Sie in die Zelle C9

=C5&C8

eintragen, wird der Inhalt von Zelle C5 mit dem Inhalt von Zelle C8 verkettet. Wenn C5 das Warengruppenkennzeichen »PX« enthält und C8 die Artikelnummer »3370086« ist das Ergebnis in Zelle C9 »PX3370086«. Es macht übrigens nichts aus, wenn die Artikelnummer in C8 als Zahl und nicht als Zeichenfolge eingetragen worden ist. Der Operator wandelt die Zahl automatisch in eine Zeichenfolge um.

Anschließend können Sie diese Formel noch in ihr Ergebnis verwandeln. Kopieren Sie die Zellen in die Zwischenablage und fügen Sie sie an derselben Stelle wieder ein, indem Sie über das Kontextmenü Inhalte einfügen aufrufen und die Option Werte verwenden. Wollen Sie zwischen den beiden Textelementen ein Leerzeichen sehen, schreiben Sie:

=C5&" "&C8

Vergessen Sie nicht, die Formel mit dem Gleichheitszeichen zu beginnen, damit Excel 2007 überhaupt merkt, dass es eine Formel berechnen soll. Wenn Sie nur

C5&C8

in die Zelle schreiben, nimmt Excel die Eintragung als ganz normalen Text.

4.1.6 Tests mit logischen Formeln 

Logische Formeln werden benutzt, um zu prüfen, ob bestimmte Tatsachen oder Bedingungen gegeben sind oder nicht. Wenn Sie die Entscheidung über eine Investition z. B. davon abhängig machen wollen, ob sich die Kosten innerhalb von fünf Jahren amortisiert haben, können Sie diese Bedingung als logische Formel formulieren:

=Kosten < Ersparnisse

Voraussetzung ist, Sie haben die Zelle, die die Kosten summiert, mit dem Namen »Kosten« belegt und die Zelle, die die Summe der Ersparnisse der letzten fünf Jahre enthält, mit dem Namen »Ersparnisse«. Wenn Sie eine solche Formel eintragen, vergleicht Excel die Werte der beiden Zellen. Ist die Bedingung erfüllt, sind also die Kosten kleiner als die Summe der Ersparnisse, zeigt die Zelle den Wert WAHR. Trotz der Anzeige des Wortes WAHR hat die Zelle zugleich den numerischen Wert 1. Ist das Ergebnis nicht so günstig, sind die Kosten also noch nicht gedeckt, erscheint in der Zelle FALSCH. Das entspricht dem numerischen Wert 0.

Das ist ganz praktisch für Prüfsummen. Wenn Sie z. B. in drei Zellen untereinander Bedingungen in Form von logischen Formeln ablegen, können Sie sehr einfach prüfen, ob alle Bedingungen erfüllt sind. Sie addieren einfach die Wahrheitswerte. Ist das Ergebnis = 3, sind alle Bedingungen erfüllt. Logische Formeln kennen also nur zwei mögliche Ergebnisse: WAHR oder FALSCH. (In Spalte D werden die logischen Formeln als Text angezeigt.)

Logische Werte lassen sich auch addieren

Eine logische Formel kann nicht nur eine Bedingung enthalten, sondern auch mehrere gleichzeitig. Diese Bedingungen können entweder alternativ oder additiv formuliert werden.

4.1.7 Funktionen 

Funktionen sind spezielle Ausdrücke, die entweder direkt als Formel oder als Teil einer Formel verwendet werden können. Auch wenn eine Formel nur aus einer Funktion besteht, muss sie wie üblich mit einem Gleichheitszeichen beginnen. Im zweiten Fall werden die Funktionen mit Hilfe von Operatoren mit den anderen Teilen einer Formel verknüpft.

Die folgenden Formeln

=A3-SUMME(A10:A20) 
=B4/RUNDEN(C6;2)

sind gültige Ausdrücke, während in dem nächsten Beispiel ein Operator fehlt:

=MITTELWERT(kosten)G6

Funktionen ersetzen z.  T. komplexe Berechnungen, wobei Sie sich um die Art und Weise der Berechnung aber nicht zu kümmern brauchen. Sie füttern die Funktionen nur mit den notwendigen Argumenten, den Rest erledigt das Programm. Bis zu 255 Argumente sind bei einer Funktion möglich.

Funktionen können auch geschachtelt werden, d.  h., das Argument einer Funktion kann selbst wieder eine Funktion sein. Eine Verschachtelungstiefe von bis zu 64 Ebenen ist erlaubt.

In Kapitel 15, Tabellenfunktionen, finden Sie eine ausführliche Referenz aller Funktionen, die Excel anbietet.

Ihre Meinung

Wie hat Ihnen das Openbook gefallen? Wir freuen uns immer über Ihre Rückmeldung. Schreiben Sie uns gerne Ihr Feedback als E-Mail an kommunikation@rheinwerk-verlag.de.