Verzauberungskosten/materialien Simulation mit Excel

Klein Kopflos fragt sich wie teuer seine Stiefel für seinen Twink wahrscheinlich wären, wenn er diese ohne Erfahrung von +0 Low auf +7 Top zieht.

In der Dropdownlist wählt er nun den Ausrüstungstyp Footwear/Handwear

In den Dropdownlist from und to entsprechend Low +0 und Top +7

In der Dropdownlist Item XP wählt er No. XP

Unter Necessary XP zeigt ihm die Tabelle jetzt schon die theoretisch benötigte Erfahrung für vollen Bonus auf jeder Stufe an.

In der Grünen Zelle unter Itemamount kann klein Kopflos jetzt noch festlegen wie viele Items simuliert werden sollen, mehr erhöhen die Genauigkeit brauchen dafür mehr Zeit er wählt deshalb 2000.

Jetzt drückt er nur noch auf Calculate ! und nach wenigen Sekunden(bruchteilen) taucht eine Nachricht mit Runetime succesfull auf.

Hezlichen Glückwünsch klein Kopflos hat jetzt 2000 Stiefel oder Handschuhe in Excel simuliert und kann mit den Daten arbeiten. Wie er das macht? Kommt später unter Auswertung!

Vorgesehen für Einstellungen sind Zellen mit grüner oder blauer Hintergrundfarbe. Dabei sind blaue sogenannte Dropdownlist und in den Grünen kann ein eigener Wert eingegeben werden.

Das sind Zellen in Excel bei denen sobald man auf diese Klickt rechts an der Zelle ein Pfeil nach unten erscheint auf den man Klicken kann. Klickt man auf diesem Öffnet sich eine vorher erstellte Liste mit Auswahlmöglichkeiten.

Mit Hilfe eines Makros in Excel wird für die gewählten Einstellungen, eine bestimmte Anzahl an Items durchsimuliert. Also im Prinzip macht Excel nichts anderes als mehrere 100 oder 1000 Item mit den Vorgaben zu Aufwerten und schreibt, die dafür notwenden Materialien in eine Liste. Ein sogenannte Monte Carlo Simulation. Das sieht in der Tabelle dann so aus.

Wobei jede Zeile einem Item entspricht.

Im Detail wird für jede Stufe eine „Erfolgstabelle“ berechnet. Der erste Versuch hat 20% Erfolg d.h. 80% schaffen es nicht mit einem Versuch. Der zweite Versuch hat 23% Erfolg d.h. 23% von den 80% schaffen es jetzt also bleiben 0,8-0,23*0,8 = 0,616 = 61,6%, die noch nicht erfolgreich waren. In der Tabelle steht bis jetzt 1 Versuch 20% 2 Versuche 100%-61.6% = 38.4%. Übersetzt also 20% haben 1 Versuch gebraucht 38.4% 2 oder weniger. Diese Tabelle wird weiter berechnet bis 100% erreicht sind.

Im Makro wird dann für die Stufe eine Zufallszahl zwischen 0 und 1 generiert. Diese Zahl wird mit der Erfolgstabelle verglichen. Wird eine 0.11 generiert bedeutet dies, dass das item zu den 20% gehört die es mit dem ersten versuch schaffen, da 0,11 < 20% ist. Bei einer 0.21 entsprechend wären es 2 Versuche da 0.21 > 0.2 aber 0.21 < 0.384 wären. So werden über das Makro die benötigten Versuche für jede Stufe zufällig bestimmt.

Min

Unter Min wird für jedes Material, die kleine benötigte Menge aufgeführt. Dies bedeutet nicht das das „günstigste“ Item entsprechend Material gebraucht hat.

Max

Unter Max wird für jedes Material, die größte benötigte Menge aufgeführt. Dies bedeuet nicht das das „teuerste“ Item von jedem Material entsprechend gebraucht hat.

Median

Gibt den Wert an der genau in der Mitte von allen Materialen liegt. D.h. 50% haben mehr und 50% weniger gebraucht.

Average

Gibt den Mittelwert der benötigten Materialien an. D.h. die Menge aller benötigten Items durch die Anzahl der simulierten Items.

Variation

Gibt die Abweichung nach unten und oben vom Average an. Und zwar so das zwischen Average minus Variation und Average plus Variation 66,6% aller simulierten Items liegen.

Quantile

Der %-Wert unter dem Quantil bestimmt, die % Grenze. D.h. Bei 10% bedeutet das 10% der Items weniger oder gleich der angegebenen Materialien gebraucht haben.

Graphen

Mit der Dropdownlist über den Graphen kann gewählt werden für welches Material die Graphen anzeigen sollen.

Graph 1

Die X-Achse gibt die % an und Y-Achse den entsprechenden Wert. Also z.B. 80,95% der Stiefel kosten 725401 Gold oder weniger!

Graph 2

Ist Graph1 nur mit Vertauschen Achsen

Graph 3

Auf der X-Achse steht der Wert und die Y-Achse zeigt die % Chance genau diesen Wert zu treffen. Bei großen Simulationen entsteht hier eine nach rechts lastige Glockenkurve.

Da die Preise für Smaragde, usw. natürlich nicht immer gleich sind kann auf der Seite CostCalculation etwas weiter unten die Goldpreise für jedes Item angepasst werden. Es ist sogar möglich den normalen Materialien einen Goldwert zu geben für die lieben Unterstützer von TERA . Die Werte passen sich dann automatisch an es muss nicht neu simuliert werden.

Sub newwaffe()
'Deaktivieren von Excelfunktionen
'deactivate excelfunktion
With Application
.ScreenUpdating = False
.Calculation = xlCalculationManual
.EnableEvents = False
End With

'Variable definieren
'set variable
t = Timer
Dim bonus As Double
Dim XP2 As Long
Dim from As Double
Dim finish As Double
Dim Itemtype As Integer
Dim XP As Variant
Dim Count As Long
Dim succhance(41, 50) As Variant
Dim x As Integer
Dim cost As Variant
Dim lastCount As Long
Dim i As Variant
Dim fbonus As Variant

'Werte aus Tabelle auslesen
'read values
lastCount = Sheets("CostCalculation").Range("E3").Value
from = Sheets("CostCalculation").Range("A4").Value + 1
finish = Sheets("CostCalculation").Range("B4").Value
XP = Sheets("CostCalculation").Range("C4").Value
Itemtype = Sheets("CostCalculation").Range("D4").Value
Count = Sheets("CostCalculation").Range("E4").Value - 1
Sheets("CostCalculation").Range("E3").Value = Count + 1
i = 1
c = -1
a = 1

'Überflüssige Simulationen löschen
'delete last simulation
With Sheets("MonteCarlo")
If lastCount > Count + 1 Then
Do Until 3000 * (i - 2) > lastCount
.Range(Worksheets("MonteCarlo").Cells((Count + 4) * a + 3000 * (i - 1), 1), Worksheets("MonteCarlo").Cells(3000 * i, 24)).Clear
i = i + 1
a = 0
Loop
End If
.Range("W3").Resize(Count + 1).FillDown
.Range("X3").Resize(Count + 1).FillDown
End With

'Verzauberungskosten nach Itemtyp in Variable einlesen
'read entchantmentcost by itemtyp
If Itemtype = 1 Then
cost = Sheets("EnchantmentcostWeapon").Range("C3:AP27")
ElseIf Itemtype = 2 Then
cost = Sheets("EnchantmentcostChest").Range("C3:AP27")
ElseIf Itemtype = 3 Then
cost = Sheets("EnchantmentcostGlovesBoots").Range("C3:AP27")
End If

'Basis Erfolgschance abhängig vom gewählten XP-Typ und denn angegebenen Werten berechnen
'base succesrate by XP- type read and calculation
For n = from To 37
XP2 = cost(25, n)
If XP > XP2 Then
If cost(1, n) > 39 Then
bonus = 0.15
XP = XP - XP2
ElseIf cost(1, n) > 30 Then
bonus = 0.15
XP = XP - XP2
ElseIf cost(1, n) > 20 Then
bonus = 0.3
XP = XP - XP2
ElseIf cost(1, n) > 10 Then
bonus = 0.5
XP = XP - XP2
Else
bonus = 0
End If
ElseIf XP > 0 And XP < 1 Then
If cost(1, n) > 39 Then
bonus = 0.15
XP = XP - XP2
ElseIf cost(1, n) > 30 Then
bonus = 0.15 * XP
ElseIf cost(1, n) > 20 Then
bonus = 0.3 * XP
ElseIf cost(1, n) > 10 Then
bonus = 0.5 * XP
Else
bonus = 0
End If
Else
If XP = 0 Then
bonus = 0
XP = 0
ElseIf cost(1, n) > 39 Then
bonus = 0.15
XP = XP - XP2
ElseIf cost(1, n) > 30 Then
bonus = XP / XP2 * 0.15
XP = 0
ElseIf cost(1, n) > 20 Then
bonus = XP / XP2 * 0.3
XP = 0
ElseIf cost(1, n) > 10 Then
bonus = XP / XP2 * 0.5
XP = 0
End If
End If
cost(23, n) = cost(23, n) + bonus
Next n

'Berechnen der Erfolgstabelle
'calculate succestable for item
For n = 1 To 40
If n < 37 Then
fbonus = 0.03
ElseIf n > 36 Then
fbonus = 0.02
End If
succhance(n, 1) = 1 - cost(23, n)
x = (1 - cost(23, n)) / fbonus + 1
i = 2
If x > i Then
Do Until i = x
succhance(n, i) = succhance(n, i - 1) * (1 - (cost(23, n) + fbonus * (i - 1)))
i = i + 1
Loop
End If
Next n

'MonteCarlo Simulation
Do Until suc = 1
'Abfrage für Simulation. Umfänge über 30.000 werden aufgeteilt.
If Count > 30000 Then
Count2 = 30000
c = c + 1
Count = Count - 30000
ElseIf Count < 30000 Then
Count2 = Count
c = c + 1
Count = 0
End If

'Variablen anlegen für einzelnen Simulation
Dim item() As Variant
Dim Rng As Variant
ReDim item(Count2, 40)

'Simulation für jedes Item und Upgradestufe
'simulation for every item and upgrade
For n = 0 To Count2
i = from
Do Until i > finish
Rng = Rnd() 'Zufallsroll
x = 1
Do Until Rng > succhance(i, x) 'Vergleich mit der Erfolgstabelle um die benötigten Versuche zu bestimmen
x = x + 1
Loop
item(n, i) = x
i = i + 1
Loop
Next n

'Variablen für einzelen Aufwertungsmaterialien
Dim mats() As Variant
ReDim mats(Count2, 21)

'Zusammenzählen der einzelnen Aufwertungsmaterialien aus den benötigten Versuchen
For n = 0 To Count2
For i = 0 To 21
For x = 1 To 40
If i = 21 Then
mats(n, i) = mats(n, i) + item(n, x) * cost(3 + i, x)
Else
mats(n, i) = mats(n, i) + item(n, x) * cost(2 + i, x)
End If
Next x
Next i
Next n

'Einfügen der Ergebnisse in die Exceltabelle
Worksheets("MonteCarlo").Range(Worksheets("MonteCarlo").Cells(3 + 30000 * c, 1), Worksheets("MonteCarlo").Cells(Count2 + 3 + 30000 * c, 22)) = mats
If Count = 0 Then suc = 1
Loop

'Reaktivieren von Excelfunktionen
With Application
.ScreenUpdating = True
.Calculation = xlCalculationAutomatic
.EnableEvents = True
End With

'Zeitausgabe
MsgBox Timer - t & " sec", , "Runetime succesfull"

End Sub