MS Excel 2010 - Profiwissen Formeln und Funktionen · PDF file1.2. Verwendung der Funktionen . Eine Funktion kann allein in einer Zelle oder innerhalb einer Formel stehen. Funktionen

Verwaltungsakademie Berlin

MS Excel 2010 - Profiwissen Formeln und Funktionen

SEMINARUNTERLAGE

© Verwaltungsakademie Berlin Nachdruck sowie jede Art von Vervielfältigung und Weitergabe nur mit Genehmigung der Verwaltungsakademie Berlin

FFOORRMMEELLNN UUNNDD FFUUNNKKTTIIOONNEENN VVEERRWWAALLTTUUNNGGSSAAKKAADDEEMMIIEE BBEERRLLIINN IINNHHAALLTTSSVVEERRZZEEIICCHHNNIISS

Inhaltsverzeichnis 1 Einstieg: Funktionen.............................................................................................................. 4

1.1 Aufbau der Funktionen................................................................................................... 4 1.2 Verwendung der Funktionen.......................................................................................... 4

2 Weiterführende Formeln und Funktionen ........................................................................... 6 2.1 Fehler in einem Arbeitsblatt ........................................................................................... 6 2.2 Kategorie Text................................................................................................................ 8 2.3 Kategorie Datum & Zeit................................................................................................ 12 2.4 Add-Ins......................................................................................................................... 13 2.5 Kategorie Math. & Trigonom........................................................................................ 14 2.6 Kategorie Statistik ........................................................................................................ 15 2.7 Kategorie Logik ............................................................................................................ 15 2.8 Kategorie Matrix........................................................................................................... 17 2.9 Verschachtelte Funktionen .......................................................................................... 19 2.10 Matrizen ....................................................................................................................... 22 2.11 Die Arbeit mit einer Verweis-Funktion ......................................................................... 24 2.12 Zellschutz einrichten .................................................................................................... 28 2.13 Dokumentschutz aufheben .......................................................................................... 29 2.14 Benutzerdefinierte Zahlenformate ............................................................................... 30 2.15 Bedingte Formatierung ................................................................................................ 35 2.16 Die Index-Funktion....................................................................................................... 39

3 Arbeiten mit der Datenüberprüfung ................................................................................... 40 3.1 Datenregel festlegen.................................................................................................... 40 3.2 Vorhandene Daten nachträglich prüfen....................................................................... 43 3.3 Ausdehnen der Datenüberprüfung .............................................................................. 44

4 Stichwortverzeichnis ........................................................................................................... 45

3

EEIINNSSTTIIEEGG:: FFUUNNKKTTIIOONNEENN VVEERRWWAALLTTUUNNGGSSAAKKAADDEEMMIIEE BBEERRLLIINN FFOORRMMEELLNN UUNNDD FFUUNNKKTTIIOONNEENN

4

1 Einstieg: Funktionen Eine besonders große Arbeitserleichterung in einer Tabellen-Kalkulation sind die Funktionen. Vereinfacht gesagt, handelt es sich um vorbereitete Formeln, um Ihnen die Arbeit zu erleich-tern. Hiermit können Sie auch sehr komplizierte Berechnungen durchführen. Mehr als 600 Funktionen stellt Ihnen Excel in den folgenden Kategorien zur Verfügung:

Finanzmathematik

Datum und Uhrzeit

Mathematik und Trigonometrie

Statistik

Matrix

Datenbank

Text

Logik

Informationen

Benutzerdefiniert

Konstruktion

Cube

Kompatibilität

1.1 Aufbau der Funktionen

Funktionsname (Argumente)

z.B.:

= S u m m e ( b 2 : b 6 )

Bei den meisten Funktionen sind nach dem Funktionsnamen ein oder mehrere Argumente ein-zutragen. Mehrere Argumente werden jeweils durch ein Semikolon (;) getrennt. Es gibt nur einige wenige Funktionen mit leeren Klammern, z.B. =Jetzt(),

aber die Klammer ist immer zu schreiben.

Steht der Funktionsname am Anfang einer Formel, muss davor unbedingt das Gleichheitszeichen = stehen!

1.2 Verwendung der Funktionen

Eine Funktion kann allein in einer Zelle oder innerhalb einer Formel stehen. Funktionen können aber auch verschachtelt werden, d.h. in der Klammer einer Funktion können als Argument wie-derum eine oder mehrere Funktionen stehen.

Den Funktionsnamen können Sie auch in Kleinbuchstaben eingeben, er wird automatisch um-gewandelt. Beim Bereich reicht ein Punkt aus. Excel setzt den Doppelpunkt selbst dorthin.

Sehr häufig werden in der Tabellen-Kalkulation statistische Funktionen eingesetzt.

Funktions-Assistent

Zum Einfügen einer Funktion, können Sie auch einen Assistenten aufrufen:

Symbol Funktion einfügen in der Bearbeitungsleiste oberhalb des Arbeitsblattes oder

(ª_)+(F3) oder

Menüband, Registerkarte Formeln, Gruppe Funktionsbibliothek, Schaltfläche Funktion einfügen.

Die Gruppe Funktionsbibliothek

Neben der Schaltfläche Funktion einfügen sind auf der Registerkarte Formeln in der Gruppe Funktionsbibliothek weitere Funktions-Kategorien platziert. Mit einem Klick auf ein Symbol, z.B. , öffnen Sie eine Liste, aus der Sie die Funktion wählen. Danach wird, mit

FFOORRMMEELLNN UUNNDD FFUUNNKKTTIIOONNEENN VVEERRWWAALLTTUUNNGGSSAAKKAADDEEMMIIEE BBEERRLLIINN EEIINNSSTTIIEEGG:: FFUUNNKKTTIIOONNEENN

einer Ausnahme, das vorher gezeigte Dialogfeld Funktionsargumente geöffnet. Wenn Sie allerdings auf den linken Teil des Symbols AutoSumme klicken, wird die Funktion direkt in die Zelle eingefügt.

Die Gruppe Funktionsbibliothek auf der Registerkarte Formeln

5

FFOORRMMEELLNN UUNNDD FFUUNNKKTTIIOONNEENN VVEERRWWAALLTTUUNNGGSSAAKKAADDEEMMIIEE BBEERRLLIINN FFOORRMMEELLNN UUNNDD FFUUNNKKTTIIOONNEENN

2 Weiterführende Formeln und Funktionen Unter einer Formel versteht man eine Anweisung, um bestimmte Berechnungen in einer Zelle durchzuführen. Folgende Elemente können Bestandteil einer Formel sein:

Werte

Zellbezüge

Namen

Bereiche

Funktionen

Operatoren.

In der Praxis werden oft Kombinationen dieser Elemente verwendet.

2.1 Fehler in einem Arbeitsblatt

Wenn bei der Berechnung eines Ergebnisses in einer Formel Probleme auftreten, zeigt Excel eine Fehlermeldung an. Ange-nommen, Sie geben in einem Arbeitsblatt in der Zelle C2 die Zahl 5 und darunter irgendeinen Text ein. In die Zelle C5 tra-gen Sie eine Formel ein: =C2+C3. Diese Formel ist falsch, da ein Wert mit einem Text addiert werden soll. Nachdem Sie den Eintrag mit (¢)-Taste abgeschlossen haben, erscheint die Fehlermeldung #WERT!, und in der oberen linken Ecke der Zelle sehen Sie ein kleines grünes Dreieck . Steht der Zell-cursor auf der fehlerhaften Zelle, sehen Sie links davon ein SmartTag.

Wie Sie fehlerfrei einen Wert mit einem Text verknüpfen kön-nen, ist ab der Seite 9 beschrieben.

Fehlermeldung mit SmartTag

Fehlerüberprüfung

Wie eine Grammatikprüfung verwendet Microsoft Excel bestimmte Regeln, um Probleme in Formeln zu ermitteln. Diese Regeln garantieren aber nicht, dass die Kalkulationstabelle keine Probleme enthält! Mit ihnen lassen sich jedoch die häufigsten Fehler finden. Sie können diese Regeln einzeln aktivieren oder deaktivieren.

Die Einstellungen für die automatische Fehlerprüfung finden Sie unter Datei-Menü,

, Seite Formeln, Bereich Fehlerüberprüfung:

Excel-Optionen, Seite Formeln

6


Ist die Prüfung aktiviert, erscheint ein SmartTag mit dem Fehlersymbol, wenn die Zelle markiert wird.

Klicken Sie auf den SmartTag neben der Fehlerzelle, um die Optionen zu dieser Fehlermeldung (hier: #WERT!) anzeigen zu lassen.

Sie können die Fehlerprüfung auch manuell durchfüh-ren, indem Sie auf der Registerkarte Formeln in der Gruppe Formelüberwachung das Symbol Fehler-überprüfung wählen:

SmartTag Fehler

Manuelle Fehlerüberprüfung

Mit den Schaltflächen [Zurück] und [Weiter] können Sie die Fehler einzeln "abklappern", im Dialogfeld wird dabei eine kurze Fehlerbeschreibung angezeigt.

Wenn das Arbeitsblatt zuvor auf Probleme überprüft, aber die Probleme ignoriert wurden, sehen Sie die Anzeige der Probleme erst nach einer Zurücksetzung der ignorierten Probleme! Dazu klicken Sie in den vorher zu sehenden Excel-Op-tionen auf die Schaltfläche

.

Fehlermeldungen

Die folgenden Fehlermeldungen können in Excel angezeigt werden:

Fehlermeldung Mögliche Ursachen

#WERT! Es wird mit einem falschen Datentyp gerechnet, wie im vorher gezeig-ten Beispiel auf der Seite 6.

#DIV/0! Eine Zahl wird durch Null dividiert, z.B. =5/0

#NAME? Text in einer Formel wird nicht als Funktions- oder Bereichsname er-kannt, vielleicht wurde der Name falsch geschrieben (Registerkarte Formeln, Gruppe Definierte Namen, Schaltfläche Namen definieren).

#NV Ein Wert für eine Funktion oder Formel ist nicht verfügbar. Zwei Bei-spiele: 1. Sie kennen den Wert eines Arguments noch nicht und haben dafür

stellvertretend die Zeichenkette #NV (Nicht Verfügbar) eingegeben. 2. Das Suchkriterium in einer Verweisfunktion ist kleiner als der kleins-

te Wert im Kopf der Matrix.

7


8

Fehlermeldung Mögliche Ursachen

#BEZUG! Ein Verweis auf eine Zelle ist ungültig, weil beispielsweise die Zeile oder Spalte, in der sich diese Zelle befand, gelöscht wurde.

#ZAHL! In einer Funktion wird ein unzulässiges Argument verwendet. Ein Bei-spiel: In einer Datumsfunktion wird mit einer negativen Jahreszahl (-2010) gerechnet.

#NULL! Diese Fehlermeldung tritt unter anderem auf, wenn Sie einen Schnitt-punkt für zwei Bereiche angeben, für die kein Schnittpunkt existiert. Ein Beispiel: =SUMME(B8:B11 E8:E11). Hier fehlt zwischen den Be-reichsangaben das Semikolon ; Richtig: B8:B11;E8:E11

Wenn in einer Zelle statt einer Zahl diese Zeichen ##### erscheinen, ist dies keine Fehlermel-dung. Die Spaltenbreite für die Darstellung dieser Zahl ist zu gering (Registerkarte Start, Grup-pe Zellen, Schaltfläche Format, Befehl Spaltenbreite oder Spaltenbreite automatisch an-passen). Bei einem Verweis auf diese Zelle wird aber mit dem richtigen Wert gerechnet.

2.2 Kategorie Text

Mit den Textfunktionen lassen sich in Excel Texte bearbeiten sowie Werte zwischen Text und Zahlen konvertieren.

Steht der Funktionsname am Anfang einer Formel, muss davor unbedingt das Gleichheitszeichen = stehen!

Nachfolgend sind einige Textfunktionen und deren Verwendung aufgelistet:

Funktionsname

und Syntax Erläuterung Beispiel Ergebnis

GROSS(Text)

KLEIN(Text)

wandelt Text in Groß-

/Kleinbuchstaben um.

GROSS("Haus")

KLEIN("Haus")

HAUS

haus

LINKS

(Text;Anzahl_Zeichen)

RECHTS

(Text;Anzahl_Zeichen)

TEIL(Text;Start;

Anzahl_Zeichen)

damit können Sie eine

vorgegebene Anzahl Zei-

chen links bzw. rechts vom

Text bzw. beginnend bei

der Angabe Zeichen-Nr.

Start isolieren

LINKS("Haus";3)

RECHTS("Haus";3)

TEIL("Haus";2;2)

Hau

aus

au

Um Daten, die mit einem Trennzeichen (z.B. Komma, Leerzeichen, Strichpunkt) versehen sind, in verschiedene Spalten

aufzuteilen, können Sie auch auf der Registerkarte Daten den Befehl Text in Spalten verwenden.

LÄNGE(Text) liefert die Anzahl der Zei-

chen von Text

LÄNGE("Haus") 4


9

Funktionsname


FINDEN

(Suchtext;Text;Start)

sucht die Position von

Suchtext in Text ab der

Position von Start;

Groß-/Kleinschreibung

wichtig, Jokerzeichen sind

nicht erlaubt!

FINDEN("us";"Haus";1)

FINDEN("h";"Haus";1)

3

#WERT

(nicht

gefunden)

SUCHEN

(Suchtext; Text;Start)

sucht die Position von

Suchtext in Text ab der

Position von Start;

Groß-/Kleinschreibung

unerheblich, Joker erlaubt!

SUCHEN("a*S";"Haus"; 1)

SUCHEN("a*S";"Mastgans";1)

SUCHEN("a*S";"Mastgans";3)

2

2

6

ERSETZEN(Alter_Text;

Start;Anzahl_Zeichen;

Neuer_Text)

ersetzt auf der Basis der

Anzahl von Zeichen, die

Sie angeben, einen Teil

eines Textes durch einen

anderen Text

ERSETZEN("2009";3;2;"10")

ERSETZEN("12.5";3;1;",")

2010

12,5

GLÄTTEN(Text) löscht Leerzeichen vor und

nach einem Text und meh-

rere Leerzeichen – bis auf

eines – innerhalb eines

Textes.

GLÄTTEN(" Alfred Mustermann ")

Ergebnis: Alfred Mustermann

VERKETTEN(Text1;Text2;…)

Text1; Text2; … sind 1 bis

maximal 255 Argumente

verbindet einzelne Text-

elemente zu einer Zei-

chenkette.

VERKETTEN("'+49";" (089) ";12345) +49 (089)

12345

WERT(Text) wandelt einen als Text

eingegebene Zeichenfolge

– wenn möglich – in eine

Zahl um

WERT("1.000 €")

WERT("1234")

WERT("12 34")

1000

1234

#WERT

TEXT(Wert; Textformat) wandelt eine Zahl (Wert) in

Text mit bestimmtem For-

mat

TEXT(12,348;"0,00")

TEXT(12,3;"000,00")

12,35

012,30

Textverknüpfung

Häufig ist es bei der Arbeit mit Tabellen notwendig, die Inhalte mehrerer Zellen in einer Zelle zusammenzufassen.


Beispiel 1 Stellen Sie sich eine Adressliste vor, in der alle Informationen in gesonderten Spalten erfasst werden. Um nun den kompletten Namen – bestehend aus Titel, Vorname und Name – in einer zusätzlichen Zelle zusammen-zufassen, geben Sie ihn nicht noch einmal ein.

Für diesen Fall stellt Excel Ihnen die Funktion VERKETTEN zur Verfügung. Mit dieser können Sie bis zu 30 Textelemente mit-einander verbinden.

Funktion Verketten

Die Argumente für die Funktion können Sie entweder als Zellbezug oder als Text direkt einge-ben. Die Abbildung zeigt die Eingabe der Argumente für die Funktion VERKETTEN im zweiten Schritt des Funktions-Assistenten.

Die einzelnen Zellen getrennt durch Leerzeichen zusammensetzen

Beispiel 2 Neben der Funktion VERKETTEN gibt es noch eine andere Möglichkeit, Text aus verschiede-nen Zellen miteinander zu verknüpfen. Es gibt einen speziellen Operator, das Text-verknüpfungs-Zeichen:

&

Tragen Sie in einem leeren Arbeitsblatt in die Zelle A1 Ihren Vornamen und in A2 Ihren Nach-namen ein, z.B.:

A1: Anita

A2: Heintz

Diese beiden Namen sollen nun in die Zelle A5 übernommen werden, getrennt durch ein Leer-zeichen. In der Zelle A5 ist einzutragen:

=A1&" "&A2

Textkonstante Damit zwischen Vor- und Nachname ein Leerzeichen steht, wird es in die Formel übernommen. Dieses Leerzeichen steht fest (konstant) in der Formel. Konstante Zeichen in einer Formel sind mit Anführungszeichen " " einzurahmen.

Verknüpfung von Text und Wert In Excel können Sie auch Texte und Werte miteinander verknüpfen. Das Alter soll noch hinter dem Nachnamen stehen. Tragen Sie ein:

10


A3: 29

Erweitern Sie die Formel in der Zelle A5:

=A1&" "&A2&" "&A3&" Jahre alt"

Adressenlisten sollten unbedingt jeweils eine separate Spalte für Anrede, Titel, Vor-name, Nachname, Straße, PLZ, Ort beinhalten. Haben Sie nur eine Spalte mit dem kompletten Namen oder für PLZ und Ort, ist die Datei für viele Funktionalitäten un-brauchbar (z.B. Serienbriefe, Sortierung, verschiedene Auswertungen).

Beispiel 3 Mitunter kann es bei der Arbeit mit Excel sinnvoll sein, Werte in einer Tabelle formatiert zu-sammen mit Text zu speichern.

Stellen Sie sich folgende Situation vor: Sie erstellen auf der Grundlage einer Excel-Tabelle ein Angebot mit einer Gültigkeit von einer Woche ab dem aktuellen Datum und wollen dies auch am Ende der Tabelle schriftlich fixieren.

Eine Kombination der Funktionen TEXT und HEUTE (Seite 12) führt in Verbindung mit dem Operator & und eingefügtem Text zum gewünschten Ergebnis. In der Zelle A10 soll folgender Text erscheinen:

Dieses Angebot gilt vom &aktuelles Datum &bis zum &aktuelles Datum + 7

Die Verknüpfung von Text, &-Zeichen und den Funktionen TEXT und HEUTE sieht folgender-maßen aus:

="Dieses Angebot gilt vom " &TEXT(HEUTE();"TT.MM.") &" bis zum " &TEXT(HEUTE()+7;"TT.MM.")

In der Zelle A10 wird der Text verknüpft

Speichern und schließen Sie die Arbeitsmappe.

Textkonvertierung

Auf der Seite 8 wurde es kurz erwähnt: Sie können Daten, die mit einem Trennzeichen (z.B. Komma, Leerzeichen, Strichpunkt) versehen sind, in verschiedene Spalten aufteilen. Zum einen haben Sie die Möglichkeit beim Öffnen einer Nur-Text-Datei im Dialogfeld Öffnen (Datei-Menü) eine Konvertierung vorzunehmen, zum anderen können Sie auch Text, der bereits in einer Ar-beitsmappe in einer Spalte angeordnet ist, für eine Datenbank-Tabelle in getrennte Spalten aufgliedern.

11


2.3 Kategorie Datum & Zeit

Excel speichert alle Datums- und Zeitwerte intern als serielle Zahlen ab. Mit den Datums- und Zeitfunktionen können Sie aus einer Datumszahl (z.B. 40304) die einzelnen Datumsangaben wie Jahreszahl (2010), Monatszahl (5), Tageszahl (6) ermitteln und umgekehrt.

Datumsangaben

Die Grundeinstellungen für das Datumsformat nehmen Sie in der Systemsteuerung vor:

Windows 7: Start-Menü, Systemsteuerung, Region und Sprache, Registerkarte For-mate, Schaltfläche [Weitere_Einstellungen], Register Datum.

Windows Vista: Start-Menü, Systemsteuerung, Regions- und Sprachoptionen, Re-gisterkarte Formate, Schaltfläche [Dieses_Format_anpassen].

Windows XP: Start-Menü, Systemsteuerung, Regions- und Sprachoptionen, Registerkarte Regionale Einstellungen, Schaltfläche [Anpassen].

Nachfolgend sind einige Datumsfunktionen und deren Verwendung aufgelistet. Um das Bei-spiel-Ergebnis zu erhalten, muss die jeweilige Zelle mit dem entsprechenden Zahlenformat formatiert sein:

Funktionsname und Syntax Erläuterung Beispiel Ergebnis

HEUTE()

JETZT()

ohne Argumente!!!

liefern das Datum als fortlau-

fende Zahl, ausgehend vom

1.1.1900 00:00 Uhr(=1,0).

JETZT() liefert auch den

abgelaufenen Bruchteil des

Tages (Formatierungsmög-

lichkeiten beachten)

am Donnerstag, 06.05.2010

13:10 Uhr :

HEUTE()

incl. Bruchteil d. Tages:

JETZT()

40304

40304,5486

JAHR(Zahl)

MONAT(Zahl)

TAG(Zahl)

STUNDE(Zahl)

MINUTE(Zahl)

SEKUNDE(Zahl)

ermitteln die jeweiligen Wer-

te als ganze Zahlen aus der

fortlaufenden Datumszahl

am Donnerstag, 06.05.2010

TAG(JETZT())

6

WOCHENTAG(Zahl; Art) ermittelt aus einer Datums-

zahl eine Zahl von 1 (Sonn-

tag) bis 7 (Samstag), die den

Wochentag angibt.

WOCHENTAG("06.05.2007")

WOCHENTAG("06.05.2010")

ergibt 1

(Sonntag!)

ergibt 5

(Donnerstag)

DATUM(Jahr; Monat; Tag) ermittelt die fortlaufende

Datumszahl aus berechneten

Werten für Jahr, Monat und

Tag.

A5=2010; B5=5; C5=6

DATUM(A5+3;B5+1;C5-4)

formatiert: TT.MM.JJJJ

41427

02.06.2013

12


13


NETTOARBEITSTAGE

(Ausgangsdatum; Enddatum;

Freie Tage)

ermittelt die Anzahl der Ar-

beitstage in einem Zeitinter-

vall. Wochenenden (Sams-

tag und Sonntag) werden

automatisch nicht als Ar-

beitstage gerechnet. Als

Freie Tage sind Feierta-

ge/Ferien anzugeben, meh-

rere freie Tage in {} zu set-

zen.

NETTOARBEITSTAGE

("08.11.2010";"21.11.2010";

{"10.11.2010";“11.11.2010“})

8

2.4 Add-Ins

Add-Ins sind Zusatzprogramme. Der Solver ist ein Beispiel für ein Add-In. Hiermit kann man in Excel Befehle und Funktionen hinzufügen. Verwaltet werden die Zusatzprogramme in den Ex-cel-Optionen.

Excel-Optionen, Seite Add-Ins

Einige Datumsfunktionen (z.B. NETTOARBEITSTAGE, KALENDERWOCHE) gehö-ren zu den Analyse-Funktionen. Ist die gewünschte Funktion im Funktions-Assistenten nicht verfügbar, wählen Sie den Weg: Datei-Menü, , Seite Add-Ins. Klicken Sie auf [Gehe_zu...]. Im rechts stehenden Dialogfeld sehen Sie eine Liste aller ver-fügbaren Add-Ins. Aktivieren Sie die Analyse-Funktionen. Verlassen Sie das Dialogfeld Add-Ins über [_OK_].

Analyse-Funktionen aktivieren


Aktivierte Add-Ins erkennen Sie an dem Häkchen im Kontrollkästchen. Deaktivierte Add-Ins werden lediglich aus dem Arbeitsspeicher, nicht aber von der Festplatte des Computers entfernt.

2.5 Kategorie Math. & Trigonom.

In dieser Kategorie finden Sie eine Sammlung von mathematischen Funktionen. Die Funktion SUMME gehört auch zu dieser "Familie". Nachfolgend sind einige mathematische und trigono-metrische Funktionen und deren Verwendung aufgelistet:

Funktionsname


ABS(Zahl) ermittelt den Absolutwert

einer Zahl

(Löscht Vorzeichen)

ABS(5)

ABS(- 5)

5

5

PI()

ohne Argumente!!!

liefert den Wert der Kreis-

zahl PI.

PI() 3,1415926

REST(Zahl; Divisor) liefert den Rest (zum

ganzzahligen Ergebnis)

der Division Zahl/Divisor

REST(10; 4,2)

(10 / 4,2 = 2 Rest 1,6)

1,6

RUNDEN(Zahl;

Anzahl_Stellen)

rundet eine Zahl auf eine

bestimmte Anzahl an De-

zimalstellen mathema-

tisch.

RUNDEN(31,446;2)

RUNDEN(31,443;2)

31,45

31,44

AUFRUNDEN(Zahl;

Anzahl_Stellen)

rundet die Zahl auf eine


zimalstellen auf.

AUFRUNDEN(31,446;2)

AUFRUNDEN(31,443;2)

31,45

31,45

ABRUNDEN(Zahl;

Anzahl_Stellen)

rundet die Zahl auf eine


zimalstellen ab.

ABRUNDEN(31,446;2)

ABRUNDEN(31,443;2)

31,44

31,44

GANZZAHL(Zahl) rundet eine Zahl auf die

nächstkleinere ganze Zahl

ab.

GANZZAHL(31,446)

GANZZAHL(-31,446)

31

-32

KÜRZEN(Zahl;Anzahl_Stellen) schneidet die Kommastel-

len der Zahl ab und liefert

als Ergebnis eine ganze

Zahl.

KÜRZEN(8,9)

KÜRZEN(-8,9)

KÜRZEN(5,49;1)

KÜRZEN(PI())

8

-8

5,4

3

OBERGRENZE(Zahl; Schritt)

UNTERGRENZE(Zahl; Schritt)

rundet eine Zahl in Sprün-

gen von Schritt auf bzw.

ab.

OBERGRENZE(5,2; 0,5)

UNTERGRENZE(5,2;0,5)

5,5

5,0

SUMMEWENN(PrüfBereich;

Kriterium;Summe_Bereich)

addiert Zahlen aus Sum-

me_Bereich, wenn der

Wert im Prüfbereich mit

Kriterium übereinstimmt.

Beispiel Seite Fehler! Textmarke nicht

definiert.

14


15

Funktionsname


WURZEL(Zahl) liefert die Quadratwurzel

von Zahl.

WURZEL(81) 9

2.6 Kategorie Statistik

Statistische Funktionen ermöglichen Ihnen aus Bereichen bestimmte Informationen herauszufil-tern, z.B. die größte bzw. kleinste Zahl oder einen Durchschnittswert anzeigen zu lassen.

Nachfolgend sind einige statistische Funktionen und deren Verwendung aufgelistet:

Funktionsname


ANZAHL(Wert1;Wert2;…)

Wert1; Wert2; … sind 1 bis


ermittelt, wie viele Zellen

im Bereich Zahlenwerte

enthalten sind.

ANZAHL2(Wert1;Wert2;…)

Wert1; Wert2; … sind 1 bis


ermittelt, wie viele Zellen

im Bereich Zahlen oder

Text enthalten sind.

ANZAHLLEEREZELLEN

(Bereich)

zählt die leeren Zellen in

einem Bereich.

Achtung – es werden auch

Zellen gezählt, in denen

Formeln stehen, die die

leere Zeichenfolge ("")

liefern. Zellen, die Nullwer-

te enthalten, werden nicht

gezählt.

ZÄHLENWENN

(PrüfBereich;

Suchkriterium)

zählt die nichtleeren Zellen

eines Bereichs, deren

Inhalte mit dem Suchkrite-

rium übereinstimmen.

A3:A6 enthält die Begriffe "Äpfel",

"Orangen", "Pfirsiche" und "Äpfel"

ZÄHLENWENN(A3:A6;

"Äpfel")

2

MAX(Zahl1;Zahl2;…)

MIN(Zahl1;Zahl2;…)

MITTELWERT(Zahl1;Zahl2;

…)

Zahl1; Zahl2; … sind 1 bis


ermittelt den Maximalwert,

den Minimalwert, den Mit-

telwert aus den angegebe-

nen Zahlenwerten.

(Gilt nicht für Text-Werte!)

2.7 Kategorie Logik

Mit Logikfunktionen können Sie Vergleiche, Verknüpfungen oder Entscheidungen ausführen.


Nachfolgend sind fünf logische Funktionen und deren Verwendung aufgelistet:

Funktionsname


WENN(Bedingung;

Dann_Wert; Sonst_Wert)

trägt in die Zelle, in der

die Funktion steht, den

Dann-Wert ein, wenn

die Bedingung zutrifft,

andernfalls den Sonst-

Wert.

Provisionsberechnung:

(in F9 steht der Umsatz)

WENN( F9>1000;10%;5%)

UND(Bedingung1;

Bedingung2;…)

Wahrheitswert1; Wahrheits-

wert2; … sind 1 bis maximal

255 Bedingungen

ergibt WAHR, wenn

beide Bedingungen

zutreffen.

F8=BetrZugehörigk;F9=Umsatz

WENN(UND(F8>5;F9>1000);10%;5%)

Als

Prü

fung

(B

edin

gung

) in

WE

NN

()

auch

:

ODER(Bedingung1;

Bedingung2;…)

Wahrheitswert1; Wahrheits-

wert2; … sind 1 bis maximal

255 Bedingungen

ergibt WAHR, wenn

mindestens eine Be-

dingung zutrifft.

FALSCH()

WAHR()

liefert den Wahrheits-

wert FALSCH bzw.

WAHR.

Einsatzmöglichkeiten

Die WENN-Funktion wird häufig in der Praxis eingesetzt. Sie können mit die-ser Funktion die unterschiedlichsten Sachverhalte testen und in Abhängig-keit vom Ergebnis verschiedene Be-rechnungen durchführen, Texte ausge-ben oder auf andere Zellinhalte verwei-sen.

Stellen Sie sich eine Projektliste vor, in der alle aktuellen Projekte mit dem jeweils dazugehörigen einzuhaltenden Enddatum aufgeführt sind.

Nun soll bei Projekten, die in weniger als 60 Tagen fällig sind, in einer zusätz-lichen Spalte Priorität der Text hoch erscheinen.

Priorität bei Projekten eintragen

16


In der Spalte Priorität setzen Sie je-weils eine WENN-Funktion ein, die den entsprechenden Termin aus Spalte B mit dem Datum aus Zelle B3 vergleicht.

Für Zelle C6 ist diese WENN-Funktion folgendermaßen aufgebaut:

=WENN(B6-$B$3<60;"hoch";"")

In Worten besagt diese Funktion: Wenn das Datum aus Zelle B6 minus dem Datum aus Zelle B3 kleiner als 60 Tage ist, soll der Text "hoch" ausgegeben werden. Ist dies nicht der Fall, soll gar nichts ("") ausgegeben werden.

WENN-Funktion

Über den absoluten Bezug (die beiden Dollarzeichen) innerhalb des Bezugs B3 bereiten Sie die Funktion für das Kopieren mit AutoAusfüllen in die Zellen B7 bis B11 vor.

2.8 Kategorie Matrix

Mit Matrixfunktionen greifen Sie auf größere Bereiche zu und durchsuchen diese nach Infor-mationen. Nachfolgend sind einige Matrixfunktionen und deren Verwendung aufgelistet:


SVERWEIS(Suchwert;

Matrix;Spaltenindex;

Bereich_Verweis)

Matrix = Tabelle ohne Spalten-

überschriften

sucht in der am weitesten

links gelegenen Spalte

einer Tabelle nach einem

Wert(e-Bereich) und lie-

fert einen Wert aus der

gleichen Zeile aus einer

von Ihnen angegebenen

Spalte in der Tabelle.

Einsatzmöglichkeiten Seite 24

WVERWEIS(Suchwert;

Matrix;Zeilenindex;

Bereich_Verweis)

Matrix = Tabelle ohne Spalten-

überschriften

sucht in der obersten

Zeile einer Tabelle nach

einem Wert(e-Bereich)

und liefert einen Wert aus

der gleichen Spalte aus

einer von Ihnen angege-

benen Zeile in der Tabel-

le.

INDEX(Matrix; Zeile;

Spalte)n

liefert den Wert, der in

einer beliebigen Matrix in

Zeile; Spalte steht.

Häufig ermittelt man Zeile

und Spalte mit der Funkti-

on VERGLEICH

17


18


VERGLEICH(Suchwert;

Suchmatrix; Vergleichstyp)

Sucht innerhalb einer

1spaltigen oder 1zeiligen

Suchmatrix (wie bei

SVERWEIS i. d. linken

Spalte) nach Suchkriteri-

um und liefert dessen

PositionsNr. Vergleichstyp

wie bei SVERWEIS

HÄUFIGKEIT

(Daten;Klassen)

gruppiert die Daten ent-

sprechend der Klassen-

einteilung und liefert die

Anzahl in jeder Klasse als

einspaltige Matrix.

Die Funktion muss als

Matrixformel eingegeben

werden (mit (Strg)+

(Shift)+ (Return)-

Taste).

siehe Einsatzmöglichkeiten

SPALTE(Bezug)

ZEILE(Bezug)

liefert die Spaltennummer

(statt Spalte "E" die

Zahl 5!) bzw. Zeilennum-

mer von Bezug.

Einsatzmöglichkeiten

Die Funktion HÄUFIGKEIT können Sie z.B. verwenden, um aus den Ergebnissen einer Prüfung herauszulesen, wie viele Prüflinge bis 20 Punkte, bis 40 Punkte usw. erreicht haben.

Besonders beachten müssen Sie bei dieser Funktion, dass der Ergebnisbereich und der Klas-senbereich unterschiedlich groß sind. Im Klassenbereich (im nachfolgenden Bild in der Spalte E) legen Sie die Intervallgrenzen fest, nach denen Sie die Zahlenwerte gruppieren möchten, und im Ergebnisbereich (im nachfolgenden Bild in der Spalte F) markieren Sie eine Zelle mehr, damit Sie als letztes Ergebnis die Zahlenwerte erhalten, die größer als das höchste Intervall sind (hier 80).


Nachfolgend sehen Sie ein kleines Beispiel einer solchen Häufigkeitstabelle:

Häufigkeitstabelle

Wichtig ist, dass Sie das Dialogfeld nicht durch Klicken auf [_OK_], sondern mit der Tastenkombination

(Strg)+(ª)+(¢) (Strg+Shift+Return) abschließen!!!

Sonst kommen Sie nicht zu dem gewünschten Ergebnis.

Nach dem Abschluss sind an der Formel die geschweiften Klammern ungewöhnlich:

Sobald Sie versuchen, eine einzelne dieser Zellen zu löschen oder zu bearbeiten, erhalten Sie folgende Fehlermeldung:

Fehlermeldung

Sie weist darauf hin, dass die Zellen nur gemeinsam bearbeitet werden können. Möchten Sie die Funktion HÄUFIGKEIT wieder ändern oder entfernen, müssen Sie deshalb zuerst alle Zel-len des Bereichs markieren, bevor Sie die Änderungen eingeben oder die Taste [Entf] drücken.

2.9 Verschachtelte Funktionen

In bestimmten Fällen kann es erforderlich sein, eine Funktion als eines der Argumente einer anderen Funktion zu verwenden. Zum Beispiel verwendet die nachfolgende Formel eine ver-schachtelte Funktion MITTELWERT und vergleicht deren Ergebnis mit dem Wert 50.

19


Verschachtelte Funktion

Grenzen der Verschachtelungsebene

Eine Formel kann verschachtelte Funktionen in bis zu 64 Ebenen enthalten. Wenn Funktion B als Argument in Funktion A verwendet wird, ist Funktion B eine Funktion zweiter Ebene. Bei-spielsweise sind die Funktionen MITTELWERT und SUMME in o. a. Abbildung beide Funktio-nen der zweiten Ebene, weil sie Argumente der WENN-Funktion darstellen. Eine verschachtelte Funktion innerhalb der Funktion MITTELWERT wäre dann eine Funktion dritter Ebene usw.

Funktionen verschachteln mit dem Assistenten

Von der folgenden Umsatzliste soll der gerunde-te Mittelwert errechnet werden. Dazu wird zuerst die Funktion RUNDEN benötigt. Stellen Sie den Zellcursor in die Zelle B9, und rufen Sie einen der nachfolgenden Befehle auf:

Symbol Funktion einfügen in der Bearbei-tungsleiste oder

(ª_)+(F3) oder über

das Symbol Funktion einfügen im Menü-band auf der Registerkarte Formeln in der Gruppe Funktionsbibliothek.

Umsatzliste

Die Funktion RUNDEN auswählen

20


Klicken Sie in das Feld Zahl.

Klicken Sie in das Feld Zahl

Öffnen Sie in der Tabelle oben links am Anfang der Bearbeitungs-leiste das Namenfeld (Funktionsfeld) , und wählen Sie die Funkti-on MITTELWERT. Wird die Funktion, die Sie benötigen, in der Liste nicht angezeigt, klicken Sie auf die Zeile Weitere Funktionen…

Geben Sie das Argument (B2:B8) für die gewählte Funktion ein, oder markieren Sie den Bereich mit der Maus: .

Argument eingeben, aber nicht auf [_OK_] klicken!

Liste Funktionen

im Namenfeld

Um wieder zu der übergeordneten Funktion zurückzukehren, klicken Sie nicht auf [_OK_], son-dern klicken Sie in der Bearbeitungsleiste auf den Namen der übergeordneten Funktion RUNDEN.

Tragen Sie anschließend noch die Zahl 0 für die Anzahl der Dezimalstellen ein, und schließen Sie dann erst das Dialogfeld über [_OK_]!

21


Argument für die übergeordnete Funktion eingeben und [_OK_]

2.10 Matrizen

Eine besondere Formelart ist die so genannte Matrixformel. Wenn in benachbarten Zellen For-meln mit dem gleichen Inhalt eingesetzt werden, sollten Sie mit Matrixformeln arbeiten. Sie erleichtern Ihnen die Arbeit, wenn bestimmte Formeln wiederholt werden.

Der Umgang mit einer Matrixformel

Der Umgang mit einer Matrixformel wird am leichtesten durch ein kleines Beispiel verständlich. Geben Sie in ein leeres Tabellenarbeitsblatt die folgenden Daten ein. Formatieren Sie die Zah-len in der B-Spalte als Euro-Währung, und speichern Sie die Mappe unter dem Namen Matrix ab:

Die Daten und der markierte Bereich C2:C8

Mit der Tastenkombination (Strg)+(F1) oder mit einem Maus-Doppelklick auf einen Registernamen (Start, Einfügen, Seitenlayout…) verkleinern und vergrößern Sie das Menüband, wie es bei diesem und dem nachfolgenden Bild zu sehen ist.

Zur Ermittlung des Gesamtpreises könnten Sie wie gewohnt in die Zelle C2 die entsprechende Formel eintragen und diese anschließend in die Zellen C3 bis C8 kopieren.

Mit Hilfe der Matrixformel wird aber anders vorgegangen. Änderungen müssen in einer Matrix-formel nur einmal eingegeben werden. Dies erspart in umfangreichen Tabellen lästige Kopier-arbeiten:

1. Markieren Sie die Zellen C2 bis C8.

2. Tragen Sie die Formel =A2:A8*B2:B8 ein. Drücken Sie nicht die (¢)-Taste!

22


3. Beenden Sie die Eingabe mit der Tastenkombination (Strg)+(ª)+(¢).

Wichtig ist, dass Sie die Eingabe tatsächlich mit der Tastenkombination (Strg)+(ª)+(¢) abschließen, sonst kommen Sie nicht zu dem gewünschten Er-gebnis.

In den Zellen C2 bis C8 befindet sich ein und dieselbe Formel. Die Formel in der Bearbeitungs-zeile ist in geschweifte Klammern eingeschlossen:

{=A2:A8*B2:B8}

Die Ergebnisse

Geschweifte Klammern sind eine Besonderheit der Matrixformeln.

Wenn Sie die Formeln im Arbeitsblatt sehen möchten, rufen Sie die im Datei-Menü auf. Aktivieren Sie auf der Seite Erweitert das Kontrollkästchen:

Anstelle der berechneten Werte Formeln in Zellen anzeigen.

Excel-Optionen, Seite Erweitert

Klicken Sie auf [_OK_]. Nun werden im Tabellenarbeitsblatt anstatt der Werte die Formeln prä-sentiert:

23


Die Formeln

Besonderheiten der Matrixformeln

Folgende Besonderheit ist beim Umgang mit Matrixformeln zu beachten:

In einer Matrix, die mehrere Zeilen umfasst, kann man weder zusätzliche Zellen einfügen noch einzelne Zellen löschen. Wenn man dieses missachtet, blendet Excel den folgenden Hinweis ein:

Der Hinweis

Matrixformeln können selbstverständlich nachträglich geändert werden. Dazu ist jedoch eine besondere Vorgehensweise erforderlich:

1. Wählen Sie eine Zelle aus, die die Matrixformel enthält.

2. Tragen Sie die Änderung ein.

3. Drücken Sie die Tastenkombination (Strg)+(ª)+(¢). Sie erhalten ein neues Ergebnis.

2.11 Die Arbeit mit einer Verweis-Funktion

Eine häufig verwendete Matrixfunktion ist der so genannte SVERWEIS.

Die Problemstellung

Sie sind als Sachbearbeiter/in in der Personalabteilung tätig. Die Geschäftsführung wünscht von Ihnen eine Aufstellung der Resturlaubstage aller Mitarbeiter. Nach Absprache mit dem Be-triebsrat sollen alle Resturlaubstage, die über eine Anzahl von 10 Tagen hinausgehen, mit ei-nem Zuschlag von 25% vergütet werden. Pro Tag werden 8 Stunden abgerechnet. Folgende Angaben werden benötigt:

Name des Mitarbeiters

Anzahl der Resturlaubstage

Stundensatz des Mitarbeiters.

24


Die Vorbereitung

In einem Arbeitsblatt liegen Ihnen folgende Angaben vor. Aus Vereinfachungsgründen wird nur ein Auszug aus den Personallisten genommen:

Die vorliegenden Daten

1. Öffnen Sie die Datei Verweis Rohdaten.xlsx, oder geben Sie die Daten in ein leeres Ta-bellenblatt ein.

2. Speichern Sie die Mappe unter dem Namen Verweis ab.

3. Sehr hilfreich sind Namen für das Tabellenarbeitsblatt und Bereichsnamen. Das Tabellen-arbeitsblatt soll den Namen Mitarbeiterdaten erhalten.

4. Führen Sie zu diesem Zweck einen Doppelklick auf das Blattregister der Tabelle1 aus. Der Name Tabelle1 ist markiert.

Recorder-Icons (links) und Blattregister

5. Tippen Sie den Begriff Mitarbeiterdaten ein, und schließen Sie die Eingabe mit der (¢)-Taste ab.

6. Markieren Sie den Bereich A1 bis F6.

7. Klicken Sie mit der rechten Maustaste in den mar-kierten Bereich, und wählen Sie im Kontextmenü den Befehl Namen definieren. Oder klicken Sie auf der Registerkarte Formeln in der Gruppe De-finierte Namen auf das Symbol Namen definie-ren. Das rechts stehende Dialogfeld wird geöffnet.

8. Tragen Sie unter Name in der Arbeitsmappe das Wort Personaldaten ein.

9. Klicken Sie auf [_OK_].

Namen definieren

Die Verweis-Funktion einsetzen

Der Erstattungsbetrag soll mit Hilfe der Funktion SVERWEIS ermittelt werden.

1. Wechseln Sie in ein leeres Tabellenarbeitsblatt Ihrer Arbeitsmappe, indem Sie unten links auf das Blattregister Tabelle2 klicken.

2. Dorthin kopieren Sie die folgenden Daten oder tragen sie neu ein:

25


Die Daten auf der Tabelle2

Nun sollen die Namen der Mitarbeiter mit Hilfe der Funktion SVERWEIS neben den entspre-chenden Personal-Nummern erscheinen:

3. Setzen Sie den Zellcursor in die Zelle B2.

4. Öffnen Sie das Dialogfeld Funktion einfügen, beispielsweise über das rechts stehende Symbol auf der Registerkarte Formeln in der Gruppe Funktionsbibliothek.

5. In dem Dialogfeld können Sie direkt im

Feld Funktion suchen den Begriff

sverweis

eingeben und rechts davon auf [_OK_] kli-cken.

Oder Sie wählen über den Listenpfeil die Kategorie Matrix aus und markieren darunter die Funktion SVERWEIS.

6. Klicken Sie unten auf [_OK_].

Funktion einfügen

7. Sie sehen die Formelpalette SVERWEIS:

Formelpalette SVERWEIS

Die Angabe der fett geschriebenen Argumente ist zwingend notwendig:

Suchkriterium Matrix Spaltenindex.

8. Das Argument Suchkriterium ist der Wert, nach dem in der ersten Spalte der Matrix zu suchen ist. Für das vorliegende Beispiel enthält die Zelle A2 das erste Suchkriterium, näm-lich die Personalnummer.

26


9. Setzen Sie nun den Cursor in das Feld für das Argument Matrix, z.B. über die (ÿ) Tab-taste. Darunter ist die Verweistabelle zu verstehen, aus der die Daten entnommen werden. Da Sie den Matrixbereich benannt haben, geben Sie den Namen Personaldaten in das dafür vorgesehene Feld ein.

10. Als Nächstes ist der Spaltenindex gefragt. Damit ist die Spalte der Matrix gemeint, aus der Excel die Informationen holen soll. Der gefragte Name steht in der zweiten Spalte der Matrix. Aus diesem Grunde ist die Eingabe der Ziffer 2 erforderlich:

Die Eingaben

Das Argument Bereich_Verweis ist optional, und deshalb ist der Name nicht in fetter Schrift angegeben. Es wird hier auch nicht benötigt.

11. Verlassen Sie das Dialogfeld über die Schaltfläche [_OK_].

Die Formel lautet: =SVERWEIS(A2;Personaldaten;2)

12. Kopieren Sie die Eingaben in den Bereich B3:B6.

13. Setzen Sie den Zellcursor in die Zelle C2.

14. Dort wird folgende Formel benötigt:

=SVERWEIS(A2;Personaldaten;6)

15. Kopieren Sie die Formel in den Bereich C3:C6.

Der Verweis auf den Resturlaub

16. Formatieren Sie die Zellen D2 bis D6 mit dem Zahlenformat Währung in € mit 2 Dezimal-stellen (Kontextmenü, Befehl Zellen formatieren, Kategorie Währung).

27


Verschachtelte Funktionen

Als Nächstes ermitteln Sie den Auszahlungsbetrag. Dieser errechnet sich durch Multiplikation des Stundensatzes mit den Resturlaubstagen, die mehr als 10 betragen und einer Anzahl von 8 Stunden täglich. Außerdem soll ein Zuschlag von 25% gezahlt werden.

Diese Rechenoperation ist nur mit Hilfe einer verschachtelten Funktion zu lösen, eingebettet in eine WENN-Bedingung:

Um rechnen zu können, muss mit Hilfe der Funktion SVERWEIS der Stundenlohn aus dem Arbeitsblatt Mitarbeiterdaten geholt werden, also SVERWEIS(A2;Personaldaten;4).

Der Stundenlohn ist mit der Anzahl der Resturlaubstage, die mehr als 10 betragen, zu multiplizieren. Die Anzahl der Tage ermitteln Sie mit der Formel C2-10.

Da der Arbeitstag in dem aktuellen Beispiel 8 Stunden betragen soll, ist zusätzlich mit 8 zu multiplizieren.

Da ein Zuschlag von 25% gezahlt wird, ist der Betrag abschließend noch mit einem Faktor von 1,25 malzunehmen.

Der Auszahlungsbetrag ist nur dann zu errechnen, wenn die Anzahl der Urlaubstage über 10 liegt.

Dazu ist die WENN-Funktion erforderlich.

Wenn der Wert der Spalte C größer als zehn ist, wird gerechnet, ansonsten bleibt die Zelle leer. Da auch dieses Argument eingetragen werden muss, stehen hier nur zwei Anführungszeichen ””. In der Fachsprache wird dieser Eintrag als Leerstring bezeichnet.

Die Formel lautet:

=WENN(C2>10;SVERWEIS(A2;Personaldaten;4)*(C2-10)*8*1,25;””)

Kopieren Sie diese Formel in die übrigen Zellen der Spalte D.

Sie erhalten als Ergebnisse bei Meier einen Auszahlungsbetrag von 515,40 € und bei Müller von 1288,50 €.

2.12 Zellschutz einrichten

Da es sich um recht komplizierte Formeln handelt, sollten diese vorsichtshalber vor dem Über-schreiben geschützt werden. Dazu gehen Sie folgendermaßen vor:

1. Markieren Sie im aktuellen Blatt Tabelle2 den Bereich A2 bis A6. Später sollen in Ihrem Arbeitsblatt nur noch diese Zellen überschreibbar sein.

2. Öffnen Sie das Kontextmenü, und wählen Sie Zellen formatieren....

3. Klicken Sie die Registerseite Schutz an.

4. Mit Ausnahme der Zellen A2 bis A6 sollen keine Änderungen in Ihrem Arbeitsblatt mehr eingegeben werden können. Deak-tivieren Sie deshalb das Kontrollkästchen

Gesperrt, und klicken Sie auf [_OK_]. Die Registerseite Schutz

28


Der Schutz des Blattes bezieht sich auch auf die Formatierungen und Anordnung der Zellen. Der Zellschutz wird jedoch nur wirksam, wenn Sie den Blattschutz einrichten:

5. Klicken Sie oben im Menüband

auf der Registerkarte Überprüfen in der Befehlsgruppe Änderun-gen auf die Schaltfläche Blatt schützen.

6. Das rechts stehende Dialogfeld wird geöffnet. Tragen Sie ein Kennwort Ihrer Wahl ein. Die Zeichen sind aber nur als Punkte zu sehen.


Dialogfeld Blatt schützen

Wenn Sie anstelle des Befehls Blatt schützen das Symbol Arbeitsmappe schützen anklicken, bezieht sich der Zellschutz auf sämtliche Blätter der aktiven Mappe.

8. Excel blendet das Dialogfeld Kennwort bestä-

tigen ein. Dort müssen Sie das Passwort er-neut eintragen.


10. Speichern Sie die Datei noch einmal ab, da die Daten später noch einmal benötigt werden.

Bestätigen Sie das Kennwort

Der Schutz ist für alle Zellen, bis auf den Bereich A2 bis A6, fertig eingerichtet. Wenn Sie den-noch versuchen, gesperrte Zellen zu überschreiben, wird dies von Excel nicht akzeptiert. Sie erhalten den folgenden Hinweis:

Hinweis

Klicken Sie in einem solchen Fall auf [_OK_], um den Hinweis zu schließen.

2.13 Dokumentschutz aufheben

Um den Dokumentschutz wieder aufzuheben, gehen Sie folgendermaßen vor:

1. Klicken Sie auf der Registerkarte Überprüfen in der Gruppe Änderungen auf das Symbol Blattschutz auf-heben.

2. In dem rechts stehenden Dialogfeld tragen Sie das Kennwort ein.

Passwort eingeben

3. Klicken Sie auf [_OK_]. Anschließend können Sie Ihr Arbeitsblatt wie gewohnt bearbeiten.

29


2.14 Benutzerdefinierte Zahlenformate

Excel bietet Ihnen eine Fülle von Zahlenformaten in den verschiedensten Kategorien an. Sie können aber auch eigene Zahlenformate erstellen bzw. die vorhandenen ergänzen.

Benutzerdefinierte Zahlenformate:

werden in die Liste der Zahlenformate übernommen und können wie jedes andere Format zukünftig über das Fenster Zellen formatieren weiteren Zellen und/oder Bereichen zugewiesen werden,

werden mit der aktuellen Arbeitsmappe gespeichert und stehen somit nicht in anderen Arbeitsmappen zur Verfügung.

Die individuellen Zahlenformate ordnet Excel in die Kategorie Benutzerdefiniert ein:

Zellen formatieren

Dort wird die Zeile Typ eingeblendet, in der Sie die entsprechenden Formatierungszeichen anhand der von Excel verwendeten Formatcodes eintragen können.

Nachfolgend werden einige dieser Codes und deren Bedeutung kurz vorgestellt:

Formatcode Erläuterung

0 oder 0,00 Die Null dient als Platzhalter für Ziffern, die immer anzuzeigen sind. Durch ein Kom-

ma wird die Lage des Dezimalkommas und danach die Anzahl der Dezimalstellen

festgelegt. Werte, deren Nachkommastellen nicht vollständig sind, werden in der An-

zeige gerundet.

# oder #.### Das #-Zeichen dient ebenfalls als Platzhalter für Ziffern aber nur für die Anordnung

signifikanter Stellen. Sie können das #-Zeichen mit einem Punkt als Platzhalter für

das 1000er-Trennzeichen kombinieren (z.B. #.##0,00)

? Auch das Fragezeichen ist ein Platzhalter für Ziffern und fügt für nicht signifikante

Nullen Leerzeichen ein, um Dezimalzahlen am Dezimalkomma auszurichten, wenn

die Formatierung mit einer Festbreitenschrift erfolgt (beispielsweise Courier New).

Das ?-Zeichen wird häufig zur Darstellung von Brüchen benutzt.

30


31


[Schwarz]

[Blau]

[Zyan]

[Grün]

[Magenta]

[Rot]

[Weiß]

[Gelb]

Farben

Die Farbe für einen Abschnitt des Formats wird eingestellt, indem Sie den Namen

einer der acht Farben in eckige Klammern eingeben. Das folgende Format zeigt posi-

tive Zahlen in Blau, negative Zahlen in Rot, eine Null in grün und Text in der Farbe

Zyan an (nähere Erläuterungen nachfolgend)

[BLAU]#.##0,00;[ROT]-#.##0,00;[GRÜN]0;[ZYAN]@

Bedingungen

Um Zahlenformate zu definieren, die nur dann angewendet werden, wenn eine Zahl

eine angegebene Bedingung erfüllt, schließen Sie die Bedingung in eckige Klammern

ein. Die Bedingung setzt sich aus einem Vergleichsoperator und einem Wert zusam-

men. Das folgende Format zeigt z.B. Zahlen kleiner oder gleich 100 rot formatiert und

Zahlen größer 100 blau formatiert an.

[Rot][<=100];[Blau][>100];Format für den Rest

M Monate als 1-12

MM Monate als 01-12

MMM Monate als Jan-Dez

MMMM Monate als Januar-Dezember

T Tage als 1-31

TT Tage als 01-31

TTT Tage als So-Sa

TTTT Tage als Sonntag-Samstag

JJ Jahre zweistellig als 00-99

JJJJ Jahre vierstellig als 1900-9999

h Stunden als 0-23

hh Stunden als 00-23

m Minuten als 0-59

mm Minuten als 00-59

s Sekunden als 0-59

ss Sekunden als 00-59

h AM/PM Stunden als 4 AM, im 12-Stunden-Format (AM oder A von Mitternacht bis Mittag, PM

oder P von Mittag bis Mitternacht)

h:mm AM/PM Uhrzeit als 4:36 PM

[hh]:mm Vergangene Zeit in Stunden und über 24 Stunden, z.B. 25:02

[mm]:ss Vergangene Zeit in Minuten und über 60 Minuten, z.B. 63:46

" " Hinzufügen von Zeichen

Um Text zusammen mit Zahlen in einer Zelle anzuzeigen, setzen Sie den Text zwi-

schen Anführungszeichen (" "). Fügen Sie den Text in den entsprechenden Abschnitt

der Formatcodes ein. Geben Sie z.B. das Format

#.##0,00 €" Überschuss";-#.##0,00 €" Fehlbetrag"

ein, um einen negativen Betrag als "-125,74 € Fehlbetrag" anzuzeigen.


32


Zur Anzeige von Leerzeichen und der folgenden Sonderzeichen $ - + / ( ) : ! ^ & ' (ein-

faches Anführungszeichen links) ' (einfaches Anführungszeichen rechts) ~ { } = < >

sind keine Anführungszeichen erforderlich.

@ Einschließen eines Textformatabschnitts

Ein Textformatabschnitt ist immer der letzte Abschnitt im Zahlenformat. Fügen Sie ein

@-Zeichen an der Stelle in den Abschnitt ein, an der in die Zelle eingegebener Text

angezeigt werden soll. Fehlt das @-Zeichen im Textabschnitt, wird der eingegebene

Text nicht angezeigt. Sollen bestimmte Zeichen immer mit dem eingegebenen Text

angezeigt werden, setzen Sie den zusätzlichen Text zwischen Anführungszeichen ("

"), z.B. "Bruttoeinnahmen für " @.

@*. oder

0,00*- oder

*-0 usw.

Sich wiederholende Zeichen

Soll ein Zeichen im Format zum Ausfüllen der Spalte wiederholt werden, schließen

Sie ein Sternchen (*) in das Zahlenformat ein. Geben Sie z.B. @*. ein, um die Zelle

mit Text "auszupunktieren". Zahlen stehen besser rechts in den Zellen, *-0 füllt bei-

spielsweise vor der Zahl mit Bindestrichen aus.

Beispiele

Eingabe verwendetes

Zahlenformat Anzeige

1234,59 #.##0,00 1.234,59

1234,59 ####,# 1234,6

8,9 #,000 8,900

,631 0,# 0,6

12 #0,0 12,0

1234,568 #,0# 1234,57

5,25 # ???/??? 5 ¼

5,3 # ???/??? 5 3/10

01.02 TT.MM.JJJJ 01.02.201x (aktuelles Jahr!)

01.02 T. MMMM JJJJ 1. Februar 201x (aktuelles Jahr!)

01.02 TTTT, TT.MM.JJJJ Donnerstag, 01.02.201x

(aktuelles Jahr!)

0,354 hh:mm 08:29

32,354 TT.MM.JJJJ hh:mm 01.02.1900 08:29

10,5 0,00" m² " 10,50 m²

1234,67 #.##0,00" km " 1.234,67 km

1,234 [hh]:mm" Std:Min " 29:36 Std:Min


Benutzerdefiniertes Zahlenformat erstellen

1. Markieren Sie die Zelle(n), für die Sie ein eigenes Format definieren möchten.

2. Wählen Sie im Kontextmenü den Befehl Zellen formatieren..., Registerseite Zahlen.

3. Klicken Sie in der Liste der Kategorien auf Benutzerdefiniert.

4. Markieren Sie in der großen Liste das Zahlenformat, das dem ge-wünschten am ähnlichsten ist.

5. Ergänzen bzw. ändern Sie das an-gezeigte Zahlenformat in dem Feld Typ (hier: #.##0,00" m² ") ( ² = Tastenkombination (AltGr)+(2)).

6. Klicken Sie auf [_OK_]. Das erstellte Format wird sofort auf die markier-ten Zellen angewendet.

Sie können bis zu vier Abschnitte For-matcodes angeben. Die Abschnitte, die durch Semikolons getrennt werden, defi-nieren die Formate für positive Zahlen, negative Zahlen, Nullwerte und Text (in dieser Reihenfolge).

Benutzerdefiniertes Zahlenformat

Wenn Sie nur zwei Abschnitte angeben, wird der erste Abschnitt für positive Zahlen und Nullen verwendet, der zweite für negative Zahlen. Wenn Sie nur einen Abschnitt angeben, verwenden alle Zahlen dieses Format. Wenn Sie für einen Abschnitt nichts angeben, müssen Sie das ab-schließende Semikolon für den betreffenden Abschnitt dennoch setzen. Werte dieses Ab-schnitts werden dann nicht angezeigt.

Beispiel:

Positive Zahlen Negative Zahlen Nullwerte Text

[BLAU]#.##0,00 [ROT]-#.##0,00 [GRÜN]0 [ZYAN]@

[BLAU]#.##0,00;[ROT]-#.##0,00;[GRÜN]0;[ZYAN]@

Das obige Zahlenformat färbt die Schrift in Abhängigkeit vom eingegebenen Wert. Positive Zah-len erscheinen in Blau mit Tausendertrennzeichen und 2 Dezimalstellen, negative Zahlen in Rot mit vorangestelltem Minuszeichen, Tausendertrennzeichen und 2 Dezimalstellen, eine Null wird immer in Grün und Texteingaben in der Zelle werden in Zyan dargestellt.

Benutzerdefiniertes Zahlenformat mit Bedingung

Sie können benutzerdefinierte Zahlenformate auch an Bedingungen knüpfen. Die Bedingung muss in eckigen Klammern stehen:

[Bedingung1] Format; [Bedingung2] Format; Format für den Rest

Operator Erläuterung

< ist kleiner als

> ist größer als

Die Bedingung setzt sich aus einem Vergleichsoperator und einem Wert zusammen.

<= ist kleiner oder gleich

33


>= ist größer oder gleich

= ist gleich (entspricht)

<> ist ungleich (entspricht nicht)

Das folgende Format zeigt z.B. Zahlen kleiner oder gleich 100 in Schriftfarbe Rot formatiert und Zahlen größer 100 in Schriftfarbe Blau formatiert an:

[Rot][<=100];[Blau][>100]

Für eine Nullunterdrückung über das Zahlenformat müsste das Format so aussehen:

[=0]""

Die benutzerdefinierten Zahlenformate wie sie eben beschrieben wurden, beeinflus-sen nur die Zahlenformatierung und Schriftfarbe. Wenn Sie Hintergrundfarben, Rah-men und Schriftattribute vom Wert einer Zelle abhängig machen möchten, verwenden Sie den Befehl Bedingte Formatierung im Menüband auf der Registerkarte Start in der Gruppe Formatvorlage (Seite 35).

Benutzerdefiniertes Zahlenformat kopieren

Die Zahlenformate, die Sie selber erstellt haben, stehen nur in der aktuellen Arbeitsmappe (Da-tei) zur Verfügung und werden auch mit dieser Mappe gespeichert und geladen. Sie können die benutzerdefinierten Formate allerdings zwischen den Arbeitsblättern kopieren.

Zum Kopieren eines Formates müssen beide Arbeitsmappen geöffnet sein. Gehen Sie an-schließend folgendermaßen vor:

1. Kopieren Sie eine Zelle mit dem jeweiligen Format in die Zwischenablage.

2. Wechseln Sie zu der zweiten Arbeitsmappe (Registerkarte Ansicht, Gruppe Fenster, Schaltfläche Fenster wechseln oder Tasten-kombination (Strg)+(F6)), und markieren Sie dort eine oder mehrere Zellen.

3. Klicken Sie mit der rechten Maustaste auf die Markierung, und wählen Sie im Kontextmenü den Befehl Inhalte einfügen...

4. Aktivieren Sie in dem rechts stehenden Dialog-feld die Option Formate. Excel kopiert mit dieser Option nur das Zellformat.

5. Hinweis: Falls danach in der Zelle die Zeichen ##### stehen, muss die Spalte verbreitert werden.

Inhalte einfügen, Formate

Ein weitere Variante, um die Formate zu übertragen: Markieren Sie die Zelle, die das ge-wünschte Format hat. Klicken Sie auf das Symbol Format übertragen auf der Register-karte Start in der Gruppe Zwischenablage. Wechseln Sie in die Arbeitsmappe, in der Sie das Format benötigen, und "überpinseln" Sie die Zellen, die das Format erhalten sollen.

34


Benutzerdefiniertes Zahlenformat löschen

Alle Zahlenformate, die Sie selber definiert haben, zeigt Ihnen Excel im Fenster Zellen forma-tieren in der Kategorie Benutzerdefiniert an. Wenn Sie ein neues Zahlenformat aus einem bestehenden ableiten, bleibt das "alte" Zahlenformat ebenfalls noch in der Liste erhalten. Damit die Liste der benutzerdefinierten Zahlenformate nicht völlig unüberschaubar wird, sollten Sie nicht mehr benötigte eigene Zahlenformate löschen:

1. Öffnen Sie wieder das Dialogfeld Zellen formatieren: Klicken Sie auf der Registerkarte Start in der Gruppe Zahl unten rechts auf diese kleine Schaltfläche , oder drücken Sie die Tastenkombination [Strg]+[1].

2. Auf der Registerseite Zahlen wählen Sie in der Kategorie Benutzerdefiniert das Zahlen-format aus, das Sie entfernen möchten.

3. Klicken Sie auf die Schaltfläche [_Löschen_].

Leider müssen Sie mehrere Einträge immer einzeln löschen. Excel lässt eine Mehrfachmarkie-rung in dem Dialogfeld nicht zu.

2.15 Bedingte Formatierung

Hiermit können Zellhintergrund, Rahmen und Schriftattribute von einer Bedingung abhängig gemacht werden. Dies können z.B. Zahlen sein, die einen bestimmten Sollwert über- oder un-terschreiten.

Für angepasste Zahlenformate und Schriftfarbe benötigen Sie benutzerdefinierte Zahlenformate wie es vorher beschrieben wurde.

Bedingte Formatierung anwenden

Beispiel 1 Angenommen, Sie haben eine Prüfungsliste mit verschiedenen Noten-gruppen erstellt, die Gesamtnote darf natürlich nur eine Note zwischen 1 bis 6 ergeben.

Um nun zu prüfen, ob die Gesamtprüfungsnote dem Notenschlüssel entspricht, können Sie die Spalte mit einer bedingten Formatierung be-legen, die Ihnen anzeigt, wenn Null, ein "Minuswert" oder ein Wert grö-ßer als 6 ausgerechnet wird.

Die Bedingung für das rechts stehende Beispiel lautet:

Wenn der Zellwert nicht zwischen 1 und 6 liegt, dann gelten folgende Formatierungen für diese Zellen:

rote Hintergrundfarbe und

Schriftfarbe Weiß.

Beispiel

In Excel können Sie Bedingungen für die Formatierung festlegen. Zusammen mit dem Grund-format stehen Ihnen verschiedene Formate, z.B. Schriftfarben, zur Verfügung. Ist keine der angegebenen Bedingungen wahr, behalten die Zellen ihr ursprüngliches Format.

1. Markieren Sie die Zellen, die eine bedingte Formatierung erhalten sollen.

35


2. Rufen Sie auf: Registerkarte Start, Gruppe Formatvorla-gen, Symbol Bedingte Formatierung, Neue Regel.

3. In dem folgenden Dialogfeld wählen Sie den Regeltyp aus: Nur Zellen formatieren, die enthalten.

Bedingte Formatierung

Symbol mit Untermenü

4. In dem Dialogfeld Neue Formatierungsregel wählen Sie aus und tragen ein:

5. Klicken Sie auf die Schaltfläche [Formatieren…]. Das Dialogfeld Zellen formatieren er-scheint.

6. Legen Sie auf den Registerseiten Schrift und Ausfüllen die Formatierungen fest, die durch die bedingte Formatierung angewendet werden sollen.

7. Schließen Sie das Dialogfeld Zellen formatieren über die Schaltfläche [_OK_].

Bedingte Formatierung mit Vorschau

8. In dem Dialogfeld Neue Formatierungsregel sehen Sie in dem kleinen Vorschau-Rahmen die bedingte Formatierung. Mit einem Klick auf [_OK_] schließen Sie auch dieses Fenster. Die Zellwerte, die nicht zwischen 1 und 6 liegen, sind jetzt in weißer Schrift auf rotem Hin-tergrund formatiert.

36


Beispiel 2 Für eine Prüfungsliste möchten wir die Zellen der Prüflinge, die nicht bestanden haben, farblich hervorheben (weniger als 50% richtig beantwortet) und die Zellen der Prüflinge, die überdurch-schnittlich gut (mehr als 80% richtig beantwortet) abgeschnitten haben, in einer anderen Farbe hervorheben. Der Wert für Bestanden 50% steht in der Zelle O4, die erreichten Prozentanteile der Prüflinge in der Spalte N in den Zeilen 8 bis 14.

Die Formel muss mit einem Gleichheitszeichen beginnen. Dann folgt die Prüfung der Zellen, in denen die relevanten Eingaben eingetragen sind mit Hilfe der Vergleichsoperatoren (<, >, >=, >=, =, <>). Achten Sie auch unbedingt auf die mög-licherweise nötige Angabe von relativen, absolu-ten und gemischten Bezügen mit oder ohne $-Zeichen.

Erstellen Sie eine weitere Regel für die Prüflin-ge, die mehr als 80% richtig beantwortet haben. Über das vorher gezeigte Untermenü des Sym-bols Bedingte Formatierung können Sie dann die Regeln verwalten:

Eine Formel eingeben

Regeln verwalten

Prüfungsliste mit Hervorhebungen

37


Zellen mit bedingten Formatierungen suchen

Um nach Zellen mit identischen bedingten Formatierun-gen zu suchen, klicken Sie auf eine Zelle mit den zu suchenden Bedingungen und Formatierungen.

1. Drücken Sie die (F5)-Taste oder die Tastenkombi-nation (Strg)+(G).

2. In der Dialogbox Gehe zu klicken Sie auf die Schaltfläche [Inhalte…], und im rechts stehenden Dialogfeld aktivieren Sie die Option Bedingte Formate.

3. Um Zellen mit

Bedingte Formate auswählen

a) identischen bedingten Formatierun-

gen zu finden, klicken Sie auf den Op-tionsbutton Gleiche.

Identische Formatierungen suchen

b) beliebigen bedingten Formatierungen zu finden, klicken Sie auf den Opti-onsbutton Alles.

Beliebige Formatierungen suchen

Datenillustration mit der bedingten Formatierung

Bestimmte Trends oder Werte in Ihrem Arbeitsblatt heben Sie schnell und professionell mit den drei neuen Möglichkeiten der Datenillustration hervor:

1. Markieren Sie den entsprechenden Bereich.

2. Rufen Sie auf: Registerkarte Start, Gruppe Formatvorlagen, Schaltfläche Bedingte For-matierung, und wählen Sie dann z.B. Datenbalken, Farbskalen oder Symbolsätze. Be-reits beim Zeigen auf ein Format im Katalog sehen Sie die Formatierung (Livevorschau):

Datenbalken zeigen einen farbigen Daten-balken in der Zelle an, dessen Länge den Wert der Zelle darstellen.

Farbskalen zeigen mehrfarbige abgestufte Farbschattierungen entsprechend dem Zell-wert an.

Symbolsätze stellen Ihnen einen Katalog von unterschiedlichen Zeichen bereit, die - in der Zelle platziert - Trends auf diverse Weise aufzeigen.

In dem nachfolgenden Ausschnitt sehen Sie eine Tabelle mit farbigen Datenbalken:

Katalog mit Livevorschau

Bedingte Formatierung mit farbigen Datenbalken

38


39

2.16 Die Index-Funktion

Mit der Index-Funktion bietet uns Excel interessante Möglichkeiten, Berichte zu erstellen oder mit den Daten zu rechnen. Für diese Funktion wird ein Bereich, eine Matrix benötigt:

=INDEX(Matrix;Spalte;Zeile)

Die Index-Funktion hat 3 Argumente:

Matrix: Bereich in dem die Daten stehen

Spaltennummer innerhalb des Bereichs

Zeilennummer innerhalb des Bereichs.

Eine Index-Tabelle

In dem vorherigen Beispiel sehen Sie einen Monatsbericht über Verbrauchszahlen in einem Un-ternehmen. Ursprünglich umfasste diese Tabelle 24 Spalten für mehrere Jahre. Wegen der besseren Übersichtlichkeit ist sie auf die zwei Spalten Strom und Dampf für ein Jahr reduziert.

DDAATTEENNÜÜBBEERRPPRRÜÜFFUUNNGG VVEERRWWAALLTTUUNNGGSSAAKKAADDEEMMIIEE BBEERRLLIINN FFOORRMMEELLNN UUNNDD FFUUNNKKTTIIOONNEENN

3 Arbeiten mit der Datenüberprüfung Tippfehler und falsche Eingaben sind manchmal recht ärgerlich. Wenn Sie gewährleisten möch-ten, dass in einer Arbeitsmappe die richtigen Daten eingegeben werden, können Sie für einzel-ne Zellen oder Zellbereiche angeben, welche Daten zulässig sind. Gerade bei großen Listen und bei Dateien, die von mehreren Benutzern ausgefüllt werden, kann dies ein Vorteil sein.

Seit der Version 97 von Excel haben Sie die Möglichkeit, für Eingabefelder eine Datenüberprü-fung (früher Gültigkeitsprüfung) vorzunehmen, um unerwünschte Eingaben abzufangen.

3.1 Datenregel festlegen

1. Markieren Sie die Zelle bzw. Berei-che, deren Eingabemöglichkeiten Sie beschränken möchten.

2. Wählen Sie auf der Registerkarte Daten in der Gruppe Datentools das Symbol Datenüberprüfung . Auf drei Registerseiten haben Sie jetzt die Möglichkeit, die Einstellungen für die Zelleingabe festzulegen.

Datenüberprüfung, Einstellungen

Registerseite Einstellungen Klicken Sie im Bereich Zulassen auf den Listenpfeil , um Einschrän-kungen auf die Dateneinträge in den markierten Zellen der Tabelle anzu-wenden.

Wählen Sie in der geöffneten Liste die Option Benutzerdefiniert, um eine Formel oder einen Ausdruck einzugeben oder auf eine Berechnung in einer anderen Zelle zu verweisen, um gültige Einträge zu bestimmen.

Achten Sie auf eine korrekte Adressierung!

Feld Zulassen

Klicken Sie auf den gewünschten Ver-gleichsoperator im Feld Daten, der vom Typ abhängt, den Sie im Feld Zulassen ausgewählt haben.

Vergleichsoperator bestimmen

Wenn Sie in dem Feld Zulassen die Auswahl Liste treffen, gibt es einige Besonder-heiten zu beachten, die im Anschluss erläutert werden.

40

FFOORRMMEELLNN UUNNDD FFUUNNKKTTIIOONNEENN VVEERRWWAALLTTUUNNGGSSAAKKAADDEEMMIIEE BBEERRLLIINN DDAATTEENNÜÜBBEERRPPRRÜÜFFUUNNGG

Registerseite Eingabemeldung Geben Sie wahlweise einen Titel für die Eingabemeldung ein, die angezeigt wird, wenn jemand den Zellcursor auf diese Zelle stellt. Es wird dann eine QuickInfo neben der Zelle angezeigt:

Im dem großen Meldungsfeld ist Platz für maximal 255 Zeichen. Drücken Sie die (¢) Return-Taste (Enter-Taste), um hier eine neue Zeile zu beginnen.

Datenüberprüfung, Eingabemeldung

Registerseite Fehlermeldung Auf der Registerseite Fehlermeldung kann in dem Listenfeld Typ das Merkzeichen ausgewählt werden, mit dem die Fehlermeldung beginnt. Das ist aber nicht nur eine optische Wahl, sondern damit entscheiden Sie auch, wie sich Excel im Fehlerfall verhält.

Wenn Sie die Option Stopp wählen, wird im Fehlerfall verhindert, dass die falsche Eingabe in die Zelle eingetragen wird. Sie müssen die Eingabe wiederholen oder abbrechen.

Bei Warnung muss ein fehlerhafter Eintrag nochmals bestätigt werden.

Bei Information wird nur im Nachhinein über den falschen Eintrag informiert.

Geben Sie wahlweise einen Titel für das Fehlermeldungsfeld ein. Der Titel wird in fetter Schrift in der Meldung angezeigt. Und tippen Sie noch die Fehlermeldung ein (max. 255 Zeichen), die bei einer ungültigen Eingabe angezeigt wird.

Mit dem Anklicken der Schaltfläche [Alle_löschen] werden alle Markierungen rückgängig gemacht und alle Informationen in allen drei Registerseiten gelöscht.

Machen Sie die gewünschten Angaben, und klicken Sie auf [_OK_], um die Da-tenüberprüfung zu aktivieren.

Datenüberprüfung, Fehlermeldung

41


Besonderheiten bei der Listenauswahl Wenn Sie auf der Registerseite Einstel-lungen die Liste auswählen, stehen Ih-nen zum Definieren dieser Liste mehrere Möglichkeiten zur Verfügung.

Sie können die Begriffe, die ausgewählt werden sollen, im Feld Quelle manuell eintragen, wobei die Begriffe durch Strichpunkte (Semikolon) zu trennen sind.

Nachdem Sie das Dialogfeld über [_OK_] geschlossen haben, ist bei den betreffen-den Zellen ein Listenpfeil angebracht. Sie können einen Begriff aus der Liste auswählen:

Aber es gibt auch noch eine andere Mög-lichkeit, die Quelle für die Liste in dem rechts stehenden Dialogfeld zu überneh-men: Falls die Begriffe auf demselben Tabellenblatt schon aufgelistet sind, mar-kieren Sie den Zellbereich in der Ta-belle und der Bereich wird mit vorange-stelltem Gleichheitszeichen und absoluten Zellbezügen in das Feld Quelle über-nommen.

Begriffe manuell eingeben

Zellbereiche werden übernommen

Haben Sie auf einem anderen Tabellenblatt die Begriffe aufgelistet, können Sie den Bereich ebenfalls markieren, indem Sie in das entsprechende Tabellenblatt wechseln. In den Excel-Vorversionen (auch Excel 2007) war im Fenster Datenüberprüfung ein Wechsel in ein anderes Tabellenblatt nicht erlaubt. Diese "Beschränkung" können Sie allerdings umgehen. Diese Vor-gehensweise ist auch für Excel 2010 zu empfehlen.

Vergeben Sie für die Begriffsliste auf dem anderen Tabellenblatt einen Bereichsna-men. Diesen können Sie dann mit voran-gestelltem Gleichheitszeichen im Textfeld Quelle verwenden (hier: =Auswahlliste) oder mit der Taste [F3] abrufen.

Diese sehr brauchbare Funktion arbeitet aber nicht wie gewünscht, wenn Sie bei-spielsweise die Liste über die Daten-Eingabemaske ausfüllen. Fehleingaben werden bei der Verwendung der Maske nicht zuverlässig abgefangen. Neue Beg-riffe direkt in die Liste zwischen den ande-

42

FFOORRMMEELLNN UUNNDD FFUUNNKKTTIIOONNEENN VVEERRWWAALLTTUUNNGGSSAAKKAADDEEMMIIEE BBEERRLLIINN DDAATTEENNÜÜBBEERRPPRRÜÜFFUUNNGG

ren Begriffen eingeben und bei Bedarf sortieren. Auch können "ungültige" Daten über die Option Warnung oder Informa-tion in der Registerseite Fehlermeldung der Datenüberprüfung zugelassen wer-den.

Bereichsnamen aus einem anderen Tabellenblatt übernehmen

In beiden Fällen können Sie die Gültigkeit der vorhandenen Daten nachträglich prüfen.

3.2 Vorhandene Daten nachträglich prüfen

Wenn Sie eine Datenüberprüfung in einer Tabelle vornehmen, die bereits Daten enthält, müs-sen Sie die vorhandenen Daten nachträglich manuell prüfen.

Mit Hilfe des Befehls Ungültige Daten einkreisen (Registerkarte Daten, Gruppe Datentools, Symbol Datenüberprüfung) können Sie die Eingabezellen nachträglich "aufspüren", die nicht der Gül-tigkeitsregel entsprechen:

1. Markieren Sie in der Datei Noten den Bereich B8:K14.

Untermenü des

Symbols Datenüberprüfung

2. Legen Sie die Gültigkeitskriterien fest. Für den Bereich B8:K14 lauten sie: Ganze Zahl – zwischen – Min: 0 – Max: =B$7 Beachten Sie zum einen das Gleichheitszeichen = und zum anderen das $-Zeichen nur für die Zeilenzahl 7, da die Werte immer in der Zeile 7 stehen! Aber der Spaltenbuchstabe B bleibt relativ, da die Werte in der Zeile 7 jeweils in den Spalten B bis K stehen!:

Gültigkeitskriterien festlegen

3. Rufen Sie den oben beschriebenen Befehl Ungültige Daten einkreisen auf:

Ungültige Eingaben werden rot eingekreist

43


44

Die entsprechenden Zellen werden rot eingekreist. Die maximal erreichbare Punktzahl steht in der Zeile 7 in der gleichen Spalte! Beispiele: Für die Spalte C steht sie in der Zelle C7, für die Spalte K in der Zelle K7. Die rot eingekreisten Zellen kennzeichnen jeweils die für diese Spalte ungültigen Eingaben.

Durch Klicken auf den Befehl Gültigkeitskreise löschen können Sie die Markierungen wieder entfernen.

Symbol Datenüberprüfung

3.3 Ausdehnen der Datenüberprüfung

Wenn Sie eine Datenregel auf weitere Bereiche ausdehnen möchten, markieren Sie die Zellen, für die die Regel bisher galt und zusätzlich die Zellen, die dieselbe Gültigkeitsregel erhalten sollen.

Wählen Sie erneut die Datenüberprüfung aus. Sie werden von Excel gefragt, ob Sie die Daten-überprüfung auf die neuen Zellen ausdehnen wollen:

Meldung bestätigen

Antworten Sie mit [_Ja_], und bestätigen Sie nur noch das Dialogfeld Datenüberprüfung mit [_OK_].

FFOORRMMEELLNN UUNNDD FFUUNNKKTTIIOONNEENN VVEERRWWAALLTTUUNNGGSSAAKKAADDEEMMIIEE BBEERRLLIINN SSTTIICCHHWWOORRTTVVEERRZZEEIICCHHNNIISS

4 Stichwortverzeichnis

Funktion........................................................... 6 A Datum & Zeit ............................................. 12

Einfügen .................................................... 26 Add-Ins ......................................................... 13 Häufigkeit .................................................. 18 Add-Ins-Manager .......................................... 13 Heute......................................................... 11 Adressenlisten .............................................. 11 Index.......................................................... 39 Analyse-Funktion .......................................... 13 Logik.......................................................... 15 Arbeitsmappe schützen................................. 29 Math. & Trigonom. ..................................... 14 Argumente ...................................................... 4 Matrix......................................................... 17 AutoAusfüllen................................................ 17 Statistik...................................................... 15

B Suchen ...................................................... 26 Sverweis .................................................... 24 Bedingte Formate ......................................... 38 Text ............................................................. 8 Bedingte Formatierung.................................. 35 Verketten ................................................... 10 Suchen...................................................... 38 Verschachtelt....................................... 19, 28 Bedingungen................................................. 35

Funktionen.......................................................4 Befehlsgruppe Funktions-Assistent ......................................... 4 Definierte Namen ...................................... 25 Funktionsname................................................ 4 Funktionsbibliothek ..................................... 4

Benutzerdefiniert............................................. 4 G Benutzerdefinierte Zahlenformate................. 30 Gesperrt ........................................................ 28 Bedingung................................................. 33 Gruppe Erstellen .................................................... 33

Definierte Namen....................................... 25 Kopieren.................................................... 34 Funktionsbibliothek...................................... 4 Löschen .................................................... 35

Gültigkeit Berechnung Eingabemeldung........................................ 41 Fehler.......................................................... 6 Einstellungen ............................................. 40 Bereich............................................................ 6 Fehlermeldung........................................... 41 Blattschutz aufheben .................................... 29 Listenauswahl............................................ 42

C Gültigkeitskriterien ......................................... 40 Gültigkeitsprüfung.......................................... 43 Cube ............................................................... 4 Gültigkeitsregel.............................................. 40

D H Daten Häufigkeit ...................................................... 18 Gültigkeit ................................................... 40

Datenbalken.................................................. 38 I Datenbank....................................................... 4 Index-Funktion............................................... 39 Datenillustration ............................................ 38 Information ...................................................... 4 Datum ............................................................. 4

Datumsfunktionen ......................................... 12 K Dezimaltrennzeichen..................................... 12

Dialogfeld Kennwort ....................................................... 29 Neue Formatierungsregel ......................... 36 Bestätigen ................................................. 29

Dokumentschutz ........................................... 29 Klassenbereich.............................................. 18 Aufheben................................................... 29 Kompatibilität ................................................... 4

Konstruktion .................................................... 4 E

L Ergebnisbereich ............................................ 18 Ländereinstellung .......................................... 12

F Logik................................................................ 4 Logikfunktionen ............................................. 15 Farbskalen .................................................... 38

Fehlermeldung ............................................ 6, 7 M Fehlersymbol .................................................. 7

Fehlerüberprüfung ...................................... 6, 7 Math. & Trigonom.-Funktionen ...................... 14 Finanzmathematik........................................... 4 Matrix................................................... 4, 27, 39 Formatcode................................................... 30 Matrixformel................................................... 22 Formatierungselement .................................. 36 Matrixformel-Besonderheiten......................... 24 Formel............................................................. 6 Matrixfunktionen ............................................ 17 Formel, Funktion einfügen ............................ 20 Merkzeichen .................................................. 41 Formeln......................................................... 23 Mittelwert ....................................................... 19

45

SSTTIICCHHWWOORRTTVVEERRZZEEIICCHHNNIISS VVEERRWWAALLTTUUNNGGSSAAKKAADDEEMMIIEE BBEERRLLIINN FFOORRMMEELLNN UUNNDD FFUUNNKKTTIIOONNEENN

N Namen definieren.......................................... 25

O Operator.......................................................... 6

R Regionale Einstellungen ............................... 12 Register

Formeln....................................................... 4 Regionale Einstellungen ........................... 12 Start .......................................................... 38

Registerkarte Eingabemeldung ....................................... 41 Einstellungen ............................................ 40 Fehlermeldung .......................................... 41 Fehlerüberprüfung....................................... 7 Schutz ....................................................... 28

Runden ......................................................... 20

S Schaltfläche

Anpassen .................................................. 12 SmartTag ........................................................ 7 Spaltenbreite................................................... 8 Spaltenindex ................................................. 27 Spaltennummer............................................. 39 Statistik ........................................................... 4 Statistische Auswertung.................................. 4 Statistische Funktionen................................. 15 Suchkriterium................................................ 26 Sverweis ..................................................24, 25 Symbol

Bedingte Formatierung .................. 36, 37, 38 Funktion einfügen ........................................ 4 Namen definieren ...................................... 25

Symbolsätze.................................................. 38

T Text ................................................................. 4 Textfunktionen................................................. 8 Trigonometrische Funktionen .......................... 4

U Übergeordnete Funktion................................ 21 Ungültige Daten markieren............................ 43

V Vergleichsoperator .................................. 37, 40 Verschachtelte Funktion .......................... 19, 28 Verschachtelungsebene ................................ 20 Verweisfunktion ............................................. 24

W Wenn-Bedingung........................................... 28 Werte............................................................... 6

Z Zeilennummer ............................................... 39 Zeit .................................................................. 4 Zellbezug....................................................... 10 Zellen formatieren.......................................... 30 Zellformat ...................................................... 34 Zellschattierung ............................................. 35 Zellschutz ...................................................... 28 Zellwert ist ..................................................... 36

46

Documents

MS Excel 2010 - Profiwissen Formeln und Funktionen · PDF file1.2. Verwendung der Funktionen . Eine Funktion kann allein in einer Zelle oder innerhalb einer Formel stehen. Funktionen