Rheinwerk Computing < openbook > Rheinwerk Computing - Professionelle Bücher. Auch für Einsteiger.
Professionelle Bücher. Auch für Einsteiger.

Inhaltsverzeichnis
1 Einleitung
2 Basiswissen für die Arbeit mit Excel 2007
3 Aufbau von Kalkulationstabellen
4 Entwicklung von Berechnungen mit Formeln
5 Gestaltung von Tabellenblättern
6 Auswertungen und Was-wäre-wenn-Analysen
7 Optimierungen
8 Grafische Präsentation von Daten
9 Diagramme optimal einsetzen
10 Tabellen grafisch aufbereiten
11 Verteilungsvorbereitung
12 Ausdruck und E-Mail-Versand
13 Excel-Daten im Web
14 Gemeinsame Arbeit an Arbeitsmappen
15 Tabellenfunktionen
16 Informationen als Tabellen ordnen und verwalten
17 Datenabfragen und Datenauszüge
18 Pivot–Tabellen und -Diagramme
19 Arbeit mit externen Daten
20 Datenaustausch zwischen Anwendungen
21 Datenaustausch mit anderen Anwendungen
22 Routineaufgaben mit Makros automatisieren
23 Visual Basic für Applikationen
A Tastenkombinationen
Stichwort

Jetzt Buch bestellen
Ihre Meinung?

Spacer
 <<   zurück
Excel 2007 - Das umfassende Handbuch von Helmut Vonhoegen
Buch: Excel 2007 - Das umfassende Handbuch

Excel 2007 - Das umfassende Handbuch

1012 S., 39,90 Euro
Rheinwerk Computing
ISBN 978-3-89842-864-4
gp 4 Entwicklung von Berechnungen mit Formeln
  gp 4.1 Der Aufbau von Formeln
    gp 4.1.1 Formeltypen
    gp 4.1.2 Operatoren und ihre Priorität
    gp 4.1.3 Addition und Subtraktion
    gp 4.1.4 Multiplikation und Division
    gp 4.1.5 Texte verketten
    gp 4.1.6 Tests mit logischen Formeln
    gp 4.1.7 Funktionen
  gp 4.2 Eingabe von Formeln und Funktionen
    gp 4.2.1 Konstanten in Formeln
    gp 4.2.2 Eingabe von Bezügen
    gp 4.2.3 Bereichsangaben
    gp 4.2.4 Tipps zur Eingabe von Bezügen
    gp 4.2.5 Hilfe bei der Eingabe von Funktionen
  gp 4.3 Relative und absolute Bezüge
    gp 4.3.1 Arbeit mit relativen Bezügen
    gp 4.3.2 Absolute und gemischte Bezüge
    gp 4.3.3 Teilabsolute Bezüge
    gp 4.3.4 Aufsummierung durch Mischbezüge
    gp 4.3.5 Verknüpfte Bereiche und Schnittmengen
  gp 4.4 Beschreibende Bereichsnamen
    gp 4.4.1 Vorteile von Bereichsnamen
    gp 4.4.2 Geltungsbereich – Mappe oder Blatt?
    gp 4.4.3 Namensgebung
    gp 4.4.4 Benannte Formeln definieren
    gp 4.4.5 Übernahme von Namen aus Beschriftungen
    gp 4.4.6 Anwenden von Namen in Formeln
  gp 4.5 Matrixformeln
    gp 4.5.1 Matrixbereiche in Excel
    gp 4.5.2 Arbeit mit Matrixformeln
    gp 4.5.3 Vereinfachung von Berechnungen
    gp 4.5.4 Ändern einer Matrixformel
  gp 4.6 Qualitätssicherung und Fehlervermeidung
    gp 4.6.1 Formelüberwachung
    gp 4.6.2 Werteprüfung im Überwachungsfenster
    gp 4.6.3 Zirkuläre Formeln
    gp 4.6.4 Formeln schrittweise prüfen
  gp 4.7 Tabellen mit Formeln verknüpfen
    gp 4.7.1 Schreibweise externer Bezüge
    gp 4.7.2 Beispiel Wechselkurse
  gp 4.8 Auswirkungen der Zellbearbeitung auf Formeln
  gp 4.9 Kontrolle der Neuberechnung

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.


Rheinwerk Computing

4.1 Der Aufbau von Formeln  downtop

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.


Rheinwerk Computing

Schnelle Summen mit AutoBerechnung  downtop

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


Rheinwerk Computing

Die Rolle der Formeln  downtop

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.


Rheinwerk Computing

Formelketten  downtop

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.


Rheinwerk Computing

4.1.1 Formeltypen  downtop

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)

Rheinwerk Computing

Datentypen  downtop

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.


Rheinwerk Computing

4.1.2 Operatoren und ihre Priorität  downtop

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.


Rheinwerk Computing

Tabelle der Operatoren  downtop


Operator Beispiel Bedeutung Priorität
Bereichsoperatoren
: B3:B7 Bereich 1
Leer B3:E8 C4:F12 Schnittmenge 2
; B3:B12;C3:C12 Vereinigung 3
Arithmetische Operatoren
- (5*2) Vorzeichen 4
% 10  % Prozent 5
^ 5^2 Potenzierung 6
* 5*6 Multiplikation 7
/ 6/3 Division 7
+ 7+4 Addition 8
- 4 – 2 Subtraktion 8
Verkettungsoperator
& B2&B3 Textverkettung 9
Vergleichsoperatoren
= B5=B7 gleich 10
<> B5<>B7 ungleich 10
<= B5<=B7 kleiner/gleich 10
>= B5>=B7 größer/gleich 10

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

Rheinwerk Computing

4.1.3 Addition und Subtraktion  downtop

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!.


Rheinwerk Computing

4.1.4 Multiplikation und Division  downtop

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.


Rheinwerk Computing

Division durch Null abfangen  downtop

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.


Rheinwerk Computing

4.1.5 Texte verketten  downtop

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.


Rheinwerk Computing

4.1.6 Tests mit logischen Formeln  downtop

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.


Rheinwerk Computing

4.1.7 Funktionen  toptop

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.

 <<   zurück
  
  Zum Rheinwerk-Shop
Zum Rheinwerk-Shop: Excel 2007 - Das umfassende Handbuch
Excel 2007 -
Das umfassende
Handbuch

Jetzt bestellen


 Ihre Meinung?
Wie hat Ihnen das Openbook gefallen?
Ihre Meinung

 Buchtipps
Zum Rheinwerk-Shop: VBA mit Excel






 VBA mit Excel


Zum Rheinwerk-Shop: Einstieg in VBA mit Excel






 Einstieg in VBA
 mit Excel


Zum Rheinwerk-Shop: Excel 2010 im Controlling






 Excel 2010
 im Controlling


Zum Rheinwerk-Shop: IT-Handbuch für Fachinformatiker






 IT-Handbuch für
 Fachinformatiker


Zum Rheinwerk-Shop: Visual C# 2012






 Visual C# 2012


 Lieferung
Versandkostenfrei bestellen in Deutschland, Österreich und der Schweiz
InfoInfo




Copyright © Rheinwerk Verlag GmbH 2008
Für Ihren privaten Gebrauch dürfen Sie die Online-Version natürlich ausdrucken. Ansonsten unterliegt das Openbook denselben Bestimmungen, wie die gebundene Ausgabe: Das Werk einschließlich aller seiner Teile ist urheberrechtlich geschützt.
Alle Rechte vorbehalten einschließlich der Vervielfältigung, Übersetzung, Mikroverfilmung sowie Einspeicherung und Verarbeitung in elektronischen Systemen.


Nutzungsbestimmungen | Datenschutz | Impressum

Rheinwerk Verlag GmbH, Rheinwerkallee 4, 53227 Bonn, Tel.: 0228.42150.0, Fax 0228.42150.77, service@rheinwerk-verlag.de

Cookie-Einstellungen ändern