如何停止/冻结/暂停不稳定的 RAND/RANDBETWEEN/RANDARRAY? [英] How to stop / freeze / pause volatile RAND / RANDBETWEEN / RANDARRAY?

查看：84
本文介绍了如何停止/冻结/暂停不稳定的 RAND/RANDBETWEEN/RANDARRAY?的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

是否有一种简单的方法(解决方法)如何禁用易失性函数的自动重新计算，例如 =RAND() =RANDARRAY() =RANDBETWEEN() 在谷歌电子表格 (?)

  • 没有脚本
  • 无插件
  • 没有宏

在构建密钥生成器的情况下，我们需要处理多个 RANDBETWEEN 输出，每次单元格更改都会重新计算，而那些 RANDBETWEEN数字不能在我们不断编辑的工作表中停留更长的时间.

有一个有问题的冷冻机插件和许多需要安装的非通用脚本变体一定程度的改装/知识，那么如何以旧方式做到这一点?

解决方案

先来看看狐狸怎么说

=WHATTHEFOXSAY()

是一个独特的复活节彩蛋谷歌表功能(由

这将使我们能够通过切换复选框来完全控制重新计算.最后一步是通过将所有数字与 PRODUCT 相乘，将更多变化添加到我们的随机性中来扩大我们最初的 1/8 机会，为了更有趣，我们将其乘以 PI 以 3 次方提升:

=ARRAYFORMULA(PI()^3*PRODUCT(LEN(IF(A1=TRUE,{狐狸怎么叫();狐狸怎么叫();狐狸怎么叫();狐狸怎么叫();狐狸怎么叫();狐狸怎么叫();狐狸怎么叫();狐狸怎么叫();狐狸怎么叫()}，{狐狸怎么叫();狐狸怎么叫();狐狸怎么叫();狐狸怎么叫();狐狸怎么叫();狐狸怎么叫();狐狸怎么叫();狐狸怎么叫();狐狸怎么叫()}))))

无限刀片工作

到目前为止，我们似乎没有创建一些有用的东西……一个带有一些随机数字来回循环的复选框(在这一点上根本不是随机的)……所以让我们转到下一个等级.我们的真正目标是创建一串数字，从中提取某些部分并将它们转换为最终输出.为此，我们需要利用 SEQUENCE 的力量，我们TRANSPOSE 以获得额外的力量:

=TRANSPOSE(SEQUENCE(5, 4, 3, 2))

这转换为 5 列乘以 4 行的网格，其中第一个单元格以数字 3 开头，每个下一个单元格的值(按列)都大 2

现在让我们定义我们的最终输出:

  • 3x 唯一字符串
  • 每个字符串有 16 个字符
  • 使用从 0 到 9
  • 以及从 a 到 f<的小写字母/kbd>

此时我们的SEQUENCE将如下所示:

=TRANSPOSE(SEQUENCE(16, 3, 29, 73))


16 = 列3 = 行29 = 起点73 = 步进

对于第三个和第四个参数 (29 & 73)，我们可以使用一些不错的

此时，我们在整个网格中的数字分布非常好

终极十六进制/密码生成器

那里的每个字符都有自己的代码.您可以通过在工作表中运行此 AF 来检查它:

=ARRAYFORMULA({{字符", 代码"};{CHAR(SEQUENCE(2500, 1, 33)), SEQUENCE(2500, 1, 33)}})


ROW(48:57) = 0-9行(65:90)= A-Z行(97:122)= a-zROW(1040:1071) = А-Я行(1072:1103) = а-яROW(913-937) = Α-ΩROW(945-969) = α-ω

为了我们的示例目的，我们需要 0-9 和 a-f 的字符代码:

ROW(48:57) = 0-9行(97:102)= a-f

我们将它放入数组 {} 并按降序对其进行排序(这是因为我们不想将自己锁定在列表中位置较低的字符之外).然后我们 JOIN 或 | 分隔符准备好进行正则表达式:

=JOIN("|", SORT({ROW(48:57); ROW(97:102)}, 1, ))

基本上，这个想法是 REGEXEXTRACT 在我们的数字网格中从上面连接的字符串中首先找到数字，以防万一没有匹配我们添加 IFNA从我们的范围中回退一些数字(在本例中为 102):

=ARRAYFORMULA(IFNA(REGEXEXTRACT(文本(PI()^3*产品(LEN(IF(A1=TRUE,{狐狸怎么叫();狐狸怎么叫();狐狸怎么叫();狐狸怎么叫();狐狸怎么叫();狐狸怎么叫();狐狸怎么叫();狐狸怎么叫();狐狸怎么叫()}，{狐狸怎么叫();狐狸怎么叫();狐狸怎么叫();狐狸怎么叫();狐狸怎么叫();狐狸怎么叫();狐狸怎么叫();狐狸怎么叫();狐狸怎么叫()})))*转置(序列(16、3、29、73))*PI()^ 3，0")，JOIN("|", SORT({ROW(48:57); ROW(97:102)}, 1, ))), 102))

并召唤CHAR字符:

=ARRAYFORMULA(CHAR(IFNA(REGEXEXTRACT(文本(PI()^3*产品(LEN(IF(A1=TRUE,{狐狸怎么叫();狐狸怎么叫();狐狸怎么叫();狐狸怎么叫();狐狸怎么叫();狐狸怎么叫();狐狸怎么叫();狐狸怎么叫();狐狸怎么叫()}，{狐狸怎么叫();狐狸怎么叫();狐狸怎么叫();狐狸怎么叫();狐狸怎么叫();狐狸怎么叫();狐狸怎么叫();狐狸怎么叫();狐狸怎么叫()})))*转置(序列(16、3、29、73))*PI()^ 3，0")，JOIN("|", SORT({ROW(48:57); ROW(97:102)}, 1, ))), 102)))

现在最后一步是使用查询粉碎它并删除剩余的空白空间 - 有关查询粉碎的更多信息

总结

  • 那些字符串真的是随机的吗?ofc 不是 - 它们只是足够随机"；因为熵"我们知道规则，但祝你好运手工破解它...
  • 主要的关键点是没有发生第 3 方重新计算，因此值保持暂停状态并在切换复选框时生成新值
  • 这些字符串会在一段时间内重复吗?- 是的，它们可能，就像常规的 RAND() 函数
  • 有没有可能一次性生成两个相同的密钥?- 是的，它可能会发生，但可以通过多种方式来降低几率或引入 UNIQUE() 或添加更多狐狸尾巴
  • 请注意这个特殊场合:onOpen 事件将在每次复选框切换后重新计算 WHATTHEFOXSAY() 一次！！因此，如果您需要使用冻结值，建议在每次切换复选框后使用 F5 键刷新电子表格

如果您希望在某些时间段内拥有一个新的自动生成的字符串，您可以插入一个易失性函数 TODAY() 或 NOW() - 但请注意每次编辑任何单元格或不编辑任何内容时每分钟/小时都会重新计算
(取决于..//文件>电子表格设置)

如何不对每个单元格更改重新计算并在每个复选框切换或每 6 分钟重新计算一次?一个星期一次?每个奇数月?只有周一和周三吗?每 11 小时一次?可能！我们需要一个更简单的 IF 语句放置在电子表格中的任何位置，它将承载我们的逻辑门并输出数组 {} 星座中的值所以我们可以用我们的狐狸公式来引用一个不包含具有易失性元素的公式的单元格.

is there an easy way (workaround) how to disable automatic re-calculations of volatile functions like =RAND() =RANDARRAY() =RANDBETWEEN() in google spreadsheet (?)

  • without scripts
  • without addons
  • without macros

in case of building a key generator, where we need to work with multiple RANDBETWEEN outputs, a re-calculation takes a place on every cell change, and those RANDBETWEEN numbers can't stay for a longer period of time in our sheet, which we constantly edit.

there's a buggy freezer addon out there and lots of non-universal script variations requiring installation & some degree of modding / knowledge, so how to do this the old way?

解决方案

first, let's see what says the fox

=WHATTHEFOXSAY()

is a unique easter egg google sheets function (discovered by @kishkin) that randomly generates a pre-set string of text on user demand which is a huge deal because while the generation is random, the recalculation is not affected by onEdit, onChange nor onOpen events, so with some tweaking, we can generate a random number without using volatile functions like RAND(), RANDBETWEEN(), ARRAYRAND() which can't be freezed / paused

the fox can speak only these phrases:

A-oo-oo-oo-ooo!
Hatee-hatee-hatee-ho!   
Wa-pa-pa-pa-pa-pa-pow!  
A-bubu-duh-bubu-dwee-dum    
Fraka-kaka-kaka-kaka-kow!   
Jacha-chacha-chacha-chow!   
Gering-ding-ding-ding-dingeringeding    
Joff-tchoff-tchoff-tchoffo-tchoffo-tchoff!

eg. what we are getting with this function is random 1 of 8 chance, which is not much, so we will need to scale it up (let's say by order of 9 fox tails). sadly function is not supported by ARRAYFORMULA, therefore we need to construct {} an array:

={WHATTHEFOXSAY(); WHATTHEFOXSAY(); WHATTHEFOXSAY();
  WHATTHEFOXSAY(); WHATTHEFOXSAY(); WHATTHEFOXSAY();
  WHATTHEFOXSAY(); WHATTHEFOXSAY(); WHATTHEFOXSAY()}

then we convert this fox language into numbers like:

=INDEX(LEN(
 {WHATTHEFOXSAY(); WHATTHEFOXSAY(); WHATTHEFOXSAY();
  WHATTHEFOXSAY(); WHATTHEFOXSAY(); WHATTHEFOXSAY();
  WHATTHEFOXSAY(); WHATTHEFOXSAY(); WHATTHEFOXSAY()}))

next, we need a switch. nothing fancy, just a simple IF statement and one checkbox:

=ARRAYFORMULA(LEN(IF(A1=TRUE, 
 {WHATTHEFOXSAY(); WHATTHEFOXSAY(); WHATTHEFOXSAY();
  WHATTHEFOXSAY(); WHATTHEFOXSAY(); WHATTHEFOXSAY();
  WHATTHEFOXSAY(); WHATTHEFOXSAY(); WHATTHEFOXSAY()}, 
 {WHATTHEFOXSAY(); WHATTHEFOXSAY(); WHATTHEFOXSAY();
  WHATTHEFOXSAY(); WHATTHEFOXSAY(); WHATTHEFOXSAY();
  WHATTHEFOXSAY(); WHATTHEFOXSAY(); WHATTHEFOXSAY()})))

this will give us the ability to have full control over recalculation simply by switching the checkbox. and the last step is to scale up our initial 1/8 chance by adding more variations into our randomness by multiplication of all numbers with PRODUCT and for more fun we multiply it by PI raised on 3rd power:

=ARRAYFORMULA(PI()^3*PRODUCT(LEN(IF(A1=TRUE, 
 {WHATTHEFOXSAY(); WHATTHEFOXSAY(); WHATTHEFOXSAY();
  WHATTHEFOXSAY(); WHATTHEFOXSAY(); WHATTHEFOXSAY();
  WHATTHEFOXSAY(); WHATTHEFOXSAY(); WHATTHEFOXSAY()}, 
 {WHATTHEFOXSAY(); WHATTHEFOXSAY(); WHATTHEFOXSAY();
  WHATTHEFOXSAY(); WHATTHEFOXSAY(); WHATTHEFOXSAY();
  WHATTHEFOXSAY(); WHATTHEFOXSAY(); WHATTHEFOXSAY()}))))

unlimited blade works

so far it doesn't look like we created something useful... a checkbox with some randomly looking number cycling back and forth (which is at this point not random at all tho)... so let's move to the next level. our true objective is to create a string of digits from which we extract certain parts and convert them to final output. for that we need to harness the power of SEQUENCE which we TRANSPOSE for extra power:

=TRANSPOSE(SEQUENCE(5, 4, 3, 2))

this translates as a grid of 5 columns times 4 rows where the first cell starts with number 3 and the value of every next cell (column-wise) is larger by 2

now let's define our final output:

  • 3x unique strings
  • each string has 16 characters
  • with numbers from 0 to 9
  • and lower case letters from a to f

at this point our SEQUENCE will look like this:

=TRANSPOSE(SEQUENCE(16, 3, 29, 73))



16 = columns
3  = rows
29 = starting point
73 = stepping

for the 3rd and 4th argument (29 & 73) we can use some nice prime numbers and multiply our whole sequence again by PI()^3 for extra chaos. also we need to convert our numeric output into a plain text string to avoid 1.79769E+308 notation so we use TEXT for that:

=ARRAYFORMULA(TEXT(PI()^3*PRODUCT(LEN(IF(A1=TRUE, 
 {WHATTHEFOXSAY(); WHATTHEFOXSAY(); WHATTHEFOXSAY();
  WHATTHEFOXSAY(); WHATTHEFOXSAY(); WHATTHEFOXSAY();
  WHATTHEFOXSAY(); WHATTHEFOXSAY(); WHATTHEFOXSAY()}, 
 {WHATTHEFOXSAY(); WHATTHEFOXSAY(); WHATTHEFOXSAY();
  WHATTHEFOXSAY(); WHATTHEFOXSAY(); WHATTHEFOXSAY();
  WHATTHEFOXSAY(); WHATTHEFOXSAY(); WHATTHEFOXSAY()})))*
 TRANSPOSE(SEQUENCE(16, 3, 29, 73))*PI()^3, "0"))

at this point, we have a pretty nice distribution of digits across the whole grid

ultimate hex / password generator

every character out there has its own code. you can check it by running this AF in your sheet:

=ARRAYFORMULA({{"character", "code"};
 {CHAR(SEQUENCE(2500, 1, 33)), SEQUENCE(2500, 1, 33)}})



ROW(48:57)     = 0-9
ROW(65:90)     = A-Z
ROW(97:122)    = a-z
ROW(1040:1071) = А-Я
ROW(1072:1103) = а-я
ROW(913-937)   = Α-Ω
ROW(945-969)   = α-ω

for our example purpose we need character codes for 0-9 and a-f:

ROW(48:57)     = 0-9
ROW(97:102)    = a-f

we put it in array {} and sort it in descending order (it's because we don't want to lock ourselves out from reaching lower positioned characters on the list). then we JOIN it with or | separator ready to be regex-ed:

=JOIN("|", SORT({ROW(48:57); ROW(97:102)}, 1, ))

basically, the idea is to REGEXEXTRACT first found number from the above-joined string within our grid of numbers, and just in case there is no match we add IFNA fallback with some number (102 in this example) from our ranges:

=ARRAYFORMULA(IFNA(REGEXEXTRACT(
 TEXT(PI()^3*PRODUCT(LEN(IF(A1=TRUE, 
 {WHATTHEFOXSAY(); WHATTHEFOXSAY(); WHATTHEFOXSAY();
  WHATTHEFOXSAY(); WHATTHEFOXSAY(); WHATTHEFOXSAY();
  WHATTHEFOXSAY(); WHATTHEFOXSAY(); WHATTHEFOXSAY()}, 
 {WHATTHEFOXSAY(); WHATTHEFOXSAY(); WHATTHEFOXSAY();
  WHATTHEFOXSAY(); WHATTHEFOXSAY(); WHATTHEFOXSAY();
  WHATTHEFOXSAY(); WHATTHEFOXSAY(); WHATTHEFOXSAY()})))*
 TRANSPOSE(SEQUENCE(16, 3, 29, 73))*PI()^3, "0"), 
 JOIN("|", SORT({ROW(48:57); ROW(97:102)}, 1, ))), 102))

and summon the CHAR characters:

=ARRAYFORMULA(CHAR(IFNA(REGEXEXTRACT(
 TEXT(PI()^3*PRODUCT(LEN(IF(A1=TRUE, 
 {WHATTHEFOXSAY(); WHATTHEFOXSAY(); WHATTHEFOXSAY();
  WHATTHEFOXSAY(); WHATTHEFOXSAY(); WHATTHEFOXSAY();
  WHATTHEFOXSAY(); WHATTHEFOXSAY(); WHATTHEFOXSAY()}, 
 {WHATTHEFOXSAY(); WHATTHEFOXSAY(); WHATTHEFOXSAY();
  WHATTHEFOXSAY(); WHATTHEFOXSAY(); WHATTHEFOXSAY();
  WHATTHEFOXSAY(); WHATTHEFOXSAY(); WHATTHEFOXSAY()})))*
 TRANSPOSE(SEQUENCE(16, 3, 29, 73))*PI()^3, "0"), 
 JOIN("|", SORT({ROW(48:57); ROW(97:102)}, 1, ))), 102)))

now the last step is to smash it with a query and remove residue empty spaces - more on query smash here

=ARRAYFORMULA(SUBSTITUTE(FLATTEN(QUERY(TRANSPOSE(
 CHAR(IFNA(REGEXEXTRACT(TEXT(PI()^3*PRODUCT(LEN(IF(A1=TRUE, 
 {WHATTHEFOXSAY(); WHATTHEFOXSAY(); WHATTHEFOXSAY();
  WHATTHEFOXSAY(); WHATTHEFOXSAY(); WHATTHEFOXSAY();
  WHATTHEFOXSAY(); WHATTHEFOXSAY(); WHATTHEFOXSAY()}, 
 {WHATTHEFOXSAY(); WHATTHEFOXSAY(); WHATTHEFOXSAY();
  WHATTHEFOXSAY(); WHATTHEFOXSAY(); WHATTHEFOXSAY();
  WHATTHEFOXSAY(); WHATTHEFOXSAY(); WHATTHEFOXSAY()})))*
 TRANSPOSE(SEQUENCE(16, 3, 29, 73))*PI()^3, "0"), 
 JOIN("|", SORT({ROW(48:57); ROW(97:102)}, 1, ))), 102))),,
 9^9)), " ", ))

summary

  • are those strings really random? ofc not - they are just "random enough" because the "entropy" rules are known to us, but good luck cracking it by hand...
  • the main key point is that there is no 3rd party recalculation happening so the values stay paused and fresh values are generated upon switching the checkbox
  • will these strings repeat over a period of time? - yes they may, just as regular RAND() function
  • is there a possibility of generating two identical keys on one go? - yes, it may happen, but it can be countered by several ways to lower the chances or introduce UNIQUE() or adding more fox tails
  • please note this special occasion: onOpen event will re-calculate WHATTHEFOXSAY() one time after every checkbox switch !! so it's recommended to refresh the spreadsheet with F5 key after every checkbox switch you make if you need to work with frozen value

in case you wish to have a fresh automatically generated string in certain periods of time you can insert a volatile function TODAY() or NOW() - but note that recalculation will happen every time you edit any cell or every minute / hour when you don't edit anything
(depending on ..//File > Spreadsheet settings)

how about not to re-calculate on every cell change and re-calculate on every checkbox switch or every 6 minutes? once a week? every odd month? only mondays and wednesdays? every 11th hour? possible! we need one more simple IF statement placed anywhere in the spreadsheet which will host our logic gate and output the values in array {} constellation so we could refer with our fox formula to a cell which does not hold formula with volatile elements. example:

这篇关于如何停止/冻结/暂停不稳定的 RAND/RANDBETWEEN/RANDARRAY?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！

查看全文
相关文章
其他开发最新文章
热门教程
热门工具
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆