Excel Grundlagen
Werbung
Grundlagen MS Excel EXCEL Skript Gliederung Excel 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 ..................................................................... 6 Zeilen 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................................................................................................ 19 5. 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: Der EXCEL - Bildschirm Verkleinern zur Statuszeile, Vollbild- und Schließfeld Titelleiste Menüzeile Symbolleiste Standard Symbolleiste Format Vertikale Bildlaufleiste Aktive Zelle Bearbeitungszeile Tabellenblatt Office-Assistent Blattregister Statusleiste Horizontale Bildlaufleiste 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! 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 Kopfzeile. in der Benutzerdefinierten Gleich ausprobieren: Einfügen ,Löschen, Umbenennen und Verschieben/ Kopieren. Aufbau einer Formel Jede 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. Übung 1 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 Zubehör automatisch eingetragen. wird die Summe Es erscheint die Formel: =SUMME(D9:D1 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 Listen Sie 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 FormatSymbolleiste). 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 Rechte Maustaste Option Kopieren. ODER 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 Tabelle Die 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. Spalte zu schmal 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. 5. Eigene Zahlenformate Wenn 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) 1,5 1234,5 Wert mit Format 1,500 1234,50 0 7777,77 7777,77 Eingabewert Eingabetext Formatierter Text 0,00 7.777,77 € Tabelle Funktion Tabelle Funktion Diagramm € 7.777,77 Diagramm - € 0 0 0,00 € absoluter Bezug Tabellenblattregist Tabellener blattregister gelb 1111 $1.111,00 1111 1.111,00 F schräge Sache 2600 2.600 Stück Nichts 10 kleine Jägermeister 0,16 1,5 16% 1 1/2 Formel Formel 10 absoluter Bezug Text mit Ausrichtung 45° 6.Formeln kopieren mit AutoAusfüllen Fü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. Übung 3 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. Lösung zu Übung 3: Lösung 3 mit Formeln: Eingabe von Formeln Arithmetische 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. Übung 4 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. Lösung zu Übung 4: Lösung mit Formeln: 8. Statistische Funktionen MIN, MAX und MITTELWERT Es 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. Übung 5 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: Die bedingte Formatierung Zum 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: Artikel 1. Quartal 2. Quartal Hemden 13.400,--€ 36.340,--€ 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! Übung 5a 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 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. Name Vereinbarte Arbeitszeit pro Monat Bertram Diesel Ebermann Ganser Maier Mutz 36 32 40 34 40 38 Erstellen Sie eine Gehaltsaufstellung für jede Mitarbeiterin nach folgendem Muster: A B C D E Name Stundenlohn Anzahl Stunden Prämienpunkte 40 32 40 40 40 40 1 2 3 4 5 6 7 8 9 Bertram Diesel Ebermann Ganser Maier Mutz 17 19 15 16 20,5 15 F Anzahl Über Grundstunden lohn G H I J Prämie Lohn für Über stunden Vorschuss Auszahlung 500 1 3 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. Lösung zu Übung 6: Lösung mit Formeln: Übung 8 Übung 7 Artikel Fichte, 60 cm Blumenerde, 50 l Rose, "Red Ball" 20er Beutel Tulpen Tonschale, 45 cm Schaufel, groß 5er Beutel Narzissen Menge 8 4 2 5 1 1 3 Nettopreis Artikel pro Stück gruppe 6,20 2,99 5,8 3,25 15 21,90 Umsatz- Bruttosteuer preis pro Stück Pflanzen Erde Pflanzen Zwiebeln Töpfe Geräte 0,07 0,19 0,07 0,07 0,19 0,19 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. Gesamt pro Artikel Ü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 A1 Beginnen Sie in Zelle A3 mit der Übertragung der Tabelle. 280,00 € Gesamtkosten der Abfallbeseitigung: 5-FamilienHaus Vogelsanger Str. 295 Schmitz, Peter+Irene Berg, Wilhelm Busch, Paul+Rosi Tater, Bernd+Agnes von Fels, Dagmar Wohnungsgröße in Miete qm pro qm Garagenstellplätze Kosten Abfall Abfall (in pro Liter) Familie 74 82 10 11,50 2 1 120 70 74 9 1 100 66 9 3 100 54 8,25 0 50 Kosten GesamtHeizung in € miete (kalt) sumlage in € in € Summe e) Aufgaben a) b) c) d) Ermitteln Sie den Anteil (Literzahl) an den Gesamtkosten der Abfallbeseitigung. Ermitteln Sie die Gesamtmiete (kalt). Berechnen Sie die Heizungsumlage pro Wohnung. Diese betragen 0,32 € pro qm. 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üge Wenn 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. Oder setzen 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 beheben Normalerweise 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 Funktionsoder 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- Funktion Jede 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: Situation Ein 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 Versandkostenpauschale. 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. AUFGABEN Erstellen Sie die untenstehende Tabelle: Zelle 1. A1 2. A5 3. A6:A9 4. B5:B9 5. B10 6. B11 7. B12 8. B12 9. A12, A11 10. A10 11. 12. Aufgabe Tragen Sie Ihren Namen ein. Artikel 1 eingeben 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. Geben Sie die Zahlenwerte ein. Ermitteln Sie den Warenwert 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. Berechnen Sie den Gesamtbetrag. Der Gesamtbetrag soll nur bei einem Warenwert unter 50,00 € erscheinen, sonst bleibt das Feld leer. Die Texte in A12 und A11 sollen nur erscheinen, wenn der Kunde eine Versandkostenpauschale zahlen soll, sonst bleibt das Feld leer. Bei einem Warenwert unter 50,00 € soll das Wort Warenwert ausgegeben werden, sonst das Wort Gesamtbetrag. Speichern Sie Ihre Tabelle unter Versandhaus.xls ab Alle Werte sollen das Format € erhalten. Nehmen Sie die notwendigen Einstellungen vor! Weiterführende Aufgaben 1 13. 14. 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 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 2 15. 16. Kopieren Sie Ihre bisherige Lösung in ein neues Blatt/Tabelle – siehe 13. 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 3 4 5 6 7 8 9 10 11 12 B C A Artikel 1 Artikel 2 Artikel 3 Artikel 4 Artikel 5 Warenwert Versandkostenpauschale Gesamtbetrag B ABRECHNUNG ABRECHNUNG 3,12 € 7,16 € 3,25 € 14,80 € 16,75 € 45,08 € 3,50 € 48,58 € Oder 2. Möglichkeit Artikel 1 Artikel 2 Artikel 3 Artikel 4 Artikel 5 Gesamtbetrag 14,00 € 19,90 € 4,98 € 14,80 € 4,78 € 58,46 € 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 SeitenansichtsFenster 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üfung Sie 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-Funktion Nehmen 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 13 Bitte 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 drinstehen das 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 Diagrammen Bleiben 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-fachPfeil: 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)- Beschriftungen Legende (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 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! des 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: grau Beschriftungen: dunkelblau „DVD Multibrenner“- Stück: türkis 7. Die ZÄHLENWENN-Funktion Zä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 15 Zum 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’!Zellbezug z.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 OLE Unsere 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-PointDatei 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 SUMMEWENNBerechnungen 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ützen Beschreibung: 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 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. das 11. Eigene Mustervorlagen in Excel 1. Bitte erstellen Sie folgende Etat-Übersicht, die zur werden soll und speichern Sie diese Tabelle ab als „1. 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 hinein. 2. Markieren Sie den Bereich B4 bis B11 und löschen Sie Zellinhalte mit der Entf.-Taste, ebenso die Überschrift Halbjahr 2000“ 3. Rufen Sie im Menü Datei den Befehl Speichern unter… auf und wählen Sie im Mustervorlage aus. 4. Geben Sie der Datei auch einen anderen Die Vorlage soll „Halbjahresabrechnung“ in heißen. Das Speichern im Vorlagenordner empfehlen, wenn Sie nicht in einem Schulungs-Netzwerk arbeiten, z.B. zu Hause an Ihrem eigenen Rechner. Vorlage Quartal B4 die „1. Dateityp Namen. Zukunft ist nur zu 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 xls-Mappe, in Ihrem Namensordner. als Das sog. Smart-Tag für hilfreiche Vorschläge, lässt sich abschalten über Extras => AutoKorrekturOptionen => Smarttags =>beide Häkchen entfernen
