reading

Bibliografische Information der Deutschen Bibliothek
Die Deutsche Bibliothek verzeichnet diese Publikation in der Deutschen Nationalbibliografie; detaillierte Daten sind im Internet über http://dnb.ddb.de abrufbar.

Hinweis: Alle Angaben in diesem Buch wurden vom Autor mit größter Sorgfalt erarbeitet bzw. zusammengestellt und unter Einschaltung wirksamer Kontrollmaßnahmen reproduziert. Trotzdem sind Fehler nicht ganz auszuschließen. Der Verlag und der Autor sehen sich deshalb gezwungen, darauf hinzuweisen, dass sie weder eine Garantie noch die juristische Verantwortung oder irgendeine Haftung für Folgen, die auf fehlerhafte Angaben zurückgehen, übernehmen können. Für die Mitteilung etwaiger Fehler sind Verlag und Autor jederzeit dankbar. Internetadressen oder Versionsnummern stellen den bei Redaktionsschluss verfügbaren Informationsstand dar. Verlag und Autor übernehmen keinerlei Verantwortung oder Haftung für Veränderungen, die sich aus nicht von ihnen zu vertretenden Umständen ergeben. Evtl. beigefügte oder zum Download angebotene Dateien und Informationen dienen ausschließlich der nicht gewerblichen Nutzung. Eine gewerbliche Nutzung ist nur mit Zustimmung des Lizenzinhabers möglich.

Alle Rechte vorbehalten, auch die der fotomechanischen Wiedergabe und der Speicherung in elektronischen Medien. Das Erstellen und Verbreiten von Kopien auf Papier, auf Datenträgern oder im Internet, insbesondere als PDF, ist nur mit ausdrücklicher Genehmigung des Verlags gestattet und wird widrigenfalls strafrechtlich verfolgt.

Die meisten Produktbezeichnungen von Hard- und Software sowie Firmennamen und Firmenlogos, die in diesem Werk genannt werden, sind in der Regel gleichzeitig auch eingetragene Warenzeichen und sollten als solche betrachtet werden. Der Verlag folgt bei den Produktbezeichnungen im Wesentlichen den Schreibweisen der Hersteller.

Herausgeber: Ulrich Dorn
EPUB-Bearbeitung und Konvertierung: www.goebel-software.com
Coverart & -design: www.ideehoch2.de

ISBN 978-3-645-22019-4

Inhaltsübersicht

1 Excel 2010 – Grundlagen

1.1 Zahlen- und Datumsformate

1.2 Eingaben mit dem Befehl Gültigkeit prüfen

1.3 Bedingte Formatierung

2 Funktionen erstellen und bearbeiten

2.1 Rechenschritte manuell eingeben und bearbeiten

2.2 Grundwissen zu den Formeln

2.3 Relative und absolute Adressierung

2.4 Der Funktions-Assistent

2.5 Formeln schützen

3 Mit Textfunktionen arbeiten

3.1 ERSETZEN

3.2 FINDEN

3.3 GLÄTTEN

3.4 GROSS

3.5 GROSS2

3.6 IDENTISCH

3.7 KLEIN

3.8 LÄNGE

3.9 LINKS

3.10 RECHTS

3.11 SÄUBERN

3.12 SUCHEN

3.13 TEIL

3.14 VERKETTEN

3.15 WECHSELN

3.16 WERT

4 Datums- und Zeitfunktionen

4.1 Rechnen mit Datum und Uhrzeit

4.2 ARBEITSTAG

4.3 DATEDIF

4.4 DATUM

4.5 DATWERT

4.6 HEUTE

4.7 JAHR

4.8 JETZT

4.9 MINUTE

4.10 MONAT

4.11 MONATSENDE

4.12 NETTOARBEITSTAGE

4.13 SEKUNDE

4.14 STUNDE

4.15 TAG

4.16 WOCHENTAG

5 Logische Funktionen

5.1 FALSCH

5.2 NICHT

5.3 ODER

5.4 UND

5.5 WAHR

5.6 WENN

5.7 WENN & ODER

5.8 WENN & UND

5.9 WENNFEHLER

6 Matrixfunktionen

6.1 ADRESSE

6.2 BEREICH.VERSCHIEBEN

6.3 BEREICHE

6.4 INDEX

6.5 INDIREKT

6.6 MTRANS

6.7 SPALTE

6.8 SPALTEN

6.9 SVERWEIS

6.10 VERGLEICH

6.11 WAHL

6.12 WVERWEIS

6.13 ZEILE

6.14 ZEILEN

7 Statistische Funktionen

7.1 ANZAHL

7.2 ANZAHL2

7.3 ANZAHLLEEREZELLEN

7.4 HÄUFIGKEIT

7.5 KGRÖSSTE

7.6 KKLEINSTE

7.7 MAX

7.8 MIN

7.9 MITTELWERT

7.10 MITTELWERTWENN

7.11 MITTELWERTWENNS

7.12 RANG.GLEICH

7.13 RANG.MITTELW

7.14 ZÄHLENWENN

7.15 ZÄHLENWENNS

8 Mathematische und trigonometrische Funktionen

8.1 ABRUNDEN

8.2 AUFRUNDEN

8.3 GANZZAHL

8.4 GERADE

8.5 KÜRZEN

8.6 OBERGRENZE

8.7 OBERGRENZE.GENAU

8.8 PI

8.9 PRODUKT

8.10 REST

8.11 RÖMISCH

8.12 RUNDEN

8.13 SUMME

8.14 SUMMENPRODUKT

8.15 SUMMEWENN

8.16 SUMMEWENNS

8.17 TEILERGEBNIS

8.18 UNGERADE

8.19 UNTERGRENZE

8.20 UNTERGRENZE.GENAU

8.21 VORZEICHEN

8.22 WURZEL

8.23 ZUFALLSBEREICH

8.24 ZUFALLSZAHL

9 Finanzmathematische Funktionen

9.1 BW

9.2 RMZ

9.3 ZINS

9.4 ZW

9.5 ZZR

Stichwortverzeichnis

1 Excel 2010 – Grundlagen

In diesem Buch geht es um den Einsatz von Excel-Funktionen in unterschiedlichen Arbeitsbereichen. Die Berechnungen liefern Ergebnisse als Zahlen, Texte oder auch Datumswerte. Dieses Kapitel beschreibt den grundlegenden Umgang mit den Werten in den Excel-Zellen. Sie erfahren, wie Sie

Zahlen formatieren und Einheiten für die Zellwerte darstellen,
Ein- und Mehrzahl mithilfe der benutzerdefinierten Formatierung einstellen,
spezielle Nummern wie firmeninterne Personalnummern oder Versicherungsscheinnummern formatieren,
den Befehl Gültigkeit zur Steuerung der Benutzereingabe nutzen,
eine Listenauswahl für eine Zelle anlegen,
die bedingte Formatierung einsetzen, um die Zelle abhängig vom Inhalt anzuzeigen,
den Befehl Inhalte einfügen einsetzen, um Zellwerte individuell zu bearbeiten,
Ihre Tabellenblätter für den Ausdruck vorbereiten und schließlich
Ihre Ergebnisse in Form von Diagrammen auswerten und präsentieren.

All diese Themen werden so komprimiert angeboten, dass Sie sich auf den Einsatz Ihrer gewünschten Funktion konzentrieren können.

Franzis

http://bit.ly/dbOW8Q

4 Stunden Video-Lernkurs zu Excel 2010

Download-Link

www.buch.cd

Hier finden Sie alle Beispieldateien übersichtlich nach Kapiteln geordnet. Einfach kurz registrieren und herunterladen.

Lesezeichen

http://www.winfuture-forum.de

http://www.office2010-hilfe.de/

http://www.office-loesung.de

Nützliche Tipps direkt aus der Office-Community

1.1 Zahlen- und Datumsformate

Jede Zahl, die Sie in Excel eingeben, können Sie im Anschluss gestalten. Sie können ihr beispielsweise ein Währungsformat zuweisen. Wichtig ist nur, dass die gewünschten Zellen markiert sind, bevor Sie das Format zuweisen. Auf dem Register Start gibt es Schaltflächen sowie ein Listenfeld zur Zellformatierung.

Bild 1.1 Die Schnellauswahl zu den Zahlenformaten

Neben den Standardkategorien wie z. B. Währung oder Zahl möchten wir im Anschluss die benutzerdefinierten Zahlenformate besonders hervorheben.

Zahlen formatieren

Drei Tastenkombinationen zur schnellen Formatierung gleich zu Beginn:

(Strg) + (Umschalt) + (1) weist den markierten Zellen zwei Nachkommastellen und den Tausenderpunkt zu.
(Strg) + (Umschalt) + (4) weist den markierten Zellen das Währungsformat zu.
(Strg) + (Umschalt) + (6) weist den markierten Zellen das Standardformat zu.

Tipp: Das Standardwährungsformat legen Sie in der Systemsteuerung von Windows fest.

Zahlen mit Einheiten über die benutzerdefinierten Zahlenformate gestalten

Sollten die angebotenen Formate nicht ausreichen, weil Sie z. B. die Angabe 500,00 Liter in einer Zelle benötigen, müssen Sie ein benutzerdefiniertes Zahlenformat anlegen.

Markieren Sie die Zelle oder Zellen, die das Format erhalten sollen.
Öffnen Sie über das Kontextmenü der rechten Maustaste das Fenster Zellen formatieren und aktivieren Sie das Register Zahlen. Alternativ können Sie das Dialogfenster Zellen formatieren über die Tastenkombination (Strg)+(1) öffnen.
In der Kategorie Benutzerdefiniert finden Sie bereits einige vordefinierte Formate, die Sie ändern bzw. anpassen können.
Geben Sie entweder Ihr eigenes Format in das Feld Typ ein oder wählen Sie eines aus der Liste aus. In diesem Beispiel haben wir den Eintrag #.##0,00 angeklickt. Dieses Format wird jetzt in das Feld Typ übernommen. Es ist das Zahlenformat für Zahlen mit Tausenderpunkt und zwei Nachkommastellen.
Klicken Sie jetzt hinter die letzte 0, drücken Sie einmal auf die (Leer)-Taste und schreiben Sie "Liter". Im Feld Typ sollte jetzt der folgende Eintrag stehen:

Bild 1.2 Das eigene benutzerdefinierte Zahlenformat für das Beispiel Liter

Das bedeutet, dass hinter den Zahlenangaben mit zwei Nachkommastellen die Einheit Liter angezeigt wird.

5,00  Liter  bzw.  5.500,00 Liter  bzw.  1.123.500,00 Liter

Die #-Symbole sind Platzhalter für den Fall, dass eine größere Zahl eingegeben wird. Wenn die Zahl größer als 1000 ist, werden Tausenderpunkte angezeigt, ohne dass Sie sie extra eingeben müssen. Wenn Sie keine Tausenderpunkte wollen, wählen Sie das folgende Format:

0,00 "Liter"

Bestätigen Sie Ihre Eingabe mit OK.

Jetzt haben alle markierten Zellen dieses Format. In der Zeile 7 haben wir die benutzerdefinierten Formate sichtbar dargestellt.

Bild 1.3 Zahlen mit Einheiten direkt in den Zellen sind besser lesbar.

Wenn Sie auf eine formatierte Zelle klicken, erkennen Sie in der Bearbeitungsleiste, dass in der Zelle nur die Zahl enthalten ist.

Tipp: Selbstverständlich können Sie diese Zahlen für weitere Berechnungen verwenden.

Benutzerdefinierte Datumsformate

Für Datumswerte verwendet man andere Symbole zur Formatierung, die im Folgenden kurz erläutert werden.

Ein Standarddatum könnte so aussehen:

10.07.2010 oder auch 10.7.10

Leider bieten einige Excel-Versionen dieses nicht in der Kategorie Datum an. Das benutzerdefinierte Format sieht folgendermaßen aus:

tt.MM.jjjj

Im Fenster Zellen formatieren finden Sie in der Kategorie Datum viele vordefinierte Datumsformate.

Ein weiteres Datumsformat, das auch oft fehlt:

Samstag, den 10. Juli 2010

Das Format muss ebenfalls bei den benutzerdefinierten Formaten eingegeben werden:

TTTT," den "TT.MMMM JJJJ

Es folgt die Auflösung der Buchstaben für das Datum 10.07.2010.

Bild 1.4 Die vordefinierten Datumsformate

Für den Tag:

Formatzeichen	Darstellung	Beschreibung
`T`	10	Tag (Zahl) ohne führende 0
`TT`	10	Tag (Zahl) mit führender 0
`TTT`	Sa	Wochentag kurz
`TTTT`	Samstag	Wochentag lang

Für den Monat:

Formatzeichen	Darstellung	Beschreibung
`M`	7	Monat (Zahl) ohne führende 0
`MM`	07	Monat (Zahl) mit führender 0
`MMM`	Jul	Monatsname kurz
`MMMM`	Juli	Monatsname lang

Tipp: Bitte beachten Sie, dass der Buchstabe »M« für den Monat groß geschrieben ist. Das kleine »m« steht für Minuten.

Bei den Jahren sind nur die folgenden beiden Kürzel sinnvoll:

Formatzeichen	Darstellung	Beschreibung
`JJ`	10	Jahreszahl kurz (zweistellig)
`JJJJ`	2010	Jahreszahl lang (vierstellig)

1.2 Eingaben mit dem Befehl Gültigkeit prüfen

Nehmen wir an, Sie haben eine Tabelle erstellt, in die auch Kollegen Daten eingeben müssen. Nun wissen Sie, dass es einige Personen gibt, die vielleicht nicht die gewünschten Informationen in die Zellen schreiben. Wenn Sie beispielsweise wünschen, dass eine Zahl zwischen 1 und 100 in die Zelle eingegeben werden soll, dann setzen Sie den Befehl Gültigkeit ein. Gültigkeit prüft die Zelleingabe und bringt bei Nichtbeachtung eine Fehlermeldung.

Markieren Sie die Zelle oder die Zellen, die einer Gültigkeitsprüfung unterliegen sollen.
Aktivieren Sie das Register Daten und klicken Sie auf die Schaltfläche Datenüberprüfung.

Bild 1.5 Die Funktion Gültigkeit lässt nur ganze Zahlen zwischen 1 und 100 in den markierten Zellen zu.
Im Feld Zulassen müssen Sie definieren, welche Prüfung erfolgen soll. In diesem Beispiel lassen wir nur ganze Zahlen zu.

Über das Feld Zulassen können Sie die Eingabe von Datums- oder Zeitwerten erzwingen. Mit dem Eintrag Textlänge definieren Sie eine maximale Eingabe von Zeichen für die Zellen. Wenn Sie beispielsweise möchten, dass nicht mehr als 10 Zeichen eingegeben werden dürfen, wählen Sie den Eintrag Textlänge.

Nachdem Sie den Eintrag Ganze Zahl gewählt haben, müssen Sie die Grenzen bestimmen. In diesem Beispiel haben wir zwischen 1 und 100 gewählt. Also sind alle Eingaben, die größer als 100 oder kleiner als 1 sind, nicht zulässig.
Wenn Sie in dieser Zelle eine Eingabe vornehmen, bei der die Bedingung nicht zutrifft, dann erscheint die folgende Fehlermeldung:

Bild 1.6 Eine falsche Eingabe wird durch diese Meldung quittiert.

Die Eingabeaufforderung

Sie können den Zellen mit der Gültigkeitsprüfung eine Eingabeaufforderung zuweisen. Dann erscheint beim Markieren der Zelle ein Hinweis, den Sie selbst gestalten können.

Markieren Sie die gewünschten Zellen.
Öffnen Sie das Fenster Datenüberprüfung und aktivieren Sie das Register Eingabemeldung.
Schreiben Sie die Eingabeaufforderung.

Wenn Sie die Zelle mit der Prüfung markieren, dann erscheint die von Ihnen generierte Meldung.

Bild 1.7 Die von Ihnen erstellte Eingabeaufforderung

Die Fehlermeldung

Sie können auch die Fehlermeldung gestalten und zusätzlich bestimmen, was bei einer Fehleingabe mit dem Zellinhalt geschehen soll.

Markieren Sie die gewünschten Zellen.
Öffnen Sie das Fenster Datenüberprüfung und aktivieren Sie das Register Fehlermeldung.
Schreiben Sie die Fehlermeldung und wählen Sie den Typ aus.

Eigene Fehlermeldung zur Datengültigkeit

Wenn Sie die Fehlermeldung Stopp gewählt haben, erscheint bei einer Falscheingabe eine Fehlermeldung, bei der Sie nur die Wahl haben zwischen Wiederholen, dann wird der Cursor wieder in die Zelle gesetzt, oder Abbrechen, dann wird der Inhalt gelöscht bzw. der vorherige Inhalt angezeigt.

Wenn Sie den Typ Warnung gewählt haben, erscheint ein Hinweisfenster, in dem bei einem Klick auf Ja die falsche Eingabe übernommen wird. Bei einem Klick auf Nein wird der Cursor wieder in die Zelle gesetzt und bei Abbrechen wird der Inhalt gelöscht bzw. der vorherige Inhalt wieder angezeigt.

Bei Wahl der Fehlermeldung Information erscheint eine Meldung, bei der die falsche Eingabe mit OK übernommen werden kann. Mit Abbrechen wird der Zellinhalt gelöscht bzw. der vorherige Inhalt wieder angezeigt.

1.3 Bedingte Formatierung

Eine bevorzugt eingesetzte Funktion ist die hervorgehobene Darstellung von Zahlenwerten über die Hintergrundfarbe, die Rahmenart oder den Schriftstil.

Vielleicht haben Sie eine Liste mit Zahlen erstellt und wollen auf den ersten Blick darüber informiert werden, welche Zahlen einer oder mehreren Bedingungen entsprechen.

In diesem Beispiel sollen alle Zellen, deren Inhalt größer als 980 ist, mit einer roten Füllfarbe gezeigt werden.

Markieren Sie alle Zellen, in denen die Bedingung geprüft werden soll.
Aktivieren Sie das Register Start und wählen Sie die Befehlsfolge Bedingte Formatierung / Regeln zum Hervorheben von Zellen / Größer als.

Bild 1.8 Die bedingte Formatierung starten
Geben Sie ins Fenster Größer als den gewünschten Wert ein und wählen Sie am Listenfeld mit das gewünschte Format aus.

Bild 1.9 Bei einem Zellwert größer als 980 soll die Zelle rot eingefärbt werden.
Bestätigen Sie mit OK.

Bild 1.10 Jetzt sind alle Zellen, die der Bedingung entsprechen, rot eingefärbt.

Weitere Bedingungen einsetzen

Wenn Sie die Bedingung größer oder gleich 980 einsetzen möchten, dann wählen Sie wieder die Befehlsfolge Bedingte Formatierung / Regeln zum Hervorheben von Zellen / Weitere Regeln.

Wählen Sie im unteren Teil des Fensters Neue Formatierungsregel die Bedingung aus, in unserem Beispiel größer oder gleich. Geben Sie dann im Feld rechts daneben den Wert ein.

Mit einem Klick auf die Schaltfläche Formatieren können Sie zwischen verschiedenen Zellformaten wählen.

Bild 1.11 Das Fenster Neue Formatierungsregel bietet noch mehr Möglichkeiten.

Datenbalken einsetzen

Ein weiterer Befehl bei der bedingten Formatierung betrifft die Datenbalken. Je höher der Wert, desto länger der Balken. Die folgende Abbildung zeigt ein Beispiel:

Bild 1.12 Datenbalken zur Kennzeichnung der Zahlen einsetzen

2 Funktionen erstellen und bearbeiten

In den folgenden Kapiteln finden Sie Beschreibungen zu allen Excel-Funktionen. Um eine der im Buch beschriebenen Excel-Funktionen in den eigenen Dateien anzuwenden, müssen Sie die Funktionen in die Zellen eingeben und bearbeiten. Es gibt verschiedene Möglichkeiten, Funktionen zu erfassen:

Manuelle Eingabe eines Rechenschritts in eine Zelle
Eingeben und Bearbeiten der Formeln in der Bearbeitungsleiste
Erstellen von Funktionen mit dem Funktions-Assistenten

Diese Varianten werden im Detail erläutert.

Download-Link

www.buch.cd

Hier finden Sie alle Beispieldateien übersichtlich nach Kapiteln geordnet.

2.1 Rechenschritte manuell eingeben und bearbeiten

Die erste Möglichkeit ist die manuelle Eingabe eines einfachen Rechenschritts in eine Excel-Zelle.

Manuelle Eingabe eines einfachen Rechenschritts

Öffnen Sie die Beispieldatei Funktionen_erfassen.xlsx.
Setzen Sie den Cursor in die Zelle D8, in der die Formel erstellt werden soll.
Eine Excel-Funktion beginnt mit einem Gleichheitszeichen (=). Um im vorliegenden Beispiel die beiden Zahlen zu addieren, geben Sie nach dem Gleichheitszeichen die folgende Formel ein:
```
=D4+D6
```
Ist die Formeleingabe beendet, drücken Sie die (Eingabe)-Taste.

Bild 2.1 Eine Formel manuell erfassen.

Wenn Sie jetzt die Zahlen in der Zelle D4 oder D6 ändern, wird das Ergebnis in D8 direkt nach Drücken der (Eingabe)-Taste aktualisiert. Auf diese Art können Sie alle Berechnungen erstellen, so einfache, wie gerade beschrieben, oder auch sehr komplexe. Die folgende Tabelle zeigt die Tasten, auf denen Sie die Rechenschritte finden.