我正在尝试使用递归 labmda 基于 randbetweeen 函数来构建计划生成器。
基本上，它需要做的事情：
- 基于包括本月开始和结束的其他2个单元格中的之间的之间生成一个随机日期。
- 查找A2＃在当天的转变，如果不是“关闭”，请将单元格的值设置为那个日期。如果关闭，请产生另一个日期。
这是我凌乱的公式。当然，它不起作用，因为我仍然不确定递归lambdas如何工作。另外，我相信公式存在逻辑错误。
=LAMBDA(f,l,counter,LET(randomd,IF(counter>70,"",RandomDate(RANDBETWEEN(f,l),counter+1)),shiftlookup,TEXT(XLOOKUP(a2#,Names,XLOOKUP(randomd,Dates,Schedule!$B$3:$AE$67)),"hh:mm"),IF((shiftlookup="08:00")+(shiftlookup="16:00")+(shiftlookup="18:00"),shiftlookup,randomd+1)))
截至目前，此 lambda 不允许我使用单元格引用来调用它。循环的退出条件需要是
IF(A2#="","",Lambda)
我不确定是否正确设置。
我正在附上一个让您查看我在做什么。
注意：有一个简单的宏嵌入来计算表，但我将其保存为无宏工作簿。
I'm trying to build a Schedule Generator using recursive LABMDA based on RANDBETWEEN function.
Basically, what it needs to do:
- Generate a random date using
RANDBETWEEN based on 2 other cells which include start and end of the month.
- Lookup A2#'s shift on that day, if it's not "OFF", set the cell's value to that date. If it's off, generate another date.
This is my messy formula. Of course it does not work, because I am still not sure how recursive lambdas work. Also, I believe there is a logical error in the formula.
=LAMBDA(f,l,counter,LET(randomd,IF(counter>70,"",RandomDate(RANDBETWEEN(f,l),counter+1)),shiftlookup,TEXT(XLOOKUP(a2#,Names,XLOOKUP(randomd,Dates,Schedule!$B$3:$AE$67)),"hh:mm"),IF((shiftlookup="08:00")+(shiftlookup="16:00")+(shiftlookup="18:00"),shiftlookup,randomd+1)))
As of now, this LAMBDA doesn't let me call it using cell references. The exit condition of the loop needs to be
IF(A2#="","",Lambda)
I'm not sure if I set it up right.
I'm attaching a sample file for you to see what I'm working on.
Note: There is a simple macro embedded to calculate the sheet, but I saved it as a macro-free workbook.
发布评论