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

问题描述

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

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

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

那里有一个有问题的冰箱附件，还有许多非通用脚本变体，需要安装和安装.某种程度的改装/知识，那么该如何使用旧方法呢?

解决方案

首先，让我们看看狐狸的声音

  = WHATTHEFOXSAY() 

是Google复活节彩蛋的独特功能(由

这将使我们能够简单地通过切换复选框来完全控制重新计算.最后一步是通过将所有数字乘以 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.

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

• 3个唯一字符串
• 每个字符串包含16个字符
• 数字从 0 到 9
• 和小写字母，从 a 到 f

这时，我们的 SEQUENCE 将如下所示:

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

  16 =列3 =行29 =起点73 =踩 

对于第3和第4个自变量( 29 & 73 )，我们可以使用一些不错的

这时，我们在整个网格上都有很好的数字分布

---

---

最终的十六进制/密码生成器

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

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

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

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

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

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

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

基本上，我们的想法是从我们的数字网格中的上述字符串中首先找到 REGEXEXTRACT 个数字，以防万一没有匹配的情况，我们添加了 IFNA 后退并带有我们范围内的某个数字(在此示例中为 102 ):

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

并召唤 CHAR 个字符:

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

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

---

摘要

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

如果希望在特定时间段内有一个自动生成的新字符串，可以插入一个易失性函数 TODAY()或 NOW()-但请注意，每当您编辑任何单元格或不进行任何编辑时，每分钟/小时都会进行重新计算
(取决于..///File> Spreadsheet设置)

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

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