骰子符号在excel [英] dice notation in excel

问题描述

快速故事：我是一个罗列，我在做角色excel中的表单，我需要做骰子符号 calcul。

我需要能够写入单元格像 3d6 + 2d4 + 6 或 3d8 + 2 + 3d9-2d6 或任何组合。然后在另一个单元格中计算结果。

像 Rolldice（A1），它会计算（如果 3d8 + 2 + 4d8 被赋予，它将在 9 和 58 ）

Basicaly 1d6 表示 1 具有六个面的骰子，因此在 1 和 6 之间的随机数字。 2d6 将意味着 1d6 + 1d6 （因此 2 和 12 ）

我不介意如果结果要求我在胸罩之间设置骰子在（3d6）+2 +（4d8）

它应该能够添加，减法或乘法

1dX + 1dX或1dX - 1dX或1dX * 1dX

/ p>

我希望我自己足够清楚。如果需要，请随时询问规格。

最好的问候，

ps：感谢修复我的缺点英语。这是非常好的看到评论并从中学习！

解决方案

说我们在列 A

高亮单元格并运行这个小宏：

  Sub DiceXlator（）
 Dim r As Range，v As String，NewForm As String，dependsode As Boolean 
 Dim dee As String 
 dee =d$ b $bέode= False 
对于每个r在选择
v = r.Value 
 NewForm ==
对于i = 1到Len（v）
 ch = Mid（v，i ，1）
如果ch = dee然后
 NewForm = NewForm& * RANDBETWEEN（1，
 dependsode = True 
 Else 
如果不是IsNumeric（ch）和dependsode然后
 dependsode = False 
 NewForm = NewForm&） 
 End If 
 NewForm = NewForm& ch 
 End If 
 Next i 
 
如果dependsode然后
 NewForm = NewForm& ）
 End If 
 
 r.Offset（0，1）.Formula = NewForm 
下一个r 
 End Sub 
  

宏将每个骰子表达式转换为标准Excel公式，并将公式放在列 B 中的相邻单元格中/ p>

以下是输入/输出

的一个小例子

编辑＃1：

以下是与用户定义函数形式相同的逻辑 - UDF

 公共函数RollDice（r As Range）As Variant 
 Application.Volatile 
 Dim v As String，NewForm As String，dependsode As Boolean 
 Dim dee As String 
 dee =d
 dependsode = False 
v = r.Value 
 NewForm ==
 For i = 1 To Len （v）
 ch = Mid（v，i，1）
如果ch = dee然后
 NewForm = NewForm& * RANDBETWEEN（1，
 dependsode = True 
 Else 
如果不是IsNumeric（ch）和dependsode然后
 dependsode = False 
 NewForm = NewForm&） 
 End If 
 NewForm = NewForm& ch 
 End If 
 Next i 
 
如果dependsode然后
 NewForm = NewForm& ）
 End If 
 
 RollDice = Evaluate（NewForm）
 
结束函数
  

编辑＃2：

可以获得 Min，Max， 和平均值。例如，在 B1 中输入：

  = RollDice（$ A $ 1）
  

然后从 B3 通过 B1000 B2 >

，最后使用：

  = MAX（B1：B1000 ）
 = MIN（B1：B1000）
 = AVERAGE（B1：B1000）
  

i'm having an issue in excel and i need help from someone who knows better.

Quick story : i'm a rolist, i'm doing character sheet on excel and i need to do dice notation calcul.

I need to be able to write in a cell something like 3d6+2d4+6 or 3d8+2+3d9-2d6 or any combinaison. and then calculate the result in a other cell.

something like Rolldice(A1) and it would calculate it (if 3d8+2+4d8 was given it would give a number between 9 and 58)

Basicaly 1d6 means 1 dice with six faces, thus a random number betwen 1 and 6. 2d6 would mean doing 1d6 + 1d6 (thus a number between 2 and 12)

I don't mind if the result requires me to set the dices between braquets as in : (3d6)+2+(4d8)

it should be able to add, substract or multiply dices

1dX + 1dX or 1dX - 1dX or 1dX * 1dX

I would be immensly thankfull to the one who manage to do that.

I hope i made myself clear enough. feel free to ask specifications if needed.

Best regards,

ps: thanks for fixing my lack of good english. it's especially great to see the reviews and learn from it !

解决方案

Say we have dice expressions in column A

Hi-light the cells and run this small macro:

Sub DiceXlator()
    Dim r As Range, v As String, NewForm As String, deemode As Boolean
    Dim dee As String
    dee = "d"
    deemode = False
    For Each r In Selection
        v = r.Value
        NewForm = "="
        For i = 1 To Len(v)
            ch = Mid(v, i, 1)
            If ch = dee Then
                NewForm = NewForm & "*RANDBETWEEN(1,"
                deemode = True
            Else
                If Not IsNumeric(ch) And deemode Then
                    deemode = False
                    NewForm = NewForm & ")"
                End If
                NewForm = NewForm & ch
            End If
        Next i

        If deemode Then
            NewForm = NewForm & ")"
        End If

        r.Offset(0, 1).Formula = NewForm
    Next r
End Sub

The macro translates each dice expression into a standard Excel formula and places the formula in the adjacent cell in column B

Here is a tiny example of inputs/outputs

EDIT#1:

Here is the same logic in the form of a User Defined Function - UDF

Public Function RollDice(r As Range) As Variant
    Application.Volatile
    Dim v As String, NewForm As String, deemode As Boolean
    Dim dee As String
    dee = "d"
    deemode = False
        v = r.Value
        NewForm = "="
        For i = 1 To Len(v)
            ch = Mid(v, i, 1)
            If ch = dee Then
                NewForm = NewForm & "*RANDBETWEEN(1,"
                deemode = True
            Else
                If Not IsNumeric(ch) And deemode Then
                    deemode = False
                    NewForm = NewForm & ")"
                End If
                NewForm = NewForm & ch
            End If
        Next i

        If deemode Then
            NewForm = NewForm & ")"
        End If

        RollDice = Evaluate(NewForm)

End Function

EDIT#2:

It is possible to get Min, Max, and Average in a statistical sense. For example in B1 enter:

=RollDice($A$1)

then copy B2 from B3 thru B1000

and finally use:

=MAX(B1:B1000)
=MIN(B1:B1000)
=AVERAGE(B1:B1000)

这篇关于骰子符号在excel的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！