EXCEL-Grundlagen - Informatik

Berufsbildende Schule Wirtschaft Trier
Wirtschaftsgymnasium
Excel-Grundlagen
Unterrichtsmaterialien
von Christoph Oberweis
Stand: 26.01.2016
1
Inhalt
Starten und Beenden von Excel
Die Excel-Oberfläche
Berechnungen durchführen
Ansichten und Druck
Zellen automatisch ausfüllen
Mathematische Funktionen
Relative und absolute Adressierung
Die logische Funktion WENN
S-Verweis und W-Verweis
Diagramme erstellen
2
Vorbemerkungen
Bei Excel handelt es sich um Tabellenkalkulationsprogramm (Kalkulieren =
„berechnen“) mit vielen Zusatzmöglichkeiten. Grundlagen in Windows (Dateiverzeichnisse, Dateien, Windows-Explorer usw.) werden zum erfolgreichen
Durcharbeiten dieses Tutorials vorausgesetzt.
Starten und Beenden von Excel:
Start:
Entweder: Doppelklick auf das Excel-Symbol auf dem Desktop (falls vorhanden)
oder:
Klick auf die Start-Schaltfläche in der Task-Leiste, es öffnet sich ein Fenster
und links könnte Excel als Programm angezeigt sein.
oder:
Klick auf die Start-Schaltfläche, Eintrag Alle Programme wählen, dort nach
Microsoft Office 2013 suchen, Ordner anklicken und Excel auswählen.
Excel öffnet sich; was dann angezeigt wird, ist von vorherigen Arbeiten mit dem
Programm abhängig. Nun kann eine leere Arbeitsmappe gestartet werden.
3
In diese Arbeitsmappe trägt den Namen Mappe1 und besteht zunächst aus nur
einer Tabelle:
Beenden:
Hierzu gibt es verschiedene Möglichkeiten:
1. Die Registerkarte Datei anklicken, dann geht es zur sogenanntem BackstageAnsicht und dort den Befehl Schließen wählen
2. Klick auf den Fensterschalter Schließen (das x rechts oben).
3. Doppelklick auf das Excel-Symbol in der Titelzeile.
4. Tastenkombination (Alt) + (F4).
Sollte sich eine Tabelle in der Bearbeitung befinden, die ist noch nicht
gespeichert wurde, reagiert das Programm mit einer entsprechenden Abfrage
(s. später).
Arbeitsauftrag 1:
Starten Sie Excel und tippen Sie in die Zelle A1 eine beliebige Zahl ein.
Speichern Sie das Dokument in einem von Ihnen gewählten Verzeichnis ab und
beenden Sie bitte Excel. Testen Sie bitte alle hier genannten Möglichkeiten.
Machen Sie sich bitte klar, worin der Unterschied zwischen Mappe1 und
Tabelle1 besteht. Demonstrieren Sie das bitte an einem kleinen Beispiel, indem
Sie eine zweite Tabelle anlegen und auch dort eine Zelle füllen.
4
Die Excel-2013-Oberfläche
Titelleiste Menüband mit den Registerkarten (…START, EINFÜGEN…), je nachdem,
welche Registerkarte aktiviert wurde, erscheinen
die dazugehörenden Befehlsgruppen
Bearbeitungsleiste
Zelle
Tabellenblatt/
Arbeitsbereich/
Arbeitsfeld
Blattregister
Statusleiste
Titelleiste: dort befindet sich links die Symbolleiste für den Schnellzugriff, die
Titelleiste wird auch als Bestandteil des Menübands angesehen, vgl.:
„Menüband-Anzeigeoptionen“ rechts oben neben dem Hilfe-Fragezeichen.
Befehlsgruppen: z. B. Schriftart, Ausrichtung, hier können einzelne Befehle
durch Anklicken ausgeführt werden
Statusleiste: Zunächst dürfte der Zoom-Regler (rechts) hier die am häufigsten
benutze Möglichkeit sein.
Der Arbeitsbereich besteht aus Zellen. Sie sind die Schnittpunkte der einzelnen
Zeilen und Spalten und ihr Name ergibt sich den Zeilen- und Spaltennamen
(hier: F2).
Durch das Anklicken mit der Maus kann von einer Zelle in eine andere gewechselt werden. Bewegungen innerhalb eines Arbeitsblattes können auch mit
den Cursortasten durchgeführt werden. In eine Zelle können sowohl Zahlen wie
auch Text eingegeben werden. Ist eine Zelle markiert, kann hinter das Gleichheitszeichen in der Bearbeitungsleiste geklickt werden und dort ebenfalls Zahlen
oder Text eingegeben werden. Synchron zur Eingabe in der Bearbeitungsleiste
5
erscheint die Eingabe in der markierten Zelle. Die Eingaben können optisch
hervorgehoben werden (Fettschrift, unterstreichen, kursiv, andere Schriftart):
Eingabe markieren und in der Befehlsgruppe den gewünschten Effekt anklicken.
Einiges zur Tastatur:
Eingabetaste (Entertaste, Returntaste): Beim Drücken im
Arbeitsbereich gelangt man in die nächste Zeile der gleichen Spalte.
Achtung: es gibt hierzu Ausnahmen, s. später!
Pfeiltasten zur Navigation, eine Alternative zu der
Möglichkeit, mit der Maus eine Zelle anzusteuern.
Das funktioniert aber nur, wenn man sich nicht im
Bearbeitungsmodus befindet.
Damit ist Folgendes gemeint:
In dieser Tabelle soll die Zelle B1 nachbearbeitet
werden. Zwischen den Ziffern 4 und 6 muss
noch die Ziffer eingefügt 5 werden. Mit der
Pfeiltaste wurde sie angesteuert. Das Drücken
einer Ziffertaste würde bewirken, dass der
Inhalt komplett verschwindet. Da ja etwas
Nachgebessert werden soll, muss die Zelle
Zur Bearbeitung geöffnet werden. Hierzu existieren mehrere Möglichkeiten:
1. Doppelklick mit der Maus an der Stelle in der Zelle, wo etwas eingefügt
werden soll.
2. Einfachklick an die entsprechende Stelle in der Bearbeitungsleiste
3. Drücken der Funktionstaste
Nun funktionieren die Pfeiltasten innerhalb des Eingabefeldes. Die fehlende
Ziffer kann eingetragen werden. Mit der Rücktaste oder der Entfernen-Taste
können Inhalte gelöscht werden.
Der Bearbeitungsmodus wird durch Drücken der Eingabetaste verlassen. Ein
Klick auf das Häkchen links neben dem Bearbeitungsfeld bewirkt das Gleiche.
6
Das Drücken der
- Taste bewirkt, dass die Änderungen rückgängig
gemacht werden.
Ebenso wird dies durch einen Klick auf das Kreuz
links neben dem Bearbeitungsfeld erreicht.
Tabulatortaste: hiermit kann man sich nach rechts und links
in der Tabelle bewegen.
Arbeitsauftrag 2: Sie sollen nun bitte einige generelle Fragen beantworten
sowie grundsätzliche Arbeitsschritte in Excel durchführen. Manche Aufgaben
können Sie „intuitiv“ lösen, bei anderen unterstützt Sie die Online-Hilfe und
manche Dinge müssen Sie einfach ausprobieren.
1. Erklären Sie bitte die Begriffe Kontextmenü und Quick-Info! (Tipp:
Kontextmenü: rechte Maustaste, wenn sich der Mauszeiger auf einem Objekt
befindet, Quick-Info: Mauszeiger länger über einer Schaltfläche o. ä. stehen
lassen)
2. Was ist ein Dialogfeld? Geben Sie bitte ein etwas ausführlicheres Beispiel.
3. Was sind Registerkarten?
4. Erstellen Sie bitte folgende Mini-Testtabelle:
Speichern Sie bitte diese Mappe
unter dem Namen „Test-Tab“ ab.
Welche Möglichkeiten gibt es?
7
5. Kopieren Sie bitte die Zellen A3, A4 und A5 nach B3, B4, B5 und nach C3,
C4 und C5:
Welche Möglichkeiten haben Sie
herausgefunden, wie Zellen-Inhalte
kopiert werden können, ohne dass
sie jeweils neu geschrieben werden
müssen?
6. Welche Möglichkeiten gibt es, den Inhalt einer Zelle zu löschen? (Testen Sie
bitte auch das Kontextmenü, vergessen Sie bitte nicht, zu markieren!)
7. Tipp: Mit dem Button „Rückgängig“ (Symbolleiste für den Schnellzugriff)
bringen Sie die Tabelle immer wieder auf den „alten“ Stand, damit Sie für
die weiteren Übungen noch Daten zur Verfügung haben und nicht alles neu
eintippen müssen. Wie können die Inhalte einer ganzen Spalte/Zeile gelöscht
werden?
8. Erklären Sie bitte ausführlich, worin der Unterschied zwischen „Inhalt einer
Zelle löschen“ und „Zelle löschen“ besteht und führen Sie die entsprechenden Tests durch!
9. Fügen Sie bitte neue Zellen ein!
10.Verändern Sie die Tabelle bitte folgendermaßen:
Hier wurde das Format der Zellen geändert. Wie funktioniert das? Welchen
Möglichkeiten existieren für Zahlen?
11.Worin besteht der Unterschied zwischen der Eingabe eines Textes und der
Eingabe einer Zahl (mit der dann auch Berechnungen durchgeführt werden
können); wie ist der Unterschied visuell erkennbar? Handelt es sich bei den
Daten in den Zellen A3, A4 und A5 (s. oben) um Zahlenfelder oder
8
Textfelder? (Hinweis: Das Thema „Berechnungen“ wird im nächsten
Kapitel genauer behandelt.)
12. Korrigieren Sie bitte einen Zelleninhalt a) in der Zelle selbst, b) in der
Bearbeitungsleiste!
13.Im Register „Datei“ gelangen Sie über „Optionen“ „Speichern“ zu dem unten
stehenden Fenster. Erläutern und testen Sie die Einstellungen, die hier
möglich sind.
Tipp: Format übertragen
Wurde für eine Zelle ein bestimmtes Format gewählt (z. B. Schrifttyp,
Währungszeichen usw.), kann dies auf einfache Weise auf andere Zellen
übertragen werden:
1. Schritt: In die Zelle klicken, die das gewünschte Format schon hat
2. Schritt: Doppelklick auf den Pinsel
3. Schritt die gewünschten Zellen anklicken
4. Mit <ESC> die Funktion wieder ausschalten
Hinweis: Einfachklick auf den Pinsel bewirkt, dass die Übertragungsfunktion
nur einmal durchgeführt werden kann.
9
Berechnungen durchführen
Ausgangspunkt: Aufgabe 4 und Aufgabe 5 des Arbeitsauftrags 2 (Mini-Testtabelle).
1. Alle Zahlen der A-Spalte sollen addiert werden, das Ergebnis steht in der
Zelle A6.
1.1 In Zelle A6 ein Gleichheitszeichen eingeben (dadurch wird Excel mitgeteilt,
dass nun eine Berechnung durchzuführen ist)
1.2 Mit der Maustaste die erste Zelle anklicken, die addiert werden soll (A3)
1.3 Eingabe des „+“ – Zeichens
1.4 Mit der Maustaste die zweite Zelle anklicken, die addiert werden soll (A4)
1.5 Eingabe des „+“ – Zeichens
1.6 Mit der Maustaste die dritte Zelle anklicken, die addiert werden soll (A5)
1.7 Mit der Enter-Taste bestätigen
1.8 Das Ergebnis der Addition wird angezeigt.
Beachte: geht man auf Zelle A6 zurück, steht in der Bearbeitungsleiste die
Formel.
2. Wiederum sollen die gleichen Zahlen addiert werden, es gibt eine 2.
Möglichkeit:
2.1 In der Bearbeitungsleiste ein Gleichheitszeichen eingeben
2.2 Eintragen der Formel (A1+A2+A3)
2.3 Mit der Enter-Taste bestätigen
2.4 Das Ergebnis der Addition wird angezeigt.
10
3. Die Schaltfläche AutoSumme als dritte Möglichkeit für Spalten/Zeilen, die
untereinander/nebeneinander stehen
3.1 Klick auf das nächste freie Feld unter der Spalte/neben der Zeile
3.2 Klick auf das Summenzeichen und Bestätigung mit Enter
Hinweis: Das Summerzeichen finden Sie sowohl auf der Start- als auch auf der
Formel-Registerkarte.
Excel umrahmt die Zellen, die addiert werden. Hier „denkt“ Excel mit: es
werden die Zellen vorgeschlagen, die zur Summenbildung herangezogen werden
könnten. Eine Korrektur in der Bearbeitungsleiste ist natürlich möglich.
Für alle drei Möglichkeiten gilt: Wenn Sie nun eine Zahl in einer Zelle ändern,
aus denen die Summe berechnet werden soll, ändert sich automatisch auch das
Ergebnis.
Arbeitsauftrag 3: Berechnen Sie jetzt alle möglichen Zeilen- und Spaltensummen dieses Beispiels nach den hier vorgestellten Verfahren!
11
Rechnen: Eine vierte Möglichkeit mit f(x)
Hier müssen Sie die Funktionsargumente bestätigen oder korrigieren.
Arbeitsauftrag 4: f(x): Diese Möglichkeit bietet Ihnen viele unterschiedliche
Berechnungen. Testen Sie bitte einige davon.
Tipp: Bearbeitungsleiste verschwunden?
Das Register DATEI im Menüband anklicken, die Schaltfläche OPTIONEN
anwählen, das Register ERWEITERT aktivieren und im Bereich ANZEIGE bei
BEARBEITUNGSLEISTE ANZEIGEN ein Häkchen setzen.
12
Beispiel: Berechnung der Mehrwertsteuer
1. Schritt. Führungstexte in Spalte A eingeben
2. In Zelle B1 einen Nettobetrag eingeben
3. In Zelle B2 die Formel zur Berechnung eingeben (mit dem „=“ beginnen!)
4. Zelle B3 als Summenfeld bezüglich B1 und B2 festlegen
5. Zahlen formatieren (Währung), Nettobetrag ändern und beobachten, wie sich
der Bruttobetrag ändert
13
Tipp: Summen von Zellen berechnen, die nicht unter/nebeneinander liegen
1. Schritt: Drei (oder mehr) beliebe Felder auswählen und dort Zahlen eintragen
2. Schritt: Das gewünschte Ergebnisfeld anklicken und die Formel eingeben,
wobei zu beachten ist, dass das Trennungszeichen hinter den Zellennamen
dann ein „;“ ist.
Hinweis: Es gibt hier noch andere Möglichkeiten. Zellen die nicht nebeneinander liegen können einzeln markiert werden: <Strg> - Taste drücken und Feld
mit der Maus markieren, experimentieren Sie bitte damit!
Ergebnis:
14
Rechenzeichen in EXCEL
Addition:
Subtraktion:
Multiplikation:
Division:
+
*
/
Tipp: Spaltenbreite ändern
Mauszeiger zwischen zwei
Spaltennamen bringen: Er
verändert sein Aussehen. Jetzt mit
gedrückter Maustaste die
Spaltenbreite ändern.
Tipp: Formeln anzeigen
1
2
15
Drucken
Vor dem Ausdruck kann die Tabelle auf ihr Aussehen hin überprüft werden.
Spezielle Einstellungen verändern das Layout:
Arbeitsauftrag 5: Testen und beschreiben Sie, welche Möglichkeiten der
Druckausgabe hier durführbar sind. Wie kann das Druckbild verändert
werden?
16
Arbeitsauftrag 6: Erstellen Sie bitte folgenden Haushaltsplan. Fügen Sie bitte
(übungshalber) unterschiedliche Währungsangaben ein.
Wert wird
berechnet!
Beispiel: Rechnungspositionszeilen und Rechnungsfuß
Hier: Linien
und
Doppellinien
einfügen
?
Arbeitsauftrag 7: Erstellen Sie bitte diese Tabelle. Beachten Sie, dass die
Zahlen von Spalte „Gesamtpreis“ alle errechnet werden. Entwerfen Sie bitte
einen geeigneten Kopf für das Rechnungsformular.
17
Tipp: Drag & Drop
1. Schritt: Zelle anklicken und Positionierung des Mauszeigers auf eine Linie
des grünen Eingabekastens (Mauszeiger ändert sich zu einem Pfeil).
2. Schritt: Mit gedrückter Maustaste den Inhalt in gewünschte neue Zelle
ziehen.
Tipp: Spalten und Zeilen schnell entfernen oder einfügen
1.
2.
3.
4.
Zeilen- oder Spaltenkopf anklicken
<Strg> Taste drücken und gedrückt halten
Mit <+> eine Zeile/Spalte einfügen, mit <-> eine Zeile/Spalte entfernen.
So können auch einzelne Zellen eingefügt werden (bitte testen).
Zellen automatisch ausfüllen
Monatsangaben und Tagesangaben (und selbst definierte Listen) können
automatisch ausgefüllt werden: In eine Zelle wird ein Monatsname eingetragen
und der Mauszeiger dorthin positioniert, so dass ein „+“ – Zeichen erscheint.
Mit gedrückter Maustaste wird nach unten (oder nach rechts) gezogen, dabei
werden die folgenden Zellen automatisch in aufzählender Form ausgefüllt.
Hinweis: Die Ausgabe in die Zellen erfolgt erst nach dem Loslassen der
Maustaste. Damit Zahlen für laufende Nummerierungen erscheinen, muss
zusätzlich die <Strg> - Taste gedrückt werden, wenn der Mauszeiger so in der
Zelle steht, dass ein „+“ – Zeichen erscheint.
18
Arbeitsauftrag 8: Testen Sie die Möglichkeiten des automatischen Ausfüllens.
Legen Sie selbst eine eigene Aufzählung fest!
http://www.tippscout.de/excel-datenreihe-ohne-wochenenden_tipp_4250.html
Tipp: Nummerische Aufzählungen mit einem Abstand, der ungleich 1 ist
Die Vorgehensweise ist wie oben, nur dass jetzt zwei Zahlen mit dem
gewünschten Abstand neben- oder untereinander geschrieben werden und beide
Kästchen markiert werden.
Arbeitsauftrag 9: Erstellen Sie bitte die untenstehende Tabelle!
Berücksichtigen Sie bitte auch die Zeilensummen, die hier nicht mehr sichtbar
sind. Nutzen Sie alle Hilfsmittel, die Sie bisher kennen gelernt haben (Kopieren,
automatisches Ausfüllen usw.). Analysieren Sie bitte in einem ersten Schritt die
Tabelle und bestimmen Sie, welche „automatischen“ Möglichkeiten Ihnen Excel
bietet, die Tabelle auf eine komfortable Art und Weise zu erstellen.
Arbeitsauftrag 10: Verändern Sie das Layout dieser Tabelle nach Ihrem
Vorstellungen bzw. erstellen Sie bitte eine neue Tabelle aus Ihrem täglichen
Arbeitsfeld!
Ändern Sie bitte die Währungsangabe in US-Dollar (oder eine andere
Währungsangabe) um.
19
Tipp: Formeln kopieren
Eine sehr hilfreiche Möglichkeit bietet Excel hinsichtlich Formeln an: Eine
Zelle, die als Ergebnisfeld einer Berechnung definiert wurde, kann mit dem
oben beschriebenen Verfahren (Mauszeiger so auf die zu kopierende Zelle
platzieren, dass ein „+“ – Zeichen erscheint, dann an gewünschte Stelle(n)
ziehen) kopiert werden. Automatisch werden die neuen Spaltennamen (oder
Zeilennamen) für die neue Berechnung eingetragen. Manchmal ist das
Angleichen aller Spalten- oder Zeilennamen unerwünscht, z. B. bei der nächsten
Aufgabe, bei der immer durch den Wert derselben Zelle dividiert werden muss
(Gesamtbetrag). Um dies zu erreichen, setzt man ein $ - Zeichen vor die
Zeilenzahl: B4/B$11. Dann wird die Zeilenzahl von B11 nicht automatisch
hochgezählt. (Dies wäre eine Alternative zu der Möglichkeit, der Summenzeile
einen Namen zu geben, siehe unten.)
Arbeitsauftrag 11: Informieren Sie sich mit der Online-Hilfe zu dem Thema
„Datums- und Zeitfunktionen“!
Tipp: F1/Suchbegriff HEUTE()
Testen Sie bitte die Funktionen.
Tipp: Spaltenbreite mehrerer Spalten ändern
Sollte die Spaltenbreite mehrerer Spalten auf die gleiche Breite verändert
werden, müssen die gewünschten Spalten (im Kopf markiert werden. Mit dem
Mauszeiger in den markierten Bereich gehen und die rechte Maustaste drücken.
Dort finden Sie einen Eintrag „Spaltenbreite ...“. Hier können Sie den
gewünschten Wert eintragen.
20
Prozenttabelle anlegen
1. Erstellen Sie bitte folgende Tabelle:
(Beachten Sie bitte die Rahmenlinie unten (roter Pfeil)
2. Geben Sie der Summenzelle einen Namen (das ist zwar nicht für die
folgende Bearbeitung notwendig, vereinfacht aber das Arbeiten mit großen
Tabellen). Hierzu wird die gewünschte Zelle angeklickt (hier: B11) und
oben im Namensfeld der (neue) Name eingetragen (mit Enter bestätigen):
21
3. Auf die erste Zelle klicken, für die der Euro-Betrag als Prozentzahl
erscheinen soll; dann folgende Eintrag hier vornehmen..
4.
Auf das %-Zeichen klicken
22
5. Kopieren Sie nun mit dem bekannten Verfahren die Formeln für die übrigen
Zellen.
Die einzelnen Prozentwerte ergeben sich nach folgender Formel:
100 * Euro-Betrag
X% =
Summe
In der Formatleiste befindet sich eine Schaltfläche % (s. Nr. 4, vorherige
Seite. Der Wert der Zelle wird beim Anklicken auf diese Schaltfläche
automatisch mit 100 multipliziert und das %-Zeichen erscheint hinter der
Zahl.
6. Zur Kontrolle: Die %-Summenzeile:
23
Tipp: Zellen mit Nullen ausblenden Klicken Sie auf die Registerkarte Datei,
anschließend auf Optionen und dann auf die Kategorie Erweitert, dann:
Allerdings: Hier ist es die Frage ob das überhaupt so gewünscht wird. Vielleicht
sollten die Nullwerte für Freitag doch erscheinen, damit klar ist, dass hier nicht
vergessen wurde, einen Wert einzutragen, sondern dass tatsächlich kein Umsatz
für diesen Tag einzutragen ist.
24
Mathematische Funktionen
Excel stellt viele mathematische Funktionen zur Verfügung, d. h. ist nicht
notwendig, in eine Ergebniszelle eine Formel einzutragen. Es muss lediglich
(mit Hilfe des Funktionsassistenten) die gewünschte Funktion ausgewählt
werden.
Beispiel: Berechnung des Mittelwerts eine Spalte mit numerischen Daten:
1. Zahlen eingeben
2. Auf Ergebniszelle
gehen
3. Die gewünschte
Funktion wählen
4. Mit OK bestätigen
25
5. Zur Kontrolle werden
nun die Argumente
angezeigt; das
Ergebnis steht
bereits links unten
6. Mit OK bestätigen
7. Nun erscheint wieder
die Tabelle mit dem
gewünschten Funktionsergebnis
8. In der Bearbeitungszeile wird die Funktion angezeigt!
Arbeitsauftrag 12: Testen Sie bitte weitere Funktionen mit Hilfe des FunktionsAssistenten. In den Kategorien „Statistik“ bzw. „Math. & Trigonom.“ finden Sie
Berechnungen, die Sie aus dem Mathematikunterricht kennen. Die Kategorie
„Finanzmathematik“ beinhaltet weitere interessante Möglichkeiten.
Arbeitsauftrag 13: Experimentieren Sie bitte mit den Möglichkeiten, Rahmenlinien festzulegen und in Excel zu zeichnen sowie Textfelder anzulegen.
26
Relative und absolute Adressierung
Kopiert man Formeln und Funktionen, werden die Zeilen- und Spaltenangaben
(also die Adressierung) automatisch angepasst:
Das ist aber nicht immer erwünscht:
Fehler, da relativ adressiert!
Abhilfe: Absolute Adressierung:
Der „Trick“ mit dem $ - Zeichen!
Jetzt klappt das „Runterziehen“:
27
Die logische Funktion WENN
In Excel-Zellen können auch programmiersprachenähnliche Konstruktionen
sehen. Ein einfaches Beispiel möge dies demonstrieren:
Beispiel:
Es soll eine Zahl von der anderen subtrahiert werden. Dabei muss die kleinere
Zahl immer von der größeren abgezogen werden; in welcher Zelle die kleinere
Zahl steht, liegt nicht fest und muss jeweils neu ermittelt werden.
In Zelle B5 steht, wie es gemacht wird, hier erscheint dann auch das Ergebnis.
WENN(B3>B4;B3-B4;B4-B3)
Funktionsname (if)
Bedingung
ja-Fall
nein-Fall (else)
Hinweis: Es existieren noch weitere Funktionen: FALSCH, WAHR, UND,
ODER, NICHT.
28
Eine andere Möglichkeit:
Auf fx klicken und dann die WENN – Funktion auswählen
Funktionsargumente eintragen und auf ok klicken.
Vorteil: Klammern und Trennzeichen werden automatisch eingefügt!
29
Das Ergebnis (genau wie oben):
Arbeitsauftrag 14: Die Provisionen von Außendienstmitarbeitern sind zu
berechnen. Jeder Mitarbeiter erhält 2% Provision. Wenn der Umsatz über
200.000 € Euro liegt, beträgt die Provision für diesen Mitarbeiter 3%.
(Hilfe: Am Ende dieser Unterlagen wird eine Lösungsmöglichkeit vorgestellt.)
30
Arbeitsauftrag 15: Eine Versicherung erstattet ihren Mitgliedern einen Teil des
Jahresbeitrags zurück: bei mehr als 10-jähriger Mitgliedschaft 14%, bei mehr als
5-jähriger Mitgliedschaft 9%, andernfalls 4% (Mehrfachauswahl).
Logik:
Programm rueckerstattung
jahre > 10
ja
rueck =
beitrag_j * 0,14
nein
jahre > 5
ja
nein
rueck =
beitrag_j * 0,09
rueck =
beitrag_j * 0,04
Erstellen Sie bitte eine geeignete Oberfläche zur Lösung dieses Problems. Wie
sieht die entsprechende WENN-Formulierung aus? (Hilfe: Am Ende dieser
Unterlagen wird eine Lösungsmöglichkeit vorgestellt.)
31
S-Verweis und W-Verweis
Diese Möglichkeit hilft beim automatischen Ausfüllen von sich wiederholenden
Zelleninhalten. Ein Tabellenbereich kopiert sich mit Hilfe eines Suchwertes
automatisch Inhalte von einem zweiten Tabellenbereich.
Beispiel: Bei einer Trainingseinheit können maximal 100 Punkte erreicht
werden. Jedem Teilnehmer ist entsprechend seiner erreichten Punktzahl eine von
vier Bemerkungen mitzuteilen:
Von 0-49 Punkte:
Trainingseinheit wiederholen
Von 50-69 Punkte:
Fehler korrigieren
Von 70-79 Punkte:
Alles ok!
Ab 80 Punkte:
Prima!
Der Text in Spalte D wird entsprechend der erreichten Punkte (Spalte C) aus
dem Tabellenbereich der Spalten F und G ermittelt:
SVERWEIS(C2;$F$2:$G$5;2)
Suchwert (hier: 60 Punkte)
Spalte C
Suchbereich: die zweite Tabelle
Auf Kopierbarkeit achten!
ErgebnisSpalte: = 2.
Spalte des
Suchbereichs
Beachte: Der Suchbereich muss hier aufsteigend sortiert sein (von…bis)!
32
Das Verfahren:
Für C2 (60) ist die entsprechende Bemerkung zu finden
Excel prüft Zeile 1 der Suchmatrix und findet dort den Wert 0.
0 ist <= 60, diese Zeile könnte theoretisch also in Frage kommen.
Excel prüft Zeile 2 der Suchmatrix und findet dort den Wert 50.
50 ist <= 60; diese Zeile könnte theoretisch also in Frage kommen.
Excel prüft Zeile 3 der Suchmatrix und findet dort den Wert 70.
70 ist allerdings NICHT <= 60, also wird die letzte theoretisch in Frage
kommende Zeile, also Zeile 3 als die passende Zeile erkannt. Dort steht in der
zweiten Spalte das Ergebnis: „Fehler korrigieren“. Diese Bemerkung erscheint
nun in D2.
Nun ist auch klar, warum der Suchbereich aufsteigend sortiert sein muss.
Die Möglichkeit, mit fx die Formel einzutragen:
33
Arbeitsauftrag 16 (Erweiterung von Aufgabe 14): Die Provisionen von
Außendienstmitarbeitern sind zu berechnen. Es existieren sechs Provisionsstufen
(1%...6%) in Abhängigkeit des Umsatzes. Formulieren Sie selbst eine
Aufgabenstellung hierzu (in Anlehnung an den Arbeitsauftrag 14) und
präsentieren Sie eine Lösung, bei welcher der S-Verweis zur Anwendung
kommt.
Der WVerweis funktioniert prinzipiell genauso wie der SVerweis (S: senkrecht).
Er findet Anwendung, wenn waagerecht zu suchen ist.
Formel für das oben stehende Beispiel:
WVERWEIS(C2;$F$1:$I$2;2)
34
S-Verweis, erweiterte Anwendungsbereiche
Es ist sinnvoll, Stammdaten wie Kundenname, Artikel-bezeichnung usw. in
einem gesonderten Tabellenblatt zu halten, so dass diese bei Bedarf dann
automatisch in ein neues Blatt übernommen werden.
Beispiel: Rechnung, Übernahme der Artikelbezeichnung aus dem Blatt "Artikel"
Diese Daten werden aus dem
Blatt "Artikel" automatisch
übernommen.
35
Wie es gemacht wird:
1) Tabellenblatt "Artikel" anlegen
2) Tabellenblatt "Rechnung (S-Verweis") anlegen
3) Beim Eintragen der Daten folgendermaßen vorgehen:
a)
b)
c)
d)
Arbeitsauftrag 17: Testen Sie bitte die Arbeitsschritte a) - d) und erklären Sie
diese!
36
Allgemeine „Formel“:
SVERWEIS(Suchkriterium;Matrix;Spaltenindex;Bereich_Verweis)
 Suchkriterium: Bei uns: Artikelnummer
 Matrix: Bezug zu einer anderen Tabelle: Tabellenname + Ausrufezeichen,
dahinter der Bereich, der angesprochen ist
 Spaltenindex: Nummer der Spalte, aus der etwas zurückgegeben werden
soll, hier: Artikelbezeichnung (steht in der 2. Spalte)
 Bereich_Verweis: Bei FALSCH: Rückgabe nur bei 100%
Übereinstimmung, ansonsten Rückgabe von #NV (non value)
 Bereich_Verweis: Bei WAHR (oder weggelassen): Rückgabe des
nächsten größeren Zelleninhaltes, wenn keine 100%ige Übereinstimmung
vorliegt
Zur Wenn-Funktion: Es soll eben nur dann nach einer Artikelbezeichnung/Preis
gesucht werden, wenn auch eine Artikelnummer vorliegt.
Hier: Ohne die WENN Erweiterung
37
Diagramme erstellen
Ausgangstabelle (Testdaten: s. am Ende dieser Unterlagen):
1. Gewünschter Bereich markieren:
2. Diagrammform auswählen:
38
Diagrammtitel ändern:
Feld anklicken und Titel ändern
Hier werden noch viele Möglichkeiten angeboten…
39
… z. B. „Achsentitel“:
Achsentitelfelder anklicken und ändern – fertig!
40
Kreisdiagramm:
Summenspalte markiren, Strg – Taste drücken und Filialnamen markieren:
Kreisdiagramm auswählen und Titel anklicken:
41
Titel ändern und „Datenbeschriftungen“ anklicken:
42
Diagramme aus dem technischen Bereich
Gewünscht wird folgende Darstellung:
A
B
C
Beispiel: Funktionsgraph der Funktion y = x²
43
Beispiel: Das Arbeiten mit mehreren Reihen (s. Diagramm-Assistent)
44
Hier enden die Excel-Grundlagen.
Eine mögliche Lösung von Arbeitsauftrag 14:
Eine mögliche Lösung von Arbeitsauftrag 15:
Testdaten (Diagramme usw.): Diese markieren und einfach nach Excel kopieren,
Summenfelder bilden und diese formatieren
2011
2012
2013
2014
Filiale Köln
779.980,00 €
980.502,00 €
923.211,00 €
905.015,00 €
Filiale Bitburg
345.234,00 €
160.318,00 €
220.254,00 €
224.032,00 €
Filiale Merzig
498.956,00 €
204.211,00 €
143.026,00 €
301.065,00 €
Filiale Trier
729.043,00 €
967.987,00 €
150.947,00 €
350.998,00 €
Filiale Saarbrücken
829.560,00 €
799.566,00 €
731.724,00 €
680.129,00 €
45