Aus- und Fortbildungszentrum

Verknüpfen, konsolidieren und auswerten

Excel 2010

Excel 2010

Freie

Hansestadt

Bremen

Impressum

Herausgeber

Aus- und Fortbildungszentrum für den bremischen öffentlichen Dienst Doventorscontrescarpe 172C

28195 Bremen

Redaktion und Koordination

Lehreinheit für Informationstechnologien Aus- und Fortbildungszentrum Doventorscontrescarpe 172C

28195 Bremen

Tel.: +49 (0)421 361-16999 E-Mail: office@afz.bremen.de E-Mail-Hotline: cc-egov@afz.bremen.de

Namensnennung - Nicht-kommerziell - Keine Bearbeitung

Diese Lizenz ermöglicht nicht die Nutzung folgender eventuell enthaltener Inhalte:

- Hoheits- und Wahrzeichen der Freien Hansestadt Bremen

- Titelbild und Logo

- Bildschirmfotos aus dem Internet

- Personenbezogene Daten

- Unrechtmäßig veröffentlichtes Material.

Inhalt

3

1. Einstellen der Arbeitsumgebung 5

2. Formeln und Funktionen 6

2.1 Operatorenübersicht 6

2.2 Allgemeines zu Funktionen 7

2.3 Funktion Summe 9

2.4 Funktionen Mittelwert, Min, Max, Anzahl und Runden 10

2.5 Funktion Wenn 12

2.6 Funktion Zählenwenn und Summewenn 13

2.7 Funktion Zählenwenns und Summewenns 14

2.8 Funktion Sverweis 15

2.9 Funktion Datedif 16

2.10 Funktion Tag, Monat, Jahr 16

3. Namen 18

3.1 Namen erstellen und benutzen 18

3.2 Namensmanagement 21

4. Umgang mit Tabellenblättern 25

5. Verknüpfungen 26

5.1 Tabellenblätter verknüpfen ausgehend von der Zieltabelle 26

5.2 Tabellenblätter verknüpfen ausgehend von der Quelltabelle 27

5.3 Verknüpfen mit Hyperlinks 29

5.4 Daten konsolidieren 31

6. Bedingte Formate 33

7. Tabellen 34

7.1 Tabellen erzeugen 34

7.2 Filter in der Tabelle 36

7.3 Sortieren 37

7.4 Benutzerdefinierte Listen 38

8. Datenauswertung mit Teilergebnis 40

8.1 Teilergebnisse erstellen 40

8.2 Teilergebnisse entfernen 41

8.3 Teilergebnisse bearbeiten 41

9. Dateneingabe steuern 42

10. Menübänder ändern 45

10.1 Ändern der Menübändern 45

10.2 Zusätzliche Registerkarte erzeugen 46

Lernmaterial 49

Tipps & Tricks 49

Kompetenzzentrum E-Government (CC-EGov) 49

4

Inhalt

Einstellen der Arbeitsumgebung

5

1. Einstellen der Arbeitsumgebung

In den Excel-Optionen können Sie einige allgemeine Einstellungen vornehmen.

- Klicken Sie auf die Registerkarte Datei, um in die Backstage-Ansicht zu gelangen.

- Wählen Sie die Schaltfläche Optionen. Das Dialogfeld Excel-Optionen öffnet sich.

- In der Kategorie Allgemein finden Sie im Abschnitt Benutzeroberflächenoptionen die Möglichkeit die Minisymbolleiste auszustellen und die Livevorschau zu deaktivieren.

- Im Listenfeld Quickinfo-Format können Sie einstellen, ob Sie Quickinfos sehen wollen, wenn der Mauszeiger auf die Symbole zeigt.

- Im Abschnitt Beim Erstellen neuer Arbeitsmappen können Sie für eine andere Schriftart als Standard einstellen. Auch Schriftgrad lässt sich hier ändern.

- Im Listenfeld Standardansicht für neue Blätter können Sie auswählen, ob Excel mit der Normalansicht der Arbeitsmappe starten soll, oder mit der Layout- oder der Seitenum-bruchvorschau

- Im Listenfeld Die folgende Anzahl Blätter aufnehmen stellen Sie ein, mit wie viel Arbeits-blättern Excel starten soll.

- Die Änderungen, die Sie vorgenommen haben, werden erst beim nächsten Start von Excel aktiv.

6

Formeln und Funktionen

2. Formeln und Funktionen

Formeln beginnen immer mit dem Gleichheitszeichen (=). Einfache Formeln können aus Zell-

bezügen , Konstanten und Operatoren bestehen. Welche Operatoren Sie einsetzen müssen, hängt vom jeweiligen Datentyp ab.

Es gibt numerische und vergleichende Operatoren, sowie Text- und Bezugsoperatoren.

2.1 Operatorenübersicht

Während numerische Operatoren unterschiedliche Ergebnisse produzieren, erzeugen Ver-gleichsoperatoren nur zwei Ergebnisse: WAHR oder FALSCH.

Arithmetische Operatoren

Operator Name Beispiel Ergebnis

+ Plus =A1+B1 7

- Minus =A1-B1 1

* Multiplikation =A1*B1 12

/ Division =A1/B1 1,3333

% Prozent =A1*B1% 0,12

^ Potenzierung =A1^B1 64

Vergleichsoperatoren

Operator Name Beispiel Ergebnis

> Größer als =A1>B1 WAHR

>= Größer gleich =A1>=B1 WAHR

< Kleiner als =A1<B1 FALSCH

<= Kleiner gleich =A1<=B1 FALSCH

= Gleich =A1=B1 FALSCH

<> Ungleich =A1<>B1 WAHR

Textoperator

Der Textoperator ist das kaufmännische Und (&).

Formel Ergebnis

Formeln und Funktionen

7

Formeln können auch mit Funktionen gebildet werden. Funktionen sind vordefinierte For-meln, die Berechnungen unter Verwendung bestimmter Daten, der so genannten Argumen-te, und einer bestimmten Reihenfolge und Struktur durchführen. Die Struktur einer Funktion beginnt mit einem Gleichheitszeichen (=) gefolgt von dem Funktionsnamen, einer öffnenden Klammer, den durch Semikola getrennten Argumenten der Funktion und einer schließenden Klammer. Argumente können Zahlen, Text, logische Werte wie WAHR oder FALSCH, Matri-zen, Fehlerwerte wie #NV oder Zellbezüge sein. Das Argument, das Sie benennen, muss ei-nen für dieses Argument gültigen Wert ergeben. Argumente können auch Konstanten, For-meln oder andere Funktionen sein.

2.2 Allgemeines zu Funktionen

Eine Funktion verlangt eine bestimmte Syntax, d.h. eine Eingaberegel. Eine Funktion ist fol-gendermaßen aufgebaut:

Jede Funktion erwartet eine bestimmte Anzahl von Argumenten. Es gibt Funktionen, die ihre Argumente direkt vom Betriebssystem erhalten, z.B. die Funktion =heute() oder =jetzt(). Trotzdem folgt nach dem Funktionsnamen die sich öffnende und dann die schließende Klammer. Die Anzahl und Reihenfolge der Argumente wird durch die Funktion bestimmt. Ebenso wie der Datentyp. Sie müssen beachten, dass jede Funktion einen bestimmten Da-tentyp erfordert. Enthalten die Argumente der Funktion einen falschen Datentyp wird keine Fehlermeldung ausgegeben, sondern einfach nicht gerechnet. Im Gegensatz dazu wird eine mit Rechenzeichen eingegebene Formel, die falsche Datentypen enthält, eine Fehlermel-dung ausgeben.

Das Beispiel unten zeigt in Zelle C1 eine Addition mit dem Pluszeichen, in Zelle C2 eine Addi-tion mit der Funktion Summe. Da in den Zellen A1 und B1 Texte enthalten, gibt die Formel die Fehlermeldung #Wert! aus.

Die Funktion Summe hingegen rechnet, sobald in den Zellen A2 und B2 eine Zahl steht. Zu-nächst steht in den Zellen nur Text, deshalb das Ergebnis 0. In der nächsten Abbildung sehen Sie in den Zellen B1 und B2 die Zahl fünf. In Zelle C1 bleibt der Fehlerwert erhalten, da nicht beide Zellen eine Zahl enthalten. Die Summenfunktion hingegen gibt den Wert fünf aus.

8

Formeln und Funktionen

Sie können Funktionen per Hand eingeben, indem Sie zunächst das Gleichheitszeichen ein-tippen und anschließend den Funktionsnamen eingeben. Sobald Sie die ersten Buchstaben eingeben, erscheint eine Ausfüllhilfe. Im Beispiel unten werden Funktionen oder Tabellen angezeigt, die mit su beginnen. Am Symbol vor dem Funktionsnamen erkennen Sie, ob es sich um eine Funktion oder eine Tabelle handelt. Wenn Sie die Funktion markieren, wird eine kleine Hilfe angezeigt, die angibt, was die Funktion berechnet.

Sie können aber auch Funktionen über die Registerkarte Formeln auswählen. Das Menüband der Registerkarte Formeln enthält einzelne Funktionskategorien, wie Text usw. Wenn

Sie auf den Befehl Funktion einfügen klicken, öffnet sich das Dialogfeld Funktion einfügen.

Sie können eine Kategorie auswählen oder sich alle Funktionen anzeigen lassen. Im Feld

darunter werden dann alle Funktionen in alphabetischer Reihenfolge aufgelistet. Wenn

Sie eine Funktion markiert haben, wird im Bereich darunter angezeigt, was die Funktion

berechnet. Zusätzlich können Sie weitere Erläuterungen zu dieser Funktion anfordern .

Formeln und Funktionen

9

Wenn Sie eine Funktion ausgewählt haben und auf die Schaltfläche OK klicken, erscheint das Dialogfeld Funktionsargumente. Für jedes Argument gibt es jetzt eine Eingabezeile. Tragen Sie die Argumente ein oder markieren Sie die entsprechenden Zellen.

Beenden Sie den Funktionsassistenten indem Sie auf die Schaltfläche OK klicken. Das Ergeb-nis der Funktion steht jetzt in der Zelle.

2.3 Funktion Summe

Die am häufigsten verwendete Funktion in Excel ist wahrscheinlich die Summenfunktion. Aus

diesem Grund gibt es auch extra ein Symbol für Summe: . Dieses Symbol findet sich auf den Registerkarten Start und Formeln.

Das Autosummensymbol in Excel erfüllt verschiedene Funktionen, je nach-dem, was mar-kiert ist. Einige Beispiele werden im Folgenden aufgeführt.

Markieren Sie beispielsweise die Zellen E3 bis E8 und klicken anschließend auf das Summensymbol, werden sofort alle Zeilen-summen errechnet.

Markieren Sie die Zellen B9 bis D9 und kli-cken anschließend auf das Summensymbol, werden sofort alle Spaltensummen errech-net.

10

Formeln und Funktionen

Bei folgender Markierung ist jeweils ein freier Bereich waagerecht und senk-recht mit markiert. Wenn Sie nun auf das Summensymbol klicken, wird je-weils das Gesamt für die Monate waa-gerecht und das Gesamt für die Dienst-stellen senkrecht gebildet.

In folgender Darstellung wurden Zwi-schensummen gebildet. Steht der Zellzeiger auf einer Zelle, deren vorangegangene Zelle be-reits eine Summe enthält, werden nur die Werte mit einbezogen, die mit dem Summensym-bol gebildet wurden.

In der Tabelle wurde in der Zelle B3 mit dem Summensymbol die Summe gebildet. In B6 wurde ebenfalls auf das Summensymbol geklickt. Excel markiert nur die Zellen bis zur nächs-ten Summenfunktion. In B7 wurde ebenfalls auf das Summensymbol geklickt. Da bereits in der Zelle darüber (B6) eine Summe gebildet wurde, nimmt Excel nur die Zellen, die eine Summenfunktion enthalten.

2.4 Funktionen Mittelwert, Min, Max, Anzahl und Runden

In der folgenden Tabelle sind monatliche Ausgaben aufgelistet.

In der Zelle E2 wurde eine Summe gebildet. Die Funktion Max ermittelt den größten Wert aus dem angegebenen Bereich und Min ermittelt den kleinsten Wert. Die Funktion Anzahl in Zelle E8 ermittelt, wie viel Zahlen in dem markierten Bereich stehen. Wenn Sie ermitteln wollen, wie viel Zellen einen Inhalt haben, egal ob Zahl oder Text, wählen Sie die Funktion Anzahl2. Mit der Funktion Mittelwert berechnen Sie den Durchschnitt, d.h. die Werte aus B2 bis B13 werden addiert und durch die Anzahl der mit Werten belegten Zellen geteilt.

Formeln und Funktionen

11

Die Struktur der Funktion Runden lautet: =Runden(Zahl;Anzahl_Stellen). Das Argument Zahl kann eine Konstante, eine Zelle oder auch wieder eine Funktion sein. Das Argument An-zahl_Stellen rundet auf die gewünschte dezimale Nachkommastelle. Wird dieses Argument, wie in Zelle E7 mit einem negativen Vorzeichen versehen, rundet die Funktion auf Stellen vor dem Komma auf. Z. B. -1 auf Zehner, -2 auf Hunderter, -3 auf Tausender usw.

Im Beispiel wird der Mittelwert in Zelle E5 separat gebildet; anschließend in der Zelle E6 ge-rundet. Sie können Formeln schachteln, so dass unnötige Zwischenschritte nicht die Über-sicht erschweren. Sie können die geschachtelte Formel wie folgt eingeben:

- Tippen Sie in der Zelle, die das Ergebnis enthalten soll, das Gleichheitszeichen und die ersten Buchstaben der Funktion ein. Die Ausfüllhilfe erscheint, so dass Sie mit der Tab-Taste den Funktionsnamen übernehmen können.

- Die Funktion Runden wird in Großbuchstaben und mit geöffneter Klammer in die Zelle eingetragen. Anschließend können Sie die Funktion Mittelwert schreiben. Sobald Sie mit den ersten Buchstaben beginnen, erscheint die Ausfüllhilfe. Auch hier können Sie per Doppelklick oder Tab-Taste den Funktionsnamen mit geöffneter Klammer übernehmen.

- Anschließend markieren Sie den Bereich, der den Mittelwert bilden soll. Sobald Sie die die schließende Klammer eingeben, wird das Klammerpaar für den Mittelwert farblich mar-kiert und die Ausfüllhilfe zeigt an, dass es mit der Funktion Runden weitergeht.

- In der Ausfüllhilfe ist das erste Argument fett gedruckt. Sobald Sie ein Semikolon einge-ben, wird das zweite Argument fett unterlegt. Damit wird angezeigt, an welcher Stelle der Eingabe Sie sich befinden.

- Geben Sie nun die Stellanzahl und die schließende Klammer für die Funktion Runden ein. Das zugehörige Klammerpaar wird ebenfalls farblich markiert und kurzzeitig fett hervor-gehoben.

Sobald Sie die Formel bestätigt haben, erscheint das Ergebnis der Formel in der Zelle. Sie sehen allerdings nur das Gesamtergebnis und kennen nicht die Ergebnisse der einzelnen Funktionen. Um zu sehen, welches Ergebnis die Funktion Mittelwert liefert, markieren den Funktionsnamen und die Argumente in den Klammern. Drücken Sie dann die Funktionstaste F9.

Statt der Funktion steht nun das Ergebnis der Funktion als erstes Argument der Funktion Runden dort. Wenn Sie weitere Funktionen in einer Formel geschachtelt haben, können Sie mit dieser Methode die Zwischenergebnisse überprüfen. Um wieder auf die geschachtelte Formel zurückzukommen, drücken Sie die ESC-Taste.

12

Formeln und Funktionen

2.5 Funktion Wenn

Sie wollen wissen, in welchem Monat eine Ausgabe höher ist als die durchschnittliche Aus-gabe. Sie können dies ganz einfach in der Spalte F überprüfen, in dem Sie in die Zelle F2 fol-gende Formel eingeben: =B2>$E$7 und diese Formel nach unten ausfüllen. In der Zelle F2 erscheint das Ergebnis der Prüfung, nämlich ob der Wert in B2 größer als der Wert in E7 ist. Als Ergebnis erscheint WAHR, da der Wert in B2 tatsächlich größer ist als der Durchschnitt in Zelle E7. Wäre es nicht so, stände dort FALSCH.

Da das Ergebnis von Vergleichsoperatoren WAHR oder FALSCH ist, bietet die Funktion Wenn eine Möglichkeit, anstatt der Ergebnisse WAHR oder FALSCH andere Ergebnisse auszugeben. Die Struktur der Funktion lautet: Wenn(Prüfung;Dann_Wert;Sonst_Wert).

=Wenn(B2>$E$7;A2;"")

Das erste Argument prüft, ob der Wert in B2 größer ist als in E7. Wenn die Bedingung WAHR ist, wird das zweite Argument ausgeführt, in diesem Fall wird der Inhalt der Zelle A2 ausge-geben. Ist die Bedingung FALSCH, d.h. der Wert in Zelle B2 ist nicht größer als der Wert in Zelle E7, dann wird das dritte Argument ausgeführt, also ein Leerzeichen geschrieben.

Formeln und Funktionen

13

Wollen Sie nicht die einzelnen Zwischenschritte, wie den gerundeten Mittelwert, separat ausweisen, so können Sie auch hier wiederum die Formel schachteln.

Um das Ergebnis der Formel zu prüfen, können Sie die Zwischenergebnisse auswerten. In der Formel markieren Sie den Bereich, der den gerundeten Mittelwert berechnet. Anschließend drück Sie die Funktionstaste F9.

Der gerundete Mittelwert beträgt 2000. Anschließend markieren Sie das erste Argument der Funktion Wenn und drücken wiederum die Funktionstaste F9.

Das Ergebnis der Prüfung ist WAHR. Das führt dazu, dass das zweite Argument ausgeführt wird. Es wird der Inhalt der Zelle A2 ausgege-ben.

2.6 Funktion Zählenwenn und Summewenn

Die Funktion Zählenwenn ermittelt, wie viele Male ein Text, ein Wert usw. in einem be-stimmten Bereich vorkommt. Die Funktion hat folgende Struktur: Zählen-wenn(Bereich;Suchkriterien). Im Beispiel unten enthält die Zelle I2 die Funktion Zählen-wenn. Das erste Argument enthält den Bereich (D2:D116), in dem das zweite Argument, das Suchkriterium (steht hier in Zelle H2) gesucht werden soll. Das Wort Hardware kommt in dem Bereich D2:D116 insgesamt 50 Mal vor.

Der Bereich ist mit absoluten Bezügen versehen, damit die Formel nach unten kopiert wer-den kann.

14

Formeln und Funktionen

Mit der Funktion Summewenn können Sie in diesem Beispiel ermitteln, was die 50 Hard-warebestellungen und die 65 Softwarebestellungen zusammen gekostet haben.

Im Beispiel unten enthält die Zelle J2 die Funktion Summewenn. Die ersten beiden Argu-mente der Funktion sind mit der Funktion Zählenwenn identisch. Die Funktion prüft, ob im Bereich D2:D116 der Inhalt der Zelle H2 (Hardware) vorhanden ist. Wird eine Übereinstim-mung gefunden, dann wird der zugehörige Wert aus dem Bereich F2:F116 addiert.

In der neuen Excelversion gibt es zwei weitere Funktionen, die es ermöglichen, eine Kreuz-tabelle zu erzeugen.

2.7 Funktion Zählenwenns und Summewenns

Diese beiden Funktionen erweitern die Funktion Zählenwenn und Summewenn um weitere 126 Kriterien und Kriterienbereiche. Im Beispiel unten vergleicht die Funktion im Bereich D2:D116, wie viel Mal das Wort in Zelle H2 (Hardware) vorkommt. Zusätzlich wird im Bereich B2:B116 mit dem Inhalt der Zelle I2 (Referat 13) verglichen. Das Ergebnis der Funktion ist 10. Wenn also das erste Suchkriterium im ersten Suchbereich und das zweite Suchkriterium im zweiten Suchbereich gefunden werden, wird die Anzahl der Übereinstimmungen ausgege-ben.

Formeln und Funktionen

15

Im Beispiel unten in Zelle I2 sehen Sie das Ergebnis der Funktion Summewenns. Das erste Argument dieser Funktion ist der Bereich F2:F116. Er enthält die Werte, die summiert wer-den sollen, wenn im Bereich D2:D116 das Suchkriterium aus Zelle H2 (Hardware) steht und wenn im Bereich B2:B116 das Suchkriterium aus Zelle I1 (Referat 13) steht.

Die Formel steht in Zelle I2. Damit sie nach links und nach unten kopiert werden kann, müs-sen die Bezüge absolut und gemischt absolut gesetzt werden.

2.8 Funktion Sverweis

Die Funktion Sverweis ist eine Suchfunktion, die in der ersten Spalte eines markierten Be-reichs nach einem bestimmten Eintrag sucht. Wird der Eintrag gefunden, soll ein Eintrag, der parallel dazu in einer anderen Spalte des markierten Bereichs steht, ausgegeben werden. Die Einschränkungen dieser Funktion ergeben sich dadurch, dass der Suchbereich in der ersten Spalte das Suchkriterium enthalten muss und dass das Suchkriterium nicht mehrfach vor-kommen darf, sonst wird der zuerst gefundene Wert ausgegeben. Im Beispiel unten steht die Formel in Zelle F2. Das erste Argument der Funktion ist die Zelle E2. Hier wird das Such-kriterium (010) eingetragen. Das zweite Argument ist der Bereich A1:C177. Dieser Bereich hat 3 Spalten. In Spalte 1 wird nach dem Suchkriterium (010) gesucht. Ist das Suchkriterium (010) in Spalte 1 gefunden, soll der zugehörige Eintrag aus der Spalte 2, also Stadtamt aus-gegeben werden. Das dritte Argument der Funktion gibt also an, aus welcher Spalte der zu-gehörige Eintrag ausgegeben werden soll, wenn eine Übereinstimmung aus Spalte 1 und dem Suchkriterium gefunden wird. Das letzte Argument bedeutet, dass eine genaue Über-einstimmung gefunden werden muss.

16

Formeln und Funktionen

2.9 Funktion Datedif

Die Funktion ermittelt die Differenz zwischen zwei Datumsangaben. Sie können beispiels-weise das Alter einer Person ermitteln.

Die Funktion ist wie folgt aufgebaut: Datedif(Anfangsdatum;Enddatum;Einheit). Einheit be-deutet, ob Sie die Differenz zwischen zwei Datumsangaben in Jahren, Monaten oder Tagen ausgegeben haben wollen.

Im Beispiel unten steht das Geburtsdatum in Zelle I126, das Enddatum ist das jeweilige Ta-gesdatum, das durch die Funktion Heute() ermittelt wird. Das dritte Argument der Funktion "y" bedeutet, dass das Jahr als Zahl ausgegeben wird.

Folgende Einheiten gibt es:

y Jahre ym Differenz nur die Restmonate zwischen Anfangs- und End-datum werden ermittelt, ohne die Jahre

m Monate md Differenz nur in Tagen zwischen Anfangs- und Enddatum, Basis ist der Monat.

d Tage yd Differenz in Tagen zwischen Anfangs- und Enddatum, Jahre und Monate werden ignoriert

Diese Funktion ist in der Excelhilfe nicht dokumentiert. Die Funktion rechnet nicht immer zufriedenstellend, wie im folgenden Beispiel zu sehen ist:

Offensichtlich ist hier der Februar und die Schaltjahre das Problem.

2.10 Funktion Tag, Monat, Jahr

Mit diesen Funktionen können Sie aus einem Datum den Tag, den Monat und das Jahr ermit-teln. Im Beispiel unten steht das Geburtsdatum in Zelle I2. In Zelle K2 wird mit der =Jahr(I2) aus der Zelle I2 das Geburtsjahr herausgefiltert. In Zelle L2 wird der Geburtsmonat mit =MONAT(I2) ermittelt und in Zelle M2 wird das Tagesdatum mit der Funktion =TAG(I2) dar-gestellt.

Formeln und Funktionen

17

Wenn Sie anstatt Monat 5 lieber den Namen des Monats (Mai) angezeigt haben wollen, können Sie hierzu die Sverweis-Funktion nutzen.

Das erste Argument, das Suchkriterium, ist die Funktion Monat. Diese ermittelt wie in der oberen Abbildung dargestellt den Monat 5. Das zweite Argument ist die Matrix, in der ersten Spalte die 5 gesucht werden soll. Die Matrix befindet sich im Bereich O2:P13. Das dritte Ar-gument welche Spalte der Matrix ausgegeben werden soll, wenn eine Übereinstimmung zwischen Suchkriterium und erster Spalte gefunden wird. In diesem Fall die Spalte 2. Das letzte Argument gibt an, dass eine genaue und keine ungefähre Übereinstimmung gesucht werden soll.

Damit die Formel nach unten kopierbar ist, muss die Matrix absolute Bezüge haben.

18

Namen

3. Namen

Wenn die Argumente in den Funktionen Zellbezüge sind, können Sie stattdessen Namen verwenden. Namen sind dann besonders nützlich, wenn Sie auf andere Tabellen verknüpfen wollen. Normalerweise müssen Sie dann auf die Tabelle klicken und den Bereich markieren. Wenn Sie mit Namen arbeiten, geben Sie einfach den Namen statt der Zellbezüge ein oder wählen aus einer Liste den entsprechenden Namen.

3.1 Namen erstellen und benutzen

Wenn Sie für einzelne Zellen oder ganze Bereiche Namen vergeben wollen, gibt es unter-schiedliche Möglichkeiten. Sie können den Namen über das so genannte Namenfeld definie-ren.

- Klicken Sie in die Zelle, die Sie mit einem Namen versehen wollen. Im Beispiel ist es die Zelle H1. Hier steht der Umsatzsteuersatz mit dem in der Zelle G4 der Steuerbetrag be-rechnet werden soll.

- Klicken Sie dann link neben der Bearbeitungsleiste in das Namenfeld und tippen einen Namen ein. In diesem Fall lautet er ust.

- Bestätigen Sie den Eintrag mit der Return-Taste.

- Um den Steuerbetrag zu berechnen klicken Sie in die Zelle B4. Geben Sie ein Gleichheits-zeichen und geben Sie die Formel ein.

- Wenn Sie wie in der Abbildung die Formel erzeugen, in dem Sie auf die Zellen klicken, wird statt des Zellbezuges H2 der Name ust in die Formel eingetragen. Anschließend kön-nen Sie die Formel nach unten ziehen.

Namen

19

Sie können natürliche auch ganze Bereiche markieren und diese mit einem Namen versehen.

- Markieren Sie den entsprechenden Bereich (B2:B13). Klicken Sie in das Namenfeld und tippen Ausgaben ein.

- Sobald Sie die Eingabe mit der Return-Taste bestätigt haben, ist der markierte Bereich mit dem Namen Ausgaben versehen.

- Sie können nun, hier in Zelle B14, die Funktion Summe eintippen, nach der geöffneten Klammer geben Sie den Namen Ausgaben ein. Sobald Sie mit dem Schreiben anfangen, wird eine Ausfüllhilfe angezeigt. Die Symbole vor den Bezeichnungen zeigen, ob es sich bei den Begriffen, die mit au beginnen, um Funktionen oder Namen handelt.

- Wenn Sie den Namen in die Formel übernommen haben und mit der Return-Taste bestä-tigen steht in der Zelle das Ergebnis und die Formel lautet: =SUMME(Ausgaben)

Sie können auch mehrere Namen gleichzeitig erzeugen.

- Markieren Sie einen Bereich, einschließlich der Überschriften.

20

Namen

- Klicken Sie in der Registerkarte Formeln, in der Gruppe Definierte Namen auf den Befehl Aus Auswahl erstellen. Im Quickinfo wird Ihnen be-reits angezeigt, was dieser Befehl bewirkt.

- Das Dialogfeld Namen aus Auswahl erstellen öffnet sich.

- Haken Sie den Eintrag oberster Zeile ab und bestätigen Sie die Eingaben mit einem Klick auf die Schaltfläche OK.

- Aus der Zeile drei werden die Namen übernommen.

- Wenn Sie auf den Listenpfeil im Namenfeld klicken, werden die Namen angezeigt.

Wie oben bereits beschrieben, können Sie Namen in einer Formel verwenden, in dem Sie zu schreiben anfangen. Im Quickinfo werden Ihnen die Namen angezeigt. Sie müssen nur noch den entsprechenden Namen auswählen. Es gibt noch andere Möglichkeiten den Namen in eine Formel einzufügen. Im Beispiel unten sehen Sie, dass die Funktion Summewenn als ers-tes Argument den Bereich benötigt, in dem nach dem Suchkriterium gesucht werden soll. Der Bereich ist D2:D116. Dieser Bereich heißt jetzt Rubrik. Um diesen Namen in die Formel einzutragen, Sie in die Registerkarte Formeln, in die Gruppe Definierte Namen und dort auf den Befehl In Formel verwenden. Wählen Sie dort den Namen Rubrik. Tragen Sie dann die weiteren Argumente ein.

Um den Namen Rubrik als erstes Argument einzugeben, können Sie aber auch die Funktions-taste F3 drücken.

Namen

21

Das Dialogfeld Namen einfügen erscheint. Wählen Sie nun den Namen Rubrik aus und bestätigen Sie die Auswahl mit der Schaltfläche OK.

3.2 Namensmanagement

Auf der Registerkarte Formeln in der Gruppe Definierte Namen gibt es weitere Möglichkei-ten mit Namen umzugehen.

Wenn Sie bereits vorhandene Bezüge durch Namen ersetzen möchten, können Sie auf den Listenpfeil neben dem Befehl Namen definieren kli-cken. Es erscheint der Befehl Namen übernehmen. Sobald Sie auf den Be-fehl geklickt haben, wird das Dialogfeld Namen übernehmen geöffnet. Im Dialogfeld wählen Sie aus, welchen Namen Sie für die jeweiligen Bezüge einsetzen wollen. Klicken Sie abschlie-ßend auf die Schaltfläche OK. Das Programm sucht nun Bereiche, die diesem Namen zuge-ordnet wurden und ersetzt den Bezug durch den Namen.

Wenn Sie auf den Befehl Namensmanager klicken, öffnet sich das Dialogfeld Namens-Manager. In der Abbildung unten sehen Sie, dass der Name Rubrik ausgewählt wurde. Vor dem Namen steht ein Symbol, eine Art gelbes Etikett, dann erst folgt der Name. Die Spalte Werte listet alle Werte auf, die in dem Wertebereich des Namens existieren. Die Spalte Be-zieht sich auf zeigt den Bereich an, der diesem Namen zugeordnet wurde. Für die Spalte Bereich weist dieser Name Arbeitsmappe aus. D. h. dieser Name ist in der gesamten Ar-beitsmappe gültig, nicht nur in Tabelle1.

22

Namen

Im folgenden Beispiel gibt es für die Tabellenblätter januar, februar und märz jeweils die gleichen Namen. Im Beispiel ist der Name Büromaterial markiert. Es gibt zwei Mal diesen Namen. Einmal im Tabellenblatt februar und einmal im Tabellenblatt januar. Die Namen sind auch nur in dem jeweiligen Tabellenblatt gültig im Gegensatz zu der Abbildung oben.

Namen

23

Wenn Sie einen Namen erstellen wollen, der nur für ein bestimmtes Tabellenblatt gültig ist, gehen Sie wie folgt vor:

- Markieren Sie den Bereich, den Sie mit dem Namen versehen wollen. Im Beispiel unten ist dies der Bereich B4:F4.

- Klicken Sie auf den Befehl Namen definieren oder wenn das Dialogfeld Namens-Manager geöffnet ist, auf die Schaltfläche Neu.

- Das Dialogfeld Neuer Name öffnet sich. Im Beispiel wird bereits der Name Büromaterial vorgeschlagen.

- Klicken Sie auf den Listenpfeil bei Bereich und wählen Sie die entsprechende Tabelle aus.

- Sobald Sie auf die Schaltfläche OK geklickt haben, wird der Name neu angelegt.

Im Namensmanager können Sie die Namen auch löschen. Wollen Sie mehrere Namen gleich-zeitig löschen, können Sie diese entweder mit gedrückter Shift-Taste oder mit gedrückter STRG-Taste mehrfach markieren. Anschließend klicken Sie auf die Schaltfläche Löschen. Sie könne die Namen aber auch bearbeiten. Z. B. wenn der Bereich abgeändert werden soll.

24

Namen

Damit Sie eine Übersicht über alle Namen in der Arbeitsmappe erhalten, können Sie sich die Namen anzeigen lassen.

- Klicken Sie zunächst in einen leeren Bereich. Enthält der Bereich Eintragungen, werden die Eintragungen überschrieben.

- Wählen Sie in der Gruppe Definierte Namen im Befehl In Formel verwenden den Eintrag Namen einfügen.

- Alternativ dazu können Sie die Funktionstaste F3 drücken.

- Beide Vorgehensweisen führen dazu, dass sich das Dialogfeld Na-men einfügen öffnet.

- Klicken Sie auf die Schaltfläche Liste einfügen. Ab der Zelle, in der der Zellzeiger steht, werden nun alle Namen mit den zuge-hörigen Bereichen eingetragen. Sie können dieses Dialogfeld auch aufrufen, wenn Sie einen Namen in eine Formel eintragen möchten.

Umgang mit Tabellenblättern

25

4. Umgang mit Tabellenblättern

Standardmäßig startet Excel mit 3 leeren Tabellenblättern. Sie können leere Tabellenblätter auf verschiedene Arten hinzufügen.

Die einfachste Methode ist über das Symbol im Blattregister. Wie Sie in der Abbildung se-hen, können Sie alternativ mit der Tastenkombination Shift + F11 ein neues Tabellenblatt erzeugen.

Auf der Registerkarte Start, in der Gruppe Zellen können Sie auch eine neue Tabelle einfü-gen. In derselben Gruppe können Sie auch die ausgewählte Tabelle löschen.

In der Gruppe Zellen finden Sie auch die Befehlsschaltfläche Format. Wenn Sie in dem Ab-schnitt Sichtbarkeit die Option Ausblenden & Einblenden anwählen, können Sie das mar-kierte Tabellenblatt ausblenden.

Im Abschnitt Blätter anordnen finden Sie die Einträge Blatt umbenennen, usw. Diese Einträ-ge können Sie aber auch finden, wenn Sie mit der rechten Maustaste in das Blattregister klicken.

26

Verknüpfungen

5. Verknüpfungen

Verknüpfungen sind Formeln. Die einfachste Verknüpfung ist die Zellver-knüpfung: Der Zellzeiger steht auf der Zielzelle. Die Formel wird mit dem Gleichheitszeichen eingeleitet. Danach klicken Sie auf die Quellzelle mit der verknüpft werden soll. Im Beispiel enthält die Zelle A2 den gleichen Wert wie die Zelle A1, nämlich 12. Der Zellebezug ist relativ.

Eine Verknüpfung kann auch mit Kopieren erzeugt wer-den. Klicken Sie in die Quellzelle, dann auf Kopieren. An-schließend klicken Sie mit der rechten Maustaste in die Zielzelle. Einfügeoptionen werden angezeigt. Wählen Sie dort das Symbol für Verknüpfung einfügen. Beachten Sie, dass mit dieser Methode die Bezüge absolut gesetzt werden.

Diese beiden Methoden Verknüpfungen zu erzeugen, einmal ausgehend von der Zielzelle, einmal ausgehen von der Quellzelle, lassen sich auch andere Verknüpfungen übertragen.

5.1 Tabellenblätter verknüpfen ausgehend von der Zieltabelle

Im Beispiel sollen die Ausgaben der Dienststellen für das erste und das zweite Halbjahr zu-sammengefasst dargestellt werden.

- Sie klicken die Zielzelle (B2) in der Tabelle Zusammenfassung nach Halbjahren an und geben das Gleichheitszeichen ein. Weil das Gleichheitszeichen eine Formel einleitet, wird nun jeder weitere Klick in der Bearbeitungsleiste sichtbar.

- Sobald Sie das Gleichheitszeichen eingegeben haben, klicken Sie auf die Tabelle, die die zu übertragenden Daten enthält, in diesem Fall die Tabelle erstes Halbjahr. Beide Tabellen-blätter sind aktiv. Klicken Sie nun auf die Zelle, die das Gesamt des ersten Halbjahres der Dienststelle 1 enthält, also die Zelle B9.

- Zum Abschluss drücken Sie die Return-Taste, ohne auf die Zieltabelle zurückzuklicken. Der Wert aus der Quelltabelle wird in die Zieltabelle übertragen. Jedes Mal, wenn Sie in der Quelltabelle die Daten verändern, wird der Wert in der Zieltabelle angepasst. Diese For-mel können Sie nach rechts ausfüllen. So übertragen Sie die Werte auch für die anderen Dienststellen. In der Zelle B3 müssen Sie für die Übertragung der Werte aus dem zweiten Halbjahr eine neue Formel bilden.

Verknüpfungen

27

5.2 Tabellenblätter verknüpfen ausgehend von der Quelltabelle

- Markieren Sie zunächst in der Quelltabelle den Bereich B9:D9 und klicken auf Kopieren oder drücken Sie die Tasten STRG und C.

- Klicken Sie in der Zieltabelle mit der rechten Maustaste die erste Zelle an, in der die Ver-knüpfung stehen soll.

- Wählen Sie aus den Einfügeoptionen das Symbol für Verknüpfung einfügen. Sobald Sie mit dem Mauszeiger auf das Symbol zeigen, wird Ihnen bereits in der Vorschau angezeigt wie die Daten aussehen werden.

- Zum Abschluss drücken Sie die ESC-Taste, um den Vorgang zu beenden. Anschließend können Sie die Werte formatieren.

28

Verknüpfungen

Nach dem gleichen Prinzip können Dateien verknüpft werden.

Der Aufbau der Verknüpfungsformeln für Tabellen:

Die Hochkommata, die den Tabellenamen umgeben, werden automatisch von Excel hinzuge-fügt, weil im Tabellennamen ein Leerzeichen vorkommt. Wenn Sie keine Leerzeichen im Ta-bellennamen haben, werden die Hochkommata nicht hinzugefügt. Sie können die Tabelle umbenennen, die Formel wird entsprechend angepasst.

Der Aufbau der Verknüpfungsformeln für Dateien:

Verknüpfungen

29

Die oben beschriebene Vorgehensweise, die Verknüpfung von der Zielzelle beginnend auf-zubauen, eignet sich dann, wenn Sie Funktionen und Berechnungen einsetzen und die Ar-gumente der Funktionen beziehen sich auf Werte aus einer anderen Tabelle. Beispielsweise wollen Sie der Tabelle Mittelwerte die durchschnittlichen Ausgaben der Dienststellen im ersten Halbjahr ermitteln.

- In der Tabelle Durchschnittsausgaben, in der Zelle B2 aktivieren Sie die Funktion Mittel-wert.

- Klicken Sie in die Tabelle erstes Halbjahr und markieren dort den Bereich B3:B8. Um die Formel zu bestätigen, drücken Sie die Return-Taste.

In der Abbildung oben können Sie die Formel bereits erkennen. Wenn Sie für den Bereich B3:B8 in der Tabelle erstes Halbjahr einen Namen vergeben hätten, könnten Sie diesen in der Formel nutzen.

5.3 Verknüpfen mit Hyperlinks

Stellen Sie sich vor, Sie haben eine Mappe mit verschiedenen Tabellen, die Sie über ein ers-tes Tabellenblatt, das quasi als Inhaltsverzeichnis fungiert, aufrufen möchten.

In der Abbildung ist ein Hyperlink eingefügt. Sobald Sie auf die Zelle A3 zeigen, wird in einem Quickinfo angezeigt, wohin der Link führt.

30

Verknüpfungen

Um einen Hyperlink anzulegen, gehen Sie wie folgt vor:

- Klicken Sie die Zelle an, von der aus Sie die anderen Tabellen aufrufen möchten, in der Abbildung ist dieses die Zelle A3.

- Wählen Sie auf der Registerkarte Einfügen, aus der Gruppe Hyperlink den Befehl Hyper-link. Sie können auch mit der rechten Maustaste in die Zelle klicken und dort den Eintrag Hyperlink auswählen.

- Im Bereich Link zu wählen Sie aus, ob Sie einen Link innerhalb der Mappe anlegen wollen oder zu einer anderen Datei. Bleiben Sie in der gleichen Mappe, werden Ihnen die Tabel-lennamen und die festgelegten Namen für Bereiche angezeigt.

- Geben Sie im Feld Zellbezug die Zelle ein, auf die der Cursor springen soll. Im Feld darun-ter wählen Sie aus, welche Tabelle angesprungen werden soll.

- Bestätigen Sie das mit der Schaltfläche OK. Wenn Sie noch einen Hinweis für die Nutzer haben möchten, wohin der Link führt, geben Sie einen Text als Quickinfo ein. Dazu klicken Sie auf die Schaltfläche Quickinfo und geben einen Text ein.

Verknüpfungen

31

5.4 Daten konsolidieren

Konsolidieren ist das Zusammenfassen gleichartig aufgebauter Daten unter zu Hilfenahme von Funktionen. Sie können in der gleichen Datei, in einer neuen Tabelle zusammenfassen oder in einer neuen Mappe.

- Klicken Sie in der Registerkarte Daten, in der Gruppe Datentools auf den Befehl Konsoli-dieren.

- Das Dialogfeld Konsolidieren öffnet sich. Im Listenfeld Funktion wählen Sie eine Funktion aus, nach der Sie die Daten zusammenfassen wollen.

- Klicken Sie in das Feld Verweis und markieren Sie den ers-ten Bereich der ersten Tabelle, den Sie in die Konsolidie-rung einbeziehen wollen.

- Klicken Sie auf die Schaltfläche Hinzufügen. Der Bereich wird in die Liste der vorhandenen Verweise aufgenommen.

- Wenn Sie nun in die zweite Tabelle klicken, um den entsprechenden Bereich zu markie-ren, werden Sie feststellen, dass bereits ein Bereich markiert ist. Wenn Sie den Bereich so übernehmen wollen, klicken Sie auf die Schaltfläche Hinzufügen.

- Im Bereich Beschriftung aus: können Sie anhaken, ob Sie die Beschriftung aus der obers-ten Zeile und der linken Spalte des markierten Bereichs übernehmen wollen. Wenn Kon-solidierungsbereich mit den Quelldaten verknüpft werden soll, haken Sie das entspre-chende Kästchen an. Wenn Sie abschließend auf die Schaltfläche OK klicken, erhalten Sie folgende gegliederte Auflistung.

32

Verknüpfungen

Vor der Zeilenbeschriftung ist eine Gliederungsspalte eingefügt worden. In der Bearbeitungsleiste sehen Sie, dass die Zelle C2 eine Verknüpfungs-formel enthält.

Die Ziffern oben zeigen die Ebenen. Wenn Sie auf die Ziffer 2 klicken, wird die zweite Ebene vollständig ange-zeigt (wie in der Abbildung).

Mit den Minuszeichen in der Gliede-rungsspalte können Sie jeweils ein-zelne Punkte der zweiten Ebene ein-ziehen. Das Minuszeichen wird dann zum Pluszeichen, um anzuzeigen, dass die Gliederung an reduziert wurde.

Bedingte Formate

33

6. Bedingte Formate

Bedingte Formatierung bedeutet, dass nur dann Formate auf einen markierten Bereich an-gewendet werden, wenn bestimmte Bedingungen zutreffen. Im Beispiel sollen alle Beträge unter 20 € grün, alle unter 500 € gelb, alle unter 1000 € hellrot und alle über 1000 € dunkel-rot formatiert werden.

- Klicken Sie in der Registerkarte Start auf den Befehl Bedingte Formatierung.

- Wählen Sie den Eintrag Regeln zum Hervorheben von Zellen und dort den Befehl Kleiner als

- Es öffnet sich das Dialogfeld Kleiner als. Hier tragen Sie den Wert 1000 ein und wählen aus dem Listenfeld die hellrote Füllung.

- In der Tabelle sehen Sie bereits in einer Vorschau, welche Zellen betroffen sind. Wenn Sie abschließend auf die Schaltfläche OK klicken, wird das Format auf die markierten Zellen angewendet.

- Nun rufen Sie erneut das Dialogfeld Kleiner als auf und tragen den nächsten Wert und die Formatierung ein. Bestätigen Sie mit OK. Das wiederholen Sie mit dem nächsten Wert.

Sie müssen auf die Reihenfolge achten, in der die Regeln eingegeben werden. Jede neue Regel kann die vorangegangene Regel überschreiben. Wenn Sie also beim kleinsten Wert mit grüner Füllung beginnen, wird der nächste höhere Wert die grüne Füllung überschreiben. Da grün bis 20 gehen soll und gelb bis 500 markiert. Bis 500 bedeutet aber inklusive 20. Deswe-gen wird alles in Gelb angezeigt.

Wenn die Regeln sich gegenseitig überschreiben, muss die Reihenfolge abgeändert werden. Dazu klicken Sie auf Bedingte Formatierung und wählen den Eintrag Regeln verwalten. Im Dialogfeld Manager für Regeln zur bedingten Formatierung ändern Sie dann die Reihenfol-ge, in der die Regeln ausgeführt werden sollen.

34

Tabellen

7. Tabellen

Tabellen sind ab der Version 2010 ein zusammenhängender Bereich auf einem Tabellen-blatt. Tabellen können sortiert bzw. gefiltert oder anderweitig ausgewertet werden konnten.

7.1 Tabellen erzeugen

Auswertbare Tabellen können Sie wie folgt erzeugen:

- Klicken Sie in einen Bereich, der zusammenhängende Daten enthält.

- Wählen Sie auf der Registerkarte Einfügen, in der Gruppe Tabellen, den Befehl Tabellen.

- Es öffnet sich das Dialogfeld Tabelle erstellen. Es wird ein Bereich markiert und in dem Feld Wo sind die Daten für die Tabelle? eingetragen. Die erste Zeile des Tabellenbe-reichs, Zeile 3, wird als Überschriftenzeile definiert.

- Alternativ zu dieser Vorgehensweise können Sie auf der Registerkarte Start, in der Grup-pe Formatvorlagen, den Befehl Als Tabelle formatieren anklicken. Auch dann öffnet sich das Dialogfeld Tabelle erstellen.

- Wenn Sie mit der Schaltfläche OK den Vorgang abschließen, ist die Tabelle formatiert, hat Filtersymbole an den Überschriften und eine zusätzliche Registerkarte Tabellentools wird aktiv.

Tabellen

35

Die Gruppe Eigenschaften enthält den Namen der Tabelle, in diesem Fall Tabelle3. Die-ser Name kann geändert werden. Sie klicken in das Feld und ändern ihn ab.

In der Gruppe Tools können Sie die Tabelle wieder in einen normalen Bereich umwan-deln. Die Formatierung bleibt allerdings erhalten.

In der Gruppe Externe Tabellendaten können Sie die Tabelle über den Befehl Exportie-ren auf einen Server hochladen. Wenn eine Tabelle aus einer externen Datenquelle stammt, kann sie aktualisiert werden oder ihre Verbindungseigenschaften können über-überprüft werden.

In der Gruppe Optionen für Tabellenformat sind Überschrift und Verbundene Zeilen angehakt. Das bedeutet, das die Überschrift sichtbar ist. Verbundene Zeilen bedeutet, dass jede zweite Zeile farblich anders ist, um die Lesbarkeit zu erhöhen. Haken Sie Erste Spalte und Letzte Spalte an, werden diese Spalten fett formatiert. Verbundene Spalten wiederum heißt, das jede zweite Spalte farblich hervorgehoben wird.

In der Gruppe Tabellenformatvorlagen können Sie sich eine andere Gestaltung der Tabelle auswählen. Sobald Sie auf eine Vorlage zeigen, zeigt die Livevorschau das veränderte Design bereits an.

Wenn Sie in der Gruppe Optionen für Tabellenformat anhaken, wird unter der letzten Zeile der Tabelle eine Ergebniszeile eingeblendet. Sie können unter jeder Spalte sich ein Ergebnis anzeigen lassen, in dem Sie auf den Listenpfeil klicken und die entsprechende Funktion aus-wählen. In der Abbildung wurde in der Spalte Preis netto die Funktion Summe ausgewählt. Außerdem können Sie in der Abbildung erkennen, dass die Datensätze ab der Zeile 112 dar-gestellt werden. Die Spaltenüberschriften A, B, C usw. sind durch die Überschriften der Ta-belle ersetzt worden. Das geschieht, sobald die Tabelle gescrollt wird.

Die Ergebnisse in der Ergebniszeile passen sich an, sobald in der Tabelle Filter gesetzt wer-den.

36

Tabellen

7.2 Filter in der Tabelle

Sie können einen Bereich filtern und sortieren, ohne dass Sie zuvor diesen Bereich in eine Tabelle umgewandelt haben. Auf der Registerkarte Start, in der Gruppe Bearbeiten finden Sie das Symbol für das Filtern und Sortieren. Wenn Sie aus dem Bereich vorher eine Tabelle generieren, haben Sie aber bestimmte Vorteile, z. B. können Sie sich in der Ergebniszeile die Ergebnisse der Filterung anzeigen lassen. Im Gegensatz zur früheren Excelversion erkennt das Programm, ob es sich um Text-, Zahlen- oder Datumfilter handelt.

Klicken Sie auf das Filtersym-bol, wird ein Menü entfaltet.

Im oberen Bereich stehen die Sortiermöglichkeiten.

Wenn Sie auf den Eintrag Da-tumsfilter klicken, wird ein weiteres Menü aufgeklappt. Es enthält u. a. auch dynami-sche Filter, z. B. Morgen, Heu-te, Gestern usw.

Im Suchfeld darunter, können Sie Daten eingeben. Die da-runter liegende Auflistung reduziert sich dann auf die Werte, die Sie im Suchfeld eingegeben haben.

Sie können aber auch Werte herausfiltern, die Sie in der Liste anhaken. Bei Datumsfil-tern ist standardmäßig zuerst nach Jahren zusammengefasst.

In einer Ebene darunter ist der Datumsfilter nach Monaten und zum Schluss nach Tagen zusammengefasst

Textfilter oder Zahlenfilter enthalten ebenfalls die passenden Filteroptionen.

Tabellen

37

7.3 Sortieren

Wenn Sie in der Tabelle auf das Filtersymbol klicken, können Sie wählen zwischen aufstei-gend und absteigend sortieren. Wenn Sie auf den Eintrag Nach Farbe sortieren klicken, kön-nen Sie Benutzerdefiniertes Sortieren auswählen. Neben dieser Möglichkeit gibt es noch weitere Wege, um in das Dialogfeld Sortieren zu gelangen.

Im Dialogfeld Sortieren können Sie mehrstufig sortieren. Im Listenfeld Sortieren nach wäh-len Sie das erste Feld aus, nach dem Sie sortieren möchten. Im Beispiel wurde das Feld Org-Einheit ausgewählt. Wenn Sie anschließend nach dem Feld Bestelldatum sortieren möchten, klicken Sie auf die Schaltfläche Ebene hinzufügen. Dort wählen Sie den entsprechenden Ein-trag aus.

Sie können die Ebenen auch wieder löschen, in dem Sie auf die Schaltfläche Ebene löschen klicken.

Neben der Schaltfläche Ebene kopieren gibt es zwei Pfeile. Mit diesen Pfeilen können Sie die Sortierreihenfolge ändern. Im Beispiel wird erst nach der Org-Einheit, dann nach dem Be-stelldatum sortiert. Im Beispiel ist die Zeile Bestelldatum markiert. Wenn Sie diese Reihen-folge ändern wollen, klicken Sie auf den Pfeil nach oben. Jetzt steht Bestelldatum an erster Stelle.

Im Listenfeld Reihenfolge ist die Vorgabe bei Textfeldern von A bis Z oder umgekehrt. Bei Zahlenfelder nach Größe beginnend beim kleins-ten Wert bis zum größten oder umgekehrt.

Wenn Sie die Stadtteile in der Abbildung nach dem Alphabet sortieren würden, würde die Folge mit Alte Neustadt beginnen und mit Süder-vorstadt enden. Allenfalls könnten Sie noch die Sortierreihenfolge umkehren und mit Südervorstadt beginnen.

Wenn Sie immer die Stadtteile in der Reihenfolge wie nebenstehend abgebildet sortiert haben wollen, können Sie selber Sortierfolgen de-finieren.

38

Tabellen

7.4 Benutzerdefinierte Listen

Um eine benutzerdefinierte Liste zu erzeugen, nach der Sie u.a. sortieren können, gehen Sie wie folgt vor:

- Markieren Sie die Zellen, die die Reihe enthält (im Beispiel oben die Zellen A3:A14)

- Klicken Sie auf die Registerkarte Datei und wählen Sie die Schaltfläche Optionen

- In den Excel-Optionen klicken Sie auf die Kategorie Erweitert. Im rechten Teil des Fensters wählen blättern Sie zum Bereich Allgemein.

- Wählen Sie dort die Schaltfläche Benutzerdefinierte Liste bearbeiten aus.

- Das Dialogfeld Benutzerdefinierte Listen öffnet sich. Der von Ihnen markierte Bereich ist bereits eingetragen. Wenn Sie jetzt auf die Schaltfläche Importieren klicken, wird die Liste übernommen.

Tabellen

39

Sie hätten die Stadtteile auch in das Feld Listeneinträge eintippen können oder Sie hätten im Nachhinein den Bereich markieren können. Sobald die Liste vorhanden ist, können Sie sie zum Autoausfüllen und zum Sortieren benutzen.

In der Abbildung sind die Stadtteile nach Alphabet sortiert. Klicken Sie im Listenfeld Rei-henfolge auf den Eintrag Benutzerdefinierte Liste. Das Dialogfeld Benutzerdefinierte Listen öffnet sich und Sie können ihre Liste auswählen.

Klicken Sie abschließend auf die Schaltfläche OK und ihre Liste wird als Sortierreihenfolge eingetragen.

40

Datenauswertung mit Teilergebnis

8. Datenauswertung mit Teilergebnis

Wenn Sie nicht lange filtern wollen, sondern die Daten nur nach bestimmten Kriterien zu-sammenfassen wollen, bietet es sich an, Teilergebnisse zu nutzen.

8.1 Teilergebnisse erstellen

Wenn Sie beispielsweise wissen wollen, wie viel für Hardware und für Software ausgegeben wurde, fassen Sie nach der Spalte Rubrik zusammen.

- Sortieren Sie die Spalte Rubrik. Wählen Sie auf der Registerkarte Daten, in der Gruppe Gliederung den Befehl Teilergebnis.

- Das Dialogfeld Teilergebnisse öffnet sich. Im Listenfeld Gruppieren nach: wählen Sie die Spalte aus, nach der zuvor sortiert wurde (im Beispiel Rubrik).

- Im Listenfeld Unter Verwendung von: wählen Sie eine Funktion aus. Zur Verfügung ste-hen z. B. Summe, Anzahl, Minimum, Maximum, Mittelwert.

- Im Listenfeld Teilergebnisse addieren zu: haken Sie das Feld an, über welches Sie, wie im Beispiel unten, eine Summe ziehen wollen.

- Klicken Sie auf die Schaltfläche OK, um die Auswahl zu bestätigen. Das Ergebnis sieht wie folgt aus:

- Die Tabelle erhält eine zusätzliche Gliederungsspalte. Es wurden drei Ebenen erzeugt. Die erste Ebene enthält das Gesamtergebnis der Spalte Preis netto, die zweite Ebene enthält das Ergebnis für Hardware und für Software. Wenn Sie auf die zweite Ebene der Gliede-rung klicken, sieht das Ergebnis wie folgt aus:

Datenauswertung mit Teilergebnis

41

8.2 Teilergebnisse entfernen

Wenn Sie die Teilergebnisse wieder entfernen wollen, müssen Sie zunächst in die Tabelle klicken. Dann wählen Sie wieder auf der Registerkarte Daten, in der Gruppe Gliederung den Befehl Teilergebnis. Im Dialogfeld Teilergebnis klicken Sie auf die Schaltfläche Alle entfer-nen. Die Gliederung der Tabelle wird aufgehoben.

8.3 Teilergebnisse bearbeiten

Wenn Sie nur die Werte der zweiten Ebene in ein anderes Tabellenblatt kopieren wollen oder diese Werte anders gestalten wollen, reicht es nicht aus, den reduzierten Bereich zu markieren. Es werden auch die ausgeblendeten Werte mit gestaltet oder kopiert. Damit die ausgeblendeten Werte nicht mit berücksichtigt werden, gehen Sie wie folgt vor:

- Markieren Sie den zu kopierenden Bereich und drücken die Funktionstaste F5. Das Dialog-feld Gehe zu öffnet sich. Um in dieses Dialogfeld zu gelangen, können Sie auch auf der Registerkarte Start, in der Gruppe Bearbeiten den Befehl Suchen und Auswählen ankli-cken. Auch dort finden Sie den Befehl Gehe zu.

- Im Dialogfeld Gehe zu klicken Sie auf die Schaltfläche Inhalte…. Sie gelangen in das Dia-logfeld Inhalt auswählen. Hier wählen Sie den Eintrag Nur sichtbare Zellen. Klicken Sie zum Schluss auf die Schaltfläche OK.

Der markierte Bereich ist nun vorbereitet. Sie können nun nur die sichtbaren Zellen ko-pieren bzw. gestalten.

42

Dateneingabe steuern

9. Dateneingabe steuern

Wenn Sie Daten auswerten möchten und dabei z. B. nach bestimmten Inhalten suchen oder filtern möchten, erhalten Sie nur dann ein zuverlässiges Ergebnis, wenn der Inhalt richtig eingegeben wurde. Im Beispiel unten sehen Sie, dass das Datum 31.8.2011 falsch eingege-ben wurde. Eingaben von Leertasten und Schrägstrichen bewirkt, dass die Zelle einen Text enthält. Die Weiterverarbeitung solcher Daten, z. B. in Pivottabellen gruppieren, ist dann nicht mehr möglich.

Um solche Fehleingaben zu vermeiden, sollten Eingabezellen mit Gültigkeitsprüfungen hin-terlegt werden.

- Markieren Sie den Bereich, in dem Sie nur bestimmte Eingaben zulassen wollen.

- Wählen Sie auf der Registerkarte Daten, in der Gruppe Datentools, den Befehl Daten-überprüfung.

- Es öffnet sich das Dialogfeld Datenüber-prüfung.

- Klicken Sie auf das Listenfeld Zulassen und wählen dort den Eintrag Datum.

- Wählen Sie im Listenfeld Daten, den Ope-rator größer als.

- Wenn Sie den Operator ausgewählt haben, erscheint das Feld Anfangsdatum. Hier tragen Sie ein, ab welchem Tag die Eintra-gungen in der Tabelle gültig sein sollen.

- Klicken Sie abschließend auf die Schaltflä-che OK.

Dateneingabe steuern

43

Zusätzlich können Sie einen Hinweis anbringen, was in die Zelle eingegeben werden darf. Klicken Sie im Dialogfeld Datenüberprüfung auf die Registerkarte Eingabemeldung.

Geben Sie in das Feld Titel, die Überschrift für das Quickinfo ein. Im Feld Eingabemeldung können Sie einen entsprechen Text formulieren. Wenn Sie auf die Zelle klicken, wird das Quickinfo, wie unten abgebildet, angezeigt.

Auf der Registerkarte Fehlermeldung können Sie den Text eingeben, der erscheinen soll, wenn eine falsche Eingabe gemacht wurde.

44

Dateneingabe steuern

Sie können auch eine Liste vorgeben, aus der ein Eintrag ausgewählt werden kann.

- Legen Sie auf einem anderen Tabellenblatt eine Liste an. Z. B. alle Na-men der Sachbearbeiter. Markieren Sie diesen Bereich und versehen ihn mit einem Namen. Im Beispiel heißt der Bereich Sach und ist auf die Zellen A1:A3 bezogen.

- Klicken Sie in die Zelle, in der die Auswahlliste aufklappen soll.

- Wählen Sie im Dialogfeld Datenüberprüfung die Registerkarte Einstellung. Im Listenfeld Zulassen wählen Sie den Eintrag Liste.

- Im Feld Quelle wählen Sie den Namen Sach. Auf der Registerkarte Eingabemeldung kön-nen Sie den Text eintragen, der erscheinen soll, sobald die Zelle angeklickt wird.

- Im Beispiel unten sehen Sie das Ergebnis der Eintragungen im Dialogfeld Datenüberprü-fung.

Wenn Sie außer den angebotenen Sachbearbeiternamen andere Ein-tragungen zulassen wollen, klicken Sie im Dialogfeld Datenüberprü-fung auf die Registerkarte Fehler-meldung. Dort wählen Sie im Lis-tenfeld Typ die Eintragung War-nung.

Geben Sie einen passenden Text ein. Nun bekommt man zwar den Hinweis, dass der eingetragene Namen nicht aus der Liste stammt. Er kann aber trotzdem eingetragen werden.

Menübänder ändern

45

10. Menübänder ändern

Sie können Menübänder ändern oder neu erstellen.

10.1 Ändern der Menübändern

Dazu gehen Sie wie folgt vor:

- Klicken Sie auf die Registerkarte Datei.

- In der Backstage-Ansicht klicken Sie auf die Schaltfläche Optionen, um in das Dialogfeld Excel-Optionen zu gelangen.

- Klicken Sie dort auf die Kategorie Menüband anpassen. Alternativ dazu gelangen Sie in die Kategorie Menüband anpasssen, in dem Sie mit der rechten Maustaste an irgendei-ner Stelle in das Menüband klicken und dort den entsprechenden Eintrag Menüband an-passsen auswählen.

Im Dialogfeld Excel Optionen sehen Sie in der Kategorie Menüband anpassen die Hauptre-

gisterkarten, wie Start , Ausrichtung usw. Diejenigen Hauptregisterkarten, die im Menü-band sichtbar sind, sind angehakt. Unter den Hauptregisterkarten finden Sie die Gruppen.

Unter der Registerkarte Start gibt es beispielsweise die Gruppe Schriftart Die Gruppe Schriftart setzt sich wiederum aus verschiedenen Befehlen zusammen, wie beispielsweise

die Schriftfarbe . Der Befehl Schriftfarbe wiederum verbirgt weitere Unterpunkte.

46

Menübänder ändern

Im Menüband erkennen Sie das am kleinen schwarzen Dreieck. In dem Dialogfeld Excel Op-tionen erkennen Sie es am Pluszeichen vor dem Befehl Schriftfarbe.

Veränderungen am Menüband können Sie vornehmen, in dem Sie bei den Hauptregisterkar-ten die Häkchen entfernen. Diese sind dann nicht mehr sichtbar. Sie können die Reihenfolge der Anordnung ändern. Dazu markieren Sie den entsprechenden Befehl oder die Gruppe und

klicken auf die Schaltfläche nach oben bzw. nach unten . Sie können die Gruppen oder

Befehle auch mit der Maustaste verschieben. Über die Schaltfläche Entfernen können Sie

Gruppen aus dem Menüband entfernen. Mit der Schaltfläche Zurücksetzen können Sie die Standardeinstellung des Menübandes wiederherstellen. Mit der Schaltfläche Importie-

ren / Exportieren können Sie die Anpassungen des Menübandes in eine separate Datei exportieren und auf andere Computer portieren.

10.2 Zusätzliche Registerkarte erzeugen

Klicken Sie im Dialogfeld Excel Optionen in der Kategorie Menüband anpassen auf die Schaltfläche Neue Registerkarte. Die neue Registerkarte wird nach der Registerkarte einge-fügt, die Sie markiert haben. Im Beispiel unten war die Registerkarte Überprüfen ausge-wählt. Eine neue Registerkarte wird darunter eingefügt. Zugleich wird eine neue Gruppe ein-gefügt.

Sie können nun mit rechter Maustaste in die neue Registerkarte klicken, um sie umzubenen-nen. Oder Sie markieren die neue Registerkarte und klicken auf die Schaltfläche Umbenen-nen. Es erscheint das Dialogfeld Umbenennen. Hier können Sie einen neuen Namen für die Registerkarte vergeben.

Die neue Gruppe können Sie mit dem gleichen Verfahren umbenennen. Sie können natürlich auch neue Gruppen in bereits bestehende Registerkarten einbauen.

In die Gruppen können Sie dann aus dem Bereich Befehle auswählen die von Ihnen benö-tigten Befehle auswählen. Die Voreinstellung ist allerdings Häufig verwendete Befehle. Wählen Sie aus dem Listenfeld Alle Befehle aus. Es wird eine alphabetische sortierte Liste

Menübänder ändern

47

aller zur Verfügung stehenden Befehle angezeigt. Wählen Sie daraus den passenden Befehl

. Klicken Sie abschließend auf die Schaltfläche Hinzufügen . Das Menüband sieht an-schließend wie folgt aus:

Im Prinzip ist in dem Standardmenüband bereits alles vorhanden, was man braucht. Allen-falls kann man die eine oder andere Gruppe mit Befehlen hinzufügen oder ändern.

Lernmaterial

49

Lernmaterial

Lernmaterial – Schulungsunterlagen, Übungsdateien, Lernprogramme und Tipps & Tricks –

finden Sie im Internet unter der Adresse:

https://www.afz.bremen.de/lernen

Wählen Sie das gewünschte Thema über die Menüstruktur am oberen Rand der Seite oder aus der Liste aus, die Sie im rechten Bereich über die Infobox Gesamtliste der Schulungsunterlagen in den einzelnen Untermenüpunkten erreichen können.

Hier können Sie Themen nachschlagen, Ihre Kenntnisse aktualisieren (z. B. bei neuer Pro-grammversion) oder sich zusätzliche Themen erarbeiten. Sie können das Lernmaterial als PDF-Dokumente am Bildschirm lesen, auf Ihrem Computer speichern oder ausdrucken. Zum Teil stellen wir zusätzlich Übungsdateien in gepackter Form (Zip-Archiv) zur Verfügung. Zu ein-zelnen Themen sind Lernprogramme vorhanden. Die einzelnen Lerneinheiten dauern 2 bis ma-ximal 15 Minuten und können bei Bedarf am Arbeitsplatz genutzt werden.

Tipps & Tricks

Oft sind es die kleinen Dinge, die die Arbeit am PC erleichtern. Dazu haben wir Tipps und Tricks zusammengestellt. Diese finden Sie sowohl bei den einzelnen Programmen als auch in einer Gesamtliste, die Sie über die Infobox Tipps und Tricks im rechten Bereich bei den einzelnen Untermenüpunkten erreichen können. Vielleicht entdecken Sie hier etwas, um Ihre Arbeit ef-fektiver zu gestalten.

Kompetenzzentrum E-Government (CC-EGov)

Sollten Sie als Beschäftigte der Freien Hansestadt Bremen bei Ihrer Arbeit auf Probleme stoßen, die beim Einsatz Ihrer Softwareausstattung auftreten (Probleme mit Word-Dokumenten, Excel-Tabellen etc.), können Sie sich mit Ihren Fragen, Problemstellungen oder Fehlermeldungen te-le-fonisch oder per E-Mail an uns wenden:

cc-egov@afz.bremen.de Tel. 16 999

Beschreiben Sie Ihre Frage bzw. die Fehlersituation und Ihre bisherige Vorgehensweise und fügen Sie die Dateien im Original-Dateiformat als Anlage bei. Wir beantworten Ihre Fragen so schnell wie möglich, in jedem Fall melden wir uns innerhalb weniger Tage bei Ihnen.