MS Excel 2013 Profiwissen - · PDF fileExcel 2013 Verwaltungsakademie Berlin Formeln und...

62
Verwaltungsakademie Berlin MS Excel 2013 Profiwissen Formeln und Funktionen S EMINARUNTERLAGE

Transcript of MS Excel 2013 Profiwissen - · PDF fileExcel 2013 Verwaltungsakademie Berlin Formeln und...

Page 1: MS Excel 2013 Profiwissen -   · PDF fileExcel 2013 Verwaltungsakademie Berlin Formeln und Funktionen Verwaltungsakademie Berlin Seite 3 Inhaltsverzeichnis 1 Funktionen

 

 

Verwaltungsakademie Berlin

MS Excel 2013 ‐ Profiwissen Formeln und Funktionen

  

 

SEMINARUNTERLAGE 

Page 2: MS Excel 2013 Profiwissen -   · PDF fileExcel 2013 Verwaltungsakademie Berlin Formeln und Funktionen Verwaltungsakademie Berlin Seite 3 Inhaltsverzeichnis 1 Funktionen

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

  

Page 3: MS Excel 2013 Profiwissen -   · PDF fileExcel 2013 Verwaltungsakademie Berlin Formeln und Funktionen Verwaltungsakademie Berlin Seite 3 Inhaltsverzeichnis 1 Funktionen

Excel 2013 Verwaltungsakademie Berlin Formeln und Funktionen

Verwaltungsakademie Berlin Seite 3

Inhaltsverzeichnis 1 Funktionen ................................................................................................................................ 4

1.1 Aufbau der Funktionen ..................................................................................................... 4 1.2 Statistische Auswertungen ................................................................................................ 4 1.3 Verwendung der Funktionen ............................................................................................. 4

2 Weiterführende Formeln, Funktionen und Befehle ...................................................................... 8 2.1 Fehler in einem Arbeitsblatt .............................................................................................. 8 2.2 Kategorie Text ................................................................................................................ 11 2.3 Kategorie Datum & Zeit ................................................................................................... 17 2.4 Add-Ins .......................................................................................................................... 18 2.5 Kategorie Math. & Trigonom. .......................................................................................... 19 2.6 Kategorie Statistik........................................................................................................... 20 2.7 Kategorie Logik ............................................................................................................... 23 2.8 Kategorie Matrix ............................................................................................................. 24 2.9 Kategorie Finanzmathematik ........................................................................................... 25 2.10 Datenbankfunktionen ..................................................................................................... 25 2.11 Verschachtelte Funktionen .............................................................................................. 25 2.12 Matrizen ........................................................................................................................ 27 2.13 Übung............................................................................................................................ 30 2.14 Die Arbeit mit einer Verweis-Funktion .............................................................................. 30 2.15 Zellschutz einrichten ....................................................................................................... 34 2.16 Dokumentschutz aufheben ............................................................................................. 35 2.17 Arbeitsmappe schützen ................................................................................................... 35 2.18 Übung 1 ......................................................................................................................... 37 2.19 Übung 2 ......................................................................................................................... 37 2.20 Benutzerdefinierte Zahlenformate ................................................................................... 39 2.21 Bedingte Formatierung ................................................................................................... 44 2.22 Die Index-Funktion ......................................................................................................... 48 2.23 Übung............................................................................................................................ 49 2.24 Hyperlinks ...................................................................................................................... 49

3 Arbeiten mit Datenlisten .......................................................................................................... 53 3.1 Allgemeines zum Aufbau einer Datenliste ......................................................................... 53 3.2 Komplexes Sortieren über ein Dialogfeld .......................................................................... 53

4 Arbeiten mit der Datenüberprüfung .......................................................................................... 56 4.1 Datenregel festlegen ....................................................................................................... 56 4.2 Vorhandene Daten nachträglich prüfen ............................................................................ 59 4.3 Ausdehnen der Datenüberprüfung ................................................................................... 60

5 Stichwortverzeichnis ................................................................................................................ 61

Page 4: MS Excel 2013 Profiwissen -   · PDF fileExcel 2013 Verwaltungsakademie Berlin Formeln und Funktionen Verwaltungsakademie Berlin Seite 3 Inhaltsverzeichnis 1 Funktionen

Excel 2013 Verwaltungsakademie Berlin Formeln und Funktionen

Verwaltungsakademie Berlin Seite 4

1 Funktionen Eine besonders große Arbeitserleichterung in einer Tabellen-Kalkulation sind die Funktionen. Verein-facht gesagt, handelt es sich um vorbereitete Formeln, um Ihnen die Arbeit zu erleichtern. Damit kön-nen 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

• Technisch

• Cube

• Kompatibilität

• Web

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 einzutragen. Mehrere Argumente werden jeweils durch ein Semikolon (;) getrennt. Es gibt nur einige wenige Funkti-onen mit leeren Klammern, z.B. =Jetzt(),

aber die Klammer ist immer zu schreiben.

Steht der Funktionsname am Anfang einer Formel, muss davor unbedingt das Gleichheitszei-chen = stehen!

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

In unserer Tabelle benötigen wir noch die Summen der einzelnen Vierteljahre und die Jahressumme. Ferner interessiert uns das Maximum, der Mittelwert und das Minimum.

Tragen Sie in die Arbeitsmappe Betriebliches Vorschlagswesen1, Einsparungen in GROSSBUCHSTABEN ein:

A8 SUMMEN:

A10 MAXIMUM:

A12 MITTELWERT:

A14 MINIMUM:

1.3 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 wiederum eine oder mehrere Funktionen stehen.

Für die Tabelle Betriebliches Vorschlagswesen benötigen Sie jetzt vier Funktionen:

• =Summe(Bereich)

• =Max(Bereich)

• =Mittelwert(Bereich)

• =Min(Bereich).

Page 5: MS Excel 2013 Profiwissen -   · PDF fileExcel 2013 Verwaltungsakademie Berlin Formeln und Funktionen Verwaltungsakademie Berlin Seite 3 Inhaltsverzeichnis 1 Funktionen

Excel 2013 Verwaltungsakademie Berlin Formeln und Funktionen

Verwaltungsakademie Berlin Seite 5

Tragen Sie in B8 ein: = S u m m e ( b 2 : b 6 )

Den Funktionsnamen und Zellbereich können Sie auch in Kleinbuchstaben eingeben, sie werden auto-matisch umgewandelt. Beim Bereich reicht ein Punkt aus. Excel setzt den Doppelpunkt selbst dorthin.

Für die anderen Vierteljahre und die Jahressummen kopieren Sie diese Funktion aus der Zelle B8 in den Bereich C8:F8.

1. Positionieren Sie den Zellcursor in der Zelle B8.

2. Kopieren Sie die neue Formel, z.B. über das Symbol Kopieren .

3. Markieren Sie den Bereich C8:F8.

4. Drücken Sie die (¢)-Taste.

Die Summen-Funktion

Nun tragen Sie in der Zelle B10 noch die nachfolgende Formel ein und kopieren die Formel auch in den Bereich C10:F10:

= M a x ( b 2 : b 6 )

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

• Tastenkombination (ª_)+(F3) oder

• Menüband, Registerkarte Formeln, Gruppe Funktionsbibliothek, Schalt-fläche Funktion einfügen.

Probieren Sie es aus:

1. Positionieren Sie den Zellcursor in der Zelle B12.

2. Klicken Sie auf das Symbol .

3. In dem Dialogfeld Funktion einfügen wählen Sie aus dem Listenfeld die Kategorie Statistik aus.

Page 6: MS Excel 2013 Profiwissen -   · PDF fileExcel 2013 Verwaltungsakademie Berlin Formeln und Funktionen Verwaltungsakademie Berlin Seite 3 Inhaltsverzeichnis 1 Funktionen

Excel 2013 Verwaltungsakademie Berlin Formeln und Funktionen

Verwaltungsakademie Berlin Seite 6

4. In der unteren Liste Funktion auswählen klicken Sie MITTELWERT an:

*

Funktions-Assistent 1. Schritt

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

6. In dem nun folgenden Dialogfeld Funktionsargumente tragen Sie im Textfeld Zahl1 den Bereich b2:b6 ein oder klicken Sie in diesem Textfeld rechts das Symbol an. Jetzt können Sie im Zeigen-Modus den Bereich markieren. Nach dem Markieren kommen Sie mit der (¢)-Taste wieder zurück in das Dialogfeld.

Funktions-Assistent 2. Schritt

7. Klicken Sie auf die Schaltfläche [_OK_]. 8. Kopieren Sie die Formel in den Bereich C12:F12. Wenn Sie das Kopieren mit Drag & Drop vorneh-

men möchten, dann bewegen Sie den Mauszeiger auf das Ausfüllkästchen in der rechten unteren Ecke des Zellcursors in der Zelle B12: Der Mauszeiger wird dort zu einem schwarzen Kreuz +. Mar-kieren Sie nun bei gedrückter linker Maustaste den Bereich B12:F12. Nach dem Loslassen der Maustaste wird die Formel in den erweiterten Bereich kopiert.

Page 7: MS Excel 2013 Profiwissen -   · PDF fileExcel 2013 Verwaltungsakademie Berlin Formeln und Funktionen Verwaltungsakademie Berlin Seite 3 Inhaltsverzeichnis 1 Funktionen

Excel 2013 Verwaltungsakademie Berlin Formeln und Funktionen

Verwaltungsakademie Berlin Seite 7

Die Gruppe Funktionsbibliothek

Neben der Schaltfläche Funktion einfügen sind auf der Registerkarte Formeln in der Gruppe Funkti-onsbibliothek 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 einer Ausnah-me, 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

Ihre Aufgabe

1. Tragen Sie auch in die Zelle B14 die nächste Formel mithilfe des Funktions-Assistenten ein: =MIN(B2:B6)

2. Kopieren Sie die Formel in den Bereich C14:F14.

3. Speichern Sie die Arbeitsmappe unter dem Dateinamen Betriebliches Vorschlagswesen2, Einsparungen ab.

Ein kleiner Tipp: Sie brauchen nicht den kompletten Dateinamen neu einzugeben. In dem Explorer-Fenster Speichern unter heben Sie mit einem Mausklick die Markierung des Namens auf und er-setzen nur die Ziffer 1 durch die 2.

Tabelle mit statistischen Auswertungen (Spalte A ist bereits optimal verbreitert (siehe nächstes Kapitel))

Page 8: MS Excel 2013 Profiwissen -   · PDF fileExcel 2013 Verwaltungsakademie Berlin Formeln und Funktionen Verwaltungsakademie Berlin Seite 3 Inhaltsverzeichnis 1 Funktionen

Excel 2013 Verwaltungsakademie Berlin Formeln und Funktionen

Verwaltungsakademie Berlin Seite 8

2 Weiterführende Formeln, Funktionen und Befehle Unter einer Formel versteht man eine Anweisung, um bestimmte Berechnungen in einer Zelle durchzu-fü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 Prob-leme auftreten, zeigt Excel eine Fehlermeldung an. Angenommen, Sie geben in einem Arbeitsblatt in der Zelle D2 die Zahl 5 und darun-ter einen beliebigen Text ein. In die Zelle D5 tragen Sie eine Formel ein: =D2+D3. Diese Formel ist falsch, da ein Wert mit einem Text addiert werden soll. Nachdem Sie den Eintrag mit (¢)-Taste abge-schlossen haben, erscheint die Fehlermeldung #WERT! und in der oberen linken Ecke der Zelle sehen Sie ein kleines grünes Dreieck . Steht der Zellcursor auf der fehlerhaften Zelle, sehen Sie links davon ein Kontextsymbol (früher: SmartTag).

Wie Sie fehlerfrei einen Wert mit einem Text verknüpfen können, ist ab der Seite 12 beschrieben.

Fehlermeldung mit Kontextsymbol

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

Page 9: MS Excel 2013 Profiwissen -   · PDF fileExcel 2013 Verwaltungsakademie Berlin Formeln und Funktionen Verwaltungsakademie Berlin Seite 3 Inhaltsverzeichnis 1 Funktionen

Excel 2013 Verwaltungsakademie Berlin Formeln und Funktionen

Verwaltungsakademie Berlin Seite 9

Ist die Prüfung aktiviert, erscheint ein Fehler-Kontext-symbol, wenn die Zelle markiert wird.

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

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

Kontextsymbol 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 Fehler überprüft, diese aber ignoriert wurden, sehen Sie die Anzeige der Probleme erst nach einer Zurücksetzung der ignorierten Fehler! Dazu klicken Sie in den vorher zu sehenden Excel-Optionen 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.

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

#NAME? Text in einer Formel wird nicht als Funktions- oder Bereichsname erkannt, 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 Beispiele: 1. Sie kennen den Wert eines Arguments noch nicht und haben dafür stell-

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

Wert im Kopf der Matrix.

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

Page 10: MS Excel 2013 Profiwissen -   · PDF fileExcel 2013 Verwaltungsakademie Berlin Formeln und Funktionen Verwaltungsakademie Berlin Seite 3 Inhaltsverzeichnis 1 Funktionen

Excel 2013 Verwaltungsakademie Berlin Formeln und Funktionen

Verwaltungsakademie Berlin Seite 10

Fehlermeldung Mögliche Ursachen

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

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

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

Formelüberwachung

Verwendet eine Formel Vorgänger- oder Nachfolgerzellen, kann mithilfe von Spurpfeilen die Beziehung zwischen diesen Zellen und der Formelzelle grafisch dargestellt und somit angezeigt werden, auf welche Zellen die Formel für Ihre Berechnung zugreift. Vor allem bei der Suche nach der Ursache eines Fehlers, kann diese Spur zum Vorgänger bzw. Nachfolger hilfreich sein.

Bei Vorgänger- und Nachfolgerzellen handelt es sich um Zellen, auf die eine Formel in einer anderen Zelle Bezug nimmt. Enthält z.B. die Zelle B6 die Formel =A3, dann ist die Zelle A3 ein Vorgänger von Zelle B6 und B6 ein Nachfolger der Zelle A3.

Spur zum Vorgänger/Nachfolger einblenden Gehen Sie wie folgt vor, um die Spurpfeile zum Vorgänger oder Nachfolger einzublenden: Markieren Sie die entsprechende Zelle und klicken Sie auf der Registerkarte Formeln in der Gruppe Formelüberwachung auf die Schaltfläche Spur zum Vorgänger bzw. Spur zum Nachfolger.

Mit einem Doppelklick auf den entsprechenden Spurpfeil gelangen Sie direkt zur Vorgänger- bzw. Nach-folgerzelle, was vor allem dann sinnvoll ist, wenn diese außerhalb des sichtbaren Bereichs liegt.

Spur zum Vorgänger

Spur zum Nachfolger

Spur zum Fehler einblenden Mithilfe von Spurpfeilen lässt sich auch die Fehlerquelle einer Zelle mit Feh-lerwert ermitteln: Markieren Sie die Zelle, die den Fehlerwert enthält, klicken Sie auf der Registerkarte Formeln in der Gruppe Formelüberwachung auf den Pfeil der Schaltfläche Fehlerüberprüfung und wählen Sie im Unterme-nü den Befehl Spur zum Fehler.

Page 11: MS Excel 2013 Profiwissen -   · PDF fileExcel 2013 Verwaltungsakademie Berlin Formeln und Funktionen Verwaltungsakademie Berlin Seite 3 Inhaltsverzeichnis 1 Funktionen

Excel 2013 Verwaltungsakademie Berlin Formeln und Funktionen

Verwaltungsakademie Berlin Seite 11

Spur zum Fehler

Spurpfeile löschen Beim Speichern der Arbeitsmappe werden die Spurpfeile von Excel gelöscht. Um die Pfeile zu löschen, ohne die Arbeitsmappe zu spei-chern, klicken Sie auf der Registerkarte Formeln in der Gruppe Formelüberwachung auf die Schaltfläche Pfeile entfernen. Ein Klick auf den Pfeil der Schaltfläche öffnet ein Untermenü, über das Sie auch nur die Spur zum Vorgänger oder die Spur zum Nachfolger lö-schen können.

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 Gleichheitszei-chen = stehen!

Nachfolgend sind einige Textfunktionen und deren Verwendung aufgelistet:

Funktionsname

und Syntax Erläuterung Beispiel Ergebnis

GROSS(Text)

KLEIN(Text)

wandelt Text in Groß-/Klein-

buchstaben 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 vor-

gegebene Anzahl Zeichen

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 Zeichen

von Text

LÄNGE("Haus") 4

FINDEN

(Suchtext;Text;Start)

sucht die Position von

Suchtext in Text ab der Posi-

tion von Start;

Groß-/Kleinschreibung wich-

tig, Jokerzeichen sind nicht

erlaubt!

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

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

3

#WERT

(nicht

gefunden)

Page 12: MS Excel 2013 Profiwissen -   · PDF fileExcel 2013 Verwaltungsakademie Berlin Formeln und Funktionen Verwaltungsakademie Berlin Seite 3 Inhaltsverzeichnis 1 Funktionen

Excel 2013 Verwaltungsakademie Berlin Formeln und Funktionen

Verwaltungsakademie Berlin Seite 12

Funktionsname

und Syntax Erläuterung Beispiel Ergebnis

SUCHEN

(Suchtext;Text;Start)

sucht die Position von

Suchtext in Text ab der Posi-

tion von Start;

Groß-/Kleinschreibung uner-

heblich, 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;"13")

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

2013

12,5

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

nach einem Text und mehre-

re Leerzeichen – bis auf eines

– innerhalb eines Textes.

GLÄTTEN(" Alfred Mustermann ")

Ergebnis: Alfred Mustermann

VERKETTEN(Text1;Text2;…)

Text1;Text2;… sind 1 bis maximal

30 Argumente

verbindet einzelne Textele-

mente zu einer Zeichenkette.

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

12345

WERT(Text) wandelt einen als Text ein-

gegebene 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 Format

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

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

12,35

012,30

Die Argumente können Sie entweder als Zellbezug oder als Text direkt eingeben, also z.B. = GLÄTTEN(B9), wenn in der Zelle B9 Alfred Mustermann steht.

Textverknüpfung

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

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 zusammenzufassen, 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 miteinander verbinden. Das nachfolgende Beispiel aus dem Arbeitsblatt Kundenliste in der Datei Datenliste.xlsx zeigt die Eingabe der Argumente für die Funktion VERKETTEN im zweiten Schritt des Funktions-Assistenten (Starten z.B. mit der Tastenkombination (ª_)+(F3)):

Page 13: MS Excel 2013 Profiwissen -   · PDF fileExcel 2013 Verwaltungsakademie Berlin Formeln und Funktionen Verwaltungsakademie Berlin Seite 3 Inhaltsverzeichnis 1 Funktionen

Excel 2013 Verwaltungsakademie Berlin Formeln und Funktionen

Verwaltungsakademie Berlin Seite 13

Die einzelnen Zellen getrennt durch Leerzeichen zusammensetzen

Ergebnis bei diesem Beispiel:

Beispiel: Tabelle Kundenliste in der Datei Datenliste.xlsx

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

&

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

A1: Anita

A2: Heintz

Diese beiden Namen sollen nun in die Zelle A5 übernommen werden, getrennt durch ein Leerzeichen. 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ührungs-zeichen " " 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:

A3: 29

Erweitern Sie die Formel in der Zelle A5:

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

Page 14: MS Excel 2013 Profiwissen -   · PDF fileExcel 2013 Verwaltungsakademie Berlin Formeln und Funktionen Verwaltungsakademie Berlin Seite 3 Inhaltsverzeichnis 1 Funktionen

Excel 2013 Verwaltungsakademie Berlin Formeln und Funktionen

Verwaltungsakademie Berlin Seite 14

Adressenlisten sollten unbedingt jeweils eine separate Spalte für Anrede, Titel, Vorname, Nachname, Straße, PLZ, Ort beinhalten. Haben Sie nur eine Spalte mit dem kompletten Na-men oder für PLZ und Ort, ist die Datei für viele Funktionalitäten unbrauchbar (z.B. Serien-briefe, Sortierung, verschiedene Auswertungen).

Beispiel 3 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 Tabel-le schriftlich fixieren.

Eine Kombination der Funktionen TEXT und HEUTE (Seite 17) 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 folgendermaß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 unter dem Namen Wohnungsangebot.

Textkonvertierung

Auf der Seite 11 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, eine Nur-Text-Datei in Excel mit dem Textkonvertierungs-Assistent zu importieren, zum anderen können Sie auch Text, der bereits in einer Arbeitsmappe in einer Spalte angeordnet ist, für eine Datenbank-Tabelle in getrennte Spalten aufgliedern:

1. Falls Excel geschlossen ist, öffnen Sie es mit einer neuen leeren Arbeitsmappe.

2. Klicken Sie im Datei-Menü auf den Befehl Öffnen und dann doppelt auf die Zeile Computer rechts vom Navigationsbereich.

3. Wählen Sie im nachfolgenden Explorer-Fenster im Listenfeld rechts vom Dateinamen den Dateityp Alle Dateien oder Textdateien.

Page 15: MS Excel 2013 Profiwissen -   · PDF fileExcel 2013 Verwaltungsakademie Berlin Formeln und Funktionen Verwaltungsakademie Berlin Seite 3 Inhaltsverzeichnis 1 Funktionen

Excel 2013 Verwaltungsakademie Berlin Formeln und Funktionen

Verwaltungsakademie Berlin Seite 15

Den Datentyp ändern

4. Navigieren Sie zu dem Ordner, in dem sich die Datei Daten.txt befindet und klicken Sie doppelt auf den Dateinamen. Der Textkonvertierungs-Assistent wird geöffnet.

Handelt es sich bei der Datei, die die Daten enthält, um eine Excel-Arbeitsmappe, starten Sie den Textkonvertierungs-Assistent über die rechtsstehende Schaltflä-che (Registerkarte Daten, Befehlsgruppe Datentools).

5. Bestimmen Sie den Dateityp Getrennt, legen Sie im Feld Import beginnen in Zeile gegebenenfalls die Zeile fest, ab der die Daten aus der Textdatei eingefügt werden sollen und gehen Sie [Weiter].

Den Dateityp festlegen

6. Im nächsten Schritt legen Sie das Trennzeichen fest, in diesem Beispiel den Strichpunkt (Semikolon):

Page 16: MS Excel 2013 Profiwissen -   · PDF fileExcel 2013 Verwaltungsakademie Berlin Formeln und Funktionen Verwaltungsakademie Berlin Seite 3 Inhaltsverzeichnis 1 Funktionen

Excel 2013 Verwaltungsakademie Berlin Formeln und Funktionen

Verwaltungsakademie Berlin Seite 16

Das Trennzeichen festlegen

7. Klicken Sie auf [Weiter]. Im letzten Schritt haben Sie die Möglichkeit, das Datenformat der einzel-nen Spalten anzupassen. Das ist aber nur selten notwendig, denn Text-, Datum- und Zahlenwerte werden in der Regel von Excel richtig erkannt.

Datenformat und Zielbereich festlegen

8. Zum Schluss klicken Sie auf [Fertig_stellen].

9. Speichern Sie die Arbeitsmappe unter dem Namen Daten, Text in Spalten ab. Achten Sie dabei unbedingt auf den Dateityp Excel-Arbeitsmappe (*.xlsx)!

Page 17: MS Excel 2013 Profiwissen -   · PDF fileExcel 2013 Verwaltungsakademie Berlin Formeln und Funktionen Verwaltungsakademie Berlin Seite 3 Inhaltsverzeichnis 1 Funktionen

Excel 2013 Verwaltungsakademie Berlin Formeln und Funktionen

Verwaltungsakademie Berlin Seite 17

Nach der Textkonvertierung

2.3 Kategorie Datum & Zeit Excel speichert alle Datums- und Zeitwerte intern als serielle Zahlen ab. Mit den Datums- und Zeitfunkti-onen können Sie aus einer Datumszahl (z.B. 41467) die einzelnen Datumsangaben wie Jahreszahl (2013), Monatszahl (7), Tageszahl (12) ermitteln und umgekehrt.

Datumsangaben

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

Windows 7: Startmenü, Systemsteuerung, Region und Sprache, Register Formate, Schaltflä-che [Weitere_Einstellungen].

-Taste Windows 8.1: Suchleiste, Suchbegriff: Systemsteuerung. Im Fenster Alle Systemsteuerungs-elemente: Region, Register Formate, [Weitere_Einstellungen].

Nachfolgend sind einige Datumsfunktionen und deren Verwendung aufgelistet. Um das Beispiel-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 abge-

laufenen Bruchteil des Tages

(Formatierungsmöglichkeiten

beachten)

am Freitag, 12.07.2013

10:53 Uhr :

HEUTE()

incl. Bruchteil d. Tages:

JETZT()

41467

41467,45401

JAHR(Zahl) ermitteln die jeweiligen Werte am Freitag, 12.07.2013

Page 18: MS Excel 2013 Profiwissen -   · PDF fileExcel 2013 Verwaltungsakademie Berlin Formeln und Funktionen Verwaltungsakademie Berlin Seite 3 Inhaltsverzeichnis 1 Funktionen

Excel 2013 Verwaltungsakademie Berlin Formeln und Funktionen

Verwaltungsakademie Berlin Seite 18

Funktionsname und Syntax Erläuterung Beispiel Ergebnis

MONAT(Zahl)

TAG(Zahl)

STUNDE(Zahl)

MINUTE(Zahl)

SEKUNDE(Zahl)

als ganze Zahlen aus der fort-

laufenden Datumszahl

TAG(JETZT()) 12

WOCHENTAG(Zahl;Art) ermittelt aus einer Datumszahl

eine Zahl von 1 (Sonntag) bis 7

(Samstag), die den Wochentag

angibt.

WOCHENTAG("12.07.2009")

WOCHENTAG("12.07.2013 ")

ergibt 1

(Sonntag!)

ergibt 6

(Freitag)

DATUM(Jahr;Monat;Tag) ermittelt die fortlaufende Da-

tumszahl aus berechneten

Werten für Jahr, Monat und

Tag.

A6=2013; B6=7; C6=12

DATUM(A6+3;B6+2;C6-4)

formatiert: TT.MM.JJJJ

42621

08.09.2016

TAGE

(Enddatum;Ausgangsdatum)

Ermittelt die Anzahl der Tage in

einem Zeitintervall. Schaltjahre

werden dabei berücksichtigt. Zu

beachten: das Enddatum steht

vor dem Ausgangsdatum.

TAGE(“09.10.2013”;”15.07.2013”) 86

NETTOARBEITSTAGE

(Ausgangsdatum;Enddatum; Freie

Tage)

ermittelt die Anzahl der Ar-

beitstage in einem Zeitintervall.

Wochenenden (Samstag und

Sonntag) werden automatisch

nicht als Arbeitstage gerechnet.

Als Freie Tage sind Feierta-

ge/Ferien anzugeben, mehrere

freie Tage in {} zu setzen.

NETTOARBEITSTAGE

("04.11.2013";"22.11.2013";

{"06.11.2013";“08.11.2013“})

13

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 Excel-Optionen:

Excel-Optionen, Seite Add-Ins

Page 19: MS Excel 2013 Profiwissen -   · PDF fileExcel 2013 Verwaltungsakademie Berlin Formeln und Funktionen Verwaltungsakademie Berlin Seite 3 Inhaltsverzeichnis 1 Funktionen

Excel 2013 Verwaltungsakademie Berlin Formeln und Funktionen

Verwaltungsakademie Berlin Seite 19

Einige Datumsfunktionen (z.B. NETTOARBEITSTAGE, KA-LENDERWOCHE) 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 trigonometrische Funktio-nen und deren Verwendung aufgelistet:

Funktionsname

und Syntax Erläuterung Beispiel Ergebnis

ABS(Zahl) ermittelt den Absolutwert

einer Zahl

(Löscht Vorzeichen)

ABS(5)

ABS(- 5)

5

5

PI()

ohne Argumente!!!

liefert den Wert der Kreiszahl

PI.

PI() 3,14159265

REST(Zahl;Divisor) liefert den Rest (zum ganz-

zahligen Ergebnis) der Divisi-

on 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 Dezi-

malstellen mathematisch.

RUNDEN(31,446;2)

RUNDEN(31,443;2)

31,45

31,44

AUFRUNDEN(Zahl;

Anzahl_Stellen)

rundet die Zahl auf eine

bestimmte Anzahl an Dezi-

malstellen auf.

AUFRUNDEN(31,446;2)

AUFRUNDEN(31,443;2)

31,45

31,45

ABRUNDEN(Zahl;

Anzahl_Stellen)

rundet die Zahl auf eine

bestimmte Anzahl an Dezi-

malstellen 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

Page 20: MS Excel 2013 Profiwissen -   · PDF fileExcel 2013 Verwaltungsakademie Berlin Formeln und Funktionen Verwaltungsakademie Berlin Seite 3 Inhaltsverzeichnis 1 Funktionen

Excel 2013 Verwaltungsakademie Berlin Formeln und Funktionen

Verwaltungsakademie Berlin Seite 20

Funktionsname

und Syntax Erläuterung Beispiel Ergebnis

KÜRZEN(Zahl;Anzahl_Stellen) schneidet die Kommastellen

der Zahl ab und liefert als

Ergebnis entweder eine

ganze Zahl oder mit dem

zweiten Argument die Anzahl

der Nachkommastellen.

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üngen

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.

WURZEL(Zahl) liefert die Quadratwurzel von

Zahl.

WURZEL(81) 9

2.6 Kategorie Statistik Statistische Funktionen ermöglichen Ihnen aus Bereichen bestimmte Informationen herauszufiltern, z.B. die größte bzw. kleinste Zahl oder einen Durchschnittswert anzeigen zu lassen.

Nachfolgend sind einige statistische Funktionen und deren Verwendung aufgelistet:

Funktionsname

und Syntax Erläuterung Beispiel Ergebnis

ANZAHL(Wert1;Wert2;…)

Wert1;Wert2;… sind 1 bis maximal

30 Argumente

ermittelt, wie viele Zellen im

Bereich Zahlenwerte enthal-

ten sind.

ANZAHL2(Wert1;Wert2;…)

Wert1;Wert2;… sind 1 bis maximal

30 Argumente

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 For-

meln stehen, die die leere

Zeichenfolge ("") liefern.

Zellen, die Nullwerte enthal-

ten, werden nicht gezählt.

ZÄHLENWENN

(PrüfBereich;

Suchkriterium)

zählt die nichtleeren Zellen

eines Bereichs, deren Inhalte

mit dem Suchkriterium über-

einstimmen.

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

gen", "Pfirsiche" und "Äpfel"

ZÄHLENWENN(A3:A6;

"Äpfel")

2

Page 21: MS Excel 2013 Profiwissen -   · PDF fileExcel 2013 Verwaltungsakademie Berlin Formeln und Funktionen Verwaltungsakademie Berlin Seite 3 Inhaltsverzeichnis 1 Funktionen

Excel 2013 Verwaltungsakademie Berlin Formeln und Funktionen

Verwaltungsakademie Berlin Seite 21

Funktionsname

und Syntax Erläuterung Beispiel Ergebnis

MAX(Zahl1;Zahl2;…)

MIN(Zahl1;Zahl2;…)

MITTELWERT(Zahl1;Zahl2;

…)

Zahl1;Zahl2;… sind 1 bis maximal

30 Argumente

ermittelt den Maximalwert,

den Minimalwert, den Mit-

telwert aus den angegebenen

Zahlenwerten.

(Gilt nicht für Text-Werte!)

HÄUFIGKEIT

(Daten;Klassen)

gruppiert die Daten entspre-

chend der Klasseneinteilung

und liefert die Anzahl in jeder

Klasse als einspaltige Matrix.

Die Funktion muss als Mat-

rixformel eingegeben werden

(mit (Strg)+

(Shift)+ (Return)-Taste).

siehe Einsatzmöglichkeiten

RANG(Zahl;Bezug;Reihenfolge) Ermittelt den Rang, den eine

Zahl innerhalb einer Liste von

Zahlen einnimmt.

Über das Argument Reihen-

folge kann festgelegt werden,

ob der Rang so ermittelt wird,

als wäre die Liste in abstei-

gender Reihenfolge sortiert

(Wert = 0 oder keine Angabe)

oder in einer aufsteigenden

Reihenfolge (Wert =1).

A1:A10 enthält die Zahlen 10, 20, 30, 40,

…, 100

RANG(A3;A1:A10) bzw.

RANG(A3;A1:A10;0)

RANG(A3;A1:A10;1)

8

3

Einsatzmöglichkeiten

Die Funktion HÄUFIGKEIT können Sie z.B. verwenden, um aus den Ergebnissen einer Prüfung herauszu-lesen, wie viele Prüflinge bis 20 Punkte, bis 40 Punkte usw. erreicht haben (Datei Weitere Beispiele.xlsx, Arbeitsblatt Häufigkeit).

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

Page 22: MS Excel 2013 Profiwissen -   · PDF fileExcel 2013 Verwaltungsakademie Berlin Formeln und Funktionen Verwaltungsakademie Berlin Seite 3 Inhaltsverzeichnis 1 Funktionen

Excel 2013 Verwaltungsakademie Berlin Formeln und Funktionen

Verwaltungsakademie Berlin Seite 22

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

Häufigkeitstabelle

Wichtig ist:

1. Dass Sie vor dem Aufruf des Funktions-Assistenten die Ergebniszellen markieren (bei diesem Beispiel: F3:F7).

2. Dass Sie das Dialogfeld Funktionsargumente 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 Funkti-on HÄUFIGKEIT wieder ändern oder entfernen, müssen Sie deshalb zuerst alle Zellen des Ergebnis-Bereichs markieren, bevor Sie die Änderungen eingeben oder die Taste [Entf] drücken. (Weitere Hin-weise zum Umgang mit einer Matrixformel ab Seite 27.)

Page 23: MS Excel 2013 Profiwissen -   · PDF fileExcel 2013 Verwaltungsakademie Berlin Formeln und Funktionen Verwaltungsakademie Berlin Seite 3 Inhaltsverzeichnis 1 Funktionen

Excel 2013 Verwaltungsakademie Berlin Formeln und Funktionen

Verwaltungsakademie Berlin Seite 23

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

und Syntax Erläuterung Beispiel Ergebnis

WENN(Bedingung;

Dann_Wert;Sonst_Wert)

trägt in die Zelle, in der

die Funktion steht, den

Dann-Wert ein, wenn die

Bedingung zutrifft, an-

dernfalls den Sonst-Wert.

Provisionsberechnung:

(in F9 steht der Umsatz)

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

Als P

rüfu

ng (B

edin

gung

) in

WEN

N()

auch

: UND(Bedingung1;

Bedingung2;…)

Wahrheits-

wert1;Wahrheitswert2;… sind 1

bis maximal 30 Bedingungen

ergibt WAHR, wenn beide

Bedingungen zutreffen.

F8=BetrZugehörigk;F9=Umsatz

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

ODER(Bedingung1;

Bedingung2;…)

Wahrheits-

wert1;Wahrheitswert2;… sind 1

bis maximal 30 Bedingungen

ergibt WAHR, wenn min-

destens eine Bedingung

zutrifft.

FALSCH()

WAHR()

liefert den Wahrheitswert

FALSCH bzw. WAHR.

Einsatzmöglichkeiten

Die WENN-Funktion wird häufig in der Praxis eingesetzt. Sie können mit dieser Funktion die unterschiedlichsten Sachver-halte testen und in Abhängigkeit vom Er-gebnis verschiedene Berechnungen durch-führen, Texte ausgeben oder auf andere Zellinhalte verweisen.

Stellen Sie sich eine Projektliste vor, in der alle aktuellen Projekte mit dem jeweils dazugehörigen einzuhaltenden Enddatum aufgeführt sind (Datei Weitere Beispie-le.xlsx, Arbeitsblatt Projekte).

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

Priorität bei Projekten eintragen

Page 24: MS Excel 2013 Profiwissen -   · PDF fileExcel 2013 Verwaltungsakademie Berlin Formeln und Funktionen Verwaltungsakademie Berlin Seite 3 Inhaltsverzeichnis 1 Funktionen

Excel 2013 Verwaltungsakademie Berlin Formeln und Funktionen

Verwaltungsakademie Berlin Seite 24

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

Für Zelle C6 ist diese WENN-Funktion fol-gendermaß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 ("") erschei-nen.

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 Informationen. Nachfolgend sind einige Matrixfunktionen und deren Verwendung aufgelistet:

Funktionsname und Syntax Erläuterung

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 liefert einen Wert aus der glei-

chen Zeile aus einer von Ihnen angegebenen Spalte in der Tabelle. Einsatzmöglichkeiten Seite 30.

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 angegebenen Zeile in der Tabelle.

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 Funktion

VERGLEICH

VERGLEICH(Suchwert;Suchmatrix;Vergleichstyp) sucht innerhalb einer 1spaltigen oder 1zeiligen Suchmatrix (wie

bei SVERWEIS i. d. linken Spalte) nach Suchkriterium und liefert

dessen PositionsNr. Vergleichstyp wie bei SVERWEIS

SPALTE(Bezug)

ZEILE(Bezug)

liefert die Spaltennummer (statt Spalte "E" die Zahl 5!) bzw.

Zeilennummer von Bezug.

FORMELTEXT(Bezug) zeigt Formeln, die sich in der Bezugszelle befinden, als Text an

und dient damit der Dokumentation von Formeln und Funktio-

nen.

Page 25: MS Excel 2013 Profiwissen -   · PDF fileExcel 2013 Verwaltungsakademie Berlin Formeln und Funktionen Verwaltungsakademie Berlin Seite 3 Inhaltsverzeichnis 1 Funktionen

Excel 2013 Verwaltungsakademie Berlin Formeln und Funktionen

Verwaltungsakademie Berlin Seite 25

2.9 Kategorie Finanzmathematik Die Kategorie Finanzmathematik enthält umfangreiche Berechnungsfunktionen für z.B. Abschreibungen von Wirtschaftsgütern oder Zinsrechnungen. Nachfolgend sind einige finanzmathematischen Funktionen und deren Verwendung aufgelistet:

Funktionsname und Syntax Erläuterung

BW

(Zins;Zeitraum;Annuität;Endwert;Fälligkeit)

gibt den Barwert einer Investition zurück, also den gegenwärti-

gen Gesamtwert einer Reihe zukünftiger Zahlungen.

RMZ

(Zins;Zeitraum;Barwert;Endwert;Fälligkeit)

berechnet die regelmäßigen Zahlungen für einen bestimmten

Zeitraum bei konstantem Zinssatz.

ZW

(Zins;Zeitraum;Annuität;Barwert;Fälligkeit)

liefert den zukünftigen Wert (Endwert) einer Investition, basie-

rend auf konstanten Zahlungen und einem konstanten Zinssatz.

2.10 Datenbankfunktionen Alle Datenbankfunktionen in Excel besitzen die Syntax

FUNKTIONSNAME(Datenbank;Feld;Kriterien).

Die folgende Tabelle erläutert einige der Datenbankfunktionen zum Auswerten von Excel-Datenbanken:

Funktionsname und Syntax Erläuterung

DBANZAHL

(Datenbank;Feld;Kriterien)

zählt in einer bestimmten Spalte der Datenbank die Zellen, die

Zahlen enthalten und den angegebenen Bedingungen entspre-

chen.

DBMAX

(Datenbank;Feld;Kriterien)

ermittelt in einer bestimmten Spalte der Datenbank den größten

Zahlenwert, der die festgelegten Bedingungen erfüllt.

DBMIN

(Datenbank;Feld;Kriterien)

ermittelt in einer bestimmten Spalte der Datenbank den kleins-

ten Zahlenwert, der mit den festgesetzten Bedingungen über-

einstimmt.

DBMITTELWERT(Datenbank;Feld;

Kriterien)

bildet in einer bestimmten Spalte der Datenbank den Mittelwert

der Felder, die den angegebenen Suchkriterien entsprechen.

2.11 Verschachtelte Funktionen In bestimmten Fällen kann es erforderlich sein, eine Funktion als eines der Argumente einer anderen Funktion zu benutzen. Zum Beispiel verwendet die nachfolgende Formel eine verschachtelte Funktion MITTELWERT und vergleicht deren Ergebnis mit dem Wert 50.

Verschachtelte Funktion

Grenzen der Verschachtelungsebene

Eine Formel kann verschachtelte Funktionen in bis zu sieben Ebenen enthalten. Wenn Funktion B als Argument in Funktion A verwendet wird, ist Funktion B eine Funktion zweiter Ebene. Beispielsweise sind die Funktionen MITTELWERT und SUMME in o. a. Abbildung beide Funktionen der zweiten Ebene, weil

Page 26: MS Excel 2013 Profiwissen -   · PDF fileExcel 2013 Verwaltungsakademie Berlin Formeln und Funktionen Verwaltungsakademie Berlin Seite 3 Inhaltsverzeichnis 1 Funktionen

Excel 2013 Verwaltungsakademie Berlin Formeln und Funktionen

Verwaltungsakademie Berlin Seite 26

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 (Datei Weitere Bei-spiele.xlsx, Arbeitsblatt Verschachteln) soll der ge-rundete 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

Klicken Sie in das Feld Zahl

Page 27: MS Excel 2013 Profiwissen -   · PDF fileExcel 2013 Verwaltungsakademie Berlin Formeln und Funktionen Verwaltungsakademie Berlin Seite 3 Inhaltsverzeichnis 1 Funktionen

Excel 2013 Verwaltungsakademie Berlin Formeln und Funktionen

Verwaltungsakademie Berlin Seite 27

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

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_], sondern kli-cken 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_]!

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

2.12 Matrizen Eine besondere Formelart ist die so genannte Matrixformel. Wenn in benachbarten Zellen Formeln 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 Zahlen in der B- und in der C-Spalte als Euro-Währung (Kontextmenü, Befehl Zellen formatieren, Kategorie Währung) und spei-chern Sie die Mappe unter dem Namen Matrix ab:

Page 28: MS Excel 2013 Profiwissen -   · PDF fileExcel 2013 Verwaltungsakademie Berlin Formeln und Funktionen Verwaltungsakademie Berlin Seite 3 Inhaltsverzeichnis 1 Funktionen

Excel 2013 Verwaltungsakademie Berlin Formeln und Funktionen

Verwaltungsakademie Berlin Seite 28

Die Daten und der markierte Bereich C2:C8

Mit der Tastenkombination (Strg)+(F1) oder mit einem Maus-Doppelklick auf einen Regis-ternamen (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 Matrixformel nur einmal eingegeben werden. Dies erspart in umfangreichen Tabellen lästige Kopierarbeiten:

1. Markieren Sie die Zellen C2 bis C8.

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

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

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

{=A2:A8*B2:B8}

Die Ergebnisse

Geschweifte Klammern sind eine Besonderheit der Matrixformeln.

Page 29: MS Excel 2013 Profiwissen -   · PDF fileExcel 2013 Verwaltungsakademie Berlin Formeln und Funktionen Verwaltungsakademie Berlin Seite 3 Inhaltsverzeichnis 1 Funktionen

Excel 2013 Verwaltungsakademie Berlin Formeln und Funktionen

Verwaltungsakademie Berlin Seite 29

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:

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 ein-zelne 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.

Page 30: MS Excel 2013 Profiwissen -   · PDF fileExcel 2013 Verwaltungsakademie Berlin Formeln und Funktionen Verwaltungsakademie Berlin Seite 3 Inhaltsverzeichnis 1 Funktionen

Excel 2013 Verwaltungsakademie Berlin Formeln und Funktionen

Verwaltungsakademie Berlin Seite 30

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

2.13 Übung Ermitteln Sie den Gewinn als Differenz zwischen Umsatz und Kosten für die nachfolgenden Angaben mit Hilfe einer Matrixformel (Datei Matrix Übung Rohdaten.xlsx):

Monat Umsatz Kosten

Januar 566000 477222

Februar 499888 350000

März 500788 470888

April 412000 385555

Mai 500012 499999

Juni 540000 512000

Juli 499881 350666

August 510200 370898

September 512896 450012

Oktober 852145 758936

November 755222 630587

Dezember 766888 690898

2.14 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 Betriebsrat sollen alle Resturlaubstage, die über eine Anzahl von 10 Tagen hinausgehen, mit einem 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.

Die Vorbereitung

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

Page 31: MS Excel 2013 Profiwissen -   · PDF fileExcel 2013 Verwaltungsakademie Berlin Formeln und Funktionen Verwaltungsakademie Berlin Seite 3 Inhaltsverzeichnis 1 Funktionen

Excel 2013 Verwaltungsakademie Berlin Formeln und Funktionen

Verwaltungsakademie Berlin Seite 31

Die vorliegenden Daten

1. Öffnen Sie die Datei Verweis Rohdaten.xlsx oder geben Sie die Daten in ein leeres Tabellenblatt ein.

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

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

4. Führen Sie zu diesem Zweck einen Doppelklick auf das Blattregister Tabelle1 aus. Der Name Tabelle1 ist mar-kiert.

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 markier-ten Bereich und wählen Sie im Kontextmenü den Be-fehl Namen definieren. Oder klicken Sie auf der Regis-terkarte Formeln in der Gruppe Definierte Namen auf das Symbol Namen definieren. Das rechts stehende Di-alogfeld 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. Fügen Sie Ihrer Arbeitsmappe ein leeres Tabellenarbeitsblatt zu, indem Sie unten links auf das Plus-zeichen klicken (siehe Bild oben neben Schritt 4.).

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

Die Daten auf der Tabelle2

Page 32: MS Excel 2013 Profiwissen -   · PDF fileExcel 2013 Verwaltungsakademie Berlin Formeln und Funktionen Verwaltungsakademie Berlin Seite 3 Inhaltsverzeichnis 1 Funktionen

Excel 2013 Verwaltungsakademie Berlin Formeln und Funktionen

Verwaltungsakademie Berlin Seite 32

Nun sollen die Namen der Mitarbeiter mit Hilfe der Funktion SVERWEIS neben den entsprechen-den 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 Funktions-bibliothek.

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ämlich die Personal-nummer.

9. Setzen Sie nun den Cursor in das Feld für das Argument Matrix, z.B. über die (ÿ)-Tabtaste. Darun-ter ist die Verweistabelle zu verstehen, aus der die Daten entnommen werden. Da Sie den Matrix-bereich 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 aus dem Tabellenarbeitsblatt Mitarbeiterdaten holen soll. Der gefragte Name steht in der zweiten Spalte der Matrix. Aus diesem Grunde ist die Eingabe der Ziffer 2 erforderlich:

Page 33: MS Excel 2013 Profiwissen -   · PDF fileExcel 2013 Verwaltungsakademie Berlin Formeln und Funktionen Verwaltungsakademie Berlin Seite 3 Inhaltsverzeichnis 1 Funktionen

Excel 2013 Verwaltungsakademie Berlin Formeln und Funktionen

Verwaltungsakademie Berlin Seite 33

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 Dezimalstellen (Kontextmenü, Befehl Zellen formatieren, Kategorie Währung).

Page 34: MS Excel 2013 Profiwissen -   · PDF fileExcel 2013 Verwaltungsakademie Berlin Formeln und Funktionen Verwaltungsakademie Berlin Seite 3 Inhaltsverzeichnis 1 Funktionen

Excel 2013 Verwaltungsakademie Berlin Formeln und Funktionen

Verwaltungsakademie Berlin Seite 34

Verschachtelte Funktionen

Als Nächstes ermitteln Sie den Auszahlungsbetrag. Dieser errechnet sich durch Multiplikation des Stun-densatzes 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 Arbeits-blatt 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 multipli-zieren.

• 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.15 Zellschutz einrichten Da es sich um recht komplizierte Formeln handelt, sollten diese vorsichtshalber vor dem Überschreiben geschützt werden. Legen Sie dabei aber vorher fest, welche Zellen weiterhin geändert werden dürfen. Dazu gehen Sie folgendermaßen vor:

1. Markieren Sie im aktuellen Blatt Tabelle2 den Bereich A2 bis A6. Später sollen in Ihrem Ar-beitsblatt nur noch diese Zellen über-schreibbar sein.

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

3. Klicken Sie das Register Schutz an.

4. Mit Ausnahme der Zellen A2 bis A6 sollen keine Änderungen in Ihrem Arbeitsblatt mehr eingegeben werden können. Deaktivieren Sie deshalb das Kontrollkästchen Gesperrt und klicken Sie auf [_OK_].

Das Register Schutz

Der Schutz des Blattes bezieht sich auch auf die Formatierungen und Anordnung der Zellen.

Page 35: MS Excel 2013 Profiwissen -   · PDF fileExcel 2013 Verwaltungsakademie Berlin Formeln und Funktionen Verwaltungsakademie Berlin Seite 3 Inhaltsverzeichnis 1 Funktionen

Excel 2013 Verwaltungsakademie Berlin Formeln und Funktionen

Verwaltungsakademie Berlin Seite 35

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 Änderungen auf die Schaltfläche Blatt schützen.

Dialogfeld Blatt schützen

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

7. Klicken Sie auf [_OK_].

Hinweis: 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 erneut eintra-gen.

9. Klicken Sie auf [_OK_].

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 dennoch versu-chen, gesperrte Zellen zu überschreiben, wird dies von Excel nicht akzeptiert. Sie erhalten den folgenden Hinweis:

Hinweis

2.16 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 aufheben.

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.

2.17 Arbeitsmappe schützen Neben dem Blattschutz besteht auch die Option, die gesamte Arbeitsmappe mit einem Kennwort zu schützen. Sie können dabei wählen, ob nur für das Bearbeiten der Arbeitsmappe die Eingabe des Kenn-worts erforderlich sein soll oder auch für das Öffnen:

Page 36: MS Excel 2013 Profiwissen -   · PDF fileExcel 2013 Verwaltungsakademie Berlin Formeln und Funktionen Verwaltungsakademie Berlin Seite 3 Inhaltsverzeichnis 1 Funktionen

Excel 2013 Verwaltungsakademie Berlin Formeln und Funktionen

Verwaltungsakademie Berlin Seite 36

1. Öffnen Sie das Datei-Menü, klicken Sie auf Speichern unter, den Speicherort (z.B. Computer), Durchsuchen und wählen Sie den entsprechenden Ordner.

2. Im Explorer-Fenster Speichern unter klicken Sie auf [Tools] und wählen im Untermenü Allgemeine Optionen.

3. Im Dialogfensterfenster Allgemeine Optionen haben Sie nun folgende Möglichkeiten:

• Kennwort zum Öffnen:

Zum Öffnen der Arbeitsmappe ist die Eingabe des Kennworts nötig. Die Benutzerinnen und Benutzer sind dann berechtigt, Änderungen in der Arbeitsmappe vorzunehmen, es sei denn Sie vergeben zusätzlich ein Kennwort zum Ändern.

Kennwort vergeben

• Kennwort zum Ändern:

Die Arbeitsmappe kann ohne Eingabe des Kennworts zwar geöffnet, nicht aber geändert werden.

• Schreibschutz empfehlen:

Bei aktiviertem Kontrollfeld erscheint beim Öffnen der Arbeitsmappe ein Dialogfenster, in dem Sie auswählen können, ob Sie die Arbeitsmappe schreibgeschützt oder zur Bearbeitung öffnen möchten.

Schließen Sie Ihre Eingaben mit [_OK_] ab.

4. Bestätigen Sie das Kennwort bzw. die Kennwörter in den folgenden Dialogfeldern durch erneute Eingabe und jeweils Klick auf [_OK_] und klicken Sie im Dialogfeld Speichern unter auf [Speichern].

Kennwort ändern oder Kennwortschutz aufheben

Wenn Sie das Kennwort ändern oder den Kennwortschutz aufheben möchten, öffnen Sie zunächst die geschützte Arbeitsmappe unter Eingabe aller geforderten Kennwörter und dann das Dialogfeld Allge-meine Optionen (Datei-Menü, Speichern unter, Computer, Durchsuchen, [Tools], Allgemeine Optio-nen). Ändern Sie das Kennwort bzw. die Kennwörter im jeweiligen Eingabefeld ab oder löschen Sie die Eintragung(en), klicken Sie auf [_OK_] und im Dialogfeld Speichern unter auf [Speichern] ohne den Datei-namen zu ändern. Bestätigen Sie das nachfolgende Dialogfeld mit [_Ja_].

Datei ersetzen? Ja

Arbeitsmappenstruktur und -fenster schützen

Wie auch der bereits erläuterte Zellschutz verhindern Schutz der Arbeitsmappenstruktur und Schutz des Arbeitsmappenfensters, dass bestimmte Elemente von anderen Nutzern geändert werden können. So verhindert der Schutz der Struktur z.B. das Verschieben, Kopieren, Löschen, Einfügen, Aus- und Einblen-den von Arbeitsblättern oder die Änderung ihres Namens, der Schutz des Fensters beispielsweise das

Page 37: MS Excel 2013 Profiwissen -   · PDF fileExcel 2013 Verwaltungsakademie Berlin Formeln und Funktionen Verwaltungsakademie Berlin Seite 3 Inhaltsverzeichnis 1 Funktionen

Excel 2013 Verwaltungsakademie Berlin Formeln und Funktionen

Verwaltungsakademie Berlin Seite 37

Ändern der Größe und Position von Fenstern in der Arbeitsmappe, wenn diese geöffnet ist. Und so rich-ten Sie den Schutz ein:

1. Wechseln Sie im Datei-Menü zur Seite Informa-tionen, klicken Sie auf die Schaltfläche Arbeits-mappe schützen und wählen Sie den Befehl Ar-beitsmappenstruktur schützen.

2. Aktivieren Sie das Kontrollfeld Struktur und/oder Fenster, je nachdem, welchen Schutz Sie aktivieren möchten.

Struktur und Fenster schützen

Arbeitsmappenstruktur schützen

3. Wenn Sie verhindern möchten, dass andere Benutzer den Struktur- und Fensterschutz aufheben können, geben Sie ein Kennwort und bestätigen Sie alles mit [_OK_].

4. Falls Sie ein Kennwort vergeben haben, wiederholen Sie es im sich danach öffnenden Dialogfeld und bestätigen Sie mit [_OK_].

5. Speichern Sie die Arbeitsmappe ab.

Um das Kennwort zu ändern, zu löschen oder den Schutz aufzuheben, klicken Sie im Datei-Menü auf der Seite Informationen auf die Schaltfläche Arbeitsmappe schützen und wählen Sie den Befehl Arbeitsmappenstruktur schützen. Wenn Sie ein Kennwort vergeben haben, müssen Sie es eingeben und mit [_OK_] bestätigen. Der Schutz ist nun erst einmal aufgehoben und kann erneut mit einem neuen oder ohne Kennwort aktiviert werden.

2.18 Übung 1 Das Unternehmen des vorangegangenen Beispiels zahlt am Jahresende eine Sonderzahlung an die Mit-arbeiter aus. Die Höhe der Zahlung ist abhängig vom jeweiligen Alter der Beschäftigten. Mitarbeiter, die älter sind als 40 Jahre, erhalten eine Prämie von 1000,- €. Die jüngeren müssen sich mit 500,- € be-gnügen.

Erstellen Sie eine Prämienliste, in der Sie auf die Matrix Personaldaten mittels SVERWEIS zugreifen.

Die Liste soll folgende Daten enthalten: • Personal-Nr.

• Geb-Datum

• Name

• Sonderzahlung.

2.19 Übung 2 1. Öffnen Sie die Datei Betriebliches Vorschlagswesen6.xlsx. Im Bereich G7:J11 wurden die Prämien

mit dem konstanten Wert 2,5% der Einsparungsbeträge berechnet:

Page 38: MS Excel 2013 Profiwissen -   · PDF fileExcel 2013 Verwaltungsakademie Berlin Formeln und Funktionen Verwaltungsakademie Berlin Seite 3 Inhaltsverzeichnis 1 Funktionen

Excel 2013 Verwaltungsakademie Berlin Formeln und Funktionen

Verwaltungsakademie Berlin Seite 38

Die Einsparungen und die Prämien

2. Nun sollen die Prämien neu berechnet werden. Dazu ist eine gestaf-felte Prozenttabelle zu verwenden. Mit Hilfe des Füllbereich-Symbols

auf der Registerkarte Start in der Gruppe Bearbeiten erstellen Sie eine Tabelle nach dem nachfolgenden Bildschirm-Foto:

Symbol Füllbereich

Die Verweis-Tabelle

3. Unter Verwendung einer Verweis-Funktion berechnen Sie die Prämien für die Mitarbeiter neu.

Page 39: MS Excel 2013 Profiwissen -   · PDF fileExcel 2013 Verwaltungsakademie Berlin Formeln und Funktionen Verwaltungsakademie Berlin Seite 3 Inhaltsverzeichnis 1 Funktionen

Excel 2013 Verwaltungsakademie Berlin Formeln und Funktionen

Verwaltungsakademie Berlin Seite 39

2.20 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:

Kategorie Benutzerdefiniert

Dort können Sie in die Zeile Typ die entsprechenden Formatierungszeichen anhand der von Excel ver-wendeten Formatcodes eintragen.

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 Komma wird die Lage des Dezimalkommas und danach die Anzahl der Dezimalstellen festgelegt. Werte, deren Nachkommastellen nicht vollständig sind, werden in der Anzeige gerundet.

# oder #.### Das #-Zeichen dient ebenfalls als Platzhalter für Ziffern aber nur für die Anordnung signifi-kanter 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.

Page 40: MS Excel 2013 Profiwissen -   · PDF fileExcel 2013 Verwaltungsakademie Berlin Formeln und Funktionen Verwaltungsakademie Berlin Seite 3 Inhaltsverzeichnis 1 Funktionen

Excel 2013 Verwaltungsakademie Berlin Formeln und Funktionen

Verwaltungsakademie Berlin Seite 40

Formatcode Erläuterung

[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 positive Zahlen in Blau, negative Zahlen in Rot, eine Null in grün und Text in der Farbe Zyan an (nähere Erläuterun-gen 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 Be-dingung setzt sich aus einem Vergleichsoperator und einem Wert zusammen. 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 zwischen An-fü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.

Page 41: MS Excel 2013 Profiwissen -   · PDF fileExcel 2013 Verwaltungsakademie Berlin Formeln und Funktionen Verwaltungsakademie Berlin Seite 3 Inhaltsverzeichnis 1 Funktionen

Excel 2013 Verwaltungsakademie Berlin Formeln und Funktionen

Verwaltungsakademie Berlin Seite 41

Formatcode Erläuterung

Zur Anzeige von Leerzeichen und der folgenden Sonderzeichen $ - + / ( ) : ! ^ & ' ~ { } = < > 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 ange-zeigt. Sollen bestimmte Zeichen immer mit dem eingegebenen Text angezeigt werden, set-zen 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 "auszu-punktieren". Zahlen stehen besser rechts in den Zellen, *-0 füllt beispielsweise 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

Page 42: MS Excel 2013 Profiwissen -   · PDF fileExcel 2013 Verwaltungsakademie Berlin Formeln und Funktionen Verwaltungsakademie Berlin Seite 3 Inhaltsverzeichnis 1 Funktionen

Excel 2013 Verwaltungsakademie Berlin Formeln und Funktionen

Verwaltungsakademie Berlin Seite 42

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..., Register Zahlen.

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

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

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

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

Sie können bis zu vier Abschnitte Format-codes angeben. Die Abschnitte, die durch Semikolons getrennt werden, definieren die Formate für positive Zahlen, negative Zahlen, Nullwerte und Text (in dieser Rei-henfolge).

Benutzerdefiniertes Zahlenformat

Wenn Sie nur zwei Abschnitte angeben, wird der erste Abschnitt für positive Zahlen und Nullen verwen-det, der zweite für negative Zahlen. Wenn Sie nur einen Abschnitt angeben, verwenden alle Zahlen die-ses Format. Wenn Sie für einen Abschnitt nichts angeben, müssen Sie das abschließende Semikolon für den betreffenden Abschnitt dennoch setzen. Werte dieses Abschnitts 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 Zahlen er-scheinen in Blau mit Tausendertrennzeichen und 2 Dezimalstellen, negative Zahlen in Rot mit vorange-stelltem 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

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

Operator Erläuterung

< ist kleiner als

> ist größer als

<= ist kleiner oder gleich

Page 43: MS Excel 2013 Profiwissen -   · PDF fileExcel 2013 Verwaltungsakademie Berlin Formeln und Funktionen Verwaltungsakademie Berlin Seite 3 Inhaltsverzeichnis 1 Funktionen

Excel 2013 Verwaltungsakademie Berlin Formeln und Funktionen

Verwaltungsakademie Berlin Seite 43

>= ist größer oder gleich

= ist gleich (entspricht)

<> ist ungleich (entspricht nicht)

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

[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, beeinflussen nur die Zahlenformatierung und Schriftfarbe. Wenn Sie Hintergrundfarben, Rahmen und Schrift-attribute vom Wert einer Zelle abhängig machen möchten, verwenden Sie den Befehl Beding-te Formatierung im Menüband auf der Registerkarte Start in der Gruppe Formatvorlage (Seite 44).

Benutzerdefiniertes Zahlenformat kopieren

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

Zum Kopieren eines Formates müssen beide Arbeitsmappen geöffnet sein. Gehen Sie anschließ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 Tastenkombination (Strg)+(F6)) und markieren Sie dort eine oder mehrere Zellen.

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

4. Aktivieren Sie in dem rechts ste-henden Dialogfeld 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 gewünsch-te Format hat. Klicken Sie auf das Symbol Format übertragen auf der Registerkarte 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. Wenn Sie doppelt auf dieses Symbol klicken, können Sie auch gleich mehrere getrennte Zellen/Bereiche "überpinseln". Mit der (Esc)-Taste beenden Sie dann diese Form der Übertragung.

Page 44: MS Excel 2013 Profiwissen -   · PDF fileExcel 2013 Verwaltungsakademie Berlin Formeln und Funktionen Verwaltungsakademie Berlin Seite 3 Inhaltsverzeichnis 1 Funktionen

Excel 2013 Verwaltungsakademie Berlin Formeln und Funktionen

Verwaltungsakademie Berlin Seite 44

Definieren Sie Ihre Zahlenformate in einer Mustervorlage oder legen Sie eine neue Standard-Arbeitsmappe für Excel mit den benutzerdefinierten Zahlenformaten an.

Benutzerdefiniertes Zahlenformat löschen

Alle Zahlenformate, die Sie selber definiert haben, zeigt Ihnen Excel im Fenster Zellen formatieren 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 benutzerdefinier-ten Zahlenformate nicht völlig unüberschaubar wird, sollten Sie nicht mehr benötigte eigene Zahlenfor-mate 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. Im Register Zahlen wählen Sie in der Kategorie Benutzerdefiniert das Zahlenformat 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 Mehrfachmarkierung in dem Dialogfeld nicht zu.

2.21 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 unterschreiten.

Bedingte Formatierung anwenden

Beispiel 1 Angenommen, Sie haben eine Prüfungsliste mit verschiedenen Notengruppen erstellt, die Gesamtnote darf natürlich nur eine Note zwischen 1 und 6 erge-ben.

Um nun zu prüfen, ob die Gesamtprüfungsnote dem Notenschlüssel entspricht, können Sie die Spalte mit einer bedingten Formatierung belegen, 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 mehrere Bedingungen für die Formatierung festlegen. Zusammen mit dem Grund-format stehen Ihnen damit diverse verschieden 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.

Page 45: MS Excel 2013 Profiwissen -   · PDF fileExcel 2013 Verwaltungsakademie Berlin Formeln und Funktionen Verwaltungsakademie Berlin Seite 3 Inhaltsverzeichnis 1 Funktionen

Excel 2013 Verwaltungsakademie Berlin Formeln und Funktionen

Verwaltungsakademie Berlin Seite 45

2. Rufen Sie auf: Registerkarte Start, Gruppe Formatvorlagen, 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 wählen Sie aus und tragen ein:

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

6. Legen Sie in den Registern 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 be-dingte 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 Hintergrund formatiert.

Beispiel 2 Für eine Prüfungsliste (Datei Weitere Beispiele.xlsx, Arbeitsblatt Noten) möchten wir die Zellen der Prüflinge, die nicht bestanden haben, farblich hervorheben (weniger als 50% richtig beantwortet) und

Page 46: MS Excel 2013 Profiwissen -   · PDF fileExcel 2013 Verwaltungsakademie Berlin Formeln und Funktionen Verwaltungsakademie Berlin Seite 3 Inhaltsverzeichnis 1 Funktionen

Excel 2013 Verwaltungsakademie Berlin Formeln und Funktionen

Verwaltungsakademie Berlin Seite 46

die Zellen der Prüflinge, die überdurchschnittlich 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 be-ginnen. 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öglicherweise nötige Angabe von relativen, absoluten und gemischten Bezügen mit oder ohne $-Zeichen.

Erstellen Sie eine weitere Regel für die Prüflinge, die mehr als 80% richtig beantwortet haben. Über das vorher gezeigte Untermenü des Symbols Bedingte Formatierung können Sie dann die Regeln verwal-ten:

Eine Formel eingeben

Regeln verwalten

Prüfungsliste mit Hervorhebungen

Page 47: MS Excel 2013 Profiwissen -   · PDF fileExcel 2013 Verwaltungsakademie Berlin Formeln und Funktionen Verwaltungsakademie Berlin Seite 3 Inhaltsverzeichnis 1 Funktionen

Excel 2013 Verwaltungsakademie Berlin Formeln und Funktionen

Verwaltungsakademie Berlin Seite 47

Zellen mit bedingten Formatierungen suchen

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

1. Drücken Sie die (F5)-Taste oder die Tastenkombination (Strg)+(G). Sie können aber auch im Untermenü der Schaltfläche Suchen und Auswählen (Registerkarte Start, Gruppe Bearbeiten) den Befehl Gehe zu wählen.

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

3. Um Zellen mit

Bedingte Formate auswählen

• identischen bedingten Formatierungen zu

finden, klicken Sie auf den Optionsbutton Gleiche.

Identische Formatierungen suchen

• beliebigen bedingten Formatierungen zu finden, klicken Sie auf den Optionsbutton Alles.

Beliebige Formatierungen suchen

Datenillustration mit der bedingten Formatierung

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

1. Markieren Sie den entsprechenden Bereich.

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

Datenbalken zeigen einen farbigen Datenbal-ken 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 auf-zeigen.

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

Katalog mit Livevorschau

Page 48: MS Excel 2013 Profiwissen -   · PDF fileExcel 2013 Verwaltungsakademie Berlin Formeln und Funktionen Verwaltungsakademie Berlin Seite 3 Inhaltsverzeichnis 1 Funktionen

Excel 2013 Verwaltungsakademie Berlin Formeln und Funktionen

Verwaltungsakademie Berlin Seite 48

Bedingte Formatierung mit farbigen Datenbalken

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

Page 49: MS Excel 2013 Profiwissen -   · PDF fileExcel 2013 Verwaltungsakademie Berlin Formeln und Funktionen Verwaltungsakademie Berlin Seite 3 Inhaltsverzeichnis 1 Funktionen

Excel 2013 Verwaltungsakademie Berlin Formeln und Funktionen

Verwaltungsakademie Berlin Seite 49

2.23 Übung 1. Öffnen Sie die Excel-Datei Index Rohdaten und speichern Sie die

Arbeitsmappe unter dem Namen Index ab.

2. Kopieren Sie in den Bereich A4:A15 die aktuelle Jahreszahl.

3. Füllen Sie mit der Maus den Bereich B4:B15 mit den Monatsna-men aus: Klicken Sie in die Zelle B4, also auf das Wort Januar. Zei-gen Sie auf das Ausfüllkästchen in der unteren rechten Ecke des Zellcursors und ziehen Sie bei gedrückter linker Maustaste die Maus bis zur Zelle B15. Rechts vom Mauszeiger erscheint ein QuickInfo mit den Monatsnamen.

4. Der Tabellenbereich von A4:D15 soll den Bereichsnamen Daten bekommen.

Ausfüllen

5. In die Zelle F5 ist mit Hilfe der Textverknüpfung ("für " und Leerzeichen " ") und der Index-Funktion der Monatsname und die Jahreszahl aus dem Bereich Daten zu übernehmen. In der Zelle G1 ist die Monatszahl (hier 9) eingetragen und dies ist gleichzeitig die Zeilennummer des Bereichs. Damit Sie die Formel kopieren können, wird die Zelle absolut adressiert ($G$1). Ein benannter Bereich (hier Daten) ist in Excel automatisch absolut. Der Monatsname steht in der Spalte 2 und die Jahreszahl in der Spalte 1. Geben Sie in F5 ein:

="für "&INDEX(Daten;$G$1;2)&" "&INDEX(Daten;$G$1;1)

6. Tragen Sie in die Zelle G1 eine andere Monatszahl ein und überprüfen Sie die Änderung in der Zelle F5.

7. Erstellen Sie in den Zellen F7 und F8 die Formeln für die Verbrauchswerte des betreffenden Monats nach dem vorherigen Bildschirmfoto. Dafür können Sie einen Teil der Formel aus der Zelle F5 ver-wenden.

8. Speichern Sie die Arbeitsmappe und schließen Sie die Datei.

2.24 Hyperlinks Hyperlinks sind Verknüpfungen in einer Datei, über die Sie durch Klicken zum festgelegen Ziel gelangen. Das Ziel kann eine Webseite, eine Zelle oder ein Name in der aktuellen Arbeitsmappe oder eine andere Datei sein. Der Hyperlink selbst kann Text oder ein Bild sein.

Hyperlink zu einer Zelle in der aktuellen Arbeitsmappe einfügen

1. Markieren Sie die Zelle bzw. das Objekt, die bzw. das Sie als Hyperlink formatieren möchten.

2. Klicken Sie auf der Registerkarte Einfügen in der Gruppe Links auf die Schaltfläche Link.

3. Aktivieren Sie im nachfolgenden Dialogfeld die Schaltfläche Aktuelles Dokument. Geben Sie die Zelle ein, zu der der Hyperlink führen soll (z.B. A1) und markieren Sie in der Liste den Zellbezug, falls Ihre Datei wie in unserem Beispiel aus mehreren Tabellenarbeitsblät-tern besteht (z.B. Tabelle2):

Page 50: MS Excel 2013 Profiwissen -   · PDF fileExcel 2013 Verwaltungsakademie Berlin Formeln und Funktionen Verwaltungsakademie Berlin Seite 3 Inhaltsverzeichnis 1 Funktionen

Excel 2013 Verwaltungsakademie Berlin Formeln und Funktionen

Verwaltungsakademie Berlin Seite 50

Einen Hyperlink zur Zelle A1 in der Tabelle2 im aktuellen Arbeitsblatt erstellen

4. Unter Anzuzeigender Text sehen Sie, welcher Text im Dokument angezeigt wird. Sie können ihn aber auch überschreiben.

5. Wenn Sie möchten, können Sie noch über die Schaltfläche [QuickInfo] einen individuellen Text eingeben, der als QuickInfo angezeigt wird, wenn Sie mit der Maus auf den Hyperlink zeigen.

6. Bestätigen Sie Ihre Auswahl mit [_OK_].

Hyperlink zu einer Webseite

Falls Sie im Text eine Internet-Adresse eingeben, die mit www. oder http:// beginnt, wird die Adresse automatisch blau und unterstrichen formatiert. Zeigen Sie bei gedrückter (Strg)-Taste mit der Maus darauf, dann verwandelt sich der Mauszeiger in eine Hand:

Wenn Sie Zugang zum Internet haben, können Sie nun direkt zu dieser Adresse surfen.

Über den Weg Registerkarte Datei (Datei-Menü), Schaltfläche , Dokumentprüfung, [AutoKor-rektur-Optionen…], Register AutoFormat während der Eingabe stellen Sie ein, ob eine Internet-Adresse erkannt und als Hyperlink automatisch dargestellt werden soll.

Internet-Adresse erkennen

Page 51: MS Excel 2013 Profiwissen -   · PDF fileExcel 2013 Verwaltungsakademie Berlin Formeln und Funktionen Verwaltungsakademie Berlin Seite 3 Inhaltsverzeichnis 1 Funktionen

Excel 2013 Verwaltungsakademie Berlin Formeln und Funktionen

Verwaltungsakademie Berlin Seite 51

Alternativ können Sie einen Hyperlink zu einer Webseite aber auch über das nachfolgende Dialogfeld einfügen: Aktivieren Sie die Schaltfläche Datei oder Webseite und geben Sie im Feld Adresse die Inter-net-Adresse ein (z.B. www.microsoft.de). Excel ergänzt das Übertragungsprotokoll http:// automatisch und bietet Ihnen eventuell bereits beim Eintippen mögliche Adressen an (im Bild an der hellblauen Her-vorhebung zu erkennen):

Einen Hyperlink zu einer Webseite erstellen

Hyperlink zu einer anderen Datei einfügen

Einen Hyperlink zu einer anderen Datei fügen Sie folgendermaßen ein:

1. Markieren Sie die Zelle bzw. das Objekt, die bzw. das Sie als Hyperlink formatieren möchten und öffnen Sie das Dialogfeld Hyperlink einfügen (Registerkarte Einfügen, Gruppe Links, Schaltfläche Link).

2. Aktivieren Sie die Schaltfläche Datei oder Webseite und wählen Sie über das Listenfeld Suchen in den Ordner aus, in dem sich die gewünschte Datei befindet.

3. Markieren Sie nun in der Liste die Datei und ändern Sie gegebenenfalls den anzuzeigenden Namen ab.

Einen Hyperlink zu einer anderen Datei erstellen

4. Über die Schaltfläche [Textmarke] können Sie noch eine Zelle oder einen Namen in der ausgewähl-ten Datei als konkretes Ziel festlegen. Bestätigen Sie Ihre Angaben mit [_OK_].

Page 52: MS Excel 2013 Profiwissen -   · PDF fileExcel 2013 Verwaltungsakademie Berlin Formeln und Funktionen Verwaltungsakademie Berlin Seite 3 Inhaltsverzeichnis 1 Funktionen

Excel 2013 Verwaltungsakademie Berlin Formeln und Funktionen

Verwaltungsakademie Berlin Seite 52

Hyperlink bearbeiten und löschen

Um einen Hyperlink zu bearbeiten, klicken Sie ihn mit der rechten Maustaste an und wählen im Kontextmenü den Befehl Hyperlink bear-beiten. Nehmen Sie im Dialogfeld Hyperlink bearbeiten die Änderungen vor und bestätigen Sie sie mit [_OK_].

Zum Löschen wählen Sie im Kontextmenü den Befehl Hyperlink entfer-nen.

Kontextmenü (Ausschnitt)

Page 53: MS Excel 2013 Profiwissen -   · PDF fileExcel 2013 Verwaltungsakademie Berlin Formeln und Funktionen Verwaltungsakademie Berlin Seite 3 Inhaltsverzeichnis 1 Funktionen

Excel 2013 Verwaltungsakademie Berlin Formeln und Funktionen

Verwaltungsakademie Berlin Seite 53

3 Arbeiten mit Datenlisten 3.1 Allgemeines zum Aufbau einer Datenliste Um Komplikationen beim Filtern und Sortieren von Daten, bei Berechnungen von Teilergebnissen und Verwendung der Pivot-Tabelle zu vermeiden, sind beim Erstellen von Listen folgendes zu beachten: In der Datenliste sollte jeder Spalte eine Spaltenüberschrift (Feldname) zugewiesen werden.

Feldnamen/Spaltenüberschrift

Weisen Sie Zahlen, die mit einer 0 (Null) beginnen (wie Postleitzahlen oder Telefonnummern), immer ein entsprechendes Format zu (kein Zahlenformat!), da Excel sonst die führende 0 löscht. Dazu markieren Sie den Bereich, der die Zahlen enthält, wählen im Kontextmenü den Befehl Zellen formatieren und im Dialogfeld Zellen formatieren die Kategorie Text oder im Fall von Postleitzahlen die Kategorie Sonderformat und dort den Typ Postleitzahl.

Vermeiden Sie es, die Liste durch Leerspalten oder -zeilen (Ausnahme: Leerzeile vor der Summenzei-le), beispielsweise aus optischen Gründen, zu "teilen".

Leerzeilen oder -spalten vermeiden

Durch das Einfügen von Leerspalten oder -zeilen erschweren Sie sich die Handhabung von Auswertun-gen erheblich, bzw. durch eine Unachtsamkeit (z.B. fehlendes oder falsches Markieren) kann die kom-plette Tabelle oder Liste eventuell unbrauchbar werden.

Ist die Liste durch Leerspalten oder -zeilen geteilt, müssen Sie diese zuerst vollständig markie-ren (einschließlich der Spaltenüberschriften), bevor Sie die Befehle Filtern (AutoFilter), Erwei-tert (Spezialfilter), Sortieren, Pivot-Tabelle oder Teilergebnis aufrufen, da sonst nur ein Teil der Datenliste erfasst wird.

Bei einem für Excel "korrekten" Aufbau der Liste ist es hingegen ausreichend, nur eine Zelle in der Datenliste anzuwählen, bevor Sie einen der o. g. Befehle ausführen, da Excel die Markie-rung dann automatisch bis zur nächsten Leerspalte und -zeile erweitert.

Speichern Sie Ihre Datei, bevor Sie mit dem Filtern oder der Berechnung von Teilergebnissen beginnen.

3.2 Komplexes Sortieren über ein Dialogfeld Sollen mehrere Spalten (z.B. zuerst nach dem Ort und dann nach dem Nachnamen) gleichzeitig sortiert werden, können Sie dies über ein Dialogfeld vornehmen:

1. Öffnen Sie die Datei Datenliste.xlsx und stellen Sie den Zellcursor in eine Zelle innerhalb der Liste.

2. Rufen Sie auf: Registerkarte Daten, Gruppe Sortieren und Filtern, Sortieren.

Feld-namen

Page 54: MS Excel 2013 Profiwissen -   · PDF fileExcel 2013 Verwaltungsakademie Berlin Formeln und Funktionen Verwaltungsakademie Berlin Seite 3 Inhaltsverzeichnis 1 Funktionen

Excel 2013 Verwaltungsakademie Berlin Formeln und Funktionen

Verwaltungsakademie Berlin Seite 54

3. Achten Sie in dem Dialogfeld Sortieren darauf, dass das Kontrollkästchen Daten haben Über-schriften aktiviert ist. Damit werden die Feldnamen nicht mit sortiert.

4. Wählen Sie nun das erste Sortierkriterium im Bereich Sortieren nach aus. Dort erscheint der Feld-name (hier: Ort).

5. Durch einen Klick auf [Ebene_hinzufügen] können Sie weitere Sortierkriterien anhängen (hier: Nachname). Dann wird innerhalb des Ortes nach dem Nachnamen sortiert:

Datenliste sortieren

6. Klicken Sie auf [_OK_], um die Sortierung zu starten.

Sie können weitere nachrangige Sortierkriterien angeben und für jede Spalte die Sortierreihenfolge (Aufsteigend oder Absteigend) individuell festlegen. Aber selbst bei einer Dateiliste von den 80 Millio-nen Einwohnern der Bundesrepublik Deutschland kommen Sie i.d.R. mit vier Sortierkriterien aus.

Excel erkennt Tabellen automatisch, wenn Sie dem Standard entsprechen, d.h. wenn über dem zusammenhängenden Datenbereich Spaltenüberschriften stehen und die Tabelle über höchstens eine Tabellenüberschrift verfügt (im Bild oben das Wort "Kundenliste"). Hat Ihre Tabelle aber z.B. zwei Überschriften, reicht es nicht, den Zellcursor in eine Zelle der Tabelle zu setzen. Vielmehr müssen Sie vor dem Sortieren den zu sortierenden Bereich inklusive Spaltenüberschriften markieren.

Benutzerdefinierte Sortierreihenfolge

Wenn Sie in diesem Dialogfeld Sortieren bei dem Listenfeld Reihenfolge die Benutzerdefinierte Liste… auswählen, erscheint das Dialogfeld Benutzerdefinierte Listen. Dort können Sie zusätzliche Sortierkrite-rien abrufen. Dies ist hilfreich, wenn nicht nach Standard-Sortierkriterium (alphanumerisch) geordnet werden soll.

Sortieren Sie z.B. Monate (Januar, Februar, März, …) oder Wochentage (Montag, Dienstag, Mittwoch) auf herkömmliche Art und Weise, würde der April an erster Stelle stehen, der Januar nur an fünfter Stelle oder Dienstag und Donnerstag stehen vor dem Montag. Für solche Zwecke benötigen Sie die be-nutzerdefinierte Sortierreihenfolge:

1. Stellen Sie wieder den Zellcursor in eine Zelle innerhalb der Liste.

2. Rufen Sie auf: Registerkarte Daten, Gruppe Sortieren und Filtern, Symbol Sortieren.

Page 55: MS Excel 2013 Profiwissen -   · PDF fileExcel 2013 Verwaltungsakademie Berlin Formeln und Funktionen Verwaltungsakademie Berlin Seite 3 Inhaltsverzeichnis 1 Funktionen

Excel 2013 Verwaltungsakademie Berlin Formeln und Funktionen

Verwaltungsakademie Berlin Seite 55

3. Achten Sie in dem Dialogfeld Sortieren darauf, dass das Kontrollkästchen Daten haben Über-schriften aktiviert ist. Damit werden die Feldnamen nicht mit sortiert.

4. Geben Sie die Spalte an, die sortiert werden soll und klicken Sie anschließend bei Rei-henfolge auf Benutzerdefinierte Liste.

5. Klicken Sie im Fenster Benut-zerdefinierte Listen auf die ge-wünschte Sortierreihenfolge (hier: Januar, Februar, …).

6. Übernehmen Sie diese Reihenfolge mit [_OK_], das Fenster Benutzerdefinierte Lis-ten wird geschlossen. Sie kön-nen nun gegebenenfalls noch weitere Sortierfolgen angeben.

Sortieroptionen

7. Durch einen Klick auf [_OK_] im Sortierfenster wird der Sortiervorgang gestartet.

Die benutzerdefinierte Sortierreihenfolge:

• Bleibt für diese Spalte bestehen, auch wenn Sie anschließend über die Symbole sortieren.

• Kann bei dieser Sortiermethode nur als 1. Sortierkriterium verwendet werden. Ggf. abwechselnd beide Methoden verwenden.

Die Liste der benutzerdefinierten Sortierreihenfolge können Sie auch erweitern, indem Sie in dem Dialogfeld Benutzerdefinierte Listen auf die Zeile Neue Liste klicken und ihre Liste dann im rechten Feld Listeneinträge hinzufügen. Bestätigen Sie ihre Eingabe durch einen Klick auf [_Hinzufügen_].

Wenn Sie das Dialogfeld über den folgenden Weg öffnen: Datei-Menü, , Seite Er-weitert, Bereich Allgemein, [Benutzerdefinierte_Listen_bearbeiten...], haben Sie zusätz-lich die Möglichkeit, eine Liste aus einer Excel-Tabelle zu importieren. Alle Listeneinträge, die Sie dort gespeichert haben, können Sie für die benutzerdefinierte Sortierung verwenden.

Um die benutzerdefinierte Sortierreihenfolge aufzuheben, rufen Sie das Fenster Sortieren erneut auf. Markieren Sie die Sortierung und klicken Sie auf die Schaltfläche [Ebene_löschen]:

Benutzerdefinierte Sortierreihenfolge aufheben

Page 56: MS Excel 2013 Profiwissen -   · PDF fileExcel 2013 Verwaltungsakademie Berlin Formeln und Funktionen Verwaltungsakademie Berlin Seite 3 Inhaltsverzeichnis 1 Funktionen

Excel 2013 Verwaltungsakademie Berlin Formeln und Funktionen

Verwaltungsakademie Berlin Seite 56

4 Arbeiten mit der Datenüberprüfung Tippfehler und falsche Eingaben sind ziemlich ärgerlich. Wenn Sie gewährleisten möchten, dass in einer Arbeitsmappe die richtigen Daten eingegeben werden, können Sie für einzelne Zellen oder Zellbereiche angeben, welche Daten zulässig sind. Gerade bei großen Listen und bei Dateien, die von mehreren Be-nutzern 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.

4.1 Datenregel festlegen 1. Markieren Sie die Zelle bzw. Bereiche,

deren Eingabemöglichkeiten Sie be-schränken möchten.

2. Wählen Sie auf der Registerkarte Daten in der Gruppe Datentools das Symbol

. In drei Registern haben Sie jetzt die Möglichkeit, die Einstellun-gen für die Zelleingabe festzulegen.

Datenüberprüfung, Einstellungen

Register Einstellungen Klicken Sie im Bereich Zulassen auf den Listenpfeil , um Einschränkungen auf die Dateneinträge in den markierten Zellen der Tabelle anzuwenden.

Wählen Sie in der geöffneten Liste die Option Benutzerdefiniert, um eine For-mel 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 ausge-wählt haben.

Vergleichsoperator bestimmen

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

Page 57: MS Excel 2013 Profiwissen -   · PDF fileExcel 2013 Verwaltungsakademie Berlin Formeln und Funktionen Verwaltungsakademie Berlin Seite 3 Inhaltsverzeichnis 1 Funktionen

Excel 2013 Verwaltungsakademie Berlin Formeln und Funktionen

Verwaltungsakademie Berlin Seite 57

Register 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

Register Fehlermeldung Im Register 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 der Titelzeile der Meldung angezeigt. Und tippen Sie noch die Fehlermeldung ein (max. 255 Zeichen), die bei einer ungülti-gen Eingabe angezeigt wird.

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

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

Datenüberprüfung, Fehlermeldung

Besonderheiten bei der Listenauswahl Wenn Sie im Register Einstellungen die Liste auswählen, stehen Ihnen zum Definieren dieser Liste meh-rere Möglichkeiten zur Verfügung.

Page 58: MS Excel 2013 Profiwissen -   · PDF fileExcel 2013 Verwaltungsakademie Berlin Formeln und Funktionen Verwaltungsakademie Berlin Seite 3 Inhaltsverzeichnis 1 Funktionen

Excel 2013 Verwaltungsakademie Berlin Formeln und Funktionen

Verwaltungsakademie Berlin Seite 58

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

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

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 Ta-bellenblatt schon aufgelistet sind, markie-ren Sie den Zellbereich in der Tabelle und der Bereich wird mit vorangestelltem Gleichheitszeichen und absoluten Zellbezü-gen in das Feld Quelle übernommen.

Begriffe manuell eingeben

Zellbereiche werden übernommen

Haben Sie auf einem anderen Tabellenblatt die Begriffe aufgelistet, können Sie den Bereich nicht mar-kieren, da Ihnen Excel im Fenster Datenüberprüfung einen Wechsel in ein anderes Tabellenblatt nicht erlaubt. Diese "Beschränkung" können Sie allerdings umgehen.

Vergeben Sie für die Begriffsliste auf dem anderen Tabellenblatt einen Bereichsna-men. Diesen können Sie dann mit vorange-stelltem Gleichheitszeichen im Textfeld Quelle verwenden (hier: =Auswahlliste):

Diese sehr brauchbare Funktion arbeitet aber nicht wie gewünscht, wenn Sie bei-spielsweise die Liste über die Daten-Einga-bemaske ausfüllen. Fehleingaben werden bei der Verwendung der Maske nicht zuver-lässig abgefangen. Auch können "ungültige" Daten über die Option Warnung oder In-formation in der Registerseite Fehlermel-dung der Datenüberprüfung zugelassen werden.

Bereichsnamen aus einem anderen Tabellenblatt übernehmen

Zum Ändern der festgelegten Datenregel markieren Sie die entsprechende Zelle bzw. den entsprechenden Bereich, öffnen das Dialogfeld Datenüberprüfung (Registerkarte Daten, Gruppe Datentools) und nehmen die gewünschten Änderungen vor.

Page 59: MS Excel 2013 Profiwissen -   · PDF fileExcel 2013 Verwaltungsakademie Berlin Formeln und Funktionen Verwaltungsakademie Berlin Seite 3 Inhaltsverzeichnis 1 Funktionen

Excel 2013 Verwaltungsakademie Berlin Formeln und Funktionen

Verwaltungsakademie Berlin Seite 59

4.2 Vorhandene Daten nachträglich prüfen Wenn Sie eine Datenüberprüfung in einer Tabelle vornehmen, die bereits Daten enthält, müssen 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 Eingabe-zellen nachträglich "aufspüren", die nicht der Gültigkeitsregel entspre-chen:

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 Zei-lenzahl 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

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

Page 60: MS Excel 2013 Profiwissen -   · PDF fileExcel 2013 Verwaltungsakademie Berlin Formeln und Funktionen Verwaltungsakademie Berlin Seite 3 Inhaltsverzeichnis 1 Funktionen

Excel 2013 Verwaltungsakademie Berlin Formeln und Funktionen

Verwaltungsakademie Berlin Seite 60

4.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_].

Page 61: MS Excel 2013 Profiwissen -   · PDF fileExcel 2013 Verwaltungsakademie Berlin Formeln und Funktionen Verwaltungsakademie Berlin Seite 3 Inhaltsverzeichnis 1 Funktionen

Excel 2013 Verwaltungsakademie Berlin Formeln und Funktionen

Verwaltungsakademie Berlin Seite 61

5 Stichwortverzeichnis A Add-In.................................................................... 18 Add-Ins-Manager .................................................. 19 Adressenlisten ....................................................... 14 Analyse-Funktion .................................................. 19 Arbeitsmappe

schützen ............................................................ 35 Arbeitsmappenstruktur schützen ......................... 36 Argumente .............................................................. 4 Ausfüllen-Befehl .................................................... 38 AutoAusfüllen ....................................................... 24 AutoFilter .............................................................. 53 AutoKorrektur-Optionen ....................................... 50

B Bedingte Formatierung ....................... 44, 45, 46, 47

Datenillustration ............................................... 47 suchen .............................................................. 47

Bedingungen ......................................................... 44 Befehlsgruppe

Funktionsbibliothek ........................................ 5, 7 Benutzerdefinierte Listen ...................................... 55 Benutzerdefinierte Sortierreihenfolge .................. 55 Benutzerdefinierte Zahlenformate ....................... 39

Bedingung ......................................................... 42 erstellen ............................................................ 42 kopieren ............................................................ 43 löschen .............................................................. 44

Berechnung Fehler .................................................................. 8

Bereich .................................................................... 8 Blattregister .......................................................... 31 Blattschutz aufheben ............................................ 35

C Cube ........................................................................ 4

D Daten

Gültigkeit .................................................... 56, 58 prüfen ............................................................... 59

Datenbalken .......................................................... 47 Datenbank ............................................................... 4 Datenbankfunktionen ........................................... 25 Datenillustration ................................................... 47 Datenliste .............................................................. 53 Datenüberprüfung ................................................ 59 Datum ..................................................................... 4 Datumsfunktionen ................................................ 17 Dialogfeld

Funktion einfügen ............................................... 5 Funktionsargumente........................................... 6 Neue Formatierungsregel ................................. 45 Sortieren ..................................................... 54, 55

Dokumentschutz ................................................... 34 aufheben ........................................................... 35

E Eingabemaske ....................................................... 58 Ergebnisbereich .................................................... 21

F Farbskalen ............................................................. 47 Fehler zurücksetzen ................................................ 9 Fehlermeldung .................................................... 8, 9 Fehlersymbol .......................................................... 9 Fehlerüberprüfung .............................................. 8, 9 Filtern .................................................................... 53 Finanzmathematik .................................................. 4 Finanzmathematische Funktionen ........................ 25 Formatcode ........................................................... 39 Formatierungselement ......................................... 45 Formel ..................................................................... 8 Formel, Funktion einfügen .................................... 26 Formeln anzeigen.................................................. 29 Formelüberwachung ............................................. 10 Funktion .................................................................. 8

Datum & Zeit .................................................... 17 Einfügen ............................................................ 32 Finanzmathematik ............................................ 25 Häufigkeit ......................................................... 21 Heute ................................................................ 14 Index ................................................................. 48 Logik ................................................................. 23 Math. & Trigonom. ........................................... 19 Matrix ............................................................... 24 Statistik ............................................................. 20 Suchen .............................................................. 32 Sverweis ............................................................ 30 Text ................................................................... 11 verketten .......................................................... 12 verschachtelt .............................................. 25, 34

Funktion einfügen ................................................... 5 Funktionen .............................................................. 4 Funktions-Assistent ........................................... 5, 12 Funktionsname ....................................................... 4 Funktionstasten .................................................... 60

G Gesperrt ................................................................ 34 Gruppe

Funktionsbibliothek ........................................ 5, 7 Gültigkeit

Eingabemeldung ............................................... 57 Einstellungen .................................................... 56 Fehlermeldung .................................................. 57 Listenauswahl ................................................... 57

Gültigkeitskriterien ............................................... 56 Gültigkeitsprüfung ................................................ 59 Gültigkeitsregel ............................................... 56, 58

H Häufigkeit .............................................................. 21 Hyperlink ......................................................... 49, 50

Page 62: MS Excel 2013 Profiwissen -   · PDF fileExcel 2013 Verwaltungsakademie Berlin Formeln und Funktionen Verwaltungsakademie Berlin Seite 3 Inhaltsverzeichnis 1 Funktionen

Excel 2013 Verwaltungsakademie Berlin Formeln und Funktionen

Verwaltungsakademie Berlin Seite 62

bearbeiten ........................................................ 52

I Index-Funktion ...................................................... 48 Information ............................................................. 4

K Kategorie

Datum & Zeit..................................................... 17 Finanzmathematik ............................................ 25 Logik .................................................................. 23 Math. & Trigonom. ........................................... 19 Matrix ............................................................... 24 Statistik ............................................................. 20 Text ................................................................... 11

Kennwort ........................................................ 35, 36 ändern .............................................................. 36 bestätigen ......................................................... 35

Kennwortschutz aufheben .................................... 36 Klassenbereich ...................................................... 21 Kompatibilität ......................................................... 4 Konstruktion............................................................ 4

L Ländereinstellung .................................................. 17 Leerspalten/-zeilen ............................................... 53 Logik ........................................................................ 4 Logikfunktionen .................................................... 23

M Math. & Trigonom.-Funktionen ............................ 19 Matrix .......................................................... 4, 32, 48 Matrixformel ......................................................... 27 Matrixformel-Besonderheiten .............................. 29 Matrixfunktionen .................................................. 24 Max(Bereich) ........................................................... 4 Merkzeichen ......................................................... 57 Min(Bereich) ........................................................... 4 Mittelwert ............................................................. 25 Mittelwert(Bereich) ................................................ 4

N Namen definieren ................................................. 31

O Operator.................................................................. 8

R Registerkarte

Formeln........................................................... 5, 7 Runden .................................................................. 26

S Schaltflächen

OK ....................................................................... 6

Sortieren ......................................................... 53, 54 Sortieren nach ....................................................... 54 Sortieroptionen ..................................................... 55 Sortierreihenfolge ................................................. 54 Spaltenbreite ........................................................ 10 Spaltenindex ......................................................... 32 Spaltennummer .................................................... 48 Spaltenüberschrift ................................................ 53 Spezialfilter ........................................................... 53 Spur zum Fehler .................................................... 10 Spur zum Nachfolger ............................................. 10 Spur zum Vorgänger ............................................. 10 Statistik ................................................................... 4 Statistische Auswertung.......................................... 4 Statistische Funktionen ......................................... 20 Suchkriterium ........................................................ 32 Summe(Bereich) ..................................................... 4 Sverweis .......................................................... 30, 31 Symbol

Funktion einfügen ........................................... 5, 7 Symbolsätze .......................................................... 47 Systemsteuerung .................................................. 17

T Text ......................................................................... 4 Textfunktionen ...................................................... 11 Textkonvertierung................................................. 14 Textverknüpfung ................................................... 12 Trigonometrische Funktionen ................................. 4

U Übergeordnete Funktion ...................................... 27 Ungültige Daten markieren ................................... 59

V Vergleichsoperator ......................................... 46, 56 Verschachtelte Funktion ................................. 25, 34 Verschachtelungsebene ........................................ 25 Verweisfunktion .................................................... 30

W Wenn-Bedingung .................................................. 34 Werte ...................................................................... 8

Z Zeilennummer ....................................................... 48 Zeit .......................................................................... 4 Zellbezug ............................................................... 12 Zellen formatieren ................................................ 39 Zellformat ............................................................. 43 Zellschattierung .................................................... 44 Zellschutz .............................................................. 34 Zellwert ................................................................. 45