Verzauberungskosten/materialien Simulation mit Excel

    • Verzauberungskosten/materialien Simulation mit Excel

      Mit der Einführung der neuen Ausrüstung, hatte ich mich bereits mit einem Vergleich zwischen alten und neuen System beschäftig. Aus dieser Arbeit ist die im folgenden vorgestellte Exceltabelle entstanden mit der die Berechnung der Kosten/Materialien für Schuhe/Handschuhe/Rüstung sowie Waffe für unterschiedliche Stufen/Ausrüstungserfahrung möglich ist.

      Für Fragen, Anmerkungen, kleine Käfer in der Tabelle oder sonstiges bin ich offen.


      Voraussetzungen

      Leider scheint es so, dass die Tabelle nur mit Microsoft Excel fehlerlos funktioniert. Mit Open Office gibt es nur Fehler und mit einigen anderen freien stürzt sie ab und zu ab.

      Da es sich um ein VBA Makro handelt müssen Makros in der Tabelle aktiviert werden, dass ist leider auch der Grund warum die Datei nicht im Forum hochgeladen werden kann.

      Downloadlink

      Datei von filehorst.de laden

      Einschränkungen

      Bis ca. 30000 Items die Simuliert werden ist die Tabelle relativ schnell, ab diesem Punkt wird die Tabelle bedeuten langsamer. Schwächere Rechner haben wahrscheinlich davor schon Problem. 5000 sollte aber jeder problemlos schaffen.

      Es handelt sich bei der Tabelle um eine Simulation mit vielen Item auf den Einzelfall lässt dies keine Rückschlüsse zu. Bitte beachtet dies!

      Nur weil die Tabelle sagt ein Item kostet z.B. 2mio Gold sagt dies nicht über den Handelswert aus. Es gibt weitere Faktoren, die den Wert eines Items beeinflussen!

      Die Genauigkeit der Simulation ist leider abhängig vom Zufallsgenerator von Excel. Es kann in der Theorie sein, dass der Zufall in Tera „besser“ also zufälliger ist als in Excel, nachweisen kann ich das leider nicht. Die Abweichungen, die dadurch entstehen sind aber zu vernachlässigen.

      Beispiel für eine Simulation

      Display Spoiler

      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!

      Was kann ich in der Tabelle ändert?
      Display Spoiler
      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.


      Was sind Dropdownlist?
      Display Spoiler



      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.





      Wie simuliert/macht die Tabelle das?
      Display Spoiler

      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.



      Auswertung

      Display Spoiler

      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.


      Goldvalue anpassen?

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

      Quellcode Makro
      Display Spoiler

      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

      The post was edited 1 time, last by Potio ().

    • ich glaub da ist noch irgendwie ein Fehler drin was "Necessary XP" angeht.
      Beispiel: Von Mid+0 nach High+0 wurden bei mir ca. 17k XP angezeigt, stell ich dann auch High+1 brauch ich plötzlich nut noch 8k XP?
      Ich befürchtet bei Ermittlungen über die Upgradegrenzen hinaus werden die nicht mehr berücksichtigt.
    • Gut gelungen, war sicherlich viel Arbeit. Deswegen auch ein dickes Lob.

      Aber auf Englisch?
      Mir ist es egal, aber es gibt sehr viele, die des Englischen nicht so mächtig sind und den Client auf deutsch spielen. Und ich fürchte, dass dies der Großteil ist.
      Deswegen habe ich ja auch den Übersetzungsguide Skills Deutsch - Englisch gemacht ;)
      Der Vorteil der Klugheit liegt darin, dass man sich dumm stellen kann. Das Gegenteil ist schon schwieriger.
      Traue einem Computer, aber niemals seinem Programmierer!
      Das Auge eines Straußes ist größer als sein Gehirn. Ich kenne Menschen, bei denen ist das nicht anders.
    • Das freut mich aber, werde mich weiterhin bemühen 8o
      Der Vorteil der Klugheit liegt darin, dass man sich dumm stellen kann. Das Gegenteil ist schon schwieriger.
      Traue einem Computer, aber niemals seinem Programmierer!
      Das Auge eines Straußes ist größer als sein Gehirn. Ich kenne Menschen, bei denen ist das nicht anders.
    • WildWerner wrote:

      Das freut mich aber, werde mich weiterhin bemühen 8o
      Ich glaube ich war gemeint. Kenn den Xilokz ja seit 2013 :P

      Zu dem auf Englisch. Das ist mir durchaus bewusst gewesen. Eigentlich ist die Tabelle für die Englische Community gedacht gewesen (einfach die größere). Ich hoffe dass, die die so wenig Englisch können dass sie Probleme mit der Tabelle haben einen finden der ihnen alles erklärt/übersetzt.
    • :D

      Naja, bin schon am Überlegen, ob ich es übersetzen soll.
      Auch die Makros werden mir keine Probleme bereiten, sollte was zu ändern sein, schließlich bin ich Visual Basic Programmierer. Und, habe es mir gerade genauer angeschaut, sieht es so aus, wie wenn ich eh alles ändern könnte.
      Wird aber ein wenig dauern, denn ich habe in Tera viel zu tun
      Der Vorteil der Klugheit liegt darin, dass man sich dumm stellen kann. Das Gegenteil ist schon schwieriger.
      Traue einem Computer, aber niemals seinem Programmierer!
      Das Auge eines Straußes ist größer als sein Gehirn. Ich kenne Menschen, bei denen ist das nicht anders.
    • WildWerner wrote:

      :D

      Naja, bin schon am Überlegen, ob ich es übersetzen soll.
      Auch die Makros werden mir keine Probleme bereiten, sollte was zu ändern sein, schließlich bin ich Visual Basic Programmierer. Und, habe es mir gerade genauer angeschaut, sieht es so aus, wie wenn ich eh alles ändern könnte.
      Wird aber ein wenig dauern, denn ich habe in Tera viel zu tun
      Kannst du gerne tun. War das einzige mal das ich mit VB was zu tun hatte bzw überhaupt was an Code geschrieben habe. Ist deswegen mehr so im try and Error entstanden xD
    • Potio, habe Du hast eine Konversation. Bitte ansehen.
      Der Vorteil der Klugheit liegt darin, dass man sich dumm stellen kann. Das Gegenteil ist schon schwieriger.
      Traue einem Computer, aber niemals seinem Programmierer!
      Das Auge eines Straußes ist größer als sein Gehirn. Ich kenne Menschen, bei denen ist das nicht anders.
    • Soderle, die Übersetzung ist von Potio geprüft, daher stelle ich sie mal rein:

      Verzauberungskosten deutsch
      Der Vorteil der Klugheit liegt darin, dass man sich dumm stellen kann. Das Gegenteil ist schon schwieriger.
      Traue einem Computer, aber niemals seinem Programmierer!
      Das Auge eines Straußes ist größer als sein Gehirn. Ich kenne Menschen, bei denen ist das nicht anders.