Google表格:如何触发随机函数? [英] Google Sheets: how to trigger random function?

问题描述

我在Google上有一张工作表，并且一直在寻找一种解决方案，以使随机脚本仅在需要时才起作用，而不是每次打开或刷新工作表时都起作用.

I have a sheet on Google and keep looking for a solution on how to make randomizing script work only when it's needed, not every time I open the sheet or refresh it.

这里是例子.让我们看一下这样的工作表:

Here's the example. Let's take the sheet looks like this:

B5单元具有以下代码:

B5 cell has the following code:

=index(B1:B3; randbetween(1;3); 1)

每次刷新或加载文档时，它会随机告诉我头发的颜色.

It randomly tells me the hair color every time I refresh or load the document.

但是，如果我只希望每周一次新发色，但又需要不时打开文档怎么办?

But what if I want the new hair color only once a week, but need to open the document from time to time?

我试图在代码上加一个标志.

I have tried to put a flag to the code.

并更改了B5的代码:

=if(B7=true; index(B1:B3; randbetween(1;3); 1); )

因此，仅发起当标志被检查RANDBETWEEN功能.

So it initiates randbetween function only when the flag is checked.

但是！取消选中该标志时，头发颜色显示为空(右，因为它在 IF 函数中具有空格).如何保持发色与以前相同，并且仅在检查标志后才能更改?

BUT! When the flag is unchecked, the hair color is shown empty (right, 'cause it has a space in IF function). How do I keep the hair color the same as it was before and make it change only if I check the flag?

在您的B5中使用:

=ARRAYFORMULA(IFNA(VLOOKUP(
 IFNA(REGEXEXTRACT(TEXT(PI()^3*PRODUCT(LEN(IF(B7=TRUE; 
 {WHATTHEFOXSAY(); WHATTHEFOXSAY(); WHATTHEFOXSAY();
  WHATTHEFOXSAY(); WHATTHEFOXSAY(); WHATTHEFOXSAY();
  WHATTHEFOXSAY(); WHATTHEFOXSAY(); WHATTHEFOXSAY()}; 
 {WHATTHEFOXSAY(); WHATTHEFOXSAY(); WHATTHEFOXSAY();
  WHATTHEFOXSAY(); WHATTHEFOXSAY(); WHATTHEFOXSAY();
  WHATTHEFOXSAY(); WHATTHEFOXSAY(); WHATTHEFOXSAY()})))*
 (TRANSPOSE(SEQUENCE(1; 1; 29; 73)))*PI()^3; "0"); 
 JOIN("|"; SORT({3;2;1}; 1; IFERROR(0/C2)))); 2); 
 {ROW(B1:B3)&""\ B1:B3}; 2; 0)))

对于每周重新计算，请在C1中使用:

and for weekly recalculation use this in C1:

=IF(IFERROR(WEEKDAY(TODAY(); 2); 0)=1; {"";1}; {"";0})

因此，每当您选中/取消选中复选框以及每个понедельник(星期一)时，B5的值都会更改

so the value of B5 will change every time you check / uncheck the checkbox and every понедельник (Monday)

这篇关于Google表格:如何触发随机函数?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！