Grundlagen MS Excel

EXCEL Skript GliederungExcel Grundlagen.....................................................................................................................2

1. Eingabe von Text und Zahlen.....................................................................................................2

2. Das Kontextmenü im Blattregister..............................................................................................3

3. Das AutoAusfüllen und Benutzerdefinierte Listen.......................................................................4

Handhabung von Zeilen, Spalten und Zellen.......................................................................6Zeilen einfügen....................................................................................................................................6

Zeilen löschen.....................................................................................................................................6

Nur die Inhalte von Zeilen löschen......................................................................................................6

Spalten einfügen.................................................................................................................................6

Spalten löschen...................................................................................................................................7

Den Inhalt von Spalten löschen..........................................................................................................7

Zellen oder Zellbereiche kopieren und einfügen.................................................................................7

Zellen oder Zellbereiche ausschneiden und einfügen.........................................................................7

4. Formatieren von Zellen und Gestalten der Tabelle.....................................................................9

6.Formeln kopieren mit AutoAusfüllen..............................................................................................11

Die bedingte Formatierung..................................................................................................195. Fehler in Formeln beheben.......................................................................................................28

6. Die WENN- Funktion.................................................................................................................29

7. Seitenanpassung, Kopf-/Fußzeilen und Drucken.....................................................................33

8. Gültigkeitsprüfung.....................................................................................................................35

9. Die SUMMEWENN-Funktion....................................................................................................36

10. Erstellen von Diagrammen....................................................................................................40

11. Die ZÄHLENWENN-Funktion................................................................................................48

12. Arbeiten mit mehreren Tabellenblättern................................................................................51

13. Excel-Daten in anderen Office-Anwendungen mit OLE........................................................52

14. Zellen schützen.....................................................................................................................53

15. Eigene Mustervorlagen in Excel............................................................................................55

Excel Grundlagen

1. Eingabe von Text und ZahlenÖffnen Sie Excel über START=>Programme=>Excel. Das Excel-Fenster schaut so aus:

Die Spalten werden mit Buchstaben bezeichnet, die Zeilen werden mit Zahlen benannt. Die Schnittpunkte von Zeilen und Spalten heißen Zellen. Zellen können Text enthalten (=Textfelder), oder sind für Eingaben vorgesehen (= Eingabefelder) oder es sind Zellen, die Formeln enthalten und etwas berechnen. Das sind dann berechnete Felder.

Sie können die Zellen einzeln mit der linken Maustaste anklicken, um in ihnen schreiben zu können. Sie können aber auch die Pfeiltasten zum Navigieren nehmen.

Der Inhalt einer Zelle steht immer in der Eingabezeile.

Wenn eine Spalte zu klein ist, dann halten Sie den Rand eines der Spaltenköpfe mit der linken Maustaste gedrückt fest und ziehen die Spalte an die gewünschte Position. Genauso funktioniert es auch mit den Zeilenköpfen.

TIPP: Wenn eine Spalte automatisch an den Text angepasst werden soll, genügt auch ein Doppelklick auf den Spaltenbegrenzer!

Übung 1

2. Das Kontextmenü im Blattregister ist eine coole Sache, denn jedes Tabellenblatt lässt sich auch umbenennen. Dann können wir den Namen eines Blattes gleich mit

ausdrucken über die Schaltfläche in der Benutzerdefinierten Kopfzeile.

Gleich ausprobieren: Einfügen ,Löschen, Umbenennen und Verschieben/ Kopieren.

Aufbau einer FormelJede Formel beginnt mit einem „=“ in der Eingabezeile. Nicht die eigentlichen Werte in den Zellen werden eingesetzt, sondern immer nur die Zelladressen, oder auch „Zellbezüge“ genannt.

Sie haben eine leere Tabelle geöffnet.Tragen Sie in A1 Ihren Namen ein, damit Sie Ihr Exemplar nach dem Drucken erhalten.Erstellen Sie nebenstehende Tabelle:

Korrigieren Sie die Spaltenbreite über das Ziehen der Spaltenköpfe, bis die Tabelle mit allen Einzelheiten lesbar ist.

Berechnung der Anzahlung:Sie muss vom Kaufpreis abgezogen werden, also D5 minus D6

Klicken Sie bitte auf D7. Geben Sie ein „=“ ein.Klicken Sie mit der Maus auf D4, geben „ – „ ein und klicken auf D6. Beenden Sie die Formel mit der Return-Taste

Berechnung der Summe Zubehör (D14):

Aktivieren Sie die Zelle. Mit einem Klick auf wird die Summe Zubehör automatisch eingetragen.Es erscheint die Formel:

= S U M M E ( D 9 : D 1 3 )

Es ist Excel egal, ob Sie Groß- oder Kleinbuchstaben für eine Formel verwenden. Die Eingabe muss aber ohne Leerzeichen stattfinden!!Lösung:

3. Das AutoAusfüllen und Benutzerdefinierte ListenSie möchten sich für Arbeit und Freizeit danach einen detaillierten Stundenplan schreiben. Klicken Sie dazu mit der Maus in Zelle C1 und schreiben Sie „Montag“ hinein.

Wenn Sie jetzt mit der linken Maustaste am rechten unteren Rand von C1 festhalten und die Markierung nach rechts ziehen, dann lesen Sie, wie in die nächsten Zellen die folgenden Wochentage automatisch ausgefüllt werden.Ziehen Sie die Markierung bis Samstag.

Wenn Sie die Maus loslassen, erscheint ein Smart-tag und wenn Sie dort drauf klicken, sehen Sie, dass momentan von Excel Datenreihen ausgefüllt werden.

Klicken Sie jetzt in B2 und schreiben Sie „8.00 Uhr“ hinein.Kopieren Sie die Eingabe nach unten ( gehen Sie mit dem Curser in die untere rechte Ecke – kleines schwarzes Quadrat - , drücken die rechte Maustaste und ziehen gleichzeitig nach unten.) Sie sehen, dass nur der Text kopiert wird, die Uhrzeit bleibt erhalten. Beim AutoAusfüllen können Sie nicht Zahlen und Text gleichzeitig verwenden. Das zeigt Ihnen das folgende Smart-Tag:

Dumm gelaufen! Sie können aber ganze Zahlen, ohne die Minuten hinschreiben, dann erkennt Excel die Datenreihe. Der Unterricht soll von Montag – Freitag, 8 – 16 Uhr dauern.

Eigene Datenreihen erstellen

Sie können natürlich auch eigene Datenreihen erstellen. Der Unterricht beginnt um 8.00 Uhr und endet um 9.30 Uhr, danach gibt es 15 Minuten Pause und anschließend geht es 9.45 Uhr bis 11.15 Uhr in die nächste Runde, usw….

Öffnen Sie im Menü Extras - Optionen - Register „Benutzerdefinierte Listen“ und klicken ins Feld „Listeneinträge“:

Schreiben Sie in dieses Feld die Unterrichtszeiten.Gehen Sie jetzt auf den Button „Hinzufügen“ und Sie sehen, dass die eingegebenen Zeiten jetzt in die Listen im linken Feld mit aufgenommen wurden. Bestätigen Sie die neue Liste mit OK.

Klicken Sie in die Zelle B2.Schreiben Sie hier „8.00 – 9.30“ hinein und ziehen Sie die Markierung am Ausfüll-Kästchen wie gewohnt nach unten: Die neue Datenreihe wird jetzt eingefügt. Das können Sie mit einem Klick auf das Smart-tag auch noch einmal überprüfen:

Für Montag und Dienstag haben tragen Sie nur RECHT ein. Schreiben Sie in C2 „Excel“ und ziehen Sie die Markierung bis 16.15 nach unten. Sie sehen, hier ist keine Datenreihe, also wird der Text „Excel“ einfach kopiert.

Wiederholen Sie diesen Vorgang für Dienstag.

Am Mittwoch haben wir vormittags Textverarbeitung und nachmittags Internet. Schreiben Sie den Text „Internet“ einmal und schreiben Sie ein „I“ in die Zelle darunter. Sie sehen, wie Excel sich den Text gemerkt hat und ihn jetzt wiederholen möchte. Bestätigen Sie mit der ENTER-Taste und „Internet“ wird so übernommen.

Ergänzen Sie Donnerstag und Freitag noch nach diesem Muster:Übung 1a:Herr Dr. Böck hat Sie mit der Seminarorganisation für die nächste Woche beauftragt. Ein Haus haben Sie schon gebucht, jetzt müssen Sie nur noch des Doktors wirre Aufzeichnungen zu einem lesbaren Stundenplan zusammen schreiben. Benutzen Sie das AutoAusfüllen und erstellen Sie eigene Listen, so oft es Ihnen hier sinnvoll erscheint.

So sollte der Seminarplan aussehen:

Wenn Sie auch Gitternetz-Linien haben möchten, dann markieren Sie den ganzen Plan und gehen Sie anschließend auf Rahmen (Icon in der Format-Symbolleiste).

Lösung 1a:Montag bis Freitag ist eine schon vorhandene Liste.Für die Uhrzeiten erstellen Sie eine neue benutzerdefinierte Liste (Extras=>Optionen=>“Ben.Listen“).Mahlzeiten sowie Seminareinheiten schreiben Sie einmal und kopieren sie dann über das AutoAusfüllen-Kästchen von links nach rechts. Die freie Diskussion von 1 – 5 nummeriert sich ebenfalls selbst weiter, wenn Sie erst mal eine 1 im ersten Kästchen gesetzt und dann die Markierung erweitert haben.

Handhabung von Zeilen, Spalten und Zellen

Zeilen einfügen

Klicken Sie mit rechter Maustaste auf den Zeilenkopf, über welchem eine neue Zeile eingefügt werden soll.

Wählen Sie im Kontextmenü Zellen einfügen. Oberhalb der markierten Zeile wird jetzt eine neue leere Zeile eingefügt.

ODER Klicken Sie auf den Zeilenkopf, damit die Zeile markiert ist, über der

eine neue Zeile eingefügt werden soll.

Klicken Sie ins Menü Einfügen und wählen die Option Zeilen.

Zeilen löschen

Klicken Sie auf den Zeilenkopf der Zeile, die komplett – also Zellen UND Inhalte - gelöscht werden soll, mit rechter MT.

Klicken Sie auf die Option Zellen löschen.ODER Markieren Sie die Zeile über den Zeilenkopf.

Klicken Sie ins Menü Bearbeiten und wählen Sie den Menüpunkt Zellen löschen

Nur die Inhalte von Zeilen löschen

Klicken Sie dazu wieder auf den Zeilenkopf mit rechter MT

wählen Sie im Kontextmenü der Zeile Inhalte löschen.ODER Klicken Sie mit linker MT den Zeilenkopf an, dessen Inhalte

gelöscht werden sollen.

Gehen Sie im Menü Bearbeiten auf die Option Löschen …

Wählen Sie im Untermenü die Option Inhalte löschen aus.

Spalten einfügen

Klicken Sie mit rechter MT einen Spaltenkopf an, neben dem links eine neue leere Spalte eingefügt werden soll.

Wählen Sie im Kontextmenü wieder Zellen einfügen.

ODER Markieren Sie die Spalte mit einem Linksklick auf den Spaltenknopf

Wählen Sie im Menü Einfügen die Option Spalten.

Spalten löschen

Klicken Sie mit einem Rechtsklick auf den Spaltenkopf der zu löschenden Spalte

Wählen Sie im Kontextmenü die Option Zellen löschen (s.o.)ODER:

Markieren Sie die Spalte mit einem Linksklick

Wählen Sie im Menü Bearbeiten - Zellen löschen (s.o.)

Den Inhalt von Spalten löschen

Klicken Sie mit rechter MT auf den Spaltenkopf - wählen Sie im Kontextmenü Inhalte löschen

ODER: Markieren Sie wie gewohnt die Spalte mit einem Linksklick

Wählen sie im Menü Bearbeiten - Löschen - Inhalte löschen.

Zellen oder Zellbereiche kopieren und einfügen

Zellen kopieren und einfügen:

Aktivieren Sie die Zelle, die kopiert werden soll. (Sie können auch mehrere Zellen markieren.)

Wählen Sie BEARBEITEN – KOPIEREN ODERRechte Maustaste Option Kopieren. ODER

klicken Sie in der Standard-Symbolleiste auf das Symbol

Um die Zelle sehen Sie jetzt eine „Ameisenkolonne“ laufen. Das ist das Zeichen dafür, dass eine exakte Kopie dieser Zelle jetzt in der Zwischenablage herum liegt.

Klicken Sie mit rechter MT auf die Zelle, in der die Kopie eingefügt werden soll, und

wählen Sie im Kontextmenü die Option Einfügen oder klicken Sie auf das Icon Inhalte einfügen …

Mit können Sie nicht nur einfügen, sondern z.B. auch nur Inhalte einfügen.

Dafür ist der kleine schwarze Listenpfeil rechts neben dieser Schaltfläche gedacht.

Gleiches gibt es noch mal im Kontextmenü der Zielzelle: Dort gibt’s auch eine Option Inhalte einfügen…, die dann zu folgendem Dialog-Fenster führt:

Hier können Sie statt Alles auch Formeln wählen, um z.B. Formeln zu kopieren. Die relativen Zellbezüge passen sich selbstverständlich sofort an, so wie wir das schon vom AutoAusfüllen her kennen gelernt haben.

Zellen oder Zellbereiche ausschneiden und einfügen

Die Option Ausschneiden (Schere) sorgt dafür, dass Zellen oder Zellbereiche wie in einer Collage einmal ausgeschnitten werden und dann eine Ameisenkollonne um den ausgeschnittenen Bereich herumläuft. Zeichen für die Zwischenablage!Dann will Excel, dass Sie das Ausgeschnittene auch irgendwo wieder einfügen. Sollten Sie irrtümlich auf die Schere geklickt haben, gehen Sie in die Zwischenablage (eine Symbolleiste, s.o.) und fügen Sie die irrtümlich ausgeschnittene Zelle damit wieder ein.

Übung 3 Bitte schreiben Sie folgende Adressliste der Kanzlei Meier & Söhne ab:

Die Auszubildende Meier hat Einiges vergessen. Nehmen Sie bitte die Ergänzungen und Korrekturen vor.

Fügen Sie eine Spalte vor dem Namen für die Anrede ein.

Ein neuer Klient muss aufgenommen werden: Herr Günter Stössl, Finkenweg 18, 33333 Gütersloh, Telefon: 0 52 41 – 80 76 03

Um die Postleitzahlen später besser ordnen zu können, muss eine Spalte „PLZ“ vor dem Ort eingefügt und alle Postleitzahlen entsprechend dort eingetragen werden. Nutzen Sie Ausschneiden und Einfügen.

Eine weitere Ordnung nach Namen und Vornamen wäre klasse. Fügen Sie bitte nach „Name“ noch eine Rubrik „Vorname“ ein und ergänzen Sie diese Spalte entsprechend.

Frau Wörl möchte nicht länger Klientin der Kanzlei sein. Löschen Sie die gesamten Zellen.

Für diese 5 Mandaten fielen in diesem Monat verschiedene Gebühren an. Tragen Sie in Spalte E die Gebühren je Mandant ein.Herr Huber hat für 9000,00 € , Herr Beckstein 2000,00 €, Herr Stössl hat 6700,00 € ,Herr Pöllot 4500,00 € und Herr Krause 5900,00 €.

Bilden Sie die Gesamtsumme für die 5 Mandanten.

4. Formatieren von Zellen und Gestalten der TabelleDie Fahrradrechnung aus Übung 1 sieht noch recht öde aus. Wir werden ihr erst mal ein bisschen Make-up verpassen. Markieren Sie dafür bitte die Zellen B4 bis D20.Wählen sie anschließend im Menü Format die Option „AutoFormat“:

Und hier wählen Sie Standard 2 aus.

Entsprechend wird unsere Rechnungstabelle neu formatiert.

Heben Sie die Markierung wieder auf, in dem Sie auf irgend eine andere Zelle klicken:

Markieren Sie bitte jetzt B4 bis C20, rechte Maustaste klicken, wählen Sie die Option Zellen formatieren…

Und machen Sie Ihre Einstellungen, z.B. im Register Muster können Sie die Hintergrundfarbe von Grau in Gelb o.ä. verändern.Bestätigen Sie alle Änderungen mit OK und markieren Sie anschließend die Zellen D5 bis D20.

Im Kontextmenü wählen Sie wieder Zellen formatiere und gehen diesmal ins Register Zahlen. Dort wählen Sie die Kategorie Währung, denn alle €-Beträge sollen auf der Rechnung mit dem €-Symbol dargestellt werden.

Bestätigen Sie mit OK. Lassen Sie sich durch die seltsame Darstellung von Spalte D nicht verwirren. Vergrößern Sie die Spalte D und alles ist wieder sichtbar.

Zur besseren Übersicht brauchen wir noch Gitternetzlinien zwischen den Zellen. Momentan sind diese Linien nur grau hinterlegt, d.h. nicht sichtbar

im Ausdruck. Klicken Sie zur Kontrolle der Seitenansicht auf .

Um Gitternetzlinien zu erzeugen, markieren Sie den Bereich D5 bis D20 und wählen im Kontextmenü dieser Markierung wieder Zellen formatieren und diesmal das Register Rahmen. Machen Sie Ihre Einstellungen und bestätigen Sie wieder mit OK.

Übung 4:Jetzt dürfen Sie noch unseren Seminarplan für Herrn Böck ein bisschen verschönern. Die Überschrift in B1 und C1 habe ich zentriert, indem ich beide Zellen markiert habe und mit der Schaltfläche „Zusammenführen und zentrieren“ in der Format-Symbolleiste vereint habe.

Lösung: Versuchen Sie jetzt auch noch, dieser Tabelle ein anderes AutoFormat zuzuweisen.

Spalte zu schmal

5. Eigene ZahlenformateWenn Sie mit den gelieferten Formaten in Excel nicht zufrieden sind, können Sie auch selbst eigene Zellenformate erstellen.

Dazu markieren Sie die Zelle B8 zum geschätzten Absatz in der eben erstellten Tabelle und wählen im Kontextmenü der Zelle die Option Zellen formatieren… aus.

Jeder der vorgegebenen Typen kann von Ihnen individuell angepasst und erweitert werden.

Wenn Sie Text hinter ganze Zahlen haben möchten, müssen Sie ins Feld „Typ“ hineinklicken und diesen Text in Anführungszeichen schreiben.

Bestätigen Sie alles mit OK.

Übung 2:

Zahlenformatierung und Textformatierung (mit Lösung) Eingabewert Wert mit Format

1,5 1,5001234,5 1234,50 Eingabetext Formatierter Text

0 0,00 Tabelle Tabelle7777,77 7.777,77 € Funktion Funktion

7777,77 € 7.777,77 DiagrammDiagramm

0 - € absoluter Bezugabsoluter Bezug

0 0,00 €Tabellenblattregister

Tabellen-blattregister gelb

1111 $1.111,00Formel

Formel

1111 1.111,00 F schräge Sache Text mit Ausrichtung 45°

2600 2.600 Stück Nichts 

10 10 kleine Jägermeister

0,16 16%1,5 1 1/2

6.Formeln kopieren mit AutoAusfüllenFür die Rechtsanwälte A bis D möchte der Chef gerne die Geschäftsergebnisse berechnet haben. Eine Formel können Sie für diese Aufgabe schnell finden. Blöd ist nur, dass ich sie für jede Zeile neu schreiben soll. Zum Glück brauchen Formeln nur einmal erstellt werden und könen dann so oft Sie wollen kopiert werden.

Klicken Sie die linke MT, halten Sie sie gedrückt und ziehen die Maus über die Werte, die summiert werden sollen, bis in die Zelle, in der die Summe stehen soll (B2:F2):

Klicken Sie auf das Summenzeichen (∑) und Sie erhalten das Addtionsergebnis.

Anschließend halten Sie mit der Maus das Ausfüll-Kästchen am rechten unteren Rand der Summen-Zelle (F2) fest und ziehen die Markierung über die Zeilen darunter.In den folgenden Zellen (F3:F5) stehen nun die gewünschten Formeln. Sehen Sie sich die Formeln an. Was ist passiert?

Wenn jetzt überall derselbe Summenwert steht, dann liegt das an einer Einstellung unter Extras=> Optionen => Berechnen. Manchmal ist noch „Manuell“ eingestellt, dann müssen Sie mit der Taste F9 die Formelergebnisse aktualisieren. Einfacher ist es jedoch die Option „Automatisch“ zu wählen, dann werden die Formeln von Excel sofort neu berechnet.

1) Bitte erstellen Sie folgende Tabelle :

2) Formatieren Sie die Tabelle mit dem AutoFormat „Farbig 2“ . Ändern Sie anschließend die Hintergrundfarbe der Zellen in Weiß und setzen Sie die Schriftfarbe der Zellen auf Schwarz, damit wieder alles sichtbar wird. Fügen Sie ein vollständiges Gitterliniennetz in die Tabelle ein.

3) Die Zellen sind:Textfelder: A1, C3 bis G4, A5 bis E11 sowie A12.Eingabefelder: Übernehmen Sie meine Zahlen für die schon verkauften Karten, oder denken Sie sich für die Spalte der verkauften Karten selbst Werte aus.

Berechnete Felder:a) Freie Plätze in F5 bis F 11: Die Anzahl der freien Plätze ergibt sich aus D5 minus E5.b) Umsatz in G5 bis G 11: den Kartenpreis mit der Zahl der verkauften Karten multiplizieren.c) C12 bis G 12: Addieren Sie hier die Werte der Spalten C5 bis C 11,….,G5 bis G11.

Übung 3

Lösung zu Übung 3:

Lösung 3 mit Formeln:

Eingabe von FormelnArithmetische Operatoren   Um elementare mathematische Operationen (z. B. Additionen, Subtraktionen oder Multiplikationen) durchzuführen, Zahlen zu verknüpfen und numerische Ergebnisse zu erzeugen, verwenden Sie die folgenden arithmetischen Operatoren.

Vergleichsoperatoren  

Mit den folgenden Operatoren können Sie zwei Werte vergleichen. Wenn zwei Werte mit diesen Operatoren verglichen werden, ist das Ergebnis ein logischer Wert: WAHR oder FALSCH.

Textverkettungsoperator  Der Textoperator "&" verknüpft mehrere Textzeichenfolgen zu einem einzigen Textwert.

Bezugsoperatoren   Verknüpfen Zellbereiche für die Durchführung von Berechnungen mit folgenden Operatoren.

Erstellen Sie bitte folgende Tabelle:

2) Berechnen Sie die IST-Werte und die Differenzwerte, sowie die Zwischensummen aller Verkaufszahlen.

3) Welches sind hier Textfelder, Eingabefelder und berechnete Felder?

Textfelder sind:

Eingabefelder sind:

Berechnete Felder sind:

4) Weisen Sie der Tabelle ein AutoFormat zu.

Übung 4

Lösung zu Übung 4:

Lösung mit Formeln:

8. Statistische Funktionen MIN, MAX und MITTELWERTEs gibt in Excel über 200 verschiedene Funktionen. Z.B. =SUMME ( ; ) ist eine Funktion, die aus mind. 2 Argumenten ( Operanden) besteht. Es gibt in Excel auch eine Übersicht über alle Funktionen, den sog. Funktionsassistenten.

Aktivieren Sie die Zelle, in die Sie die Funktion eingeben möchten (B3), klicken auf fx und suchen die Funktion MAX, mit Doppelklick auswählen, mit der Maus die Zellen C1:C5 markieren, mit Return oder mit einem Klick auf das grüne Häkchen die Formel beenden.

Geben Sie die Funktion =MIN(C1:C5) ein. um die kleinste Zahl des aufgeführten Bereiches anzeigen zu lassen.

Geben Sie die Funktion =MITTELWERT(C1:C5) ein, um den Mittelwert der im angegebenen Bereich liegenden Zahlen anzeigen zu lassen. Bei dieser Funktion addiert Excel alle im angegebenen Bereich befindlichen Zahlen und teilt das Ergebnis durch die Anzahl der addierten Werte.

Möchten Sie wissen, wie viele Zellen im angegebenen Bereich Daten enthalten, dann verwenden Sie die Funktion =ANZAHL(C1:C5).

Gängige Fehler bei der Eingabe von Funktionen sind vergessene Gleichheitszeichen, Tippfehler im Funktionsnamen und fehlende beziehungsweise falsch gesetzte Klammern. Sollten Sie vergessen haben, eine Klammer zu schließen, dann macht das nichts. In der Regel merkt das Excel von selbst und schließt die Klammer automatisch. TIPPS

Um nicht zusammenhängende Bereiche und einzelne Zellen in Funktionen einzufügen, separieren Sie diese mit Strichpunkten. Also beispielsweise =MIN(B4;D8;L10).

Funktionsnamen und auch Spaltennamenkönnen nach Belieben klein (min, b4) oder groß (MIN, B4) geschrieben werden.

Wenn Sie sich mit den vielen Funktionen von Excel vertraut machen möchten, dann werfen Sie einen Blick in die Hilfefunktion. Wählen Sie dazu im Hilfe-Menü (das Fragezeichen neben dem Fenster-Menü) den Punkt Microsoft Excel-Hilfethemen. Im Register INHALT doppelklicken Sie auf Informationen zum Nachschlagen und dann auf Tabellenfunktionen.

Sie können Funktionen auch mit Hilfe des Funktions-Assistenten eingeben. Dazu klicken Sie zuerst auf die Zelle, in die Sie die Funktion eingeben wollen, und danach auf die fx-Schaltfläche in der Eingabezeile. Der Assistent führt Sie dann Schritt für Schritt durch den gesamten Vorgang.

Bitte beachten Sie auch die Möglichkeit des AutoBerechnens in der Statusleiste rechts-mittig-unten im Excel-Fenster:Wenn nämlich mindestens 2 Zellen markiert sind, zeigt Excel bei diesem Statusbereich automatisch die AutoSumme an. Diese Berechnungsfunktion ist voreingestellt. Um ein anderes AutoBerechnen zu aktivieren, klicken Sie mit der rechten Maustaste auf diesen Bereich und wählen Sie eine andere Funktion durch Anklicken aus. Anschließend wählen Sie einen zu berechnenden Zellbereich aus durch Markieren mit der Maus. Dann sehen Sie in der Statusleiste das neue Ergebnis.

1)Erstellen Sie diese Tabelle zur Ermittlung der Kundenzufriedenheit einer Bank:

2) Ermitteln Sie für jeden Dienst den jeweiligen Mittelwert in Zeile 12.3) Schreiben Sie in A13 den Text „Kleinster Wert“ und ermitteln Sie in B13 mit Hilfe der entsprechenden Funktion diesen Wert aus dem Bereich B2 bis B11, ebenso in C13 und D13.4) Schreiben Sie in A14 den Text „Höchster Wert“ und ermitteln Sie in B14 den größten Wert aus dem Bereich B2 bis B11. Verfahren Sie ebenso für die anderen beiden Dienste in C14 und D14.

Lösung zu Übung 5:

Lösung 5 mit Formeln:

Übung 5

Die bedingte FormatierungZum farbigen Hervorheben einzelner Zellen bietet Excel die Möglichkeit beim Erfüllen von bestimmten Bedingungen die Formatierung entsprechend zu ändern. Das kann sehr sinnvoll sein, wenn Sie bestimmte Ergebnisse automatisch farbig signalisiert haben möchte.

Beispiel: Erstellen Sie einen IST/SOLL-Vergleich Ihrer Einnahmen. Übertrifft das IST Ihr SOLL deutlich, soll dieses Ergebnisse farblich hinterlegt angezeigt werden. Ein bisschen angeben tut schließlich jedem gut.Bitte erstellen Sie zum besseren Verständnis folgende Tabelle in A1 bis C2:

In Zelle E7 schreiben Sie bitte den SOLL-Wert von 30.000,--€ hinein.

Markieren Sie den Zellbereich, der bedingt formatiert werden soll, also B2 bis C3.

Wählen Sie im Menü Format die Option bedingte Formatierung… aus.

Es erscheint ein Dialogfenster:

Da der Vergleichs-Soll-Wert in der Zelle E7 steht, müssen folgende Einstellungen vorgenommen werden

Klicken Sie jetzt auf die Schaltfläche Format… und bestimmen Sie zur Hervorhebung eine andere

Schriftfarbe und einen anderen Hintergrund nach eigenem Ermessen.

Ergebnis: Zelle C2 ist jetzt farbig hervorgehoben wegen Erfüllen der von uns gesetzten Bedingung!

Artikel 1. Quartal 2. QuartalHemden 13.400,--€ 36.340,--€

Zur Auswertung eines Leichtathletik-Wettkampfes in Ihrem Sportverein werden Sie beauftragt, folgende Tabelle unter dem Namen „sportstatistik.xls“ zu erstellen und zu speichern:

1) Berechnen Sie den Mittelwert, sowie den schlechtesten und den besten Wert für alle 3 Disziplinen.2) Die Werte, die beim Weitsprung über 5,1 liegen (die Sieger), sollen mit bedingter Formatierung gelb

unterlegt sein, die Standardschrift dabei in orange.

Lösung 5a:

TIPPS Möchten Sie die SUMME-Funktion über eine ganze Zeile oder Spalte verteilen, haben Sie die Wahl: die

SUMME-Schaltfläche und das Ausfüllkästchen.

In der Regel ist der Mausklick auf die Schaltfläche die schnellere Methode. Anders sieht es jedoch bei den restlichen Funktionen aus, für die es keine Schaltfläche gibt.

Wenn Sie Formeln mit dem Ausfüllkästchen vervielfältigen, sollten Sie besonders acht geben, dass die

Zellreferenzen (=Zellbezüge; ist der Wert wirklich in B2 und nicht in C2???) stimmen. Ziehen Sie eine Funktion nach rechts, ändert Excel den Buchstaben der Zelladresse, ziehen Sie die Funktion nach unten, erhöht Excel die Zahl.

Übung 5a

Beim Rechtsanwalt Gütlich werden einige Angestellten nach einem festgesetzten Stundensatz bezahlt.

In folgender Tabelle ist festgehalten, wie viele Stunden jede Mitarbeiterin pro Monat arbeiten möchte.

NameVereinbarte Arbeitszeit pro Monat

Bertram 36Diesel 32Ebermann 40Ganser 34Maier 40Mutz 38

Erstellen Sie eine Gehaltsaufstellung für jede Mitarbeiterin nach folgendem Muster:

A B C D E F G H I J3

NameStunden-lohn

Anzahl Stunden

Prämien-punkte

Anzahl Über

stundenGrund-lohn Prämie

Lohn für Überstunden

Vor-schuss

Aus-zahlung

4 Bertram 17 40 1  5 Diesel 19 32 2  6 Eberman

n 15 40 5007 Ganser 16 40 1  8 Maier 20,5 40 3  9 Mutz 15 40 2  

a) Berechnen Sie die Anzahl der Überstunden.

b) Berechnen Sie den Grundlohn (Produkt aus Stundenlohn und Anzahl vereinbarter Stunden). Überstunden werden extra berechnet.

c) Für besondere Aufgaben bzw. für besonderen Einsatz erhalten die Angestellten Prämienpunkte. Je Prämienpunkt erhält eine Angestellte 30,00 € zusätzlich zu ihrem Gehalt.

d) Für jede Überstunde erhalten die Mitarbeiterinnen einen Lohnaufschlag von 10 %.

e) Berechnen Sie den Auszahlungsbetrag für jede Mitarbeiterin.

Formatierungen:

Geht der Text weit über den Zellrand, soll ein Zeilenumbruch stattfinden.Der Inhalt in den Spalten C, D und E soll zentriert sein.Die Tabelle soll durch entsprechende Rahmen/Linien aufbereitet werden.

Übung

Lösung zu Übung 6:

Lösung mit Formeln:

Übung 8

Übung 7Artikel Menge Nettopreis

pro StückArtikelgruppe

Umsatz-steuer

Brutto-preis pro Stück

Gesamtpro Artikel

Fichte, 60 cm 8 6,20 Pflanzen 0,07Blumenerde, 50 l 4 2,99 Erde 0,19Rose, "Red Ball" 2 5,8 Pflanzen 0,0720er Beutel Tulpen 5 3,25 Zwiebeln 0,07Tonschale, 45 cm 1 15 Töpfe 0,19Schaufel, groß 1 21,90 Geräte 0,195er Beutel Narzissen 3 3,75 Zwiebeln 0,07                     Gesamtpreis:          Verpackungskosten:          Endpreis:

a) Ermitteln Sie den Bruttopreis entsprechend der Umsatzsteuer für den einzelnen Artikel.b) Ermitteln Sie den Bruttogesamtpreis des Artikels.c) Ermitteln Sie den Gesamtpreis aller Artikel. d) Die Verpackungskosten betragen 2% vom Gesamtpreis.e) Der Endpreis ist der Gesamtpreis plus Verpackungskosten.

ÜBUNG

Sie werden gebeten für ein 5-Familien Wohnhaus die gesamten Mietkosten pro Familie zu ermitteln. Ihnen liegt die folgende Tabelle vor:

Öffnen Sie eine neue Datei.Schreiben Sie Ihren Namen in die Zelle A1Beginnen Sie in Zelle A3 mit der Übertragung der Tabelle.

Gesamtkosten der Abfallbeseitigung: 280,00 €

5-Familien-Haus Vogelsanger Str. 295

Woh-nungs-größe in qm

Miete pro qm

Garagen-stellplätze

Abfall (in Liter)

Kosten Abfall pro Familie

Gesamt-miete (kalt) in €

Heizungsumlage in €

Kosten in €

Schmitz, Peter+Irene 74 10 2 120Berg, Wilhelm 82 11,50 1 70Busch,

Paul+Rosi 74 9 1 100Tater,

Bernd+Agnes 66 9 3 100von Fels,

Dagmar 54 8,25 0 50Summe e)

Aufgaben

a) Ermitteln Sie den Anteil (Literzahl) an den Gesamtkosten der Abfallbeseitigung.b) Ermitteln Sie die Gesamtmiete (kalt).c) Berechnen Sie die Heizungsumlage pro Wohnung. Diese betragen 0,32 € pro qm. d) Ermitteln Sie die Gesamtkosten, indem Sie zur der Gesamtmiete (kalt) die Heizungsumlage, die Kosten der

Abfallbeseitigung und die Kosten für die Garagennutzung (30 € pro Stellplatz) addieren. e) Berechnen Sie die Gesamtzahlungen des Hauses..

f) In der Spalte E soll hinter der Zahl ein l (Liter) stehen, z. B. 10 l. g) Speichern Sie die Tabelle unter Haus.xls ab.

Lösung zu Übung 7:

Lösung mit Formeln:

Lösung zu Übung 8:

Lösung mit Formeln:

Absolute und relative BezügeWenn Sie Formeln mit dem Ausfüllkästchen kopieren, werden die Zellbezüge automatisch relativ zu ihrer Position aktualisiert. Deshalb nennt man sie auch relative Bezüge. Manchmal möchte man jedoch, dass die Bezüge beim Kopieren nicht geändert werden. Deshalb können Sie auch so genannte feste Bezüge definieren.

Versuchen Sie zunächst eine Standardformel einzugeben und mit dem Ausfüllkästchen zu kopieren. Ziehen Sie nun das Ausfüllkästchen bis zur Zelle C8.

Nachdem Sie die Maustaste loslassen, sehen Sie die Ergebnisse: In den Zellen C5 bis C8 wurde die Mehrwertsteuer nicht hinzuaddiert. Klicken Sie eine der fehlerhaften Zellen an. Wie Sie sehen, wird hier mit einer Zelle gerechnet, die offensichtlich leer ist (B11).In den anderen Zellen der Spalte C wird Bezug genommen auf die Zellen B12, B13 etc. Ganz offensichtlich wurden beim Nachuntenziehen der Formel alle Zellbezüge jeweils eine Zeile tiefer gesetzt.

Die Mehrwertsteuer steht jedoch in Zelle B10 und nicht in B11, B12 etc. Dieser (Zell-)Bezug hätte also nicht verändert werden dürfen. Doppelklicken Sie auf die Zelle C4, um in den Bearbeitungsmodus zu gelangen.Danach geben Sie bei B10 jeweils vor das B und vor die 10 ein Dollarzeichen ein.

Odersetzen Sie den Textcursor in der Bearbeitungsleiste in die Zelladresse, die absolut werden soll und benutzen Sie die Taste F4.

Jetzt handelt es sich bei $B$10 um einen absoluten Bezug, d. h. beim Kopieren bleibt die Zelladresse erhalten. Man sagt auch, die Zelle wird eingefroren. Beenden Sie die Änderung mit Return. Kopieren Sie die Formel bis C8.Sehen Sie sich die Formeln in C5:C8 an. In jeder Formel steht die Zelladresse $B$10.

TIPPS Eine Zelle, auf die sich feste Bezüge beziehen, können Sie selbstverständlich beliebig

verschieben. Die Bezüge werden dabei automatisch aktualisiert.

In dem hier gezeigten Beispiel steht der Mehrwertsteuersatz in einer separaten Zelle. Die Frage

lautet, warum das so ist? Schließlich könnte man ihn in jede Formel der letzten Spalte einbauen

und dann auf feste Bezüge verzichten. Die Antwort ist einfach: Ändert sich die Mehrwertsteuer,

müssen Sie nur eine Zelle ändern, andernfalls alle, die den Satz enthalten.

Relative Bezüge werden durch je ein Dollarzeichen vor dem Buchstaben und der Zahl definiert.

Wenn Sie möchten, können Sie auch relative mit festen Bezügen mischen. Zum Beispiel, wenn

sich zwar die Zeile ändern darf, die Spalte jedoch nicht (Dollarzeichen vor dem Buchstaben,

keines vor der Zahl).

HERDT-VERLAG: RELATIVE UND ABSOUTE ADRESSIERUNG EINFÜGEN.

Fehler in Formeln behebenNormalerweise sollten Ihre Formeln problemlos funktionieren. Aber wie es der Teufel will, schleichen sich manchmal Fehler ein. In der Regel bemerkt Excel dies und zeigt dann in den entsprechenden Zellen kryptische Meldungen an, wie etwa: #DIV/0!, #BEZUG! oder #NAME?.

Wenn Sie den Ausdruck #DIV/0! in einer Zelle sehen, dann versucht die Formel in dieser Zelle, einen Wert durch Null zu teilen.Wie Sie wissen, kann man nicht durch Null teilen. Prüfen Sie, ob in der fraglichen Zelle eine Null steht oder leer ist (leere Zellen erhalten automatisch den Wert Null).

Sollten Sie in einer Zelle die Meldung #NAME? sehen, dann steht in der dortigen Formel ein Ausdruck, den Excel nicht interpretieren kann. In der Regel wurde dabei ein Funktions- oder Bereichsname falsch geschrieben. Sollte Ihre Formel als Text in der Zelle angezeigt werden und jegliche Berechnung verweigern, dann haben Sie das führende Gleichheitszeichen vergessen.

Überprüfen Sie alle Rechenergebnisse in Ihrer Tabelle. Häufig schleichen sich nämlich Fehler ein, die gar nicht angezeigt werden. Z.B. können Sie falsche Bezüge beziehungsweise Zelladressen in Formeln angegeben haben. Excel zieht dann die Rechnung stur durch, sobald sie nicht gegen geltende Regeln verstößt. Letztendlich sollten Sie auch das Zahlenmaterial überprüfen. Tippfehler können hier verheerend wirken.

Enthält Ihre Formel viele Operatoren (summe,wenn,und,oder) und produziert sie offensichtlich falsche

Ergebnisse, dann fehlen eventuell Klammern. Excel nimmt Berechnungen nicht von links nach rechts

vor.

Die Fehlermeldung #NAME? kann auch erscheinen, wenn Sie den Namen eines Bereiches oder einer

Zelle falsch eingegeben haben.

Ist eine Zahl zu lang, um in einer Zelle angezeigt zu werden, dann wird sie als Reihe von

Doppelkreuzen (#######) dargestellt, s. Übung 1. Um die Zahl korrekt anzuzeigen, ändern Sie einfach

die Spaltenbreite.

Die WENN- FunktionJede und jeder von uns kennt diese Drohung als Erziehungsmittel: „WENN Du brav bist, DANN bekommst Du einen Keks, sonst bekommst Du Stubenarrest“.

Als Formel könnten wir das so ausdrücken:= WENN(ich=brav;Keks;Stubenarrest)

Ausgeschrieben heißt die Formel: = WENN(Bedingung;Wahr;Falsch) Oder WENN(Frage;Ja;Nein)

Übung 10: In einem Schulsekretariat fallen wichtige Sachen an. Weil Lehrer Winkelmann zwar eine Seele von Mensch ist, aber leider keine Ahnung von Excel hat, bittet er Sie, folgenden Notenspiegel für seine Klasse auszurechnen (Bestanden bei <=4, sonst nein)

Übung 11: Ihre Bürovorsteherin beauftragt Sie die monatliche Inventur der Büro-Utensilien vorzunehmen. Sie bittet Sie eine EXCEL-Tabelle zu erstellen, der zu entnehmen ist, welche Büromaterialien nachbestellt werden müssen. Lösen Sie die Aufgabe mit Hilfe von EXCEL-Funktionen.

Übung 11a:

1) Bitte errechnen Sie Summen, Minimalwerte, Maximalwerte und Durchschnittswerte aus 2007 und 2008.

2) Berechnen Sie die Umsatzdifferenzen für jeden Monat aus den beiden Jahren. Bei einer Senkung erhalten Sie einen negativen Wert, bei einer Steigerung oder einem Gleichstand sollten Sie positive Zahlen in der Differenz-Spalte stehen haben.

3) In der Spalte „Änderung“ sollen die Worte Senkung oder „Steigerung bzw. Gleichstand“ stehen.

Lösung 10:

Lösung 10 mit Formeln:

Lösung 11:

mit Formeln:

SituationEin Kunde bestellt bei einem Versandhaus fünf Artikel. Das Versandhaus berechnet bei Bestellungen unter 50,00 € eine Versandkostenpauschale von 3,50 €, bei Bestellungen ab 50,00 € entfällt die Versand-kostenpauschale. Sie möchten wissen, ob bei der vorliegenden Bestellung die Versandkostenpauschale anfällt. Erstellen Sie eine EXCEL-Tabelle, die für beliebige Verkäufe die Versandkotenpauschale ermittelt.

AUFGABENErstellen Sie die untenstehende Tabelle:

Zelle Aufgabe1. A1 Tragen Sie Ihren Namen ein.2. A5 Artikel 1 eingeben3. A6:A9 Reihe ausfüllen – automatische Nummerierung

Stellen Sie den Cursor in Zelle A5. Kopieren Sie die Markierung 4 Zeilen nach unten. Lassen Sie die Maustaste los.

4. B5:B9 Geben Sie die Zahlenwerte ein.5. B10 Ermitteln Sie den Warenwert6. B11 Die Versandkostenpauschale soll automatisch von EXCEL ermittelt werden. Falls keine

Versandkostenpauschale anfällt, bleibt die Zelle leer. Erstellen Sie für dieses Problem zuerst ein Struktogramm und anschließend eine entsprechende Formel.

7. B12 Berechnen Sie den Gesamtbetrag.8. B12 Der Gesamtbetrag soll nur bei einem Warenwert unter 50,00 € erscheinen, sonst bleibt das

Feld leer. 9. A12, A11

Die Texte in A12 und A11 sollen nur erscheinen, wenn der Kunde eine Versandkostenpauschale zahlen soll, sonst bleibt das Feld leer.

10. A10 Bei einem Warenwert unter 50,00 € soll das Wort Warenwert ausgegeben werden, sonst das Wort Gesamtbetrag.

11. Speichern Sie Ihre Tabelle unter Versandhaus.xls ab12. Alle Werte sollen das Format € erhalten. Nehmen Sie die notwendigen Einstellungen vor!

Weiterführende Aufgaben 113. Kopieren Sie Ihre bisherige Lösung in ein neues Tabellenblatt, so dass Sie zwei identische

Lösungen erhalten:Vorgehen:Bearbeiten – Blatt verschieben kopieren – vor Blatt2 - Kopieren – ok

14. Das Versandhaus ändert die Versandkostenbedingungen:bei Bestellungen bis 50,00 € beträgt die Versandkostenpauschale 3,50 €,bei Bestellungen bis 150,00 € beträgt die Versandkostenpauschale 2,00 € bei Bestellungen über 150,00 € werden keine Versandkosten berechnet.Erstellen Sie ein Struktogramm. Entwickeln Sie entsprechende Formeln.

Weiterführende Aufgaben 215. Kopieren Sie Ihre bisherige Lösung in ein neues Blatt/Tabelle – siehe 13.16. Nach dem Gesamtpreis soll jeweils die 16 %ige Umsatzsteuer ermittelt werden. Ändern Sie

Ihre Formel, so dass dieses Problem gelöst werden kann.Vorgehen:a) Überlegen Sie, in welchen Zellen die Umsatzsteuer und der Bruttobetrag stehen sollen.b) Stehen in den Zellen schon Formeln?c) Wie müssen evt. schon vorhandene Formeln umgestaltet werden?

A B C A B3 ABRECHNUNG

Oder

2. Möglichkeit

ABRECHNUNG45 Artikel 1 3,12 € Artikel 1 14,00 € 6 Artikel 2 7,16 € Artikel 2 19,90 € 7 Artikel 3 3,25 € Artikel 3 4,98 € 8 Artikel 4 14,80 € Artikel 4 14,80 € 9 Artikel 5 16,75 € Artikel 5 4,78 €

10 Warenwert 45,08 € Gesamtbetrag 58,46 € 11 Versandkostenpauschale 3,50 €

12 Gesamtbetrag 48,58 €

Lösung 11a:

Lösung zu Übung 11a mit Formeln:

Seitenanpassung, Kopf-/Fußzeilen und DruckenÜbung 12:Der Hausmeister unserer Schule braucht dringend die BestellListe aus Übung 11. Bitte drucken Sie ihm diese Liste aus. Das Schullogo, der Schulname, BESTELL-LISTE Büro und das Tagesdatum soll im Bestellkopf erscheinen

1. Klicken Sie auf der Standardsymbolleiste auf

2. Klicken Sie im Seitenansichts-Fenster in der Symbolleiste auf Layout…:

3. Richten Sie die Tabelle auf dem Blatt im Querformat vertikal und horizontal zentriert aus.

Die Seitenränder lassen wir so, wie sie sind. Mit Optionen…können Sie den Drucker anpassen.

4. Wechseln Sie ins Register Kopf-/Fußzeile und klicken Sie auf

5. Erstellen Sie eine Kopfzeile mit dem Logo „Reinhard-Mohn-Berufskolleg“ auf Laufwerk P:/Spahn-Wagner/RN71 und mit dem Namen der Schule (Reinhard-Mohn-Berufskolleg, 33330 Gütersloh) im linken Feld, „Bestellliste Büro“ in der Mitte und dem aktuellen Datum im rechten Feld.

6. Formatieren Sie das Textformat mit der Schaltfläche .

7. Das Schul-Logo fügen Sie mit einem Klick auf in die Kopfzeile ein.

8. Mit können Sie Größe und Bildsteuerung für das Logo noch anpassen.9. Bestätigen Sie mit OK und verlassen Sie die Seitenansicht über Schließen.10. Gehen Sie jetzt auf Menü Datei und auf die Option Drucken…

11. Bei besonders riesigen Tabellen empfiehlt es sich, nur bestimmte Bereiche auszudrucken. Das können Sie tun, indem Sie einen Druckbereich festlegen.- Markieren Sie zuerst den Zellenbereich, der auf Ihrem Ausdruck erscheinen soll.- Klicken Sie dann ins Menü Datei und dort auf die Option Druckbereich…, dann auf die erste Unteroption „Druckbereich festlegen“.

12. Gehen Sie in die Seitenansicht (z.B. auch möglich im Menü Datei, Seite einrichten… In jedem

der 4 Register gibt es einen Button .

Im 4. Register Tabelle gibt es noch verschiedene Möglichkeiten, eine Tabelle auszudrucken, z.B. mit oder ohne Gitternetzlinien und sogar mit Zeilen- und Spaltenköpfen zur besseren Orientierung.

Sie sehen schon, dass Sie auch hier den Druckbereich festlegen können.

Bei besonders riesigen Tabellen können Sie auch Zeilen- oder Spaltenüberschriften auf jeder Folgeseite im Ausdruck wiederholen lassen.

Und Sie können sogar bestimmen, in welcher Reihenfolge die Seiten dargestellt werden sollen.

Im folgenden werden alle Tabellen auf folgende Art ausgedruckt:Papierformat: Hoch- oder Querdruck (Je nach Aufgabenstellung)

Anpassen auf 1 Seite (schwarz markieren)Tabelle: Gitternetzlinien und

Zeilen und Spaltenüberschriften markieren.Kopf/Fußzeile: geben Sie bitte Ihren Namen in die Kopzeile ein.

GültigkeitsprüfungSie wollen Ihre Leichtsinnsfehler bei der Dateneingabe zumindest ein bisschen verhindern. So richtig toll geht das in Excel leider nur mit Zahlenfeldern (die Eingabe von Text kann ich dort verhindern), perfekt wird die Datenerfassung und Datenüberprüfung erst mit Access.

Übung 12a:1) Öffnen Sie die Mandantenliste aus Übung1.

2) Markieren Sie den Zellbereich F3:F7 (Spalte PLZ ohne Überschrift).

3) Wählen Sie im Menü Daten die Option Gültigkeit…

4) Geben Sie im Register Einstellungen folgendes ein:

5) Klicken Sie auf das Register Fehlermeldungen und geben Sie folgendes hier ein:

6) Klicken Sie auf OK und geben Sie bei PLZ nun einen falschen Wert zur Probe ein.

8) Setzen Sie in der Spalte Telefon Warnungen bei Telefonnummern , wenn Sie nicht ganze Zahlen zwischen 100 und 9999999 enthalten. Geht nur mit Nummern ohne Vorwahl.

9) Probieren Sie es mal mit Stopps in den Spalten Vorname, Straße und Ort. Prüfung: Es werden Textlängen zwischen 3 und 20 Zeichen zugelassen.Stopp geht nicht? Klar geht es nicht! Zahlen und Buchstaben werden beide als Text(-länge) von Excel verstanden. Erinnern Sie sich bitte an unsere „22.5“.

10) Setzen Sie diese Eingabemeldung für die Spalte Straße und deaktivieren Sie für diese Spalte die Fehlermeldung im 3. Register.

11) Überprüfen Sie alles noch mal mit falschen Probe-Eingaben.

Übung 12b:

a) Herr Hansen beantragt im Sommer 16 Urlaubstage, Herr Becker möchte 13 Tage, Frau Fischer beantragt 11 Tage und für den neuen Mitarbeiter sind 18 Urlaubstage vorgesehen und schon beantragt.

b) Weil Sie Ihren Mitarbeitern nur insgesamt 20 Urlaubstage (H3) genehmigen können, müssen Sie hier eine Gültigkeitsprüfung für E6:E9 durchführen. Vergeben Sie außerdem für diesen Bereich den Namen „BewilligterUrlaub“, unbedingt zusammen geschrieben!!.

Es soll ein Stopp erscheinen, wenn mehr als 20 Urlaubstage verlangt werden. Erlaubt sind also nur kleiner gleich 20. Nehmen Sie dafür "=$H$3" oder tragen Sie per Hand den Wert 20 ein.

c) Bewilligte Urlaubstage von den Urlaubstagen gesamt (das ist $H$3 !!) abziehen für den Resturlaub.

d) Herr Hansen möchte in den Herbstferien gerne noch 3 Tage haben, Herr Becker beantragt noch 5 Tage, Frau Fischer probiert es mal mit einem Antrag auf 3 Tage und der neue Mitarbeiter kennt sich noch gar nicht aus und möchte am liebsten 12 Tage Urlaub haben.

e)Lassen Sie die Zellen frei und überlegen Sie sich eine Gültigkeitsprüfung, bei der nur Zahlen zugelassen werden, die maximal =dem Wert in D12 ff sind und mindestens den Wert 0 haben müssen. Auch hier soll ein Stopp erscheinen, wenn die Prüfung ungültig ausfällt.

Überprüfen Sie Ihre immer wieder Gültigkeiten, indem Sie in den Bewilligungsspalten fiktive Werte eingeben. Mal über, mal unter der magischen Gültigkeitsgrenze.

g) Bewilligte Urlaubstage werden von den jeweiligen Resttagen aus dem Bereich G6:G9 abgezogen.

Lösung zu Übung 12 b:

5. Die SUMMEWENN-FunktionNehmen wir mal an, Sie haben sich eine Würstchenbude als „ICH-AG“ zugelegt und wollen nun am Ende Ihres ersten erfolgreichen Monats als BesitzerIn von „Rock around the Wurst“ wissen, wie viel Geld Sie mit welchen Würstchen und sonstigem Fast Food verdient haben.

Übung 13Bitte erstellen Sie für dieses Beispiel folgende Tabelle:

1) Errechnen Sie den Betriebskostenanteil (BK-Anteil) für jeden Artikel. Die

Gesamtbetriebskosten für Ihre Bude betragen 843,88 €

2) Der Verkaufspreis für jeden Artikel ist die Summe von Einkaufpreis plus BK-Anteil zuzüglich

7% von dieser Summe. Oder einfach: Summe * 1,07.

3) Die Summe der Einnahmen für jeden Artikel ergibt sich aus dem Verkaufspreis (incl. MWSt.)

mal der verkauften Menge.

4) Bitte errechnen Sie das Gesamt aller Einnahmen.

Lösung 13:

Lösung 13 mit Formeln:

Übung 13a:In unserer Bude verkaufen wir schon jetzt 6 verschiedene Artikelgruppen, erkennbar an unseren Bezeichnungen „Fleisch“, „Brot“, „Extras“, „Süssigkeiten“, „Fast Food“ und „Getränke“. Mit der SUMMEWENN-Funktion können wir auf einfache Art schnell herausbekommen, welche Artikelgruppe wie viel Gewinn eingebracht hat. Bitte ergänzen Sie Ihre Tabelle um folgende Zeilen:

Unsere Formel in B25 lautet:=SUMMEWENN(Bereich1;Prüfung;Bereich2)

WENN im 1. Bereich, wo unsere Bezeichnungen drinstehendas Wort „Fleisch“ vorkommt, wie in A25, DANN bilde eine SUMME mit den dazugehörigen Werten aus dem 2. Bereich.

Die Formel lautet jetzt:=SUMMEWENN(B9:B22;A25;G9:G22)

Wenn Sie die Formel mit dem AutoAusfüllen-Kästchen nach unten kopieren möchten,dann müssen Sie noch absolute Bezüge für Bereich 1 und Bereich 2 mit den $-Zeichen festlegen. Sonst werden Ihnen diese relativen Bezüge beim Kopieren nach unten angepasst. Und das wollen wir doch nicht.

Die Formel lautet vollständig: =SUMMEWENN($B$9:$B$22;A25;$G$9:$G$22)Lösung 13a:

6. Erstellen von DiagrammenBleiben wir noch ein bisschen bei unserer Würstchenbude.

1) Um ein Diagramm zu erstellen, markieren Sie zuerst die relevanten Zellen.

2) Neben Eingabefeldern müssen Sie auch die dazugehörigen Spalten- und Zeilenüberschriften

markieren, damit das Diagramm auch die richtigen Beschriftungen erhält.

3) Mit einem Klick auf kommen Sie in das Fenster des Diagramm-Assistenten. Hier erhalten

Sie schrittweise die Anweisungen, um Ihr Diagramm zu erstellen.

Sie können hier Den Diagrammtyp auswählen,

den gewählten Tabellenbereich kontrollieren und

die weiteren Diagramm-Einstellungen (=Optionen) bestimmen.

Zuletzt legen Sie noch fest, ob das neue Diagramm auf dem gerade geöffneten

Tabellenblatt platziert wird, oder auf einem neuen Tabellenblatt.

Übung 13b :Erstellen Sie mit den Daten aus Übung 13a ein Säulendiagramm in Schritt 1.

Schalten Sie im Schritt 2 um auf Zeilen:

Tragen Sie den Diagrammtitel und den Titel für die Größenachse per Hand ein:

Wir möchten das neue Diagramm auf demselben Tabellenblatt haben:

Klicken Sie noch auf Fertig stellen. Voilá!

Halten Sie an einer freien Stelle im Diagramm, nahe des Rahmens. die linke MT gedrückt und es erscheint ein 4-fach-

Pfeil:

Jetzt können Sie das Diagramm an eine beliebige Stelle mit linker gedrückter MT auf unserem Blatt verschieben.

Z.B. neben unsere Überblickstabelle:

Lösung 13b:

Die einzelnen Diagramm-Komponenten sind:Diagramm-Fläche (alles)Diagramm-Typ (Nur die Grafik)Achsen- und Rubriken- und Titel(=Name)- BeschriftungenLegende (das Kästchen mit den Rubriken)Datenpunkt (z.B. eine Säule oder ein Kuchenstück)Datenreihe= alle vorher markierten Datenpunkte in der Ursprungstabelle

Übung 14:

1) Schreiben Sie zuerst diese Tabelle ab.

2) Ermitteln Sie mit der SUMMEWENN-Funktion die Gesamtsummen für jede Artikelgruppe.

3) Gestalten Sie die Tabelle übersichtlich, z.B. mit AutoFormat.

4) Erstellen Sie ein 3-D-Kuchendiagramm für die Artikelgruppen in einem neuen Tabellenblatt. Der Diagrammtitel lautet "1. Quartal, verkaufte Produkte". Übernehmen Sie alle sonstigen vorgegebenen Einstellungen.

5) Nennen Sie das Blatt für das Diagramm "1.Quartal 2005". Wählen Sie als Registerfarbe Olivgrün.

Lösung 14:

Lösung 14 mit Formeln:

Diagramm in Übung 14:

Übung 14a:1) Klicken Sie mit der rechten MT auf den freien Bereich des Diagramms und wählen Sie im Kontextmenü Datenquelle… aus:

2) Wählen Sie im anschließenden Dialog-Fenster bitte folgendes:

Klicken Sie auf das „Verkleinern“-Button beim Feld „Name“, klicken Sie auf das erste Tabellenblatt und wählen Sie Ihre Zellen für die 9 Artikelgruppen durch Markierung dort aus!

Gleichzeitig übernimmt Excel die von Ihnen markierten Zellen in das „Name“-Feld.

Klicken Sie wieder auf das Button ganz rechts

3) Dasselbe können Sie noch einmal im Feld „Rubrikenachsenbeschriftung“ tun.

Jetzt sehen wir in der Vorschau, dass auch die Legende rechts vom Diagramm die Artikelgruppen-Namen übernommen hat.

4) Klicken Sie auf OK.

Wenn die Legende jetzt zu klein sein sollte, klicken Sie auf die Legende einmal drauf und es erscheinen die Ziehpunkte, mit denen Sie bei gedrückter linker MT die Größe anpassen

können, mit oder oder oder .

Wenn uns unser Kuchendiagramm nicht gefällt, können wir uns auch einen anderen Diagrammtyp auswählen, wieder im Kontextmenü des Diagramms:

Wählen Sie im Kontextmenü der Diagrammfläche die Option Diagrammtyp…

Klicken Sie ins Register Benutzerdefinierte Typen und wählen Sie hier den Typ Kreisexplosion.

Hier wird es richtig anschaulich. Die einzelnen Kuchenstücke bekommen je eine eigene Beschriftung und sie lassen sich sogar an ihren Ziehpunkten weiter vom Mittelpunkt wegziehen.

Passen Sie bitte jetzt Ihr Kuchen-Diagramm jeweils im Kontextmenü der einzelnen Diagramm-Komponenten (Fläche, Kuchenstück, Beschriftung) entsprechend an:

Diagramm-Muster: grauBeschriftungen: dunkelblau „DVD Multibrenner“- Stück: türkis

7. Die ZÄHLENWENN-FunktionZählt die nichtleeren Zellen eines Bereichs, deren Inhalte mit den Suchkriterien übereinstimmen. Gegenteil ist z.B. SUMMEWENN, diese Funktion rechnet mit den INHALTEN von Zellen, die ein Suchkriterium erfüllen!=ZÄHLENWENN(Bereich;Suchkriterien)

Bereich ist der Zellbereich, von dem Sie wissen möchten, wie viele seiner Zellen einen Inhalt haben, der mit den Suchkriterien übereinstimmt.

Suchkriterien gibt die Kriterien in Form einer Zahl in „“, eines Ausdrucks (mehrere Wörter und Zahlen in „“)oder einer Zeichenfolge (nur Text mit „“) an. Diese Kriterien bestimmen, welche Zellen gezählt werden.

Übung 15Zum Beispiel kann ein Suchkriterium als 32, "32", ">32" oder "Äpfel" formuliert werden.

Der Bereich A3:A6 enthält die Begriffe "Äpfel", "Orangen", "Pfirsiche" und "Äpfel": ZÄHLENWENN(A3:A6;"Äpfel") ergibt 2 .

Der Bereich B3:B6 enthält 32, 54, 75 und 86: ZÄHLENWENN(B3:B6;">55") ergibt 2 .

Übung 16:

In der COEWAG GmbH steht mal wieder eine Bilanz an. Diesmal geht es aber nicht um satte Gewinne, sondern um allzu satte und zahlungsunwillige Mitglieder des dortigen Sportvereins.Wie viel muss wer berappen?

Lösung 16:

Lösung 16 mit Formeln:

8. Arbeiten mit mehreren Tabellenblättern

Übung: 1. Erstellen Sie über das Kontextmenü des Blattregisters aus Übung 16 eine Kopie dieses 1.

Tabellenblattes und löschen Sie eventuell vorhandene leere Tabellenblätter in Ihrer „Übung

16.xls“-Mappe.

2. Geben Sie der Kopie den Namen „Neue Mitglieder BS Coewag“ und stellen Sie das Blatt

hinter das 1. Blatt von Übung 16.

3. Löschen Sie in „Neue Mitglieder BS Coewag“ die Zellinhalte von F3 und G3 und schreiben in

F3 nur ein = hinein.

4. Klicken Sie jetzt in Ihre 1. Tabelle und markieren Sie dort die Zelle F3.

5. Klicken Sie wieder in „Neue Mitglieder BS Coewag“. Folgender Bezug wurde dort in F3 jetzt

zwischen beiden Blättern eingebaut: =’Übung 16’!F3

6. Diesen Verweis auf die Zelle eines anderen Blattes können Sie auch per Hand eingeben. Er

lautet:

=’(Name des Tabellenblattes)’!Zellbezug

7. Zwischen verschiedenen Mappen lautet er so:

=’[Name der Mappe.xls]Name des Tabellenblattes’!Zellbezugz.B. kann ich in eine freie Zelle meiner „DB für neue Mitarbeiter“ klicken und noch eine 2. Mappe öffnen. Dort markiere ich eine oder mehrere Zellen und klicke mich über die Taskleiste wieder zurück in meine „Neue Mitglieder BS Coewag“.

ACHTUNG: Der Bezug funktioniert nur in EINE Richtung!

9. Excel-Daten in anderen Office-Anwendungen mit OLEUnsere Hausbank macht ihren nächsten Kredit für „Schredder&Co.“ abhängig von einem aussagekräftigen Bericht über unsere Einnahmen im letzen Monat. Der Bericht soll als Power-Point-Datei schon vorab an Ihren Kundenbetreuer in der Kreditabteilung per mail zugesandt werden. Außerdem sollen Sie in WORD eine kurze Darstellung Ihrer Bilanz vom letzten Monat erstellen und zum nächsten Treffen mitbringen.

1. Öffnen Sie unsere „Übung 14.xls“ noch einmal.

2. Öffnen Sie Power Point über START – Programme

3. Klicken Sie über die Task-Leiste zurück auf Excel.

4. Markieren Sie zuerst den Tabellenbereich oberhalb der SUMMEWENN-Rechnungen.

5. Klicken Sie mit rechter MT auf den markierten Bereich und wählen Sie Kopieren.

6. Wechseln Sie zurück ins Power Point-Fenster und gehen Sie auf Menü Bearbeiten – Inhalte

einfügen… und wählen Sie im anschließenden Fenster „Microsoft Excel Arbeitsblatt Objekt“

sowie den Button Verknüpfen

7. Ergebnis: Alles, was Sie

anschließend an

Werten in Ihrer Excel-

Quelldatei ändern, wird automatisch in die Power-Point-Datei übernommen. Aber Achtung:

Auch das ist nur eine Einbahnstraße!!

8. Fügen Sie danach in unserer Power-Point-Datei eine neue Folie ein (Menü Einfügen – neue

Folie) und kopieren Sie jetzt in Excel auch den Bereich, indem die SUMMEWENN-

Berechnungen für alle Artikelgruppen drinstehen. Verknüpfen Sie wieder diesen Bereich in der

neuen PP-Folie.

9. Wiederholen Sie diese Prozedur auf einer neuen Folie für das Tortendiagramm.

10. Speichern Sie die pp-Datei als „Präsentation Schredder.ppt“ und wiederholen Sie die 3

Kopier- und Verknüpfungsvorgänge, aber diesmal mit Word (Schredder.doc).

10. Zellen schützenBeschreibung: Sie haben eine Rechnungstabelle erstellt. Dort sollen verschiedene Zellen nicht bearbeitet werden können. Ein Versuch, diese Zellen zu verändern, soll zu einer Meldung führen, dass diese Zellen nicht verändert werden dürfen.

 Übung 18 c:

In dieser Rechnung werden die Kunden-Adresse sowie die Bestellmengen von Hand eingegeben. Alles andere soll durch Formeln errechnet werden.  Der Blattschutz schützt unsere Formeln und lässt manuelle Eingaben zu!  Der Excel-Zellenschutz Jede Zelle besitzt einen sog. Sperrschutz. Dieser ist ein Bestandteil des normalen Zellenformates und auch im Einstellmenü für das Zellenformat zu finden. Er wird jedoch erst wirksam, wenn Sie das gesamte Tabellenblatt über das Menü Extras, Option Schutz-Blatt schützen sperren.

 So gehen Sie vor: Markieren Sie C3:C6, den bearbeitbaren Bereich für die Zukunft,

Halten Sie die STRG-Taste gedrückt und markieren Sie zusätzlich noch den Bereich B9:B14. Wählen Sie im Kontextmenü die Option Zellen formatieren aus.

Wählen Sie im folgenden Fenster die Registerkarte Schutz aus.

·        Entfernen Sie das Häkchen vor „Gesperrt“

(damit nehmen Sie den Zellen den Sperrschutz) ·        Bestätigen Sie Ihre Wahl mit OK

·        Wählen Sie im Menü Extras die Option Schutz-

Blatt schützen aus

Im folgenden Fenster aktivieren Sie NUR das 2.

Kästchen

„Nicht gesperrte Zellen (zur Bearbeitung) auswählen“ ·        Testen Sie den Erfolg, indem Sie

versuchen, die Zellen A9 bis A14 zu

verändern.

Geht nicht? Versuchen Sie einmal, eine Kundenadresse oben in C3 bis C6 rein zu schreiben.

Das geht. Und genau das wollten Sie erreichen.

11. Eigene Mustervorlagen in Excel1. Bitte erstellen Sie folgende Etat-Übersicht, die zur Vorlage werden soll und speichern Sie diese Tabelle ab als „1. Quartal 2000“.

In B12 schreiben Sie die Formel für die Summe B6:B11.In B13 kommt die Formel für das Restkapital aus B12 minus B4 hinein.

2. Markieren Sie den Bereich B4 bis B11 und löschen Sie die Zellinhalte mit der Entf.-Taste, ebenso die Überschrift „1. Halbjahr 2000“3. Rufen Sie im Menü Datei den Befehl Speichern unter… auf und wählen Sie im Dateityp Mustervorlage aus.

4. Geben Sie der Datei auch einen anderen Namen. Die Vorlage soll „Halbjahresabrechnung“ in Zukunft heißen. Das Speichern im Vorlagenordner ist nur zu empfehlen, wenn Sie nicht in einem Schulungs-Netzwerk arbeiten, z.B. zu Hause an Ihrem eigenen Rechner.

5. Speichern Sie bitte die Vorlage im Unterricht lieber auf Ihrem eigenen Laufwerk A:/!Schließen Sie die Datei. 6. Öffnen Sie anschließend in Excel mit Menü Datei und der Option Neu… eine neue Arbeitsmappe. Schauen Sie dazu in den Aufgabenbereich

Fügen Sie in die neue Mappe für das 2. Halbjahr 2000 folgende Werte ein und speichern Sie sie ebenfalls ab, diesmal wieder als xls-Mappe, in Ihrem Namensordner.

Das sog. Smart-Tag für hilfreiche Vorschläge, lässt sich abschalten über Extras => AutoKorrektur-

Optionen => Smarttags =>beide Häkchen entfernen