Grundlagen in EXCEL (Kurzanleitung)
für Ingenieure
(Ohne ausführliche Einführung
in Visual Basic)
von
Rolf Wirz
Version 1.0.1 vom 11.10.2008 (mit Programmversion MS EXCEL 2002)
GrundlagenInEXCEL.htm
erstellt mit MS-Word
© Rolf Wirz |
2007 / 2008 |
|
(Ausdruck: Ca 117 A4-Seiten ohne Zusatzmaterial) Hinweis: Für den Ausdruck ist das Material im als
pdf formatiert. Siehe Link: http://rowicus.ch/Wir/MathcadExcelAndereMath/ScriptEXCEL/GrundlagenInEXCEL.pdf
|
Adresse
des Autors |
Rolf Wirz, Prof.
f. Math. Hochschule für Architektur, Bau und Holz HSB Pestalozzistrasse 20, CH-3400 Burgdorf Tel. +41 (0)34 426 42 30 |
Zusammenführung
der Skripts „Einführung in EXCEL“ und „EXCEL-Trickkiste“
Zum Inhaltsverzeichnis
klick klick zum Inhaltsverzeichnis
1.1. Was ist und was soll EXCEL?
1.2. Einige Vor- und Nachteile
2. Was finden wir auf einer EXCEL-Mappe?
2.1 Allgemeines zur Arbeitsmappe
3. Erste Schritte beim Arbeiten mit Tabellen: Schreiben und rechnen
3.1. Matrixstruktur, Schreiben und rechnen
3.2. Umgang mit Zellen, Zeilen, Spalten und Feldern
3.3. Ausfüllen einer Tabelle anders als von oben nach unten
4.2. Das Beispiel einer Summation von vielen Zahlen
4.5. Text- und Zellenformatierung (Bereichs- oder
Tabellenformatierungen)
5. Formatierung und Einbindung extern erstellter Grafiken
5.1. Einbindung einer extern erstellten einfachen Graphik
5.2. Einbindung von mit einem Mathematik-Programm erstellten Graphiken
und Formeln
6.3. Funktionen: Verwendung von Hilfen (Hilfe-Funktion und
Funktionsassistent)
6.4. Mehrfachanwendung von Formeln
6.5. Einfache Funktionen in Formeln, Anwendung auf Bereiche
6.8. Zusammengesetzte (verschachtelte) Formeln
6.9. Voreingestellte automatische Berechnung
6.10. Das Problem von Fehlern in Formeln
7. Relative und absolute Bezüge
7.1. Die Notwendigkeit von absoluten Bezügen
7.2. Bezüge auf Zellen von andern Tabellen derselben Arbeitsmappe
7.3. Bezüge auf Zellen von andern Tabellen in anderen Dateien
7.5 Lehren aus dem Arbeitsblatt 1 EXCEL
8. Namen von Zellen und Matrizen, Matrixprodukt, inverse Matrix,
Gleichungen
8.1. Benennung von Zellen und Matrizen (Bereichen)
8.4. Gleichungen lösen mit EXCEL
9. Aspekte professioneller Formatierung
9.2. Layout für eine Arbeitsmappe
9.3. Sortieren nach Spalten, ausblenden von Zeilen, gruppieren von
Zeilen
9.5. Lehren aus dem Arbeitsblatt 2 EXCEL
9.6. Lehren aus dem Arbeitsblatt 3 EXCEL
10. Datenkontrolle, Datengenerierung, Datendarstellung (Diagramme)
10.1. Automatische Eingabeprüfung bei Dateneingabe
10.2. Datengenerierung für Funktionsgraphen
10.3. Funktionsgraphen und Diagramme (Datendarstellung)
10.5. Lehren aus dem Arbeitsblatt 4 EXCEL
11. EXCEL als einfaches Datenbankprogramm
11.1. Eine einfache Datentabelle und die Datenbankprobleme
11.2. Sortieren und Auswahl mit der Maske
12. Verschiedene weitere Stärken von EXCEL
12.1. Verschiedene Arbeitsmappen gleichzeitig geöffnet haben
12.2. Ausblenden von Spalten, Zeilen oder Zellen
12.3. Tabellenblätter oder Bereiche vor Veränderungen schützen
12.4. Kommentare in Zellen einfügen
12.8. Zahlenformate und Formatvorlagen in EXCEL
13. Bemerkung zu den Vorgängerskripts, EXCEL-Trickkiste
13.1. Links zu den Vorgängerskripts
13.2. An Stelle einer Einleitung zu „EXCEL-Trickkiste“
15. Mehrfachoperationen oder Tabellenerzeugung
15.2. Ein Beispiel mit zwei variablen Eingängen
15.3. Ein Beispiel mit nur einem variablen Eingang
16.2. EXCEL-Werkzeuge zur
Trendermittlung
16.3. Anwendung 1: Die Trendfunktion
16.4. Anwendung 2: Die Schätzer-Funktion
16.5. Anwendung 3: Die VARIATION-Funktion
17.2. Das Aufzeichnen eines Macros an einem Beispiel
18. Anhang: Arbeitsblätter und weiterführende Literatur
18.2. Weiterführende Literatur
(Ein
Tabellenkalkulationsprogramm, nutzbar als numerischer
"Taschenrechner")
von
Rolf Wirz
EXCEL ist ein Tabellenkalkulationsprogramm,
das von Microsoft übernommen, ausgebaut und ins Office-Paket integriert worden
ist. Der Name „Office“ sagt, dass es dabei um Büro-Software geht. Das ist
Software, welche von Büroangestellten, hauptsächlich also Leute mit
durchschnittlicher kaufmännischer Ausbildung, sofort eingesetzt werden können
sollte und darüber hinaus auch noch einiges weiteres zu bieten hat. Fest steht
dabei allerdings, dass es sich nicht um ein Programm handelt, das den Anspruch
stellt, Mathematik-Software, zu sein, etwa ein Computeralgebra-Programm. Das
ursprüngliche Ziel war wohl, damit rasch Berechnungstabellen für
buchhalterische und andere Zwecke erstellen zu können. (Der Kundschaft wegen sind
dies vor allem kaufmännische Zwecke.) Eine wichtige Eigenschaft des Programms
war es, dass mit der Änderung einer in irgendeiner Zelle stehenden Zahl auch
sofort alle Resultate angepasst werden sollten, die sich auf der Grundlage
dieser Zahl berechnen. Das ist natürlich auch im Ingenieurbereich bequem.
Damit kann man folgende Vor- und Nachteile
bezüglich Ingenieuranwendung einander gegenüberstellen:
Einige Vorteile:
1. Das Programm ist heutzutage auf den meisten Computern
vorhanden. Die Portabilität ist daher problemlos.
2. Der Einstieg ins Programm ist sehr einfach: Man kann
sehr schnell erste Anwendungen selber machen.
3. Tabellenkalkulation ist ein Arbeitsgang, welcher im
Ingenieurberuf häufig auftritt.
4. Mit einem gewissen Aufwand lassen sich damit auch
Projekte mit komplexeren Berechnungen ausführen.
5. Man kann mit dem Programm Wysiwyg (what you see ist
waht you get) sehr große Tabellen bearbeiten. Den Tabellengrößen sind aber
durch die Bildschirmgrößen der Übersichtlichkeit wegen auch Grenzen gesetzt.
6. EXCEL-Tabellen lassen sich hübsch gestalten. Sie
erzeugen bei Präsentationen eine gute Wirkung.
7. In EXCEL ist noch eine leistungsfähige Programmiersprache
integriert: VBA oder Visual Basic.
Einige Nachteile:
1. EXCEL ist kein Computeralgebra-Programm, wie sie
heute auch im industriellen Ingenieurbereich standardmäßig eingesetzt werden.
Funktionen einer symbolischen Metasprache, mit denen man regelbasiert arbeiten
könnte, oder Eigenschaften wie Pattern Matching resp. musterbasierte Absuche
fehlen vollständig.
2. Der Umfang der mathematischen Möglichkeiten ist trotz
des Lobs der Herstellerfirma begrenzt.
3. Das Programm kann man unter die Numerikprogramme
einordnen. Symbolisch oder exakt kann man damit praktisch nicht arbeiten.
4. Das Programm ist betreffend der mathematischen
Fähigkeiten mit einem numerischen Taschenrechner vergleichbar, der
Tabellenkalkulations-eigenschaften und hübsche Formatierungsmöglichkeiten
bietet, mathematisch aber kein Gewicht hat, abgesehen von der Möglichkeit,
VBA-Macros zu schreiben und aufzurufen.
5. Das Erlernen von VBA ist ein zusätzlicher größerer
Aufwand. Einfacher und mit der Arbeitszeit vielleicht billiger wäre es, diese
Zeit in ein Computeralgebra-Programm zu investieren.
6. Mit der Möglichkeit von Macros hat man auch das
Problem der Viren.
7. Man wird mit EXCEL leicht verleitet, das Programm
„nicht gewinnbringend“ anzuwenden, indem man auf aufwändige Weise Operationen
programmiert, die mit CAS-Programmen ohne großen Zeitverlust erledigt werden
können.
Dokumentiere dich im Internet über EXCEL.
Hinweise:
-
Wikipedia sowie EXCEL-Foren
-
http://rowicus.ch/Wir/Scripts/restricted/MasterIndex.html (nach EXCEL suchen, nur für internen
Gebrauch, passwortgeschützt)
-
http://rowicus.ch/Wir/MathcadExcelAndereMath/FileList2.html
In der Fußleiste, auf dem Desktop oder unter
„Start è Programme è …“, eventuell unter „Microsoft Office …“ muss das Icon
zu finden sein, wenn EXCEL installiert ist. Ein Klick auf dieses Icon öffnet eine Arbeitsmappe. Ihr Anblick ist etwa wie
folgt:
Beim Öffnen einer neuen Mappe erhält diese
default den nummerierten Titel „Mappe1“. Wenn wir diese Mappe unter „Datei è Speichern unter“ z.B. unter dem Namen Work1.xls
abspeichern, so erscheint der neue Name in der Titelleiste. „xls“
ist die Dateierweiterung, welche eine Datei dem Programm als EXCEL-Datei
identifiziert. |
|
Unten sehen wir „Tabelle 1“, „Tabelle 2“ und
„Tabelle 3“. Momentan ist in der Abbildung oben Tabelle 1 geöffnet. Klickt man
auf Tabelle 2, so wird diese geöffnet und Tabelle 1 verschwindet. Immer in der geöffneten
Tabelle kann gerechnet oder geschrieben werden. Klicken wir z.B. mit der
rechten Maustaste auf den Namen „Tabelle 3“, so öffnet sich ein Fenster.
Klicken wir in diesem auf „verschieben, kopieren …“ und kreuzen dann im neuen
Fenster „Kopie erstellen an“, so wird die hier immer noch leere Tabelle
kopiert. Dann haben wir 4 Tabellen zur Verfügung.
Unterhalb der Namensleiste
(mit dem Dateinamen Work1.xls) befinden sich die Symbolleisten. Hier finden wir die wichtigen Pull-down-Menüs
„Datei“, „Bearbeiten“, „Ansicht“, „Einfügen“, „Format“, „Extras“ u.s.w., welche
viele Funktionen zur Verfügung stellen (über 200). Es lohnt sich, mit
Arbeitsmappe, welche noch keine wichtigen Daten enthält, diese Menüeinträge
etwas auszuprobieren. Hier darf vorerst
einmal gespielt werden!
-
Probiere das hier Gelesene praktisch mit EXCEL aus.
-
Probiere die folgenden Tastenkombinationen in EXCEL
praktisch aus: Caps+Pfeiltasten, Ctrl+ Pfeiltasten, Alt+ Pfeiltasten,
Caps+PageUp/PageDown, Ctrl+PageUp/PageDown, Alt+PageUp/PageDown.
Eine Tabelle
besteht aus mit Zahlen nummerierten Zeilen
und aus mit Buchstaben nummerierten Spalten.
Eine Tabelle ist daher immer eine Matrix,
bestehend aus Zellen. Die erste
Zelle links oben ist somit A1. A1
ist hier die Zellennummer (oder
der Index). |
|
In eine Zelle kann man einen Text schreiben
oder in ihr eine Rechnung ausführen.
Nebenstehendes Bild zeigt, dass hier in A1 der Text „Guten Tag!“
geschrieben worden ist. Dadurch ist sie zu einem Textfeld geworden. In A2 wurde die Rechnung „1+1“ ausgeführt. Hier hat man jetzt ein Zahlenfeld (berechnetes Feld).
Um eine solche Berechnung auszuführen muss
man entweder in A2 „=1+1“ ohne Leerschläge eingeben oder denselben Text in die Eingabezeile schreiben (neben dem
Symbol fx). Mit der „Enter-Taste“ (angeschrieben mit „Enter“) oder der „Return-Taste“
(angeschrieben mit einem Pfeil nach links, hinten nach oben angewinkelt) kann
die Rechenoperation ausgeführt werden.
In der Eingabezeile
erscheint immer der Inhalt der aktiven Zelle oder der ersten Zelle oben links
eines aktiven Bereiches. (Eine aktive Zelle ist im Bearbeitungsmodus. Dieser Modus kann auch mit Hilfe der Esc-Taste verlassen werden.)
Ein Klick auf das Symbol fx
öffnet den Funktionsassistenten, mit
dessen Hilfe diverse mathematische Funktionen eingegeben werden können. Da es
viele solche Funktionen gibt, lernt man diese am besten bei Gelegenheit durch
ihre Benutzung kennen.
Die Zellennummern können beim Rechnen auch
als Namen von Variablen oder als Speicheradressen
verwendet werden. In der Zelle A2 steht z.B. der Wert 2 und in B1 der Wert 3.
Schreiben wir in B2 dann die Formel
„=A2+B1“, so erhalten wir durch drücken der Enter-Taste den Wert 5. Erhöhen wir dann in B1 den Wert 3 auf 10,
so erscheint in B2 nach dem Drücken der Enter-Taste automatisch der Wert 12. |
|
Achtung: Damit keine „Unfälle“ (z. B. Datenverlust) passieren
ist es ratsam, die Mappe von Zeit zu Zeit abzuspeichern.
Unter „Bearbeiten“. Im Menü „Bearbeiten“ (Symbolleiste) existiert eine Funktion,
welche es erlaubt, Falscheingaben rückgängig zu machen.
Bei
den Inhalten der Zellen, welche wir durch Variablennamen ansprechen, redet man
auch von Zellbezügen.
Arbeitet man z.B. in Zelle A2, so erscheint
der Name dieser Zelle am linken Rand links neben dem Symbol fx.
Der Rand der Zelle, mit der man gerade Arbeitet, wird fett angezeigt. Klickt man auf die Zeilennummer 2, so wird die
ganze Zeile 2 fett angezeigt. Das bedeutet dann, dass diese Zeile ausgewählt
oder aktiviert ist. Ebenso wird die ganze Spalte B fett angezeigt (aktiviert),
wenn man auf die Spaltennummer B klickt.
Man kann aber auch ganze Bereiche
(Matrizen) aktivieren, indem man in eine Zelle klickt und dann mit der Maus
bei gedrückter linker Maustaste den Bereich soweit zieht (aktiviert), wie man
ihn haben will. Wir haben dann einen aktivierten
Bereich. Zellen oder Felder können auch dafür
vorgesehen sein, später noch irgendwelche Eingaben aufzunehmen, die eventuell
dann als Daten für schon eingegebene Formeln dienen. Dann handelt es sich um Eingabefelder. |
|
Statt Zellen anzuklicken, kann man in ihnen
auch mit den Pfeiltasten navigieren.
Ist eine Zelle (z.B. eine Textzelle) zu
klein, so kann ihre angehörige Zeile oder Spalte durch anklicken des Randes bei
den Spalten- oder Zeilennummern und durch Ziehen mit der Maustaste vergrößert werden. Doppelklick auf den
Rand bei der Spaltennummer bewirkt eine automatische
Anpassung.
Wenn wir einen Wert in eine Zelle eingeben und
dann auf „Enter“ oder auf „Wagenrücklauf“ drücken, so wird sofort die nächste
untere Zelle aktiviert. Wir hätten aber gerne diejenige gleich rechts daneben
aktiviert, denn wir müssten als Beispiel viele Zahlen in eine Zeile eingeben.
Das können wir nun umstellen. Wir klicken dazu in der Symbolleiste auf „Extras“
und im sich öffnenden Fenster dann auf
„Optionen…“. Dann wird wieder ein Fenster geöffnet, in dem wir die
Registerkarte „Bearbeiten“ anklicken. Nun ist hier unter vielen Einträgen auch
ein Eintrag mit dem Namen „Richtung“ sichtbar. Hier klicken wir auf den Pfeil
und wählen dann „Rechts“ aus. Wenn wir nun „OK“ anklicken, so können wir uns
davon überzeugen, dass jetzt das „Ausfüllen nach rechts“ aktiviert ist. Auf
diese Weise lassen sich noch sehr viele andere Dinge einstellen.
Dokumentiere dich im Internet über EXCEL. Hinweise:
-
Wikipedia
-
http://rowicus.ch/Wir/Scripts/restricted/MasterIndex.html (nach EXCEL suchen, nur für internen
Gebrauch, passwortgeschützt)
-
http://rowicus.ch/Wir/MathcadExcelAndereMath/FileList2.html
Importiere mittels „Datei è Öffnen“ eine
Datei in einem anderen Dateiformat.
Ebenso für Datenexport!
Beobachte, was dabei herauskommt!
Wir wollen einen Text formatieren. Dazu stehen uns die Hilfen zur Verfügung, wie
wir sie auch z.B. von Word kennen. Wenn wir eine Zelle oder einen Text (nur
einen Teil einer Zelle) in der Eingabezeile mit der Maus (linke Maustaste) aktivieren
und dann in der Symbolleiste F (FETT) anklicken, so erscheint der
markierte Text fett. Eine größere Anzahl von Formatierungshilfen findet man
unter „Format“ und z.B. „Zellen…“ in der Symbolleiste. Man kann auch ganze
Zeilen, Spalten oder Bereiche formatieren. In der Mitte des obigen Bildes
sehen wir die Icons für „linksbündig“, „zentriert“ und „rechtsbündig“. |
|
Man kann z.B. auch mit der rechten Maustaste
in eine Zelle oder in einen aktivierten Bereich klicken. Dann erscheint das
rechts nebenstehend gezeigte Menü, das u.a. auch Formatierungshilfen zur
Verfügung stellt.
Am besten lernt man den Umgang mit den
Formatierungsmöglichkeiten, indem man sie einmal ausprobiert.
Bemerkung: Beachte die Symbole in der Symbolleiste für Formatvorlage, Schriftgröße,
fett, kursiv, unterstrichen, rechtsbündig, zentriert, linksbündig,
zusammenführen und zentrieren, Einzug verkleinern und vergrößern, Rahmen,
hervorheben und Schriftfarbe!
|
|
Hier ist in die noch leere Zelle B7 geklickt
worden. In der Symbolleiste finden wir das Symbol „Sigma“ (griechisches grosses
S). Klicken wir auf das Sigma, so wird automatisch die Summierungsfunktion zur
Verfügung gestellt.
„Enter“ ergibt den Wert 13. Statt „Enter“ kann man auch den grünen
Hacken links von der Formel in der Eingabezeile benutzen. (Das rote X dort
bedeutet abbrechen.)
Die Formel „=Summe(B3:B6)“ (summiert die Zellen B3 bis und mit B6) lässt
sich in der Eingabezeile übrigens von Hand verändern (mit der Maus an die
entsprechende Stelle klicken und abändern).
Achtung: Unsinnige Änderungen ergeben keine vernünftigen Resultate!
|
|
Wenn wir 2 übereinander liegende Zellen
aktivieren und dann mit der linken Maustaste am unten rechts erscheinenden „quadratischen
Punkt“ fassen und nach unten ziehen, so wird die Nummerierung automatisch
eingefügt. Wenn wir dann A1 bis B11 aktivieren und in |
der Symbolleiste „Format è AutoFormat“
wählen, dann wird ein Menü zur Verfügung gestellt (siehe Bild unten), das die
Auswahl verschiedener Standard-Formattypen gestattet. Der Balken rechts in
diesem Menü lässt sich nach unten verschieben. Die Auswahlmöglichkeiten sind
sehr gross. Wir wählen den 3. Typ und erhalten die oben rechts gezeigte
Darstellung.
Wir markieren den Bereich B4 bis B11. Im Kontextmenü (hier Formatieren è Zellen) wählen wir im Fenster „Zellen formatieren“ die Option „Währung“
aus und klicken auf OK. Das ergibt bei der Einstellung für die Schweiz die oben
gezeigte Formatierungsart.
Wir aktivieren den Bereich C2 bis D3. Im Kontextmenü (hier Formatieren è Zellen) wählen wir im Fenster „Muster“ die Farbe gelb und klicken auf
OK. Das ergibt die oben gezeigte Hintergrundsfarbe. Wir können auch Ausrichtung
eines Textes, Schrift, Tabellenrahmen u.s.w. auswählen. Das lernt man alles am
besten durch ausprobieren.
Schriftfarbe rot in D3, Hintergrundsfarbe gelb in E3, Rahmen der Zellen
schwach und der aktivierten Tabelle stark ergibt das Bild links. Dabei wurde
noch die Textausrichtung in D3 auf 90 Grad gestellt. (Siehe obiges Fenster
rechts.)
Bemerkung zur Zellen- und Spaltenformatierung: Im oben gezeigten
Bild existieren auch die Optionen „Textsteuerung è An Zellgrösse anpassen“ sowie „Zellen verbinden“! In dieser Beziehung
interessant ist auch „Format è Spalte è Optimale Breite festlegen“ für die Minimalbreite ausgefüllter Spalten!
Probiere das hier Gelesene praktisch mit EXCEL aus.
Wir erstellen eine Graphik mit einem Graphikprogramm, z. B. mit dem sehr einfachen Programm Paint. Diese Graphik wird dann in einem
Ordner als jpg-Datei abgelegt.
Wichtig ist es zu wissen, dass jpg- Dateien und gif-Dateien internetfähig sind, d.h. mit den
meisten Browsern dargestellt werden können. Dazu ist der Speicherbedarf für
diese Dateien sehr klein.
|
Nun können wir diese eine Zelle aktivieren
und diese Datei dann unter „Einfügen è Grafik è Aus Datei“ unter ihrem Namen abrufen. Hier war die
aktivierte Zelle A59. Das Bild wird mit der linken oberen Ecke in die Zelle
A59 platziert. Es kann danach durch anfassen mit der Maus verschoben werden.
Zieht man mit der Maus an den weißen kleinen Kreislein, so kann man das Bild
vergrößern oder verkleinern. Zieht man am grünen Punkt, so kann man das Bild
drehen. Am besten probiert man den Umgang mit
Grafiken in EXCEL gleich selbst aus. |
Darstellung einer mathematischen Formel,
die mit Mathematica erzeugt worden
und darauf als Grafik-Datei gespeichert worden ist. |
|
Die Formel wird dann als Grafik eingefügt,
wie oben beschrieben. Oberhalb ist noch die gedrehte vorhergehende Graphik
sichtbar.
Hier sehen wir das Bild einer Matrix,
welche mit Mathematica erstellt
worden ist. Natürlich gespeichert als Graphik-Datei. Und nebenstehend ein mit
Mathematica erstellter Plot. Nach
der Einfügung entstand das unten sichtbare Bild. |
|
EXCEL ist zwar nicht das geeignete Mittel, um Flyers zu erstellen. Wir
wollen es der Übung halber aber trotzdem versuchen. Aufgabe: Erstelle mit Excel
einen Flyer (PDF, A4) für eine Einladung an eine Ausstellung, an der du
beteiligt bist. Die genaueren Spezifikationen kannst du selbst nach eigener,
freier Phantasie wählen. Allerdings wird eingebundene Graphik erwartet. Zu
präsentieren sind ein XLS-File und ein PDF-File, welche eindeutig auf den 1.
Blick von jeder Drittperson dir zugeordnet werden können. Weiter soll Schule,
Klasse und Datum sofort sichtbar sein.
Zeichen |
Zahlenbeispiel |
Resultat |
Formel (A1 è 3, B1 è 2) |
+ |
=3+2 |
5 |
=A1+B1 |
- |
=3-2 |
1 |
=A1-B1 |
* |
=3*2 |
6 |
=A1*B1 |
/ |
=3/2 |
1.5 |
=A1/B1 |
^ |
=3^2 |
9 |
=A1^B1 |
% |
=3% |
0.03 |
=A1% |
Bemerkenswert ist, dass „=3/2“ numerisch 1.5
ergibt. Das Programm wählt also nicht exakte Brüche, sondern Dezimalbrüche,
die auch unexakt sein können.
Beispiel: (Bild) In A1 steht 3.00000001 (7-mal die 0), in B1
3.000000001 (8-mal die 0). Die 9. Stelle wird also nicht ausgegeben. |
|
In nebenstehendem Bild die Situation für
„=A1%“ gezeigt. Wichtig ist es noch zu wissen, dass EXCEL
ein Numerikprogramm ist. |
|
Ein solches Programm liefert
Dezimalbrüche oder ganze Zahlen zurück. Beispiel: Die Eingabe è „2/3“ erzeugt die Ausgabe „0.666666667“ im Format „Format è Zellen… è Zahlen è Standard“.
Vergleichsoperatoren dienen dem Vergleich zweier Werte oder Zellen (Variablen).
Das Resultat ist einer der dual-logischen Wahrheitswerte „WAHR“ oder „FALSCH“.
Folgende Operatoren sind erwähnenswert:
Zeichen |
Bezeichnung |
Beispiel eines Befehls (Formel) |
= |
gleich |
= (A1=B1) è Bedeutung siehe unten |
> |
grösser |
= (A1>B1 |
< |
kleiner |
= (A1<B1 |
>= |
grösser gleich |
= (A1>=B1 |
<= |
kleiner gleich |
= (A1<=B1 |
<> |
ungleich |
= (A1<>B1 |
Der Befehl
„=(A1=B1)“ in der Zelle A2 geschrieben ergibt „FALSCH“. |
|
Der Befehl „=Wenn(A1=B1;1,2)“ in Zelle A2
geschrieben ergibt den Wert 1, wenn A1 gleich B1 ist und 2, wenn A1 ungleich
B1 ist. Da in A1 7- und in B1 8-mal die 0 steht, kommt 2 heraus. |
|
Um Funktionen in Formeln verwenden zu können, muss man eine Ahnung
haben, was es da überhaupt für Funktionen geben könnte und welche Namen diese
etwa haben könnten. Sonst kann man sie nicht suchen. Man muss also soviel Ahnung von Mathematik haben, dass diese
Funktionen in der eigenen Ahnung einge-schlossen oder durch die Ahnung erreicht
werden können. Um das Vorgehen bei der Suche nach einer Funktion zu erhellen,
wollen wir von einem Beispiel ausgehen.
Beispiel: Sie wollen die Sinus-Funktion studieren und herausfinden, ob
der Sinus von 180 (also in Grad gerechnet) dann 0 ergibt oder ob der Sinus von
3.14159 (also im Bogenmass gerechnet) etwa 0 ergibt. Wie finden wir die EXCEL-Schreibweise
des Sinus?
1.
Möglichkeit:
Wir benützen
die Hilfe-Funktion: Irgendwo (hier rechts außen) in der Symbolleiste steht die
Aufforderung „Frage hier eingeben“. Schreibt man in dieses Feld „Sinus“, so
klappt sich ein Fenster auf. Wenn wir dort SIN anklicken, kommen wir zum
Ziel. (Siehe nachstehendes Bild.) |
|
2.
Möglichkeit:
Verwenden wir
dagegen den Funktionsassistenten (nach unten zeigender Pfeil in der
Symbolleiste neben dem großen Sigma oder Summenzeichen, auf „weitere Funktionen“ klicken), dann
klappt ein Fester auf. Wir geben als zu suchende Funktion „Sinus“ ein und
wählen als Kategorie „Alle“ aus. Dann drücken wir OK. Damit kommen wir zum
Ziel. Auf diese Weise
können wir irgendwelche Funktionen verwenden, sofern wir von ihrer Existenz
wissen. |
|
Ein Blick auf
das aufgeklappte Fenster der Kategorien der Funktionen zeigt, aus welchen
Bereichen der Mathematik EXCEL Funktionen bereitstellt. An der Zahl stehen
über 200 eingebaute Funktionen zur Verfügung. Diese hier aufzuzählen
übersteigt den dem Skript zugedachten Umfang. Man kann aber im Internet
Listen mit Beschreibungen der EXCEL-Funktionen finden. |
|
3.
Möglichkeit:
Die Verwendung der EXCEL-Hilfe (Fragezeichen
in der Symbolleiste) ist selbsterklärend.
Beispiel: Neun mittels
Nummern identifizierbare Studenten haben eine Prüfung absolviert und dabei
Punkte erreicht. Nun soll die Note nach der Formel „Note = (Punkte/20)*5+1“
berechnet werden. Das soll dann nach der in der Schweiz früher üblichen Art
einen Notenwert zwischen 1 und 6 ergeben. Vorgehen:
-
Die Studierenden mit ihren Punkten werden nun in eine
Tabelle eingetragen. Zuerst Student 1 und 2 in A5 und A6. A5 und A6 werden dann
aktiviert, rechts unten mit der Maus (linke Maustaste) am kleinen fetten Winkel
gefasst und nach unten bis zu A13 gezogen. Die Nummerierung wird dadurch
automatisch vervollständigt.
-
Darauf wird in C5 die Formel „=B5/20*5+1“ eingegeben.
Darauf drückt man „Enter“, damit der Notenwert berechnet wird.
-
Nun wird die Zelle C5 rechts unten mit der Maus am
kleinen fetten Winkel gefasst und nach unten bis zu C13 gezogen. Das löst eine
Anpassung der Speicheradresse B5 durch die jeweils gültige Speicheradresse bis
B13 aus. Die Werte werden automatisch richtig ausgegeben und die Tabelle ist
fertig! Diese automatische Anpassung von
Formeln über ganze Tabellen macht den vermutlich grössten Vorteil von EXCEL
aus! Man kann damit in kürzester Zeit grössere Datenmengen verarbeiten!
-
Aktiviert man nun die Zellen C5 bis C13 und zieht sie
nach rechts, wiederum angefasst am fetten kleinen Winkel unten rechts, so
entstehen in der Kolonne D Werte, welche keinen Sinn zu machen scheinen.
Rechnen kann man ja immer etwas, die Frage ist dabei immer dieselbe: Macht es
überhaupt Sinn, was man da rechnet? Im 4. Bild unten sehen wir, was nun hinter
dem Wert in D13 für eine Formel steht: „=C13/20*5+1“. EXCEL hat also die
Speicheradressen B5 bis B13 durch C5 bis C13 ersetzt. Nach unten ziehen
bedeutet demnach die Nummern ersetzen (erhöhen), nach rechts ziehen bedeutet
die Buchstaben ersetzen (hier auch „erhöhen“). Man redet hier auch von
„relativen Bezügen“ der Werte. Davon wird ein eigenes Unterkapitel handeln.
|
|
|
|
Bemerkenswert ist, dass das Einfügen von Zellen die Bezüge nicht stört,
obwohl dabei die Namen der Speicheradressen ändern können. EXCEL passt dann die
Formeln automatisch richtig an:
|
Im obigen Bild wurde mit der rechten
Maustaste auf das C im Titel der Kolonne C geklickt. Wenn man das ausführt,
öffnet sich ein Fenster, das einem verschiedene Handlungs-möglichkeiten zur
Verfügung stellt. Wählt man „Zellen einfügen“, so wird Kolonne C nach rechts
in Kolonne D verschoben. Eine neue, leere Kolonne C ist also eingefügt
worden. Die in der neuen Kolonne D berechneten Werte stimmen nun alle immer
noch. Die Formeln sind also nicht verändert worden. |
Ebensolches stellt man fest, wenn man eine neue Kolonne B einfügt. Nach
der gleichen Methode kann man auch neue Zeilen einfügen. Dabei ändert sich
wiederum nichts an den Berechnungen.
Wir wollen nun Wege aufzeigen, die es erlauben, mit einfachen Mitteln
sofort ganze Datenbereiche mittels
Formeln zu verarbeiten. Als Beispiel wollen wir Maximum, Minimum, Mittelwert
und Anzahl der Noten in obiger Tabelle berechnen, unabhängig davon wie viele
Noten da eingetragen sind. Es handelt sich hier also um statistische
Kenngrössen, welche dazu dienen, beliebig grosse Datensätze mittels weniger
Zahlen zu beschreiben.
-
Um das Maximum zu berechnen, aktivieren wir erst den
Bereich der Noten, hier also die Zellen C5 bis C13. Dann suchen wir mit Hilfe
des Funktionsassistenten die Funktion Max für Maximum. Die Datenausgabe erfolgt
dann automatisch in Zelle C14. Wenn wir nun auf Zelle C14 klicken, sehen wir
die verwendete Formel: „=MAX(C5:C13)“.
Dabei werden die Zellen C5 bis C15 blau eingerahmt, wie man es im 4. Bild unten
ablesen kann. Die Formel: „=MAX(C5:C13)“ lässt sich leicht verändern. Ersetzen
wir z.B. 13 durch 11, so wird das Maximum nur über die Zellen C5 bis C11
berechnet. Um das zu bewerkstelligen, kann man auch auf gewohnte Weise mit der
Maus den blauen Rahmen anpassen. Gibt
man hingegen die Formel „=MAX(C6;C8;C9;D7)“,
so wird das Maximum über die Zellen C6, C8, C9 und D7 berechnet. Fehlende Werte
(hier in Zelle D7) werden dabei einfach ignoriert. Am besten probiert man das
gleich selbst aus.
-
Entsprechend verfährt man mit dem Minimum. In der
Formel wird dabei einfach „Max“ durch „Min“ ersetzt.
-
Ebenso geht es mit dem Mittelwert und der Anzahl.
-
Noch ein Hinweis: Wenn man die
Spaltennamen (Buchstaben) oder die Formelnamen klein schreibt, so passt EXCEL die Sache automatisch an.
|
|
|
|
Schon
gesehene Beispiele: Der Doppelpunkt in : „=MAX(C5:C13)“ oder das Semikolon in „=MAX(C6;C8;C9;D7)“.
Operator |
Typ und Beispiel |
|
Doppelpunkt, „ : “ |
Bereichsoperator,
„=MAX(C5:C13)“ |
Stellt einen Bezug auf alle
Zellen zwischen C5 und C13 her. |
Semikolon, „ ; “ |
Verbindungsoperator
(Vereinigung), „=MAX(C5:C13;D6:C9)“ |
Vereinigt die
Bezugsmengen C5:C13 und D6:C9. |
Leerschritt, „ „ |
Schnittmengenoperator,
„=MAX(C5:D12 C7:D16)“ |
Nimmt die Schnittmenge der
Bezugsmengen C5:D12 und C7:D16, also
C7:D12 |
Beispiel
für den Schnittmengenoperator:
|
|
Der Operator „&“ dient dazu,
Textzellen in einer Formel zu verketten. Beispiel:
In Zelle C1
steht der Wert „Tolle Sache “ (mit Leerschlag am Schluss) und in Zelle D2
steht der Wert „gestern um 11 Uhr.“ Wir geben in Zelle C3 ein: „=C1&D2“.
Dann kommt in C3 heraus: „Tolle Sache gestern um 11 Uhr“ |
|
Beispiel:
-
„=Exp(1)“ ergibt 2.71828183. Das ist eine Näherung für
die eulersche Zahl e.
-
Damit berechnen wir in einer Zelle
„=SIN(COS(3.14159)-1/3*EXP(1))“. Also
Kosinus von einer Näherung von Pi, davon minus 1/3 mal e hoch 1 (das ist gleich
e) und vom entstehenden Resultat anschliessend den Sinus.
-
und erhalten das Resultat -0.94431242.
-
„=Pi()“ ergibt den numerischen Näherungswert 3.141592654 für die Zahl Pi.
Anhand dieses Beispiels können wir einige Regeln betreffend die
Zusammensetzung von Formeln erläutern:
Regeln:
EXCEL ist eine
automatische Berechnung voreingestellt. Aktiviert man einen ganzen
Zellbereich, so erscheint unten die Summe (diese ist im Bild gleich 24).
Klickt man mit der rechten Maustaste auf diese Summe, so öffnet sich ein
Fenster. Darin kann man die Voreinstellung den eigenen Bedürfnissen anpassen. |
|
Wir betrachten
das nebenstehende Bild. Die Zelle Ck berechnet sich jeweils mit Hilfe der
Formel „=Ak/Bk“, wobei k eine Zeilennummer ist. In Zelle C2 steht #Div/0!.
Dort ist in B2 kein Nenner eingetragen. Der fehlende Wert wird als 0
interpretiert. Division durch 0 ist verboten! In C3 jedoch steht 0, weil A3
leer ist und daher als 0 interpretiert wird. 0 ist aber falsch. (C3 müsste
auch leer sein.) |
|
Leere Zellen sollten bei Anwendung von Formeln besonders beachtet
werden. Einmal wegen der Division durch 0, andererseits weil man aus fehlenden
Werten nichts berechnen kann, also kein Resultat erhalten kann, also auch nicht
0 als Resultat. Daraus geht hervor, dass es
Fehler gibt, die eine Fehlermeldung verursachen und solche, welche keine
verursachen. Am schlimmsten sind logische
Fehler, die zu Resultaten führen, welche falsch sind, aber nicht als falsch
erkannt werden.
Hier wird die
Funktion Sinus nicht dach den Vorgaben von EXCEL geschrieben. Resultat:
#NAME? è Unbekannter Name. |
|
Hier wird die
Zahl 12345678901234 in E1 eingegeben. Die Ausgabe in Zelle E1 müsste
1.23457E+13 sein. Mit E+13 ist 10 hoch 13 gemeint (wissenschaftliche
Zahlenschreibweise, Platz sparend). Da die Zellenbreite sogar für 1E+13 (kürzeste Form) zu klein ist, werden
Doppelkreuze ### ausgegeben. Bei Vergrößerung der Spaltenbreite erscheint
wieder der richtige Wert. |
|
Eine Weitere Möglichkeit ist die Benutzung der Funktionen „Extras è Fehlerüberprüfung“ oder von „Extras è Formelüberwachung“ (Detektiv).
Die Funktionen sind weitgehend selbsterklärend.
Wenn man Formeln wie in 6.4
geschildert mit der Maus kopiert oder mehrfach kopiert, dann werden die
Bezugsadressen resp. die Speicheradressen in einer Formel automatisch relativ
zur neuen Position der Speicherorte aktualisiert. Man spricht hier von
relativen Bezügen. Manchmal jedoch möchte man, dass eine alte
Speicheradresse beim Kopieren nicht
geändert wird, weil ausnahmsweise dort nichts verschoben worden ist. Daher wird
es notwendig, so genannte feste oder absolute Bezüge zu definieren.
Wir betrachten dazu ein Beispiel:
Im Bild rechts sehen wir eine Notentabelle.
Die Ergebnisse werden hier mit Hilfe einer Rundung auf eine halbe Note
berechnet. Exakt eine 6 bekommt, wer 55 Punkte erreicht. 55 steht deshalb in
der Formel in einem Nenner. Nun
geschieht es aber, dass nachträglich noch einige neue Studenten zur Prüfung
kommen, welche viel mehr Punkte erreichen, so dass 55 nicht mehr realistisch
ist. Es müssen daher neue Zeilen eingeschoben und zusätzlich muss die Zahl 55
verändert werden. |
|
Das Problem soll nun so gelöst werden, dass 55 durch das Maximum der
erreichten Punktzahlen minus eine noch festzulegende Zahl (Bonus) ersetzt
werden soll. Das Maximum soll automatisch berechnet werden. Der Bonus hingegen
soll immer von Hand schnell angepasst werden können. Beim kopieren der Formel
mit der Maus werden nun aber die Bezugsadressen immer angepasst (hier
Zeilennummern). Daher muss eine solche Anpassung für das Maximum und den Bonus
verhindert werden.
|
|
Wie man aus den beiden
Bildern sieht, wurde die Berechnung des Maximums beim Einfügen einer Zeile
angepasst. Das geschah automatisch.
Was in den Bildern nicht sichtbar wird, sind die Berechnungsformeln.
-
Im linken Bild steht in C6 die Formel „=RUNDEN(2*(B4/55*5+1);0)/2“.
Hier wird B4/55*5+1 zuerst verdoppelt, dann auf ganze Zahlen gerundet und
danach wieder halbiert. Dabei entstehen halbe Noten.
-
Statt 55 im Nenner ist nun im rechten Bild B3 (also
das Maximum) minus A3 (also 3) verwendet worden. Hier steht in C6 die Formel
„=RUNDEN(2*(B6/($B$3-$A$3)*5+1);0)/2“.
Die $-Zeichen
in $B$3 und in $A$3 verhindern die Erhöhung der Zeilennummer beim Kopieren der
Formel nach unten sowie auch die Weiterzählung der Buchstaben beim Kopieren
nach rechts, was hier vorerst keine Rolle spielt. Es handelt sich hier demnach
um absolute Bezüge, d.h. ein
Referenzieren auf fixe Zeilen- und Spaltennummern.
Regeln:
Beispiel:
Wir nehmen an, dass in der Zelle C5 von Tabelle1 ein Wert steht, den wir
in Tabelle2 in einer Formel verwenden wollen. Wie geht das nun?
Regeln:
Bemerkung: Tabellennamen lassen sich auch umbenennen: Klick auf den Tabellennamen
mit der rechten Maustaste è selbsterklärend.
Beispiel:
Die Datenquelle ist das File Proj5.xls im selben Ordner wie die
Arbeitsmappe. Dort holen wir in Tabelle3 den Bereich „$B$4:$C$54“. Die
Bezugsformel lautet dann:
"=[Proj5.xls]Tabelle3!$B$4:$C$54"
Bearbeite das Arbeitsblatt 1, abrufbar unter http://rowicus.ch/Wir/MathcadExcelAndereMath/FileList2.html .
Z.B. $F$12: Beim Kopieren (Ziehen) bleibt die Zeilen- und die
Spaltennummer erhalten.
Beispiel:
In A2 steht der
Wert 4, welcher mit k bezeichnet werden soll. Dazu klicken wir auf A2
(aktivieren) und darauf auf den Namen A2, welcher im Feld links am Ende der
Eingabezeile erscheint. „Enter“ sorgt dafür, dass die Zelle A2 jetzt k heißt.
Entsprechend verfahren wir mit den Bereichen C2:E4 (Matrix1) und G2:I4
(Matrix2), siehe Bild. |
|
Namen von Zellen oder Matrizen dürfen keine Leerschläge enthalten und
müssen mit einem Buchstaben oder einem Unterstrich „_“ beginnen. Sie dürfen bis
zu 255 Zeichen enthalten.
Namen können auch durch einen Titel (im Bild unten „Bereich 3“) mittels
„Einfügen è Namen è Erstellen è Namen“ erstellen
aus „(Hacken) Oberste Zeile è OK“ erstellt
werden. Bei diesem Verfahren muss allerdings beachtet werden, dass nur
Spaltenabschnitte als benannte Bereiche erstellt werden. Beim im nachstehenden
Bild dargestellten Beispiel besteht der Bereich3 dann aus den Zellen A2:A4. Die
Zellen B2:C4 werden nicht berücksichtigt, denn dort steht oben kein Titel.
|
|
Die Bereiche kann man
durch anklicken des Namens abrufen, den man im Fenster findet, das sich beim
Klick auf den Pfeil links neben dem Symbol fx öffnet.
Klicken wir z.B. auf den Namen „Matrix2“, so wird diese aktiviert (siehe im
Bild rechts). |
|
Will man gerade
serienweise Bereiche aus Spaltenabschnitten mit Titeln erstellen, so hilft
„Einfügen è Namen è Einfügen è Namen erstellen“ mit „(Hacken) Oberste Zeile è OK“. Wobei jetzt in jeder Spalte ein Bereichtitel
steht. (Die nebenstehenden Bilder |
|
|
zeigen, dass hier nun die
Namen „Bereich3“, „a“ und „b“ erstellt worden sind.)
Klickt man auf Einfügen è Namen è Definieren, so öffnet sich ein Fenster wie im nachstehenden Bild gezeigt.
Wir wollen z.B. den Namen „Matrix3“ löschen. Dann klicken wir auf den Namen
(aktivieren) und dann auf Löschen. Oder wir wischen einfach den Bezug in der
untersten Zeile aus. Wir können aber den Namen auch in der Zeile unterhalb von
„Namen in der Arbeitsmappe“ ändern. Wir klicken zuerst den alten Namen
an. In der Fusszeile wird der Bezug sichtbar. Dann tippen wir in der Kopfzeile
einen neuen gültigen Namen ein (Zellenadressen sind dabei ungültig). Dann
klicken wir auf „Hinzufügen“. Jetzt können wir bei bedarf den alten Namen noch
löschen wie eben beschrieben.
Beispiel:
Das Bild rechts
zeigt zwei Matrizen. Matrix1 ist A2:C4 und Matrix2 ist E2:G4. Die Namen und
die Bereichsangaben können wir synonym verwenden. Wir wollen das
Matrixprodukt Matrix1 mal Matrix2 im Bereich A6:C8 haben. Dazu aktivieren wir
diesen Bereich. Anschließend tragen wir in der Eingabezeile |
|
den Befehl
„=MMULT(Matrix1;Matrix2)“ ein.
Um die Formel (Befehl) nun wirksam zu machen, müssen wir sie als Matrixformel eingeben. Dazu drücken wir
erst die Taste F2 und anschließend die Kombination STRG+Ctrl+Enter. Wie man nun im Bild rechts oben sieht,
erscheinen um die Formel geschweifte Klammern. Der Versuch, diese direkt mit
der Formel einzugeben schlägt aber fehl.
Beispiel:
Das Bild rechts
zeigt die Berechnung der Inversen von Matrix2. Für diese Berechnung
aktivieren wir als Zielbereich E6:G8. Anschließend tragen wir in der
Eingabezeile den Befehl „=MINV(Matrix2)“ ein. Um die Formel wiederum wirksam
zu machen, müssen wir sie als Matrixformel
eingeben. Dazu drücken wir wieder erst die Taste F2 und anschließend die
Kombination STRG+Ctrl+Enter. Wie man
nun im Bild rechts oben sieht, erscheinen um die Formel geschweifte Klammern. |
|
Beispiel:
Nachstehend findet man ein Bild einer
Tabelle, in der Matrixoperationen ausgeführt worden sind.
EXCEL ist bekanntlich, wie bereits
erwähnt, nicht als Mathematikprogramm, sondern als Tabellenkalkulationsprogramm
für spezifische Berechnungs- und Darstellungsprobleme mit Tabellen konzipiert
worden. Daher erstaunt es nicht, dass es in EXCEL zum lösen von Gleichungen ein
Zusatztool braucht, den SOLVER. Bei
linearen Gleichungen kann man natürlich ein System mit Hilfe von Matrizen lösen. Doch sind eben nicht alle Gleichungen
linear. Eine weitere Möglichkeit ist die Verwendung eines Näherungsverfahrens,
das mit EXCEL zur Verfügung gestellt wird: Die Zielwertsuche. Man hat daher die drei folgenden Möglichkeiten zur
Auswahl, falls eine davon sinnvoll ist:
a.
Matrixmethode (wird in der Mathematik besprochen)
b.
Solver-Methode (siehe unten)
c.
Zielwertsuche (siehe unten)
Siehe Mathematikunterricht!
Diese wird in der EXCEL-Hilfe wie folgt beschrieben:
|
Wenn man in EXCEL auf „Extras è Zielwertsuche…“
klickt, so öffnet sich ein Fenster wie links im Bild gezeigt. Mit diesem Tool
kann so wie in der EXCEL-Hilfe beschrieben bearbeitet werden. |
Die Zielwertsuche ist in EXCEL-online
(http://www.online-excel.de/excel/singsel.php?f=1). Ein weiterer
Link (nur für internen Gebrauch, passwortgeschützt): http://rowicus.ch/Wir/Scripts/restricted/MasterIndex.html (unter EXCEL (6) suchen è Zielwertsuche). Man wird aber
bald feststellen, dass die Handhabung dieses Tools nicht sehr selbsterklärend ist.
Die Art der Programmierung dieses Tools kann daher von Fachleuten leicht als
Zumutung und als Ignoranz der mathematischen Kultur empfunden werden. Daher
hier zur Erklärung ein „Blödian-Beispiel“ ohne grosse mathematische
Voraussetzungen. Die Zielwertsuche kann zur
Lösung von Gleichungen mit nur einer Variablen verwendet werden.
Beispiel:
Wir wollen mittels
Zielwertsuche die einfache Gleichung „3*x = 1“ lösen. Da EXCEL ein
Numerikprogramm ist, erwarten wir als Lösung „ 0.333333…“. Wir werden
schliesslich gerundet „0.333333333“ erhalten. Wenn wir die Methode zur Lösung
eines derart einfachen Beispiels uns zu Eigen gemacht haben, wird es kein
Problem mehr sein, durch die Eingabe entsprechender Formeln kompliziertere
Gleichungen zu lösen.
Zuerst geben wir auf einem
neuen Arbeitsblatt in die Zelle A2 (Zielzelle) die Formel „=3*$A$1“ oder
„=3*A1“ ein. In Zelle A1 kann ein Wert stehen, der danach überschrieben wird.
A1 kann aber auch leer sein. Nun rufen wir unter „Extras è Zielwertsuche“ das Zielwertsuchprogramm auf (siehe
letztes Bild oben). Bei „Zielzelle“ im erschienenen Fenster tragen wir hier die
Zelle A2 (die Zelle mit der Formel) ein. Neben „Zielwert“ schreiben wir das
Ergebnis der Gleichung, nämlich 1, denn 3*A1 soll ja 1 ergeben. Unter
„Veränderbare Zelle“ schreiben wir A1. Denn aus dieser Zelle wird der Wert
bezogen, auf die sich die Formel bezieht und der bei einer Iteration verändert
werden soll. Wir brauchen demnach nur 2 Zellen, denn der Zielwert muss eine
Zahl, also kein Bezug sein. Wenn wir „OK“ drücken, so erscheint in Zelle A1 das
Resultat „0.333333333“ wie erwartet.
|
|
Ersetzen wir dann die Formel durch
„=SIN(A1)*EXP(A1)“, so wird jetzt mit denselben Eingaben wie oben in das
Zielwertsuchfenster die veränderte Gleichung 1= sin(x)*ex
gelöst.
Als Resultat erhalten wir „0.588512177452438“ in A1, stellen aber fest, dass in
A2 jetzt nicht mehr der Zielwert „1“, sondern der Näherungswert „0.99994862“
erscheint. Das Programm kann nur eine Näherungslösung finden.
Wir
verzichten hier auf Bildmaterial, da man das Vorgehen leicht nachvollziehen
kann und auch ausprobieren sollte.
Falls der Solver installiert
ist (oft ist das nicht der Fall),
findet man ihn in der Symbolleiste unter „Extras è Solver“. Der Solver gehört leider bei manchen
Installationen zu den Wünschen und nicht zu den Aktiven. Falls er daher nicht
installiert ist, so gehen wir auf „Extras è Add-Ins“, worauf eine selbsterklärende Dialog-Box
erscheint, mit der wir den Solver installieren können. Eventuell besteht auch
eine Installationsmöglichkeit mit „Extras è Tools im Web“ (oder Web-Suche, z.B. mit Google). Wer
Hilfe für den Solver möchte, findet diese unter folgenden Links:
- http://rowicus.ch/Wir/Scripts/restricted/MasterIndex.html (unter EXCEL (6) suchen è Solver // nur intern zugänglich,
passwortgeschützt).
- http://www.opti.ch/kopfnuss.htm
Beispiel 1:
Wir wollen mittels dem Solver
die oben gehabte einfache Gleichung „3*x = 1“ lösen. Da EXCEL ein
Numerikprogramm ist, erwarten wir als Lösung wieder „ 0.333333…“. Wir gehen in
das vorher gebrauchtes Arbeitsblatt und geben in die Zelle A2 (Zielzelle)
wieder die Formel „=3*$A$1“ oder „=3*A1“ ein. In Zelle A1 kann wieder ein Wert
stehen, der danach überschrieben wird. A1 kann aber auch leer sein. Nun rufen
wir unter „Extras è Solver“ das
Solver-Programm auf. Dann geben wir unter Zielzelle die Adresse $A$2 ein. Wir
können auch mit der Maus auf die Zelle A2 klicken. Dann erscheint die Adresse im
Solver-Fenster. Dann klicken wir auf der Zielwert-Zeile „Wert“ an und geben
daneben „1“ ein. Als veränderbare Zelle definieren wir $A$1 (Eingabe der
Adresse ins Fenster oder Cursor im Eingabebereich platzieren und auf die Zelle
A1 klicken). Mit OK erhalten wir „etwa“ (ev. mit numerischer kleiner
Abweichung) die gleiche Lösung wie bei der Zielwertsuche. (Wir verzichten hier
wiederum auf Bildmaterial, da man das Vorgehen leicht nachvollziehen kann und
auch ausprobieren sollte.)
Mit dem Slover kann man kompliziertere
Gleichungsgebilde als mit der Zielwertsuche lösen. Z.B. eine Gleichung mit
Nebenbedingungen. Dazu wieder ein Beispiel:
Beispiel 2:
A1 und A2 lassen wir wie vorhin.
In A3 geben wir aber die Formel „=-2*A2+1“ ein und rufen das Solver-Fenster
auf. Dort nennen wir als Zielzelle wider $A$2. Bei „Zielwert“ geben wir jedoch
hier „Max“ und 10 an und bei „Veränderbare Zelle“ wieder $A$1. Das heisst, das
wir jetzt die Ungleichung „3*A1 <= 10“ betrachten. Dann klicken wir
auf den Balken „Hinzufügen“ bei „Nebenbedingungen“. Dort geben wir bei
„Zellbezug“ die Adresse $A$3 ein. Daneben wählen wir „>=“ aus. Als Wert bei
„Nebenbedingung:“ wählen wir 3. Damit haben wir die Ungleichung „-2*A1+1 >=
3“ und damit das Ungleichungssystem „3*A1 <= 10; -2*A1+1 >= 3 “ für den unbekannten Wert in
A1 eingegeben. Als Lösung erscheint dann in A1 der Wert -1.
(Wir verzichten hier wiederum
auf Bildmaterial, da man das Vorgehen leicht nachvollziehen kann und auch
ausprobieren sollte.)
Beispiel 3:
Hier noch ein Vergleich Solver- Zielwertsuche, wobei zuerst
die quadratische Gleichung „3*x-2*(x-2)*x+1=0“ wie beschrieben mittels Zielwertsuche behandelt worden ist. Die
gefundene Lösung ist „x=-0.137461445“.
Anschliessend wollen das
Gleichungssystem „3*x-2*(y-2)*x+1=0; x=y“ mit der Solver-Methode lösen und dabei einiges mehr über diese Methode
erfahren. Dieses Gleichungssystem ist äquivalent zur vorher bei der
Zielwertsuche betrachteten Gleichung. Wir zeigen hier, dass wir auch mit einem
System statt mit nur einer Gleichung arbeiten können. Dazu schreiben wir
versuchsweise in Zelle B11 den Startwert 1, in Zelle B12 den Startwert 2, in
Zelle B13 die Formel „=B11-B12“ und in Zelle B14 die Formel
„=3*B11-2*(B12-2)*B11+1“. Im Solver wird dann in der Zielzelle „$B$14“ und als
Zielwert „Wert“ sowie „0“ eingegeben. Die veränderbaren Zellen sind
„$B$11:$B$12“. Man kann hier also Zellbereiche
eingeben. Die Nebenbedingung ist
„$B$13=0“ (erst $B$13, dann = und dann 0 eingeben, von links nach rechts). Als
Lösung kommt bei beiden Methoden „-0.137461445“ für x und hier dann auch für y
heraus. Diese Situation ist in den nachstehenden Abbildungen gezeigt.
|
|
Geben wir aber als
Nebenbedingung „2*y=x“ ein, d.h. übersetzt „=2*B11-B12“ mit Zielwert 0, so
erhalten wir für x den Wert „-0.140054903“ und für y dazu den Wert „-0.070027451“. Steht dann aber in Zelle
B13 die Formel „=B11-2*B12“ mit Nebenbedingung „=0“, also als 2. Gleichung
„2*x=y“, so ergibt sich für x und y das Wertepaar 1.882782199 und 3.765564398
u.s.w. Wir sehen damit, dass wir ein System verändern und so schliesslich auch
Lösungen von komplizierten Systemen berechnen können. Angaben über die
Lösungsbedingungen erhalten wir aber vom System kaum. Unten sehen wir einen
Ausschnitt aus einer Situation im Arbeitsblatt „während dem Ausprobieren“.
Trage
in eine EXCEL-Tabelle zwei frei gewählte quadratische Matrizen ein und
multipliziere und invertiere sie. Versuche mit Hilfe der Help-Funktion
herauszufinden, welche anderen Matrixoperationen es noch gibt und wie man sie
anwendet.
Beispiel:
|
|
Bei der Besprechung der absoluten Bezüge haben wir das Beispiel einer
Notenliste betrachtet. Wir möchten nun diese Liste noch etwas besser leserlich
gestalten. Da wir uns speziell für die ungenügenden Noten interessieren, soll die
Tabelle so eingerichtet werden, dass diese automatisch rot hinterlegt
erscheinen, sobald der betroffene Wert unter 4 liegt. Zuerst tragen wir in C3
noch die kleinste genügende Note ein.
Diese ist 4. Dann können wir später bei Bedarf diesen Wert der einzigen
Stelle C4 anpassen. Darauf wird die Anpassung überall wirksam. Nun klicken wir
bei aktivem Bereich C6:C17 auf Format è Bedingte
Formatierung…, worauf sich das Fenster „Bedingte Formatierung“ öffnet. Dort
wählen wir folgende Einstellungen: „Zellwert ist“ sowie „kleiner als“ und
tragen den Bezug „=$C$3“ ein, womit wir die Grenznote 4 zentral erfassen.
Anschliessend klicken wir auf „Format…“ und dann auf „Muster“. Hier können wir
„rot“ auswählen und die Fenster dann mit OK und wieder OK verlassen.
Zusätzlich formatieren wir die Notenwerte dann auch noch so, dass sie
fett erscheinen.
Darauf erhalten wir das
nebenstehende Bild. Wenn wir nun hier neue Studenten mittels „rechte
Maustaste è Zellen einfügen“ oder kopieren und Wertanpassung (letzte Zeile)
eintragen, so passt sich die Tabelle automatisch richtig an. Diese Formatierungsweise
nennen wir „bedingte Formatierung“, weil die Formatierung (hier rote
Hinterlegung) von der Erfüllung einer Bedingung abhängig ist. |
|
Beispiel:
Falls die Notenliste lang werden sollte, so möchten wir jede Druck-Seite
mit derselben Überschrift versehen. Jede Seite soll auch das Datum, die
Erstellungszeit und die Seitennummer aufweisen.
Dazu klicken
wir in der Symbolleiste auf das hier gezeigte Symbol („Seitenansicht“): |
Nachdem jetzt das Fenster gewechselt hat,
sehen wir das folgende Bild, wo wir auf „Layout“ klicken. |
Darauf öffnet sich wieder ein Fenster. Hier stellen wir zuerst das
Papierformat „Hochformat“ ein. Dann aktivieren
wir noch die Seitenränder „Horizontal“ und „Vertikal“, womit die Tabelle auf
der Druckseite zentriert wird.
Darauf klicken wir auf den Reiter des Registers „Kopfzeile/Fusszeile“,
worauf das Fenster wie folgt aussieht:
Hier klicken wir auf den Balken „Benutzerdefinierte Kopfzeile…“ und
füllen das nun sich öffnende Fenster wie folgt aus:
Mit dem Knopf „ A “ können
wir den eingegebenen Text „Notentabelle Professor Ordnungslos“ wie üblich
formatieren. Anschliessend sehen wir die Knöpfe für die Seitennummern und die
Zeitengesamtzahl. Anschliessend den Knopf für das Datum und denjenigen für die
Uhrzeit u.s.w. (Am besten probiert man die Knöpfe einmal aus. Dann kennt man
ihre Funktion.) Mit OK è OK verlassen wir
diese Fenster wieder und kehren zur Tabelle zurück. Wir stellen fest, dass der
gewollte Titel jetzt vorhanden ist – leider aber nur auf der ersten Druckseite.
Um den Titel
automatisch auf allen Druckseiten zu haben, aktivieren wir die Titelzeile und
öffnen das Fenster „Seite einrichten“ unter „Datei è Seite einrichten…“. Dort klicken wir auf den
Reiter des Registers „Tabelle“. Wir platzieren den Cursor im Schreibfeld
neben „Wiederholungszeilen oben:“ Wenn wir nun in irgend eine Zelle
der Titelzeile 1 klicken, erscheint im genannten Schreibfeld der Eintrag
„$1:$1“. |
|
Mit dem Icon neben dem Schreibfeld können wir übrigens das Fenster
zwecks Sicht auf die Tabelle reduzieren und dann wieder aufklappen. Mit OK aktivieren
wir die Sache und verlassen das Fenster.
Zur Kontrolle kehren wir aber nochmals zurück und klicken auf
„Seitenansicht“. Nun sehen wir die Druckvorschau. Mit Hilfe des Icons „Weiter“
links oben im nun sichtbaren Fenster können wir die Seiten durchblättern und
den Titel kontrollieren.
Bemerkung: Z.B. unter „Drucken…
è Eigenschaften“
lassen sich „Hochformat“ oder „Querformat“ sowie weitere Grössen
einstellen. Will man mehrere Blätter
(Druckbereich) miteinander ausdrucken, so lassen sich diese erst mit
„Caps+linke Maustaste“ aktivieren. Vor dem Ausdruck empfiehlt es sich, die Druck-Vorschau anzusehen.
Beispiel:
(a) Sortieren
Wir wollen unsere Notentabelle nach aufsteigenden Noten in 1. Priorität
und nach Punkten in 2. Priorität neu sortieren und anschliessend diejenigen
Zeilen verbergen, welche genügende Noten enthalten, um die Probleme anlässlich
einer Konferenz vorzeigen zu können, ohne dass dabei die Zuschauer durch allzu
viele Daten irritiert werden.
Wir markieren die Zeilen, welche Noten enthalten, durch ziehen mit dem
Mauszeiger über die Zeilennummern. Dann klicken wir in der Symbolleiste auf
„Daten è Sortieren…“. Dann
öffnet sich ein Fenster wie im linken nachfolgenden Bild gezeigt. Die Noten
stehen in Spalte C und die Punkte in Spalte B. Also tragen wir unseren
Prioritäten entsprechend Spalte C und Spalte B wie gezeigt ins Fenster ein. Die
Sortierung bezieht sich nun auf die markierten Zeilen. Nach dem Sortieren
präsentiert sich die Situation so wie im rechen Bild (immer noch im markierten
Modus).
|
|
Beispiel:
(b) Ausblenden der Zeilen mit genügenden Noten
Jetzt möchten wir die Zeile ausblenden, welche genügende Noten
enthalten. Dazu markieren wir wiederum diese Zeilen und klicken dann auf „Daten
è Gruppierung und
Gliederung è Gruppierung“.
Dann präsentiert sich die Tabelle so wie im linken nachstehenden Bild
gezeigt wird.
|
Wenn wir mit der Maus auf das Minuszeichen am
linken Rand unterhalb der Gruppierungsklammer klicken, so klappen die Zeilen
zu, welche nun umklammert sind. Durch klicken auf das nun erscheinende
Pluszeichen öffnen sich die Zeilen wieder. |
Beispiel:
(c) Automatische Gliederung
Nun wollen wir ausprobieren, wenn wir die Gliederung dem System
überlassen. Dafür existiert die Möglichkeit der automatischen Gliederung.
Zuerst markieren wir alle Zeilen, welche Daten enthalten und klicken dann auf
„Daten è Gruppierung und
Gliederung è Gruppierung è AutoGliederung“.
Es entsteht dann das nebenstehend gezeigte
Bild. Klicken wir auf das Minuszeichen, so klappen die Zeilen zu, welche nun umklammert
sind. Durch klicken auf das nun erscheinende Pluszeichen öffnen sich die
Zeilen wieder. Dasselbe geschieht, wenn wir auf die oben links erscheinenden
Zahlen 1 und 2 klicken. Der Übersichtlichkeit
halber werden hier keine komplizieren Tabellen gezeigt. Es wird aber auch so
klar, dass die AutoGliederung die Struktur einer Tabelle erfassen kann. Wie
man die Bereiche vorher markieren darf und was dabei herauskommt testet man
am besten an einer eigenen oder an einer „importierten“, komplizierten Tabelle
aus. |
|
Bearbeite das Arbeitsblatt 2 sowie Arbeitsblatt 3, abrufbar unter http://rowicus.ch/Wir/MathcadExcelAndereMath/FileList2.html .
Es ist sinnvoll und bequem, sich Information in Internet-Foren zu EXCEL
zu besorgen. Das hat anderen schon oft in verzweifelten Situationen weiter
geholfen.
Beispiel:
In unserer Notentabelle sollen
die Noten durch eine Drittperson eingegeben werden. Es steht eine Prüfung mit
sehr vielen Teilnehmern bevor. Man will absolut vermeiden, dass unsinnige und
lächerlich machende Punktzahlen ins System eingegeben werden und dann
eventuell an die Öffentlichkeit gelangen. Wir aktivieren den Bereich
der schon vorhandenen Punkzahlen. Das Maximum setzen wir fix auf 60. Damit
kann es nicht aus falschen Punkten unsinnig berechnet werden. Nun klicken wir
auf „Daten è Gültigkeit“. Dann öffnet sich ein Fenster, in dem
wir die unten gezeigten Einträge in die Registerkarten vornehmen. |
|
|
|
Mit OK schliessen wir die
Eingabe ab. Wenn wir jetzt z.B. beim Studenten „neu1“ 62 Punkte eingeben, erhalten
wir die nebenstehend gezeigte Meldung. Mit Hilfe dieses Prinzips lassen sich
unzulässige oder ungültige Eingaben absichern. |
|
Ein Beispiel:
Wir nehmen uns vor, mit Excel eine Sinuslinie zu zeichnen. EXCEL ist
natürlich nicht ein Programm, das zu solchen Zwecken geschrieben worden ist.
Dafür existieren hochwertige Mathematik-Programme. Wir versuchen es aber
trotzdem mit EXCEL, um dabei etwas lernen zu können.
EXCEL erstellt Diagramme, welche in Tabellen vorhandene Daten
darstellen. Daher müssen wir zuerst Daten erzeugen, die EXCEL als Diagramm
einer Sinuslinie darstellen kann. Wir wollen den Sinus zwischen 0 und 2*Pi
(also 360 Grad) durch das Programm zeichnen lassen. Dabei gehen wir wie folgt
vor:
Das
Beispiel mit dem Sinus:
Nun soll aus den obigen Daten ein Diagramm erstellt werden. Die Werte
auf der x-Achse stammen dabei aus Kolonne B, diejenigen auf der y-Achse aus
Kolonne C. Daher aktivieren wir diese beiden Kolonnen. Anschliessend klicken
wir auf den Diagrammassistenten
(Icon in der Symbolleiste). EXCEL verfügt über eine
sehr grosse Menge von voreingestellten
Diagrammtypen. Wen man mit diesem Programm wenig Übung hat, kann die
optimale Auswahl nun schwer fallen.
Im nachfolgenden Bild sehen wir, dass der
Diagrammtyp „Punkt (XY)“ und davon der Untertyp rechts in der Mitte (mit
schwarzem Hintergrund) ausgewählt wird. Wir klicken nun im gezeigten Fenster
unten auf „Weiter >“.
Jetzt öffnet sich ein neues Fenster im Diagrammassistenten. Wir sehen
folgende beiden Registerkarten, die wir nicht verändern.
Wir klicken auf „Weiter“ und entfernen im neuen Fenster den Haken bei
„Legende anzeigen“.
Das Objekt soll im selben Blatt
wie die Daten platziert werden (Tabelle3). Nun klicken wir auf „Fertig
stellen“. Das Resultat sehen wir unten. Wenn wir nun in das
fertige Diagramm klicken, |
|
machen wir die Erfahrung,
dass wir da einiges verschieben und verändern können!
Das
Beispiel mit der Grösse von Zahlenwerten als Balken:
Mit Hilfe der Formel „=OBERGRENZE(20*ZUFALLSZAHL()+7;1)“ erzeugen wir in
der Kolonne A (A1:A29) eine Menge von Zufallszahlen zwischen 7 und 27. Die Grösse dieser Zahlen tragen wir gegen
ihre Entstehungsnummer in einem Diagramm auf. Wir wollen hier wieder die
Entstehungsschritte einzeln besprechen. (Das ganze hier ist ein Lehrbeispiel
für Diagramme, ohne weiteren Sinn.)
Zuerst aktivieren wir wieder den Bereich der entstandenen Zufallszahlen
in Kolonne A und rufen danach den Diagrammassistenten
(Icon in der Symbolleiste) auf.
Wir wählen das Säulendiagramm ganz unten links.
Bei den Diagrammoptionen tragen wir den Titel sowie die Beschriftung der
x- und der z-Achse ein.
Nach der Fertigstellung sieht die Sache wie folgt aus:
Die Auswahl des Diagrammtypen erfolgt nach praktisch-psychologischen
Gesichtspunkten. Wen will man was wie u.s.w. überzeugen? Was kann mit welchem
Diagrammtypen wie verstärkt herausgehoben werden? …
Ein
Beispiel zum Notenvergleich zweier Klassen:
Nachstehend ist das Diagramm mit den vorhandenen Daten gezeigt.
Unten sieht man Angaben zum Diagrammtypen und zur Skalierung der Rubrikachse (x-Achse).
Um die Rubrikachse (x-Achse) zu skalieren klicken wir in das
entsprechende Feld bei „Beschriftung der Rubrikachse (X)“ und markieren dann in
den Rohdaten mit der Maus die Rubrikdaten (aus Kolonne A, A33:A43). Betrachte
dazu das nächste Bild.
Bemerkung: Je nach Installation ist es
auch möglich, in EXCEL vom Programm bereitgestellte Landkarten aufzurufen und zu benutzen (abzuändern und in eine
Tabelle einzubinden). Dafür existiert dann gegebenenfalls ein Symbol (z.B.
Globus) in der Symbolleiste.
Bearbeite das Arbeitsblatt 4 und das Arbeitsblatt
5, abrufbar unter http://rowicus.ch/Wir/MathcadExcelAndereMath/FileList2.html
.
10.5. Lehren aus dem Arbeitsblatt 4 EXCEL
1. Nochmals: Verwendung des Online-EXCEL-Forums, siehe
unter http://www.online-excel.de/ .
2. Weitere Foren: Siehe http://rowicus.ch/Wir/Links/Linkpage3.html#EXCEL
.
3. Diagramme mit Daten auf der x-Achse und auf der
y-Achse: Daten gesamthaft als Bereich markieren, dann im Diagramm-Assistent
unter Icon „Punkt (XY)“ z.B. das 2. Icon wählen.
Beispiel:
Hier ist eine EXCEL-Tabelle
präsentiert, in welcher eine große Menge Daten eingetragen ist. Es handelt sich
um eine Partnerkartei einer Firma.
Die Einträge geben Auskunft über gewisse für die eigene Firma wichtige Angaben
über Partnerfirmen. Für uns ist der Inhalt unwesentlich, da wir uns hier nur
für die Datenbankprobleme interessieren.. (Wir können ihn als frei erfunden
betrachten.)
Dieser Datensatz ist recht umfangreich und daher unübersichtlich oder komplex.
Wir brauchen daher Methoden um aus ihm Informationen herauslesen zu können. Wir
müssen die Daten nach strukturellen, mathematischen, mengentheoretischen und
graphentheoretischen, relationalen Gesichtspunkten strukturieren. Das klingt
alles sehr fachchinesisch. Das soll aber klar machen, dass hinter
Datenbankanwendungen sehr viel Mathematik stecken kann.
Für die praktische Verwendbarkeit sind uns nun folgende Fragen wichtig:
Wir werden sehen, dass in den Beziehungen von Daten hierarchische Abhängigkeiten,
Zugriffspfade und Mengenverknüpfungen (resp. einfache formale Aussagenlogik)
eine zentrale Rolle spielen.
Dabei stellt sich natürlich auch die Frage, ob EXCEL das richtige
Instrument ist, um solche Dinge zu bewerkstelligen. Sicher ist EXCEL nicht zu
diesem Zweck gebaut worden. Für den einfachen Gebrauch im kleineren Rahmen kann
es aber immer wertvolle Dienste leisten. Man kann ja immer feststellen, dass
ein Dreirad-Kindervelo nicht das richtige Tool ist um daran einen Pflug zu
hängen und damit einen Acker zu pflügen. Doch mindestens kann man daran lernen,
was beim Pflügen wichtig ist und wie es funktioniert. Das ist auch mit EXCEL
so.
Beispiel:
Das Sortieren ist bereits im
Kapitel über „Aspekte professioneller Formatierung“ besprochen worden. EXCEL
bietet uns in „Daten è Sortieren…“ die
Möglichkeit an, nach drei verschiedenen, hierarchisch geordneten Kriterien zu
sortieren. Hier noch ein Bild dazu:
Hier sind die Daten nach den
Firmennamen sortiert. Wir sortieren sie
nun für unsere Betrachtung um nach Handwerker und Bonität.
Darauf rufen wir unter „Daten è Maske“ das unten
sichtbare Fenster (Maske) auf:
Man sieht sofort, dass man mit dieser Maske die Datensätze einzeln
ansprechen kann. Ingold mit der Nummer 27 steht aber nicht in Zeile 27 sondern
in Zeile 30. Das kommt daher, weil in den ersten drei Zeilen keine Daten
stehen, wie man im Bild weiter oben sieht. Mit der Maske kann man gezielt
Datensätze durchsuchen, löschen, neue
eingeben oder auch Suchkriterien eingeben. Wählen wir z. B. „Kriterien“ an und tragen wir unter „Büro“
dann ZH ein, so werden beim Durchblättern nur diejenigen Datensätze angezeigt,
die die Bedingung „Büro è ZH“ erfüllen. Man
kann in der Maske auch Daten ändern. Beim Schliessen werden die Änderungen dann
gespeichert.
Beispiel:
Wir wollen hier als Beispiel
den „Autofilter“ besprechen. Zuerst aktivieren wir
unsere Daten (A4:M66). Dann klicken wir auf „Daten è Filter è AutoFilter“ (siehe Bild rechts). Danach
sehen wir, dass rechts neben den Spaltentiteln quadratische Knöpfe mit nach unten
zeigenden Pfeilen aufgetaucht sind (Bild unten). |
|
Wenn wir z.B. auf den
Pfeil rechts neben „Handwerker“ klicken, so öffnet sich ein Fenster, in dem
wir nach den drei voreingestellten Optionen die vorkommende Wertemenge der Handwerker-Kolonne
erblicken. Wenn wir z.B. auf 28 klicken, so wird gefiltert: Alle
Datenzeilen, in denen bei „Handwerker“ nicht 28 steht, werden jetzt
ausgeblendet. Auf diese Weise kann man beliebig filtern. Ein Klick auf „Alle“
bringt wieder alle Daten zum Vorschein. Ein Klick auf „Top 10“ bewirkt, dass
nur diejenigen Zeilen sichtbar bleiben, in denen in aufsteigender Reihenfolge
die 10 grössten Werte stehen. Wenn wir hingegen auf „Benutzerdefiniert…“
klicken, so öffnet sich das unten gezeigte Fenster mit der Aufschrift
„Benutzerdefinierte AutoFilter“, in das wir Eintragungen machen können. Die
Filterung bezieht sich natürlich auf die Kolonne „Handwerker“. |
|
Die Eintragungen sind selbsterklärend.
Es werden am Bildschirm mit der nebenstehenden Definition nur Zeilen
ausgegeben, in denen der Wert in der Kolonne „Handwerker“ grösser als 22 oder
kleiner als 14 ist. Das heisst, wir sehen dann hier die extremen Werte. Im
unten gezeigten Bild sehen wir einen Ausschnitt des Ergebnisses. |
|
Bearbeite das Arbeitsblatt 6, abrufbar unter http://rowicus.ch/Wir/MathcadExcelAndereMath/FileList2.html .
Um die Arbeitstechnik mit verschiedenen
Arbeitsmappen demonstrieren zu können, öffnen wir unsere bisherige Arbeitsmappe
und darauf noch eine zweite durch klicken auf das rot eingekreiste Symbol, wie
im Bild unten links gezeigt.
|
Nachher klicken wir auf „Fenster è Anordnen… è Unterteilt“. Und schon sehen wir die beiden
Arbeitsmappen nebeneinander geöffnet im EXCEL-Fenster. Mit der Maus können wir
bei Bedarf in jene Arbeitsmappe klicken und diese aktivieren, in der wir
arbeiten wollen. |
Es ist dem Leser überlassen, die diversen
Einstellungsmöglichkeiten der Fenster bei mehreren offenen Arbeitsmappen
auszuprobieren!
Es gibt diverse Gründe um Spalten, Zeilen
oder Zellen in einem Arbeitsblatt zu verstecken. Z.B. Layout-Kosmetik: Man
möchte Hilfszellen einem Betrachter von Ergebnissen nicht zeigen, weil darin
nur Hilfsrechnungen ausgeführt worden sind.
Um z.B. die Zeilen 2 und 3 auszublenden,
aktivieren wir diese Zeilen und klicken dann auf „Format è Zeile è Ausblenden “. Dann verschwindet die Zeile,
die Nummerierung jedoch bleibt bestehen. Mit „Format è Zeile è Einblenden “ können wir später die
verschwundenen Zeilen wieder sichtbar machen. Entsprechend operieren wir mit
den Spalten.
Um einzelne Zellen zu verbergen, muss man
anders vorgehen. Es soll z.B. D7:E7 verborgen werden. Dazu aktivieren wir durch
klicken und ziehen mit der Maus diese Zellen. Dann klicken wir mit der rechten
Maustaste in den aktivierten Bereich, worauf sich ein Fenster öffnet. In diesem
Fenster klicken wir auf „Zellen formatieren… è Zahlen è Benutzerdefiniert“. Dann doppelklicken wir in den
Schreibbereich „Standard“ (eine Auswahl aus der darunter erscheinenden
Zeichenliste ist vermutlich noch nicht möglich). Dann geben wir im angeklickten
Bereich „;;;“ ein (drei mal Strichpunkt). Darauf klicken wir auf „OK“. Wir
sehen jetzt, dass die Zellinhalte verschwunden sind. Wollen wir sie wider
sichtbar machen, so geht das umgekehrt jetzt mit „Zellen formatieren… è Zahlen è Benutzerdefiniert è Doppelklick in den Bereich mit ;;; è Standard aus der Liste auswählen è OK“.
Wir
verzichten hier auf Bildmaterial, da man das Vorgehen leicht nachvollziehen
kann und auch ausprobieren sollte.
Wir wählen ein Tabellenblatt aus, das wir
schützen möchten. (Für das momentane Beispiel soll nur ein Blatt ausgewählt
sein.) Dann klicken wir auf „Extras è Schutz è Blatt schützen…“. Im nun erscheinenden Fenster
lassen wir die voreingestellten Häkchen stehen. Auf die Eingabe eines
Passwortschutzes verzichten wir hier vorerst. Wir Drücken einfach auf „OK“ und
stellen anschließend fest, dass keine Eingabe in die Tabelle mehr möglich ist.
Den Schutz können wir wieder aufheben mit „Extras è Schutz è Blattschutz aufheben…“.
Um einzelne
Zellen alleine nicht zu schützen, d.h. veränderbar zu erhalten, müssen wir
zuerst einen eventuellen Tabellenschutz wieder aufheben. Wir wollen z.B.
D10:E11 als einzige Zellen nicht schützen, denn hier soll ein Kunde Eingaben
machen können. Wir aktivieren nun diese Zellen D10:E11 wie gewohnt und klicken
dann mit der rechten Maustaste in den aktivierten Bereich. Im darauf sich
öffnenden Fenster wählen wir „Zellen formatieren.. è Schutz“ und entfernen im sich öffnenden Fenster das
Häkchen bei „Gesperrt“. Danach drücken wir OK und schützen darauf die gesamte
Tabelle wie oben erläutert. Durch ausprobieren überzeugen wir uns jetzt davon,
dass wirklich nur im Bereich D10:E11 noch Eingaben möglich sind. Um die Sache
wieder rückgängig zu machen entfernen wir zuerst den Blattschutz wieder und
bringen darauf das Häkchen für den Zellenschutz wieder an.
Wir
verzichten hier wiederum auf Bildmaterial, da man das Vorgehen leicht
nachvollziehen kann und auch ausprobieren sollte.
Wir wollen in die ungeschützte Zelle einen
Kommentar einfügen, der nur beim Anklicken der Zelle sichtbar wird. Dazu
aktivieren wir die Zelle und klicken sie dann mit der rechten Maustaste an. Im
sich öffnenden Fenster klicken wir auf „Kommentar einfügen“, worauf sich ein
Schreibbereich öffnet, in welchen wir schreiben können und den wir auch durch
anfassen mit der Maus in seiner Größe verändern können. Ebenso lässt sich der
Kommentar verschieben. Wenn wir neben den Schreibbereich in eine Zelle klicken,
so schießt sich das Eingabefenster. Der Kommentar ist nun eingegeben. Dass zur
bearbeiteten Zelle ein Kommentar gehört, erkennen wir am kleinen roten Dreieck
oben rechts an der Zelle. Sobald wir mit der Maus den Pfeil (Cursor) über die
Zelle lenken, wird der Kommentar sichtbar, bis wir die Maus danach weiter bewegen.
Wenn wir jetzt wieder mit der rechten Maustaste in die Zelle klicken, so werden
im sich öffnenden Fenster die Optionen „Kommentar bearbeiten“, „Kommentar
löschen“, „Kommentar anzeigen“ resp. „Kommentar ausblenden“ sichtbar. Damit
haben wir wiederum neue Auswahlmöglichkeiten.
Wir
verzichten hier wiederum auf Bildmaterial, da man das Vorgehen leicht
nachvollziehen kann und auch ausprobieren sollte.
Wenn wir mit der rechten Maustaste auf eine
leere Zelle klicken und dann im sich öffnenden Fenster die Option „Hyperlink…“
wählen, so können wir im sich jetzt öffnenden Fenster unten eine
Internetadresse eingeben. Wir geben zu Probierzwecken http://www.rowicus.ch/Wir/indexTotalF.html
ein. Wenn wir nach dem Schließen des Fensters mit „OK“ auf diesen Hyperlink
klicken, so öffnet sich der Internetbrowser und zeigt die damit angewählte
Internetseite an. Diese Möglichkeit der direkten Wahl einer Internetseite kann
manchmal sinnvoll sein, z.B. um sich gezielt Information zu beschaffen.
Ebenso interessant ist das gezielte
anspringen mit einer Zelle aus einer weit entfernten anderen Zelle heraus. Wir
müssen z.B. von der Zelle J91 aus immer wieder zur Zelle D35 springen. Dazu
klicken wir mit der rechten Maustaste auf die Zelle J91 und dann im sich
öffnenden Fenster auf „Hyperlink…“. Im nun sich zeigenden Fenster „Hyperlink
einfügen“ klicken wir auf „Textmarke…“, worauf sich nochmals ein Fenster
öffnet. Darin geben wir „$D$35“ und „OK“ ein. Danach schließen wir auch das
Fenster „Hyperlink einfügen“, in dem nun unten als Sprungadresse z.B.
„#Tabelle1!D35“ sichtbar geworden ist. (Wir befinden uns in Tabelle 1.) Klicken
wir mit der linken Maustaste jetzt in J91, so erscheint sogleich links oben die
aktive Zelle D35.
Wir
verzichten hier wiederum auf Bildmaterial, da man das Vorgehen leicht
nachvollziehen kann und auch ausprobieren sollte.
Zu Daten konsolidieren mit
EXCEL: Siehe auch http://www.online-excel.de/excel/singsel.php?f=100
Um das Konsolidieren von Daten etwas zu verstehen, verwenden wir die
nachfolgende Bonitätstabelle als Datenquelle. Es handelt sich hier um Daten,
die bei der statistischen Erfassung von getesteten Bearbeitungseinheiten
angefallen sind. Die Detailbedeutungen der Messungen fallen unter das
Fabrikationsgeheimnis. Die Daten befinden sich im Tabellenblatt „BasisKonsolidieren“. (Umbenannt mit
„rechte Maustaste è Umbenennen“
u.s.w.)
|
Nun
wechseln wir ins leere Tabellenblatt „Konsolidieren“ und klicken in die Zelle
A3.Unter „Daten è
Konsolidieren…“ können wir nun, wie in den nachstehenden Bildern gezeigt, ein
Fenster öffnen und darin die Verweise auf benannte Zellbereiche eintragen.
Erst tragen wir den Bereich ‚BasisKonsolidieren’!$C$4:C$26 aus dem
Arbeitsblatt „BasisKonsolidieren“ mit Hilfe von „Hinzufügen“ ein. Wie man
sieht, wird hier auf Daten aus einem anderen Arbeitsblatt verwiesen. (Daten
aus einer anderen Mappe |
wären
wie bei den Verweisen in Formeln auch möglich.) Wir sehen, dass der Datensatz „BasisKonsolidieren!$C$4:C$26“ aus der
genannten Tabelle übertragen worden ist. Eigentlich handelt es sich hier um
eine Aufsummierung mit nur einem Summanden, denn im Fenster „Konsolidieren“ ist
unter „Funktion“ die Option „Summe“ ausgewählt.
|
|
Danach
klicken wir in die Zelle B3 und tragen
noch T1_ ein im Fenster zur Konsolidierung ein. Dann klicken wir auf „OK“. (Der
Name „T1_“ kommt von der Benennung eines Spaltenbereichs nach dem Namen oben an
der Datenspalten mittels aktivieren des entsprechenden Zellbereichs und dann
Anwendung von „Einfügen è Namen è Erstellen…“). Wir sehen, dass der Datensatz
„T1_“ identisch ist mit „BasisKonsolidieren!$C$4:C$26“. Als Eintrag finden wir
jetzt von B3 aus nach unten die Summe von „T1_“ und „BasisKonsolidieren!$C$4:C$26“. Das ist
natürlich 2-mal T1_. (Der Name „T1_“ genügt, da in der Arbeitsmappe unter den
Tabellenblättern diese Bezeichnung eindeutig ist.)
Anschließend klicken wir in Zelle C3 und
fügen noch T2_ beim Konsolidieren hinzu. (T2_ wiederum benannt nach dem Namen
oben an der Datenspalten, Benennung wie eben erwähnt, mit Unterstrich, da ein
Bereichsname kein Variablenname für den Bezug aus einer Zelle sein kann.) Wir
sehen, dass das Resultat jetzt die Summe (BasisKonsolidieren!$C$4:C$26) + T1_ +
T2_ ist. Darauf fügen wir mit
vorgängigem Klick in D3 noch „ ‚BasisKonsolidieren’!MP “ an. In der Kolonne D
sehen wir dann die Summe der Bereichen aus den vier erwähnten Kolonnen A, B, C
und D, aus denen die Bezüge stammen. (Siehe nachstehendes Bild.)
Zu Pivot-Tabellen in EXCEL siehe
auch
o
http://de.wikipedia.org/wiki/Pivot-Tabelle
oder
o
http://www.online-excel.de/excel/singsel.php?f=55
oder
http://www.isd-hamburg.de/nrw/Datenauswertung%20mit%20Pivot-Tabellen.pdf )
Zur Erklärung der Pivot-Tabellen verwenden wir das nachstehende
Datenmaterial, das schon bei der Konsolidierung Verwendung gefunden hat.
Wir klicken im geöffneten
Tabellenblatt in eine beliebige Zelle und rufen dann „Daten è Pivot-Tabelle- und BivotChart-Bericht…“
auf. Im erscheinenden Fenster markieren
wir, sofern eine andere Einstellung vorhanden ist, die Optionen „Microsoft-Excel-Liste
oder - Datenbank“ sowie „Pivot-Tabelle“.
|
Anschließend
klicken wir auf „Weiter“. |
Im nun erscheinenden Fenster tragen wir unseren Analyse-Bereich
$A$*:$O$32 ein, wofern das nicht automatisch geschieht. Dann klicken wir auf
wieder auf „Weiter“.
Wir entscheiden uns für
die Pivot-Tabelle in einem neuen Arbeitsblatt. |
|
Wenn wir jetzt statt auf „Weiter“ auf „Layout“ klicken, sehen wir das
folgende Fenster, in dem wir die Datenbereiche (dargestellt links durch Knöpfe
mit den Datenbereichsnamen) in die Bereiche „Zeile“, „Spalte“ oder „Daten“
kopieren können. Wenn wir später etwas mehr geübt sind, leistet dieses Fenster
gute Dienste.
Wenn wir aber statt auf „Layout“ erst auf „Optionen“ klicken, so öffnet
sich ein Fenster, in dem wir wiederum allerlei auswählen können. Auch hier
verzichten wir vorerst auf eine Veränderung der Einstellungen, da man dafür
erst etwas Übung haben muss.
Wenn wir statt auf „Layout“ oder „Optionen“ auf „Fertig stellen“
klicken, so öffnet sich ein Fenster, das wie folgt aussieht:
Auch hier können wir die rechts auftauchenden Datenbereichsnamen
(Spaltennamen) mit der Maus (linke Maustaste) in die Bereiche „Zellenfelder
hierher ziehen“, „Spaltenfelder hierher ziehen“, „Datenfelder hierher ziehen“
oder „Seitenfelder hierher ziehen“ kopieren. Wir fixieren mit der Maus
„Total_1“ und ziehen den erscheinenden Balken in „Datenfelder hierher ziehen“.
Anschliessend ziehen wir auch noch „Total_2“ in die nun erscheinende Zelle mit
dem Titel „Summe von Total_1“. Danach sehen wir das folgende Bild, in dem wir
rechts jetzt die Summen der genannten Datenbereiche orten:
Die Anschrift „Summe von Total_1“ hat nun in „Daten gewechselt“. Wenn
wir den Pfeil neben „Daten“ anklicken und einen Haken entfernen, so
verschwindet der angesprochene Datensatz wieder. Doch wir können ihn immer neu
holen…. Klicken wir mit der rechten Maustaste in den oben gezeigten Bereich und
wählen im sich öffnenden Fenster „PivotChart“, so erhalten wir eine graphische
Darstellung der beiden Summen mittels Balken.
Schön, nicht? Doch wozu ist so etwas gut? Das erfahren wir, wenn wir
einmal in der Microsoft-Excel-Hilfe den Eintrag „Informationen zu
PivotTable-Berichten“ anschauen (siehe Bild unten). Daraus sehen wir, dass wir
mit Hilfe von Pivot-Tabellen „Datenbankauszüge“
(interessante Schnittmengen) darstellen können.
Wenn wir nun in unserer Tabelle unter „Daten è
Pivot-Tabelle- und BivotChart-Bericht… è Fertig stellen“ links in den Bereich „Seitenfelder
hierher ziehen“ den Balken für „Wertung_1“ hinziehen, dann in den
Bereich oben „Spaltenfelder hierher ziehen“ den Balken für „Wertung_2“ und den
Bereich „Datenfelder hierher ziehen“ den Balken für „T_1“, so ergibt sich das
unten gezeigte Bild. Steht dann bei „Wertung_1“ der Eintrag „E“, so finden wir
in „T2_“ nur Einträge, wenn bei „Wertung_2“ ebenfalls ein Eintrag vorhanden
ist, im Beispiel hier bei „E“ den Eintrag (Summe) 74.5 und bei „FX“ den Eintrag
(Summe) 24. So kann man einfach „Schnittsummen“ berechnen und dadurch Daten
vergleichen oder interessante Eigenschaften hervorheben.
Im obigen Beispiel aus „Informationen zu PivotTable-Berichten“ werden so
Gesamtverkäufe in der aus Sportarten und Quartalen (Zeit)
gebildeten Matrix dargestellt.
-
Zu Zahlenformate in EXCEL siehe z.B.
o
http://www.uni-giessen.de/~g021/PDF/xl_zahlenformate.pdf
-
Zu Mustervorlagen
in EXCEL siehe z.B.
o
http://office.microsoft.com/de-de/excel/HP051995961031.aspx
Wir tragen in einer leeren Tabelle in A1 den Wert 4.131591424159 ein. Dann rufen wir unter „Format è Zellen… è Zahlen è Benutzerdefiniert“ das unten gezeigte Fenster auf, in dem wir in den
Bereich „Typ“ den Eintrag „
#’###.0.00 „Sec“ “ wie gezeigt gemacht haben. Wenn wir nun „OK“ drücken, so
erscheint die Zahl 4.131591424159 im
Format „4.132 Sec“.
Wie im nachstehenden Bild
waren in den Kolonnen A und B die Werte 4.131591424159, 3 und 6.93 eingetragen. Wir klicken nun
Spate A an und rufen darauf mit „Format è Zellen… è Zahlen è Benutzerdefiniert“ rufen wir nun das eben
definierte Format auf (ganz unten in der gezeigten Liste). |
|
Ergebnis: Siehe rechts im Bild. Nun wollen
wir weiter noch eine wieder verwendbare
Formatvorlage erstellen. Wir benutzen dazu (s. nächste Seite) die Tabelle
„Notentabelle A1B“, in der vorläufig einige Einträge gemacht sind.
Die vorhandenen Zahlen in Zeile 4 sind
alle auf 0 gesetzt. In C2 steht das Datum (Funktion „JETZT()“. Damit wollen
wir auf sinnlose, jedoch amüsante Art etwas spielen. Zuerst klicken wir auf
die Schaltfläche „Alles markieren“. Das ist das Schnitt-rechteck der
Spaltenkopfzellen und der Zeilenkopfzellen. Damit ist das ganze Tabellenblatt
aktiviert. Anschließend rufen wir „Format è Formatvorlage… è “ auf und geben im Fenster „Name der Formatvorlage:“
den Namen „Neu“ und „OK“ ein. Anschließend drücken wir im gezeigten Fenster
den Balken „Ändern…“. Darauf erscheint ein Fenster „Zellen
formatieren“, in dem wir einiges einstellen können. |
|
Hier
wählen wir die folgenden Einstellungen:
- Zahlen: „Uhrzeit
è 13:30:55“
- Ausrichtung:
Kein neuer Eintrag
- Schrift:
„Antique Olive“, Rest belassen
- Rahmen:
„Außen“
- Muster: Orange
Farbe auswählen
- Schutz:
belassen
Dann
drücken wir „OK“ und wieder „OK“. Die 0 erscheint jetzt im Zeitformat.
Nun gehen wir in ein neues Tabellenblatt, in dem nur in A2 die Zahl 0
eingetragen ist und in dem wir den Bereich A1:C4 markieren. Rufen wir nun
„Format è Formatvorlage…
è Neu è OK“ auf, so wird der Bereich A1:C4 im neu erstellten Format namens
„Neu“ gezeigt. Siehe dazu das Bild unten.
Zahlenformate lassen sich übrigens auch formatabhängig farbig gestalten. (Farben „Schwarz,
Weiß, Rot, Hellgrün, Blau, Gelb, Magenta, Zyan“.)
|
Analog
zur Formatvorlage „Neu“ erstellen wir
zur Demonstration eine Formatvorlage „Neu1“ und geben das benutzerdefinierte
Zahlenformat in der Form „ [Gelb]#’##0.000 „Sec“ “ ein, siehe Bild unten. Dazu wählen wir
unter Schrift noch die Einstellung „Fett“ aus. Die Anwendung auf die Notentabelle zeigt
das unten folgende Bild: „Notenverschleierung“, Demonstration und auch Spiel! |
Zu EXCEL
und Outlook, Mappen und Tabellen zur Bearbeitung via Internet
weiterleiten (siehe http://www.office.gmxhome.de/_excel_outlook.htm#Mappe_mit_Outlook_senden
Ein Beispiel zum Versenden von
Arbeitsmappen aus dem EXCEL-Programm heraus
EXCEL spielt mit dem
E-Mail-Programm Outlook zusammen. Im nachstehenden Bild ist zu sehen, „wo man
klicken“ muss, um aus einer offenen Arbeitsmappe heraus Daten per E-Mail
weiterzuleiten. Durch ein wenig herumspielen mit der Sache findet man schnell
heraus, wie das funktioniert und was dabei auch die Tücken sind. Vor einer
Sache sei hier gewarnt: Vor dem Überschreiben von Originaldaten durch falsch
veränderte Daten.
Bei diesem Kurs kann es sich des Umfangs wegen nur um eine Einführung in EXCEL handeln. Das
bedeutet, dass sehr viele interessante Möglichkeiten des Programms in diesem
Rahmen nicht erörtert werden können. Daher ist es angebracht, in einem Ausblick
kurz auf nicht besprochene wesentliche Punkte aufzählend einzugehen. Damit es
trotzdem dem Leser möglich ist, sich ein Bild von der Sache zu machen und sich
eventuell in einzelne Teilaspekte im Selbststudium einzuarbeiten, sind dazu
einerseits jeweils Hyperlinks angegeben, deren Verfügbarkeitsdauer andererseits
nicht kontrollierbar ist.
Die Besprechung der folgenden Themenbereiche würde jetzt eine logische
Fortsetzung zu diesem Skriptum bilden:
-
Szenarien
-
Mehrfachoperationen
-
Trends
-
Macros mit Visual Basic for Applications (VBA) in EXCEL (siehe z.B. http://de.wikipedia.org/wiki/Visual_Basic_for_Applications
)
- Weitere Tricks siehe Skript „Die Trickkiste“
Zur dieser Sache ist ein
Skript erscheinen unter http://rowicus.ch/Wir/MathcadExcelAndereMath/FileList2.html (siehe „Trickkiste“ unter diesem Link, htm oder
pdf“). (In dieser Ausgabe ist der Stoff unten angehängt.)
Ein interner,
passwortgeschützter Link zu Literatur in dieser Sache ist ganz am Schlusse
dieses Skripts angegeben.
http://rowicus.ch/Wir/MathcadExcelAndereMath/ScriptEXCEL/EinfuehrungInEXCEL.htm
http://rowicus.ch/Wir/MathcadExcelAndereMath/ScriptEXCEL/EinfuehrungInEXCEL.pdf
http://rowicus.ch/Wir/MathcadExcelAndereMath/ScriptEXCEL/EXCEL_Trickkiste.htm
http://rowicus.ch/Wir/MathcadExcelAndereMath/ScriptEXCEL/EXCEL_Trickkiste.pdf
1. Dieses Skript ist für Ingenieure gedacht. Mathematik
ist daher Voraussetzung, vertiefte Kenntnisse in Buchhaltung jedoch nicht.
2. Man besorge sich und sichte erst das Vorgängerskript.
Im nachfolgenden Text werden die dort gemachten Ausführungen vorausgesetzt.
3. EXCEL ist ein Tabellenkalkulationsprogramm, nutzbar
als numerischer "Taschenrechner"
Man besorge sich und sichte
erst das Vorgängerskript, siehe unter dem Link
-
http://rowicus.ch/Wir/MathcadExcelAndereMath/FileList2.html
Bei der Züchtung von 29
Sorten eines biologischen Produkts ist die nachstehende Datentabelle
entstanden. Kolonne A zeigt die Sorten, Kolonne B den erzielten
Benotungsschnitt bei einzelnen Tests. Kolonne C zeigt die Übersetzung der Daten
der Kolonne B in eine Wertung, wie man sie heute auch im ECTS-System an den
Hochschulen benutzt. In Kolonne D sind dann die Punktzahlen eingetragen, welche
von einer durch die Behörden veranstaltete Qualitätsprüfung gewonnen worden
sind. Diese Qualitätsprüfung bezweckt
die Erteilung der Verkaufserlaubnis unter einem speziellen Gütesiegel. In
Kolonne E steht das Ergebnis der Umrechnung der Punktzahl von Kolonne D in
einen Notenwert, der mit dem Wert in Kolonne B vergleichbar ist. In Kolonne F
finden wir dann ein gewichtetes arithmetisches Mittel der Daten aus den Kolonnen
B und E. In Kolonne G steht wiederum eine den ECTS-Punkten vergleichbare
Wertung. Einige repräsentative Umrechnungen und die dabei benutzten Formeln
können wir an den folgenden Beispielen ablesen:
E7:
„=WENN(D7>$E$3/2;(D7-$E$3/2)/($E$3/2)*2+4;(D7)/($E$3/2)*3+1)“
F7: „=B7*0.3+E7*0.7“
G7:
„=WENN(F7>$I$11;"A";WENN(F7>$I$10;"B";WENN(F7>$I$9;"C";
WENN(F7>$I$8;"D";WENN(F7>$I$7;"E";"F")))))“
In der Kolonne I sind die
Grenzen eingetragen, innerhalb derer ein Wert liegen muss, um in Kolonne G
eingetragen zu werden. So ergibt ein Schnitt oberhalb dem Wert I11 den
Rating-Wert A. Ein Schnitt oberhalb I10 und kleiner gleich I11 gibt Rating B
u.s.w.
Rechts sind dann in einer
Graphik die Häufigkeiten des Auftretens der verschiedenen Rating-Werte dargestellt.
Man erwartet etwa die Form einer Gauß-Verteilung. Andere Argumente spielen beim
Entscheid für die Akzeptanz der gewählten Verteilung aber auch eine Rolle. Die
Form der Verteilung wird dabei durch die Zahlen in Kolonne I festgelegt. Die
dazu gehörigen Häufigkeiten finden wir in Kolonne J. Ihre Berechnung (Zellen
J7:J12) erfolgt durch die folgende Matrixformel:
{=HÄUFIGKEIT(F6:F34;I7:I11)}
Dabei wird auf die
Wertsymbole in Kolonne K Bezug genommen. In den Kolonnen L bis R ermittelt man
dann noch ein Chi-Quadrat-Wert bezüglich der Normalverteilung.
Formelbeispiele dazu sind
nachstehend aufgeführt, ohne explizit darauf einzugehen.
M8: „=J8/ANZAHL($F$6:$F$34)“
N8:
„=NORMVERT(I8;$Q$7;$Q$8;WAHR)-NORMVERT(I7;$Q$7;$Q$8;WAHR)“
O8: „=(M8-N8)^2/N8“
Es soll nun untersucht
werden was passiert, wenn man die Werte in der Kolonne I infolge einschlägiger
Argumente anders wählt. Die restlichen Eingabedaten sollen dabei dieselben bleiben.
Nun wäre es möglich, die
oben gezeigte Tabelle in ein anderes Arbeitsblatt zu kopieren und dort dann die
Kolonne I mit neuen Werten zu beschicken. Auf diese Weise könnte man eine ganze
Sammlung von Arbeitsblättern mit Tabellen mit verschiedenen Werten in I
anlegen. Dieses Vorgehen ist jedoch nicht empfehlenswert, da man eine
spätere Änderung in den Rohdaten dann in allen Tabellen nachtragen muss, was
erfahrungsgemäß immer wieder zu Fehlern führt. Das Nachtragen oder das Updaten
von Daten sollte immer nur an einer Stelle, also quasi zentral geschehen. Dann
sind Unterlassungsfehler ausgeschlossen.
Um zu Darstellungen mit
verschiedenen Werten in der Kolonne I und zu kommen mit gleichzeitig zentral
gespeicherten Rohdaten, welche für alle Ausprägungen der Kolonne I Gültigkeit
haben, verwenden wir die in EXCEL implementierte Technik der Szenarien.
Dazu wählen wir unter „Extras“
die Option „Szenarien…“. (Vergleiche dazu das folgende Bild.)
Im folgenden Fenster
(Szenario-Manager) klicken wir auf „Hinzufügen“.
Dann öffnet sich ein Fenster
mit dem Namen „Szenario bearbeiten“.
In diesem Fenster tragen wir
einen Namen ein, z.B. „Erfolgsberechnung“ und geben dann die veränderbaren
Zellen ein, in welchen wir die Werte jeweils anpassen wollen. Hier sind dies
die Zellen „$I$7:$I$12“. Der Kommentar wird dann vom System selbständig
ergänzt. Als Ersteller trägt das System hier den Autor „Rolf Wirz“ ein.
Als Schutz tragen wir
„Änderungen verhindern“ ein.
Nachdem wir mit „OK“
abgeschlossen haben, öffnet sich das Fenster der Szenariowerte, in welchem die
bestehenden Werte schon eingetragen sein sollten. Daher wollen wir diese jetzt
nicht ändern und schließen wieder mit „OK“ ab.
Nach dem Abschließen mit „OK“
gelangen wir in den Szenario-Manager, wo diverse Optionen angeboten werden:
Wir ändern nun den
bestehenden Szenario-Namen so auf „Erfolgsberechnung 1“ und fügen anschließend
ein neues Szenario mit dem Namen „Erfolgsberechnung 2“ hinzu. Hier geben wir
die im obigen Bild gezeigten Werte ein. Wenn wir dann dieses neue Szenario
„Erfolgsberechnung 2“ mit „Anzeigen“ aufrufen, so zeigt sich uns das
nachfolgend dargestellte Bild mit einem veränderten Balkendiagramm. Man sieht
sofort, dass dieses neue Szenario nicht so gut wie das erste die Form einer
Normalverteilung erzeugt.
Wenn wir nun im Szenario-Manager auf
„Zusammenfassen…“ klicken, so erhalten wir eine Tabelle mit den Eingängen „Aktuelle
Werte“ und „Erfolgsberechnung 1“ sowie „Erfolgsberechnung 2“, in denen die
ursprünglich in „$I$7:$I$12“ stehenden Werte sowie die später im Fenster
„Szenariowerte “eingegebenen Werte stehen. Die weiter noch erscheinenden
„Ergebniszellen“ lassen sich unter „Zusammenfassen…“ mit der Maus auswählen.
Man kann so schnell die veränderbaren Daten und deren Konsequenzen bei
verschiedenen Szenarien vergleichen, was hier oben, wo erst zwei Szenarien
vorhanden sind, noch nicht so aufregend erscheint.
Auf diese Weise lassen sich in EXCEL Präsentationen erstellen, welche man mit Hilfe des Szenario-Managers abrufen
kann.
-
Probiere das hier Gelesene praktisch mit
EXCEL aus: Rufe die Datei http://rowicus.ch/Wir/MathcadExcelAndereMath/ScriptEXCEL/Szenario.xls
auf und füge weitere Szenarien hinzu.
Mit Hilfe der Mehrfachoperationen oder des „Tabellenausfüllens“ gelingt
es, auf der Grundlage einer gegebenen Formel eine Tabelle mit einem oder zwei
variablen Eingängen in einem Zuge auszufüllen. Als Werte für die Eingänge
dienen entweder die Werte einer Zeile, einer Spalte oder die Werte aus zwei
Eingängen, „einer Zeile und einer Spalte“. Am besten lässt sich das mit Hilfe
von Beispielen erklären. Eine andere Möglichkeit für das Ausfüllen von Tabellen
bietet der schon besprochene Szenario-Manager.
Wir wollen für ein festes Kapital von sFr. 100'000.- den Kontostand nach
einem, zwei, drei, vier und fünf Jahren berechnen und das bei einem Zinssatz
von 0.5%, 1%, 1.5%, …, 3.5 % und 4 %. Dafür verwenden wir eine Tabelle, in der
als Eingänge in einer Randzeile die Jahre 1 bis 5 und in einer Randspalte die
Zinsfüsse 0.5% bis 4% stehen. Um die zu verwendende Formel Kn = K0 * (1+p)n
(mit n = Anzahl Jahre und p = Zinsfuss) zu testen, machen wir zuerst ein
Referenzbeispiel. Wir verwenden dazu p = 3.00% und n = 4. Diese Werte stehen in
den Zellen B3, B4 und B5, siehe im nachstehenden Bild. (Das Resultat steht in
Zelle B8.)
Die Formel für Kn, d. h. „=$B$3*(1+B4)^B5“ steht, wie schon
erwähnt, in der Eckzelle der verwendeten Tabelle oben links, d. h. in Zelle B8.
Dass diese Formel dort steht und folglich der Kontostand nach 4 Jahren bei 3%
dort erscheint, ist für die Tabellenerzeugung sehr wichtig. Für unsere Tabelle ist der Zellbereich B8:G16
vorgesehen. Nun aktivieren wir diesen Bereich mit der Maus und klicken auf
„Daten è
Tabelle… “, worauf sich das unten gezeigte Fenster mit dem Titel
„Tabelle“ öffnet.
Achtung:
In EXCEL 97 heißt es „Daten è Mehrfachoperationen… “.
Jetzt tragen wir im
Fenster die Adressen der Referenzzellen der variablen Eingangszeile und der
variablen Eingangsspalte ein. Für die Zeileneingänge (Zellen aus Zeile 8) ist
das B5 und für die Spalteneingänge (Zellen aus Spalte B) ist das Zelle B4.
EXCEL kennt nun die Formel, da diese als einzige Formel im aktivierten
Bereich steht. |
|
Wenn wir das Fenster mit „OK“
abschließen, wird diese Formel automatisch für die Zellen der Tabelle angepasst
und angewendet. Die Bezugsadressen stammen aus der Formel und den eben im
Fenster eingegebenen Adressen für die Referenzwerte der Werte aus den Zeilen
uns Spalten, welche als Eingänge dienen.
Wie das Resultat danach
aussieht, kann man dem nächsten Bild entnehmen.
Wenn wir nun den in der
Formel konstanten Wert SFr. 100'000.00 in SFr. 20'000.00 umändern, so wird die
Tabelle automatisch angepasst (siehe nächstes Bild).
Nun wollen wir als Beispiel
die Kontostände nach 5 Jahren bei 3% Zins pro Jahr für verschiedene Kapitalien ausrechnen.
Dazu tragen wir wie vorhin wieder unsere Referenzwerte (Anfangskapital,
Zinssatz und Anzahl Jahre) in die Zellen B4, B4 und B5 ein. Die Tabelle kommt
in den Bereich B8:H9 zu liegen. Die variablen Eingangswerte für die Kapitalien
stehen hier in der Zeile 8 (Zellen C8:H8). Die Formel schreiben wir an den
Beginn der noch leeren Zeile, die die berechneten Werte aufnehmen wird, d.h. in
Zelle B9. Sie lautet diesmal: „=B3*(1+B4)^B5“.
Danach rufen wir wieder
„Daten è
Tabelle… “ auf, worauf sich wieder das Fenster mit dem Titel „Tabelle“
öffnet. Nun schreiben wir B3 ins Fenster für „Werte aus Zeile“ (zur
Bezeichnung der Variablen in den Formeln, die nun durch die in der Tabelle
vorhandenen Eingänge je nach Zelle ersetzt werden soll). Eine Spalte mit
variablen Eingängen existiert hier nicht. Daher bleibt diese Position im
Fenster leer. Wenn wir jetzt mit „OK“ schließen, so erhalten wir das unten
gezeigte Resultat.
Analog geht man vor, wenn
man die variablen Eingänge in eine Spalte statt in eine Zeile schreibt.
Achtung:
Will man Daten löschen, die als
Resultate einer falsch produzierten Mehrfachoperation entstanden sind, so kann
man diese Daten mit der Maus markieren und dann mit „Bearbeiten è Löschen è Inhalte Entf“ löschen. Die Eingänge
(Randzeile, Randspalte der Matrix) können dabei stehen gelassen werden.
-
Probiere das hier Gelesene praktisch mit
EXCEL aus: Rufe die Datei http://rowicus.ch/Wir/MathcadExcelAndereMath/ScriptEXCEL/Szenario.xls auf. Dort findet man die oben gezeigten
vorbereiteten Tabellen. Füge weitere
Daten hinzu.
In der Praxis stellt sich oft das Problem der Regressionsanalyse. Gegeben ist eine Punktwolke z.B. in einem
(x,y)-Koordinatensystem, die aus einer Messreihe stammt. Frage: Wie lautet die am
„besten passende“ lineare Funktion durch diese Punktwolke?
Statt nach einer linearen Funktion können wir z.B. auch nach einer
Exponentialfunktion oder nach anderen Funktionen fragen. Sei nun für den Moment
x die Zeit t. Von Trendbestimmung sprechen wir dann, wenn es darum geht, die
Funktionswerte y(x) für diejenigen x-Werte zu berechnen, die grösser als der
grösste vorhandene x-Wert unter den Messwerten sind. D. h. man versucht durch
Extrapolation über die gegebene Zeit hinaus Vorhersagen zu machen. Dafür stellt
EXCEL die in solchen Situationen oft zur Anwendung kommenden linearen und
exponentiellen Funktionen zur Verfügung. Will man andere Funktionen verwenden,
so kann man diese erst selbst berechnen, was mit EXCEL natürlich sehr aufwendig
sein kann.
RGP
RGB ist eine
Statistik-Funktion, aufrufbar über den Funktionsassistenten. Die EXCEL-Hilfe
sagt dazu:
- Berechnet die Statistik für eine Linie unter
Verwendung der Methode der kleinsten Quadrate, zur Berechnung einer geraden Linie,
die für die Daten am geeignetsten ist, und gibt eine Matrix zurück, die die
Linie beschreibt.
- Da diese Funktion ein Wertarray liefert, muss die
Formel als Matrixformel eingegeben werden.
- Die Gleichung einer solchen Geraden lautet: y = m x + b
- oder y = m1x1 + m2x2 + ... + b (bei mehreren
Bereichen mit x-Werten)
- Dabei ist der abhängige y-Wert eine Funktion der
unabhängigen x-Werte.
- Die m-Werte sind Koeffizienten, die zu den jeweiligen
x-Werten gehören, und b ist eine Konstante.
- Beachten Sie, dass y, x und m Vektoren sein können.
Eine von RGP ausgegebene Matrix hat die Form {mn;mn-1;...;m1;b}.
- RGP kann darüber hinaus zusätzliche
Regressionskenngrößen bereitstellen.
RKP
RKB ist ebenfalls eine
Statistik-Funktion, aufrufbar über den Funktionsassistenten. Die EXCEL-Hilfe
sagt dazu:
- In Regressionsanalysen berechnet diese Funktion eine
Exponentialkurve, die möglichst gut an die von Ihnen bereitgestellten Daten
angepasst ist, und liefert ein Wertarray, die diese Kurve beschreibt.
- Da diese Funktion ein Wertarray liefert, muss die
Formel als Matrixformel eingegeben werden.
- Die Gleichung der Kurve lautet: y = b*m^x
- oder y =
(b*(m1^x1)*(m2^x2)*_) (bei mehreren x-Werten)
wobei der abhängige y-Wert eine Funktion der unabhängigen x-Werte ist.
- Jeder m-Wert ist eine Basis, zu der ein
entsprechender x-Wert als Exponent gehört, und b ist eine Konstante.
- Beachten Sie, dass y, x und m Vektoren sein können.
- Eine von RKP ausgegebene Matrix hat die Form {mn.mn-1
... .m1.b}
SCHÄTZER
SCHÄTZER ist eine Statistik-Funktion,
aufrufbar über den Funktionsassistenten. Die EXCEL-Hilfe sagt dazu:
- Gibt den Schätzwert für einen linearen Trend zurück.
- Der Vorhersagewert ist ein Y-Wert bei einem gegebenen
X-Wert.
- Bei den bekannten Werten handelt es sich um vorhandene
X- und Y-Werte, und der neue Wert wird, ausgehend von einer linearen
Regression, vorhergesagt.
- Diese Funktion ermöglicht Ihnen, zukünftige Umsätze,
erforderliche Lagerbestände oder Verbrauchertrends vorherzusagen.
TREND ist eine Statistik-Funktion,
aufrufbar über den Funktionsassistenten. Die EXCEL-Hilfe sagt dazu:
- Liefert Werte, die sich aus einem linearen Trend
ergeben.
- Diese Funktion passt den als Matrizen Y_Werte und
X_Werte übergebenen Werten eine Gerade an (nach der Methode der kleinsten
Quadrate).
- Als Ergebnis liefert die Funktion die auf der Geraden
liegenden y-Werte, die zu den in Neue_X_Werte angegebenen x-Werten gehören.
VARIATION
VARIATION ist eine
Statistik-Funktion, aufrufbar über den Funktionsassistenten. Die EXCEL-Hilfe
sagt dazu:
- Liefert Werte, die sich aus einem exponentiellen
Trend ergeben.
- VARIATION liefert die y-Werte für eine Reihe neuer
x-Werte, die Sie mit Hilfe vorhandener x- und y-Werte festlegen.
- Sie können die Arbeitsblattfunktion VARIATION auch
verwenden, um eine zu den vorhandenen x- und y-Werten passende Exponentialkurve
zu ermitteln.
Gegeben ist eine Messreihe über 27 Tage. Die Tage sind in A4:A30
verzeichnet, die zugehörigen Messwerte in B4:B30. Nun soll anhand dieser
Messreihe durch lineare Extrapolation der Trend für die Tage 28 bis 50
vorausberechnet werden. Diese Tage sind hier in D4:D26 eingetragen.
Wir markieren erst mit der Maus die Zellen E4:E26, in die die berechneten
Werte eingetragen werden sollen. Für die Berechnung rufen wir im
Funktionsassistenten die Funktion „TREND“ auf, worauf wir das oben gezeigte
Fenster ausfüllen können. Für die Y-Werte tragen wir die Zellen B4:B30 ein, für
die X-Werte die Zellen A4:A30. Die neuen X-Werte stehen in D4:D26. Bei
„Konstante“ tragen wir WAHR ein, damit die berechnete Gerade nicht durch den
Ursprung gelegt wird. Wenn wir dann mit „OK“ abschliessen, stellen wir fest,
dass nur die erste Zelle E4 berechnet worden ist. Das liegt daran, dass „Trend“
eine Matrixfunktion ist. Daher
markieren wir nach der eben beschriebenen Berechnung die Zellen E4:E26
abermals. Darauf drücken wir die F2-Taste und anschliessend die Kombination
Ctrl+Caps+Enter (Strg und Grossbuchstaben und Eingabe). Dadurch wird die Formel
so angewandt, dass über mehrere Zellen „eine Matrix“ ausgegeben wird. In der
Kommandozeile erscheint dann die Formel in geschweiften Klammern, wie im
nächsten Bild gezeigt.
Bemerkung: EXCEL stellt
zusätzlich ein einfaches Tool zur Verfügung, um den Anwendungsbereich der
Matrixfunktion zu ermitteln. Klicken wir auf eine Zelle der Matrix, z.B. auf
E11 und rufen wir dann mittels „Bearbeiten è Gehe zu… è Inhalte…“ das Fenster „Inhalte auswählen“, so können wir
dort „Aktuelles Array“ resp. „Aktuelle Matrix“ anwählen. Schliessen wir
mit „OK“ ab, so zeigt sich der abgefragte Bereich blau hinterlegt.
Nun wollen wir dasselbe Ergebnis wie im letzten Abschnitt für die Tage
28 bis 50 mit Hilfe der Schätzer-Funktion berechnen. Dazu klicken wir auf die
Zelle F4 und rufen im Funktions-Assistenten die Funktion „Schätzer“ auf. (Die
Grossschreibung wird dabei automatisch ergänzt.) Dann füllen wir das sich
öffnende Fenster wie unten gezeigt aus:
Der X-Wert wird für F4 aus D4 bezogen, die Y-Werte stehen in $B$4:$B$30
und die X-Werte in $A$4:$A$30. Dabei müssen wir für die X- und Y-Werte in den
Kolonnen A und B absolute Bezüge verwenden, da sonst dieses Datenfeld beim
„Herunterziehen der Formel“ unerlaubterweise verlassen wird. Mit „OK“
schliessen wir ab und schliessen so das Eingabefenster. Nun steht der Wert 6.7259379 in Zelle F4. Um die restlichen Zellen
auszufüllen, packen wir diese Zelle unten rechts mit der linken Maustaste und
ziehen sie nach unten. Dann sehen wir das folgende Bild:
Wir stellen fest, dass die Zellen mit den numerischen Werten in den
Kolonnen E und F jetzt übereinstimmen, was auch richtig ist.
Jetzt versuchen wir noch, für die Tage 28 bis 50 eine Vorhersage mit
Hilfe der Variation-Funktion (exponentieller Trend) zu machen.
Wir markieren erst mit der Maus die Zellen G4:G26, in die die berechneten
Werte eingetragen werden sollen. Für die Berechnung rufen wir im
Funktionsassistenten die Funktion „VARIATION“ auf, worauf wir das unten
gezeigte Fenster ausfüllen können. Für die Y-Werte tragen wir wie bei „TREND“
die Zellen B4:B30 ein, für die X-Werte die Zellen A4:A30. Die neuen X-Werte
stehen in D4:D26. Bei „Konstante“ tragen wir wieder WAHR ein. Wenn wir dann mit
„OK“ abschliessen, stellen wir wieder fest, dass nur die erste Zelle G4
berechnet worden ist. Das liegt wiederum daran, dass „VARIATION“ eine Matrixfunktion ist. Daher markieren wir
nach der eben beschriebenen Berechnung die Zellen G4:G26 abermals. Darauf
drücken wir die F2-Taste und anschliessend die Kombination Ctrl+Caps+Enter
(Strg und Grossbuchstaben und Eingabe). Dadurch wird die Formel so angewandt,
dass über mehrere Zellen „eine Matrix“ ausgegeben wird. Das Ergebnis sehen wir
unten.
-
Probiere das hier Gelesene praktisch mit
EXCEL aus: Rufe die Datei http://rowicus.ch/Wir/MathcadExcelAndereMath/ScriptEXCEL/Szenario.xls
auf. Dort findet sich „Tabelle 1“ mit den eben verwendeten Daten. Versuche, durch
einfügen neuer Daten die Rohdaten zu erweitern und die Voraussagen dann
anzupassen. Versuche dann auch, die Daten und auch die vorausberechneten Daten
graphisch darzustellen.
Hinter den Komponenten von MS-Office,
speziell also hinter EXCEL, ist auch noch eine sehr potente Programmiersprache
versteckt, die man als gewöhnlicher Benutzer kaum bemerkt. Zu dieser
Programmiersprache, dem Visual Basic vor Applications
(VBA) existieren Zugriffe von EXCEL aus. Man kann z.B. dort mit Hilfe des
VBA-Editors Programme schreiben, die man Macros
oder eingedeutscht „Makros“
nennt. Man kann auch die „Makros“ aufzeichnen, indem man die zu
programmierenden Schritte in EXCEL unter Beobachtung des Systems ausführt und
dann abspeichert. Hier wollen wir nur diese 2. Methode praktizieren, da die
erste ein profundes Erlernen der Programmiersprache voraussetzt, was nicht
trivial ist und viel Zeit benötigt.
Um mit dem System etwas
Bekanntschaft zu schließen, wählen wir „Extras è Makro è Makros…“. Darauf erscheint ein Fenster mit
dem Namen „Makro“. In der Absicht, die Sache einmal einfach so auszuprobieren,
geben wir hier den Namen „Tabelle_7x7“ ein. Das Makro soll für alle offenen
Arbeitsmappen gültig sein. Darauf klicken wir auf „Erstellen“.
Die Fenstersituation ist in
den nachfolgenden beiden Bildern wiedergegeben.
Darauf öffnet sich der Visual-Basic-Editor
wie nachfolgend gezeigt. Es wird also eine Subroutine mit Namen Tabelle_7x7
angelegt, in der noch kein Programmcode vorhanden ist. Hier könnten wir Code
eingeben. Dafür müssten wir aber die Sprache kennen, sonst hat man da nichts
verloren. Weiter können wir uns aber auch noch auf der linken Seite unter
„Projekt - VBAProjekt“ über die Datenstruktur informieren, in der das Macro
dann abgelegt wird. Wenn man auf einen der dort vorhandenen Namen oder auf die
+-Zeichen klickt, so öffnet sich vielleicht ein Untermenü, worauf man sehen
kann, was dort gespeichert ist. Mehr können wir da nicht tun. Daher schließen
wir den Editor mittels Klick auf den X-Knopf oben rechts im Fenster.
Nun befinden wir uns wieder
im gewöhnlichen EXCEL. Um keine Dateien mit wertvollen Daten zu beschädigen
öffnen wir jetzt eine neue leere Arbeitsmappe. (Hier die Datei z__work_1.xls).
Wir befinden uns jetzt in Tabelle1.
Wir klicken in die Zelle B3
und rufen darauf „Extras è Makro è aufzeichnen…“ auf, worauf sich ein Fenster
mit Namen „Makro aufzeichnen“ öffnet. Hier tragen wir den Namen T_7x7 ein,
tragen noch bei „Strg+“ ein t ein, was uns den Makro-Aufruf durch die
Tastenkombination „Strg+t“ sichert. Wenn wir das Makro in „Diese Arbeitsmappe“
speichern, d.h. in die jetzt aktive Arbeitsmappe, dann können wir es anderswo
nicht gebrauchen. Wenn wir es aber in „Persönliche Arbeitsmappe“ speichern,
dann könnten wir dort mit der Zeit wegen der großen Menge von Makros mit dem
Suchen beschäftigt werden. Wir wählen aber jetzt trotzdem die persönliche
Mappe, denn man kann ein Makro im VBA-Editor auch immer wieder löschen.
|
|
Wenn wir jetzt mit “OK” abschließen, so kann die Aufzeichnung beginnen!
Achtung! Jetzt ja keine Fehler mehr machen, denn alles wird aufgezeichnet!
Ideal ist es, wenn man sich für diese Sache ein Drehbuch zurechtgelegt hat, dem man folgen kann.
|
Auf dem Bildschirm erscheint nun das links
gezeigte Dialogfenster. Mit Klick auf den blauen Punkt rechts unten kann man
die Aufzeichnung beenden. Das
Symbol rechts dient zur Aktivierung der relativen Aufzeichnung mit relativen
Zellbezügen. Andernfalls sind diese absolut. |
Wir fabrizieren
nun eine Tabelle wie unten gezeigt, mit der ersten Zelle in B3. Anschließend
beenden wir die Aufzeichnung.
Nun gehen wir noch den
Speicherort des Macros suchen. Unter „Extras è Makro è Makros…“ gelangen wir zu einem Fenster wie
nachfolgend gezeigt:
Wir sehen, dass jetzt vor dem
Namen „T_7x7“ vom System noch eine Zeichenkette vorgehängt worden ist, die wir
später als den Namen des zugehörigen VBA-Projekts erkennen werden. Mit einem
Klick auf „Schritt“ gelangen wir in den Macro-Editor und können uns dort
den Code ansehen. (Siehe in den nachfolgenden Bildern, wo nur der Anfang und
der Schlussteil gezeigt sind.) Man staunt nicht schlecht, wenn man
herausfinden, dass dieses Macro hier 151 Zeilen besitzt. Davon kann man sich
überzeugen, wenn man persönlich die Aufzeichnung an der eigenen Maschine
nachvollzieht.
Ein Teil des Codes erweist
sich als selbsterklärend, ein anderer, großer Teil jedoch nicht. Das ist eben
so, weil jemand einmal die Sprache so festgelegt und damit geregelt hat. Ihre
Erarbeitung braucht Zeit. Dafür ist als Gegengewicht ihre Effizienz groß, wie
man das meistens bei Programmiersprachen trifft.
Nun klicken wir in der
Tabelle3 in die Zelle A1 und rufen unter „Extras è Makro è Makros…“ mit Hilfe von „Ausführen“ unser Macro
auf. Was passiert danach?
Das Resultat sehen wir im
nachstehenden Bild: Die Tabelle wird ab der Zelle A1 eingefügt. Wiederholen wir
die Sache mit unserer Tastenkombination „Ctrl+t“, nachdem wir zuerst in E6
geklickt haben, dann wird die Tabelle nochmals mit der linken oberen Ecke in
Zelle E6 erzeugt, womit dann die erste Tabelle mit der linken oberen Ecke in A1
teilweise überdeckt wird! Das soll man in der anschließenden Übung
ausprobieren!
-
Zeichne ein Makro nach eigenem Gutdünken auf, das den
oben gezeigten Makro ähnelt. Rufe es dann mit der eingegebenen
Tastenkombination in verschiedenen Situationen auf und beobachte, was passiert.
-
Überlege dir, welche Vorgänge in deiner Arbeit man
sinnvoll in einem Macro erfassen sollte und welche dagegen nicht, weil es sich
kaum lohnt.
Zu einer Fortzsetzung in VBA:
Siehe Anhang, „Weiterführende Literatur“.
Arbeitsblätter werden je nach Entstehung oder internem Bedarf auf http://rowicus.ch/Wir/MathcadExcelAndereMath/FileList2.html
mittels Links zugänglich gemacht.
Damit sind wir am vorläufigen Ende dieses Einführungsskripts. Wenn die Umstände
es erfordern, werden hier ohne Ankündigung noch neue Kapitel angefügt.
Gelegentlich notwendiges neues Material
kann aber auch über das oben erwähnte URL zugänglich gemacht werden.
Externe Links zu Literatur zur
Fortsetzung des Stoffes dieses Skripts:
- Unter http://de.wikibooks.org/wiki/VBA_in_Excel_-_Grundlagen findet man ein öffentlich und gratis
verfügbares Wiki-Buch zur
Visual-Basic-Programmierung.
o
- http://www.herber.de/wiki.html:
Link zu Herbers EXCEL-Lehr- und Lernmaterialien
(das Standardwerk für EXCEL und VBA).
o
- http://de.wikipedia.org/wiki/Microsoft_Excel
(mit weiterführenden Links).
Interne Links zu Literatur und
Arbeitsmaterial:
- Unter http://rowicus.ch/Wir/Links/Linkpage3.html#EXCEL
ist eine öffentlich zugängliche
Linksammlung zu EXCEL-Themen bereitgestellt.
o
- Unter http://rowicus.ch/Wir/MathcadExcelAndereMath/FileList2.html
findet
man weitere öffentlich zugängliche Links zu internem Material.
o
- „Nur intern“: http://rowicus.ch/Wir/Scripts/restricted/MasterIndex.html (unter EXCEL suchen, nur intern
zugänglich, passwortgeschützt). Unter
diesem Link (Rubrik "Diverse Skripts Mathcad, Maple und EXCEL")
findet man für den internen Gebrauch (Selbststudium) weitere EXCEL-Skripte,
VBA-Skripte (Visual Basic for Applications) und Skripte zu EXCEL-Spezialthemen
wie Zahlenformate mit EXCEL, Daten konsolidieren (zusammenfassen) mit EXCEL,
Mustervorlagen erstellen mit EXCEL, Einstieg
in Piviot-Tabellen, Datenauswertung mit Pivot-Tabellen mit EXCEL,
Zusammenspiel EXCEL und Outlook, erstellen von Macros u.s.w.
Wird bei Bedarf fortgesetzt
ENDE
Wir1/10/08