在 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 以将其从公式转换为值的情况下执行此操作 此处.使用宏没问题.

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?)

注意:存在关闭表单时Static值丢失的问题，因此每次打开表单时该值都会发生变化.

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屋！