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

 

 

---

   

Inhaltsverzeichnis

  

 

Inhaltsverzeichnis. 2

EXCEL-Trickkiste (Kurzanleitung) 3

1. Vorgängerskript 3

1.1. Links. 3

1.2. An Stelle einer Einleitung. 3

1.3. Übung. 3

2. Szenarien. 3

2.1 Die Problemstellung. 3

2.2. Die Idee der Szenarien. 5

2.2. Übung. 10

3. Mehrfachoperationen oder Tabellenerzeugung. 10

3.1. Um was geht es?. 10

3.2. Ein Beispiel mit zwei variablen Eingängen. 10

3.3. Ein Beispiel mit nur einem variablen Eingang. 13

3.4. Übung. 14

4. Trends. 14

4.1. Um was geht es hier?. 14

4.2. EXCEL-Werkzeuge zur Trendermittlung. 15

4.3. Anwendung 1: Die Trendfunktion. 16

4.4. Anwendung 2: Die Schätzer-Funktion. 18

4.5. Anwendung 3: Die VARIATION-Funktion. 20

4.6. Übung. 21

5. Makros. 22

5.1. Um was geht es?. 22

5.2. Das Aufzeichnen eines Macros an einem Beispiel 22

5.3. Übung. 29

 

---

 

EXCEL-Trickkiste (Kurzanleitung)

 

 

(EXCEL ist ein Tabellenkalkulationsprogramm, nutzbar als numerischer "Taschenrechner")

 

von Rolf Wirz

 

1. Vorgängerskript

1.1. Links

Dieses Skript baut auf http://rowicus.ch/Wir/MathcadExcelAndereMath/EinfuehrungInEXCEL.htm oder http://rowicus.ch/Wir/MathcadExcelAndereMath/EinfuehrungInEXCEL.pdf .

1.2. An Stelle einer Einleitung

 

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.

 

1.3. Übung

 

Man besorge sich und sichte erst das Vorgängerskript, siehe unter dem Link

-       http://rowicus.ch/Wir/MathcadExcelAndereMath/FileList2.html  

Go to the Top

2. Szenarien

 

2.1 Die Problemstellung

 

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“

Go to the Top

 

2.2. Die Idee der Szenarien

 

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.

 

Go to the Top

 

 

 

Nach dem Abschließen mit „OK“ gelangen wir in den Szenario-Manager, wo diverse Optionen angeboten werden:

  1. Drücken wir „Anzeigen“, so wird die eingetragene Situation angezeigt. Da wir die Werte von vorher übernommen haben, sehen wir hier das alte Bild.
  2. Mit „Schließen“ können wir den Szenario-Manager verlassen.
  3. Mit „Hinzufügen“ können wir ein weiteres Szenario entwerfen. Wir können einen neuen Namen definieren und in den Zellen „$I$7:$I$12“ andere Werte eingeben. Wenn wir dann so mehrere Szenarien eingegeben haben, können wir diese im Szenario-Manager abrufen und so präsentieren.
  4. Mit „Bearbeiten“ können wir ein eingegebenes Szenario ändern.

 

 

 

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.

Go to the Top

 

 

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.

Go to the Top

 

2.2. Übung

 

-       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.

 

 Go to the Top

 

3. Mehrfachoperationen oder Tabellenerzeugung

3.1. Um was geht es?

 

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.

 

Go to the Top

 

3.2. Ein Beispiel mit zwei variablen Eingängen

 

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).

 

 

 

Go to the Top

 

 

3.3. Ein Beispiel mit nur einem variablen Eingang

 

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.

 

3.4. Übung

 

-       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.

 

 Go to the Top

 

4. Trends

 

4.1. Um was geht es hier?

 

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.

Go to the Top

 

4.2. EXCEL-Werkzeuge zur Trendermittlung

 

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

 

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.

 

Go to the Top

 

 

4.3. Anwendung 1: Die Trendfunktion

 

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.

 

 

 

Go to the Top

 

 

4.4. Anwendung 2: Die Schätzer-Funktion

 

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.

 

 

4.5. Anwendung 3: Die VARIATION-Funktion

 

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.

 

 

 

 

4.6. Übung

 

-       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.

 

 

Go to the Top

 

5. Makros

 

5.1. Um was geht es?

 

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.

Go to the Top

 

5.2. Das Aufzeichnen eines Macros an einem Beispiel

 

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.

 

 

 

 

Go to the Top

 

 

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!

 

 

 

Go to the Top

 

5.3. Übung

 

-       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.

 

Go to the Top

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

---

 

  Wird bei Bedarf fortgesetzt

 

 

 

---

 

Go to the Top

 

 

 

ENDE