EXCEL-Trickkiste (Kurzanleitung)
für Ingenieure
(Ohne ausführliche Einführung in Visual Basic)
von
Rolf Wirz
Version 1.0.0 vom 04.10.2008 (mit Programmversion MS EXCEL
2002)
(Ausdruck: Ca. 30 A4-Seiten ohne Zusatzmaterial)
Hinweis: Für den Ausdruck ist das Material im als pdf
formatiert. Siehe Link: http://rowicus.ch/Wir/MathcadExcelAndereMath/ScriptEXCEL/EXCEL_Trickkiste.pdf
Adresse des Autors
Hochschule für
Architektur, Bau und Holz HSB
Pestalozzistrasse
20, CH-3400 Burgdorf
Tel. +41 (0)34 426
42 30
1.2. An Stelle einer Einleitung
3. Mehrfachoperationen oder Tabellenerzeugung
3.2. Ein Beispiel mit zwei variablen Eingängen
3.3. Ein Beispiel mit nur einem variablen Eingang
4.2. EXCEL-Werkzeuge zur Trendermittlung
4.3. Anwendung 1: Die Trendfunktion
4.4. Anwendung 2: Die Schätzer-Funktion
4.5. Anwendung 3: Die VARIATION-Funktion
5.2. Das Aufzeichnen eines Macros an einem Beispiel
(EXCEL ist ein Tabellenkalkulationsprogramm, nutzbar
als numerischer "Taschenrechner")
von Rolf Wirz
Dieses Skript baut auf http://rowicus.ch/Wir/MathcadExcelAndereMath/EinfuehrungInEXCEL.htm
oder http://rowicus.ch/Wir/MathcadExcelAndereMath/EinfuehrungInEXCEL.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.
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.
Wird bei Bedarf fortgesetzt
ENDE