Google如何在模拟数据时防止负值？

Question

背景信息

• 星期一至星期五，我最多有15个小时可供工作。
• 每一天，从周一到周四，我都会在0-8之间随机工作几个小时.
• 周一到周四我可以工作0小时。
• 在星期五，我将工作，无论剩下的时间，从最多的15个小时。
• 我星期五必须工作至少一个小时。

公式使用

• (单元格: C4)星期一: RANDBETWEEN(0-8)
• (手机: D4)星期二: RANDBETWEEN(0-8)
• (手机: E4)星期三: RANDBETWEEN(0-8)
• (单元: F4)星期四: RANDBETWEEN(0-8)
• (单元: G4)星期五: 15和(C4:F4)
• 总时数: SUM(C4:G4)

发行

• 星期五的牢房偶尔会收到负数的工作时间。

示例输出的问题

输出

当前的解决方案

• 通过按空单元格上的“DEL”来更新RANDBETWEEN生成的值。这就迫使所有的值都改变了。 重复，直到星期五在单元格中收到正值为止。

Google工作表设置

目标

如果在周五的单元格中接收到负值，请让单元格自动更新。

可能的解决方案/想法

有没有办法迫使RANDBETWEEN号通过公式进行更新？

如果是，是否有一种方法可以设置一个WHILE循环来更新RANDBETWEEN值直到星期五的单元格有一个正数？

有办法在特定的单元格上运行脚本吗？其目的是模拟工作计划中变化的数据。

我确实试图通过一个脚本来完成这个任务，但是没有能够让单元格正确地更新，而其他时候它根本就不会更新。

function randomTotal() 
{
  var Monday = SpreadsheetApp.getActiveSheet().getRange('C4');
  var Tuesday = SpreadsheetApp.getActiveSheet().getRange('D4');
  var Wednesday = SpreadsheetApp.getActiveSheet().getRange('E4');
  var Thursday = SpreadsheetApp.getActiveSheet().getRange('F4');
  var Friday = SpreadsheetApp.getActiveSheet().getRange('G4');
  var FridayValue = SpreadsheetApp.getActiveSheet().getRange('G4').getValue();
  
  while(FridayValue < 0)
  {
    newTotal(Monday,Tuesday,Wednesday,Thursday,Friday);
    FridayValue = SpreadsheetApp.getActiveSheet().getRange('G4').getValue();
  }
}

function newTotal(Monday,Tuesday,Wednesday,Thursday,Friday)
{
  Monday.setFormula('=RANDBETWEEN(0,8)');
  Tuesday.setFormula('=RANDBETWEEN(0,8)');
  Wednesday.setFormula('=RANDBETWEEN(0,8)');
  Thursday.setFormula('=RANDBETWEEN(0,8)');
  Friday.setFormula('=15-SUM(C4:F4)');
}

Answer 1

这实际上可以在没有Google脚本的情况下完成。我建议单元格D4的下列公式

=RANDBETWEEN(0,IF(SUM($C4:C4)<=6,8,14-SUM($C4:C4)))

然后，您可以将它从D4复制/粘贴到E4和F4 (公式引用将有效)，并保持C4和G4的原样。那应该就行了！

您绝对可以通过编程来完成这一任务，但是通常情况下，如果可能的话，这通常是比较简单的方法。

要快速解释为什么会这样做:如果单元格到左边的和为<=6，那么您总是可以加起来8小时，因为它使您处于<= 14的总数范围内。但是，如果不是这样的话，你想从14个小时中减去多少个小时，因为14小时是你在星期一到星期四可以拥有的最大时间，并且在周五得到至少一个小时的剩余时间。

Answer 2

虽然给出的另一个答案过于复杂，但我还是进去并为此创建了一个脚本。

以下是代码：

function setSame() {
  console.log('Start check');
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheetMaster = ss.getSheetByName("Sheet2"); 
  var sortRange = sheetMaster.getRange('B3:E3');
  var forms = ['=RANDBETWEEN(0,8)','=RANDBETWEEN(0,8)','=RANDBETWEEN(0,8)','=RANDBETWEEN(0,8)'];

  sortRange.setValues([forms]);

  var mon = sheetMaster.getRange('B3').getValue();
  sortRange.getCell(1,1).setValue(mon);
  var tues = sheetMaster.getRange('C3').getValue();
  sortRange.getCell(1,2).setValue(tues);
  var wed = sheetMaster.getRange('D3').getValue();
  sortRange.getCell(1,3).setValue(wed);
  var thurs = sheetMaster.getRange('E3').getValue();
  sortRange.getCell(1,4).setValue(thurs);
  var fri = sheetMaster.getRange('F3').getValue();

  console.log('Monday: '+mon);
  console.log('Tuesday: '+tues);
  console.log('Wednesday: '+wed);
  console.log('Thursday: '+thurs);
  console.log('Friday: '+fri);

  if(fri < 1) {
    console.log("less");
    setSame();
  }
}

板材

setSame()函数是用onChange触发器设置的(触发器>添加触发器)。console.log()行的存在是为了调试目的，而不是必要的。

我相信有更好的方法来解决这个问题，但这是我能想到的解决这个问题的最快方法。