在Excel中计算静态随机数（计算一次） [英] Compute a static random number (compute it once) in Excel

问题描述

我正在寻找一种在Excel中计算一次随机数的方法。所以它计算的第一次被调用，但后来它不会改变。

I'm looking for a way to compute a random number once in Excel. So its computed the first time its called, but then it doesn't change afterwards.

所以例如，如果我有这样的B1 RANDONCE（A1），那么我第一次在A1中输入一个值，它会计算一个随机值，但不会再次改变。或者至少不会，直到我再次更改A1。

So for example, if I had something like this in B1 RANDONCE(A1), then the first time I put a value in A1 it would compute a random value but then it wouldn't change again. Or at least not until I changed A1 again.

我想这样做，而无需手动重新启动B1，将其从公式转换为一个值，如所述 here 。

I would like to do this without manually recopying B1 to turn it from a formula to a value as described here. Use of macros is fine.

推荐答案

您需要一个带有内存的UDF，所以它知道单元格是否已更改

You need a UDF with memory, so it knows if the cell has changed

当引用调用更改时，UDF将返回一个新的随机值，否则返回最后一个随机值（即不改变）

如果源单元格也返回空白是空白的（可能或可能不是你需要的？）

This UDF will return a new random value when the refered to call changes, otherwise returns the last random value (ie no change)
Also return blank if source cell is blank (may or may not be what you require?)

注意：它有问题，静态当纸张关闭时，值将丢失，因此每次打印纸张时，值都将更改。

Note: it has the problem that the Static values are lost when the sheet is closed, so the value will change each time the sheet is opened.

Function randonce(r As Range)
    Static trigger As Variant
    Static v As Double
    If r <> trigger Then
        v = Rnd
        trigger = r
    End If
    If Len(r) <> 0 Then
        randonce = v
    Else
        randonce = vbNullString
    End If
End Function

这篇关于在Excel中计算静态随机数（计算一次）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！