VBA-Zufallszahlen - Aufgabe der 2BWI

Titel

Ziel

Erstellen sie in VBA eine vorgegebene Function und in Excel das Funktions-Schaubild dazu.

Details

Die Funktion, welche sie darstellen sollen, liefert sog. normalverteilte Zufallszahlen Diese sind nicht gleichmässig, sondern vorwiegend um ein Häufigkeits-Maximum (Mittelwert, peak) herum verteilt, d.h. die gelieferten Zahlen liegen häufig in der Nähe des Mittelwerts, seltener weiter davon entfernt.
Der Algorithmus (Rechenvorschrift) lautet:

zuf ist eine Zufallszahl aus dem (in den meisten Programmen) eingebauten Zufallszahlen-Generator, welcher gleichförmig verteilte Zufallszahlen im Intervall 0...1 liefert.
StdAbw (Standardabweichung) ist ein Mass für die Streuung um den Mittelwert.
MiWe ( Mittelwert) ist die Lage des peaks ihrer Funktion, d.h. der häufigste gelieferte "Zufalls"-Wert.

Algorithmus:
Schleife ausführen, solange v>=1
x1 = 2 * zuf - 1
x2 = 2 * zuf - 1
v = x1² + x2²
Ende der Schleife
w = Wurzel((-2 * Ln(v)) / v)
zg = (StdAbw * x1 * w) + MiWe

Beachten sie: Dieser Algorithmus ist kein VBA-Programm, sondern ein umgangssprachlich formuliertes "Rezept"

Erzeugen sie mindestens 100, höchstens 1000 Funktionswerte dieser Funktion zg. Während der Entwicklung beschränken sie die Anzahl (z.B. auf 10), damit sie durch die Rechenzeit nicht aufgehalten werden.

Zählen sie die Häufigkeit der erzeugten Zufallszahlen in ca. 10 Zähl-Intervallen beiderseits des Mittelwerts, z.B. von MiWe-3,5*StdAbw bis MiWe+3,5*StdAbw. Die Zählung programmieren sie entweder in Excel oder in VBA (5 Zusatzpunkte).
Beachten sie: Bei dieser Verteilung ist es möglich, dass einzelne erzeugte Zufallszahlen (mit sehr geringer Wahrscheinlichkeit) auch weitab vom Mittelwert liegen und daher nicht in die Zähl-Intervalle fallen.

Nach erfolgter Zählung können sie das Diagramm Häufigkeit (y) gegen Zahlenwert (x) darstellen. Als Zahlenwert verwenden sie den Mittelwert jedes Intervalls.

Ihre Excel-Lösung soll nach Abschluss folgende Elemente enthalten:
Standard-Daten, kurze Erklärung, einstellbare Vorgaben, Werte-Tabelle, Diagramm, VBA-Quelltext
Ihre Web-Lösung soll folgende Elemente enthalten:
Standard-Daten, charakteristische Beispiele (mit screenshots), sowie einen Link, der das Laden eines ZIP-Archivs ihrer Original-Lösung erlaubt.

Hilfe

Hilfe zu VBA sollten sie vorwiegend von der eingebauten Hilfe-

-Funktion erhalten. Dort finden sie zu jedem hier angeführten Stichwort die genaue Definition und Beispiele !
Zusätzlich dazu werden auf dieser Seite einige bereits bekannte Themen wiederholt, und einige Hinweise und Beispiele gegeben:

Function: Kennzeichnet eine VBA-Function. Sie reicht von Function bis End Function. Legen sie "sprechenden" Namen für ihre Functions fest, jedoch keine Namen, die bereits als Excel-Standardfunktionen existieren. In ( ) können sie jeder Function Argumente (Werte oder Bereiche aus Excel) mitgeben, die sie zur Berechnung verwenden können. Mit Zuweisung an den Function-Namen (meist am Ende einer Function) geben sie einen berechneten Wert an Excel zurück. Beispiel:

VBA-Function rechteckflaeche erhält 2 Argumente (Laenge, Breite) von Excel, berechnet daraus die Fläche f und gibt den Wert zuletzt an Excel zurück, wo der berechnete Zahlenwert an Stelle der Funktion (z.B.) =rechteckflaeche(B1,B2) angezeigt wird.
Diese Function können sie in Excel ebenso wie die eingebauten Standardfunktionen verwenden und in jede Zelle einsetzen.

Function rechteckflaeche(Laenge, Breite)
f = Laenge * Breite
rechteckflaeche = f
End Function

Zufallszahlen in VBA erzeugen:
Der Zufallsgenerator muss vor erstmaliger Verwendung initialisiert (auf einen zufälligen Anfangswert gesetzt) werden. Das wird mit Anweisung Randomize ausgeführt. Anschliessend können sie nit rnd beliebig viele Zufallszahlen erhalten, die wie üblich im Intervall 0...1 gleichmässig verteilt sind. Beispiel:

Initialisierung des Zufallsgenerators
Zuweisung einer Zufallszahl an eine Variable
Rechnen mit einer Zufallszahl

Randomize
x = rnd
y = rnd * 6 + 1

Logische Ausdrücke in VBA:
Verzweigungen und Schleifen werden von logischen Ausdrücken (Bedingungen) gesteuert: Das sind Ausdrücke, die (nur) entweder WAHR (Excel) = True (VBA) oder FALSCH (Excel) = False (VBA) sein können. Logische Ausdrücke können, müssen jedoch nicht in (Klammern) gesetzt werden. Beispiele:

Für diese Beispiele werden einigen Variablen Werte zugewiesen:

Diese logischen Ausdrücke sind True:
Diese logischen Ausdrücke sind False:

a=1
b=10

(a=1), (b=10), (a<b), (b>=a), (a<100)
(a=10), (b=0), (a>b), (a>100), (b<=a)

Logische Ausdrücke können verknüpft werden und ergeben dabei wieder die logischen Werte True oder False.
()And () ist nur dann True, wenn alle verknüpften logischen Ausdrücke True sind.
() Or () ist immer dann True, wenn mindestens ein verknüpfter logischer Ausdruck True ist.

(a=1) And (b=10)
(a>0) And (a<b) And (a<100)
(a<5) And (b<5)
(a<5) Or (b<5)
(a<0) Or (b<0)
(a<0) Or (a<b) Or (b<0)

' True
' True
' False
' True
' False
' True

Verzweigung in VBA:
Eine Verzweigung wird durch die Anweisung If (Bedingung) Then ... End If gesteuert.
Nach If folgt ein logischer Ausdruck (Bedingung), der die Verzweigung steuert. Wenn der Ausdruck True ist, werden alle Anweisungen zwischen Then und End If ausgeführt, wenn der Ausdruck False ist, dann springt das Programm zur nächsten Anweisung nach End If.
Bei Bedarf kann noch Else eingefügt werden: In diesem Fall werden die Anweisungen zwischen Else und End If dann ausgeführt, wenn der logische Ausdruck False ist. Beispiele:

Wenn x<0 zutrifft, dann wird x=0 gesetzt. In jedem Fall arbeitet das Programm mit dem nächsten Befehl nach End If weiter.	If (x < 0) Then x = 0 End If
Wenn die Bedingung zutrifft (x liegt zwischen a und b) dann wird der "Zähler" z um 1 weitergezählt, ansonsten wird kein Befehl ausgeführt.	If ( (x>a) And (x<b) ) Then z = z+1 End If
Wenn die Bedingung zutrifft (x<a), dann wird der Zähler z um 1 erhöht, wenn nicht (x=a oder x>a), dann wird der Zähler um 1 verringert.	If (x < a) Then z = z+1 Else z = z-1 End If

Schleifen in VBA:
Eine Schleife ist ein Programmteil, der mehrmals durchlaufen wird. Schleifen sind ein wichtiges Element aller Programmiersprachen und in Excel nicht oder nur sehr aufwendig darstellbar. Prinzipiell gibt es zwei Arten von Schleifen:
Wenn sie wissen, wie oft eine Schleife ausgeführt werden soll, dann verwenden sie eine For...Next-Schleife.
Wenn sie das nicht wissen dann verwenden sie eine Do While...Loop-Schleife.

Hier werden alle ganzen Zahlen zwischen 1 und 100 addiert: Nach For folgt immer ein Schleifenzähler, für den Anfang (nach =) und Ende (nach To) angegeben wird. In jedem Schleifendurchgang wird der Schleifenzähler automatisch um 1 erhöht. Die Schleife wird so lange ausgeführt, bis der Schleifenzähler gleich oder grösser ist als der angegebene Maximalwert.	x = 0 For i = 1 To 100 x = x + i Next
Hier wird bei 100 Zufallszahlen geprüft, ob sie kleiner als 0,3 (Kommapunkt in VBA !!!) sind oder nicht. Die "Treffer" werden in der Variablen a gezählt. Den Schleifenzähler (hier z) können sie selbst bestimmen. Die Zähl-Grenzen können sie mit Zahlen einstellen (z.B. 1 To 100 oder -100 To 100) oder mit anderen Variablen (1 To e oder e To f).	a = 0 For z = 1 To 100 If rnd < 0.3 Then a = a + 1 End If Next
Hier werden ganze Zahlen addiert, und zwar so lange, bis deren Summe 1000 erreicht. Nach der Anweisung Do While wird eine logische Bedingung für die Schleife angegeben. Die Schleife immer wieder ausgeführt, solange die Bedingung True ist. Danach werden wie üblich die nach Loop folgenden Befehle ausgeführt. Achtung: Wenn die Bedingung nie erfüllt wird, haben sie eine Endlos-Schleife programmiert !	s = 0 i = 0 Do While (s < 1000) i = i + 1 s = s + i Loop
Hier wird gezählt, wie oft man würfeln muss, um einen Pasch (zwei 6er) zu erhalten. Zuerst werden Anfangswerte gesetzt, dann folgt die Schleife. Die Bedingung pasch=0 sorgt dafür, dass die Schleife solange durchlaufen wird, bis ein Pasch gewürfelt ist. Ein Zähler z zählt mit, wie oft die Schleife durchlaufen wird. In der Schleife wird 2x gewürfelt (wurf1, wurf2) und dann geprüft, ob ein Pasch vorliegt (beide Würfe müssen 6 zeigen). Nach dem Ende der Schleife enthält z die gesuchte Anzahl der Würfe bis zum ersten Pasch.	pasch = 0 z = 0 Do While (pasch = 0) z = z + 1 wurf1 = Int(rnd * 6) + 1 wurf2 = Int(rnd * 6) + 1 If ((wurf1 = 6) And (wurf2 = 6)) Then pasch = 1 End If Loop

Die Funktion für die Quadratwurzel lautet Sqr( ). Beispiel:

x = Sqr(a+b)

Die Funktion für den natürlichen Logarithmus lautet Log( ).

y = Log(a+123)

Bereiche: Excel kennt den Begriff "Bereich" (Range): Sie geben die Adresse der ersten Zelle (links oben) an, danach die Adresse der letzten Zelle (rechts unten), getrennt mit Doppelpunkt. Beispiele: A1:B2, A$1:A$10, $E10:$E100, ...
VBA behandelt Excel-Bereiche als Objekte der Type Range.

So übergeben sie Bereiche von Excel an VBA: Sie vergeben einen Variablen-Namen für den Bereich, danach das Schlüsselwort As und die Type Range	function test1 (Bereich As Range)
So ermitteln sie die Länge (Anzahl der Zellen) eines Bereichs. Eigenschaften von Objekten erhalten sie durch Angabe des Objekts, dann seiner Eigenschaft, getrennt durch Punkt.	z = Bereich.Count
So bezeichnen sie eine bestimmte Zelle eines Bereichs: Die erste Zelle Bereich(1) liegt wie in Excel links oben, dann folgen alle Zellen bis zur letzten. Die Zahl in Klammer (Index) kann eine natürliche ganze Zahl oder eine Variable sein.	Bereich(1), Bereich(2), ... Bereich(i), ... Bereich(z)
So ermitteln sie den Wert (angezeigter Zahlenwert oder Text) einer Excel-Zelle in einem Bereich. Value ist eine Einschaft jeder Excel-Zelle.	w = Bereich(x).Value
So ändern sie mit VBA den Wert einer Excel-Zelle in einem Bereich	Bereich(x).Value = w
Hier wird in einer Schleife gezählt, wie viele Werte eines Bereichs grösser als Null sind: Vor derBearbeitung von Bereichen sollten sie immer die Länge (Anzahl der Zellen) ermitteln. Danach bearbeitet man alle Zellen des Bereichs in einer For-Schleife. Der Wert w der Zelle i wird ermittelt, danach der Zähler z weitergezählt, wenn die Bedingung erfüllt ist. Nach dem Ende der Schleife enthält z die gesuchte Anzahl.	laenge = Bereich.Count z = 0 For i = 1 To laenge w = Bereich(i).Value If (w > 0) Then z = z + 1 End If Next

Ein Muster der Aufgaben-Lösung finden sie in diesem ZIP-Archiv. In dieser Excel-Arbeitsmappe ist das Beispiel nicht nur wie verlangt in VBA sondern zusätzlich in Excel programmiert. Das soll ihnen beim Verständnis des Algorithmus helfen und demonstrieren, wieviel Excel-Programmierarbeit man sich mit geeignet spezialisierten VBA-Functions erspart.

Ergebnis

Das Ergebnis ist eine Zusammenfassung der Aufgaben Zufall(Excel) und Zufall (VBA)
Stand vom 2002-12-10
Aufgabe gelöst von 4 StudentInnen, nicht gelöst von 1 StudentInnen.
Vergebene Punkte: Mittelwert = 16.0 von 20 Punkten. (80 %)